{"id":335,"date":"2013-06-27T09:06:36","date_gmt":"2013-06-27T07:06:36","guid":{"rendered":"http:\/\/www.ludovicocaldara.net\/dba\/?p=335"},"modified":"2020-08-18T16:57:19","modified_gmt":"2020-08-18T14:57:19","slug":"oracle-database-12c-rman-recover-at-table-level","status":"publish","type":"post","link":"https:\/\/www.ludovicocaldara.net\/dba\/oracle-database-12c-rman-recover-at-table-level\/","title":{"rendered":"Oracle Database 12c: RMAN recover at table level"},"content":{"rendered":"

\"Brett<\/a>Oracle Database 12c comes with a new feature named “RMAN table level recovery”.<\/p>\n

After a quick try it’s easy to understand that we are talking about Tablespace Point-in-Time Recovery (TSPITR) with some automation to have it near-transparent.<\/p>\n

 <\/p>\n

How to launch it<\/strong><\/p>\n

The syntax is quite trivial. Suppose you’ve dropped a table ludovico.reco and then purged it (damn!) then you can’t flashback it to before drop and don’t want to flashback the entire database.<\/p>\n

SQL> create table reco (field1 varchar2(50) primary key);\r\n\r\nTable created.\r\n\r\nSQL> insert into reco values ('test');\r\n\r\n1 row created.\r\n\r\nSQL> insert into reco values ('foo');\r\n\r\n1 row created.\r\n\r\nSQL> commit;\r\n\r\nCommit complete.\r\n\r\nSQL> select * from reco;\r\n\r\nFIELD1\r\n--------------------------------------------------\r\nfoo \r\ntest\r\n\r\nSQL> select dbms_flashback.get_system_change_number from dual;\r\n\r\nGET_SYSTEM_CHANGE_NUMBER\r\n------------------------\r\n803916\r\n\r\nSQL> drop table reco;\r\n\r\nTable dropped.\r\n\r\nSQL> purge table reco;\r\n\r\nTable purged.\r\n\r\nSQL> flashback table reco to before drop;\r\nflashback table reco to before drop\r\n*\r\nERROR at line 1:\r\nORA-38305: object not in RECYCLE BIN<\/pre>\n
 <\/p>\n
You can recover the table with:<\/p>\n
RMAN> recover table ludovico.reco until scn 803916 auxiliary destination '\/tmp\/recover';<\/pre>\n
 <\/p>\n
You identify the schema.table:partition to restore, optionally you can pass the pluggable database containing the table to recover, the time definition as usual (scn, seq# or timestamp) and an auxiliary destination.<\/p>\n
This Auxiliary destination is well-known to be mandatory for TSPITR. You can pass other options like table renaming or tablespace remapping.<\/p>\n
Off course, the database must be open in read-write, in archivelog mode and at least one successful backup must be taken.<\/p>\n
How it works<\/strong><\/p>\n
Oracle prepare an auxiliary instance by restoring the SYSTEM, UNDO and SYSAUX tablespaces.<\/p>\n
Starting recover at 10-JUN-13\r\nusing channel ORA_DISK_1 \r\nRMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time\r\n\r\nList of tablespaces expected to have UNDO segments\r\nTablespace SYSTEM \r\nTablespace UNDOTBS1\r\n\r\nCreating automatic instance, with SID='kCoq'\r\n\r\ninitialization parameters used for automatic instance:\r\ndb_name=CLASSIC \r\ndb_unique_name=kCoq_pitr_CLASSIC \r\ncompatible=12.0.0.0.0 \r\ndb_block_size=8192 \r\ndb_files=200 \r\nsga_target=1G \r\nprocesses=80 \r\ndiagnostic_dest=\/u01\/app\/oracle \r\ndb_create_file_dest=\/tmp\/recover \r\nlog_archive_dest_1='location=\/tmp\/recover' \r\n#No auxiliary parameter file used\r\n\r\nstarting up automatic instance CLASSIC\r\n\r\nOracle instance started\r\n\r\nTotal System Global Area 1068937216 bytes\r\n\r\nFixed Size 2295952 bytes\r\nVariable Size 281020272 bytes\r\nDatabase Buffers 780140544 bytes\r\nRedo Buffers 5480448 bytes\r\nAutomatic instance created\r\n\r\ncontents of Memory Script:\r\n{ \r\n# set requested point in time\r\nset until scn 803916; \r\n# restore the controlfile \r\nrestore clone controlfile; \r\n# mount the controlfile \r\nsql clone 'alter database mount clone database';\r\n# archive current online log \r\nsql 'alter system archive log current'; \r\n} \r\nexecuting Memory Script\r\n\r\nexecuting command: SET until clause\r\n\r\nStarting restore at 10-JUN-13\r\nallocated channel: ORA_AUX_DISK_1\r\nchannel ORA_AUX_DISK_1: SID=19 device type=DISK\r\n\r\nchannel ORA_AUX_DISK_1: starting datafile backup set restore\r\nchannel ORA_AUX_DISK_1: restoring control file \r\nchannel ORA_AUX_DISK_1: reading from backup piece +FRA\/CLASSIC\/BACKUPSET\/2013_06_10\/ncsnf0_tag20130610t165249_0.270.817750463\r\nchannel ORA_AUX_DISK_1: piece handle=+FRA\/CLASSIC\/BACKUPSET\/2013_06_10\/ncsnf0_tag20130610t165249_0.270.817750463 tag=TAG20130610T165249\r\nchannel ORA_AUX_DISK_1: restored backup piece 1 \r\nchannel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07 \r\noutput file name=\/tmp\/recover\/CLASSIC\/controlfile\/o1_mf_8vctyxcy_.ctl \r\nFinished restore at 10-JUN-13\r\n\r\nsql statement: alter database mount clone database\r\n\r\nsql statement: alter system archive log current\r\n\r\ncontents of Memory Script:\r\n{ \r\n# set requested point in time\r\nset until scn 803916; \r\n# set destinations for recovery set and auxiliary set datafiles\r\nset newname for clone datafile 1 to new; \r\nset newname for clone datafile 3 to new; \r\nset newname for clone datafile 2 to new; \r\nset newname for clone tempfile 1 to new; \r\n# switch all tempfiles \r\nswitch clone tempfile all; \r\n# restore the tablespaces in the recovery set and the auxiliary set\r\nrestore clone datafile 1, 3, 2; \r\nswitch clone datafile all; \r\n} \r\nexecuting Memory Script\r\n\r\nexecuting command: SET until clause\r\n\r\nexecuting command: SET NEWNAME\r\n\r\nexecuting command: SET NEWNAME\r\n\r\nexecuting command: SET NEWNAME\r\n\r\nexecuting command: SET NEWNAME\r\n\r\nrenamed tempfile 1 to \/tmp\/recover\/CLASSIC\/datafile\/o1_mf_temp_%u_.tmp in control file\r\n\r\nStarting restore at 10-JUN-13\r\nusing channel ORA_AUX_DISK_1\r\n\r\nchannel ORA_AUX_DISK_1: starting datafile backup set restore\r\nchannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set\r\nchannel ORA_AUX_DISK_1: restoring datafile 00001 to \/tmp\/recover\/CLASSIC\/datafile\/o1_mf_system_%u_.dbf\r\nchannel ORA_AUX_DISK_1: restoring datafile 00003 to \/tmp\/recover\/CLASSIC\/datafile\/o1_mf_undotbs1_%u_.dbf\r\nchannel ORA_AUX_DISK_1: restoring datafile 00002 to \/tmp\/recover\/CLASSIC\/datafile\/o1_mf_sysaux_%u_.dbf \r\nchannel ORA_AUX_DISK_1: reading from backup piece +FRA\/CLASSIC\/BACKUPSET\/2013_06_10\/nnndf0_tag20130610t165249_0.269.817750371\r\nchannel ORA_AUX_DISK_1: piece handle=+FRA\/CLASSIC\/BACKUPSET\/2013_06_10\/nnndf0_tag20130610t165249_0.269.817750371 tag=TAG20130610T165249\r\nchannel ORA_AUX_DISK_1: restored backup piece 1 \r\nchannel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:56 \r\nFinished restore at 10-JUN-13\r\n\r\ndatafile 1 switched to datafile copy\r\ninput datafile copy RECID=4 STAMP=817751486 file name=\/tmp\/recover\/CLASSIC\/datafile\/o1_mf_system_8vctzf86_.dbf\r\ndatafile 3 switched to datafile copy \r\ninput datafile copy RECID=5 STAMP=817751486 file name=\/tmp\/recover\/CLASSIC\/datafile\/o1_mf_undotbs1_8vctzfc9_.dbf\r\ndatafile 2 switched to datafile copy \r\ninput datafile copy RECID=6 STAMP=817751487 file name=\/tmp\/recover\/CLASSIC\/datafile\/o1_mf_sysaux_8vctzf32_.dbf<\/pre>\n
Then it opens in READ-ONLY mode the partial database.<\/p>\n
contents of Memory Script:\r\n{ \r\n# set requested point in time\r\nset until scn 803916; \r\n# online the datafiles restored or switched\r\nsql clone \"alter database datafile 1 online\";\r\nsql clone \"alter database datafile 3 online\";\r\nsql clone \"alter database datafile 2 online\";\r\n# recover and open database read only \r\nrecover clone database tablespace \"SYSTEM\", \"UNDOTBS1\", \"SYSAUX\";\r\nsql clone 'alter database open read only'; \r\n} \r\nexecuting Memory Script\r\n\r\nexecuting command: SET until clause\r\n\r\nsql statement: alter database datafile 1 online\r\n\r\nsql statement: alter database datafile 3 online\r\n\r\nsql statement: alter database datafile 2 online\r\n\r\nStarting recover at 10-JUN-13\r\nusing channel ORA_AUX_DISK_1\r\n\r\nstarting media recovery\r\n\r\nchannel ORA_AUX_DISK_1: starting archived log restore to default destination\r\nchannel ORA_AUX_DISK_1: restoring archived log \r\narchived log thread=1 sequence=54 \r\nchannel ORA_AUX_DISK_1: restoring archived log \r\narchived log thread=1 sequence=55 \r\nchannel ORA_AUX_DISK_1: reading from backup piece +FRA\/CLASSIC\/BACKUPSET\/2013_06_10\/annnf0_tag20130610t170210_0.277.817750931\r\nchannel ORA_AUX_DISK_1: piece handle=+FRA\/CLASSIC\/BACKUPSET\/2013_06_10\/annnf0_tag20130610t170210_0.277.817750931 tag=TAG20130610T170210\r\nchannel ORA_AUX_DISK_1: restored backup piece 1 \r\nchannel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03 \r\narchived log file name=\/tmp\/recover\/1_54_814717091.dbf thread=1 sequence=54 \r\narchived log file name=\/tmp\/recover\/1_55_814717091.dbf thread=1 sequence=55 \r\nmedia recovery complete, elapsed time: 00:00:10 \r\nFinished recover at 10-JUN-13\r\n\r\nsql statement: alter database open read only<\/pre>\n
 <\/p>\n
It uses then the read-only dictionary to take the tablespace that was containing the table before the data loss. This tablespace (users in my example) is restored and recovered, and the database is opened.<\/p>\n
contents of Memory Script:\r\n\r\n{ \r\nsql clone \"create spfile from memory\";\r\nshutdown clone immediate; \r\nstartup clone nomount; \r\nsql clone \"alter system set control_files = \r\n''\/tmp\/recover\/CLASSIC\/controlfile\/o1_mf_8vctyxcy_.ctl'' comment=\r\n''RMAN set'' scope=spfile\"; \r\nshutdown clone immediate; \r\nstartup clone nomount; \r\n# mount database \r\nsql clone 'alter database mount clone database'; \r\n} \r\nexecuting Memory Script\r\n\r\nsql statement: create spfile from memory\r\n\r\ndatabase closed\r\ndatabase dismounted\r\nOracle instance shut down\r\n\r\nconnected to auxiliary database (not started)\r\nOracle instance started\r\n\r\nTotal System Global Area 1068937216 bytes\r\n\r\nFixed Size 2295952 bytes\r\nVariable Size 285214576 bytes\r\nDatabase Buffers 775946240 bytes\r\nRedo Buffers 5480448 bytes\r\n\r\nsql statement: alter system set control_files = ''\/tmp\/recover\/CLASSIC\/controlfile\/o1_mf_8vctyxcy_.ctl'' comment= ''RMAN set'' scope=spfile\r\n\r\nOracle instance shut down\r\n\r\nconnected to auxiliary database (not started)\r\nOracle instance started\r\n\r\nTotal System Global Area 1068937216 bytes\r\n\r\nFixed Size 2295952 bytes\r\nVariable Size 285214576 bytes\r\nDatabase Buffers 775946240 bytes\r\nRedo Buffers 5480448 bytes\r\n\r\nsql statement: alter database mount clone database\r\n\r\ncontents of Memory Script:\r\n{ \r\n# set requested point in time\r\nset until scn 803916; \r\n# set destinations for recovery set and auxiliary set datafiles\r\nset newname for datafile 4 to new; \r\n# restore the tablespaces in the recovery set and the auxiliary set\r\nrestore clone datafile 4; \r\nswitch clone datafile all; \r\n} \r\nexecuting Memory Script\r\n\r\nexecuting command: SET until clause\r\n\r\nexecuting command: SET NEWNAME\r\n\r\nStarting restore at 10-JUN-13\r\nallocated channel: ORA_AUX_DISK_1\r\nchannel ORA_AUX_DISK_1: SID=26 device type=DISK\r\n\r\nchannel ORA_AUX_DISK_1: starting datafile backup set restore\r\nchannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set\r\nchannel ORA_AUX_DISK_1: restoring datafile 00004 to \/tmp\/recover\/KCOQ_PITR_CLASSIC\/datafile\/o1_mf_users_%u_.dbf\r\nchannel ORA_AUX_DISK_1: reading from backup piece +FRA\/CLASSIC\/BACKUPSET\/2013_06_10\/nnndf0_tag20130610t165249_0.269.817750371\r\nchannel ORA_AUX_DISK_1: piece handle=+FRA\/CLASSIC\/BACKUPSET\/2013_06_10\/nnndf0_tag20130610t165249_0.269.817750371 tag=TAG20130610T165249\r\nchannel ORA_AUX_DISK_1: restored backup piece 1 \r\nchannel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03 \r\nFinished restore at 10-JUN-13\r\n\r\ndatafile 4 switched to datafile copy\r\ninput datafile copy RECID=8 STAMP=817751583 file name=\/tmp\/recover\/KCOQ_PITR_CLASSIC\/datafile\/o1_mf_users_8vcv7wh0_.dbf\r\n\r\ncontents of Memory Script:\r\n{ \r\n# set requested point in time\r\nset until scn 803916; \r\n# online the datafiles restored or switched\r\nsql clone \"alter database datafile 4 online\";\r\n# recover and open resetlogs \r\nrecover clone database tablespace \"USERS\", \"SYSTEM\", \"UNDOTBS1\", \"SYSAUX\" delete archivelog;\r\nalter clone database open resetlogs; \r\n} \r\nexecuting Memory Script\r\n\r\nexecuting command: SET until clause\r\n\r\nsql statement: alter database datafile 4 online\r\n\r\nStarting recover at 10-JUN-13\r\nusing channel ORA_AUX_DISK_1\r\n\r\nstarting media recovery\r\n\r\nchannel ORA_AUX_DISK_1: starting archived log restore to default destination\r\nchannel ORA_AUX_DISK_1: restoring archived log \r\narchived log thread=1 sequence=54 \r\nchannel ORA_AUX_DISK_1: restoring archived log \r\narchived log thread=1 sequence=55 \r\nchannel ORA_AUX_DISK_1: reading from backup piece +FRA\/CLASSIC\/BACKUPSET\/2013_06_10\/annnf0_tag20130610t170210_0.277.817750931\r\nchannel ORA_AUX_DISK_1: piece handle=+FRA\/CLASSIC\/BACKUPSET\/2013_06_10\/annnf0_tag20130610t170210_0.277.817750931 tag=TAG20130610T170210\r\nchannel ORA_AUX_DISK_1: restored backup piece 1 \r\nchannel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03 \r\narchived log file name=\/tmp\/recover\/1_54_814717091.dbf thread=1 sequence=54 \r\nchannel clone_default: deleting archived log(s) \r\narchived log file name=\/tmp\/recover\/1_54_814717091.dbf RECID=4 STAMP=817751585 \r\narchived log file name=\/tmp\/recover\/1_55_814717091.dbf thread=1 sequence=55 \r\nchannel clone_default: deleting archived log(s) \r\narchived log file name=\/tmp\/recover\/1_55_814717091.dbf RECID=5 STAMP=817751587 \r\nmedia recovery complete, elapsed time: 00:00:01 \r\nFinished recover at 10-JUN-13\r\n\r\ndatabase opened<\/pre>\n
 <\/p>\n
At this \u00a0point, RMAN starts an export\/import with datapump to move the table from the auxiliary database back to the target database:<\/p>\n
contents of Memory Script:\r\n{ \r\n# create directory for datapump import\r\nsql \"create or replace directory TSPITR_DIROBJ_DPDIR as ''\r\n\/tmp\/recover''\"; \r\n# create directory for datapump export \r\nsql clone \"create or replace directory TSPITR_DIROBJ_DPDIR as ''\r\n\/tmp\/recover''\"; \r\n} \r\nexecuting Memory Script\r\n\r\nsql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''\/tmp\/recover''\r\n\r\nsql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''\/tmp\/recover''\r\n\r\nPerforming export of tables...\r\nEXPDP> Starting \"SYS\".\"TSPITR_EXP_kCoq_fqic\": \r\nEXPDP> Estimate in progress using BLOCKS method...\r\nEXPDP> Processing object type TABLE_EXPORT\/TABLE\/TABLE_DATA\r\nEXPDP> Total estimation using BLOCKS method: 64 KB \r\nEXPDP> Processing object type TABLE_EXPORT\/TABLE\/TABLE \r\nEXPDP> Processing object type TABLE_EXPORT\/TABLE\/IDENTITY_COLUMN\r\nEXPDP> Processing object type TABLE_EXPORT\/TABLE\/CONSTRAINT\/CONSTRAINT\r\nEXPDP> Processing object type TABLE_EXPORT\/TABLE\/INDEX\/STATISTICS\/INDEX_STATISTICS\r\nEXPDP> Processing object type TABLE_EXPORT\/TABLE\/STATISTICS\/TABLE_STATISTICS \r\nEXPDP> Processing object type TABLE_EXPORT\/TABLE\/STATISTICS\/MARKER \r\nEXPDP> . . exported \"LUDOVICO\".\"RECO\" 5.054 KB 2 rows\r\nEXPDP> Master table \"SYS\".\"TSPITR_EXP_kCoq_fqic\" successfully loaded\/unloaded \r\nEXPDP> ******************************************************************************\r\nEXPDP> Dump file set for SYS.TSPITR_EXP_kCoq_fqic is: \r\nEXPDP> \/tmp\/recover\/tspitr_kCoq_82218.dmp \r\nEXPDP> Job \"SYS\".\"TSPITR_EXP_kCoq_fqic\" successfully completed at Mon Jun 10 17:14:44 2013 elapsed 0 00:00:41\r\nExport completed\r\n\r\ncontents of Memory Script:\r\n{\r\n# shutdown clone before import\r\nshutdown clone abort\r\n}\r\nexecuting Memory Script\r\n\r\nOracle instance shut down\r\n\r\nPerforming import of tables...\r\nIMPDP> Master table \"SYS\".\"TSPITR_IMP_kCoq_Fbti\" successfully loaded\/unloaded\r\nIMPDP> Starting \"SYS\".\"TSPITR_IMP_kCoq_Fbti\":\r\nIMPDP> Processing object type TABLE_EXPORT\/TABLE\/TABLE\r\nIMPDP> Processing object type TABLE_EXPORT\/TABLE\/TABLE_DATA\r\nIMPDP> . . imported \"LUDOVICO\".\"RECO\" 5.054 KB 2 rows\r\nIMPDP> Processing object type TABLE_EXPORT\/TABLE\/IDENTITY_COLUMN\r\nIMPDP> Processing object type TABLE_EXPORT\/TABLE\/CONSTRAINT\/CONSTRAINT\r\nIMPDP> Processing object type TABLE_EXPORT\/TABLE\/INDEX\/STATISTICS\/INDEX_STATISTICS\r\nIMPDP> Processing object type TABLE_EXPORT\/TABLE\/STATISTICS\/TABLE_STATISTICS\r\nIMPDP> Processing object type TABLE_EXPORT\/TABLE\/STATISTICS\/MARKER\r\nIMPDP> Job \"SYS\".\"TSPITR_IMP_kCoq_Fbti\" successfully completed at Mon Jun 10 17:15:12 2013 elapsed 0 00:00:07\r\nImport completed<\/pre>\n
 <\/p>\n
Finally, the auxiliary instance is cleaned:<\/p>\n
Removing automatic instance\r\nAutomatic instance removed\r\nauxiliary instance file \/tmp\/recover\/CLASSIC\/datafile\/o1_mf_temp_8vcv5mrt_.tmp deleted\r\nauxiliary instance file \/tmp\/recover\/KCOQ_PITR_CLASSIC\/onlinelog\/o1_mf_3_8vcv8nfh_.log deleted\r\nauxiliary instance file \/tmp\/recover\/KCOQ_PITR_CLASSIC\/onlinelog\/o1_mf_2_8vcv8fqb_.log deleted\r\nauxiliary instance file \/tmp\/recover\/KCOQ_PITR_CLASSIC\/onlinelog\/o1_mf_1_8vcv86fv_.log deleted\r\nauxiliary instance file \/tmp\/recover\/KCOQ_PITR_CLASSIC\/datafile\/o1_mf_users_8vcv7wh0_.dbf deleted\r\nauxiliary instance file \/tmp\/recover\/CLASSIC\/datafile\/o1_mf_sysaux_8vctzf32_.dbf deleted\r\nauxiliary instance file \/tmp\/recover\/CLASSIC\/datafile\/o1_mf_undotbs1_8vctzfc9_.dbf deleted\r\nauxiliary instance file \/tmp\/recover\/CLASSIC\/datafile\/o1_mf_system_8vctzf86_.dbf deleted\r\nauxiliary instance file \/tmp\/recover\/CLASSIC\/controlfile\/o1_mf_8vctyxcy_.ctl deleted\r\nauxiliary instance file tspitr_kCoq_82218.dmp deleted\r\nFinished recover at 10-JUN-13<\/pre>\n
 <\/p>\n
We can check if our table is ok:<\/p>\n
RMAN> select * from ludovico.reco;\r\n\r\nFIELD1\r\n--------------------------------------------------\r\nfoo\r\ntest\r\n\r\nRMAN><\/pre>\n
 <\/p>\n
Oh, and yes, now we can select directly from RMAN! \ud83d\ude42<\/p>\n
 <\/p>\n
\u00a0My opinion<\/strong><\/p>\n