Oracle Database Recovery Case

When we use the Oracle database, suddenly power outages, causing a lot of problems, resulting in the loss of old data, affecting the correctness of the data, destroying the database. At this point, users desperately need to recover data. This article takes this as an example to describe database data recovery.

First, Case Description:

Database QICU report due to sudden power outage system01.dbf more recovery is required to maintain consistency, the database cannot be opened, the database is not backed up, and the archive log is not contiguous. The customer provides the online file of the database and needs to restore The data under the ZXFG user urgently .

two , recovery process:

1 failure detection of the database

2 try to suspend the database and repair the database

3 Parsing data Files

4 Validating Data

5 Export data and delivery data (import)

three . Recovering data

1 Fault Detection of database

Use DBV command to detect the integrity of a data file

The results are as follows:

650) this.width=650; "title=" 1.png "src=" Http://s1.51cto.com/wyfs02/M01/7A/86/wKiom1arH8uRacvCAABA9IPe88E075.png " alt= "Wkiom1arh8uracvcaaba9ipe88e075.png"/>

The analysis results found that the SYSAUX01.DBF file block (data) detection failed 40 pages, index page detection failed 29 pages, indicating that there is a bad block sysaux01.dbf.

Conclusion: The integrity test of data file is SYSAUX01 by DBV. DBF has bad blocks and other detected files are intact.

　　2 Use the client's database to suspend the database locally and attempt to repair the database.

2.1 Create a new Os:windows server x86, install Oracle 11.2.0.1.0 for 32-bit

Version database, suspending database

650) this.width=650; "title=" 2.png "src=" Http://s5.51cto.com/wyfs02/M02/7A/86/wKiom1arIAiyN0W1AABOkbNaO7k734.png " alt= "Wkiom1ariaiyn0w1aabokbnao7k734.png"/>

ORA-01110 error in the library, SYSTEM01.DBF requires more consistent recovery. Use the Recover database command to make media recovery using online logs.

650) this.width=650; "title=" 3.png "src=" Http://s1.51cto.com/wyfs02/M00/7A/86/wKiom1arIE7BIdf-AAAVUPNUF5s858.png " alt= "Wkiom1arie7bidf-aaavupnuf5s858.png"/>

The control file for the database has been modified, and the database needs to be recovered using the control file

650) this.width=650; "title=" 4.png "src=" Http://s3.51cto.com/wyfs02/M01/7A/86/wKioL1arIQmyArwbAABmkOf0gMI745.png " alt= "Wkiol1ariqmyarwbaabmkof0gmi745.png"/>

The recovery database requires a 2016_01_19 11th archive log. The Cancel parameter is used for incomplete recovery because the archive log is missing.

650) this.width=650; "title=" 5.png "src=" Http://s2.51cto.com/wyfs02/M01/7A/86/wKiom1arIQuia0_-AAAtB_ydNBA317.png " alt= "Wkiom1ariquia0_-aaatb_ydnba317.png"/>

Execute the ALTER DATABASE open command again, and the databases are opened.

650) this.width=650; "title=" 6_ copy. png "src=" http://s5.51cto.com/wyfs02/M02/7A/86/ Wkiol1ariyzdsd6saaaglgjjqgk441.png "alt=" Wkiol1ariyzdsd6saaaglgjjqgk441.png "/>

2.2 Query instance status, database report ora_00600 error, other queries, some queries can be carried out, some queries error, and error is ora_00600 errors.

650) this.width=650; "title=" 7.png "src=" Http://s1.51cto.com/wyfs02/M00/7A/86/wKioL1arIbGBvUJNAAAeb3ITkwo168.png " alt= "Wkiol1aribgbvujnaaaeb3itkwo168.png"/>

2.4 View the warning log trace file to view the internal error code;

The warning log section reads as follows:

Ora-00600:internal error code, arguments: [13013], [5001], [267], [8456009], [5], [8456009], [17], [], [], [], [], []

Non-fatal Internal Error happenned while Smon is doing logging scn->time mapping.

Make a variety of attempts to access a large amount of information. This internal error of the database cannot be repaired by the command. Try to export the database.

　　2.3 Using the Expdp/exp tool to export the database;

2.3.1 Exporting a database using EXPDP

650) this.width=650; "title=" 8.png "src=" Http://s4.51cto.com/wyfs02/M02/7A/86/wKioL1arIfizlmDuAAAvlG_9Y64465.png " alt= "Wkiol1arifizlmduaaavlg_9y64465.png"/>

EXPDP Export Database error, and the above query report the same errors. SYSAUX01.DBF file corruption causes the EXPDP tool to be unavailable, and the export database fails. Attempt to export a database using EXP

2.3.2 Exporting a database using EXP

Exp exports the database, and the same error is reported above. The database reported a serious internal error, causing the export tool exp to not be used, and even some queries could not be made, and the export database failed.

3 Parse data file to get user data

From the above, the recovery of the database is not possible. The underlying parsing, parsing the data file, gets the user object.

3.1 Using North Asia's own research and development of DBF analytic tools, to obtain data. The results are as follows:

650) this.width=650; "style=" width:670px;height:391px; "title=" QQ picture 20160129151439.png "Src=" http://s1.51cto.com/ Wyfs02/m02/7a/86/wkiol1arinibts5laaf-pvzjcwi242.png "width=" 873 "height=" 387 "alt=" Wkiol1arinibts5laaf-pvzjcwi242.png "/>

3.2 Migrating objects to the database

Create a database, create a user in a database, assign a table space to a user, unlock users, and authorize. Then, the way the channel data is bridged, the resolved user objects are migrated to the database.

Iv. Validation Data

Validating data using Toad for Oracle Tools

650) this.width=650; "style=" WIDTH:664PX;HEIGHT:397PX; "title=" 10.png "src=" http://s5.51cto.com/wyfs02/M01/7A/86/ Wkiom1ario-bud9vaah6omvxnwq840.png "width=" 856 "height=" 809 "alt=" Wkiom1ario-bud9vaah6omvxnwq840.png "/>

V. Export data and deliver the user

5.1 Export all objects under ZXFG user using exp or EXPDP, this example uses EXP to export the data

The command is as follows:

Exp SYSTEM/ABC file=c:\test\dump\zxfg.dmp Log=c:\test\dump\zxfg.log OWNER=ZXFG

650) this.width=650; "title=" 13.png "src=" http://s1.51cto.com/wyfs02/M01/7A/86/wKioL1arIxqDUy59AACuc4uSwMA616.png "alt=" Wkiol1arixqduy59aacuc4uswma616.png "/>

650) this.width=650; "title=" 14.png "src=" http://s5.51cto.com/wyfs02/M00/7A/86/wKiom1arIxWwzJ6LAAB2LkCSqIM765.png "alt=" Wkiom1arixwwzj6laab2lkcsqim765.png "/>

Review the DMP file and export log of the exported database to make sure that the export file is not problematic.

5.2 Users import data to view the integrity of the imported data.

After the user validates the data, it is all right and expresses satisfaction

This article is from the "Zhang Yu (Data Recovery)" blog, please be sure to keep this source http://zhangyu.blog.51cto.com/197148/1739889

Oracle Database Recovery Case