DATA REFRESH (PDADMIN+DB2ADMIN)

About

This document will help the user to perform the data refresh activity, for the PeopleSoft from one environment to another where the database are on different box or on same box. It contain complete steps of both PS Admin activity as well as database admin activity to perform the refresh. Follow the step by step instruction. Complete activity will take around 2-3 hours if you are doing it for the first time. It may take less time , once you will save the script for future use.

Assumptions

• User should know how to use PS Utility.
• User should have basic command line language knowledge.
• User should have database admin access to take backup and restoration of database.
• User should have access of PIA to take screen shots.
• User should have basic knowledge of how to run db2 commands.

Abbreviations

• DMS- Data Mover Script.
• SYSADM- system Administrator for Database.
Platform
• People tool: 8.49
• Database: db2 9.5
• OS: AIX
• Webserver : IBM Web sphere
• Application HRMS 9.0 and Finance 9.0
SUMMARY
Pre Refresh Activity

• Take backup of development database for roll back if required.
• Take the screenshot and note the URL of the important pages.
• Take the export of the environment tables and security tables.
• Shut down all the servers and start restoration of new database.

Database Refresh

• Do the databases refresh and hand it over to PSADMIN.

Post Refresh Activity

• Truncate unwanted Tables
• Run Import Script
• Change all required password and encrypt them.
• Make necessary changes to database.
• Grant the required permissions.
• Clear the server cache and reboot the server.
• Change all the required nodes and URL from the PIA.
• Test the application.
Detailed Step by Step Process:

Pre Refresh Activity

Take development database (for roll back if required).
• Login to putty or command line of db2.User should be SYSADM
• Find a directory which has more than 10 GB of free space.
• Use the following command to take the backup.[ db2 backup database online to ‘path to store backup’ with 3 buffers buffer 1024 compress INCLUDE LOGS without prompting].
• Here database name is name of the database for which you want to take backup,
Path to store backup is the directory where you want to save you backup image.
• Below is the screen shot of desired result. Please contact DB2 admin if backup was not successful.
• To check the backup Image .Go to path where back image is stored. Run the following command.[ db2ckbkp –h ]
NOTE: 1. Database should be configured to take online backup.
2. Path where you want to store the backup image should have at least 10 GB free space.
3. Database backup can only be taken with the sysadm user id.
4.Backup : Online , compressed , with Logs.

Take the screenshot of the following PIA pages and also note the URL for the same.

• People Tools->Process Scheduler-> Process Monitor -> Server list
• People Tools->Process Scheduler->Report Nodes
o Search and Take the screenshot.
o search and take screenshots of all respective nodes for that instance which Dist Nodes are listed in step 1.
• People Tools->Integration Broker->Configuration->Quick configurations
• People Tools->Integration Broker->Configuration->Gateways
o search and take the screenshot.
o Take screenshot of that gateway with the instance name which has local gateway as “Y”.
o Click on properties link [user/password – administrator/password] and take the screenshot.
• People Tools->Integration Broker->Integration setup->Nodes
o Take screenshot(all tabs) of node with the instance name
o Take screenshots of node QAS_REMOTE
• People Tools-> Integration Broker->Service Operations Monitor->Administration->Domain Status
• People Tools->Web Profile->Web Profile Configurations
• If SSO enable – Login to App Designer and take a backup of LDAPAUTH (Field) (mandatory) AND OPRID(field) People Code from FUNCLIB_LDAP record.
• People Tools -> Security – Security objects – single sign on
• People Tools -> Security – Security objects – sign on people code

NOTE: Copy the respective URL’s and paste it below each screenshots.

Go to data mover and take the back up of security table and environment tables.

For Security Tables
• Open the Data mover in regular mode.
• Open the DMS file for security tables export.
• Create the dms file to export the security tables, if you don’t have DMS file for import.
• General structure of DMS file.
o SET LOG \security.LOG;
o SET OUTPUT \security.DAT;
o EXPORT

;
• List of important security table can be found below. NOTE: -It is not necessary to take backup of the entire table listed in file. You have to choose table name according to your instance. Just comment those tables which are not present in your instance.

• Run the script.
• You will get the message script run successfully.
• Log file will be generated in the path which you have given in DMS file. Check the log file for the success of script. Following is an example of log file generated.

• List of security related tables are given below.

• If script does not run successfully, comment the table which led to an error, then run the script again.
• DAT file will be created in the path which you have given in the DMS file. This file will be used in post refresh activity, for data import.
• Save the DMS script for future use.

For Environment Tables
• Open the Data mover in regular mode.
• Open the DMS file for environment tables export.
• Create the dms file to export the environment tables, if you don’t have DMS file for import.
• General structure of DMS file.
o SET LOG \environment.LOG;
o SET OUTPUT \environment.DAT;
o EXPORT

;
• List of important environment table can be found below. NOTE: -It is not necessary to take backup of the entire table listed in file. You have to choose table name according to your instance. Just comment those tables which are not present in your instance.

• Run the script.
• You will get the message script run successfully.
• Log file will be generated in the path which you have given in DMS file. Check the log file for the success of script. Following is an example of log file generated.

• List of security related tables are given below.

• If script does not run successfully, comment the table which led to an error, then run the script again.
• DAT file will be created in the path which you have given in the DMS file. This file will be used in post refresh activity, for data import.
• Save the DMS script for future use.

Close all Servers

Close Webserver
• Go to web server Path : cd PS_HOME/webserv/domain name/bin
• Check the status of Webserver: ./serverStatus.sh server1
• Stop the webserver : ./ stopServer.sh server1
• Check the status of Webserver: ./serverStatus.sh server1
• Check no process should be running of this domain name : [ps –ef|grep java|grep ]

Close Application Server

• Go to PS Home .
• Start the PS Environment variable: . ./psconfig.sh
• Go to App server: cd appserv
• Start PS Admin Utility: ./psadmin
• Check status of application server. (It should be Up And running).[using Ps Admin Utility]
• Shutdown correct domain, using Normal Shut Down. [using Ps Admin Utility]
• Check Status of Application Server. (It Should be Down). [using Ps Admin Utility]
• Check no Process Should be running for Domain Name: [ps –ef|grep BBL|grep ]

Close Process Scheduler

• Go to PS Home : PS_Home\appserv
• Run Ps Admin Utility: Psadmin.exe
• Check Server Status.[Using Ps Admin Utility]
• Stop Process Server .[Using Ps Admin Utility]
• Check Server Status. .[Using Ps Admin Utility]

Database Restoration(db admin)

• Copy the backup image of production in Development server.
• Login with the sysadm user id .
• Make a directory to store the log files. Command : mkdir -p //logtarget
• Create the restoration script by following command: db2 “restore database from ” taken at into logtarget ” with 2 buffers buffer 1024 redirect generate script restore_psf.clp”
• Script will be generated : restore_psf.clp.
• Edit the generated script for table spaces path and file.
o Set PATH value
▪ Earlier value: PATH ‘///’
▪ New value : PATH ‘///’
▪ Eg: PATH ‘/home/db2inst2/PSFINPRO/PSFINPRO_0’ to PATH ‘/upg/data/PSFINUAT/PSFINUAT_0’
o Set FILE value
▪ Earlier value: FILE ‘///


▪ New value : PATH ‘///


▪ Eg: FILE ‘/home/db2inst2/PSFINPRO/AMAPP’ to FILE ‘/upg/data/PSFINUAT/AMAPP’
• Save the Script.
• Run the script with following command: db2 –tvf restore_psf.clp
• Check restoration status by command : db2 list utilities show detail
• Run the roll forward after the restoration command: db2 “rollforward database to end of logs and complete overflow log path (”)”
• Once the roll forward command completed try to connect to the database.
• Once database connection is established , start the post refresh activity.
Post Refresh Activity
Truncate unwanted Tables

▪ Login to database from sql developer or db2 command prompt using sysadm id.
▪ Truncate all unwanted table(App engine tables, Synchronous core tables, archive tables , process scheduler tables , report repository tables , informatica tables)
▪ List of tables can be found below

Run Import scripts

• Login to the data Mover with SYSADM user.
Security Tables Import
• Open the import script for security table, and run.
• If you don’t have the import script, build one import script using following guidelines
o Set the path of the DAT file generated during export.
o Set the path for the log file to generate logs.(it will be helpful if you get error while import).
o Import all the tables.
o Following is the example of import script.

• Run the Script.
• Desired Output, Script run successfully.
Environment Table Import

• Open the import script for security table, and run.
• If you don’t have the import script, build one import script using following guidelines
o Set the path of the DAT file generated during export.
o Set the path for the log file to generate logs.(it will be helpful if you get error while import).
o Import all the tables.
o Following is the example of import script.

• Run the Script.
• Desired Output, Script run successfully.
Update Important Tables

• Login to database.( using database user id ,either in command line or using sql developer)
• Run the following update command in an order.
o Update PSLOCK table:
UPDATE PSLOCK SET VERSION = VERSION + 1 WHERE OBJECTTYPENAME = ‘CLM’;
UPDATE PSLOCK SET VERSION = VERSION + 1 WHERE OBJECTTYPENAME = ‘UPM’;

o Update PSVERSION table:
UPDATE PSVERSION SET VERSION = VERSION + 1 WHERE OBJECTTYPENAME = ‘SYS’; UPDATE PSVERSION SET VERSION = VERSION + 1 WHERE OBJECTTYPENAME = ‘CLM’; UPDATE PSVERSION SET VERSION = VERSION + 1 WHERE OBJECTTYPENAME = ‘UPM’

o Update PSCLASSDEFN table:
UPDATE PSCLASSDEFN SET VERSION = (SELECT VERSION FROM PSVERSION WHERE OBJECTTYPENAME = ‘CLM’);
UPDATE PSOPRDEFN SET VERSION = (SELECT VERSION FROM PSVERSION WHERE OBJECTTYPENAME = ‘UPM’);

Grant Permissions

• Login to database.( using database user id ,either in command line or using sql developer)
• Run the following grant statement.
o Grant select on PSSTATUS;
o grant select on PSOPRDEFN;
o grant select on PSACCESSPRFL;
Update Passwords

• Login to Data mover in the boot Strap Mode.(user will be sysadm to be in boot strap mode .)
• Update psacessprfl using following command: UPDATE PSACCESSPRFL set ACCESSID = ” , ACCESSPSWD = ” , ENCRYPTED = 0, where SYMBOLICID = ‘symbolic ID of the database being refreshed’;
• Update PSDBOWNER using following command: update ps.PSDBOWNER set DBNAME = ”
• Update psoprdefn using following command: update psoprdefn set operpswd = ”,encrypted = 0 where oprid=’’

Encrypt password

• Login to Data mover in the boot Strap Mode.(user will be sysadm to be in boot strap mode .)
• Run following command : Encrypt_password *;

Clear cache
• Clear cache of application server.
• Clear Cache of webserver.
• Clear Cache of process Scheduler.
Start Server
• Start Application server.
• Start Web Server.
• Start Process Scheduler.
Change PIA Settings

• Login to PIA
• Go to : People Tools->Process Scheduler-> Process Monitor -> Server list. Compare it with the screen shot ,an make changes if required. Save the page
• Go to : People Tools->Process Scheduler->Report Nodes
o Search and change the field according to your screen shots.
• People Tools->Integration Broker->Configuration->Quick configurations
o Search and change the field according to your screen shots.

• People Tools->Integration Broker->Configuration->Gateways
o search and change the URL according to the screenshot you have taken.
o Click on properties link [user/password – administrator/password] and change the lield according to your screen shot.
• People Tools->Integration Broker->Integration setup->Nodes
o Search and change the field according to your screen shots.
• People Tools-> Integration Broker->Service Operations Monitor->Administration->Domain Status
o Search and change the field according to your screen shots.
• People Tools->Web Profile->Web Profile Configurations
• Search and change the field according to your screen shots.
• People Tools -> Security – Security objects – single sign on
o Search and change the field according to your screen shots.
• People Tools -> Security – Security objects – sign on people code
o Search and change the field according to your screen shots.

Sample checkout
Once the application is brought up please perform a sample checkout with all necessary screen shots and after successful completion please drop mail informing post refresh activity has been completed and application is back online.

August 2017
M T W T F S S
« Jul    
 123456
78910111213
14151617181920
21222324252627
28293031