ADG 只读备库和读写快照库 状态互相切换
文章目录
一、只读备库切快照库：
1、查看备库状态：
export ORACLE_SID = evans
sqlplus / as sysdba
set linesize 300
col DATABASE_ROLE for a20
col PROTECTION_MODE for a20
col PROTECTION_LEVEL for a20
col OPEN_MODE for a25
col FLASHBACK_ON for a10
col LOG_MODE for a20
col SWITCHOVER_STATUS for a15
select a.inst_id,a.DATABASE_ROLE,a.PROTECTION_MODE,a.PROTECTION_LEVEL,a.OPEN_MODE from gv$database a ;
INST_ID DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE
1 PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ ONLY WITH APPLY
2、查看备库延迟情况：
select l.thread# ,l.maxonlineseq, t.maxarcseq, t.dest_id, t.archived,t.applied,t.archived - t.applied gap
from (select thread#,
dest_id,
max(sequence#) maxarcseq,
max(decode(archived, 'YES', sequence#, 1)) archived,
max(decode(applied, 'YES', sequence#, 1)) applied
from v$archived_log
group by thread#,dest_id) t,
(select thread#,max(sequence#) maxonlineseq from v$log group by thread#) l
where t.thread#=l.thread#
order by l.thread#,t.dest_id;
THREAD# MAXONLINESEQ MAXARCSEQ DEST_ID ARCHIVED APPLIED GAP
1 161453 161452 1 161452 161452 0
1 161453 161426 3 161426 161426 0
2 162363 162362 1 162362 162361 1
2 162363 162135 3 162135 162135 0
3、取消备库应用：
alter database recover managed standby database cancel;
4、检查检查闪回是否开启
select flashback_on from v$database;
FLASHBACK_ON
NO
查看 recovery 参数配置：
show parameter recovery
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_recovery_file_dest string /backup/flashback
db_recovery_file_dest_size big integer 1024G
recovery_parallelism integer 0
5、设置闪回路径 db_recovery_file_dest
alter system set db_recovery_file_dest_size = 1024G scope = both;
alter system set db_recovery_file_dest ='/backup/oracle/flashback/evans' scope = both;
6、evans 切换为快照库
shutdown immediate;
startup mount;
alter database convert to snapshot standby;
alter database open;
7、检查状态：
set linesize 300
col DATABASE_ROLE for a20
col PROTECTION_MODE for a20
col PROTECTION_LEVEL for a20
col OPEN_MODE for a25
col FLASHBACK_ON for a10
col LOG_MODE for a20
col SWITCHOVER_STATUS for a15
select database_role, open_mode, protection_mode, protection_level, flashback_on, force_logging, log_mode, switchover_status from v$database;
col archived_day for a20;
set linesize 300;
select to_char(completion_time,'yyyymmdd') archived_day, count(1) nodelarch_nums, round(sum(blocks * block_size) / 1024 / 1024 / 1024,0) total_gb from v$archived_log
where completion_time > sysdate - 2
group by to_char(completion_time,'yyyymmdd')
order by to_char(completion_time,'yyyymmdd');
select thread#,
max(sequence#),
to_char(completion_time,'yyyymmdd') archived_day from v$archived_log where to_char(completion_time,'yyyymmdd') = to_char(sysdate,'yyyymmdd') group by thread#, to_char(completion_time,'yyyymmdd')
order by thread#;
二、快照库切换只读备库：
1、检查数据库状态：
set linesize 300
col DATABASE_ROLE for a20
col PROTECTION_MODE for a20
col PROTECTION_LEVEL for a20
col OPEN_MODE for a25
col FLASHBACK_ON for a10
col LOG_MODE for a20
col SWITCHOVER_STATUS for a15
select database_role,open_mode,protection_mode,protection_level,flashback_on,force_logging,log_mode,switchover_status from v$database;
col archived_day for a20;
set linesize 300;
select to_char(completion_time,'yyyymmdd') archived_day,count(1) nodelarch_nums, round(sum(blocks * block_size) / 1024 / 1024 / 1024,0) total_gb from v$archived_log
where completion_time > sysdate - 2
group by to_char(completion_time,'yyyymmdd')
order by to_char(completion_time,'yyyymmdd');
select thread#,max(sequence#),to_char(completion_time,'yyyymmdd') archived_day from v$archived_log where to_char(completion_time,'yyyymmdd') = to_char(sysdate,'yyyymmdd') group by thread#,to_char(completion_time,'yyyymmdd') order by thread#;
2、停止快照库：
alter system switch logfile;
alter system checkpoint;
shutdown immediate;
3、切换只读备：
startup mount;
alter database convert to physical standby;
shutdown immediate;
startup nomount;
alter database mount standby database;
4、关闭闪回：
alter database flashback off;
alter system set db_recovery_file_dest='' scope=both;
5、重启备库
shutdown immediate;
startup;
6、检查日志延迟情况
select l.thread# ,l.maxonlineseq, t.maxarcseq, t.dest_id, t.archived,t.applied,t.archived - t.applied gap
from (select thread#,
dest_id,
max(sequence#) maxarcseq,
max(decode(archived, 'YES', sequence#, 1)) archived,
max(decode(applied, 'YES', sequence#, 1)) applied
from v$archived_log
group by thread#,dest_id) t,
(select thread#,max(sequence#) maxonlineseq from v$log group by thread#) l
where t.thread#=l.thread#
order by l.thread#,t.dest_id;
7、查看归档位置 日志情况
set linesize 500
set pagesize 30
col value for a90
col name for a50
select name, value
from v$parameter
where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2','archive_lag_target','log_archive_min_succeed_dest',
'log_archive_dest_state_1','log_archive_dest_state_2', 'remote_login_passwordfile','log_archive_trace',
'log_archive_format','log_archive_max_processes','fal_server','db_file_name_convert','fal_client',
'log_file_name_convert', 'standby_file_management','LOG_ARCHIVE_DEST_STATE_2') order by name;
8、启动日志应用进程，同步生产主库数据
--alter database recover managed standby database disconnect from session;
alter database recover managed standby database using current logfile disconnect from session;
9、多次重复执行检查主备同步状态，传输日志和应用日志时间
SELECT PROCESS, STATUS,SEQUENCE#,thread# FROM V$MANAGED_STANDBY;
set linesize 150;
set pagesize 20;
column name format a13;
column value format a20;
column unit format a30;
column TIME_COMPUTED format a30;
select name,value,unit,time_computed from v$dataguard_stats ;
NAME VALUE UNIT TIME_COMPUTED
------------------------------ -------------------- ------------------------------ ----------------------------------------
transport lag +00 00:00:00 day(2) to second(0) interval 11/29/2024 16:02:37
apply lag +00 00:00:00 day(2) to second(0) interval 11/29/2024 16:02:37
apply finish time day(2) to second(3) interval 11/29/2024 16:02:37
estimated startup time 14 second 11/29/2024 16:02:37
select l.thread# ,l.maxonlineseq, t.maxarcseq, t.dest_id, t.archived,t.applied,t.archived - t.applied gap
from (select thread#,
dest_id,
max(sequence#) maxarcseq,
max(decode(archived, 'YES', sequence#, 1)) archived,
max(decode(applied, 'YES', sequence#, 1)) applied
from v$archived_log
group by thread#,dest_id) t,
(select thread#,max(sequence#) maxonlineseq from v$log group by thread#) l
where t.thread#=l.thread#
order by l.thread#,t.dest_id;