Re: RMAN in Windows: Issues With Point-In-Time Recovery

From: joel garry <joel-garry_at_home.com>
Date: Thu, 16 Sep 2010 17:40:46 -0700 (PDT)
Message-ID: <7620deed-bbd4-4caf-9e8a-a78c430ea972_at_l25g2000prn.googlegroups.com>

On Sep 16, 2:54 pm, "if_inves..._at_yahoo.com" <basis_consult..._at_hotmail.com> wrote:
> Hi,
>
> I would appreeciate ideas regarding the following; I am
> new to RMAN (Having used SAP backup tools until now).
>
> I am running 10g on Win2003. I have an RMAN script that backups the
> database and controlfiles. However, when
> I try a restore, I cannot recover to a point-in-time recovery taken
> soon after the backup (Point-in-time recoveries an hour or two
> afterwards are fine). It is a small database (5GB or so; It is on a VM
> system and I have a snapshot so I can recover that way, but I am
> testing RMAN)
>
> For example, I run the RMAN scipt at 4:20PM. It finishes
> about 4:26PM. The log indicates no problems. At 4:30PM I make a change
> in the database (Add a table).
>
> I then break the database (Rename a datafile, etc.) and
> run a point-in-time recovery script to 4:29PM. The RMAN log indicates
> that the database recovered.
>
> However, when I login to SQLPLUS and try to open the database,
> I am forced to issue the statement "Recover Database". The
> database recovers, I can open it, but my 4:30PM change
> is present.
>
> I am not sure why the RMAN recovery was insufficient, or why
> the database is rolled to 4:30PM+, not 4:29PM (Likely due
> to the SQL "recover database" command, I think).
>
> These are not the most elegent RMAN scripts, but they are
> as follows:
>
> The backup script (I don't think that I need to swich logfile a few
> times, but I wanted all archive changes saved)
> ***************************************************************
> spool log to <logfile>;
> configure retention policy to none;
> RUN
> {
>   CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '\\<drive>\<share>\admin
> \RMAN%d_1%U';
>   BACKUP as compressed backupset database;
>   SQL "alter system switch logfile";
>   SQL "alter system switch logfile";
>   SQL "alter system switch logfile";
>   SQL "alter system switch logfile";
>   backup as compressed backupset archivelog from sequence 327;   (The
> archive sequence numbers are a lot higher  than
> 327)}
>
> exit;
> spool off;
>
> ***************************************************************************
> Recovery script:
>
> Spool log to <recovery log_file>;
> connect target;
> startup mount;
> RUN
>  {
>   configure CHANNEL DEVICE TYPE DISK FORMAT '\\<drive>\<share>\admin
> \RMANCSA_11TLO24F6_1_1';
>   restore DATABASE;
>   configure channel device type disk format '\\<Drive>\\<share>\admin
> \RMANCSA_11VLO2535_1_1';
>   restore archivelog from sequence 327;
>   set until time "to_date('2010 sep 16 16:29','yyyy mon dd hh24:mi')";
>   recover database;
>  }
> exit;
> spool off;
>
> Thanks,
> QZ

The association between timestamp and scn is in a table called something like smon_scn_time. The granularity is 5 minutes, I think. You might consider figuring out which SCN to restore until, rather than a time too close to where you want, and 10g has a function to get it within 3 seconds. See http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9434169398289

Some things are strange for historical reasons.

jg

--
_at_home.com is bogus.
http://www.bbc.co.uk/news/technology-11313194

Received on Thu Sep 16 2010 - 19:40:46 CDT