2 Jun 2013

DB Refresh(Pre & Post Acivities of DB Refresh)


REFRESH POST ACTIVITIES:
1)      Before refreshing DB from Production to UAT , first we need to run “SECURITY.DMS” through DataMover. The security export document find the below:

Run securityexport.dms  script as shown below:
======================================================================

SET OUTPUT C:\SECURITYEXPORT.DAT;
SET LOG C:\SECURITYEXPORT.LOG;

-- ACCESS PROFILES
EXPORT PSACCESSPRFL;

-- USERS
EXPORT PSOPRDEFN;
EXPORT PSOPRALIAS;
EXPORT PSROLEUSER;
EXPORT PSUSERATTR;
EXPORT PSUSEREMAIL;
EXPORT PSUSERPRSNLOPTN;
EXPORT PS_ROLEXLATOPR;
EXPORT PS_RTE_CNTL_RUSER;

-- ROLES
EXPORT PSROLEDEFN;
EXPORT PSROLEDEFNLANG;
EXPORT PSROLECANGRANT;
EXPORT PSROLECLASS;

-- PERMISSION LISTS
EXPORT PSCLASSDEFN;
EXPORT PSAUTHBUSCOMP;
EXPORT PSAUTHCHNLMON;
EXPORT PSAUTHCUBE;
EXPORT PSAUTHITEM;
EXPORT PSAUTHOPTN;
EXPORT PSAUTHPRCS;
EXPORT PSAUTHSIGNON;
EXPORT PSPRCSPRFL;
EXPORT PS_MC_OPR_SECURITY;
EXPORT PS_MC_OPRID;
EXPORT PS_SCRTY_ACC_GRP;
EXPORT PS_SCRTY_QUERY;

-- DEFINITION SECURITY
EXPORT PSOBJGROUP;
EXPORT PSOPROBJ;

-- PERSONALIZATIONS
EXPORT PSUSEROPTNDEFN;
EXPORT PSUSEROPTNLANG;
EXPORT PSOPTNCATGRPLNG;
EXPORT PSOPTNCATGRPTBL;
EXPORT PSOPTNCATTBL;
EXPORT PSOPTNCATLANG;

-- SECURITY OPTIONS
EXPORT PSSECOPTIONS;

-- SECURITY LINKS
EXPORT PSUSEROTHER;
EXPORT PSUSERSELFOTHER;
EXPORT PSROLEOTHER;
EXPORT PSPERMLISTOTHER;

-- USER ID TYPES
EXPORT PSOPRALIASTYPE;
EXPORT PSOPRALIASFIELD;

-- DELETE USER BYPASS TABLE
EXPORT PS_BYPASS_TABLE;

-- FORGOT EMAIL TEXT
EXPORT PSPSWDEMAIL;

-- PASSWORD HINTS
EXPORT PSPSWDHINT;

-- SIGNON PEOPLECODE
EXPORT PSSIGNONPPC;

-- DIRECTORY
EXPORT PSDSDIR;
EXPORT PSDSSRVR;
EXPORT DSCONNECTID;
EXPORT PSDSEXT_INSTALL;
EXPORT PSDSSECMAPMAIN;
EXPORT PSDSSECMAPSRVR;
EXPORT DSUSRPRFLMAP;
EXPORT PSDSUSERPRFL;
EXPORT PSDSSECROLERULE;
EXPORT DSSRCH_SBR;
EXPORT DSSRCHATTR;
EXPORT DSSECFILTER;
EXPORT PT_WF_NOT_DSCFG;
=======================================================================

This Script should run because it exports all users or saves  information from UAT DB, after refresh db we have to import these tables.
Refresh DATABASE:
1)      Login to production DB, (192.168.1.179) and  we need to create one directory and give the permissions to that directory like below:
Sql> CREATE OR REPLACE DIRECTORY  ps_nabadm  as  ‘/oradata1/exp/’;
Sql> SELECT SUM(BYTES/1024/1024) FROM DBA_SEGMENTS WHERE OWNER=’PS’;
Sql> SELECT SUM(BYTES/1024/1024) FROM DBA_SEGMENTS WHERE OWNER=’NABADM’;
Sql> SELECT SUM(BYTES/1024/1024) FROM DBA_SEGMENTS WHERE OWNER=’PEOPLE’;

2)      Then after completion of the above step, exp these schemas into ‘/oradata/exp/’ folder

Oradata1/exp# expdp schemas=nabadm directory=ps_nabadm dumpfile=nabadm. dmp logfile=naba.log

It will ask user name: /as sysdba

Oradata1/exp# expdp schemas=ps  directory=ps_nabadm dumpfile=ps.dmp  logfile=ps.log

Oradata1/exp# expdp schemas=people directory=ps_nabadm dumpfile=people.dmp logfile=people.log

3)      To check the TABLESPACES IN Production
(a)    Sql> desc dba_users;
(b)   Sql> select username,DEFAULT_TABLESPACE from dba_users where username=’NABADM’;
(c)    Sql> select TEMPORARY_TABLESPACE from dba_users where username=’NABADM’;

4)      After copying the .dmp  and .log file in the directory “/oradata/exp’
Copy the .dmp and .log files in UAT environment using FILEZILLA (E:\NABADM) IN UAT Env.
After copying .dmp and .log files in UAT , we have to import the data into UAT ENVIRONMENT.
5)      Before importing the data, we have to drop the ‘3’ schemas in UAT ENVIRONMENT and TABLESPACES
For  importing tablespaces find the below attachment

Create Table Spaces in Dev environment same as Prod:
=========================================================================
For Peoplesoft we need export only 3 schemas that is NABADM,PS,PEOPLE from production.

================================================================================================================================================================
In NDEV we need to drop the table spaces as we found in Production:
---------------------------------------------------------------------
Ex:

DROP TABLESPACE PSTEMP INCLUDING CONTENTS AND DATAFILES;                                                                       
DROP TABLESPACE PSDEFAULT INCLUDING CONTENTS AND DATAFILES;                                                                     
DROP TABLESPACE AAAPP INCLUDING CONTENTS AND DATAFILES;                                                                         
DROP TABLESPACE AALARGE INCLUDING CONTENTS AND DATAFILES;               etc...
---------------------------------------------------------------------------------
After dropping the table spaces again we need to create the table spaces

TABLESPACE_NAME-ndev
------------------------------
create tablespace PSTEMP datafile 'E:\oracle\oradata\NDEV\pstemp.dbf' size 23068672 autoextend on maxsize 34359721984 default storage ( initial 65536 minextents 1 maxextents unlimited) ;
create tablespace AAAPP datafile 'E:\oracle\oradata\NDEV\aaapp.dbf' size 23068672 autoextend on maxsize 34359721984 default storage ( initial 65536 minextents 1 maxextents unlimited) ;
create tablespace AALARGE datafile 'E:\oracle\oradata\NDEV\aalarge.dbf' size 7340032 autoextend on maxsize 34359721984  default storage ( initial 65536 minextents 1 maxextents unlimited) ;
create tablespace ADAPP datafile 'E:\oracle\oradata\NDEV\adapp.dbf' size 62914560 autoextend on maxsize 34359721984 default storage ( initial 65536 minextents 1 maxextents unlimited) ;
create tablespace AMAPP datafile 'E:\oracle\oradata\NDEV\amapp.dbf' size 10485760 autoextend on maxsize 34359721984 default storage ( initial 65536 minextents 1 maxextents unlimited) ;
create tablespace AVAPP datafile 'E:\oracle\oradata\NDEV\avapp.dbf' size 19922944 autoextend on maxsize 34359721984 default storage ( initial 65536 minextents 1 maxextents unlimited) ;
create tablespace BDAPP datafile 'E:\oracle\oradata\NDEV\bdapp.dbf' size 2097152 autoextend on maxsize 34359721984 default storage ( initial 65536 minextents 1 maxextents unlimited) ;
create tablespace PILARGE datafile 'E:\oracle\oradata\NDEV\pilarge.dbf' size 4194304 autoextend on maxsize 34359721984 default storage ( initial 65536 minextents 1 maxextents unlimited) ;
create tablespace PYLARGE datafile 'E:\oracle\oradata\NDEV\pylarge.dbf' size 3145728 autoextend on maxsize 34359721984 default storage ( initial 65536 minextents 1 maxextents unlimited) ;
create tablespace PYWORK datafile 'E:\oracle\oradata\NDEV\pywork.dbf' size 3145728 autoextend on maxsize 34359721984 default storage ( initial 65536 minextents 1 maxextents unlimited) ;
create tablespace SAAPP datafile 'E:\oracle\oradata\NDEV\saapp.dbf' size 314572800 autoextend on maxsize 34359721984 default storage ( initial 65536 minextents 1 maxextents unlimited) ;
create tablespace SACAPP datafile 'E:\oracle\oradata\NDEV\
create tablespace SALARGE datafile 'E:\oracle\oradata\NDEV\salarge.dbf' size 9437184 autoextend on maxsize 34359721984 default storage ( initial 65536 minextents 1 maxextents unlimited) ;
create tablespace SRAPP datafile 'E:\oracle\oradata\NDEV\srapp.dbf' size 158334976 autoextend on maxsize 34359721984 default storage ( initial 65536 minextents 1 maxextents unlimited) ;
create tablespace STAPP datafile 'E:\oracle\oradata\NDEV\stapp.dbf' size 33554432 autoextend on maxsize 34359721984 default storage ( initial 65536 minextents 1 maxextents unlimited) ;
create tablespace STLARGE datafile 'E:\oracle\oradata\NDEV\stlarge.dbf' size 13631488 autoextend on maxsize 34359721984 default storage ( initial 65536 minextents 1 maxextents unlimited) ;
=======================================================================
SQL> SELECT TABLESPACE_NAME  AUTOEXTENSIBLE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME=’PSDEFAULT’

It ask username: / as sysdba

SQL> SHO PARAMETER DB_NAME;

SQL> ARCHIVE LOG LIST;

TO DROP TABLES

SQL> DROP USER NABADM CASCADE;

SQL> DROP USER PS CASCADE;

SQL> DROP  USER PEOPLE CASCADE;

1)      After dropping the above users, we need to recreate the users in UAT DATABASE.
SQL> CREATE USER NABADM IDENTIFIED BY NABADM DEFAULT _TABLESPACE PSDEFAULT;
SQL> GRANT CREATE SESSION TO NABADM;
SQL> GRANT CONNECT,RESOURCE  TO NABADM;
2)      SQL> >CREATE USER PS IDENTIFIED BY PS DEFAULT TABLESPACE PSDEFAULT;

SQL> GRANT CREATE SESSION TO PS;

SQL> GRANT CONNECT,RESOURCE  TO PS;

3)      SQL> >CREATE USER PEOPLE IDENTIFIED BY PEOPLE  DEFAULT TABLESPACE PSDEFAULT;

SQL> GRANT CREATE SESSION TO PEOPLE;

SQL> GRANT CONNECT,RESOURCE  TO PEOPLE;

4)      Need to alter PSDEFAULT  TABLESPACE
              SQL> DBA_DATA_FILES
             SQL> ALTER TABLESPACE PSDEFAULT ADD DATAFILE  ‘E:\ORADATA\UATXYZ\PSDEFAULT03.DBF’        SIZE  8g autoextend on
        SQL>CREATE OR REPLACE DIRECTORY nabadm as ‘E:\nabadm\’;
Importing DB IN UAT:
1)      Login to UAT(192.168.1.185) and open command prompt

C:\users\administrator>impdp schemas=nabadm directory=nabadm dumpfile=nabadm.dmp logfile=naba.log

C:\users\administrator> impdp schemas=ps  directory=nabadm dumpfile=ps.dmp  logfile=ps.log

C:\users\administrator> impdp schemas=people directory=nabadm dumpfile=people.dmp logfile=people.log

2)      TO check it is importing or not

SQL>SELECT SUM(BYTES/1024/1024) FROM DBA_SEGMENTS WHERE OWNER=’NABADM’
SQL>/
SQL>SELECT SUM(BYTES/1024/1024) FROM DBA_SEGMENTS WHERE OWNER=’PS’
SQL>/
SQL>SELECT SUM(BYTES/1024/1024) FROM DBA_SEGMENTS WHERE OWNER=’PEOPLE’
SQL>/

3)      CHECK WHETHER TABLES ARE SHOWING SAME WHICH WERE IMPORTED FROM PRODUCTION:

SQL> SELECT COUNT(*) FROM TAB;    IN UAT
SQL>SELECT COUNT(*) FROM TAB;     IN PRODUCTION
SQL>SELECT COUNT(*) FROM DBA_OBJECTS WHERE OWNER=’NABADM’ AND OBJECT_TYPE=’TABLE’;

TABLES COUNT SHOULD BE BOTH IN BOTH ENVIRONMENTS.

*********************REFRESH END********************************************

POST ACTIVITIES OF DB REFRESH:

THRU SYS LOGIN

UPDATE PSDBOWNER SET DBNAME='UATXYZ' WHERE OWNERID='NABADM';

select GUID FROM PSOPTIONS;

update psoptions set GUID=' ';

DATA MOVER

UPDATE PSACCESSPRFL SET ACCESSID = 'NABADM', SYMBOLICID = 'SYSADM1', ACCESSPSWD = 'NABARD', VERSION = 0, ENCRYPTED = 0;

UPDATE PSOPRDEFN SET OPERPSWD = 'PS', ACCTLOCK = 0, ENCRYPTED = 0 WHERE OPRID = 'PS';

SET LOG C:\temp\encryptA1.LOG;
ENCRYPT_PASSWORD *;

THRU TOAD

update PSUSEREMAIL set EMAILID='  ';
update PS_ROLEXLATOPR set EMAILID='  ';
update PSOPRDEFN set EMAILID='  ';
update PS_EMAIL_ADDRESSES set EMAIL_ADDR='  ';

Run the below three scripts THRU DATA MOVER

1) Run prcsclr.dms - this will cleanup process scheduler tables. (PSPRCSRQST, PSPRCSQUE, PSPRCSPARMS, PS_MESSAGE_LOG etc.)                                                  

2)Run rptclr.dms - this will cleanup Report Manager tables. (PS_CDM_LIST, PS_CDM_AUTH etc.)

3) Cleanup messaging tables, run appmsgpurgeall.dms 

THROUGH COMMAND PROMPT ---Login with ACCESSID-NABADM

SQL> GRANT SELECT ON PSSTATUS TO PEOPLE;
SQL>GRANT SELECT ON PSOPRDEFN TO PEOPLE;
SQL>GRANT SELECT ON PSACCESSPRFL TO PEOPLE;

NOTE: Change the ftp address in PIA Page of this table: PS_ GP_SS_PSLP_FTP
Go to: PeopleTools->Utilities->Administration->URLS-> Search for GP_SS_PSLP_FTP

And change the URL AS: ftp://payslip:payslip@192.168.1.175/psoft/uatxyz

OR IN SQL EXECUTE THIS QUERY TO CHANGE FTP SITE ADDRESS
update psurldefn set URL='ftp://payslip:payslip@192.168.1.175/psoft/test' where GP_SS_PSLP_FTP;


IMPORTING SECURITY.IMPORT THROUTH DATAMOVER

1)      FINALLY WE NEED TO IMPORT THE SECURITY TABLES , WHICH WERE EXPORTED IN THE FIRST STEP.
Run Securityimport.dms script as shown below:
=================================================================

SET INPUT C:\SECURITYEXPORT.DAT;
SET LOG C:\SECURITYIMPORT.LOG;


IMPORT *;
=================================================================
2)      THEN BOOT THE APPSERVER AND PROCESS SCHEDULER SERVER AND LOGIN TO PIA.
CHANGE THE SETTINGS IN “REPORT NODE”

3)      CHANGE THE URLS IN PEOPLETOOLS->INTEGRATION BROKER->CONFIGURATION GATEWAYS


****************************END*************************************




Posted by at No comments:
Subscribe to: Posts (Atom)