After setting a Physical Standby Database and enabling Active Data Guard on Peoplesoft OVM, it is probably the nicest part for the Peoplesoft guys. Going further and look for Batch processes running onto that secondary database. Much useful for the big and heavy report, for instance.
As said earlier, the latest PSOVM is coming with the Peopletools 8.51, we can get all the latest technologies offered.
The new property regarding the standby database in the process scheduler configuration file allow us such thing, here in red from psprcs.cfg
[Startup]
;=========================================================================
; Database Signon settings
;=========================================================================
DBName=
DBType=
UserId=
UserPswd=
ConnectId=
ConnectPswd=
ServerName=
StandbyDBName=
StandbyDBType=
StandbyUserId=
StandbyUserPswd=
It is working only for an enabled Oracle Active Data Guard database.
At the end of this stage, the global architecture will be as follow (in red, the change compared to the previous state) :
As said earlier, the latest PSOVM is coming with the Peopletools 8.51, we can get all the latest technologies offered.
The new property regarding the standby database in the process scheduler configuration file allow us such thing, here in red from psprcs.cfg
[Startup]
;=========================================================================
; Database Signon settings
;=========================================================================
DBName=
DBType=
UserId=
UserPswd=
ConnectId=
ConnectPswd=
ServerName=
StandbyDBName=
StandbyDBType=
StandbyUserId=
StandbyUserPswd=
It is working only for an enabled Oracle Active Data Guard database.
At the end of this stage, the global architecture will be as follow (in red, the change compared to the previous state) :
1. Required scripts
Warning : 4 scripts are required to set up the configuration to run processes on read-only database through the Batch Server. Unfortunately, PSOVM does not offer any default scripts ($PS_HOME/scripts), so an additional Peoplesoft home installation is required before going further.
Those scripts are psadmin.sql ($PS_HOME/scripts/unix), createlocalsynonyms.sql, createdblinktoprimarydb.sql and createremotesynonyms.sql (from $PS_HOME/scripts). They are delivered on default Peoplesoft installation, but so far, not in PSOVM.
Eventually, here the copy onto the primary site (hosting primary database ) from an other Linux Peoplesoft installation I made on my own :
[root@psovmhcmdb ~]# su - oracle
[oracle@psovmhcmdb ~]$ scp root@192.168.1.141:/apps/psoft/pt851/scripts/unix/psadmin.sql .
root@192.168.1.141's password:
psadmin.sql 100% 2714 2.7KB/s 00:00
[oracle@psovmhcmdb ~]$ scp root@192.168.1.141:/apps/psoft/pt851/scripts/createlocalsynonyms.sql .
root@192.168.1.141's password:
createlocalsynonyms.sql 100% 1986 1.9KB/s 00:01
[oracle@psovmhcmdb ~]$ scp root@192.168.1.141:/apps/psoft/pt851/scripts/createdblinktoprimarydb.sql .
root@192.168.1.141's password:
createdblinktoprimarydb.sql 100% 1739 1.7KB/s 00:00
[oracle@psovmhcmdb ~]$ scp root@192.168.1.141:/apps/psoft/pt851/scripts/createremotesynonyms.sql .
root@192.168.1.141's password:
createremotesynonyms.sql 100% 4485 4.4KB/s 00:00
Warning : 4 scripts are required to set up the configuration to run processes on read-only database through the Batch Server. Unfortunately, PSOVM does not offer any default scripts ($PS_HOME/scripts), so an additional Peoplesoft home installation is required before going further.
Those scripts are psadmin.sql ($PS_HOME/scripts/unix), createlocalsynonyms.sql, createdblinktoprimarydb.sql and createremotesynonyms.sql (from $PS_HOME/scripts). They are delivered on default Peoplesoft installation, but so far, not in PSOVM.
Eventually, here the copy onto the primary site (hosting primary database ) from an other Linux Peoplesoft installation I made on my own :
[root@psovmhcmdb ~]# su - oracle
[oracle@psovmhcmdb ~]$ scp root@192.168.1.141:/apps/psoft/pt851/scripts/unix/psadmin.sql .
root@192.168.1.141's password:
psadmin.sql 100% 2714 2.7KB/s 00:00
[oracle@psovmhcmdb ~]$ scp root@192.168.1.141:/apps/psoft/pt851/scripts/createlocalsynonyms.sql .
root@192.168.1.141's password:
createlocalsynonyms.sql 100% 1986 1.9KB/s 00:01
[oracle@psovmhcmdb ~]$ scp root@192.168.1.141:/apps/psoft/pt851/scripts/createdblinktoprimarydb.sql .
root@192.168.1.141's password:
createdblinktoprimarydb.sql 100% 1739 1.7KB/s 00:00
[oracle@psovmhcmdb ~]$ scp root@192.168.1.141:/apps/psoft/pt851/scripts/createremotesynonyms.sql .
root@192.168.1.141's password:
createremotesynonyms.sql 100% 4485 4.4KB/s 00:00
2. Create a secondary ACCESSID/SYMBOLICID on primary db – psadmin.sql
2.1 PSADMIN.SQL on primary database
Run the script psadmin.sql will create an other Peoplesoft “owner” (ACCESSID) beside the default one SYSADM :
[oracle@psovmhcmdb ~]$ export ORACLE_SID=H91TMPLT
[oracle@psovmhcmdb ~]$ sqlplus / as sysdba
@psadmin
=> user : SYSBKP
=> pwd : SYSBKP
=> default permanent tablespace : psdefault
2.2 Insert the corresponding line in PS.PSDBOWNER for the new ACCESSID linked to the standby database
SQL> conn sysbkp/SYSBKP
Connected.
SQL> insert into ps.psdbowner values ('H91STBY','SYSBKP');
1 row created. 2.1 PSADMIN.SQL on primary database
Run the script psadmin.sql will create an other Peoplesoft “owner” (ACCESSID) beside the default one SYSADM :
[oracle@psovmhcmdb ~]$ export ORACLE_SID=H91TMPLT
[oracle@psovmhcmdb ~]$ sqlplus / as sysdba
@psadmin
=> user : SYSBKP
=> pwd : SYSBKP
=> default permanent tablespace : psdefault
2.2 Insert the corresponding line in PS.PSDBOWNER for the new ACCESSID linked to the standby database
SQL> conn sysbkp/SYSBKP
Connected.
SQL> insert into ps.psdbowner values ('H91STBY','SYSBKP');
SQL> commit;
2.3 Add a new SYMBOLICID
Open Application Designer, and create a new SYMBOLICID for the newly created ACCESSID (step 2.1)
3. Create a dedicated application user attached to the secondary SYMBOLICID
Within the front-end application, copy a user (e.g. PS) which is able to manage process scheduler and application server.
Make sure you set the secondary SYMBOLICID created earlier on step 2.3 (here SYSADM2) :
We can check the PSACCESSPRFL table to see the new line :
SQL> select * from sysadm.psaccessprfl;
SYMBOLICID VERSION
-------------------------------- ----------
ACCESSID
----------------------------------------------------------------
ACCESSPSWD ENCRYPTED
---------------------------------------------------------------- ----------
SYSADM2 157
sBzLcoZSrag=
sBzLcoZSrag= 1
SYSADM1 1
sBzLcYlPrag=
sBzLcYlPrag= 1
4. Create database link on primary db to the primary db – createdblinktoprimarydb.sql
Before going further, a small explanation. Running a process through the process scheduler is never purely read-only even if the process itself is only selecting data. The process scheduler has to update a couple of tables, for instance to state the batch status. So, here it will be used a very nice little tricky thing. We’ll create a database link to the primary database on the primary (remember, whether the secondary database is open, it is open in read-only mode), it will become a remote database link onto the secondary database once replicated (it is assume the tnsnames.ora file on primary and secondary are same).
On the primary database, connected as the secondary ACCESSID SYSADM2 run :
SQL> CREATE DATABASE LINK PRIMARY CONNECT TO sysadm IDENTIFIED BY SYSADM USING 'H91TMPLT';
Database link created. Before going further, a small explanation. Running a process through the process scheduler is never purely read-only even if the process itself is only selecting data. The process scheduler has to update a couple of tables, for instance to state the batch status. So, here it will be used a very nice little tricky thing. We’ll create a database link to the primary database on the primary (remember, whether the secondary database is open, it is open in read-only mode), it will become a remote database link onto the secondary database once replicated (it is assume the tnsnames.ora file on primary and secondary are same).
On the primary database, connected as the secondary ACCESSID SYSADM2 run :
SQL> CREATE DATABASE LINK PRIMARY CONNECT TO sysadm IDENTIFIED BY SYSADM USING 'H91TMPLT';
5. Create local synonyms – createlocalsynonyms.sql
The local synonyms are synonyms for the read-only objects (tables and views). Bear in mind to run it whenever you create customized records.
On the primary database, connected as the secondary ACCESSID SYSADM2 run the following script, it will create two subsequent scripts to be run :
[oracle@psovmhcmdb ~]$ more createlocalsynonyms.sql
...
SET ECHO ON
REM Create local synonyms for tables The local synonyms are synonyms for the read-only objects (tables and views). Bear in mind to run it whenever you create customized records.
On the primary database, connected as the secondary ACCESSID SYSADM2 run the following script, it will create two subsequent scripts to be run :
[oracle@psovmhcmdb ~]$ more createlocalsynonyms.sql
...
SET ECHO ON
SET HEADING OFF
Spool CREATELOCALTABLESYNONYMS.SQL
SELECT 'CREATE SYNONYM ' || TABLE_NAME || ' FOR SYSADM.' || TABLE_NAME || ';'
FROM ALL_TABLES
WHERE OWNER = 'SYSADM'
ORDER BY TABLE_NAME
;
Spool off
REM Create local synonyms for views
Spool CREATELOCALVIEWSYNONYMS.SQL
SET HEADING OFF
SELECT 'CREATE SYNONYM ' || VIEW_NAME || ' FOR SYSADM.' || VIEW_NAME || ';'
FROM ALL_VIEWS
WHERE OWNER = 'SYSADM'
ORDER BY VIEW_NAME
;
Spool off
[oracle@psovmhcmdb ~]$ sqlplus sysbkp/SYSBKP
SQL*Plus: Release 11.1.0.7.0 - Production on Fri Mar 11 10:11:06 2011
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @createlocalsynonyms.sql
...
<snipped>
...
SQL> !ls
createdblinktoprimarydb.sql CREATELOCALTABLESYNONYMS.SQL createremotesynonyms.sql psadmin.sql scripts templates
createlocalsynonyms.sql CREATELOCALVIEWSYNONYMS.SQL psadmin.log scp.sh setup
SQL> @CREATELOCALTABLESYNONYMS.SQL
...
<snipped>
...
SQL> @CREATELOCALVIEWSYNONYMS.SQL
...
<snipped>
...
SQL>
6. Create remote synonyms – createremotesynonyms.sql
The remote synonyms are synonyms for the read-writable objects (the tables updated by the process scheduler itself). Bear in mind to run it whenever you create customized records.
On the primary database, connected as the secondary ACCESSID SYSADM2 run :
SQL> @createremotesynonyms.sql
...
<snipped>
...
SQL> Spool CREATEREMOTESYNONYMS.LOG
SQL>
SQL> CREATE or REPLACE SYNONYM PS_AE_QE_AETEST_TAO1 FOR PS_AE_QE_AETEST_TAO1@PRIMARY;
Synonym created. The remote synonyms are synonyms for the read-writable objects (the tables updated by the process scheduler itself). Bear in mind to run it whenever you create customized records.
On the primary database, connected as the secondary ACCESSID SYSADM2 run :
SQL> @createremotesynonyms.sql
...
<snipped>
...
SQL> Spool CREATEREMOTESYNONYMS.LOG
SQL>
SQL> CREATE or REPLACE SYNONYM PS_AE_QE_AETEST_TAO1 FOR PS_AE_QE_AETEST_TAO1@PRIMARY;
...
<snipped>
...
SQL> CREATE or REPLACE SYNONYM PS_BAT_TIMINGS_FN FOR PS_BAT_TIMINGS_FN@PRIMARY;
Synonym created.
SQL>
SQL> Spool off
7. On the secondary site, confirm everything has been replicated
SQL> conn sysbkp/SYSBKP
Connected.
SQL> select * from ps.psdbowner;
DBNAME OWNERID
-------------------------------- --------------------------------
H91STBY SYSBKP
H91TMPLT SYSADM
SQL> select count(*), object_type from user_objects group by object_type;
COUNT(*) OBJECT_TYPE
---------- ----------------------------------------------------------------------------
1 DATABASE LINK
47701 SYNONYM
8. Configure the Batch server
8.1 Configure the tnsnames.ora file, it must contain both databases (primary and secondary)
[root@psovmhcm ~]# cat /etc/tnsnames.ora
H91TMPLT=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.146)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SID=H91TMPLT)))
H91STBY=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.148)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SID=H91TMPLT)))
[root@psovmhcm ~]#
8.2 Modify the psprcs.cfg file
[psadm2@psovmhcm ~]$ pwd
/home/psadm2
[psadm2@psovmhcm ~]$ ls
java0.log oradiag_psadm2 psft
[psadm2@psovmhcm ~]$ cd psft/pt/8.51/appserv/prcs/PRCSDOM/
[psadm2@psovmhcm PRCSDOM]$
[psadm2@psovmhcm PRCSDOM]$ more psprcs.cfg
[Startup] 8.1 Configure the tnsnames.ora file, it must contain both databases (primary and secondary)
[root@psovmhcm ~]# cat /etc/tnsnames.ora
H91TMPLT=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.146)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SID=H91TMPLT)))
H91STBY=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.148)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SID=H91TMPLT)))
[root@psovmhcm ~]#
8.2 Modify the psprcs.cfg file
[psadm2@psovmhcm ~]$ pwd
/home/psadm2
[psadm2@psovmhcm ~]$ ls
java0.log oradiag_psadm2 psft
[psadm2@psovmhcm ~]$ cd psft/pt/8.51/appserv/prcs/PRCSDOM/
[psadm2@psovmhcm PRCSDOM]$
[psadm2@psovmhcm PRCSDOM]$ more psprcs.cfg
;=========================================================================
; Database Signon settings
;=========================================================================
DBName=H91TMPLT
DBType=ORACLE
UserId=PS
UserPswd=PS
ConnectId=people
ConnectPswd=peop1e
ServerName=192.168.1.146
StandbyDBName=H91STBY
StandbyDBType=ORACLE
StandbyUserId=PSBKP
StandbyUserPswd=PSBKP
…
8.3 Reconfigure the modified PRCSDOM batch server
[psadm2@psovmhcm PRCSDOM]$ cd $PS_HOME/appserv
[psadm2@psovmhcm appserv]$ ./psadmin
PSADMIN -- Tools Release: 8.51.02
Copyright (c) 1996, 2010, Oracle. All rights reserved.
--------------------------------
PeopleSoft Server Administration
--------------------------------
1) Application Server
2) Process Scheduler
3) Search Server
4) Replicate Config Home
q) Quit
Command to execute (1-4, q): 2
--------------------------------------------
PeopleSoft Process Scheduler Administration
--------------------------------------------
1) Administer a domain
2) Create a domain
3) Delete a domain
4) Import domain configuration
q) Quit
Command to execute (1-4, q) : 1
Domain list:
1) PRCSDOM
Select domain: 1
--------------------------------------------
PeopleSoft Process Scheduler Administration
--------------------------------------------
Domain Name: PRCSDOM
1) Boot this domain
2) Domain shutdown menu
3) Domain status menu
4) Configure this domain
5) TUXEDO command line (tmadmin)
6) Edit configuration/log files menu
7) Clean IPC resources of this Domain
q) Quit
Command to execute (1-7, q) : 4
This option will shutdown the domain.
Do you want to continue? (y/n) [n] :y
<…snipped…>
----------------------------------------------
Quick-configure menu -- domain: PRCSDOM
----------------------------------------------
Features Settings
========== ==========
1) App Engine : Yes 5) DBNAME :[H91TMPLT]
2) Master Scheduler : Yes 6) DBTYPE :[ORACLE]
7) PrcsServer :[OVMDEMO]
8) UserId :[PS]
9) UserPswd :[PS]
10) ConnectID :[people]
11) ConnectPswd:[peop1e]
12) ServerName :[192.168.1.146]
13) Log/Output Dir:[%PS_SERVDIR%/log_output]
14) SQRBIN :[%PS_HOME%/bin/sqr/%PS_DB%/bin]
15) AddToPATH :[.]
Actions
=========
3) Load config as shown
4) Custom configuration
h) Help for this menu
q) Return to previous menu
HINT: Enter 5 to edit DBNAME, then 3 to load
Enter selection (1-15, h, or q): 3
Performing load prechecks ...
Loading validation table...
setting DBName=H91TMPLT
setting DBType=ORACLE
setting UserId=PS
setting UserPswd=PS
setting ConnectId=people
setting ConnectPswd=peop1e
setting ServerName=192.168.1.146
setting PrcsServerName=OVMDEMO
setting Log/Output Directory=%PS_SERVDIR%/log_output
setting Add to PATH=.
setting SQRBIN=%PS_HOME%/bin/sqr/%PS_DB%/bin
New CFG file written with modified Startup parameters
Log Directory entry not found in configuration file.
Setting Log Directory to the default... [PS_SERVDIR/LOGS]
Spawning enabled for server PSDSTSRV.
Configuration file successfully created.
CFG setting changes completed
Loading configuration...
Domain configuration complete.
8.4 Start the modified PRCSDOM batch server
--------------------------------------------
PeopleSoft Process Scheduler Administration
--------------------------------------------
Domain Name: PRCSDOM
1) Boot this domain
2) Domain shutdown menu
3) Domain status menu
4) Configure this domain
5) TUXEDO command line (tmadmin)
6) Edit configuration/log files menu
7) Clean IPC resources of this Domain
q) Quit
Command to execute (1-7, q) : 1
Archived a copy of the domain configuration to /home/psadm2/psft/pt/8.51/appserv/prcs/PRCSDOM/Archive/psprcs.cfg
Attempting to boot bulletin board...
tmadmin - Copyright (c) 2007-2008 Oracle.
Portions * Copyright 1986-1997 RSA Data Security, Inc.
All Rights Reserved.
Distributed under license by Oracle.
Tuxedo is a registered trademark.
No bulletin board exists. Entering boot mode.
> INFO: Oracle Tuxedo, Version 10.3.0.0, 64-bit, Patch Level 031
Booting admin processes ...
exec BBL -A :
process id=1910 ... Started.
1 process started.
Attaching to active bulletin board.
> Attempting to boot ...
INFO: Oracle Tuxedo, Version 10.3.0.0, 64-bit, Patch Level 031
Booting server processes ...
exec PSMSTPRC -o ./LOGS/stdout -e ./LOGS/stderr -A -- -CD H91TMPLT -PS OVMDEMO -A start -S PSMSTPRC :
process id=1914 ... Started.
exec PSAESRV -o ./LOGS/stdout -e ./LOGS/stderr -- -CD H91TMPLT -S PSAESRV :
process id=1919 ... Started.
exec PSAESRV -o ./LOGS/stdout -e ./LOGS/stderr -- -CD H91TMPLT -S PSAESRV :
process id=1923 ... Started.
exec PSAESRV -o ./LOGS/stdout -e ./LOGS/stderr -- -CD H91TMPLT -S PSAESRV :
process id=1927 ... Started.
exec PSDSTSRV -o ./LOGS/stdout -e ./LOGS/stderr -p 1,600:1,1 -sPostReport -- -CD H91TMPLT -PS OVMDEMO -A start -S PSDSTSRV :
process id=1931 ... Started.
exec PSPRCSRV -o ./LOGS/stdout -e ./LOGS/stderr -sInitiateRequest -- -CD H91TMPLT -PS OVMDEMO -A start -S PSPRCSRV :
process id=1936 ... Started.
exec PSMONITORSRV -o ./LOGS/stdout -e ./LOGS/stderr -A -- -ID 51639 -PS OVMDEMO -S PSMONITORSRV :
process id=1944 ... Started.
7 processes started.
8.5 On secondary database, check for the connections
SQL> conn / as sysdba
Connected.
SQL> select instance_name,host_name from v$instance;
INSTANCE_NAME HOST_NAME
---------------- ----------------------------------------------------------------
H91TMPLT psovmhcmdb2
SQL> select username,machine,program from v$session where username is not null;
USERNAME MACHINE PROGRAM
---------- ------------------------------ ------------------------------------------------
SYSBKP psovmhcm.phoenix.nga PSMONITORSRV@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP psovmhcm.phoenix.nga PSPRCSRV@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP psovmhcm.phoenix.nga PSDSTSRV@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP psovmhcm.phoenix.nga PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP psovmhcm.phoenix.nga PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP psovmhcm.phoenix.nga PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP psovmhcm.phoenix.nga PSMSTPRC@psovmhcm.phoenix.nga (TNS V1-V3)
PUBLIC psovmhcmdb.phoenix.nga oracle@psovmhcmdb.phoenix.nga (TNS V1-V3)
PUBLIC psovmhcmdb.phoenix.nga oracle@psovmhcmdb.phoenix.nga (TNS V1-V3)
PUBLIC psovmhcmdb.phoenix.nga oracle@psovmhcmdb.phoenix.nga (TNS V1-V3)
PUBLIC psovmhcmdb.phoenix.nga oracle@psovmhcmdb.phoenix.nga (TNS V1-V3)
SYS psovmhcmdb2 sqlplus@psovmhcmdb2 (TNS V1-V3)
12 rows selected.
We can see in the above output the process scheduler processes coming in with the secondary ACCESSID connection user.
9. Test, DDDAUDIT setting to read-only
9.1 We’ll first try to run a very well known process onto that secondary database. We first have to make the process as “read-only” on the process definition page.
9.2 Run the process DDDAUDIT
9.3 First result
Well, according to the databases connections, nothing is coming onto the standby database, on the contrary, a sqr connection is made from the client batch server onto the primary database.
The command line used to launch the SQR is not changed compared to a default one, still pointing to the primary database…
SQR Command = /opt/oracle/psft/pt/tools/bin/sqr/ORA/bin/sqr /opt/oracle/psft/pt/tools/sqr/dddaudit.sqr */*@H91TMPLT "-o/home/psadm2/psft/pt/8.51/appserv/prcs/PRCSDOM/log_output/SQR_DDDAUDIT_851/dddaudit_851.out" -xb -xi "-i/home/psadm2/psft/pt/8.51/appserv/prcs/PRCSDOM/log_output/SQR_DDDAUDIT_851/,/opt/oracle/psft/pt/tools/sqr/,/,/,/" -zif/opt/oracle/psft/pt/tools/sqr/pssqr.unx "-f/home/psadm2/psft/pt/8.51/appserv/prcs/PRCSDOM/log_output/SQR_DDDAUDIT_851/dddaudit_851.PDF" -PRINTER:PD H91TMPLT 851 PS psovmhcm
A bit disappointing, but there’s an clear explanation, SQR and COBOL processes are not designed yet to run on a standby database, despite the read-only property on the process definition page. It was well explained during one of the sessions of the last Open World as I reported on OOW2010 – Day 4. It is planned for a future version, you can read it as well on the Peopletools 8.51 roadmap.
So, no SQR on standby database, let’s try the simplest program ever, AEMINITEST.
10. Test, AEMINITEST setting to read-only
10.1 Make the AEMINITEST read-only
10.2 Run the AEMINITEST
Once it is running, we can see an incoming connection to the primary from the secondary database (as SYSADM – through the database link) to update the process scheduler’s tables :
Here an output from the primary database :
USERNAME MACHINE PROGRAM
----------- ------------------------ ------------------------------------------------
SYSADM psovmhcm.phoenix.nga PSPRCSRV@psovmhcm.phoenix.nga (TNS V1-V3)
SYSADM psovmhcmdb2 oracle@psovmhcmdb2 (TNS V1-V3) <--
SYSADM psovmhcm.phoenix.nga PSMONITORSRV@psovmhcm.phoenix.nga (TNS V1-V3)
I was setting the SQLTrace (=7) on Process scheduler to see how it is behave when running a read-only process. It is rather interesting to see the trace file : We can see in the above output the process scheduler processes coming in with the secondary ACCESSID connection user.
9. Test, DDDAUDIT setting to read-only
9.1 We’ll first try to run a very well known process onto that secondary database. We first have to make the process as “read-only” on the process definition page.
9.2 Run the process DDDAUDIT
9.3 First result
Well, according to the databases connections, nothing is coming onto the standby database, on the contrary, a sqr connection is made from the client batch server onto the primary database.
The command line used to launch the SQR is not changed compared to a default one, still pointing to the primary database…
SQR Command = /opt/oracle/psft/pt/tools/bin/sqr/ORA/bin/sqr /opt/oracle/psft/pt/tools/sqr/dddaudit.sqr */*@H91TMPLT "-o/home/psadm2/psft/pt/8.51/appserv/prcs/PRCSDOM/log_output/SQR_DDDAUDIT_851/dddaudit_851.out" -xb -xi "-i/home/psadm2/psft/pt/8.51/appserv/prcs/PRCSDOM/log_output/SQR_DDDAUDIT_851/,/opt/oracle/psft/pt/tools/sqr/,/,/,/" -zif/opt/oracle/psft/pt/tools/sqr/pssqr.unx "-f/home/psadm2/psft/pt/8.51/appserv/prcs/PRCSDOM/log_output/SQR_DDDAUDIT_851/dddaudit_851.PDF" -PRINTER:PD H91TMPLT 851 PS psovmhcm
A bit disappointing, but there’s an clear explanation, SQR and COBOL processes are not designed yet to run on a standby database, despite the read-only property on the process definition page. It was well explained during one of the sessions of the last Open World as I reported on OOW2010 – Day 4. It is planned for a future version, you can read it as well on the Peopletools 8.51 roadmap.
So, no SQR on standby database, let’s try the simplest program ever, AEMINITEST.
10. Test, AEMINITEST setting to read-only
10.1 Make the AEMINITEST read-only
10.2 Run the AEMINITEST
Once it is running, we can see an incoming connection to the primary from the secondary database (as SYSADM – through the database link) to update the process scheduler’s tables :
Here an output from the primary database :
USERNAME MACHINE PROGRAM
----------- ------------------------ ------------------------------------------------
SYSADM psovmhcm.phoenix.nga PSPRCSRV@psovmhcm.phoenix.nga (TNS V1-V3)
SYSADM psovmhcmdb2 oracle@psovmhcmdb2 (TNS V1-V3) <--
SYSADM psovmhcm.phoenix.nga PSMONITORSRV@psovmhcm.phoenix.nga (TNS V1-V3)
=> it connect onto the primary database to retrieve the object type and few other information.
H91TMPLT RC=0 Dur=0.000993 COM Stmt=SELECT VERSION FROM PSVERSION WHERE OBJECTTYPENAME = 'SYS'
H91TMPLT RC=0 Dur=0.000069 COM Stmt=SELECT OBJECTTYPENAME, VERSION FROM PSVERSION
H91TMPLT RC=0 Dur=0.000128 COM Stmt=SELECT MESSAGE_SET_NBR FROM PSMSGSETDEFN WHERE VERSION > :1
H91TMPLT RC=0 Dur=0.000004 Bind-1 type=8 length=4 value=3
H91TMPLT RC=0 Dur=0.000047 COM Stmt=SELECT SETCNTRLVALUE FROM PS_SET_CNTRL_TBL WHERE VERSION > :1
H91TMPLT RC=0 Dur=0.000004 Bind-1 type=8 length=4 value=3
H91TMPLT RC=0 Dur=0.000079 COM Stmt=SELECT PRCSTYPE, PRCSNAME FROM PS_PRCSDEFN WHERE VERSION > :1
H91TMPLT RC=0 Dur=0.000005 Bind-1 type=8 length=4 value=3
H91TMPLT RC=0 Dur=0.000060 COM Stmt=SELECT SERVERNAME FROM PS_SERVERDEFN WHERE VERSION > :1
H91TMPLT RC=0 Dur=0.000005 Bind-1 type=8 length=4 value=3
H91TMPLT RC=0 Dur=0.000048 COM Stmt=SELECT PRCSTYPE, OPSYS, DBTYPE FROM PS_PRCSTYPEDEFN WHERE VERSION > :1
H91TMPLT RC=0 Dur=0.000004 Bind-1 type=8 length=4 value=3
H91TMPLT RC=0 Dur=0.000064 COM Stmt=SELECT PRCSJOBNAME FROM PS_PRCSJOBDEFN WHERE VERSION > :1
H91TMPLT RC=0 Dur=0.000036 Bind-1 type=8 length=4 value=3
H91TMPLT RC=0 Dur=0.000085 COM Stmt=SELECT URL_ID FROM PSURLDEFN WHERE VERSION > :1 UNION SELECT URL_ID FROM PSURLDEL
H91TMPLT RC=0 Dur=0.000004 Bind-1 type=8 length=4 value=3
H91TMPLT RC=0 Dur=0.000005 Bind-2 type=8 length=4 value=3
H91TMPLT RC=0 Dur=0.000000 COM Stmt=SELECT SCOPRID, SCLANG FROM PSSCDICTDEFN WHERE VERSION > :1
H91TMPLT RC=0 Dur=0.000000 Bind-1 type=8 length=4 value=3
H91TMPLT RC=0 Dur=0.000005 Commit
H91TMPLT RC=0 Dur=0.000049 Disconnect
=> it connect onto the secondary database to run the AE, even some update appear to run onto the standby (which work fine since, as explained earlier, it is updating remote synonyms tables) :
H91STBY RC=0 Dur=0.018112 COM Stmt=SELECT 'PS_DOES_TABLE_EXIST' FROM PSOPTIONS
H91STBY RC=0 Dur=0.001922 COM Stmt=SELECT DBLENGTHTYPE,LANGUAGE_CD,MULTICURRENCY,MULTIJOB,MULTICOMPANY,HELP_CONTEXT_LAST,GRANT_A
H91STBY RC=0 Dur=0.000084 Disconnect
notSamTran RC=0 Dur=0.000021 Open Cursor Handle=0000000009954E60
H91STBY RC=0 Dur=0.000041 Disconnect
notSamTran RC=0 Dur=0.000012 Open Cursor Handle=0000000009954E60
H91STBY RC=0 Dur=0.000057 COM Stmt=SELECT JOBINSTANCE, MAINJOBINSTANCE FROM PSPRCSRQST WHERE PRCSINSTANCE = :1
H91STBY RC=0 Dur=0.000005 Bind-1 type=8 length=4 value=852
H91STBY RC=0 Dur=0.000052 Disconnect
notSamTran RC=0 Dur=0.000073 Open Cursor Handle=0000000009954E60
H91STBY RC=0 Dur=0.000119 COM Stmt=UPDATE PSPRCSRQST SET RUNSTATUS = :1,PRCSRTNCD = :2,CONTINUEJOB = :3,BEGINDTTM = CAST(SYSTIMES
H91STBY RC=0 Dur=0.000004 Bind-1 type=2 length=1 value=7
H91STBY RC=0 Dur=0.000004 Bind-2 type=6 length=4 value=0
H91STBY RC=0 Dur=0.000004 Bind-3 type=6 length=4 value=0
H91STBY RC=0 Dur=0.000004 Bind-4 type=8 length=4 value=852
H91STBY RC=0 Dur=0.000030 COM Stmt=UPDATE PSPRCSQUE SET RUNSTATUS = :1,PRCSRTNCD = :2,CONTINUEJOB = :3 WHERE PRCSINSTANCE = :4
H91STBY RC=0 Dur=0.000004 Bind-1 type=2 length=1 value=7
H91STBY RC=0 Dur=0.000005 Bind-2 type=6 length=4 value=0
H91STBY RC=0 Dur=0.000005 Bind-3 type=6 length=4 value=0
H91STBY RC=0 Dur=0.000005 Bind-4 type=8 length=4 value=852
H91STBY RC=0 Dur=0.004184 Commit
H91STBY RC=0 Dur=0.000019 Disconnect
H91STBY RC=0 Dur=0.000518 COM Stmt=SELECT PROCESS_INSTANCE, AE_RTNCD, AE_CRITICAL_PHASE, AE_STEP_DONE, MARKET, TO_CHAR(ASOF_DT,'Y
H91STBY RC=0 Dur=0.000005 Bind-1 type=2 length=2 value=PS
H91STBY RC=0 Dur=0.000005 Bind-2 type=2 length=8 value=psovmhcm
H91STBY RC=0 Dur=0.000004 Bind-3 type=2 length=10 value=AEMINITEST
H91STBY RC=0 Dur=0.000090 COM Stmt=SELECT OPRID, RUN_CNTL_ID, AE_APPLID FROM PS_AERUNCONTROL WHERE PROCESS_INSTANCE = :1
H91STBY RC=0 Dur=0.000005 Bind-1 type=8 length=4 value=852
H91STBY RC=0 Dur=0.000077 COM Stmt=SELECT OPRID, RUN_CNTL_ID, AE_APPLID, MIN(AE_DISABLE_RESTART) FROM PS_AETEMPTBLMGR WHERE PROCE
H91STBY RC=0 Dur=0.000005 Bind-1 type=8 length=4 value=852
H91STBY RC=0 Dur=0.000095 COM Stmt=INSERT INTO PS_AERUNCONTROL (PROCESS_INSTANCE, OPRID, RUN_CNTL_ID, AE_APPLID, RUN_DTTM, AE_RTN
H91STBY RC=0 Dur=0.000004 Bind-1 type=8 length=4 value=852
H91STBY RC=0 Dur=0.000004 Bind-2 type=2 length=2 value=PS
H91STBY RC=0 Dur=0.000004 Bind-3 type=2 length=8 value=psovmhcm
H91STBY RC=0 Dur=0.000004 Bind-4 type=2 length=10 value=AEMINITEST
notSamTran RC=0 Dur=0.000052 Open Cursor Handle=0000000009954F98
=> in the end, the process scheduler reconnect one more time onto the primary database :
H91STBY RC=0 Dur=0.000217 COM Stmt=UPDATE PS_AETEMPTBLMGR SET AE_DEDICATED = 1 WHERE 0 = 1
H91STBY RC=0 Dur=0.002722 Commit
notSamTran RC=0 Dur=0.000026 Open Cursor Handle=0000000009954E60
H91STBY RC=0 Dur=0.000065 COM Stmt=UPDATE PSPRCSRQST SET RUNSTATUS = :1,PRCSRTNCD = :2,CONTINUEJOB = :3,ENDDTTM = CAST(SYSTIME
H91STBY RC=0 Dur=0.000004 Bind-1 type=2 length=1 value=9
H91STBY RC=0 Dur=0.000005 Bind-2 type=6 length=4 value=0
H91STBY RC=0 Dur=0.000005 Bind-3 type=6 length=4 value=1
H91STBY RC=0 Dur=0.000005 Bind-4 type=8 length=4 value=852
H91STBY RC=0 Dur=0.000042 COM Stmt=UPDATE PSPRCSQUE SET RUNSTATUS = :1,PRCSRTNCD = :2,CONTINUEJOB = :3 WHERE PRCSINSTANCE = :4
H91STBY RC=0 Dur=0.000013 Bind-1 type=2 length=1 value=9
H91STBY RC=0 Dur=0.000004 Bind-2 type=6 length=4 value=0
H91STBY RC=0 Dur=0.000004 Bind-3 type=6 length=4 value=1
H91STBY RC=0 Dur=0.000004 Bind-4 type=8 length=4 value=852
H91STBY RC=0 Dur=0.003032 Commit
H91STBY RC=0 Dur=0.000023 Disconnect
H91TMPLT RC=0 Dur=0.000143 Disconnect
H91TMPLT RC=0 Dur=0.000109 Disconnect
H91TMPLT RC=0 Dur=0.000114 Disconnect
H91TMPLT RC=0 Dur=0.000100 Disconnect
H91TMPLT RC=0 Dur=0.000140 Disconnect
H91TMPLT RC=0 Dur=0.000140 Disconnect
H91TMPLT RC=0 Dur=0.000004 Commit
H91TMPLT RC=0 Dur=0.000124 Disconnect
H91STBY RC=0 Dur=0.000005 Commit
H91STBY RC=0 Dur=0.000102 Disconnect
notSamTran RC=0 Dur=0.000018 Open Cursor Handle=0000000009954F98
H91TMPLT RC=0 Dur=0.000096 COM Stmt=SELECT UPPER(MACHINENAME),PROCESSID,TO_CHAR(CAST((PTATREGDTTM) AS TIMESTAMP),'YYYY-MM-DD-H
H91TMPLT RC=0 Dur=0.000005 Commit
H91TMPLT RC=0 Dur=0.000054 Disconnect
By curiosity, I revert the read-only property to the default unchecked (read-write) for the AEMINITEST, and re-run it. No surprise, the tracefile shows only primary database connections.
Right now, except failover, we got a very nice and complete PSOVM environment ! Enjoy it !
Read my next article :
Nicolas.
Documentation, find out more :
http://download.oracle.com/docs/cd/E18083_01/pt851pbr0/eng/psbooks/tadm/book.htm?File=tadm/htm/tadm13.htm#H4064
10 comments:
This is amazing work!! :)
Thank you, I hope it helps to well understand this new mechanism which should be used more often than it is.
Nicolas.
When will SQR's be able to run on the standby databsae ?
If you are sure your SQR is only selecting data, you can define it as read-only process and it will run on standby.
Find out more Configuring Read-Only Processes
Nicolas.
On step 8.1 for configuring tnsnames, is the second connection for H91STBY there supposed to be (SID=H91TMPLT) or is that a typo? Thanks.
@gotthemunchies, thank for stopping by.
No, it's not a typo. The secondary database name is the same as the primary but on different server. However, the tns entry is different to make other connection working.
Nicolas.
Hi Nicolas,
Thanks for posting this nice document and I was going through it but it looks like a modification is required on running the createlocalsynonyms.sql script.
(STEP 5. Create local synonyms – createlocalsynonyms.sql)
The peoplebook said to run this as a Primary access id( but In your notes it says secondary access id).
@Anonymous,
I ran the script connected with the secondary accessid. Anyway, it should not hurt since the script is about ALL_TABLES and ALL_VIEWS.
The secondary accessid is granted to read primary accessid's tables/views.
Nicolas.
Hi Nicole,
Step 2.2 2.2 Insert the corresponding line in PS.PSDBOWNER for the new ACCESSID linked to the standby database. Which database should be run the Insert statement, primary or standby? Thanks!
@Teiki, it has to be run on primary db.
Post a Comment