RMAN duplicate and read-only tablespaces

This one is to be filed under the “little things I didn’t really know until now” section: RMAN duplicate. Be honest to yourself: would you have known the answer to this question: what happens to tablespaces which are read-only on the source during an RMAN duplication (not for standby)?

Some background

I have started my career as a DBA on Oracle 8.1.7.0, and at the time not everyone fully embraced RMAN. OK, RMAN made it really hard at the time to fall in love with it. So when we “cloned” our production database to development, the following steps were followed:

1. For each tablespace in the database, put it in hot backup mode and copy the files across to the destination location, then end the hot backup mode.
2. Copy any archived logs generated in during the hot backup mode to DEV.
3. Create a backup controlfile to trace.
4. Edit the create controlfile statement (Set #2-resetlogs) and extract the “create controlfile” statement, adding the new database name in the “set” clause.
5. Start DEV in nomount mode.
6. Run create controlfile statement.
7. Recover the database until cancel using the backup controlfile, applying all archived logs.
8. Open the database with the resetlogs clause.

That worked ok, unless you had a data file offline or read only.

The test

I recently had interesting lunch discussions with my friends Jerry and Martin, because I tried that again in relation with clonedb testing. The clonedb procedure for some reason didn’t like a read-only datafile so I wanted to dig deeper.

Obviously times have moved on and I reproduced the 8i case with 11.2.0.3. To prove the point, I defined USERS tablespace on the source as read only. That’s a common data lifecycle measure and I was wondering what effect that had.

SQL> select tablespace_name,status from dba_tablespaces
  2  where tablespace_name = 'USERS';
TABLESPACE_NAME                STATUS
------------------------------ ---------
USERS                          READ ONLY

Without adding further tension to it, there was no difference in the procedure. Here is the trace file, generated using the “alter database backup controlfile to trace as ‘/tmp/martin.sql’ syntax. Nice to be able to specify where the trace goes-otherwise you’d have to dig it out in the diagnostic destination. Have a look:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE set DATABASE DEV RESETLOGS ARCHIVELOG
 MAXLOGFILES 21
 MAXLOGMEMBERS 3
 MAXDATAFILES 300
 MAXINSTANCES 1
 MAXLOGHISTORY 2920
LOGFILE
 GROUP 1 (
 '/oradata/DEV/redo/redo01.dbf'
 ) SIZE 100M BLOCKSIZE 512,
 GROUP 2 (
 '/oradata/DEV/redo/redo02.dbf'
 ) SIZE 100M BLOCKSIZE 512,
 GROUP 3 (
 '/oradata/DEV/redo/_redo03.dbf'
 ) SIZE 100M BLOCKSIZE 512,
 GROUP 4 (
 '/oradata/DEV/redo/redo04.dbf',
 ) SIZE 100M BLOCKSIZE 512
DATAFILE
 '/oradata/DEV/data/SYSTEM01.dbf',
 '/oradata/DEV/data/SYSAUX01.dbf',
 '/oradata/DEV/data/UNDO01.dbf',
 '/oradata/DEV/data/SOE.dbf'
CHARACTER SET AL32UTF8
;

No surprises here. However-where is the USERS data file gone? Scroll down in the script:

-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

-- Files in read-only tablespaces are now named.
ALTER DATABASE RENAME FILE 'MISSING00005'
 TO '/oradata/DEV/data/USERS01.dbf';

-- Online the files in read-only tablespaces.
ALTER TABLESPACE "USERS" ONLINE;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.

Aha! So in order to finish the procedure, you have to “online” the file, and if memory serves me right these steps weren’t in the 8i script (but I may be mistaken). After opening the database with the resetlogs option the datafile was indeed “missing”, and I had to run through the steps shown above before I could select from it.

Can RMAN do better?

I was curious how RMAN duplication handles a read-only tablespace. The command executed was simple enough:

$ rman target sys/xxx@source auxiliary sys/xxx@dest

I spare you the RMAN output, it’s verbose enough even without enabling debug mode to find out what was happening. There was no surprise (to me):

contents of Memory Script:
{
 set newname for datafile 1 to
 "/oradata/DEV/data/SYSTEM01.dbf";
 set newname for datafile 2 to
 "/oradata/DEV/data/SYSAUX01.dbf";
 set newname for datafile 3 to
 "/oradata/DEV/data/UNDO01.dbf";
 set newname for datafile 4 to
 "/oradata/DEV/data/SOE.dbf";
 set newname for datafile 5 to
 "/oradata/DEV/data/USERS01.dbf";
...

Except when I saw this:

contents of Memory Script:
{
 Alter clone database open resetlogs;
}
executing Memory Script

database opened

contents of Memory Script:
{
 catalog clone datafilecopy "/oradata/DEV/data/USERS01.dbf";
 switch clone datafile 5 to datafilecopy
 "/oradata/DEV/data/USERS01.dbf";
 #online the readonly tablespace
 sql clone "alter tablespace USERS online";
}
executing Memory Script

cataloged datafile copy
datafile copy file name=/oradata/DEV/data/USERS01.dbf RECID=6 STAMP=779713734

datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=779713734 file name=/oradata/DEV/data/USERS01.dbf

sql statement: alter tablespace USERS online
Finished Duplicate Db at 04-APR-12

RMAN> exit

So long story reduced to the minimum: the procedure seems to be enhanced in the current release! Read only tablespaces stay read only. There is one slight caveat: if you have an “OFFLINE” datafile in the source, it will be “ONLINE” in the clone.

SQL> connect sys/xxx@source
Connected.
SQL> select name,status from v$datafile;

NAME                                                        STATUS
----------------------------------------------------------- -------
/oradata/PROD/data/SYSTEM01.dbf                             SYSTEM
/oradata/PROD/data/SYSAUX01.dbf                             ONLINE
/oradata/PROD/data/UNDO01.dbf                               ONLINE
/oradata/PROD/data/SOE.dbf                                  ONLINE
/oradata/PROD/data/USERS01.dbf                              OFFLINE

SQL> connect sys/xxx@clone
Connected.
SQL> select name,status from v$datafile;

NAME                                                       STATUS
---------------------------------------------------------- -------
/oradata/DEV/data/SYSTEM01.dbf                             SYSTEM
/oradata/DEV/data/SYSAUX01.dbf                             ONLINE
/oradata/DEV/data/UNDO01.dbf                               ONLINE
/oradata/DEV/data/SOE.dbf                                  ONLINE
/oradata/DEV/data/USERS01.dbf                              ONLINE

Interesting!