READ ONLY with backup optimization on

ENV:     11gR2 (11.2.0.2), Windows 2008 R2

Ran into the same problem that was posted on Hemant’s blog (http://hemantoracledba.blogspot.com/2010/05/read-only-tablespaces-and-backup.html) concerning an RMAN backup of READ ONLY tablespaces with backup optimization set to ON:

You can see the datafiles being backed up (using 11gR2 active duplicate feature):

backup as copy reuse
“D:\ORACLE\DATA\REPEMR95\LOGIC_IX.DB1”   datafile
14 auxiliary format
“D:\ORACLE\DATA\REPEMR95\LOGIC_IX.DB2”   datafile
15 auxiliary format
“D:\ORACLE\DATA\REPEMR95\LOGIC_RO.DB”   datafile
16 auxiliary format
“D:\ORACLE\DATA\REPEMR95\LOG_IXRO.DB”   datafile

channel c1: starting datafile copy
input datafile file number=00015 name=O:\ORACLE\DATA\EMR95\LOGIC_RO.DB
output file name=D:\ORACLE\DATA\REPEMR95\LOGIC_RO.DB tag=TAG20121008T170129
channel c1: datafile copy complete, elapsed time: 00:00:15
channel c1: starting datafile copy
input datafile file number=00016 name=O:\ORACLE\DATA\EMR95\LOG_IXRO.DB
output file name=D:\ORACLE\DATA\REPEMR95\LOG_IXRO.DB tag=TAG20121008T170129
channel c1: datafile copy complete, elapsed time: 00:00:15

but when it get’s to switching the datafiles, it skips right over the two read only tablespaces:

datafile 14 switched to datafile copy
input datafile copy RECID=390 STAMP=796171835 file name=D:\ORACLE\DATA\REPEMR95\LOGIC_IX.DB2
datafile 17 switched to datafile copy
input datafile copy RECID=391 STAMP=796171835 file name=D:\ORACLE\DATA\REPEMR95\LOG_TUT.DB

And of course, the RMAN error at the tail end of the log:

RMAN-00571: ======================================
RMAN-00569: ========== ERROR MESSAGE STACK FOLLOWS
RMAN-00571: ======================================
RMAN-03002: failure of Duplicate Db command at 10/08/2012 22:53:39
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06094: datafile 15 must be restored

Now this is a database where we are performing FULL level 0’s every night with the retention window set to ONE day and still having issues as seen above.  The way we got around the problem was to add the following to our windows batch file:

REM ### making tablespaces READ WRITE for backup ###
%ORACLE_HOME%\bin\rman nocatalog target sys/PASSWD@emr95 @%SCRIPT_DIR%\tblspace_RW.txt msglog %RMANCLONELOG%

which called out to:

run {
sql “alter tablespace LOGICIAN_DATA_RO read write”;
sql “alter tablespace LOGICIAN_INDEX_RO read write”;
}

and of course, reverse the action after the RMAN backup is done.

Advertisements

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