Physical standby role transformation for Oracle 11g Data Guard

I. switchover operation of the physical standby library

The main content of this section is the switchover operation of the physical standby, which starts at the main library and ends at the target repository.

1. Confirm that the main library can be switched to standby role

Sql> SELECT switchover_status from V$database;

If the result shows to standby or sessions ACTIVE, the main library can switch to the standby role, otherwise the switchover will not work because the redo transport is not a configuration error or is not working properly.

2. Main Library Boot Switchover

Execute the following SQL statement to switch the main library to the standby role:

Sql> ALTER DATABASE COMMIT to switchover-physical STANDBY with SESSION SHUTDOWN;

Prior to switchover, the current control file is backed up to the current SQL execution session trace file, which allows the current control file to be rebuilt if necessary.

Note: If the query results in step 1th are to STANDBY, the WITH SESSION shutdown clause in the SQL statement can be omitted.

3. Close and mount the front main library

Sql> SHUTDOWN ABORT; Sql> STARTUP MOUNT;

At this time, the original repository has become a standby role.

Note: Oracle 11g (11.2.0.4) or newer version does not need to perform shutdown ABORT as soon as the database executes "ALTER database COMMIT to switchover to physical STANDBY WIT H SESSION SHUTDOWN This action is included by default.

4. Ensure that the switchover target can be switched to the primary role

Querying the Switchover_status value of the standby library via V$database:

Sql> SELECT switchover_status from V$database;

If the query results show to primary or sessions ACTIVE, the repository can switch to the primary role. Otherwise, verify that the Redo app is active, that the configuration of the redo transport is correct, and that it works correctly until the query results show to primary or sessions ACTIVE.

5. Switch target physical standby to primary role

Execute the following SQL on the target physical standby:

Sql> ALTER DATABASE COMMIT to switchover to PRIMARY with SESSION SHUTDOWN;

Note: If the query results in step 1th are to PRIMARY, the WITH SESSION shutdown clause in the SQL statement can be omitted.

6. Open the new Main library

sql> ALTER DATABASE OPEN;

7. Open the Redo application for the new physical standby library

sql> ALTER database RECOVER MANAGED STANDBY database USING current LOGFILE DISCONNECT from SESSION;

8. Restart the redo application if the Redo app is turned off in any other physical repository in the data guard configuration

sql> ALTER database RECOVER MANAGED STANDBY database USING current LOGFILE DISCONNECT from SESSION;

two. Failover operation of the physical standby library

The following steps are failover for the physical standby:

1. Flush all redo data from the master library to the target repository

If the main library can be mounted, it may be possible to flush all unsent archive logs and current redo from the main library to the repository, and if the operation succeeds, then even if the primary library is not running in 0 data loss protected mode, 0 data loss failover is possible.

Make sure that the Redo app for the target repository is on, then mount but do not open the main library (if the main library is not mounted, skip to step 2nd).

To execute SQL in the main library:

sql> ALTER SYSTEM FLUSH REDO to Target_db_name;

Target_db_name, specifies the db_unique_name of the repository, which is used to receive redo data refreshed by the main library. This SQL is used to refresh all unsent redo data from the main library to the standby and wait for the redo to be applied to the standby repository. If the step is complete, proceed directly to step 5th, or step 2nd if the step is executed incorrectly or because it cannot be stopped when it is finished.

2. Make sure that the repository has the latest archive log for each main library thread

Querying the target repository through the V$archived_log view queries the maximum log sequence for each redo thread:

Sql> SELECT UNIQUE thread# as THREAD, MAX (sequence#) over (PARTITION by thread#) as last from V$archived_log;

If there is no archive log for the maximum log sequence for each main library redo thread in the repository, copy and register them from the main library to the standby repository if possible:

sql> ALTER DATABASE REGISTER physical LOGFILE ' filespec1 ';

3. Identify and resolve any missing archive logs

Querying the target repository through the V$archive_gap view for log deletions:

Sql> SELECT thread#, low_sequence#, high_sequence# from V$archive_gap;

For the missing log, perform step 2nd until there is no log missing.

4. Close the Redo application

In the target repository execution:

sql> ALTER database RECOVER MANAGED STANDBY database CANCEL;

5. Complete all the Redo log applications received

In the target repository execution:

sql> ALTER database RECOVER MANAGED STANDBY database FINISH;

If the SQL executes correctly, enter the 7th step, and if the error is performed, the redo received is not applied, and the errors need to be resolved before moving on to the next step. Note that if the 3rd step redo missing is not resolved, you will receive a redo missing error warning, and if the error is not resolved, the failover can be completed by using the following SQL on the standby library:

Sql> ALTER Database ACTIVATE physical STANDBY database;

After the statement executes, enter the 8th step.

6. Confirm that the target repository can be converted to the primary role

Confirm by querying the Switchover_status value of the V$database view:

Sql> SELECT switchover_status from V$database;

If the query results show to primary or sessions ACTIVE, the repository can switch to the primary role. Otherwise, confirm that the Redo app is started until the query results show to primary or sessions ACTIVE.

7. Switch the physical standby to the primary role

Execute the following SQL on the target physical standby:

Sql> ALTER DATABASE COMMIT to switchover to PRIMARY with SESSION SHUTDOWN;

Note: If the query results in step 1th are to PRIMARY, the WITH SESSION shutdown clause in the SQL statement can be omitted.

8. Open the new Main library

sql> ALTER DATABASE OPEN;

9. Back up the new main library

10. Restart the Redo application if the Redo app is turned off in any other physical repository in the data guard configuration

sql> ALTER database RECOVER MANAGED STANDBY database USING current LOGFILE DISCONNECT from SESSION;

11. Selective Recovery Failure Main Library

After failover, the master library can be converted to a physical standby of the new primary library through the flashback or Rman tool, or it can be rebuilt as a physical standby through a backup of the new primary library. Once the primary repository is converted to the standby role, it is possible to restore its master identity through the switchover operation.

This article is about Oracle's Official document translation, and the shortcomings are welcome criticism !

Physical standby role transformation for Oracle 11g Data Guard