Re: Table recovery using Standby Database and RMAN

"Kaptain524" <Kaptain524_at_zxmail.com> wrote in message news:3f6a20bd.0301231307.4c70e9fb_at_posting.google.com...
> 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?
>

I haven't followed this thread, I'm afraid. But on the specific issue of needing to use LMT...

What they're getting at is this. Since 8i, you can open a standby database for read only purposes. But reading data usually means you want to sort the data, or order it, or aggregate it... all stuff which requires work to take place in the PGA before the results are returned to you. But if the PGA runs out of space (sort_area_size not big enough), then Oracle normally swaps the contents down to the TEMP tablespace, and processes new results, swaps, processes, swaps, processes and so on... and then eventually it merges each of the partial swap runs on TEMP into a single, fully processed, set of results which are then returned to you.

But this is a read-only database... so how do I allow you to write down to TEMP in this way when the thing is read-only?

Well, the workaround for that was to use the locally-managed tempfile sort of temporary tablespace. That way, the extent allocations that happen when a PGA swaps to TEMP are recorded only in the bitmap at the beginning of the tablespace itself -there's no need to modify UET$ and FET$ in the data dictionary (which can't be modified, of course, because this is a read-only database).

So instead of

Create tablespace TEMP datafile 'X/Y.dbf' size 1M temporary

you are supposed to do:

create temporary tablespace TEMP tempfile 'X/Y.dbf' size 1m extent management local
uniform size A;

(And "A" should be a multiple of your sort_area_size).

With a tempfile temporary tablespace, sorts are now possible even though the database is read only, and any tools or utilities which need to 'massage' data before extracting it can also function normally.

Now as I say, though I haven't been following the thread, it seems to me that the rationale for having a read-only database is going out of the window if I take your comments in this particular post at face value. It sound like you are going to clone your live system, perform a point in time recovery on the clone, and use export to extract data, and import to put it back into the production system. And you seem to be saying in this post that such a clone would be disposable... "you are telling me I should re-create the standby database every time I need to do this kind of recovery".

Well, if it's disposable, there's no need for it to be read only, is there? Read only was invented so that you get at the data in a standby database, but there was zero possibility of users causing logical inconsistencies between production and the standby (such as updating a salary for HJR to $3.50 in production and then updating the same person's salary to $4000000 in standby. I wish.) If you are going to dispose of the 'standby' after doing the export/import routine, there's no reason to keep that restriction.

Just a thought.

Regards
HJR
> 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:24:05 CST