解决Oracle数据库突然连接不了报Session ID: 0 Serial number: 0的错误
Oracle数据库突然连接不上了，出现以下问题
- 报错：
SQL> conn c##scott/tiger
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0
Warning: You are no longer connected to ORACLE.
SQL>
SQL>
SQL> select * from emp;
SP2-0640: Not connected
提示没有连接，尝试重启一下：
SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
提示已经启动了，这什么问题？
然后，查看一下可插拔数据库启动状态：
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 3050600444 PDB$SEED MOUNTED
3 131642792 PDBORCL MOUNTED
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
也没有open，再次登录dba用户登录再尝试启动：
SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> SHUTDOWN IMMEDIATE;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1140850688 bytes
Fixed Size 2923584 bytes
Variable Size 721421248 bytes
Database Buffers 402653184 bytes
Redo Buffers 13852672 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
依然报：ORA-01589: must use RESETLOGS or NORESETLOGS option for database open错误，不过现在已经提示去must use RESETLOGS or NORESETLOGS option for database open
SQL> conn sys as sysdba;
Enter password:
Connected.
SQL>
SQL>
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1140850688 bytes
Fixed Size 2923584 bytes
Variable Size 721421248 bytes
Database Buffers 402653184 bytes
Redo Buffers 13852672 bytes
Database mounted.
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: '/oracle/oradata/orcl/system01.dbf'
按照以上步骤操作，还是出现错误：ORA-01194: file 1 needs more recovery to be consistent,接下来怎么办呢？
- 以下是正式的恢复过程，虽然有点乱但是，总算是恢复成功了。
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2925981 generated at 05/30/2025 10:50:46 needed for thread 1
ORA-00289: suggestion :
/oracle/fast_recovery_area/ORCL/archivelog/2025_06_01/o1_mf_1_36_%u_.arc
ORA-00280: change 2925981 for thread 1 is in sequence #36
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
2925981
ORA-00308: cannot open archived log '2925981'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
recover database using backup controlfile until cancel;
ORA-00308: cannot open archived log 'recover'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log
'/oracle/fast_recovery_area/ORCL/archivelog/2025_06_01/o1_mf_1_36_%u_.arc'
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: '/oracle/oradata/orcl/system01.dbf'
SQL>
SQL>
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2925981 generated at 05/30/2025 10:50:46 needed for thread 1
ORA-00289: suggestion :
/oracle/fast_recovery_area/ORCL/archivelog/2025_06_01/o1_mf_1_36_%u_.arc
ORA-00280: change 2925981 for thread 1 is in sequence #36
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log
'/oracle/fast_recovery_area/ORCL/archivelog/2025_06_01/o1_mf_1_36_%u_.arc'
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: '/oracle/oradata/orcl/system01.dbf'
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2925981 generated at 05/30/2025 10:50:46 needed for thread 1
ORA-00289: suggestion :
/oracle/fast_recovery_area/ORCL/archivelog/2025_06_01/o1_mf_1_36_%u_.arc
ORA-00280: change 2925981 for thread 1 is in sequence #36
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oracle/fast_recovery_area/ORCL/archivelog/2025_06_01/o1_mf_1_36_%u_.arc
ORA-00308: cannot open archived log
'/oracle/fast_recovery_area/ORCL/archivelog/2025_06_01/o1_mf_1_36_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
alter system set "_allow_resetlogs_corruption"=true scope=spfile;
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: '/oracle/oradata/orcl/system01.dbf'
SQL>
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.
SQL>
SQL>
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: '/oracle/oradata/orcl/system01.dbf'
SQL> shutdown immedaite;
SP2-0717: illegal SHUTDOWN option
SQL> shutdown abort
ORACLE instance shut down.
SQL> shutdown immediate;
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3640
Additional information: 529514867
Process ID: 0
Session ID: 0 Serial number: 0
SQL> startup;
ORACLE instance started.
Total System Global Area 1140850688 bytes
Fixed Size 2923584 bytes
Variable Size 721421248 bytes
Database Buffers 402653184 bytes
Redo Buffers 13852672 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> startup force;
ORACLE instance started.
Total System Global Area 1140850688 bytes
Fixed Size 2923584 bytes
Variable Size 721421248 bytes
Database Buffers 402653184 bytes
Redo Buffers 13852672 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs
2 ;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [2662], [0], [2925988], [0],
[2930266], [4194545], [], [], [], [], [], []
Process ID: 5882
Session ID: 1 Serial number: 16481
SQL> SQL> sqlplus / as sysdba
SP2-0734: unknown command beginning "sqlplus / ..." - rest of line ignored.
SQL> sqlplus / as sysdba
SP2-0734: unknown command beginning "sqlplus / ..." - rest of line ignored.
SQL> startup force;
SP2-0642: SQL*Plus internal error state 2133, context 3114:0:0
Unsafe to proceed
ORA-03114: not connected to ORACLE
SQL> conn sys as sysdba;
Connected to an idle instance.
SQL>
SQL>
SQL>
SQL> startup force;
ORACLE instance started.
Total System Global Area 1140850688 bytes
Fixed Size 2923584 bytes
Variable Size 721421248 bytes
Database Buffers 402653184 bytes
Redo Buffers 13852672 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> alter system set "_allow_resetlogs_corruption" =false scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 1140850688 bytes
Fixed Size 2923584 bytes
Variable Size 721421248 bytes
Database Buffers 402653184 bytes
Redo Buffers 13852672 bytes
Database mounted.
Database opened.
- 其中关键操作步骤：
SQL> startup force;
ORACLE instance started.
Total System Global Area 1140850688 bytes
Fixed Size 2923584 bytes
Variable Size 721421248 bytes
Database Buffers 402653184 bytes
Redo Buffers 13852672 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs
2 ;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [2662], [0], [2925988], [0],
[2930266], [4194545], [], [], [], [], [], []
Process ID: 5882
Session ID: 1 Serial number: 16481
SQL> SQL> sqlplus / as sysdba
SP2-0734: unknown command beginning "sqlplus / ..." - rest of line ignored.
SQL> sqlplus / as sysdba
SP2-0734: unknown command beginning "sqlplus / ..." - rest of line ignored.
SQL> startup force;
SP2-0642: SQL*Plus internal error state 2133, context 3114:0:0
Unsafe to proceed
ORA-03114: not connected to ORACLE
SQL> conn sys as sysdba;
Connected to an idle instance.
SQL>
SQL>
SQL>
SQL> startup force;
ORACLE instance started.
Total System Global Area 1140850688 bytes
Fixed Size 2923584 bytes
Variable Size 721421248 bytes
Database Buffers 402653184 bytes
Redo Buffers 13852672 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> alter system set "_allow_resetlogs_corruption" =false scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 1140850688 bytes
Fixed Size 2923584 bytes
Variable Size 721421248 bytes
Database Buffers 402653184 bytes
Redo Buffers 13852672 bytes
Database mounted.
Database opened.
- 总结一下：其实就是采取强制启动来进行恢复。
-- 关闭再强制启动
shutdown immediate;
startup force;
-- 尝试再恢复
recover database using backup controlfile until cancel;
-- 没有效果，再停止强制启动
shutdown immediate;
-- 设置一下这个操作
alter system set "_allow_resetlogs_corruption"=true scope=spfile;
startup force;
-- 启动后再resetlogs
alter database open resetlogs
-- 意料之中的报错：
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [2662], [0], [2925988], [0],
[2930266], [4194545], [], [], [], [], [], []
Process ID: 5882
Session ID: 1 Serial number: 16481
-- 忽略不管它，继续关闭再重启，
shutdown immediate;
startup;
-- 此时已经修复好了。重启就会正常了
SQL> startup
ORACLE instance started.
Total System Global Area 1140850688 bytes
Fixed Size 2923584 bytes
Variable Size 721421248 bytes
Database Buffers 402653184 bytes
Redo Buffers 13852672 bytes
Database mounted.
Database opened.
-- 再把状态改回去。
alter system set "_allow_resetlogs_corruption" =false scope=spfile;
这样就可以恢复了，之所以出现这个问题，是因为进行的冷备份后还原引起的，导致数据不匹配了。
PS：这是一个经常会出现的问题，因此记录一下。这里的操作，是因为没有归档备份，如果有归档备份，还是需要从归档备份里面去恢复，不建议这样粗暴的恢复。
本文来自博客园，作者：刘文江，转载请注明原文链接：https://www.cnblogs.com/liuwenjiang/p/18905796
浙公网安备 33010602011771号