Oracle Data Guard 状态监控项检查

最新推荐文章于 2025-06-28 00:49:15 发布

欢心之宇

最新推荐文章于 2025-06-28 00:49:15 发布

阅读量736

点赞数 5

CC 4.0 BY-SA版权

文章标签： oracle 数据库 sql

本文链接：https://blog.csdn.net/weixin_60275015/article/details/136373842

0、查询当前字符集：

SQL> col parameter for a40
SQL> col value for a40
SQL> select * from nls_database_parameters where parameter like '%SET' or parameter like '%LANGUAGE';

PARAMETER VALUE
---------------------------------------- ----------------------------------------
NLS_DATE_LANGUAGE AMERICAN
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET AL32UTF8
NLS_LANGUAGE AMERICAN

SQL>

设置环境变量（不设置则查询结果中的中文显示为问号“？”）：

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

1、主库传输状态检查

col chkvalue for a20
col name for a20
col chkreault for a20
col limvalue for a20
set lines 200

with dest as (select count(*) destcnt from v$archive_dest where target='STANDBY' and status= 'VALID'),
dbinfo as (select name,database_role from v$database)
select name,
   case when database_role = 'PRIMARY'
       then
           case when destcnt > 0 then '正常' else '异常' end
       else
           '不适用'
   end chkresult,
   case when database_role = 'PRIMARY'
       then
           destcnt || '个传输进程'
       else
           '非主库'
   end chkvalue,
   '>=1 个传输进程' limvalue
from dest,dbinfo;

2、主库 DG 状态检查（检查一天内有无报错，可以酌情修改，比如 30 分钟前：timestamp >= sysdate - 30/1440 ）

col chkvalue for a20
col name for a20
col chkreault for a20
col limvalue for a20
set lines 200

with dest as (select count(*) destcnt from v$dataguard_status where timestamp >= sysdate - 1 and severity in ('Error','fatal')),
dbinfo as (select name,database_role from v$database)
select name,
   case when database_role = 'PRIMARY'
       then
           case when destcnt > 0 then '异常' else '正常' end
       else
           '不适用'
   end chkresult,
   case when database_role = 'PRIMARY'
       then
           destcnt || '个错误'
       else '非主库'
   end chkvalue,
   '0 个错误' limvalue
from dest,dbinfo;

3、主库 GAP 状态检查

col chkvalue for a20
col name for a20
col chkreault for a20
col limvalue for a20
set lines 200

with dbinfo as (select db_unique_name name,database_role from v$database),
dest as (select count(*) cnt from v$dataguard_config),
arch_status as (select gap_status from v$archive_dest_status where dest_id=2)
select name,
case when cnt>1 and database_role = 'P