scn增量备份恢复dataguard从库
环境:
OS:Centos 7
DB:19.3.0.0
1.正常同步情况下的scn主从是一致的
主库查询:
SQL> select SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#,APPLIED,STATUS from v$archived_log t where name='tnsslavea';
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# APPLIED STATUS
--------- ------------- ------------ ---------- ----------
6 2039053 2040717 YES A
7 2040717 2207453 YES A
8 2207453 2298178 YES A
9 2298178 2458419 YES A
10 2458419 2634622 YES A
11 2634622 2634641 YES A
12 2634641 2634648 YES A
7 rows selected.
从库查询
SQL> select SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#,APPLIED,STATUS from v$archived_log;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# APPLIED STATUS
--------- ------------- ------------ ---------- ----------
6 2039053 2040717 YES A
7 2040717 2207453 YES A
8 2207453 2298178 YES A
9 2298178 2458419 YES A
10 2458419 2634622 IN-MEMORY A
11 2634622 2634641 YES A
12 2634641 2634648 YES A
7 rows selected.
2.停掉从库
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
3.主库上生成归档日志
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
4.然后将主库生成的归档日志删除掉(这个时候是不会同步到从库的)
SQL> !rm /u01/archivelog/1_13_1134611537.dbf
SQL> !rm /u01/archivelog/1_14_1134611537.dbf
SQL> !rm /u01/archivelog/1_15_1134611537.dbf
5.尝试启动从库
SQL> startup
ORACLE instance started.
Total System Global Area 3019895288 bytes
Fixed Size 8901112 bytes
Variable Size 620756992 bytes
Database Buffers 2382364672 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> SELECT * FROM V$ARCHIVE_GAP;
no rows selected
这个时候该查询没有数据,可以使用如下查询:
该视图查询不出来,可以尝试使用如下的语句查询(在从库上查询)
select USERENV('Instance'), high.thread#, low.lsq, high.hsq
from
(select a.thread#, rcvsq, min(a.sequence#)-1 hsq
from v$archived_log a,
(select lh.thread#, lh.resetlogs_change#, max(lh.sequence#) rcvsq
from v$log_history lh, v$database_incarnation di
where lh.resetlogs_time = di.resetlogs_time
and lh.resetlogs_change# = di.resetlogs_change#
and di.status = 'CURRENT'
and lh.thread# is not null
and lh.resetlogs_change# is not null
and lh.resetlogs_time is not null
group by lh.thread#, lh.resetlogs_change#
) b
where a.thread# = b.thread#
and a.resetlogs_change# = b.resetlogs_change#
and a.sequence# > rcvsq
group by a.thread#, rcvsq) high,
(select srl_lsq.thread#, nvl(lh_lsq.lsq, srl_lsq.lsq) lsq
from
(select thread#, min(sequence#)+1 lsq
from
v$log_history lh, x$kccfe fe, v$database_incarnation di
where to_number(fe.fecps) <= lh.next_change#
and to_number(fe.fecps) >= lh.first_change#
and fe.fedup!=0 and bitand(fe.festa, 12) = 12
and di.resetlogs_time = lh.resetlogs_time
and lh.resetlogs_change# = di.resetlogs_change#
and di.status = 'CURRENT'
group by thread#) lh_lsq,
(select thread#, max(sequence#)+1 lsq
from
v$log_history
where (select min( to_number(fe.fecps))
from x$kccfe fe
where fe.fedup!=0 and bitand(fe.festa, 12) = 12)
>= next_change#
group by thread#) srl_lsq
where srl_lsq.thread# = lh_lsq.thread#(+)
) low
where low.thread# = high.thread#
and lsq < = hsq
and hsq > rcvsq
主库查询归档应用情况
set linesize 1000;
column name format a48;
column SEQUENCE# format 99;
column APPLIED format a5;
column APPLIED format a5;
SQL> select name,SEQUENCE#,APPLIED,APPLIED from v$archived_log t order by SEQUENCE#;
NAME SEQUENCE# APPLI APPLI
------------------------------------------------ --------- ----- -----
/u01/archivelog/1_5_1134611537.dbf 5 NO NO
/u01/archivelog/1_6_1134611537.dbf 6 NO NO
tnsslavea 6 YES YES
/u01/archivelog/1_7_1134611537.dbf 7 NO NO
tnsslavea 7 YES YES
tnsslavea 8 YES YES
/u01/archivelog/1_8_1134611537.dbf 8 NO NO
/u01/archivelog/1_9_1134611537.dbf 9 NO NO
tnsslavea 9 YES YES
tnsslavea 10 YES YES
/u01/archivelog/1_10_1134611537.dbf 10 NO NO
NAME SEQUENCE# APPLI APPLI
------------------------------------------------ --------- ----- -----
/u01/archivelog/1_11_1134611537.dbf 11 NO NO
tnsslavea 11 YES YES
/u01/archivelog/1_12_1134611537.dbf 12 NO NO
tnsslavea 12 YES YES
/u01/archivelog/1_13_1134611537.dbf 13 NO NO
/u01/archivelog/1_14_1134611537.dbf 14 NO NO
/u01/archivelog/1_15_1134611537.dbf 15 NO NO
18 rows selected.
发现13 14和15是没有归档到从库的
在从库上查询
SQL> set linesize 1000;
SQL> column name format a48;
SQL> column SEQUENCE# format 99;
SQL> column APPLIED format a5;
SQL> column APPLIED format a5;
SQL> select name,SEQUENCE#,APPLIED,APPLIED from v$archived_log t order by SEQUENCE#;
NAME SEQUENCE# APPLI APPLI
------------------------------------------------ --------- ----- -----
/u01/archivelog/1_6_1134611537.dbf 6 YES YES
/u01/archivelog/1_7_1134611537.dbf 7 YES YES
/u01/archivelog/1_8_1134611537.dbf 8 YES YES
/u01/archivelog/1_9_1134611537.dbf 9 YES YES
/u01/archivelog/1_10_1134611537.dbf 10 YES YES
/u01/archivelog/1_11_1134611537.dbf 11 YES YES
/u01/archivelog/1_12_1134611537.dbf 12 YES YES
7 rows selected.
从库的归档只应用到了12
##################################恢复##########################################
1.设置源端log_archive_dest_state_2为defer
alter system set log_archive_dest_state_2=defer scope=both;
完成恢复后需要修改回来
alter system set log_archive_dest_state_2=enable scope=both;
2.备库上查询当前的scn
SQL> select to_char(current_scn) from v$database;
TO_CHAR(CURRENT_SCN)
----------------------------------------
2635413
或是查询从库已经应用了的最大归档日志记录的NEXT_CHANGE#
SQL> select t.NEXT_CHANGE#
2 from v$archived_log t
3 where t.SEQUENCE# =
4 (select max(SEQUENCE#) from v$archived_log where APPLIED = 'YES');
NEXT_CHANGE#
------------
2634648
本次实验实验第一种方法,即使用 2635413 在主库上进行增量备份
3.在主库上创建一个备库的控制文件
SQL>alter database create standby controlfile as '/home/oracle/standby.ctl';
4.确认主备GAP期间是否新增数据文件
SQL> select file# from v$datafile where creation_change# > =2635413;
no rows selected
主库根据备库scn号进行增量备份
rman target /
RMAN>run{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup INCREMENTAL from scn 2635413 database format '/u01/rmanbak/incre_%U';
release channel c1;
release channel c2;
}
注意：如果存在新增数据文件，备库恢复时需要先restore新添加的数据文件。
c.将增量备份和控制文件拷贝到备库上
5.主库拷贝增量备份和控制文件你至备库
scp /u01/rmanbak/incre* oracle@192.168.1.102:/u01/rmanbak/
scp /home/oracle/standby.ctl oracle@192.168.1.102:/home/oracle/
注意：确认备库的磁盘空间是否足够存放。
d.使用新的控制文件将备库启动到mount状态
6.备库关闭数据库实例，开启至nomount状态
SQL>sqlplus / as sysdba
SQL>shutdown immediate
SQL>startup nomount
7.备库恢复新的控制文件
rman target /
RMAN>restore controlfile from '/home/oracle/standby.ctl';
8.备库开启到mount状态
SQL>alter database mount;
增量备份注册到RMAN的catalog，取消日志应用，恢复增量备份
确认备库已关闭DG同步进程
9.备库rman注册增量备份文件
rman target /
RMAN>catalog start with '/u01/rmanbak/';
10.备库开启恢复增量备份--done
RMAN>recover database noredo;
11.开启备库的恢复进程
SQL>sqlplus / as sysdba
SQL>alter database open;
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/app/oracle/oradata/slavea/system01.dbf'
这个时候打开数据库报错误,先应用日志后面再打开
SQL>alter database recover managed standby database using current logfile disconnect from session;
12.主库重新激活同步
SQL>sqlplus / as sysdba
SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=enable;
13.打开数据库
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in
progress
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
说明:若goldengate是部署在adg的从库的话,做了增量恢复后，需要重新部署ogg
因为主库的归档13 14 15是丢失了的，那么从库也没有这几个归档日志,ogg找不到这些归档日志会报错误
2023-04-23 02:03:47 ERROR OGG-02828 Not able to establish initial position for sequence 13, rba 3,472,912, Could
not find archived log for sequence 13 thread 1 under default destinations SQL <SELECT name FROM v$archived_log W
HERE sequence# = :1 AND thread# = :2 AND resetlogs_id = :3 AND archived = 'YES' AND d
eleted = 'NO' >, error retrieving redo file name for sequence 13, archived = 1, use_alternate = 0.
