Friday, July 14, 2017

Upgrading to 12.2.0.1 Using 11.2.0.4 Backups

This post list steps for upgrading a 11.2.0.4 single instance DB to 12.2.0.1 (non-CDB) using backups of the 11.2.0.4. There are two similar posts one for upgrading from 11.2.0.4 to 12.1.0.2 and another for upgrading from 12.1.0.2 to 12.2.0.1. It is assumed that a backup of the database is taken and copied to server where 12.2.0.1 software is installed.
1. Set the Oracle home to 12cR2 and oracle SID to the database to be restored.
echo $ORACLE_HOME
/opt/app/oracle/product/12.2.0/sehome_1

export ORACLE_SID=std11g4
2. Restore and recover the database
RMAN> startup nomount;
RMAN> create spfile from pfile;
RMAN> startup force nomount;

RMAN> restore controlfile from '/home/oracle/backup/control.bkp';
Starting restore at 13-JUN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=191 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/opt/app/oracle/oradata/STD11G4/controlfile/o1_mf_dmzw8m24_.ctl
output file name=/opt/app/oracle/fast_recovery/STD11G4/controlfile/o1_mf_dmzw8m6g_.ctl
Finished restore at 13-JUN-17

RMAN> alter database mount;
Statement processed
released channel: ORA_DISK_1

RMAN> crosscheck backupset;
Starting implicit crosscheck backup at 13-JUN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=156 device type=DISK
Crosschecked 4 objects
Finished implicit crosscheck backup at 13-JUN-17

Starting implicit crosscheck copy at 13-JUN-17
using channel ORA_DISK_1
Finished implicit crosscheck copy at 13-JUN-17

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/usr/local/backups/db0rs6mrr3_1_1.bkp RECID=25 STAMP=946564963
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/usr/local/backups/db0ss6mrto_1_1.bkp RECID=26 STAMP=946565048
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/usr/local/backups/db0ts6mrtp_1_1.bkp RECID=27 STAMP=946565049
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/data/flash_recovery/STD11G4/autobackup/2017_06_13/o1_mf_s_946564859_dmzv1c4q_.bkp RECID=28 STAMP=946565051
Crosschecked 4 objects


RMAN> delete noprompt expired backup;
using channel ORA_DISK_1
deleted backup piece
backup piece handle=/usr/local/backups/db0rs6mrr3_1_1.bkp RECID=25 STAMP=946564963
deleted backup piece
backup piece handle=/usr/local/backups/db0ss6mrto_1_1.bkp RECID=26 STAMP=946565048
deleted backup piece
backup piece handle=/usr/local/backups/db0ts6mrtp_1_1.bkp RECID=27 STAMP=946565049
deleted backup piece
backup piece handle=/data/flash_recovery/STD11G4/autobackup/2017_06_13/o1_mf_s_946564859_dmzv1c4q_.bkp RECID=28 STAMP=946565051
Deleted 4 EXPIRED objects


RMAN> catalog  start with '/home/oracle/backup/' noprompt;
searching for all files that match the pattern /home/oracle/backup/

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/backup/res.rmn
File Name: /home/oracle/backup/control.bkp
File Name: /home/oracle/backup/db0rs6mrr3_1_1.bkp
File Name: /home/oracle/backup/db0ss6mrto_1_1.bkp
File Name: /home/oracle/backup/db0ts6mrtp_1_1.bkp
File Name: /home/oracle/backup/pfile.ora
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/backup/control.bkp
File Name: /home/oracle/backup/db0rs6mrr3_1_1.bkp
File Name: /home/oracle/backup/db0ss6mrto_1_1.bkp
File Name: /home/oracle/backup/db0ts6mrtp_1_1.bkp

List of Files Which Were Not Cataloged
=======================================
File Name: /home/oracle/backup/res.rmn
  RMAN-07517: Reason: The file header is corrupted
File Name: /home/oracle/backup/pfile.ora
  RMAN-07517: Reason: The file header is corrupted

RMAN> run {
2> restore database;
3> recover database;
4> }
Starting restore at 13-JUN-17
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /data/oradata/STD11G4/datafile/o1_mf_system_c783t3v8_.dbf
channel ORA_DISK_1: restoring datafile 00002 to /data/oradata/STD11G4/datafile/o1_mf_sysaux_c783t3vy_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /data/oradata/STD11G4/datafile/o1_mf_undotbs1_c783t3x3_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /data/oradata/STD11G4/datafile/o1_mf_users_c783t3ss_.dbf
channel ORA_DISK_1: restoring datafile 00005 to /data/oradata/STD11G4/datafile/o1_mf_tools_c783t3rr_.dbf
channel ORA_DISK_1: restoring datafile 00006 to /data/oradata/STD11G4/datafile/o1_mf_abc_c783t3yz_.dbf
channel ORA_DISK_1: restoring datafile 00009 to /data/oradata/STD11G4/datafile/o1_mf_abc_crdch1bd_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/db0rs6mrr3_1_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/backup/db0rs6mrr3_1_1.bkp tag=TAG20170613T144243
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:16
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /data/oradata/STD11G4/datafile/o1_mf_tbs2k_cwto1wx1_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/db0ss6mrto_1_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/backup/db0ss6mrto_1_1.bkp tag=TAG20170613T144243
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00008 to /data/oradata/STD11G4/datafile/o1_mf_tbs32k_cwto2cr8_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/db0ts6mrtp_1_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/backup/db0ts6mrtp_1_1.bkp tag=TAG20170613T144243
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 13-JUN-17

Starting recover at 13-JUN-17
using channel ORA_DISK_1

starting media recovery

RMAN-08187: warning: media recovery until SCN 13940762 complete
Finished recover at 13-JUN-17
3. Instead of the "open resetlogs" open the database using "open resetlogs upgrade". The errors are ignorable.
RMAN> alter database open resetlogs upgrade;
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
4. Check the database status, it should be in open migrate status.
SQL> select status from v$instance;

STATUS
------------
OPEN MIGRATE
5. Run the dbupgrade to commence the DB upgrade to 12.2
$ dbupgrade

Argument list for [/opt/app/oracle/product/12.2.0/sehome_1/rdbms/admin/catctl.pl]
Run in                c = 0
Do not run in         C = 0
Input Directory       d = 0
Echo OFF              e = 1
Simulate              E = 0
Forced cleanup        F = 0
Log Id                i = 0
Child Process         I = 0
Log Dir               l = 0
Priority List Name    L = 0
Upgrade Mode active   M = 0
SQL Process Count     n = 0
SQL PDB Process Count N = 0
Open Mode Normal      o = 0
Start Phase           p = 0
End Phase             P = 0
Reverse Order         r = 0
AutoUpgrade Resume    R = 0
Script                s = 0
Serial Run            S = 0
RO User Tablespaces   T = 0
Display Phases        y = 0
Debug catcon.pm       z = 0
Debug catctl.pl       Z = 0

catctl.pl VERSION: [12.2.0.1.0]
           STATUS: [production]
            BUILD: [RDBMS_12.2.0.1.0_LINUX.X64_170125]


/opt/app/oracle/product/12.2.0/sehome_1/rdbms/admin/orahome = [/opt/app/oracle/product/12.2.0/sehome_1]
/opt/app/oracle/product/12.2.0/sehome_1/bin/orabasehome = [/opt/app/oracle/product/12.2.0/sehome_1]
catctlGetOrabase = [/opt/app/oracle/product/12.2.0/sehome_1]

Analyzing file /opt/app/oracle/product/12.2.0/sehome_1/rdbms/admin/catupgrd.sql

Log file directory = [/tmp/cfgtoollogs/upgrade20170613150844]

catcon: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20170613150844/catupgrd_catcon_14703.lst]
catcon: See [/tmp/cfgtoollogs/upgrade20170613150844/catupgrd*.log] files for output generated by scripts
catcon: See [/tmp/cfgtoollogs/upgrade20170613150844/catupgrd_*.lst] files for spool files, if any

Number of Cpus        = 8
Database Name         = STD11G4
DataBase Version      = 11.2.0.4.0
catcon: ALL catcon-related output will be written to [/opt/app/oracle/product/12.2.0/sehome_1/cfgtoollogs/STD11G4/upgrade20170613150846/catupgrd_catcon_14703.lst]
catcon: See [/opt/app/oracle/product/12.2.0/sehome_1/cfgtoollogs/STD11G4/upgrade20170613150846/catupgrd*.log] files for output generated by scripts
catcon: See [/opt/app/oracle/product/12.2.0/sehome_1/cfgtoollogs/STD11G4/upgrade20170613150846/catupgrd_*.lst] files for spool files, if any

Log file directory = [/opt/app/oracle/product/12.2.0/sehome_1/cfgtoollogs/STD11G4/upgrade20170613150846]

Parallel SQL Process Count            = 4
Components in [STD11G4]
    Installed [APS CATALOG CATJAVA CATPROC CONTEXT EM JAVAVM OWM XDB XML XOQ]
Not Installed [APEX DV MGW ODM OLS ORDIM RAC SDO WK]

------------------------------------------------------
Phases [0-115]         Start Time:[2017_06_13 15:08:46]
------------------------------------------------------
***********   Executing Change Scripts   ***********
Serial   Phase #:0    [STD11G4] Files:1    Time: 177s
***************   Catalog Core SQL   ***************
Serial   Phase #:1    [STD11G4] Files:5    Time: 126s
Restart  Phase #:2    [STD11G4] Files:1    Time: 1s
***********   Catalog Tables and Views   ***********
Parallel Phase #:3    [STD11G4] Files:19   Time: 48s
Restart  Phase #:4    [STD11G4] Files:1    Time: 0s
*************   Catalog Final Scripts   ************
Serial   Phase #:5    [STD11G4] Files:6    Time: 43s
*****************   Catproc Start   ****************
Serial   Phase #:6    [STD11G4] Files:1    Time: 28s
*****************   Catproc Types   ****************
Serial   Phase #:7    [STD11G4] Files:2    Time: 25s
Restart  Phase #:8    [STD11G4] Files:1    Time: 0s
****************   Catproc Tables   ****************
Parallel Phase #:9    [STD11G4] Files:69   Time: 59s
Restart  Phase #:10   [STD11G4] Files:1    Time: 1s
*************   Catproc Package Specs   ************
Serial   Phase #:11   [STD11G4] Files:1    Time: 97s
Restart  Phase #:12   [STD11G4] Files:1    Time: 0s
**************   Catproc Procedures   **************
Parallel Phase #:13   [STD11G4] Files:97   Time: 16s
Restart  Phase #:14   [STD11G4] Files:1    Time: 0s
Parallel Phase #:15   [STD11G4] Files:118  Time: 25s
Restart  Phase #:16   [STD11G4] Files:1    Time: 1s
Serial   Phase #:17   [STD11G4] Files:13   Time: 5s
Restart  Phase #:18   [STD11G4] Files:1    Time: 0s
*****************   Catproc Views   ****************
Parallel Phase #:19   [STD11G4] Files:33   Time: 41s
Restart  Phase #:20   [STD11G4] Files:1    Time: 0s
Serial   Phase #:21   [STD11G4] Files:3    Time: 15s
Restart  Phase #:22   [STD11G4] Files:1    Time: 0s
Parallel Phase #:23   [STD11G4] Files:24   Time: 106s
Restart  Phase #:24   [STD11G4] Files:1    Time: 0s
Parallel Phase #:25   [STD11G4] Files:11   Time: 63s
Restart  Phase #:26   [STD11G4] Files:1    Time: 0s
Serial   Phase #:27   [STD11G4] Files:1    Time: 0s
Serial   Phase #:28   [STD11G4] Files:3    Time: 6s
Serial   Phase #:29   [STD11G4] Files:1    Time: 0s
Restart  Phase #:30   [STD11G4] Files:1    Time: 0s
***************   Catproc CDB Views   **************
Serial   Phase #:31   [STD11G4] Files:1    Time: 0s
Restart  Phase #:32   [STD11G4] Files:1    Time: 1s
Serial   Phase #:34   [STD11G4] Files:1    Time: 0s
*****************   Catproc PLBs   *****************
Serial   Phase #:35   [STD11G4] Files:283  Time: 35s
Serial   Phase #:36   [STD11G4] Files:1    Time: 0s
Restart  Phase #:37   [STD11G4] Files:1    Time: 0s
Serial   Phase #:38   [STD11G4] Files:1    Time: 7s
Restart  Phase #:39   [STD11G4] Files:1    Time: 0s
***************   Catproc DataPump   ***************
Serial   Phase #:40   [STD11G4] Files:3    Time: 70s
Restart  Phase #:41   [STD11G4] Files:1    Time: 0s
******************   Catproc SQL   *****************
Parallel Phase #:42   [STD11G4] Files:13   Time: 63s
Restart  Phase #:43   [STD11G4] Files:1    Time: 1s
Parallel Phase #:44   [STD11G4] Files:12   Time: 14s
Restart  Phase #:45   [STD11G4] Files:1    Time: 0s
Parallel Phase #:46   [STD11G4] Files:2    Time: 1s
Restart  Phase #:47   [STD11G4] Files:1    Time: 0s
*************   Final Catproc scripts   ************
Serial   Phase #:48   [STD11G4] Files:1    Time: 13s
Restart  Phase #:49   [STD11G4] Files:1    Time: 0s
**************   Final RDBMS scripts   *************
Serial   Phase #:50   [STD11G4] Files:1    Time: 39s
************   Upgrade Component Start   ***********
Serial   Phase #:51   [STD11G4] Files:1    Time: 0s
Restart  Phase #:52   [STD11G4] Files:1    Time: 1s
****************   Upgrading Java   ****************
Serial   Phase #:53   [STD11G4] Files:1    Time: 0s
Restart  Phase #:54   [STD11G4] Files:1    Time: 0s
*****************   Upgrading XDK   ****************
Serial   Phase #:55   [STD11G4] Files:1    Time: 0s
Restart  Phase #:56   [STD11G4] Files:1    Time: 0s
*********   Upgrading APS,OLS,DV,CONTEXT   *********
Serial   Phase #:57   [STD11G4] Files:1    Time: 77s
*****************   Upgrading XDB   ****************
Restart  Phase #:58   [STD11G4] Files:1    Time: 0s
Serial   Phase #:60   [STD11G4] Files:3    Time: 21s
Serial   Phase #:61   [STD11G4] Files:3    Time: 9s
Parallel Phase #:62   [STD11G4] Files:9    Time: 4s
Parallel Phase #:63   [STD11G4] Files:24   Time: 4s
Serial   Phase #:64   [STD11G4] Files:4    Time: 13s
Serial   Phase #:65   [STD11G4] Files:1    Time: 0s
Serial   Phase #:66   [STD11G4] Files:30   Time: 5s
Serial   Phase #:67   [STD11G4] Files:1    Time: 0s
Parallel Phase #:68   [STD11G4] Files:6    Time: 3s
Serial   Phase #:69   [STD11G4] Files:2    Time: 22s
Serial   Phase #:70   [STD11G4] Files:3    Time: 68s
Restart  Phase #:71   [STD11G4] Files:1    Time: 0s
*********   Upgrading CATJAVA,OWM,MGW,RAC   ********
Serial   Phase #:72   [STD11G4] Files:1    Time: 80s
****************   Upgrading ORDIM   ***************
Restart  Phase #:73   [STD11G4] Files:1    Time: 0s
Serial   Phase #:75   [STD11G4] Files:1    Time: 0s
Parallel Phase #:76   [STD11G4] Files:2    Time: 0s
Serial   Phase #:77   [STD11G4] Files:1    Time: 1s
Restart  Phase #:78   [STD11G4] Files:1    Time: 0s
Parallel Phase #:79   [STD11G4] Files:2    Time: 0s
Serial   Phase #:80   [STD11G4] Files:2    Time: 0s
*****************   Upgrading SDO   ****************
Restart  Phase #:81   [STD11G4] Files:1    Time: 1s
Serial   Phase #:83   [STD11G4] Files:1    Time: 0s
Serial   Phase #:84   [STD11G4] Files:1    Time: 0s
Restart  Phase #:85   [STD11G4] Files:1    Time: 0s
Serial   Phase #:86   [STD11G4] Files:1    Time: 1s
Restart  Phase #:87   [STD11G4] Files:1    Time: 0s
Parallel Phase #:88   [STD11G4] Files:3    Time: 0s
Restart  Phase #:89   [STD11G4] Files:1    Time: 0s
Serial   Phase #:90   [STD11G4] Files:1    Time: 0s
Restart  Phase #:91   [STD11G4] Files:1    Time: 1s
Serial   Phase #:92   [STD11G4] Files:1    Time: 0s
Restart  Phase #:93   [STD11G4] Files:1    Time: 0s
Parallel Phase #:94   [STD11G4] Files:4    Time: 0s
Restart  Phase #:95   [STD11G4] Files:1    Time: 0s
Serial   Phase #:96   [STD11G4] Files:1    Time: 1s
Restart  Phase #:97   [STD11G4] Files:1    Time: 0s
Serial   Phase #:98   [STD11G4] Files:2    Time: 0s
Restart  Phase #:99   [STD11G4] Files:1    Time: 0s
Serial   Phase #:100  [STD11G4] Files:1    Time: 1s
Restart  Phase #:101  [STD11G4] Files:1    Time: 0s
***********   Upgrading Misc. ODM, OLAP   **********
Serial   Phase #:102  [STD11G4] Files:1    Time: 0s
****************   Upgrading APEX   ****************
Restart  Phase #:103  [STD11G4] Files:1    Time: 0s
Serial   Phase #:104  [STD11G4] Files:1    Time: 0s
Restart  Phase #:105  [STD11G4] Files:1    Time: 1s
***********   Final Component scripts    ***********
Serial   Phase #:106  [STD11G4] Files:1    Time: 0s
*************   Final Upgrade scripts   ************
Serial   Phase #:107  [STD11G4] Files:1    Time: 81s
**********   End PDB Application Upgrade   *********
Serial   Phase #:108  [STD11G4] Files:1    Time: 0s
*******************   Migration   ******************
Serial   Phase #:109  [STD11G4] Files:1    Time: 46s
Serial   Phase #:110  [STD11G4] Files:1    Time: 0s
Serial   Phase #:111  [STD11G4] Files:1    Time: 48s
*****************   Post Upgrade   *****************
Serial   Phase #:112  [STD11G4] Files:1    Time: 252s
****************   Summary report   ****************
Serial   Phase #:113  [STD11G4] Files:1    Time: 0s
Serial   Phase #:114  [STD11G4] Files:1    Time: 0s
Serial   Phase #:115  [STD11G4] Files:1     Time: 29s

------------------------------------------------------
Phases [0-115]         End Time:[2017_06_13 15:40:23]
------------------------------------------------------

Grand Total Time: 1899s

 LOG FILES: (/opt/app/oracle/product/12.2.0/sehome_1/cfgtoollogs/STD11G4/upgrade20170613150846/catupgrd*.log)

Upgrade Summary Report Located in:
/opt/app/oracle/product/12.2.0/sehome_1/cfgtoollogs/STD11G4/upgrade20170613150846/upg_summary.log

Grand Total Upgrade Time:    [0d:0h:31m:39s]


6. View the upgrade summary. This shows that time-zone file need upgrading to latest version.
$ cat /opt/app/oracle/product/12.2.0/sehome_1/cfgtoollogs/STD11G4/upgrade20170613150846/upg_summary.log

Oracle Database 12.2 Post-Upgrade Status Tool           06-13-2017 15:39:54

Component                               Current         Version  Elapsed Time
Name                                    Status          Number   HH:MM:SS

Oracle Server                          UPGRADED      12.2.0.1.0  00:18:41
Oracle Workspace Manager               UPGRADED      12.2.0.1.0  00:01:19
OLAP Analytic Workspace              OPTION OFF      11.2.0.2.0  00:00:00
Oracle OLAP API                      OPTION OFF      11.2.0.2.0  00:00:00
Oracle Text                            UPGRADED      12.2.0.1.0  00:01:15
Oracle XML Database                    UPGRADED      12.2.0.1.0  00:02:28
Final Actions                                                    00:02:06
Post Upgrade                                                     00:04:11

Total Upgrade Time: 00:30:13

Database time zone version is 14. It is older than current release time
zone version 26. Time zone upgrade is needed using the DBMS_DST package.

Grand Total Upgrade Time:    [0d:0h:31m:39s]
7. Before the timezone upgrade run the utlrp to compile packages.
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql
catcon: ALL catcon-related output will be written to [/opt/app/oracle/product/12.2.0/sehome_1/rdbms/admin/utlrp_catcon_24388.lst]
catcon: See [/opt/app/oracle/product/12.2.0/sehome_1/rdbms/admin/utlrp*.log] files for output generated by scripts
catcon: See [/opt/app/oracle/product/12.2.0/sehome_1/rdbms/admin/utlrp_*.lst] files for spool files, if any
catcon.pl: completed successfully
8. Check the timezone upgrade state on the database. Initially the upgrade state will be 0 and secondary timezone will be 0. Refer here for further information on timezone upgrade.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME; 

PROPERTY_NAME             VALUE
------------------------- ----------
DST_PRIMARY_TT_VERSION    14
DST_SECONDARY_TT_VERSION  0
DST_UPGRADE_STATE         NONE
9. Start the database in upgrade mode and being the timezone upgrade.
SQL> shutdown immediate;
SQL> startup upgrade;


exec DBMS_DST.BEGIN_UPGRADE(26);

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;   

PROPERTY_NAME             VALUE
------------------------- ----------
DST_PRIMARY_TT_VERSION    26
DST_SECONDARY_TT_VERSION  14
DST_UPGRADE_STATE         UPGRADE
10. Restar the database in normal mode and complete the timezone upgrade.
shutdown immedaite;
startup;

truncate table sys.dst$error_table;
truncate table sys.dst$trigger_table;

VAR numfail number
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel                  => TRUE,
log_errors                => TRUE,
log_errors_table          => 'SYS.DST$ERROR_TABLE',
log_triggers_table        => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time     => TRUE,
error_on_nonexisting_time => TRUE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/
exec DBMS_DST.END_UPGRADE(:numfail);
11. Verify timezone has been upgraded to 26.
SQL> select * from v$timezone_file;

FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_26.dat              26          0

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;  

PROPERTY_NAME             VALUE
------------------------- ----------
DST_PRIMARY_TT_VERSION    26
DST_SECONDARY_TT_VERSION  0
DST_UPGRADE_STATE         NONE


SQL> @?/rdbms/admin/utlu122s.sql

Oracle Database 12.2 Post-Upgrade Status Tool           06-13-2017 15:50:24

Component                               Current         Version  Elapsed Time
Name                                    Status          Number   HH:MM:SS

Oracle Server                             VALID      12.2.0.1.0  00:18:41
Oracle Workspace Manager                  VALID      12.2.0.1.0  00:01:19
OLAP Analytic Workspace              OPTION OFF      11.2.0.2.0  00:00:00
Oracle OLAP API                      OPTION OFF      11.2.0.2.0  00:00:00
Oracle Text                               VALID      12.2.0.1.0  00:01:15
Oracle XML Database                       VALID      12.2.0.1.0  00:02:28
Final Actions                                                    00:02:06
Post Upgrade                                                     00:04:11
Post Compile                                                     00:01:52

Total Upgrade Time: 00:32:05

Database time zone version is 26. It meets current release needs.

Summary Report File = /opt/app/oracle/product/12.2.0/sehome_1/cfgtoollogs/STD11G4/upgrade20170613150846/upg_summary.log
12. As part of the upgrade statspack was upgraded to 12.2 (refer 394937.1).
SQL> @?/rdbms/admin/spup112

Statspack Upgrade script
~~~~~~~~~~~~~~~~~~~~~~~~

Warning
~~~~~~~
Converting existing Statspack data to 12.1 format may result in
irregularities when reporting on pre-12.1 snapshot data.

This script is provided for convenience, and is not guaranteed to
work on all installations.  To ensure you will not lose any existing
Statspack data, export the schema before upgrading.  A downgrade
script is not provided.  Please see spdoc.txt for more details.

Press return before continuing

Usage
~~~~~
-> Disable any programs which run Statspack (including any dbms_jobs),
before continuing, or this upgrade will fail.

-> You MUST be connected as a user with SYSDBA privilege to successfully
run this script.

-> You will be prompted for the PERFSTAT password, and for the
tablespace to create any new PERFSTAT tables/indexes.

Press return before continuing

Please specify the PERFSTAT password
Enter value for perfstat_password: test
test
Note:
Please check remainder of upgrade log file, which is continued in
the file spup112b.lis

Grant succeeded.

Connected.
USER is "PERFSTAT"
insert into STATS$IDLE_EVENT (event) values ('GCR sleep')
*
ERROR at line 1:
ORA-00001: unique constraint (PERFSTAT.STATS$IDLE_EVENT_PK) violated

insert into STATS$IDLE_EVENT (event) values ('LogMiner builder: branch')
*
ERROR at line 1:
ORA-00001: unique constraint (PERFSTAT.STATS$IDLE_EVENT_PK) violated

insert into STATS$IDLE_EVENT (event) values ('LogMiner builder: idle')
*
ERROR at line 1:
ORA-00001: unique constraint (PERFSTAT.STATS$IDLE_EVENT_PK) violated

insert into STATS$IDLE_EVENT (event) values ('LogMiner client: transaction')
*
ERROR at line 1:
ORA-00001: unique constraint (PERFSTAT.STATS$IDLE_EVENT_PK) violated

insert into STATS$IDLE_EVENT (event) values ('LogMiner preparer: idle')
*
ERROR at line 1:
ORA-00001: unique constraint (PERFSTAT.STATS$IDLE_EVENT_PK) violated

insert into STATS$IDLE_EVENT (event) values ('parallel recovery control message reply')
*
ERROR at line 1:
ORA-00001: unique constraint (PERFSTAT.STATS$IDLE_EVENT_PK) violated

1 row created.
...
1 row created.

Commit complete.
Table altered.
Commit complete.
Table created.
Synonym created.
Commit complete.

Note:
Please check the log file of the package recreation, which is
in the file spcpkg.lis
Creating Package STATSPACK...

Package created.

No errors.
Creating Package Body STATSPACK...

Warning: Package Body created with compilation errors.

Errors for PACKAGE BODY STATSPACK:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3219/8   PL/SQL: SQL Statement ignored
3223/15  PL/SQL: ORA-00904: "REMASTER_TYPE": invalid identifier

NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.

SQL>
SQL> --  End of Upgrade script

conn / as sysdba

SQL> @?/rdbms/admin/spup12200.sql
13. Finally update the compatibility parameter to 12.2
SQL> alter system set compatible='12.2.0.1.0' scope=spfile;
14. This concludes the upgrade to 12.2.
SQL> select * from dba_registry_history order by 1;

ACTION_TIME                    ACTION          NAMESPACE                      VERSION                                ID COMMENTS
------------------------------ --------------- ------------------------------ ------------------------------ ---------- ----------------------------------------
05-SEP-10 06.22.14.370943 AM   APPLY           SERVER                         11.2.0.2                                0 Patchset 11.2.0.2.0
28-JAN-11 02.04.20.929972 PM   APPLY           SERVER                         11.2.0.2                                1 PSU 11.2.0.2.1
28-JAN-11 02.08.47.891548 PM   APPLY           SERVER                         11.2.0.2                                1 PSU 11.2.0.2.1
24-MAY-11 01.32.49.622817 PM   APPLY           SERVER                         11.2.0.2                                2 PSU 11.2.0.2.2
20-JUL-11 11.46.58.623557 AM   APPLY           SERVER                         11.2.0.2                                3 PSU 11.2.0.2.3
19-OCT-11 11.56.37.705897 AM   APPLY           SERVER                         11.2.0.2                                4 PSU 11.2.0.2.4
18-JAN-12 03.25.41.512830 PM   APPLY           SERVER                         11.2.0.2                                5 PSU 11.2.0.2.5
13-FEB-12 03.27.42.343779 PM   VIEW INVALIDATE                                                                  8289601 view invalidation
13-FEB-12 03.27.42.835430 PM   UPGRADE         SERVER                         11.2.0.3.0                                Upgraded from 11.2.0.2.0
13-FEB-12 03.28.03.564686 PM   APPLY           SERVER                         11.2.0.3                                0 Patchset 11.2.0.2.0
30-APR-12 12.23.17.690100 PM   APPLY           SERVER                         11.2.0.3                                2 PSU 11.2.0.3.2
18-JUL-12 10.48.29.139682 AM   APPLY           SERVER                         11.2.0.3                                3 PSU 11.2.0.3.3
06-NOV-12 02.33.09.598897 PM   APPLY           SERVER                         11.2.0.3                                4 PSU 11.2.0.3.4
21-JAN-13 10.59.24.458354 AM   APPLY           SERVER                         11.2.0.3                                5 PSU 11.2.0.3.5
19-APR-13 04.27.41.823099 PM   APPLY           SERVER                         11.2.0.3                                6 PSU 11.2.0.3.6
29-JUL-13 02.28.13.060031 PM   APPLY           SERVER                         11.2.0.3                                7 PSU 11.2.0.3.7
22-OCT-13 05.17.58.670146 PM   APPLY           SERVER                         11.2.0.3                                8 PSU 11.2.0.3.8
21-JAN-14 10.40.22.294040 AM   APPLY           SERVER                         11.2.0.3                                9 PSU 11.2.0.3.9
30-APR-14 03.58.10.311583 PM   APPLY           SERVER                         11.2.0.3                               10 PSU 11.2.0.3.10
16-JUL-14 01.25.56.344898 PM   APPLY           SERVER                         11.2.0.3                               11 PSU 11.2.0.3.11
11-NOV-14 02.59.11.471061 PM   APPLY           SERVER                         11.2.0.3                               12 PSU 11.2.0.3.12
21-JAN-15 11.47.41.906220 AM   jvmpsu.sql      SERVER                         11.2.0.3.2OJVMBP                        0 RAN jvmpsu.sql
21-JAN-15 11.47.41.926803 AM   APPLY           SERVER                         11.2.0.3.2OJVMBP                        0 OJVM PSU post-install
21-JAN-15 11.50.26.504554 AM   APPLY           SERVER                         11.2.0.3                               13 PSU 11.2.0.3.13
18-DEC-15 02.29.09.949010 PM   VIEW INVALIDATE                                                                  8289601 view invalidation
18-DEC-15 02.29.09.994073 PM   UPGRADE         SERVER                         11.2.0.4.0                                Upgraded from 11.2.0.3.0
18-DEC-15 02.29.31.582833 PM   APPLY           SERVER                         11.2.0.4                                0 Patchset 11.2.0.2.0
18-DEC-15 02.47.17.164443 PM   APPLY           SERVER                         11.2.0.4                                8 PSU 11.2.0.4.8
20-APR-16 01.39.00.784851 PM   APPLY           SERVER                         11.2.0.4                           160419 PSU 11.2.0.4.160419
13-JUN-17 03.33.00.073857 PM   VIEW INVALIDATE                                                                  8289601 view invalidation
13-JUN-17 03.34.54.163714 PM   UPGRADE         SERVER                         12.2.0.1.0                                Upgraded from 11.2.0.4.0
                               BOOTSTRAP       DATAPATCH                      12.2.0.1                                  RDBMS_12.2.0.1.0_LINUX.X64_170125

          
SQL> select comp_id,status,version from dba_registry order  by 2,3;

COMP_ID                        STATUS                                       VERSION
------------------------------ -------------------------------------------- -----------
APS                            OPTION OFF                                   11.2.0.2.0
XOQ                            OPTION OFF                                   11.2.0.2.0
XML                            REMOVED                                      11.2.0.3.0
JAVAVM                         REMOVED                                      11.2.0.3.0
CATJAVA                        REMOVED                                      11.2.0.3.0
CATPROC                        VALID                                        12.2.0.1.0
CATALOG                        VALID                                        12.2.0.1.0
OWM                            VALID                                        12.2.0.1.0
CONTEXT                        VALID                                        12.2.0.1.0
XDB                            VALID                                        12.2.0.1.0
Related Posts
Upgrading to 12.1.0.2 Using 11.2.0.4 Backups
Upgrading to 12.2.0.1 Using 12.1.0.2 Backups