A Peoplesoft Database Refresh

For example, here the source database will be PRD, and the target database will be the TST database that you're refreshing from source database. These are exhaustive steps covering most of the tasks.

 

Prepare for refresh -

-Check and compare the tablespace size, used space and free space for both the databases. (DBA_DATA_FILES, DBA_FREE_SPACE).

-Alter tablespaces and check adequate disk space; add datafiles if required.

-Prepare an agreed-upon list of Peoplesoft objects that you need to remigrate after the refresh is complete. Use below SQL to identify project differences in TST and PRD.    

             SELECT PROJECTNAME, COUNT(OBJECTTYPE)

             FROM PSPROJECTITEM

             GROUP BY PROJECTNAME;

-Ensure the last day export backup of TST is complete.

-Comment cronjobs on TST.

-Report Node backup- EXPORT PS_CDM_DIST_NODE

-Backup Web Profile -

-Export any security that you need to preserve from the target database to a flat file. Data Mover is a nice tool to use for this since you can qualify each table with a list of operator ID's to export. The tables you should consider exporting for specific users are PSOPRDEFN, PSOPRALIAS, PSROLEUSER, PSUSERATTR, PSUSEREMAIL, PSUSERPRSNLOPTN, PS_ROLEXLATOPR and PS_RTE_CNTL_RUSER. There is a delivered script - securityexport.dms which can be used for this task.

-Notify users about refresh and the downtime.

 

 

REFRESH Database -

 

-Stop the target application environment, including application servers and process schedulers. Stopping the web server is optional since it doesn't connect directly to the database. Be sure to clear cache.

-Turn off archive logging in TST

             SQL> shutdown immediate;

             SQL> startup mount;

             SQL> ALTER DATABASE NOARCHIVELOG;

             SQL> ALTER DATABASE OPEN;

-Remove archive logs (if any)Drop SCHEMA objects in TST and ensure that all objects are dropped.

-Import into the target database recent backup of the Production database.

-Set DBNAME in PSDBOWNER back to the target database name.

-Set GUID to ' ' in the PSOPTIONS table. This will cause PeopleSoft to generate a new GUID so that change assistant can track it separately from the source database.

-Using datamover bootstrap mode, reset the ACCESSID and its password, encrypt it. Change Access Password (SYSADM)-
    CHANGE_ACCESS_PASSWORD SYSADM TSTPSWD;

-In data mover, change the user passwords that are configured in your application server, process scheduler, and integration broker configurations back to the pre-refresh values:
UPDATE PSOPRDEFN SET OPERPSWD = 'devpswd', ENCRYPTED = 0 WHERE OPRID = ('PSAPPS');
ENCRYPT_PASSWORD PSAPPS;
UPDATE PSOPRDEFN SET OPERPSWD = 'devpswd', ENCRYPTED = 0 WHERE OPRID = ('PTWEBSERVER');
ENCRYPT_PASSWORD PTWEBSERVER;

-Verify grant to PEOPLE account- 

       SELECT FROM DBA_TAB_PRIVS WHERE GRANTEE=’PEOPLE’;  /* This should return SELECT privileges on three security records.

-Test 2-tier connectivity

Post-refresh activities -

-Delete the data from the reporting tables, process scheduler tables and application messaging tables since this data isn't relevant in the target database.                               

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 

-Truncate PSACCESSLOG and history tables.

-Import the security that you exported - run securityimport.dms. Important PS objects/tables that are imported using securityimport are-

o    Users - PSOPRDEFN, PSUSEREMAIL , PSUSERATTR , PSOPRALIAS , PSROLEUSE,

o    Roles - PSROLEDEFN, PSROLECLASS,

o    Access Profiles - PSACCESSPRFL, 

-Import Permission Lists, Definition Security records, and Personalization etc.

-Truncate PSACCESSLOG and history tables.

-Delete application server cache and restart the domain.

-Delete webserver cache and restart the webserver domain. Clear application server/web server and process scheduler cache. Start the target application environment.

-Test PIA signon.

-Manual online configuration- Navigate to PeopleTools > Utilities > Options and update Environment Long/Short name, TimeZone, F1 help URL and help URL on web profile configuration. Update the Report Node configuration and verify your Process Scheduler Servers are using the correct configuration.

-Clear Process Scheduler cache, import report node (PS_CDM_DIST_NODE). Reconfigure process scheduler, boot and test run a report, verify the report runs to completion, posts, and allows you to view the report without having to sign in a second time.

-Verify performance indexes, recreate missing one.

-Check for invalid objects, recreate if any.

-Run data scrambling scripts. Reset everyone's e-mail address to a pre-defined value so that workflow messages from the Test environment don't get sent to real users.  e.g. SSN#, telephone# etc., emails in PSUSEREMAIL, PSOPRDEFN, PS_ROLEXLATOPR etc.

-Re-enable archive logging-

        SQL> startup mount;

          SQL> ALTER DATABASE ARCHIVELOG;

          SQL> ALTER DATABASE OPEN;

          SQL> ALTER SYSTEM SWITCH LOGFILE;

-Run hot RMAN backup for refreshed environment. Run RMAN backup for archive logs.

Copy all of the batch objects (SQR's, Crystal Reports, COBOL programs, etc.) on both UNIX and Windows servers from your source to your target environment to keep the environment in synch.

-Re-migrate projects/SQRs.

-Notify users that refresh is complete and environment is available again.

* In a DB2 database, run stats/re-orgs on tables after refresh and prior to running data masking/scrambling scripts otherwise you may experience slower performance of these scripts.