Re: Restore Readonly Tablespace Via Partial Clone

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 DATABASE RENAME FILE 'MISSING00005' TO 'J:\thrash\clone\BOB_01.DBF';
Database altered.

> ALTER DATABASE RENAME FILE 'MISSING00006' TO 'J:\thrash\clone\BOB_02.DBF';
Database altered.

> ALTER DATABASE RENAME FILE 'MISSING00007' TO 'J:\thrash\clone\BOB_03.DBF';
Database altered.

> 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."

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


Other related posts: