Table recovery using Standby Database and RMAN

From: Kaptain524 <Kaptain524_at_zxmail.com>
Date: 22 Jan 2003 10:44:25 -0800
Message-ID: <3f6a20bd.0301221044.1e0fb7e5@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 Wed Jan 22 2003 - 12:44:25 CST