Onlineredo Log File Corruption recovery

The onlineredo log file is very important to the database. When the current log file is damaged, it usually means data loss, but it is not absolute.

The online redo log file is very important to the database. When the current log file is damaged, it usually means data loss, but it is not absolute.

Online redo log file corruption recovery

[Date:] Source: Linux community Author: aaron8219 [Font:]

The online redo log file is very important to the database. When the current log file is corrupted, it usually means data loss, but it is not absolute, you can restore the redo log file by some means. If you are lucky, uncommitted data will not be lost.

[Oracle @ zlm2 backup] $ sqlplus/as sysdba

SQL * Plus: Release 11.2.0.3.0 Production on Wed Dec 31 22:53:23 2014

Copyright (c) 1982,201 1, Oracle. All rights reserved.


Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn zlm/zlm
Connected.

-- Create a test table
SQL> create table test (tbid number (10 ));

Table created.

-- Insert data
SQL> insert into test values (1 );

1 row created.

Note that the insert operation commit is not performed.

SQL> select group #, sequence #, status, first_change # from v $ log;

GROUP # SEQUENCE # STATUS FIRST_CHANGE #

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

1 55 ACTIVE 1723785

2 56 CURRENT 1723866

3 54 INACTIVE 1723562

SQL>!

[Oracle @ zlm2 backup] $ cd/u01/app/oracle/oradata/zlm11g/

[Oracle @ zlm2 zlm11g] $ ll

Total 2572744

-Rwxrwxr-x 1 oracle oinstall 9748480 Dec 31 control01.ctl

-Rw-r ----- 1 oracle oinstall 362422272 Dec 31 example01.dbf

-Rw-r ----- 1 oracle oinstall 52429312 Dec 31 redo01.log

-Rw-r ----- 1 oracle oinstall 52429312 Dec 31 redo02.log

-Rw-r ----- 1 oracle oinstall 52429312 Dec 31 redo03.log

-Rw-r ----- 1 oracle oinstall 608182272 Dec 31 sysaux01.dbf

-Rw-r ----- 1 oracle oinstall 775954432 Dec 31 system01.dbf

-Rw-r ----- 1 oracle oinstall 20979712 Dec 31 temp01.dbf

-Rw-r ----- 1 oracle oinstall 178266112 Dec 31 undotbs01.dbf

-Rw-r ----- 1 oracle oinstall 13115392 Dec 31 users01.dbf

-Rw-r ----- 1 oracle oinstall 524296192 Dec 31 zlm01.dbf

-- Simulate online destruction of three redo logs

[Oracle @ zlm2 zlm11g] $ echo> redo01.log

[Oracle @ zlm2 zlm11g] $ echo> redo02.log

[Oracle @ zlm2 zlm11g] $ echo> redo03.log

[Oracle @ zlm2 zlm11g] $ exit

Exit

-- Close the database

SQL> shutdown immediate

ORA-01031: insufficient privileges

SQL> conn/as sysdba

Connected.

SQL> shutdown immediate

ORA-03113: end-of-file on communication channel

Process ID: 4667

Session ID: 40 Serial number: 105

Because the redo file has been corrupted, consistency close database error reported, ora-03113 Error

-- Restart the database

SQL> startup

The ORA-24324: service handle not initialized.

ORA-01041: internal error. hostdef extension doesn't exist

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[Oracle @ zlm2 backup] $ sqlplus/as sysdba

SQL * Plus: Release 11.2.0.3.0 Production on Wed Dec 31 22:57:34 2014

Copyright (c) 1982,201 1, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2232960 bytes

Variable Size 494931328 bytes

Database Buffers 335544320 bytes

Redo Buffers 2396160 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/zlm11g/redo02.log'

ORA-27048: skgfifi: file header information is invalid

Additional information: 14

Found unable to automatically start to open status, prompting for illegal file header information, ora-27048 error, and ora-00313, ora-00312

The consistency check will be performed when the database is started, and the redo corruption will make the consistency check fail. You can set the implicit parameter _ allow_resetlogs_uption to solve this problem.

-- Set implicit Parameters

System altered.

-- Shut down and restart the database

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2232960 bytes

Variable Size 494931328 bytes

Database Buffers 335544320 bytes

Redo Buffers 2396160 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/zlm11g/redo02.log'

ORA-27048: skgfifi: file header information is invalid

Additional information: 14

SQL> show parameter _ allow_resetlogs_uption

NAME TYPE VALUE

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

_ Allow_resetlogs_upload uption boolean TRUE

We can see that the implicit parameter just set has taken effect.

-- Perform an incomplete cancel-based recovery

SQL> recover database until cancel;

ORA-00279: change 1723866 generated at 12/31/2014 22:51:41 needed for thread 1

ORA-00289: suggestion:

/U01/app/oracle/fast_recovery_area/ZLM11G/archivelog/2014_12_31/o1_mf_000056 _ % u _.

Rc

ORA-00280: change 1723866 for thread 1 is in sequence #56

Specify log :{ = Suggested | filename | AUTO | CANCEL}

Auto

ORA-00308: cannot open archived log

'/U01/app/oracle/fast_recovery_area/ZLM11G/archivelog/2014_12_31/o1_mf_000056 _ % u _.

Arc'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00308: cannot open archived log

'/U01/app/oracle/fast_recovery_area/ZLM11G/archivelog/2014_12_31/o1_mf_000056 _ % 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 wocould get error below

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/u01/app/oracle/oradata/zlm11g/system01.dbf'

SQL>!

[Oracle @ zlm2 backup] $ cd/u01/app/oracle/fast_recovery_area/ZLM11G/archivelog/2014_12_31/

[Oracle @ zlm2 2014_12_31] $ ll

Total 56076

-Rw-r ----- 1 oracle oinstall 33343488 Dec 31 o1_mf_1_50_bb7wtcln _. arc

-Rw-r ----- 1 oracle oinstall 30720 Dec 31 o1_mf_1_51_bb7wzzlk _. arc

-Rw-r ----- 1 oracle oinstall 23774208 Dec 31 o1_mf_1_52_bb82m1dp _. arc

-Rw-r ----- 1 oracle oinstall 172032 Dec 31 o1_mf_1_53_bb82rsqv _. arc

-Rw-r ----- 1 oracle oinstall 6656 Dec 31 o1_mf_000054_bb837jy3 _. arc

-Rw-r ----- 1 oracle oinstall 14848 Dec 31 o1_mf_1_55_bb83cxqy _. arc

The latest archive only reaches 55, and archive No. 56th has not yet been generated. You can only Restore Archive No. 55 with auto

-- Execute recover database until cancel again.

SQL> recover database until cancel;

ORA-00279: change 1723866 generated at 12/31/2014 22:51:41 needed for thread 1

ORA-00289: suggestion:

/U01/app/oracle/fast_recovery_area/ZLM11G/archivelog/2014_12_31/o1_mf_000056 _ % u _.

Rc

ORA-00280: change 1723866 for thread 1 is in sequence #56

Specify log :{ = Suggested | filename | AUTO | CANCEL}

Cancel

ORA-01547: warning: RECOVER succeeded but open resetlogs wocould get error below

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/u01/app/oracle/oradata/zlm11g/system01.dbf'

ORA-01112: media recovery not started

SQL> alter database open;

Alter database open

*

ERROR at line 1:

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

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: [2662], [0], [1723874], [0],

[1724203], [4194432], [], [], [], [], [], []

Process ID: 5139

Session ID: 1 Serial number: 5

Use resetlogs to open the database and report a 600 error. ignore this.

SQL> select open_mode from v $ database;

ERROR:

ORA-03114: not connected to ORACLE

The system prompts that the database is not connected.

SQL> conn/as sysdba

Connected to an idle instance.

SQL> select open_mode from v $ database;

Select open_mode from v $ database

*

ERROR at line 1:

A ORA-01034: ORACLE not available

Process ID: 0

Session ID: 0 Serial number: 0

Prompt database instance not started

SQL> startup mount

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2232960 bytes

Variable Size 494931328 bytes

Database Buffers 335544320 bytes

Redo Buffers 2396160 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 until cancel;

ORA-00283: recovery session canceled due to errors

The ORA-16433: The database must be opened in read/write mode.

SQL> alter database open;

Database altered.

SQL> select count (*) from zlm. test;

COUNT (*)

----------

1

SQL> select * from zlm. test;

TBID

----------

1