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:
[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]
Primary
[code language=”sql”]
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
[/code]
Standby
[code language=”sql”]
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
[/code]
Remember that there is NO static entries nowere.
Let’s switchover to p04ncs.
[code language=”sql”]
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"
[/code]
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.
[code language=”sql”]
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"
[/code]
The configurationis now:
[code language=”sql”]
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)
[/code]
The primary
[code language=”sql”]
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
[/code]
The Standby, no changes for static connect identifiers
[code language=”sql”]
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
[/code]
Now convert the standby to snapshot
[code language=”sql”]
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
[/code]
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.
[code language=”sql”]
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:
[/code]
[code language=”sql”]
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
[/code]
How about datagard configuration?
[code language=”sql”]
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
[/code]
We have one more instance on node5
[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=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
[/code]
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.
 
[code language=”sql”]
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
[/code]
And we redo the switchover back to production.
[code language=”sql”]
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"
[/code]
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


     

Leave a Reply

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