然后使用oradebug推进内存中scn号,以便于执行后面的recover来恢复丢失的redo文件,因为recover的过程会读取内存中scn。注意 alter session set events '10015 trace name adjust_scn level 10';这种方式在11.2.0.4已经失效了
(题外话:我们先聊聊Oracle的SCN。在数据库内部,SCN是一个单向递增的数字编号,控制文件、数据文件、在线Redo日志、归档日志和备份集合中,都包括这个数字编号。在内部文件中,SCN是通过Base和Wrap两个部分进行保存。Base是SCN编号的基础位,是通过32位二进制位进行保存。一旦超过这32位长度,系统会自动在Wrap进位。也就是说,Wrap表示的超过4G个数的进位次数)
SQL> oradebug poke 0x06001AE70 4 0x001B7740
oradebug 推进scn号,poke命令中,第一位参数是对应写入的内存位数,第二位参数是写入长度,第三位参数是写入取值。默认写入取值是10进制,我们在这里指定写入16进制(0x开头),每一个取值段,用8个16进制对应,对应到数字位数是4位
首先查出数据库的控制文件中的scn号
SQL> select file#, checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 21959486
2 21959486
3 21959486
4 21959486
5 21959486
6 21959486
7 21959486
7 rows selected.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [06001AE70, 06001AEA0) = 014F14A2 00000001 00000000 00000000 000000EB 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000
SQL> oradebug poke 0x06001AE70 4 21959486
BEFORE: [06001AE70, 06001AE74) = 00000000
AFTER: [06001AE70, 06001AE74) = 014F133E
(或者可以把21959486转换成16进制,然后再修改
SQL> select to_char(21959486, 'XXXXXXXXXXX') from dual;
TO_CHAR(2195
------------
14F133E
SQL> oradebug poke 0x06001AE70 4 0x14F133E
BEFORE: [06001AE70, 06001AE74) = 00000000
AFTER: [06001AE70, 06001AE74) = 014F133E)
再次查看确实已经变成了014F133E(对应10进制是21959486)
SQL> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [06001AE70, 06001AEA0) = 014F133E 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000
然后执行recover进行不完全恢复:
SQL> recover database until cancel;
ORA-00279: change 21959486 generated at 04/06/2019 23:52:28 needed for thread 1
ORA-00289: suggestion :
/data/u01/app/oracle/fast_recovery_area/STDB59/archivelog/2019_04_07/o1_mf_1_2_%
u_.arc
ORA-00280: change 21959486 for thread 1 is in sequence #2
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: '/data/u01/app/oracle/oradata/stdb59/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
Database altered.
至此恢复成功!
三:oracle开归档,一致性关闭
这种情况是同情况1,不需要做实例恢复,所以可以直接删除从新或者recover所有的redo组即可,
方法一:直接clear相应的redo日志组!也就是删除重新建立!
SQL> shutdown immediate #一致性关闭
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 1275071648 bytes
Database Buffers 318767104 bytes
Redo Buffers 7319552 bytes
Database mounted.
清理删除从新建立或者直接clear所有的redo 日志组,包括当前状态的和active状态的redo 日志组!
SQL> alter database clear logfile group 1;
Database altered.
SQL> alter database clear logfile group 3;
Database altered.
SQL> alter database open ;
Database altered.
方法二:recover的方式恢复重做日志,我的实验过程中,有的时候这个方法会报错,如果报错那么就使用第一种方式恢复!
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 536874104 bytes
Database Buffers 289406976 bytes
Redo Buffers 2392064 bytes
Database mounted.
SQL>
###恢复丢失的redo文件,但是需要open resetlogs之后才能自动创建上!
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
四:开归档,非一致性关闭;
这种情况,只能借助归档日志做不完全恢复!
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
1 1 39 52428800 512 1 YES
INACTIVE 4318162327 20-APR-19 4318209770 20-APR-19
3 1 40 52428800 512 1 NO
CURRENT 4318209770 20-APR-19 2.8147E+14
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 39
Next log sequence to archive 40
Current log sequence 40
删除redo log文件
[oracle@testdb59 stdb59]$ rm -f *.log
然后非一致性关闭
SQL> shu abort
ORACLE instance shut down.
解决过程:
SQL> startup mount
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 1275071648 bytes
Database Buffers 318767104 bytes
Redo Buffers 7319552 bytes
Database mounted.
###恢复丢失的redo文件,但是需要open resetlogs之后才能自动创建上!
SQL> recover database until cancel;
Media recovery complete.
尝试resetlog方式打开,如果报错如下,那么还得借助隐含参数_allow_resetlogs_corruption;
SQL> alter database open RESETLOGS;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data/u01/app/oracle/oradata/stdb59/system01.dbf'