我要评论1、判断有无adg
show parameter dest show parameter log_archive_config select open_mode,protection_mode,database_role,switchover_status from v$database;
2、主库查询gap
set lines 222 pages 100 col error for a20 col gap_status for a20 col dest_name for a20 select dest_id,dest_name,status,type,error,gap_status from v$archive_dest_status where type='physical';
3、备库查询gap
select * from v$archive_gap; --无结果说明无gap
gap_status： no gap：无gap log switch gap : 有gap,日志还没有切换完成，不需要人工干预 resolvable gap：有gap,但是可以自动解决，不需要人工干预 unresolvable gap：有gap，不能自动解决，需要人工干预(从主库scp归档到备库并重新注册归档恢复) locally unresolvable gap：有gap，不能从本机获取日志自动解决，可以存其他机器获取日志自动解决
4、查看主库多节点最大日志编号、查备库recover到的日志序号
select thread#,max(sequence#) from gv$archived_log group by thread# order by 1; tail -1000f alert.log
5、主备库查看adg异常信息
select status,error,valid_type,valid_role,db_unique_name from v$archive_dest;
6、主库查找备库缺失的日志路径和名称：
set lines 200 pages 100 col name for a100 select name from v$archived_log where thread#=1 and dest_id=1 and sequence# between xxx and xxx;
7、判断未应用日志(备库查)
select thread#,0 not_send_count,not_apply_count
from (select s.thread#,
s.sequence# - ( select max(s.sequence#) seq
from gv$managed_standby i
where i.process='arch'
and i.thread# = s.thread#) not_apply_count
from gv$managed_standby s
where s.process = 'mrp0')
where not_apply_count >= 0;8、判断未传输日志(主库查)
with temp as
( select process,thread#,max(sequence#) seq
from gv$managed_standby
where process in ('arch','lns')
group by process,thread#)
select aa.thread#,bb.seq - aa.seq not_send_count,0 not_apply_count
from temp aa.temp bb
where aa.process='arch'
and bb.process='lns'
and aa.thread#=bb.thread#
and bb.seq - aa.seq >= 0
order by 1;9、查案备库未应用日志(在备库执行)
select dest_id,count(*) from v$archived_log where applied='no' group by thread#,dest_id order by 1; select thread#,count(*) from v$archived_log where applied='no' group by thread#,dest_id order by 1; select thread#,sequence#,applied from v$archived_log where applied='no'; select thread#,dest_id,to_char(first_time,'yyyy-mm-dd hh24:mi:ss'),name,applied,deleted,status from v$archived_log where applied='no' order by 3;
10、查询同步信息及相关进程(在备库执行)
set lines 222 pages 100 col client_pid for a10 select inst_id,thread#,process,status,client_pid,sequence# from gv$managed_standby;
主要看mrp0进程(日志应用进程)
mrp0有多个状态：
wait_for_gap：有gap，看看gap是否可以自动解决，不需要人工干预
applying_log：正在应用日志
wait_for_log：正在等待日志同步
如果wait_for_gap持续太久就需要关注了
如果wait_for_gap持续不长，可以看看主库当天归档量的大小
11、查询一周的归档量
select logtime,count(*),round(sum(sr)/1024/1024/1024,2) size_gb
from ( select to_char(first_time,'yyyy-mm-dd') as logtime
(a.blocks * a.block_size) sz
from gv$archived_log a
where a.dest_id = 1
and a.first_time > trunc(sysdate - 8))
group by logtime
order by logtime;12、关闭日志应用
alter database recover managed standby database cancel;
13、开启日志应用
alter database recover managed standby database parallel 16 using current logfile disconnect from session; alter database recover managed standby database using current logfile disconnect from session;
14、adg备库删除归档
1、一般备库应用完归档会自动删除已经应用的归档 2、备库归档不要使用强制删除(force),如果备库强制删除未应用的归档，此时主库相应的归档也会被策略删除，adg主备就不可用了 select count(*) from v$archive_gap; select count(*) from v$archived_log where applied='no';
15、停止主库向备库传输日志
在主库操作，将主库的"log_archive_dest_state_2"设置为defer,停止主库归档向备库传输，可缓解备库归档空间压力，等备库归档应用并自动删除足够多时，再将"log_archive_dest_state_2"设置为enable，开启主库对备库的传输 show parameter log_archive_dest_state_2 alter system set log_archive_dest_state_2=defer; alter system set log_archive_dest_state_2=enable;
16、手动应用归档日志(在备库执行)
asmcmd cd +arch/archivelog/2025-01-14/ cp thread_2_seq_458535 /tmp scp /tmp/thread_2_seq_458535 bakup:/tmp/ sqlplus / as sysdba alter database register logfile '/tmp/thread_2_seq_458535'; #或 rman target / catalog start with '/tmp/xxxx.dbf'
17、adg切换
adg切换之后，主备库不传输的情况，除了密码文件的问题，有可能是触发器的问题。（主库禁用系统触发器，等回切之后要改成true,一直是备库禁用系统触发器）
alter system set "_system_tring_enabled"=false;
adg主备切换，如果主库有多个备库，先验证每个备库是否都通，如果不能通的话需要把不通的备库的log_archive_dest_*参数置空，要不主库的归档一直发不到不通的备库，其他备库一直等待归档传输
到此这篇关于oracle adg相关查询的实现的文章就介绍到这了,更多相关oracle adg查询内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论