How to recover UNDO tablespace with NO backup

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.
SQL>startup
——————–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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
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.
Thats it…
Advertisements

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s