{"id":917,"date":"2008-10-22T13:16:22","date_gmt":"2008-10-22T17:16:22","guid":{"rendered":"http:\/\/onlineappsdba.com\/index.php\/2008\/10\/22\/recover-an-accidentally-deleted-file-when-the-database-is-still-open\/"},"modified":"2008-10-22T13:20:05","modified_gmt":"2008-10-22T17:20:05","slug":"recover-an-accidentally-deleted-file-when-the-database-is-still-open","status":"publish","type":"post","link":"https:\/\/onlineappsdba.com\/index.php\/2008\/10\/22\/recover-an-accidentally-deleted-file-when-the-database-is-still-open\/","title":{"rendered":"Recover an accidentally deleted file when the database is still open."},"content":{"rendered":"

On Unix\/Linux, when a file is deleted, but a process still has the file open, the file is still there in the filesystem, and only the inode is removed.
\nBut the process can continue to use its file handle, and the file can also be accessible under \/proc\/<pid>\/fd .<\/font><\/p>\n

In the following example, we use that behavior to recover a lost datafile after is has been dropped from the os (with rm) but the datafile is still open by the background processes.<\/font><\/p>\n

First, we create a tablespace, and populate a table in it.<\/font><\/p>\n

SQL> REM we create a tablespace: <\/strong>
\nSQL> create tablespace TEST_RM datafile ‘\/var\/tmp\/test_rm.dbf’ size 10M;
\nTablespace created.<\/font><\/p>\n

SQL> REM we create a table in it: <\/strong>
\nSQL> create table FRANCK tablespace test_rm as select * from dba_objects;
\nTable created.<\/font><\/p>\n

SQL> REM we check that table data is accessible: <\/strong>
\nSQL> select count(*) from FRANCK;
\nCOUNT(*)
\n———-
\n12708<\/font><\/p>\n

SQL> exit
\nDisconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 – Production
\n<\/font><\/p>\n

Then, we drop the datafile from unix prompt.<\/font><\/p>\n

here is the datafile <\/strong>
\nls -l \/var\/tmp\/test_rm.dbf
\n-rw-r—– 1 oracle dba 10493952 Mar 26 14:25 \/var\/tmp\/test_rm.dbf<\/font><\/p>\n

we ‘accidently’ drop the datafile <\/strong>
\nrm \/var\/tmp\/test_rm.dbf
\nls -l \/var\/tmp\/test_rm.dbf
\nls: \/var\/tmp\/test_rm.dbf: no such file or directory
\n<\/font><\/p>\n


\nHere the datafile is lost.
\nNow we connect again.<\/font>
\n<\/strong><\/font><\/p>\n

sqlplus \/ as sysdba<\/font><\/p>\n

Connected to:
\nOracle Database 10g Express Edition Release 10.2.0.1.0 – Production<\/font><\/p>\n

SQL> REM and we check if table data is accessible: <\/strong>
\nSQL> select count(*) from FRANCK;<\/font><\/p>\n

select * from franck
\n*
\nERROR at line 1:
\nORA-01116: error in opening database file 5
\nORA-01110: data file 5: ‘\/var\/tmp\/test_rm.dbf’
\nORA-27041: unable to open file
\nLinux Error: 2: No such file or directory
\nAdditional information: 3
\n<\/font><\/p>\n

The datafile is lost and data is not accessible.<\/font><\/p>\n

However, the datafile should still have an open file descriptor by an oracle background process
\n<\/font><\/p>\n

we check the dbwriter pid: <\/strong>
\nps -edf | grep dbw
\noracle 2661<\/strong> 1 0 Mar25 ? 00:00:06 xe_dbw0<\/strong>_XE
\noracle 7044 7037 0 14:40 pts\/1 00:00:00 \/bin\/bash -c ps -edf | grep dbw
\noracle 7046 7044 0 14:40 pts\/1 00:00:00 grep dbw<\/font><\/p>\n

and we check its opened file descriptors for our file: <\/strong>
\nls -l \/proc\/2661<\/strong>\/fd | grep test_rm
\nlrwx—— 1 oracle dba 64 Mar 26 14:02 66<\/strong> -> \/var\/tmp\/test_rm.dbf (deleted)<\/font><\/p>\n

here it is: <\/strong>
\nls -l \/proc\/2661\/fd\/66<\/strong>
\nlrwx—— 1 oracle dba 64 Mar 26 14:02 \/proc\/2661<\/strong>\/fd\/66<\/strong> -> \/var\/tmp\/test_rm.dbf (deleted)
\n<\/font><\/p>\n

In some other unix, lsof may be needed to map the file descriptor with the deleted file name<\/font><\/p>\n

first we set a symbolic link so that oracle can see it as it was before the delete:<\/strong>
\nln -s \/proc\/2661\/fd\/66 \/var\/tmp\/test_rm.dbf
\n<\/font><\/p>\n

here data is accessible, but that will be lost if dbwriter closes it file handle (i.e if the database is closed)<\/font><\/p>\n

However we can now set the tablespace read only so that it is checkpointed, and no writes occurs on it.<\/font><\/p>\n

SQL> alter tablespace TEST_RM read only;
\nTablespace altered.
\n<\/font><\/p>\n

We can now copy the file safely.<\/font><\/p>\n

then we drop the symbolic link: <\/strong>
\nrm \/var\/tmp\/test_rm.dbf
\nls -l \/var\/tmp\/test_rm.dbf
\n<\/font><\/p>\n

ls: \/var\/tmp\/test_rm.dbf: No such file or directory
\n<\/font><\/p>\n

and we can now copy the file <\/strong>
\ncp -p \/proc\/2661\/fd\/66 \/var\/tmp\/test_rm.dbf
\nls -l \/var\/tmp\/test_rm.dbf
\n<\/font><\/p>\n

-rw-r—– 1 oracle dba 10493952 Mar 26 14:54 \/var\/tmp\/test_rm.dbf
\n<\/font><\/p>\n

And datafile is now available again.<\/font><\/p>\n

SQL> REM we have it back, lets put the tablespace back in read\/write <\/strong>
\nSQL> alter tablespace test_rm read write;
\nTablespace altered.<\/font><\/p>\n

SQL> REM and we check data is still there: <\/strong>
\nSQL> select count(*) from FRANCK;<\/font><\/p>\n

COUNT(*)
\n———-
\n12708
\n<\/font><\/p>\n

This is not to be used like that in production. This is unsupported and may behave differently on different unix\/linux or oracle versions.<\/font><\/p>\n","protected":false},"excerpt":{"rendered":"

On Unix\/Linux, when a file is deleted, but a process still has the file open, the file is still there […]<\/p>\n","protected":false},"author":115,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[424,134],"tags":[],"class_list":["post-917","post","type-post","status-publish","format-standard","hentry","category-database","category-unix"],"yoast_head":"\nRecover an accidentally deleted file when the database is still open. -<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/onlineappsdba.com\/index.php\/2008\/10\/22\/recover-an-accidentally-deleted-file-when-the-database-is-still-open\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Recover an accidentally deleted file when the database is still open. -\" \/>\n<meta property=\"og:description\" content=\"On Unix\/Linux, when a file is deleted, but a process still has the file open, the file is still there […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/onlineappsdba.com\/index.php\/2008\/10\/22\/recover-an-accidentally-deleted-file-when-the-database-is-still-open\/\" \/>\n<meta property=\"article:published_time\" content=\"2008-10-22T17:16:22+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2008-10-22T17:20:05+00:00\" \/>\n<meta name=\"author\" content=\"Masroof Ahmad\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Masroof Ahmad\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/onlineappsdba.com\/index.php\/2008\/10\/22\/recover-an-accidentally-deleted-file-when-the-database-is-still-open\/\",\"url\":\"https:\/\/onlineappsdba.com\/index.php\/2008\/10\/22\/recover-an-accidentally-deleted-file-when-the-database-is-still-open\/\",\"name\":\"Recover an accidentally deleted file when the database is still open. -\",\"isPartOf\":{\"@id\":\"https:\/\/onlineappsdba.com\/#website\"},\"datePublished\":\"2008-10-22T17:16:22+00:00\",\"dateModified\":\"2008-10-22T17:20:05+00:00\",\"author\":{\"@id\":\"https:\/\/onlineappsdba.com\/#\/schema\/person\/909a876ed58d400faf82caf81d61bfdb\"},\"breadcrumb\":{\"@id\":\"https:\/\/onlineappsdba.com\/index.php\/2008\/10\/22\/recover-an-accidentally-deleted-file-when-the-database-is-still-open\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/onlineappsdba.com\/index.php\/2008\/10\/22\/recover-an-accidentally-deleted-file-when-the-database-is-still-open\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/onlineappsdba.com\/index.php\/2008\/10\/22\/recover-an-accidentally-deleted-file-when-the-database-is-still-open\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/onlineappsdba.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Recover an accidentally deleted file when the database is still open.\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/onlineappsdba.com\/#website\",\"url\":\"https:\/\/onlineappsdba.com\/\",\"name\":\"\",\"description\":\"Oracle Implementation & Training Experts\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/onlineappsdba.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/onlineappsdba.com\/#\/schema\/person\/909a876ed58d400faf82caf81d61bfdb\",\"name\":\"Masroof Ahmad\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/onlineappsdba.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/10f9db7bdbbd7f9ccfbe9b2d208e5978fc28315e9c704383e639a926ea0fce5f?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/10f9db7bdbbd7f9ccfbe9b2d208e5978fc28315e9c704383e639a926ea0fce5f?s=96&d=mm&r=g\",\"caption\":\"Masroof Ahmad\"},\"url\":\"https:\/\/onlineappsdba.com\/index.php\/author\/masroof\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Recover an accidentally deleted file when the database is still open. -","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/onlineappsdba.com\/index.php\/2008\/10\/22\/recover-an-accidentally-deleted-file-when-the-database-is-still-open\/","og_locale":"en_US","og_type":"article","og_title":"Recover an accidentally deleted file when the database is still open. -","og_description":"On Unix\/Linux, when a file is deleted, but a process still has the file open, the file is still there […]","og_url":"https:\/\/onlineappsdba.com\/index.php\/2008\/10\/22\/recover-an-accidentally-deleted-file-when-the-database-is-still-open\/","article_published_time":"2008-10-22T17:16:22+00:00","article_modified_time":"2008-10-22T17:20:05+00:00","author":"Masroof Ahmad","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Masroof Ahmad","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/onlineappsdba.com\/index.php\/2008\/10\/22\/recover-an-accidentally-deleted-file-when-the-database-is-still-open\/","url":"https:\/\/onlineappsdba.com\/index.php\/2008\/10\/22\/recover-an-accidentally-deleted-file-when-the-database-is-still-open\/","name":"Recover an accidentally deleted file when the database is still open. -","isPartOf":{"@id":"https:\/\/onlineappsdba.com\/#website"},"datePublished":"2008-10-22T17:16:22+00:00","dateModified":"2008-10-22T17:20:05+00:00","author":{"@id":"https:\/\/onlineappsdba.com\/#\/schema\/person\/909a876ed58d400faf82caf81d61bfdb"},"breadcrumb":{"@id":"https:\/\/onlineappsdba.com\/index.php\/2008\/10\/22\/recover-an-accidentally-deleted-file-when-the-database-is-still-open\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/onlineappsdba.com\/index.php\/2008\/10\/22\/recover-an-accidentally-deleted-file-when-the-database-is-still-open\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/onlineappsdba.com\/index.php\/2008\/10\/22\/recover-an-accidentally-deleted-file-when-the-database-is-still-open\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/onlineappsdba.com\/"},{"@type":"ListItem","position":2,"name":"Recover an accidentally deleted file when the database is still open."}]},{"@type":"WebSite","@id":"https:\/\/onlineappsdba.com\/#website","url":"https:\/\/onlineappsdba.com\/","name":"","description":"Oracle Implementation & Training Experts","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/onlineappsdba.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/onlineappsdba.com\/#\/schema\/person\/909a876ed58d400faf82caf81d61bfdb","name":"Masroof Ahmad","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/onlineappsdba.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/10f9db7bdbbd7f9ccfbe9b2d208e5978fc28315e9c704383e639a926ea0fce5f?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/10f9db7bdbbd7f9ccfbe9b2d208e5978fc28315e9c704383e639a926ea0fce5f?s=96&d=mm&r=g","caption":"Masroof Ahmad"},"url":"https:\/\/onlineappsdba.com\/index.php\/author\/masroof\/"}]}},"_links":{"self":[{"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/posts\/917","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/users\/115"}],"replies":[{"embeddable":true,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/comments?post=917"}],"version-history":[{"count":0,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/posts\/917\/revisions"}],"wp:attachment":[{"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/media?parent=917"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/categories?post=917"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/tags?post=917"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}