Wednesday, May 14, 2014

11gR2 Database Duplicate from Active Database to new Host

Here is a procedure to duplicate an 11gR2 database to a new host with the same O/S and DB version (in the example is 11.2.0.3) but to a different location using common area for all DB files (OMF).

The procedure is for Linux x86_64 and assuming the Oracle Software has been installed and Listener is running and that the directories used in the init.ora have been created and oracle user has full privileges on them.

Everything is executed from the new host


1. Create a custom initaux.ora or copy from the old host but the following parameters are required:

*.control_files='/oradata/aux/control01.ctl','/oradata/aux/control02.ctl'
*.db_block_size=8192
*.db_name='aux'
*.db_create_file_dest=/oradata/aux  # Instead of using DB_FILE_NAME_CONVERT , use OMF !!
*.db_recovery_file_dest='/oradata/aux/fra'
*.db_recovery_file_dest_size=10G
*.diagnostic_dest='/oradata'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=auxXDB)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
*.memory_target=2G
*.remote_login_passwordfile='EXCLUSIVE'

Copy the file to $ORACLE_HOME/dbs/initaux.ora 

2. Add static entry to the listener

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = aux
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = aux)
    )

Reload listener configuration: 
$ lsnrctl reload

3. Add TNSNAMES entries for TARGET and AUX and RMAN_CATALOG (if exists) in the new host tnsnames.ora

# If target usesd catalog from rman backups
RMAN_CATALOG=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rman_catalog_host)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rman_catalog)
    )
  )

TARGET =
  (DESCRIPTION =
    (address = (protocol = TCP)(host = target_host)(port = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = target)
    )
  )

AUX =
  (DESCRIPTION =
    (address = (protocol = TCP)(host = aux_host)(port = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = aux)
    )
  )

!!! ATTENTION !!! 
AUX & TARGET TNSNAMES ALIAS entry must also created on the old host TNSNAMES.ORA other wise RMAN session will fail with :

ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified
ORA-17629: Cannot connect to the remote database server

during copying the control file from TARGET in the first steps of the memory script execution.

4. Create a new password file for AUX to match the production one

$ rm $ORACLE_HOME/dbs/orapwaux
$ orapwd file=orapwaux password=pass

5. Start the auxiliary instance

$ export ORACLE_SID=aux 
$ sqlplus / as sysdba
SQL> startup nomount
SQL>exit;

6. Start RMAN and execute the CLONE creation script

!!! ATTENTION !!! 
Always use password when invoking RMAN otherwise session may fail

$ rman target sys@target/pass auxiliary sys@aux/pass catalog rman_catalog_user@rman_catalog/pass
RMAN>run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate auxiliary channel aux1 type disk;
allocate auxiliary channel aux2 type disk;
sql 'alter system switch logfile';
duplicate target database to aux from active database;
}

6. You have finsihed

After RMAN session is completed then AUX is up and running.

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Executing: alter database flashback on
Finished Duplicate Db at 14-MAY-14
released channel: c1
released channel: c2
released channel: aux1
released channel: aux2

RMAN>exit;

You can now create a new spfile in the default location and modify all the parameters you want by editing the pfile created in the following commands.

$ sqlplus / as sysdba
SQL>create pfile='/mylocation/mypfile' from memory;
SQL>shutdown immediate;
SQL>create spfile from pfile='/mylocation/mypfile';
SQL>startup