RE: RMAN backup with KEEP option not restorable

From: Chitale, Hemant K <Hemant-K.Chitale_at_sc.com>
Date: Wed, 11 Jun 2014 14:23:06 +0800
Message-ID: <0BDF2A25A09ADD40908745EEFC0A0FB601DD18B2_at_HKMGAXMB103A.zone1.scb.net>

Had the SYSTEM tablespace been put in BEGIN BACKUP mode on Friday 6th and not taken out of BACKUP mode since then ? Query V$BACKUP.

Hemant K Chitale

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of De DBA Sent: Wednesday, June 11, 2014 10:26 AM
To: oracle-l_at_freelists.org
Subject: RMAN backup with KEEP option not restorable

G'day.

Yesterday I ran into an unexpected problem, which rather shook my confidence in RMAN archival backups. I had to create a standby database on a new server, which I decided to create from a backup without connection to the target or catalog (there are compelling reasons for this approach). Not wanting to impede on the normal backup schedule, I create an archival backup, using the KEEP option. According the documentation (and the RMAN output) this should be self-contained and allow me to restore the database without the need for any other input. I already know that the duplicate will need the current redo log as well, and this is not included in the backup. So I copy that one (seq# 37378) separately, after the database has archived it.

I have done this before and never ran into problems, until yesterday. The duplicate ended with error in the recovery phase, indicating that the SYSTEM datafile (file 1) was several days old. It asked for an archived log (seq# 37290) created last Friday (6/6) to bring the datafile up to speed. Needless to say that the required archived logs were not part of the newly created backup. They were still all available, so I could complete the build, but I am at a loss to explain why a datafile belonging to a busy 24/7 database could be 4 days old. Also, RMAN output produced during the backup clearly states that "archived logs required to recover from this backup will be backed up", but they weren't. Am I missing something, or can archival backups (with the KEEP option) not be trusted after all?

Oracle EE 11.2.0.3, October 2013 CPU, RedHat EL6.

Cheers,
Tony

• backup listing ====

RMAN> backup tag 'BKP4STDBY' format '/backup/BKP4STDBY-%U' database keep

RMAN> until time 'sysdate + 1';

Starting backup at 10-JUN-14
current log archived

using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=722 device type=DISK backup will be obsolete on date 11-JUN-14 archived logs required to recover from this backup will be backed up channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001
name=/oradata/PROD/datafile/o1_mf_system_7d9cvo58_.dbf input datafile file number=00003
name=/oradata/PROD/datafile/o1_mf_undotbs1_7d9cvo3t_.dbf ...<snip>...
channel ORA_DISK_1: starting piece 1 at 10-JUN-14 channel ORA_DISK_1: finished piece 1 at 10-JUN-14 piece
handle=/backup/BKP4STDBY-31paftnd_1_1 tag=BKP4STDBY comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:04:35

using channel ORA_DISK_1
backup will be obsolete on date 11-JUN-14 archived logs required to recover from this backup will be backed up channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 10-JUN-14 channel ORA_DISK_1: finished piece 1 at 10-JUN-14 piece handle=/backup/BKP4STDBY-32pafu00_1_1 tag=BKP4STDBY comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

current log archived
using channel ORA_DISK_1
backup will be obsolete on date 11-JUN-14 archived logs required to recover from this backup will be backed up channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=37377 RECID=28759 STAMP=849868801 channel ORA_DISK_1: starting piece 1 at 10-JUN-14 channel ORA_DISK_1: finished piece 1 at 10-JUN-14 piece handle=/backup/BKP4STDBY-33pafu01_1_1 tag=BKP4STDBY comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

using channel ORA_DISK_1
backup will be obsolete on date 11-JUN-14 archived logs required to recover from this backup will be backed up channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set channel ORA_DISK_1: starting piece 1 at 10-JUN-14 channel ORA_DISK_1: finished piece 1 at 10-JUN-14 piece handle=/backup/BKP4STDBY-34pafu02_1_1 tag=BKP4STDBY comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 10-JUN-14

===> copy to new STBY server, also copy active redo log, seq. 37378
which is known to the control file, but not included in the backup, after it is archived

• Duplicate Database listing ====

RMAN> run {

2>    allocate auxiliary channel c1 device type disk;
3>    allocate auxiliary channel c2 device type disk;
4>    allocate auxiliary channel c3 device type disk;
5>
6>    duplicate database PROD
7>       for standby
8>       backup location '/data/backup/PROD'
9>       dorecover
10>       pfile '/oracle/admin/SBY1/pfile/initSBY1.ora'
11>       nofilenamecheck

12> ;
13> }
allocated channel: c1
channel c1: SID=234 device type=DISK

allocated channel: c2
channel c2: SID=292 device type=DISK
...
<snip>
...
archived log for thread 1 with sequence 37376 is already on disk as file /data/backup/PROD/o1_mf_1_37376_9sdnpcxt_.arc archived log for thread 1 with sequence 37377 is already on disk as file /data/backup/PROD/o1_mf_1_37377_9sdnz1c2_.arc archived log for thread 1 with sequence 37378 is already on disk as file /data/backup/PROD/o1_mf_1_37378_9sdpvx5w_.arc unable to find archived log
archived log thread=1 sequence=37290
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:
'/oradata/SBY1/datafile/o1_mf_system_9s2tosx1_.dbf'

released channel: c1
released channel: c2
released channel: c3
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 06/10/2014 11:14:32
RMAN-05501: aborting duplication of target database

RMAN-03015: error occurred in stored script Memory Script RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 37290 and starting SCN of 1067643896

RMAN>
RMAN> **end-of-file**

--
http://www.freelists.org/webpage/oracle-l


This email and any attachments are confidential and may also be privileged. If you are not the intended recipient, please delete all copies and notify the sender immediately. You may wish to refer to the incorporation details of Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at https://www.sc.com/en/incorporation-details.html.
--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 11 2014 - 08:23:06 CEST