题目 部分
在Oracle中，备库数据文件异常，物理DG如何恢复♣ 答案部分
有的时候由于备库空间不足，在主库添加了数据文件后，导致备库数据文件的缺失，可能很久之后才发现，但是由于归档的缺失等其它原因而导致备库不能正常应用Redo日志。还有其它情况可能导致备库的数据文件不能正常ONLINE，在这种情况下，可以在主库上利用CONVERT命令备份一个数据文件然后拷贝到备库即可。若是备库归档文件比较全，则可以直接在备库创建数据文件后应用Redo日志即可，而不需要从主库拷贝数据文件。
恢复过程中的一些关键性的命令如下所示：
1CONVERT DATAFILE '+DATA1/oralhrs/datafile/tbs101.262.923139373' FORMAT '/tmp/tbs101.dbf_bk';--主库备份相关文件 2CONVERT DATAFILE '/tmp/tbs101.dbf_bk' FORMAT '+DATA1';--备库修改从主库拷贝过来的文件为ASM格式 3ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL' SID='*'; --备库修改文件管理模式为手动 4ALTER DATABASE CREATE DATAFILE 64 AS '+DATA1';--备库若数据文件丢失可以先创建一个数据文件 5ALTER DATABASE RENAME FILE '+DATA1/oralhrsg/datafile/tbs101.483.923151901' TO '+DATA1/oralhrsg/datafile/tbs101.382.923151215'; --重命名刚新建的数据文件为从主库拷贝过来的数据文件 6ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;--启用Redo恢复
接下来演示整个恢复过程。
首先查看备库的文件情况，发现64号文件处于OFFLINE状态。
1SYS@oraLHRDG2> SELECT A.FILE#,A.NAME,A.RECOVER,A.CHECKPOINT_CHANGE#,STATUS FROM V$DATAFILE_HEADER A WHERE A.FILE# IN (1,2,64); 2 FILE# NAME REC CHECKPOINT_CHANGE# STATUS 3---------- ---------------------------------------------------- --- ------------------ ------- 4 1 +DATA1/oralhrsg/datafile/system.358.869055401 1.5760E+10 ONLINE 5 2 +DATA1/oralhrsg/datafile/sysaux.354.869047985 1.5760E+10 ONLINE 6 64 +DATA1/oralhrsg/datafile/tbs101.382.875442343 1764555149 OFFLINE 7SYS@oraLHRDG2> RECOVER DATAFILE 64; 8ORA-00283: recovery session canceled due to errors 9ORA-01153: an incompatible media recovery is active 10SYS@oraLHRDG2> RECOVER MANAGED STANDBY DATABASE CANCEL; 11Media recovery complete. 12SYS@oraLHRDG2> RECOVER DATAFILE 64; 13ORA-00283: recovery session canceled due to errors 14ORA-01610: recovery using the BACKUP CONTROLFILE option must be done 15SYS@oraLHRDG2> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; 16Database altered. 17SYS@oraLHRDG2> ALTER DATABASE DATAFILE 64 ONLINE; 18alter database datafile 64 online 19* 20ERROR at line 1: 21ORA-01113: file 64 needs media recovery 22ORA-01110: data file 64: '+DATA1/oralhrsg/datafile/tbs101.382.875442343'
虽然可以开启实时应用进程，但是64号文件依然不能ONLINE，因为现在的系统SCN号和64号文件头的SCN号相差很大了，归档日志必然不存在了，所以使用日志来恢复文件的方法自然不可行了。那么，接下来在主库用CONVERT命令备份64号文件：
1[ZFLHRSDB1:oracle]:/oracle>rman target / 2Recovery Manager: Release 11.2.0.3.0 - Production on Wed Sep 21 14:49:56 2016 3Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 4connected to target database: ORAIPPS (DBID=1344172889) 5RMAN> CONVERT DATAFILE '+DATA1/oralhrs/datafile/tbs101.262.923139373' FORMAT '/tmp/tbs101.dbf_bk'; 6Starting conversion at target at 2016-09-21 14:51:16 7using channel ORA_DISK_1 8channel ORA_DISK_1: starting datafile conversion 9input file name=+DATA1/oralhrs/datafile/tbs101.262.923139373 10converted datafile=/tmp/tbs101.dbf_bk 11channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03 12Finished conversion at target at 2016-09-21 14:51:19
将备份的文件拷贝到备库：
1[ZFLHRSDB1:oracle]:/tmp>scp /tmp/tbs101.dbf_bk oracle@192.68.155.16:/tmp/tbs101.dbf_bk 2The authenticity of host '192.68.155.16 (192.68.155.16)' can't be established. 3RSA key fingerprint is 7b:d6:ba:ca:b3:71:b5:0b:bf:14:f4:e4:18:5f:51:45. 4Are you sure you want to continue connecting (yes/no)? yes 5Warning: Permanently added '192.68.155.16' (RSA) to the list of known hosts. 6tbs101.dbf_bk 100% 100MB 50.0MB/s 00:02
在备库上转换文件为ASM格式：
1RMAN> CONVERT DATAFILE '/tmp/tbs101.dbf_bk' FORMAT '+DATA1'; 2Starting conversion at target at 2016-09-21 14:53:33 3using target database control file instead of recovery catalog 4allocated channel: ORA_DISK_1 5channel ORA_DISK_1: SID=1542 instance=oraLHRDG2 device type=DISK 6channel ORA_DISK_1: starting datafile conversion 7input file name=/tmp/tbs101.dbf_bk 8converted datafile=+DATA1/oralhrsg/datafile/tbs101.382.923151215 9channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01 10Finished conversion at target at 2016-09-21 14:53:36
备库上进行重命名操作，若是备库上64号文件被删除了，则需要先重建64号文件：
1SYS@oraLHRDG2> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL' SID='*'; 2System altered. 3SYS@oraLHRDG2> ALTER DATABASE CREATE DATAFILE 64 AS '+DATA1'; 4Database altered. 5SYS@oraLHRDG2> SELECT A.FILE#,A.NAME,A.RECOVER,A.CHECKPOINT_CHANGE#,STATUS FROM V$DATAFILE_HEADER A WHERE A.FILE# IN (1,2,64); 6 FILE# NAME REC CHECKPOINT_CHANGE# STATUS 7---------- ------------------------------------------------ --- ------------------ ------- 8 1 +DATA1/oralhrsg/datafile/system.358.869055401 1.5761E+10 ONLINE 9 2 +DATA1/oralhrsg/datafile/sysaux.354.869047985 1.5761E+10 ONLINE 10 64 +DATA1/oralhrsg/datafile/tbs101.483.923151901 1.5761E+10 OFFLINE 11SYS@oraLHRDG2> ALTER DATABASE DATAFILE 64 ONLINE; 12ALTER DATABASE DATAFILE 64 ONLINE 13* 14ERROR at line 1: 15ORA-01113: file 64 needs media recovery 16ORA-01110: data file 64: '+DATA1/oralhrsg/datafile/tbs101.483.923151901'
可以看到已经有64号文件了，下边进行重命名，修改为从主库拷贝过来的64号文件：
1SYS@oraLHRDG2> ALTER DATABASE RENAME FILE '+DATA1/oralhrsg/datafile/tbs101.483.923151901' TO '+DATA1/oralhrsg/datafile/tbs101.382.923151215'; 2ALTER DATABASE RENAME FILE '+DATA1/oralhrsg/datafile/tbs101.483.923151901' TO '+DATA1/oralhrsg/datafile/tbs101.382.923151215' 3* 4ERROR at line 1: 5ORA-01511: error in renaming log/data files 6ORA-01121: cannot rename database file 64 - file is in use or recovery 7ORA-01110: data file 64: '+DATA1/oralhrsg/datafile/tbs101.483.923151901' 8SYS@oraLHRDG2> ! oerr ora 01121 901121, 00000, "cannot rename database file %s - file is in use or recovery" 10// *Cause: Attempted to use ALTER DATABASE RENAME to rename a 11// datafile that is online in an open instance or is being recovered. 12// *Action: Close database in all instances and end all recovery sessions.
该错误提示文件在使用，不能被重命名。由于该库是RAC库，需要先关闭DG，启动到MOUNT状态后再重命名：
1[ZFLHRSDB4:root]:/>srvctl stop db -d oralhrsg 2[ZFLHRSDB4:root]:/>srvctl start db -d oralhrsg -o mount
在数据库中重命名：
1SYS@oraLHRDG2> conn / as sysdba
2Connected.
3SYS@oraLHRDG2> ALTER DATABASE RENAME FILE '+DATA1/oralhrsg/datafile/tbs101.483.923151901' TO '+DATA1/oralhrsg/datafile/tbs101.382.923151215';
4Database altered.
5SYS@oraLHRDG2> ALTER DATABASE DATAFILE 64 ONLINE;
6Database altered.<<<<<<<<<---------数据文件可以ONLINE了
7SYS@oraLHRDG2> COL NAME FOR A50
8SYS@oraLHRDG2> COL CHECKPOINT_CHANGE# FOR 9999999999999
9SYS@oraLHRDG2> SELECT A.FILE#,A.NAME,A.RECOVER,A.CHECKPOINT_CHANGE#,STATUS FROM V$DATAFILE_HEADER A WHERE A.FILE# IN (1,2,64);
10 FILE# NAME REC CHECKPOINT_CHANGE# STATUS
11---------- -------------------------------------------------- --- ------------------ -------
12 1 +DATA1/oralhrsg/datafile/system.358.869055401 15760776695 ONLINE
13 2 +DATA1/oralhrsg/datafile/sysaux.354.869047985 15760776695 ONLINE
14 64 +DATA1/oralhrsg/datafile/tbs101.382.923151215 15760492416 ONLINE
15SYS@oraLHRDG2> ALTER DATABASE OPEN READ ONLY;
16ALTER DATABASE OPEN READ ONLY
17*
18ERROR at line 1:
19ORA-10458: standby database requires recovery
20ORA-01194: file 64 needs more recovery to be consistent
21ORA-01110: data file 64: '+DATA1/oralhrsg/datafile/tbs101.382.923151215'
22 <<<<<<<<<------------ 打开数据库依然报错，尝试手动恢复一下，看看需要哪些日志，因为64号文件已经是最新的了
23SYS@oraLHRDG2> RECOVER DATABASE;
24ORA-00283: recovery session canceled due to errors
25ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
26SYS@oraLHRDG2> RECOVER STANDBY DATABASE USING BACKUP CONTROLFILE;
27ORA-00279: change 15760492416 generated at 09/21/2016 11:38:54 needed for thread 1
28ORA-00289: suggestion : /arch/1_12918_868895513.arc
29ORA-00280: change 15760492416 for thread 1 is in sequence #12918
30Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
31cancel
32ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
33ORA-01194: file 64 needs more recovery to be consistent
34ORA-01110: data file 64: '+DATA1/oralhrsg/datafile/tbs101.382.923151215'
35ORA-01112: media recovery not started
36 <<<<<<<<<------------ 缺少12918日志，很欣慰，因为12918已经是最新的日志了，这里解决起来就很简单了，可以从主库拷贝12918日志到备库，但是这样太麻烦，可以开启备库的应用进程让其自动解决备库的GAP问题
37SYS@oraLHRDG2> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
38Database altered.
此时查看告警日志，很欣慰看到了12918日志过来了：
1Wed Sep 21 15:24:33 2016 2alter database recover managed standby database using current logfile disconnect from session 3Attempt to start background Managed Standby Recovery process (oraLHRDG2) 4Wed Sep 21 15:24:33 2016 5MRP0 started with pid=44, OS id=12649040 6MRP0: Background Managed Standby Recovery process started (oraLHRDG2) 7 started logmerger process 8Wed Sep 21 15:24:39 2016 9Managed Standby Recovery starting Real Time Apply 10Parallel Media Recovery started with 16 slaves 11Waiting for all non-current ORLs to be archived... 12All non-current ORLs have been archived. 13Wed Sep 21 15:24:40 2016 14Media Recovery Log /arch/1_12918_868895513.arc 15Media Recovery Log /arch/2_12918_868895513.arc 16Completed: alter database recover managed standby database using current logfile disconnect from session 17Datafile 64 added to flashback set 18Media Recovery Log /arch/2_12919_868895513.arc 19Media Recovery Log /arch/1_12919_868895513.arc 20Media Recovery Log /arch/2_12920_868895513.arc 21Media Recovery Log /arch/1_12920_868895513.arc 22Media Recovery Log /arch/2_12921_868895513.arc 23Media Recovery Log /arch/1_12921_868895513.arc 24Media Recovery Waiting for thread 2 sequence 12922 (in transit) 25Recovery of Online Redo Log: Thread 2 Group 12 Seq 12922 Reading mem 0 26 Mem# 0: +DATA1/oralhrsg/onlinelog/group_12.353.869055809 27Media Recovery Waiting for thread 1 sequence 12922 (in transit) 28Recovery of Online Redo Log: Thread 1 Group 8 Seq 12922 Reading mem 0 29 Mem# 0: +DATA1/oralhrsg/onlinelog/group_8.344.869055791
最后重启备库的2个节点：
1[ZFLHRSDB4:root]:/>srvctl stop db -d oralhrsg 2[ZFLHRSDB4:root]:/>srvctl start db -d oralhrsg 3[ZFLHRSDB4:root]:/>srvctl status db -d oralhrsg 4Instance oraLHRDG1 is running on node zflhrsdb3 5Instance oraLHRDG2 is running on node zflhrsdb4
而数据库中64号文件已经正常了：
1SYS@oraLHRDG2> SELECT A.FILE#,A.NAME,A.RECOVER,A.CHECKPOINT_CHANGE#,STATUS FROM V$DATAFILE_HEADER A WHERE A.FILE# IN (1,2,64); 2 FILE# NAME REC CHECKPOINT_CHANGE# STATUS 3---------- -------------------------------------------------- --- ------------------ ------- 4 1 +DATA1/oralhrsg/datafile/system.358.869055401 15760815694 ONLINE 5 2 +DATA1/oralhrsg/datafile/sysaux.354.869047985 15760815694 ONLINE 6 64 +DATA1/oralhrsg/datafile/tbs101.382.923151215 15760815694 ONLINE 7SYS@oraLHRDG2> show parameter standby 8NAME TYPE VALUE 9------------------------------------ ----------- ------------------------------ 10standby_archive_dest string ?/dbs/arch 11standby_file_management string MANUAL 12SYS@oraLHRDG2> ALTER SYSTEM SET standby_file_management='AUTO' SID='*';====>>>>> 别忘记将该参数修改回来 13System altered.
最后不要忘记将STANDBY_FILE_MANAGEMENT参数修改为AUTO。
