Information Technology Blog

If someone has deleted datafile,there is user errors or disk is crushed then there is necessary to restore a datafile from backup. If you restore any datafile then just copy it to the location in the parameter specified in the control_files. If you restore any control file then copy in the destination which spfile or pfile points in control_files parameter.
In the following section you can know what you do if you choose user managed recovery:

1) If all copies of the control file have lost and you have backup of the control file then simply copy the backup of control file to the location as located in the control_files parameter in initialization parameter file pfile/spfile. After do this you have to open your database with resetlogs option. And if you don't have backup of the control file then you have to create new control file.

2) If you lose one or more datafiles then copy the datafiles to the location where it was. You can copy it to another location and point the control file to the new location by ALTER DATABASE RENAME FILE ' old' TO 'New '.

3) If you have backup of archived log then you can copy it to the location and perform recovery from there. But if the deleted archived log that is needed for media recovery and you have no backup then you must perform incomplete recovery (UNTIL SCN) and open the database with RESETLOGS open.

Related Post:- Solution of Inconsistent Control File

Undo tablespace is used to store the post modification data of every transaction for rollback or read consistency or for recovery in case of abort. All system transactions will use the rollback segments created in the system tablespace. It cannot be used by other schema operations, they should use the undo tablespace. So an Undo tablespace is a must for any transaction to occur. So, recovery of undo tablespace is more important in case of undo tablespace lost. There are few steps to recover undo tablespace:

[oracle10@localhost ~]$export_SID=luck
[oracle10@localhost ~]$sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 30 09:11:10 2011

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

Connected to an idle instance.

SQL>startup
ORACLE instance started.

Total System Global Area   314572800 bytes
Fixed Size                            1219160 bytes
Variable Size                 96470440 bytes
Database Buffers        213909504 bytes
Redo Buffers               2973696 bytes
Database mounted.
ORA-01157 :cannot identify/lock datafile 2 - see DBWR trace file
ORA-01110 :datafile 2: '/opt/luck/undotbs.dbf'

we cannot open the database by taking the datafile offline but we need to use 'OFFLINE FOR DROP'.Datafile once marked offline for drop can never be brought online.

SQL>alter database datafile 2 offline for drop;
Database altered.

SQL>alter database open;
Database altered.

SQL>drop tablespace undotbs including contents and datafiles;
Tablespace dropped.

Now we need to create another undo tablespace for the transactions to use and once the new undo tablespace is created, shutdown the database and edit the init.ora file and change the parameter undo_tablespace=new undo_tablespace

SQL>create undo tablespace undotbs1 datafile '/opt/luck/undotbs1.dbf' size 20m autoextend on;
Tablespace created.

SQL>shutdown immediate;

In simple steps:
a) startup
b) error ora:01157
    error ora:01110
c) alter system set undo_management = manual scope=spfile
d) shutdown immediate
e) startup
f ) drop undo tablespace
g) Create UNDO Tablespace UNDOTBS datafile ...
h) alter system set undo_management = auto scope=spfile
i) Shutdown
j) Startup Database

Related Post:- Undo and Rollback segment