[转帖]Cloning a PeopleSoft Database_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 3363 | 回复: 0

主题： [转帖]Cloning a PeopleSoft Database

renruishen

注册用户

等级：新兵
经验：58
发帖：79
精华：0
注册：2011-12-25
状态：离线
发送短消息息给renruishen

加好友发送短消息息给renruishen

发消息

发表于：

2014-10-16 11:00:05 | [全部帖] [楼主帖]

楼主

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 :