正确的开关机顺序是：
启动的时候，先备库的listener,再启动备库,再启动主库的listener,再启动主库。
关闭的时候，先关闭主库，再关闭备库。
--为主数据库或备用数据库添加/删除日志组
SQL> alter database add standby logfile group 5 '/oracle/oradata/orcl/standbyredo05.log' size 100M;
SQL> alter database drop standby logfile group 5;
--查询DataGuard当前处于哪种日志传输方式：
SQL> select process,client_process,sequence#,status from v$managed_standby;
PROCESS列显示进程信息
CLIENT_PROCESS列显示对应的主数据库中的进程
SEQUENCE#列显示归档redo的序列号
STATUS列显示的进程状态
--查询standby库中所有已被应用的归档文件信息(不论该归档文件是否还存在)
SQL> select first_time,first_change#,next_change#,sequence# from v$log_history;
--最后一个被应用的log
SQL> SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#;
--在primary server上查询有哪些日志没有被传输到Standby
SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL WHERE LOCAL.SEQUENCE# NOT IN
(SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND THREAD# = LOCAL.THREAD#);
--对于troubleshooting有用
SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;
m.监控恢复操作的进程
SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS;
--从正在恢复状态只读打开;
SQL> Alter DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> Alter DATABASE OPEN READ ONLY;
--切换回到恢复状态;
SQL> Alter DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
切换角色
注意：Swithover时只能先从Primary切到Standby，再从Standby切到Primary.
1、在主库端
select database_role,switchover_status from v$database;
如果是to standby 表可以正常切换.
直接执行 ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
否则执行: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
SQL> shutdown immediate;
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> select database_role from v$database;
SQL> alter database recover managed standby database disconnect from session;
2、在备库端
select database_role,switchover_status from v$database;
如果是to_primary 表可以正常切换.
执行:ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
否则执行: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
shutdown immediate;
startup;
然后观察主备库日志,如果正常的话会看到备库会自动应用日志.
failover测试
1. 备库上检查是否存在归档中断
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
2. 在主库上执行语句并找出归档文件 SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND SEQUENCE# BETWEEN XX AND XX;
--如果存在拷贝相应的归档到STANDBY数据库，并注册.
SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'xxx';
***其他情况(primary数据库无法打开)：
--检查归档文件是否完整
分别在primary/standby执行下列语句：
SQL> select distinct thread#,max(sequence#) over(partition by thread#) a from v$archived_log;
把相差的归档复制到待转换的standby服务器
3. 启动failover
SQL> alter database recover managed standby database finish force;
FORCE关键字将会停止当前活动的RFS进程，以便立刻执行failover。
或
SQL> alter database recover managed standby database finish skip standby logfile;
4. 切换物理standby角色为primary SQL> alter database commit to switchover to primary;
SQL> shutdown immediate
SQL> startup
责任编辑：小草
