data guard日常维护及管理

日常维护
1、正确打开主库和备库
主库:
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
备库:
SQL> STARTUP MOUNT;
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
DISCONNECT FROM SESSION;

2、正确关闭顺序
备库:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL>SHUTDOWN IMMEDIATE;
主库
SQL>SHUTDOWN IMMEDIATE;

3、备库Read-Only模式打开
当前主库正常OPEN 状态、备库处于日志传送状态.
1 在备库停止日志传送
SQL> recover managed standby database cancel;

2 备库Read-only 模式打开
SQL> alter database open read only;

3 备库回到日志传送模式
SQL> recover managed standby database disconnect from session;
SQL> select status from v$instance;

4、日志传送状态监控
1 主库察看当前日志状况
SQL> select sequence#,status from v$log;

2 备库察看RFS(Remote File Service)接收日志情况和MRP 应用日志同步主库情况
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

3 察看备库是否和主库同步
SQL> SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#,APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS;

4 察看备库已经归档的redo
SQL> SELECT REGISTRAR, CREATOR, THREAD#, SEQUENCE#, FIRST_CHANGE#,NEXT_CHANGE# FROM V$ARCHIVED_LOG;

5 察看备库已经应用的redo
SQL> SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# FROM V$LOG_HISTORY;

6 察看备库接收,应用redo 数据过程.
SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;

5 备库归档目录维护
1 找到备库归档目录
SQL> show parameter log_archive_dest_1

6 主库正常切换
一人工干预主库正常切换
1 在主库端检验数据库可切换状态

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS:TO STANDBY 表示可以正常切换.
如果SWITCHOVER_STATUS 的值为SESSIONS ACTIVE,表示当前有会话处于ACTIVE状态

2 开始主库正常切换
如果SWITCHOVER_STATUS 的值为TO STANDBY 则:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
如果SWITCHOVER_STATUS 的值为SESSIONS ACTIVE 则:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
成功运行这个命令后，主库被修改为备库

3 重启先前的主库
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

4 在备库验证可切换状态
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO_PRIMARY
1 row selected

5 将目标备库转换为主库
如果SWITCHOVER_STATUS 的值为TO STANDBY 则:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
如果SWITCHOVER_STATUS 的值为SESSIONS ACTIVE 则:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
成功运行这个命令后，备库被修改为主库

6 重启目标备库
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

7 先前主库启动日志传送进程
SQL> alter database recover managed standby database disconnect;
总结: 这样主库的一次正常切换完成.切换后的状态,原先的主库变为备库,原先的备库变为主库.

7 主库灾难切换
人工干预主库灾难切换
SQL>alter database recover managed standby database cancel;
SQL>shutdown immediate
SQL>startup mount
SQL>ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
SQL>alter database recover managed standby database finish;
-- switch
SQL>alter database commit to switchover to primary with session shutdown;
-- open
SQL>shutdown immediate
SQL>startup