ORACLE11gR2 DG角色切换(physical standby)
这里的角色切换是指正常的切换而非故障转移。
角色切换时计划内的事件,并且不会丢失数据,因为允许切换开始前,主数据库必须结束在生产数据上的所有重做数据生成
使用Broker或grid control时,切换后都将自动配置所有参数,以便新的主库将redo数据发送给所有备库。如果使用sql*plus来处理,则需要配置一些参数。
在主库上执行切换SQL命令时,重做数据生成被终止,所有与DML相关的游标不再有效,用户无法执行事务(logical standby)或终止事务(physical standby),
并归档所有线程的当前日志。然后,在每个线程下一个序列的头部放置一个称为EOR(重做结束)的特殊切换标记,再次归档online redo logfile,并将最后序列
发送给备用数据库。
如果正在执行physical standby切换,主数据库关闭,在不允许主数据库为每个线程提高序列号的情况,完成最后日志切换。
在将EOR重做发送给备用数据库后,原来的主数据库看最终成为备用数据库,其控制文件备份到trac文件中,并转换为正确的备用控制文件类型
切换角色后,中间层的连接需要重新定义
一、检查
1、配置完整性检查及其是否有相关作业跑在后台
2、验证备库已接收全部重做数据
–主库
SQL> select db_unique_name,protection_mode,synchronization_status,synchronized from v$archive_dest_status where db_unique_name<>‘NONE’;
DB_UNIQUE_NAME PROTECTION_MODE SYNCHRONIZATION_STATUS SYN
—————————— ——————– ———————- —
s_dgtrsen MAXIMUM PERFORMANCE CHECK CONFIGURATION NO==>备库s_dgtrsen
SQL> select thread#,sequence#,status from v$log;
THREAD# SEQUENCE# STATUS
———- ———- —————-
1 52 INACTIVE
1 53 INACTIVE
1 54 CURRENT
SQL> set linesize 120;
SQL> select NAME,DB_UNIQUE_NAME,SWITCHOVER_STATUS,OPEN_MODE,PROTECTION_MODE from v$database;
NAME DB_UNIQUE_NAME SWITCHOVER_STATUS OPEN_MODE PROTECTION_MODE
——— —————————— ——————– ——————– ——————–
DGTRSEN p_dgtrsen TO STANDBY READ WRITE MAXIMUM PERFORMANCE
–备库
SQL> select client_process,process,sequence#,status from v$managed_standby;
CLIENT_P PROCESS SEQUENCE# STATUS
——– ——— ———- ————
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 52 CLOSING
ARCH ARCH 51 CLOSING
N/A MRP0 53 APPLYING_LOG==>状态为WAIT_FOR_GAP,消除GAP做切换;状态WAIT_FOR_LOG,调整状态切换
ARCH RFS 0 IDLE
UNKNOWN RFS 0 IDLE
LGWR RFS 53 IDLE==>表明主库当前发送的log seq为53
UNKNOWN RFS 0 IDLE
SQL> set linesize 120;
SQL> select NAME,DB_UNIQUE_NAME,SWITCHOVER_STATUS,OPEN_MODE,PROTECTION_MODE from v$database;
NAME DB_UNIQUE_NAME SWITCHOVER_STATUS OPEN_MODE PROTECTION_MODE
——— —————————— ——————– ——————– ——————–
DGTRSEN s_dgtrsen NOT ALLOWED READ ONLY WITH APPLY MAXIMUM PERFORMANCE
从上述的信息可以查出其备库的日志apply与主库的gap为0,可以做主备切换
二、切换
–主库上操作
SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered.
SQL> set linesize 120;
SQL> select NAME,DB_UNIQUE_NAME,SWITCHOVER_STATUS,OPEN_MODE,PROTECTION_MODE from v$database;
NAME DB_UNIQUE_NAME SWITCHOVER_STATUS OPEN_MODE PROTECTION_MODE
——— —————————— ——————– ——————– ——————–
DGTRSEN p_dgtrsen RECOVERY NEEDED READ WRITE MAXIMUM PERFORMANCE
/*此时在备库上做完相关操作后回来再执行启动且apply日志操作*/
SQL> shutdown immediate;
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL> exit
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 682135552 bytes
Fixed Size 1347120 bytes
Variable Size 415236560 bytes
Database Buffers 260046848 bytes
Redo Buffers 5505024 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> set linsize 120;
SP2-0158: unknown SET option “linsize”
SQL> set linesize 120;
SQL> select NAME,DB_UNIQUE_NAME,SWITCHOVER_STATUS,OPEN_MODE,PROTECTION_MODE from v$database;
NAME DB_UNIQUE_NAME SWITCHOVER_STATUS OPEN_MODE PROTECTION_MODE
——— —————————— ——————– ——————– ——————–
DGTRSEN p_dgtrsen NOT ALLOWED READ ONLY WITH APPLY MAXIMUM PERFORMANCE
SQL> select client_process,process,sequence#,status from v$managed_standby;
CLIENT_P PROCESS SEQUENCE# STATUS
——– ——— ———- ————
ARCH ARCH 86 CLOSING
ARCH ARCH 0 CONNECTED
ARCH ARCH 85 CLOSING
ARCH ARCH 0 CONNECTED
UNKNOWN RFS 0 IDLE
LGWR RFS 87 IDLE
ARCH RFS 0 IDLE
N/A MRP0 87 APPLYING_LOG
–备库操作
SQL> set linesize 120;
SQL> select NAME,DB_UNIQUE_NAME,SWITCHOVER_STATUS,OPEN_MODE,PROTECTION_MODE from v$database;
NAME DB_UNIQUE_NAME SWITCHOVER_STATUS OPEN_MODE PROTECTION_MODE
——— —————————— ——————– ——————– ——————–
DGTRSEN s_dgtrsen NOT ALLOWED READ ONLY WITH APPLY MAXIMUM PERFORMANCE
SQL> /–在主库运行完切换命令后open数据库前,旧备库查询到的状态
NAME DB_UNIQUE_NAME SWITCHOVER_STATUS OPEN_MODE PROTECTION_MODE
——— —————————— ——————– ——————– ——————–
DGTRSEN s_dgtrsen TO PRIMARY READ ONLY WITH APPLY MAXIMUM PERFORMANCE
SQL> alter database commit to switchover to primary with session shutdown;
Database altered.
SQL> set linesize 120;
SQL> select NAME,DB_UNIQUE_NAME,SWITCHOVER_STATUS,OPEN_MODE,PROTECTION_MODE from v$database;
NAME DB_UNIQUE_NAME SWITCHOVER_STATUS OPEN_MODE PROTECTION_MODE
——— —————————— ——————– ——————– ——————–
DGTRSEN s_dgtrsen NOT ALLOWED MOUNTED MAXIMUM PERFORMANCE
SQL> alter database open;
Database altered.
SQL> select NAME,DB_UNIQUE_NAME,SWITCHOVER_STATUS,OPEN_MODE,PROTECTION_MODE from v$database;
NAME DB_UNIQUE_NAME SWITCHOVER_STATUS OPEN_MODE PROTECTION_MODE
——— —————————— ——————– ——————– ——————–
DGTRSEN s_dgtrsen TO STANDBY READ WRITE MAXIMUM PERFORMANCE