RE: Restore Readonly Tablespace Via Partial Clone

  • From: "Ken Naim" <kennaim@xxxxxxxxx>
  • To: <orcl@xxxxxxxxxxx>
  • Date: Wed, 3 May 2006 22:14:25 -0500

You are missing the undo tablespace, oracle relies heavily on it and will
behave oddly if you do not restore it. 
Ken Naim
 
  _____  

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Bob
Sent: Wednesday, May 03, 2006 7:09 PM
Cc: oracle-l
Subject: Re: Restore Readonly Tablespace Via Partial Clone
 
Hi - my partial clone is to recover 1 table from a multi terabyte database
when all I have is a cold backup:

Shutdown cleanly
Perform cold backup
(weeks pass by....)
Request datafiles for system, sysaux, and all datafiles from the needed
tablespace
Create init.ora
Start instance
Create control file below
alter database open resetlogs;
export the table

This method always works- but I need a different technique for  the readonly
tablespace

 I've done a test today on a dev db by creating a tablespace and making it
readonly, then run backup control file to trace. The output is saying (see
below) that I can open the database and rename the "MISSINGXXX" file then
put the tablespace online. Interesting note the MISSING000NN  NN is the
file# of the datafile.

But doing controlfile technique below corrupts the system and sysaux, like
so....
create control file from below syntax, then

alter database open resetlogs
.... waiting
"disconnection forced, host def doesnt't exist"

Which, from my experience is Oracles way of saying "your database is toast"
 
Tonight I plan to test this on a tiny windows db at home and see what I can
do. I want to do a clone with just system and sysaux and see if the database
will open. 
Previously, bringing in undo is a no-no as I get the dreaded "disconnection
forced, host def doesnt't exist" which indicates "failed recovery""

 I'm open for any suggestions, and will post my findings

Thanks

Bob

 STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "newt" RESETLOGS  NOARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 3
MAXDATAFILES 254
MAXINSTANCES 1
MAXLOGHISTORY 909
LOGFILE
  GROUP 1 ('/sourcedb001/oradata/sourcedb/redo101.log') SIZE 100M,
  GROUP 2 ('/sourcedb001/oradata/sourcedb/redo201.log') SIZE 100M
DATAFILE
  '/sourcedb001/oradata/sourcedb/system01.dbf',
  '/sourcedb001/oradata/sourcedb/sysaux01.dbf'
*readonly files do not appear in this list*
CHARACTER SET US7ASCII
;

 # 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 'MISSING00017' TO
'/sourcedb001/oradata/sourcedb/readonly_01.dbf';

 # Online the files in read-only tablespaces.
ALTER TABLESPACE "READONLY" ONLINE;
Andrey Kriushin wrote: 
Bob wrote: 

Hi, Im running behind, but my experience is , including undo and opening the
db via partial clone, will corrupt the datafiles .  Just system and sysaux.
bob
As far as I know, undo doesn't corrupt datafiles, but instead participates
in recovering them ;-). Of course, in your particular case with cold backup
after clean (it that true?) shutdown there are no dead transactions which
need to be recovered.

BTW, probably I missed something: what do you mean by "partial clone"? Is
that some new procedure in 10g which I'm not aware of? Some automated step
in TSPITR? Or is it just good old "take some datafiles, switch the others
offline or not mention them when creating controlfile"?

- Andrey




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

Other related posts: