日常维护：

正确的开关机顺序是：
启动的时候，先备库的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;

3. 在主库上执行语句并找出归档文件
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