Martins Blog

Trying to explain complex things in simple terms

RMAN duplicate and read-only tablespaces

Posted by Martin Bach on April 4, 2012

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!

About these ads

4 Responses to “RMAN duplicate and read-only tablespaces”

  1. Anand said

    Thanks for sharing.Feel the same “little things I didn’t really know until now” :)

  2. Vaibhav said

    Hi Martin,

    Thank you for sharing the experience. Yes, if RMAN is not being used for the database cloning, the datafiles associated with the READ ONLY tablespaces need to be taken care of manually.

    In case if the source database is having an OFFLINE datafile, the Active Database Duplication option will drop the corresponding tablespace in the cloned database.

    I performed a small testcase as following on 11g R2 database.

    Source database
    —————————

    SYS @ ora11prd> select * from v$version;

    BANNER
    ————————————————————————-
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – Production
    PL/SQL Release 11.2.0.2.0 – Production
    CORE 11.2.0.2.0 Production
    TNS for 32-bit Windows: Version 11.2.0.2.0 – Production
    NLSRTL Version 11.2.0.2.0 – Production

    SYS @ ora11prd> alter table space test offline normal;
    Tablespace altered.

    SYS @ ora11prd> select file#,status from v$datafile;

    FILE# STATUS
    ———- ——-
    1 SYSTEM
    2 ONLINE
    3 ONLINE
    4 ONLINE
    5 OFFLINE <— datafile 5 is offline

    RMAN script
    ———–
    run{
    allocate channel d1 type disk;
    allocate channel d2 type disk;
    allocate channel d3 type disk;
    allocate auxiliary channel a1 type disk;
    allocate auxiliary channel a2 type disk;
    duplicate target database to oraclone from active database;
    }

    RMAN Output (snippet)
    ——————

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

    database opened
    Dropping offline and skipped tablespaces
    Executing: drop table space "TEST" including contents cascade constraints select file#,status from v$datafile;

    FILE# STATUS
    ———- ——-
    1 SYSTEM
    2 ONLINE
    3 ONLINE
    4 ONLINE

    Vaibhav

  3. Hi Marting

    It’s interesting post!

    Thanks

  4. [...] to tablespaces which are read-only on the source during an RMAN duplication (not for standby)? Martin Bach [...]

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 2,368 other followers

%d bloggers like this: