An example of solutions for log switch gap and resolvable gap in switchover master database

Switchover is implemented today and the environment is 11.2.0.3 + oel5.7. The status of the master and slave databases is normal at the beginning, and the direct switching conditions are met:
Master database:
SQL> select open_mode, database_role, switchover_status from V $ database;

Open_mode database_role switchover_status
--------------------------------------------------------
Read Write primary to standby

Slave database:
SQL> select open_mode, database_role, switchover_status from V $ database;

Open_mode database_role switchover_status
--------------------------------------------------------
Read Only with apply physical standby not allowed

The master database directly performs swichover:
SQL> alter database commit to switchover to physical standby;
Alter database commit to switchover to physical standby
*
Error at line 1:
ORA-01093: Alter database close only permitted with no sessions connected

SQL> select open_mode, database_role, switchover_status from V $ database;

Open_mode database_role switchover_status
--------------------------------------------------------
Read Write primary log switch gap

The following message is displayed:
SQL> startup force
Oracle instance started.

Total system global area 413372416 bytes
Fixed size 2228904 bytes
Variable Size 322964824 bytes
Database buffers 83886080 bytes
Redo buffers 4292608 bytes
Database mounted.
Database opened.
SQL> select open_mode, database_role, switchover_status from V $ database;

Open_mode database_role switchover_status
--------------------------------------------------------
Read Write primary resolvable gap

The status changes from log switch gap to resolvable gap. Literally, there is a gap between the master and slave databases, so the execution is:

SQL> alter system flush redo to ora11dg2;

SQL> select open_mode, database_role, switchover_status from V $ database;

Open_mode database_role switchover_status
--------------------------------------------------------
Read Write primary to standby

After the primary database redo is flushed to the standby database, the above State disappears and the status is returned to the previous to STANDBY state. You can perform switchover again.
If the status of the master database is not to standby, but the session is active, it must be added with Session shutdown. Otherwise, the switchover fails. other statuses cannot be switched directly. The status of the standby database is not allowed. After the master database is switched, it will become to primary. Therefore, the primary database usually performs role transition and then performs it in the standby database.

The following are some commands and instructions for eliminating the gap between the master and slave databases:

-- The master database transfers all the records not transmitted to the slave database. target_db_name uses db_unique_name.
Alter system flush redo to target_db_name;

-- Verify the slave Database
Select unique thread # as thread, max (sequence #) over (partition by thread #) as last from V $ archived_log;

-- If necessary, copy the archived logs to the slave database and register them.
Alter database register physical logfile 'filespec1 ';

-- Repeat the previous step to confirm that all archives have been completed.
Select thread #, low_sequence #, high_sequence # from V $ archive_gap;

-- View the target log transmission path status and gap status
Select status, gap_status from V $ archive_dest_status where dest_id = 2;

-- Stop the log application on the target slave Database
Alter database recover managed standby database cancel;

-- On the target slave Database
Alter database recover managed standby database finish;

-- If the log is determined to be lost, the activation method can be used, but there will be data loss.
-- Alter database activate physical standby database;

-- Verify the target slave Database
Select switchover_status from V $ database;

-- Start switching. If the status is "to primary", the with Session shutdown clause can be removed.
Alter database commit to switchover to primary with Session shutdown;

-- Open the new master database
Alter database open;

The following table describes the Switch Status of the active/standby database of the DG:

V $ Database
Not allowed-on a primary database, this status indicates that there are no valid and enabled standby databases. on a standby database, this status indicates that a switchover request has not been stored ed from the primary database.
 
Sessions active-The database has active sessions. on a physical standby database, the with Session shutdown SQL clause must be specified to perform a role transition while in this state. on a logical standby database, a role transition can be performed med while
In this state, but the role transition will not complete until all current transactions have committed.
 
Switchover pending-on a physical standby database, this status indicates that a switchover request has been received ed from the primary database and is being processed. A physical standby database cannot switch to the primary role while in this transient state.
 
Switchover latent-on a physical standby database, this status indicates that a switchover request was pending, but the original primary database has been switched back to the primary role.
 
To primary-The database is ready to switch to the primary role.
 
To standby-The database is ready to switch to either the physical or logical standby role.
 
To logical standby-The database has stored ed a data dictionary from a logical standby database and is ready to switch to the logical standby role.
 
Recovery needed-on a physical standby database, this status indicates that additional redo must be applied before the database can switch to the primary role.
 
Preparing switchover-on a primary database, this status indicates that a data dictionary is being stored ed from a logical standby database in preparation for switching to the logical standby role. on a logical standby database, this status indicates that
The data dictionary has been sent to the primary database and other standby databases.
 
Preparing dictionary-on a logical standby database, this status indicates that the data dictionary is being sent to the primary database and other standby databases in preparation for switching to the primary role.
 
Failed destination-on a primary database, this status indicates that one or more standby destinations are in an error state.
 
Resolvable gap-on a primary database, this status indicates that one or more standby databases have a redo gap that can be automatically resolved by fetching the missing redo from the primary database or from another standby database.
 
Unresolvable gap-on a primary database, this status indicates that one or more standby databases have a redo gap that cannot be automatically resolved by fetching the missing redo from the primary database or from another standby database.
 
Log switch gap-on a primary database, this status indicates that one or more standby databases are missing redo due to a recent log switch.

Switchover example:

Original master database:
SQL> alter database commit to switchover to physical standby;

Database altered.

SQL> select open_mode, database_role, switchover_status from V $ database;

Open_mode database_role switchover_status
--------------------------------------------------------
Read Write physical standby recovery needed

SQL> shutdown abort
Oracle instance shut down.
SQL> startup Mount
Oracle instance started.

Total system global area 413372416 bytes
Fixed size 2228904 bytes
Variable Size 331353432 bytes
Database buffers 75497472 bytes
Redo buffers 4292608 bytes
Database mounted.

Original standby database:
SQL> select open_mode, database_role, switchover_status from V $ database;

Open_mode database_role switchover_status
--------------------------------------------------------
Read Only with apply physical standby to primary

SQL> alter database commit to switchover to primary;

Database altered.

SQL> alter database open;

Database altered.

SQL> select open_mode, database_role, switchover_status from V $ database;

Open_mode database_role switchover_status
--------------------------------------------------------
Read Write primary to standby

Original master database (new slave database ):
SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.
SQL> select open_mode, database_role, switchover_status from V $ database;

Open_mode database_role switchover_status
--------------------------------------------------------
Mounted physical standby recovery needed

In this case, the switchover status of the newly converted slave database is recovery needed, which is normal. After the master database switches logs several times, it will become not allowed. In addition, this mode is used for redo apply, although it is 11 GB, it cannot be opened directly. to open it, you must stop redo apply first:

SQL> alter database open;
Alter database open
*
Error at line 1:
ORA-10456: cannot open standby database; media recovery session may be in progress

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

Because the 11g active DG function (lisence is required, otherwise it is used illegally), you can still redo apply even in the open state:

SQL> alter database recover managed standby database disconnect;

Database altered.

SQL> select open_mode, database_role, switchover_status from V $ database;

Open_mode database_role switchover_status
--------------------------------------------------------
Read Only with apply physical standby not allowed

So far, the entire switchover is finished, and switchover will not lose data (unlike failover). If failover can do without gap, it can also do not lose data, switchover switches normally (See Chapter b25608 and Chapter 2 of the official document ).

Bytes -----------------------------------------------------------------------------------------------------
By mongoon8219 chinaunix blog: http://blog.chinaunix.net/uid/24612962.html
For Original article reprinted, please specify the link. Thank you!

Http://blog.chinaunix.net/uid-24612962-id-3842449.html

Http://blog.csdn.net/aaron8219/article/details/10035547