ADG swicthover操作（主备切换）

ADG switch over（主备切换）

1：准备工作

1.1检查ADG状态是否正常

Standby

Sql>select process,status fromv$managed_standby;

 

Note：RFS为idle。MRP0为APPLYING_LOG。

 

Primary

Sql>SELECT RECOVERY_MODE FROMV$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;

 

Note：RECOVERY_MODE为MANAGED REAL TIME APPLY 

 

1.2 检查是否有gap

Sql>select * from v$archive_gap

Note：如果存在gap，可以从主库复制传输缺失的归档文件到备库，并注册

 

1.3停掉job（primary）

主库停止运行的JOB，并设置参数：

Sql>Alter system set job_queue_processing=0 scope=both;

Sql>Alter system set AQ_TM_PROCESSES=0 scope=both;

Note：切换完成在开启

 

1.4：停掉业务（local=no）

Note：中断外部业务连接数据库的进程。防止写入数据。如果开启了dbconsole，停掉dbconsole

 

 

2：primary

SQL> select DATABASE_ROLE from v$database;

 

DATABASE_ROLE

----------------

PRIMARY

 

SQL> select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;

 

OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS

---------- -------------------- -------------------- ------------------

READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO STANDBY

 

Note：

如果switchover_status为TO_STANDBY说明可以直接转换

SQL>aalter database commit to switchover to physical standby;

如果switchover_status为SESSIONS ACTIVE则关闭会话

SQL>alter database commit to switchover to physical standby with session shutdown

 

3：standby

 

SQL> select DATABASE_ROLE from v$database;

 

DATABASE_ROLE

----------------

PHYSICAL STANDBY

 

SQL> select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;

 

OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS

---------- -------------------- -------------------- ------------------

MOUNTED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE NOT ALLOWED

 

Note：not allowed表示当前备库为不可以做switch的操作

 

4：primary切换成standby

SQL>  alter database commit to switchover to physical standby ;

 

Database altered.

 

SQL> shutdown immediate;

ORA-01012: not logged on

 

5：查询standby的状态

SQL> select DATABASE_ROLE from v$database;

 

DATABASE_ROLE

----------------

PHYSICAL STANDBY

 

SQL> select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;

 

OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS

---------- -------------------- -------------------- ------------------

MOUNTED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO PRIMARY

Note：switch over从not allow切换成TO PRIMARY

 

6：standby切换成primary

SQL> alter database commit to switchover to primary;

 

Database altered.

 

7：重启新的primary

SQL> shutdown immediate;                              

ORA-01109: database not open

 

 

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area  784998400 bytes

Fixed Size                  2257352 bytes

Variable Size             515903032 bytes

Database Buffers          264241152 bytes

Redo Buffers                2596864 bytes

Database mounted.

Database opened.

 

 

 

 

SQL>  select DATABASE_ROLE from v$database;

 

DATABASE_ROLE

----------------

PRIMARY

 

8：重启新的standby

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area  784998400 bytes

Fixed Size                  2257352 bytes

Variable Size             515903032 bytes

Database Buffers          264241152 bytes

Redo Buffers                2596864 bytes

Database mounted.

SQL> alter database open;

 

Database altered.

 

SQL> alter database recover managed standby database disconnect from session;

 

Database altered.

 

SQL> select DATABASE_ROLE from v$database;                                                                  

 

DATABASE_ROLE

----------------

PHYSICAL STANDBY