备案控制台
开发者社区 数据库 文章 正文

oracle-dg-dg管理常用查询

2022-07-06 317
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
日志服务 SLS，月写入数据量 50GB 1个月
简介：

检查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
数据库
关键词：
Oracle数据库一键上云查询
相关实践学习
日志服务之数据清洗与入湖
本教程介绍如何使用日志服务接入NGINX模拟数据，通过数据加工对数据进行清洗并归档至OSS中进行存储。
lemotree
目录
相关文章
ZPY0821
|
2月前
|
SQL Oracle 关系型数据库
Oracle查询优化-查询只包含数字或字母的数据
【2月更文挑战第4天】【2月更文挑战第10篇】查询只包含数字或字母的数据
ZPY0821
220 1
讓丄帝愛伱
|
8月前
|
存储 Oracle 关系型数据库
Oracle 代码异常查询（五）
Oracle 代码异常查询
讓丄帝愛伱
180 0
Zh.晨光
|
2月前
|
SQL Oracle 关系型数据库
JAVAEE框架数据库技术之12_oracle常用函数和高级查询子查询
JAVAEE框架数据库技术之12_oracle常用函数和高级查询子查询
Zh.晨光
82 0
JAVAEE框架数据库技术之12_oracle常用函数和高级查询子查询
芯在这
|
2月前
|
分布式计算 DataWorks 关系型数据库
DataWorks产品使用合集之在 DataWorks 中，使用Oracle作为数据源进行数据映射和查询，如何更改数据源为MaxCompute或其他类型
DataWorks作为一站式的数据开发与治理平台，提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案，帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述，涵盖数据处理的各个环节。
芯在这
41 1
讓丄帝愛伱
|
8月前
|
SQL Oracle 关系型数据库
Oracle 代码异常查询（九）
Oracle 代码异常查询
讓丄帝愛伱
216 0
讓丄帝愛伱
|
8月前
|
SQL 存储 Oracle
Oracle 代码异常查询（三）
Oracle 代码异常查询
讓丄帝愛伱
205 0
机器学习Zero
|
2月前
|
SQL Oracle 关系型数据库
Oracle系列之八：SQL查询
Oracle系列之八：SQL查询
机器学习Zero
43 0
_长银_
|
2月前
|
Oracle 关系型数据库
Oracle 递归查询
Oracle 递归查询
_长银_
19 0
小小工匠
|
2月前
|
SQL Oracle 关系型数据库
Oracle高级查询
Oracle高级查询
小小工匠
84 1
飞翔的佩奇
|
2月前
|
SQL Oracle 关系型数据库
oracle查询数据库参数sql语句
oracle查询数据库参数sql语句
飞翔的佩奇
52 0