Sunday, March 13, 2011

PSOVM and Active Data Guard : enabling ADG

To go further, it is assumed you have set a Physical Standby Database on Peoplesoft OVM as shown in my previous blog entry : PSOVM and Activate Data Guard : Physical Standby Database
As explained in previous post, we created a Physical Standby Database, it should be relatively straightforward to enable the Active Data Guard. That will allow end users to query (in read only mode) the standby database whilst the redo are applying onto that standby database, feature known to be real-time query.
At the end of this stage, the architecture will be as follow (in blue the change compared to the previous post) :
PS_ACTIVEDG_035
1. dg_broker_start init parameter
Set the dg_broker_start to true on primary database and on secondary database
SQL> alter system set dg_broker_start=true;
System altered.
2. Create the dataguard configuration
On the primary site, start the Data Guard Manager client command line for the configuration settings :
[oracle@psovmhcmdb dbs]$ dgmgrl
DGMGRL for Linux: Version 11.1.0.7.0 - 64bit Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle@H91TMPLT (connection to the primary database)
Connected.
DGMGRL> create configuration psovmdgmgrl
> as primary database is H91TMPLT
> connect identifier is H91TMPLT;
Configuration "psovmdgmgrl" created with primary database "h91tmplt"
DGMGRL> add database H91STBY
> as connect identifier is H91STBY
> maintained as physical;
Database "h91stby" added
DGMGRL>
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration

Configuration
  Name:                psovmdgmgrl
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Databases:
    h91tmplt - Primary database
    h91stby  - Physical standby database

Fast-Start Failover: DISABLED
Current status for "psovmdgmgrl":
SUCCESS

DGMGRL>
3. Turn off the redo apply (dgmgrl)
In order to have an active data guard, we should stop the replication first (run dgmgrl on primary site) :
DGMGRL> edit database h91stby set state=apply-off;
Succeeded.
DGMGRL>
4. Read-only mode
Now, we can open the secondary database in read-only mode (on the further startups, that mode will be the default one):
SQL> alter database open read only;
Database altered.
5. Turn on the redo apply (dgmgrl)
Now, we can turn the redo apply on again (run dgmgrl on primary site)
DGMGRL> edit database h91stby set state=apply-on;
Succeeded.
DGMGRL>
6. Test
We will see if the replication is working whilst the standby database is open in read-only mode and “query-able” as SYSADM user.
6.1 On the secondary database
SQL> select instance_name,host_name from v$instance
SQL> /
INSTANCE_NAME    HOST_NAME
---------------- ----------------------------------------------------------------
H91TMPLT         psovmhcmdb2

SQL> conn sysadm/SYSADM
Connected.
SQL> select count(*) from psoprdefn;

  COUNT(*)
----------
       155
SQL> select oprid from psoprdefn where trunc(LASTPSWDCHANGE)=trunc(sysdate);

no rows selected
6.2 Within the front-end application, we’ll duplicate PS user as following
PS_ACTIVEDG_001
PS_ACTIVEDG_002 PS_ACTIVEDG_003

6.3 And now, on the secondary database, re-run the previous query, it should return one more row
SQL> select oprid from psoprdefn where trunc(LASTPSWDCHANGE)=trunc(sysdate);
OPRID
---------------------------------------------------
NICOLAS

SQL> select count(*) from psoprdefn;
  COUNT(*)
----------
       156

SQL>
=> the new created user is coming to the secondary database, and we can check it directly from PSOPRDEFN (that would not be possible without Active Data Guard).
7. For the record, show the dgmgrl status (configuration and databases)
[oracle@psovmhcmdb ~]$ dgmgrl
DGMGRL for Linux: Version 11.1.0.7.0 - 64bit Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle@H91TMPLT
Connected.
DGMGRL> show configuration

Configuration
  Name:                psovmdgmgrl
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Databases:
    h91tmplt - Primary database
    h91stby  - Physical standby database

Fast-Start Failover: DISABLED
Current status for "psovmdgmgrl":
SUCCESS

DGMGRL> show database h91stby
Database
  Name:            h91stby
  Role:            PHYSICAL STANDBY
  Enabled:         YES
  Intended State:  APPLY-ON
  Instance(s):
    H91TMPLT

Current status for "h91stby":
SUCCESS

DGMGRL> show database h91tmplt
Database
  Name:            h91tmplt
  Role:            PRIMARY
  Enabled:         YES
  Intended State:  TRANSPORT-ON
  Instance(s):
    H91TMPLT

Current status for "h91tmplt":
SUCCESS

DGMGRL>
Now we can talk, query a database without loading the primary database while the redo continue to be applied. That's really helpful, especially for users who run a lot of reports through a tier query tool. Once more, PSOVM allow us to test it easily before implementing it in real live.
But it can be not enough. An other great feature coming with the latest PSOVM and Peopletools 8.51 is the StandbyDBName configuration possiblity on Batch and AppServer. That's obviously the next step. To be continued.

Read my next articles :

No comments: