Oracle DBA – A lifelong learning experience

Recover table from RMAN in 12c

Posted by John Hallas on October 21, 2013

When  I read the list of 12c new features the one that interested me immediately was the ability to be able to recover a table from an RMAN backup. This seemed to be quite challenging as RMAN is an image copy of blocks and a table is normally copied using a logical Datapump export.

What Oracle have delivered is a packaged technique which recovers only the necessary tables so as to get the data dictionary and any undo/redo segments necessary to get all the data back to a  specific point in time whether that be a SCN or a timestamp.

The whole process is very neat and it creates a new database, recovers the necessary datafiles and then creates a dumpfile of the necessary table, puts the data back to wherever you want and then deletes all evidence and traces of what it has done.

I have provided a full example of such a recovery below and it is well worth looking at to see how it all works.

A few things seem worthy of note and even further  investigation.

The new database SID is a randomly generated four character one – xzqD in the example below with a unique name of xzqD_pitr_SCRATCH where SCRATCH was my original database name.

The default size of the SGA  is 1G. That might be an issue on some small servers, especially when the original database is smaller than this. I think it is a hard-coded parameter as it is not dependant on the existing database SGA size. The file recover.bsq file in $ORACLE_HOME/rdbms/admin seems to contain the functions used to size the SGA .  Read the header first before altering it as it is actually generated from another file.

My next observation is that the functionality is very powerful, the key statement run from within RMAN is below. In my example I was recovering a table into a different table but I could have used remap_tablespace as well or used ASM disks by putting  a diskgroup name in place of the flat file I specified.

recover table 'TEST'.'TESTRECOVER'

until scn 10849405521118

auxiliary DESTINATION  ‘/app/oracle/export/'          --'+DATA'

remap table 'TEST'.'TESTRECOVER':'TESTRECOVER1';   --remap_tablespace

It is also possible to overwrite or append into the existing table with the rows that have been recovered but on a production system I don’t think that is likely to be a common usage. I think it would always be preferable to output to a new table just to check the contents before changing the current table.

So when might you use the RECOVER TABLE functionality. One good reason is when a DDL operation has changed the structure of a table because that would prohibit the use of flashback table. My example shows how I restore after a truncate table operation which would not be allowed to be flashed back. Other examples are when a dropped table has been purged out of the recycle bin or when there is insufficient undo to enable one to flashback as far as you would like.

Objects created in the SYS or SYSAUX tablespace cannot be recovered and it is pretty self-evident that archive logging must be enabled (and the logs available).

One point that probably says more about my reading comprehension than anything else is the NOTABLE IMPORT parameter to the RECOVER command. I initially read this as NOTABLE and I did not really understand what it meant. What it does is control whether or not a datapump file is imported back into the target database. In my example it did recover the data back into the table TESTRECOVER1 because I had not specified the NOTABLE IMPORT parameter. Perhaps I should have read it as NO_TABLE IMPORT !!

Finally, I am intrigued by the line in the documentation which states “Single table partitions can be recovered only if your Oracle Database version is Oracle Database 11g Release 1 or later”.  This implies to me that whilst this a 12c feature it might be backward compatible to 11g or even 10g if you are recovering a table and not a table partition.  I have tried to get it working across versions with no success, by calling it from 12c binaries in an 11g database  My next thought is to find the script which creates the procedures in it and apply it to a 11g database and try it out again. That is probably the recover.bsq script as mentioned earlier. However even if it worked it is not likely to be done on production and all this function does is simply what is an existing process, although it is very neat and easy to use.

rmant

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Oct 16 08:52:22 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SCRATCH (DBID=2135410166)

using target database control file instead of recovery catalog

RMAN> recover table 'TEST'.'TESTRECOVER'

until scn 10849405521118

auxiliary DESTINATION '/app/oracle/export/'

remap table 'TEST'.'TESTRECOVER':'TESTRECOVER1';


Starting recover at 2013-10-16:08:52:28

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=25 device type=DISK

RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time

 

List of tablespaces expected to have UNDO segments

Tablespace SYSTEM

Tablespace UNDOTBS1


Creating automatic instance, with SID='xzqD'


initialization parameters used for automatic instance:

db_name=SCRATCH

db_unique_name=xzqD_pitr_SCRATCH

compatible=12.1.0.0.0

db_block_size=8192

db_files=200

sga_target=1G

processes=80

diagnostic_dest=/app/oracle

db_create_file_dest=/app/oracle/export/

log_archive_dest_1='location=/app/oracle/export/'

#No auxiliary parameter file used

 

 

starting up automatic instance SCRATCH

 

Oracle instance started

 

Total System Global Area    1068937216 bytes

 

Fixed Size                     2296576 bytes

Variable Size                281019648 bytes

Database Buffers             780140544 bytes

Redo Buffers                   5480448 bytes

Automatic instance created

 

contents of Memory Script:

{

# set requested point in time

set until  scn 10849405521118;

# restore the controlfile

restore clone controlfile;

# mount the controlfile

sql clone 'alter database mount clone database';

# archive current online log

sql 'alter system archive log current';

}

executing Memory Script

 

executing command: SET until clause

 

Starting restore at 2013-10-16:08:52:45

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=82 device type=DISK

 

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece +FRA/SCRATCH/BACKUPSET/2013_10_15/ncnnf0_tag20131015t080614_0.338.828864481

channel ORA_AUX_DISK_1: piece handle=+FRA/SCRATCH/BACKUPSET/2013_10_15/ncnnf0_tag20131015t080614_0.338.828864481 tag=TAG20131015T080614

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:05

output file name=/app/oracle/export/SCRATCH/controlfile/o1_mf_95wkgmqq_.ctl

Finished restore at 2013-10-16:08:52:52

 

sql statement: alter database mount clone database

 

sql statement: alter system archive log current

 

contents of Memory Script:

{

# set requested point in time

set until  scn 10849405521118;

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile  1 to new;

set newname for clone datafile  4 to new;

set newname for clone datafile  3 to new;

set newname for clone tempfile  1 to new;

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile  1, 4, 3;

switch clone datafile all;

}

executing Memory Script

 

executing command: SET until clause

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

renamed tempfile 1 to /app/oracle/export/SCRATCH/datafile/o1_mf_temp_%u_.tmp in control file

 

Starting restore at 2013-10-16:08:52:59

using channel ORA_AUX_DISK_1

 

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /app/oracle/export/SCRATCH/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /app/oracle/export/SCRATCH/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /app/oracle/export/SCRATCH/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece +FRA/SCRATCH/BACKUPSET/2013_10_15/nnndf0_tag20131015t080614_0.337.828864377

channel ORA_AUX_DISK_1: piece handle=+FRA/SCRATCH/BACKUPSET/2013_10_15/nnndf0_tag20131015t080614_0.337.828864377 tag=TAG20131015T080614

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55

Finished restore at 2013-10-16:08:53:55

 

datafile 1 switched to datafile copy

input datafile copy RECID=4 STAMP=828953636 file name=/app/oracle/export/SCRATCH/datafile/o1_mf_system_95wkgwtq_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=5 STAMP=828953636 file name=/app/oracle/export/SCRATCH/datafile/o1_mf_undotbs1_95wkgwtv_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=6 STAMP=828953636 file name=/app/oracle/export/SCRATCH/datafile/o1_mf_sysaux_95wkgwtm_.dbf

 

contents of Memory Script:

{

# set requested point in time

set until  scn 10849405521118;

# online the datafiles restored or switched

sql clone "alter database datafile  1 online";

sql clone "alter database datafile  4 online";

sql clone "alter database datafile  3 online";

# recover and open database read only

recover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX";

sql clone 'alter database open read only';

}

executing Memory Script

 

executing command: SET until clause

 

sql statement: alter database datafile  1 online

 

sql statement: alter database datafile  4 online

 

sql statement: alter database datafile  3 online

 

Starting recover at 2013-10-16:08:53:56

using channel ORA_AUX_DISK_1

 

starting media recovery

 

archived log for thread 1 with sequence 83 is already on disk as file +FRA/SCRATCH/ARCHIVELOG/2013_10_15/thread_1_seq_83.339.828864483

archived log for thread 1 with sequence 84 is already on disk as file +FRA/SCRATCH/ARCHIVELOG/2013_10_15/thread_1_seq_84.351.828873937

archived log for thread 1 with sequence 85 is already on disk as file +FRA/SCRATCH/ARCHIVELOG/2013_10_15/thread_1_seq_85.345.828885637

archived log for thread 1 with sequence 86 is already on disk as file +FRA/SCRATCH/ARCHIVELOG/2013_10_15/thread_1_seq_86.352.828885885

archived log file name=+FRA/SCRATCH/ARCHIVELOG/2013_10_15/thread_1_seq_83.339.828864483 thread=1 sequence=83

archived log file name=+FRA/SCRATCH/ARCHIVELOG/2013_10_15/thread_1_seq_84.351.828873937 thread=1 sequence=84

archived log file name=+FRA/SCRATCH/ARCHIVELOG/2013_10_15/thread_1_seq_85.345.828885637 thread=1 sequence=85

archived log file name=+FRA/SCRATCH/ARCHIVELOG/2013_10_15/thread_1_seq_86.352.828885885 thread=1 sequence=86

media recovery complete, elapsed time: 00:00:06

Finished recover at 2013-10-16:08:54:05

 

sql statement: alter database open read only

 

contents of Memory Script:

{

sql clone "create spfile from memory";

shutdown clone immediate;

startup clone nomount;

sql clone "alter system set  control_files =

''/app/oracle/export/SCRATCH/controlfile/o1_mf_95wkgmqq_.ctl'' comment=

''RMAN set'' scope=spfile";

shutdown clone immediate;

startup clone nomount;

# mount database

sql clone 'alter database mount clone database';

}

executing Memory Script

 

sql statement: create spfile from memory

 

database closed

database dismounted

Oracle instance shut down

 

connected to auxiliary database (not started)

Oracle instance started

 

Total System Global Area    1068937216 bytes

 

Fixed Size                     2296576 bytes

Variable Size                285213952 bytes

Database Buffers             775946240 bytes

Redo Buffers                   5480448 bytes

 

sql statement: alter system set  control_files =   ''/app/oracle/export/SCRATCH/controlfile/o1_mf_95wkgmqq_.ctl'' comment= ''RMAN set'' scope=spfile

 

Oracle instance shut down

 

connected to auxiliary database (not started)

Oracle instance started

 

Total System Global Area    1068937216 bytes

 

Fixed Size                     2296576 bytes

Variable Size                285213952 bytes

Database Buffers             775946240 bytes

Redo Buffers                   5480448 bytes

 

sql statement: alter database mount clone database

 

contents of Memory Script:

{

# set requested point in time

set until  scn 10849405521118;

# set destinations for recovery set and auxiliary set datafiles

set newname for datafile  7 to new;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile  7;

switch clone datafile all;

}

executing Memory Script

 

executing command: SET until clause

 

executing command: SET NEWNAME

 

Starting restore at 2013-10-16:08:54:41

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=15 device type=DISK

 

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00007 to /app/oracle/export/XZQD_PITR_SCRATCH/datafile/o1_mf_rman11gp_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece +FRA/SCRATCH/BACKUPSET/2013_10_15/nnndf0_tag20131015t080614_0.337.828864377

channel ORA_AUX_DISK_1: piece handle=+FRA/SCRATCH/BACKUPSET/2013_10_15/nnndf0_tag20131015t080614_0.337.828864377 tag=TAG20131015T080614

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15

Finished restore at 2013-10-16:08:54:57

 

datafile 7 switched to datafile copy

input datafile copy RECID=8 STAMP=828953697 file name=/app/oracle/export/XZQD_PITR_SCRATCH/datafile/o1_mf_rman11gp_95wkl2o8_.dbf

 

contents of Memory Script:

{

# set requested point in time

set until  scn 10849405521118;

# online the datafiles restored or switched

sql clone "alter database datafile  7 online";

# recover and open resetlogs

recover clone database tablespace  "RMAN11GP", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

executing Memory Script

 

executing command: SET until clause

 

sql statement: alter database datafile  7 online

 

Starting recover at 2013-10-16:08:54:58

using channel ORA_AUX_DISK_1

 

starting media recovery

 

archived log for thread 1 with sequence 83 is already on disk as file +FRA/SCRATCH/ARCHIVELOG/2013_10_15/thread_1_seq_83.339.828864483

archived log for thread 1 with sequence 84 is already on disk as file +FRA/SCRATCH/ARCHIVELOG/2013_10_15/thread_1_seq_84.351.828873937

archived log for thread 1 with sequence 85 is already on disk as file +FRA/SCRATCH/ARCHIVELOG/2013_10_15/thread_1_seq_85.345.828885637

archived log for thread 1 with sequence 86 is already on disk as file +FRA/SCRATCH/ARCHIVELOG/2013_10_15/thread_1_seq_86.352.828885885

archived log file name=+FRA/SCRATCH/ARCHIVELOG/2013_10_15/thread_1_seq_83.339.828864483 thread=1 sequence=83

archived log file name=+FRA/SCRATCH/ARCHIVELOG/2013_10_15/thread_1_seq_84.351.828873937 thread=1 sequence=84

archived log file name=+FRA/SCRATCH/ARCHIVELOG/2013_10_15/thread_1_seq_85.345.828885637 thread=1 sequence=85

archived log file name=+FRA/SCRATCH/ARCHIVELOG/2013_10_15/thread_1_seq_86.352.828885885 thread=1 sequence=86

media recovery complete, elapsed time: 00:00:03

Finished recover at 2013-10-16:08:55:02

 

database opened

 

contents of Memory Script:

{

# create directory for datapump import

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/app/oracle/export/''";

# create directory for datapump export

sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/app/oracle/export/''";

}

executing Memory Script

 

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/app/oracle/export/''

 

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/app/oracle/export/''

 

Performing export of tables...

EXPDP> Starting "SYS"."TSPITR_EXP_xzqD_ohzh":

EXPDP> Estimate in progress using BLOCKS method...

EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

EXPDP> Total estimation using BLOCKS method: 11 MB

EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

EXPDP> . . exported "TEST"."TESTRECOVER"                        8.540 MB   73744 rows

EXPDP> Master table "SYS"."TSPITR_EXP_xzqD_ohzh" successfully loaded/unloaded

EXPDP> ******************************************************************************

EXPDP> Dump file set for SYS.TSPITR_EXP_xzqD_ohzh is:

EXPDP>   /app/oracle/export/tspitr_xzqD_40404.dmp

EXPDP> Job "SYS"."TSPITR_EXP_xzqD_ohzh" successfully completed at Wed Oct 16 08:56:37 2013 elapsed 0 00:01:05

Export completed

 

 

contents of Memory Script:

{

# shutdown clone before import

shutdown clone abort

}

executing Memory Script

 

Oracle instance shut down

 

Performing import of tables...

IMPDP> Master table "SYS"."TSPITR_IMP_xzqD_yjpd" successfully loaded/unloaded

IMPDP> Starting "SYS"."TSPITR_IMP_xzqD_yjpd":

IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

IMPDP> . . imported "TEST"."TESTRECOVER1"                       8.540 MB   73744 rows

IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

IMPDP> Job "SYS"."TSPITR_IMP_xzqD_yjpd" successfully completed at Wed Oct 16 08:58:45 2013 elapsed 0 00:01:44

Import completed

 

 

Removing automatic instance

Automatic instance removed

auxiliary instance file /app/oracle/export/SCRATCH/datafile/o1_mf_temp_95wkjy0t_.tmp deleted

auxiliary instance file /app/oracle/export/XZQD_PITR_SCRATCH/onlinelog/o1_mf_3_95wklswp_.log deleted

auxiliary instance file /app/oracle/export/XZQD_PITR_SCRATCH/onlinelog/o1_mf_2_95wklrbg_.log deleted

auxiliary instance file /app/oracle/export/XZQD_PITR_SCRATCH/onlinelog/o1_mf_1_95wklpt2_.log deleted

auxiliary instance file /app/oracle/export/XZQD_PITR_SCRATCH/datafile/o1_mf_rman11gp_95wkl2o8_.dbf deleted

auxiliary instance file /app/oracle/export/SCRATCH/datafile/o1_mf_sysaux_95wkgwtm_.dbf deleted

auxiliary instance file /app/oracle/export/SCRATCH/datafile/o1_mf_undotbs1_95wkgwtv_.dbf deleted

auxiliary instance file /app/oracle/export/SCRATCH/datafile/o1_mf_system_95wkgwtq_.dbf deleted

auxiliary instance file /app/oracle/export/SCRATCH/controlfile/o1_mf_95wkgmqq_.ctl deleted

auxiliary instance file tspitr_xzqD_40404.dmp deleted

Finished recover at 2013-10-16:08:58:52

 

SQL> select count(*) from test.testrecover1;

 

COUNT(*)

----------

73744

 

 

OWNER      TABLE_NAME                     TABLESPACE_NAME

---------- ------------------------------ ------------------------------

TEST       TESTRECOVER1                   RMAN11GP

TEST       TESTRECOVER                    RMAN11GP
About these ads

2 Responses to “Recover table from RMAN in 12c”

  1. vkaminsky said

    Looks like really useful feature. I will be testing it as soon as any of my customers move to 12c. On the other hand, this makes Oracle even more like SQL server where the same functionality can be implemented in more elegant way – just ask Stuart, but SQL server is a different beast alltogether…

  2. […] about 12C RMAN new features. He covered recovery of a table from an RMAN backup and I proposed my unproven theory that it might be possible to use the functionality in 11GR2 (using the 12c binaries) and he suggested trying it in 11.2.0.4 – the story continues. He also […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 184 other followers

%d bloggers like this: