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

From: if_investor_at_yahoo.com <basis_consultant_at_hotmail.com>
Date: Thu, 16 Sep 2010 14:54:28 -0700 (PDT)
Message-ID: <cd622456-19a0-4911-802c-7eea352ce91a_at_t3g2000vbb.googlegroups.com>

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 Received on Thu Sep 16 2010 - 16:54:28 CDT