Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Table recovery using Standby Database and RMAN

Re: Table recovery using Standby Database and RMAN

From: Kaptain524 <Kaptain524_at_zxmail.com>
Date: 24 Jan 2003 11:40:38 -0800
Message-ID: <3f6a20bd.0301241140.4e7712cf@posting.google.com>


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. 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?

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

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<5WYX9.31589$jM5.80687_at_newsfeeds.bigpond.com>...

> 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
>
>
Received on Fri Jan 24 2003 - 13:40:38 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US