I am an IT professional with 10 years of professional experience,I have good proficiency on Oracle technologies, and at last 2 years of my career to study Real Application Clusters,data guard and participate actively on Oracle community ,If you want to hire me on Contract or to quote on project basis contact me at khurrampc@hotmail.com.

Thursday, February 7, 2008

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,otherwise in noarchivelog mode you may lose some data.Every steps is self explainatory so i feel need'nt to add some textual description during demo.
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
  2  /

NAME
---------
ORCL



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

Tablespace created.

SQL> set linesize 1000
SQL> select * from v$tablespace
  2  /

       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
  2  /

User altered.

SQL> conn scott/tiger@orcl
Connected.

SQL> create table my_table as select * from all_objects
  2  /

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.1.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.1.0\oradata\orcl>del my_datafile.dbf

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       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.1.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
  2  /

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

SQL>alter database create datafile 
  2  'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\MY_DATAFILE.DBF' as
  3  '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
  2  /

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)

SQL> 

9 comments:

Vikas said...

Khurram bhaijaan
thanks for the help
you help a lot by this

thanks again

Jose Manuel Perdices said...

his solution is applicated too at a datafile from tablespaces system ??

Alexis said...

In the world there is one good tool-dbf repair.It helped me many times,tool has free status,moreover software can too recovering data from damaged files of the *.dbf format (dBase IV, Clipper, FoxPro and others).

Khurram Siddiqui said...

Alexis

Thanks for sharing that knowledge,have you ever used this tool with oracle dbf damaged files?

Anonymous said...

Khurram,
is your database in archivelog mode? if it is - then this "your method" is documented in Oracle manuals.
if it isn't - have you tried to switch logfiles N+1 times (where N is the number of redo logs you configured) and then try "your method"?

Khurram Siddiqui said...

At which step you are referring to switch logfile?Yes my database in archivelog mode.

Alex said...

I have heard about many applications in like sphere. But some days ago I was in different situation and no one of it couldn't help me. How fortunatel that I called a friend up and he advised me - recover foxpro. It performed my trouble quite quickly and absolutely without money. Moreover I recommended it for my friends and they thanked me a lot.

Yaseen AR said...

Hi,

If my Table stored in My_datafile
If i del & creating the datafile with existing datafile ref.

How the Data will copy to My_datafile01.dbf .

I cant understand how it Works. Can you Please Explain me.

Apologise if my question is basically wrong.

Regards
Yaseen AR

vishal said...

GOT BORED BY SEARCHING FOR PHONE NUMBERS IN TELEPHONE DIRECTORY, NOW U DON’T WORRY FOR SEARCHING,WE MAKE YOUR JOB EASY FOR SEARCHING PHONE NUMBERS CLICKHERE AND LOGIN INTO OUR SITE AND ENJOY THE SEARCH

THANK YOU

Followers

About Me

My photo
Melbourne, Victoria, Australia