Oracle备库基于最小SCN恢复

• 主库：出现UNRESOLVABLE GAP的解决办法
• 主库做基于SCN的增量备份，恢复备库

1.备库操作
(1) 备库停止MRP，取消应用日志

alter database recover managed standby database cancel;

(2) 备库查看需要恢复的最小SCN，显示的是xxxxxx

col name format a60;
set lin 1000;
set pagesize 1000;
select min(f.fhscn) from x$kcvfh f,v$datafile d where f.hxfil=d.file# and d.enabled !='READ ONLY';

2.主库操作
(1) 主库rman基于该SCN做增量备份

rman target /

run{
    allocate channel c1 type disk;
    allocate channel c2 type disk;
    allocate channel c3 type disk;
    allocate channel c4 type disk;
    backup as compressed backupset incremental from scn 895235 database format '/backup/xman/incre202403_%U%t';
    backup current controlfile for standby format '/backup/xman/control.bak';
    release channel c1;
    release channel c2;
    release channel c3;
    release channel c4;
}

(2) 将备份文件拷贝到备库中

cd /backup/xman/
scp -r /backup/xman/* 备库IP:$PWD

3.备库还原
(1) 备库shutdown并启动备库到nomount状态，恢复控制文件

-- shutdown immediate;

-- startup nomount;

rman target /

restore standby controlfile from '/backup/xman/control.bak';

alter database mount;

(2) 备库注册备份集并应用增量备份

catalog start with '/backup/xman/';

recover database noredo;

• --noredo的意思是rman recover备份集而不恢复本地的归档

(3) clear standby redo

set pages 9999;
select 'ALTER DATABASE CLEAR LOGFILE GROUP '||GROUP#||';' from v$logfile where TYPE='STANDBY' group by GROUP# order by GROUP# ;

(4) 备库重新启动并应用日志

sqlplus / as sysdba

alter database recover managed standby database using current logfile disconnect from session;

(5) 检查主备是否正常传输

sqlplus / as sysdba

select ARCHIVED_THREAD#,ARCHIVED_SEQ#,APPLIED_THREAD#,APPLIED_SEQ#, DEST_NAME,error from v$archive_dest_status;

• 如果状态为异常，尝试关闭再开启

alter system set log_archive_dest_state_2='defer';

alter system set log_archive_dest_state_2='enable';

• -- 再检查状态

select ARCHIVED_THREAD#,ARCHIVED_SEQ#,APPLIED_THREAD#,APPLIED_SEQ#, DEST_NAME,error from v$archive_dest_status;

(6) 主库和备库的日志号是否一致

SELECT UNIQUE THREAD#, MAX(SEQUENCE#) OVER(PARTITION BY THREAD#) LAST FROM V$ARCHIVED_LOG;

• 检查备库日志应用是否存在间隙

SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM /* 检查是否有缺失日志 */ V$ARCHIVE_GAP;

select thread#,sequence#,first_time,next_time,applied from /* 备库查应用日志，主库不需要查 */ v$archived_log where applied='NO';

(7) 确认主备归档及日志应用日志一致后：关闭日志应用 --> open数据库 --> 开启日志应用

alter database recover managed standby database cancel;

alter database open;

alter database recover managed standby database using current logfile disconnect from session;

4.主库检查

select ARCHIVED_THREAD#,ARCHIVED_SEQ#,APPLIED_THREAD#,APPLIED_SEQ#, DEST_NAME,error from v$archive_dest_status;

SELECT UNIQUE THREAD#, MAX(SEQUENCE#) OVER(PARTITION BY THREAD#) LAST FROM V$ARCHIVED_LOG;