Since the Oracle Rolling Segment Table Space Data File Loss caused the database, it is not possible to report ORA-01157 errors. There are a lot of recovery after returning to the segment data file, this example is mainly used to recover the database with Oracle implicit parameters. An example of:
The meaning of implies parameters:
SQL> SELECT KSPPDESC from x $ ksppi where ksppinm = '_ corrupted_rollback_segments'
Ksppdesc ------------------------------------- --------------- Corrupted undo segment list
The specific steps are as follows:
First, the initialization parameter init.ora file automatically manage for manual management, then add hidden parameters: # undo_management = AUTOundo_tablespace = UNDOTBS_corrupted_rollback_segments = (_ SYSSMU1 $, _ SYSSMU2 $, _ SYSSMU3 $, _ SYSSMU4 $, _ SYSSMU5 $, _ SYSSMU6 $, _ SYSSMU7 $ , _SysSmu8 $, _ syssmu9 $, _ syssmu10 $)
SQL> Startup Restrict Mount (Database Board to Mount Status) SQL> ALTER DATABASE DATAFILE 'D: / ORACLE/Ordata/orcl/undotbs01.dbf' OFFLINE DROP; DATABASE ALTERED.
SQL> ALTABASE OPEN; DATABASE OPENED.SQL> SHOW Parameter Undo
Name Type Value --------------------------------- -------- UNDO_MANAGEMENT STRING MANUALUNDO_RETENTION INTEGER 900UNDO_SUPPRESS_ERRORS BOOLEAN FALSEUNDO_TABLESPACE STRING UNDOTBS
SQL> DROP TABLESPACE UNDOTBS INCLUDING Contents; (if there is activity in the back section, the undo table space may be Drop can't drop, this example is when there is no activity in the rollover segment) TableSpace Dropped.
Rebuild undotbs table space: SQL> CREATE undo TableSpace undotbs DataFile 'D: /oracle/oradata/orcl/undotbs01.dbf' size 100m; tablespace created.
SQL> SHUTDOWN IMMEDIATE (Close Database) Database Closed.Database DISMOUNTED.Oracle Instance Shut Down.
Edit init.ora initialization parameter file, remove the implicit parameter, set undo_management = AUTOundo_tablespace = UNDOTBS save init.ora initialization parameter file, and then execute SQL> startup mountORACLE instance mounted.Total System Global Area 114061244 bytesFixed Size 282556 bytesVariable Size 79691776 bytesDatabase Buffers 33554432 Bytesredo buffers 532480 bytesdatabase mounted.
SQL> ALTABASE DATAFILE 'D: /ORACLE/Ordata/orcl/undotbs01.dbf' Online; Database Altered.SQL> ALTER DATABASE Open; Database Opened.SQL> Show Parameter Undo
Name Type Value --------------------------------- ------------------------------- UNDO_MANAGEMENT STRING Auto_RETENTION INTEGER 900UNDO_SUPPRESS_ERRORS BOOLEAN FALSEUNDO_TABLESPACE STRING UNDOTBS
It is recommended to use the implied parameters to make the database Open, immediately make an EXP fullness, if the amount of data is not large, the best way is to re-establish the library, re-enter the data out of the new database.
At this point, the database is completed. This method is not recommended, the best way is to recover with the backup of the previous data file, because the database is opened with the implicit parameter, and the database may be inconsistent, because for back When there is activity in the roll, the database may lose some data, mainly the part of the data in the retrace, so the backup of the database is crucial, so it is simple and convenient for recovery. .
