How to restore the database for Oracle to lose archive log files

Case Description: a data file ordtab03.dbf in the ordtab tablespace is destroyed. It contains many partitions in the ORDERS table. The data files are hot-standby in July4, 2004, July4.

Case Description: a data file ordtab03.dbf in the ordtab tablespace is destroyed. It contains many partitions in the ORDERS table. The data files are hot-standby on July 4, 2004, July 4.

The restoration method for a database that loses archive log files is to extract data from a database that cannot be opened normally (because one or more database files are inconsistent with other files. Scenario: A disk is damaged and a database file is lost. Unfortunately, several archive log files are lost when a data file is dumped from the hot standby one week ago. But the problematic data file contains the most important table. How can we save the data?

Extract data from a database that cannot be opened normally (because one or more database files are inconsistent with other files. Scenario: A disk is damaged and a database file is lost. Unfortunately, several archive log files are lost when a data file is dumped from the hot standby one week ago. But the problematic data file contains the most important table. How can we save the data?

Every DBA knows that this is a problem. Data will be lost because some transactions are lost. How much data will be lost? Oracle uses hard line locations and cannot open data properly due to integrity constraints. However, if Oracle deletes its hard-line attributes using unconventional methods, it should be able to extract as much data as possible. This is usually much better than losing all the data.

If the detailed process only loses the index of the heap table, or some data that can be easily reconstructed, the best way is to delete the tablespace, recreate these objects, and then re-input them. However, if the lost data file contains important data and is difficult to recover, and only the previous backup has lost some archive logs, you may want to restore data from the problematic tablespace as much as possible and delete and recreate the tablespace.

The main steps are as follows:

1. Perform a cold backup for the current data;

2. Dumping the backup of lost database files and applying logs that can be applied;

3. Set undocumented initialization parameters that allow you to open the database in the current State;

4. Execute exp and extract all the data that can be extracted from the problematic tablespace;

5. Dump the database from the previous cold backup;

6. Make the destroyed data file offline;

7. Execute exp and extract additional data not extracted in step 1;

8. In a cold backup dump;

9. Delete the problematic tablespace;

10. Recreate the problematic tablespace;

11. Use the data extracted in step 4 and Step 7 to reconstruct the data;

Case Description: a data file ordtab03.dbf in the ordtab tablespace is destroyed. It contains many partitions in the ORDERS table. The data files are hot-standby in July 4, 2004, july 4-some archived logs are lost so far.

Step 1: backup the database. The first step is to cold-standby any data files currently in use, online redo logs, and control files. If one or more data files are lost but the database is still open, perform hot backup for each remaining data file and ensure that the archive during/after backup is saved securely.

After the BACKUP is created, back up the control file: alter database backup controlfile to trace resetlogs; then open the BACKUP control file and delete all rows above the first, and delete "recover database ..." All at the end of the file.

Step 2: dump the lost database file backup and apply the log; this step should dump the backup and apply the log until it cannot be rolled forward. If you try to open the database normally, the ORA-01589: must use RESETLOGS or NORESETLOGS option for database open error will be obtained.

If you try to execute alter database open resetlogs, you will get a ORA-01195 error: ORA-01195: online backup of file % s needs more recovery to be consistent. Here is where Oracle uses its hard line. Because the dumped data file cannot be recovered to the same location as other files, there may be interrupted data and Oracle does not allow normal access to the database.

Step 3: Set undocumented instance parameters and open the database. In the initialization parameter file, set job_queue_processes to 0 and SET _ allow_resetlogs_appsuption = TRUE. After changing this parameter, switch to the directory where the new control file is saved. Connect to SYSDBA and run the new control file creation script.

Now the database can be opened.

SQL> SELECT COUNT (*) FROM OE. orders;

Step 4: Execute export and extract data. In this step, you can easily see the tables that have exported all the data.

Step 5: dump the backup database, and the following two steps are optional. These three steps are combined to allow more data to be extracted. This step, from the backup database dump, can efficiently cancel any damages caused by the _ allow_resetlogs_uption parameter. Therefore, this step will not recover any lost data files.

Step 6: Make the corrupted data file offline alter database datafile '/u07/oradata/PRD/ordtab03.dbf' OFFLINE; In this step, the DATABASE is fully consistent.

Step 7: Execute export and extract additional data. This step may extract additional data that cannot be extracted from step 4, such as data in the index.

Step 8: dump the database. This is the last dump of the database. In this step, the database is rolled back to the moment before implicit parameters are used, and then the database is returned to the normal state, skip this step if no data has been updated since Step 5 is dumped.

Step 9: To delete a problematic tablespace, first check whether there are integrity constraints. Use the following query:

Select cr. constraint_name

FROM dba_constraints CR, dba_constraints CP, dba_tables TP, dba_tables TR

Where cr. r_owner = CP. owner

And cr. r_constraint_name = CP. constraint_name

And cr. constraint_type = 'R'

And cp. constraint_type IN ('P', 'U ')

And cp. table_name = TP. table_name

And cp. owner = TP. owner

And cr. table_name = TR. table_name

And cr. owner = TR. owner

And tr. tablespace_name <> 'ordtab'

And tp. tablespace_name = 'ordtab ';

If there are constraints, you may need to create a reconstruction script. If you use export dump to recreate data, the constraints can be dumped from the export file.

Drop tablespace ordtab including contents cascade constraints;

Step 10: Create a tablespace

Step 2: re-create the data and execute Import