Oracle Table Space Recovery scheme

Comments: Oracle Table Space Recovery scheme includes user table space, temporary table space, system table space, rollback table space, control files and other recovery schemes, in which user table space and rollback table space, control file recovery is more complex, divided into a variety of situations and their respective solutions, This article will give you one by one detailed explanation of these Oracle tablespace Recovery solutions.

A. User table space

Error:

ORA-01157,ORA-01110 or operating system-level errors, such as ora-07360, occur when you start the database, which causes an error immediate when you close the database (using shutdown normal or shutdown ora-01116), ORA-01110 and operating system-level errors ora-07368

Here are two solutions:

1. The user's table space can be easily rebuilt, that is, the most recently exported object is available or the objects in the tablespace can be easily rebuilt, and so on. In this case, the easiest way to do this is to offline and delete the data file, delete the table space, and rebuild the table space and all the objects.

Svrmgrl> Startup Mount

svrmgrl> ALTER DATABASE datafile filename offline drop;

svrmgrl> ALTER DATABASE open;

Svrmgrl> drop tablespace tablespace_name including contents;

Rebuilds the table space and all objects.

2. User's tablespace cannot be easily rebuilt in most cases, rebuilding tablespaces is impossible and too hard work. The method is to reverse backup and do media recovery. If your system is running in Noarchivelog mode, only the missing data in the online redo log can be restored.

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 then: svrmgrl> select file#,change# from V$recover_file;

If the change# is greater than the smallest first_change# the data file can be restored. If the change# is less than the smallest first_change# the data file is not recoverable, restoring the most recent full backup or adopting scenario one.

5) svrmgrl> recover datafile filename;

6 Confirm Oracle Tablespace Recovery success

7) svrmgrl> ALTER DATABASE open resetlogs;

3. read-only table space does not require media recovery, as long as the backup can be restored.

The only exceptions are:

Tablespace is changed to Read-write mode after last backup

Tablespace is changed to read-only mode after last backup

In this case, media recovery is required

Two. Temporary table space

The temporary tablespace does not contain real data, and the method of recovery is to delete the temporary table space and rebuild it.

Three. system table space

If backup is not available, you can only use the method of rebuilding the database

Four. Roll back the table space

There are two kinds of situations:

1, the database has been completely closed (using shutdown immediate or shutdown command)

1 Confirm that the database is completely closed

2) Modify Init.ora file, 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 of the occurrence:

"Statement processed"

"Ora-00604,ora-00376,ora-01110"

6) svrmgrl> shutdown Immediate Modify Init.ora file, add the following line: _corrupted_rollback_segments = (, ...) svrmgrl> startup restrict

7) svrmgrl> drop tablespace tablespace_name including contents;

8 reconstruction of table space and rollback section

9) svrmgrl> Alter system disable restricted session;

10) Modify Init.ora file

2, the database is not completely shut down (database crashes or use the Shutdown Abort command to close the database)

1 Oracle tablespace Recovery 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.gr oup#;

5) svrmgrl> Select file#,change# from V$recover_file;

6) svrmgrl> recover datafile filename;

7) svrmgrl> ALTER DATABASE open;

3, the database is in the open state

1 Delete rollback segment and table space

2 reconstruction of table space and rollback segment

Five. Control File Recovery

1. All control files have been compromised.

Copy the backup control file to the original directory for raw device:

DD if= ' con.bak ' of= '/dev/rdrd/drd1 ' seek=128

2. Not all control files have been compromised

Start the database with another control file