Prerequisites
On the new host…
RMAN backups are run on the source – level 0 full, and 5 incremental level 1 backups – 4 hours apart, are being automatically (oracle crontab scheduled) and copied nightly to the new host / target. Auto backup of control and spfile are turned on on the source.
Moved the RMAN backups to the new host, got the “set until sequence” out of the rman last level1 backup – log maximum sequence in the log. Used the control file backup that went with that level 1.
In this instance, archivelog was turned on at the source DB and block change tracking was on to facilitate fast incremental RMAN backups.
Oracle binaries already installed on the target – matching version and patch level of the source. Target has the Oracle environment variables set.
Copied a pfile over from source to destination and used that to create a spfile.
This is the production dbid, and one of the first steps, restore/create the control file from the source rman backups.
RMAN> SET DBID 334635315;
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE CONTROLFILE FROM “/u02/app/prod/backups/2017_08_11/c-334635315-20170811-02”;
What if your target mounts are different for the data files?
If your mount points are different use sql like this to create the set newnames below from the source DB – if mount points on source and target are the same this step is not necessary – and it is a head-ache – syntactically difficult to get right – there must be an easier way like a wild card or a convert parameter… I found away around doing this by using matching mount points on source and destination.
set head off
set pagesize 999
set feedback off
select ‘SET NEWNAME FOR DATAFILE ‘||file#||’ to ”’|| trim( name)||”’;’ from v$datafile;
select trim(member) from v$logfile;
Uncatalog and catalog – if your backup source is different (precautionary step)
My mounts target mount points on the target were the same as the source but the backups are in a different place – so I uncataloged what the control file knew about backups and re-cataloged them
rman target /
# uncatalog all archivelogs
CHANGE ARCHIVELOG ALL UNCATALOG;
#uncatalog all backups
crosscheck backup;
delete noprompt expired backup;
# catalog all backups in the dir that has a file name that starts with “rbu”
catalog start with ‘/u02/app/prod/backups/rbu’ noprompt;
Restore and recover
Then just use the highest sequence in the source rman log file in the “set until sequence” clause below to restore and recover.
rman target / cmdfile=new.rmn log=new.log
My new.rmn script looks like this:
RUN {
SET UNTIL Sequence 21963;
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}
Open with resetlogs…
So now it’s a simple matter of opening the database with “resetlogs”, so just for the hell of it prove we really need resetlogs.
sqlplus / as sysdba
NAME OPEN_MODE LOG_MODENAME OPEN_MODE LOG_MODE——— ——————– ————PROD MOUNTED ARCHIVELOG
SQL> alter database open;
# yes I knew that “resetlogs” was necessary because of the “set until sequence” which makes it a point in time restore
alter database open*ERROR at line 1:ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
But, here is an unforeseen issue, what’s this? Don’t know but I googled my way out of it. Found the “alter database clear logfile group 12”. I’m going to guess this occurred because I tried to open without doing the resetlogs… don’t have time to confirm yet.
SQL> alter database open
SQL> alter database open resetlogs;
alter database open resetlogs * ERROR at line 1:ORA-00392: log 12 of thread 1 is being cleared, operation not allowedORA-00312: online log 12 thread 1:’/u03/app/oracle/fast_recovery_area/PROD/onlinelog/redogG12_m1.redo
‘ORA-00312: online log 12 thread 1:’/u03/app/oracle/oradata/prod/redoG12_m2.redo’
SQL> select group#,status,member from v$logfile;
GROUP# STATUS———- ——-MEMBER——————————————————————————– 11/u03/app/oracle/fast_recovery_area/PROD/onlinelog/redogG11_m1.redo
11/u03/app/oracle/oradata/prod/redoG11_m2.redo
12/u03/app/oracle/fast_recovery_area/PROD/onlinelog/redogG12_m1.redo
GROUP# STATUS———- ——-MEMBER——————————————————————————– 12/u03/app/oracle/oradata/prod/redoG12_m2.redo
13/u03/app/oracle/fast_recovery_area/PROD/onlinelog/redogG13_m1.redo
13/u03/app/oracle/oradata/prod/redoG13_m2.redo
GROUP# STATUS———- ——-MEMBER——————————————————————————– 14/u03/app/oracle/fast_recovery_area/PROD/onlinelog/redogG14_m1.redo
14/u03/app/oracle/oradata/prod/redoG14_m2.redo
8 rows selected.
SQL> alter database clear logfile group 12
SQL> /
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE——————–MOUNTED
SQL> alter database open resetlogs;
Database altered.
SQL>