1.检查警告日志
数据库警告日志是按照时间顺序记录数据库的信息和错误,除了提供oracle数据库信息之外,还包括对Data Guard一些操作的信息,如下:
1.1执行相关sql语句:alter database recover managed standby,startup,shutdown,archive log还有recover;
1.2一些后台进程会报相关错误,如:ARCO,MRP0,RFS,LGWR;
1.3操作完成时间戳;
警告日志还会记录进程的trace或dump文件的路径;
2.监控活动进程
你可以获得重做日志在备库上应用的信息,可以监控以下进程:
Reference Name System Process Names
ARCH ARC0,ARC1,ARC2,…
MRP MRP, MRP0
RFS ORACLE{SID}
可以使v$managed_standby动态性视图在备库上查询redo传输和redo应用的情况,client_process列是和主库的进程对应的;
SYS@jzh>select process,client_process,sequence#,status from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 93 CLOSING
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 92 CLOSING
MRP0 N/A 94 WAIT_FOR_LOG
RFS ARCH 0 IDLE
RFS UNKNOWN 0 IDLE
RFS LGWR 94 IDLE
8 rows selected.
3.确定redo应用进程
可以通过v$archive_dest_status视图在主库与备库查询归档日志是否有被应用,以及sequence号;
SYS@jzh>select ARCHIVED_THREAD#,ARCHIVED_SEQ#,APPLIED_THREAD#,APPLIED_SEQ# from v$archive_dest_status;
ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
---------------- ------------- --------------- ------------
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
1 93 1 93
4.standby查询归档路径和由什么进和创建
select name,creator,sequence#,applied,completion_time from v$archived_log;
NAME CREATOR SEQUENCE# APPLIED COMPLETIO
-------------------------------------------------- ------- ---------- --------- ---------
/u01/oracle/arch/1_90_860549359.arc SRMN 90 YES 26-DEC-14
/u01/oracle/arch/1_91_860549359.arc SRMN 91 YES 26-DEC-14
/u01/oracle/arch/1_92_860549359.arc ARCH 92 YES 26-DEC-14
/u01/oracle/arch/1_93_860549359.arc ARCH 93 YES 26-DEC-14
5.查询standby数据库在open resetlog之前,之后的incarnations(primary在open resetlog之前)
SYS@jzh>select incarnation#,resetlogs_id,status from v$database_incarnation;
INCARNATION# RESETLOGS_ID STATUS
------------ ------------ -------
1 860549359 CURRENT
SYS@jzh>select resetlogs_id,thread#,sequence#,status,archived from v$archived_log order by resetlogs_id,sequence#;
RESETLOGS_ID THREAD# SEQUENCE# S ARC
------------ ---------- ---------- - ---
860549359 1 90 A YES
860549359 1 91 A YES
860549359 1 92 A YES
860549359 1 93 A YES
6.将primary以resetlog打开再查询
SYS@jzh>select incarnation#,resetlogs_id,status from v$database_incarnation;
INCARNATION# RESETLOGS_ID STATUS
------------ ------------ -------
1 860549359 PARENT
2 867348172 CURRENT
SYS@jzh>select resetlogs_id,thread#,sequence#,status,archived from v$archived_log order by resetlogs_id,sequence#;
RESETLOGS_ID THREAD# SEQUENCE# S ARC
------------ ---------- ---------- - ---
860549359 1 90 A YES
860549359 1 91 A YES
860549359 1 92 A YES
860549359 1 93 A YES
860549359 1 94 A YES
860549359 1 95 A YES
860549359 1 96 A YES
860549359 1 97 A YES
860549359 1 98 A YES
860549359 1 99 A YES
860549359 1 100 A YES
7.查询归档历史归档日志
可以通过v$log_history查询备库的归档日志信息,包括log中最小的scn,最大的scn,sequence号;
SYS@jzh>select first_time,first_change#,next_change#,sequence# from v$log_history;
FIRST_TIM FIRST_CHANGE# NEXT_CHANGE# SEQUENCE#
--------- ------------- ------------ ----------
26-DEC-14 893773 893831 100
26-DEC-14 893831 893956 101
26-DEC-14 893956 893964 102
26-DEC-14 893964 893969 103
26-DEC-14 893969 893973 104
26-DEC-14 893973 893976 105
26-DEC-14 893976 893980 106
26-DEC-14 893980 893983 107
26-DEC-14 893983 893987 108
26-DEC-14 893987 894398 109
26-DEC-14 894398 894401 1
FIRST_TIM FIRST_CHANGE# NEXT_CHANGE# SEQUENCE#
--------- ------------- ------------ ----------
26-DEC-14 894401 894575 2
8.查看哪些归档在备库已经应用
SYS@jzh>select thread#,max(sequence#) as "last_applied_log"
2 from v$log_history
3 group by thread#;
THREAD# last_applied_log
---------- ----------------
1 109
也可以通以下查询语句
SYS@jzh>SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG order by sequence#;
THREAD# SEQUENCE# APPLIED
---------- ---------- ---------
1 1 YES
1 2 YES
1 90 YES
1 91 YES
1 92 YES
1 93 YES
1 94 YES
1 95 YES
1 96 YES
1 97 YES
1 98 YES
THREAD# SEQUENCE# APPLIED
---------- ---------- ---------
1 99 YES
1 100 YES
1 101 YES
1 102 YES
1 103 YES
1 104 YES
1 105 YES
1 106 YES
1 107 YES
1 108 YES
1 109 NO
22 rows selected.
9.查询哪些归档日志没有在standby应用
SYS@jzh>SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM
2 (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=4) LOCAL
3 WHERE LOCAL.SEQUENCE# NOT IN
4 (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=3 AND
5 THREAD# = LOCAL.THREAD#);
no rows selected
注意:dest_id可与dest_name查询出来,其实就是归档路径。
数据库警告日志是按照时间顺序记录数据库的信息和错误,除了提供oracle数据库信息之外,还包括对Data Guard一些操作的信息,如下:
1.1执行相关sql语句:alter database recover managed standby,startup,shutdown,archive log还有recover;
1.2一些后台进程会报相关错误,如:ARCO,MRP0,RFS,LGWR;
1.3操作完成时间戳;
警告日志还会记录进程的trace或dump文件的路径;
2.监控活动进程
你可以获得重做日志在备库上应用的信息,可以监控以下进程:
Reference Name System Process Names
ARCH ARC0,ARC1,ARC2,…
MRP MRP, MRP0
RFS ORACLE{SID}
可以使v$managed_standby动态性视图在备库上查询redo传输和redo应用的情况,client_process列是和主库的进程对应的;
SYS@jzh>select process,client_process,sequence#,status from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 93 CLOSING
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 92 CLOSING
MRP0 N/A 94 WAIT_FOR_LOG
RFS ARCH 0 IDLE
RFS UNKNOWN 0 IDLE
RFS LGWR 94 IDLE
8 rows selected.
3.确定redo应用进程
可以通过v$archive_dest_status视图在主库与备库查询归档日志是否有被应用,以及sequence号;
SYS@jzh>select ARCHIVED_THREAD#,ARCHIVED_SEQ#,APPLIED_THREAD#,APPLIED_SEQ# from v$archive_dest_status;
ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
---------------- ------------- --------------- ------------
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
1 93 1 93
4.standby查询归档路径和由什么进和创建
select name,creator,sequence#,applied,completion_time from v$archived_log;
NAME CREATOR SEQUENCE# APPLIED COMPLETIO
-------------------------------------------------- ------- ---------- --------- ---------
/u01/oracle/arch/1_90_860549359.arc SRMN 90 YES 26-DEC-14
/u01/oracle/arch/1_91_860549359.arc SRMN 91 YES 26-DEC-14
/u01/oracle/arch/1_92_860549359.arc ARCH 92 YES 26-DEC-14
/u01/oracle/arch/1_93_860549359.arc ARCH 93 YES 26-DEC-14
5.查询standby数据库在open resetlog之前,之后的incarnations(primary在open resetlog之前)
SYS@jzh>select incarnation#,resetlogs_id,status from v$database_incarnation;
INCARNATION# RESETLOGS_ID STATUS
------------ ------------ -------
1 860549359 CURRENT
SYS@jzh>select resetlogs_id,thread#,sequence#,status,archived from v$archived_log order by resetlogs_id,sequence#;
RESETLOGS_ID THREAD# SEQUENCE# S ARC
------------ ---------- ---------- - ---
860549359 1 90 A YES
860549359 1 91 A YES
860549359 1 92 A YES
860549359 1 93 A YES
6.将primary以resetlog打开再查询
SYS@jzh>select incarnation#,resetlogs_id,status from v$database_incarnation;
INCARNATION# RESETLOGS_ID STATUS
------------ ------------ -------
1 860549359 PARENT
2 867348172 CURRENT
SYS@jzh>select resetlogs_id,thread#,sequence#,status,archived from v$archived_log order by resetlogs_id,sequence#;
RESETLOGS_ID THREAD# SEQUENCE# S ARC
------------ ---------- ---------- - ---
860549359 1 90 A YES
860549359 1 91 A YES
860549359 1 92 A YES
860549359 1 93 A YES
860549359 1 94 A YES
860549359 1 95 A YES
860549359 1 96 A YES
860549359 1 97 A YES
860549359 1 98 A YES
860549359 1 99 A YES
860549359 1 100 A YES
7.查询归档历史归档日志
可以通过v$log_history查询备库的归档日志信息,包括log中最小的scn,最大的scn,sequence号;
SYS@jzh>select first_time,first_change#,next_change#,sequence# from v$log_history;
FIRST_TIM FIRST_CHANGE# NEXT_CHANGE# SEQUENCE#
--------- ------------- ------------ ----------
26-DEC-14 893773 893831 100
26-DEC-14 893831 893956 101
26-DEC-14 893956 893964 102
26-DEC-14 893964 893969 103
26-DEC-14 893969 893973 104
26-DEC-14 893973 893976 105
26-DEC-14 893976 893980 106
26-DEC-14 893980 893983 107
26-DEC-14 893983 893987 108
26-DEC-14 893987 894398 109
26-DEC-14 894398 894401 1
FIRST_TIM FIRST_CHANGE# NEXT_CHANGE# SEQUENCE#
--------- ------------- ------------ ----------
26-DEC-14 894401 894575 2
8.查看哪些归档在备库已经应用
SYS@jzh>select thread#,max(sequence#) as "last_applied_log"
2 from v$log_history
3 group by thread#;
THREAD# last_applied_log
---------- ----------------
1 109
也可以通以下查询语句
SYS@jzh>SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG order by sequence#;
THREAD# SEQUENCE# APPLIED
---------- ---------- ---------
1 1 YES
1 2 YES
1 90 YES
1 91 YES
1 92 YES
1 93 YES
1 94 YES
1 95 YES
1 96 YES
1 97 YES
1 98 YES
THREAD# SEQUENCE# APPLIED
---------- ---------- ---------
1 99 YES
1 100 YES
1 101 YES
1 102 YES
1 103 YES
1 104 YES
1 105 YES
1 106 YES
1 107 YES
1 108 YES
1 109 NO
22 rows selected.
9.查询哪些归档日志没有在standby应用
SYS@jzh>SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM
2 (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=4) LOCAL
3 WHERE LOCAL.SEQUENCE# NOT IN
4 (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=3 AND
5 THREAD# = LOCAL.THREAD#);
no rows selected
注意:dest_id可与dest_name查询出来,其实就是归档路径。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10271187/viewspace-1381621/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10271187/viewspace-1381621/