{"id":9206,"date":"2019-05-16T09:15:36","date_gmt":"2019-05-16T09:15:36","guid":{"rendered":"https:\/\/ittutorial.org\/?p=9206"},"modified":"2020-03-29T14:08:39","modified_gmt":"2020-03-29T14:08:39","slug":"restore-any-lost-datafilewith-flashback-database-technology-2","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/restore-any-lost-datafilewith-flashback-database-technology-2\/","title":{"rendered":"Restore Any Lost Datafile with Flashback Database Technology -2"},"content":{"rendered":"<p>Hi,<\/p>\n<p>I will continue to explain\u00a0datafile restore with Flashback Database Technology in Oracle in this article.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"727\" height=\"491\" class=\"wp-image-9207\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/05\/https-salihdeveci-files-wordpress-com-2019-01-pr-2.jpeg\" alt=\"https:\/\/salihdeveci.files.wordpress.com\/2019\/01\/prsentation-oracle-database-11g-23-728.jpg\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/05\/https-salihdeveci-files-wordpress-com-2019-01-pr-2.jpeg 727w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/05\/https-salihdeveci-files-wordpress-com-2019-01-pr-2-300x203.jpeg 300w\" sizes=\"auto, (max-width: 727px) 100vw, 727px\" \/><\/p>\n<p><!--more--><\/p>\n\n<p>&nbsp;<\/p>\n<p>Read previous article before this.<\/p>\n<p>&nbsp;<\/p>\n<p>https:\/\/ittutorial.org\/restore-datafile-with-flashback-database-technology-1\/<\/p>\n<p>&nbsp;<\/p>\n<p>In the previous article we have restored tablespace and datafile with Flashback. But we have taken errors when query the data, and now let&#8217;s go to fix these errors.<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>7-)\u00a0Not lets go to change datafile\u00a0mode as offline and then online.<\/p>\n<p>&nbsp;<\/p>\n<pre>SQL&gt; select * from v$recover_file;\r\n\r\nFILE# ONLINE\u00a0 ONLINE_\r\n\r\n---------- ------- -------\r\n\r\nERROR\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHANGE#\r\n\r\n----------------------------------------------------------------- ----------\r\n\r\nTIME\r\n\r\n---------\r\n\r\n7 OFFLINE OFFLINE\r\n\r\nFILE NOT FOUND\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\r\n\r\n8 OFFLINE OFFLINE\r\n\r\nUNKNOWN ERROR\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01857557\r\n\r\n25-FEB-15\r\n\r\nFILE# ONLINE\u00a0 ONLINE_\r\n\r\n---------- ------- -------\r\n\r\nERROR\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHANGE#\r\n\r\n----------------------------------------------------------------- ----------\r\n\r\nTIME\r\n\r\n---------\r\n\r\n9 OFFLINE OFFLINE\r\n\r\nUNKNOWN ERROR\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1859232\r\n\r\n25-FEB-15<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n\n<p>&nbsp;<\/p>\n<p>Change tablespace mode as offline.<\/p>\n<p>&nbsp;<\/p>\n<pre>SQL&gt; alter tablespace ts offline immediate;\r\n\r\nTablespace altered.<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>Change tablespace mode as online.<\/p>\n<pre>SQL&gt; alter tablespace ts online;\r\n\r\nalter tablespace ts online\r\n\r\n*\r\n\r\nERROR at line 1:\r\n\r\nORA-01190: control file or data file 9 is from before the last RESETLOGS\r\n\r\nORA-01110: data file 9: '\/u01\/app\/oracle\/oradata\/deneme\/ts_01.dbf'\r\n\r\n\r\n<\/pre>\n<p>&nbsp;<\/p>\n<p>8- But since there is inconsistency in our datafile, we cannot get it online mode. For this we will get help from rman recovery advisor.<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>We will recover and open database on RMAN Data Recovery Advisor. With this tool, Firstly perform list failure, then Advise failure step and then perform Repair failure like following.<\/p>\n<p>&nbsp;<\/p>\n<pre>[oracle@MehmetSalih ~]$ rman target \/\r\n\r\nRecovery Manager: Release 11.2.0.4.0 - Production on Wed Feb 25 11:42:59 2015\r\n\r\nCopyright (c) 1982, 2011, Oracle and\/or its affiliates.\u00a0 All rights reserved.\r\n\r\nconnected to target database: DENEME (DBID=1985990806)\r\n\r\n\r\n<strong><span style=\"color: #ff0000;\">RMAN&gt; list failure;<\/span><\/strong>\r\n\r\nList of Database Failures\r\n\r\n=========================\r\n\r\nFailure ID Priority Status\u00a0\u00a0\u00a0 Time Detected Summary\r\n\r\n---------- -------- --------- ------------- -------\r\n\r\n348\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 HIGH\u00a0\u00a0\u00a0\u00a0 OPEN\u00a0\u00a0\u00a0\u00a0\u00a0 25-FEB-15\u00a0\u00a0\u00a0\u00a0 One or more non-system datafiles need media recovery\r\n\r\n452\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 HIGH\u00a0\u00a0\u00a0\u00a0 OPEN\u00a0\u00a0\u00a0\u00a0\u00a0 25-FEB-15\u00a0\u00a0\u00a0\u00a0 One or more non-system datafiles are offline\r\n\r\n427\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 HIGH\u00a0\u00a0\u00a0\u00a0 OPEN\u00a0\u00a0\u00a0\u00a0\u00a0 25-FEB-15\u00a0\u00a0\u00a0\u00a0 One or more non-system datafiles are missing\r\n\r\n\r\n\r\n\r\n\r\n<strong><span style=\"color: #ff0000;\">RMAN&gt; advise failure;<\/span><\/strong>\r\n\r\nList of Database Failures\r\n\r\n=========================\r\n\r\nFailure ID Priority Status\u00a0\u00a0\u00a0 Time Detected Summary\r\n\r\n---------- -------- --------- ------------- -------\r\n\r\n348\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 HIGH\u00a0\u00a0\u00a0\u00a0 OPEN\u00a0\u00a0\u00a0\u00a0\u00a0 25-FEB-15\u00a0\u00a0\u00a0\u00a0 One or more non-system datafiles need media recovery\r\n\r\n452\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 HIGH\u00a0\u00a0\u00a0\u00a0 OPEN\u00a0\u00a0\u00a0\u00a0\u00a0 25-FEB-15\u00a0\u00a0\u00a0\u00a0 One or more non-system datafiles are offline\r\n\r\n427\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 HIGH\u00a0\u00a0\u00a0\u00a0 OPEN\u00a0\u00a0\u00a0\u00a0\u00a0 25-FEB-15\u00a0\u00a0\u00a0\u00a0 One or more non-system datafiles are missing\r\n\r\nanalyzing automatic repair options; this may take some time\r\n\r\nallocated channel: ORA_DISK_1\r\n\r\nchannel ORA_DISK_1: SID=36 device type=DISK\r\n\r\nanalyzing automatic repair options complete\r\n\r\nNot all specified failures can currently be repaired.\r\n\r\nThe following failures must be repaired before advise for others can be given.\r\n\r\nFailure ID Priority Status\u00a0\u00a0\u00a0 Time Detected Summary\r\n\r\n---------- -------- --------- ------------- -------\r\n\r\n348\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 HIGH\u00a0\u00a0\u00a0\u00a0 OPEN\u00a0\u00a0\u00a0\u00a0\u00a0 25-FEB-15\u00a0\u00a0\u00a0\u00a0 One or more non-system datafiles need media recovery\r\n\r\n427\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 HIGH\u00a0\u00a0\u00a0\u00a0 OPEN\u00a0\u00a0\u00a0\u00a0\u00a0 25-FEB-15\u00a0\u00a0\u00a0\u00a0 One or more non-system datafiles are missing\r\n\r\nMandatory Manual Actions\r\n\r\n========================\r\n\r\nno manual actions available\r\n\r\nOptional Manual Actions\r\n\r\n=======================<\/pre>\n<ol>\n<li>\n<pre>If you restored the wrong version of data file \/u01\/app\/oracle\/oradata\/deneme\/tb_01.dbf, then replace it with the correct one<\/pre>\n<\/li>\n<li>\n<pre>If you restored the wrong version of data file \/u01\/app\/oracle\/oradata\/deneme\/ts_01.dbf, then replace it with the correct one<\/pre>\n<\/li>\n<li>\n<pre>If file \/u01\/app\/oracle\/product\/11.2.0.4\/db\/dbs\/UNNAMED00007 was unintentionally renamed or moved, restore it<\/pre>\n<\/li>\n<\/ol>\n<pre>Automated Repair Options\r\n\r\n========================\r\n\r\nOption Repair Description\r\n\r\n------ ------------------\r\n\r\n1\u00a0\u00a0\u00a0\u00a0\u00a0 Restore and recover datafile 7; Recover datafile 8; Recover datafile 9\r\n\r\nStrategy: The repair includes complete media recovery with no data loss\r\n\r\nRepair script: \/u01\/app\/oracle\/diag\/rdbms\/deneme\/deneme\/hm\/reco_3656126672.hm\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n<strong><span style=\"color: #ff0000;\">RMAN&gt; repair failure;<\/span><\/strong>\r\n\r\nStrategy: The repair includes complete media recovery with no data loss\r\n\r\nRepair script: \/u01\/app\/oracle\/diag\/rdbms\/deneme\/deneme\/hm\/reco_3656126672.hm\r\n\r\ncontents of repair script:\r\n\r\n# restore and recover datafile\r\n\r\nrestore datafile 7;\r\n\r\nrecover datafile 7;\r\n\r\n# recover datafile\r\n\r\nrecover datafile 8, 9;\r\n\r\nDo you really want to execute the above repair (enter YES or NO)? YES\r\n\r\nexecuting repair script\r\n\r\nStarting restore at 25-FEB-15\r\n\r\nusing channel ORA_DISK_1\r\n\r\ncreating datafile file number=7 name=\/u01\/app\/oracle\/product\/11.2.0.4\/db\/dbs\/UNNAMED00007\r\n\r\nrestore not done; all files read only, offline, or already restored\r\n\r\nFinished restore at 25-FEB-15\r\n\r\nStarting recover at 25-FEB-15\r\n\r\nusing channel ORA_DISK_1\r\n\r\nstarting media recovery\r\n\r\narchived log for thread 1 with sequence 2 is already on disk as file \/u01\/app\/oracle\/fast_recovery_area\/DENEME\/archivelog\/2015_02_25\/o1_mf_1_2_bgv509dy_.arc\r\n\r\narchived log for thread 1 with sequence 1 is already on disk as file \/u01\/app\/oracle\/fast_recovery_area\/DENEME\/archivelog\/2015_02_25\/o1_mf_1_1_bgv5d0sy_.arc\r\n\r\narchived log file name=\/u01\/app\/oracle\/fast_recovery_area\/DENEME\/archivelog\/2015_02_25\/o1_mf_1_2_bgv509dy_.arc thread=1 sequence=2\r\n\r\narchived log file name=\/u01\/app\/oracle\/fast_recovery_area\/DENEME\/archivelog\/2015_02_25\/o1_mf_1_1_bgv5d0sy_.arc thread=1 sequence=1\r\n\r\narchived log file name=\/u01\/app\/oracle\/fast_recovery_area\/DENEME\/archivelog\/2015_02_25\/o1_mf_1_1_bgv5vhq4_.arc thread=1 sequence=1\r\n\r\nmedia recovery complete, elapsed time: 00:00:00\r\n\r\nFinished recover at 25-FEB-15\r\n\r\nStarting recover at 25-FEB-15\r\n\r\nusing channel ORA_DISK_1\r\n\r\nstarting media recovery\r\n\r\narchived log for thread 1 with sequence 1 is already on disk as file \/u01\/app\/oracle\/fast_recovery_area\/DENEME\/archivelog\/2015_02_25\/o1_mf_1_1_bgv5d0sy_.arc\r\n\r\narchived log file name=\/u01\/app\/oracle\/fast_recovery_area\/DENEME\/archivelog\/2015_02_25\/o1_mf_1_1_bgv5d0sy_.arc thread=1 sequence=1\r\n\r\narchived log file name=\/u01\/app\/oracle\/fast_recovery_area\/DENEME\/archivelog\/2015_02_25\/o1_mf_1_1_bgv5vhq4_.arc thread=1 sequence=1\r\n\r\n<span style=\"color: #ff0000;\"><strong>media recovery complete, elapsed time: 00:00:01<\/strong><\/span>\r\n\r\n<span style=\"color: #ff0000;\"><strong>Finished recover at 25-FEB-15<\/strong><\/span>\r\n\r\n<span style=\"color: #ff0000;\"><strong>repair failure complete<\/strong><\/span>\r\n\r\n<span style=\"color: #ff0000;\"><strong>RMAN&gt; exit<\/strong><\/span><\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>9-) Now problem has been solved, we can query related table and data like following. We have been recovered lost or dropped datafile via Flashback database and RMAN tool.<\/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:44: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; alter tablespace ts online;\r\n\r\nTablespace altered.\r\n\r\n\r\n\r\n\r\nSQL&gt; select * from ts.ts;\r\n\r\nID\r\n\r\n----------\r\n\r\n10<\/pre>\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 continue to explain\u00a0datafile restore with Flashback Database Technology in Oracle in this article.<\/p>\n","protected":false},"author":1,"featured_media":9207,"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-9206","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\/05\/https-salihdeveci-files-wordpress-com-2019-01-pr-2.jpeg","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/9206","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=9206"}],"version-history":[{"count":9,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/9206\/revisions"}],"predecessor-version":[{"id":12554,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/9206\/revisions\/12554"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/9207"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=9206"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=9206"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=9206"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}