Friday, May 1, 2015

Upgrading to 12.1.0.2 Using 11.2.0.4 Backups

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