Hi - here is the output of my research. undo is *NOT* needed for this
type of "partial clone".
Keep in mind my reason for a "partial" clone is that I'm only interested
in 1 table, as soon as I get the table exported, the clone is deleted.
So, for example- my undo can be up to 60GB and my datafiles for the
tablespace 200GB and if undo is not needed why try to find space for it.?
However, my claim that bringing in undo will "corrupt" the datafiles was
incorrect. What *will* cause trouble is if you leave the reference to
the undo tablespace in the init.ora file. My test case below worked
perfectly with the offline tablespace. The output shows what was done.
We can see undo and users tablespace are "missing" but that doesnt’t
stop me from getting the table.
Why it failed in another environment? - I’m not sure. I wasn’t doing the
job.
So, here is a technique to deal with read-only tablespaces and restores.
HTH
bob
INIT.ORA db_name=partial db_block_size=8192 compatible='10.2.0.1.0' control_files='J:\thrash\clone\control01.ctl' db_recovery_file_dest_size=2147483648 sga_target=289406976 user_dump_dest=J:\thrash\clone\logs audit_file_dest=J:\thrash\clone\logs background_dump_dest=J:\thrash\clone\logs core_dump_dest=J:\thrash\clone\logs db_recovery_file_dest=J:\thrash\clone\logs #############################################
startup nomount pfile=J:\thrash\clone\initTHRASH.ora
CREATE CONTROLFILE SET DATABASE "PARTIAL" RESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 'J:\thrash\clone\REDO01.LOG' SIZE 5M, GROUP 2 'J:\thrash\clone\REDO02.LOG' SIZE 5M DATAFILE 'J:\thrash\clone\SYSTEM01.DBF', 'J:\thrash\clone\SYSAUX01.DBF' CHARACTER SET WE8MSWIN1252 ;
> controlfile created
>alter database open resetlogs;
>database open
######################################## > select file#, name from v$datafile;
1 J:\THRASH\CLONE\SYSTEM01.DBF 2 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\MISSING00002 <-- undo 3 J:\THRASH\CLONE\SYSAUX01.DBF 4 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\MISSING00004 <-- users 5 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\MISSING00005 6 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\MISSING00006 7 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\MISSING00007
NOTE the BOB datafiles are actually in the J:\thrash\clone\ directory
> ALTER TABLESPACE "BOB" ONLINE;
Tablespace altered.
> select count(*) from bob.mytab;
COUNT(*) ---------- 147543
--
"Oracle error messages being what they are, do not highlight the correct cause of fault, but will identify some other error located close to where the real fault lies."
-- //www.freelists.org/webpage/oracle-l