I'm running a 12.1 SE database on Oracle Linux 7. I am getting nightly errors/warnings from my rman scripts about missing datafiles when trying to recover.
I have been running rman to a local drive using the following script:
RUN {
RECOVER COPY OF DATABASE WITH TAG 'r2cig_incr' UNTIL TIME 'SYSDATE-7';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'r2cig_incr' DATABASE;
BACKUP DEVICE TYPE DISK TAG 'r2cig_inc' ARCHIVELOG ALL NOT BACKED UP DELETE ALL INPUT;
DELETE NOPROMPT OBSOLETE DEVICE TYPE DISK;
}
Due to diskspace concerns, a second disk was added and I tried to move rman to use this disk by changing the channel device eg
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/rman/r2cig/rman/%U' maxpiecesize 10G;
(in hindsight, symlinks may have been a better option but at the time I preferred to make the location of the backups on a seperate drive explicit/obvious).
This channel change didn't seem to have any effect. Initially I suspected because I have a 7 day retention period set and the old image were still valid, however after 7 days datafile images were still being created in the old location.
As diskspace was becoming increasingly tight, I changed the backup script to use a different TAG to try and force a new set of backup images to be create on the new disk. This seemed to work – the new set of backup images were created okay on the new disk that night, but subsequent recovery operations seem to be failing.
Starting recover at 19/12/2016 10:45:12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=251 device type=DISK
no copy of datafile 1 found to recover
no copy of datafile 2 found to recover
no copy of datafile 3 found to recover
no copy of datafile 4 found to recover
no copy of datafile 5 found to recover
My understanding from the documentation is that this is normal on the first run (when there is no datafile image copy), and normal on the 2nd run (when there is a datafile copy, but no incrementals), but on the 3rd and subsequent run there should always be a datafile copy and an incremental to apply to it. This has now been failing for the last 6 nights.
From https://docs.oracle.com/cd/B19306_01/backup.102/b14192/bkup004.htm
The first time the script runs, this command has no effect, because
there is neither a datafile copy nor a level 1 incremental backup.The second time the script runs, there is a datafile copy (created by
the first BACKUP command), but no incremental level 1 backup, so
again, the command has no effect.On the third run and all subsequent runs, there is a datafile copy and
a level 1 incremental from the previous run, so the level 1
incremental is applied to the datafile copy, bringing the datafile
copy up to the checkpoint SCN of the level 1 incremental.
The incremental backups them selves seem to be being created fine and in the correct place, it's just the recovery that is failing.
Is there any way to see what/where RMAN is looking for the datafiles during the recover command?
Or is there a way to force it to look in the new location?
If needed, my RMAN config below:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/rman/r2cig/rman/%U' MAXPIECESIZE 10 G;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/app/oracle/product/12.1.0/dbhome_1/dbs/snapcf_r2cig.f'; # default
Best Answer
Asking the database to do the impossible.
You changed the TAG, so you created a new initial set of image copies.
Lets say this happened on
2016-12-13(just assuming, based on this):Next day, the backup script on
2016-12-14reached this point:RECOVER COPY OF DATABASE WITH TAG 'r2cig_incr' UNTIL TIME 'SYSDATE-7';So it wanted to recover the image copies created on
2016-12-13to an earlier state:SYSDATE-7=2016-12-07. That does not work like that. You can not rewind image copies to earlier states. When you try to do something like this, RMAN will look for an earlier image copy, which it can recover (forward, not backward), but there is no earlier image copy with the specified TAG, hence:no copy of datafile 1 found to recover.So this happened all day until today. Today, this script ran again:
RECOVER COPY OF DATABASE WITH TAG 'r2cig_incr' UNTIL TIME 'SYSDATE-7';Which means, recover the image copy created on
2016-12-13to a state:SYSDATE-7=2016-12-12. Still won't work.Tomorrow
SYSDATE-7will mean2016-12-13, the day the new initial image copies were created. I suspect this will also fail. Lets say, your script starts at the same time every day,00:00. It started at2016-12-13 00:00, finished creating the image copies at2016-12-13 01:00. The next time the script starts (2016-12-20 00:00),SYSDATE-7will still mean an earlier time:2016-12-13 00:00.But the day after tomorrow, when
SYSDATE - 7=2016-12-14, it will start recovering the image copies.If you run the above script every day, it will not recover anything on the first 7 days because of
SYSDATE-7. It will start recovering on the 8th day.Or, you could try this right now by running the script with the
PREVIEWoption. This will not do any recovery, just a preview. This, I suspect, will still print the same errors as in the last 6 days:But this should list the image copies that would be recovered, the starting and ending SCN, and the logfile used for recovery: