Skip to content

PeopleSoft database refresh steps

October 11, 2010

Below are steps can we followed for pre and post refresh of PeopleSoft application environment.Please also  copy the SQR,COBOL,Crystal for file object refresh.

  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:
    — Author: Brent Martin, ERP Associates Inc.
    — Date: February 10, 2006
    — Purpose: Reset application passwords and purge PeopleTools tables after a database refresh so that
    — the application can be started with minimal reconfiguration.
    — Compatibility: This works with PeopleTools 8.4x releases
    — Notice: This script modifies data in your PeopleSoft database. It changes account passwords, and
    — it deletes data from process scheduler, report repository and integration broker tables.
    — It as provided for reference purposes only. Use it at your own risk.

    set log c:\temp\dbrefresh.log;

    — Change Access Password (SYSADM)

    — Change Application Password (PSAPPS)
    update psoprdefn set OPERPSWD = ‘changeme1’, encrypted = 0 where oprid = ‘PSAPPS’;
    encrypt_password PSAPPS;

    — Set GUID to blank so PSEMAgent and Change Assistant doesn’t get confused
    update psoptions set guid = ‘ ‘;

    — Purge Process Scheduler
    RUN h:\fdmo881\scripts\PRCSCLR.DMS;

    — Purge Report Repository Tables
    RUN h:\fdmo881\scripts\RPTCLR.DMS;

    — Purge Application Messaging RUN h:\fdmo881\scripts\APPMSGPURGEALL.DMS;

  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.

From → PeopleSoft

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: