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:
- 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.
- Copy any archived logs generated in during the hot backup mode to DEV.
- Create a backup controlfile to trace.
- Edit the create controlfile statement (Set #2-resetlogs) and extract the “create controlfile” statement, adding the new database name in the “set” clause.
- Start DEV in nomount mode.
- Run create controlfile statement.
- Recover the database until cancel using the backup controlfile, applying all archived logs.
- 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!
Responses
Thanks for sharing.Feel the same “little things I didn’t really know until now” :)
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
Hi Marting
It’s interesting post!
Thanks
[…] to tablespaces which are read-only on the source during an RMAN duplication (not for standby)? Martin Bach […]