redo文件丢失,没有开启归档归档 恢复 _allow_resetlogs_corruption"=true

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup mount

ORACLE instance started.

 

Total System Global Area  285212672 bytes

Fixed Size                    1218992 bytes

Variable Size                  117442128 bytes

Database Buffers          163577856 bytes

Redo Buffers                    2973696 bytes

Database mounted.

SQL>

 

SQL> alter database noarchivelog;

 

Database altered.

 

SQL> alter database open;

 

Database altered.

 

产生交易 切换日志  数据库直接断电

 

SQL> select * from v$Log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME

---------- ---------- ---------- ---------- ---------- --- ---------- ------------- -------------------

 1            1               8   52428800             1 NO  ACTIVE             617432 2011-06-13 05:47:27

 2            1               9   52428800             1 NO  CURRENT             617437 2011-06-13 05:47:30

 3            1               7   52428800             1 NO  ACTIVE             617420 2011-06-13 05:47:02

 

SQL> select * from v$Logfile;

 

    GROUP# STATUS     TYPE    MEMBER                                            IS_

---------- ---------- ------- --------------------------------------------- ---

 3              ONLINE  /u01/oracle/oradata/db20/redo03.log            NO

 2              ONLINE  /u01/oracle/oradata/db20/redo02.log            NO

 1              ONLINE  /u01/oracle/oradata/db20/redo01.log            NO

 

SQL> ! cp /etc/passwd /u01/oracle/oradata/db20/redo01.log

 

SQL> shut abort

ORACLE instance shut down.

SQL>

 

SQL> startup

ORACLE instance started.

 

Total System Global Area  285212672 bytes

Fixed Size                    1218992 bytes

Variable Size                  117442128 bytes

Database Buffers          163577856 bytes

Redo Buffers                    2973696 bytes

Database mounted.

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/db20/redo01.log'

ORA-27046: file size is not a multiple of logical block size

Additional information: 1

 

 

SQL>

SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/db20/redo01.log'

ORA-27046: file size is not a multiple of logical block size

Additional information: 1

 

 

SQL> recover database until cancel;

ORA-00279: change 617432 generated at 06/13/2011 05:47:27 needed for thread 1

ORA-00289: suggestion : /home/oracle/arc1/1_8_753687918.dbf

ORA-00280: change 617432 for thread 1 is in sequence #8

 

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

 

ORA-00308: cannot open archived log '/home/oracle/arc1/1_8_753687918.dbf'

ORA-27037: unable to obtain file status

Linux 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/oracle/oradata/db20/system01.dbf'

 

 

SQL>

SQL> !  ls /home/oracle/arc1/1_[5-9]_753687918.dbf

ls: /home/oracle/arc1/1_[5-9]_753687918.dbf: 没有那个文件或目录

 

数据库根本没归档 但数据库还根据 log_archive_dest_1 和 log_archive_format 参数的记录去找归档

 

 

借助隐藏参数不要再恢复了,也不要redo了,直接启库

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

 

System altered.

 

 

SQL> shut immediate

ORA-01109: database not open

 

 

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area  285212672 bytes

Fixed Size                    1218992 bytes

Variable Size                  117442128 bytes

Database Buffers          163577856 bytes

Redo Buffers                    2973696 bytes

Database mounted.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

 

 

SQL> alter database open RESETLOGS;

 

Database altered.

 

SQL>

 

SQL> alter system set "_allow_resetlogs_corruption"=false scope=spfile;

 

System altered.

 

SQL> shut immediate

ORA-01507: database not mounted

 

 

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area  285212672 bytes

Fixed Size                    1218992 bytes

Variable Size                  117442128 bytes

Database Buffers          163577856 bytes

Redo Buffers                    2973696 bytes

Database mounted.

Database opened.

SQL>