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 (and higher), a new option is available, “NOOPEN”, requesting of RMAN to complete the duplicate activity but NOT open the auxiliary database. This allows for the ‘open upgrade’ option to be manually executed when cloning between versions.
“NOOPEN”: It specifies that the duplicate database must not be opened after duplicate is completed.
NOFILENAMECHECK Prevents RMAN from checking whether target datafiles sharing the same names as the duplicated files are in use. Note that the NOFILENAMECHECK option is required when the standby and primary datafiles and logs have identical filenames.
Let me duplicate a database for upgrade
1) Execute the pre-upgrade 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.2 the minimum required version in each release are;
Release Minimum require version
11gR2 18.104.22.168 / 22.214.171.124
12c 126.96.36.199 / 188.8.131.52
2) Once pre-upgrade checks are complete, take a complete backup of 11g database:
RMAN> connect target /
RMAN> backup database format ‘/orabackup/db_%U’ plus archivelog format ‘/orabackup/arch_%U’;
RMAN> backup current controlfile format ‘/orabackup/cf_%U’;
This will generate a set of backupsets in orabackup directory.
3) Move these backup and pfile to 12c host:
Using OS command like scp , we can copy the backup pieces to Destination server
scp -p user@:/orabackup/
4) Create password file for 12c database. We can choose new name for 12c database using duplicate method
$ORACLE_HOME/bin/orapwd file=orapw password=
5) Edit copied pfile and make necessary changes. Set necessary values like:
db_file_name_convert and log_file_name_convert (if source and target dbf 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
$ export ORACLE_SID=
RMAN> connect auxiliary
8) Using RMAN 12c, execute the duplicate command. In this example, ‘dest backup PATH’ denotes RMAN backup location on destination. Note, “NOOPEN” option is specified.
RMAN> DUPLICATE DATABASE TO NOOPEN BACKUP LOCATION ‘/orabackup/’ ;
Once it completes exit from Rman.
9) Once RMAN completes, the auxiliary database remains in MOUNT mode. Within sqlplus, you can open the database with UPGRADE option:
SQL> alter database open resetlogs upgrade;
10) Ensure the tempfiles were created in auxiliary.
SQ> select * from v$tempfile ;
Check the files exist physically on disk.
11) 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 /[path] catupgrd.sql
12) Execute post upgrade scripts (please refer Document reference given for 12.1 and 12.2 accordingly)
$ORACLE_HOME/perl/bin/perl catctl.pl –n 4 –l /[path] catuppst.sql
13) 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