Re: Table recovery using Standby Database and RMAN

I think I missed the earlier part of the thread when you were explaining why you are doing all of this in the first place.

But from what I have read, I wouldn't call this a standby. All that is happening (as far as I can tell) is that you are cloning your database in order to be able to point-in-time recover it, and export from it back into production. It just happens that you are using RMAN to create the clone.

And whilst, if it wasn't 5.40am, and if I wasn't just dashing off to work, I could think about it and maybe get RMAN to do what you are after, it strikes me that it would be easier to clone the database manually. And in that case you definitely wouldn't need to clone every single data file just to recover one of them.

You'd do an alter database backup controlfile to trace. You'd edit that script to point to the clone's location, and to remove all reference to datafiles which you aren't interested in recovering, except for SYSTEM and UNDO (or rollback) and TEMP. You'd also change the line 'recover database' to 'recover database until...' . You'd copy those three datafiles to the clone location. You'd copy the datafile(s) you wanted to p-i-t-recover to the clone location. You might also need to copy the online redo logs to the clone location (depending on how close to current time you wanted to p-i-t-recover). That would of course require the original database to be down for the copy.

Then at the clone location, you'd run the create controlfile script the backup to trace had generated for you.

I don't know whether that would be easier, or whether the downtime would be unacceptable. But it's worked for me in the past.

Regards
HJR "Kaptain524" <Kaptain524_at_zxmail.com> wrote in message news:3f6a20bd.0301281010.6ef0f074_at_posting.google.com...
> Okay, exporting from the standby database is no longer a problem.
> Thanks for your replies concerning that matter.
>
> Now my only remaining concern is how to use RMAN to restore and
> recover to an auxiliary (standby?) database so I can export just the
> tables that I need. It would be nice if it didn't have to restore and
> recover the whole database. But I can't even do that. Currently, the
> only way I can meet the requirements is to totally rebuild the standby
> database for every table recovery operation. RMAN won't let me
> restore to anything but the original target database or a standby. If
> I just make a "point-in-time" duplicate, it still has to be a standby
> database. RMAN only allows the DORECOVER option when duplicating FOR
> STANDBY. Maybe this is the only way that it can be done apart from
> the tablespace point in time recovery feature that requires enterprise
> edition. If anyone knows more about this, I would appreciate any
> input.
>
> Thanks,
> Kaptain524
>
>
> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
news:<mLiY9.32396$jM5.82628_at_newsfeeds.bigpond.com>...
> > Kaptain524 wrote:
> > > Okay, I tried making the LMT in the standby database, but it didn't
> > > work. Here is the error I got:
> > >
> > > SQL> create temporary tablespace xtemp tempfile 'd:\xtemp.dbf'
> > > 2 size 20M reuse extent management local uniform size 16M;
> > > create temporary tablespace xtemp tempfile 'd:\xtemp.dbf'
> > > *
> > > ERROR at line 1:
> > > ORA-00604: error occurred at recursive SQL level 1
> > > ORA-16000: database open for read-only access
> > >
> > > I basically used the example right out of the documentation.
> >
> > Come on. Think about it. Your database is open read-only, and what does
> > a 'create anything' DDL get converted into? Correct: DML on the data
> > dictionary. (In this specific case, it will be inserts into TS$, amongst
> > others) Reckon you are going to be allowed to do DML on SYSTEM tables
> > when the thing is in a read-only state?
> >
> > > According to the documentation, the "size" and "extend management
> > > local" and "uniform size" parts are totally optional. It claims that
> > > because it is a temporary tablespace, it is automatically local and
> > > uniform. Anyway, it didn't include any considerations for a read-only
> > > database. All it said was that it can be done.
> > > Has anyone successfully created the temp tablespace for a read-only
> > > database? What do I need to do to make it work?
> > >
> >
> > Make it read-write. You can't expect DDL commands to work when the
> > database is read-only. Any of them.
> >
> > It's the subsequent USE of the temporary tablespace that is permitted in
> > read-only databases, precisely because the extent allocations that
> > happen within them are handled by the tablespace's bitmap, not data
> > dictionary tables UET$ and FET$.
> >
> > You're supposed to be using this sort of temporary tablespace anyway in
> > 8i and 9i, because they perform a bit more efficiently, and multiple
> > simultaneous sorts won't swamp the one set of data dictionary tables as
> > they start. So when you set up your standby database, the tempfile
> > temporary tablespace is assumed to be already part of that which you
> > need to clone.
> >
> > Regards
> > HJR
> >
> >
> > > It would be nice if I could just use a regular duplicate instance.
> > > But it appears that I have to use a standby database to make RMAN
> > > happy. I cannot recover a duplicate to a point in time, not even when
> > > I create it. RMAN will only perform recovery on a standby database.
> > > So I have to use a standby. And currently the only way that I can
> > > successfully recover it to a specific point in time is to fully
> > > rebuild it every time. I really don't think this is the only way.
> > > There must be something that I am missing to get the recovery to work
> > > on the standby database without using the DUPLICATE command. Even so,
> > > it will suffice to accomplish the required task. At this point, I
> > > mainly want to be able to export from the standby database.
> > >
> > > Thanks,
> > > Kaptain524
Received on Tue Jan 28 2003 - 12:43:30 CST