1.启动/停止MRP进程

log_archive_dest 为LGWR时需要创建standby redolog,为arch时无须设置standby redolog--必须设置standby redolog，数据实时同步alter database recover managed standby database using current controlfile disconnect;--数据异步同步，当主库切换归档时进行数据同步alter database recover managed standby database disconnect from session;--关闭MRP进程alter database recover managed standby database cancel;

2.检查主备库状态

select OPEN_MODE,PROTECTION_MODE,ACTIVATION#,DATABASE_ROLE,SWITCHOVER#,SWITCHOVER_STATUS FROM V$DATABASE;确认主库状态为to standby或者为sessions active、保护模式应该maximum performance、角色为PRIMARY在备库查询时通常为not allowed 或者sessions active，角色为PHYSICAL STANDBY

3.检查数据同步情况

-查看应用日志延迟时间：select value from v$dataguard_stats where name='apply lag';-查看接收日志延迟时间：select value from v$dataguard_stats where name='transport lag';-查看主库归档Primary: SQL> select thread#, max(sequence#) "Last Primary Seq Generated"from v$archived_log val, v$database vdbwhere val.resetlogs_change# = vdb.resetlogs_change#group by thread# order by 1;-查看备库已接收归档PhyStdby:SQL> select thread#, max(sequence#) "Last Standby Seq Received"from v$archived_log val, v$database vdbwhere val.resetlogs_change# = vdb.resetlogs_change#group by thread# order by 1;-查看备库已应用归档PhyStdby:SQL>select thread#, max(sequence#) "Last Standby Seq Applied"from v$archived_log val, v$database vdbwhere val.resetlogs_change# = vdb.resetlogs_change#and val.applied in ('YES','IN-MEMORY')group by thread# order by 1;-查看归档应用详细情况select first_time,sequence#,applied from v$archived_log;-查看主备库GAPselect * from v$archive_gap;

4.查询ASM Diskgroup 使用率

SET LINES 300 PAGES 9999COL name FOR a15COL USED_PERCENT FOR a15SELECT GROUP_NUMBER,NAME,TOTAL_MB / 1024 total_gb,FREE_MB / 1024,USABLE_FILE_MB / 1024,ROUND ( (TOTAL_MB - USABLE_FILE_MB) * 100 / TOTAL_MB) || '%'USED_PERCENTFROM V$ASM_DISKGROUPORDER BY 1;

5.检查进程

主库(确认ARCH进程正常)SQL> SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;PROCESS CLIENT_PROCESS SEQUENCE# STATUS------------------ ---------------- ---------- ------------------------ARCH ARCH 731 CLOSINGDGRD N/A 0 ALLOCATEDDGRD N/A 0 ALLOCATEDARCH ARCH 732 CLOSINGARCH ARCH 733 CLOSINGARCH ARCH 734 CLOSINGLNS LNS 735 WRITINGDGRD N/A 0 ALLOCATEDDGRD N/A 0 ALLOCATED备库(要确认存在MRP、ARCH、RFS进程)SQL> SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;PROCESS CLIENT_PROCESS SEQUENCE# STATUS------------------ ---------------- ---------- ------------------------ARCH ARCH 735 CLOSINGDGRD N/A 0 ALLOCATEDDGRD N/A 0 ALLOCATEDARCH ARCH 731 CLOSINGARCH ARCH 693 CLOSINGARCH ARCH 692 CLOSINGRFS LGWR 736 IDLERFS UNKNOWN 0 IDLERFS UNKNOWN 0 IDLERFS Archival 0 IDLERFS LGWR 694 IDLEPROCESS CLIENT_PROCESS SEQUENCE# STATUS------------------ ---------------- ---------- ------------------------MRP0 N/A 736 APPLYING_LOGRFS UNKNOWN 0 IDLERFS Archival 0 IDLE

6.查询，添加standby log

点击(此处)折叠或打开

select GROUP#, DBID db_id, THREAD#, SEQUENCE#, BYTES, USED, ARCHIVED, STATUS, FIRST_CHANGE#, NEXT_CHANGE#,LAST_CHANGE# from v$standby_log ;
alter database add standby logfile thread 1 group 7 size xxx ,group 8 size xxx ,group 9 size xxx,group 10 size xxx ;