Re: Table recovery using Standby Database and RMAN

Thanks for the reply.

Okay, so it sounds like you are telling me I should re-create the standby database every time I need to do this kind of recovery?? I already follow all the same steps in creating the standby database, including the "backup current controlfile for standby." I guess this solution would work, but it sounds more like a workaround. As I understand, the actual purpose of a standby database is to have a mirrored copy of the live database. So I would naturally expect it to be able to restore and recover as if it were the live database. But perhaps that is not how it was designed... They made the TSPITR for that, which is virtually the same exact thing. If that is the case, someone should tell Oracle to add such a feature to the standby database. It really isn't that complicated and would make things much easier.
But I still believe that the functionality is already there and it just hasn't been documented well enough. The whole point of using a standby database was so that I could restore and recover it as if it were the main database. If I'm not going to do that, why should I use a standby database instead of a regular duplicate instance? But if the regular duplicate doesn't support point-in-time recovery as it is created, I would still need to use a standby database.

Anyway, at least I can accomplish the task, even if it isn't very efficient. So after I rebuild the standby database with the DORECOVER option so it is in the state from which I want to export/import the desired table(s), I still need to be able to export from it. The documentation says I need to use a locally managed tablespace because it is a read-only database.

So how do I set up the LMT to export from the standby database? The documentation says to simply execute a CREATE TEMPORARY TABLESPACE command. Will this work on a read-only database and allow the export to work like normal? Or is there anything else I need to do because it is a read-only database?

Thanks,
Kaptain524

empete2000_at_yahoo.com (Pete's) wrote in message news:<6724a51f.0301230655.66e1680d_at_posting.google.com>...
> Kaptain:
>
> Ok, I ran into same thing. Basically you need to duplicate the
> database via RMAN.
>
> First, you need to have the following statement in your backup script:
> backup current controlfile for standby;
> If you do not have this, RMAN will never be able to recover the
> database to be a standby database. The control file for the standby
> is slightly different than that of your production db, why, I do not
> know exactly.
>
> Next, for you standby database's init.ora, you should use the
> following init.ora parameters:
> db_file_name_convert
> log_file_name_convert
> Having those two parameters defined in the init.ora would save you
> from having to do the "set newname", plus, what happens when you add a
> datafile, you have to update your script, so, I'd use the parameters.
>
> Finally, the script you have written for the restore will not work as
> you have found that when RMAN is finished, it's going to try to do a
> reset logs. Your RMAN script should look like the following, note
> that before running the following, your standby needs to be in nomount
> stage:
>
>
> connect catalog rman/rman_at_catalog;
> connect target sys/change_on_install_at_PROD;
> connnet auxiliary sys/change_on_install_at_STANDBY_PROD;
>
> run{
> allocate auxiliary channel t1 type 'SBT_TAPE';
> set until time "to_date('1/20/2003','MM/DD/YYYY')";
> DUPLICATE TARGET DATABASE FOR STANDBY
> NOFILENAMECHECK;
> }
> This will then bring the db into mount stage. From there, you should
> be able to do what you will with it, open it readonly and do your
> export.
>
> HTH,
> PETE's
>
> Kaptain524_at_zxmail.com (Kaptain524) wrote in message news:<3f6a20bd.0301221044.1e0fb7e5_at_posting.google.com>...
> > Hello,
> >
> > I am trying to create a series of scripts to automate a single table
> > recovery from RMAN backups of an Oracle 9.2 database. Since I am
> > unable to use RMAN's built-in tablespace point in time recovery (needs
> > Enterprise Ed), I am trying to manually achieve the same sort of
> > thing. I have tried different ways of setting up my auxiliary
> > database, and it seems that my best chance for success is to use a
> > Standby Database.
> > This is because RMAN just can't restore from a particular backup to
> > just any database. The DB_NAME param in the init.ora needs to match,
> > as well as the internal name in the control file. So the only other
> > option would be to manually perform a tweaked version of what RMAN
> > does automatically when you create the standby database using the
> > DUPLICATE command. So for now, I'm going to assume that I can use the
> > built-in standby database creation method.
> >
> > Basically, I need to restore from backups to a different database from
> > the original so that I can recover particular tables without affecting
> > the rest of the database. I am able to restore and recover the
> > standby database to a point in time. But then I am unable to open it
> > anymore since something gets messed up. Here is the recovery script
> > and the RMAN warning I am getting:
> >
> > D:\rman target "sys/resd_at_resd" catalog "rman/rman_at_rcat"
> >
> > RMAN> run {
> > 2> set until time "to_date('1/20/2003','MM/DD/YYYY')";
> > 3>
> > 4> set newname for datafile 1 to "D:\ORACLE\ORADATA\RESD\SYSTEM.DBF";
> > ...
> > <a lot of set newname commands>
> > ...
> > 31> set newname for datafile 28 to "D:\RESD\LAST.DBF";
> > 32>
> > 33> restore database;
> > 34> switch datafile all;
> > 35> recover database;
> > 36> }
> >
> > Oracle Error:
> > ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get
> > error below
> > ORA-01152: file 1 was not restored from a sufficiently old backup
> > ORA-01110: data file 1: 'D:\ORACLE\ORADATA\RESD\SYSTEM.DBF'
> >
> > Am I doing something wrong here? I have to recover the whole
> > database, since it tells me to when I try to recover anything else
> > (b/c of the controlfile). And it won't recover datafiles that haven't
> > been restored. Without the switch command, it tries to recover the
> > datafiles for the original database. This looks like the only
> > possible RMAN script to recover the standby database to the desired
> > point in time.
> > I really need to fix this problem since I can't even open the database
> > after a recovery. And as I understand it, the database has to be open
> > for an export.
> >
> > Assuming I can resolve the recovery issue, the main difficulty is how
> > the standby database is read only. This makes it very difficult to
> > export tables from it so I can import them to the main database. But
> > according to the documentation, it is quite possible. I just need to
> > know how to point the user temp tablespace to a "temporary, locally
> > managed tablespace." But how do I do that?
> >
> > Any input on how to recover and export from a standby database,
> > especially example scripts, would be very helpful :)
> >
> > Thanks,
> > Kaptain524
Received on Thu Jan 23 2003 - 15:07:52 CST