oracle| data
Continued above: HTTP://WWW.CSDN.NET/DEVELOP/READ_ARTICLE.ASP?ID=20220
Ii. the database is activated.
If you detect a data file that is missing or corrupted the rollback segment tablespace, and the database is running, do not drop it. In many cases, the database is open rather than closed to solve the problem.
Two possible solutions to this situation are:
A offline The missing data file and restores it from backup, which is true for databases that are archived.
B Another method is to offline the rollback segment of all the table spaces that the file belongs to, drop that tablespace, and then build them. You may have to kill the process that uses the rollback segment to make it offline.
Method II.A: Restore that data file from backup
This method only uses your library in an archived way.
1, offline (offline) that lost data file.
ALTER DATABASE datafile ' <full_path_file_name> ' OFFLINE;
Tip: For the current database transaction volume, you may need to build a temporary rollback table space and some temporary rollback segments for normal business operation.
2, restore the data file from the backup (Restore).
3. Execute the following order
SELECT V1. group#, member, sequence#
From V$log V1, V$logfile V2
WHERE V1. group# = V2. group#;
This will list all the online redo logs and their serial number and first change numbers.
4, to use the online log and archive log to restore the file
RECOVER datafile ' <full_path_file_name> '
5. Verify that all logs are restored, only to the information you receive "Media recovery complete".
6. Make this data file online
ALTER DATABASE datafile ' <full_path_file_name> ' ONLINE;
Method II.b: Rebuilding the rollback table space
This approach does not have to take into account whether the database is archive mode.
The steps are as follows:
1. Any missing or corrupted data files that you attempt to go offline contain the rollback segments that are contained in the rollback table space.
ALTER ROLLBACK SEGMENT <rollback_segment> OFFLINE;
Repeat this command until the included rollback segment is taken offline.
2, check the status of the rollback segment.
They must be offline state before dropping them.
SELECT segment_name, STATUS from Dba_rollback_segs
WHERE tablespace_name = ' <TABLESPACE_NAME> ';
3, delete all offline C.
DROP ROLLBACK SEGMENT <rollback_segment>;
4, processing those keep online status of the rollback segment
Repeat the command under 21, if the rollback segment in the execution 1 command still keep the "online" status, meaning that there are active transactions, 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 = ' <TABLESPACE_NAME> ' and segment_id = USN;
If the query returns without results, it means that there are no transactions in those rollback segments. Which results are returned, the status of the rollback segment that cannot be OFFLINE should be "PENDING OFFLINE". You can kill these transactions in 5.
5. Force the rollback segment with active transaction to go offline
Perform the following query to see what transactions the rollback segment of these "PENDING OFFLINE" contains.
Select S.sid, s.serial#, S.username, r.name "ROLLBACK"
From V$session S, V$transaction T, V$rollname R
WHERE r.name in (' <PENDING_ROLLBACK_1> ', ..., ' <PENDING_ROLLBACK_N> ')
and s.taddr = t.addr and T.xidusn = R.usn;
Use ALTER SYSTEM kill session ' <sid> <SERIAL#> '; statement kill these transactions, repeat the query until no transaction exists, then run the query in 2, Confirm that the rollback segments are in the offline state and drop them with the statements in 3.
6, delete this rollback table space.
DROP tablespace <tablespace_name> including CONTENTS;
If the statement fails, please contact Oracle Technical Support, or turn to 7
7, rebuild the rollback segment table space.
8. Rebuild the rollback segment and bring them online.
Translator by:
The loss or damage of data files in the rollback segment tablespace is more difficult and common in practice, resulting in many of the original problems, such as damage to the media, artificial misoperation, sudden power outages, and so on.
It is recommended that the Oracle enthusiasts who have not practiced this kind of operation can simulate this kind of fault, the actual measurement, attention must be in the test library, I simulate the following methods:
1, built a separate RBS table space, and in this table space built a rollback segment rbs_test.
2. Specify a transaction 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, another Telnet window telnet to the host, execute the following command:
Sqlplus/nolog
Sql>conn/as SYSDBA
Sql>shutdown Abort
4, the new addition of the rollback section of the table space data file more name.
Complete Full text
