Shulou(Shulou.com)06/01 Report--

Today, a development library can not start, send an error to see that the log file is corrupted (see figure below), and then talk about the causes and consequences. It is said that the server was powered off a year ago, and then it never started again. It was only when someone used it today that they thought of dealing with it.

First of all, let's talk about the general idea. If the damaged redo log is in INACTIVE state, that is, the redo log that is not needed for instance crash recovery, it is easier to deal with, directly alter database clear logfile group #; or alter database clear unarchived logfile group #; rebuild the log group. It is recommended to make a full backup of the database after rebuilding the log file level, especially the archive log file fault caused by mandatory clear. If the corrupted redo log is in ACTIVE or CURRENT state, that is, the redo log needed for instance crash recovery, it is troublesome to deal with, and corrupting this redo log means losing data.

There are three states of redo log:

INACTIVE: the changes corresponding to the log have been written to the hard disk

ACTIVE: the changes corresponding to the log have not been written to the hard disk.

CURRENT: the log file being used by the instance

Due to various problems with this development library, various situations have been encountered in recovery. Here, a database on a virtual machine is used to demonstrate how to recover if the log files in the CURRENT or ACTIVE state are corrupted.

1. Construct the scene

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

# session 1sys@ORCL > delete from zx;2858 rows deleted.#session 2sys@ORCL > select group#,status from vault log; GROUP# STATUS- 1 INACTIVE 2 ACTIVE 3 CURRENTsys@ORCL > shutdown abort ORACLE instance shut down.# deletes the redo log file [oracle@rhel6 ~] $cd / u02/app/oracle/oradata/orcl/ [oracle@rhel6 orcl] $ls-l total 1944992Murray 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 [oracle@rhel6 orcl] $rm redo*logl [oracle@rhel6 orcl] $ls-ltotal 1791212 Muhammad Rwashi 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. Error occurred when starting the database

Idle > startup ORACLE instance started.Total System Global Area 1603411968 bytesFixed Size 2253664 bytesVariable Size 1476398240 bytesDatabase Buffers 117440512 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/orcl/redo02.log'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3

3. An error occurred when trying to reconstruct the log group using clear.

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'idle > 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 alter 1:'/ u02ApplicationLacleGreater oradataUnip orclandredo02.log'

It can be seen from the error message that log 2 is the log file needed for instance crash recovery and cannot be rebuilt directly.

4. In this case, use the implicit parameter _ allow_resetlogs_corruption to create a pfile and add *. _ allow_resetlogs_corruption=TRUE to the pfile. Then mount the database, force incomplete recovery, and then open resetlogs

Idle > create pfile='/home/oracle/initorcl.ora' from spfile;File created. [oracle@rhel6 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 ORACLE 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 boolean TRUEidle > recover database until cancel ORA-00279: change 1023441 generated at 02 for thread 24Accord 23:54:54 needed for thread 1ORA-00289: suggestion: / u02/app/oracle/product/11.2.4/db1/dbs/arch2_2_936817668.dbfORA-00280: change 1023441 for thread 1 is in sequence # 2Specify log: {= 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 > select open_mode from vaulted database and open open model Murray read WRITE

You can see that the database has been open now.

5. Look at the table of the deleted data in the first step again, and there is still a log file indicating that the loss of CURRENT or ACTIVE status will lead to data loss.

Idle > select count (*) from zx; COUNT (*)-2858

The above is the recovery process for testing on a virtual machine, but the recovery of the development libraries mentioned earlier is not as simple as this process. It can be said that one error report has been solved and a new one has been reported.

While performing an incomplete recovery, open resetlogs, using the _ allow_resetlogs_corruption parameter, ORA-01248 was encountered

SQL > alter database open resetlogs;alter database open resetlogs*ERROR at line 1:ORA-01248: file 5 was created in the future of incomplete recovery

So offline drop this file first.

SQL > alter database datafile 5 offline drop

When I open resetlogs again, I encounter 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 smallProcess ID: 3396Session ID: 573Serial number: 51

As the current level is limited, looking up information on the Internet has not been able to solve this series of problems, and finally there is no way but to rebuild the database and re-guide the data.

If anyone has encountered a similar problem and solved it, please share your experience.

In fact, when simulating this problem in the morning, I also encountered a classic error ORA-600 [2662] when I was on open resetlogs. For this error, please refer to eygle's 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

Http://www.killdb.com/2014/06/19/%E6%95%B0%E6%8D%AE%E5%BA%93open%E6%8A%A5%E9%94%99ora-01555-snapshot-too-old.html

Http://www.askmaclean.com/archives/%E3%80%90oracle%E6%81%A2%E5%A4%8D%E3%80%91ora-704.html

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.