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 = 'PRIMARY'
then
case when gap_status <> 'NO GAP' then '异常' else '正常' end
else
'不适用'
end chkresult,
case when cnt>1 and database_role = 'PRIMARY'
then
gap_status
else
'非主库'
end chkvalue,
'NO GAP' limvalue
from dest,arch_status,dbinfo;
4、备库日志应用进程状态检查
col chkvalue for a20
col name for a20
col chkreault for a20
col limvalue for a20
set lines 200
select name
,case when database_role <> 'PRIMARY' then
case when cnt > 0 then '正常' else '异常' end
else '不适用' end chkresult
,case when database_role <> 'PRIMARY' then cnt || '个应用进程'
else '非备库' end chkvalue
,'>=1个应用进程' limvalue
from (
select count(*) cnt from gv$managed_standby
where process like 'MRP%' and status in ('WAIT_FOR_LOG','APPLYING_LOG')),
(select name,database_role from v$database);
5、备库日志 GAP 检查
col chkvalue for a20
col name for a20
col chkreault for a20
col limvalue for a20
set lines 200
select name
,case when database_role <> 'PRIMARY' then
case when cnt > 0 then '异常' else '正常' end
else '不适用' end chkresult
,case when database_role <> 'PRIMARY' then cnt || '个日志GAP'
else '非备库' end chkvalue
,'0个日志GAP' limvalue
from (
select count(*) cnt from v$archive_gap),
(select name,database_role from v$database);
6、备库日志应用时延检查
col chkvalue for a20
col name for a20
col chkreault for a20
col limvalue for a20
set lines 200
select name
,case when database_role <> 'PRIMARY' then
case when delayhour > value then '异常' else '正常' end
else '不适用' end chkresult
,case when database_role <> 'PRIMARY' then delayhour || '秒'
else '非备库' end chkvalue
,value || '秒' limvalue
from (
select
regexp_substr(substr(value,2,10),'[^ ]+',1,1)*24*60*60
+regexp_substr(regexp_substr(substr(value,2,20),'[^ ]+',1,2),'[^:]+',1,1)*60*60
+regexp_substr(regexp_substr(substr(value,2,20),'[^ ]+',1,2),'[^:]+',1,2)*60
+regexp_substr(regexp_substr(substr(value,2,20),'[^ ]+',1,2),'[^:]+',1,3) delayhour
from v$dataguard_stats where name='apply lag'
),
(select name,database_role from v$database),
(select max(delay_mins)*60+900 value from v$archive_dest);
7、备库 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
select name
,case when database_role <> 'PRIMARY' then
case when cnt > 0 then '异常' else '正常' end
else '不适用' end chkresult
,case when database_role <> 'PRIMARY' then cnt || '个错误'
else '非备库' end chkvalue
,'0 个错误' limvalue
from (
select count(*) cnt from v$dataguard_status
where timestamp >= sysdate - 1 and severity in ('Error','fatal')),
(select name,database_role from v$database);
示例:
SQL> -- 主库检查
SQL> -- 1、主库传输状态检查
SQL> col chkvalue for a20
SQL> col name for a20
SQL> col chkreault for a20
SQL> col limvalue for a20
SQL> set lines 200
SQL>
SQL> with dest as
2 (select count(*) destcnt from v$archive_dest where target='STANDBY' and status= 'VALID'),
3 dbinfo as
4 (select name,database_role from v$database)
5 select name
6 ,case when database_role = 'PRIMARY' then
7 case when destcnt > 0 then '正常' else '异常' end
8 else '不适用' end chkresult
9 ,case when database_role = 'PRIMARY' then destcnt || '个传输进程'
10 else '非主库' end chkvalue
11 ,'>=1 个传输进程' limvalue
12 from dest,dbinfo;
NAME CHKRESULT CHKVALUE LIMVALUE
-------------------- --------------------------- -------------------- --------------------
FWEIDB 正常 1个传输进程 >=1 个传输进程
SQL> -- 2、主库 DG 状态检查
SQL> with dest as
2 (select count(*) destcnt from v$dataguard_status where timestamp >= sysdate - 1 and severity in ('Error','fatal')),
3 dbinfo as
4 (select name,database_role from v$database)
5 select name
6 ,case when database_role = 'PRIMARY' then
7 case when destcnt > 0 then '异常' else '正常' end
8 else '不适用' end chkresult
9 ,case when database_role = 'PRIMARY' then destcnt || '个错误'
10 else '非主库' end chkvalue
11 ,'0 个错误' limvalue
12 from dest,dbinfo;
NAME CHKRESULT CHKVALUE LIMVALUE
-------------------- --------------------------- -------------------- --------------------
FWEIDB 异常 23个错误 0 个错误
SQL> -- 3、主库 GAP 状态检查
SQL> with dbinfo as
2 (select db_unique_name name,database_role from v$database),
3 dest as
4 (select count(*) cnt from v$dataguard_config),
5 arch_status as
6 (select gap_status from v$archive_dest_status where dest_id=2)
7 select name
8 ,case when cnt>1 and database_role = 'PRIMARY' then
9 case when gap_status <> 'NO GAP' then '异常' else '正常' end
10 else '不适用' end chkresult
11 ,case when cnt>1 and database_role = 'PRIMARY' then gap_status
12 else '非主库' end chkvalue
13 ,'NO GAP' limvalue
14 from dest,arch_status,dbinfo;
NAME CHKRESULT CHKVALUE LIMVALUE
-------------------- --------------------------- -------------------- --------------------
fweidb 正常 NO GAP NO GAP
SQL> -- 备库检查
SQL> -- 4、备库日志应用进程状态检查
SQL> col chkvalue for a20
SQL> col name for a20
SQL> col chkreault for a20
SQL> col limvalue for a20
SQL> set lines 200
SQL>
SQL> select name
2 ,case when database_role <> 'PRIMARY' then
3 case when cnt > 0 then '正常' else '异常' end
4 else '不适用' end chkresult
5 ,case when database_role <> 'PRIMARY' then cnt || '个应用进程'
6 else '非备库' end chkvalue
7 ,'>=1个应用进程' limvalue
8 from (
9 select count(*) cnt from gv$managed_standby
10 where process like 'MRP%' and status in ('WAIT_FOR_LOG','APPLYING_LOG')),
11 (select name,database_role from v$database);
NAME CHKRESULT CHKVALUE LIMVALUE
-------------------- --------------------------- -------------------- --------------------
FWEIDB 正常 1个应用进程 >=1个应用进程
SQL> -- 5、备库日志 GAP 检查
SQL> select name
2 ,case when database_role <> 'PRIMARY' then
3 case when cnt > 0 then '异常' else '正常' end
4 else '不适用' end chkresult
5 ,case when database_role <> 'PRIMARY' then cnt || '个日志GAP'
6 else '非备库' end chkvalue
7 ,'0个日志GAP' limvalue
8 from (
9 select count(*) cnt from v$archive_gap),
10 (select name,database_role from v$database);
NAME CHKRESULT CHKVALUE LIMVALUE
-------------------- --------------------------- -------------------- --------------------
FWEIDB 正常 0个日志GAP 0个日志GAP
SQL> -- 6、备库日志应用时延检查
SQL> select name
2 ,case when database_role <> 'PRIMARY' then
3 case when delayhour > value then '异常' else '正常' end
4 else '不适用' end chkresult
5 ,case when database_role <> 'PRIMARY' then delayhour || '秒'
6 else '非备库' end chkvalue
7 ,value || '秒' limvalue
8 from (
9 select
10 regexp_substr(substr(value,2,10),'[^ ]+',1,1)*24*60*60
11 +regexp_substr(regexp_substr(substr(value,2,20),'[^ ]+',1,2),'[^:]+',1,1)*60*60
12 +regexp_substr(regexp_substr(substr(value,2,20),'[^ ]+',1,2),'[^:]+',1,2)*60
13 +regexp_substr(regexp_substr(substr(value,2,20),'[^ ]+',1,2),'[^:]+',1,3) delayhour
14 from v$dataguard_stats where name='apply lag'
15 ),
16 (select name,database_role from v$database),
17 (select max(delay_mins)*60+900 value from v$archive_dest);
NAME CHKRESULT CHKVALUE LIMVALUE
-------------------- --------------------------- -------------------- --------------------
FWEIDB 正常 0秒 900秒
SQL> -- 7、备库 DG 状态检查
SQL> select name
2 ,case when database_role <> 'PRIMARY' then
3 case when cnt > 0 then '异常' else '正常' end
4 else '不适用' end chkresult
5 ,case when database_role <> 'PRIMARY' then cnt || '个错误'
6 else '非备库' end chkvalue
7 ,'0 个错误' limvalue
8 from (
9 select count(*) cnt from v$dataguard_status
10 where timestamp >= sysdate - 1 and severity in ('Error','fatal')),
11 (select name,database_role from v$database);
NAME CHKRESULT CHKVALUE LIMVALUE
-------------------- --------------------------- -------------------- --------------------
FWEIDB 正常 0个错误 0 个错误
SQL>