Performing the switchover in 11gr2 datagurad steps
1) There are few steps we need to check before performing the switchover in dataguard environment
a)If we are using the OEM Enable a blackout for primary database in enterprise manager.
b)check the crontab jobs for Primary database need to disabled.
c)Check for executing backups/exports and cancel.
d)Identify the log gap if gap present resolve the gap
e)From Primary and Standby database check the list of last log should be applied on both database
2)Verify that it is possible to perform a switchover operation On the primary database
On the primary database the switchover_status column of v$database check which is possible to perform a switchover operation
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
------------------
TO STANDBY
-In order to perform a switchover all sessions to the database need to be disconnected. This process has been automated with the “with session shutdown” clause that has been added to the alter database commit to switchover command.
SWITCHOVER_STATUS
-------------------------------
SESSIONS ACTIVE
-Then you should either disconnect all sessions manually (or) when performing step 3 you should append the “with session shutdown” clause.
For example:
SQL> alter database commit to switchover to standby with session shutdown;
(Note: that the clause "with session shutdown" also works with the switchover to primary command
Ex: alter database commit to switchover to primary with session shutdown;)
SWITCHOVER_STATUS
-----------------------------
NOT ALLOWED
-Either this is a standby database and the primary database has not been switched first, or this is a primary database and there are no standby databases.
SWITCHOVER_STATUS
-----------------------------
SESSIONS ACTIVE -
-Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted.
SWITCHOVER_STATUS
-----------------------------
SWITCHOVER PENDING -
-This is a standby database and the primary database switchover request has been received but not processed.
SWITCHOVER_STATUS
-----------------------------
SWITCHOVER LATENT -
-The switchover was in pending mode, but did not complete and went back to the primary database.
SWITCHOVER_STATUS
-----------------------------
TO PRIMARY
- This is a standby database, with no active sessions, that is allowed to switch over to a primary database.
SWITCHOVER_STATUS
-----------------------------
TO STANDBY
- This is a primary database, with no active sessions, that is allowed to switch over to a standby database.
SWITCHOVER_STATUS
-----------------------------
RECOVERY NEEDED
- This is a standby database that has not received the switchover request.
During normal operations it is acceptable to see the following values for
SWITCHOVER_STATUS
-----------------------------
SESSIONS ACTIVE (or) TO STANDBY. ( on the primary database)
During normal operations it is acceptable to see these values
SWITCHOVER_STATUS
-----------------------------
NOT ALLOWED (or) SESSIONS ACTIVE. (on the standby database)
-During normal operations it is acceptable to see these values
3. Convert the primary database to the new standby:
SQL> alter database commit to switchover to physical standby ;
Database altered.
4. Shutdown the former primary and mount as a standby database:
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 818401280 bytes
Fixed Size 2217792 bytes
Variable Size 528484544 bytes
Database Buffers 285212672 bytes
Redo Buffers 2486272 bytes
SQL> alter database mount standby database;
Database altered.
5. Defer the remote archive destination on the old primary:
SQL> alter system set log_archive_dest_state_3=defer;
6. Verify that the physical standby can be converted to the new primary:
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
------------------
SWITCHOVER PENDING
(Note that if the status returns SESSIONS ACTIVE then you should append the with session shutdown clause to the command in step 7.
7. Convert the physical standby to the new primary:
SQL> alter database commit to switchover to primary;
Database altered.
8. Shutdown and startup the new primary:
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 818401280 bytes
Fixed Size 2217792 bytes
Variable Size 482347200 bytes
Database Buffers 331350016 bytes
Redo Buffers 2486272 bytes
Database mounted.
Database opened.
9. Enable remote archiving on the new primary to the new standby:
SQL> alter system set log_archive_dest_state_2=enable;
10. Start managed recover on the new standby database:
SQL> recover managed standby database disconnect;
Media recovery complete.
11) There are few steps we need to check after performing the switchover in dataguard environment
a)If we are using the OEM check the status of primary database in enterprise manager.
b)check the crontab jobs for Primary database need to enabled
c)Check for executing backups/exports and start as per schedule
d)Identify the log gap if gap present resolve the gap
e)From Primary and Standby database check the list of last log should be applied on both database
Perform Failover Operation to a Physical Standby
Use the following steps to perform a failover to a physical standby database.
Identify and resolve any archive log gaps that may exist on the target standby database.
Query the view V$ARCHIVE_GAP on the target standby database to determine if there are gaps in the archive sequence. This view contains the sequence numbers of any archived redo logs that are known to be missing for each thread. The data returned reflects the lowest and highest known gap in the archive sequence.
SQL> select * from v$archive_gap;
no rows selected
If the above query against V$ARCHIVE_GAP returns no rows, there are no known archive log gaps.
If the above query against V$ARCHIVE_GAP returns a record, it will display the sequence numbers of the archived redo logs known to be missing from the standby database as illustrated in the following example:
SQL> select * from v$archive_gap;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 854 859
From the output above, the physical standby database is currently missing archived redo logs from sequence 854 to sequence 859 for thread 1. If possible locate and copy all of the identified missing archived redo logs to the target standby database from either the primary database (if available) or from other standbys. If working in a RAC environment, this would need to include missing archived redo logs from all threads. Once copied over, the missing archived redo logs will need to be registered with the target standby database:
SQL> alter database register logfile '/u03/flash_recovery_area/PRODDB/archive/2010_12_16/o1_mf_1_854_6jmc5ngd_.arc';
Database altered.
SQL> alter database register logfile '/u03/flash_recovery_area/PRODDB/archive/2010_12_16/o1_mf_1_855_6jmc5pht_.arc';
Database altered.
SQL> alter database register logfile '/u03/flash_recovery_area/PRODDB/archive/2010_12_16/o1_mf_1_856_6jmc5t4t_.arc';
Database altered.
SQL> alter database register logfile '/u03/flash_recovery_area/PRODDB/archive/2010_12_16/o1_mf_1_857_6jmc6jq9_.arc';
Database altered.
SQL> alter database register logfile '/u03/flash_recovery_area/PRODDB/archive/2010_12_16/o1_mf_1_858_6jmc7z5o_.arc';
Database altered.
SQL> alter database register logfile '/u03/flash_recovery_area/PRODDB/archive/2010_12_16/o1_mf_1_859_6jmcb9m0_.arc';
Database altered.
Copy and register any other missing archived redo logs. Query the V$ARCHIVED_LOG view on all other available standby databases in the configuration (if any others exist) to obtain the highest log sequence number for each thread.
SQL> select unique thread# as "Thread", max(sequence#)
2 over (partition by thread#) as "Last" from v$archived_log;
Thread Last
---------- ----------
1 900
If you find any archived redo logs that contain sequence numbers higher than the highest sequence number available on the target standby database, copy and register them to the target standby database. If the standby database is in managed recovery mode (and why wouldn't it be), the newly registered archived redo logs will be automatically applied to the standby. If the standby database is not in managed recovery mode, then manually recover the newly registered archived redo logs. This must be done for all threads if you are in a RAC environment.
SQL> alter database register logfile '';
About Partial Archived Redo Logs
It is possible to copy over and register what is known as a partial archived redo log file. A partial archived redo log file contains all of the primary database redo data received by the standby database when the primary database fails, but the archived redo log is not automatically registered in the standby database.
When you register a partial archived redo log, it prevents the recovery of the standby redo logs (if they exist). Therefore, whether or not you have registered a partial archived redo log determines which failover command will be necessary to run (next step).
You will know if you registered a partial archived redo log if you get receive the following message when attempting to register the archived redo log:
Register archivelog 'filespec1' was created due to a network disconnect;
archivelog contents are valid but missing subsequent data
Perform terminal recovery on the target standby by issuing managed recovery mode with the FINISH keyword. If the physical standby database is configured with active standby redo logs AND you have not registered any partial archived redo log files, issue the following command to initiate the failover operation:
SQL> alter database recover managed standby database finish;
Database altered.
If the standby database was not configured with standby redo logs, or they are not active, you must enter the following command instead:
SQL> alter database recover managed standby database finish skip standby logfile;
Database altered.
The above SQL statement performs incomplete recovery until the last SCN included in the latest archived redo log available at the physical standby database.
Once the terminal recovery command completes, convert the physical standby into a primary database using the following command:
SQL> alter database commit to switchover to primary;
Database altered.
After the "COMMIT TO SWITCHOVER" command completes, you can no longer use this database as a standby database. Also, subsequent redo logs from the original primary database cannot be applied.
The above SQL statement will only succeed if the correct "ALTER DATABASE RECOVER MANAGED STANDBY DATABASE ... FINISH;" statement was issued in the previous step. If you forgot the "... SKIP STANDBY LOGFILE" clause although you have no standby redo log files, the "COMMIT TO SWITCHOVER" will fail with the error that more media recovery is required.
If the "COMMIT TO SWITCHOVER" fails for any reason, you have to use the "ACTIVATE STANDBY DATABASE" SQL statement which forces the failover operation:
SQL> alter database activate standby database;
To complete the failover operation, restart the new primary database in read/write mode using the appropriate initialization parameter file (or SPFILE) for the primary role:
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 1241513984 bytes
Fixed Size 1273420 bytes
Variable Size 318767540 bytes
Database Buffers 905969664 bytes
Redo Buffers 15503360 bytes
Database mounted.
Database opened.
Perform a full backup of the new primary database!
Keep in mind that if this was the only standby database in the Data Guard configuration, then the database environment will be running unprotected until a new standby database can be setup and synchronized with the current primary. If Flashback Database was enabled on the original primary database before the failover and the original primary database is still available after the original primary machine comes back online,
1) There are few steps we need to check before performing the switchover in dataguard environment
a)If we are using the OEM Enable a blackout for primary database in enterprise manager.
b)check the crontab jobs for Primary database need to disabled.
c)Check for executing backups/exports and cancel.
d)Identify the log gap if gap present resolve the gap
e)From Primary and Standby database check the list of last log should be applied on both database
2)Verify that it is possible to perform a switchover operation On the primary database
On the primary database the switchover_status column of v$database check which is possible to perform a switchover operation
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
------------------
TO STANDBY
-In order to perform a switchover all sessions to the database need to be disconnected. This process has been automated with the “with session shutdown” clause that has been added to the alter database commit to switchover command.
SWITCHOVER_STATUS
-------------------------------
SESSIONS ACTIVE
-Then you should either disconnect all sessions manually (or) when performing step 3 you should append the “with session shutdown” clause.
For example:
SQL> alter database commit to switchover to standby with session shutdown;
(Note: that the clause "with session shutdown" also works with the switchover to primary command
Ex: alter database commit to switchover to primary with session shutdown;)
SWITCHOVER_STATUS
-----------------------------
NOT ALLOWED
-Either this is a standby database and the primary database has not been switched first, or this is a primary database and there are no standby databases.
SWITCHOVER_STATUS
-----------------------------
SESSIONS ACTIVE -
-Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted.
SWITCHOVER_STATUS
-----------------------------
SWITCHOVER PENDING -
-This is a standby database and the primary database switchover request has been received but not processed.
SWITCHOVER_STATUS
-----------------------------
SWITCHOVER LATENT -
-The switchover was in pending mode, but did not complete and went back to the primary database.
SWITCHOVER_STATUS
-----------------------------
TO PRIMARY
- This is a standby database, with no active sessions, that is allowed to switch over to a primary database.
SWITCHOVER_STATUS
-----------------------------
TO STANDBY
- This is a primary database, with no active sessions, that is allowed to switch over to a standby database.
SWITCHOVER_STATUS
-----------------------------
RECOVERY NEEDED
- This is a standby database that has not received the switchover request.
During normal operations it is acceptable to see the following values for
SWITCHOVER_STATUS
-----------------------------
SESSIONS ACTIVE (or) TO STANDBY. ( on the primary database)
During normal operations it is acceptable to see these values
SWITCHOVER_STATUS
-----------------------------
NOT ALLOWED (or) SESSIONS ACTIVE. (on the standby database)
-During normal operations it is acceptable to see these values
3. Convert the primary database to the new standby:
SQL> alter database commit to switchover to physical standby ;
Database altered.
4. Shutdown the former primary and mount as a standby database:
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 818401280 bytes
Fixed Size 2217792 bytes
Variable Size 528484544 bytes
Database Buffers 285212672 bytes
Redo Buffers 2486272 bytes
SQL> alter database mount standby database;
Database altered.
5. Defer the remote archive destination on the old primary:
SQL> alter system set log_archive_dest_state_3=defer;
6. Verify that the physical standby can be converted to the new primary:
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
------------------
SWITCHOVER PENDING
(Note that if the status returns SESSIONS ACTIVE then you should append the with session shutdown clause to the command in step 7.
7. Convert the physical standby to the new primary:
SQL> alter database commit to switchover to primary;
Database altered.
8. Shutdown and startup the new primary:
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 818401280 bytes
Fixed Size 2217792 bytes
Variable Size 482347200 bytes
Database Buffers 331350016 bytes
Redo Buffers 2486272 bytes
Database mounted.
Database opened.
9. Enable remote archiving on the new primary to the new standby:
SQL> alter system set log_archive_dest_state_2=enable;
10. Start managed recover on the new standby database:
SQL> recover managed standby database disconnect;
Media recovery complete.
11) There are few steps we need to check after performing the switchover in dataguard environment
a)If we are using the OEM check the status of primary database in enterprise manager.
b)check the crontab jobs for Primary database need to enabled
c)Check for executing backups/exports and start as per schedule
d)Identify the log gap if gap present resolve the gap
e)From Primary and Standby database check the list of last log should be applied on both database
Perform Failover Operation to a Physical Standby
Use the following steps to perform a failover to a physical standby database.
Identify and resolve any archive log gaps that may exist on the target standby database.
Query the view V$ARCHIVE_GAP on the target standby database to determine if there are gaps in the archive sequence. This view contains the sequence numbers of any archived redo logs that are known to be missing for each thread. The data returned reflects the lowest and highest known gap in the archive sequence.
SQL> select * from v$archive_gap;
no rows selected
If the above query against V$ARCHIVE_GAP returns no rows, there are no known archive log gaps.
If the above query against V$ARCHIVE_GAP returns a record, it will display the sequence numbers of the archived redo logs known to be missing from the standby database as illustrated in the following example:
SQL> select * from v$archive_gap;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 854 859
From the output above, the physical standby database is currently missing archived redo logs from sequence 854 to sequence 859 for thread 1. If possible locate and copy all of the identified missing archived redo logs to the target standby database from either the primary database (if available) or from other standbys. If working in a RAC environment, this would need to include missing archived redo logs from all threads. Once copied over, the missing archived redo logs will need to be registered with the target standby database:
SQL> alter database register logfile '/u03/flash_recovery_area/PRODDB/archive/2010_12_16/o1_mf_1_854_6jmc5ngd_.arc';
Database altered.
SQL> alter database register logfile '/u03/flash_recovery_area/PRODDB/archive/2010_12_16/o1_mf_1_855_6jmc5pht_.arc';
Database altered.
SQL> alter database register logfile '/u03/flash_recovery_area/PRODDB/archive/2010_12_16/o1_mf_1_856_6jmc5t4t_.arc';
Database altered.
SQL> alter database register logfile '/u03/flash_recovery_area/PRODDB/archive/2010_12_16/o1_mf_1_857_6jmc6jq9_.arc';
Database altered.
SQL> alter database register logfile '/u03/flash_recovery_area/PRODDB/archive/2010_12_16/o1_mf_1_858_6jmc7z5o_.arc';
Database altered.
SQL> alter database register logfile '/u03/flash_recovery_area/PRODDB/archive/2010_12_16/o1_mf_1_859_6jmcb9m0_.arc';
Database altered.
Copy and register any other missing archived redo logs. Query the V$ARCHIVED_LOG view on all other available standby databases in the configuration (if any others exist) to obtain the highest log sequence number for each thread.
SQL> select unique thread# as "Thread", max(sequence#)
2 over (partition by thread#) as "Last" from v$archived_log;
Thread Last
---------- ----------
1 900
If you find any archived redo logs that contain sequence numbers higher than the highest sequence number available on the target standby database, copy and register them to the target standby database. If the standby database is in managed recovery mode (and why wouldn't it be), the newly registered archived redo logs will be automatically applied to the standby. If the standby database is not in managed recovery mode, then manually recover the newly registered archived redo logs. This must be done for all threads if you are in a RAC environment.
SQL> alter database register logfile '
About Partial Archived Redo Logs
It is possible to copy over and register what is known as a partial archived redo log file. A partial archived redo log file contains all of the primary database redo data received by the standby database when the primary database fails, but the archived redo log is not automatically registered in the standby database.
When you register a partial archived redo log, it prevents the recovery of the standby redo logs (if they exist). Therefore, whether or not you have registered a partial archived redo log determines which failover command will be necessary to run (next step).
You will know if you registered a partial archived redo log if you get receive the following message when attempting to register the archived redo log:
Register archivelog 'filespec1' was created due to a network disconnect;
archivelog contents are valid but missing subsequent data
Perform terminal recovery on the target standby by issuing managed recovery mode with the FINISH keyword. If the physical standby database is configured with active standby redo logs AND you have not registered any partial archived redo log files, issue the following command to initiate the failover operation:
SQL> alter database recover managed standby database finish;
Database altered.
If the standby database was not configured with standby redo logs, or they are not active, you must enter the following command instead:
SQL> alter database recover managed standby database finish skip standby logfile;
Database altered.
The above SQL statement performs incomplete recovery until the last SCN included in the latest archived redo log available at the physical standby database.
Once the terminal recovery command completes, convert the physical standby into a primary database using the following command:
SQL> alter database commit to switchover to primary;
Database altered.
After the "COMMIT TO SWITCHOVER" command completes, you can no longer use this database as a standby database. Also, subsequent redo logs from the original primary database cannot be applied.
The above SQL statement will only succeed if the correct "ALTER DATABASE RECOVER MANAGED STANDBY DATABASE ... FINISH;" statement was issued in the previous step. If you forgot the "... SKIP STANDBY LOGFILE" clause although you have no standby redo log files, the "COMMIT TO SWITCHOVER" will fail with the error that more media recovery is required.
If the "COMMIT TO SWITCHOVER" fails for any reason, you have to use the "ACTIVATE STANDBY DATABASE" SQL statement which forces the failover operation:
SQL> alter database activate standby database;
To complete the failover operation, restart the new primary database in read/write mode using the appropriate initialization parameter file (or SPFILE) for the primary role:
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 1241513984 bytes
Fixed Size 1273420 bytes
Variable Size 318767540 bytes
Database Buffers 905969664 bytes
Redo Buffers 15503360 bytes
Database mounted.
Database opened.
Perform a full backup of the new primary database!
Keep in mind that if this was the only standby database in the Data Guard configuration, then the database environment will be running unprotected until a new standby database can be setup and synchronized with the current primary. If Flashback Database was enabled on the original primary database before the failover and the original primary database is still available after the original primary machine comes back online,
