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
[code language=”sql”]
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
[/code]
Server Pools details:
[code language=”sql”]
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:
[/code]
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)
[code language=”sql”]
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
[/code]
This is the dataguard configuration
ps: there is no static entries <db_unique_name>_DGMGRL
[code language=”sql”]
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)
[/code]
[code language=”sql”]
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
[/code]
[code language=”sql”]
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
[/code]
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.
[code language=”sql”]
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
[/code]
Now how about dataguard configuration?
[code language=”sql”]
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)
[/code]
[code language=”sql”]
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
[/code]
[code language=”sql”]
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
[/code]
The static connect identifier was modified to reflect the instance relocation
[code language=”sql”]
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
[/code]
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.
[code language=”sql”]
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
[/code]
Now how about dataguard configuration?
[code language=”sql”]
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)
[/code]
[code language=”sql”]
DGMGRL show database p03ncs
Database – p03ncs
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
p03ncs_1
p03ncs_2
p03ncs_3
Database Status:
SUCCESS
[/code]
[code language=”sql”]
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
[/code]
[code language=”sql”]
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
[/code]
The new instance is now part of the configuration with correct static connect identifier!
[code language=”sql”]
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
[/code]
What if now if we convert the rac one node standby to RAC?
[code language=”sql”]
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
[/code]
This is the result after the operation.
[code language=”sql”]
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
[/code]
Now how about dataguard configuration?
[code language=”sql”]
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)
[/code]
Configuration is okay!
And now show database p04ncs should show two instances…
[code language=”sql”]
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
[/code]
Correct and the new instance is now part of the configuration with correct static connect identifier!
[code language=”sql”]
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
[/code]
[code language=”sql”]
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
[/code]
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.
[code language=”sql”]
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
[/code]
Now how about dataguard configuration?
[code language=”sql”]
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)
[/code]
Still okay!
But
[code language=”sql”]
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
[/code]
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…
[code language=”sql”]
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
[/code]
[code language=”sql”]
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)
[/code]
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


     

Leave a Reply

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