Oracle rollback segment tablespace file loss handling

The database is started.

If you detect that the data file of the tablespace In the rollback segment is lost or damaged and the database is running, do not drop it down. In many cases, it is easier to start a database than to close it to solve the problem.

There are two possible solutions for this situation:

A) make the lost data file offline and restore it from the backup. This situation applies when the database is archived.

B) Another method is to remove the rollback segments of the tablespace to which all files belong, drop the tablespace, and create them. You may have to kill the processes that use rollback segments to make them offline.

Method II. A: Restore the data file from the backup

This method can be used only when your database is archived.

1. offline data file.

ALTER DATABASE DATAFILE OFFLINE;

Tip: you may need to create a temporary rollback tablespace and some temporary rollback segments for normal business operation as the transaction volume of the current database.

2. restore the data file from the backup.

3. Execute the following command:

SELECT V1.GROUP #, MEMBER, SEQUENCE # From v $ LOG V1, V $ LOGFILE V2 WHERE V1.GROUP # = V2.GROUP #;

This will list all online redo logs and their sequence numbers and the first change numbers ).

4. Use online logs and archive logs to restore the file.

RECOVER DATAFILE

5. confirm that all logs are restored, only when you receive the message "Media recovery complete.

6. Make the data file online

ALTER DATABASE DATAFILE ONLINE;

Method II. B: recreate the rollback tablespace

This method does not have to consider whether the database is in the archive mode.

The procedure is as follows:

1. Try to take offline all the rollback segments contained in the rollback tablespace where the data file is lost or damaged.

Alter rollback segment offline;

Repeat this command until all rollback segments are offline.

2. Check the status of the rollback segment.

They must be in offline state before dropping them.

SELECT SEGMENT_NAME, status from DBA_ROLLBACK_SEGS WHERE TABLESPACE_NAME = ;

3. Delete all offline c files.

DROP ROLLBACK SEGMENT ;

4. Process rollback segments that remain online

Repeat the 2-bit command. If the rollback segment is still in the "ONLINE" status when executing 1, it means that there are active transactions in it, you can use the following query to confirm:

SELECT SEGMENT_NAME, XACTS ACTIVE_TX, V. STATUS From v $ rollstat v, DBA_ROLLBACK_SEGS WHERE TABLESPACE_NAME = AND SEGMENT_ID = USN;

If no result is returned for this query, it means that no transaction is in these rollback segments. If any result is returned, the status of rollback segments that cannot be offline should be "pending offline ". You can use the method in step 5 to kill these transactions.

5. Force the rollback segments of active transactions to be taken offline

Run the following query to check the transactions contained in the rollback segments of these pending offline statements.

Select s. SID, S. SERIAL #, S. USERNAME, R. NAME "ROLLBACK" From v $ session s, V $ transaction t, V $ ROLLNAME R Where r. name in ( ,..., ) And s. TADDR = T. addr and t. XIDUSN = R. USN;

Run the alter system kill session Statement to KILL these transactions and repeat the preceding query until no transaction exists. Then run the query in step 2 to confirm that these rollback segments are in the offline status, drop them with the statements in step 3.

6. Delete the rollback tablespace.

DROP TABLESPACE Including contents;

If the statement fails to be executed, contact oracle technical support. Otherwise, go to 7.

7. Recreate the tablespace In the rollback segment.

8. Recreate the rollback segments and make them online ).

In practice, the loss or damage of data files in the tablespace In the rollback segment is tricky and common, for example, damage to the media, human error, and sudden power failure of the machine.

It is recommended that oracle fans who have not practiced such operations can simulate such a fault. The actual test should be carried out in the test database. The simulation method is as follows:

1. An rbs tablespace is created separately and a rollback segment rbs_test is created in the tablespace.

2. Specify a transaction to use This rollback segment.

SQL> set transaction use rollback segment rbs_test; SQL> insert into test values (2 ); SQL> insert into test values (3 );

3. Open another telnet window to telnet to the host and execute the following command:

Sqlplus/nolog SQL> conn/as sysdba SQL> shutdown abort

4. Change the data file name of the newly added tablespace In the rollback segment.

(