Monday, March 14, 2011

PSOVM and Active Data Guard : Appserver

After setting a Physical Standby Database and enable Active Data Guard, this part assume you’ve done all the requirements to run the batch process onto the read-only database, as explained in earlier post : secondary ACCESSID, secondary SYMBOLICID, database link, local synonyms and remote synonyms must have been configured.
This part will be focused on the Application Server which also can be configured to run onto the read-only database for few elements. Working on PSOVM make the configuration even easier for testing and learning purpose, it is coming with the latest Peopletools version, 8.51, which come with that configuration option in psappsrv.cfg :
[Startup]
;=========================================================================
; Database Signon settings
;=========================================================================
DBName=
DBType=
UserId=
UserPswd=
ConnectId=
ConnectPswd=
ServerName=
StandbyDBName=
StandbyDBType=
StandbyUserId=
StandbyUserPswd=

That option will work only for an enable Oracle Active Data Guard.
At the end of this stage, the global architecture of my PSOVM environment will be as follow (in green the changes compared to the previous post) :
PS_ACTIVEDG_051 1. Required scripts
Please follow instruction given for the Batch server configuration on Active Data Guard, step 1.

2. Create a secondary ACCESSID/SYMBOLICID on primary db – psadmin.sql
Please follow instruction given for the Batch server configuration on Active Data Guard, step 2.

3. Create a dedicated application user attached to the secondary SYMBOLICID
Please follow instruction given for the Batch server configuration on Active Data Guard, step 3.

4. Create database link on primary db to the primary db – createdblinktoprimarydb.sql
Please follow instruction given for the Batch server configuration on Active Data Guard, step 4.

5. Create local synonyms – createlocalsynonyms.sql
Please follow instruction given for the Batch server configuration on Active Data Guard, step 5.

6. Create remote synonyms – createremotesynonyms.sql
Please follow instruction given for the Batch server configuration on Active Data Guard, step 6.

7. Application server configuration
7.1
Be sure the tnsnames.ora is properly configured, please follow instruction given for the Batch server configuration on Active Data Guard, step 8.1.

7.2 Modify psappsrv.cfg
Set the standby database and standby user :
[Startup]
;=========================================================================
; Database Signon settings
;=========================================================================
DBName=H91TMPLT
DBType=ORACLE
UserId=PS
UserPswd=PS
ConnectId=people
ConnectPswd=peop1e
ServerName=____
StandbyDBName=H91STBY
StandbyDBType=ORACLE
StandbyUserId=PSBKP
StandbyUserPswd=PSBKP

7.3 Reconfigure the application server APPDOM (psadmin tool)
[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): 1

--------------------------------------------
PeopleSoft Application Server 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) APPDOM

Select domain: 1

--------------------------------
PeopleSoft Domain Administration
--------------------------------
     Domain Name: APPDOM

  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) Messaging Server Administration menu
  8) Purge Cache
  9) Preload Cache
10) Clean IPC resources of this domain
  q) Quit

Command to execute (1-10, q) : 4

This option will shutdown the domain.
Do you want to continue? (y/n) [n] :y

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.

> INFO: Oracle Tuxedo, Version 10.3.0.0, 64-bit, Patch Level 031

Booting admin processes ...

exec BBL -A :
        CMDTUX_CAT:821: INFO: Duplicate server.
0 processes started.

> Shutting down all admin and server processes in /home/psadm2/psft/pt/8.51/appserv/APPDOM/PSTUXCFG

Shutting down server processes ...
<…snipped…>
Shutting down admin processes ...

        Server Id = 0 Group Id = psovmhcm.phoenix.nga Machine = psovmhcm.phoenix.nga:   shutdown succeeded
18 processes stopped.

All domain processes have stopped.

----------------------------------------------
Quick-configure menu -- domain: APPDOM
----------------------------------------------
      Features                      Settings
     ==========                    ==========
  1) Pub/Sub Servers   : Yes   15) DBNAME     :[H91TMPLT]
  2) Quick Server      : No    16) DBTYPE     :[ORACLE]
  3) Query Servers     : Yes   17) UserId     :[PS]
  4) Jolt              : Yes   18) UserPswd   :[PS]
  5) Jolt Relay        : No    19) DomainID   :[APPDOM]
  6) WSL               : No    20) AddToPATH  :[.]
  7) PC Debugger       : No    21) ConnectID  :[people]
  8) Event Notification: No    22) ConnectPswd:[peop1e]
  9) MCF Servers       : No    23) ServerName :[____]
10) Perf Collator     : No    24) WSL Port   :[7000]
11) Analytic Servers  : Yes   25) JSL Port   :[9000]
12) Domains Gateway   : No    26) JRAD Port  :[9100]

      Actions
     =========
13) Load config as shown
14) Custom configuration
  h) Help for this menu
  q) Return to previous menu

HINT: Enter 15 to edit DBNAME, then 13 to load

Enter selection (1-26, h, or q): 13
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=____
  setting Port=7000
  setting Port=9000
  setting Listener Port=9100
  setting Domain ID=APPDOM
  setting Add to PATH=.
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 disabled for server PSAPPSRV.
Spawning disabled for server PSQRYSRV.
Spawning disabled for server PSBRKHND_dflt.
Spawning disabled for server PSPUBHND_dflt.
Spawning disabled for server PSSUBHND_dflt.
Configuration file successfully created.
CFG setting changes completed
Loading configuration...
Domain configuration complete.

7.4 Restart the application server
--------------------------------
PeopleSoft Domain Administration
--------------------------------
     Domain Name: APPDOM

  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) Messaging Server Administration menu
  8) Purge Cache
  9) Preload Cache
10) Clean IPC resources of this domain
  q) Quit

Command to execute (1-10, q) :  1

-------------------------------
PeopleSoft Domain Boot Menu
-------------------------------
     Domain Name: APPDOM

  1) Boot (Serial Boot)
  2) Parallel Boot
  q) Quit

Command to execute (1-2, q) [q]: 1

Archived a copy of the domain configuration to /home/psadm2/psft/pt/8.51/appserv/APPDOM/Archive/psappsrv.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=1905 ... 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 PSWATCHSRV -o ./LOGS/stdout -e ./LOGS/stderr -A -- -ID 152926 -D APPDOM -S PSWATCHSRV :
        process id=1909 ... Started.

<snipped>

exec JREPSVR -o ./LOGS/stdout -e ./LOGS/stderr -A -- -W -P /home/psadm2/psft/pt/8.51/appserv/APPDOM/jrepository :
        process id=1975 ... Started.
17 processes started.

7.5 On secondary database, check for the connections
SQL> select substr(username,1,8) userm,machine,program,module from v$session where username is not null
SQL> /

USERM       MACHINE                   PROGRAM                                          MODULE
----------- ------------------------- ------------------------------------------------ --------------------------------------
SYSBKP      psovmhcm.phoenix.nga      PSMONITORSRV@psovmhcm.phoenix.nga (TNS V1-V3)    PSMONITORSRV@psovmhcm.phoenix.nga (TNS
SYSBKP      psovmhcm.phoenix.nga      PSSUBDSP@psovmhcm.phoenix.nga (TNS V1-V3)        PSSUBDSP@psovmhcm.phoenix.nga (TNS V1-
SYSBKP      psovmhcm.phoenix.nga      PSSUBHND@psovmhcm.phoenix.nga (TNS V1-V3)        PSSUBHND@psovmhcm.phoenix.nga (TNS V1-
SYSBKP      psovmhcm.phoenix.nga      PSPUBDSP@psovmhcm.phoenix.nga (TNS V1-V3)        PSPUBDSP@psovmhcm.phoenix.nga (TNS V1-
SYSBKP      psovmhcm.phoenix.nga      PSPUBHND@psovmhcm.phoenix.nga (TNS V1-V3)        PSPUBHND@psovmhcm.phoenix.nga (TNS V1-
SYSBKP      psovmhcm.phoenix.nga      PSBRKDSP@psovmhcm.phoenix.nga (TNS V1-V3)        PSBRKDSP@psovmhcm.phoenix.nga (TNS V1-
SYSBKP      psovmhcm.phoenix.nga      PSBRKHND@psovmhcm.phoenix.nga (TNS V1-V3)        PSBRKHND@psovmhcm.phoenix.nga (TNS V1-
SYSBKP      psovmhcm.phoenix.nga      PSANALYTICSRV@psovmhcm.phoenix.nga (TNS V1-V3)   PSANALYTICSRV@psovmhcm.phoenix.nga
SYSBKP      psovmhcm.phoenix.nga      PSANALYTICSRV@psovmhcm.phoenix.nga (TNS V1-V3)   PSANALYTICSRV@psovmhcm.phoenix.nga
SYSBKP      psovmhcm.phoenix.nga      PSANALYTICSRV@psovmhcm.phoenix.nga (TNS V1-V3)   PSANALYTICSRV@psovmhcm.phoenix.nga
SYSBKP      psovmhcm.phoenix.nga      PSSAMSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSSAMSRV@psovmhcm.phoenix.nga (TNS V1-
SYSBKP      psovmhcm.phoenix.nga      PSQRYSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSQRYSRV@psovmhcm.phoenix.nga (TNS V1-
SYSBKP      psovmhcm.phoenix.nga      PSAPPSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSAPPSRV@psovmhcm.phoenix.nga (TNS V1-
SYSBKP      psovmhcm.phoenix.nga      PSAPPSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSAPPSRV@psovmhcm.phoenix.nga (TNS V1-
SYSBKP      psovmhcm.phoenix.nga      PSMONITORSRV@psovmhcm.phoenix.nga (TNS V1-V3)    PSMONITORSRV@psovmhcm.phoenix.nga (TNS
SYSBKP      psovmhcm.phoenix.nga      PSPRCSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSPRCSRV@psovmhcm.phoenix.nga (TNS V1-
SYSBKP      psovmhcm.phoenix.nga      PSDSTSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSDSTSRV@psovmhcm.phoenix.nga (TNS V1-
SYSBKP      psovmhcm.phoenix.nga      PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)         PSAESRV@psovmhcm.phoenix.nga (TNS V1-
SYSBKP      psovmhcm.phoenix.nga      PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)         PSAESRV@psovmhcm.phoenix.nga (TNS V1-
SYSBKP      psovmhcm.phoenix.nga      PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)         PSAESRV@psovmhcm.phoenix.nga (TNS V1-
SYSBKP      psovmhcm.phoenix.nga      PSMSTPRC@psovmhcm.phoenix.nga (TNS V1-V3)        PSMSTPRC@psovmhcm.phoenix.nga (TNS V1-
PUBLIC      psovmhcmdb.phoenix.nga    oracle@psovmhcmdb.phoenix.nga (TNS V1-V3)        oracle@psovmhcmdb.phoenix.nga (TNS V1-
PUBLIC      psovmhcmdb.phoenix.nga    oracle@psovmhcmdb.phoenix.nga (TNS V1-V3)        oracle@psovmhcmdb.phoenix.nga (TNS V1-
PUBLIC      psovmhcmdb.phoenix.nga    oracle@psovmhcmdb.phoenix.nga (TNS V1-V3)        oracle@psovmhcmdb.phoenix.nga (TNS V1-
PUBLIC      psovmhcmdb.phoenix.nga    oracle@psovmhcmdb.phoenix.nga (TNS V1-V3)        oracle@psovmhcmdb.phoenix.nga (TNS V1-
SYS         psovmhcmdb2               sqlplus@psovmhcmdb2 (TNS V1-V3)                  sqlplus@psovmhcmdb2 (TNS V1-V3)

26 rows selected.

We can see now all the processes coming from the batch server as well as all the processes coming from the application server.

8. Test
8.1 The Application server is able to run a read-only component onto the secondary database. Let’s choose a simple one.
PS_ACTIVEDG_038 
Onto the primary database, run the following :
SQL> select substr(username,1,8) userm,machine,program,module from v$session where username is not null
SQL> /

USERM       MACHINE                    PROGRAM                                          MODULE
----------- -------------------------- ------------------------------------------------ --------------------------------------
SYSADM      WORKGROUP\PHOENIX          pside.exe                                        pside.exe
SYSADM      psovmhcm.phoenix.nga       PSMONITORSRV@psovmhcm.phoenix.nga (TNS V1-V3)    PSMONITORSRV@psovmhcm.phoenix.nga
SYSADM      psovmhcm.phoenix.nga       PSPRCSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSPRCSRV@psovmhcm.phoenix.nga (TNS V1-
SYSADM      psovmhcm.phoenix.nga       PSDSTSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSDSTSRV@psovmhcm.phoenix.nga (TNS V1-
SYSADM      psovmhcm.phoenix.nga       PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)         PSAESRV@psovmhcm.phoenix.nga (TNS V1-
SYSADM      psovmhcm.phoenix.nga       PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)         PSAE
SYSADM      psovmhcm.phoenix.nga       PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)         PSAE
SYSADM      psovmhcm.phoenix.nga       PSMSTPRC@psovmhcm.phoenix.nga (TNS V1-V3)        PSMSTPRC@psovmhcm.phoenix.nga (TNS V1-
SYSADM      psovmhcm.phoenix.nga       PSMONITORSRV@psovmhcm.phoenix.nga (TNS V1-V3)    PSMONITORSRV@psovmhcm.phoenix.nga
SYSADM      psovmhcm.phoenix.nga       PSSUBDSP@psovmhcm.phoenix.nga (TNS V1-V3)        PSSUBDSP@psovmhcm.phoenix.nga (TNS V1-
SYSADM      psovmhcm.phoenix.nga       PSSUBHND@psovmhcm.phoenix.nga (TNS V1-V3)        PSSUBHND@psovmhcm.phoenix.nga (TNS V1-
SYSADM      psovmhcm.phoenix.nga       PSPUBDSP@psovmhcm.phoenix.nga (TNS V1-V3)        PSPUBDSP@psovmhcm.phoenix.nga (TNS V1-
SYSADM      psovmhcm.phoenix.nga       PSQRYSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSQRYSRV@psovmhcm.phoenix.nga (TNS V1-
SYSADM      psovmhcm.phoenix.nga       PSANALYTICSRV@psovmhcm.phoenix.nga (TNS V1-V3)   PSANALYTICSRV@psovmhcm.phoenix.nga
SYSADM      psovmhcm.phoenix.nga       PSBRKDSP@psovmhcm.phoenix.nga (TNS V1-V3)        PSBRKDSP@psovmhcm.phoenix.nga (TNS V1-
SYSADM      psovmhcm.phoenix.nga       PSPUBHND@psovmhcm.phoenix.nga (TNS V1-V3)        PSPUBHND@psovmhcm.phoenix.nga (TNS V1-
SYSADM      psovmhcm.phoenix.nga       PSAPPSRV@psovmhcm.phoenix.nga (TNS V1-V3)        HR_DR_SELECTION_UI
SYSADM      psovmhcm.phoenix.nga       PSANALYTICSRV@psovmhcm.phoenix.nga (TNS V1-V3)   PSANALYTICSRV@psovmhcm.phoenix.nga
SYSADM      psovmhcm.phoenix.nga       PSANALYTICSRV@psovmhcm.phoenix.nga (TNS V1-V3)   PSANALYTICSRV@psovmhcm.phoenix.nga
SYSADM      psovmhcm.phoenix.nga       PSBRKHND@psovmhcm.phoenix.nga (TNS V1-V3)        PSBRKHND@psovmhcm.phoenix.nga (TNS V1-
SYSADM      psovmhcm.phoenix.nga       PSSAMSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSSAMSRV@psovmhcm.phoenix.nga (TNS V1-
SYSADM      psovmhcm.phoenix.nga       PSAPPSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PROCESSMONITOR
PUBLIC      psovmhcmdb2                oracle@psovmhcmdb2 (TNS V1-V3)                   oracle@psovmhcmdb2 (TNS V1-V3)
SYS         psovmhcmdb.phoenix.nga     sqlplus@psovmhcmdb.phoenix.nga (TNS V1-V3)       sqlplus@psovmhcmdb.phoenix.nga (TNS
On the output above, in bold, the component we’ll work on (HR_DR_SELECTION_UI), this component in that test will read data only without updated anything, so a good candidate for the test.

8.2 Change the component property
Open the Application Designer, open that component :
PS_ACTIVEDG_039
Go to the properties definitions
PS_ACTIVEDG_040 
Go to the tab “Use”
PS_ACTIVEDG_041
Check the read-only checkbox, click ok and save
 PS_ACTIVEDG_042 PS_ACTIVEDG_043
PS_ACTIVEDG_044

8.3 Go back to the front-end application, and run the transaction
 PS_ACTIVEDG_046PS_ACTIVEDG_047
PS_ACTIVEDG_048 

8.4 Whilst running the transaction, check the back-end
On the primary site, run the following :
SQL> select substr(username,1,8) userm,machine,program,module from v$session where username is not null
SQL> /

USERM      MACHINE                   PROGRAM                                          MODULE
---------- ------------------------- ------------------------------------------------ ----------------------------------------
SYSADM     WORKGROUP\PHOENIX         pside.exe                                        pside.exe

SYSADM     psovmhcm.phoenix.nga      PSMONITORSRV@psovmhcm.phoenix.nga (TNS V1-V3)    PSMONITORSRV@psovmhcm.phoenix.nga (TNS
SYSADM     psovmhcm.phoenix.nga      PSPRCSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSPRCSRV@psovmhcm.phoenix.nga (TNS V1-
SYSADM     psovmhcm.phoenix.nga      PSDSTSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSDSTSRV@psovmhcm.phoenix.nga (TNS V1-
SYSADM     psovmhcm.phoenix.nga      PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)         PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)
SYSADM     psovmhcm.phoenix.nga      PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)         PSAE
SYSADM     psovmhcm.phoenix.nga      PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)         PSAE
SYSADM     psovmhcm.phoenix.nga      PSMSTPRC@psovmhcm.phoenix.nga (TNS V1-V3)        PSMSTPRC@psovmhcm.phoenix.nga (TNS V1-
SYSADM     psovmhcm.phoenix.nga      PSMONITORSRV@psovmhcm.phoenix.nga (TNS V1-V3)    PSMONITORSRV@psovmhcm.phoenix.nga (TNS
SYSADM     psovmhcm.phoenix.nga      PSSUBDSP@psovmhcm.phoenix.nga (TNS V1-V3)        PSSUBDSP@psovmhcm.phoenix.nga (TNS V1-
SYSADM     psovmhcm.phoenix.nga      PSSUBHND@psovmhcm.phoenix.nga (TNS V1-V3)        PSSUBHND@psovmhcm.phoenix.nga (TNS V1-
SYSADM     psovmhcm.phoenix.nga      PSPUBDSP@psovmhcm.phoenix.nga (TNS V1-V3)        PSPUBDSP@psovmhcm.phoenix.nga (TNS V1-
SYSADM     psovmhcm.phoenix.nga      PSQRYSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSQRYSRV@psovmhcm.phoenix.nga (TNS V1-
SYSADM     psovmhcm.phoenix.nga      PSANALYTICSRV@psovmhcm.phoenix.nga (TNS V1-V3)   PSANALYTICSRV@psovmhcm.phoenix.nga (TNS
SYSADM     psovmhcm.phoenix.nga      PSBRKDSP@psovmhcm.phoenix.nga (TNS V1-V3)        PSBRKDSP@psovmhcm.phoenix.nga (TNS V1-
SYSADM     psovmhcm.phoenix.nga      PSPUBHND@psovmhcm.phoenix.nga (TNS V1-V3)        PSPUBHND@psovmhcm.phoenix.nga (TNS V1-
SYSADM     psovmhcm.phoenix.nga      PSAPPSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSAPPSRV@psovmhcm.phoenix.nga (TNS V1-
SYSADM     psovmhcm.phoenix.nga      PSANALYTICSRV@psovmhcm.phoenix.nga (TNS V1-V3)   PSANALYTICSRV@psovmhcm.phoenix.nga (TNS
SYSADM     psovmhcm.phoenix.nga      PSANALYTICSRV@psovmhcm.phoenix.nga (TNS V1-V3)   PSANALYTICSRV@psovmhcm.phoenix.nga (TNS
SYSADM     psovmhcm.phoenix.nga      PSBRKHND@psovmhcm.phoenix.nga (TNS V1-V3)        PSBRKHND@psovmhcm.phoenix.nga (TNS V1-
SYSADM     psovmhcm.phoenix.nga      PSSAMSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSSAMSRV@psovmhcm.phoenix.nga (TNS V1-
SYSADM     psovmhcm.phoenix.nga      PSAPPSRV@psovmhcm.phoenix.nga (TNS V1-V3)        HR_DR_SELECTION_UI
PUBLIC     psovmhcmdb2               oracle@psovmhcmdb2 (TNS V1-V3)                   oracle@psovmhcmdb2 (TNS V1-V3)
SYS        psovmhcmdb.phoenix.nga    sqlplus@psovmhcmdb.phoenix.nga (TNS V1-V3)       sqlplus@psovmhcmdb.phoenix.nga (TNS V1-

We still can see that component running onto the primary database, what about the secondary database.

On the secondary site, run the following :
SQL> select substr(username,1,8) userm,machine,program,module from v$session where username is not null
SQL> /

USERM     MACHINE                 PROGRAM                                          MODULE
--------- ----------------------- ------------------------------------------------ -------------------------------------------
SYSBKP    psovmhcm.phoenix.nga    PSMONITORSRV@psovmhcm.phoenix.nga (TNS V1-V3)    PSMONITORSRV@psovmhcm.phoenix.nga (TNS V1-
SYSBKP    psovmhcm.phoenix.nga    PSSUBDSP@psovmhcm.phoenix.nga (TNS V1-V3)        PSSUBDSP@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP    psovmhcm.phoenix.nga    PSSUBHND@psovmhcm.phoenix.nga (TNS V1-V3)        PSSUBHND@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP    psovmhcm.phoenix.nga    PSPUBDSP@psovmhcm.phoenix.nga (TNS V1-V3)        PSPUBDSP@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP    psovmhcm.phoenix.nga    PSPUBHND@psovmhcm.phoenix.nga (TNS V1-V3)        PSPUBHND@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP    psovmhcm.phoenix.nga    PSBRKDSP@psovmhcm.phoenix.nga (TNS V1-V3)        PSBRKDSP@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP    psovmhcm.phoenix.nga    PSBRKHND@psovmhcm.phoenix.nga (TNS V1-V3)        PSBRKHND@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP    psovmhcm.phoenix.nga    PSANALYTICSRV@psovmhcm.phoenix.nga (TNS V1-V3)   PSANALYTICSRV@psovmhcm.phoenix.nga (TNS V1-
SYSBKP    psovmhcm.phoenix.nga    PSANALYTICSRV@psovmhcm.phoenix.nga (TNS V1-V3)   PSANALYTICSRV@psovmhcm.phoenix.nga (TNS V1-
SYSBKP    psovmhcm.phoenix.nga    PSANALYTICSRV@psovmhcm.phoenix.nga (TNS V1-V3)   PSANALYTICSRV@psovmhcm.phoenix.nga (TNS V1-
SYSBKP    psovmhcm.phoenix.nga    PSSAMSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSSAMSRV@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP    psovmhcm.phoenix.nga    PSQRYSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSQRYSRV@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP    psovmhcm.phoenix.nga    PSAPPSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSAPPSRV@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP    psovmhcm.phoenix.nga    PSAPPSRV@psovmhcm.phoenix.nga (TNS V1-V3)        HR_DR_SELECTION_UI
SYSBKP    psovmhcm.phoenix.nga    PSMONITORSRV@psovmhcm.phoenix.nga (TNS V1-V3)    PSMONITORSRV@psovmhcm.phoenix.nga (TNS V1-
SYSBKP    psovmhcm.phoenix.nga    PSPRCSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSPRCSRV@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP    psovmhcm.phoenix.nga    PSDSTSRV@psovmhcm.phoenix.nga (TNS V1-V3)        PSDSTSRV@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP    psovmhcm.phoenix.nga    PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)         PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP    psovmhcm.phoenix.nga    PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)         PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP    psovmhcm.phoenix.nga    PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)         PSAESRV@psovmhcm.phoenix.nga (TNS V1-V3)
SYSBKP    psovmhcm.phoenix.nga    PSMSTPRC@psovmhcm.phoenix.nga (TNS V1-V3)        PSMSTPRC@psovmhcm.phoenix.nga (TNS V1-V3)
PUBLIC    psovmhcmdb.phoenix.nga  oracle@psovmhcmdb.phoenix.nga (TNS V1-V3)        oracle@psovmhcmdb.phoenix.nga (TNS V1-V3)
PUBLIC    psovmhcmdb.phoenix.nga  oracle@psovmhcmdb.phoenix.nga (TNS V1-V3)        oracle@psovmhcmdb.phoenix.nga (TNS V1-V3)
PUBLIC    psovmhcmdb.phoenix.nga  oracle@psovmhcmdb.phoenix.nga (TNS V1-V3)        oracle@psovmhcmdb.phoenix.nga (TNS V1-V3)
PUBLIC    psovmhcmdb.phoenix.nga  oracle@psovmhcmdb.phoenix.nga (TNS V1-V3)        oracle@psovmhcmdb.phoenix.nga (TNS V1-V3)
SYS       psovmhcmdb2             sqlplus@psovmhcmdb2 (TNS V1-V3)                  sqlplus@psovmhcmdb2 (TNS V1-V3)

Ok, the HR_DR_SELECTION_UI we defined earlier as read-only component is running on the secondary database. However similarly to the batch server which run AE onto the secondary database, it runs few queries onto the primary database. Let’s have a look into a trace file.

8.5 Set the TraceSQL=7
After setting the TraceSQL, re-run the same transaction as above, and check for the generated file. Here below an extract of that file :
…<snipped>…
H91TMPLT RC=0 Dur=0.000007 Disconnect
H91TMPLT RC=0 Dur=0.000005 Commit
H91TMPLT RC=0 Dur=0.000007 Disconnect
notSamTran RC=0 Dur=0.000028 Open Cursor Handle=000000000FC0DB08
notSamTran RC=0 Dur=0.000009 Open Cursor Handle=000000000FC0DB08
H91TMPLT RC=0 Dur=0.000014 Disconnect
H91TMPLT RC=0 Dur=0.000004 Commit
H91TMPLT RC=0 Dur=0.000007 Disconnect
notSamTran RC=0 Dur=0.000011 Open Cursor Handle=000000000FC0DB08
notSamTran RC=0 Dur=0.000008 Open Cursor Handle=000000000FC0DB08
H91TMPLT RC=0 Dur=0.000008 Disconnect
H91TMPLT RC=0 Dur=0.000004 Commit
H91TMPLT RC=0 Dur=0.000006 Disconnect
notSamTran RC=0 Dur=0.000011 Open Cursor Handle=000000000FC0D9D0
H91STBY RC=0 Dur=0.000005 Commit
H91STBY RC=0 Dur=0.000008 Disconnect
notSamTran RC=0 Dur=0.000008 Open Cursor Handle=000000000FC0D9D0
H91STBY RC=0 Dur=0.000148 COM Stmt=SELECT A.TEXT_ID , TO_CHAR(A.EFFDT,'YYYY-MM-DD') , A.HR_SSTEXT_KEY1 , A.HR_SSTEXT_KEY2 ,
H91STBY RC=0 Dur=0.000005 Bind-1 type=2 length=3 value=HHR
H91STBY RC=0 Dur=0.000005 Bind-2 type=2 length=4 value=DRPT
H91STBY RC=0 Dur=0.000005 Bind-3 type=2 length=11 value=GROUP_INSTR
H91STBY RC=0 Dur=0.000004 Bind-4 type=26 length=10 value=2011-03-14
H91STBY RC=0 Dur=0.000004 Bind-5 type=2 length=13 value=HR_EE_INF_MGR
H91STBY RC=0 Dur=0.000004 Bind-6 type=2 length=1 value=
H91STBY RC=0 Dur=0.000004 Bind-7 type=2 length=1 value=
H91STBY RC=0 Dur=0.000005 Bind-8 type=2 length=1 value=
H91STBY RC=0 Dur=0.000005 Bind-9 type=2 length=1 value=
H91STBY RC=0 Dur=0.000004 Bind-10 type=2 length=1 value=
H91STBY RC=0 Dur=0.000006 Commit
H91STBY RC=0 Dur=0.000043 Disconnect
notSamTran RC=0 Dur=0.000013 Open Cursor Handle=000000000FC0D9D0
H91STBY RC=0 Dur=0.000006 Commit
H91STBY RC=0 Dur=0.000008 Disconnect
notSamTran RC=0 Dur=0.000009 Open Cursor Handle=000000000FC0D9D0
H91STBY RC=0 Dur=0.000126 COM Stmt=SELECT A.TEXT_ID , TO_CHAR(A.EFFDT,'YYYY-MM-DD') , A.HR_SSTEXT_KEY1 , A.HR_SSTEXT_KEY2 ,
H91STBY RC=0 Dur=0.000005 Bind-1 type=2 length=3 value=HHR
H91STBY RC=0 Dur=0.000005 Bind-2 type=2 length=4 value=DRPT
H91STBY RC=0 Dur=0.000006 Bind-3 type=2 length=15 value=GROUP_GROUP_HDR
H91STBY RC=0 Dur=0.000006 Bind-4 type=26 length=10 value=2011-03-14
H91STBY RC=0 Dur=0.000005 Bind-5 type=2 length=13 value=HR_EE_INF_MGR
H91STBY RC=0 Dur=0.000006 Bind-6 type=2 length=1 value=
…<snipped>…
Like for the batch server, the application server start running queries onto the primary database (H91TMPLT), then switch to the secondary database (H91STBY) for the main part of the component.

So, now we have batch server and application server able to run some queries onto the secondary database in read-only mode. But be aware, none of the Peoplesoft processes are purely read-only, that’s the reason it’s still continue to start/end transactions onto the primary database, and also the need for the remote synonyms.

Enjoy it !

Nicolas.

2 comments:

Devesh said...

How do you access the secondary read only app-server. Do we have to create a new website for that? Similarly how do we run the batch process off the second server. Do we define a new process scheduler definition?

Nicolas Gasparotto said...

There is only one appserver (and one process scheduler). All is defined in the same psappsrv.cfg file (and psprcs.cfg for batch server), see the step 7.2. The primary AND the secondary declaration take place in the same file. As I said in step 8.2, you have to change the component property to read-only to run the component onto the secondary database. That's it. Peoplesoft will "balance" transparently by itself the components/processes read-write/read-only between primary and secondary respectively.

Nicolas.