How to handle lost undo datafile

As DBA we are not suppose to drop any datafile without proper analysis and backup even if it is offline datafile. But as Human we make mistakes which sometimes cost us large. In this blog i am going to share how to recover lost undo datafile.

There are 2 scenarios lost undo datafile with active transactions and without active transactions. In active transaction case, the preferred option is to restore the datafile from backup and perform media recovery. If there is no active transactions then follow these steps to recover the database.

1. Query to check for active transactions,

SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */ KTUXESTA Status,KTUXECFL Flags
FROM x$ktuxe WHERE ktuxesta!=’INACTIVE’;

select local_tran_id, state from dba_2pc_pending;

Both the above query should return “no rows”.

2. Make clean shutdown of database,

shutdown immediate;

3. create a parameter file from spfile

create pfile from spfile;

4. Update pfile with “undo_management=manual”

5. Start the database

startup pfile=$ORACLE_HOME/dbs/init.ora

6. Drop undo tablespace with datafile

drop tablespace including contents and datafiles;

7. Create a new undo tablespace,

CREATE UNDO TABLESPACE DATAFILE ‘’ SIZE 2M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

8. Shutdown the database

shutdown immediate;

9. Update pfile with “undo_management=AUTO”

10. Create spfile from pfile

create spfile from pfile;

11. Start the database

startup;

Reference
*********

How to Recover from a Lost Datafile with Different Scenarios [ID 198640.1]

SYSTEM09.dbf file corrupted in R12.1.1 fresh install on Linux x86_64

Fresh installed R12.1.1 with rapidwizard version (StartCD) 12.1.1.9 or below on Linux X86_64 contains corrupted SYSTEM09.dbf file. Thankfully those corruption are not data corruption (does not belong to any extents/segments) hence will not affect your installation and database startup. Might be this was the reason that Oracle could not capture this issue before it releases to the customer.

User will identify this corruption only when they starts to backup using RMAN or validates datafile using RMAN. RMAN will fail with below error message,

ORA-19566: exceeded limit of 0 corrupt blocks for file /DBF_LOC/system09.dbf

On verifying the datafile using dbv, there will be around 20000+ corrupted blocks in system09.dbf,

...
Page 192127 is marked corrupt
Corrupt block relative dba: 0x5802ee7f (file 352, block 192127)
Completely zero block found during dbv:

Page 192128 is marked corrupt
Corrupt block relative dba: 0x5802ee80 (file 352, block 192128)
Completely zero block found during dbv:



DBVERIFY - Verification complete

Total Pages Examined : 192128
Total Pages Processed (Data) : 54272
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 26750
Total Pages Failing (Index): 0
Total Pages Processed (Other): 2063
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 82570
Total Pages Marked Corrupt : 26473
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 3755938521 (1388.3755938521)
$

Since the affected blocks are empty blocks we can resize the datafile to fix this corruption issue. Run this command as sysdba,

alter database datafile '/DBF_LOC/system09.dbf' resize 1000M;

Reference
=======
1. Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.1.1) for Linux x86-64 [ID 761566.1]
2. More than 20000 Blocks Corrupted in system09.dbf After Fresh Installation of E-Business Suite R12.1.1 on Linux x86_64 Platform [ID 840963.1]