11gR2 RAC DB switchover using DG broker (文件 ID 880017.1)

xychong123發表於2016-12-15

In this Document

Goal
Solution
References


APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.4 [Release 11.2]
Information in this document applies to any platform.
***Checked for relevance on 17-Feb-2014*** 
***Checked for relevance on 16-Jul-2015*** 

GOAL

Before performing a switchover from an Oracle RAC primary database to a physical standby database, shut down all but one primary database instance. Any primary database instances shut down at this time can be started after the switchover completes

If dataguard broker is configured, the broker is able to handle this by shutting down ALL but one instance from the primary DB.

How to configure the broker with RAC DB for 11gR2, can refer to note:1349977.1

SOLUTION

Both Primary and standby databases are RAC with two instances.
Primary -  DG112I_PRM(DG112i1 and DG112i2)
Standby - DG112I_STB (DG112i1 and DG112i2)

DB configuration:

Primary
=====
$ srvctl config database -d dg112i_prm -a
Database unique name: dg112i_prm
Database name: dg112i
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_2
Oracle user: oracle
Spfile: +DATA/dg112i/spfiledg112i.ora
Domain: au.oracle.com
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: dg112i_prm
Database instances: dg112i1,dg112i2
Disk Groups: DATA,FRA
Mount point paths: 
Services: 
Type: RAC
Database is enabled
Database is administrator managed

Standby
=====
$ srvctl config database -d dg112i_stb -a
Database unique name: dg112i_stb
Database name: dg112i
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_2
Oracle user: oracle
Spfile: +DATA/dg112i_stb/spfiledg112i.ora
Domain: au.oracle.com
Start options: read
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: dg112i_stb
Database instances: dg112i1,dg112i2
Disk Groups: DATA,FRA
Mount point paths: 
Services: 
Type: RAC
Database is enabled
Database is administrator managed



Listener status from primary:

$ lsnrctl status LISTENER_DG

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 08-SEP-2011 18:53:28

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_DG)))
STATUS of the LISTENER
------------------------
Alias LISTENER_DG
Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date 08-SEP-2011 18:00:33
Uptime 0 days 0 hr. 52 min. 55 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0.2/grid/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/grid1vm1/listener_dg/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_DG)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.225)(PORT=1555)))
Services Summary...
Service "DG112I_PRM.au.oracle.com" has 2 instance(s).
Instance "dg112i1", status READY, has 2 handler(s) for this service...
Instance "dg112i2", status READY, has 1 handler(s) for this service...
Service "DG112I_PRM_DGB.au.oracle.com" has 2 instance(s).
Instance "dg112i1", status READY, has 2 handler(s) for this service...
Instance "dg112i2", status READY, has 1 handler(s) for this service...
Service "dg112i_prm_dgmgrl.au.oracle.com" has 1 instance(s).
Instance "dg112i1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully



Listener status from standby:

$ lsnrctl status listener_dg

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 08-SEP-2011 18:27:57

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_DG)))
STATUS of the LISTENER
------------------------
Alias LISTENER_DG
Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date 08-SEP-2011 16:15:37
Uptime 0 days 2 hr. 12 min. 19 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0.2/grid/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/grid2vm2/listener_dg/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_DG)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.229)(PORT=1555)))
Services Summary...
Service "dg112i_stb.au.oracle.com" has 2 instance(s).
Instance "dg112i1", status READY, has 1 handler(s) for this service...
Instance "dg112i2", status READY, has 2 handler(s) for this service...
Service "dg112i_stb_DGB.au.oracle.com" has 2 instance(s).
Instance "dg112i1", status READY, has 1 handler(s) for this service...
Instance "dg112i2", status READY, has 2 handler(s) for this service...
Service "dg112i_stb_dgmgrl.au.oracle.com" has 1 instance(s).
Instance "dg112i2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


DG Broker configuration:

DGMGRL> connect sys@DG112I_PRM
Connected.
DGMGRL> show configuration verbose;

Configuration - dg112

Protection Mode: MaxPerformance
Databases:
dg112i_prm - Primary database
dg112i_stb - Physical standby database

Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS



Primary database setting:

DGMGRL> show database verbose 'dg112i_prm'

Database - dg112i_prm

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

Properties:
DGConnectIdentifier = 'dg112i_prm'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'MANUAL'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName(*)
StaticConnectIdentifier(*)
StandbyArchiveLocation(*)
AlternateLocation(*)
LogArchiveTrace(*)
LogArchiveFormat(*)
TopWaitEvents(*)
(*) - Please check specific instance for the property value

Database Status:
SUCCESS

DGMGRL> show instance verbose dg112i1 on database dg112i_prm;

Instance 'dg112i1' of database 'dg112i_prm'

Host Name: grid1vm1.au.oracle.com
PFILE: 
Properties:
SidName = 'dg112i1'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.11.225)(PORT=1555))(CONNECT_DATA=(SERVICE_NAME=DG112I_PRM_DGMGRL.au.oracle.com)(INSTANCE_NAME=dg112i1)(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 dg112i2 on database dg112i_prm;

Instance 'dg112i2' of database 'dg112i_prm'

Host Name: grid1vm2.au.oracle.com
PFILE: 
Properties:
SidName = 'dg112i2'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.11.226)(PORT=1555))(CONNECT_DATA=(SERVICE_NAME=DG112I_PRM_DGMGRL.au.oracle.com)(INSTANCE_NAME=dg112i2)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'

Instance Status:
SUCCESS


Standby database setting:

DGMGRL> show database verbose 'dg112i_stb'

Database - dg112i_stb

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
dg112i1
dg112i2 (apply instance)

Properties:
DGConnectIdentifier = 'dg112i_stb'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'MANUAL'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName(*)
StaticConnectIdentifier(*)
StandbyArchiveLocation(*)
AlternateLocation(*)
LogArchiveTrace(*)
LogArchiveFormat(*)
TopWaitEvents(*)
(*) - Please check specific instance for the property value

Database Status:
SUCCESS

DGMGRL> show instance verbose dg112i1 on database dg112i_stb;

Instance 'dg112i1' of database 'dg112i_stb'

Host Name: grid2vm1.au.oracle.com
PFILE: 
Properties:
SidName = 'dg112i1'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.11.228)(PORT=1555))(CONNECT_DATA=(SERVICE_NAME=dg112i_stb_DGMGRL.au.oracle.com)(INSTANCE_NAME=dg112i1)(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 dg112i2 on database dg112i_stb;

Instance 'dg112i2' of database 'dg112i_stb'

Host Name: grid2vm2.au.oracle.com
PFILE: 
Properties:
SidName = 'dg112i2'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.11.229)(PORT=1555))(CONNECT_DATA=(SERVICE_NAME=dg112i_stb_DGMGRL.au.oracle.com)(INSTANCE_NAME=dg112i2)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'

Instance Status:
SUCCESS



It is ready for the switchover. Note that we do not need to shutdown any instances

DGMGRL> switchover to dg112i_stb;
Performing switchover NOW, please wait...
New primary database "dg112i_stb" is opening...
Operation requires shutdown of instance "dg112i1" on database "dg112i_prm"
Shutting down instance "dg112i1"...
ORACLE instance shut down.
Operation requires startup of instance "dg112i1" on database "dg112i_prm"
Starting instance "dg112i1"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "dg112i_stb"
DGMGRL>


The status of the DB changed to:

$ srvctl config database -d dg112i_prm -a
Database unique name: dg112i_prm
Database name: dg112i
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_2
Oracle user: oracle
Spfile: +DATA/dg112i/spfiledg112i.ora
Domain: au.oracle.com
Start options: open
Stop options: immediate
Database role: PHYSICAL_STANDBY             <<<<<<<< Standby
Management policy: AUTOMATIC
Server pools: dg112i_prm
Database instances: dg112i1,dg112i2
Disk Groups: DATA,FRA
Mount point paths: 
Services: 
Type: RAC
Database is enabled
Database is administrator managed

$ srvctl config database -d dg112i_stb -a
Database unique name: dg112i_stb
Database name: dg112i
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_2
Oracle user: oracle
Spfile: +DATA/dg112i_stb/spfiledg112i.ora
Domain: au.oracle.com
Start options: open
Stop options: immediate
Database role: PRIMARY                                <<<<<<<< Primary
Management policy: AUTOMATIC
Server pools: dg112i_stb
Database instances: dg112i1,dg112i2
Disk Groups: DATA,FRA
Mount point paths: 
Services: 
Type: RAC
Database is enabled
Database is administrator managed


During the switchover, second instance of primary (DG112i2) was shutdown abort.
Both of the original standby instances (DG112i1 / DG112i2) are opened as new primary DB.

Both instances of the original primary instances started as standby role.


ORA-12514 during the switchover:
With the above configuration, the StaticConnectIdentifier is set correctly. 
If the port is changed to 1521, then the DG broker will return ORA-12514 when try to restart the instance during the switchover.

DGMGRL> switchover to dg112i_stb;
Performing switchover NOW, please wait...
New primary database "dg112i_stb" is opening...
Operation requires shutdown of instance "dg112i1" on database "dg112i_prm"
Shutting down instance "dg112i1"...
ORACLE instance shut down.
Operation requires startup of instance "dg112i1" on database "dg112i_prm"
Starting instance "dg112i1"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
Warning: You are no longer connected to ORACLE.

Please complete the following steps to finish switchover:
start up instance "dg112i1" of database "dg112i_prm"

DGMGRL>



The switchover is success, just need to perform the startup of the failed instance(s).

The fix is to modify instance property "StaticConnectIdentifier" to the correct setting, in this case, port 1555, or unset it and it will use the database property "DGConnectIdentifier" instead, which should be this connection string ''dg112i_prm_dgmgrl'.

DGMGRL> edit instance dg112i1 on database dg112i_prm set PROPERTY StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.11.225)(PORT=1555))(CONNECT_DATA=(SERVICE_NAME=DG112I_PRM_DGMGRL.au.oracle.com)(INSTANCE_NAME=dg112i1)(SERVER=DEDICATED)))';
Property "staticconnectidentifier" updated
DGMGRL> edit instance dg112i2 on database dg112i_prm set PROPERTY StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.11.226)(PORT=1555))(CONNECT_DATA=(SERVICE_NAME=DG112I_PRM_DGMGRL.au.oracle.com)(INSTANCE_NAME=dg112i2)(SERVER=DEDICATED)))';
Property "staticconnectidentifier" updated
DGMGRL> edit instance dg112i1 on database dg112i_stb set PROPERTY StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.11.228)(PORT=1555))(CONNECT_DATA=(SERVICE_NAME=dg112i_stb_DGMGRL.au.oracle.com)(INSTANCE_NAME=dg112i1)(SERVER=DEDICATED)))';
Property "staticconnectidentifier" updated
DGMGRL> edit instance dg112i2 on database dg112i_stb set PROPERTY StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.11.229)(PORT=1555))(CONNECT_DATA=(SERVICE_NAME=dg112i_stb_DGMGRL.au.oracle.com)(INSTANCE_NAME=dg112i2)(SERVER=DEDICATED)))';
Property "staticconnectidentifier" updated


or

DGMGRL> edit instance dg112i1 on database dg112i_prm set PROPERTY StaticConnectIdentifier='';
Property "staticconnectidentifier" updated
DGMGRL> edit instance dg112i2 on database dg112i_prm set PROPERTY StaticConnectIdentifier='';
Property "staticconnectidentifier" updated
DGMGRL> edit instance dg112i1 on database dg112i_stb set PROPERTY StaticConnectIdentifier='';
Property "staticconnectidentifier" updated
DGMGRL> edit instance dg112i2 on database dg112i_stb set PROPERTY StaticConnectIdentifier='';
Property "staticconnectidentifier" updated

 

For switchover on 12c refer,

12c Dataguard Switchover Best Practices using SQLPLUS (Doc ID 1578787.1)

12c Dataguard Switchover Best Practices using DGMGRL(Dataguard Broker Command Prompt) (Doc ID 1582837.1)

REFERENCES

NOTE:1349977.1 - Data Guard Physical Standby 11.2 RAC Primary to RAC Standby using a second network
NOTE:1305019.1 - 11.2 Data Guard Physical Standby Switchover Best Practices using the Broker

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/20747382/viewspace-2130687/，如需轉載，請註明出處，否則將追究法律責任。

相關文章