Wednesday, November 19, 2008

ORA-00604: error occurred at recursive SQL level 1... ORA-00376:... ORA-01110:

I found error this below... and found out to solve it:

Database mounted.

ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/oradata/db2/undotbs01.dbf'


Check '/oradata/db2/undotbs01.dbf' file:

$ ls -al /oradata/db2/undotbs01.dbf

-rw-r----- 1 oracle oinstall 325066752 Nov 19 11:44 /oradata/db2/undotbs01.dbf


SQL> select segment_name,status from dba_rollback_segs;

SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU10_1192403689$ NEEDS RECOVERY
_SYSSMU9_1192403689$ NEEDS RECOVERY
_SYSSMU8_1192403689$ NEEDS RECOVERY
_SYSSMU7_1192403689$ NEEDS RECOVERY
_SYSSMU6_1192403689$ NEEDS RECOVERY
_SYSSMU5_1192403689$ NEEDS RECOVERY
_SYSSMU4_1192403689$ NEEDS RECOVERY
_SYSSMU3_1192403689$ NEEDS RECOVERY
_SYSSMU2_1192403689$ NEEDS RECOVERY
_SYSSMU1_1192403689$ NEEDS RECOVERY


Check undo_tablespace parameter:

SQL> show parameter undo_tablespace

NAME TYPE VALUE
------------------------------------ 
undo_tablespace string UNDOTBS1


Try to recover it:

SQL> recover datafile 3;

Media recovery complete.


But can't solve it:

So, Create New UNDO TABLESPACE:

SQL> CREATE UNDO TABLESPACE "UNDOTBS2" 
DATAFILE'/oradata/db2/undotbs02.dbf' SIZE 100M AUTOEXTEND 
ON NEXT 10M MAXSIZE UNLIMITED RETENTION NOGUARANTEE;

Tablespace created.


SQL> alter system set undo_tablespace=UNDOTBS2 ;

System altered.



SQL> shutdown immediate ;

ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/oradata/db2/undotbs01.dbf'



But can not shutdown:

SQL> select segment_name,status from dba_rollback_segs;

SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU10_1192403689$ NEEDS RECOVERY
_SYSSMU9_1192403689$ NEEDS RECOVERY
_SYSSMU8_1192403689$ NEEDS RECOVERY
_SYSSMU7_1192403689$ NEEDS RECOVERY
_SYSSMU6_1192403689$ NEEDS RECOVERY
_SYSSMU5_1192403689$ NEEDS RECOVERY
_SYSSMU4_1192403689$ NEEDS RECOVERY
_SYSSMU3_1192403689$ NEEDS RECOVERY
_SYSSMU2_1192403689$ NEEDS RECOVERY
_SYSSMU1_1192403689$ NEEDS RECOVERY
_SYSSMU20_1227070673$ ONLINE
_SYSSMU19_1227070673$ ONLINE
_SYSSMU18_1227070673$ ONLINE
_SYSSMU17_1227070673$ ONLINE
_SYSSMU16_1227070673$ ONLINE
_SYSSMU15_1227070673$ ONLINE
_SYSSMU14_1227070673$ ONLINE
_SYSSMU13_1227070673$ ONLINE
_SYSSMU12_1227070673$ ONLINE
_SYSSMU11_1227070673$ ONLINE



try to online on old file:

SQL> alter database datafile 3 online;

After that can shutdown...

SQL> shutdown

SQL> startup
.
.
.
Database mounted.
Database opened.

SQL> select segment_name,status from dba_rollback_segs;

SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU10_1192403689$ OFFLINE
_SYSSMU9_1192403689$ OFFLINE
_SYSSMU8_1192403689$ OFFLINE
_SYSSMU7_1192403689$ OFFLINE
_SYSSMU6_1192403689$ OFFLINE
_SYSSMU5_1192403689$ OFFLINE
_SYSSMU4_1192403689$ OFFLINE
_SYSSMU3_1192403689$ OFFLINE
_SYSSMU2_1192403689$ OFFLINE
_SYSSMU1_1192403689$ OFFLINE
_SYSSMU20_1227070673$ ONLINE
_SYSSMU19_1227070673$ ONLINE
_SYSSMU18_1227070673$ ONLINE
_SYSSMU17_1227070673$ ONLINE
_SYSSMU16_1227070673$ ONLINE
_SYSSMU15_1227070673$ ONLINE
_SYSSMU14_1227070673$ ONLINE
_SYSSMU13_1227070673$ ONLINE
_SYSSMU12_1227070673$ ONLINE
_SYSSMU11_1227070673$ ONLINE


But don't want Old rollback segments (OFFLINE)

SQL> drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.

SQL> select segment_name,status from dba_rollback_segs;

SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU20_1227070673$ ONLINE
_SYSSMU19_1227070673$ ONLINE
_SYSSMU18_1227070673$ ONLINE
_SYSSMU17_1227070673$ ONLINE
_SYSSMU16_1227070673$ ONLINE
_SYSSMU15_1227070673$ ONLINE
_SYSSMU14_1227070673$ ONLINE
_SYSSMU13_1227070673$ ONLINE
_SYSSMU12_1227070673$ ONLINE
_SYSSMU11_1227070673$ ONLINE

solved its.......

No comments: