检查dg主从库运行情况

in primary

----最新的日志
select thread#, sequence# from v$thread;

---- 备库列表及状态,是否有gap
set line 300
col DB_UNIQUE_NAME for a20
col DEST_NAME for a20
col DESTINATION for a20
col DATABASE_MODE for a20
col recovery_mode for a25
col destination for a25
col gap_status for a10
col type for a10
col status for a10
select dest_id,dest_name,status,type,database_mode,recovery_mode,destination,db_unique_name,gap_status from v$archive_dest_status where status='VALID';

---- lns进程的状态
select process, status, thread#, sequence# from v$managed_standby where process='LNS';

in standby

----MRP0当前正在处理的日志
select process, status, thread#, sequence# from v$managed_standby where process='MRP0';

select * from v$archive_gap;

----已应用的日志
select sequence#,applied from v$archived_log where applied='YES';

select min(sequence#) from v$archived_log where applied='NO' and first_time>sysdate-30;

select name,sequence#,first_time,registrar,applied from v$archived_log where applied='NO' and first_time>sysdate-30;

---- 备库列表及状态
set line 300
col DB_UNIQUE_NAME for a20
col DEST_NAME for a20
col DESTINATION for a20
col DATABASE_MODE for a20
col recovery_mode for a25
col destination for a25
col gap_status for a10
col type for a10
col status for a10
select dest_id,dest_name,status,type,database_mode,recovery_mode,destination,db_unique_name,gap_status from v$archive_dest_status where status='VALID';

开启同步

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;

查看数据库状态

in primary

set line 200
col DATABASE_ROLE for a20
col OPEN_MODE for a20
col PROTECTION_MODE for a20
col SWITCHOVER_STATUS for a20
col PRIMARY_DB_UNIQUE_NAME for a20
select name,db_unique_name, database_role, open_mode,PROTECTION_MODE,switchover_status,primary_db_unique_name from v$database;

in standby

set line 200
col DATABASE_ROLE for a20
col OPEN_MODE for a20
col PROTECTION_MODE for a20
col SWITCHOVER_STATUS for a20
col PRIMARY_DB_UNIQUE_NAME for a20
select name,db_unique_name, database_role, open_mode,PROTECTION_MODE,switchover_status,primary_db_unique_name from v$database;

dg延迟情况的检查（在主库执行）

set line 300 numwidth 16
col VALUE for a60
col APPLIED_LAG for a20
with
t1 as (select p.value, ad.dest_id,ad.APPLIED_SCN from v$archive_dest AD , v$parameter p where --APPLIED_SCN!=0 and
TARGET='STANDBY' and AD.STATUS='VALID' and p.name=lower(ad.DEST_NAME)),
t2 as (select current_scn from v$database)
select t2.current_scn,t1.APPLIED_SCN,t1.value,t1.dest_id, 'DG Lag: ' ||
trunc((to_date(to_char(scn_to_timestamp(t2.current_scn),'yyyy-mm-dd hh24:mi:ss'),'YYYY-MM-DD HH24:MI:SS')
-to_date(to_char(scn_to_timestamp(t1.APPLIED_SCN),'yyyy-mm-dd hh24:mi:ss'),'YYYY-MM-DD HH24:MI:SS'))*24*60*60)||'s' APPLIED_LAG
from t1,t2;

APPLIED_LAG 一般少于60s

(on standby) Oracle日常运维之DG库日志应用慢参数调整实战:

https://www.modb.pro/db/42407

SQL> show parameter parallel_execution_message_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_execution_message_size      integer     16384
SQL> 

alter system set parallel_execution_message_size=32768 scope=spfile ;

dg库日志应用性能监控(on standby)

SET LINES 200 PAGES 2000
COL process FORMAT a8
COL spid FORMAT a8
COL event FORMAT a40 TRU
COL SIW FORMAT 999999

SELECT TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS') "Current time",
s.process,
p.spid,
SUBSTR (s.program, -6) PROC,
s.status,
s.event,
s.p1,
s.p2,
s.p3,
s.seconds_in_wait SIW,
s.seq#
FROM v$session s, v$process p
WHERE     p.addr = s.paddr
AND (   s.program LIKE '%MRP%'
OR s.program LIKE '%PR0%'
OR s.program LIKE '%DBW%'
OR s.program LIKE '%CKPT%')
order by s.process
/

指定并行度恢复(on standby)

alter database recover managed standby database parallel 8 disconnect from session;


alter database recover managed standby database parallel 4 disconnect from session; ----并行度根据CPU核数*2设定

查看未应用的日志(on standby)

select sequence#,applied from v$archived_log where applied='NO'; 

alter database open;

select open_mode from v$database;

check_primary_status.sql

spool check_primary_status.out
select to_char(sysdate,'yyyy-MM-dd HH24:MI:SS') from dual;
PROMPT ========================================
PROMPT >> 1.show  primary the last redo 
PROMPT ======================================== 
PROMPT 
PROMPT select thread#, sequence# from v$thread;
select thread#, sequence# from v$thread;

PROMPT =========================================
PROMPT >> 2.show archive_dest_status list and gap 
PROMPT =========================================
PROMPT
PROMPT select dest_id,dest_name,status,type,database_mode,recovery_mode,destination,db_unique_name,gap_status from v$archive_dest_status where status='VALID';
set line 300
col DB_UNIQUE_NAME for a20
col DEST_NAME for a20
col DESTINATION for a20
col DATABASE_MODE for a20
col recovery_mode for a25
col destination for a25
col gap_status for a10
col type for a10
col status for a10
select dest_id,dest_name,status,type,database_mode,recovery_mode,destination,db_unique_name,gap_status from v$archive_dest_status where status='VALID';

PROMPT =========================================
PROMPT >> 3.show  lns process status
PROMPT =========================================
PROMPT
PROMPT select process, status, thread#, sequence# from v$managed_standby where process='LNS';
select process, status, thread#, sequence# from v$managed_standby where process='LNS';


PROMPT =========================================
PROMPT >> 4.show primary database status
PROMPT =========================================
PROMPT
PROMPT select name,db_unique_name, database_role, open_mode,PROTECTION_MODE,switchover_status,primary_db_unique_name from v$database;
set line 200
col DATABASE_ROLE for a20
col OPEN_MODE for a20
col PROTECTION_MODE for a20
col SWITCHOVER_STATUS for a20
col PRIMARY_DB_UNIQUE_NAME for a20
select name,db_unique_name, database_role, open_mode,PROTECTION_MODE,switchover_status,primary_db_unique_name from v$database;
spool off
exit

check_stby_status.sql

spool check_stby_status.out
select to_char(sysdate,'yyyy-MM-dd HH24:MI:SS') from dual;
PROMPT =========================================
PROMPT >> 1.show stdy MRP0 applying log currently 
PROMPT =========================================
PROMPT
PROMPT select process, status, thread#, sequence# from v$managed_standby where process='MRP0';
col status for a20
select process, status, thread#, sequence# from v$managed_standby where process='MRP0';

PROMPT =========================================
PROMPT >> 2.show logs that has been applied or not has been applied
PROMPT =========================================
PROMPT
PROMPT select max(sequence#) from v$archived_log where applied='YES' ;
select max(sequence#) from v$archived_log where applied='YES' ;
PROMPT select min(sequence#) from v$archived_log where applied='NO' and first_time>sysdate-3;
select min(sequence#) from v$archived_log where applied='NO' and first_time>sysdate-3;
PROMPT select name,sequence#,first_time,registrar,applied from v$archived_log where applied='NO' and first_time>sysdate-3;
select name,sequence#,first_time,registrar,applied from v$archived_log where applied='NO' and first_time>sysdate-3;

PROMPT =========================================
PROMPT >> 3. show standby archive_dest_status list and gap  
PROMPT =========================================
PROMPT
PROMPT select dest_id,dest_name,status,type,database_mode,recovery_mode,destination,db_unique_name,gap_status from v$archive_dest_status where status='VALID';
set line 300
col DB_UNIQUE_NAME for a20
col DEST_NAME for a20
col DESTINATION for a20
col DATABASE_MODE for a20
col recovery_mode for a25
col destination for a25
col gap_status for a10
col type for a10
col status for a10
select dest_id,dest_name,status,type,database_mode,recovery_mode,destination,db_unique_name,gap_status from v$archive_dest_status where status='VALID';

PROMPT =========================================
PROMPT >> 4.show stby database status
PROMPT =========================================
PROMPT
PROMPT select name,db_unique_name, database_role, open_mode,PROTECTION_MODE,switchover_status,primary_db_unique_name from v$database;

set line 200
col DATABASE_ROLE for a20
col OPEN_MODE for a20
col PROTECTION_MODE for a20
col SWITCHOVER_STATUS for a20
col PRIMARY_DB_UNIQUE_NAME for a20
select name,db_unique_name, database_role, open_mode,PROTECTION_MODE,switchover_status,primary_db_unique_name from v$database;

PROMPT =========================================
PROMPT >> 5. check stdy applying status
PROMPT =========================================
PROMPT

SET LINES 200 PAGES 2000
COL process FORMAT a8
COL spid FORMAT a8
COL event FORMAT a40 TRU
COL SIW FORMAT 999999
col PROC for a15
col P1 for 999999
col P2 for 999999
col P3 for 999999
col Current_time for a20

SELECT TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS') "Current_time",
s.process,
p.spid,
SUBSTR (s.program, -6) PROC,
s.status,
s.event,
s.p1,
s.p2,
s.p3,
s.seconds_in_wait SIW,
s.seq#
FROM v$session s, v$process p
WHERE     p.addr = s.paddr
AND (   s.program LIKE '%MRP%'
OR s.program LIKE '%PR0%'
OR s.program LIKE '%DBW%'
OR s.program LIKE '%CKPT%')
order by s.process;

spool off
exit

oracle-dg-dg管理常用查询

检查dg主从库运行情况

in primary

in standby

开启同步

开启实时同步

取消同步

查看数据库状态

dg延迟情况的检查（在主库执行）

(on standby) Oracle日常运维之DG库日志应用慢参数调整实战:

dg库日志应用性能监控(on standby)

指定并行度恢复(on standby)

查看未应用的日志(on standby)

check_primary_status.sql

check_stby_status.sql

热门文章

最新文章

相关课程

相关电子书

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

oracle-dg-dg管理常用查询

检查dg主从库运行情况

in primary

in standby

开启同步

开启实时同步

取消同步

查看数据库状态

dg延迟情况的检查（在主库执行）

(on standby) Oracle日常运维之DG库日志应用慢参数调整实战:

dg库日志应用性能监控(on standby)

指定并行度恢复(on standby)

查看未应用的日志(on standby)

check_primary_status.sql

check_stby_status.sql

热门文章

最新文章

相关课程

相关电子书

推荐镜像