{"id":11597,"date":"2018-08-28T15:18:39","date_gmt":"2018-08-28T13:18:39","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/rman-pitr-recover-table-oracle-12c\/"},"modified":"2018-08-28T15:18:39","modified_gmt":"2018-08-28T13:18:39","slug":"rman-pitr-recover-table-oracle-12c","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/rman-pitr-recover-table-oracle-12c\/","title":{"rendered":"RMAN PITR recover table Oracle 12c"},"content":{"rendered":"<p class=\"dbiNormal\">At one client\u2019s site, I had to restore a table someone had partially deleted one week before. Before Oracle 12c, we had to duplicate the target database to another server, and then to export and import data to the target database. But depending on the database size, it could cost a lot of time, and as nobody knew when the delete action happened, it was more practical to use the rman recover table command in order to have multiple versions of the table content.<\/p>\n<p>At first for security, we save the application table:<\/p>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">SQL&gt; create table appuser.employe_save as select * from appuser.employe;\n\nTable created.\n\n<\/pre>\n<p>My backups are configured on sbt_tape with ddboost, so I thought I only have to run such a command\u00a0:<\/p>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">run {\nALLOCATE CHANNEL C1 DEVICE TYPE SBT_TAPE PARMS 'BLKSIZE=1048576, \nSBT_LIBRARY=\/opt\/dpsapps\/dbappagent\/lib\/lib64\/libddboostora.so, \nSBT_PARMS=(CONFIG_FILE=\/opt\/dpsapps\/dbappagent\/config\/oracle_ddbda_proddb.cfg)' \nFORMAT '%d_%U' ;\nrecover table appuser.employe\nuntil time \"to_date('16-AUG-2018 08:00:00','DD-MON-YYYY HH24:MI:SS')\"\nauxiliary destination '\/tmp\/proddb\/aux';\n}\n\nBut I got this error message:\n\n<\/pre>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">RMAN-03002: failure of recover command at 08\/23\/2018 10:50:04\nRMAN-03015: error occurred in stored script Memory Script\nRMAN-06026: some targets not found - aborting restore\nRMAN-06101: no channel to restore a backup or copy of the control file<\/pre>\n<p>The problem is\u00a0documented with bug 17089942:<\/p>\n<p>The table recovery fails when channels are allocated manually within a run block. The solution consists in defining the channel device type in the rman configuration:<\/p>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">rman target \/\n\nRecovery Manager: Release 12.1.0.2.0 - Production on Thu Aug 23 13:52:39 2018\n\nCopyright (c) 1982, 2014, Oracle and\/or its affiliates.  All rights reserved.\n\nconnected to target database: PRODDB (DBID=271333692)\nconnected to recovery catalog database\n\nRMAN&gt; configure channel device type sbt_tape parms 'BLKSIZE=1048576, \nSBT_LIBRARY=\/opt\/dpsapps\/dbappagent\/lib\/lib64\/libddboostora.so, \nSBT_PARMS=(CONFIG_FILE=\/opt\/dpsapps\/dbappagent\/config\/oracle_ddbda_proddb.cfg)';\n\nstarting full resync of recovery catalog\nfull resync complete\nnew RMAN configuration parameters:\nCONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'BLKSIZE=1048576, \nSBT_LIBRARY=\/opt\/dpsapps\/dbappagent\/lib\/lib64\/libddboostora.so, \nSBT_PARMS=(CONFIG_FILE=\/opt\/dpsapps\/dbappagent\/config\/oracle_ddbda_proddb.cfg)';\nnew RMAN configuration parameters are successfully stored\nstarting full resync of recovery catalog\nfull resync complete<\/pre>\n<p>Then connected with rman we can run the following recover command\u00a0in order to restore the employe table with a new name employe_16082018:<\/p>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">RMAN&gt; run {\n recover table appuser.employe\nuntil time \"to_date('16-AUG-2018 08:00:00','DD-MON-YYYY HH24:MI:SS')\"\nauxiliary destination '\/tmp\/proddb\/aux'\nremap table appuser.employe:employe_16082018;\n}<\/pre>\n<p>What happens\u00a0? Oracle will create a pseudo database under \/tmp\/proddb\/aux with SYSTEM SYSAUX TEMP UNDO and data tablespaces, then it restores the appuser.employe table at the specified date and renames it with the specified new name. Finally Oracle deletes the pseudo database.<\/p>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">RMAN&gt; run {\n2&gt; recover table appuser.employe\n3&gt; until time \"to_date('16-AUG-2018 08:00:00','DD-MON-YYYY HH24:MI:SS')\"\n4&gt; auxiliary destination '\/tmp\/PRODDB\/aux'\n5&gt; remap table appuser.employe:employe_16082018;\n6&gt; }\n\nStarting recover at 23-AUG-2018 14:03:05\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=765 device type=DISK\nallocated channel: ORA_SBT_TAPE_1\nchannel ORA_SBT_TAPE_1: SID=2562 device type=SBT_TAPE\nchannel ORA_SBT_TAPE_1: database app agent Oracle v4.5.0.0\n\nCreating automatic instance, with SID='ecvh'\n\ninitialization parameters used for automatic instance:\ndb_name=PRODDB\ndb_unique_name=ecvh_pitr_PRODDB\ncompatible=12.1.0.2.0\ndb_block_size=8192\ndb_files=200\ndiagnostic_dest=\/u00\/app\/oracle\n_system_trig_enabled=FALSE\nsga_target=2560M\nprocesses=200\ndb_create_file_dest=\/tmp\/PRODDB\/aux\nlog_archive_dest_1='location=\/tmp\/PRODDB\/aux'\n#No auxiliary parameter file used\n\n\u2026..\n\nPerforming import of tables...\n   IMPDP&gt; Master table \"SYS\".\"TSPITR_IMP_ecvh_cesy\" successfully loaded\/unloaded\n   IMPDP&gt; Starting \"SYS\".\"TSPITR_IMP_ecvh_cesy\":\n   IMPDP&gt; Processing object type TABLE_EXPORT\/TABLE\/TABLE\n   IMPDP&gt; Processing object type TABLE_EXPORT\/TABLE\/TABLE_DATA\n   IMPDP&gt; . . imported \"APPUSER\".\"EMPLOYE_16082018\"           7.137 MB   16173 rows\n   IMPDP&gt; Processing object type TABLE_EXPORT\/TABLE\/GRANT\/OWNER_GRANT\/OBJECT_GRANT\n   IMPDP&gt; Processing object type TABLE_EXPORT\/TABLE\/STATISTICS\/TABLE_STATISTICS\n   IMPDP&gt; Processing object type TABLE_EXPORT\/TABLE\/STATISTICS\/MARKER\n   IMPDP&gt; Job \"SYS\".\"TSPITR_IMP_ecvh_cesy\" successfully completed at Thu Aug 23 14:10:28 2018 elapsed 0 00:00:10\nImport completed\n\n\nRemoving automatic instance\nAutomatic instance removed\nauxiliary instance file \/tmp\/PRODDB\/aux\/PRODDB_CI\/datafile\/o1_mf_temp_fqx8w89p_.tmp deleted\nauxiliary instance file \/tmp\/PRODDB\/aux\/ECVH_PITR_PRODDB\/onlinelog\/o1_mf_3_fqx90jyn_.log deleted\nauxiliary instance file \/tmp\/PRODDB\/aux\/ECVH_PITR_PRODDB\/onlinelog\/o1_mf_2_fqx90hyd_.log deleted\nauxiliary instance file \/tmp\/PRODDB\/aux\/ECVH_PITR_PRODDB\/onlinelog\/o1_mf_1_fqx90gwo_.log deleted\nauxiliary instance file \/tmp\/PRODDB\/aux\/ECVH_PITR_PRODDB\/datafile\/o1_mf_affac_1_fqx8xybx_.dbf deleted\nauxiliary instance file \/tmp\/PRODDB\/aux\/PRODDB_CI\/datafile\/o1_mf_sysaux_fqx8p1p7_.dbf deleted\nauxiliary instance file \/tmp\/PRODDB\/aux\/PRODDB_CI\/datafile\/o1_mf_undotbs1_fqx8nskn_.dbf deleted\nauxiliary instance file \/tmp\/PRODDB\/aux\/PRODDB_CI\/datafile\/o1_mf_system_fqx8olyx_.dbf deleted\nauxiliary instance file \/tmp\/PRODDB\/aux\/PRODDB_CI\/controlfile\/o1_mf_fqx8nb57_.ctl deleted\nauxiliary instance file tspitr_ecvh_63884.dmp deleted\nFinished recover at 23-AUG-2018 14:10:29<\/pre>\n<p>The recover was quite fast, so I had the possibility to run multiple recover at different times allowing me to understand at which time the delete command happened:<\/p>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">SQL&gt; select table_name from all_tables where owner = 'APPUSER' and table_name like 'EMPLOYE%'\n\nTABLE_NAME\n--------------------------------------------------------------------------------\nEMPLOYE\nEMPLOYE_16082018\nEMPLOYE_22072018\nEMPLOYE_SAVE\n\n<\/pre>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">SQL&gt; select count(*) from appuser.employe_22072018;\n\n  COUNT(*)\n----------\n     16141\n\nSQL&gt; r\n  1* select count(*) from appuser.employe_16082018\n\n  COUNT(*)\n----------\n     16173\n\nSQL&gt; select count(*) from appuser.employe;\n\n  COUNT(*)\n----------\n     16226\n\n<\/pre>\n<p>I already tested this recover feature on my own virtual machine on a test database. Running this recover command on a production database allowed me to discover the Oracle bug when your backups are on tape. Finally using ddboost with rman is so fast that you do not have to hesitate to restore tables with Oracle 12c even with a huge volumetry.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>At one client\u2019s site, I had to restore a table someone had partially deleted one week before. Before Oracle 12c, we had to duplicate the target database to another server, and then to export and import data to the target database. But depending on the database size, it could cost a lot of time, and [&hellip;]<\/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],"tags":[209,270],"type_dbi":[],"class_list":["post-11597","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-oracle-12c","tag-rman"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>RMAN PITR recover table Oracle 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\/rman-pitr-recover-table-oracle-12c\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"RMAN PITR recover table Oracle 12c\" \/>\n<meta property=\"og:description\" content=\"At one client\u2019s site, I had to restore a table someone had partially deleted one week before. Before Oracle 12c, we had to duplicate the target database to another server, and then to export and import data to the target database. But depending on the database size, it could cost a lot of time, and [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/rman-pitr-recover-table-oracle-12c\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-08-28T13:18:39+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=\"5 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\/rman-pitr-recover-table-oracle-12c\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/rman-pitr-recover-table-oracle-12c\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"RMAN PITR recover table Oracle 12c\",\"datePublished\":\"2018-08-28T13:18:39+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/rman-pitr-recover-table-oracle-12c\/\"},\"wordCount\":310,\"commentCount\":0,\"keywords\":[\"Oracle 12c\",\"RMAN\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/rman-pitr-recover-table-oracle-12c\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/rman-pitr-recover-table-oracle-12c\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/rman-pitr-recover-table-oracle-12c\/\",\"name\":\"RMAN PITR recover table Oracle 12c - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2018-08-28T13:18:39+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/rman-pitr-recover-table-oracle-12c\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/rman-pitr-recover-table-oracle-12c\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/rman-pitr-recover-table-oracle-12c\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"RMAN PITR recover table Oracle 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":"RMAN PITR recover table Oracle 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\/rman-pitr-recover-table-oracle-12c\/","og_locale":"en_US","og_type":"article","og_title":"RMAN PITR recover table Oracle 12c","og_description":"At one client\u2019s site, I had to restore a table someone had partially deleted one week before. Before Oracle 12c, we had to duplicate the target database to another server, and then to export and import data to the target database. But depending on the database size, it could cost a lot of time, and [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/rman-pitr-recover-table-oracle-12c\/","og_site_name":"dbi Blog","article_published_time":"2018-08-28T13:18:39+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/rman-pitr-recover-table-oracle-12c\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/rman-pitr-recover-table-oracle-12c\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"RMAN PITR recover table Oracle 12c","datePublished":"2018-08-28T13:18:39+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/rman-pitr-recover-table-oracle-12c\/"},"wordCount":310,"commentCount":0,"keywords":["Oracle 12c","RMAN"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/rman-pitr-recover-table-oracle-12c\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/rman-pitr-recover-table-oracle-12c\/","url":"https:\/\/www.dbi-services.com\/blog\/rman-pitr-recover-table-oracle-12c\/","name":"RMAN PITR recover table Oracle 12c - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2018-08-28T13:18:39+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/rman-pitr-recover-table-oracle-12c\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/rman-pitr-recover-table-oracle-12c\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/rman-pitr-recover-table-oracle-12c\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"RMAN PITR recover table Oracle 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\/11597","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=11597"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11597\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=11597"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=11597"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=11597"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=11597"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}