ERP Associates, Inc.

Update: You may find a more current version of this article in the PS Corner Wiki.

One of the most common tasks of a PeopleSoft administrator or DBA is to clone a development or test database from a production database. I just wanted to take a minute to lay out the steps you'll want to consider when refreshing PeopleSoft databases that are running PeopleTools 8.4x.

I'm sure this list isn't complete for every installation and it can certainly be improved. Please let me know if you see something that I missed, or if you have a suggestion to improve the process.

For purposes of discussion, the source database will be production, and the target database will be the dev/test database that you're overlaying.

1) 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.

2) 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.

3) Overlay the target database with a recent backup of the Production database. The exact process will differ for your database platform, but your DBA should be able to do this with minimal direction.

4) After the database comes back on-line, set DBNAME in PSDBOWNER back to the target database name.

5) 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.

6) Delete the data from the reporting tables, process scheduler tables and application messaging tables since this data isn't relevant in the target database. Run prcsclr.dms, rptclr.dms and appmsgpurgeall.dms. In addition, I also delete from the report manager tables PSRF_RATTR_TBL; PSRF_RSCRTY_TBL; PSRF_RINFO_TBL; and delete from PSRF_FINFO_TBL where PSRF_PRNT_FLDR_ID <> 0;

7) If you have a script to 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, run it now. It should update the PSUSEREMAIL and PS_ROLEXLATOPR tables.

8) Log on to the target database with data mover and run the following command to set the SYSADM password and any other back to it's pre-refreshed value:
CHANGE_ACCESS_PASSWORD SYSADM1 MYSECRETPWD;

DBRefresh.DMS is an example data mover script that performs steps 5-8.

9) Import the security that you exported in step 1.

10) While you're 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;

11) Clear application server, web server and process scheduler cache if you haven't already done it. Start the target application environment.

12) Log on through the web front end.

13) Update the Report Node configuration and verify your Process Scheduler Servers are using the correct configuration. Make any Web Profile changes if needed.

14) Navigate to PeopleTools > Utilities > Options and update the database name and description.

15) Change password rules as appropriate for a development environment.

16) Change your default local node if it should be named differently than the default local node from the source database. If it's not named differently, you may be at risk for a single sign-on vulnerability.

17) Reconfigure attachment servers (if used)

18) Reconfigure REN servers and clusters if the application server didn't configure it correctly at startup.

19) Make sure you copy all of the batch objects (SQR's, Crystal Reports, COBOL programs, etc.) from your source to your target environment to keep the environment in synch. Don't forget to do this on both UNIX and Windows servers.

20) Perform some sanity checks to make sure the environment is behaving itself before you let users back into it. At a minimum you should verify you can log on and 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.