Cloning PeopleSoft Database

Overview

This document describes the steps required to create a clone of the PeopleSoft production database. This is the most common task of a PeopleSoft Administrator / in any PeopleSoft upgrade project.

Here are some of the steps that need to be consider when creating a Development or Test environment from a production database on PeopleTools 8.4X.


Steps

1. Create a backup of the production database. (This is your source database)

2. Stop the Target Application Environment. (If application environment is running)



Application Servers
Process Schedulers
Web Servers

3. Overlay the target database with a recent backup of the production database.

4. Start the database

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

UPDATE PSDBOWNER SET DBOWNER = 'Database Name';


6. Set GUID to ' ' in PSOPTIONS table.

UPDATE PSOPTIONS SET GUID = ' ';

This will cause PeopleSoft to generate a new GUID so that change assistant can track it separately from the source database.

7. Delete the data from

Reporting tables
Process scheduler tables
Application messaging tables

This data isn't relevant in the target database.
Below DMS scripts are provided by PeopleSoft. Use these scripts to clean the above tables.
prcsclr.dms (Process Scheduler Tables)
rptclr.dms (Reporting Tables)
appmsgpurgeall.dms (Application Messaging Tables)

8. Reset email address to blank or predefined value.


Since you are creating a clone of the production database you will have actual email address of all the employees. Email address is a very critical data. 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.

Some of the common tables of PeopleSoft ERP are as follows

PSOPRDEFN
PS_ROLEXLATOPR
PSUSEREMAIL


You need to take care of Application specific tables. You can find out these tables by using below script.


SELECT RECNAME
FROM PSRECFIELD B
WHERE EXISTS (SELECT 'X' FROM PSRECDEFN A WHERE RECTYPE = 0 AND A.RECNAME = B.RECNAME)
AND B.FIELDNAME = 'EMAILID'


8. Change application password (VP1)

Logon to PeopleSoft data mover script and execute the below scripts if you want to chage the application password.

UPDATE PSOPRDEFN SET OPERPSWD = 'PASSWORD', ENCRYPTED = 0 WHERE OPRID = VP1;
ENCRYPT_PASSWORD VP1;