UNDO tablespace ORA-1122 error to resolve (3)

Dec 31 posted in Oracle (RSS), 238 views.

Some time ago to solve an ORA-1122 error, just the native database environment because of the automatic re-starting Windows, resulting in the database can not be opened, an error message is ORA-1122.
The error of the table space is UNDO tablespace.
Introduced on a recovery using a backup method, this attempt to rebuild UNDO tablespace method.
UNDO table space to solve ORA-1122 error (1): http://yangtingkun.itpub.net/post/468/455691
UNDO tablespace ORA-1122 error to resolve (2): http://yangtingkun.itpub.net/post/468/455743

An article on the use of hidden parameters _offline_rollback_segments try to open a database, where you can also

Choose to UNDO tablespace data files directly to delete, and then use _corrupted_rollback_segments parameters to open a database, the entire process was similar to the one described in method.
SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area 76619308 bytes
Fixed Size 454188 bytes
Variable Size 50331648 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
The database load is completed.
SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01122: database file 2 validation fails
ORA-01110: data file 2: 'F: \ ORACLE \ ORADATA \ TEST \ UNDOTBS01.DBF'
ORA-01200: 25600 of 26880 actual file size is smaller than try to use the correct size of the _corrupted_rollback_segments below to open a database, use _offline_rollback_segments parameters, Oracle will try to block the delayed clearance, and for _corrupted_rollback_segments run, Oracle assumes transaction has been rolled back, rather than other checks. General UNDO tablespace when the loss or damage can try to use the implicit parameter:
SQL> ALTER DATABASE DATAFILE 'F: \ ORACLE \ ORADATA \ TEST \ UNDOTBS01.DBF' OFFLINE DROP;
The database has changed.
The following initialization parameters to create PFILE:
SQL> SHUTDOWN IMMEDIATE
ORA-01109: database not open

The database has been uninstalled.
ORACLE instance shut down.
SQL> CREATE PFILE = 'F: \ INITTEST.ORA' FROM SPFILE;
File has been created.
Manually add the following initialization parameters:
*. undo_management = 'MANUAL'
_corrupted_rollback_segments = (_SYSSMU1 $, _SYSSMU2 $, _SYSSMU3 $, _SYSSMU4 $, _SYSSMU5 $, _SYSSMU6 $, _SYSSMU7 $, _SYSSMU8 $, _SYSSMU9 $, _SYSSMU10 $)
Can be given in an article on the way to open a database query to get the rollback DBA_ROLLBACK_SEGS view a list.
For starting the database, you can command strings through the linux operating system from the system tablespace rollback segment data file to obtain the information.
Windows environment for Oracle, can be data files via ftp, copy to the linux system, then run the strings command:
strings system01.dbf | grep _SYSSMU
Here the use of the modified PFILE to open database:
SQL> STARTUP PFILE = F: \ INITTEST.ORA
ORACLE instance started.
Total System Global Area 76619308 bytes
Fixed Size 454188 bytes
Variable Size 50331648 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
The database load is completed.
The database has been opened.
The following processing method and described in an article on the comparison of similar, and remove rollback segments, and then delete the rollback table space and then create a new UNDO table space:
SQL> SELECT SEGMENT_NAME, OWNER, TABLESPACE_NAME, STATUS
2 FROM DBA_ROLLBACK_SEGS;
SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
--------------- ------ -------------------- --------- -------
SYSTEM SYS SYSTEM ONLINE
_SYSSMU1 $ PUBLIC UNDOTBS1 NEEDS RECOVERY
_SYSSMU2 $ PUBLIC UNDOTBS1 NEEDS RECOVERY
_SYSSMU3 $ PUBLIC UNDOTBS1 NEEDS RECOVERY
_SYSSMU4 $ PUBLIC UNDOTBS1 NEEDS RECOVERY
_SYSSMU5 $ PUBLIC UNDOTBS1 NEEDS RECOVERY
_SYSSMU6 $ PUBLIC UNDOTBS1 NEEDS RECOVERY
_SYSSMU7 $ PUBLIC UNDOTBS1 NEEDS RECOVERY
_SYSSMU8 $ PUBLIC UNDOTBS1 NEEDS RECOVERY
_SYSSMU9 $ PUBLIC UNDOTBS1 NEEDS RECOVERY
_SYSSMU10 $ PUBLIC UNDOTBS1 NEEDS RECOVERY
Have chosen to line 11.
SQL> DROP ROLLBACK SEGMENT "_SYSSMU1 $";
Rollback segment has been deleted.
SQL> DROP ROLLBACK SEGMENT "_SYSSMU2 $";
Rollback segment has been deleted.
SQL> DROP ROLLBACK SEGMENT "_SYSSMU3 $";
Rollback segment has been deleted.
SQL> DROP ROLLBACK SEGMENT "_SYSSMU4 $";
Rollback segment has been deleted.
SQL> DROP ROLLBACK SEGMENT "_SYSSMU5 $";
Rollback segment has been deleted.
SQL> DROP ROLLBACK SEGMENT "_SYSSMU6 $";
Rollback segment has been deleted.
SQL> DROP ROLLBACK SEGMENT "_SYSSMU7 $";
Rollback segment has been deleted.
SQL> DROP ROLLBACK SEGMENT "_SYSSMU8 $";
Rollback segment has been deleted.
SQL> DROP ROLLBACK SEGMENT "_SYSSMU9 $";
Rollback segment has been deleted.
SQL> DROP ROLLBACK SEGMENT "_SYSSMU10 $";
Rollback segment has been deleted.
SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
The table space has been discarded.
SQL> CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE 'F: \ ORACLE \ ORADATA \ TEST \ UNDOTBS1.DBF' SIZE200M;
Table space has been created.
SQL> SELECT COUNT (*) FROM YANGTK.T;
COUNT (*)
----------
1273614
SQL> SHUTDOWN IMMEDIATE
Database has been closed.
The database has been uninstalled.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.
Total System Global Area 76619308 bytes
Fixed Size 454188 bytes
Variable Size 50331648 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
The database load is completed.
The database has been opened.
And an article in the same way as, Oracle will need to roll back the data deemed to have submitted data, in order to avoid subsequent unforeseen problems in re-remove the hidden parameters from the database, it should export the data and create a new library, the source data into.