{"id":220,"date":"2014-12-17T12:56:48","date_gmt":"2014-12-17T11:56:48","guid":{"rendered":"https:\/\/www.carajandb.com\/2014\/12\/17\/restore-database-after-fatal-user-error-en\/"},"modified":"2014-12-17T12:56:48","modified_gmt":"2014-12-17T11:56:48","slug":"restore-database-after-fatal-user-error-en","status":"publish","type":"post","link":"https:\/\/carajandb.com\/en\/2014\/12\/17\/restore-database-after-fatal-user-error-en\/","title":{"rendered":"Restore Database after fatal User Error"},"content":{"rendered":"

Even though Oracle enhances the restore or fault tolerance capabilities with every release there are still fatal errors which lead into a major outage of the database. Those errors are mainly produced by human beings \u2013 sorry guys, but that\u2019s how it is.<\/p>\n

In this blog I\u2019ll show you how to restore the database to a specific SCN after a major incident. Actually, there is a true story behind this: Some years ago I had a customer whose production database was named \u201cTEST\u201d. Even though I told them to rename the database to somewhat meaningful the DBA was totally convinced that this is not a problem\u2026<\/p>\n

As time goes by he had a development database where he wanted to test a new version of the application so he dropped the user\u2026<\/p>\n

You probably know what happened?<\/p>\n

He dropped the application user on the \u201cTEST\u201d database. The entire production line came to a full stop \u2013 no wonder- and the DBA became a little nervous. He was shocked and called me to help restore his database.<\/p>\n

<\/p>\n

Find out when the Problem occured<\/h3>\n

My first question was: \u201cWhen did you execute the drop\u201d \u2013 silence. Actually I think he did not even know what day it was so his answer was: \u201cabout one hour ago \u2013 or so\u201d.<\/p>\n

Not a real help if you need to restore a database to a very specific point in time because even though the production stopped we didn\u2019t want to lose too much data. So I was using Toad and the LogMiner wizard to find out when he executed the fatal script.<\/p>\n

To give you an impression on how to look for the last DML and first DDL (drop) I inserted one row in the customer table and checked for that time:<\/p>\n

\"4201.image001\"<\/p>\n

Picture 1: Insert Customer Data<\/p>\n

As you can see the time of the insert was at 16:31:58 on November 27th<\/sup> 2014. And how the fatal SQL occurs.<\/p>\n

SQL> DROP USER demeng CASCADE;   User dropped<\/span><\/pre>\n
Now we can start the LogMiner and give it some estimations where to look for the data. Menu \u2192 Database \u2192 Diagnostic \u2192 LogMiner<\/p>\n
\"4428.image002\"<\/p>\n
Picture 2: Dictionary select in LogMiner<\/p>\n
Even though I\u2019m not on the server (the database JOHANN is running on a linux box) I\u2019m able to directly use the Online Data Dictionary and check for the most recent redo log files.<\/p>\n
\"6254.image004\"<\/p>\n
Picture 3: Find Files for LogMiner Session<\/p>\n
\"5282.image005\"<\/p>\n
Picture 4: Select online RedoLogs<\/p>\n
At this point it doesn\u2019t matter if you are using the online redologs or the archived ones as long as they are still available. That\u2019s why it makes sense to keep the archived redologs on disk as long as you can.<\/p>\n
With the next step the content of the redologs is analyzed. Unfortunately the timestamp given back on the screen is wrong. The only value which matters is the SCN where the high number indicates that the SCN is invalid. So I\u2019m reading until the end of the logfiles.<\/p>\n
I changed the \u201cFrom\u201d and \u201cTo\u201d Data fields to the approximately time window I\u2019m looking for. And I eliminated uncommitted data.<\/p>\n
\"8422.image006\"<\/p>\n
Picture 5: Narrow Date for LogMiner Session<\/p>\n
Before I press the green triangle to execute the LogMiner query I add some more columns to the list because they might help me to find the right SCN. Those columns are \u201cSegment Owner\u201d, \u201cSegment Name\u201d and \u201cOperation\u201d.<\/p>\n
\"4705.image007\"<\/p>\n
Picture 6: Select additional Columns<\/p>\n
So let’s do the analysis:<\/p>\n
\"6763.image008\"<\/p>\n
Picture 7: Query Operations of DEMOENG<\/p>\n
Because I\u2019ve dropped the schema \u201cDEMOENG\u201d I filter the output to only in that \u201cSchema Owner\u201d. I had expected to see the \u201cINSERT INTO CUSTOMER \u2026\u201d statement first but actually only the DROP statements occurring right after the INSERT where shown. The reason is simple: Because that user had been dropped there is no longer a relationship between the tables and the owner (except for DDLs).<\/p>\n
So a new filter on the SCN gave me an indication on the DML statements occurring right before that fatal DROP.<\/p>\n
\"image009\"<\/p>\n
Picture 8: Get SCN before fatal Drop<\/p>\n
As you can see and verify with the first screenshot the INSERT INTO CUSTOMER still exists (highlighted) but there is no longer any meaningful data as there are no corresponding columns or object names. But that doesn\u2019t matter. Looking for the details I can see that I probably logged in before the fatal error at approximately 16:35:00. So I can recover until that time or SCN.<\/p>\n
Restoring the Database<\/h3>\n
In a real life environment I would have opened the database in restricted mode to avoid any other application to continue to work and I would have taken a second backup just to ensure I\u2019ve some more trials if miscalculating the SCN. In my example I\u2019m now shutting down the instance and using RMAN to restore the database.<\/p>\n
RMAN> SHUTDOWN IMMEDIATE   RMAN> STARTUP MOUNT   RMAN> RESTORE DATABASE UNTIL SCN 859045;   RMAN> RECOVER DATABASE UNTIL SCN 859045;   RMAN> ALTER DATABASE OPEN RESETLOGS;<\/span><\/pre>\n
The successful login to the schema \u201cDEMOENG\u201d will show that the recovery was successful. And a query for custid=200000 will list the row I inserted right before the DROP USER.<\/p>\n
Conclusion<\/h3>\n
The LogMiner wizard is easy to use if you are able to read the content of V$LOGMNR_CONTENTS (that\u2019s the view behind the result set). It doesn\u2019t matter if you are using Oracle Standard Edition as in this case and have to restore the database using RMAN. If you are on Enterprise Edition you might want to use flashback database instead. But the behavior is similar. During my tests I realized the INSERT command for the single row I added did not show up in the logminer session. I had to enable supplemental logging first. So I would guess it does make sense to set supplemental logging even if you don\u2019t use replication. In case of a fatal user error it will probably help identify the correct SCN.<\/p>\n","protected":false},"excerpt":{"rendered":"
Even though Oracle enhances the restore or fault tolerance capabilities with every release there are still fatal errors which lead into a major outage of the database. Those errors are mainly produced by human beings \u2013 sorry guys, but that\u2019s how it is. In this blog I\u2019ll show you how to restore the database to a specific SCN after a major incident. Actually, there is a true story behind this: Some years ago I had a customer whose production database was named \u201cTEST\u201d. Even though I told them to rename the database to somewhat meaningful the DBA was totally convinced […]<\/p>\n","protected":false},"author":3,"featured_media":593,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_crdt_document":"","_uag_custom_page_level_css":"","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":"","ast-disable-related-posts":"","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-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":"","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-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":"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":""},"mobile":{"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":""}},"footnotes":""},"categories":[],"tags":[179,168,178,177,180,50],"class_list":["post-220","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","tag-drop-table","tag-flashback-database","tag-logical-error","tag-logminer","tag-restore-database","tag-rman"],"acf":{"occupation":"Write the Occupation of the Person","person_can-be-speaker":true,"person_is-team":true,"person_related-user-account":null,"person_ordering-number":50,"publication_speakers":null,"publication_content-language":["de"],"publication_files":null,"publication_event":"","publication_date":null},"spectra_custom_meta":{"_thumbnail_id":["593"],"_fgj2wp_old_id":["220"],"_yoast_wpseo_metadesc":["Restore Database after Fatal User Error"],"_yoast_wpseo_metakeywords":["RMAN, LogMiner, Logical Error, DROP TABLE, Flashback Database, Restore Database"],"_fgj2wp_translation_association_key":["fd800fad9b1b4469a4f94086f975861b"],"layout_show-author-box":["1"],"_layout_show-author-box":["field_5a64ee9cc6490"],"_edit_lock":["1518084413:3"],"_wpml_media_duplicate":["1"],"rank_math_description":["Restore Database after Fatal User Error"],"rank_math_news_sitemap_robots":["index"],"rank_math_robots":["a:1:{i:0;s:5:\"index\";}"],"astra_style_timestamp_css":["1773752518"],"rank_math_internal_links_processed":["1"],"wpil_sync_report3":["1"],"wpil_links_inbound_internal_count":["0"],"wpil_links_inbound_internal_count_data":["eJxLtDKwqq4FAAZPAf4="],"wpil_links_outbound_internal_count":["0"],"wpil_links_outbound_internal_count_data":["eJxLtDKwqq4FAAZPAf4="],"wpil_links_outbound_external_count":["0"],"wpil_links_outbound_external_count_data":["eJxLtDKwqq4FAAZPAf4="],"wpil_sync_report2_time":["2024-08-27T11:47:29+00:00"],"copied_media_ids":["a:8:{i:0;i:7300;i:1;i:7301;i:2;i:7302;i:3;i:7303;i:4;i:7304;i:5;i:7305;i:6;i:7306;i:7;i:7307;}"],"referenced_media_ids":["a:1:{i:0;i:593;}"],"_uag_css_file_name":["uag-css-220.css"]},"uagb_featured_image_src":{"full":["https:\/\/carajandb.com\/wp-content\/uploads\/2014\/12\/4201.image001.jpg",604,565,false],"thumbnail":["https:\/\/carajandb.com\/wp-content\/uploads\/2014\/12\/4201.image001-200x200.jpg",200,200,true],"medium":["https:\/\/carajandb.com\/wp-content\/uploads\/2014\/12\/4201.image001-450x421.jpg",450,421,true],"medium_large":["https:\/\/carajandb.com\/wp-content\/uploads\/2014\/12\/4201.image001.jpg",604,565,false],"large":["https:\/\/carajandb.com\/wp-content\/uploads\/2014\/12\/4201.image001.jpg",604,565,false],"1536x1536":["https:\/\/carajandb.com\/wp-content\/uploads\/2014\/12\/4201.image001.jpg",604,565,false],"2048x2048":["https:\/\/carajandb.com\/wp-content\/uploads\/2014\/12\/4201.image001.jpg",604,565,false]},"uagb_author_info":{"display_name":"Johannes Ahrends","author_link":"https:\/\/carajandb.com\/en\/author\/9aa6cdb2095bd409\/"},"uagb_comment_info":0,"uagb_excerpt":"Even though Oracle enhances the restore or fault tolerance capabilities with every release there are still fatal errors which lead into a major outage of the database. Those errors are mainly produced by human beings \u2013 sorry guys, but that\u2019s how it is. In this blog I\u2019ll show you how to restore the database to…","_links":{"self":[{"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/posts\/220","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/comments?post=220"}],"version-history":[{"count":0,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/posts\/220\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/media\/593"}],"wp:attachment":[{"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/media?parent=220"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/categories?post=220"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/tags?post=220"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}