DG 日常管理命令汇总

1.启动/停止MRP进程

点击(此处)折叠或打开

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

2.检查主备库状态

点击(此处)折叠或打开

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

3.检查数据同步情况

点击(此处)折叠或打开

1. -查看应用日志延迟时间：
2. select value from v$dataguard_stats where name='apply lag';
3. -查看接收日志延迟时间：
4. select value from v$dataguard_stats where name='transport lag';
5. -查看主库归档
6. Primary: SQL> select thread#, max(sequence#) "Last Primary Seq Generated"
7. from v$archived_log val, v$database vdb
8. where val.resetlogs_change# = vdb.resetlogs_change#
9. group by thread# order by 1;
10. -查看备库已接收归档
11. PhyStdby:SQL> select thread#, max(sequence#) "Last Standby Seq Received"
12. from v$archived_log val, v$database vdb
13. where val.resetlogs_change# = vdb.resetlogs_change#
14. group by thread# order by 1;
15. -查看备库已应用归档
16. PhyStdby:SQL>select thread#, max(sequence#) "Last Standby Seq Applied"
17. from v$archived_log val, v$database vdb
18. where val.resetlogs_change# = vdb.resetlogs_change#
19. and val.applied in ('YES','IN-MEMORY')
20. group by thread# order by 1;
21. -查看归档应用详细情况
22. select first_time,sequence#,applied from v$archived_log;
23. -查看主备库GAP
24. select * from v$archive_gap;

4.查询ASM Diskgroup 使用率

点击(此处)折叠或打开

1. SET LINES 300 PAGES 9999
2. COL name FOR a15
3. COL USED_PERCENT FOR a15
4. SELECT GROUP_NUMBER,
5. NAME,
6. TOTAL_MB / 1024 total_gb,
7. FREE_MB / 1024,
8. USABLE_FILE_MB / 1024,
9. ROUND ( (TOTAL_MB - USABLE_FILE_MB) * 100 / TOTAL_MB) || '%'
10. USED_PERCENT
11. FROM V$ASM_DISKGROUP
12. ORDER BY 1;

5.检查进程

点击(此处)折叠或打开

1. 主库(确认ARCH进程正常)
2. SQL> SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;
3. PROCESS CLIENT_PROCESS SEQUENCE# STATUS
4. ------------------ ---------------- ---------- ------------------------
5. ARCH ARCH 731 CLOSING
6. DGRD N/A 0 ALLOCATED
7. DGRD N/A 0 ALLOCATED
8. ARCH ARCH 732 CLOSING
9. ARCH ARCH 733 CLOSING
10. ARCH ARCH 734 CLOSING
11. LNS LNS 735 WRITING
12. DGRD N/A 0 ALLOCATED
13. DGRD N/A 0 ALLOCATED
15. 备库(要确认存在MRP、ARCH、RFS进程)
16. SQL> SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;
17. PROCESS CLIENT_PROCESS SEQUENCE# STATUS
18. ------------------ ---------------- ---------- ------------------------
19. ARCH ARCH 735 CLOSING
20. DGRD N/A 0 ALLOCATED
21. DGRD N/A 0 ALLOCATED
22. ARCH ARCH 731 CLOSING
23. ARCH ARCH 693 CLOSING
24. ARCH ARCH 692 CLOSING
25. RFS LGWR 736 IDLE
26. RFS UNKNOWN 0 IDLE
27. RFS UNKNOWN 0 IDLE
28. RFS Archival 0 IDLE
29. RFS LGWR 694 IDLE
30. PROCESS CLIENT_PROCESS SEQUENCE# STATUS
31. ------------------ ---------------- ---------- ------------------------
32. MRP0 N/A 736 APPLYING_LOG
33. RFS UNKNOWN 0 IDLE
34. RFS Archival 0 IDLE

6.查询，添加standby log

点击(此处)折叠或打开

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15412087/viewspace-2155142/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15412087/viewspace-2155142/