A user requested a database backup that needed to be kept for 3 months and then restored.
Oh. Gee. Not had to do this for a while.
This was a non Prod database and backups are taken every night and only one copy kept. So the first thing I had to do was make sure the backups didn't get removed.
A bit of googling and this is the script I created:
run {
allocate channel d1 device type disk;
allocate channel d2 device type disk;
backup format '/backup/oraflash/UIQUT1/rman/keep/%u_%s_%p'
keep until time 'sysdate+100'
database
current controlfile
format '/backup/oraflash/UIQUT1/rman/keep/uiqut1_ctrl_%u.rman'
keep until time 'sysdate+100'
spfile
format '/backup/oraflash/UIQUT1/rman/keep/spfile_%u_%s_%p'
keep until time 'sysdate+100'
plus archivelog
format '/backup/oraflash/UIQUT1/rman/keep/arc_%u_%s_%p'
keep until time 'sysdate+100';
}
The first time I did this I only had the 'keep until time' as the last line - and it only kept the archived logs and removed the other backups - so you need it for each backup.
I checked it the next day and the files were all there.
You should also change the parameter 'control_file_record_keep_time' to a relevant figure depending on how long you want to keep the backups - not essential because the backup files can be recatalogued, but worthwhile.
I ran a 'restore database preview' which verified that the files were OK.
However, the user was pretty adamant that it was essential that the database could be restored down the line - and the only way I could be sure was to actually do it. I obviously couldn't overwrite the existing database, so I tarred up the backup files and scp'd them to another host.
Once there, I had some preparation to do
I created the directory - not 100% sure this is necessary, but worth doing
mkdir /backup/oraflash/UIQUT1/rman/keep
and moved the tar file there and extracted it.
I also copied the init.ora file to the $ORACLE_HOME/dbs directory and checked the paths of the controlfiles, log_arch_dest and audit dump dest, and created directories for those if they didn't exist
mkdir /app/oracle/admin/UIQUT1
mkdir /app/oracle/admin/UIQUT1/adump
*.control_files='/app/oracle/admin/UIQUT1/control01.ctl','/app/oracle/admin/UIQUT1/control02.ctl','/app/oracle/admin/UIQUT1/control03.ctl'
*.log_archive_dest_1='LOCATION=/backup/oraflash/UIQUT1/archivelog/'
There was also a local listener parameter that I removed.
I added an entry to the oratab and set the database name with . oraenv
So the process to restore a backup like this is
1. Startup nomount
2. Restore controlfile
3. Alter database mount
4. Restore datafiles
5. Recover database
6. Open resetlogs
But there are some more things to do in between.
First off though, restore the controlfile:
The script backed up the controlfile to a file with this format - uiqut1_ctrl_%u.rman - so find that file and restore it
rman target/
restore controlfile from '/backup/oraflash/UIQUT1/rman/keep/uiqut1_ctrl_bhsmcleg.rman';
It should do the job.
Mount the database (in RMAN)
alter database mount;
You need to tell RMAN about the backups
catalog start with ''/backup/oraflash/UIQUT1/rman/keep/'
This should run through and register the files. If you 'list backup of database;' it should show the files.
An added complication here is that the host I am restoring to uses ASM, but the source database doesn't. Not really a big deal, just have to generate the new file names. If you don't need to do this, just create the directories like the host server.
In SQLPlus:
set pages 9999
set lines 200
select 'set newname for datafile '''||name||''' to '''||replace(name, '/dat/oradata/UIQUT1/', '+STSORA050_DATA_01')||''';' FROM V$DATAFILE;
This will generate something like this:
set newname for datafile '/dat/oradata/UIQUT1/system01.dbf' to '+STSORA050_DATA_01/UIQUT1/system01.dbf';
set newname for datafile '/dat/oradata/UIQUT1/sysaux01.dbf' to '+STSORA050_DATA_01/UIQUT1/sysaux01.dbf';
set newname for datafile '/dat/orasys/UIQUT1/undotbs01.dbf' to '+STSORA050_DATA_01/UIQUT1/undotbs01.dbf';
Do the same for the TEMPFILEs (change V$DATAFILE to V$TEMPFILE)
Keep the output, you'll need it later.
Do the same for the redo logs :
alter database rename file '/dat/redoa/UIQUT1/redo03a.log' to '+STSORA050_DATA_01/UIQUT1/redo03a.log';
alter database rename file '/dat/redob/UIQUT1/redo03b.log' to '+STSORA050_DATA_01/UIQUT1/redo03b.log';
This is all that is needed.
Create a restore.rcv script and put this in it (if you need to rename the files - if not, just have the restore and recover commands)
run {
set newname for datafile '/dat/oradata/UIQUT1/system01.dbf' to '+STSORA050_DATA_01/UIQUT1/system01.dbf';
set newname for datafile '/dat/oradata/UIQUT1/sysaux01.dbf' to '+STSORA050_DATA_01/UIQUT1/sysaux01.dbf';
set newname for datafile '/dat/orasys/UIQUT1/undotbs01.dbf' to '+STSORA050_DATA_01/UIQUT1/undotbs01.dbf';
set newname for datafile '/dat/oradata/UIQUT1/users01.dbf' to '+STSORA050_DATA_01/UIQUT1/users01.dbf';
.
.
. (all the files from the rename command)
.
.
set newname for tempfile '/dat/orasys/UIQUT1/temp01.dbf' to '+STSORA050_DATA_01/UIQUT1/temp01.dbf';
restore database;
switch datafile all;
switch tempfile all;
recover database;
}
Run it like this:
nohup rman target / cmdfile='/backup/oraflash/UIQUT1/rman/keep/restore.rcv' log='/backup/oraflash/UIQUT1/rman/keep/restore.log' &
This will ensure it will keep going if your session disconnects.
Once complete, you might see this at the end of the logfile
RMAN-03002: failure of recover command at 12/19/2017 11:27:29
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 146352 and starting SCN of 505408658
Doesn't matter, just means it has applied the last log that was backed up.
Go to SQLPlus, rename the log files, and open the database
alter database rename file '/dat/redoa/UIQUT1/redo03a.log' to '+STSORA050_DATA_01/UIQUT1/redo03a.log';
alter database rename file '/dat/redob/UIQUT1/redo03b.log' to '+STSORA050_DATA_01/UIQUT1/redo03b.log';
alter database open resetlogs;
Hopefully it will open.
All good.
