This post list the steps for upgrading a 11.2.0.4 RAC database as a single instance 12.1.0.2 database using the backups of 11.2.0.4 DB. Only difference in this case to that of normal restore is, database is restored using the higher version (12.1.0.2) oracle binaries.
Following steps assume that a backup of the RAC database is taken and copied to server where 12.1.0.2 software is installed. A pfile that's compatible with a single instance database is created. These steps are similar to
restoring a RAC database to single instance.
1. Set the Oracle home to 12c and oracle SID to the database to be restored
$ echo $ORACLE_HOME
/opt/app/oracle/product/12.1.0/dbhome_2
$ export ORACLE_SID=std11g2
2. Start the DB using 12c binaries in nomount. Restore the control files and mount the database. At the same time update the pfile with control file details.
sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 27 11:36:25 2015
Connected to an idle instance.
SQL> startup nomount pfile='pfile.ora';
ORACLE instance started.
rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Mon Apr 27 11:36:57 2015
connected to target database: STD11G2 (not mounted)
RMAN> restore controlfile from '/home/oracle/backups/ctl2dq5eac3_1_1';
Starting restore at 27-APR-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=131 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/data/oradata/STD11G2/controlfile/o1_mf_bmw4btg1_.ctl
output file name=/data/flash_recovery/STD11G2/controlfile/o1_mf_bmw4btpg_.ctl
Finished restore at 27-APR-15
RMAN> alter database mount;
3. Restore and recover the database.
run {
set newname for database to new;
restore database;
switch datafile all;
recover database;
}4. Once the recovery has ended open the database in upgrade mode with resetlogs
RMAN> alter database open resetlogs upgrade;
using target database control file instead of recovery catalog
Statement processed
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-04023: Object SYS.STANDARD could not be validated or authorized
The warning and error could be ignored. Verify database is open in read write mode and instance status is migrate.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> select status from v$instance;
STATUS
------------
OPEN MIGRATE
5. Create a spfile from the pfile and then run the manual database upgrade from the 12c home. At the end of the upgrade database will be shutdown.
SQL> create spfile from pfile='/home/oracle/backups/pfile.ora';
cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catctl.pl -n 4 -l $ORACLE_HOME/diagnostics catupgrd.sql
Argument list for [catctl.pl]
SQL Process Count n = 4
SQL PDB Process Count N = 0
Input Directory d = 0
Phase Logging Table t = 0
Log Dir l = /opt/app/oracle/product/12.1.0/dbhome_2/diagnostics
Script s = 0
Serial Run S = 0
Upgrade Mode active M = 0
Start Phase p = 0
End Phase P = 0
Log Id i = 0
Run in c = 0
Do not run in C = 0
Echo OFF e = 1
No Post Upgrade x = 0
Reverse Order r = 0
Open Mode Normal o = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
Display Phases y = 0
Child Process I = 0
catctl.pl version: 12.1.0.2.0
Oracle Base = /opt/app/oracle
Analyzing file catupgrd.sql
Log files in /opt/app/oracle/product/12.1.0/dbhome_2/diagnostics
catcon: ALL catcon-related output will be written to /opt/app/oracle/product/12.1.0/dbhome_2/diagnostics/catupgrd_catcon_3508.lst
catcon: See /opt/app/oracle/product/12.1.0/dbhome_2/diagnostics/catupgrd*.log files for output generated by scripts
catcon: See /opt/app/oracle/product/12.1.0/dbhome_2/diagnostics/catupgrd_*.lst files for spool files, if any
Number of Cpus = 4
SQL Process Count = 4
------------------------------------------------------
Phases [0-73] Start Time:[2015_04_27 12:23:18]
------------------------------------------------------
Serial Phase #: 0 Files: 1 Time: 111s
Serial Phase #: 1 Files: 5 Time: 60s
Restart Phase #: 2 Files: 1 Time: 0s
Parallel Phase #: 3 Files: 18 Time: 15s
Restart Phase #: 4 Files: 1 Time: 0s
Serial Phase #: 5 Files: 5 Time: 25s
Serial Phase #: 6 Files: 1 Time: 18s
Serial Phase #: 7 Files: 4 Time: 11s
Restart Phase #: 8 Files: 1 Time: 0s
Parallel Phase #: 9 Files: 62 Time: 44s
Restart Phase #:10 Files: 1 Time: 0s
Serial Phase #:11 Files: 1 Time: 19s
Restart Phase #:12 Files: 1 Time: 0s
Parallel Phase #:13 Files: 91 Time: 11s
Restart Phase #:14 Files: 1 Time: 0s
Parallel Phase #:15 Files: 111 Time: 21s
Restart Phase #:16 Files: 1 Time: 0s
Serial Phase #:17 Files: 3 Time: 1s
Restart Phase #:18 Files: 1 Time: 0s
Parallel Phase #:19 Files: 32 Time: 25s
Restart Phase #:20 Files: 1 Time: 0s
Serial Phase #:21 Files: 3 Time: 8s
Restart Phase #:22 Files: 1 Time: 0s
Parallel Phase #:23 Files: 23 Time: 100s
Restart Phase #:24 Files: 1 Time: 0s
Parallel Phase #:25 Files: 11 Time: 45s
Restart Phase #:26 Files: 1 Time: 1s
Serial Phase #:27 Files: 1 Time: 0s
Restart Phase #:28 Files: 1 Time: 0s
Serial Phase #:30 Files: 1 Time: 0s
Serial Phase #:31 Files: 257 Time: 25s
Serial Phase #:32 Files: 1 Time: 0s
Restart Phase #:33 Files: 1 Time: 0s
Serial Phase #:34 Files: 1 Time: 5s
Restart Phase #:35 Files: 1 Time: 0s
Restart Phase #:36 Files: 1 Time: 0s
Serial Phase #:37 Files: 4 Time: 60s
Restart Phase #:38 Files: 1 Time: 0s
Parallel Phase #:39 Files: 13 Time: 61s
Restart Phase #:40 Files: 1 Time: 1s
Parallel Phase #:41 Files: 10 Time: 7s
Restart Phase #:42 Files: 1 Time: 0s
Serial Phase #:43 Files: 1 Time: 7s
Restart Phase #:44 Files: 1 Time: 0s
Serial Phase #:45 Files: 1 Time: 6s
Serial Phase #:46 Files: 1 Time: 0s
Restart Phase #:47 Files: 1 Time: 0s
Serial Phase #:48 Files: 1 Time: 108s
Restart Phase #:49 Files: 1 Time: 0s
Serial Phase #:50 Files: 1 Time: 38s
Restart Phase #:51 Files: 1 Time: 0s
Serial Phase #:52 Files: 1 Time: 0s
Restart Phase #:53 Files: 1 Time: 1s
Serial Phase #:54 Files: 1 Time: 106s
Restart Phase #:55 Files: 1 Time: 0s
Serial Phase #:56 Files: 1 Time: 75s
Restart Phase #:57 Files: 1 Time: 0s
Serial Phase #:58 Files: 1 Time: 1s
Restart Phase #:59 Files: 1 Time: 0s
Serial Phase #:60 Files: 1 Time: 0s
Restart Phase #:61 Files: 1 Time: 0s
Serial Phase #:62 Files: 1 Time: 17s
Restart Phase #:63 Files: 1 Time: 0s
Serial Phase #:64 Files: 1 Time: 2s
Serial Phase #:65 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/opt/app/oracle/product/12.1.0/dbhome_2/lib; export LD_LIBRARY_PATH; LIBPATH=/opt/app/oracle/product/12.1.0/dbhome_2/lib; export LIBPATH; LD_LIBRARY_PATH_64=/opt/app/oracle/product/12.1.0/dbhome_2/lib; export LD_LIBRARY_PATH_64; DYLD_LIBRARY_PATH=/opt/app/oracle/product/12.1.0/dbhome_2/lib; export DYLD_LIBRARY_PATH; /opt/app/oracle/product/12.1.0/dbhome_2/perl/bin/perl -I /opt/app/oracle/product/12.1.0/dbhome_2/rdbms/admin -I /opt/app/oracle/product/12.1.0/dbhome_2/rdbms/admin/../../sqlpatch /opt/app/oracle/product/12.1.0/dbhome_2/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose -upgrade_mode_only > /opt/app/oracle/product/12.1.0/dbhome_2/diagnostics/catupgrd_datapatch_upgrade.log 2> /opt/app/oracle/product/12.1.0/dbhome_2/diagnostics/catupgrd_datapatch_upgrade.err
returned from sqlpatch
Time: 44s
Serial Phase #:66 Files: 1 Time: 33s
Serial Phase #:68 Files: 1 Time: 0s
Serial Phase #:69 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/opt/app/oracle/product/12.1.0/dbhome_2/lib; export LD_LIBRARY_PATH; LIBPATH=/opt/app/oracle/product/12.1.0/dbhome_2/lib; export LIBPATH; LD_LIBRARY_PATH_64=/opt/app/oracle/product/12.1.0/dbhome_2/lib; export LD_LIBRARY_PATH_64; DYLD_LIBRARY_PATH=/opt/app/oracle/product/12.1.0/dbhome_2/lib; export DYLD_LIBRARY_PATH; /opt/app/oracle/product/12.1.0/dbhome_2/perl/bin/perl -I /opt/app/oracle/product/12.1.0/dbhome_2/rdbms/admin -I /opt/app/oracle/product/12.1.0/dbhome_2/rdbms/admin/../../sqlpatch /opt/app/oracle/product/12.1.0/dbhome_2/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose > /opt/app/oracle/product/12.1.0/dbhome_2/diagnostics/catupgrd_datapatch_normal.log 2> /opt/app/oracle/product/12.1.0/dbhome_2/diagnostics/catupgrd_datapatch_normal.err
returned from sqlpatch
Time: 57s
Serial Phase #:70 Files: 1 Time: 124s
Serial Phase #:71 Files: 1 Time: 1s
Serial Phase #:72 Files: 1 Time: 0s
Serial Phase #:73 Files: 1 Use of uninitialized value $gsRTInclusion in concatenation (.) or string at catctl.pl line 1500.
Time: 19s
------------------------------------------------------
Phases [0-73] End Time:[2015_04_27 12:45:11]
------------------------------------------------------
Grand Total Time: 1315s
LOG FILES: (catupgrd*.log)
Upgrade Summary Report Located in:
/opt/app/oracle/product/12.1.0/dbhome_2/cfgtoollogs/std11g2/upgrade/upg_summary.log
Grand Total Upgrade Time: [0d:0h:21m:55s]Upgrade summary log showed the following.
Oracle Database 12.1 Post-Upgrade Status Tool 04-27-2015 12:50:10
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
Oracle Server UPGRADED 12.1.0.2.0 00:11:25
JServer JAVA Virtual Machine VALID 12.1.0.2.0 00:01:47
Oracle Real Application Clusters OPTION OFF 12.1.0.2.0 00:00:01
Oracle Workspace Manager VALID 12.1.0.2.0 00:01:00
Oracle XDK VALID 12.1.0.2.0 00:00:38
Oracle XML Database VALID 12.1.0.2.0 00:01:46
Oracle Database Java Packages VALID 12.1.0.2.0 00:00:13
Final Actions 00:01:16
Post Upgrade 00:02:02
6. Start the database and verify the registry entries.
SQL> select action_time,action,version,comments from registry$history;
ACTION_TIME ACTION VERSION COMMENTS
---------------------------------------- ------------------------------ ------------------------------ ----------------------------------------
22-JAN-15 05.21.55.225164 PM APPLY 11.2.0.4 PSU 11.2.0.4.5
27-APR-15 12.40.55.455359 PM VIEW INVALIDATE view invalidation
27-APR-15 12.41.48.624963 PM UPGRADE 12.1.0.2.0 Upgraded from 11.2.0.4.0
SQL> select comp_name,version from dba_registry;
COMP_NAME VERSION
-------------------------------------------------- ------------------------------
Oracle XML Database 12.1.0.2.0
Oracle Workspace Manager 12.1.0.2.0
Oracle Database Catalog Views 12.1.0.2.0
Oracle Database Packages and Types 12.1.0.2.0
JServer JAVA Virtual Machine 12.1.0.2.0
Oracle XDK 12.1.0.2.0
Oracle Database Java Packages 12.1.0.2.0
Oracle Real Application Clusters 12.1.0.2.0
7. Follow step 11 on
Restore RAC DB Backup as a Single Instance DB post to carry out the clean up of additional thread and undo tablespaces.
8. If upgrade is satisfactory update the compatible parameter to 12c.
This conclude the upgrading to 12c using 11gR2 backups.
Useful metalink notes
RMAN Restore of Backups as Part of a Database Upgrade
[ID 790559.1]
Related Posts
Upgrading RAC from 11.2.0.4 to 12.1.0.2 - Database
Upgrading 11.2.0.3 (11gR2) Database to 12.1.0.1 (12c) Using DBUA
Upgrading from 11.2.0.3 to 12.1.0.1 RAC
Upgrade Oracle Database 12c1 from 12.1.0.1 to 12.1.0.2
Upgrading 12c CDB and PDB from 12.1.0.1 to 12.1.0.2
Update 9th October 2015
When upgrading from 11.1.0.7 to 11.2.0.3 using RMAN backup the open resetlog upgrade fails with following
RMAN> alter database open resetlogs upgrade;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "upgrade": expecting one of: ";"
RMAN-01007: at line 1 column 31 file: standard input
Open without upgrade option fails with the following
RMAN> alter database open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 10/09/2015 14:55:01
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Process ID: 10306
Session ID: 191 Serial number: 9
However this does create the redo logs and database could be opened in the 11.2 home with startup upgrade and run the catupgrd.sql to manually upgrade the database.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2233000 bytes
Variable Size 230690136 bytes
Database Buffers 599785472 bytes
Redo Buffers 6574080 bytes
Database mounted.
Database opened.
log files are craeted.
SQL> select status from v$instance;
STATUS
------------
OPEN MIGRATE
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/data/oradata/RACSE11G1/onlinelog/o1_mf_1_c1h77yk9_.log
/data/flash_recovery/RACSE11G1/onlinelog/o1_mf_1_c1h77yph_.log
/data/oradata/RACSE11G1/onlinelog/o1_mf_2_c1h77zrt_.log
/data/flash_recovery/RACSE11G1/onlinelog/o1_mf_2_c1h77zwt_.log
/data/oradata/RACSE11G1/onlinelog/o1_mf_3_c1h78106_.log
/data/flash_recovery/RACSE11G1/onlinelog/o1_mf_3_c1h7814n_.log
/data/oradata/RACSE11G1/onlinelog/o1_mf_4_c1h7826v_.log
/data/flash_recovery/RACSE11G1/onlinelog/o1_mf_4_c1h782c0_.log
8 rows selected.
SQL>@utlu112i.sql
SQL>@?/rdbms/admin/catupgrd
SQL> select comp_name,version,status from dba_registry;
COMP_NAME VERSION STATUS
---------------------------------------- ------------------------------ --------
Oracle XML Database 11.2.0.3.0 VALID
Oracle Expression Filter 11.2.0.3.0 VALID
Oracle Rules Manager 11.2.0.3.0 VALID
Oracle Workspace Manager 11.2.0.3.0 VALID
Oracle Database Catalog Views 11.2.0.3.0 VALID
Oracle Database Packages and Types 11.2.0.3.0 VALID
JServer JAVA Virtual Machine 11.2.0.3.0 VALID
Oracle XDK 11.2.0.3.0 VALID
Oracle Database Java Packages 11.2.0.3.0 VALID
SQL> select action,version,comments from registry$history;
ACTION VERSION COMMENTS
------------------------------ ------------------------------ ------------------------------
APPLY 11.1.0.7 PSU 11.1.0.7.11
CPU view recompilation
APPLY 11.1.0.7 PSU 11.1.0.7.12
APPLY 11.1.0.7 PSU 11.1.0.7.13
APPLY 11.1.0.7 PSU 11.1.0.7.14
APPLY 11.1.0.7 PSU 11.1.0.7.15
APPLY 11.1.0.7 PSU 11.1.0.7.16
APPLY 11.1.0.7 PSU 11.1.0.7.17
APPLY 11.1.0.7 PSU 11.1.0.7.18
APPLY 11.1.0.7 PSU 11.1.0.7.19
APPLY 11.1.0.7 PSU 11.1.0.7.20
APPLY 11.1.0.7 PSU 11.1.0.7.21
jvmpsu.sql 11.1.0.7.2OJVMBP RAN jvmpsu.sql
APPLY 11.1.0.7.2OJVMBP OJVM PSU post-install
APPLY 11.1.0.7 PSU 11.1.0.7.22
VIEW INVALIDATE view invalidation
UPGRADE 11.2.0.3.0 Upgraded from 11.1.0.7.0
Related Posts
Upgrading to 12.2.0.1 Using 11.2.0.4 Backups
Upgrading to 12.2.0.1 Using 12.1.0.2 Backups
