RMAN Duplicate Command and Skip Tablespaces

Oracle 9.2.0.6, AIX 5.  

I ran the following RMAN script to create a new database for ourselves:  

$ cat dup_FAST.rcv

run {

set until time "7-JUL-2005 06:00:00";

allocate auxiliary channel ch1 type disk;

allocate auxiliary channel ch2 type disk;

duplicate target database to 'FAST' skip tablespace development,development_big,help,indexes,indexes_big,evisions,mercury,ha rvestblob,harvestindex,harvestmeta,tools,users,xdb;

release channel ch1;

release channel ch2;

}  

Most of the script ran fine. Database files were restored, appropriate data files were skipped, archived logs were restored and applied to data files, the database was stopped, then opened with resetlogs. This was all successful. Then RMAN issued commands to drop skipped tablespaces, including contents. The drop of the first tablespace, XDB, was successful. However, the drop of the second tablespaces, USERS, failed with an error as follows:  

executing script: Memory Script  

sql statement: drop tablespace XDB including contents  

sql statement: drop tablespace USERS including contents

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 07/07/2005 08:37:16

RMAN-03015: error occurred in stored script Memory Script

RMAN-03009: failure of sql command on clone_default channel at 07/07/2005 08:37:16

RMAN-11003: failure during parse/execution of SQL statement: drop tablespace USERS including contents

ORA-00604: error occurred at recursive SQL level 1

ORA-02429: cannot drop index used for enforcement of unique/primary key  

RMAN> **end-of-file**  

Later, I manually ran all the RMAN commands to drop tablespaces (eg. sql clone "drop tablespace DEVELOPMENT including contents";). This worked, but I discovered that sequence of dropping tablespaces is important. When I dropped tablespaces in the sequence RMAN wrote to the screen (reverse alphabetical) I sometimes encountered an error similar to the one above. But if I dropped index tablespaces first, then I could successfully drop the other tablespaces. Is this normal RMAN behaviour? Is there a workaround to this RMAN limitation? In my situation, RMAN would have encountered this problem even if USERS was the last tablespace to be dropped - because a table in SYSTEM had an index in USERS (bad, I know). But would RMAN have crashed anyway because of the sequence in which it drops tablespaces? Is there a way around this? Does Oracle consider this to be a RMAN bug? I have manually issued RMAN commands to drop tablespaces, including contents, but have I missed any important RMAN steps?  

Thanks so much for your help ...      

Sam Bootsma

George Brown College

sbootsma_at_gbrownc.on.ca <mailto:sbootsma_at_gbrownc.on.ca>

416-415-5000 x4933  

--
http://www.freelists.org/webpage/oracle-l