RMAN duplicate. How complicated can we make this? How about upgrading from 11 to 12 and chuck in different file and log locations

I’ve recently worked with a client who need to quickly upgrade and migrate a database to a new server. They needed to minimise downtime and couldn’t offer any additional disk space on the new server to accommodate a backup. I considered taking an export, compressing it and using that as there would have been just enough space, but then I felt that an RMAN duplicate was a better option with less downtime.

This guide is to migrate and upgrade an Oracle 11.2 standard edition database to a new server running Oracle 12.1 with different file locations using RMAN duplication.

Database and server names are:

Source

Server – n4test11
DB – test11
Version – 11.2.0.4

Destination

Server – n4test12
DB – test12
Version – 12.1.0.1

 

 

Notes on naming convention

Oracle seems to have used an odd naming convention for the databases taking part in a duplication. Please bear this in mind if there is any confusion as to which is which!

TARGET – SOURCE

AUXILIARY – DESTINATION

 

High Level Steps

Prepare auxiliary (destination server)

  • TNSNAMES.ora file
  • Static listener registration
  • Modify SPFILE copied from target (source)
  • Create a password file
  • Check connectivity

Prepare target (source server)

  • TNSNAMES.ora file
  • Static listener registration
  • Enable archivelog mode
  • Check connectivity
  • Run pre-upgrade check and fix any errors
  • Run the duplicate command
  • Run the upgrade scripts

Prepare auxiliary

TNSNAMES.ora file

Add entries for both the target and auxiliary in the tnsnames.ora file:

TEST11 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = n4test11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = n4test11)
    )
  )

TEST12 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = n4test12)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = n4test12)
    )
  )


Static listener registration

Add an entry into your listener.ora file. The location will depend on if you are using Oracle Grid or not and can usually be found by running lsnrctl status if you are unsure. If this doesn’t work, you can try checking the Service properties for the running listener if you are on Windows, or for Linux, you can run ps -ef|grep tns to get the location of where the listener is running from.

SID_LIST_LISTENER =
  (SID_LIST =
    )
    (SID_DESC =
      (SID_NAME = TEST12)
      (ORACLE_HOME = /app/oracle/product/12.2.0/db_1)
      (GLOBAL_DBNAME = TEST12)
    )
  )

Now reload the listener to pick up the new configuration, don’t forget to open Command Prompt as Admin if using windows:

Lsnrctl reload

 

Modify SPFILE copied from target

Connect to the target (source) database and create a pfile from spfile (assuming you are using an spfile, if not skip this step):

Create pfile from spfile;

This file will be created in %ORACLE_HOME%/database on Windows or $ORACLE_HOME/dbs on linux.

Copy the file to the same location on the auxiliary server, but rename it to match your database name.

Modify the file to reflect any changes you need making such as the database name, file locations etc. Here are the changes I made to reflect the different file locations and database name. Make sure you update all references to your source database to the destination:

*.audit_file_dest='/oracle/admin/test12/adump'
*.diagnostic_dest='/oracle/diag'
*.compatible='12.1.0.1.0'
*.control_files='/oradata/test12/control01.ctl','/oradata/ test12/control02.ctl'
*.db_name=' test12'
*.db_recovery_file_dest='/oradata/test12'
*.db_create_file_dest='/oradata/test12/data'
*.db_log_file_dest_1='/oradata/test12/log'

 

Shutdown and open the auxiliary database in nomount mode using the new pfile, then create an spfile from the pfile to allow RMAN to make any needed modifications:

Shutdown immediate;
Startup nomount pfile=/app/oracle/product/12.2.0/db_1/dbs/initTEST12.ora
Create spfile from pfile;

 

Create a password file

You will only need to do this if the database you are duplicating to is new and empty. If you’ve created it using DBCA for example then this step is unnecessary.

Set your ORACLE_HOME and ORACLE_SID:

Windows:

SET ORACLE_HOME=/app/oracle/product/12.2.0/db_1
SET ORACLE_SID=TEST12

cd $ORACLE_HOME/database

orapwd password=Password1 file=orapwTEST12

 

Linux:

. oraenv
TEST12
cd $ORACLE_HOME/dbs

orapwd password=Password1 file=orapwTEST12


Check connectivity

Check you can successfully connect to the TARGET database from AUXILIARY and to itself as RMAN does not use O/S auth:

sqlplus sys/Password1@TEST11 as sysdba

sqlplus sys/Password1@TEST12 as sysdba

 

Prepare target

TNSNAMES.ora file

Add entries for both the target and auxiliary in the tnsnames.ora file:

TEST11 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = n4test11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = n4test11)
    )
  )

TEST12 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = n4test12)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = n4test12)
    )
  )


Static listener registration

Add an entry into your listener.ora file. The location will depend on if you are using Oracle Grid or not and can usually be found by running lsnrctl status if you are unsure. If this doesn’t work, you can try checking the Service properties for the running listener if you are on Windows, or for Linux, you can run ps -ef|grep tns to get the location of where the listener is running from.

 

SID_LIST_LISTENER =
  (SID_LIST =
    )
    (SID_DESC =
      (SID_NAME = TEST11)
      (ORACLE_HOME = /app/oracle/product/11.2.0/db_1)
      (GLOBAL_DBNAME = TEST11)
    )
  )

Now reload the listener to pick up the new configuration, don’t forget to open Command Prompt as Admin if using windows:

Lsnrctl reload

 

Check connectivity

Check you can successfully connect to the AUXILIARY database from TARGET and to itself as RMAN does not use O/S auth:

sqlplus sys/Password1@TEST11 as sysdba

sqlplus sys/Password1@TEST12 as sysdba

 

Run pre-upgrade check and fix any errors

As you will be doing an upgrade with an exact copy of this database, you need to make sure it’s compatible. On the source/target database, run the pre-upgrade check script:

@$ORACLE_HOME/preupgrd.sql

 

Check the output carefully and fix any steps it states are USER ACTION REQUIRED.

 

Run the duplicate

From the auxiliary database, connect to RMAN using the TNSNAMES entries for both auxiliary and target:

Rman target sys/Password1@test11 auxiliary sys/Password1@test12

Now run the duplicate command, the noopen parameter at the end is important. It stops the database being automatically opened by RMAN at the end of the duplicate which allows us to manually open it in UPGRADE mode instead.

Duplicate target database to test12 noopen;

Run the upgrade steps

Once the duplicate command has completed successfully we can now carry out the upgrade steps as if we were doing a standard manual upgrade. Connect to the new 12c database and open it in upgrade mode:

alter database open resetlogs upgrade;

Now from the command line, run the upgrade script to complete the work:

cd $ORACLE_HOME/rdbms/admin/
$ORACLE_HOME/perl/bin/perl catctl.pl -n 4 -l /app/oracle/log catupgrd.sql

Once that has finished, check the dba_registry view to ensure the upgrade was successful.

Select * from dba_registry;

COMP_ID COMP_NAME                        SCHEMA      STATUS     VERSION
-------- ---------------------------------- ------------ ---------- ------------
CATALOG Oracle Database Catalog Views    SYS         VALID      12.1.0.2.0
CATJAVA Oracle Database Java Packages    SYS         VALID      12.1.0.2.0
CATPROC Oracle Database Packages and TypesSYS        VALID      12.1.0.2.0
JAVAVM  JServer JAVA Virtual Machine     SYS         VALID      12.1.0.2.0
OLS     Oracle Label Security            LBACSYS     VALID      12.1.0.2.0
OWM     Oracle Workspace Manager         WMSYS       VALID      12.1.0.2.0
RAC     Oracle Real Application Clusters SYS         VALID      12.1.0.2.0
XDB     Oracle XML Database              XDB         VALID      12.1.0.2.0
XML     Oracle XDK                       SYS         VALID      12.1.0.2.0

Leave a Reply