一、非归档模式非current（inactive）
1、环境
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIV STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- ------ -------------------------------- ------------- ------------ ------------ ------------ ----------
1 1 28 209715200 512 1 NO CURRENT 11780221 06-JUN-23 1.8447E+19 0
2 1 26 209715200 512 1 YES INACTIVE 11523885 06-MAY-23 11673233 17-MAY-23 0
3 1 27 209715200 512 1 YES INACTIVE 11673233 17-MAY-23 11780221 06-JUN-23 0
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_REC CON_ID
---------- -------------- -------------- -------------------------------------------------------------------------------- ------ ----------
3 ONLINE /u01/app/oracle/oradata/ORCL/redo03.log NO 0
2 ONLINE /u01/app/oracle/oradata/ORCL/redo02.log NO 0
1 ONLINE /u01/app/oracle/oradata/ORCL/redo01.log NO 0
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch
Oldest online log sequence 26
Current log sequence 28
2、损坏
[oracle@19c01 ~]$ dd if=/dev/null of=/u01/app/oracle/oradata/ORCL/redo02.log
记录了0+0 的读入
记录了0+0 的写出
0 bytes copied, 8.8403e-05 s, 0.0 kB/s
3、重启
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 2415917880 bytes
Fixed Size 26594104 bytes
Variable Size 620756992 bytes
Database Buffers 1761607680 bytes
Redo Buffers 6959104 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 700145
Session ID: 22 Serial number: 58312
4、日志报错
2023-06-08T14:42:27.168008+08:00
Database mounted in Exclusive Mode
Lost write protection disabled
.... (PID:700130): Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch [krsd.c:18141]
Completed: ALTER DATABASE MOUNT
2023-06-08T14:42:27.232757+08:00
ALTER DATABASE OPEN
2023-06-08T14:42:27.235831+08:00
Ping without log force is disabled:
instance mounted in exclusive mode.
Endian type of dictionary set to little
2023-06-08T14:42:27.249096+08:00
TT00 (PID:700147): Gap Manager starting
2023-06-08T14:42:27.282323+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_700097.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ORCL/redo02.log'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
2023-06-08T14:42:27.282400+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_700097.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ORCL/redo02.log'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
2023-06-08T14:42:27.283945+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_700145.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ORCL/redo02.log'
USER (ospid: ): terminating the instance due to ORA error
2023-06-08T14:42:27.371533+08:00
System state dump requested by (instance=1, osid=700145), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_diag_700080.trc
2023-06-08T14:42:27.438187+08:00
Dumping diagnostic data in directory=[cdmp_20230608144227], requested by (instance=1, osid=700145), summary=[abnormal instance termination].
2023-06-08T14:42:28.473087+08:00
Instance terminated by USER, pid = 700145 --注意：这里干掉了实例
5、清空日志
QL> startup mount;
ORACLE instance started.
Total System Global Area 2415917880 bytes
Fixed Size 26594104 bytes
Variable Size 620756992 bytes
Database Buffers 1761607680 bytes
Redo Buffers 6959104 bytes
Database mounted.
SQL> alter database clear logfile group 2;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
/
System altered.
SQL> /
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIV STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- ------ -------------------------------- ------------- ------------ ------------ ------------ ----------
1 1 31 209715200 512 1 NO INACTIVE 11820033 08-JUN-23 11820036 08-JUN-23 0
2 1 32 209715200 512 1 NO CURRENT 11820036 08-JUN-23 1.8447E+19 0
3 1 30 209715200 512 1 NO INACTIVE 11820029 08-JUN-23 11820033 08-JUN-23 0
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_REC CON_ID
---------- -------------- -------------- -------------------------------------------------------------------------------- ------ ----------
3 ONLINE /u01/app/oracle/oradata/ORCL/redo03.log NO 0
2 ONLINE /u01/app/oracle/oradata/ORCL/redo02.log NO 0
1 ONLINE /u01/app/oracle/oradata/ORCL/redo01.log NO 0
二、非归档模式非current（active）
1、破环
SQL> drop table t;
Table dropped.
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIV STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- ------ -------------------------------- ------------- ------------ ------------ ------------ ----------
1 1 34 209715200 512 1 NO INACTIVE 11820122 08-JUN-23 11820125 08-JUN-23 0
2 1 35 209715200 512 1 NO ACTIVE 11820125 08-JUN-23 11820160 08-JUN-23 0
3 1 36 209715200 512 1 NO CURRENT 11820160 08-JUN-23 1.8447E+19 0
[oracle@19c01 ~]$ dd if=/dev/null of=/u01/app/oracle/oradata/ORCL/redo02.log
记录了0+0 的读入
记录了0+0 的写出
0 bytes copied, 0.000137876 s, 0.0 kB/s
2、重启
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 2415917880 bytes
Fixed Size 26594104 bytes
Variable Size 620756992 bytes
Database Buffers 1761607680 bytes
Redo Buffers 6959104 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ORCL/redo02.log'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
3、报错
2023-06-08T14:59:24.797045+08:00
Started redo scan
2023-06-08T14:59:24.797834+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_713184.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ORCL/redo02.log'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
2023-06-08T14:59:24.818625+08:00
Slave encountered ORA-10388 exception during crash recovery
2023-06-08T14:59:24.819099+08:00
Slave encountered ORA-10388 exception during crash recovery
2023-06-08T14:59:24.821281+08:00
Aborting crash recovery due to error 313
2023-06-08T14:59:24.821402+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_713184.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ORCL/redo02.log'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
2023-06-08T14:59:24.821623+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_713184.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ORCL/redo02.log'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
ORA-313 signalled during: ALTER DATABASE OPEN...
2023-06-08T14:59:24.839272+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_mz00_713190.trc:
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ORCL/redo02.log'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
2023-06-08T14:59:24.902035+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_mz00_713190.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ORCL/redo02.log'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
2023-06-08T14:59:24.964957+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_mz00_713190.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ORCL/redo02.log'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
虽然报错和inactive报错一样，但是这里并没有终止实例
4、检查日志状态
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIV STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- ------ -------------------------------- ------------- ------------ ------------ ------------ ----------
1 1 34 209715200 512 1 NO INACTIVE 11820122 08-JUN-23 11820125 08-JUN-23 0
3 1 36 209715200 512 1 NO CURRENT 11820160 08-JUN-23 1.8447E+19 0
2 1 35 209715200 512 1 NO ACTIVE 11820125 08-JUN-23 11820160 08-JUN-23 0
SQL> col member for a80
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_REC CON_ID
---------- -------------- -------------- -------------------------------------------------------------------------------- ------ ----------
3 ONLINE /u01/app/oracle/oradata/ORCL/redo03.log NO 0
2 ONLINE /u01/app/oracle/oradata/ORCL/redo02.log NO 0
1 ONLINE /u01/app/oracle/oradata/ORCL/redo01.log NO 0
5、处置
SQL> alter database clear logfile group 2;
alter database clear logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance orcl (thread 1)
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ORCL/redo02.log'
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance orcl (thread 1)
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ORCL/redo02.log'
看来因为日志是active状态的，不能clear
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ORCL/redo02.log'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
SQL> recover database until cancel;
ORA-00279: change 11820125 generated at 06/08/2023 14:56:50 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_35_1134038767.dbf
ORA-00280: change 11820125 for thread 1 is in sequence #35
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_35_1134038767.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/19.3.0/dbhome_1/dbs/arch1_35_1134038767.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/ORCL/system01.dbf'
SQL> alter database open resetlogs;
Database altered.
看来只需要open resetlogs就可以
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIV STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- ------ -------------------------------- ------------- ------------ ------------ ------------ ----------
1 1 1 209715200 512 1 NO CURRENT 11820126 08-JUN-23 1.8447E+19 0
2 1 0 209715200 512 1 YES UNUSED 0 0 0
3 1 0 209715200 512 1 YES UNUSED 0 0 0
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_REC CON_ID
---------- -------------- -------------- -------------------------------------------------------------------------------- ------ ----------
3 ONLINE /u01/app/oracle/oradata/ORCL/redo03.log NO 0
2 ONLINE /u01/app/oracle/oradata/ORCL/redo02.log NO 0
1 ONLINE /u01/app/oracle/oradata/ORCL/redo01.log NO 0
主意2 、3日志组均被重置
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIV STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- ------ -------------------------------- ------------- ------------ ------------ ------------ ----------
1 1 1 209715200 512 1 NO CURRENT 11820126 08-JUN-23 1.8447E+19 0
2 1 0 209715200 512 1 YES UNUSED 0 0 0
3 1 0 209715200 512 1 YES UNUSED 0 0 0
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_REC CON_ID
---------- -------------- -------------- -------------------------------------------------------------------------------- ------ ----------
3 ONLINE /u01/app/oracle/oradata/ORCL/redo03.log NO 0
2 ONLINE /u01/app/oracle/oradata/ORCL/redo02.log NO 0
1 ONLINE /u01/app/oracle/oradata/ORCL/redo01.log NO 0
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch
Oldest online log sequence 1
Current log sequence 1
开启了新的incarnation
三、非归档(active)(shutdown immediate)
1、准备
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIV STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- ------ -------------------------------- ------------- ------------ ------------ ------------ ----------
1 1 1 209715200 512 1 NO ACTIVE 11820126 08-JUN-23 11821007 08-JUN-23 0
2 1 2 209715200 512 1 NO CURRENT 11821007 08-JUN-23 1.8447E+19 0
3 1 0 209715200 512 1 YES UNUSED 0 0 0
[oracle@19c01 ~]$ dd if=/dev/null of=/u01/app/oracle/oradata/ORCL/redo01.log
记录了0+0 的读入
记录了0+0 的写出
0 bytes copied, 0.000107759 s, 0.0 kB/s
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 2415917880 bytes
Fixed Size 26594104 bytes
Variable Size 620756992 bytes
Database Buffers 1761607680 bytes
Redo Buffers 6959104 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 723472
Session ID: 252 Serial number: 24390
2、报错
2023-06-08T15:13:08.698626+08:00
.... (PID:723467): Redo network throttle feature is disabled at mount time
2023-06-08T15:13:08.702318+08:00
Successful mount of redo thread 1, with mount id 1666794624
2023-06-08T15:13:08.702683+08:00
Database mounted in Exclusive Mode
Lost write protection disabled
.... (PID:723467): Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch [krsd.c:18141]
Completed: ALTER DATABASE MOUNT
2023-06-08T15:13:08.772010+08:00
ALTER DATABASE OPEN
2023-06-08T15:13:08.774620+08:00
Ping without log force is disabled:
instance mounted in exclusive mode.
Endian type of dictionary set to little
2023-06-08T15:13:08.804744+08:00
TT00 (PID:723474): Gap Manager starting
2023-06-08T15:13:08.818568+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_723434.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ORCL/redo01.log'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
2023-06-08T15:13:08.818645+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_723434.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ORCL/redo01.log'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
2023-06-08T15:13:08.819251+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_723472.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ORCL/redo01.log'
USER (ospid: ): terminating the instance due to ORA error
2023-06-08T15:13:08.923680+08:00
System state dump requested by (instance=1, osid=723472), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_diag_723417.trc
2023-06-08T15:13:08.968264+08:00
Dumping diagnostic data in directory=[cdmp_20230608151308], requested by (instance=1, osid=723472), summary=[abnormal instance termination].
2023-06-08T15:13:10.010509+08:00
Instance terminated by USER, pid = 723472
3、处置
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2415917880 bytes
Fixed Size 26594104 bytes
Variable Size 620756992 bytes
Database Buffers 1761607680 bytes
Redo Buffers 6959104 bytes
Database mounted.
SQL> alter database clear logfile group 1;
Database altered.
SQL> alter database open;
Database altered.
与处置非active方法一样
四、非归档(active)处置方法二
1、环境
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIV STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- ------ -------------------------------- ------------- ------------ ------------ ------------ ----------
1 1 3 209715200 512 1 NO CURRENT 11822469 08-JUN-23 1.8447E+19 0
2 1 2 209715200 512 1 NO ACTIVE 11821007 08-JUN-23 11822469 08-JUN-23 0
3 1 0 209715200 512 1 YES UNUSED 0 0 0
[oracle@19c01 ~]$ dd if=/dev/null of=/u01/app/oracle/oradata/ORCL/redo02.log
记录了0+0 的读入
记录了0+0 的写出
0 bytes copied, 0.000115119 s, 0.0 kB/s
2、报错
SQL> startup
ORACLE instance started.
Total System Global Area 2415917880 bytes
Fixed Size 26594104 bytes
Variable Size 620756992 bytes
Database Buffers 1761607680 bytes
Redo Buffers 6959104 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ORCL/redo02.log'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
Lost write protection disabled
.... (PID:732914): Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch [krsd.c:18141]
Completed: ALTER DATABASE MOUNT
2023-06-08T15:25:42.434168+08:00
ALTER DATABASE OPEN
2023-06-08T15:25:42.436690+08:00
Ping without log force is disabled:
instance mounted in exclusive mode.
2023-06-08T15:25:42.440325+08:00
Beginning crash recovery of 1 threads
parallel recovery started with 2 processes
Thread 1: Recovery starting at checkpoint rba (logseq 2 block 1688), scn 0
2023-06-08T15:25:42.455112+08:00
Started redo scan
2023-06-08T15:25:42.455771+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_732919.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ORCL/redo02.log'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
2023-06-08T15:25:42.476555+08:00
Slave encountered ORA-10388 exception during crash recovery
2023-06-08T15:25:42.477891+08:00
Slave encountered ORA-10388 exception during crash recovery
2023-06-08T15:25:42.480608+08:00
Aborting crash recovery due to error 313
2023-06-08T15:25:42.480743+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_732919.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ORCL/redo02.log'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
2023-06-08T15:25:42.481158+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_732919.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ORCL/redo02.log'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
ORA-313 signalled during: ALTER DATABASE OPEN...
2023-06-08T15:25:42.495543+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_mz00_732925.trc:
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ORCL/redo02.log'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
2023-06-08T15:25:42.560288+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_mz00_732925.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ORCL/redo02.log'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
2023-06-08T15:25:42.623247+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_mz00_732925.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ORCL/redo02.log'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
3、启动
_allow_resetlogs_corruption=true
此参数允许用于测试目的的错误模拟。通常它用于 Oracle 内部目的。
_allow_error_simulation=true
注意： Oracle 将不支持使用此方法恢复的数据库，除非随后将其导出并重建。
本质上，使用 _allow_resetlogs_corruption 会强制打开数据文件，即使它们的 SCN 不匹配；然后，在下一个检查点，旧的 SCN 值被覆盖。就并发性而言，这可能会使数据库处于未知状态
通过设置 _ALLOW_RESETLOGS_CORRUPTION=TRUE，某些一致性检查 在数据库打开阶段被跳过。这基本上意味着它不会检查 数据文件标头，了解关闭前的状态以及关闭方式。此参数有助于打开数据库，即使数据文件标头与 SCN 详细信息不同步。它将允许您使用 resetlogs 选项打开数据库，甚至是需要恢复的数据文件。一旦使用重置日志打开数据库，下一个检查点 SCN 将更新为数据文件头。这使数据库处于未知状态，因为它避免了前滚过程。
SQL> startup pfile='/tmp/pfile.ora';
ORACLE instance started.
Total System Global Area 2415917880 bytes
Fixed Size 26594104 bytes
Variable Size 620756992 bytes
Database Buffers 1761607680 bytes
Redo Buffers 6959104 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ORCL/redo02.log'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
SQL> startup mount pfile='/tmp/pfile.ora';
ORACLE instance started.
Total System Global Area 2415917880 bytes
Fixed Size 26594104 bytes
Variable Size 620756992 bytes
Database Buffers 1761607680 bytes
Redo Buffers 6959104 bytes
Database mounted.
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;
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ORCL/redo02.log'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
SQL> recover database until cancel;
ORA-00279: change 11821543 generated at 06/08/2023 15:18:53 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_2_1138979250.dbf
ORA-00280: change 11821543 for thread 1 is in sequence #2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto;
ORA-00308: cannot open archived log 'auto;'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_2_1138979250.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/19.3.0/dbhome_1/dbs/arch1_2_1138979250.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/ORCL/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: 738985
Session ID: 18 Serial number: 5683
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: 738985
Session ID: 18 Serial number: 5683
4、报错日志
TT00 (PID:740208): Gap Manager starting
2023-06-08T15:35:12.563193+08:00
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
stopping change tracking
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_738985.trc (incident=218711):
ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_218711/orcl_ora_738985_i218711.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Please look for redo dump in pinned buffers history in incident trace file, if not dumped for what so ever reason, use the following command to dump it at the earliest. ALTER SYSTEM DUMP REDO DBA MIN 1 128 DBA MAX 1 128 SCN MIN 1;
2023-06-08T15:35:13.165040+08:00
*****************************************************************
An internal routine has requested a dump of selected redo.
This usually happens following a specific internal error, when
analysis of the redo logs will help Oracle Support with the
diagnosis.
It is recommended that you retain all the redo logs generated (by
all the instances) during the past 12 hours, in case additional
redo dumps are required to help with the diagnosis.
*****************************************************************
Undo initialization recovery: err:600 start: 60606162 end: 60606805 diff: 643 ms (0.6 seconds)
2023-06-08T15:35:13.268707+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_738985.trc:
ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [], [], [], [], [], [], [], []
2023-06-08T15:35:13.268752+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_738985.trc:
ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_738985.trc (incident=218712):
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/orcl/orcl/incident/incdir_218712/orcl_ora_738985_i218712.trc
2023-06-08T15:35:13.706158+08:00
opiodr aborting process unknown ospid (738985) as a result of ORA-603
5、中bug
Bug 31887074 sr21.1bigscn_hipu3 - trc - ksfdopn2 - ORA-600 [kcbzib_kcrsds_1]
This note gives a brief overview of bug 31887074.
The content was last updated on: 16-AUG-2022
Click here for details of each of the sections below.
Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions BELOW 23.1
Versions confirmed as being affected
(None Specified)
Platforms affected Generic (all / most platforms affected)
Fixed:
The fix for 31887074 is first included in
(None Specified)
Interim patches may be available for earlier versions - click here to check.
Symptoms:
Related To:
Instance May Crash
ORA-600 [kcbzib_kcrsds_1]
(None Specified)
Description
Test BigSCN changes with core regress
- Signaling function: ksfdopn2 (ksfd.c)
- Symptom: ORA-600 [kcbzib_kcrsds_1]
workaround: None
6、再来
SQL> startup mount pfile='/tmp/pfile.ora';
ORACLE instance started.
Total System Global Area 2415917880 bytes
Fixed Size 26594104 bytes
Variable Size 620756992 bytes
Database Buffers 1761607680 bytes
Redo Buffers 6959104 bytes
Database mounted.
SQL> recover database until cancel;
ORA-00279: change 12022337 generated at 06/08/2023 15:44:30 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_3_1138980911.dbf
ORA-00280: change 12022337 for thread 1 is in sequence #3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_3_1138980911.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/19.3.0/dbhome_1/dbs/arch1_3_1138980911.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/ORCL/system01.dbf'
SQL> alter database open resetlogs;
Database altered.
最后修改时间：2023-06-09 10:38:46
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
