Subscribe to Posts by Email

Subscriber Count

    696

Disclaimer

All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove. Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only. This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only. If you see any issues with Content and copy write issues, I am happy to remove if you notify me. Contact Geek DBA Team, via geeksinsights@gmail.com

Pages

Recover datafile that accidentally deleted

Thanks to Martin's blog, which show us the data file recovery when it accidentally dropped.
(apologies i lost the original link)

***** DO IT ON YOUR OWN RISK *****

Created tablespace and users and some tables.

	SQL> create tablespace test datafile '/oradata/TESTTest01.dbf' size 100m;
	Tablespace created.

	SQL> create user test identified by test default tablespace test;
	User created.

	SQL> grant connect,resource to test;
	Grant succeeded.

	SQL> grant dba to test;
	Grant succeeded.

	SQL> create table t1 as select * from dba_objects;
	Table created.

	SQL> create table t2 as select * from dba_users;
	Table created.

	SQL> conn test/test
	Connected

	SQL> select count(*) from t1;
	35000

	SQL> select count(*) from t2;
	46
	

Removed the datafile

	rm -f /oradata/TESTTest01.dbf

Datafile showing recover status as its got deleted

	SQL> Select file#,name,status from v$datafile;


	     FILE#	NAME				STATUS
	----------	-------------------------- 	--------------- 
		38	/oradata/TESTTest01.dbf		RECOVER
	

Check for the dbwr process ID, The process id for db writer process is 14906, where the dbwriter locks the file headers and useful for this diagnosis.

	
	ps -eaf | grep dbw*_
	oracle   14306     1  0 Apr03 ?        00:02:16 ora_dbw0_
	

using lsof, find which files the dbwr process accessing or its hanged on,

	[oracle@TESTRAC1]/users/oracle # lsof -p 14306

	COMMAND   PID   USER   FD   TYPE    DEVICE   SIZE/OFF    NODE NAME
	oracle  14306 oracle  cwd    DIR     253,7       4096  615765 /u01/app/oracle/ora112/dbs
	oracle  14306 oracle  rtd    DIR     104,2       4096       2 /
	oracle  14306 oracle  txt    REG     253,7  218550485 1130499 /u01/app/oracle/ora112/bin/oracle
	oracle  14306 oracle  DEL    REG      0,13            2228239 /SYSV48ff1ce4
	oracle  14306 oracle  mem    CHR       1,5               3894 /dev/zero
	oracle  14306 oracle  mem    REG     104,2      35688   83628 /usr/lib64/libnuma.so.1
	oracle  14306 oracle  mem    REG     104,2    1661454   16169 /lib64/libc-2.11.1.so
	oracle  14306 oracle  mem    REG     104,2     108213   16180 /lib64/libnsl-2.11.1.so
	oracle  14306 oracle  mem    REG     104,2     135646   16195 /lib64/libpthread-2.11.1.so
	oracle  14306 oracle  mem    REG     104,2     388274   16177 /lib64/libm-2.11.1.so
	oracle  14306 oracle  mem    REG     104,2      19114   16175 /lib64/libdl-2.11.1.so
	oracle  14306 oracle  mem    REG     104,2       5560   16214 /lib64/libaio.so.1.0.1
	oracle  14306 oracle  mem    REG     253,7     150599   84476 /u01/app/oracle/ora112/lib/libocrutl11.so
	oracle  14306 oracle  mem    REG     253,7    3295575   84394 /u01/app/oracle/ora112/lib/libocrb11.so
	oracle  14306 oracle  mem    REG     253,7    1559829   84362 /u01/app/oracle/ora112/lib/libocr11.so
	oracle  14306 oracle  mem    REG     253,7      12763   84499 /u01/app/oracle/ora112/lib/libskgxn2.so
	oracle  14306 oracle  mem    REG     253,7   16067428  402373 /u01/app/oracle/ora112/lib/libhasgen11.so
	oracle  14306 oracle  mem    REG     253,7     161828   84503 /u01/app/oracle/ora112/lib/libdbcfg11.so
	oracle  14306 oracle  mem    REG     253,7     216789   84310 /u01/app/oracle/ora112/lib/libclsra11.so
	oracle  14306 oracle  mem    REG     253,7    7925240   84384 /u01/app/oracle/ora112/lib/libnnz11.so
	oracle  14306 oracle  mem    REG     104,2      47206   16199 /lib64/librt-2.11.1.so
	oracle  14306 oracle  mem    REG     253,7     993144   84391 /u01/app/oracle/ora112/lib/libskgxp11.so
	oracle  14306 oracle  mem    REG     253,7     532417   84323 /u01/app/oracle/ora112/lib/libcell11.so
	oracle  14306 oracle  mem    REG     104,2     149797   16162 /lib64/ld-2.11.1.so
	oracle  14306 oracle  DEL    REG     253,6              73768 /var/run/nscd/dbQpkhsj
	oracle  14306 oracle  mem    REG     253,7      12315   84306 /u01/app/oracle/ora112/lib/libodm11.so
	oracle  14306 oracle    0r   CHR       1,3        0t0    3883 /dev/null
	oracle  14306 oracle    1w   CHR       1,3        0t0    3883 /dev/null
	oracle  14306 oracle    2w   CHR       1,3        0t0    3883 /dev/null
	oracle  14306 oracle    3r   CHR       1,3        0t0    3883 /dev/null
	oracle  14306 oracle    4r   CHR       1,3        0t0    3883 /dev/null
	oracle  14306 oracle    5r   CHR       1,3        0t0    3883 /dev/null
	oracle  14306 oracle    7r   CHR       1,3        0t0    3883 /dev/null
	oracle  14306 oracle    8r   CHR       1,3        0t0    3883 /dev/null
	oracle  14306 oracle    9r   CHR       1,3        0t0    3883 /dev/null
	oracle  14306 oracle   10r   CHR       1,5        0t0    3894 /dev/zero
	oracle  14306 oracle   11r   CHR       1,5        0t0    3894 /dev/zero
	oracle  14306 oracle   13r   REG     253,7    1092608  583904 /u01/app/oracle/ora112/rdbms/mesg/oraus.msb
	oracle  14306 oracle   14r   DIR       0,3          0  137759 /proc/14306/fd
	oracle  14306 oracle   15r   CHR       1,5        0t0    3894 /dev/zero
	oracle  14306 oracle   18r   REG     253,7    1092608  583904 /u01/app/oracle/ora112/rdbms/mesg/oraus.msb
	oracle  14306 oracle  256u   REG 199,17000   10108928     559 /oradata/TEST/Control1.ctl
	oracle  14306 oracle  257u   REG 199,17000   10108928     560 /oradata/TEST/Control2.ctl
	oracle  14306 oracle  258uW  REG 199,17000  419438592     138 /oradata/TEST/system.dbf
	oracle  14306 oracle  259uW  REG 199,17000  419438592     141 /oradata/TEST/sysaux.dbf
	oracle  14306 oracle  260uW  REG 199,17000 1992302592     142 /oradata/TEST/rollbacks.dbf
	oracle  14306 oracle  295uW  REG 199,17000 1677729792     143 /oradata/TEST/temp.dbf
	oracle  14306 oracle  296uW  REG 199,17000  104865792  260497 /oradata/TEST/Test01.dbf (deleted)
	

Did you observed?, the last line shows the file we just deleted. Lets check in the process for 296

	[oracle@TESTRAC1] cd /proc/14306/fd # 
	

Cat the process 296 to a orbitory file

	[oracle@TESTRAC1]/proc/14306/fd # cat 296 > /tmp/Test01.dbf
	

File got created in /tmp

	[oracle@TESTRAC1]/proc/14306/fd # ls -ltr /tmp/Test01.dbf
	-rw-rw-r-- 1 oracle oracle 104865792 2013-04-24 08:45 /tmp/Test01.dbf
	

Make the old file offline drop

	SQL> alter database datafile '/oradata/TEST/Test01.dbf' offline drop;
	Database altered.
	

Rename the old file name to new file (i.e in new location)

	SQL> alter database rename file '/oradata/TEST/Test01.dbf' to '/tmp/Test01.dbf';

	SQL> alter database datafile '/tmp/Test01.dbf' online;
	alter database datafile '/tmp/Test01.dbf' online
	*
	ERROR at line 1:
	ORA-01113: file 38 needs media recovery
	ORA-01110: data file 38: '/tmp/Test01.dbf'
	

Recover the datafile

	SQL> recover datafile '/tmp/Test01.dbf';
	Media recovery complete.
	

Online the datafile

	SQL> alter database datafile '/tmp/Test01.dbf' online;
	Database altered.
	

Check the status

	SQL> select file#,name,status from v$datafile;


	     FILE#	NAME				STATUS
	-----------	---------------------------	----------------------------------
		38	/tmp/Test01.dbf			ONLINE
	

Bingo your file is back, But wait, this may work only when your database is not bounced after the deletion of the file.

Hope this helps
-Thanks
Geek DBA

Comments are closed.