Oracle rollback segment Data file corruption repair _

Oracle rollback segment Data file corruption repair __oracle

Last Update:2018-07-23 Source: Internet

Author: User

Tags rollback

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

The following is from: http://lovexueer.itpub.net/post/5072/49241

December 26 (Monday)

Test environment:

Os:xp

db:9201

This test tests the following conditions:

A. Normal shutdown database, database (not) archive, rollback segment table space lost, successful recovery

B. Abnormal shutdown of the database, the database is not archived, there are active transactions, rollback segment table space lost, successful recovery

C. Abnormal shutdown of the database, database archiving, active transactions, rollback segment tablespace loss, recovery failure, at present do not know why

A. Normal shutdown Database , Database ( not ) Archive

such as shutdown Normal,shutdown immediate

1. Normal shutdown of the database, simulation rollback segment tablespace loss

Sql> Conn Sys/test@jumper as Sysdba

is connected.

sql> shutdown immediate;

The database has been closed.

The database has been unloaded.

The ORACLE routine has been closed.

Sql> Host del d:oracleoradatajumperundotbs01.dbf

Sql> Startup

The ORACLE routine has started.

Total System Global area 126950220 bytes

Fixed Size 453452 bytes

Variable Size 109051904 bytes

Database buffers 16777216 bytes

Redo buffers 667648 bytes

Database loading complete.

ORA-01157: Unable to identify/lock data file 2-see DBWR trace file

ORA-01110: Data file 2: ' D:oracleoradatajumperundotbs01. DBF '

2. confirm rollback segment table space is missing

Sql> select File#,online_status,error from V$recover_file;

file# Online_ Status Error

---------- -------

2 ONLINE FILE not FOUND

Sql> select name from V$datafile where file#=2;

NAME

----------------------------------------------------

D:oracleoradatajumperundotbs01. Dbf

3. Open the database

sql> ALTER DATABASE datafile ' D:ORACLEORADATAJUMPERUNDOTBS01.DBF ' offline drop;

The database has changed.

sql> ALTER DATABASE open;

The database has changed.

4. Create a new rollback segment tablespace

Sql> Select Segment_name, tablespace_name,status from Dba_rollback_segs;

Segment_name Tablespace_name STATUS

------------------------------ ------------------------------ ----------------

System System ONLINE

_syssmu1$ UNDOTBS1 OFFLINE

_syssmu2$ UNDOTBS1 OFFLINE

_syssmu3$ UNDOTBS1 OFFLINE

_syssmu4$ UNDOTBS1 OFFLINE

_syssmu5$ UNDOTBS1 OFFLINE

_syssmu6$ UNDOTBS1 OFFLINE

_syssmu7$ UNDOTBS1 OFFLINE

_syssmu8$ UNDOTBS1 OFFLINE

_syssmu9$ UNDOTBS1 OFFLINE

_syssmu10$ UNDOTBS1 OFFLINE

11 rows have been selected.

sql> Create undo tablespace undots datafile ' undotbs02.dbf ' size 100m autoextend on next 10m maxsize 200m

The table space has been created.

Sql> alter system set undo_tablespace=undots;

The system has changed.

Sql> drop tablespace UNDOTBS1 including contents and datafiles;

Table space has been discarded.

Sql> Select Segment_name, tablespace_name,status from Dba_rollback_segs;

Segment_name Tablespace_name STATUS

------------------------------ ------------------------------ ---------------

System System ONLINE

_syssmu11$ Undots ONLINE

_syssmu12$ Undots ONLINE

_syssmu13$ Undots ONLINE

_syssmu14$ Undots ONLINE

_syssmu15$ Undots ONLINE

_syssmu16$ Undots ONLINE

_syssmu17$ Undots ONLINE

_syssmu18$ Undots ONLINE

_syssmu19$ Undots ONLINE

_syssmu20$ Undots ONLINE

11 rows have been selected.

5. reboot

sql> startup force;

The ORACLE routine has started.

Total System Global area 126950220 bytes

Fixed Size 453452 bytes

Variable Size 109051904 bytes

Database buffers 16777216 bytes

Redo buffers 667648 bytes

Database loading complete.

The database is already open.

Sql> Select Segment_name, tablespace_name,status from Dba_rollback_segs;

Segment_name Tablespace_name STATUS

------------------------------ ------------------------------ ----------------

System System ONLINE

_syssmu11$ Undots ONLINE

_syssmu12$ Undots ONLINE

_syssmu13$ Undots ONLINE

_syssmu14$ Undots ONLINE

_syssmu15$ Undots ONLINE

_syssmu16$ Undots ONLINE

_syssmu17$ Undots ONLINE

_syssmu18$ Undots ONLINE

_syssmu19$ Undots ONLINE

_syssmu20$ Undots ONLINE

11 rows have been selected.

Sql> Show Parameter Undo

NAME TYPE VALUE

------------------------------------ ----------- -----------------------------

Undo_management string AUTO

Undo_retention integer 10800

Undo_suppress_errors Boolean FALSE

Undo_tablespace string Undots

With the above method, the (not) archive can open the database normally

B. shut down database not properly ( database not archived )

as Shutdown Abort or Crash

1. generate test Data

Sql> Conn Test/test@jumper

is connected.

sql> TRUNCATE TABLE test;

The table has been truncated.

sql> INSERT INTO test values (1);

1 lines have been created.

2. Abnormal shutdown database, delete rollback segment tablespace

Open a window again

sql> Shutdown Abort

The ORACLE routine has been closed.

Sql> Host del d:oracleoradatajumperundotbs01.dbf

3. Create startup parameter file

Sql> Startup

The ORACLE routine has started.

Total System Global area 135338868 bytes

Fixed Size 453492 bytes

Variable Size 109051904 bytes

Database buffers 25165824 bytes

Redo buffers 667648 bytes

Database loading complete.

ORA-01157: Unable to identify/lock data file 2-see DBWR trace file

ORA-01110: Data file 2: ' D:oracleoradatajumperundotbs01. DBF '

Sql> create Pfile from SPFile;

The file has been created.

sql> shutdown Immediate

ORA-01109: Database not open

The database has been unloaded.

The ORACLE routine has been closed.

4. Modify startup parameter file D:oracleora92databaseinitjumper.ora,moun Database

Original parameter:

Undo_management= ' AUTO '

Undo_tablespace= ' UNDOTBS1 '

Modified to:

undo_management= ' manual '

Undo_tablespace= ' System '

_corrupted_rollback_segments= (_syssmu1$,_syssmu2$,_syssmu3$,_syssmu4$,_syssmu5$,_syssmu6$,_syssmu7$,_syssmu8$, _syssmu9$,_syssmu10$)

5.sql> Startup Mount Pfile=d:oracleora92databaseinitjumper.ora

The ORACLE routine has started.

Total System Global area 135338868 bytes

Fixed Size 453492 bytes

Variable Size 109051904 bytes

Database buffers 25165824 bytes

Redo buffers 667648 bytes

Database loading complete.

5. confirm that the undo Table space is missing

Sql> select File#,error from V$recover_file;

file# ERROR

---------- ---------------------------------------

2 FILE not FOUND

Sql> select name from V$datafile where file#=2;

NAME

------------------------------------------------------------

D:oracleoradatajumperundotbs01. Dbf

6. Open the database

sql> ALTER DATABASE DataFile ' D:ORACLEORADATAJUMPERUNDOTBS01. DBF ' offline drop;

The database has changed.

sql> Recover database;

Complete media recovery.

sql> ALTER DATABASE open;

The database has changed.

Sql> Conn Test/test@jumper;

is connected.

sql> Select *from test;

----------

7 . Create a new ROLLBACK table Space

Sql> select * from V$rollname;

USN NAME

---------- ------------------------------

0 SYSTEM

Sql> select Segment_name,tablespace_name,status from Dba_rollback_segs;

Segment_name Tablespace_name STATUS

------------------------------ ------------------------------ ----------------

System System ONLINE

_syssmu1$ UNDOTBS1 NEEDS RECOVERY

_syssmu2$ UNDOTBS1 NEEDS RECOVERY

_syssmu3$ UNDOTBS1 NEEDS RECOVERY

_syssmu4$ UNDOTBS1 NEEDS RECOVERY

_syssmu5$ UNDOTBS1 NEEDS RECOVERY

_syssmu6$ UNDOTBS1 NEEDS RECOVERY

_syssmu7$ UNDOTBS1 NEEDS RECOVERY

_syssmu8$ UNDOTBS1 NEEDS RECOVERY

_syssmu9$ UNDOTBS1 NEEDS RECOVERY

_syssmu10$ UNDOTBS1 NEEDS RECOVERY

11 rows have been selected.

Sql> drop rollback segment "_syssmu1$";