User-defined database repair

Data | database








user-defined database recovery





one, automatic application redo log





1, use the Set AutoRecovery command to automatically apply redo log





completes the repair operation of the data file





Sql>startup MOUNT; Start the instance and load the database





sql>set AutoRecovery on enable Redo Log auto Apply feature





sql>recover database restores specified tablespaces, data files, or entire databases





sql>alter DATABASE OPEN; Open database after recovery completes





2, use Recovery Automatic command to automatically apply redo log





completes the repair operation of the data file





Sql>startup MOUNT; Start the instance and load the database





sql>recover AUTOMATIC DATABASE





sql>alter DATABASE OPEN; Open database after recovery completes





database Media Recovery in non-archive mode





1, restore the database to its original location





Sql>shutdown IMMEDIATE shut down the database if the database is still open;





the database file back to its original location, restoring the entire database with the last established consistent full backup, all data files and control files must be repaired;





sql>recover DATABASE UNTIL CANCEL





Sql>cancel





sql>alter DATABASE OPEN resetlogs, set the current redo log order number to 1.





2, restore the database to a new location





Sql>shutdown IMMEDIATE shut down the database if the database is still open;





the database file to a new location, using the last established consistent full backup to restore the entire database, all data files and control files must be repaired;





edits the Control_files parameter in the initialization parameter file so that it performs the control file saved in the new location;





Sql>startup MOUNT





if the restored database file is in a new location, the control file must be modified using the ALTER DATABASE RENAME file statement to point to the repaired data file in the new location. such as:





Sql>alter DATABASE RENAME FILE ' I:\ora9i\oradata ystem01.dbf ' to ' K:\oracle\oradata ystem01.dbf ';





sql>recover DATABASE UNTIL CANCEL





Sql>cancel





sql>alter DATABASE OPEN resetlogs, set the current redo log order number to 1.





Full Media recovery
in archive mode




1, full recovery in the shutdown state





Sql>shutdown ABORT (if the database is open, forcibly shut it down)





the data file to the original location, if the media failure can not be excluded, you need to restore the data files to other locations;





Use the backup to repair missing or corrupted data files, or you can use the ALTER DATABASE CREATE datafile statement to reconstruct a blank data file to replace the view or corrupted data file;





Sql>startup MOUNT





if the restored data file is not in its original location, you need to use alter DATABASE RENAME file ... To ... Statement to update their information in the control file





Sql>select name,status from V$datafile; the name and status of the query data file





sql>alter DATABASE datafile ... Online; Change offline data files to online





sql>recover DATABASE or sql>recover tablespace users





or Sql>recover datafile ' I:\ora9i\oradata\users0.dbf;





sql>alter DATABASE OPEN;





2, full media recovery in the open state





sql>select d.file# f#,d.name,d.status,h.status from v$datafile d,v$datafile_header h





where d.file#=h.file# query which data files are automatically set to offline;





sql>alter tablespace users OFFLINE temporary; Set the table space containing the corrupted data files offline;





the data file to the original location, if the media failure can not be excluded, you need to restore the data files to other locations;





use Backup to repair lost or corrupted data files;





If the restored data file is not in its original location, you will need to use alter DATABASE RENAME file ... To ... Statement to update their information in the control file





sql>recover tablespace users automatic to restore offline tablespace space containing corrupted data files;





sql>alter tablespace users ONLINE;





IV, incomplete media recovery in archive mode





1, operating criteria for incomplete recovery;





a full backup of the database before and after the recovery





completes incomplete media recovery, checks whether the database has been restored to the state under the target time;





Archive redo log files are moved to another location after the incomplete media recovery is complete.





2, time-based incomplete recovery





a full backup of the database, including control files and all online redo log files





Sql>shutdown ABORT





determines the target time for incomplete media recovery, which is the state you need to restore the database to, and then determine what backups need to be used to fix the data, and the control file backup used by the database repair should correctly reflect the physical structure of the database at the target time, The data file backup used should be created before the target time, and all data files must be repaired,





If you do not have a backup of the data files that were established before the target time, you will need to re-create the blank data file





If the database contains data files that were created after the target time, do not fix the data file because it should not exist at all in the database after the incomplete recovery is complete;





the data file back to the original location, if the media failure can not be excluded, then revert to another location;





use selected backup files to repair all control files and data files;





Sql>startup mount to start the instance and load the database;





if the restored data files are not in their original locations, you need to use alter DATABASE RENAME file ... To ... Statement to update their information in the control file;





sql>select name,status from V$datafile; Make sure all data files are online





sql>alter DATABASE datafile ... Online; restore data files to on-line;





sql>recover DATABASE UNTIL time ' 2004-02-01:12:30:30 '





If the control file is repaired using Backup, you must specify the using Backup Controlfile clause in recover fame and wealth;





sql>alter DATABASE OPEN resetlogs;





immediately makes a full backup of the database.





3, incomplete recovery based on undo





sql>recover DATABASE UNTIL CANCEL





Other steps with time based incomplete recovery





4, non-complete recovery based on SCN





when an incomplete recovery based on SCN is performed, Oracle terminates the recovery process after applying all the redo records that have a transaction that is less than or equal to the specified SCN





 





resetlogs Option





You must open the database using the Resetlogs option in the following three cases:





1, after performing any type of incomplete media recovery;





2, after using Backup to repair the control file (using the Using Backup Controlfile clause in the Recover command);





after a full restore of an archive database without an online redo log file backup.