Recovery From Undo Tablespace Block Corruption

Applies to:
 

 Oracle Version: 10G and later
Information in this document applies to any platform.

Message:

ORA-01172: recovery of thread 1 stuck at block % of file %
ORA-01151: use media recovery to recover block, restore backup if needed

At startup of database above error message is observer and database doesn’t start.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01172: recovery of thread 1 stuck at block 25 of file 16
ORA-01151: use media recovery to recover block, restore backup if needed
 

Reason:

There is block corruption in UNDO tablespace.

Solutions:


 Here, I am assuming database is in no archive log mode. Use the following steps:

1. Find out the detail of file# 16 as in above error message

SQL> select file#,  TS# from  v$datafile;

     FILE#        TS#
---------- ----------
        13         13
        14         14
        15         15
        16         16
15 rows selected.

SQL> select ts#, name from v$tablespace;

       TS# NAME
---------- ------------------------------
         0 SYSTEM
         2 SYSAUX
         3 TEMP
         4 USERS
         5 INDX
         6 TOOLS
-----------------------
        16 UNDO_TBS

 16# tablespace is undo tablespace.
 

 2.Shutdown database and startup in mount state
 

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  536870912 bytes
Fixed Size                  2022144 bytes
Variable Size             109053184 bytes
Database Buffers          423624704 bytes
Redo Buffers                2170880 bytes
Database mounted.

3. Create pfile from spfile.

SQL> create pfile='$ORACLE_HOME/dbs/inittest01.ora' from spfile;

File created.

4. Edit pfile and change UNDO_MANAGEMENT from AUTOMATIC to MANUAL and startup the database using pfile.

 
SQL> startup pfile='$ORACLE_HOME/dbs/inittest01.ora'
ORACLE instance started.
Total System Global Area  536870912 bytes
Fixed Size                  2022144 bytes
Variable Size             109053184 bytes
Database Buffers          423624704 bytes
Redo Buffers                2170880 bytes

Check the undo tablespace using.

SQL> show parameter undo;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL
undo_retention                       integer     900
undo_tablespace                      string      UNDO_TBS

5. Drop undo tablespace datafile:


SQL> alter database datafile '/test01/dbs/undotbs0.dbf' offline drop;

Database altered.

6. Create new undo tablespace and make it default and crosscheck

SQL> create undo tablespace undotbs1 datafile '/test01/dbs/undotbs1.dbf' size 1G;
Tablespace created.
SQL> alter system set undo_tablespace=undotbs1;
System altered.

SQL> show parameter undo;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

 7. Shutdown the database and open it using spfile (Default).


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

SQL> startup
ORACLE instance started.
Total System Global Area  536870912 bytes
Fixed Size                  2022144 bytes
Variable Size             109053184 bytes
Database Buffers          423624704 bytes
Redo Buffers                2170880 bytes
Database mounted.
Database opened.

8. Drop the Old undo tablespace

If tablespace is dropped successful, otherwise you make face issue like.

SQL> drop tablespace UNDO_TBS;  (Tablespace dropped.)
drop tablespace UNDO_TBS
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU52$' found, terminate dropping tablespace
 

Reason:

 An attempt was made to drop a tablespace that contains active rollback segments.

Solution:

1.  Edit pfile and set undo management to manual.
undo_management = manual
Put the entries of the undo segments in the pfile by using the following statement in the pfile:
_offline_rollback_segments_SYSSMU52$,_SYSSMU53.....)

2.  Open the database using new pfile.

sql> Startup pfile='fullpath of pfile' ;

3.  Drop Old undo tablespace.

sql>Drop Tablespace old_tablespace_name Including Contents and datafiles;


4 comments:

  1. Hi,

    We have a
    Windows Server 2008 R2 Enterprise x64
    Oracle 10.2.0.3
    Adempiere - ERP


    1. We had a power breakdown at 03.00 AM.
    2. At 07.30 AM we started the server
    3. At 08.00 our colleagues observed that the ERP is not functioning
    4. We started Adempiere
    5. ERP was not able to connect to Oracle DB
    6. We entered EM Console and we checked the DB status
    7. The listener was functional - up and running
    8. DB was shotdown in progress
    9. We tried to abort shot down via SQL Plus using command :
    shutdown abort
    10. Nothing happen
    11. We tried shutdown - no result
    12. We tried shutdown immediate - no result
    13. Then we made Windows Shutdown from start menu
    14. After restart of the server we checked in Windows Services.MSC the Oracle running services
    15. Oracle JoobscheduleORCL was disabled, the rest were running. We set this one on automatic
    16. The listener was functional - up and running
    17. DB was shotdown in progress
    18. We tried to abort shot down via SQL Plus using command :
    shutdown abort
    19. Nothing happen
    20. We tried shutdown - no result
    21. We tried shutdown immediate - no result
    22. We tried to restart the Oracle Services with no result
    23. We searched on the forum.oracle.com and we found out that the problem could be that the undotablespace is corrupted.
    24. We tried to fix the table
    25. We used the following command line :

    alter database datafile 'xxxxx\undotbs01.dbf' offline drop;

    http://www.dbas-oracle.com/2011/06/recovery-from-undo-tablespace-block.html


    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5669213349582

    26. The current situation is :

    SQL> startup
    ORA-01081: cannot start already-running ORACLE - shut it down first

    SQL> shutdown immediate
    ORA-24324: service handle not initialized
    ORA-24323: value not allowed
    ORA-01092: ORACLE instance terminated. Disconnection forced

    SQL> startup
    ORA-01031: insufficient privileges

    ReplyDelete
  2. What is there in your alert log files ?

    ReplyDelete
  3. As stated in the point 4, we need to edit the pfile and change the parameter undo_management from AUTO to MANUAL. But in the below screen shot undo_management still reflects as AUTO only.

    Is it correct / needs to be updated ?

    4. Edit pfile and change UNDO_MANAGEMENT from AUTOMATIC to MANUAL and startup the database using pfile.

    SQL> startup pfile='$ORACLE_HOME/dbs/inittest01.ora'
    ORACLE instance started.
    Total System Global Area 536870912 bytes
    Fixed Size 2022144 bytes
    Variable Size 109053184 bytes
    Database Buffers 423624704 bytes
    Redo Buffers 2170880 bytes

    Check the undo tablespace using.

    SQL> show parameter undo;
    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    undo_management string AUTO
    undo_retention integer 900
    undo_tablespace string UNDO_TBS

    ReplyDelete
  4. Yes Friend, You are right, Thanks for your help.

    ReplyDelete