Upgrading to Oracle 12c using RMAN DUPLICATE with “NOOPEN” clause and “BACKUP LOCATION”

When cloning (duplicating) a database to a higher version, traditionally (prior to 12c), you must use restore/recover of an RMAN backup. This is because the database in the destination must be opened with UPGRADE option. Therefore, the RMAN duplicate could not be used because RMAN automatically opens the auxiliary database with resetlogs. This would fail with error ‘ORA-39700: database must be opened with UPGRADE option’.

In RMAN 12c, a new option is available “NOOPEN” requesting RMAN to complete the duplicate activity but NOT open the auxiliary database. This would allow for the ‘open upgrade’ option to be manually executed when going between versions.

Steps to duplicate a database to 12c

1. Execute Preupgrade script (preupgrd.sql) at 11g database. This script will validate 11g database for upgrade and provide recommendations. This script is available in the 12c $ORACLE_HOME/rdbms/admin directory. Note, both preupgrd.sql and utluppkg.sql (called by preupgrd.sql) must be copied from 12c.

To upgrade to 12.1 the minimum required version in each release are:

To upgrade to 12.2 the minimum required version in each release are:

2. Once Preupgrade checks are complete, take complete backup of 11g database:

RMAN> connect target / catalog [catalog_schema]/[password]@[catalog database]
RMAN> backup database format '/u01/source_backup/db_%U' plus archivelog format '/u01/source_backup/arch_%U';
RMAN> backup current controlfile format '/u01/source_backup/cf_%U'; 

This will generate a set of backupsets in /u01/source_backup directory.

3. Move these backup and pfile to 12c host. Using OS command like scp, we can copy the backup pieces to Destination server. For Example:

$ scp -p [backup-piece] user@[host]:/u01/dest_backup/

4. Create password file for 12c database. We can choose new name for 12c database using duplicate method:

$ORACLE_HOME/bin/orapwd file=orapw[sid] password=[password]

5. Edit copied pfile and make necessary changes. Set necessary values like:

db_name =   
controfile_name =
db_file_name_convert and log_file_name_convert ( if source and destination locations are different ) 

6. startup the Auxiliary instance in NOMOUNT:

Sql> connect / as sysdba
Sql> startup nomount

7. From RMAN connect this instance as AUXILIARY. For Example:

$ export ORACLE_SID=[12cdbxxx]
rman > connect auxiliary /

8. Using RMAN 12c, execute the duplicate command. In this example, ‘/u01/backup’ denotes RMAN backup location on destination. Note, “NOOPEN” option is specified.

RMAN> DUPLICATE DATABASE TO [new_DB] NOOPEN  BACKUP LOCATION '/u01/dest_backup/' ;

Once it completes exit from Rman.

Rman> exit

9. Once RMAN duplication completes, the auxiliary database remains in MOUNT mode. With sqlplus, you can open the database with UPGRADE option:

SQL> alter database open resetlogs upgrade;

Ensure the tempfiles have got created.

sql> Select * from v$tempfile ;

Also, check the files exist physically on disk.

10. Execute Manual upgrade scripts. In 12c manual upgrade can be done parallel using perl utility catctl.pl.

$ cd $ORACLE_HOME/rdbms/admin
$ ORACLE_HOME/perl/bin/perl  catctl.pl –n 4 –l /u01/upgrade/logs catupgrd.sql

Here,
n – number of parallel process to execute catupgrd.sql.
l – log location for catupgrd.sql. Based on number of process log files gets created (catupgrd.log to catupgrd0.log).

11. Execute post upgrade scripts depending on the version 12.1/12.2.

$ORACLE_HOME/perl/bin/perl  catctl.pl –n 4 –l /u01/upgrade/logs catuppst.sql

12. Check the database component status to confirm upgrade completion. Connect as SYS user to the database:

col comp_id format a10
col comp_name format a30
col version format a10
col status format a8
select substr(comp_id,1,15) comp_id,substr(comp_name,1,30) comp_name,substr(version,1,10) version,status from dba_registry;