增量备份恢复DATAGUARD(出现gap并后续有新增数据文件,主从数据文件路径一致)
1 环境
| 主库 | 192.168.1.118 |
| 备库 | 192.168.1.85 |
| OS | Centos7 |
| DB | 11.2.0.4 |
2.现状
从库无法启动:
3 查询主备库数据文件路径(避免主从库数据路径不一致需要转换)
主库
|
SQL> set linesize 1000; SQL> select FILE#,name from v$datafile;
FILE# NAME ---------- ---------------------------------------------- 1 /u01/app/oracle/oradata/slnngk/system01.dbf 2 /u01/app/oracle/oradata/slnngk/sysaux01.dbf 3 /u01/app/oracle/oradata/slnngk/undotbs01.dbf 4 /u01/app/oracle/oradata/slnngk/users01.dbf 5 /u01/app/oracle/oradata/slnngk/tps_data01.dbf 6 /u01/app/oracle/oradata/slnngk/mrkt.dbf 7 /u01/app/oracle/oradata/slnngk/tps_data02.dbf 8 /u01/app/oracle/oradata/tps_data02.dbf 9 /u01/app/oracle/oradata/slnngk/tps_data04.dbf 10 /u01/app/oracle/oradata/tps_data05.dbf 11 /u01/app/oracle/oradata/tps_data06.dbf 12 /u01/app/oracle/oradata/tps_data07.dbf 13 /u01/app/oracle/oradata/tps_data08.dbf 14 /u02/oradata/tps_data09.dbf 15 /u02/oradata/tps_data10.dbf 16 /u01/app/oracle/oradata/tps_data11.dbf 17 /u02/oradata/tps_data12.dbf 18 /u01/app/oracle/oradata/slnngk/tps_data13.dbf 19 /u01/app/oracle/oradata/tps_data14.dbf 20 /u01/app/oracle/oradata/tps_data15.dbf 21 /u01/app/oracle/oradata/tps_data16.dbf 22 /u02/oradata/tps_data17.dbf 22 rows selected. |
从库
|
SQL> set linesize 1000; SQL> select FILE#,name from v$datafile;
FILE# NAME ---------- ---------------------------------------------- 1 /u01/app/oracle/oradata/slnngk/system01.dbf 2 /u01/app/oracle/oradata/slnngk/sysaux01.dbf 3 /u01/app/oracle/oradata/slnngk/undotbs01.dbf 4 /u01/app/oracle/oradata/slnngk/users01.dbf 5 /u01/app/oracle/oradata/slnngk/tps_data01.dbf 6 /u01/app/oracle/oradata/slnngk/mrkt.dbf 7 /u01/app/oracle/oradata/slnngk/tps_data02.dbf 8 /u01/app/oracle/oradata/tps_data02.dbf 9 /u01/app/oracle/oradata/slnngk/tps_data04.dbf 10 /u01/app/oracle/oradata/tps_data05.dbf 11 /u01/app/oracle/oradata/tps_data06.dbf 12 /u01/app/oracle/oradata/tps_data07.dbf 13 /u01/app/oracle/oradata/tps_data08.dbf 14 /u02/oradata/tps_data09.dbf 15 /u02/oradata/tps_data10.dbf 16 /u01/app/oracle/oradata/tps_data11.dbf 17 /u02/oradata/tps_data12.dbf 18 /u01/app/oracle/oradata/slnngk/tps_data13.dbf 19 /u01/app/oracle/oradata/tps_data14.dbf 20 /u01/app/oracle/oradata/tps_data15.dbf |
4.备库停掉日志应用
SQL> alter database recover managed standby database cancel;
Database altered.
这个时候相应的进程已经关闭，如下
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CONNECTED
ARCH CONNECTED
ARCH CLOSING
ARCH CLOSING
5.找到主库需要增量备份的SCN
备库执行
| SQL> select current_scn from v$database; CURRENT_SCN ----------- 142237796 |
|
SQL> select min(checkpoint_change#) from v$datafile_header where file# not in (select file# from v$datafile where enabled = 'READ ONLY');
MIN(CHECKPOINT_CHANGE#) ----------------------- 142237797 |
这里我们取最小的那个值:= 142237796
说明:上面一个为控制文件中记录的SCN号,另一个为数据文件头记录的SCN号,我们需要选择较小SCN号(142237796)在主库上进行增量备份
有些场景如下查询发现显示0或是空,这种情况，以第一个查询获取到的值为准
| SQL> select min(checkpoint_change#) from v$datafile_header where file# not in (select file# from v$datafile where enabled = 'READ ONLY'); MIN(CHECKPOINT_CHANGE#) ----------------------- 0 |
6.检查scn之后是否添加有新的数据文件
主库执行
|
SQL> set linesize 1000; SQL> select FILE#,name from v$datafile where CREATION_CHANGE#> =142237796;
FILE# NAME ---------- ---------------------------------------------------------- 21 /u01/app/oracle/oradata/tps_data16.dbf 22 /u02/oradata/tps_data17.dbf |
7.在主库上进行增量备份
|
run{ allocate channel c1 device type disk; allocate channel c2 device type disk; backup as compressed backupset datafile 21,22 format '/home/oracle/rman_backup/datafile_%U.bak' tag 'datafile'; backup as compressed backupset incremental from scn 142237796 database format '/home/oracle/rman_backup/zengliang_%u.bak' tag 'zengliang'; release channel c1; release channel c2; } |
8.在主库上生成standby控制文件
RMAN> backup current controlfile for standby format '/home/oracle/rman_backup/standby_ctl.bak';
9.拷贝上面备份的文件到备库
|
scp /home/oracle/rman_backup/datafile_o9uj36nd_1_1.bak oracle@192.168.1.85:/tmp/rman_bak scp /home/oracle/rman_backup/datafile_oauj36nd_1_1.bak oracle@192.168.1.85:/tmp/rman_bak scp /home/oracle/rman_backup/zengliang_obuj36ne.bak oracle@192.168.1.85:/tmp/rman_bak scp /home/oracle/rman_backup/zengliang_ocuj36ne.bak oracle@192.168.1.85:/tmp/rman_bak scp /home/oracle/rman_backup/zengliang_oduj36nm.bak oracle@192.168.1.85:/tmp/rman_bak scp /home/oracle/rman_backup/standby_ctl.bak oracle@192.168.1.85:/tmp/rman_bak |
10.恢复控制文件并注册备份集
| RMAN> shutdown immediate; RMAN> startup nomount; RMAN> restore standby controlfile from '/tmp/rman_bak/standby_ctl.bak'; RMAN> alter database mount; RMAN> catalog start with '/tmp/rman_bak'; |
这里需要先恢复控制文件再做下面的恢复，若是先做恢复的话，新增的数据文件在原来的控制文件里是没有记录的
11.从库还原新增的数据文件
RMAN>restore datafile 21;
RMAN>restore datafile 22;
12.使用增量recover备库
mount状态下操作
SQL> connect / as sysdba
Connected.
SQL> select status from v$instance;
STATUS
------------
MOUNTED
RMAN> recover database noredo;
13.清空日志组
SQL> select status from v$instance;
STATUS
------------
MOUNTED
将所有的日志组进行清空掉
|
SQL> select group#,bytes from v$log;
GROUP# BYTES ---------- ---------- 1 52428800 3 52428800 2 52428800
alter database clear logfile group 1; alter database clear logfile group 2; alter database clear logfile group 3; |
查看是否采用了standby log
|
SQL> Select * From v$standby_log; no rows selected |
注：如果采用了standby log模式，不需要清空，如果清空会出现
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
ALTER DATABASE CLEAR LOGFILE GROUP 1
*
ERROR at line 1:
ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1: '/u01/oradata/badly9/redo01.log'
说明：如果没有采用standby log模式，有几组需要清空几组
14.从库启动监听器(没有启动的话)
lsnrctl start
15.从库开始应用日志
|
SQL> select status from v$instance;
STATUS ------------ MOUNTED
SQL> alter database recover managed standby database using current logfile disconnect from session; alter database recover managed standby database using current logfile disconnect from session * ERROR at line 1: ORA-38500: USING CURRENT LOGFILE option not available without standby redo logs |
需要添加standby日志
|
SQL> alter database add standby logfile group 4 '/u01/app/oracle/oradata/slnngkdg/standby_redo01.log' size 50m reuse; SQL> alter database add standby logfile group 5 '/u01/app/oracle/oradata/slnngkdg/standby_redo02.log' size 50m reuse; SQL> alter database add standby logfile group 6 '/u01/app/oracle/oradata/slnngkdg/standby_redo03.log' size 50m reuse; |
再次日志应用
SQL> alter database recover managed standby database using current logfile disconnect from session;
16.打开数据库
等日志应用同步完成后退出日志应用，然后打开数据库,否则打开数据库的时候也会先应用日志,等日志应用追上来了后才能打开数据库
alter database recover managed standby database cancel;
alter database open;
然后启用日志应用
alter database recover managed standby database using current logfile disconnect from session;
17.再次检查是否有gap日志
从库执行:
SQL> select thread#, low_sequence#, high_sequence# from v$archive_gap;
no rows selected
18.主库尝试日志切换
同步正常后第一次要做一次日志切换
SQL> alter system switch logfile;
19.从库查看进程状态
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
RFS IDLE
RFS IDLE
RFS IDLE
MRP0 APPLYING_LOG
8 rows selected.
