导航：首页 > 数据库 >

DG 日常管理命令汇总

发表于：2025-01-20 作者：千家信息网编辑

千家信息网最后更新 2025年01月20日，1.启动/停止MRP进程log_archive_dest 为LGWR时需要创建standby redolog,为arch时无须设置standby redolog--必须设置standby redolo

千家信息网最后更新 2025年01月20日DG 日常管理命令汇总

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 vdb

whereval.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 vdb

whereval.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 vdb

whereval.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;

-查看主备库GAP

select * from v$archive_gap;

4.查询ASM Diskgroup 使用率

SET LINES 300 PAGES 9999

COL name FOR a15

COL USED_PERCENT FOR a15

SELECT 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_PERCENT

FROM V$ASM_DISKGROUP

ORDER BY 1;

5.检查进程

主库(确认ARCH进程正常)
SQL> SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;
PROCESS CLIENT_PROCESS SEQUENCE# STATUS
------------------ ---------------- ---------- ------------------------
ARCH ARCH 731 CLOSING
DGRD N/A 0 ALLOCATED
DGRD N/A 0 ALLOCATED
ARCH ARCH 732 CLOSING
ARCH ARCH 733 CLOSING
ARCH ARCH 734 CLOSING
LNS LNS 735 WRITING
DGRD N/A 0 ALLOCATED
DGRD 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 CLOSING
DGRD N/A 0 ALLOCATED
DGRD N/A 0 ALLOCATED
ARCH ARCH 731 CLOSING
ARCH ARCH 693 CLOSING
ARCH ARCH 692 CLOSING
RFS LGWR 736 IDLE
RFS UNKNOWN 0 IDLE
RFS UNKNOWN 0 IDLE
RFS Archival 0 IDLE
RFS LGWR 694 IDLE
PROCESS CLIENT_PROCESS SEQUENCE# STATUS
------------------ ---------------- ---------- ------------------------
MRP0 N/A 736 APPLYING_LOG
RFS UNKNOWN 0 IDLE
RFS 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 ;