UNDO tablespace offline dropped

UNDO tablespace offline dropped

Alert log reports the following

Mon Mar  8 16:13:08 2010
Dictionary check beginning
File #2 is offline, but is part of an online tablespace.
data file 2: '/undo1/undo_01.dbf'
Dictionary check complete
Mon Mar  8 16:13:08 2010
SMON: enabling tx recovery
SMON: about to recover undo segment 1
Mon Mar  8 16:13:08 2010
Database Characterset is UTF8
Mon Mar  8 16:13:08 2010
SMON: mark undo segment 1 as needs recovery
SMON: about to recover undo segment 2
SMON: mark undo segment 2 as needs recovery
SMON: about to recover undo segment 3
SMON: mark undo segment 3 as needs recovery
SMON: about to recover undo segment 4
SMON: mark undo segment 4 as needs recovery
SMON: about to recover undo segment 5
SMON: mark undo segment 5 as needs recovery
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
SMON: about to recover undo segment 7
SMON: mark undo segment 7 as needs recovery
SMON: about to recover undo segment 8
SMON: mark undo segment 8 as needs recovery
SMON: about to recover undo segment 9
SMON: mark undo segment 9 as needs recovery
SMON: about to recover undo segment 10
SMON: mark undo segment 10 as needs recovery
Mon Mar  8 16:13:08 2010
Errors in file /apps1/oracle1/product/9.2.0.1.0/admin/SSEAST1/bdump/sseast1_smon_2
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/undo1/undo_01.dbf'

Solution

SQL> select segment_name,status from dba_rollback_segs;

SEGMENT_NAME                   STATUS

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

  SYSTEM                          ONLINE
_SYSSMU1$                      NEEDS RECOVERY
_SYSSMU2$                      NEEDS RECOVERY
_SYSSMU3$                      NEEDS RECOVERY
_SYSSMU4$                      NEEDS RECOVERY
_SYSSMU5$                      NEEDS RECOVERY
_SYSSMU6$                      NEEDS RECOVERY
_SYSSMU7$                      NEEDS RECOVERY
_SYSSMU8$                      NEEDS RECOVERY
_SYSSMU9$                      NEEDS RECOVERY
_SYSSMU10$                    NEEDS RECOVERY

here undo segment 1 to 10 needs recovery

SQL> select status from v$instance;

 

STATUS

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

OPEN

SQL> shut immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

Set this parameter in init<sid>.ora

 *._OFFLINE_ROLLBACK_SEGMENTS=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)

 

SQL> startup pfile=init<sid>.ora

ORACLE instance started.

 

Total System Global Area 3763039696 bytes

Fixed Size                   740816 bytes

Variable Size            1174405120 bytes

Database Buffers         2566914048 bytes

Redo Buffers               20979712 bytes

Database mounted.

ORA-00604: error occurred at recursive SQL level 1

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: '/undo1/undo_01.dbf'

 

SQL> show parameter undo_tablespace

 

NAME                                 TYPE

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

VALUE

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

undo_tablespace                      string

UNDOTBS

 

 

SQL> create undo tablespace undotbs1 datafile '/undo1/undotbs01.dbf' size 2048m;

 

Tablespace created.

SQL> select * from v$recover_file;

 

     FILE# ONLINE  ONLINE_

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

ERROR                                                                CHANGE#

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

TIME

---------

         2 OFFLINE OFFLINE

UNKNOWN ERROR                                                     7.9468E+12

06-MAR-10

 

SQL> shut immediate

ORA-00604: error occurred at recursive SQL level 1

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: '/undo1/undo_01.dbf'

 

SQL> alter system set undo_tablespace=UNDOTBS1;

 

System altered.

 

SQL> SHOW PARAMETER UNDO_TABLESPACE;

 

NAME                                 TYPE

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

VALUE

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

undo_tablespace                      string

UNDOTBS1

SQL>

  

SQL> SHUT IMMEDIATE

ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/undo1/undo_01.dbf'

SQL> shut abort;

ORACLE instance shut down.

 Set in init<sid>.ora

 
undo_tablespace                 = UNDOTBS1

SQL> drop tablespace undotbs including contents and datafiles;

 Tablespace dropped.

 SQL> shut immediate

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

SQL> create undo tablespace undotbs datafile '/undo1/undo_01.dbf' size 4096m;

 Tablespace created.

 

SQL> alter system set undo_tablespace=UNDOTBS;

System altered.

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

 

SQL> shut immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup pfile=initSSEAST1.ora

ORACLE instance started.

 
Total System Global Area 3763039696 bytes
Fixed Size                   740816 bytes
Variable Size            1174405120 bytes
Database Buffers         2566914048 bytes
Redo Buffers               20979712 bytes
Database mounted.
Database opened.

SQL> create spfile from pfile;

File created.

 SQL> shut immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 
Total System Global Area 3763039696 bytes
Fixed Size                   740816 bytes
Variable Size            1174405120 bytes
Database Buffers         2566914048 bytes
Redo Buffers               20979712 bytes
Database mounted.
Database opened.

SQL> exit