Reinstate a failed over Data Guard using Flashback database
By Arif Malik
In a Data Guard environment, the operations of any Oracle DBA wants to avoid is a Data Guard infrastructure failover.
However, in a MAA environments, unplanned failures can occur and they forces to do a database failover
However, if this failure is repaired in a few hours and the lost data center , is operational again. Do you need torecreate all the Data Guard infrastructure once the failover has been performed?
With Flashback database, if flashback logs retention time has not exceeded , it is not necessary. Here's an exampleusing two single-instance Data Guard.
However, in a MAA environments, unplanned failures can occur and they forces to do a database failover
However, if this failure is repaired in a few hours and the lost data center , is operational again. Do you need torecreate all the Data Guard infrastructure once the failover has been performed?
With Flashback database, if flashback logs retention time has not exceeded , it is not necessary. Here's an exampleusing two single-instance Data Guard.
First of all, We need to set up Flashback Database:
PRIMARY DATABASE: Mount the database, configure flashback retention, start flashback database and open the database.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=240; # Set up for 4 hour retention
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=240; # Set up for 4 hour retention
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;
STANDBY DATABASE: Stop redo apply, configure flashback retention, start flashback database, open the database and start redo apply (Is active DG).
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=240;
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=240;
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Then wait for some time...
Let's test how Flashback Database help us in Data Guard architectures
Initial Configuration ODGIB01 is PRIMARY and ODGB02 is a STANDBY instance
***************************************************
DGMGRL> show configuration
Configuration - odgib.eu.customer.net
Protection Mode: MaxAvailability
Databases:
ODGIB01 - Primary database
ODGIB02 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
We perform a switchover using our script that calls DG Broker:
***************************************************
oracle@odgi01[odgib01]:/opt/oracle/db/dbodgib/scripts$ ./switchover_bd.sh
Obteniendo el role de la base de datos local odgib01 ...
Base de datos con role PRIMARY.
####################################################################
Wed Mar 14 17:13:49 CET 2012 - Switchover de base de datos.
La base de datos ODGIB01 pasara a ser STANDBY
La base de datos ODGIB02 pasara a ser PRIMARY
Switchover to ODGIB02 ...
DGMGRL for Linux: Version 11.2.0.2.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> Connected.
DGMGRL> Performing switchover NOW, please wait...
New primary database "ODGIB02" is opening...
Operation requires shutdown of instance "odgib01" on database "ODGIB01"
Shutting down instance "odgib01"...
ORACLE instance shut down.
Operation requires startup of instance "odgib01" on database "ODGIB01"
Starting instance "odgib01"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "ODGIB02"
DGMGRL>
Configuration - odgib.eu.customer.net
Protection Mode: MaxAvailability
Databases:
ODGIB02 - Primary database
ODGIB01 - 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
DGMGRL> DGMGRL>
Wed Mar 14 17:13:49 CET 2012 - Switchover a ODGIB02 correcto.
####################################################################
Data Guard Check with DG Broker, The switchover has been succeeded.
Initial Configuration ODGIB01 is PRIMARY and ODGB02 is a STANDBY instance
***************************************************
DGMGRL> show configuration
Configuration - odgib.eu.customer.net
Protection Mode: MaxAvailability
Databases:
ODGIB01 - Primary database
ODGIB02 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
We perform a switchover using our script that calls DG Broker:
***************************************************
oracle@odgi01[odgib01]:/opt/oracle/db/dbodgib/scripts$ ./switchover_bd.sh
Obteniendo el role de la base de datos local odgib01 ...
Base de datos con role PRIMARY.
####################################################################
Wed Mar 14 17:13:49 CET 2012 - Switchover de base de datos.
La base de datos ODGIB01 pasara a ser STANDBY
La base de datos ODGIB02 pasara a ser PRIMARY
Switchover to ODGIB02 ...
DGMGRL for Linux: Version 11.2.0.2.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> Connected.
DGMGRL> Performing switchover NOW, please wait...
New primary database "ODGIB02" is opening...
Operation requires shutdown of instance "odgib01" on database "ODGIB01"
Shutting down instance "odgib01"...
ORACLE instance shut down.
Operation requires startup of instance "odgib01" on database "ODGIB01"
Starting instance "odgib01"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "ODGIB02"
DGMGRL>
Configuration - odgib.eu.customer.net
Protection Mode: MaxAvailability
Databases:
ODGIB02 - Primary database
ODGIB01 - 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
DGMGRL> DGMGRL>
Wed Mar 14 17:13:49 CET 2012 - Switchover a ODGIB02 correcto.
####################################################################
Data Guard Check with DG Broker, The switchover has been succeeded.
Primary database is ODGIB02
***************************************************
DGMGRL> show configuration;
Configuration - odgib.eu.customer.net
Protection Mode: MaxAvailability
Databases:
ODGIB02 - Primary database
ODGIB01 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
We Perform a Failover to ODGIB01
***********************************************************
DGMGRL> failover to 'ODGIB01';
Performing failover NOW, please wait...
Failover succeeded, new primary is "ODGIB01"
Once the failoves has done.If we check the infrastructure status, ODGIB02 has been disabled due the failover.
***********************************************************
DGMGRL> show configuration;
Configuration - odgib.eu.customer.net
Protection Mode: MaxAvailability
Databases:
ODGIB01 - Primary database
Warning: ORA-16629: database reports a different protection level from the protection mode
ODGIB02 - Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: DISABLED
Configuration Status:
WARNING
As we have Flashback database configured, we can reinstate the Data Guard without restoring a backup.
************************************************************
DGMGRL> reinstate database 'ODGIB02';
Reinstating database "ODGIB02", please wait...
Operation requires shutdown of instance "odgib02" on database "ODGIB02"
Shutting down instance "odgib02"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "odgib02" on database "ODGIB02"
Starting instance "odgib02"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "ODGIB02" ...
Operation requires shutdown of instance "odgib02" on database "ODGIB02"
Shutting down instance "odgib02"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "odgib02" on database "ODGIB02"
Starting instance "odgib02"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "ODGIB02" ...
Reinstatement of database "ODGIB02" succeeded
After reinstate finishes, we just check the Data Guard status with the broker
******************************************
DGMGRL> show configuration;
Configuration - odgib.eu.customer.net
Protection Mode: MaxAvailability
Databases:
ODGIB01 - Primary database
ODGIB02 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
***************************************************
DGMGRL> show configuration;
Configuration - odgib.eu.customer.net
Protection Mode: MaxAvailability
Databases:
ODGIB02 - Primary database
ODGIB01 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
We Perform a Failover to ODGIB01
***********************************************************
DGMGRL> failover to 'ODGIB01';
Performing failover NOW, please wait...
Failover succeeded, new primary is "ODGIB01"
Once the failoves has done.If we check the infrastructure status, ODGIB02 has been disabled due the failover.
***********************************************************
DGMGRL> show configuration;
Configuration - odgib.eu.customer.net
Protection Mode: MaxAvailability
Databases:
ODGIB01 - Primary database
Warning: ORA-16629: database reports a different protection level from the protection mode
ODGIB02 - Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: DISABLED
Configuration Status:
WARNING
As we have Flashback database configured, we can reinstate the Data Guard without restoring a backup.
************************************************************
DGMGRL> reinstate database 'ODGIB02';
Reinstating database "ODGIB02", please wait...
Operation requires shutdown of instance "odgib02" on database "ODGIB02"
Shutting down instance "odgib02"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "odgib02" on database "ODGIB02"
Starting instance "odgib02"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "ODGIB02" ...
Operation requires shutdown of instance "odgib02" on database "ODGIB02"
Shutting down instance "odgib02"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "odgib02" on database "ODGIB02"
Starting instance "odgib02"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "ODGIB02" ...
Reinstatement of database "ODGIB02" succeeded
After reinstate finishes, we just check the Data Guard status with the broker
******************************************
DGMGRL> show configuration;
Configuration - odgib.eu.customer.net
Protection Mode: MaxAvailability
Databases:
ODGIB01 - Primary database
ODGIB02 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
Data Guard has been successfully reinstated using few steps. Easy isn't it?
No comments:
Post a Comment