RMAN Restore on another machine with different file system- same database name

RMAN Restore on another machine with different file system – same database name

Primary/Target Machine			ITLINUXDEVBLADE08
Secondary/Auxiliary Machine		ITLINUXDEVBLADE07
Database Name				OPSDBA

Note

For this example, we will be using TSM in order to backup and duplicate the database to the new machine. Hence TSM needs to be configured on both the machines.

If you are using a disk backup solution and duplicate to a remote node you must first copy the backupsets from the original hosts backup location to the same mount and path on the remote server. Because duplication uses auxiliary channels the files must be where the IO pipe is allocated. So the IO will take place on the remote node and disk backups must be locally available.

Configure the listener.ora on the auxiliary machine and the tnsnames.ora on both the machines.

Listener.ora
=============
PRIMARY_OPSDBA =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = itlinuxdevblade07.hq.emirates.com)(PO
RT = 1527))
      )
    )
  )

SID_LIST_PRIMARY_OPSDBA =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_NAME = OPSDBA)
      (ORACLE_HOME = /opt/oracle/product10gpr2)
      (SID_NAME = opsdba)
    )
  )

tnsnames.ora
=============

opsdba =				Primary database connect string
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = itlinuxdevblade08.hq.emirates.com)(PORT =
 1527))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = opsdba)
    )
  )

opsdba_clone =				New connect string, but same name
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = itlinuxdevblade07.hq.emirates.com)(PORT =
 1527))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = opsdba)
    )
  )

Start the listener and make sure your are able to ping to both these entries form both the machines

opsdba:/opt/oracle>tnsping opsdba

TNS Ping Utility for Linux: Version 10.2.0.2.0 - Production on 30-JAN-2007 13:53:44

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = itlinuxdevblade08.hq.emirates.com)(PORT = 1527)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = opsdba)))
OK (0 msec)
opsdba:/opt/oracle>tnsping opsdba_clone

TNS Ping Utility for Linux: Version 10.2.0.2.0 - Production on 30-JAN-2007 13:53:50

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = itlinuxdevblade07.hq.emirates.com)(PORT = 1527)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = opsdba)))
OK (0 msec)
  • On the auxiliary machine, create the necessary directories for the database.
  • Copy the parameter file from the target machine to the auxiliary machine and change the parameters to point to the new directory structure. Changes may be required for the following entries:
    • audit_file_dest
    • background_dump_dest
    • control_files
    • core_dump_dest
    • log_archive_dest_1
    • user_dump_dest
  • Make sure the value for parameter remote_login_passwordfile is set to exclusive
    	remote_login_passwordfile='EXCLUSIVE'
  • Create the password file for the database.
    orapwd file=orapwopsdba password=oracle entries=5

Note
You can use the parameters db_file_name_convert and log_file_name_convert and they will
generate the “set newname for datafile” syntax needed to change the controlfile. These parameters accommodate up to 5 different file placement locations using:

db_file_name_convert = ('/old/path1', '/new/path1',
                        '/old/path2', '/new/path2',
                        '/old/path3', '/new/path3')
log_file_name_convert = ('/old/path1', '/new/path1',
                         '/old/path2', '/new/path2',
                         '/old/path3', '/new/path3')

You can also create your own “set newname for datafile” syntax for all your files skipping these parameters all together. You can still use the log_file_name_convert parameter or you can use the ‘logfile’ command to specifiy where the logs will be created and what size.

For this exercise, I am doing the hard way, that is creating my own script.

  • Get the list of datafiles from the target database
    SQL > select file#, name from v$datafile;
    
    FILE# NAME
    ----- --------------------------------
        1 /u02/ORACLE/opsdba/system01.dbf
        2 /u02/ORACLE/opsdba/undotbs01.dbf
        3 /u02/ORACLE/opsdba/sysaux01.dbf
        4 /u02/ORACLE/opsdba/users01.dbf
        5 /u02/ORACLE/opsdba/users02.dbf
        6 /u02/ORACLE/opsdba/users03.dbf
        7 /u02/ORACLE/opsdba/users05.dbf
        8 /u02/ORACLE/opsdba/users06.dbf
        9 /u02/ORACLE/opsdba/users07.dbf
       10 /u02/ORACLE/opsdba/users04.dbf
       11 /u02/ORACLE/opsdba/drtbs1.dbf
       12 /u02/ORACLE/opsdba/drtbs2.dbf
       13 /tmp/undonew.dbf
    
  • Get the logfile sequence number and status from the target database:
    SQL> select SEQUENCE#, status from v$log;
    
     SEQUENCE# STATUS
    ---------- ----------------
             7 CURRENT
             5 INACTIVE
             6 INACTIVE
    
    
  • Create the script file as follows:
    New directory				/u01/ORACLE/opsdba
    Log sequence to recover until		5
    
    run
    {
    allocate channel ch1 type 'sbt_tape' parms
    'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opsdbad.opt)';
    allocate auxiliary channel aux1 type 'sbt_tape' parms
    'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opsdbad.opt)';
    set until logseq 5 thread 1;
    SET NEWNAME FOR DATAFILE 1 TO '/u01/ORACLE/opsdba/system01.dbf';
    SET NEWNAME FOR DATAFILE 2 TO '/u01/ORACLE/opsdba/undotbs01.dbf';
    SET NEWNAME FOR DATAFILE 3 TO '/u01/ORACLE/opsdba/sysaux01.dbf';
    SET NEWNAME FOR DATAFILE 4 TO '/u01/ORACLE/opsdba/users01.dbf';
    SET NEWNAME FOR DATAFILE 5 TO '/u01/ORACLE/opsdba/users02.dbf';
    SET NEWNAME FOR DATAFILE 6 TO '/u01/ORACLE/opsdba/users03.dbf';
    SET NEWNAME FOR DATAFILE 7 TO '/u01/ORACLE/opsdba/users05.dbf';
    SET NEWNAME FOR DATAFILE 8 TO '/u01/ORACLE/opsdba/users06.dbf';
    SET NEWNAME FOR DATAFILE 9 TO '/u01/ORACLE/opsdba/users07.dbf';
    SET NEWNAME FOR DATAFILE 10 TO '/u01/ORACLE/opsdba/users04.dbf';
    SET NEWNAME FOR DATAFILE 11 TO '/u01/ORACLE/opsdba/drtbs1.dbf';
    SET NEWNAME FOR DATAFILE 12 TO '/u01/ORACLE/opsdba/drtbs2.dbf';
    SET NEWNAME FOR DATAFILE 13 TO '/u01/ORACLE/opsdba/undonew.dbf';
    SET NEWNAME FOR TEMPFILE 1 TO '/u01/ORACLE/opsdba/temp01.dbf'; 
      DUPLICATE TARGET DATABASE TO opsdba
          LOGFILE
          GROUP 1 ('/u01/ORACLE/opsdba/redo01.log') SIZE 50M REUSE, 
          GROUP 2 ('/u01/ORACLE/opsdba/redo02.log') SIZE 50M REUSE,
          GROUP 3 ('/u01/ORACLE/opsdba/redo03.log') SIZE 50M REUSE;
    release channel ch1;
    release channel aux1;
    }
    
  • Start the auxiliary database in nomount state
    opsdba:/opt/oracle>sql
    
    SQL*Plus: Release 10.2.0.2.0 - Production on Tue Jan 30 14:53:11 2007
    
    Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.
    
    Connected to an idle instance.
    
    SQL> startup nomount
    ORACLE instance started.
    
    Total System Global Area  264241152 bytes
    Fixed Size                  2070416 bytes
    Variable Size             113248368 bytes
    Database Buffers          142606336 bytes
    Redo Buffers                6316032 bytes
    SQL>
    
  • On the primary machine, connect to RMAN and take a complete backup of the database and archivelog files.
    rman target / catalog rman10/rman10@rman10s
    
    Recovery Manager: Release 10.2.0.2.0 - Production on Tue Jan 30 14:21:17 2007
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    connected to target database: OPSDBA (DBID=1493612009)
    connected to recovery catalog database
    
    RMAN>run
    {allocate channel ch1 type 'sbt_tape' parms
    'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opsdbad.opt)';
    backup database plus archivelog;
    release channel ch1;
    }
    After backup is completed we can now proceed with cloning the database.
  • Connect to both the databases using RMAN and then use the script that we generated earlier.
    rman target / auxiliary sys/oracle@opsdba_clone catalog rman10/rman10@rman10s
    
    Recovery Manager: Release 10.2.0.2.0 - Production on Tue Jan 30 10:48:54 2007
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    connected to target database: OPSDBA (DBID=1493612009)
    connected to recovery catalog database
    connected to auxiliary database: OPSDBA (not mounted)
    
    RMAN>run
    {
    allocate channel ch1 type 'sbt_tape' parms
    'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opsdbad.opt)';
    allocate auxiliary channel aux1 type 'sbt_tape' parms
    'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opsdbad.opt)';
      set until logseq 5 thread 1;
      SET NEWNAME FOR DATAFILE 1 TO '/u01/ORACLE/opsdba/system01.dbf';
    SET NEWNAME FOR DATAFILE 2 TO '/u01/ORACLE/opsdba/undotbs01.dbf';
    SET NEWNAME FOR DATAFILE 3 TO '/u01/ORACLE/opsdba/sysaux01.dbf';
    SET NEWNAME FOR DATAFILE 4 TO '/u01/ORACLE/opsdba/users01.dbf';
    SET NEWNAME FOR DATAFILE 5 TO '/u01/ORACLE/opsdba/users02.dbf';
    SET NEWNAME FOR DATAFILE 6 TO '/u01/ORACLE/opsdba/users03.dbf';
    SET NEWNAME FOR DATAFILE 7 TO '/u01/ORACLE/opsdba/users05.dbf';
    SET NEWNAME FOR DATAFILE 8 TO '/u01/ORACLE/opsdba/users06.dbf';
    SET NEWNAME FOR DATAFILE 9 TO '/u01/ORACLE/opsdba/users07.dbf';
    SET NEWNAME FOR DATAFILE 10 TO '/u01/ORACLE/opsdba/users04.dbf';
    SET NEWNAME FOR DATAFILE 11 TO '/u01/ORACLE/opsdba/drtbs1.dbf';
    SET NEWNAME FOR DATAFILE 12 TO '/u01/ORACLE/opsdba/drtbs2.dbf';
    SET NEWNAME FOR DATAFILE 13 TO '/u01/ORACLE/opsdba/undonew.dbf';
    SET NEWNAME FOR TEMPFILE 1 TO '/u01/ORACLE/opsdba/temp01.dbf'; 
      DUPLICATE TARGET DATABASE TO opsdba
          LOGFILE
          GROUP 1 ('/u01/ORACLE/opsdba/redo01.log') SIZE 50M REUSE, 
          GROUP 2 ('/u01/ORACLE/opsdba/redo02.log') SIZE 50M REUSE,
          GROUP 3 ('/u01/ORACLE/opsdba/redo03.log') SIZE 50M REUSE;
    release channel ch1;
    release channel aux1;
    }
    
    allocated channel: ch1
    channel ch1: sid=156 devtype=SBT_TAPE
    channel ch1: Data Protection for Oracle: version 5.2.4.0
    
    allocated channel: aux1
    channel aux1: sid=156 devtype=SBT_TAPE
    channel aux1: Data Protection for Oracle: version 5.2.4.0
    
    executing command: SET until clause
    
    executing command: SET NEWNAME
    
    executing command: SET NEWNAME
    
    executing command: SET NEWNAME
    
    executing command: SET NEWNAME
    
    executing command: SET NEWNAME
    
    executing command: SET NEWNAME
    
    executing command: SET NEWNAME
    
    executing command: SET NEWNAME
    
    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 Duplicate Db at 30-JAN-07
    
    contents of Memory Script:
    {
       set until scn  3215195;
       set newname for datafile  1 to
     "/u01/ORACLE/opsdba/system01.dbf";
       set newname for datafile  2 to
     "/u01/ORACLE/opsdba/undotbs01.dbf";
       set newname for datafile  3 to
     "/u01/ORACLE/opsdba/sysaux01.dbf";
       set newname for datafile  4 to
     "/u01/ORACLE/opsdba/users01.dbf";
       set newname for datafile  5 to
     "/u01/ORACLE/opsdba/users02.dbf";
       set newname for datafile  6 to
     "/u01/ORACLE/opsdba/users03.dbf";
       set newname for datafile  7 to
     "/u01/ORACLE/opsdba/users05.dbf";
       set newname for datafile  8 to
     "/u01/ORACLE/opsdba/users06.dbf";
       set newname for datafile  9 to
     "/u01/ORACLE/opsdba/users07.dbf";
       set newname for datafile  10 to
     "/u01/ORACLE/opsdba/users04.dbf";
       set newname for datafile  11 to
     "/u01/ORACLE/opsdba/drtbs1.dbf";
       set newname for datafile  12 to
     "/u01/ORACLE/opsdba/drtbs2.dbf";
       set newname for datafile  13 to
     "/u01/ORACLE/opsdba/undonew.dbf";
       restore
       check readonly
       clone database
    ;
    }
    executing Memory Script
    .
    .
    .
    .
    .
    .
    .
    Starting restore at 30-JAN-07
    
    channel aux1: starting datafile backupset restore
    channel aux1: specifying datafile(s) to restore from backup set
    restoring datafile 00001 to /u01/ORACLE/opsdba/system01.dbf
    restoring datafile 00002 to /u01/ORACLE/opsdba/undotbs01.dbf
    restoring datafile 00003 to /u01/ORACLE/opsdba/sysaux01.dbf
    restoring datafile 00004 to /u01/ORACLE/opsdba/users01.dbf
    .
    .
    .
    Finished restore at 30-JAN-07
    sql statement: CREATE CONTROLFILE REUSE SET DATABASE "OPSDBA" RESETLOGS ARCHIVEL
    OG
      MAXLOGFILES     16
      MAXLOGMEMBERS      3
      MAXDATAFILES      100
      MAXINSTANCES     8
      MAXLOGHISTORY      292
     LOGFILE
      GROUP  1 ( '/u01/ORACLE/opsdba/redo01.log' ) SIZE 50 M  REUSE,
      GROUP  2 ( '/u01/ORACLE/opsdba/redo02.log' ) SIZE 50 M  REUSE,
      GROUP  3 ( '/u01/ORACLE/opsdba/redo03.log' ) SIZE 50 M  REUSE
     DATAFILE
      '/u01/ORACLE/opsdba/system01.dbf'
     CHARACTER SET WE8ISO8859P1
    
    
    contents of Memory Script:
    {
       switch clone datafile all;
    }
    executing Memory Script
    
    datafile 2 switched to datafile copy
    input datafile copy recid=1 stamp=613222231 filename=/u01/ORACLE/opsdba/undotbs0
    1.dbf
    datafile 3 switched to datafile copy
    input datafile copy recid=2 stamp=613222231 filename=/u01/ORACLE/opsdba/sysaux01
    .dbf
    .
    .
    .
    .
    datafile 13 switched to datafile copy
    input datafile copy recid=12 stamp=613222231 filename=/u01/ORACLE/opsdba/undonew
    .dbf
    
    contents of Memory Script:
    {
       set until scn  3215195;
       recover
       clone database
        delete archivelog
       ;
    }
    executing Memory Script
    
    Starting recover at 30-JAN-07
    
    starting media recovery
    
    channel aux1: starting archive log restore to default destination
    channel aux1: restoring archive log
    archive log thread=1 sequence=3
    channel aux1: reading from backup piece 5si8otd5_1_1
    channel aux1: restored backup piece 1
    piece handle=5si8otd5_1_1 tag=TAG20070130T010853
    channel aux1: restore complete, elapsed time: 00:00:02
    archive log filename=/u01/ORACLE/opsdba/arch/arch_1_3_613184450.dbf thread=1 seq
    uence=3
    channel clone_default: deleting archive log(s)
    .
    .
    .
    .
    .
    Finished recover at 30-JAN-07
    
    contents of Memory Script:
    {
       shutdown clone;
       startup clone nomount ;
    }
    executing Memory Script
    
    database dismounted
    Oracle instance shut down
    
    connected to auxiliary database (not started)
    Oracle instance started
    
    Total System Global Area     264241152 bytes
    
    Fixed Size                     2070416 bytes
    Variable Size                113248368 bytes
    Database Buffers             142606336 bytes
    Redo Buffers                   6316032 bytes
    sql statement: CREATE CONTROLFILE REUSE SET DATABASE "OPSDBA" RESETLOGS ARCHIVEL
    OG
      MAXLOGFILES     16
      MAXLOGMEMBERS      3
      MAXDATAFILES      100
      MAXINSTANCES     8
      MAXLOGHISTORY      292
     LOGFILE
      GROUP  1 ( '/u01/ORACLE/opsdba/redo01.log' ) SIZE 50 M  REUSE,
      GROUP  2 ( '/u01/ORACLE/opsdba/redo02.log' ) SIZE 50 M  REUSE,
      GROUP  3 ( '/u01/ORACLE/opsdba/redo03.log' ) SIZE 50 M  REUSE
     DATAFILE
      '/u01/ORACLE/opsdba/system01.dbf'
     CHARACTER SET WE8ISO8859P1
    
    
    contents of Memory Script:
    {
       set newname for tempfile  1 to
     "/u01/ORACLE/opsdba/temp01.dbf";
       switch clone tempfile all;
       catalog clone datafilecopy  "/u01/ORACLE/opsdba/undotbs01.dbf";
       catalog clone datafilecopy  "/u01/ORACLE/opsdba/sysaux01.dbf";
       catalog clone datafilecopy  "/u01/ORACLE/opsdba/users01.dbf";
       catalog clone datafilecopy  "/u01/ORACLE/opsdba/users02.dbf";
       catalog clone datafilecopy  "/u01/ORACLE/opsdba/users03.dbf";
       catalog clone datafilecopy  "/u01/ORACLE/opsdba/users05.dbf";
       catalog clone datafilecopy  "/u01/ORACLE/opsdba/users06.dbf";
       catalog clone datafilecopy  "/u01/ORACLE/opsdba/users07.dbf";
       catalog clone datafilecopy  "/u01/ORACLE/opsdba/users04.dbf";
       catalog clone datafilecopy  "/u01/ORACLE/opsdba/drtbs1.dbf";
       catalog clone datafilecopy  "/u01/ORACLE/opsdba/drtbs2.dbf";
       catalog clone datafilecopy  "/u01/ORACLE/opsdba/undonew.dbf";
       switch clone datafile all;
    }
    executing Memory Script
    
    executing command: SET NEWNAME
    
    renamed temporary file 1 to /u01/ORACLE/opsdba/temp01.dbf in control file
    
    cataloged datafile copy
    datafile copy filename=/u01/ORACLE/opsdba/undotbs01.dbf recid=1 stamp=613222327
    
    cataloged datafile copy
    datafile copy filename=/u01/ORACLE/opsdba/sysaux01.dbf recid=2 stamp=613222327
    .
    .
    .
    .
    .
    .
    datafile 13 switched to datafile copy
    input datafile copy recid=12 stamp=613222328 filename=/u01/ORACLE/opsdba/undonew
    .dbf
    
    contents of Memory Script:
    {
       Alter clone database open resetlogs;
    }
    executing Memory Script
    
    database opened
    Finished Duplicate Db at 30-JAN-07
    
    released channel: ch1
    
    Recovery Manager complete.
    

    Go to the auxiliary machine and you can check that the database is open and restored till the logsequence specified.

Visit Us On FacebookVisit Us On Google PlusVisit Us On Youtube