-
1. The meaning of recovery
When we use a database, we always hope that the contents of the database are reliable and correct. However, computer system failures (hardware failures, network failures, process failures, and system failures) affect the operation of the database system and the accuracy of the data in the database. Sexually, even destroy the database, causing all or part of the data in the database to be lost. Therefore, when the above failure occurs, it is hoped that a complete database can be re-established. This process is called database recovery. The recovery subsystem is an important part of the database management system. Recovery processing varies depending on the structure affected by the type of failure that occurred.
2. Recovery method
IMPORT method:
Use IMPORT to IMPORT the last data file exported to the new database. This method can restore any database object to the state when it was exported, and subsequent changes will be irreversible. The IMPORT command can be executed interactively. For the specific meaning of each parameter, see the detailed explanation of ORACLE EXP/IMP parameters. This method is suitable for environments that do not use archive mode.
Safe recovery methods:
If the database is running in archive mode, once the database is damaged, the database can be restored to the breakpoint state through cold backup (hot backup) and archive backup.
Database control file recovery (assuming all control files are destroyed):
The database is based on the file system: just use the tar, cp and other commands of the operating system.
The database is based on raw devices: dd if=$ORACLE_BASE/con.bak of=/dev/rdrd/drd1 seek=12
Database data file recovery
Recovery of data and index table spaces and system table spaces:
Copy back the relevant database files and all logical log files generated since the data file was backed up and execute the following command:
svrmgrl > startup mount
svrmgrl > alter database recover automatic
If the control file is damaged, then: svrmgrl > alter database recover using backup controfile; Enter the log file name and redolog file name as prompted.
svrmgrl > alter database open resetlogs;
Recovery of database temporary files and rollback table spaces: simply drop offline and rebuild.
Note: If the database is not running in archive mode, recovery can only restore to the state of the last backup. For information about archive mode settings and backup-related technologies, see ORACLE database backup technology.
3. ORACLE table space recovery solution
(1) Household table space error phenomenon:
ORA-01157, ORA-01110 or operating system level error occurs when starting the database
Errors such as ORA-07360, when shutting down the database (using shutdown normal or shutdown immediate) will result in errors ORA-01116, ORA-01110 and operating system level error ORA-07368
solve:
There are two solutions below:
Solution 1: The user's table space can be easily rebuilt, that is, the recently exported objects are available or the objects in the table space can be easily rebuilt, etc. In this case, the easiest way is to go offline and delete the data files, drop the tablespace and rebuild the tablespace and all objects.
svrmgrl> startup mount
svrmgrl> alter database datafile filename offline drop;
svrmgrl> alter database open;
svrmgrl> drop tablespace tablespace_name including contents;
Rebuild the tablespace and all objects.
Solution 2: The user's table space cannot be easily reconstructed. In most cases, rebuilding the table space is impossible and too hard work. The method is to reverse the backup and perform media recovery. If your system is running in NOARCHIVELOG mode, Then only the lost data can be recovered in the online redo log.
The steps are as follows:
1)Restore the lost datafile from a backup
2)svrmgrl> startup mount
3)svrmgrl> select v1.group#,member,sequence#,first_change# from v$log v1,v$logfile v2 where v1.group#=v2.group#;
4) If the database is running in NOARCHIVELOG mode: svrmgrl> select file#,change# from v$recover_file;
If CHANGE# is greater than the minimum FIRST_CHANGE# then the data file can be restored.
If CHANGE# is less than the minimum FIRST_CHANGE#, the data file is not recoverable. Restore the most recent full backup or use option one.
5)svrmgrl> recover datafile filename;
6) Confirm the recovery is successful
7)svrmgrl> alter database open resetlogs;
There is no need to perform media recovery for read-only table spaces, just restore the backup. The only exceptions are:
The table space was changed to read-write mode after the last backup. The table space was changed to read-only mode after the last backup. In this case, media recovery is required.
(2) Temporary table space Temporary table space does not contain real data. The recovery method is to delete the temporary table space and rebuild it.
(3) If the backup of the system table space is unavailable, the only method can be to rebuild the database.
(4) There are two situations for rolling back table space:
1. The database has been completely shut down (use shutdown immediate or shutdown command)
1) Confirm that the database is completely closed
2) Modify the init.ora file and comment "rollback-segment"
3) svrmgrl> startup restrict mount
4) svrmgrl> alter database datafile filename offline drop;
5) svrmgrl> alter database open;
Based on the results that appear: "statement processed" go to (7); "ORA-00604,ORA-00376,ORA-01110" go to (6)
6) svrmgrl> shutdown immediate
Modify the init.ora file and add the following line: _corrupted_rollback_segments = (<roll1>,...<rolln>)
svrmgrl> startup restrict
7) svrmgrl> drop tablespace tablespace_name including contents;
8) Rebuild table space and rollback segment
9) svrmgrl> alter system disable restricted session;
10) Modify the init.ora file
2. The database is not completely closed (the database crashes or the shutdown abort command is used to close the database)
1) Restore backup
2) svrmgrl> startup mount
3) svrmgrl> select file#,name,status from v$datafile;
svrmgrl> alter database datafile filename online;
4) svrmgrl> select v1.group#,member,sequence#,first_change# from v$log v1,v$logfile v2 where v1.group#=v2.group#;
5) svrmgrl> select file#,change# from v$recover_file; #See solution 2-4
6) svrmgrl> recover datafile filename;
7) svrmgrl> alter database open;
3. The database is open
1) Delete the rollback segment and table space
2) Rebuild table space and rollback segment (5), control file recovery
1. All control files are destroyed. Copy the backup control files to the original directory. For RAW DEVICE (naked device), then: dd if='con.bak' of='/dev/rdrd/drd1' seek=128
2. Not all control files are destroyed. Use other control files to start the database (6), save the data blocks and the data in them. Phenomenon: ORA-01578 error occurs when executing ORACLE operation. Analysis: ORA-1578 error occurs when ORACLE considers a data Blocks may be corrupted and may occur due to the following reasons:
I/O hardware or firmware damage Operating system I/O or cache failure Memory or page swap error Part of the data file is overwritten Trying to access an unformatted block disk Repair Other causes Solution steps:
Check the log and trace files to see if there are any other errors or positioning errors:
sql>select * from v$datafile where file#=<F>;
sql>select owner,segment_name,segment_type from dba_extents where file_id=<F> and <B> between block_id and block_id+blocks-1;
Based on the returned segment_type:
The segment type is temporary or cache or has no return value. Check whether the SQL statement is correct.
If the segment type is rollback segment, the data block needs to be restored.
The segment type is index, check the table where it is located. Just rebuild the index.
sql> select owner,table_name from dba_tables where cluster_name = name_of_segment
Error 1578 still occurs and the database needs to be restored.
The segment type is a table and saves the data in the table.
Analyze whether an entity has permanent data corruption
sql> analyze table table.name validate structure cascade;
sql> analyze table clustername validate structure cascade;
Hardware error recovery database running in ARCHIVE mode
OFFLINE corresponding data file copy backup data file
rename the datafile to new location
recover the datafile using archive log
The online data file database is running in non-ARCHIVE mode
OFFLINEThe corresponding data file copies the backed up data file, rename the datafile and online it
Save the data in the table, for example: sql>select * from bigemp;
ERROR: ORA-01578: ORACLE DATA block corrupted (file#8,block#8147) ORA-00110: data file 8: '/oracle/usr714.dbf' … … corrupt file id : 8=8(hex) corrupt block id : 8147=1fd3(hex) first rowid in the corrupt block: 0000.1fd3.0000.0008 last rowid in the corrupt block: 0000.1fd2.7fff.0008 first rowid affer this block: 0000.1fd4.0000.0008
sql > create table temp as select * from bigemp where 1=2;
sql > insert into temp select * from bigemp /*+rowid(bigemp) */ where rowid >='0000.1fd4.0000.0008';
sql > insert into temp select * from bigemp where rowid <='0000.1fd2.7fff.0008';
In versions before ORACLE 7.1, when rowid range scan does not exist, the same purpose as above can be achieved through indexing.
4. Postscript
ORACLE's backup and recovery technology can be said to be extensive and profound. I only know a small part of it, and it is not very thorough. I hope these articles can be helpful to everyone. You are also welcome to share your experience with backup and recovery. Tell me the problem, I will organize it and publish it here for the reference of all DBA friends and data administrators who are interested in doing it. Maybe your little effort will save a company!
At the same time, I would like to remind all my friends that backup is very, very, very, very, very, very, very, very, very important. . . Importantly, if possible, you must use the ARCHIVE mode, otherwise, something may go wrong and you won’t even be able to cry.