Re: Table recovery using Standby Database and RMAN

Okay, that sounds like exactly what I am trying to do. But it does sound more complex than just using the DUPLICATE FOR STANDBY command every time, especially if I have to shutdown the main database to do it. It also sounds like I would still have to start from scratch for each recovery, like I would be doing anyway with the standby database.

But I still might want to consider it as a better solution since it would only involve the datafiles required for recovery. Only restoring 5 datafiles instead of 28 sounds like it's worth some extra effort, especially when some of them can have very large tables that aren't even being recovered.

Your solution sounds similar to an example in the RMAN User's Guide that uses a temporary auxiliary instance to recover a tablespace for export to whatever. But I was unable to make it work. RMAN did not like how I was trying to recover only a few datafiles when using a controlfile that was restored from backup. But that is exactly what the example was doing... The example also seemed like it was messed up because the restored controlfile still pointed to the original datafiles instead of the auxiliary ones, despite the SET NEWNAME commands. When trying to recover, RMAN told me I had to use a switch command, which the example did not mention. And the recovery still failed when I tried to recover the whole database to the auxiliary instance because of the archivelogs. Perhaps the example I was following did not clearly explain certain details for what I am trying to do. Your solution creates the controlfile differently and might fix these problems.

So I want to make sure I understand this procedure you have described.  I have listed some questions about it that would probably be best answered with a step-by-step guide to performing this type of recovery.

1. will I be able to run the clone database as a stand alone instance in the same ORACLE_HOME on the same machine as the original database?
2. will RMAN be able to restore and recover the clone as if it were the main database?
3. what will the pfile look like for the clone instance? will it have the DB_FILE_NAME_CONVERT params? should the DB_NAME param be the same as the main database or different? RMAN will not be able to restore to it if it has a different DB_NAME.
4. where is the script that is generated by 'ALTER DATABASE BACKUP CONTROLFILE TO TRACE'? all I got from SQLplus was "Database Altered." is there a certain directory that it places this script in?
5. why should I manually copy anything if RMAN will do the restore and recover? I must be able to use RMAN to restore the datafiles from the backups.

If your suggested solution does not involve RMAN, I am afraid that it will not be compatible with the rest of my backup/restore system. We already have some "home brew" scripts that can do everything we need without using RMAN at all. But they are difficult to configure and maintain. In fact, I can't even get them to work on our development system because they are outdated and poorly documented. So to increase portability, and make things easier to use in general, I am writing a new backup/restore system that uses RMAN for everything. Since I believe this was already made clear by my earlier questions, I have been under the assumption that your soultion does involve RMAN.

Thanks,
Kaptain524

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<T2AZ9.35314$jM5.90138_at_newsfeeds.bigpond.com>...
> 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 Wed Jan 29 2003 - 15:11:43 CST