【Oracle】ORA-00313 ORA-00312问题解决（无备份）

db02:/home/oracle$ export ORACLE_SID=bjltjcpt2
db02:/home/oracle$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Nov 14 08:07:29 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  922849280 bytes
Fixed Size                  2226816 bytes
Variable Size             864028032 bytes
Database Buffers           50331648 bytes
Redo Buffers                6262784 bytes
Database mounted.
ORA-00313: open failed for members of log group 7 of thread 2 ORA-00312: online log 7 thread 2: '+REDOSSD/bjltjcpt/onlinelog/group_7.log'
ORA-17503: ksfdopn:2 Failed to open file
+REDOSSD/bjltjcpt/onlinelog/group_7.log
ORA-15173: entry 'bjltjcpt' does not exist in directory '/'

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> select group#,thread#,members,status from v$log;

    GROUP#    THREAD#    MEMBERS STATUS
---------- ---------- ---------- ----------------
         1          1          1 INACTIVE
         2          1          1 INACTIVE
         3          1          1 INACTIVE
         4          1          1 INACTIVE
         5          1          1 INACTIVE
         6          1          1 CURRENT
         7          2          1 CURRENT
         8          2          1 INACTIVE
         9          2          1 INACTIVE
        10          2          1 INACTIVE
        11          2          1 INACTIVE
        12          2          1 INACTIVE

12 rows selected.

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> alter database clear logfile group 1;

Database altered.

SQL> alter database clear logfile group 2;

Database altered.

SQL> alter database clear logfile group 3;

Database altered.

SQL> alter database clear logfile group 4;

Database altered.

SQL> alter database clear logfile group 5;

Database altered.

SQL> alter database clear logfile group 6;
alter database clear logfile group 6
*
ERROR at line 1:
ORA-01624: log 6 needed for crash recovery of instance bjltjcpt1 (thread 1)
ORA-00312: online log 6 thread 1: '+REDOSSD/bjltjcpt/onlinelog/group_6.log'

SQL> alter database clear logfile group 7;
alter database clear logfile group 7
*
ERROR at line 1:
ORA-01624: log 7 needed for crash recovery of instance bjltjcpt2 (thread 2)
ORA-00312: online log 7 thread 2: '+REDOSSD/bjltjcpt/onlinelog/group_7.log'

SQL> alter database clear logfile group 8;

Database altered.

SQL> alter database clear logfile group 9;

Database altered.

SQL> alter database clear logfile group 10;

Database altered.

SQL> alter database clear logfile group 11;

Database altered.

SQL> alter database clear logfile group 12;

Database altered.

SQL> select group#,thread#,members,status from v$log;

    GROUP#    THREAD#    MEMBERS STATUS
---------- ---------- ---------- ----------------
         1          1          1 UNUSED
         2          1          1 UNUSED
         3          1          1 UNUSED
         4          1          1 UNUSED
         5          1          1 UNUSED
         6          1          1 CURRENT
         7          2          1 CURRENT
         8          2          1 UNUSED
         9          2          1 UNUSED
        10          2          1 UNUSED
        11          2          1 UNUSED
        12          2          1 UNUSED

12 rows selected.

SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open

SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open

SQL> alter database clear unarchived logfile group 6;
alter database clear unarchived logfile group 6
*
ERROR at line 1:
ORA-01624: log 6 needed for crash recovery of instance bjltjcpt1 (thread 1)
ORA-00312: online log 6 thread 1: '+REDOSSD/bjltjcpt/onlinelog/group_6.log'

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>  alter system switch logfile;
 alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open

SQL> shutown immediate
SP2-0734: unknown command beginning "shutown im..." - rest of line ignored.
SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  922849280 bytes
Fixed Size                  2226816 bytes
Variable Size             864028032 bytes
Database Buffers           50331648 bytes
Redo Buffers                6262784 bytes
Database mounted.
ORA-00313: open failed for members of log group 7 of thread 2
ORA-00312: online log 7 thread 2: '+REDOSSD/bjltjcpt/onlinelog/group_7.log'
ORA-17503: ksfdopn:2 Failed to open file +REDOSSD/bjltjcpt/onlinelog/group_7.log
ORA-15173: entry 'group_7.log' does not exist in directory 'onlinelog'

SQL> recover database until cancel;
ORA-00279: change 14966658777392 generated at 11/10/2016 02:30:11 needed for thread 1
ORA-00289: suggestion : +FRA/bjltjcpt/archive_log/1_816_925645456.dbf
ORA-00280: change 14966658777392 for thread 1 is in sequence #816

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '+FRA/bjltjcpt/archive_log/1_816_925645456.dbf'
ORA-17503: ksfdopn:2 Failed to open file +FRA/bjltjcpt/archive_log/1_816_925645456.dbf
ORA-15173: entry '1_816_925645456.dbf' does not exist in directory 'archive_log'

ORA-00308: cannot open archived log '+FRA/bjltjcpt/archive_log/1_816_925645456.dbf'
ORA-17503: ksfdopn:2 Failed to open file +FRA/bjltjcpt/archive_log/1_816_925645456.dbf
ORA-15173: entry '1_816_925645456.dbf' does not exist in directory 'archive_log'

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/bjltjcpt/datafile/system.510.925642071'

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/bjltjcpt/datafile/system.510.925642071'

SQL> arhchive log list;
SP2-0734: unknown command beginning "arhchive l..." - rest of line ignored.
SQL> archivelog list;
SP2-0734: unknown command beginning "archivelog..." - rest of line ignored.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +FRA/bjltjcpt/archive_log
Oldest online log sequence     871
Next log sequence to archive   871
Current log sequence           871
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> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

System altered.

SQL> alter system set "_allow_terminal_recovery_corruption"=true scope=spfile;

System altered.

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  922849280 bytes
Fixed Size                  2226816 bytes
Variable Size             864028032 bytes
Database Buffers           50331648 bytes
Redo Buffers                6262784 bytes
Database mounted.
ORA-00313: open failed for members of log group 7 of thread 2
ORA-00312: online log 7 thread 2: '+REDOSSD/bjltjcpt/onlinelog/group_7.log'
ORA-17503: ksfdopn:2 Failed to open file +REDOSSD/bjltjcpt/onlinelog/group_7.log
ORA-15173: entry 'group_7.log' does not exist in directory 'onlinelog'

SQL> recover database until cancel;
ORA-00279: change 14966658777392 generated at 11/10/2016 02:30:11 needed for thread 1
ORA-00289: suggestion : +FRA/bjltjcpt/archive_log/1_816_925645456.dbf
ORA-00280: change 14966658777392 for thread 1 is in sequence #816

Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log '+FRA/bjltjcpt/archive_log/1_816_925645456.dbf'
ORA-17503: ksfdopn:2 Failed to open file +FRA/bjltjcpt/archive_log/1_816_925645456.dbf
ORA-15173: entry '1_816_925645456.dbf' does not exist in directory 'archive_log'

ORA-00308: cannot open archived log '+FRA/bjltjcpt/archive_log/1_816_925645456.dbf'
ORA-17503: ksfdopn:2 Failed to open file +FRA/bjltjcpt/archive_log/1_816_925645456.dbf
ORA-15173: entry '1_816_925645456.dbf' does not exist in directory 'archive_log'

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/bjltjcpt/datafile/system.510.925642071'

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kclchkblk_4], [3484], [2992720189], [3484], [2992718134], [], [], [], [], [], [], []
Process ID: 11468884
Session ID: 432 Serial number: 3

SQL> startup force
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
db01:/u01/app/oracle/product/11.2.0/db_1/dbs$sqlplus / as sysdba
db01:/u01/app/oracle/product/11.2.0/db_1/dbs$sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Nov 14 14:44:20 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  922849280 bytes
Fixed Size                  2226816 bytes
Variable Size             864028032 bytes
Database Buffers           50331648 bytes
Redo Buffers                6262784 bytes
Database mounted.
Database opened.

SQL> set pages 200 lines 200
SQL> select group#,thread#,members,archived,status from v$log;

    GROUP#    THREAD#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
         1          1          1 YES INACTIVE
         2          1          1 NO  CURRENT
         3          1          1 YES UNUSED
         4          1          1 YES UNUSED
         5          1          1 YES UNUSED
         6          1          1 YES UNUSED
         7          2          1 YES INACTIVE
         8          2          1 YES UNUSED
         9          2          1 YES UNUSED
        10          2          1 YES UNUSED
        11          2          1 YES UNUSED
        12          2          1 YES UNUSED

12 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> select group#,thread#,members,archived,status from v$log;

    GROUP#    THREAD#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
         1          1          1 YES ACTIVE
         2          1          1 YES ACTIVE
         3          1          1 YES ACTIVE
         4          1          1 YES ACTIVE
         5          1          1 NO  CURRENT
         6          1          1 YES INACTIVE
         7          2          1 YES INACTIVE
         8          2          1 YES ACTIVE
         9          2          1 NO  CURRENT
        10          2          1 YES UNUSED
        11          2          1 YES UNUSED
        12          2          1 YES UNUSED

12 rows selected.

SQL>  alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL>  select group#,thread#,members,archived,status from v$log;

    GROUP#    THREAD#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
         1          1          1 YES INACTIVE
         2          1          1 YES INACTIVE
         3          1          1 NO  CURRENT
         4          1          1 YES INACTIVE
         5          1          1 YES INACTIVE
         6          1          1 YES INACTIVE
         7          2          1 YES INACTIVE
         8          2          1 YES ACTIVE
         9          2          1 YES ACTIVE
        10          2          1 NO  CURRENT
        11          2          1 YES UNUSED
        12          2          1 YES UNUSED

12 rows selected.

SQL>  alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> select group#,thread#,members,archived,status from v$log;

    GROUP#    THREAD#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
         1          1          1 NO  CURRENT
         2          1          1 YES INACTIVE
         3          1          1 YES ACTIVE
         4          1          1 YES ACTIVE
         5          1          1 YES ACTIVE
         6          1          1 YES ACTIVE
         7          2          1 YES INACTIVE
         8          2          1 YES ACTIVE
         9          2          1 YES ACTIVE
        10          2          1 YES ACTIVE
        11          2          1 NO  CURRENT
        12          2          1 YES UNUSED

12 rows selected.

SQL>  alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> select group#,thread#,members,archived,status from v$log;

    GROUP#    THREAD#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
         1          1          1 YES INACTIVE
         2          1          1 YES INACTIVE
         3          1          1 YES INACTIVE
         4          1          1 YES INACTIVE
         5          1          1 NO  CURRENT
         6          1          1 YES INACTIVE
         7          2          1 YES INACTIVE
         8          2          1 YES ACTIVE
         9          2          1 YES ACTIVE
        10          2          1 YES ACTIVE
        11          2          1 YES ACTIVE
        12          2          1 NO  CURRENT

12 rows selected.

 

数据库恢复成功后，建议逻辑备份全库或者导出重要数据，重建数据库进行恢复还原。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30327022/viewspace-2128579/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30327022/viewspace-2128579/