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

Saturday, July 1, 2017

Upgrading to 12.2.0.1 Using 12.1.0.2 Backups

This post list the steps for upgrading a 12.1.0.2 single instance CDB to 12.2.0.1 using the backups of 12.1.0.2 DB. The database is restored using the higher version (12.2.0.1) oracle binaries. The steps below show the restore and upgrade process for SE2 CDB with one PDB. It is assumed that a backup of the database is taken and copied to server where 12.2.0.1 software is installed. A previous post is available which shows upgrading from 11.2.0.4 to 12.1 using backups.
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=std12cdb
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 08-JUN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 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/STD12CDB/controlfile/o1_mf_dmx12r7y_.ctl
output file name=/opt/app/oracle/fast_recovery/STD12CDB/controlfile/o1_mf_dmx12rk3_.ctl
Finished restore at 08-JUN-17

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

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

Starting implicit crosscheck copy at 08-JUN-17
using channel ORA_DISK_1
Finished implicit crosscheck copy at 08-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/db02s675bb_1_1.bkp RECID=2 STAMP=946050411
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/usr/local/backups/db07s696ij_1_1.bkp RECID=7 STAMP=946117203
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/usr/local/backups/db08s696iq_1_1.bkp RECID=8 STAMP=946117210
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/usr/local/backups/db09s696it_1_1.bkp RECID=9 STAMP=946117213
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/ssdoracle/flash_recovery/STD12CDB/autobackup/2017_06_08/o1_mf_s_946116946_dml5pk8q_.bkp RECID=10 STAMP=946117217
Crosschecked 5 objects


RMAN> delete noprompt expired backup;
using channel ORA_DISK_1
deleted backup piece
backup piece handle=/usr/local/backups/db02s675bb_1_1.bkp RECID=2 STAMP=946050411
deleted backup piece
backup piece handle=/usr/local/backups/db07s696ij_1_1.bkp RECID=7 STAMP=946117203
deleted backup piece
backup piece handle=/usr/local/backups/db08s696iq_1_1.bkp RECID=8 STAMP=946117210
deleted backup piece
backup piece handle=/usr/local/backups/db09s696it_1_1.bkp RECID=9 STAMP=946117213
deleted backup piece
backup piece handle=/ssdoracle/flash_recovery/STD12CDB/autobackup/2017_06_08/o1_mf_s_946116946_dml5pk8q_.bkp RECID=10 STAMP=946117217
Deleted 5 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/pfile.ora
File Name: /home/oracle/backup/control.bkp
File Name: /home/oracle/backup/db07s696ij_1_1.bkp
File Name: /home/oracle/backup/db08s696iq_1_1.bkp
File Name: /home/oracle/backup/db09s696it_1_1.bkp
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/backup/control.bkp
File Name: /home/oracle/backup/db07s696ij_1_1.bkp
File Name: /home/oracle/backup/db08s696iq_1_1.bkp
File Name: /home/oracle/backup/db09s696it_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 08-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 /ssdoracle/oradata/STD12CDB/datafile/o1_mf_system_dmj1qxvl_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /ssdoracle/oradata/STD12CDB/datafile/o1_mf_sysaux_dmj1r5mo_.dbf
channel ORA_DISK_1: restoring datafile 00005 to /ssdoracle/oradata/STD12CDB/datafile/o1_mf_undotbs1_dmj1r9qh_.dbf
channel ORA_DISK_1: restoring datafile 00006 to /ssdoracle/oradata/STD12CDB/datafile/o1_mf_users_dmj1rx1x_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/db07s696ij_1_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/backup/db07s696ij_1_1.bkp tag=TAG20170608T102003
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
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 00002 to /ssdoracle/oradata/STD12CDB/516028AF3ECC7D6DE0534200A8C08E0E/datafile/o1_mf_system_dmj1r1jc_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /ssdoracle/oradata/STD12CDB/516028AF3ECC7D6DE0534200A8C08E0E/datafile/o1_mf_sysaux_dmj1r80p_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/db08s696iq_1_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/backup/db08s696iq_1_1.bkp tag=TAG20170608T102003
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
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 00027 to /ssdoracle/oradata/STD12CDB/5170445434C83D39E0534200A8C0B781/datafile/o1_mf_system_dml59x8q_.dbf
channel ORA_DISK_1: restoring datafile 00028 to /ssdoracle/oradata/STD12CDB/5170445434C83D39E0534200A8C0B781/datafile/o1_mf_sysaux_dml59x8r_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/db09s696it_1_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/backup/db09s696it_1_1.bkp tag=TAG20170608T102003
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 08-JUN-17

Starting recover at 08-JUN-17
using channel ORA_DISK_1

starting media recovery

RMAN-08187: warning: media recovery until SCN 7924317 complete
Finished recover at 08-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-04063: package body "SYS.DBMS_BACKUP_RESTORE" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_BACKUP_RESTORE"
4. The root CDB will be open in migrate mode. Open the PDB in migrate mode as well.
SQL> select open_mode from v$database;

OPEN_MODE
-----------
READ WRITE

SQL> select status from v$instance;

STATUS
------------
OPEN MIGRATE

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MIGRATE    YES
         3 IRPDB                          MOUNTED
SQL> alter pluggable database irpdb open upgrade;

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MIGRATE    YES
         3 IRPDB                          MIGRATE    YES
5. From this point onwards the steps to upgrade are similar to that of a manual upgrade of a CDB. 12.2 provides a single upgrade script called "dbupgrade" to upgrade the CDB and all or subset of PDBs. In this case all the PDBs (there's only one as this is a SE2) will be upgraded. The upgrade output is given below.
$ which dbupgrade
/opt/app/oracle/product/12.2.0/sehome_1/bin/dbupgrade

$ 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/upgrade20170608103851]

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

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

Log file directory = [/opt/app/oracle/product/12.2.0/sehome_1/cfgtoollogs/std12cdb/upgrade20170608103852]

Parallel SQL Process Count (PDB)      = 2
Parallel SQL Process Count (CDB$ROOT) = 8
Concurrent PDB Upgrades               = 4
Generated PDB Inclusion:[PDB$SEED IRPDB]
Components in [CDB$ROOT]
    Installed [CATALOG CATPROC CONTEXT XDB]
Not Installed [APEX APS CATJAVA DV EM JAVAVM MGW ODM OLS ORDIM OWM RAC SDO WK XML XOQ]

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

------------------------------------------------------
Phases [0-115]         End Time:[2017_06_08 10:58:53]
Container Lists Inclusion:[CDB$ROOT] Exclusion:[NONE]
------------------------------------------------------

Start processing of PDB$SEED
[/opt/app/oracle/product/12.2.0/sehome_1/perl/bin/perl /opt/app/oracle/product/12.2.0/sehome_1/rdbms/admin/catctl.pl -I -i pdb_seed -n 2 -c 'PDB$SEED' -l /opt/app/oracle/product/12.2.0/sehome_1/cfgtoollogs/std12cdb/upgrade20170608103852 /opt/app/oracle/product/12.2.0/sehome_1/rdbms/admin/catupgrd.sql]

Start processing of IRPDB
[/opt/app/oracle/product/12.2.0/sehome_1/perl/bin/perl /opt/app/oracle/product/12.2.0/sehome_1/rdbms/admin/catctl.pl -I -i irpdb -n 2 -c 'IRPDB' -l /opt/app/oracle/product/12.2.0/sehome_1/cfgtoollogs/std12cdb/upgrade20170608103852 /opt/app/oracle/product/12.2.0/sehome_1/rdbms/admin/catupgrd.sql]

Argument list for [/opt/app/oracle/product/12.2.0/sehome_1/rdbms/admin/catctl.pl]
Run in                c = PDB$SEED
Do not run in         C = 0
Input Directory       d = 0
Echo OFF              e = 1
Simulate              E = 0
Forced cleanup        F = 0
Log Id                i = pdb_seed
Child Process         I = 1
Log Dir               l = /opt/app/oracle/product/12.2.0/sehome_1/cfgtoollogs/std12cdb/upgrade20170608103852
Priority List Name    L = 0
Upgrade Mode active   M = 0
SQL Process Count     n = 2
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]



Argument list for [/opt/app/oracle/product/12.2.0/sehome_1/rdbms/admin/catctl.pl]
Run in                c = IRPDB
Do not run in         C = 0
Input Directory       d = 0
Echo OFF              e = 1
Simulate              E = 0
Forced cleanup        F = 0
Log Id                i = irpdb
Child Process         I = 1
Log Dir               l = /opt/app/oracle/product/12.2.0/sehome_1/cfgtoollogs/std12cdb/upgrade20170608103852
Priority List Name    L = 0
Upgrade Mode active   M = 0
SQL Process Count     n = 2
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/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 = [/opt/app/oracle/product/12.2.0/sehome_1/cfgtoollogs/std12cdb/upgrade20170608103852]

/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 = [/opt/app/oracle/product/12.2.0/sehome_1/cfgtoollogs/std12cdb/upgrade20170608103852]

catcon: ALL catcon-related output will be written to [/opt/app/oracle/product/12.2.0/sehome_1/cfgtoollogs/std12cdb/upgrade20170608103852/catupgrdpdb_seed_catcon_581.lst]
catcon: See [/opt/app/oracle/product/12.2.0/sehome_1/cfgtoollogs/std12cdb/upgrade20170608103852/catupgrdpdb_seed*.log] files for output generated by scripts
catcon: See [/opt/app/oracle/product/12.2.0/sehome_1/cfgtoollogs/std12cdb/upgrade20170608103852/catupgrdpdb_seed_*.lst] files for spool files, if any
catcon: ALL catcon-related output will be written to [/opt/app/oracle/product/12.2.0/sehome_1/cfgtoollogs/std12cdb/upgrade20170608103852/catupgrdirpdb_catcon_585.lst]
catcon: See [/opt/app/oracle/product/12.2.0/sehome_1/cfgtoollogs/std12cdb/upgrade20170608103852/catupgrdirpdb*.log] files for output generated by scripts
catcon: See [/opt/app/oracle/product/12.2.0/sehome_1/cfgtoollogs/std12cdb/upgrade20170608103852/catupgrdirpdb_*.lst] files for spool files, if any

Number of Cpus        = 8

Number of Cpus        = 8
Database Name         = std12cdb
Database Name         = std12cdb
DataBase Version      = 12.2.0.1.0
DataBase Version      = 12.2.0.1.0
Generated PDB Inclusion:[IRPDB]
CDB$ROOT  Open Mode = [OPEN]
Generated PDB Inclusion:[PDB$SEED]
Components in [IRPDB]
    Installed [CATALOG CATPROC CONTEXT XDB]
Not Installed [APEX APS CATJAVA DV EM JAVAVM MGW ODM OLS ORDIM OWM RAC SDO WK XML XOQ]

------------------------------------------------------
Phases [0-115]         Start Time:[2017_06_08 10:58:58]
Container Lists Inclusion:[IRPDB] Exclusion:[NONE]
------------------------------------------------------
CDB$ROOT  Open Mode = [OPEN]
Components in [PDB$SEED]
    Installed [CATALOG CATPROC CONTEXT XDB]
Not Installed [APEX APS CATJAVA DV EM JAVAVM MGW ODM OLS ORDIM OWM RAC SDO WK XML XOQ]

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

------------------------------------------------------
Phases [0-115]         End Time:[2017_06_08 11:34:51]
Container Lists Inclusion:[IRPDB] Exclusion:[NONE]
------------------------------------------------------

Grand Total Time: 2154s [IRPDB]

 LOG FILES: (/opt/app/oracle/product/12.2.0/sehome_1/cfgtoollogs/std12cdb/upgrade20170608103852/catupgrdirpdb*.log)

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

Total Upgrade Time:          [0d:0h:35m:54s]

   Time: 475s
****************   Summary report   ****************
Serial   Phase #:113  [PDB$SEED] Files:1    Time: 0s
Serial   Phase #:114  [PDB$SEED] Files:1    Time: 3s
Serial   Phase #:115  [PDB$SEED] Files:1     Time: 0s

------------------------------------------------------
Phases [0-115]         End Time:[2017_06_08 11:41:09]
Container Lists Inclusion:[PDB$SEED] Exclusion:[NONE]
------------------------------------------------------

Grand Total Time: 2533s [PDB$SEED]

 LOG FILES: (/opt/app/oracle/product/12.2.0/sehome_1/cfgtoollogs/std12cdb/upgrade20170608103852/catupgrdpdb_seed*.log)

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

Total Upgrade Time:          [0d:0h:42m:13s]

     Time: 1200s For CDB$ROOT
     Time: 2541s For PDB(s)

Grand Total Time: 3741s

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

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

Grand Total Upgrade Time:    [0d:1h:2m:21s]


6. Upgrading summary shows that time-zone requires upgrading for CDB and the PDBs.
$ cat /opt/app/oracle/product/12.2.0/sehome_1/cfgtoollogs/std12cdb/upgrade20170608103852/upg_summary.log

Oracle Database 12.2 Post-Upgrade Status Tool           06-08-2017 10:58:52
                             [CDB$ROOT]

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

Oracle Server                          UPGRADED      12.2.0.1.0  00:14:14
Oracle Real Application Clusters       UPGRADED      12.2.0.1.0  00:00:00
Oracle Text                            UPGRADED      12.2.0.1.0  00:00:50
Oracle XML Database                    UPGRADED      12.2.0.1.0  00:01:35
Final Actions                                                    00:02:01
Post Upgrade                                                     00:00:12

Total Upgrade Time: 00:19:08 [CDB$ROOT]

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


Oracle Database 12.2 Post-Upgrade Status Tool           06-08-2017 11:34:49
                             [IRPDB]

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

Oracle Server                          UPGRADED      12.2.0.1.0  00:27:49
Oracle Real Application Clusters       UPGRADED      12.2.0.1.0  00:00:00
Oracle Text                            UPGRADED      12.2.0.1.0  00:00:58
Oracle XML Database                    UPGRADED      12.2.0.1.0  00:02:13
Final Actions                                                    00:02:37
Post Upgrade                                                     00:01:30

Total Upgrade Time: 00:35:28 [IRPDB]

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


Oracle Database 12.2 Post-Upgrade Status Tool           06-08-2017 11:41:06
                             [PDB$SEED]

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

Oracle Server                             VALID      12.2.0.1.0  00:27:49
Oracle Real Application Clusters     OPTION OFF      12.2.0.1.0  00:00:00
Oracle Text                               VALID      12.2.0.1.0  00:00:57
Oracle XML Database                       VALID      12.2.0.1.0  00:02:13
Final Actions                                                    00:02:34
Post Upgrade                                                     00:01:29
Post Compile                                                     00:06:22

Total Upgrade Time: 00:41:47 [PDB$SEED]

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


Upgrade Times Sorted In Descending Order

Total Upgrade Time: 00:41:47 [PDB$SEED]
Total Upgrade Time: 00:35:28 [IRPDB]
Total Upgrade Time: 00:19:08 [CDB$ROOT]
Grand Total Upgrade Time:    [0d:1h:2m:21s]
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_18972.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. If timezone is being updated then it must be updated in both CDB$ROOT and the PDBs. Check the timezone upgrade state on CDB and PDBs. 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         18
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE
9. Start the CDB and all the PBDs in upgrade mode
SQL> Startup  upgrade;
SQL> alter pluggable database irpdb open upgrade;

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MIGRATE    YES
         3 IRPDB                          MIGRATE    YES
10. Create a timezone upgrade script to prepare for timezone upgrade and run it using catcon.pl on all the containers.
cat begin_tz.sql
exec DBMS_DST.BEGIN_UPGRADE(26);

$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b begintz -d '''.''' begin_tz.sql
catcon: ALL catcon-related output will be written to [/opt/app/oracle/product/12.2.0/sehome_1/rdbms/admin/begintz_catcon_14761.lst]
catcon: See [/opt/app/oracle/product/12.2.0/sehome_1/rdbms/admin/begintz*.log] files for output generated by scripts
catcon: See [/opt/app/oracle/product/12.2.0/sehome_1/rdbms/admin/begintz_*.lst] files for spool files, if any
catcon.pl: completed successfully
11. Shutdown the and start the database in normal mode.
SQL> shutdown immedaite;
SQL> startup;
The upgrade state and primary TT version should have been changed as below. Output is from the PDB execution but it should be same on the CDB as well.
SQL> show con_name

CON_NAME
------------------------------
IRPDB
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       18
DST_UPGRADE_STATE              UPGRADE
12. Create a timezone upgrade script that will be the actual timezone upgrade. Run it using catcon.pl.
$ cat upgrdtz.sql
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);

$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b upgrdtz -d '''.''' upgrdtz.sql
catcon: ALL catcon-related output will be written to [/opt/app/oracle/product/12.2.0/sehome_1/rdbms/admin/upgrdtz_catcon_15286.lst]
catcon: See [/opt/app/oracle/product/12.2.0/sehome_1/rdbms/admin/upgrdtz*.log] files for output generated by scripts
catcon: See [/opt/app/oracle/product/12.2.0/sehome_1/rdbms/admin/upgrdtz_*.lst] files for spool files, if any
13. Check if the timezone has been upgraded.
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> select * from v$timezone_file;

FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_26.dat              26          0
14. Run the post ugprade status tool and verify the upgrade status. The output now shows that timezone meets the current release needs.
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlu122s -d '''.''' utlu122s.sql

Oracle Database 12.2 Post-Upgrade Status Tool           06-08-2017 12:27:50
                             [CDB$ROOT]

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

Oracle Server                          UPGRADED      12.2.0.1.0  00:14:14
Oracle Real Application Clusters       UPGRADED      12.2.0.1.0  00:00:00
Oracle Text                            UPGRADED      12.2.0.1.0  00:00:50
Oracle XML Database                    UPGRADED      12.2.0.1.0  00:01:35
Final Actions                                                    00:02:01
Post Upgrade                                                     00:00:12

Total Upgrade Time: 00:19:08 [CDB$ROOT]

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

Oracle Database 12.2 Post-Upgrade Status Tool           06-08-2017 12:27:52
                             [PDB$SEED]

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

Oracle Server                             VALID      12.2.0.1.0  00:27:49
Oracle Real Application Clusters     OPTION OFF      12.2.0.1.0  00:00:00
Oracle Text                               VALID      12.2.0.1.0  00:00:57
Oracle XML Database                       VALID      12.2.0.1.0  00:02:13
Final Actions                                                    00:02:34
Post Upgrade                                                     00:01:29
Post Compile                                                     00:06:22

Total Upgrade Time: 00:41:47 [PDB$SEED]

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


Oracle Database 12.2 Post-Upgrade Status Tool           06-08-2017 12:27:52
                             [IRPDB]

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

Oracle Server                          UPGRADED      12.2.0.1.0  00:27:49
Oracle Real Application Clusters       UPGRADED      12.2.0.1.0  00:00:00
Oracle Text                            UPGRADED      12.2.0.1.0  00:00:58
Oracle XML Database                    UPGRADED      12.2.0.1.0  00:02:13
Final Actions                                                    00:02:37
Post Upgrade                                                     00:01:30

Total Upgrade Time: 00:35:28 [IRPDB]

Database time zone version is 26. It meets current release needs.
15. Check registry history reflect the upgrade.
SQL> select * from cdb_registry_history;

ACTION_TIME                    ACTION          NAMESPACE  VERSION            ID COMMENTS                                                         CON_ID
------------------------------ --------------- ---------- ---------- ---------- ------------------------------------------------------------ ----------
                               BOOTSTRAP       DATAPATCH  12.1.0.2              RDBMS_12.1.0.2.0DBPSU_LINUX.X64_161210                                1
                               BOOTSTRAP       DATAPATCH  12.2.0.1              RDBMS_12.2.0.1.0_LINUX.X64_170125                                     1
08-JUN-17 04.12.06.632154 PM   VIEW INVALIDATE                          8289601 view invalidation                                                     1
08-JUN-17 04.14.00.598280 PM   UPGRADE         SERVER     12.2.0.1.0            Upgraded from 12.1.0.2.0                                              1
                               BOOTSTRAP       DATAPATCH  12.1.0.2              RDBMS_12.1.0.2.0DBPSU_LINUX.X64_161210                                3
                               BOOTSTRAP       DATAPATCH  12.2.0.1              RDBMS_12.2.0.1.0_LINUX.X64_170125                                     3
08-JUN-17 04.48.47.103524 PM   VIEW INVALIDATE                          8289601 view invalidation                                                     3
08-JUN-17 04.50.59.197968 PM   UPGRADE         SERVER     12.2.0.1.0            Upgraded from 12.1.0.2.0                                              3

SQL>  select con_id,comp_id,comp_name,version,status from cdb_registry order by 1;

    CON_ID COMP_ID    COMP_NAME                                VERSION    STATUS
---------- ---------- ---------------------------------------- ---------- ----------
         1 CATALOG    Oracle Database Catalog Views            12.2.0.1.0 VALID
         1 CATPROC    Oracle Database Packages and Types       12.2.0.1.0 VALID
         1 CONTEXT    Oracle Text                              12.2.0.1.0 VALID
         1 XDB        Oracle XML Database                      12.2.0.1.0 VALID
         1 RAC        Oracle Real Application Clusters         12.2.0.1.0 OPTION OFF
         3 CATALOG    Oracle Database Catalog Views            12.2.0.1.0 VALID
         3 CATPROC    Oracle Database Packages and Types       12.2.0.1.0 VALID
         3 CONTEXT    Oracle Text                              12.2.0.1.0 VALID
         3 XDB        Oracle XML Database                      12.2.0.1.0 VALID
         3 RAC        Oracle Real Application Clusters         12.2.0.1.0 OPTION OFF
16. Complete other post-upgrade steps and finally upgrade the compatible parameter.
ALTER SYSTEM SET compatible='12.2.0.1.0' SCOPE=SPFILE;
Useful metalink notes
Complete Checklist for Upgrading to Oracle Database 12c Release 2 (12.2) using DBUA [ID 2189854.1]
Complete Checklist for Manual Upgrades to Non-CDB Oracle Database 12c Release 2 (12.2) [ID 2173141.1]
Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12c database . [ID 1585343.1]
Complete Checklist for upgrading Oracle 12.1 Container Database (CDB) to Oracle Database 12c Release 2 (12.2) using DBUA [ID 2240413.1]

Related Posts
Upgrading to 12.1.0.2 Using 11.2.0.4 Backups
Upgrading to 12.2.0.1 Using 11.2.0.4 Backups

Saturday, June 17, 2017

Enabling Automatic AWR Snapshots on PDB

Oracle 12.2 allows AWR snapshots to be taken both at CDB and PDB level. CDB level snapshots will contain both CDB and PDB statistics.
AWR report will list the containers whose statistics are available in the AWR report. Below screenshot shows part of SQL statistics section of a CDB level AWR report. It could be seen it has SQL stats for both PDB and the root CDB.
On the other-hand PDB level snapshots will have statistics local to that PDB and some global statistics. Read here for further information on these.
Automatic snapshot is only enabled on the CDB level and by default is disabled on PDB. Oracle recommendation is "to generally use manual snapshots for a PDB. You should enable automatic snapshots only selectively for a PDB for performance reasons".
However, if required automatic AWR snapshots could be enabled on PDB by setting awr_pdb_autoflush_enabled to true. This could be set on CDB root level or PDB level. If set to true on CDB root level then automatic snapshots will be enabled for all the PDBs. If set for PDB then automatic AWR snapshot is enabled only for that PDB. Once awr_pdb_autoflush_enabled is set to true login to the PDB and run DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS with an interval value greater than 0.
Below steps shows enable automatic AWR snapshots for all the PDBs in a CDB.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 APDB                           READ WRITE NO
Enable awr_pdb_autoflush to true on cdb
SQL> show con_name

CON_NAME
---------
CDB$ROOT

SQL>  alter system set awr_pdb_autoflush_enabled=true scope=both sid='*';
System altered.
Login to PDB and run the MODIFY_SNAPSHOT_SETTINGS with interval > 0. using PDB's DBID.
SQL> show con_name;

CON_NAME
---------
APDB

SQL> select * from awr_pdb_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL        CON_ID
---------- -------------------- -------------------- ---------- ----------
3913427709 +40150 00:01:00.0    +00008 00:00:00.0    DEFAULT             3


BEGIN
 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 11520, 
                                                           interval => 60,
                                                           topnsql => 34,
                                                           dbid => 3913427709);
END;
/

SQL> select * from awr_pdb_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL        CON_ID
---------- -------------------- -------------------- ---------- ----------
3913427709 +00000 01:00:00.0    +00008 00:00:00.0            34          3
AWR_PDB_WR_CONTROL view shows that interval has been set to every one hour.



When the first snapshot is generated, the AWR_PDB_DATABASE_INSTANCE view will be populated with PDB data.
SQL> select con_id,dbid,INSTANCE_NUMBER,INSTANCE_NAME,CDB,LAST_ASH_SAMPLE_ID from AWR_PDB_DATABASE_INSTANCE;

    CON_ID       DBID INSTANCE_NUMBER INSTANCE_NAME    CDB LAST_ASH_SAMPLE_ID
---------- ---------- --------------- ---------------- --- ------------------
         3 3913427709               2 cdb12c22         YES            1102638
         3 3913427709               1 cdb12c21         YES            1105261
Running awrrpt.sql before a record for PDB exists in AWR_PDB_DATABASE_INSTANCE view will result in "ORA-20200: Database/Instance 3913427709/1 does not exist in AWR_PDB_DATABASE_INSTANCE" (refer 2267849.1). When running PDB AWR reports use AWR_PDB as the location for the AWR_DATA.
Specify the location of AWR Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
AWR_ROOT - Use AWR data from root (default)
AWR_PDB - Use AWR data from PDB
Enter value for awr_location: AWR_PDB
There is another optional parameter to consider when enable automatic AWR snapshots for PDBs. AWR_SNAPSHOT_TIME_OFFSET specifies an off-set for snapshot start time. When there are large number of PDBs in a CDB, setting same snapshot time for all of them could result in CPU spikes. Setting this to special value of 1000000 will result in an off-set based on the DB name, reducing CPU spikes due to multiple PDBs taking snapshot at the same time.
SQL> alter system set awr_snapshot_time_offset=1000000 scope=both sid='*';
System altered.
In summary to enable automatic snapshot taking on PDB, set awr_pdb_autoflush_enabled to true and set snapshot interval to value greater than 0.

Useful metalink notes
ORA-20200 Error When Generating AWR or ADDM Report as a PDB DBA User From a 12.2.0.1 CDB Database [ID 2267849.1]