Re: Table recovery using Standby Database and RMAN

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 - 08:55:05 CST