Recovery of a lost datafile without backup

Its the demo for recovery of a lost datafile without backup, but you should have database in archivelog in order to make complete recovery and also all the archive logs along with the current redo log should be available, otherwise in noarchivelog mode and archivelog missing you may lose data.

Every steps is self explainatory so i feel need'nt to add some textual description during demo.

sql> conn system/manager

SQL*Plus: Release 10.1.0.2.0 - Production on Fri Feb 8 10:35:24 2008
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select name from v$database;

NAME
---------
ORCL

SQL> create tablespace my_tablespace datafile 'C:\oracle\product\10.2.0\oradata\orcl\my_datafile.dbf' size 500M;

Tablespace created.

SQL> set linesize 1000
SQL> select * from v$tablespace ;

TS# NAME INC BIG FLA
---------- ------------------------------ --- --- ---
0 SYSTEM YES NO YES
1 UNDOTBS1 YES NO YES
2 SYSAUX YES NO YES
4 USERS YES NO YES
3 TEMP YES NO YES
6 EXAMPLE YES NO YES
9 MY_TABLESPACE YES NO YES

7 rows selected.

SQL> alter user scott default tablespace my_tablespace;

User altered.

SQL> conn scott/tiger@orcl
Connected.

SQL> create table my_table as select * from all_objects ;

Table created.

SQL> desc my_table

Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)


SQL> conn sys/sys@orcl as sysdba
Connected.

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\>cd C:\oracle\product\10.2.0\oradata\orcl

C:\oracle\product\10.1.0\oradata\orcl>dir *.dbf
Volume in drive C has no label.
Volume Serial Number is F49D-FF2B
Directory of C:\oracle\product\10.1.0\oradata\orcl

02/08/2008 10:41 AM 157,294,592 EXAMPLE01.DBF
02/08/2008 10:41 AM 524,296,192 MY_DATAFILE.DBF
02/08/2008 10:41 AM 283,123,712 SYSAUX01.DBF
02/08/2008 10:41 AM 482,353,152 SYSTEM01.DBF
02/07/2008 11:43 PM 104,865,792 TEMP01.DBF
02/08/2008 10:41 AM 31,465,472 UNDOTBS01.DBF
02/08/2008 10:41 AM 56,369,152 USERS01.DBF
7 File(s) 1,639,768,064 bytes
0 Dir(s) 63,114,174,464 bytes free

C:\oracle\product\10.2.0\oradata\orcl>del my_datafile.dbf

C:\oracle\product\10.2.0\oradata\orcl>dir *.dbf
Volume in drive C has no label.
Volume Serial Number is F49D-FF2B
Directory of C:\oracle\product\10.1.0\oradata\orcl

02/08/2008 10:41 AM 157,294,592 EXAMPLE01.DBF
02/08/2008 10:41 AM 283,123,712 SYSAUX01.DBF
02/08/2008 10:41 AM 482,353,152 SYSTEM01.DBF
02/07/2008 11:43 PM 104,865,792 TEMP01.DBF
02/08/2008 10:41 AM 31,465,472 UNDOTBS01.DBF
02/08/2008 10:41 AM 56,369,152 USERS01.DBF
6 File(s) 1,115,471,872 bytes
0 Dir(s) 63,638,204,416 bytes free

C:\oracle\product\10.2.0\oradata\orcl>

SQL> startup
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\MY_DATAFILE.DBF'


SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ------------------ ---------- ---------
6 ONLINE ONLINE FILE NOT FOUND 0

SQL>alter database create datafile
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\MY_DATAFILE.DBF' as
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\MY_DATAFILE01.DBF';

Database altered.

C:\oracle\product\10.1.0\oradata\orcl>dir my_*.dbf
Volume in drive C has no label.
Volume Serial Number is F49D-FF2B
Directory of C:\oracle\product\10.1.0\oradata\orcl
02/08/2008 10:53 AM 524,296,192 MY_DATAFILE01.DBF
1 File(s) 524,296,192 bytes
0 Dir(s) 63,110,365,184 bytes free

C:\oracle\product\10.1.0\oradata\orcl>

SQL> recover tablespace my_tablespace
Media recovery complete.

SQL> alter database open;

Database altered.

SQL> conn scott/tiger@orcl
Connected.
SQL> desc my_table
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)