首先说明,这种方法步骤多、比较复杂,更适合量很大重搭dg相当耗时的库,如果是小库,直接重搭更简单。
一、 现象
从库alert日志出现报错,有gap sequence现象。
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 2473-2572
DBID 2826037382 branch 929228744
FAL[client]: All defined FAL servers have been attempted.
v$archive_gap视图也可看到缺少的归档日志号。
SQL> select * from v$archive_gap;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 2473 2572
二、 修复步骤
1. 停备库日志应用
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
2. 查询备库的scn
用下面两个查询中的最小值在主库进行增量备份。
--防止current_scn以科学计数法显示
col current_scn for 99999999999999999
SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
--------------
3164433
select min(checkpoint_change#) from v$datafile_header
where file# not in (select file# from v$datafile where enabled = 'READ ONLY');
MIN(F.FHSCN)
----------------
3162298
-- 这里应该用3162298
3. 确定主库在此SCN后是否加过数据文件
select FILE#,name from v$datafile where CREATION_CHANGE#>=3162298;
根据主库是否加过数据文件,选择下面不同的操作步骤,注意不要直接在从库add数据文件,会报错的。
4. 主库增量备份并传输到备库上
1)如果主库没加过数据文件
直接用查到的最小SCN进行增备即可
RMAN> BACKUP INCREMENTAL FROM SCN 3162298 DATABASE FORMAT '/tmp/ForStandby_%U' tag 'FORSTANDBY';
2)如果主库加了数据文件
还需要备份新加的数据文件和主库当前控制文件
RMAN> backup datafile <missing file number>,<missing file number> format '/tmp/ForStandby_%U' tag 'FORSTANDBY';
RMAN> backup incremental from SCN 3162298 database format '/tmp/ForStandby_%U' tag 'FORSTANDBY';
RMAN> backup current controlfile for standby format '/tmp/ForStandbyCTRL.bck';
3)备份文件传到备库
scp /tmp/ForStandby_* standby:/tmp
5. 主库没加过数据文件的备库恢复方法
1)注册恢复文件
rman target /
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_2lkglss4_1_1
File Name: /tmp/ForStandby_2mkglst8_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_2lkglss4_1_1
File Name: /tmp/ForStandby_2mkglst8_1_1
2)恢复数据库
RMAN> SHUTDOWN IMMEDIATE ;
RMAN> STARTUP MOUNT;
RMAN> RECOVER DATABASE NOREDO;
starting recover at 03-JUN-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=28 devtype=DISK
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DATA/STBY/datafile/system.297.688213333
destination for restore of datafile 00002: +DATA/STBY/datafile/undotbs1.268.688213335
destination for restore of datafile 00003: +DATA/STBY/datafile/sysaux.267.688213333
channel ORA_DISK_1: reading from backup piece /tmp/ForStandby_2lkglss4_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/tmp/ForStandby_2lkglss4_1_1 tag=FORSTANDBY
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished recover at 03-JUN-09
3)备份主库当前控制文件并传到从库
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/ForStandbyCTRL.bck';
scp /tmp/ForStandbyCTRL.bck standby:/tmp
4)获取从库当前数据文件信息
由于主从库数据文件名、路径可能不同,需要先保存从库数据文件信息,以供还原主库备份的控制文件后参考 。
spool standby_datafile_names.txt
set pagesize 1000;
set lines 200
col name format a60
select file#, name from v$datafile order by file# ;
spool off
5)从库还原备份的控制文件
RMAN> SHUTDOWN IMMEDIATE ;
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/ForStandbyCTRL.bck';
Starting restore at 03-JUN-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
output filename=+DATA/STBY/controlfile/current.257.688583989
Finished restore at 03-JUN-09
6)mount从库
RMAN> ALTER DATABASE MOUNT;
7)如果数据文件的位置、名称不同,需要在从库Catalog数据文件(相同则跳过此步)
- 建议在此步骤之前检查主从库化身
RMAN> list incarnation;
- 对从库数据文件所在的每个磁盘组或目录,执行以下命令
RMAN> CATALOG START WITH '+DATA/STBY/datafile/';
List of Files Unknown to the Database
=====================================
File Name: +DATA/STBY/DATAFILE/SYSTEM.309.685535773
File Name: +DATA/STBY/DATAFILE/SYSAUX.301.685535773
File Name: +DATA/STBY/DATAFILE/UNDOTBS1.302.685535775
File Name: +DATA/STBY/DATAFILE/SYSTEM.297.688213333
File Name: +DATA/STBY/DATAFILE/SYSAUX.267.688213333
File Name: +DATA/STBY/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/STBY/DATAFILE/SYSTEM.297.688213333
File Name: +DATA/STBY/DATAFILE/SYSAUX.267.688213333
File Name: +DATA/STBY/DATAFILE/UNDOTBS1.268.688213335
- 使用从库正确的数据文件位置和名称更新控制文件
RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy "+DATA/STBY/datafile/system.297.688213333"
datafile 2 switched to datafile copy "+DATA/STBY/datafile/undotbs1.268.688213335"
datafile 3 switched to datafile copy "+DATA/STBY/datafile/sysaux.267.688213333"
- 如果switch报错"RMAN-06571: datafile .. does not have recoverable copy"。说明尚未catalog所有从库数据文件,或者某些从库数据文件位置、名称与主库相同。
- 如果是后者,必须只switch位置、名称与主库不同的数据文件。
RMAN> switch datafile 5 to copy;
- 比较当前从库数据文件与还原前(前面的standby_datafile_names.txt)是否有差异,确保已将所有数据文件添加到从库。
- 再次运行步骤2中的查询,确认增量恢复已前滚数据文件。现在,SCN应该大于初始值。
8)从库配置闪回(可选)
SQL> ALTER DATABASE FLASHBACK OFF;
SQL> ALTER DATABASE FLASHBACK ON;
9)清除从库所有standby redolog组
SQL> select GROUP# from v$logfile where TYPE='STANDBY' group by GROUP#;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
10)从库恢复日志应用
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
6. 主库加过数据文件的备库恢复方法
1)恢复新的控制文件并catalog备份文件
RMAN> shutdown;
RMAN> startup nomount;
RMAN> restore standby controlfile from '/tmp/ForStandbyCTRL.bck';
RMAN> alter database mount;
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_2lkglss4_1_1
File Name: /tmp/ForStandby_2mkglst8_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_2lkglss4_1_1
File Name: /tmp/ForStandby_2mkglst8_1_1
2)还原主库新建的数据文件
run
{
set newname for datafile X to '+DISKGROUP';
set newname for datafile Y to '+DISKGROUP';
set newname for datafile Z to '+DISKGROUP';
...
restore datafile x,y,z,....;
}
3)重命名新从库控制文件中的数据文件
由于控制文件是从主库恢复的,当前控制文件中即的数据文件路径会跟主库一样。如果从库数据文件路径、名称与主库不同,需要执行此步骤。
RMAN> CATALOG START WITH '+DATA/<db_unqiue_name>/datafile/';
List of Files Unknown to the Database
=====================================
File Name: +data/<db_unqiue_name>/DATAFILE/SYSTEM.309.685535773
File Name: +data/<db_unqiue_name>/DATAFILE/SYSAUX.301.685535773
File Name: +data/<db_unqiue_name>/DATAFILE/UNDOTBS1.302.685535775
File Name: +data/<db_unqiue_name>/DATAFILE/SYSTEM.297.688213333
File Name: +data/<db_unqiue_name>/DATAFILE/SYSAUX.267.688213333
File Name: +data/<db_unqiue_name>/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/<db_unqiue_name>/DATAFILE/SYSTEM.297.688213333
File Name: +data/<db_unqiue_name>/DATAFILE/SYSAUX.267.688213333
File Name: +data/<db_unqiue_name>/DATAFILE/UNDOTBS1.268.688213335
catalog所有文件后,执行switch the database to copy命令
RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy "+DATA/<db_unqiue_name>/datafile/system.297.688213333"
datafile 2 switched to datafile copy "+DATA/<db_unqiue_name>/datafile/undotbs1.268.688213335"
datafile 3 switched to datafile copy "+DATA/<db_unqiue_name>/datafile/sysaux.267.688213333"
4)恢复备库
RMAN> RECOVER DATABASE NOREDO;
starting recover at 03-JUN-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=28 devtype=DISK
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DATA/<db_unqiue_name>/datafile/system.297.688213333
destination for restore of datafile 00002: +DATA/<db_unqiue_name>/datafile/undotbs1.268.688213335
destination for restore of datafile 00003: +DATA/<db_unqiue_name>/datafile/sysaux.267.688213333
channel ORA_DISK_1: reading from backup piece /tmp/ForStandby_2lkglss4_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/tmp/ForStandby_2lkglss4_1_1 tag=FORSTANDBY
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished recover at 03-JUN-09
5)从库配置闪回(可选)
SQL> ALTER DATABASE FLASHBACK OFF;
SQL> ALTER DATABASE FLASHBACK ON;
6)清除从库所有standby redolog组
SQL> select GROUP# from v$logfile where TYPE='STANDBY' group by GROUP#;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
7)从库恢复日志应用
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
参考
Steps to perform for Rolling Forward a Physical Standby Database using RMAN Incremental Backup. (Doc ID 836986.1)
Steps to perform for Rolling forward a standby database using RMAN incremental backup when datafile is added to primary (Doc ID 1531031.1)