1 Reply Latest reply: Dec 10, 2012 7:20 AM by Michel RSS

    Cloning PSoft environment

    Cliff Mack
      I'm an Oracle DBA by trade and have created a few scripts which automate the database cloning process for a PSoft database, however, what I'm missing
      as part of my automation is the backend stuff that needs to occur with a PSoft clone.
      I've already included the update of PSDBOWNER, PSOPTIONS and PSPRCSRQST, but what I'm looking for, aside from what's provided as a general outline
      from Oracle for cloning a PSoft environment, are things like the process scheduler definitions, etc. that seem to be done by hand.
      Is there not a way to update some tables with this needed information or does it have to be done manually?

      Tks,
      C
        • 1. Re: Cloning PSoft environment
          Michel
          Almost everything can be done via scripting. The process scheduler definitions for example are stored in PS_SERVERDEFN. The easiest way of finding out where everything is stored is checking the debug screen on a page in PeopleSoft (most of the time this functionality is not enabled in production, but only in the development environment). When you navigate to a page in PeopleSoft and press CTRL+J you get a debug page. On this page you can find the component name. When you open the component in the Application designer you can see which record holds the information on the page.

          What I generally do when it comes to cloning environments is making sure that all webprofiles, process scheduler definitions, report nodes etc. are stored in ALL databases. When cloning a database you'd only have to point for example the report node of a process scheduler to the correct one:

          UPDATE PS_SERVERDEFN
          SET DISTNODENAME = 'ACCNODE'
          WHERE SERVERNAME IN ('PSNT', 'PSNT2');

          I've included a post clone script I generally use, it gives a good indication of what tables you should check:
          UPDATE PSOPTIONS SET GUID = ' ';
          /
          
          --UPDATE PS.PSDBOWNER SET DBNAME = 'ACC'
          /
          TRUNCATE TABLE SYSADM.PS_CDM_LIST;
          /
          TRUNCATE TABLE SYSADM.PS_CDM_AUTH;
          /
          TRUNCATE TABLE SYSADM.PS_CDM_LIST_ARCH;
          /
          TRUNCATE TABLE SYSADM.PS_CDM_TRANSFER;
          /
          TRUNCATE TABLE SYSADM.PS_CDM_TRNFR_RJCT;
          /
          TRUNCATE TABLE SYSADM.PSPRCSRQST;
          /
          TRUNCATE TABLE SYSADM.PSPRCSPARMS;
          /
          TRUNCATE TABLE SYSADM.PSPRCSQUE;
          /
          TRUNCATE TABLE SYSADM.PSPRCSRQSTTEXT;
          /
          TRUNCATE TABLE SYSADM.PSPRCSRQSTXFER;
          /
          TRUNCATE TABLE SYSADM.PS_PRCSRQSTDIST;
          /
          TRUNCATE TABLE SYSADM.PS_PRCSRQSTNOTIFY;
          /
          TRUNCATE TABLE SYSADM.PS_MESSAGE_LOG;
          /
          TRUNCATE TABLE SYSADM.PS_MESSAGE_LOGPARM;
          /
          -- Truncate server statistics table 
          
          TRUNCATE TABLE PSSERVERSTAT;
          
          TRUNCATE TABLE PSAPMSGPUBHDR;
          TRUNCATE TABLE PSAPMSGPUBDATA;
          TRUNCATE TABLE PSAPMSGPUBCON;
          TRUNCATE TABLE PSAPMSGSUBCON;
          TRUNCATE TABLE PSAPMSGPUBERR;
          TRUNCATE TABLE PSAPMSGPUBERRP;
          TRUNCATE TABLE PSAPMSGPUBCERR;
          TRUNCATE TABLE PSAPMSGPUBCERRP;
          TRUNCATE TABLE PSAPMSGSUBCERR;
          TRUNCATE TABLE PSAPMSGSUBCERRP;
          TRUNCATE TABLE PSAPMSGPCONDATA;
          TRUNCATE TABLE PSAPMSGSCONDATA;
          
          -- synchronous core tables:
          TRUNCATE TABLE PSIBLOGHDR;
          TRUNCATE TABLE PSIBLOGDATA;
          TRUNCATE TABLE PSIBLOGERR;
          TRUNCATE TABLE PSIBLOGERRP;
          
          --  archive tables:
          TRUNCATE TABLE PSAPMSGARCHPH;
          TRUNCATE TABLE PSAPMSGARCHPD;
          TRUNCATE TABLE PSAPMSGARCHPC;
          TRUNCATE TABLE PSAPMSGARCHSC;
          TRUNCATE TABLE PSAPMSGARCHPT;
          TRUNCATE TABLE PSAPMSGARCHST;
          TRUNCATE TABLE PSIBLOGHDRARCH;
          TRUNCATE TABLE PSIBLOGDATAARCH;
          
          -- clean up message dispatchers
          TRUNCATE TABLE PSAPMSGDSPSTAT;
          TRUNCATE TABLE PSAPMSGDOMSTAT;
          
          -- Clean up ren server clusters
          TRUNCATE TABLE PSREN;
          /
          TRUNCATE TABLE PSMCFRENURLID;
          /
          TRUNCATE TABLE PSRENCLUSTER;
          /
          TRUNCATE TABLE PSRENCLUS_OWNER;
          /
          
          --UPDATE PSGATEWAY
          --SET CONNURL = 'http://accurl:port/PSIGW/PeopleSoftListeningConnector'
          /
          
          --UPDATE PS_SERVERDEFN
          --SET DISTNODENAME = 'ACCNODE'
          --WHERE SERVERNAME IN ('PSNT', 'PSNT2')
          
          /
          
          UPDATE PSNODEURITEXT 
          SET URI_TEXT='https://accurl:port/psc/PST/'
          where msgnodename='PSFT_HR'
          and URI_TYPE='CN'; 
          
          UPDATE PSNODEURITEXT 
          SET URI_TEXT='https://accurl:port/psp/PST/'
          where msgnodename='PSFT_HR'
          and URI_TYPE='PL'; 
          
          /
          
          UPDATE PSOPRDEFN A
          SET A.ACCTLOCK = 1, EMAILID = 'peoplesoft@peoplesoft.com'
          WHERE NOT EXISTS
          (SELECT 'X' FROM PSROLEUSER B 
            WHERE B.ROLEUSER = A.OPRID
            AND   ( B.ROLENAME = 'Functional Maintenance'
              OR B.ROLENAME = 'PeopleSoft Administrator') )
          AND A.OPRID NOT IN ('PTWEBSERVER', 'PS');      
          /
          
          UPDATE PSUSEREMAIL A
          SET A.EMAILID = 'peoplesoft@peoplesoft.com'
          WHERE EXISTS
          (SELECT 'X' FROM PSOPRDEFN B
           WHERE B.OPRID = A.OPRID
           AND  B.ACCTLOCK = 1);
          /   
          
          UPDATE PS_ROLEXLATOPR A
          SET A.EMAILID = 'peoplesoft@peoplesoft.com'
          WHERE EXISTS
          (SELECT 'X' FROM PSOPRDEFN B
           WHERE B.OPRID = A.OPRID
           AND  B.ACCTLOCK = 1);
          /
          
          --UPDATE PSACCESSPRFL SET ACCESSPSWD = 'encrypted_password_here'
          /
          
          COMMIT;
          /