Hi,
RAC 11.2.0.4.160419
RHEL 6.7
Ok, so I was tasked with flashing back a database to a guaranteed restore point.
SYS@prlif2q1 SQL>select
2 name,
3 scn,
4 database_incarnation#,
5 guarantee_flashback_database,
6 time,
7 name from v$restore_point;
NAME SCN DATABASE_INCARNATION# GUA TIME NAME
---------------------------------------- ---------- --------------------- --- -------------------------------------------------- ----------------------------------------
RP_TESTSETTINGSOKINSTALLERT711OK 3273286398 7 YES 24-MAY-16 11.45.15.000000000 AM RP_TESTSETTINGSOKINSTALLERT711OK
But to my surprise the flashback failed because of missing required redo:
RMAN> rman target /
RMAN> connect catalog <username><password>@rmancat
run {
allocate channel sbt1 device type SBT_TAPE parms 'SBT_LIBRARY=/u01/app/oracle/product/11.2.0.4/db_1/lib/libddobk.so,SBT_PARMS=(STORAGE_UNIT=xxxx,BACKUP_HOST=xxxxx,/u01/app/oracle/product/11.2.0.4/db_1,BLKSIZE=1048576)';
allocate channel sbt2 device type SBT_TAPE parms 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64 ENV=(NB_ORA_SERV=xxxx,NB_ORA_POLICY=xxxxx,NB_ORA_CLIENT=xxxx)';
flashback database to restore point RP_TESTSETTINGSOKINSTALLERT711OK;
}
allocated channel: sbt1
channel sbt1: SID=770 instance=prlif2q1 device type=SBT_TAPE
channel sbt1: Data Domain Boost API
allocated channel: sbt2
channel sbt2: SID=835 instance=prlif2q1 device type=SBT_TAPE
channel sbt2: Veritas NetBackup for Oracle - Release 7.6 (2014070620)
Starting flashback at 09-JUN-16
starting media recovery
unable to find archived log
archived log thread=1 sequence=12
released channel: sbt1
released channel: sbt2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of flashback command at 06/09/2016 08:31:47
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 12 and starting SCN of 3273286361
RMAN> list backup of archivelog sequence 12 thread 1;
specification does not match any backup in the repository
RMAN> list archivelog sequence 12 thread 1;
using target database control file instead of recovery catalog
specification does not match any archived log in the repository
I thought that RMAN would keep the archived redo logs on disk needed for the restore point, but I found this is the documentation:
So Ok, the archived redo logs needed for a guaranteed restore point might in fact be removed from the FRA, but should be restored from backup.
So I started going through my backup logs trying to figure out what had happened:
From the first archived redo log backup taken some hours after the restore point was created:
channel ORA_SBT_TAPE_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=12 RECID=1047 STAMP=912686789
input archived log thread=2 sequence=11 RECID=1046 STAMP=912686788
channel ORA_SBT_TAPE_1: starting piece 1 at 24.05.16 12:06:30
channel ORA_SBT_TAPE_1: finished piece 1 at 24.05.16 12:06:55
piece handle=PRLIF2Q_ARCH_2sr6cvm6_1_1_20160524_912686790 tag=TAG20160524T120630 comment=API Version 2.0,MMS Version 5.0.0.0
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25
Finished backup at 24.05.16 12:06:55
Here I can see that the required sequences from both threads are contained in backuppiece "PRLIF2Q_ARCH_2sr6cvm6_1_1_20160524_912686790"
And all future backups of the database gives the expected warning that it cannot delete archived redo logs required for the restore point:
Finished backup at 09.06.16 03:05:27
released channel: ORA_SBT_TAPE_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=774 instance=prlif2q1 device type=DISK
RMAN-08139: WARNING: archived redo log not deleted, needed for guaranteed restore point
archived log file name=+FRA/prlif2q/archivelog/2016_05_24/thread_1_seq_12.3558.912686789 thread=1 sequence=12
RMAN-08139: WARNING: archived redo log not deleted, needed for guaranteed restore point
archived log file name=+FRA/prlif2q/archivelog/2016_05_24/thread_2_seq_11.1081.912686789 thread=2 sequence=11
Since we are currently moving from one backup system to another I have another script that allocates channel to both of these systems as well as disk to delete obsolete backup based on my recovery window of 14 days.
So what happens next is not understandable to me
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jun 9 03:05:33 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRLIF2Q (DBID=2689239201)
connected to recovery catalog database
RMAN>
echo set on
RMAN> allocate channel for maintenance device type SBT_TAPE parms 'SBT_LIBRARY=/u01/app/oracle/product/11.2.0.4/db_1/lib/libddobk.so, SBT_PARMS=(STORAGE_UNIT=xxxxx,BACKUP_HOST=xxxxx,/u01/app/oracle/product/11.2.0.4/db_1,BLKSIZE=1048576)';
allocated channel: ORA_MAINT_SBT_TAPE_1
channel ORA_MAINT_SBT_TAPE_1: SID=774 instance=prlif2q1 device type=SBT_TAPE
channel ORA_MAINT_SBT_TAPE_1: Data Domain Boost API
RMAN> allocate channel for maintenance device type SBT_TAPE parms 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64 ENV=(NB_ORA_SERV=xxxxxx,NB_ORA_POLICY=xxxx,NB_ORA_CLIENT=xxxxxx)';
allocated channel: ORA_MAINT_SBT_TAPE_2
channel ORA_MAINT_SBT_TAPE_2: SID=903 instance=prlif2q1 device type=SBT_TAPE
channel ORA_MAINT_SBT_TAPE_2: Veritas NetBackup for Oracle - Release 7.6 (2014070620)
RMAN> allocate channel for maintenance device type disk;
allocated channel: ORA_MAINT_DISK_3
channel ORA_MAINT_DISK_3: SID=966 instance=prlif2q1 device type=DISK
RMAN> delete noprompt obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 14 days
...
...
...
backup piece handle=PRLIF2Q_ARCH_2rr6colp_1_1_20160524_912679609 RECID=2138 STAMP=912679609
deleted backup piece
backup piece handle=PRLIF2Q_ARCH_2sr6cvm6_1_1_20160524_912686790 RECID=2139 STAMP=912686790
deleted archived log
archived log file name=+FRA/prlif2q/archivelog/2016_05_24/thread_2_seq_11.1081.912686789 RECID=1046 STAMP=912686788
deleted archived log
archived log file name=+FRA/prlif2q/archivelog/2016_05_24/thread_1_seq_12.3558.912686789 RECID=1047 STAMP=912686789
So not only does RMAN delete the backuppiece it deletes the archived redo logs on disk as well even though I have a guaranteed restore point that needs it!
Any tips to share?
AJ