Backup and Recovery with RMAN is easy!<\/a>” I wrote how easy it is to backup a database using RMAN if “Oracle” can take care of the file structure.<\/p>\n\n\n\nThe third blog will than give some details about database cloning \/ database restore. <\/p>\n\n\n\n
Let’s start with a curruped database first. Linux is very handy so to destroy or corrupt a data file you can simply use the following command:<\/p>\n\n\n\n
dd if=\/dev\/zero of=\/u02\/oradata\/PAUL\/88ECC48AE4632772E0530D63A8C04AEF\/datafile\/o1_mf_users_gfqznp2m_.dbf' bs=8192 count=1000<\/pre>\n\n\n\nPlease don’t test on your production environment!<\/p>\n\n\n\n
This command leads into an error message if you want to query some data stored in that file:<\/p>\n\n\n\n
SQL> select count(*) from auftraege;\nselect count(*) from auftraege\n *\nERROR at line 1:\nORA-01578: ORACLE data block corrupted (file # 16, block # 482)\nORA-01110: data file 16:\n'\/u02\/oradata\/PAUL\/88ECC48AE4632772E0530D63A8C04AEF\/datafile\/o1_mf_users_gfqznp2m_.dbf' <\/pre>\n\n\n\nAnd now?<\/p>\n\n\n\n
Before we start we should know what happened:<\/p>\n\n\n\n
- It’s the tablespace USERS – so probably not the entire database.<\/li>
- The data file is part of a PDB – other PDBs in the same CDB might not be affected.<\/li>
- The database instance is open. Probably only a small part of the application is affected – may be the application is still doing fine.<\/li><\/ol>\n\n\n\n
So please don’t recover the entire database as that will have an impact on all applications. You only need to restore one file! <\/p>\n\n\n\n
Let’s ask RMAN if he has an idea:<\/p>\n\n\n\n
RMAN> LIST FAILURE;\n \nusing target database control file instead of recovery catalog\nDatabase Role: PRIMARY\n \nList of Database Failures\n=========================\n \nFailure ID Priority Status Time Detected Summary\n---------- -------- --------- ------------- -------\n202 HIGH OPEN 15-MAY-19 One or more non-system datafiles are corrupt <\/pre>\n\n\n\nOkay we already new that. But the command is important for the further analysis. RMAN is now aware that a data file is corrupt. So RMAN might give us an advise:<\/p>\n\n\n\n
RMAN> ADVISE FAILURE;\n \nDatabase Role: PRIMARY\n \nList of Database Failures\n=========================\n \nFailure ID Priority Status Time Detected Summary\n---------- -------- --------- ------------- -------\n202 HIGH OPEN 15-MAY-19 One or more non-system datafiles are corrupt\n \nanalyzing automatic repair options; this may take some time\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=79 device type=DISK\nanalyzing automatic repair options complete\n \nMandatory Manual Actions\n========================\nno manual actions available\n \n \nOptional Manual Actions\n=======================\n1. Automatic repairs may be available if you shutdown the database and restart it in mount mode\n \nAutomated Repair Options\n========================\nOption Repair Description\n------ ------------------\n1 Restore and recover datafile 16\n Strategy: The repair includes complete media recovery with no data loss\n Repair script: \/opt\/oracle\/diag\/rdbms\/paul\/PAUL\/hm\/reco_1304580712.hm <\/pre>\n\n\n\nWow: RMAN created a script namedreco_1304580712.hm<\/code> with all the advises to repair the failure. This is how the file looks:<\/p>\n\n\n\ncat \/opt\/oracle\/diag\/rdbms\/paul\/PAUL\/hm\/reco_1304580712.hm\n# restore and recover datafile\nsql 'BOXER' 'alter database datafile 16 offline';\nrestore ( datafile 16 );\nrecover datafile 16;\nsql 'BOXER' 'alter database datafile 16 online' <\/pre>\n\n\n\nWe could execute that file to repair the failure. But even that isn’t necessary as RMAN can do this for us:<\/p>\n\n\n\n
RMAN> REPAIR FAILURE;\n \nStrategy: The repair includes complete media recovery with no data loss\nRepair script: \/opt\/oracle\/diag\/rdbms\/paul\/PAUL\/hm\/reco_3216729380.hm\n \ncontents of repair script:\n # restore and recover datafile\n sql 'BOXER' 'alter database datafile 16 offline';\n restore ( datafile 16 );\n recover datafile 16;\n sql 'BOXER' 'alter database datafile 16 online';\n \nDo you really want to execute the above repair (enter YES or NO)?\n \nexecuting repair script\n \nsql statement: alter database datafile 16 offline\n \nStarting restore at 15-MAY-19\nusing channel ORA_DISK_1\n \nchannel ORA_DISK_1: starting datafile backup set restore\nchannel ORA_DISK_1: specifying datafile(s) to restore from backup set\nchannel ORA_DISK_1: restoring datafile 00016 to \/u02\/oradata\/PAUL\/88ECC48AE4632772E0530D63A8C04AEF\/datafile\/o1_mf_users_gfqznp2m_.dbf\nchannel ORA_DISK_1: reading from backup piece \/u03\/orabackup\/PAUL\/88ECC48AE4632772E0530D63A8C04AEF\/backupset\/2019_05_15\/o1_mf_nnndf_TAG20190515T141943_gfr103o6_.bkp\nchannel ORA_DISK_1: piece handle=\/u03\/orabackup\/PAUL\/88ECC48AE4632772E0530D63A8C04AEF\/backupset\/2019_05_15\/o1_mf_nnndf_TAG20190515T141943_gfr103o6_.bkp tag=TAG20190515T141943\nchannel ORA_DISK_1: restored backup piece 1\nchannel ORA_DISK_1: restore complete, elapsed time: 00:00:01\nFinished restore at 15-MAY-19\n \nStarting recover at 15-MAY-19\nusing channel ORA_DISK_1\n \nstarting media recovery\nmedia recovery complete, elapsed time: 00:00:01\n \nFinished recover at 15-MAY-19\n \nsql statement: alter database datafile 16 online\nrepair failure complete <\/pre>\n\n\n\nDone! The database is restored and the application or query will run without any errors.<\/p>\n","protected":false},"excerpt":{"rendered":"
In this blog I will show you how easy it is to restore a corrupted tablespace or datafile using RMAN. In my last blog “Backup and Recovery with RMAN is easy!” I wrote how easy it is to backup a database using RMAN if “Oracle” can take care of the file structure. The third blog will than give some details about database cloning \/ database restore. Let’s start with a curruped database first. Linux is very handy so to destroy or corrupt a data file you can simply use the following command: dd if=\/dev\/zero of=\/u02\/oradata\/PAUL\/88ECC48AE4632772E0530D63A8C04AEF\/datafile\/o1_mf_users_gfqznp2m_.dbf’ bs=8192 count=1000 Please don’t test […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_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":[406],"tags":[50],"class_list":["post-7531","post","type-post","status-publish","format-standard","hentry","category-oracle-en","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":{"layout_header":["light"],"_layout_header":["field_5a48111185e5f"],"layout_background":[""],"_layout_background":["field_5a481193c0995"],"layout_hide_services":["0"],"_layout_hide_services":["field_5a4811c5c0996"],"layout_show-author-box":["1"],"_layout_show-author-box":["field_5a64ee9cc6490"],"layout_show-categories-tags":["1"],"_layout_show-categories-tags":["field_5a64eee4c6491"],"blogpost_related-publications":[""],"_blogpost_related-publications":["field_5a480debdac62"],"blogpost_related-files":[""],"_blogpost_related-files":["field_5a480f377db29"],"_yoast_wpseo_primary_category":["406"],"_wpml_media_duplicate":["1"],"_wpml_media_featured":["1"],"_yoast_wpseo_content_score":["90"],"_wpml_location_migration_done":["1"],"_edit_lock":["1560786333:3"],"_wp_old_slug":["recovery-mit-rman-ist-einfach"],"_last_translation_edit_mode":["native-editor"],"_edit_last":["3"],"_wpml_word_count":["{\"total\":815,\"to_translate\":{\"de\":815}}"],"rank_math_primary_category":["406"],"rank_math_news_sitemap_robots":["index"],"rank_math_robots":["a:1:{i:0;s:5:\"index\";}"],"astra_style_timestamp_css":["1784928946"],"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":["1"],"wpil_links_outbound_internal_count_data":["eJx1kM1uwyAQhF\/F5e5gnPRvc2rPTSrl0iNaY2TTELAAN42ivHsXO6rSQ2\/sMrPzaRAEnA1U63cQ98A+BmPlxrfayjfj9gzEEs4RHoFZGqVp2TqLIyyBjcEyej2RrU9pAM6Dtjg61ddVvVooDPiJrm0Wyh+4dryxvuN1JZ55g2o\/DiV9lkEr\/6XDqTya1JfhgK40sdQYTzwfX9FtH1N+1g\/A\/g+YSIAZl3RwSGANiNk\/TP4tDXQAyexDFi9J\/TpxFHSj2F05isxR7DYv28LEInPcZXXuBttWt7I5ycGOnXE5oppTrVeYjHdZSVUpTxRuhq6uzR17EwcdpAoak\/6tUQhijhLH5LPsdp0C0RnX3ZSe1wffjlbLP+r6GjIFf6d5f7n8ANatm4M="],"wpil_links_outbound_external_count":["0"],"wpil_links_outbound_external_count_data":["eJxLtDKwqq4FAAZPAf4="],"wpil_sync_report2_time":["2024-08-27T11:47:24+00:00"],"_uagb_previous_block_counts":["a:90:{s:21:\"uagb\/advanced-heading\";i:0;s:15:\"uagb\/blockquote\";i:0;s:12:\"uagb\/buttons\";i:0;s:18:\"uagb\/buttons-child\";i:0;s:19:\"uagb\/call-to-action\";i:0;s:15:\"uagb\/cf7-styler\";i:0;s:11:\"uagb\/column\";i:0;s:12:\"uagb\/columns\";i:0;s:14:\"uagb\/container\";i:0;s:21:\"uagb\/content-timeline\";i:0;s:27:\"uagb\/content-timeline-child\";i:0;s:14:\"uagb\/countdown\";i:0;s:12:\"uagb\/counter\";i:0;s:8:\"uagb\/faq\";i:0;s:14:\"uagb\/faq-child\";i:0;s:10:\"uagb\/forms\";i:0;s:17:\"uagb\/forms-accept\";i:0;s:19:\"uagb\/forms-checkbox\";i:0;s:15:\"uagb\/forms-date\";i:0;s:16:\"uagb\/forms-email\";i:0;s:17:\"uagb\/forms-hidden\";i:0;s:15:\"uagb\/forms-name\";i:0;s:16:\"uagb\/forms-phone\";i:0;s:16:\"uagb\/forms-radio\";i:0;s:17:\"uagb\/forms-select\";i:0;s:19:\"uagb\/forms-textarea\";i:0;s:17:\"uagb\/forms-toggle\";i:0;s:14:\"uagb\/forms-url\";i:0;s:14:\"uagb\/gf-styler\";i:0;s:15:\"uagb\/google-map\";i:0;s:11:\"uagb\/how-to\";i:0;s:16:\"uagb\/how-to-step\";i:0;s:9:\"uagb\/icon\";i:0;s:14:\"uagb\/icon-list\";i:0;s:20:\"uagb\/icon-list-child\";i:0;s:10:\"uagb\/image\";i:0;s:18:\"uagb\/image-gallery\";i:0;s:13:\"uagb\/info-box\";i:0;s:18:\"uagb\/inline-notice\";i:0;s:11:\"uagb\/lottie\";i:0;s:21:\"uagb\/marketing-button\";i:0;s:10:\"uagb\/modal\";i:0;s:18:\"uagb\/popup-builder\";i:0;s:16:\"uagb\/post-button\";i:0;s:18:\"uagb\/post-carousel\";i:0;s:17:\"uagb\/post-excerpt\";i:0;s:14:\"uagb\/post-grid\";i:0;s:15:\"uagb\/post-image\";i:0;s:17:\"uagb\/post-masonry\";i:0;s:14:\"uagb\/post-meta\";i:0;s:18:\"uagb\/post-taxonomy\";i:0;s:18:\"uagb\/post-timeline\";i:0;s:15:\"uagb\/post-title\";i:0;s:20:\"uagb\/restaurant-menu\";i:0;s:26:\"uagb\/restaurant-menu-child\";i:0;s:11:\"uagb\/review\";i:0;s:12:\"uagb\/section\";i:0;s:14:\"uagb\/separator\";i:0;s:11:\"uagb\/slider\";i:0;s:17:\"uagb\/slider-child\";i:0;s:17:\"uagb\/social-share\";i:0;s:23:\"uagb\/social-share-child\";i:0;s:16:\"uagb\/star-rating\";i:0;s:23:\"uagb\/sure-cart-checkout\";i:0;s:22:\"uagb\/sure-cart-product\";i:0;s:15:\"uagb\/sure-forms\";i:0;s:22:\"uagb\/table-of-contents\";i:0;s:9:\"uagb\/tabs\";i:0;s:15:\"uagb\/tabs-child\";i:0;s:18:\"uagb\/taxonomy-list\";i:0;s:9:\"uagb\/team\";i:0;s:16:\"uagb\/testimonial\";i:0;s:14:\"uagb\/wp-search\";i:0;s:19:\"uagb\/instagram-feed\";i:0;s:10:\"uagb\/login\";i:0;s:17:\"uagb\/loop-builder\";i:0;s:18:\"uagb\/loop-category\";i:0;s:20:\"uagb\/loop-pagination\";i:0;s:15:\"uagb\/loop-reset\";i:0;s:16:\"uagb\/loop-search\";i:0;s:14:\"uagb\/loop-sort\";i:0;s:17:\"uagb\/loop-wrapper\";i:0;s:13:\"uagb\/register\";i:0;s:19:\"uagb\/register-email\";i:0;s:24:\"uagb\/register-first-name\";i:0;s:23:\"uagb\/register-last-name\";i:0;s:22:\"uagb\/register-password\";i:0;s:30:\"uagb\/register-reenter-password\";i:0;s:19:\"uagb\/register-terms\";i:0;s:22:\"uagb\/register-username\";i:0;}"],"_uag_css_file_name":["uag-css-7531.css"]},"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false},"uagb_author_info":{"display_name":"Johannes Ahrends","author_link":"https:\/\/carajandb.com\/en\/author\/9aa6cdb2095bd409\/"},"uagb_comment_info":0,"uagb_excerpt":"In this blog I will show you how easy it is to restore a corrupted tablespace or datafile using RMAN. In my last blog “Backup and Recovery with RMAN is easy!” I wrote how easy it is to backup a database using RMAN if “Oracle” can take care of the file structure. The third blog…","_links":{"self":[{"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/posts\/7531","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=7531"}],"version-history":[{"count":3,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/posts\/7531\/revisions"}],"predecessor-version":[{"id":7541,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/posts\/7531\/revisions\/7541"}],"wp:attachment":[{"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/media?parent=7531"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/categories?post=7531"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/tags?post=7531"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}