常见的文件丢失场景及恢复

常见的文件丢失场景及恢复
常见文件丢失包括三大类：
1）、控制文件丢失；
2）、数据文件丢失；
3）、重做日志丢失；


一、控制文件的丢失：
A:控制文件部分丢失
解决方案：复制可用的control file或修改spfile/pfile重新启动。

B:控制文件全部丢失
解决方案：
>恢复物理备份
>通过备份的脚本重建
Shutdown
Startup nomount
Create controlfile…..
Recover database
Alter database open
>如果没有备份或trace脚本，尝试手工编写脚本重建


二、重做日志丢失：
A：丢失非current redo log
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: ‘/add/test1/test1/redo01.log’
select a.group#,b.member,a.status from v$log a,v$logfile b where a.group#=b.group#;
GROUP# MEMBER STATUS
---------- ---------------------------------------- ----------------
1 /add/test1/test1/redo01.log INACTIVE
3 /add/test1/test1/redo03.log CURRENT
2 /add/test1/test1/redo02.log INACTIVE
解决方案：
Alter database clear logfile /xxx/xxx/redoxx.log;

B:丢失current redo log，但是数据库是正常关闭的
select a.group#,b.member,a.status from v$log a,v$logfile b where a.group#=b.group#;
GROUP# MEMBER STATUS
---------- ---------------------------------------- ----------------
1 /add/test1/test1/redo01.log INACTIVE
3 /add/test1/test1/redo03.log CURRENT
2 /add/test1/test1/redo02.log INACTIVE
解决方案：
以resetlogs方式打开
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.

C:丢失current/active redo log，但是数据库时非正常关闭的
尝试fake recover后以resetlogs方式打开数据库，出现报错
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/add/test1/test1/system01.dbf’
SQL> select file#,status,checkpoint_change#,checkpoint_time,fuzzy from v$datafile_header order by 1;
FILE# STATUS CHECKPOINT_CHANGE# CHECKPOIN FUZZY
---------- ------- -------------------------- --------- ---
1 ONLINE 4565550461182 26-MAY-13 YES
2 ONLINE 4565550461182 26-MAY-13 YES
3 ONLINE 4565550461182 26-MAY-13 YES
4 ONLINE 4565550461182 26-MAY-13 YES
解决方案：
>使用备份进行基于时间点恢复
Restore old backup
SQL> startup mount
SQL> recover database until cancel using backup controlfile; SQL> alter database open resetlogs;
>没有任何备份的情况下，无法正常OPEN,只能将数据库在不一致情况下强制启动，将用户数据导出备份并重建数据库


三、数据文件丢失：
A:非系统数据文件丢失
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/add/test1/test1/test1.dbf'
解决方案：
>使用备份数据对数据文件进行恢复;
>忽略这部分数据，offline drop该数据文件启动数据库(这样话，在此数据文件的数据丢失)

B:系统数据文件丢失
>使用备份对数据文件进行恢复
>没有备份的情况下，该数据库将无法启动(强制or正常)，数据无法恢复，如果数据非常重要，可以尝试使用DUL进行数据抢救


四、强制启动&数据抢救
SCN+NO FUZZY ======>一致性启动
SCN主要有四类:
1、    System checkpoint SCN 记录在V$DATABASE：checkpoint_change#
2、    Datafile checkpoint SCN记录在V$DATAFILE:checkpoint_change#
3、    Datafile start SCN 记录在V$DATAFILE_HEADER:checkpoint_change#
4、    Datafile stop SCN 记录在V$DATAFILE:last_change#

A:NO FUZZY
Media-Recoery-Fuzzy
当datafile上有block的SCN比datafile header中的SCN更前时，可以认为该数据文件包含脏块，处于fuzzy状态，需要更多的recovery保持一致
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
Database mounted.
SQL> select file#,status,checkpoint_change#,
checkpoint_time,fuzzy from v$datafile_header order by 1;
FILE# STATUS CHECKPOINT_CHANGE# CHECKPOIN FUZ
---------- ------- ------------------ --------- ---
1 ONLINE 4565550830945 17-JUN-13 YES
2 ONLINE 4565550830945 17-JUN-13 YES
3 ONLINE 4565550830945 17-JUN-13 YES
4 ONLINE 4565550830945 17-JUN-13 YES
$ dbv file=system01.dbf blocksize=8192
........
Highest block SCN : 595433 (1063.595433) SCN_WRAP.SCN_BASE
SCN= (SCN_WRAP*4294967296)+SCN_BASE =>4565550831081

隐藏参数使用：
_allow_resetlogs_corruption
Database open 阶段强制跳过一致性检查，不再检查该文件在数据库关闭前时什么状态以及数据库是如何关闭的。
ORA-01190: control file or data file %s is from before the last RESETLOGS
ORA-01194: file %s needs more recovery to be consistent
ORA-01113: file '%s' needs media recovery starting at log sequence # %s
ORA-01195: on-line backup of file %s needs more recovery to be consistent"
ORA-01196: file %s is inconsistent due to a failed media recovery session
ORA-01152: file '%s' was not restored from a sufficientluy old backup"

使用须知：
1、    客户没有备份
2、    可能丢失的数据非常重要和珍贵并且无法通过其他方式生成
3、    客户已经准备好进行全库导出并重建数据库
4、    设置改隐含参数并不能保证数据库100%能够强制拉起来
5、    ORACLE不再对使用该隐含参数强制拉起的的数据库提供support

_corrupted_rollback_segments
实例启动阶段阻止所有对指定回滚段的访问，回滚段中的活动事务被认为已经提交
使用方法：
>修改undo_management=manual
>添加_corrupted_rollback_segments=(_SYSSMU1$,…,…)，可通过下面脚本获取相关信息
Strings system01.dbf | grep _SYSSMU |cut –d $ -f 1 | sort –u
>注释undo_tablespace和undo_retention

_allow_resetlogs_corruption后可能遇到报错
1.    ORA-00600:[2662]
A data block SCN is ahead of the current SCN
>_minimum_giga_scn
>event ADJUST_SCN
>event 10015
2.ORA-00600:[2662]+ORA-00704
BOOTSTRAP错误，无法恢复
3.ORA-00600:[4137]/[4138]/[4139]
FORCE OPEN后常见smon访问undo出现问题，因此一般使用
4.ORA-00600：[kdsgrp1]
扫描遇到数据坏块
>event 10231
>dbms_repair.skip_corrupt_blocks

简单案例：
1.数据库
2. 查询出所有回滚段
-bash-3.2$ strings system01.dbf | grep _SYSSMU | cut -d $ -f 1 | sort –
_SYSSMU10_1221199237
_SYSSMU10_1221203537
……
3. 修改pfile文件如下
#*.undo_tablespace='UNDOTBS1'
_allow_resetlogs_corruption = true
undo_management = MANUAL
_CORRUPTED_ROLLBACK_SEGMENTS = (_SYSSMU10_1221199237,…）
4.关闭数据，利用pfile重新启动到mount阶段，resetlogs强制打开数据库
_allow_resetlogs_corruption时结合_corrupted_rollback_segments
>undo_management=manual
>_corrupted_rollback_segments=所有回滚段的信息
SQL> startup mount
Database mounted.
SQL> recover database until cancel;
ORA-00279: change 4565550830945 generated at 06/17/2013 00:02:46 needed for thread 1
ORA-00289: suggestion :
………..
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/add/test1/test1/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
Database altered.

如果起不来，就要到用到下面的参数了
SCN 参数：_minimum_giga_scn (event ADJUST_SCN/10015)
ORA-00600:[2662]
A data block SCN is ahead of the currentSCN
>_minimum_giga_scn
>event ADJUST_SCN
>event 10015

简单案例:
设置参数 _allow_resetlogs_corruption 和_CORRUPTED_ROLLBACK_SEGMENTS 后，尝试resetlogs模式打开数据库，报错：
ORA-00600: internal error code, arguments: [2662], [1826], [1818451944], [1826], [1818507298], [322961417], [], []
ORA-00600: internal error code, arguments: [2663], [0], [637083365], [0], [637083437], [], [], [], [], [], [], []
ORA-600 [2662] [a] [b] [c] [d] [e]：
Arg [a] Current SCN WRAP：当前（控制文件）的SCN WRAP
Arg [b] Current SCN BASE：当前（控制文件）的SCN BASE
Arg [c] dependent SCN WRAP：目标SCN WRAP
Arg [d] dependent SCN BASE：目标SCN BASE

ORA-00600: internal error code, arguments: [2662], [1826], [1818451944], [1826], [1818507298], [322961417], [], [] 我们知道SCN= (SCN_WRAP * 4294967296)+SCN_BASE，所以
1.期望的SCN值为1826. 1818507298=(1826*4294967296)+ 1818507298=7844428789794
2. 期望SCN转换为giga值 = 7844428789794/1024/1024/1024= 7305.XXXX 因此，需要设置_MINIMUM_GIGA_SCN=7306 稍大一点来调整当前SCN大于block的SCN
3．在pfile中增加参数_minimum_giga_scn=7306
4.重新启动数据库
Startup mount
Recover database
Alter database open
5.数据库open成功后，必须删除该参数后再次重启动
Delete parameter _minimum_giga_scn from the init.ora file
Shutdown the database
Startup

使用ADJUST_SCN event的几个场景
1．    出现ORA-00600[2662]报错
2．    强制启动后不断报ORA-1555或者启动时报ORA-604/ORA-1555
（如果启动报ORA-704和ORA-1555则不能使用该event因为报错发生在bootstrap.如果SCN差距较小可以尝试反复启动）
2.    使用_ALLOW_RESETLOGS_CORRUPTION强制启动数据库



实际案例：
数据库版本:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
其他信息：无备份，非归档，无法open起来，并且之前做个多次恢复未成功，才有下面的的操作
1、利用select file#,status,checkpoint_change#,
checkpoint_time,fuzzy from v$datafile_header order by 1;
发现所有数据文件的fuzzy位是YES，异常关闭数据库，启动时需要做数据库一致性验证，尝试alter database open；
发现需要undo文件要做恢复，此刻，尝试recover datafile xxx，发现做了过后，没有效果，还是要做验证；
2、查看undo segment信息
[root@localhost orcl]# strings system01.dbf | grep _SYSSMU | cut -d $ -f 1 |sort -u
              and substr(drs.segment_name,1,7) != '_SYSSMU'
D'              and substr(drs.segment_name,1,7) != ''_SYSSMU'' ' );
_SYSSMU10_3550978943
_SYSSMU10_3904554333
_SYSSMU11_286947212
_SYSSMU11_379893357
_SYSSMU12_3068564564
_SYSSMU12_3345414330
_SYSSMU13_1045611951
_SYSSMU13_2761193625
_SYSSMU1_3780397527
_SYSSMU14_1060866920
_SYSSMU14_2421411996
_SYSSMU15_1683924174
_SYSSMU15_2554699021
_SYSSMU16_2313212396
_SYSSMU16_2701506487
_SYSSMU17_1787446293
_SYSSMU17_2041439332
_SYSSMU1_783380902
_SYSSMU18_2800789714
_SYSSMU18_2983290590
_SYSSMU19_2323602401
_SYSSMU19_53723967
_SYSSMU20_2611377660
_SYSSMU20_3850939844
_SYSSMU21_158022190
_SYSSMU2_2232571081
_SYSSMU22_4293381698
_SYSSMU2_3138176977
_SYSSMU23_3502087459
_SYSSMU24_3911757283
_SYSSMU25_969882745
_SYSSMU26_4265453263
_SYSSMU27_2796764416
_SYSSMU28_2949705525
_SYSSMU29_916836042
_SYSSMU30_2951968219
_SYSSMU3_1645411166
_SYSSMU31_77080155
_SYSSMU3_2097677531
_SYSSMU32_2383226926
_SYSSMU33_2171873015
_SYSSMU34_4218399528
_SYSSMU35_1281591169
_SYSSMU36_4223850388
_SYSSMU37_3891560429
_SYSSMU38_830470978
_SYSSMU39_4269670886
_SYSSMU40_1548085334
_SYSSMU41_1028836633
_SYSSMU4_1152005954
_SYSSMU42_180022750
_SYSSMU43_319060321
_SYSSMU44_934106214
_SYSSMU45_2860974049
_SYSSMU46_3982067532
_SYSSMU47_11933987
_SYSSMU4_870421980
_SYSSMU48_724432902
_SYSSMU49_3198896008
_SYSSMU50_1790859891
_SYSSMU51_4188899104
_SYSSMU5_1527469038
_SYSSMU52_288285783
_SYSSMU5_2525172762
_SYSSMU53_311086950
_SYSSMU54_1597897898
_SYSSMU55_1028194913
_SYSSMU56_2625382688
_SYSSMU57_1912349309
_SYSSMU58_1635312664
_SYSSMU6_2443381498
_SYSSMU6_3753507049
_SYSSMU7_1260614213
_SYSSMU7_3286610060
_SYSSMU8_2012382730
_SYSSMU8_2806087761
_SYSSMU9_1424341975
_SYSSMU9_973944058
[root@localhost orcl]#

在pfile里添加如下参数，并且注释了undo_tablespace参数
_allow_resetlogs_corruption=true
undo_management=MANUAL
_CORRUPTED_ROLLBACK_SEGMENTS=(_SYSSMU10_3550978943,_SYSSMU10_3904554333,_SYSSMU11_286947212,_SYSSMU11_379893357,_SYSSMU12_3068564564,_SYSSMU12_3345414330,_SYSSMU13_1045611951,_SYSSMU13_2761193625,_SYSSMU1_3780397527,_SYSSMU14_1060866920,_SYSSMU14_2421411996,_SYSSMU15_1683924174,_SYSSMU15_2554699021,_SYSSMU16_2313212396,_SYSSMU16_2701506487,_SYSSMU17_1787446293,_SYSSMU17_2041439332,_SYSSMU1_783380902,_SYSSMU18_2800789714,_SYSSMU18_2983290590,_SYSSMU19_2323602401,_SYSSMU19_53723967,_SYSSMU20_2611377660,_SYSSMU20_3850939844,_SYSSMU21_158022190,_SYSSMU2_2232571081,_SYSSMU22_4293381698,_SYSSMU2_3138176977,_SYSSMU23_3502087459,_SYSSMU24_3911757283,_SYSSMU25_969882745,_SYSSMU26_4265453263,_SYSSMU27_2796764416,_SYSSMU28_2949705525,_SYSSMU29_916836042,_SYSSMU30_2951968219,_SYSSMU3_1645411166,_SYSSMU31_77080155,_SYSSMU3_2097677531,_SYSSMU32_2383226926,_SYSSMU33_2171873015,_SYSSMU34_4218399528,_SYSSMU35_1281591169,_SYSSMU36_4223850388,_SYSSMU37_3891560429,_SYSSMU38_830470978,_SYSSMU39_4269670886,_SYSSMU40_1548085334,_SYSSMU41_1028836633,_SYSSMU4_1152005954,_SYSSMU42_180022750,_SYSSMU43_319060321,_SYSSMU44_934106214,_SYSSMU45_2860974049,_SYSSMU46_3982067532,_SYSSMU47_11933987,_SYSSMU4_870421980,_SYSSMU48_724432902,_SYSSMU49_3198896008,_SYSSMU50_1790859891,_SYSSMU51_4188899104,_SYSSMU5_1527469038,_SYSSMU52_288285783,_SYSSMU5_2525172762,_SYSSMU53_311086950,_SYSSMU54_1597897898,_SYSSMU55_1028194913,_SYSSMU56_2625382688,_SYSSMU57_1912349309,_SYSSMU58_1635312664,_SYSSMU6_2443381498,_SYSSMU6_3753507049,_SYSSMU7_1260614213,_SYSSMU7_3286610060,_SYSSMU8_2012382730,_SYSSMU8_2806087761,_SYSSMU9_1424341975,_SYSSMU9_973944058)

利用该pfile启动数据库到mount阶段
ALTER DATABASE RECOVER UNTIL
 Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 8 slaves
ORA-279 signalled during: ALTER DATABASE RECOVER  database until cancel  ...
ALTER DATABASE RECOVER    CANCEL  
Wed Jul 30 09:25:30 2014
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_pr00_3896.trc:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u02/oradata/orcl/system01.dbf'
Slave exiting with ORA-1547 exception
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_pr00_3896.trc:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u02/oradata/orcl/system01.dbf'
ORA-10879 signalled during: ALTER DATABASE RECOVER    CANCEL  ...
ALTER DATABASE RECOVER CANCEL
Media Recovery Canceled
Completed: ALTER DATABASE RECOVER CANCEL
Wed Jul 30 09:26:27 2014
ALTER database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 637040346
Resetting resetlogs activation ID 1351186164 (0x508976f4)
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3154.trc:
ORA-00367: checksum error in log file header
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: '/u02/oradata/orcl/redo01.log'
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3154.trc:
ORA-00367: checksum error in log file header
ORA-00322: log 2 of thread 1 is not current copy
ORA-00312: online log 2 thread 1: '/u02/oradata/orcl/redo02.log'
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3154.trc:
ORA-00367: checksum error in log file header
ORA-00322: log 3 of thread 1 is not current copy
ORA-00312: online log 3 thread 1: '/u02/oradata/orcl/redo03.log'
Wed Jul 30 09:26:31 2014
Setting recovery target incarnation to 2
Wed Jul 30 09:26:31 2014
Assigning activation ID 1382066206 (0x5260a81e)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /u02/oradata/orcl/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Jul 30 09:26:31 2014
SMON: enabling cache recovery
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3154.trc  (incident=74556):
ORA-00600: internal error code, arguments: [2663], [0], [637083365], [0], [637083437], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_74556/orcl_ora_3154_i74556.trc
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3154.trc:
ORA-00600: internal error code, arguments: [2663], [0], [637083365], [0], [637083437], [], [], [], [], [], [], []
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3154.trc:
ORA-00600: internal error code, arguments: [2663], [0], [637083365], [0], [637083437], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 3154): terminating the instance due to error 600
Instance terminated by USER, pid = 3154
ORA-1092 signalled during: ALTER database open resetlogs...
opiodr aborting process unknown ospid (3154) as a result of ORA-1092

发现数据库起不来，那么接下来就做一个跳SCN处理：
_minimum_giga_scn=1放到pfile
重新启动，open数据库，正常启动
删除此参数和重建临时表空间，导出数据放到新库里；