Monday, June 6, 2011

How to recover Database when accidentally deleted datafile without backup

How to recover Database when accidentally deleted datafile without backup:
------------------------------------------------------------------------------

If you have accidentally deleted a datafile from a physical storage without backup you can recover database by dropping that particular datafile and Tablespace as follows,However if that datafile is very much crucial you can try the below link to hit and try:

http://gmdba.blogspot.com/2009/12/oracle-recover-deleted-datafile-enen.html

Step 1:Bring the tablespace offline so that you can shutdown the database.
-------

SQL>alter tablespace test11 offline immediate;

OR

$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jun 7 10:26:54 2011

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

connected to target database: ORCL (DBID=1248484971)

RMAN> run
{
sql 'alter tablespace test11 offline immediate';
}

using target database control file instead of recovery catalog
sql statement: alter tablespace test11 offline immediate


SQL> shut immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.


Step 2: In the mount state drop the datafile and than Drop the tablespace including contents.
-------

SQL> startup mount
ORACLE instance started.

Total System Global Area 764157952 bytes
Fixed Size 2217184 bytes
Variable Size 335547168 bytes
Database Buffers 423624704 bytes
Redo Buffers 2768896 bytes
Database mounted.
SQL> ALTER DATABASE DATAFILE '/u02/SCRIPTS/test11_data_01.dbf' OFFLINE DROP;

Database altered.


SQL> select file_name||' '||tablespace_name from dba_data_files;

FILE_NAME||''||TABLESPACE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/users01.dbf USERS
/u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/orcl/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
/u01/app/oracle/oradata/orcl/example01.dbf EXAMPLE
/u01/app/oracle/oradata/orcl/apex01.dbf APEX
/u02/SCRIPTS/test11_data_01.dbf TEST11

7 rows selected.

SQL> DROP TABLESPACE TEST11 INCLUDING CONTENTS;

Tablespace dropped.

SQL> select file_name||' '||tablespace_name from dba_data_files;

FILE_NAME||''||TABLESPACE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/users01.dbf USERS
/u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/orcl/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
/u01/app/oracle/oradata/orcl/example01.dbf EXAMPLE
/u01/app/oracle/oradata/orcl/apex01.dbf APEX


Note 1: If the datafile is drop accidentally or taken offline than RMAN we cannot take full database rman backup,As RMAN will be searching for that
file for taking physical backup.
Note 2: I tried this experiment in my competancy server,It is always advisable to have one backup for complete recovery and to avoid data loss and never try such experiment in any development or real time systems.

Hope it helps.


Best regards,

Rafi.

No comments:

Post a Comment