DataGuard管理语句
-----
**启动日志应用服务:**
alter database recover managed standby database disconnect from session;
**启动实时应用服务:**
alter database recover managed standby database using current logfile disconnect from session;
**停止日志应用服务:**
alter database recover managed standby database cancel;
//停止redo应用,并不是停止物理standby数据库,standby仍会保持接收,只不过不会再应用接收到的归档,直到你再次启动redo应用为止。
**standby强制active打开:**
SQL>alter database activate standby database;
**查看进程的活动状态:**
V$managed_standby视图专门用于显示物理standby数据库相关进程的当前状态
SQL>select process,client_process,sequence#,status from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 49 CLOSING
ARCH ARCH 50 CLOSING
ARCH ARCH 0 CONNECTED
ARCH ARCH 48 CLOSING
MRP0 N/A 51 WAIT_FOR_LOG
RFS UNKNOWN 0 IDLE
RFS UNKNOWN 0 IDLE
RFS LGWR 51 IDLE
Process: 进程名称,如ARCH\RFS\MRP0等
Client_p: 对应的primary 数据库中的进程,如ARCH\LGWR等。
SEQUENCE#: 归档序号。
STATUS:进程的当前状态
**查看日志应用情况:**
**方式1:**
//备库查询
SQL>select sequence#,applied from v$archived_log
SEQUENCE# APPLIED
---------- ---------
42 YES
43 YES
44 YES
45 YES
46 YES
47 YES
48 YES
49 YES
50 YES
如果生产上归档日志太多,采用如上方式会很不方便。
**方式2:**
//主备库查询
SQL> select max(sequence#) from v$archived_log where applied='YES'; //备库查询
SQL> select max(sequence#) from v$archived_log; //主库查询
如果以上两个查询sequence一致,则日志应用正常。
**查询物理standby数据库未接收的日志文件:(主库查询)**
日志的发送是通过 log_archive_dest_n参数来控制,
因此我们只需要对比本地的归档和远端生成的归档间差异即可
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#);
**查看是否存在日志间隙:(备端查询)**
SYS> select thread#, low_sequence#, high_sequence# from v$archive_gap;
**检查DG传输是否报错:**
SYS>select dest_name,status,error from v$archive_dest
**查询备库standby_log状态:**
Standby redolog:存放的是从primary database接收来的redo数据。
Online redolog: 存放的是本机的redo记录
SYS>select group#,type,member from v$logfile;
SQL>select group#,thread#,status,sequence# from v$standby_log;
GROUP# THREAD# STATUS SEQUENCE#
---------- ---------- ---------- ----------
4 1 UNASSIGNED 0
5 1 ACTIVE 51
6 0 UNASSIGNED 0
7 0 UNASSIGNED 0
**查询数据库角色,保护模式,保护级别,切换状态:**
SQL>select database_role,db_unique_name,open_mode,protection_mode,protection_level,switchover_status from v$database;
**检查应用模式(是否启用了实时应用)**
SQL>select recovery_mode from v$archive_dest_status
如果开启了实时应用recovery_mode列会显示为:
**MANAGED REAL TIME APPLY**
**DG事件(v$dataguard_status)**
该视图显示那些自动触发写入alter.log或服务器trace文件的事件。
SQL>select message from v$dataguard_status;
MESSAGE
-----
Media Recovery Waiting for thread 1 sequence 50 (in transit)
ARC1: Beginning to archive thread 1 sequence 50 (1212168-1212187)
ARC1: Completed archiving thread 1 sequence 50 (0-0)
Media Recovery Log /oracle/app/arch/stddb/1_50_1046815491.arc
Media Recovery Waiting for thread 1 sequence 51 (in transit)
ending
待补充
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/69975956/viewspace-2712448/,如需转载,请注明出处,否则将追究法律责任。