场景:
备库执行日志应用出现如下报错:
- Thu Mar 29 11:21:45 2018
- FAL[client]: Failed to request gap sequence
- GAP - thread 1 sequence 184-185
- DBID 1484954774 branch 960494131
- FAL[client]: All defined FAL servers have been attempted.
- ------------------------------------------------------------
- Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
- parameter is defined to a value that's sufficiently large
- enough to maintain adequate log switch information to resolve
- archivelog gaps.
查询缺失的归档日志:
- SQL> select * from v$archive_gap;
- THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
- ---------- ------------- --------------
- 1 183 185
去主库查看归档, 发现归档已丢失
- SQL> archive log list;
- Database log mode Archive Mode
- Automatic archival Enabled
- Archive destination /u01/archivelog
- Oldest online log sequence 186
- Next log sequence to archive 188
- Current log sequence 188
- [Oracle@prim archivelog]$ pwd
- /u01/archivelog
- [oracle@prim archivelog]$ ls -ltr
- total 1964
- -rw-r----- 1 oracle oinstall 74752 Mar 29 11:17 1_186_960494131.dbf
- -rw-r----- 1 oracle oinstall 1930240 Mar 29 11:17 1_187_960494131.dbf
下面开始使用 RMAN 进行基于 SCN 增量备份恢复的方式进行恢复, 参考文档 ID 836986.1
1. 取消备库日志应用
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;
2. 在备库上确定需要开始增量备份的 SCN
- SQL> SELECT CURRENT_SCN FROM V$DATABASE;
- CURRENT_SCN
- -----------
- 3505254
- 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#)
- -----------------------
- (如果结果为空, 重启备库到 mount 状态)
- SQL> shutdown immediate;
- Database closed.
- Database dismounted.
- ORACLE instance shut down.
- SQL> startup mount;
- ORACLE instance started.
- Total System Global Area 1002127360 bytes
- Fixed Size 2259440 bytes
- Variable Size 285214224 bytes
- Database Buffers 708837376 bytes
- Redo Buffers 5816320 bytes
- Database mounted.
- 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#)
- -----------------------
- 3505255
选择以上结果中最小 SCN 作为增量备份的起点 (此处是 350524).
3. 在主库上进行基于 SCN 的增量备份
RMAN> BACKUP INCREMENTAL FROM SCN 3505254 DATABASE FORMAT '/tmp/ForStandby_%U' tag 'FORSTANDBY';
4. 拷贝刚才的备份到备库
scp /tmp/ForStandby_* 192.168.211.162:/tmp
5. 将拷贝过来的备份注册到备库的控制文件中
- [oracle@stand ~]$ rman target /
- Recovery Manager: Release 11.2.0.4.0 - Production on Thu Mar 29 11:37:52 2018
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
- connected to target database: ORCL (DBID=1484954774, not open)
- RMAN> CATALOG START WITH '/tmp/ForStandby';
- using target database control file instead of recovery catalog
- searching for all files that match the pattern /tmp/ForStandby
- List of Files Unknown to the Database
- =====================================
- File Name: /tmp/ForStandby_08sv0bdj_1_1
- File Name: /tmp/ForStandby_07sv0bcg_1_1
- Do you really want to catalog the above files (enter YES or NO)? yes
- cataloging files...
- cataloging done
- List of Cataloged Files
- =======================
- File Name: /tmp/ForStandby_08sv0bdj_1_1
- File Name: /tmp/ForStandby_07sv0bcg_1_1
6. 使用增量备份恢复备库
- RMAN> RECOVER DATABASE NOREDO;
- Starting recover at 29-MAR-18
- allocated channel: ORA_DISK_1
- channel ORA_DISK_1: SID=16 device type=DISK
- channel ORA_DISK_1: starting incremental datafile backup set restore
- channel ORA_DISK_1: specifying datafile(s) to restore from backup set
- destination for restore of datafile 00001: /u01/app/oracle/oradata/rzorcl/system01.dbf
- destination for restore of datafile 00002: /u01/app/oracle/oradata/rzorcl/sysaux01.dbf
- destination for restore of datafile 00003: /u01/app/oracle/oradata/rzorcl/undotbs01.dbf
- destination for restore of datafile 00004: /u01/app/oracle/oradata/rzorcl/users01.dbf
- destination for restore of datafile 00005: /u01/app/oracle/oradata/rzorcl/example01.dbf
- destination for restore of datafile 00006: /u01/app/oracle/oradata/rzorcl/odc_tps01.dbf
- destination for restore of datafile 00007: /u01/app/oracle/oradata/rzorcl/test01.dbf
- destination for restore of datafile 00008: /u01/app/oracle/oradata/rzorcl/big01.dbf
- destination for restore of datafile 00009: /u01/app/oracle/oradata/rzorcl/big02.dbf
- channel ORA_DISK_1: reading from backup piece /tmp/ForStandby_07sv0bcg_1_1
- channel ORA_DISK_1: piece handle=/tmp/ForStandby_07sv0bcg_1_1 tag=FORSTANDBY
- channel ORA_DISK_1: restored backup piece 1
- channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
- Finished recover at 29-MAR-18
7. 在主库为备库重新备份控制文件, 并 scp 到备库
- RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/ForStandbyCTRL.bck';
- Starting backup at 29-MAR-18
- using target database control file instead of recovery catalog
- allocated channel: ORA_DISK_1
- channel ORA_DISK_1: SID=9 device type=DISK
- channel ORA_DISK_1: starting full datafile backup set
- channel ORA_DISK_1: specifying datafile(s) in backup set
- including standby control file in backup set
- channel ORA_DISK_1: starting piece 1 at 29-MAR-18
- channel ORA_DISK_1: finished piece 1 at 29-MAR-18
- piece handle=/tmp/ForStandbyCTRL.bck tag=TAG20180329T114413 comment=NONE
- channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
- Finished backup at 29-MAR-18
- [oracle@prim tmp]$ scp ForStandbyCTRL.bck 192.168.211.162:/tmp
- mailto:oracle@192.168.211.162's password:
- ForStandbyCTRL.bck 100% 9856KB 9.6MB/s 00:00
8. 备库还原控制文件:
- RMAN> shutdown immediate;
- database dismounted
- Oracle instance shut down
- RMAN> startup nomount;
- connected to target database (not started)
- Oracle instance started
- Total System Global Area 1002127360 bytes
- Fixed Size 2259440 bytes
- Variable Size 285214224 bytes
- Database Buffers 708837376 bytes
- Redo Buffers 5816320 bytes
- RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/ForStandbyCTRL.bck';
- Starting restore at 29-MAR-18
- allocated channel: ORA_DISK_1
- channel ORA_DISK_1: SID=10 device type=DISK
- channel ORA_DISK_1: restoring control file
- channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
- output file name=/u01/app/oracle/oradata/rzorcl/control01.ctl
- output file name=/u01/app/oracle/oradata/rzorcl/control02.ctl
- Finished restore at 29-MAR-18
9. 重启备库到 mount
- RMAN> SHUTDOWN;
- RMAN> STARTUP MOUNT;
10. 如果数据库是用 OMF 方式管理数据文件的, 则需要在备库的控制文件中重新注册下数据文件, 示例如下:
- (如果数据文件存放在文件系统, 没有使用 OMF, 则跳过此步骤)
- RMAN> CATALOG START WITH '+DATA/rzorcl/datafile/';
- List of Files Unknown to the Database
- =====================================
- File Name: +data/rzorcl/DATAFILE/SYSTEM.309.685535773
- File Name: +data/rzorcl/DATAFILE/SYSAUX.301.685535773
- File Name: +data/rzorcl/DATAFILE/UNDOTBS1.302.685535775
- File Name: +data/rzorcl/DATAFILE/SYSTEM.297.688213333
- File Name: +data/rzorcl/DATAFILE/SYSAUX.267.688213333
- File Name: +data/rzorcl/DATAFILE/UNDOTBS1.268.688213335
- Do you really want to catalog the above files (enter YES or NO)? YES
- cataloging files...
- cataloging done
- List of Cataloged Files
- =======================
- File Name: +data/rzorcl/DATAFILE/SYSTEM.297.688213333
- File Name: +data/rzorcl/DATAFILE/SYSAUX.267.688213333
- File Name: +data/rzorcl/DATAFILE/UNDOTBS1.268.688213335
确保主库在这个 SCN 之后没有添加新的数据文件, 如果有则需要单独进行备份和还原, 参考文档文档 ID 836986.1
- SQL> select file#,name from v$datafile where creation_change#> 3505254;
- no rows selected
- RMAN> SWITCH DATABASE TO COPY;
- datafile 1 switched to datafile copy "+DATA/rzorcl/datafile/system.297.688213333"
- datafile 2 switched to datafile copy "+DATA/rzorcl/datafile/undotbs1.268.688213335"
- datafile 3 switched to datafile copy "+DATA/rzorcl/datafile/sysaux.267.688213333"
11. 若备库开启了闪回, 需要重新开启闪回
- SQL> ALTER DATABASE FLASHBACK OFF;
- SQL> ALTER DATABASE FLASHBACK ON;
12. 备库 clear standby log group
- SQL> ALTER DATABASE CLEAR LOGFILE GROUP 4;
- SQL> ALTER DATABASE CLEAR LOGFILE GROUP 5;
- SQL> ALTER DATABASE CLEAR LOGFILE GROUP 6;
- SQL> ALTER DATABASE CLEAR LOGFILE GROUP 7;
13. 备库开启日志应用, 至此整个过程结束
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
来源: http://www.linuxidc.com/Linux/2018-03/151630.htm