Restore full database on different server with same name.

 

Here I am taking example of database ORA11G which is hosted on oel1.oracle.com and restoring this on oel2.oracle.com with same name.

Assuming we have different file system on both servers.

• Copy pfile and passwordfile from oel1.oracle.com to $ORACLE_HOME/dbs location of oel2.oracle.com and modify pfile per current server i.e. controlfile, adump location etc. also remove all hidden parameters from pfile.
• Restore controlfile that was taken after level0 backup to oel2.oracle.com at location define in pfile.
• Now create script to restore database.

·         In below script we are using ‘SET NEWNAME’ because we have different file system.

·         At the end of script we are using ‘SWITCH DATAFILE ALL;’ to update controlfile with new location.

·         Recover database command will restore archive logs from tape to disk on location mention in archive dest log_archive_dest_1, and start recovery.

·         Channels are multiple hands of RMAN to restore datafiles.

export ORACLE_SID=DB11G

rman target / <<! >DB11G_rman_restore.log

run {
sql 'alter session set optimizer_mode=rule';
allocate channel a1 type 'sbt_tape';
allocate channel a2 type 'sbt_tape';
allocate channel a3 type 'sbt_tape';
allocate channel a4 type 'sbt_tape';
send 'NB_ORA_SERV=denver, NB_ORA_CLIENT=ora11gdb-g1';
SET NEWNAME FOR DATAFILE 4 to '/u01/app/oracle/oradata/DB11G/datafile/o1_mf_users_8yobsg8j_.dbf';

SET NEWNAME FOR DATAFILE 3 to '/u01/app/oracle/oradata/DB11G/datafile/o1_mf_undotbs1_8yobsg7w_.dbf';

SET NEWNAME FOR DATAFILE 2 to '/u01/app/oracle/oradata/DB11G/datafile/o1_mf_sysaux_8yobsg7o_.dbf';

SET NEWNAME FOR DATAFILE 1 to '/u01/app/oracle/oradata/DB11G/datafile/o1_mf_system_8yobsg0w_.dbf';

SET NEWNAME FOR DATAFILE 5 to '/u01/app/oracle/oradata/DB11G/datafile/vijay_ts_01.dbf';

RESTORE DATABASE;

SWITCH DATAFILE ALL;

RECOVER DATABASE;

}

Exit;

• Startup mount, and then get to know the max. Sequence of archive file information from the v$log_history. This will tell us, till what sequence recovery is needed.
  
  SQL> startup nomount;
  ORACLE instance started.
  
  
  Total System Global Area  413372416 bytes
  Fixed Size                  2213896 bytes
  Variable Size             306186232 bytes
  Database Buffers          100663296 bytes
  Redo Buffers                4308992 bytes
  SQL> alter database mount;
  
  
  Database altered.
  
  
  
  
  SQL> SELECT MAX(SEQUENCE#) FROM V$LOG_HISTORY;
  
  
  MAX(SEQUENCE#)
  --------------
              39
  
  

• Now database is ready for restoration. Run restore script that we have prepared at step number 3.

 

 

[oracle@oel2 script]$ nohup sh restore_db11g.sh &

[1] 24665

[oracle@oel2 script]$ nohup: ignoring input and appending output to `nohup.out'

 

• Once restore and recovery completes, login to sqlplus and rename redolog file before executing resetlog command, because if we don’t rename it to TST11G directory then while resetlog it will look for ORA11G directory to create redolog file and this may cause database corruption.

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------

/u01/app/oracle/oradata/ORA11G/onlinelog/o1_mf_3_90ob65o7_.log
/u01/app/oracle/oradata/ORA11G/onlinelog/o1_mf_2_90ob64x1_.log
/u01/app/oracle/oradata/ORA11G/onlinelog/o1_mf_1_90ob64bj_.log

SQL> alter database rename file '/u01/app/oracle/oradata/ORA11G/onlinelog/o1_mf_3_90ob65o7_.log' to '/u01/app/oracle/oradata/TST11G/onlinelog/o1_mf_3_90ob65o7_.log';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/ORA11G/onlinelog/o1_mf_2_90ob64x1_.log' to '/u01/app/oracle/oradata/TST11G/onlinelog/o1_mf_2_90ob64x1_.log';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/ORA11G/onlinelog/o1_mf_1_90ob64bj_.log' to '/u01/app/oracle/oradata/TST11G/onlinelog/o1_mf_1_90ob64bj_.log';

Database altered.

SQL> alter database open resetlogs;

Database altered.

SQL> select instance_name, host_name from v$instance;

INSTANCE_NAME    HOST_NAME
---------------- ---------------------------------
ORA11G           oel2.oracle.com

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ORA11G    READ WRITE

SQL> select count(*) from v$recover_file;

 
COUNT(*)
----------
         0