1，创建测试表
[oracle@devin1 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 31 14:39:29 2020 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> create table del_log_test (id number, name nvarchar2(10)); Table created.
查看当前日志状态：
SQL> select group#,status from v$log; GROUP# STATUS ---------- ---------------- 1 INACTIVE 2 INACTIVE 3 CURRENT
插入测试数据：
SQL> insert into del_log_test values(1,'devin1'); 1 row created. SQL> insert into del_log_test values(2,'devin2'); 1 row created. SQL> commit; Commit complete. SQL> insert into del_log_test values(3,'devin3'); 1 row created.
--id=3的数据暂不提交
删除当前日志，abort方式关闭数据库 [oracle@devin1 ~]$ cd /u01/app/oracle/oradata/ora12c/ [oracle@devin1 ora12c]$ rm -rf redo03.log SQL> shutdown abort ORACLE instance shut down.
重启数据库：
SQL> startup ORACLE instance started. Total System Global Area 3103784960 bytes Fixed Size 8625512 bytes Variable Size 922747544 bytes Database Buffers 2164260864 bytes Redo Buffers 8151040 bytes Database mounted. ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ora12c/redo03.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 7
alert log：
Errors in file /u01/app/oracle/diag/rdbms/ora12c/ora12c/trace/ora12c_ora_6838.trc: ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ora12c/redo03.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 7 ORA-313 signalled during: ALTER DATABASE OPEN...
2，恢复
首先设置隐含参数强制起库看看：
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile; System altered.
重启数据库到mount：
SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 3103784960 bytes Fixed Size 8625512 bytes Variable Size 922747544 bytes Database Buffers 2164260864 bytes Redo Buffers 8151040 bytes Database mounted.
恢复数据库，resetlogs方式打开数据库：
SQL> recover database until cancel ;
ORA-00279: change 602910137 generated at 09/02/2020 08:20:36 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/12.2/db_home1/dbs/arch1_6_1049905180.dbf
ORA-00280: change 602910137 for thread 1 is in sequence #6
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/u01/app/oracle/product/12.2/db_home1/dbs/arch1_6_1049905180.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
ORA-00308: cannot open archived log
'/u01/app/oracle/product/12.2/db_home1/dbs/arch1_6_1049905180.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
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/app/oracle/oradata/ora12c/system01.dbf'
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [],
[], [], [], [], [], [], []
Process ID: 7097
Session ID: 1 Serial number: 37386
alert log报了600错误：
Errors in file /u01/app/oracle/diag/rdbms/ora12c/ora12c/trace/ora12c_ora_7097.trc: ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [], [], [], [], [], [], [], [] 2020-09-02T08:56:23.615676+08:00 Errors in file /u01/app/oracle/diag/rdbms/ora12c/ora12c/trace/ora12c_ora_7097.trc: ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [], [], [], [], [], [], [], [] 2020-09-02T08:56:23.615706+08:00 Error 600 happened during db open, shutting down database Errors in file /u01/app/oracle/diag/rdbms/ora12c/ora12c/trace/ora12c_ora_7097.trc (incident=117970): ORA-00603: ORACLE server session terminated by fatal error ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /u01/app/oracle/diag/rdbms/ora12c/ora12c/incident/incdir_117970/ora12c_ora_7097_i117970.trc opiodr aborting process unknown ospid (7097) as a result of ORA-603
ORA-600 kcbzib_kcrsds_1一般都是由于使用隐含参数_allow_resetlogs_corruption强制拉库导致。再次尝试open resetlogs，发现控制文件已经损坏。
SQL> startup mount ORACLE instance started. Total System Global Area 3103784960 bytes Fixed Size 8625512 bytes Variable Size 922747544 bytes Database Buffers 2164260864 bytes Redo Buffers 8151040 bytes Database mounted. SQL> recover database until cancel; ORA-00283: recovery session canceled due to errors ORA-16433: The database or pluggable database must be opened in read/write mode.
接下来需要重建控制文件，推进SCN重建控制文件：
SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup nomount ORACLE instance started. Total System Global Area 3103784960 bytes Fixed Size 8625512 bytes Variable Size 922747544 bytes Database Buffers 2164260864 bytes Redo Buffers 8151040 bytes SQL> CREATE CONTROLFILE REUSE DATABASE "ora12c" RESETLOGS NOARCHIVELOG 2 MAXLOGFILES 50 3 MAXLOGMEMBERS 5 4 MAXDATAFILES 100 5 MAXINSTANCES 1 6 MAXLOGHISTORY 226 7 LOGFILE 8 GROUP 1 '/u01/app/oracle/oradata/ora12c/redo01.log' SIZE 200M, 9 GROUP 2 '/u01/app/oracle/oradata/ora12c/redo02.log' SIZE 200M, 10 GROUP 3 '/u01/app/oracle/oradata/ora12c/redo03.log' SIZE 200M 11 DATAFILE 12 '/u01/app/oracle/oradata/ora12c/system01.dbf', 13 '/u01/app/oracle/oradata/ora12c/ccps_data.dbf', 14 '/u01/app/oracle/oradata/ora12c/sysaux01.dbf', 15 '/u01/app/oracle/oradata/ora12c/undotbs01.dbf', 16 '/u01/app/oracle/oradata/ora12c/ogg.dbf', 17 '/u01/app/oracle/oradata/ora12c/users01.dbf' 18 CHARACTER SET AL32UTF8; Control file created.
通过21307096事件来推SCN：
SQL> create pfile ='/home/oracle/pfile.ora' from spfile; File created.
pfile.ora添加event
event="21307096 trace name context forever, level 1" SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup mount pfile='/home/oracle/pfile.ora'; ORACLE instance started. Total System Global Area 3103784960 bytes Fixed Size 8625512 bytes Variable Size 922747544 bytes Database Buffers 2164260864 bytes Redo Buffers 8151040 bytes Database mounted. SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-00603: ORACLE server session terminated by fatal error ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [4194], [3], [5], [], [], [], [], [], [], [], [], [] Process ID: 9977 Session ID: 1 Serial number: 35878
还是open失败，并且报了ora-600 4194，4194是回滚段相关错误，通过将undo指到system表空间，先将数据库open，然后重建undo在pfile中添加如下参数：
undo_management= MANUAL undo_tablespace= SYSTEM
SQL> startup mount pfile='/home/oracle/pfile.ora';
ORACLE instance started.
Total System Global Area 3103784960 bytes
Fixed Size 8625512 bytes
Variable Size 922747544 bytes
Database Buffers 2164260864 bytes
Redo Buffers 8151040 bytes
Database mounted.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string SYSTEM
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SQL> recover database until cancel;
ORA-00279: change 603910149 generated at 09/02/2020 09:37:55 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/12.2/db_home1/dbs/arch1_1_1050053865.dbf
ORA-00280: change 603910149 for thread 1 is in sequence #1
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: '/u01/app/oracle/oradata/ora12c/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
Database altered.
最后重建undo表空间，重启数据库从spfile启动等略 查看表数据：
SQL> select * from del_log_test; select * from del_log_test * ERROR at line 1: ORA-00942: table or view does not exist
发现表已经丢失。是因为还没有checkpoint，数据未落盘就已经删除了redo，所以丢失。
