{"id":12229,"date":"2019-01-30T13:30:21","date_gmt":"2019-01-30T12:30:21","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/recover-a-corrupted-datafile-in-your-dataguard-environment-11g12c\/"},"modified":"2019-01-30T13:30:21","modified_gmt":"2019-01-30T12:30:21","slug":"recover-a-corrupted-datafile-in-your-dataguard-environment-11g12c","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/recover-a-corrupted-datafile-in-your-dataguard-environment-11g12c\/","title":{"rendered":"Recover a corrupted datafile in your DataGuard environment 11G\/12C."},"content":{"rendered":"

On a DG environment, a datafile needs to be recovered on the STANDBY site, in two situations : when is deleted<\/strong> or corrupted.<\/strong>
\nBelow, I will explain\u00a0 how to recover a corrupted datafile, in order to be able to repair the Standby database, without to be necessary to restore entire database.<\/p>\n

Initial situation :<\/p>\n

DGMGRL> connect \/\nConnected to \"PROD_SITE2\"\nConnected as SYSDG.\nDGMGRL> show configuration;\n\nConfiguration - CONFIG1\n\n  Protection Mode: MaxPerformance\n  Members:\n  PROD_SITE2 - Primary database\n    PROD_SITE1 - Physical standby database\n\nFast-Start Failover: DISABLED\n\nConfiguration Status:\nSUCCESS   (status updated 15 seconds ago)\n\n<\/pre>\n
On this\u00a0 environment, we have a table called EMP with 100 rows, owned by the user TEST (default tablespace TEST).<\/p>\n
SQL> set linesize 220;\nSQL> select username,default_tablespace from dba_users where username='TEST';\n\nUSERNAME     DEFAULT_TABLESPACE\n-------------------------------\nTEST         TEST\n\nSQL> select count(*) from test.emp;\n\n  COUNT(*)\n----------\n       100\n<\/pre>\n
By mistake, the datafile on Standby site, get corrupted.<\/p>\n
SQL> alter database open read only;\nalter database open read only\n*\nORA-01578: ORACLE data block corrupted (file # 5, block # 3)\nORA-01110: data file 5: '\/u02\/oradata\/PROD\/test.dbf'<\/pre>\n
As is corrupted, the apply of the redo log is stopped until will be repaired. So the new inserts into the EMP table will not be applied:<\/p>\n
SQL> begin\n  2  for i in 101..150 loop\n  3  insert into test.emp values (i);\n  4  end loop;\n  5  END;\n  6  \/\n\nPL\/SQL procedure successfully completed.\n\nSQL> COMMIT;\n\nCommit complete.\n\nSQL> select count(*) from test.emp;\n\n  COUNT(*)\n----------\n       150\n\nSQL> select name,db_unique_name,database_role from v$database;\n\nNAME      DB_UNIQUE_NAME                 DATABASE_ROLE\n--------- ------------------------------ ----------------\nPROD      PROD_SITE2                     PRIMARY<\/pre>\n
To repair it, we will use PRIMARY site to backup controlfile and the related datafile.<\/p>\n
oracle@dbisrv03:\/home\/oracle\/ [PROD] rman target \/\n\nconnected to target database: PROD (DBID=410572245)\n\nRMAN> backup current controlfile for standby format '\/u02\/backupctrl.ctl';\n\n\nRMAN> backup datafile 5 format '\/u02\/testbkp.dbf';\n\nStarting backup at 29-JAN-2019 10:59:37\nusing target database control file instead of recovery catalog\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=276 device type=DISK<\/pre>\n
We will transfer the backuppieces on the STANDBY server, using scp:<\/p>\n
 scp backupctrl.ctl oracle@dbisrv04:\/u02\/\n scp testbkp.dbf oracle@dbisrv04:\/u02\/\n<\/pre>\n
Now, will start the restore\/recover on the STANDBY server :<\/p>\n
SQL> startup nomount\nORACLE instance started.\n\nTotal System Global Area 1895825408 bytes\nFixed Size                  8622048 bytes\nVariable Size             570425376 bytes\nDatabase Buffers         1308622848 bytes\nRedo Buffers                8155136 bytes\nSQL> exit\noracle@dbisrv04:\/u02\/oradata\/PROD\/ [PROD] rman target \/\n\n\nCopyright (c) 1982, 2017, Oracle and\/or its affiliates.  All rights reserved.\n\nconnected to target database: PROD (not mounted)\n\nRMAN> restore controlfile from '\/u02\/backupctrl.ctl'; \n.........\nRMAN> alter database mount;\n\n\nRMAN> catalog start with '\/u02\/testbkp.dbf';\n\nsearching for all files that match the pattern \/u02\/testbkp.dbf\n\nList of Files Unknown to the Database\n=====================================\nFile Name: \/u02\/testbkp.dbf\n\nDo you really want to catalog the above files (enter YES or NO)? YES\ncataloging files...\ncataloging done\n\nList of Cataloged Files\n=======================\nFile Name: \/u02\/testbkp.dbf\n\n\n\n\nRMAN> restore datafile 5;\n\nStarting restore at 29-JAN-2019 11:06:31\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 00005 to \/u02\/oradata\/PROD\/test.dbf\nchannel ORA_DISK_1: reading from backup piece \/u02\/testbkp.dbf\nchannel ORA_DISK_1: piece handle=\/u02\/testbkp.dbf tag=TAG20190129T105938\nchannel ORA_DISK_1: restored backup piece 1\nchannel ORA_DISK_1: restore complete, elapsed time: 00:00:01\nFinished restore at 29-JAN-2019 11:06:33\n\nRMAN> exit\n<\/pre>\n
Now, we will start to apply the logs again and try to resync the STANDBY database.
\n!!! Here you need to stop recovery process if you do not have a dataguard active license.<\/strong><\/p>\n
SQL> recover managed standby database using current logfile disconnect from session;\nMedia recovery complete.\nSQL> recover managed standby database cancel;\nSQL> alter database open read only;\n\nDatabase altered.\n\nSQL> select count(*) from test.emp;\n\n  COUNT(*)\n----------\n       150\n<\/pre>\n
Now, we can see the last insert activity on the PRIMARY site that is available on the STANDBY site.<\/p>\n
On 12c environment, with an existing container PDB1, the things are easier, with the feature RESTORE\/RECOVER from service :<\/p>\n
connect on the standby site\nrman target \/\nrestore tablespace PDB1:USERS from service PROD_PRIMARY;\nrecover tablespace PDB1:USERS;<\/pre>\n
<\/pre>\n","protected":false},"excerpt":{"rendered":"
On a DG environment, a datafile needs to be recovered on the STANDBY site, in two situations : when is deleted or corrupted. Below, I will explain\u00a0 how to recover a corrupted datafile, in order to be able to repair the Standby database, without to be necessary to restore entire database. Initial situation : DGMGRL> […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198,368,59],"tags":[656,297,221,280,23,84,96,988,209],"type_dbi":[],"class_list":["post-12229","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","category-development-performance","category-oracle","tag-12-2","tag-availability-groups","tag-data-guard","tag-database","tag-dba","tag-high-availability","tag-oracle","tag-oracle-12-2","tag-oracle-12c"],"acf":[],"yoast_head":"\nRecover a corrupted datafile in your DataGuard environment 11G\/12C. - dbi Blog<\/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:\/\/www.dbi-services.com\/blog\/recover-a-corrupted-datafile-in-your-dataguard-environment-11g12c\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Recover a corrupted datafile in your DataGuard environment 11G\/12C.\" \/>\n<meta property=\"og:description\" content=\"On a DG environment, a datafile needs to be recovered on the STANDBY site, in two situations : when is deleted or corrupted. Below, I will explain\u00a0 how to recover a corrupted datafile, in order to be able to repair the Standby database, without to be necessary to restore entire database. Initial situation : DGMGRL> […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/recover-a-corrupted-datafile-in-your-dataguard-environment-11g12c\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2019-01-30T12:30:21+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/recover-a-corrupted-datafile-in-your-dataguard-environment-11g12c\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/recover-a-corrupted-datafile-in-your-dataguard-environment-11g12c\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Recover a corrupted datafile in your DataGuard environment 11G\/12C.\",\"datePublished\":\"2019-01-30T12:30:21+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/recover-a-corrupted-datafile-in-your-dataguard-environment-11g12c\/\"},\"wordCount\":223,\"commentCount\":0,\"keywords\":[\"12.2\",\"Availability groups\",\"Data Guard\",\"database\",\"DBA\",\"High availability\",\"Oracle\",\"Oracle 12.2\",\"Oracle 12c\"],\"articleSection\":[\"Database Administration & Monitoring\",\"Database management\",\"Development & Performance\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/recover-a-corrupted-datafile-in-your-dataguard-environment-11g12c\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/recover-a-corrupted-datafile-in-your-dataguard-environment-11g12c\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/recover-a-corrupted-datafile-in-your-dataguard-environment-11g12c\/\",\"name\":\"Recover a corrupted datafile in your DataGuard environment 11G\/12C. - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2019-01-30T12:30:21+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/recover-a-corrupted-datafile-in-your-dataguard-environment-11g12c\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/recover-a-corrupted-datafile-in-your-dataguard-environment-11g12c\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/recover-a-corrupted-datafile-in-your-dataguard-environment-11g12c\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Recover a corrupted datafile in your DataGuard environment 11G\/12C.\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Recover a corrupted datafile in your DataGuard environment 11G\/12C. - dbi Blog","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:\/\/www.dbi-services.com\/blog\/recover-a-corrupted-datafile-in-your-dataguard-environment-11g12c\/","og_locale":"en_US","og_type":"article","og_title":"Recover a corrupted datafile in your DataGuard environment 11G\/12C.","og_description":"On a DG environment, a datafile needs to be recovered on the STANDBY site, in two situations : when is deleted or corrupted. Below, I will explain\u00a0 how to recover a corrupted datafile, in order to be able to repair the Standby database, without to be necessary to restore entire database. Initial situation : DGMGRL> […]","og_url":"https:\/\/www.dbi-services.com\/blog\/recover-a-corrupted-datafile-in-your-dataguard-environment-11g12c\/","og_site_name":"dbi Blog","article_published_time":"2019-01-30T12:30:21+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/recover-a-corrupted-datafile-in-your-dataguard-environment-11g12c\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/recover-a-corrupted-datafile-in-your-dataguard-environment-11g12c\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Recover a corrupted datafile in your DataGuard environment 11G\/12C.","datePublished":"2019-01-30T12:30:21+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/recover-a-corrupted-datafile-in-your-dataguard-environment-11g12c\/"},"wordCount":223,"commentCount":0,"keywords":["12.2","Availability groups","Data Guard","database","DBA","High availability","Oracle","Oracle 12.2","Oracle 12c"],"articleSection":["Database Administration & Monitoring","Database management","Development & Performance","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/recover-a-corrupted-datafile-in-your-dataguard-environment-11g12c\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/recover-a-corrupted-datafile-in-your-dataguard-environment-11g12c\/","url":"https:\/\/www.dbi-services.com\/blog\/recover-a-corrupted-datafile-in-your-dataguard-environment-11g12c\/","name":"Recover a corrupted datafile in your DataGuard environment 11G\/12C. - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2019-01-30T12:30:21+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/recover-a-corrupted-datafile-in-your-dataguard-environment-11g12c\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/recover-a-corrupted-datafile-in-your-dataguard-environment-11g12c\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/recover-a-corrupted-datafile-in-your-dataguard-environment-11g12c\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Recover a corrupted datafile in your DataGuard environment 11G\/12C."}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/12229","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=12229"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/12229\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=12229"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=12229"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=12229"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=12229"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}