概述
检查灾备
检查保护模式
在主备库
set linesize 1000;
col DATABASE_ROLE for a25;
col INSTANCE for a25;
col OPEN_MODE for a25;
col PROTECTION_MODE for a25;
col PROTECTION_LEVEL for a25;
col SWITCHOVER_STATUS for a25;
SQL> SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;
--确认主库状态为to standby或者为sessions active、保护模式应该maximum performance、角色为PRIMARY，
如果状态异常，请按《检查DataGauard状态》章节操作检查DataGuard信息
--在备库查询时通常为not allowed 或者sessions active，角色为PHYSICAL STANDBY； 当primary主库改为standby角色后，那么其中的任意一个standby库都可能被切换为primary角色，这时需要确认standby库的状态为to primary
检查DataGuard状态
在主备库
SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;
MESSAGE
--------------------------------------------------------------------------------
ARC0: Archival started
ARC1: Archival started
Archivelog destination LOG_ARCHIVE_DEST_2 validated for no-data-loss
recovery
Creating archive destination LOG_ARCHIVE_DEST_2: 'dest2'
ARCH: Transmitting activation ID 0
LGWR: Completed archiving log 3 thread 1 sequence 11
Creating archive destination LOG_ARCHIVE_DEST_2: 'dest2'
LGWR: Transmitting activation ID6877c1fe
LGWR: Beginning to archive log 4 thread 1 sequence 12
ARC0: Evaluating archive log 3 thread 1 sequence 11
ARC0: Archive destination LOG_ARCHIVE_DEST_2: Previously completed
ARC0: Beginning to archive log 3 thread 1 sequence 11
Creating archive destination LOG_ARCHIVE_DEST_1:
'/oracle/arch/arch_1_11.arc'
ARC0: Completed archiving log 3 thread 1 sequence 11
ARC1: Transmitting activation ID6877c1fe
15 rows selected.
--使用V$DATAGUARD_STATUS结合alert日志信息，判断DataGuard使用过程中的错误信息，查看当前日志应用的状态。
可以用ls -lt|more命令来查看主库最新归档日志文件的序号，对比备库当前归档日志应用状态了解备库目前和主库日志文件之间的差异情况。
检查进程
在主库
SQL> SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 9 CLOSING
ARCH ARCH 9 CLOSING
在备库
SQL> SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
MRP0 N/A 10 WAIT_FOR_LOG --备库已经完成了来至于主库的日志文件9，现在正在等待日志文件10
RFS UNKNOWN 0 IDLE
--MRP是应用日志，ARCH是归档进程，RFS是取重做日志的进程。
主库进程需要确认有ARCH进程，备库需要确认存在MRP、ARCH、RFS进程，如果不存在则表示dataguard工作不正常，查询V$DATAGUARD_STATUS视图和alert日志需找具体的错误信息。
检查归档状态
在主库
SELECT DEST_ID,DEST_NAME,STATUS from V$ARCHIVE_DEST_STATUS;
--判断primary库的2个归档路径status是否为valid
在主备库
set linesize 1000
col NAME for a60
col CREATOR for a10
col SEQUENCE# for a10
col APPLIED for a10
col COMPLETION_TIME for a15
SQL> SELECT NAME, CREATOR, SEQUENCE#, APPLIED, COMPLETION_TIME FROM V$ARCHIVED_LOG;
NAME CREATOR SEQUENCE# APP COMPLETIO
---------------------------------------------- ------- --------- --- ---------
H:ORACLEORADATAPAYROLLSTANDBYARC00198.001 ARCH 198 YES 30-MAY-02
H:ORACLEORADATAPAYROLLSTANDBYARC00199.001 ARCH 199 YES 30-MAY-02
H:ORACLEORADATAPAYROLLSTANDBYARC00200.001 ARCH 200 YES 30-MAY-02
H:ORACLEORADATAPAYROLLSTANDBYARC00201.001 LGWR 201 YES 30-MAY-02
H:ORACLEORADATAPAYROLLSTANDBYARC00202.001 ARCH 202 YES 30-MAY-02
H:ORACLEORADATAPAYROLLSTANDBYARC00203.001 LGWR 203 YES 30-MAY-02
判断当前有哪些未APPLY，可通过操作系统命令ls查看对应目录下未APPLY的日志是否已经传输到standby端
或者依据《检查主库未传输的日志》章节操作进行判断；
查询V$DATAGUARD_STATUS是否有ERROR信息；依据《检查进程》章节判断进程是否正常
检查备库归档应用状态
在备库查询
SQL>
ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
---------------- ------------- --------------- ------------
1 947 1 945
--注意ARCHIVED_SEQ#和APPLIED_SEQ#的差异
SQL> SELECT THREAD#, MAX(SEQUENCE#) FROM V$LOG_HISTORY GROUP BY THREAD#;
THREAD# LAST_APPLIED_LOG
---------- ----------------
1 7
--找出最后应用的日志的sequence
SQL> select t.THREAD#,t.SEQUENCE#,t.APPLIED from v$archived_log t;
THREAD# SEQUENCE# APP
---------- ---------- ---
1 1 YES
1 2 YES
1 3 YES
1 4 YES
1 5 YES
1 6 YES
1 7 YES
7 rows selected.
--找出具体哪些日志被应用了，哪些未被应用
SELECT FIRST_TIME, THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# FROM V$LOG_HISTORY;
THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ------------- ------------
1 945 74651 74739
--查找备库应用的SCN号
检查主库未传输的日志
--在主库
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#);
THREAD# SEQUENCE#
---------- ----------
1 12
1 13
1 14
在主备库
SQL> select * from V$ARCHIVE_GAP;
注：dest_2目录与dest_1目录中的日志差异
如果有较多未应用的日志，可参考《检查归档状态》章节进行处理。
相关视图
V$ARCHIVE_DEST
V$ARCHIVE_DEST_STATUS
V$ARCHIVE_GAP
V$ARCHIVED_LOG
V$DATABASE
V$DATAFILE
V$DATAGUARD_STATUS
V$LOG
V$LOGFILE
V$LOG_HISTORY
V$MANAGED_STANDBY (Physical Standby Databases Only)
V$STANDBY_LOG
正常开关机顺序
关闭的时候，先关闭主库的listener，再关闭主库，再关闭备库的listene，再关闭备库。
主库(hsvipdb1)
[oracle@vipdb1 ~]$ lsnrctl stop
[oracle@vipdb1 ~]$ sqlplus / as sysdba
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
备库(hsvipdb2)
[oracle@vipdb2 ~]$ lsnrctl stop
[oracle@vipdb2 ~]$ sqlplus / as sysdba
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
启动的时候，先启动备库的listener，再启动备库，再启动主库的listener，再启动主库。
备库(hsvipdb2)
[oracle@vipdb2 ~]$ lsnrctl start
[oracle@vipdb2 ~]$ sqlplus / as sysdba
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2.5166E+10 bytes
Fixed Size 2141736 bytes
Variable Size 2617246168 bytes
Database Buffers 2.2532E+10 bytes
Redo Buffers 14635008 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
主库(hsvipdb1)
[oracle@vipdb1 ~]$ lsnrctl start
[oracle@vipdb1 ~]$ sqlplus / as sysdba
SQL>startup;
Database closed.
Database dismounted.
ORACLE instance shut down.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24695024/viewspace-2132098/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/24695024/viewspace-2132098/
最后
以上就是机智蛋挞为你收集整理的DataGuard日常运维的全部内容，希望文章能够帮你解决DataGuard日常运维所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
发表评论 取消回复