RMAN

-- Launch rman from the command line

C:\>rman target /

-- Check the RMAN configuration

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'E:\oracle\ORABACKUP\rman\backups\UAT\CONTROLFILE_%d_%F';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 3;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT 'E:\oracle\ORABACKUP\rman\backups\UAT\%U' MAXPIECESIZE 2000 M;
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT 'E:\oracle\ORABACKUP\rman\backups\UAT\%U' MAXPIECESIZE 2000 M;
CONFIGURE CHANNEL 3 DEVICE TYPE DISK FORMAT 'E:\oracle\ORABACKUP\rman\backups\UAT\%U' MAXPIECESIZE 2000 M;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE ENCRYPTION FOR DATABASE OFF;
CONFIGURE ENCRYPTION ALGORITHM 'AES128';
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\10.2.0\DB_1\DATABASE\SNCFUAT.ORA'; # default

 

-- To make changes just issue the relevant above commands with the appropriate change; for example the below changes the retention policy to a recovery window of 15 days:

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 15 DAYS;

old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 15 DAYS;
new RMAN configuration parameters are successfully stored

-- Checking the configuration again shows that the recovery window is now set to 15 days:

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 15 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'E:\oracle\ORABACKUP\rman\backups\UAT\CONTROLFILE_%d_%F';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 3;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT 'E:\oracle\ORABACKUP\rman\backups\UAT\%U' MAXPIECESIZE 2000 M;
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT 'E:\oracle\ORABACKUP\rman\backups\UAT\%U' MAXPIECESIZE 2000 M;
CONFIGURE CHANNEL 3 DEVICE TYPE DISK FORMAT 'E:\oracle\ORABACKUP\rman\backups\UAT\%U' MAXPIECESIZE 2000 M;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE ENCRYPTION FOR DATABASE OFF;
CONFIGURE ENCRYPTION ALGORITHM 'AES128';
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\10.2.0\DB_1\DATABASE\SNCFUAT.ORA'; # default

 

-- The below will backup the database and archive logs; will delete the original archive logs that were backed up by this command; delete old backups beyond the abover retention policy recovery window (e.g. older than 15 days); and verify that all required backups needed for recovery are in place:

RMAN> run

    {
     BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;
     DELETE NOPROMPT OBSOLETE;
     CROSSCHECK BACKUP;
    }

-- The below command will backup archive logs and delete the originals that were backed up:

RMAN> backup archivelog all delete input;

-- The below command verifies that all required backups needed for recovery are in place:

RMAN> crosscheck backup;

-- The below will perform a full recovery:

RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;


-- The below will recover a specific tablespace; the datafile must be taken off line first if the database is up:

RMAN> restore tablespace users;

RMAN> recover tablespace users;

-- The below will recover a specific datafile; must be taken offline first if the database is up:

RMAN> restore datafile 'D:\oradata\UAT\users_01.dbf';

RMAN> recover datafile 'D:\oradata\UAT\users_01.dbf';

-- The below will perform point in time recovery:

run {
shutdown immediate;
startup mount;
set until time 'APR 15 2007 09:00:00';
# set until scn 1000; # alternatively, you can specify SCN -- 1000 will restore up to 999
# set until sequence 1234; # alternatively, you can specify log sequence number -- 1234 will restore up to 1233
restore database;
recover database;
alter database open resetlogs;
}

Cloning Oracle Database - Cold Cloning, Hot Cloning

Clone an Oracle Database using Cold Physical Backup

Database Name: troy

Source Database side: (Troy database)

Cold Backup Steps:

1. Get the file path information using below query

Select name from v$datafile;
select member from v$logfile;
select name from v$controlfile;

2. Parameter file backup

If troy database running on spfile

Create pfile=’/u01/backup/inittroy.ora’ from spfile;

If database running in pfile using os command to copy the pfile and placed in backup path.

3.Taken the control file backup
Alter database backup controlfile to trace as ‘/u01/backup/control01.ora’;

4.Shutdown immediate

5.Copy all the data files/log files using os command & placed in backup path.

6.Startup the database.


Clone Database side: (Clone database)

Database Name: Clone

Clone Database Steps:

1.Create the appropriate folder in corresponding path & placed the backup files in corresponding folder.(bdump,udump,create,pfile,cdump,oradata)

2.Change the init.ora parameter like control file path, dbname, instance name etc...

3.Create the password file using orapwd utility.

(Database in windows we need to create the service id using oradim utility)

4.Startup the Database in NOMOUNT stage.

5.Create the control file for cloning database.

Using backup controlfile trace to generate the create controlfile scripts.
Change the Database name & files path, also change 'REUSE' needs to be changed to 'SET'.


CREATE CONTROLFILE SET DATABASE "clone" RESETLOGS FORCE LOGGING NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 '/U01/oradata/clone/redo01.log' SIZE 200M,
GROUP 2 '/U01/oradata/clone/redo02.log' SIZE 200M,
GROUP 3 '/U01/oradata/clone/redo03.log' SIZE 200M
DATAFILE
'/U01/oradata/clone/system01.dbf',
'/U01/oradata/clone/undotbs01.dbf',
'/U01/oradata/clone/users01.dbf',
CHARACTER SET WE8ISO8859P1;

Note: placed the script in sql prompt. Now controlfile created.

6.Now open the database.

Alter database open resetlogs;

Note: Check the logfile, datafiles & instance status

**********************************************************************************

Clone an Oracle Database using Hot Physical Backup

Database Name: troy

Database must be in Archive log mode.

Source Database side: (Troy database)

Hot Backup Steps:

1.Get the file path information using below query.
Select tablespace_name, file_name from dba_data_files order by 1;

2. Parameter file backup

If troy database running on spfile

Create pfile=’/u01/backup/inittroy.ora’ from spfile;

If database running in pfile using os command to copy the pfile and placed in backup path.

3.Put the tablespace in begin backup mode Using os command to copy the datafiles belongs to begin backup mode tablespace & placed in backup path. (Refer below example)
4.Once copied the datafile, release the tablespace from begin backup mode to end backup
5.Repeat the steps 1-3 for all your tablespaces.
6.Taken the controlfile backup
Alter database backup controlfile to trace as ‘/u01/backup/control01.ora’;

7.Backup all your archive log files between the previous backup and the new backup as well.

Example:
steps:
2.Alter tablespace system begin backup;

To ensure the begin backup mode tablespace using below query
Select * from v$backup; (refer the Change#, Time column)

3. Host cp /u01/oracle/raja/system1.DBF /u01/backup/system01.dbf à using os command to copy the datafile.
4. Alter tablespace system end backup;
To ensure the begin backup mode tablespace using below query
Select * from v$backup;



Clone Database side: (Clone database)

Database Name: Clone

Clone Database Steps:

1.Create the appropriate folder in corresponding path & placed the backup files in corresponding folder.(bdump,udump,create,pfile,cdump,oradata)

2.Change the init.ora parameter like control file path, dbname, instance name etc...

3.Create the password file using orapwd utility.

(Database in windows we need to create the service id using oradim utility)

4.Startup the Database in NOMOUNT stage.


5.Create the control file for cloning database.

Using backup controlfile trace to generate the create controlfile scripts.
Change the Database name & files path, also change 'REUSE' needs to be changed to 'SET'.


CREATE CONTROLFILE SET DATABASE "clone" RESETLOGS FORCE LOGGING NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 '/U01/oradata/clone/redo01.log' SIZE 200M,
GROUP 2 '/U01/oradata/clone/redo02.log' SIZE 200M,
GROUP 3 '/U01/oradata/clone/redo03.log' SIZE 200M
DATAFILE
'/U01/oradata/clone/system01.dbf',
'/U01/oradata/clone/undotbs01.dbf',
'/U01/oradata/clone/users01.dbf',
CHARACTER SET WE8ISO8859P1;

Note: placed the script in sql prompt. Now controlfile created.

6.Recover the database using controlfile.

Recover database using backup controlfile until cancel;

7.Now open the database.

Alter database open resetlogs;

Note: Check the logfile, datafiles status.

Grant access for a User to perform object migration

Grant access for a User to perform object migration

How to grant access for a User to perform object migration

To create user who can perform object migration from one environment to another environment, without having write access to the Application Designer objects like record, field, Application Engine, PepleCode etc.

Solution:

Login to PIA:

Go to Home > People Tools > Maintain Security > Use > Permission Lists open the permission list the user has.  Then go to People Tools tab, tick Application Designer Access.  You may set different objects permissions to read only if you click the links for Definition Permissions, Tools Permissions, and Miscellaneous Permissions.
You should have "full access" to project.

You can set Build script only or more in Build / Data Admin of Tools permission. Once the user has this permission list, the user should be able to migrate the project and build the project only.

Application Designer - Change Control

To enable change control in the Application Designer, navigate to the link [shown in the picture] in the application designer and check the check box appropriately.

• Use change control locking

• Use change control history

   

PSAPPSRV - How to connect to the Oracle database

PSAPPSRV - How to connect to the Oracle database

Configuration file

UseLocalOracleDB=0

The configuration file for the application server (psappsrv.cfg) has a section Database Options, and in that section there is a parameter UseLocalOracleDb. Possible settings are zero (off / default value) and one (on). This parameter is used to determine how to connect to the Oracle database.

A value of zero for this parameter will cause the domain to read the tnsnames.ora at 'connect time'. Feedback from customers tells us that the domains will start slower, help get around problems with shutting down the application server, and result in less remote call errors, with this setting.

A value of one is appropriate only if the database and application server are built on the same machine. This is sometimes used if the tnsnames.ora is not yet set. It will cause the domain to boot up faster.

Application server load balancing and failover

Application server load balancing and failover

In a previous blog’s entry, I explained about Multiple application messaging servers on Peopletools 8.51 and facilities for load balance and failover on messaging domain server.
In a very similar way, the application server can also be used in load balance and failover configuration.
First of all, you need at least two applications servers, please note that if you are on the same server, then domain name and JSL (jolt) port should be different from each other. Of course, there are not many advantages to have this configuration if working on one and only one server, but rather good to have different server, if one crash, the other one takes place.
Here we go.
First application server :
 
And the second one :

Please, note that whether I name one “first” and the other one “second”, there is no precedence of one compared to the other by default (except if you define a weight in configuration.properties, but let’s leave it for later explanation).
Then starting each application server
 
Note that the number of listened ports regarding Jolt is defined is the psappsrv.cfg of the domain as following :
[JOLT Listener]
;=========================================================================
; Settings for JOLT Listener
;=========================================================================
Address=%PS_MACH%
Port=9000
Encryption=0
Min Handlers=5
Max Handlers=7 In the current case, the ports between 9000 and 9005 will be immediately in use after started :
[psadm2@psovmfscmfp1 APPDOM]$ netstat -an|grep 900
tcp        0      0 192.168.1.150:9000          0.0.0.0:*                   LISTEN
tcp        0      0 192.168.1.150:9001          0.0.0.0:*                   LISTEN
tcp        0      0 192.168.1.150:9002          0.0.0.0:*                   LISTEN
tcp        0      0 192.168.1.150:9003          0.0.0.0:*                   LISTEN
tcp        0      0 192.168.1.150:9004          0.0.0.0:*                   LISTEN
tcp        0      0 192.168.1.150:9005          0.0.0.0:*                   LISTEN
[psadm2@psovmfscmfp1 APPDOM]$ It means the second domain needs to be configured with a port above 9005.
 

Here’s the output from the database side, since my two apps are on the same machine, there’s nothing really useful so far, cannot distinguished which domain a database session is coming from :
SQL> select username,program, client_info from v$session where username = 'SYSADM'
SQL> /
USERNAME                       PROGRAM                                          CLIENT_INFO
------------------------------ ------------------------------------------------ ----------------------------------------------------------------
SYSADM                         PSSUBDSP@psovmfscmfp1.phoenix.nga (TNS V1-V3)    VP1,psadm2,psovmfscmfp1.phoenix.nga,,PSSUBDSP,
SYSADM                         PSSUBHND@psovmfscmfp1.phoenix.nga (TNS V1-V3)    VP1,psadm2,psovmfscmfp1.phoenix.nga,,PSSUBHND,
SYSADM                         PSPUBDSP@psovmfscmfp1.phoenix.nga (TNS V1-V3)    VP1,psadm2,psovmfscmfp1.phoenix.nga,,PSPUBDSP,
SYSADM                         PSBRKDSP@psovmfscmfp1.phoenix.nga (TNS V1-V3)    VP1,psadm2,psovmfscmfp1.phoenix.nga,,PSBRKDSP,
SYSADM                         PSPUBHND@psovmfscmfp1.phoenix.nga (TNS V1-V3)    VP1,psadm2,psovmfscmfp1.phoenix.nga,,PSPUBHND,
SYSADM                         PSBRKHND@psovmfscmfp1.phoenix.nga (TNS V1-V3)    VP1,psadm2,psovmfscmfp1.phoenix.nga,,PSBRKHND,
SYSADM                         PSSAMSRV@psovmfscmfp1.phoenix.nga (TNS V1-V3)    VP1,psadm2,psovmfscmfp1.phoenix.nga,,PSSAMSRV,
SYSADM                         PSQRYSRV@psovmfscmfp1.phoenix.nga (TNS V1-V3)    VP1,psadm2,psovmfscmfp1.phoenix.nga,,PSQRYSRV,
SYSADM                         PSAPPSRV@psovmfscmfp1.phoenix.nga (TNS V1-V3)    VP1,psadm2,psovmfscmfp1.phoenix.nga,,PSAPPSRV,
SYSADM                         PSAPPSRV@psovmfscmfp1.phoenix.nga (TNS V1-V3)    VP1,psadm2,psovmfscmfp1.phoenix.nga,,PSAPPSRV,
SYSADM                         PSMONITORSRV@psovmfscmfp1.phoenix.nga (TNS V1-V3 VP1,psadm2,psovmfscmfp1.phoenix.nga,,PSMONITORSRV,
SYSADM                         PSSUBDSP@psovmfscmfp1.phoenix.nga (TNS V1-V3)    VP1,psadm2,psovmfscmfp1.phoenix.nga,,PSSUBDSP,
SYSADM                         PSSUBHND@psovmfscmfp1.phoenix.nga (TNS V1-V3)    VP1,psadm2,psovmfscmfp1.phoenix.nga,,PSSUBHND,
SYSADM                         PSPUBDSP@psovmfscmfp1.phoenix.nga (TNS V1-V3)    VP1,psadm2,psovmfscmfp1.phoenix.nga,,PSPUBDSP,
SYSADM                         PSPUBHND@psovmfscmfp1.phoenix.nga (TNS V1-V3)    VP1,psadm2,psovmfscmfp1.phoenix.nga,,PSPUBHND,
SYSADM                         PSBRKDSP@psovmfscmfp1.phoenix.nga (TNS V1-V3)    VP1,psadm2,psovmfscmfp1.phoenix.nga,,PSBRKDSP,
SYSADM                         PSMONITORSRV@psovmfscmfp1.phoenix.nga (TNS V1-V3 VP1,psadm2,psovmfscmfp1.phoenix.nga,,PSMONITORSRV,
SYSADM                         PSBRKHND@psovmfscmfp1.phoenix.nga (TNS V1-V3)    VP1,psadm2,psovmfscmfp1.phoenix.nga,,PSBRKHND,
SYSADM                         PSAPPSRV@psovmfscmfp1.phoenix.nga (TNS V1-V3)    VP1,psadm2,psovmfscmfp1.phoenix.nga,,PSAPPSRV,
SYSADM                         PSSAMSRV@psovmfscmfp1.phoenix.nga (TNS V1-V3)    VP1,psadm2,psovmfscmfp1.phoenix.nga,,PSSAMSRV,
SYSADM                         PSAPPSRV@psovmfscmfp1.phoenix.nga (TNS V1-V3)    VP1,psadm2,psovmfscmfp1.phoenix.nga,,PSAPPSRV,
21 rows selected.

Now, webserver configuration to be done in configuration.properties file ($PS_HOME/webserv/peoplesoft//applications/peoplesoft/PORTAL.war/WEB-INF/psftdocs/ps).
This is a easy configuration, put the application servers separated by simple comma as following :
[psadm2@psovmfscmfp1 peoplesoft]$ more ./applications/peoplesoft/PORTAL.war/WEB-INF/psftdocs/ps/configuration.properties
<<...snipped...>>
# psserver must point to your application server machine name (recommended) or IP address
# and JOLT port.  The PeopleTools release of the application server must match the PeopleTools
# release of the PeopleSoft files on the web server.
#
# To enable jolt failover and load balancing, provide a list of application server
# domains in the format of;  psserver=AppSrvr:JSLport,...
# For example:  psserver=SERVER1:9000,SERVER2:9010,SERVER3:9020
## sd="Application Server Connect String", ld="This string embodies the failover and load balancing string for PIA to connect to Application Server domains.  Consult the PeopleB
ooks recommendations on load balancing and failover for more information.  The format is psserver=AppSrvr:JSLport where AppSrvr is your fully qualified domain name.   Multiple d
omains should be comma separated", tip="Format: psserver=AppSrvr:JSLport where AppSrvr is your fully qualified domain name, e.g  psserver=SERVER1.oracle.com:9000,SERVER2.oracle.
com:9010"
psserver=psovmfscmfp1:9000,psovmfscmfp1:9010 ## sd="Jolt Pooling", dt="c", ld="Enable Jolt Pooling"
joltPooling=true
<<...snipped...>> Configured as above, all the application servers have the same weight (the same priority) in the round-robin algorithm. This algorithm affect ingoing transaction randomly to one or the other configured application server, there’s no priority, no load checkup, no network checkup, it’s just random (find out more in the My Oracle Support note E-WS: Load Balancing Options for Web Server and Application Server [ID 618367.1]).
But we could also introduced weight (from 1 to 10) on application server to make one precedence on the other as following :
psserver=psovmfscmfp1:9000#10,psovmfscmfp1:9010#1 In that case, the first server can takes extra work for an heavy load compared to the second one. 

Not only the load balance will be set, but the failover as well.

After the first connection to the front end application, we can check the client status of both application servers :
 
We can see a first pool as been instantiated.
Since round-robin algorithm is randomly given connection to the application server, it’s not always easy to show when alone, furthermore, the pool connection are making it even less simple.
But you probably can check it from the back end :
SQL> select username,program, client_info from v$session where username = 'SYSADM'
  2* order by 2
SQL> /
USERNAME                       PROGRAM                                          CLIENT_INFO
------------------------------ ------------------------------------------------ ----------------------------------------------------------------
SYSADM                         PSAPPSRV@psovmfscmfp1.phoenix.nga (TNS V1-V3)    NGA,,192.168.1.202,APPDOM,PSAPPSRV,
SYSADM                         PSAPPSRV@psovmfscmfp1.phoenix.nga (TNS V1-V3)    VP1,,192.168.1.202,APPDOM2,PSAPPSRV,
SYSADM                         PSAPPSRV@psovmfscmfp1.phoenix.nga (TNS V1-V3)    NGA,,192.168.1.202,APPDOM,PSAPPSRV,
SYSADM                         PSAPPSRV@psovmfscmfp1.phoenix.nga (TNS V1-V3)    VP1,,192.168.1.202,APPDOM2,PSAPPSRV,
… Here, from the same client machine, different users are in connected through the 2 applications servers (APPDOM and APPDOM2).

Now, let’s simulate a application server crash :

The online users won’t be disconnected, and the opened connections are automatically redirected to the second application server (failover) :

A second pool is now created, but that one on APPDOM2. Everything will be transparent to the front end users. 

Cloning a PeopleSoft Database

Cloning a PeopleSoft Database

One of the most common tasks of a PeopleSoft administrator or DBA is to clone a development or test database from a production database. Here are some of the steps you'll want to consider when refreshing PeopleSoft databases that are running PeopleTools 8.4x.

Note:  I'm sure this list isn't complete for every installation and it can certainly be improved, but that's the purpose of this Wiki -- Please incorporate your corrections, comments or suggestions so everyone can benefit. 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:
-- 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_ACCESS_PASSWORD SYSADM1 CHANGEME1; 
-- 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.