Database installation on a New Host using RMAN

Hi guys... in this blog I am going to explain you the detailed steps about how to restore a database on New Host using RMAN. You can also use it to move a production database to new host as test database. Here, we are going to use the source database with the DB_NAME=ORCL and we are going to restore this ORCL database on the test database machine.

1. Preparing to restore the database on a new host:

If you use the procedure in this section, then the DBID for the restored database equals the DBID for the original database. Do not register a test database created in this way in the same recovery catalog as the source database. Because the DBID of the two databases is the same, the metadata for the test database can interfere with RMAN's ability to restore and recover the source database. 

Record the DBID for your source database. You can find the DBID by connecting to the database with RMAN as shown below:

	C:\WINDOWS\system32>rman target /
	Recovery Manager: Release 11.2.0.4.0 - Production on Thu Feb 25 17:45:02 2016
	Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
	connected to target database: ORCL (DBID=1886297637)
	--or
	select dbid from v$database;

view raw RMAN-DBID.sql hosted with ❤ by GitHub

Take backups of all the datafiles, controlfiles, spfile and archivelog files on the source database machine. Perform following steps on the source database machine:

	set oracle_sid=ORCL
	rman target /
	host "mkdir C:\backup";
	run
	{
	shutdown immediate
	startup mount
	sql 'alter database archivelog';
	alter database open;
	delete noprompt backup;
	configure controlfile autobackup on;
	backup database format 'C:\backup\full_datafiles_%s.bkp'
	current controlfile format 'C:\backup\control_file.bkp'
	spfile format 'C:\backup\sp_file.bkp';
	backup archivelog all format 'C:\backup\archive_log_%s.bkp';
	}

view raw New-host-production-db-step-1.sql hosted with ❤ by GitHub

Ensure that the initialization parameter file and the backups of datafiles, controlfiles and archivelogs are available on the new host machine for the restore operation. Copy all the backup files from the old host to new host using operating system utility or you can transfer it through pendrive also. If you are using operating system utility then you can perform following step to copy the files from shared folder 'backup' of Neha-pc:

	host "copy C:\backup\full_datafiles_350.bkp \\Neha-pc\backup";
	host "copy C:\backup\control_file.bkp \\Neha-pc\backup";
	host "copy C:\backup\sp_file.bkp \\Neha-pc\backup";
	host "copy C:\backup\archive_log_356.bkp \\Neha-pc\backup";
	host "copy C:\backup\archive_log_357.bkp \\Neha-pc\backup";

view raw sharing-files-on-new-host.sql hosted with ❤ by GitHub

When we restore the database, we are not going to have the active redo logs from the source database, so we will need the last SCN of archivelogs for the restore operation. We can find it by querying V$BACKUP_ARCHIVELOG_DETAILS.

	sqlplus sys as sysdba
	select max(next_change#) from V$backup_archivelog_details;
	--OR
	rman target /
	list backup of archivelog all;

view raw archivelog-SCN.sql hosted with ❤ by GitHub

2. Creating the instance for the test machine and creating the pfile by restoring it from spfile: 

Perform the following steps on the test database machine:

Use the oradim utility to create the instance for orcl database on the test machine. 

	oradim -new -sid orcl -syspwd admin
	set oracle_sid=orcl
	rman target /

view raw New-host-production-db-step-2.sql hosted with ❤ by GitHub

When we start the database in nomount mode we receive error because the parameter file is not found. This is expected because we are restoring to a new host, hence, Oracle will use a “dummy” parameter file to start the instance.

	RMAN> startup nomount
	startup failed: ORA-01078: failure in processing system parameters
	LRM-00109: could not open parameter file 'C:\APP\NEHA\PRODUCT\11.2.0\DBHOME_1\DATABASE\INITORCL.ORA'
	starting Oracle instance without parameter file for retrieval of spfile
	Oracle instance started
	Total System Global Area 1071333376 bytes
	Fixed Size 1410028 bytes
	Variable Size 281021460 bytes
	Database Buffers 784334848 bytes
	Redo Buffers 4567040 bytes
	RMAN>

view raw New-host-production-db-step-3.sql hosted with ❤ by GitHub

Restore and edit the server parameter file which is in the SP_FILE.BKP. Since SPFILE is a binary file we need to create the file from it and then make the initialization parameter changes.

	RMAN>restore spfile to pfile '?\database\initorcl.ora' from 'C:\backup\SP_FILE.BKP';
	shutdown immediate

view raw restore-spfile.sql hosted with ❤ by GitHub

Now that we have the SPFILE, we need to edit some of the location specific parameters in the initorcl.ora file to reflect the new host's directory structure. Following are the parameters that need to be changed:

	control_files='C:\APP\NEHA\ORADATA\ALICE\CONTROL01.CTL','C:\APP\NEHA\FAST_RECOVERY_AREA\ALICE\CONTROL02.CTL'
	db_recovery_file_dest='C:\app\Neha\fast_recovery_area'
	audit_file_dest='C:\APP\NEHA\ADMIN\ALICE\ADUMP'
	diagnostic_dest='C:\APP\NEHA'

view raw parameters.sql hosted with ❤ by GitHub

Make sure the directories are already present.

	host "mkdir C:\app\Neha\admin\orcl\adump";
	host "mkdir C:\app\Neha\oradata\orcl\";
	host "mkdir C:\app\Neha\fast_recovery_area\orcl\";

view raw new-host-mkdir.sql hosted with ❤ by GitHub

3. Restoring the database on the New Host:

Restart the instance with the edited initialization parameter file.Then, restore the control file to whatever locations you specified in the CONTROL_FILES initialization parameter and then mount the database. After that, catalog the datafiles and archivelogs from the FULL_DATAFILES_350.bkp, ARCHIVE_LOG_356.bkp and ARCHIVE_LOG_357.bkp.

	rman target /
	startup nomount pfile='?\database\initorcl.ora'
	sql "create spfile from pfile=''?\database\initorcl.ora'' ";
	startup nomount force
	run
	{
	restore controlfile from 'C:\backup\CONTROL_FILE.bkp';
	alter database mount;
	catalog backuppiece 'C:\backup\FULL_DATAFILES_350.bkp';
	catalog backuppiece 'C:\backup\ARCHIVE_LOG_356.bkp';
	catalog backuppiece 'C:\backup\ARCHIVE_LOG_357.bkp';
	}

view raw New-host-production-db-step-4.sql hosted with ❤ by GitHub

Execute the following RMAN restore and recovery script. The script does the following things:

• SET NEWNAME : For each data file on the destination host that is restored to a different path than it had on the source host, use this command to specify the new path on the test machine. If the file systems on the destination system are set up to have the same paths as the source host, then do not use SET NEWNAME for those files restored to the same path as on the source host.
• ALTER DATABASE RENAME FILE : For each online redo log that is to be created at a different location than it had on the source host, use this SQL commands to specify the path name on the destination host. If the file systems on the destination system are set up to have the same paths as the source host, then do not use ALTER DATABASE RENAME FILE for those files restored to the same path as on the source host.
• SET UNTIL : Perform this operation to limit recovery to the end of the archived redo logs. The recovery stops with an error if no SET UNTIL command is specified.
• RESTORE the database.
• SWITCH DATAFILE ALL : Run the SWITCH DATAFILE ALL command so that the control file recognizes the new path names as the official new names of the data files.
• RECOVER the database.

	host "sqlplus -s / as sysdba";
	spool C:\copy_files.sql
	set heading off
	set line 200 pages 200
	set feedback off
	select 'run' from dual;
	select '{' from dual;
	select 'set newname for datafile '||file#||' to ''C:\app\neha\oradata\orcl'||substr(name,instr(name,'\',-1))||''''||';' from v$datafile;
	select 'sql "alter database rename file '||''''''||member||''''''||' to ''''C:\app\neha\oradata\orcl'||substr(member,instr(member,'\',-1))||''''''||' ";' from v$logfile;
	select 'sql "alter database rename file '||''''''||name||''''''||' to ''''C:\app\neha\oradata\orcl'||substr(name,instr(name,'\',-1))||''''''||' ";' from v$tempfile;
	select 'set until scn '||max(next_change#)||';' from v$backup_archivelog_details;
	select 'restore database;' from dual;
	select 'switch datafile all;' from dual;
	select 'recover database; ' from dual;
	select '}' from dual;
	spool off
	exit

view raw restore-script.sql hosted with ❤ by GitHub

Following is the output of the restore-script.sql. Execute the following code in RMAN:

	rman target /
	run
	{
	set newname for datafile 1 to 'C:\app\Neha\oradata\orcl\SYSTEM01.DBF';
	set newname for datafile 2 to 'C:\app\Neha\oradata\orcl\SYSAUX01.DBF';
	set newname for datafile 3 to 'C:\app\Neha\oradata\orcl\UNDOTBS01.DBF';
	set newname for datafile 4 to 'C:\app\Neha\oradata\orcl\USERS01.DBF';
	set newname for datafile 5 to 'C:\app\Neha\oradata\orcl\EXAMPLE01.DBF';
	sql "alter database rename file ''E:\APP\SAURABH\ORADATA\ORCL\REDO03.LOG'' to ''C:\app\Neha\oradata\orcl\REDO03.LOG'' ";
	sql "alter database rename file ''E:\APP\SAURABH\ORADATA\ORCL\REDO02.LOG'' to ''C:\app\Neha\oradata\orcl\REDO02.LOG'' ";
	sql "alter database rename file ''E:\APP\SAURABH\ORADATA\ORCL\REDO01.LOG'' to ''C:\app\Neha\oradata\orcl\REDO01.LOG'' ";
	sql "alter database rename file ''E:\APP\SAURABH\ORADATA\ORCL\TEMP01.DBF'' to ''C:\app\Neha\oradata\orcl\TEMP01.DBF'' ";
	set until scn 1171854;
	restore database;
	switch datafile all;
	recover database;
	alter database open resetlogs;
	}

view raw output-restore-script.sql hosted with ❤ by GitHub

From the RMAN prompt, open the database with the RESETLOGS option:

	rman target /
	alter database open resetlogs;

view raw open.sql hosted with ❤ by GitHub

If all the above steps are executed properly, then you will have the production database installed on the test machine.


Stay tuned for more contents...