{"id":9183,"date":"2019-05-16T09:11:00","date_gmt":"2019-05-16T09:11:00","guid":{"rendered":"https:\/\/ittutorial.org\/?p=9183"},"modified":"2020-03-29T14:09:51","modified_gmt":"2020-03-29T14:09:51","slug":"restore-any-lost-datafile-with-flashback-database-technology-1","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/restore-any-lost-datafile-with-flashback-database-technology-1\/","title":{"rendered":"Restore Any Lost Datafile with Flashback Database Technology -1"},"content":{"rendered":"<p>Hi,<\/p>\n<p>I will explain\u00a0datafile restore with Flashback Database Technology in Oracle in this article.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"638\" height=\"479\" class=\"wp-image-9181\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/05\/https-salihdeveci-files-wordpress-com-2019-01-ba.jpeg\" alt=\"https:\/\/salihdeveci.files.wordpress.com\/2019\/01\/backup-and-recovery-in-oracle-32-638.jpg\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/05\/https-salihdeveci-files-wordpress-com-2019-01-ba.jpeg 638w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/05\/https-salihdeveci-files-wordpress-com-2019-01-ba-300x225.jpeg 300w\" sizes=\"auto, (max-width: 638px) 100vw, 638px\" \/><\/p>\n<p><!--more--><\/p>\n\n<p>&nbsp;<\/p>\n<p>Read previous article before this. If you don&#8217;t know What is the Flashback and its architecture.<\/p>\n<blockquote class=\"wp-embedded-content\" data-secret=\"qqz4Yy11z2\"><p><a href=\"https:\/\/ittutorial.org\/flashback-oracle-architecture-flashback-usage\/\">Oracle Flashback Architecture<\/a><\/p><\/blockquote>\n<p><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;Oracle Flashback Architecture&#8221; &#8212; IT Tutorial\" src=\"https:\/\/ittutorial.org\/flashback-oracle-architecture-flashback-usage\/embed\/#?secret=21YROMPl8x#?secret=qqz4Yy11z2\" data-secret=\"qqz4Yy11z2\" width=\"600\" height=\"338\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>You can restore any lost or dropped datafile with flashback and rman after a tablespace is dropped.<\/p>\n<p>&nbsp;<\/p>\n\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>1-)First of all, create a tablespace, user a table and insert sample data.<\/p>\n<p>&nbsp;<\/p>\n<pre>[oracle@MehmetSalih ~]$ sqlplus \/ as sysdba\r\n\r\nSQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 25 11:31:49 2015\r\n\r\nCopyright (c) 1982, 2013, Oracle.\u00a0 All rights reserved.\r\n\r\nConnected to:\r\n\r\nOracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production\r\n\r\nWith the Partitioning, OLAP, Data Mining and Real Application Testing options\r\n\r\n\r\n\r\nSQL&gt; create tablespace ts datafile '\/u01\/app\/oracle\/oradata\/deneme\/ts_01.dbf' size 100m;\r\n\r\nTablespace created.\r\n\r\n\r\n\r\nSQL&gt; create user ts identified by Deveci;\r\n\r\nUser created.\r\n\r\nSQL&gt; grant dba to ts;\r\n\r\nGrant succeeded.\r\n\r\n\r\n\r\nSQL&gt; alter user ts default tablespace ts;\r\n\r\nUser altered.\r\n\r\nSQL&gt; exit\r\n\r\nDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production\r\n\r\nWith the Partitioning, OLAP, Data Mining and Real Application Testing options\r\n\r\n\r\n\r\n\r\n\r\n[oracle@MehmetSalih ~]$ sqlplus ts\/Deveci\r\n\r\nSQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 25 11:33:13 2015\r\n\r\nCopyright (c) 1982, 2013, Oracle.\u00a0 All rights reserved.\r\n\r\nConnected to:\r\n\r\nOracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production\r\n\r\nWith the Partitioning, OLAP, Data Mining and Real Application Testing options\r\n\r\n\r\n\r\nSQL&gt; select name from v$datafile;\r\n\r\nNAME\r\n\r\n--------------------------------------------------------------------------------\r\n\r\n\/u01\/app\/oracle\/oradata\/deneme\/system01.dbf\r\n\r\n\/u01\/app\/oracle\/oradata\/deneme\/sysaux01.dbf\r\n\r\n\/u01\/app\/oracle\/oradata\/deneme\/undotbs01.dbf\r\n\r\n\/u01\/app\/oracle\/oradata\/deneme\/users01.dbf\r\n\r\n\/u01\/app\/oracle\/oradata\/deneme\/example01.dbf\r\n\r\n\/u01\/app\/oracle\/product\/11.2.0.4\/db\/dbs\/aydem_data.dbf\r\n\r\n\/u01\/app\/oracle\/product\/11.2.0.4\/db\/dbs\/UNNAMED00007\r\n\r\n\/u01\/app\/oracle\/oradata\/deneme\/tb_01.dbf\r\n\r\n\/u01\/app\/oracle\/oradata\/deneme\/ts_01.dbf\r\n\r\n9 rows selected.\r\n\r\n\r\n\r\n<span style=\"color: #ff0000;\">SQL&gt; create table ts(id number);<\/span>\r\n\r\n<span style=\"color: #ff0000;\">Table created.<\/span>\r\n\r\n<span style=\"color: #ff0000;\">SQL&gt; insert into ts(id) values(10);<\/span>\r\n\r\n<span style=\"color: #ff0000;\">1 row created.<\/span>\r\n\r\n<span style=\"color: #ff0000;\">SQL&gt; commit;<\/span>\r\n\r\n<span style=\"color: #ff0000;\">Commit complete.<\/span>\r\n\r\n<span style=\"color: #ff0000;\">SQL&gt; select * from ts;<\/span>\r\n\r\n<span style=\"color: #ff0000;\">ID<\/span>\r\n\r\n<span style=\"color: #ff0000;\">----------<\/span>\r\n\r\n<span style=\"color: #ff0000;\">10<\/span>\r\n\r\n<span style=\"color: #ff0000;\">SQL&gt; exit<\/span><\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>2-) Check and specify Current SCN, we will return back to this SCN.<\/p>\n<p>&nbsp;<\/p>\n<pre>[oracle@MehmetSalih ~]$ sqlplus \/ as sysdba\r\n\r\nSQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 25 11:34:04 2015\r\n\r\nCopyright (c) 1982, 2013, Oracle.\u00a0 All rights reserved.\r\n\r\nConnected to:\r\n\r\nOracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production\r\n\r\nWith the Partitioning, OLAP, Data Mining and Real Application Testing options\r\n\r\n\r\nSQL&gt; select current_scn from v$database;\r\n\r\nCURRENT_SCN\r\n\r\n-----------\r\n\r\n1859609\r\n\r\n\r\n<\/pre>\n\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>3-) Then drop tablespace and datafile for testing purposes.<\/p>\n<p>&nbsp;<\/p>\n<pre>SQL&gt; drop tablespace ts including contents and datafiles;\r\n\r\nTablespace dropped.<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>4-)\u00a0Now try to restore our datafile using flashback database.<\/p>\n<p>&nbsp;<\/p>\n<pre>SQL&gt; shutdown immediate;\r\nDatabase closed.\r\nDatabase dismounted.\r\nORACLE instance shut down.\r\n\r\n\r\nSQL&gt; startup mount;\r\nORACLE instance started.\r\nTotal System Global Area 1419685888 bytes\r\nFixed Size\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2253224 bytes\r\nVariable Size\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 855641688 bytes\r\nDatabase Buffers\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 553648128 bytes\r\nRedo Buffers\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 8142848 bytes\r\nDatabase mounted.<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>With the flashback database feature, we will return database to a previous time or to SCN.<\/p>\n<pre>SQL&gt; flashback database to scn 1859609;\r\nFlashback complete.<\/pre>\n<p>&nbsp;<\/p>\n<p>Now let&#8217;s go to open database with resetlogs option.<\/p>\n<pre>SQL&gt; alter database open resetlogs;\r\nDatabase altered.<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>Check all datafiles, our datafiles has been restored with unnamed name like following<\/p>\n<pre>SQL&gt; select name from v$datafile;\r\n\r\nNAME\r\n\r\n--------------------------------------------------------------------------------\r\n\r\n\/u01\/app\/oracle\/oradata\/deneme\/system01.dbf\r\n\r\n\/u01\/app\/oracle\/oradata\/deneme\/sysaux01.dbf\r\n\r\n\/u01\/app\/oracle\/oradata\/deneme\/undotbs01.dbf\r\n\r\n\/u01\/app\/oracle\/oradata\/deneme\/users01.dbf\r\n\r\n\/u01\/app\/oracle\/oradata\/deneme\/example01.dbf\r\n\r\n\/u01\/app\/oracle\/product\/11.2.0.4\/db\/dbs\/aydem_data.dbf\r\n\r\n\/u01\/app\/oracle\/product\/11.2.0.4\/db\/dbs\/UNNAMED00007\r\n\r\n\/u01\/app\/oracle\/oradata\/deneme\/tb_01.dbf\r\n\r\n<span style=\"color: #ff0000;\">\/u01\/app\/oracle\/product\/11.2.0.4\/db\/dbs\/UNNAMED00009\r\n<\/span>\r\n9 rows selected.<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>5-) Change datafile name\u00a0to his old name again because named of dropped datafile has changed.<\/p>\n<pre>SQL&gt; alter database create datafile '\/u01\/app\/oracle\/product\/11.2.0.4\/db\/dbs\/UNNAMED00009' as '\/u01\/app\/oracle\/oradata\/deneme\/ts_01.dbf';\r\n\r\nDatabase altered.<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>6-)\u00a0Datafile has been restored , but can not query the tables in this datafile.<\/p>\n<p>&nbsp;<\/p>\n<pre>SQL&gt; select * from ts.ts;\r\n\r\nselect * from ts.ts\r\n\r\n*\r\n\r\nERROR at line 1:\r\n\r\nORA-00376: file 9 cannot be read at this time\r\n\r\nORA-01110: data file 9: '\/u01\/app\/oracle\/oradata\/deneme\/ts_01.dbf'\r\n\r\n<\/pre>\n<p>&nbsp;<\/p>\n<pre>SQL&gt; select name from v$datafile;\r\n\r\nNAME\r\n\r\n--------------------------------------------------------------------------------\r\n\r\n\/u01\/app\/oracle\/oradata\/deneme\/system01.dbf\r\n\/u01\/app\/oracle\/oradata\/deneme\/sysaux01.dbf\r\n\/u01\/app\/oracle\/oradata\/deneme\/undotbs01.dbf\r\n\/u01\/app\/oracle\/oradata\/deneme\/users01.dbf\r\n\/u01\/app\/oracle\/oradata\/deneme\/example01.dbf\r\n\/u01\/app\/oracle\/product\/11.2.0.4\/db\/dbs\/aydem_data.dbf\r\n\/u01\/app\/oracle\/product\/11.2.0.4\/db\/dbs\/UNNAMED00007\r\n\/u01\/app\/oracle\/oradata\/deneme\/tb_01.dbf\r\n\/u01\/app\/oracle\/oradata\/deneme\/ts_01.dbf\r\n\r\n9 rows selected.<\/pre>\n<p>&nbsp;<\/p>\n<p>I will continue to explain restore datafile with flashback database feature in the next post.<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<h5><span style=\"color: #ff0000;\">Do you want to learn Oracle Database for Beginners, then read the following articles.<\/span><\/h5>\n<p><a href=\"https:\/\/ittutorial.org\/oracle-database-19c-tutorials-for-beginners\/\">https:\/\/ittutorial.org\/oracle-database-19c-tutorials-for-beginners\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Hi, I will explain\u00a0datafile restore with Flashback Database Technology in Oracle in this article.<\/p>\n","protected":false},"author":1,"featured_media":4549,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[5239,1994],"tags":[4315,4320,4317,2672,4301,485,2747,508,4300,4346,4348,4295,4306,638,4297,4322,4296,4310,4311,4316,4292,4299,4302,4298,2473,4323,4294,2671,2674,1031,4319,4318,1267,1268,4290,4347,4304,4305,1269,1270,4312,1271,4314,4303,1272,1345,2673,2686,1515,4308,4321,2620,4309,4313,4291,4307,4349,4293],"class_list":["post-9183","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-flashback","category-oracle","tag-alter-table-table_name-enable-row-movement","tag-as-of-scn","tag-as-of-timestamp","tag-database-tutorial","tag-db_flashback_retention_target","tag-db_recovery_file_dest","tag-db_recovery_file_dest_size","tag-deveci","tag-flashback-data-archive","tag-flashback-data-archive-in-oracle-database","tag-flashback-data-archive-oracle-database","tag-flashback-database","tag-flashback-database-oracle","tag-flashback-ne-ise-yarar","tag-flashback-query","tag-flashback-query-in-oracle-database","tag-flashback-table","tag-flashback-table-in-oracle-database","tag-flashback-table-oracle","tag-flashback-table-to-scn","tag-flashback-technology-oracle","tag-flashback-transaction-query","tag-flashback-usage-oracle","tag-flashback-version-query","tag-goldengate-tutorial","tag-how-to-query-past-in-oracle","tag-how-to-use-flashback","tag-it-tutorial","tag-linux-tutorial","tag-mehmet-salih-deveci","tag-oracle-as-of-scn","tag-oracle-as-of-timestamp","tag-oracle-egitimi","tag-oracle-flashback","tag-oracle-flashback-architecture","tag-oracle-flashback-data-archive","tag-oracle-flashback-database","tag-oracle-flashback-database-feature","tag-oracle-flashback-ne-icin-kullanilir","tag-oracle-flashback-nedir","tag-oracle-flashback-table","tag-oracle-flashback-teknolojisi","tag-oracle-flashback-tutorial","tag-oracle-flashback-usage","tag-oracle-flashback-veritabanini-ileriye-geriye-sarma","tag-oracle-support","tag-oracle-tutorial","tag-programming-language-tutorial","tag-salih-deveci","tag-scn-system-change-number","tag-show-parameter-undo_retention","tag-sql-server-tutorial","tag-system-change-number","tag-table-flashback-oracle","tag-what-is-oracle-flashback","tag-what-is-scn","tag-what-is-the-flashback-data-archive","tag-what-is-the-flashback-in-oracle"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/01\/backup-and-recovery-in-oracle-32-638.jpg","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/9183","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=9183"}],"version-history":[{"count":16,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/9183\/revisions"}],"predecessor-version":[{"id":12557,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/9183\/revisions\/12557"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/4549"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=9183"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=9183"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=9183"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}