环境:
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.添加数据文件
alter tablespace TBS_OGG add datafile '/u01/app/oracle/oradata/SLNNGK/tps_ogg02.dbf' size 10m;
6.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;
column standby_dest format a5;
SQL> select name,SEQUENCE#,APPLIED,standby_dest from v$archived_log t order by SEQUENCE#,name;
NAME SEQUENCE# APPLI standby_dest
------------------------------------------------ --------- ----- -----
/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是没有归档到从库的
在从库上查询
set linesize 1000;
column name format a48;
column SEQUENCE# format 99;
column APPLIED format a5;
column APPLIED format a5;
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)
----------------------------------------
2847423
或是查询从库已经应用了的最大归档日志记录的NEXT_CHANGE#
select t.NEXT_CHANGE#
from v$archived_log t
where t.SEQUENCE# =
(select max(SEQUENCE#) from v$archived_log where APPLIED = 'YES');
NEXT_CHANGE#
------------
2834318
本次实验实验第一种方法,即使用 2847423 在主库上进行增量备份
3.在主库上创建一个备库的控制文件
SQL>alter database create standby controlfile as '/home/oracle/standby.ctl';
4.确认主备GAP期间是否新增数据文件
SQL> select file#,name from v$datafile where creation_change# > =2847423;
FILE# NAME
---------- ------------------------------------------------
2 /u01/app/oracle/oradata/SLNNGK/tps_ogg02.dbf
主库根据备库scn号进行增量备份+新增数据文件备份
rman target /
RMAN>run{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup datafile 2 format '/u01/rmanbak/datafile_%U.bak' tag 'datafile';
backup INCREMENTAL from scn 2847423 database format '/u01/rmanbak/incre_%U';
release channel c1;
release channel c2;
}
5.主库拷贝增量备份和控制文件你至备库
scp /u01/rmanbak/incre* oracle@192.168.1.102:/u01/rmanbak/
scp /u01/rmanbak/datafile* oracle@192.168.1.102:/u01/rmanbak/
scp /home/oracle/standby.ctl oracle@192.168.1.102:/home/oracle/
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;
9.备库rman注册增量备份文件
rman target /
RMAN>catalog start with '/u01/rmanbak/';
10.备库开启恢复增量备份-
RMAN>restore datafile 2;
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;