Oracle 11g – RMAN Restore with Different Database Name on Windows

oracleoracle-11g-r2restorerman

Can anyone share the proper method of RMAN restore to another server with another database name in oracle 11g(Windows platform). I'm totally confused with all the RMAN restore methods which I've found. I've taken the backup using the command 

BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

Please confirm whether is it required to take control file backup separately.

Any help will be appreciated.

Best Answer

By default, the controlfile is backed up with your data. This is turned on or off by this command:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

But as a matter of habit, I usually follow my data backups with:

backup current controlfile ;
backup spfile ;

I've had a great deal of trouble using duplicate database on Windows. Typically, I run into NTS permissions failures for the target login, which prevents the auxiliary db from creating. My efforts to troubleshoot these errors... well, I gave up.

This has led me to drafting a "playbook" document with all of the various OS and RMAN commands needed to manually restore a database to a new server with a new instance name. This also lets me tweak things to the new host with more control.

These scripts were tested in my Oracle 12.1 Windows environment, but may not be ideal for yours. I know RMAN is more willing to run SQL statements in 12c than in 11g, for example. Hopefully they help.

These scripts were created over several trial and error efforts, based on this link for the restore and this for changing the SID. The steps are:

Make sure everything is backed up.

I like to get a fresh copy of the spfile and controlfile, as it makes it easier later.

backup device type disk incremental level = 0 section size 1g database plus archivelog;
backup current controlfile tag='Controlfile Level 0';
backup spfile tag='SPFile Level 0';

If your old and new servers do not use the same file paths for Oracle data, then run this:

select '  Set Newname For Datafile ' || file# || ' to ''' || name || ''';' from v$datafile;
select '  Alter Database Rename File ''' || Member || ''' To ''' || Member || ''';' from v$logfile;

take the output and change the paths / filenames as needed to match your new environment.

Copy backups from old to new host

Optional but I like the RMAN backup files to be on the box I'm restoring to. So log into the new server, and copy the full backups, archivelogs, and autobackup folders from the old server's fast recovery area. Note that you only need to go back to the most recent full backup, no need to get weeks worth of files.

Stage the new instance

On the new host, create your data folder, your fast recovery folder, and your admin folder. Make sure Oracle is installed and the Oracle user accounts have full control of these folders.

Create an empty instance, with the same name as the old:

oradim -new -sid <<old sid name>>

Set ORACLE_SID=<<old sid name>>
RMAN target /

Use RMAN to recover the SPFILE and Control Files. inside RMAN, set the DBID to match the old instance's DB ID, and the file names and paths to match your environment:

SET DBID=1234567;
startup nomount;
restore spfile to 'C:\Oracle\Product\12.1.0\dbhome_1\database\init<<Old sid>>/ora' from 'f:\fast_recovery_area\<<old sid>>\BackupSet\2017_07_17\O1_MF_NNSNF_SPFILE_LEVEL_0_DOSTOJT8_.BKP';
create pfile='c:\oracle\product\12.1.0\dbhome_1\Database\pfile<<old sid>>.ora' from spfile='c:\oracle\product\12.1.0\dbhome_1\Database\Init<<old sid>>.ora';
shutdown immediate;

Do not close RMAN yet. Edit the pfile from the previous step in NotePad or NotePad++. Adjust the paths and memory requirements as needed to reflect the new environment. Don't change the name of the database, but change the folder paths to the new instance name.

Go back to your RMAN session and restore the control files. Again, fix paths as needed.

startup nomount pfile = 'c:\oracle\product\12.1.0\dbhome_1\Database\pfile<<old sid>>.ora’;
restore controlfile from 'F:\fast_recovery\<<old sid>>\BACKUPSET\2017_07_05\O1_MF_NCNNF_CONTROLFILE_LEVEL_0_DOSTO715_.BKP';
Alter Database Mount;

Restore your database files

Still in RMAN, inventory the backup files you copied over.

 catalog start with 'f:\fast_recovery_Area\<<old sid>>';

Now move your redo log files to their new folders in RMAN. You generated these scripts earlier.

Alter Database Rename File 'O:\ORADATA\<<old sid>>\REDO03.LOG' To 'f:\ORADATA\<<NEW sid>>\REDO03.LOG';
Alter Database Rename File 'O:\ORADATA\<<old sid>>\REDO02.LOG' To 'f:\ORADATA\<<new sid>>\REDO02.LOG';
Alter Database Rename File 'O:\ORADATA\<<old sid>>\REDO01.LOG' To 'f:\ORADATA\<<NEW Sid>>\REDO01.LOG';

And now we do the restore and recover, substitute the NewName scripts created earlier.

run { 
    Set Newname For Datafile 1 to 'f:\ORADATA\<<new sid>>\SYSTEM01.DBF';
    Set Newname For Datafile 3 to 'f:\ORADATA\<<new sid>>\SYSAUX01.DBF';
    Set Newname For Datafile 5 to 'f:\ORADATA\<<new sid>>\UNDOTBS01.DBF';
    Set Newname For Datafile 6 to 'f:\ORADATA\<<new sid>>\USERS01.DBF';

    RESTORE DATABASE;
    SWITCH DATAFILE ALL;
    RECOVER DATABASE;
}

Once RMAN finishes the base restore and recovery, create a new SPFile.

create spfile from pfile = 'c:\oracle\product\12.1.0\dbhome_1\Database\pfile<<OLD sid>>.ora’;
alter database open resetlogs;

Oracle does not backup or restore TEMP tablespace. This will prevent the full recovery from succeeding. Create a new temp tablespace, drop the old, and then rebuild the original one.

CREATE TEMPORARY TABLESPACE TEMP_NEW TEMPFILE 'f:\OraData\<<NEW Sid>>\TempNew1.dbf' SIZE 5m autoextend on next 10m maxsize unlimited;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_NEW;
DROP TABLESPACE TEMP including contents;
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE 'f:\OraData\<<NEW Sid>>\Temp01.dbf' SIZE 50m autoextend on next 10m maxsize unlimited;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
DROP TABLESPACE TEMP_NEW including contents and datafiles;

Change the instance name

At this point, Oracle should be running a fully restored copy of your database. But you want a new name. In RMAN:

Shutdown immediate;
Startup mount;

Don't close RMAN, but from a new CMD window running as administrator:

 set ORACLE_SID=<<old sid>>
 NID target=/ DBNAME=<<new sid>>

Note the DBID this gives you, as you'll need it later for disaster recovery (notice that was our first RMAN command at the top).

Back in the RMAN window:

Shutdown Immediate;
Startup Mount;
Alter System Set DB_NAME=<<New sid>> scope=SPFILE;
Shutdown Immediate;
exit;

Start up your new DB!

From the other CMD window, create a new password file, and recreate the Windows services. Note, this utility does not use quotes around the path, unlike most cases in Oracle.

orapwd file=c:\oracle\product\12.1.0\dbhome_1\Database\pwd<<new SID>>.ora password=<SYS pwd> entries=25
oradim -delete -sid <<old sid>>
oradim -new -sid <<new sid>> -intpwd <SYS pwd> -startmode a

Start the database.

Set Oracle_SID=<<new sid>>
lsnrctl reload
sqlplus / as sysdba
Startup Mount;
Alter Database Open ResetLogs;

Back up your new database

At this point, your DB has no backups. Perform an RMAN backup before you do any work in this DB.

Related Question