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>
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.
Subscribe to:
Post Comments (Atom)
9 comments:
Khurram bhaijaan
thanks for the help
you help a lot by this
thanks again
his solution is applicated too at a datafile from tablespaces system ??
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).
Alexis
Thanks for sharing that knowledge,have you ever used this tool with oracle dbf damaged files?
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"?
At which step you are referring to switch logfile?Yes my database in archivelog mode.
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.
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
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
Post a Comment