oracle 11g physical standby switchover

本DG环境采用LGWR SYNC传输模式，使用ACTIVE DATAGUARD应用日志，即备库在OPEN状态下应用日志

1、查询主备库switchover_status

主库

SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
RESOLVABLE GAP

备库

SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE

2、在主库上切换日志，解决日志断档的问题

SQL> alter system switch logfile;
System altered.

SQL> /
System altered.

SQL> /
System altered.

SQL> /

System altered.

3、再查询主备库switchover_status

主库

SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY

备库

SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------

NOT ALLOW

4、将主库切换到备库

SQL>  alter database commit to switchover to physical standby with session shutdown;
Database altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.

SQL> startup 
ORACLE instance started.
Total System Global Area  217157632 bytes
Fixed Size                  2211928 bytes
Variable Size             159387560 bytes
Database Buffers           50331648 bytes
Redo Buffers                5226496 bytes
Database mounted.

5、查询备库状态

SQL> select open_mode,db_unique_name,switchover_status,database_role from v$database;

OPEN_MODE           DB_UNIQUE_NAME      SWITCHOVER_STATUS      DATABASE_ROLE
--------------------          ------------------------------       ------------------------------    ------------------------------
READ ONLY WITH APPLY         prim                      NOT ALLOWED               PHYSICAL STANDBY

6、将备库切换到主库

SQL> alter database commit to switchover to primary with session shutdown;
Database altered.

SQL> alter database open;
Database altered.

7、查询主库状态

SQL> select open_mode,db_unique_name,switchover_status,database_role from v$database;

OPEN_MODE          DB_UNIQUE_NAME        SWITCHOVER_STATUS       DATABASE_ROLE
--------------------      ------------------------------       ------------------------------            ------------------------------
READ WRITE              stby                                                   TO STANDBY                                  PRIMARY

8、在备库应用redo日志

SQL> alter database recover managed standby database using current logfile disconnect from session;
alter database recover managed standby database using current logfile disconnect from session
*
ERROR at line 1:
ORA-38500: USING CURRENT LOGFILE option not available without standby redo logs
原因在于之前主库没有创建standby reo log，需要创建之后再应用

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.
Total System Global Area  217157632 bytes
Fixed Size                  2211928 bytes
Variable Size             159387560 bytes
Database Buffers           50331648 bytes
Redo Buffers                5226496 bytes
Database mounted.

SQL> alter database add standby logfile group 7 '/oradata/standby_redo04.log' size 50m;

Database altered.

SQL> alter database add standby logfile group 4 '/oradata/standby_redo01.log' size 50m;
Database altered.

SQL> alter database add standby logfile group 5 '/oradata/standby_redo02.log' size 50m;
Database altered.

SQL> alter database add standby logfile group 6 '/oradata/standby_redo03.log' size 50m;
Database altered.

SQL> alter database open;
Database altered.

重新应用日志

SQL> alter database recover managed standby database using current logfile disconnect from session; 
Database altered.

9、在主库切换日志

SQL> alter system switch logfile;
System altered.

SQL> /
System altered.

SQL> /
System altered.

SQL> /
System altered.

10、在主库上查询归档日志

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oradata/orcl
Oldest online log sequence     48
Next log sequence to archive   50
Current log sequence           50
SQL> 

11、在备库上查询归档日志

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle/oradata/orcl
Oldest online log sequence     48
Next log sequence to archive   0
Current log sequence           50

12、在备库上查询日志应用进程

SQL> select process,client_process,sequence#,status from v$managed_standby;

PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH      ARCH             49 CLOSING
ARCH      ARCH              0 CONNECTED
RFS       UNKNOWN           0 IDLE
RFS       LGWR             50 IDLE
MRP0      N/A              50 APPLYING_LOG