DataGuard 12c: Is broker mature with serverPools?

Hi there,

Having a DataGuard configuration in RAC with the introduction of ServerPools was chalenging in 11g.

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

Let see how this is going in 12c (12.1.0.2).

The configuration is the following:

8 nodes cluster (node1… node8)
3 server pools

oracle@node1:~/ [CDBJKO_1] 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

Server Pools details:

oracle@node2:/u01/app/oracle/diag/rdbms/p04ncs/p04ncs_1/trace/ [p04ncs_1] 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/diag/rdbms/p04ncs/p04ncs_1/trace/ [p04ncs_1] srvctl config srvpool -serverpool PRD
Server pool name: PRD
Importance: 3, Min: 2, Max: 2
Category: hub
Candidate server names:
oracle@node2:/u01/app/oracle/diag/rdbms/p04ncs/p04ncs_1/trace/ [p04ncs_1] srvctl config srvpool -serverpool WEB
Server pool name: WEB
Importance: 2, Min: 3, Max: 4
Category: hub
Candidate server names:

Database CDBJKO: 4 nodes RAC in srvpool WEB
Database p03ncs: 2 nodes RAC in serverpool PRD (primary)
Database p04ncs: 2 nodes RAC one node (Standby)

ora.cdbjko.db
      1        ONLINE  ONLINE       node1                    Open,STABLE
      2        ONLINE  ONLINE       node3                    Open,STABLE
      3        ONLINE  ONLINE       node6                    Open,STABLE
      4        ONLINE  ONLINE       node8                    Open,STABLE
ora.p03ncs.db
      1        ONLINE  ONLINE       node7                    Open,STABLE
      2        ONLINE  ONLINE       node5                    Open,STABLE
ora.p04ncs.db
      1        ONLINE  INTERMEDIATE node2                    Mounted (Closed),STABLE

This is the dataguard configuration
ps: there is no static entries <db_unique_name>_DGMGRL

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 32 seconds ago)
DGMGRL show instance verbose p03ncs_1

Instance 'p03ncs_1' of database 'p03ncs'

  Host Name: node7
  PFILE:
  Properties:
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.117)(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
192.168.1.117 = node7-vip
DGMGRL show instance verbose p03ncs_2

Instance 'p03ncs_2' 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_2)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Instance Status:
SUCCESS

192.168.1.115 = node5-vip

Now shunting down node7…
Because of importance of serverpool PRD (3) and importance (2) and min servers (3) of serverpool WEB, one the nodes from serverpool WEB will be moved to serverpool PRD

This is the result when completed.

ora.cdbjko.db
      1        ONLINE  ONLINE       node1                    Open,STABLE
      2        ONLINE  ONLINE       node3                    Open,STABLE
      3        ONLINE  ONLINE       node6                    Open,STABLE
      4        ONLINE  OFFLINE                               Instance Shutdown,STABLE
ora.node7.vip
      1        ONLINE  INTERMEDIATE node3                    FAILED OVER,STABLE
ora.p03ncs.db
      1        ONLINE  ONLINE       node8                    Open,STABLE
      2        ONLINE  ONLINE       node5                    Open,STABLE

Now how about dataguard 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)
DGMGRL show database p03ncs

Database - p03ncs

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    p03ncs_1   <-- Still our two instances
    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=<span style="color: #ff0000;"><strong>192.168.1.118</strong></span>)(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

The static connect identifier was modified to reflect the instance relocation

DGMGRL show instance verbose p03ncs_2

Instance 'p03ncs_2' 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_2)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Instance Status:
SUCCESS

Now let’s add one more instance to p03ncs database…

==> srvctl modify srvpool -serverpool PRD -l 2 -u 3

And start back the host node7

The host node7 will be, according to serverpool changes, affected to PRD server pool.
This is the result at the end.

ora.cdbjko.db
      1        ONLINE  ONLINE       node1                    Open,STABLE
      2        ONLINE  ONLINE       node3                    Open,STABLE
      3        ONLINE  ONLINE       node6                    Open,STABLE
      4        ONLINE  OFFLINE                               Instance Shutdown,STABLE
ora.node7.vip
      1        ONLINE  ONLINE       node7                    STABLE
ora.p03ncs.db
      1        ONLINE  ONLINE       node8                    Open,STABLE
      2        ONLINE  ONLINE       node5                    Open,STABLE
      3        ONLINE  ONLINE       node7                    Open,STABLE

Now how about dataguard 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)
DGMGRL show database p03ncs

Database - p03ncs

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

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: node5
  PFILE:
  Properties:
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.115)(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

The new instance is now part of the configuration with correct static connect identifier!

DGMGRL  show instance verbose p03ncs_3

Instance 'p03ncs_3' of database 'p03ncs'

  Host Name: node5
  PFILE:
  Properties:
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<strong><span style="color: #ff0000;">192.168.1.117</span></strong>)(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

What if now if we convert the rac one node standby to RAC?

srvctl convert database -db p04ncs -dbtype RAC
srvctl status database -db p04ncs 
	Instance p04ncs_1 is running on node node2
	Instance p04ncs_2 is running on node node4

This is the result after the operation.

ora.cdbjko.db
      1        ONLINE  ONLINE       node1                    Open,STABLE
      2        ONLINE  ONLINE       node3                    Open,STABLE
      3        ONLINE  ONLINE       node6                    Open,STABLE
      4        ONLINE  OFFLINE                               Instance Shutdown,STABLE
ora.p03ncs.db
      1        ONLINE  ONLINE       node8                    Open,STABLE
      2        ONLINE  ONLINE       node5                    Open,STABLE
      3        ONLINE  ONLINE       node7                    Open,STABLE
ora.p04ncs.db
      1        ONLINE  ONLINE       node2                    Mounted (Closed),STABLE
      2        ONLINE  ONLINE       node4                    Mounted (Closed),STABLE

Now how about dataguard 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)

Configuration is okay!

And now show database p04ncs should show two instances…

DGMGRL show database p04ncs

Database - p04ncs

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          13 minutes 35 seconds (computed 1 second ago)
  Average Apply Rate: 6.32 MByte/s
  Real Time Query:    OFF
  Instance(s):
    p04ncs_1 (apply instance)
    p04ncs_2  <-- New instance added

Database Status:
SUCCESS

Correct and the new instance is now part of the configuration with correct static connect identifier!

DGMGRL show instance verbose p04ncs_1

Instance 'p04ncs_1' of database 'p04ncs'

  Host Name: node2
  PFILE:
  Properties:
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.112)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=p04ncs_DGMGRL)(INSTANCE_NAME=p04ncs_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 p04ncs_2

Instance 'p04ncs_2' of database 'p04ncs'

  Host Name: node4
  PFILE:
  Properties:
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<strong><span style="color: #ff0000;">192.168.1.114</span></strong>)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=p04ncs_DGMGRL)(INSTANCE_NAME=p04ncs_2)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Instance Status:
SUCCESS

Ok, now let’s try to remove one instance from p03ncs database and see how it goes.

For doing this, we will increase the importance of serverpool WEB to 4 and the minimum to 4 to produce a node move from serverpool PRD

==>srvctl modify srvpool -serverpool WEB -i 4 -l 4 -force

-force because this operation will still one node from serverpool PRD and therefore stop one instance from database p03ncs.

This is the result after the operation.

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
      1        ONLINE  ONLINE       node8                    Open,STABLE
      2        ONLINE  ONLINE       node5                    Open,STABLE
      3        ONLINE  OFFLINE                               Instance Shutdown,STABLE
ora.p04ncs.db
      1        ONLINE  ONLINE       node2                    Mounted (Closed),STABLE
      2        ONLINE  ONLINE       node4                    Mounted (Closed),STABLE

Now how about dataguard configuration?

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 1 second ago)

Still okay!

But

DGMGRL show database p03ncs

Database - p03ncs

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    p03ncs_1
    p03ncs_2
    p03ncs_3  <-- The instance is still here

Database Status:
SUCCESS

We still have three instances here!
So clearly in this case the broker configuration is NOT updated.
But wait, there is a broker command:

REMOVE INSTANCE <instance name> [ON { DATABASE | FAR_SYNC } <object name>];

Now we know…

DGMGRL remove instance p03ncs_3 on database p03ncs
Removed instance "p03ncs_3" from database "p03ncs"
DGMGRL show database p03ncs

Database - p03ncs

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

Database Status:
SUCCESS
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 36 seconds ago)

Conclusion, works quiet well. I would appreciate a complete update of the broker configuration in the next release:-)

Coming next: what happen from OEM point of view for all those targets during relocates?

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 *