Restore database on another server and change database name (Linux)

Hello, everyone! Today I will show you how to restore a database on another server with different directory structures and in the sequence change the database name. This operation is very common to tests procedure restore and recovery. I always used this process for many environments for evidences of restore.

Let’s start: On our cenario we have two VMs Oracle Linux 6, on the host one (srvprod) we have one database (DB11G). On host two (srvdev) we have Oracle binaries installed. Into both servers are on 11.2.0.4. Login srvprod:

[oracle@srvprod ~]$ mkdir -p /u01/app/oracle/oradata/MYDBTEST/

  • Now log into rman:

[oracle@srvprod ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 – Production on Thu Jul 2 22:24:40 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: DB11G (DBID=340876712)

RMAN> BACKUP DATABASE FORMAT ‘/u01/app/oracle/oradata/MYDBTEST/DB11G_%U’ PLUS ARCHIVELOG DELETE ALL INPUT FORMAT ‘/u01/app/oracle/oradata/MYDBTEST/DB11G_%U’;

Starting backup at 02-JUL-15 current log archived using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=98 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=1 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=105 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=109 device type=DISK allocated channel: ORA_DISK_5 channel ORA_DISK_5: SID=89 device type=DISK channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=223 RECID=229 STAMP=884039221 channel ORA_DISK_1: starting piece 1 at 02-JUL-15 channel ORA_DISK_1: finished piece 1 at 02-JUL-15 piece handle=/u01/app/oracle/oradata/MYDBTEST/DB11G_2kqb2nhm_1_1 tag=TAG20150702T222702 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 channel ORA_DISK_1: deleting archived log(s) archived log file name=/u01/app/oracle/fast_recovery_area/DB11G/archivelog/2015_07_02/o1_mf_1_223_bscwqo6n_.arc thread=1 sequence=223 Finished backup at 02-JUL-15 Starting backup at 02-JUL-15 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4 using channel ORA_DISK_5 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/DB11G/system01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/DB11G/users01.dbf channel ORA_DISK_1: starting piece 1 at 02-JUL-15 channel ORA_DISK_2: starting full datafile backup set channel ORA_DISK_2: specifying datafile(s) in backup set input datafile file number=00002 name=/u01/app/oracle/oradata/DB11G/sysaux01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/DB11G/undotbs01.dbf channel ORA_DISK_2: starting piece 1 at 02-JUL-15 channel ORA_DISK_3: starting full datafile backup set channel ORA_DISK_3: specifying datafile(s) in backup set input datafile file number=00005 name=/u01/app/oracle/oradata/DB11G/example01.dbf input datafile file number=00006 name=/u01/app/oracle/oradata/DB11G/tbs_vendas.dbf channel ORA_DISK_3: starting piece 1 at 02-JUL-15 channel ORA_DISK_3: finished piece 1 at 02-JUL-15 piece handle=/u01/app/oracle/oradata/MYDBTEST/DB11G_2nqb2nho_1_1 tag=TAG20150702T222703 comment=NONE channel ORA_DISK_3: backup set complete, elapsed time: 00:00:55 channel ORA_DISK_1: finished piece 1 at 02-JUL-15 piece handle=/u01/app/oracle/oradata/MYDBTEST/DB11G_2lqb2nho_1_1 tag=TAG20150702T222703 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25 channel ORA_DISK_2: finished piece 1 at 02-JUL-15 piece handle=/u01/app/oracle/oradata/MYDBTEST/DB11G_2mqb2nho_1_1 tag=TAG20150702T222703 comment=NONE channel ORA_DISK_2: backup set complete, elapsed time: 00:01:25 Finished backup at 02-JUL-15 Starting backup at 02-JUL-15 current log archived using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4 using channel ORA_DISK_5 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=224 RECID=230 STAMP=884039309 channel ORA_DISK_1: starting piece 1 at 02-JUL-15 channel ORA_DISK_1: finished piece 1 at 02-JUL-15 piece handle=/u01/app/oracle/oradata/MYDBTEST/DB11G_2oqb2nkd_1_1 tag=TAG20150702T222829 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 channel ORA_DISK_1: deleting archived log(s) archived log file name=/u01/app/oracle/fast_recovery_area/DB11G/archivelog/2015_07_02/o1_mf_1_224_bscwtf9f_.arc thread=1 sequence=224 Finished backup at 02-JUL-15 Starting Control File and SPFILE Autobackup at 02-JUL-15 piecehandle=/u01/app/oracle/fast_recovery_area/DB11G/autobackup/2015_07_02/o1_mf_s_884039310_bscwtgy6_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 02-JUL-15

  • Create a pfile:

[oracle@srvprod ~]$ sqlplus / as sysdba

SQL> create pfile=’/home/oracle/initMYDBTEST.ora’ from spfile;

File created.

  • Copy the pfile, backup of controfile, and backups to srvdev:

[oracle@srvprod ~]$ scp initMYDBTEST.ora oracle@srvdev:/home/oracle [oracle@srvprod 2015_07_02]$ scp o1_mf_s_884039310_bscwtgy6_.bkp oracle@srvdev:/u01/app/oracle/fast_recovery_area/DB11G/autobackup/2015_07_02/

[oracle@srvprod oradata]$ scp -r MYDBTEST oracle@srvdev:/u01/app/oracle/oradata/

  • On srvdev make the necessary modifications on pfile (remember to create the necessary directories):

MYDBTEST.__db_cache_size=285212672 MYDBTEST.__java_pool_size=16777216 MYDBTEST.__large_pool_size=33554432 MYDBTEST.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment MYDBTEST.__pga_aggregate_target=771751936 MYDBTEST.__sga_target=503316480 MYDBTEST.__shared_io_pool_size=0 MYDBTEST.__shared_pool_size=150994944 MYDBTEST.__streams_pool_size=0 *.audit_file_dest=’/u01/app/oracle/admin/MYDBTEST/adump’ *.audit_trail=’db’ *.compatible=’11.2.0.4.0′ *.control_files=’/u01/app/oracle/oradata/MYDBTEST/control01.ctl’ *.db_block_size=8192 *.db_domain=” *.db_name=’DB11G’ *.db_unique_name=’MYDBTEST’ *.db_recovery_file_dest=’/u01/app/oracle/MYDBTEST/fast_recovery_area’ *.db_recovery_file_dest_size=4385144832 *.diagnostic_dest=’/u01/app/oracle’ *.dispatchers='(PROTOCOL=TCP) (SERVICE=MYDBTESTXDB)’ *.log_archive_format=’%t_%s_%r.arc’ *.log_archive_max_processes=30 *.memory_target=1261436928 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile=’EXCLUSIVE’ *.standby_file_management=’AUTO’ *.undo_tablespace=’UNDOTBS1′

  • Now log into the srvdev and start the database in nomount mode from pfile.

[oracle@srvdev ~]$ sqlplus / as sysdba

Connected to an idle instance.

SQL> startup nomount pfile=’/home/oracle/initMYDBTEST.ora’;

  • Now log into rman and make the restore of controlfile and put the database on mount mode:

[oracle@srvdev ~]$ rman target /

connected to target database: DB11G (not mounted)

RMAN> restore controlfile from ‘/u01/app/oracle/fast_recovery_area/DB11G/autobackup/2015_07_02/o1_mf_s_884039310_bscwtgy6_.bkp’;

Starting restore at 02-JUL-15 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=19 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:02 output file name=/u01/app/oracle/oradata/MYDBTEST/control01.ctl Finished restore at 02-JUL-15

RMAN> alter database mount;

database mounted released channel: ORA_DISK_1

  • Change the redologs paths:

[oracle@srvdev ~]$ sqlplus / as sysdba

SQL> alter database rename file ‘/u01/app/oracle/oradata/DB11G/redo01.log’ to ‘/u01/app/oracle/oradata/MYDBTEST/redo01.log’;

SQL> alter database rename file ‘/u01/app/oracle/oradata/DB11G/redo02.log’ to ‘/u01/app/oracle/oradata/MYDBTEST/redo02.log’;

SQL> alter database rename file ‘/u01/app/oracle/oradata/DB11G/redo03.log’ to ‘/u01/app/oracle/oradata/MYDBTEST/redo03.log’;

Database altered.

  • Now start the restore process:

RMAN> run { set newname for datafile 1 to ‘/u01/app/oracle/oradata/MYDBTEST/system01.dbf’ ; set newname for datafile 2 to ‘/u01/app/oracle/oradata/MYDBTEST/sysaux01.dbf’ ; set newname for datafile 3 to ‘/u01/app/oracle/oradata/MYDBTEST/undotbs01.dbf’ ; set newname for datafile 4 to ‘/u01/app/oracle/oradata/MYDBTEST/users01.dbf’ ; set newname for datafile 5 to ‘/u01/app/oracle/oradata/MYDBTEST/example01.dbf’ ; set newname for datafile 6 to ‘/u01/app/oracle/oradata/MYDBTEST/tbs_vendas.dbf’ ; restore database ; switch datafile all ; recover database ; }2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12>

executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting restore at 02-JUL-15 Starting implicit crosscheck backup at 02-JUL-15 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=19 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=20 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=21 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=22 device type=DISK allocated channel: ORA_DISK_5 channel ORA_DISK_5: SID=23 device type=DISK Crosschecked 5 objects Finished implicit crosscheck backup at 02-JUL-15 Starting implicit crosscheck copy at 02-JUL-15 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4 using channel ORA_DISK_5 Finished implicit crosscheck copy at 02-JUL-15 searching for all files in the recovery area cataloging files… no files cataloged using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4 using channel ORA_DISK_5 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/MYDBTEST/tbs_vendas.dbf channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/DB11G/tbs_vendas.dbf channel ORA_DISK_1: reading from backup piece /u01/app/oracle/oradata/MYDBTEST/DB11G_2nqb2nho_1_1 channel ORA_DISK_2: starting datafile backup set restore channel ORA_DISK_2: specifying datafile(s) to restore from backup set channel ORA_DISK_2: restoring datafile 00002 to /u01/app/oracle/oradata/MYDBTEST/sysaux01.dbf channel ORA_DISK_2: restoring datafile 00003 to /u01/app/oracle/oradata/MYDBTEST/undotbs01.dbf channel ORA_DISK_2: reading from backup piece /u01/app/oracle/oradata/MYDBTEST/DB11G_2mqb2nho_1_1 channel ORA_DISK_3: starting datafile backup set restore channel ORA_DISK_3: specifying datafile(s) to restore from backup set channel ORA_DISK_3: restoring datafile 00001 to /u01/app/oracle/oradata/MYDBTEST/system01.dbf channel ORA_DISK_3: restoring datafile 00004 to /u01/app/oracle/oradata/MYDBTEST/users01.dbf channel ORA_DISK_3: reading from backup piece /u01/app/oracle/oradata/MYDBTEST/DB11G_2lqb2nho_1_1 channel ORA_DISK_1: piece handle=/u01/app/oracle/oradata/MYDBTEST/DB11G_2nqb2nho_1_1 tag=TAG20150702T222703 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:25 channel ORA_DISK_2: piece handle=/u01/app/oracle/oradata/MYDBTEST/DB11G_2mqb2nho_1_1 tag=TAG20150702T222703 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:01:15 channel ORA_DISK_3: piece handle=/u01/app/oracle/oradata/MYDBTEST/DB11G_2lqb2nho_1_1 tag=TAG20150702T222703 channel ORA_DISK_3: restored backup piece 1 channel ORA_DISK_3: restore complete, elapsed time: 00:01:25 Finished restore at 02-JUL-15 datafile 1 switched to datafile copy input datafile copy RECID=12 STAMP=884040250 file name=/u01/app/oracle/oradata/MYDBTEST/system01.dbf datafile 2 switched to datafile copy input datafile copy RECID=13 STAMP=884040250 file name=/u01/app/oracle/oradata/MYDBTEST/sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID=14 STAMP=884040250 file name=/u01/app/oracle/oradata/MYDBTEST/undotbs01.dbf datafile 4 switched to datafile copy input datafile copy RECID=15 STAMP=884040250 file name=/u01/app/oracle/oradata/MYDBTEST/users01.dbf datafile 5 switched to datafile copy input datafile copy RECID=16 STAMP=884040250 file name=/u01/app/oracle/oradata/MYDBTEST/tbs_vendas.dbf Starting recover at 02-JUL-15 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4 using channel ORA_DISK_5 starting media recovery archived log for thread 1 with sequence 224 is already on disk as file /u01/app/oracle/fast_recovery_area/DB11G/archivelog/2015_07_02/o1_mf_1_224_bscwtf9f_.arc archived log file name=/u01/app/oracle/fast_recovery_area/DB11G/archivelog/2015_07_02/o1_mf_1_224_bscwtf9f_.arc thread=1 sequence=224 unable to find archived log archived log thread=1 sequence=225

…………………………………… If the media recovery show an error for you, you will requires archive logs. Copy the new archivelogs from srvprod to srvdev and return to the rman command for recover database. You can ignore this error if you prefer. ……………………………………

RMAN> alter database open resetlogs;

database opened

  • Now log into sqlplus on srvdev and create a spfile.

SQL> create spfile from pfile=’/home/oracle/initMYDBTEST.ora’; File created.

SQL> !hostname

srvdev.localdomain

  • Now we will start the process to change the name of the database. Run the select below on srvdev and you will see the DB11G name instead of MYDBTEST.

[oracle@srvdev ~]$ sqlplus / as sysdba

SQL> select name from v$database;

NAME

DB11G

  • Log into srvprod and run the commands the below:

[oracle@srvprod ~]$ sqlplus / as sysdba

SQL> alter database backup controlfile to trace;

  • Alter the necessary parameters.
  • On srvdev run the commands below:

[oracle@srvdev ~]$ sqlplus / as sysdba

SQL> startup nomount

SQL> ALTER SYSTEM SET DB_NAME=MYDBTEST SCOPE=SPFILE; System altered.

SQL> SHUTDOWN ABORT;

  • Drop or rename the control01.ctl from the current path.

[oracle@srvdev ~]$ rm -rf /u01/app/oracle/oradata/MYDBTEST/control01.ctl

[oracle@srvdev ~]$ sqlplus / as sysdba

SQL> STARTUP NOMOUNT

CREATE CONTROLFILE SET DATABASE “MYDBTEST” LOGFILE GROUP 1 ‘/u01/app/oracle/oradata/MYDBTEST/redo01.log’ SIZE 50M BLOCKSIZE 512, GROUP 2 ‘/u01/app/oracle/oradata/MYDBTEST/redo02.log’ SIZE 50M BLOCKSIZE 512, GROUP 3 ‘/u01/app/oracle/oradata/MYDBTEST/redo03.log’ SIZE 50M BLOCKSIZE 512 RESETLOGS DATAFILE ‘/u01/app/oracle/oradata/MYDBTEST/system01.dbf’, ‘/u01/app/oracle/oradata/MYDBTEST/sysaux01.dbf’, ‘/u01/app/oracle/oradata/MYDBTEST/undotbs01.dbf’, ‘/u01/app/oracle/oradata/MYDBTEST/users01.dbf’, ‘/u01/app/oracle/oradata/MYDBTEST/example01.dbf’, ‘/u01/app/oracle/oradata/MYDBTEST/tbs_vendas.dbf’ MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 CHARACTER SET WE8MSWIN1252 SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Control file created.

SQL> alter database open resetlogs;

Database altered.

SQL> SELECT NAME FROM V$DATABASE;

NAME

MYDBTEST

Well this type of activity it is very important for DBAs. I hope that you enjoy. Cheers! Felipe.

2 thoughts on “Restore database on another server and change database name (Linux)

  1. Pat Cummings 10 de July de 2015 / 18:13

    Felipe,
    thank you very much for posting this article. I was interested in your technique because I just did this same procedure to refresh our training server. I may have missed it but I did not see where you copied (scp) the rman backup files from svrprod to svrdev in the “/u01/app/oracle/oradata/MYDBTEST” directory. Also, I used the oracle dbnewid program to rename the database versus the control file method you used. Is there a reason you didn’t use dbnewid.

    Liked by 1 person

    • Felipe Leal 13 de July de 2015 / 14:19

      Hi Pat, thank you very much for your question. It is very important for me!

      About the SCP I am only mentioning. But I did this update at the post.
      When the DBID of a database is changed, all previous backups and archived logs of the database become unusable. This is similar to creating a database except that the data is already in the data files. After you change the DBID, backups and archive logs that were created before the change can no longer be used because they still have the original DBID, which does not match the current DBID. You must open the database with the RESETLOGS option, which re-creates the online redo logs and resets their sequence to 1. Consequently, you should make a backup of the whole database immediately after changing the DBID.

      Changing the DBNAME without changing the DBID does not require you to open with the RESETLOGS option, so database backups and archived logs are not invalidated. However, changing the DBNAME does have consequences.You must change the DB_NAME initialization parameter after a database name change to reflect the new name.
      Also, you may have to re-create the Oracle password file. If you restore an old backup of the control file (before the name change), then you should use the initialization parameter file and password file from before the database name change.
      If you maintain the same DBID you can use any period of the backups from database (but you have to do some changes).

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s