Automating People Soft Instance Refreshes

Hi All,

Do you still do the PeopleSoft instance refreshes manually ?Configure the report nodes, integration settings, restore the database user and PeopleSoft application user manually after every refresh with production data ?
Well, no more ...In this post, i'll describe an approach to automate the instance refreshes ..

Here i assume you have the responsibility to restore the oracle database/schema backup in addition to taking care of people soft application.

I'll divide the steps in two categories :

1) Before Dropping the Old Database :
   
   i) Create an sql script, which backups the existing database users(with their       original passwords);
   ii) Create a data mover script(command line form) which takes backup of existing        application users       (passwords,roles, permission list, all other accesses including        every table related to people soft       security) and report node settings.
   iii)Now create a batch script which calls the sql script first, then invokes the data        mover from command   line to take security and report node tables backup and finally   include the expdp command to take a  backup of existing old database.

This way you just have to run the batch script and it does all the task you have to do before dropping the old database.

2) Drop the old database and create a new database with the same name .

3) After new database has been created :

    Create a batch file and include the following calls in that script line by line :

  i) Create a consolidated sql script with the 6 scripts, creating the psdbowner table, access id, creating           tablespaces and data files , connect id , creating required roles.
  ii)set oracle sid and import the data from dump file with impdp command .

  iii) Create a post refresh script which updates the tables with data from production to what it should be in      dev/test environment including updates to mask the emails, update the gateway url and other integration      broker tables, creating synonym for all the tables, creating the db links if any, clearing the message and      process request tables and other updates as per your project requirements .

  iv) Call data mover from command line and import the security and report node table backup(.dat file) that we took before dropping  the table.

   v) call a sql script which gathers the statistics for the tables.

   vi) Write cmd commands to zip the log folder, where logs for each and every step are created, and email to a configured mail id which you can verify from anywhere.

And this is all ... you have to call a batch file, drop and create database and call another batch file and you refresh is done !!!!

Note : If you have enough competency you can automate the drop and create of database as well and then you just have to start a batch file and wait until you receive an email of all the logs !!!

---**--- If you get stuck anywhere or need the scripts to complete the task, email me ...

Also there are other approaches to do the database refresh where drop of database is not require ..i'll explain this in another post ...