Skip to content

Undodatafile Corrupt in Noarchive-Archive mode Scenario1

December 29, 2013

Recovery from Corrupted/deleted Undo datafile without backup, after last Clean Shutdown in NOARCHIVE/Archive mode.

After last clean shutdown, while restarting DB we see database not coming up and found datafile to corrupted/deleted.

In this case steps remain same for Archive and NoArchive mode.

1) Check DB is NOArchive mode and Clean Shutdown DB

SQL> select NAME,OPEN_MODE,LOG_MODE from v$database;

 NAME      OPEN_MODE            LOG_MODE
——— ——————– ————
D041      READ WRITE           NOARCHIVELOG
 
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            C:\Oracle\oradata\d041\arch
Oldest online log sequence     24
Current log sequence           26

 SQL> shut immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.
 

2) Corrupt the UNDO datafile – Simulate the scenario

 C:\Oracle\oradata\d041>dbv FILE=UNDOTBS01.DBF
 DBVERIFY: Release 11.2.0.1.0 – Production on Sun Dec 29 11:17:30 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY – Verification starting : FILE = C:\ORACLE\ORADATA\D041\UNDOTBS01.DBF
DBVERIFY – Verification complete
Total Pages Examined         : 2560
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 168
Total Pages Processed (Seg)  : 20
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 2392
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 1080034 (0.1080034)
 
Now Corrupt Undo file
================================

Manually adding data to undo file to the end of file to simulate corruption.

C:\Oracle\oradata\d041>echo A >> UNDOTBS01.DBF

C:\Oracle\oradata\d041>dbv FILE=UNDOTBS01.DBF

DBVERIFY: Release 11.2.0.1.0 – Production on Sun Dec 29 11:18:24 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
DBV-00600: Fatal Error – [21] [5] [0] [0]

OR

If you Edit using the Edit plus , and delete some content from middle of file.

C:\Oracle\oradata\d041>dbv FILE=UNDOTBS01.DBF
DBVERIFY: Release 11.2.0.1.0 – Production on Sun Dec 29 10:35:22 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
DBV-00102: File I/O error on FILE (C:\ORACLE\ORADATA\D041\UNDOTBS01.DBF) during end read operation (7680)

3) Now Start the DB, and initiate recover database command as below.

SQL> startup
ORACLE instance started.
Total System Global Area  368263168 bytes
Fixed Size                  1374668 bytes
Variable Size             209716788 bytes
Database Buffers          150994944 bytes
Redo Buffers                6176768 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 – see DBWR trace file
ORA-01110: data file 3: ‘C:\ORACLE\ORADATA\D041\UNDOTBS01.DBF’
 
SQL> select NAME,OPEN_MODE,LOG_MODE from v$database;
NAME       OPEN_MODE            LOG_MODE
———- ——————– ————
D041       MOUNTED              NOARCHIVELOG
 

Whenever DB shuts cleanly, CHECKPOINT_CHANGE# and LAST_CHANGE# must always be sync else they need to do crash/media recovery, as below

SQL> set line 200
col LAST_TIME for a25
col name for a45
select NAME,STATUS,CHECKPOINT_CHANGE#,LAST_CHANGE#,to_char(LAST_TIME,’DD-MON-YYYY HH24:MI:SS’) LAST_TIME from  v$datafile;
NAME                                          STATUS  CHECKPOINT_CHANGE# LAST_CHANGE# LAST_TIME
——————————————— ——- —————— ———— ———————
C:\ORACLE\ORADATA\D041\SYSTEM01.DBF           SYSTEM             1080041      1080041 29-DEC-2013 10:58:30
C:\ORACLE\ORADATA\D041\SYSAUX01.DBF           ONLINE             1080041      1080041 29-DEC-2013 10:58:30
C:\ORACLE\ORADATA\D041\UNDOTBS01.DBF          ONLINE             1080041      1080041 29-DEC-2013 10:58:30
C:\ORACLE\ORADATA\D041\USERS01.DBF            ONLINE             1080041      1080041 29-DEC-2013 10:58:30
C:\ORACLE\ORADATA\D041\EXAMPLE01.DBF          ONLINE             1080041      1080041 29-DEC-2013 10:58:30

SQL> recover database;

ORA-00283: recovery session canceled due to errors
ORA-01110: data file 3: ‘C:\ORACLE\ORADATA\D041\UNDOTBS01.DBF’
ORA-01157: cannot identify/lock data file 3 – see DBWR trace file
ORA-01110: data file 3: ‘C:\ORACLE\ORADATA\D041\UNDOTBS01.DBF’
 
SQL> set line 200
col error for a20
select * from v$recover_file;
    FILE# ONLINE  ONLINE_ ERROR                   CHANGE# TIME
——— ——- ——- ——————– ———- ———
        3 ONLINE  ONLINE  FILE NOT FOUND                0
 

 

4) Try to Offline the corrupted undo Datafile and open Database.

SQL> alter database datafile 3 offline; (Use this for Archive Log Mode)
alter database datafile 3 offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled

http://docs.oracle.com/cd/B28359_01/server.111/b28310/dfiles004.htm#ADMIN11429

SQL> alter database datafile 3 offline for drop; (Use this for NOArchive Log Mode)

Database altered.

SQL> alter database open;

Database altered.

SQL> select name,open_mode from v$database;

NAME            OPEN_MODE
————— ——————–
D041            READ WRITE

SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                   CHANGE# TIME
———- ——- ——- ——————– ———- ———
         3 OFFLINE OFFLINE FILE NOT FOUND                0
 
SQL> col name for a45
set line 200
select name,status from v$datafile;
NAME                                                         STATUS
———————————————————— ——-
C:\ORACLE\ORADATA\D041\SYSTEM01.DBF                          SYSTEM
C:\ORACLE\ORADATA\D041\SYSAUX01.DBF                          ONLINE
C:\ORACLE\ORADATA\D041\UNDOTBS01.DBF                         OFFLINE
C:\ORACLE\ORADATA\D041\USERS01.DBF                           ONLINE
C:\ORACLE\ORADATA\D041\EXAMPLE01.DBF                         ONLINE
 
SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace ‘UNDOTBS1’ is currently in use
 

On checking parameter undo_management found to be AUTO as below, so it is not all allowing to Drop.

 SQL> show parameter undo
NAME                                 TYPE                             VALUE
———————————— ——————————– ————-
undo_management                      string                           AUTO
undo_tablespace                      string                           UNDOTBS1
 
SQL> show parameter rollback_segments
NAME                                 TYPE                             VALUE
———————————— ——————————– ————-
rollback_segments                    string

5) Change the undo_management to Manual parameters and Restart DB

SQL> alter system set undo_management=MANUAL scope=spfile;

System altered.

SQL> alter system set rollback_segments=SYSTEM scope=spfile;

System altered.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
 
SQL> Startup
ORACLE instance started.
Total System Global Area  368263168 bytes
Fixed Size                  1374668 bytes
Variable Size             209716788 bytes
Database Buffers          150994944 bytes
Redo Buffers                6176768 bytes
Database mounted.
Database opened.

SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR              CHANGE# TIME
———- ——- ——- ————— ———- ———
         3 OFFLINE OFFLINE FILE NOT FOUND           0

SQL> select name,status from v$datafile;

NAME                                          STATUS
——————————————— ——-
C:\ORACLE\ORADATA\D041\SYSTEM01.DBF           SYSTEM
C:\ORACLE\ORADATA\D041\SYSAUX01.DBF           ONLINE
C:\ORACLE\ORADATA\D041\UNDOTBS01.DBF          OFFLINE
C:\ORACLE\ORADATA\D041\USERS01.DBF            ONLINE
C:\ORACLE\ORADATA\D041\EXAMPLE01.DBF          ONLINE
 
SQL> SELECT SEGMENT_NAME, XACTS ACTIVE_TX, V.STATUS
FROM V$ROLLSTAT V, DBA_ROLLBACK_SEGS
WHERE TABLESPACE_NAME = ‘UNDOTBS1’ AND SEGMENT_ID = USN;
no rows selected

6) Drop undo tablespace.

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

 SQL> select * from v$rollname ;
       USN NAME
———- ———————————————
         0 SYSTEM
 
SQL> select SEGMENT_NAME,SEGMENT_ID,status,tablespace_name from dba_rollback_segs ;
SEGMENT_NAME                   SEGMENT_ID STATUS           TABLESPACE_NAME
—————————— ———- —————- ————————
SYSTEM                                  0 ONLINE           SYSTEM
 

7) Create undo TBS, Reset undo_management to AUTO and restart the DB.

 SQL> select * from v$tablespace;

       TS# NAME                                          INC BIG FLA ENC
———- ——————————————— — — — —
         0 SYSTEM                                        YES NO  YES
         1 SYSAUX                                        YES NO  YES
         4 USERS                                         YES NO  YES
         3 TEMP                                          NO  NO  YES
         6 EXAMPLE                                       YES NO  YES

SQL> create undo tablespace UNDOTBS1 datafile ‘C:\ORACLE\ORADATA\D041\UNDOTBS01.DBF’ size 20M reuse;

Tablespace created.

SQL> Alter System set undo_management=AUTO scope=spfile;

System altered.

SQL> Alter System Reset rollback_segments;

System altered.

SQL> show parameter undo
NAME                TYPE       VALUE
——————- ———- ————-
undo_management     string     MANUAL
undo_retention      integer    900
undo_tablespace     string     UNDOTBS1
 
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
 
SQL> startup
ORACLE instance started.
Total System Global Area  368263168 bytes
Fixed Size                  1374668 bytes
Variable Size             209716788 bytes
Database Buffers          150994944 bytes
Redo Buffers                6176768 bytes
Database mounted.
Database opened.

8) Re-check if the Parameters were unset.

SQL> show parameter rollback_segments
NAME                                 TYPE                             VALUE
———————————— ——————————– ————
rollback_segments                    string
 
SQL> show parameter undo
NAME                                 TYPE                             VALUE
———————————— ——————————– ————
undo_management                      string                           AUTO
undo_retention                       integer                          900
undo_tablespace                      string                           UNDOTBS1
 
SQL> select SEGMENT_NAME,SEGMENT_ID,status,tablespace_name from dba_rollback_segs ;
SEGMENT_NAME                   SEGMENT_ID STATUS           TABLESPACE_NAME
—————————— ———- —————- ————————
SYSTEM                                  0 ONLINE           SYSTEM
_SYSSMU20_2357329706$                  20 ONLINE           UNDOTBS1
_SYSSMU19_3758938093$                  19 ONLINE           UNDOTBS1
_SYSSMU18_3208537726$                  18 ONLINE           UNDOTBS1
_SYSSMU17_4050930344$                  17 ONLINE           UNDOTBS1
_SYSSMU16_4130629382$                  16 ONLINE           UNDOTBS1
_SYSSMU15_282708105$                   15 ONLINE           UNDOTBS1
_SYSSMU14_222891688$                   14 ONLINE           UNDOTBS1
_SYSSMU13_2419240782$                  13 ONLINE           UNDOTBS1
_SYSSMU12_1069090612$                  12 ONLINE           UNDOTBS1
_SYSSMU11_2414251578$                  11 ONLINE           UNDOTBS1
11 rows selected.
 
SQL> SELECT SEGMENT_NAME, XACTS ACTIVE_TX, V.STATUS
FROM V$ROLLSTAT V, DBA_ROLLBACK_SEGS
WHERE TABLESPACE_NAME = ‘UNDOTBS1’ AND SEGMENT_ID = USN;
 
SEGMENT_NAME                    ACTIVE_TX STATUS
—————————— ———- ———
_SYSSMU11_2414251578$                   0 ONLINE
_SYSSMU12_1069090612$                   0 ONLINE
_SYSSMU13_2419240782$                   0 ONLINE
_SYSSMU14_222891688$                    0 ONLINE
_SYSSMU15_282708105$                    0 ONLINE
_SYSSMU16_4130629382$                   0 ONLINE
_SYSSMU17_4050930344$                   0 ONLINE
_SYSSMU18_3208537726$                   0 ONLINE
_SYSSMU19_3758938093$                   0 ONLINE
_SYSSMU20_2357329706$                   0 ONLINE
10 rows selected.

12) Verify the user data and database.

SQL> conn scott/tiger

Connected.

SQL> set line 200

select * from emp;

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH      CLERK           7902 17-DEC-80        800                    20
7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
7566 JONES      MANAGER         7839 02-APR-81       2975                    20
7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
7839 KING       PRESIDENT            17-NOV-81       5000                    10
7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
7900 JAMES      CLERK           7698 03-DEC-81        950                    30
7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
14 rows selected.
 
SQL> col name for a45
select name,status from v$datafile;
NAME                                          STATUS
——————————————— ——-
C:\ORACLE\ORADATA\D041\SYSTEM01.DBF           SYSTEM
C:\ORACLE\ORADATA\D041\SYSAUX01.DBF           ONLINE
C:\ORACLE\ORADATA\D041\UNDOTBS01.DBF          ONLINE
C:\ORACLE\ORADATA\D041\USERS01.DBF            ONLINE
C:\ORACLE\ORADATA\D041\EXAMPLE01.DBF          ONLINE
 
SQL> select * from v$recover_file;
no rows selected
 
Click Here for the PDF.
 
Advertisements
Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: