One of the most common tasks of a PeopleSoft administrator or DBA isto clone a development or test database from a production database.Here are some of the steps you'll want to consider when refreshingPeopleSoft databases that are running PeopleTools 8.4x.
Note: I'm sure this list isn't complete for every installation andit can certainly be improved, but that's the purpose of this Wiki --Please incorporate your corrections, comments or suggestions soeveryone can benefit. For purposes of discussion, the source databasewill be production, and the target database will be the dev/testdatabase that you're overlaying.
Export any securitythat you need to preserve from the target database to a flat file. DataMover is a nice tool to use for this since you can qualify each tablewith a list of operator ID's to export. The tables you should considerexporting for specific users are PSOPRDEFN, PSOPRALIAS, PSROLEUSER,PSUSERATTR, PSUSEREMAIL, PSUSERPRSNLOPTN, PS_ROLEXLATOPR andPS_RTE_CNTL_RUSER.
Stop the target applicationenvironment, including application servers and process schedulers.Stopping the web server is optional since it doesn't connect directlyto the database. Be sure to clear cache.
Overlay the targetdatabase with a recent backup of the Production database. The exactprocess will differ for your database platform, but your DBA should beable to do this with minimal direction.
After the database comes back on-line, set DBNAME in PSDBOWNER back to the target database name.
Set GUID to ' ' in the PSOPTIONS table. This will cause PeopleSoft togenerate a new GUID so that change assistant can track it separatelyfrom the source database.
Delete the data from the reportingtables, process scheduler tables and application messaging tables sincethis data isn't relevant in the target database. Run prcsclr.dms,rptclr.dms and appmsgpurgeall.dms. In addition, I also delete from thereport manager tables PSRF_RATTR_TBL; PSRF_RSCRTY_TBL; PSRF_RINFO_TBL;and delete from PSRF_FINFO_TBL where PSRF_PRNT_FLDR_ID <> 0;
If you have a script to reset everyone's e-mail address to apre-defined value so that workflow messages from the Test environmentdon't get sent to real users, run it now. It should update thePSUSEREMAIL and PS_ROLEXLATOPR tables.
Log on to the targetdatabase with data mover and run the following command to set theSYSADM 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;
Import the security that you exported in step 1.
While you're in data mover, change the user passwords that areconfigured in your application server, process scheduler, andintegration 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;
Clear application server, web server and process scheduler cache if youhaven't already done it. Start the target application environment.
Log on through the web front end.
Update the Report Node configuration and verify your Process SchedulerServers are using the correct configuration. Make any Web Profilechanges if needed.
Navigate to PeopleTools > Utilities > Options and update the database name and description.
Change password rules as appropriate for a development environment.
Change your default local node if it should be named differently thanthe default local node from the source database. If it's not nameddifferently, you may be at risk for a single sign-on vulnerability.
Reconfigure attachment servers (if used)
Reconfigure REN servers and clusters if the application server didn't configure it correctly at startup.
Make sure you copy all of the batch objects (SQR's, Crystal Reports,COBOL programs, etc.) from your source to your target environment tokeep the environment in synch. Don't forget to do this on both UNIX andWindows servers.
Perform some sanity checks to make sure theenvironment is behaving itself before you let users back into it. At aminimum you should verify you can log on and run a report. Verify thereport runs to completion, posts, and allows you to view the reportwithout having to sign in a second time.
DBA : Deep
Its really cool
August 01, 2007
... : Jolt
This is awesome. Would you be updating this?? or do you have a complete list?...
Thank you and keep posting
August 24, 2007
... : Brent Martin
Irealize that I haven't updated this article in a while. My philosophyhas changed a bit. I don't export data from the target environmentbefore the refresh any more (unless it's unavoidable), I just have adata mover script insert and update the tables to the correct values.See ./blogs/attach/clonedb/DBRefresh_HQA.dms for an example. I just maintain a data mover refresh script for each environment.
August 26, 2007
Moving Peoplesoft Database : aaneelu
Hi,I imported the PROD SYSADM into DEMO2 SYSADM. There is no WebserverAppserver defined. But I use to connect via 2 tier using PS user andpassword from PROD it returns invalid password. I know it not exactlythe case of cloning PS as you described here, but can you explain whatmight be done to connect via 2 tier.
February 05, 2008
RE: Moving PeopleSoft Database : Brent Martin
Here are some things to check:
1) Make sure you set DBNAME in PSDBOWNER to match your database name.
2) Make sure your connect id (i.e. people) has the same password in DEMO2 as it did in PROD
3) Make sure SYSADM has the same password in DEMO2 as it did in PROD
4) Make sure PS and SYSADM have the same rights and grants as PROD
February 05, 2008
Re : Moving PeopleSoft Database : Brent Martin : aaneelu
Hi Martin,
Thanks for the quick reply. Even my senior PSADMINs are not able to solve this problem and it still exists.
Here are some things to check:
1) Make sure you set DBNAME in PSDBOWNER to match your database name.
>>> Not possible as there are 4 schemas : SYSADMA, SYSADM....
2) Make sure your connect id (i.e. people) has the same password in DEMO2 as it did in PROD
>>>Connect ID is PSCON is same in both env but passwords are different andcant be changed as it may effect other instances(schemas)
3) Make sure SYSADM has the same password in DEMO2 as it did in PROD
>>>No definetly cant as security violations. But using Toad I am able toconnect to DEMO2 using SYSADMD but I am unable to connect to Data Moveras Boot Strap mode. (Intrestingly, I observer via SQL Trace, Data Moverconsiders SYSADMD as Peoplesoft user (like PS..). How does Data Movervalidate User ID and Password? Does it look PSOPRDEFN table if I amusing SYSADMD user?(which in my case is YES)...
4) Make sure PS and SYSADM have the same rights and grants as PROD
>>> Dint look it so far.
February 05, 2008
Re : Moving PeopleSoft Database : Brent Martin
Here'show the login process works. App Designer connects to the database withthe connect Id you set up in config manager. It selects OWNERID FROMPSDBOWNER where DBNAME = whatever you specified in app designer to getto the database. This may be where it's breaking down and you may needto create another entry in TNSNAMES.ORA to create a unique dbname forthis schema. Anyway then it selects the SYMBOLICID, OPERPSWD fromPSOPRDEFN where oprid = whatever you specified as the oprid in the AppDesigner signon window. This is another place it might break down ifyou don't have the right symbolicid specified in PSOPRDEFN for thisschema. Then it selects ACCESSID and ACCESSPSWD from PSACCESSPRFL whereSYMBOLICID = the symbolic id selected in the previous step. Then itbuilds the password decryption key and decrypts SYSADM's password. Thenit logs out and logs back in as SYSADM using the decrypted SYSADMpassword.
Since you don't have an entry in PSDBOWNER thatmatches what you enter in app designer, you're doomed from the outset.And since SYSADM's password isn't the same in your new environment asit was in PROD it'll decrypt to the production value which won't work.
I'drecommend fixing PSDBOWNER so you can log in using data mover, and editand run resetpswd.dms to reset and encrypt all of the passwords.
BTW,PeopleSoft has never recommended running multiple application databaseschemas within a single database. It's good to avoid it if you can.
February 05, 2008
Re : Moving PeopleSoft Database : aaneelu : aaneelu
Hi Martin,
Thanks for the execellent info. Now I understood how the PS thingsvalidates a user. We solved problem by resetting the password forSYSADMD. Infact the problem was that the DBA set the SYSADMD passwordas 15 character one and Data Mover considers only 8 character one andonce I reset it to 8 character one everything went smooth, I was ableto login to Data Mover in Boot Strap mode and I updated thePSACCESSPRFL table with new password and ran the ENCRYPT.DMS in DataMover, then evary is working great.
Once again I thank you for your effort. Great.
Anil Kumar Bommareddy
February 06, 2008
... : Tom Groenwald
Anyone used Linux and baboo a 3rd party cloning tool
February 26, 2008
... : ramarao rayapati
HI ALL,
I have one issue while booting application services.
below are the list of activities i have done.
1) We have started the FUAT database using the restored production backup.
2) Changed SYSADM password at database level
3) Deleted the existing rows from PS.PSDBOWNER and inserted values for FUAT
SQL> select * from PS.PSDBOWNER;
DBNAME OWNERID
-------- --------
FUAT SYSADM
SQL>
4) Using data mover executed the below statements (logged in using SYSADM Account).
UPDATE PSACCESSPRFL SET ACCESSID = 'SYSADM', ACCESSPSWD = 'PASSWORD', ENCRYPTED = '0';
UPDATE PSOPRDEFN SET OPERPSWD = 'VP1', ENCRYPTED = 0 where OPRID='VP1';
ENCRYPT_PASSWORD VP1;
5) When I try to start up/Shutdown the Appserever using PSADMIN.exe. Initially we got the below error.
Command to execute (1-6, q) : 2
-------------------------------
PeopleSoft Domain Shutdown Menu
-------------------------------
Domain Name: FUAT
1) Normal shutdown
2) Forced shutdown
q) Quit
Command to execute (1-2, q) [q]: 1
The name specified is not recognized as an
internal or external command, operable program or batch file.
==============ERROR!================
Shutdown attempt encountered errors! Check the TUXEDO log for details.
==============ERROR!================
6) We have set few environment variable (TUXDIR,ORACLE_SID,PATH )and try to start again.
Now its giving the below error.
Command to execute (1-6, q) : 1
Attempting to boot ...
Booting all admin and server processes in F:psoft n75appservFUATPSTUXCFG
INFO: TUXEDO(r) System Release 6.5
INFO: Serial #: 1000000044, Expiration NONE, Maxusers 1000000
INFO: Licensed to: PeopleSoft
Booting admin processes ...
exec BBL -A :
process id=603 ... Started.
Booting server processes ...
exec PSAUTH -A -- -C psappsrv.cfg -D FUAT -S PSAUTH :
CMDTUX_CAT:1685: ERROR: Application initialization failure
exec WSL -A -- -n //AVAFARMS2:7100 -z 0 -Z 0 -I 60 -T 60 -m 2 -M 3 -x 60 -c 5000 :
process id=564 ... Started.
exec PSAPPSRV -p 1,600:1,1 -A -- -C psappsrv.cfg -D FUAT -S PSAPPSRV :
CMDTUX_CAT:1685: ERROR: Application initialization failure
exec PSQCKSRV -p 1,600:1,1 -s MgrClear,RamList,SqlRequest,StmChgPswd,StmGetTimeOut,SamGetParmsSvc,WamChgInstSvc,WamStar
CMDTUX_CAT:1685: ERROR: Application initialization failure
exec PSQRYSRV -p 1,600:1,1 -sSqlQuery:SqlRequest -- -C psappsrv.cfg -D FUAT -S PSQRYSRV :
CMDTUX_CAT:1685: ERROR: Application initialization failure
exec PSSAMSRV -A -- -C psappsrv.cfg -D FUAT -S PSSAMSRV :
CMDTUX_CAT:1685: ERROR: Application initialization failure
exec PSAPISRV -A -- -C psappsrv.cfg -D FUAT -S PSAPISRV :
CMDTUX_CAT:1685: ERROR: Application initialization failure
2 processes started.
==============ERROR!================
Boot attempt encountered errors!. Check the TUXEDO log for details.
==============ERROR!================
Weare using the same server for database and application.We are able toPing FUAT from client and server and able to connect to the databasefrom Client/Server.
.Can any one help me for getting this issue resolved.
Oracle 8i and OS windows-NT,Peoplesoft 7.5
March 29, 2008
... : Brent Martin
Soundslike you didn't change SYSADM's password correctly. Instead of changingthe password at the database level, you should have run the data movercommand:
CHANGE_ACCESS_PASSWORD SYSADM1 MYSECRETPWD;
(or you can use the Change Access Password feature in App Designer).
Ifyou look at your APPSERV.LOG, it'll tell you more info about the errorthat's occurring. I'm guessing it can't log on with your applicationID, probably because SYSADM's password wasn't changed correctly.
March 31, 2008
... : ramarao rayapati
hi Martin, Thanks for the reply...
i have changed the password in datamover LEVEL also...
in APPSERV.log i am getting the below error.
when i try to boot using PSRUN user getting the below error.
=========================== GenMessageBox ===========================
Title: SQL Access Manager
Message: SQL error. Stmt #: 2 Error Position: 0 Return: 1 - ORA-12154: TNS:could not resolve service name
Style: 8208
Msg Set: 0
Msg #: 0
=====================================================================
PSAUTH.282 [03/26/08 16:11:13](1)
=========================== GenMessageBox ===========================
Title: Database Signon
Message: Could not sign on to database FUAT with operator PSRUN.
Style: 8208
Msg Set: 0
Msg #: 0
=====================================================================
if i am trying to boot from the VP1 user i am getting the below error...
=========================== GenMessageBox ===========================
Title: Database Signon
Message: Could not sign on to database FUAT with operator VP1.
Style: 8208
Msg Set: 0
Msg #: 0
=====================================================================
PSAPPSRV.575 [03/27/08 12:43:59](1)
=========================== GenMessageBox ===========================
Title: SQL Access Manager
Message: SQL error. Stmt #: 2 Error Position: 0 Return: 1 - ORA-01017: invalid username/password; logon denied
Style: 8208
Msg Set: 0
Msg #: 0
=====================================================================
And in tuxlog file we have this...
094249.AVAFARMS2!tmloadcf.64: 033108: TUXEDO Version 6.5 32-bit Windows.
094249.AVAFARMS2!tmloadcf.64: CMDTUX_CAT:879: INFO: A new file system has been created. (size = 398 512-byte blocks)
094254.AVAFARMS2!tmloadcf.64: CMDTUX_CAT:871: INFO: TUXCONFIG file F:psoft n75appservFUATPSTUXCFG has been created
094538.AVAFARMS2!BBL.82: 033108: TUXEDO Version 6.5 32-bit Windows.
094538.AVAFARMS2!BBL.82: LIBTUX_CAT:262: INFO: Standard main starting
094538.AVAFARMS2!PSAUTH.242: 033108: TUXEDO Version 6.5 32-bit Windows.
094538.AVAFARMS2!PSAUTH.242: LIBTUX_CAT:262: INFO: Standard main starting
094539.AVAFARMS2!PSAUTH.242: LIBTUX_CAT:250: ERROR: tpsvrinit() failed
094539.AVAFARMS2!WSL.209: 033108: TUXEDO Version 6.5 32-bit Windows.
094539.AVAFARMS2!WSL.209: LIBTUX_CAT:262: INFO: Standard main starting
094540.AVAFARMS2!WSH.52: 033108: TUXEDO Version 6.5 32-bit Windows.
094540.AVAFARMS2!WSH.52: WSNAT_CAT:1030: INFO: Work Station Handler joining application
094540.AVAFARMS2!WSH.275: 033108: TUXEDO Version 6.5 32-bit Windows.
094540.AVAFARMS2!WSH.275: WSNAT_CAT:1030: INFO: Work Station Handler joining application
094540.AVAFARMS2!PSAPPSRV.283: 033108: TUXEDO Version 6.5 32-bit Windows.
094540.AVAFARMS2!PSAPPSRV.283: LIBTUX_CAT:262: INFO: Standard main starting
094541.AVAFARMS2!PSAPPSRV.283: LIBTUX_CAT:250: ERROR: tpsvrinit() failed
094541.AVAFARMS2!PSQCKSRV.276: 033108: TUXEDO Version 6.5 32-bit Windows.
094541.AVAFARMS2!PSQCKSRV.276: LIBTUX_CAT:262: INFO: Standard main starting
094542.AVAFARMS2!PSQCKSRV.276: LIBTUX_CAT:250: ERROR: tpsvrinit() failed
094542.AVAFARMS2!PSQRYSRV.284: 033108: TUXEDO Version 6.5 32-bit Windows.
094542.AVAFARMS2!PSQRYSRV.284: LIBTUX_CAT:262: INFO: Standard main starting
094543.AVAFARMS2!PSQRYSRV.284: LIBTUX_CAT:250: ERROR: tpsvrinit() failed
094543.AVAFARMS2!PSSAMSRV.271: 033108: TUXEDO Version 6.5 32-bit Windows.
094543.AVAFARMS2!PSSAMSRV.271: LIBTUX_CAT:262: INFO: Standard main starting
094544.AVAFARMS2!PSSAMSRV.271: LIBTUX_CAT:250: ERROR: tpsvrinit() failed
094544.AVAFARMS2!PSAPISRV.190: 033108: TUXEDO Version 6.5 32-bit Windows.
094544.AVAFARMS2!PSAPISRV.190: LIBTUX_CAT:262: INFO: Standard main starting
094545.AVAFARMS2!PSAPISRV.190: LIBTUX_CAT:250: ERROR: tpsvrinit() failed
i user the CHANGE_ACCESS_PASSWORD SYSADM PASSWORD; for changing the sysadm password...
April 01, 2008
... : Brent Martin
Youshould be able to use SQLPlus on that box to connect as either user. Inaddition, you should also be able to use either ID to connect to AppDesigner 2-tier (although the actual app designer access you get afterlogon is successful doesn't matter). So confirm both of those testswork on both ID's, then it shouldn't matter which one you use.
The"Tns cannot resolve service name" makes me think something in yourTNSNAMES or SQLNET file isn't correct. The "Could not sign on todatabase FUAT with operator VP1" makes me think VP1's password is outof sync between the database and PSOPRDEFN.
April 02, 2008
Appserver problem after cloning : rajani
I have cloned my database. I am able to sign on to the app designer with VP in 2 tier mode.
But not in 3-tier mode ...since i my app server is not being up.
The error i could see in the logs is cannot sign on to database using VP1.
I have checked all my security tables...no table is missing and
my connect id and accessprofile are fine.
Please suggest me on this....
thanks and regards
rajani
July 23, 2008
having same problem!!! did you fix it??? : ganesh
having same problem!!! did you fix it???
October 27, 2008
RE: Appserver problem after cloning : Brent Martin
If you can connect 2-tier, the app server should start. Some thoughts:
1) Verify the app server configuration has the correct connect id based on the database you just got cloned.
2) Double-check your VP1 password.
3) Make sure you can connect via SQLPlus on the same server running as the same user ID as your app server.
4) Make sure your database name, PSDBOWNER and PSACCESSPRFL are all in sync.
October 27, 2008
... : chandu
Hi All,
Ihave installed hrms 9.0 on PT 8.49. now applying cumulative maintenancepack 6 which i have downloaded from customer connect. Now I haveconfigured Change Assistant as well.
Now can anyone guide me regarding the same...its urgent
December 16, 2008
... : shilpa
Hi,
Iamtrying for a backup and recovery process.I copiec the PShome from theexisting server to new server and took the database(which is SQL server2005) backup and restored it on new server.I installed weblogic andtuxedo.I tried to configure the appserver domain which is on the pshomemaaping the database,connectid and password etc.when trying to boot thedomain I got the same error.
CMDTUX_CAT:1685:Application initialization failure
CMDTUX_CAT:827:fatal error encountered iniiating user error handler.
Can anyone help how to rsolve this .
April 13, 2009
RE: Shilpa : Brent Martin
Arethese errors coming out of your APPSRV.LOG, or are they showing up onthe screen when you boot? The APPSRV.LOG is definately the best placeto look.
Off the top of my head, make sure all of thepasswords are the same in the cloned database that they were in thesource database and try again. Don't change any passwords until you getit to boot.
April 13, 2009
Importing DB on MS SQL 2005 from 2000 : Adrian
Ialready have installed a Tools DEMO DB on the same SQL 2005 server andthat one works just fine. When I try to install a new Tools DB on thisserver I get the same error as when I try to connect to the one Irestored(copied) from a working SQL 2000 system. What is broken ?
Whycan't Datamover install a new DEMO or connect to this second DB on this2005 server ? It can only connect to the first and only DEMO DB. The DBI have problems with, on SQL 2005, can be restored to a 2000 server(that has no DEMO on it) and it works there.
To sum up:
It works on 2000 even when reimported to a diffrent 200 SQL server
Itdoes not work on MS SQL 2005. On this MS SQL 2005 I have a DEMO DB anda second DB setup will also fail, as does the connection to theimported DB from SQL 2000.
Could someone suggest anything ?
Thanks !!
I get a warning that my comment is too short:
So here I go:
What is PSDBOWNER Table in SQL 2005 ?
June 05, 2009
RE: Importing DB on MS SQL 2005 from 2000 : Adrian : Tom
Adrian,
Probablythe link of the "people" user is broken, there is a "people" user ondatabase server level and a "people" user on database level. But whenyou restore a PS database these two instances of user are notautomatically linked to each other.
What you can do is:
- remove the "people" on database level
- run PS_HOME/scripts/connect_2005.sql
-- Instructions:
-- 1) Run script using Query Analyzer or osql while in the PeopleSoft database.
-- 2) Must have sysadmin rights to run this script
-- 3) Replace with the CONNECTID chosen when creating the DBSETUP script. Example: people
-- 4) Replace with the CONNECTID PASSWORD chosen when creating theDBSETUP script. Example: peop1e (the l = number one)
- run PS_HOME/scripts/grant.sql
-- Instructions:
-- 1) Run script using ISQL/w while in the PeopleSoft database.
-- 2) Must have sysadmin rights to run this script.
-- 3) Replace 'people' with the CONNECTID chosen during the create install script process.