Recovery after Oracle online redo log file is lost

Today, a development library can not start, sent over an error to see the log file is damaged (see), and then said the cause and the cause. Said the server was out of power and then did not start up again. Today someone used to think about dealing with.

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M00/8D/F6/wKiom1iwUvihDrlHAAOLpRmbTUI019.jpg-wh_500x0-wm_ 3-wmp_4-s_711888344.jpg "title=" 900687689.jpg "alt=" Wkiom1iwuvihdrlhaaolprmbtui019.jpg-wh_50 "/>

First talk about the general idea, if the damaged redo log is inactive state, that is, the instance crash recovery redo log, it is easier to handle, directly alter DATABASE clear logfile Group #; Database Clear unarchived logfile Group #; Rebuilding the log groups is OK. It is recommended that you rebuild the log file level after you make a full library backup of the database, especially after forcing clear, resulting in the archive log file fault. If the corrupted redo log is active or current state, that is, the instance crash recovery needs to use the redo log, it is more troublesome to deal with, the corruption of this redo log means the loss of data.

Three states of the redo log:

• INACTIVE: Log corresponding changes have been written to the hard disk

• ACTIVE: The changes to the log are not yet written to the hard disk

• Current: The log file that the instance is using

Because of the problems with this development library, there are a variety of situations that can be recovered by using a database on a virtual machine to demonstrate how to recover if the log file of the current or active state is corrupted.

1. Tectonic scene

Delete data from one table without committing, then shutdown the database to abort in another session. Then delete all the redo log files.

#session  1[email protected]>delete from zx;2858 rows deleted. #session  2[ email protected]>select group#,status from v$log;    group#  STATUS---------- ------------------------------------------------ 1 inactive 2 active  3 current[email protected]>shutdown abort;oracle instance shut down.# Delete redo log file [[email protected] ~]$ cd /u02/app/oracle/oradata/orcl/[[email  Protected] orcl]$ ls -l  total 1944992-rw-r----- 1 oracle  Oinstall   9748480 feb 24 23:56 control01.ctl-rw-r----- 1 oracle  oinstall   9748480 feb 24 23:56 control02.ctl-rw-r----- 1  Oracle oinstall 328343552 feb 24 23:54 example01.dbf-rw-r----- 1  Oracle oinstall  52429312 feb 24 23:54 redo01.log-rw-r----- 1 oracle oinstall   52429312 feb 24 23:55 redo02.log-rw-r----- 1 oracle oinstall   52429312 feb 24 23:55 redo03.log-rw-r----- 1 oracle oinstall 545267712  feb 24 23:54 sysaux01.dbf-rw-r----- 1 oracle oinstall 796925952  Feb 24 23:54 system01.dbf-rw-r----- 1 oracle oinstall  30416896  Feb 24 13:58 temp01.dbf-rw-r----- 1 oracle oinstall 110108672 feb  24 23:54 undotbs01.dbf-rw-r----- 1 oracle oinstall   5251072  feb 24 23:54 users01.dbf[[email protected] orcl]$ rm redo*logl[[email  protected] orcl]$ ls -ltotal 1791212-rw-r----- 1 oracle oinstall    9748480 fEb 24 23:56 control01.ctl-rw-r----- 1 oracle oinstall   9748480  feb 24 23:56 control02.ctl-rw-r----- 1 oracle oinstall 328343552  feb 24 23:54 example01.dbf-rw-r----- 1 oracle oinstall 545267712  feb 24 23:54 sysaux01.dbf-rw-r----- 1 oracle oinstall 796925952  Feb 24 23:54 system01.dbf-rw-r----- 1 oracle oinstall  30416896  Feb 24 13:58 temp01.dbf-rw-r----- 1 oracle oinstall 110108672 feb  24 23:54 undotbs01.dbf-rw-r----- 1 oracle oinstall   5251072  feb 24 23:54 users01.dbf

2, start the database error

Idle>startup ORACLE instance started. Total System Global area 1603411968 bytesfixed size 2253664 bytesvariable size 1476398240 bytesdatabase buffers 117440 Bytesredo buffers 7319552 bytesdatabase mounted. Ora-00313:open failed for members of log Group 2 of thread 1ora-00312:online log 2 thread 1: '/u02/app/oracle/oradata/or Cl/redo02.log ' ora-27037:unable to obtain file statuslinux-x86_64 error:2: No such file or directoryadditional Informatio N:3

3, try to use the clear method to rebuild the log group error

Idle>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: '/u02/app/oracle/oradata/orcl/redo02.log ' I Dle>alter Database Clear unarchived logfile Group 2;alter database clear unarchived 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: '/u02/app/oracle/orad Ata/orcl/redo02.log '

From the error message can be seen in log 2 is the instance crash recovery required log files, not directly rebuilt.

4. Use implicit parameters in this case _allow_resetlogs_corruption, create pfile, add *._allow_resetlogs_corruption=true to Pfile. Then mount the database, force incomplete recovery, and then open Resetlogs

Idle>create pfile= '/home/oracle/initorcl.ora '  from spfile; File created. [[email protected] orcl]$ vi /home/oracle/initorcl.ora idle>shutdown  Immediate;ora-01109: database not opendatabase dismounted. Oracle instance shut down.idle>startup pfile= '/home/oracle/initorcl.ora '  mount;O Racle instance started. total system global area 1603411968 bytesfixed size     2253664 bytesvariable size 1476398240 bytesdatabase buffers  117440512  bytesRedo Buffers    7319552 bytesDatabase mounted.idle>show  parameter _allow_name     type        VALUE------------------------------------ --------------------------------- ------------------------ ------_ALLOW_RESETLOGS_CORRUPTION&NBSP;&NBSP;&NBSP;&Nbsp; boolean       trueidle>recover database until  cancel;ora-00279: change 1023441 generated at 02/24/2017 23:54:54 needed  for thread 1ora-00289: suggestion : /u02/app/oracle/product/11.2.4/db1/dbs/ arch1_2_936817668.dbfora-00280: change 1023441 for thread 1 is in  sequence  #2Specify  log: {<ret>=suggested | filename | auto |  cancel}cancelora-01547: warning: recover succeeded but open resetlogs  would get error belowORA-01194: file 1 needs more recovery  to be consistentora-01110: data file 1:  '/u02/app/oracle/oradata/orcl/ SYSTEM01.DBF ' Ora-01112: media recovery not startedidle>alter database open  resetlogs;database altered.idle&gT;select open_mode from v$database;open_ MODE------------------------------------------------------------Read write

You can see now that the database has been open.

5. Once again, look at the table of the data that was deleted in the first step, and the data still exists indicating that the loss of the current or active state of the log file can result in data loss.

Idle>select Count (*) from ZX; COUNT (*)----------2858

These are the recovery procedures for testing on a virtual machine, but there is no simple process for recovering from the development library mentioned earlier. Can be said to solve an error and come out with a new error.

When you perform an incomplete restore using the _allow_resetlogs_corruption parameter, the open resetlogs encounters a ORA-01248

sql> ALTER DATABASE Open Resetlogs;alter database open Resetlogs*error at line 1:ora-01248:file 5 is created in the F Uture of incomplete recovery

and offline this file to drop first.

sql> ALTER DATABASE datafile 5 offline drop;

Again open Resetlogs again met ORA-00704 and ORA-01555

sql> ALTER DATABASE Open Resetlogs;alter database open Resetlogs*error at line 1:ora-01092:oracle instance terminated.  Disconnection Forcedora-00704:bootstrap process Failureora-00704:bootstrap process failureora-00604:error occurred at Recursive SQL level 1ora-01555:snapshot too old:rollback segment number 5 with name "_syssmu5_4116806824$" Too Smallproc ESS id:3396session id:573 Serial number:51

Due to the current level of limited, on-line search data has not been able to solve this series of problems, and finally no way can only rebuild the library, re-guide the data.

If you have a similar problem and have solved it, please share your experience.

In fact, the morning in the simulation of this problem, in open Resetlogs also encountered a classic error ORA-600 [2662], this error can refer to Eygle blog http://www.eygle.com/archives/2005/12/ Oracle_diagnostics_howto_deal_2662_error.html

Reference: HTTP://IQUICKSANDI.BLOG.163.COM/BLOG/STATIC/13228526220107642655204/

Http://www.linuxidc.com/Linux/2012-02/53426.htm

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1901100

Recovery after Oracle online redo log file is lost