查询Dataguard的状态：
set linesize 200 select d.DB_UNIQUE_NAME,d.open_mode,d.log_mode,d.DATABASE_ROLE,d.PROTECTION_MODE from v$database d; DB_UNIQUE_NAME OPEN_MODE LOG_MODE DATABASE_ROLE PROTECTION_MODE ------------------------------ -------------------- ------------ ---------------- -------------------- orcl_st MOUNTED ARCHIVELOG PHYSICAL STANDBY MAXIMUM PERFORMANCE
注意：从open_mode看不出来是否apply log，这两种状态都是mounted，要看进程MRP是否存在来决定是否apply log。
ps aux|grep mrp --检查进程是否存在
[oracle@dg2:/home/oracle]$ps -ef|grep mrp oracle 15157 1 0 7月29 ? 00:03:18 ora_mrp0_scutech2 oracle 24630 22132 0 02:09 pts/0 00:00:00 grep --color=auto mrp [oracle@dg2:/home/oracle]$
如果不存在执行以下语句：
alter database recover managed standby database using current logfile disconnect; alter database recover managed standby database disconnect from session; --后台执行 alter database recover managed standby database --前台执行，执行这个可以看到报错的情况
如果有报错，查看alert日志和log.xml日志
验证是否正常
SQL> select process,status,sequence# from v$managed_standby; PROCESS STATUS SEQUENCE# ------------------ ------------------------ ---------- ARCH CLOSING 73 DGRD ALLOCATED 0 DGRD ALLOCATED 0 ARCH CONNECTED 0 ARCH CLOSING 74 ARCH CLOSING 51 RFS IDLE 0 MRP0 APPLYING_LOG 75 RFS IDLE 75 9 rows selected.
可以看到看到mrp0是否正常。
查询最近每个归档目标的状态，每个目标的归档文件应该一样，如果不一样，status不是“VALID”。
set linesize 200 col DESTINATION form a40 SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ# FROM V$ARCHIVE_DEST_STATUS WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE'; DESTINATION STATUS ARCHIVED_THREAD# ARCHIVED_SEQ# ---------------------------------------- --------- --------------------- --------------------- /u01/app/oracle/oradata/orcl/archivelog VALID 1 106 orcl_st VALID 1 106
查询最近的一次归档日志序号：
SQL> SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG WHERE RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG) GROUP BY THREAD#; MAX(SEQUENCE#) THREAD# --------------------- --------------------- 106 1
检查已经归档到目标1但没有归档到目标2的归档日志:
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#);
检查gap
SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;
查询日志应用速度
COLUMN NAME FORMAT A24 COLUMN VALUE FORMAT A16 COLUMN DATUM_TIME FORMAT A24 SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS;
在Redo源数据库执行以下SQL语句显示目的地2的响应时间柱状图
select frequency,duration from v$redo_dest_resp_histogram where dest_id = 2 and frequency > 1; 1 一个不断生成redolog的程序 COUNTER=0 while [ $COUNTER -lt 2000 ] do sqlplus / as sysdba<<EOF create table test_a as select * from dba_objects; exit; EOF sleep 100 sqlplus / as sysdba<<EOF drop table test_a; exit; EOF let COUNTER+=1 done
