插入测试数据
SQL> insert into t select * from dba_objects;
50351 rows created.
SQL> commit;
Commit complete.
SQL> select group#, status from v$log ;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE
SQL> shutdown abort;
ORACLE instance shut down.
破坏redo log
[oracle@racdg rac]$ mv group_1.261.841267257 group_1.261.841267257.bak
启动数据库
SQL> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 2020448 bytes
Variable Size 96471968 bytes
Database Buffers 213909504 bytes
Redo Buffers 2170880 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/oradata/rac/group_1.261.841267257'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance rac1 (thread 1)
ORA-00312: online log 1 thread 1: '/u01/oradata/rac/group_1.261.841267257'
设置隐含参数
rac1._ALLOW_RESETLOGS_CORRUPTION=true
再次启动数据库
SQL> startup pfile='/u01/product/10.2/db_1/dbs/initrac1.ora';
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 2020448 bytes
Variable Size 96471968 bytes
Database Buffers 213909504 bytes
Redo Buffers 2170880 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/oradata/rac/group_1.261.841267257'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> recover database until cancel;
ORA-00279: change 808373 generated at 03/21/2014 15:08:12 needed for thread 1
ORA-00289: suggestion : /u01/oradata/rac/arch/1_4_842797543.dbf
ORA-00280: change 808373 for thread 1 is in sequence #4
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/u01/oradata/rac/arch/1_4_842797543.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/u01/oradata/rac/arch/1_4_842797543.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
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: '/u01/oradata/rac/system.dbf'
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
alert报错
Fri Mar 21 15:44:43 2014
Errors in file /u01/admin/rac/udump/rac1_ora_3679.trc:
ORA-00600: internal error code, arguments: [2662], [0], [808380], [0], [808410], [4194313], [], []
Fri Mar 21 15:44:43 2014
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600
Instance terminated by USER, pid = 3679
ORA-1092 signalled during: alter database open resetlogs...
使用ADJUST_SCN事件
SQL> startup pfile='/u01/product/10.2/db_1/dbs/initrac1.ora' mount;
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 2020448 bytes
Variable Size 96471968 bytes
Database Buffers 213909504 bytes
Redo Buffers 2170880 bytes
Database mounted.
SQL> col name for a50
SQL> set lines 150
SQL> select name, checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- --------------------
/u01/oradata/rac/system.dbf 808376
/u01/oradata/rac/undotbs1.dbf 808376
/u01/oradata/rac/sysaux.dbf 808376
/u01/oradata/rac/users.dbf 808376
/u01/oradata/rac/example.dbf 808376
/u01/oradata/rac/undotbs2.dbf 808376
/u01/oradata/rac/users_2.dbf 808376
SQL> alter session set events 'IMMEDIATE trace name ADJUST_SCN level 1'; (强行将scn增大,level越大,增加的SCN越多)
Session altered.
SQL> alter database open;
Database altered.
SQL> select name, checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- --------------------
/u01/oradata/rac/system.dbf 828381
/u01/oradata/rac/undotbs1.dbf 828381
/u01/oradata/rac/sysaux.dbf 828381
/u01/oradata/rac/users.dbf 828381
/u01/oradata/rac/example.dbf 828381
/u01/oradata/rac/undotbs2.dbf 828381
/u01/oradata/rac/users_2.dbf 828381
7 rows selected.
但是最初向T中插入的数据查不出来了
SQL> select count(*) from t;
COUNT(*)
--------------------
0
重新插入,及切换日志操作正常
SQL> insert into t select * from dba_objects;
50351 rows created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
最后,应该将整个数据库exp导出,重新建库。