How to recover UNDO tablespace with NO backup

I happen to see an email from the requester asking how to recover lost UNDO tablespace with NO backups.
I remember somewhere in my archives i should be having a note for this scenario. Yes i did have that note, however i wanted to simulate it on my LOCAL non-production database (on my laptop). Here are the test results:

 

C:\Users\Raheel>sqlplus “/as sysdba”

SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 3 19:00:43 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$datafile;

NAME
---------------------------------------------------------------------
C:\APP\ORACLE11GR2\ORADATA\ORCL\DATAFILE\O1_MF_SYSTEM_7OS7JZGJ_.DBF
C:\APP\ORACLE11GR2\ORADATA\ORCL\DATAFILE\O1_MF_SYSAUX_7OS7JZJL_.DBF
C:\APP\ORACLE11GR2\ORADATA\ORCL\DATAFILE\O1_MF_UNDOTBS1_7OS7JZJV_.DBF  --> We could see UNOD tbs here
C:\APP\ORACLE11GR2\ORADATA\ORCL\DATAFILE\O1_MF_USERS_7OS7JZMW_.DBF
C:\APP\ORACLE11GR2\ORADATA\ORCL\DATAFILE\O1_MF_EXAMPLE_7OS7MVRD_.DBF

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

Renamed O1_MF_UNDOTBS1_7OS7JZJV_.DBF to O1_MF_UNDOTBS1_7OS7JZJV_.DBF.OLD (on Unix use ‘mv’ command and on Windows right click and rename)

C:\Users\Raheel>sqlplus “/as sysdba”

SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 3 19:06:54 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 417546240 bytes
Fixed Size 2176328 bytes
Variable Size 171969208 bytes
Database Buffers 234881024 bytes
Redo Buffers 8519680 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3:
'C:\APP\ORACLE11GR2\ORADATA\ORCL\DATAFILE\O1_MF_UNDOTBS1_7OS7JZJV_.DBF'

SQL> sho parameter undo
NAME TYPE VALUE
--------------- ------ -------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1

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

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

If your database is using PFILE and NOT spfile then edit init.ora as undo_management=manual and start the database using pfile as below

SQL> startup pfile='C:\app\oracle11gR2\product\11.2.0\dbhome_1\database\INITorcl.ora'
ORACLE instance started.

Total System Global Area 417546240 bytes
Fixed Size 2176328 bytes
Variable Size 171969208 bytes
Database Buffers 234881024 bytes
Redo Buffers 8519680 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3:
'C:\APP\ORACLE11GR2\ORADATA\ORCL\DATAFILE\O1_MF_UNDOTBS1_7OS7JZJV_.DBF'

I’m using SPFILE here

SQL> startup
ORACLE instance started.

Total System Global Area 417546240 bytes
Fixed Size 2176328 bytes
Variable Size 171969208 bytes
Database Buffers 234881024 bytes
Redo Buffers 8519680 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3:
'C:\APP\ORACLE11GR2\ORADATA\ORCL\DATAFILE\O1_MF_UNDOTBS1_7OS7JZJV_.DBF'

SQL> sho parameter undo

NAME TYPE VALUE
--------------- ------ --------
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string UNDOTBS1

This is to confirm no backups available


C:\Users\Raheel>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Apr 3 19:50:36 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1305446457, not open)

RMAN> restore tablespace undotbs1;

Starting restore at 03-APR-12
 using target database control file instead of recovery catalog
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=63 device type=DISK

RMAN-00571: ===========================================================
 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
 RMAN-00571: ===========================================================
 RMAN-03002: failure of restore command at 04/03/2012 19:50:46
 RMAN-06026: some targets not found - aborting restore
 RMAN-06023: no backup or copy of datafile 3 found to restore

RMAN> exit
SQL> alter database datafile 'C:\APP\ORACLE11GR2\ORADATA\ORCL\DATAFILE\O1_MF_UNDOTBS1_7OS7JZJV_.DBF' offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> select name from v$tablespace;

NAME
 ------------------------------
 SYSTEM
 SYSAUX
 UNDOTBS1
 USERS
 TEMP
 EXAMPLE

6 rows selected.

SQL> select name from v$datafile;

NAME
 --------------------------------------------------------------------------------
 C:\APP\ORACLE11GR2\ORADATA\ORCL\DATAFILE\O1_MF_SYSTEM_7OS7JZGJ_.DBF
 C:\APP\ORACLE11GR2\ORADATA\ORCL\DATAFILE\O1_MF_SYSAUX_7OS7JZJL_.DBF
 C:\APP\ORACLE11GR2\ORADATA\ORCL\DATAFILE\O1_MF_UNDOTBS1_7OS7JZJV_.DBF -- we could see undo tbs here, lets drop it
 C:\APP\ORACLE11GR2\ORADATA\ORCL\DATAFILE\O1_MF_USERS_7OS7JZMW_.DBF
 C:\APP\ORACLE11GR2\ORADATA\ORCL\DATAFILE\O1_MF_EXAMPLE_7OS7MVRD_.DBF

SQL> drop tablespace undotbs1;

Tablespace dropped.

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
USERS
TEMP
EXAMPLE

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
C:\APP\ORACLE11GR2\ORADATA\ORCL\DATAFILE\O1_MF_SYSTEM_7OS7JZGJ_.DBF
C:\APP\ORACLE11GR2\ORADATA\ORCL\DATAFILE\O1_MF_SYSAUX_7OS7JZJL_.DBF
C:\APP\ORACLE11GR2\ORADATA\ORCL\DATAFILE\O1_MF_USERS_7OS7JZMW_.DBF
C:\APP\ORACLE11GR2\ORADATA\ORCL\DATAFILE\O1_MF_EXAMPLE_7OS7MVRD_.DBF

SQL> create UNDO tablespace undotbs1 datafile 'C:\APP\ORACLE11GR2\ORADATA\ORCL\DATAFILE\O1_MF_UNDOTBS1_new.dbf' size 25m autoextend on next 1m maxsize 50m;

Tablespace created.

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

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

If you are using PFILE instead of SPFILE then modify undo_management=auto in init.ora and startup pfile='<dir_loc>/init.ora’
Since i’m using spfile file i havent gave pfile option with startup

C:\Users\Raheel>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 4 01:53:56 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to an idle instance.


SQL> startup
 ORACLE instance started.

Total System Global Area 417546240 bytes
 Fixed Size 2176328 bytes
 Variable Size 171969208 bytes
 Database Buffers 234881024 bytes
 Redo Buffers 8519680 bytes
 Database mounted.
 Database opened.

SQL> sho parameter undo

NAME TYPE VALUE
 -------------- ------- -----------
 undo_management string AUTO
 undo_retention integer 900
 undo_tablespace string UNDOTBS1

SQL> select name from v$datafile;

NAME
 -------------------------------------------------------------------
 C:\APP\ORACLE11GR2\ORADATA\ORCL\DATAFILE\O1_MF_SYSTEM_7OS7JZGJ_.DBF
 C:\APP\ORACLE11GR2\ORADATA\ORCL\DATAFILE\O1_MF_SYSAUX_7OS7JZJL_.DBF
 C:\APP\ORACLE11GR2\ORADATA\ORCL\DATAFILE\O1_MF_UNDOTBS1_NEW.DBF ---- this is our new UNDO tbs
 C:\APP\ORACLE11GR2\ORADATA\ORCL\DATAFILE\O1_MF_USERS_7OS7JZMW_.DBF
 C:\APP\ORACLE11GR2\ORADATA\ORCL\DATAFILE\O1_MF_EXAMPLE_7OS7MVRD_.DBF

SQL> select name from v$tablespace;

NAME
 ------------------------------
 SYSTEM
 SYSAUX
 UNDOTBS1
 USERS
 TEMP
 EXAMPLE

6 rows selected.

Cheers!

Raheel

Advertisements

About Raheel Syed

Oracle DBA
This entry was posted in Undo and tagged , , , , . Bookmark the permalink.

5 Responses to How to recover UNDO tablespace with NO backup

  1. Ahmed says:

    Good Experience….!!! Thanks for sharing ….!!!!

    Ahmed.

  2. Abdeslam M says:

    thanks a lot for time and conseration;

    It is working also for linux..

    Abdeslam …

  3. anil kumar says:

    excellent blog sir ………………..very very helpfull

  4. mrashed82 says:

    Reblogged this on Oracle in Action and commented:
    How to recover UNDO tablespace with NO backup

  5. Kalki says:

    You Saved me

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