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.

 

Written by :
Brent Martin
 
Trackback(0)
Comments (23)Add Comment
0
DBA
written by Deep, August 01, 2007
Its really cool smilies/wink.gif
0
...
written by Jolt, August 25, 2007
This is awesome. Would you be updating this?? or do you have a complete list?...

Thank you and keep posting
Brent Martin
...
written by Brent Martin, August 27, 2007
I realize that I haven't updated this article in a while. My philosophy has changed a bit. I don't export data from the target environment before the refresh any more (unless it's unavoidable), I just have a data 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.
0
Moving Peoplesoft Database
written by aaneelu, February 05, 2008
Hi, I imported the PROD SYSADM into DEMO2 SYSADM. There is no Webserver Appserver defined. But I use to connect via 2 tier using PS user and password from PROD it returns invalid password. I know it not exactly the case of cloning PS as you described here, but can you explain what might be done to connect via 2 tier.
Brent Martin
RE: Moving PeopleSoft Database
written by Brent Martin, February 05, 2008
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
0
Re : Moving PeopleSoft Database : Brent Martin
written by aaneelu, February 06, 2008
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 and cant 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 to connect to DEMO2 using SYSADMD but I am unable to connect to Data Mover as Boot Strap mode. (Intrestingly, I observer via SQL Trace, Data Mover considers SYSADMD as Peoplesoft user (like PS..). How does Data Mover validate User ID and Password? Does it look PSOPRDEFN table if I am using 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.
Brent Martin
Re : Moving PeopleSoft Database
written by Brent Martin, February 06, 2008
Here's how the login process works. App Designer connects to the database with the connect Id you set up in config manager. It selects OWNERID FROM PSDBOWNER where DBNAME = whatever you specified in app designer to get to the database. This may be where it's breaking down and you may need to create another entry in TNSNAMES.ORA to create a unique dbname for this schema. Anyway then it selects the SYMBOLICID, OPERPSWD from PSOPRDEFN where oprid = whatever you specified as the oprid in the App Designer signon window. This is another place it might break down if you don't have the right symbolicid specified in PSOPRDEFN for this schema. Then it selects ACCESSID and ACCESSPSWD from PSACCESSPRFL where SYMBOLICID = the symbolic id selected in the previous step. Then it builds the password decryption key and decrypts SYSADM's password. Then it logs out and logs back in as SYSADM using the decrypted SYSADM password.

Since you don't have an entry in PSDBOWNER that matches 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 as it was in PROD it'll decrypt to the production value which won't work.

I'd recommend fixing PSDBOWNER so you can log in using data mover, and edit and run resetpswd.dms to reset and encrypt all of the passwords.

BTW, PeopleSoft has never recommended running multiple application database schemas within a single database. It's good to avoid it if you can.
0
Re : Moving PeopleSoft Database : aaneelu
written by aaneelu, February 06, 2008
Hi Martin,
Thanks for the execellent info. Now I understood how the PS things validates a user. We solved problem by resetting the password for SYSADMD. Infact the problem was that the DBA set the SYSADMD password as 15 character one and Data Mover considers only 8 character one and once I reset it to 8 character one everything went smooth, I was able to login to Data Mover in Boot Strap mode and I updated the PSACCESSPRFL table with new password and ran the ENCRYPT.DMS in Data Mover, then evary is working great.

Once again I thank you for your effort. Great.

Anil Kumar Bommareddy
Tom Groenwald
...
written by Tom Groenwald, February 27, 2008
Anyone used Linux and baboo a 3rd party cloning tool
ramarao rayapati
...
written by ramarao rayapati, March 29, 2008
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,Sa
mGetParmsSvc,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!================



We are using the same server for database and application.We are able to Ping FUAT from client and server and able to connect to the database from Client/Server.

.Can any one help me for getting this issue resolved.
Oracle 8i and OS windows-NT,Peoplesoft 7.5
Brent Martin
...
written by Brent Martin, March 31, 2008
Sounds like you didn't change SYSADM's password correctly. Instead of changing the password at the database level, you should have run the data mover command:

CHANGE_ACCESS_PASSWORD SYSADM1 MYSECRETPWD;

(or you can use the Change Access Password feature in App Designer).

If you look at your APPSERV.LOG, it'll tell you more info about the error that's occurring. I'm guessing it can't log on with your application ID, probably because SYSADM's password wasn't changed correctly.
ramarao rayapati
...
written by ramarao rayapati, April 01, 2008
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...
Brent Martin
...
written by Brent Martin, April 02, 2008
You should be able to use SQLPlus on that box to connect as either user. In addition, you should also be able to use either ID to connect to App Designer 2-tier (although the actual app designer access you get after logon is successful doesn't matter). So confirm both of those tests work on both ID's, then it shouldn't matter which one you use.

The "Tns cannot resolve service name" makes me think something in your TNSNAMES or SQLNET file isn't correct. The "Could not sign on to database FUAT with operator VP1" makes me think VP1's password is out of sync between the database and PSOPRDEFN.
0
Appserver problem after cloning
written by rajani, July 23, 2008
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
0
having same problem!!! did you fix it???
written by ganesh, October 27, 2008
having same problem!!! did you fix it???
Brent Martin
RE: Appserver problem after cloning
written by Brent Martin, October 27, 2008
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.
Chandresh
...
written by chandu, December 16, 2008
Hi All,

I have installed hrms 9.0 on PT 8.49. now applying cumulative maintenance pack 6 which i have downloaded from customer connect. Now I have configured Change Assistant as well.
Now can anyone guide me regarding the same...its urgent
0
...
written by shilpa, April 13, 2009
Hi,

Iam trying for a backup and recovery process.I copiec the PShome from the existing server to new server and took the database(which is SQL server 2005) backup and restored it on new server.I installed weblogic and tuxedo.I tried to configure the appserver domain which is on the pshome maaping the database,connectid and password etc.when trying to boot the domain 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 .
Brent Martin
RE: Shilpa
written by Brent Martin, April 13, 2009
Are these errors coming out of your APPSRV.LOG, or are they showing up on the screen when you boot? The APPSRV.LOG is definately the best place to look.

Off the top of my head, make sure all of the passwords are the same in the cloned database that they were in the source database and try again. Don't change any passwords until you get it to boot.
Adrian
Importing DB on MS SQL 2005 from 2000
written by Adrian, June 06, 2009

I already have installed a Tools DEMO DB on the same SQL 2005 server and that one works just fine. When I try to install a new Tools DB on this server I get the same error as when I try to connect to the one I restored(copied) from a working SQL 2000 system. What is broken ?
Why can't Datamover install a new DEMO or connect to this second DB on this 2005 server ? It can only connect to the first and only DEMO DB. The DB I 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
It does not work on MS SQL 2005. On this MS SQL 2005 I have a DEMO DB and a second DB setup will also fail, as does the connection to the imported 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 ?
0
RE: Importing DB on MS SQL 2005 from 2000 : Adrian
written by Tom, June 25, 2009
Adrian,

Probably the link of the "people" user is broken, there is a "people" user on database server level and a "people" user on database level. But when you restore a PS database these two instances of user are not automatically 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 the DBSETUP 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.

0
Unable to launch PSADMIN.EXE
written by New to PS, November 02, 2009
I am doing a new PS install on MSS. When trying to launch PSADMIN.EXE (chapter 8 on the install guide - Configuring the Application Server on Windows) I get this error message:
Failed to query windows registry for T environment Variable
TUXDIR environment variable not set

If you could please help w/ detail steps.
Brent Martin
RE: Unable to launch PSADMIN
written by Brent Martin, November 02, 2009
Open the System Utility within Control Panel, and click the System Variables section of the Environment tab. Create a new environment variable called TUXDIR, and specify the location of the Tuxedo directory on the windows server (something like C:BEATuxedo). Then try again.

Write comment

security code
Write the displayed characters


busy