DataGuard 12c: Is broker mature with serverPools? part 2

Hi there,

This is the part II for DataGuard 12c in RAC with serverpools.

This is a test case in production, you will probably never ever have the standby on the same cluster…

This time we will perform some kind of operations like switchover and snapshots, and we will see how the broker in 12c behaves.
The configuration is the same as described here

Prerequisits:

Password files are sync
Force_loging is on
Flashback is active
Standby_file_management is set to auto
Standby redo logs exist and CAN cope with the maximum number of instances you PLAN to have.

The current configuration:

oracle@node1:~/ [CDBJKO_1] dgmgrl
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL connect sys/xxxxxx@p03ncs
Connected as SYSDBA.
DGMGRL show configuration

Configuration - ncs

  Protection Mode: MaxPerformance
  Members:
  p03ncs - Primary database
    p04ncs - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status: 
SUCCESS   (status updated 59 seconds ago)

Primary

DGMGRL show database p03ncs

Database - p03ncs

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    p03ncs_1
    p03ncs_2

Database Status:
SUCCESS

DGMGRL show instance verbose p03ncs_1

Instance 'p03ncs_1' of database 'p03ncs'

  Host Name: node8
  PFILE:
  Properties:
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.118)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=p03ncs_DGMGRL)(INSTANCE_NAME=p03ncs_1)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Instance Status:
SUCCESS

DGMGRL  show instance verbose p03ncs_2

Instance 'p03ncs_2' of database 'p03ncs'

  Host Name: node4
  PFILE:
  Properties:
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.114)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=p03ncs_DGMGRL)(INSTANCE_NAME=p03ncs_2)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Instance Status:
SUCCESS

Standby

DGMGRL show database verbose p04ncs

Database - p04ncs

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 68.00 KByte/s
  Active Apply Rate:  0 Byte/s
  Maximum Apply Rate: 0 Byte/s
  Real Time Query:    OFF
  Instance(s):
    p04ncs_1

  Properties:
    DGConnectIdentifier             = 'p04ncs'
    ...
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.112)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=p04ncs_DGMGRL)(INSTANCE_NAME=p04ncs_1)(SERVER=DEDICATED)))'
    ...
Database Status:
SUCCESS

Remember that there is NO static entries nowere.

Let’s switchover to p04ncs.

DGMGRL connect /
Connected as SYSDG.
DGMGRL> switchover to p04ncs;
Performing switchover NOW, please wait...
Operation requires a connection to instance "p04ncs_1" on database "p04ncs"
Connecting to instance "p04ncs_1"...
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

        connect to instance "p04ncs_1" of database "p04ncs"

What’s this? All password files are okay!
In 11g, when you have setup all static entries, you could just connect / to the local database and do your switchover.
Now you hav explicitly to connect to a database using the network identifier.

DGMGRL connect sys/xxxxx@"p04ncs"
Connected as SYSDBA.
DGMGRL switchover to p04ncs;
Performing switchover NOW, please wait...
New primary database "p04ncs" is opening...
Oracle Clusterware is restarting database "p03ncs" ...
Switchover succeeded, new primary is "p04ncs"

The configurationis now:

DGMGRL> show configuration

Configuration - ncs

  Protection Mode: MaxPerformance
  Members:
  p04ncs - Primary database
    p03ncs - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 51 seconds ago)

The primary

DGMGRL show database verbose p04ncs

Database - p04ncs

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    p04ncs_1

  Properties:
    DGConnectIdentifier             = 'p04ncs'
    ...
    RecvQEntries                    = '(monitor)'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.112)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=p04ncs_DGMGRL)(INSTANCE_NAME=p04ncs_1)(SERVER=DEDICATED)))'
    ...
    
Database Status:
SUCCESS

The Standby, no changes for static connect identifiers

DGMGRL> show database p03ncs

Database - p03ncs

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 30.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    p03ncs_1 (apply instance)
    p03ncs_2

Database Status:
SUCCESS

Now convert the standby to snapshot

DGMGRL convert database p03ncs to snapshot standby;
Converting database "p03ncs" to a Snapshot Standby database, please wait...
Database "p03ncs" converted successfully

Resources:

ora.p03ncs.db
      1        ONLINE  ONLINE       node8                    Open,STABLE
      2        ONLINE  ONLINE       node4                    Open,STABLE

select INSTANCE_NUMBER,INSTANCE_NAME,INSTANCE_ROLE  from gv$instance;

INSTANCE_NUMBER INSTANCE_NAME    INSTANCE_ROLE
--------------- ---------------- ------------------
              2 p03ncs_2         PRIMARY_INSTANCE
              1 p03ncs_1         PRIMARY_INSTANCE

A new application feature will be added and development team would like to validate if the feature will be able to work with three nodes.

We will add one more instance to our snapshot standby!

Let’s do this by changing some serverpools settings.
We do not want downtime for the WEB application under WEB serverpool. So the only available server is the spare one for the RAC one NODE.

srvctl status srvpool
Server pool name: Free
Active servers count: 0
Server pool name: Generic
Active servers count: 0
Server pool name: PRD
Active servers count: 2
Server pool name: STB
Active servers count: 2
Server pool name: WEB
Active servers count: 4
oracle@node2:/u01/app/oracle/product/12.1.0/dbhome_1/dbs/ [+ASM8] srvctl config srvpool -serverpool PRD
Server pool name: PRD
Importance: 3, Min: 2, Max: 3
Category: hub
Candidate server names:
oracle@node2:/u01/app/oracle/product/12.1.0/dbhome_1/dbs/ [+ASM8] srvctl config srvpool -serverpool STB
Server pool name: STB
Importance: 2, Min: 2, Max: 2
Category: hub
Candidate server names:
oracle@node2:/u01/app/oracle/product/12.1.0/dbhome_1/dbs/ [+ASM8] srvctl config srvpool -serverpool WEB
Server pool name: WEB
Importance: 4, Min: 4, Max: 4
Category: hub
Candidate server names:
srvctl modify srvpool -serverpool PRD -l 3

ora.cdbjko.db
      1        ONLINE  ONLINE       node1                    Open,STABLE
      2        ONLINE  ONLINE       node3                    Open,STABLE
      3        ONLINE  ONLINE       node6                    Open,STABLE
      4        ONLINE  ONLINE       node7                    Open,STABLE
oora.p03ncs.db
      1        ONLINE  ONLINE       node8                    Open,STABLE
      2        ONLINE  ONLINE       node4                    Open,STABLE
      3        ONLINE  ONLINE       node5                    Open,STABLE
ora.p04ncs.db
      1        ONLINE  ONLINE       node2                    Open,STABLE
      

select INSTANCE_NUMBER,INSTANCE_NAME,INSTANCE_ROLE  from gv$instance;

INSTANCE_NUMBER INSTANCE_NAME    INSTANCE_ROLE
--------------- ---------------- ------------------
              2 p03ncs_2         PRIMARY_INSTANCE
              1 p03ncs_1         PRIMARY_INSTANCE
              3 p03ncs_3         PRIMARY_INSTANCE
      

How about datagard configuration?

DGMGRL show configuration

Configuration - ncs

  Protection Mode: MaxPerformance
  Members:
  p04ncs - Primary database
    p03ncs - Snapshot standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 0 seconds ago)

DGMGRL show database p03ncs

Database - p03ncs

  Role:               SNAPSHOT STANDBY
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          20 minutes (computed 1 second ago)
  Instance(s):
    p03ncs_1
    p03ncs_2
    p03ncs_3

Database Status:
SUCCESS

We have one more instance on node5

DGMGRL show instance verbose p03ncs_3

Instance 'p03ncs_3' of database 'p03ncs'

  Host Name: node5
  PFILE:
  Properties:
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.115)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=p03ncs_DGMGRL)(INSTANCE_NAME=p03ncs_3)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Instance Status:
SUCCESS

Now development can start there tests!

After some time, we are ready to implement the changes in production.
Now we convert back the snapshot stantby to physical standby.

 

DGMGRL connect sys/xxxxx@p04ncs
Connected as SYSDBA.
DGMGRL convert database p03ncs to physical standby;
Converting database "p03ncs" to a Physical Standby database, please wait...
Oracle Clusterware is restarting database "p03ncs" ...
Continuing to convert database "p03ncs" ...
Database "p03ncs" converted successfully

And we redo the switchover back to production.

DGMGRL switchover to p03ncs;
Performing switchover NOW, please wait...
Operation requires a connection to instance "p03ncs_1" on database "p03ncs"
Connecting to instance "p03ncs_1"...
Connected as SYSDBA.
New primary database "p03ncs" is opening...
Oracle Clusterware is restarting database "p04ncs" ...
Switchover succeeded, new primary is "p03ncs"

Now we have three nodes in production ready for the implemtnation of the new feature!
During all steps, the broker was able to maintain the configuration without any specific action required.

Conclusion, things are going well!

Cheers
jko

The following two tabs change content below.

Jacques

I am Oracle Certified Master 11g & 12c database architect with significant experience in heterogeneous environments, and strong ability to lead complex and critical projects requiring multiple technical implementations. at Trivadis SA

Leave a Reply

Your email address will not be published. Required fields are marked *