Loss or damage processing method for oracle rollback segment tablespace data file (2)

Continued above: http://www.9cbs.net/develop/read_article.asp? Id = 20220

Ii. The database is started

If you detect a data file that is lost or damaged, the database is running, don't drop it down. In many cases, the database is more likely to solve the problem than closing.

Two possible solutions for this situation:

A) Make the lost data file offline and restore it from the backup, which is applicable to the database is an archive.

B) Another method is OFFLINE to drop all the returns of the table space in the form of the file, the Drop of the table space, and then build them. You may have to kill those processes that use back rolls in order to make it offline.

Method II.A: Restore that data file from backup

This method is only your library is in the archive mode.

1, offline (offline) that lost data file. Alter Database DataFile '' Offline; Tip: It is a transaction volume of the current database, you may need to build a temporary rolling table space and some temporary rollback segments for normal business.

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 serial number and the first change number FIRST CHANGE NUMBERS.

4, with online log and archiving log to restore that file recover datafile '' 5, confirm that all logs are recovered, only to "Media Recovery Complete" information.

6, make this data file online alter database datafile '' online;

Method II.B: Reconstruction Rolling Mark Space

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

Proceed as follows:

1. Attempting to offline all lost or damaging the rollback segments contained in the rolling table space in the data file. Alter Rollback Segment offline; Repeat this command until the contained rollback segment is offline. 2, check the status of the backgrou. They must be OFFLIN before DROP. Select segment_name, Status from dba_rollback_segs where tablespace_name = '';

3. Delete all off all offline c. Drop rollback segment ; 4, handling those returns to keep the backlit segment that keeps the ONLINE state, if the segment is in executing 1, the command remains to turn to the "Online" status, meaning it. transaction activity, you can confirm this with the following query: SELECT SEGMENT_NAME, XACTS ACTIVE_TX, V.STATUS FROM V $ ROLLSTAT V, DBA_ROLLBACK_SEGS WHERE TABLESPACE_NAME = '' aND SEGMENT_ID = USN; if this query returns no result, It means that there is no transaction in these returns. Where is the result of returning, the state of those returning segments that cannot OFFLINE should be "pending offline". These transactions can be killed in 5 methods. 5. Force the rollback section of the active affairs to perform the following query, see which transactions are included in the rollback segment of these "Pending Offline". Select S.SID, S.Serial #, S.Username, R.Name "Rollback" from V $ Session S, V $ T, V $ ROLLNAME R Where R.Name in (' ", ... , '') And S.Taddr = T.Addr and T.Xidusn = R.USN; use ALTER SYSTEM KILL SESSION ', '; the statement kills these transactions, repeat the above Query until there is no transaction, then run the query in this 2, confirm that these returns are in the OFFLINE status, and use the statements in 3 to drop them DROP.

6. Delete this rolling table space. Drop tableSpace include Contents; if the statement does fail, please contact Oracle Technical Support, otherwise steering 7

7, rebuild the split table space.

8, rebuild the roll band and make them online.

Translator presses:

Data file loss or damage in the splitting table space is more tricky and common in practice, which produces a lot of original back, such as damage, artificial malfunction, and sudden power outages of the machine.

It is recommended that this kind of fans who have not practiced this operation can simulate this failure. It actually measures, pay attention to the test library, my simulation method is as follows:

1. A RBS table space has been built separately and built a backgrou RBS_TEST in this table.

2, specify a transaction with 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 Telnet to the host, execute the following command:

SQLPlus / NOLOG

SQL> conn / as sysdba

SQL> Shutdown Abort

4, more names of the newly added back paragraph table space.

Full text