After
upgrading the GI from 12.1.0.2 to 12.2.0.1 the next phase is to upgrade the database software and the actual databases. This cluster has two standard edition 2 RAC databases. One is a non-CDB while other is a CDB with just one PDB. The post shows upgrade of them of both. First is the database software upgrade. This is done as an out of place upgrade. Before upgrade commence run cluvfy with database pre-upgrade option.
[oracle@rhel12c1 ~]$ /opt/app/12.2.0/grid/bin/cluvfy stage -pre dbinst -upgrade -src_dbhome /opt/app/oracle/product/12.1.0/dbhome_2 -dest_dbhome /opt/app/oracle/product/12.2.0/dbhome_1 -dest_version 12.2.0.1.0
12.2 has introduced a new OS group for RAC management. Add the group to oracle user, keeping the user equivalence across all nodes.
# groupadd racdba
# usermod -g oinstall -G dba,oper,asmdba,asmoper,asmadmin,backupdba,dgdba,kmdba,racdba oracle
# id oracle
uid=500(oracle) gid=502(oinstall) groups=502(oinstall),501(dba),503(oper),504(asmoper),505(asmdba),506(asmadmin),507(backupdba),508(dgdba),509(kmdba),510(racdba)
Run 12.2 installer and select software only install option.
Upgrading non-CDB
As mentioned earlier this cluster has two databases, non-CDB and CDB. This section shows the upgrading of the non-CDB. Before the upgrade make sure the sga_target is at least 2068MB. This requirement is not flagged in orachk or any other pre-req checks. If sga_target is lower than this, the upgrade would fail.
The component version and status before the upgrade is given below
COMP_ID COMP_NAME VERSION STATUS
---------- ----------------------------------- ---------- --------
CATALOG Oracle Database Catalog Views 12.1.0.2.0 VALID
CATPROC Oracle Database Packages and Types 12.1.0.2.0 VALID
RAC Oracle Real Application Clusters 12.1.0.2.0 VALID
XDB Oracle XML Database 12.1.0.2.0 VALID
OWM Oracle Workspace Manager 12.1.0.2.0 VALID
CONTEXT Oracle Text 12.1.0.2.0 VALID
Run the preupgrade.jar, the new pre-upgrade check tool available with 12.2 for pre-upgrade checks.
[oracle@rhel12c1 ~]$ $ORACLE_HOME/jdk/bin/java -jar preupgrade.jar TERMINAL
Report generated by Oracle Database Pre-Upgrade Information Tool Version
12.2.0.1.0
Upgrade-To version: 12.2.0.1.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: STD12C1
Container Name: STD12C1
Container ID: 0
Version: 12.1.0.2.0
Compatible: 12.1.0.2.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 18
Database log mode: NOARCHIVELOG
Readonly: FALSE
Edition: SE
Oracle Component Upgrade Action Current Status
---------------- -------------- --------------
Oracle Server [to be upgraded] VALID
Real Application Clusters [to be upgraded] VALID
Oracle Workspace Manager [to be upgraded] VALID
Oracle Text [to be upgraded] VALID
Oracle XML Database [to be upgraded] VALID
==============
BEFORE UPGRADE
==============
Run /preupgrade_fixups.sql to complete all
of the BEFORE UPGRADE action items below marked with '(AUTOFIXUP)'.
REQUIRED ACTIONS
================
+ Adjust TABLESPACE SIZES as needed.
Auto 12.2.0.1.0
Tablespace Size Extend Min Size Action
---------- ---------- -------- ---------- ------
SYSAUX 550 MB ENABLED 667 MB None
SYSTEM 700 MB ENABLED 806 MB None
TEMP 21 MB ENABLED 150 MB None
UNDOTBS1 230 MB ENABLED 400 MB None
Note that 12.2.0.1.0 minimum sizes are estimates.
If you plan to upgrade multiple pluggable databases concurrently,
then you must ensure that the UNDO tablespace size is equal to at least
the number of pluggable databases that you upgrade concurrently,
multiplied by that minimum. Failing to allocate sufficient space can
cause the upgrade to fail.
+ (AUTOFIXUP) Empty the RECYCLEBIN immediately before database upgrade.
The database contains 179 objects in the recycle bin.
The recycle bin must be completely empty before database upgrade.
RECOMMENDED ACTIONS
===================
+ Run 12.1.0.2.0 $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile invalid
objects. You can view the individual invalid objects with
SET SERVEROUTPUT ON;
EXECUTE DBMS_PREUP.INVALID_OBJECTS;
8 objects are INVALID.
There should be no INVALID objects in SYS/SYSTEM or user schemas before
database upgrade.
+ Review and remove any unnecessary EVENTS.
The database contains events.
There are events set that should be removed before upgrade, unless your
application vendors and/or Oracle Support state differently. Changes
will need to be made in the spfile.
+ (AUTOFIXUP) Gather stale data dictionary statistics prior to database
upgrade in off-peak time using:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Dictionary statistics do not exist or are stale (not up-to-date).
Dictionary statistics help the Oracle optimizer find efficient SQL
execution plans and are essential for proper upgrade timing. Oracle
recommends gathering dictionary statistics in the last 24 hours before
database upgrade.
For information on managing optimizer statistics, refer to the 12.1.0.2
Oracle Database SQL Tuning Guide.
=============
AFTER UPGRADE
=============
Run /postupgrade_fixups.sql to complete all
of the AFTER UPGRADE action items below marked with '(AUTOFIXUP)'.
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
+ Upgrade the database time zone version using the DBMS_DST package.
The database is using timezone datafile version 18 and the target
12.2.0.1.0 database ships with timezone datafile version 26.
Oracle recommends using the most recent timezone data. For further
information, refer to My Oracle Support Note 1585343.1.
+ (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
command:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Oracle recommends gathering dictionary statistics after upgrade.
Dictionary statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. After a
database upgrade, statistics need to be re-gathered as there can now be
tables that have significantly changed during the upgrade or new tables
that do not have statistics gathered yet.
Preupgrade generated files:
/opt/app/oracle/cfgtoollogs/std12c1/preupgrade/preupgrade_fixups.sql
/opt/app/oracle/cfgtoollogs/std12c1/preupgrade/postupgrade_fixups.sqlRun the pre-upgrade fixup
SQL> @/opt/app/oracle/cfgtoollogs/std12c1/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 12.2.0.1.0 Build: 1
Generated on: 2017-09-19 16:31:13
For Source Database: STD12C1
Source Database Version: 12.1.0.2.0
For Upgrade to Version: 12.2.0.1.0
Fixup
Check Name Status Further DBA Action
---------- ------ ------------------
purge_recyclebin Passed None
invalid_objects_exist Failed Manual fixup recommended.
underscore_events Failed Manual fixup recommended.
dictionary_stats Passed None
PL/SQL procedure successfully completed.Once the pre-ugprade checks are fixed run dbua from the 12.2 Oracle home. Select the database for upgrade, in this case the non-CDB is selected.
Timezone is upgraded at the same time as database.
Upgrade summary
Upgrade progress
Upgrade completion summary
Run the postupgrade fixup script
SQL> @/opt/app/oracle/cfgtoollogs/std12c1/preupgrade/postupgrade_fixups.sql
Auto-Generated by: Oracle Preupgrade Script
Version: 12.2.0.1.0 Build: 1
Generated on: 2017-09-19 16:35:42
For Source Database: STD12C1
Source Database Version: 12.1.0.2.0
For Upgrade to Version: 12.2.0.1.0
Fixup
Check Name Status Further DBA Action
---------- ------ ------------------
old_time_zones_exist Passed None
post_dictionary Passed NoneAfter the upgrade the DB components version and status
COMP_ID COMP_NAME VERSION STATUS
---------- ----------------------------------- ---------- --------
CATALOG Oracle Database Catalog Views 12.2.0.1.0 VALID
CATPROC Oracle Database Packages and Types 12.2.0.1.0 VALID
RAC Oracle Real Application Clusters 12.2.0.1.0 VALID
XDB Oracle XML Database 12.2.0.1.0 VALID
OWM Oracle Workspace Manager 12.2.0.1.0 VALID
CONTEXT Oracle Text 12.2.0.1.0 VALID
The database registry history added an entry for the upgrade
ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS
------------------------------ --------------- ---------- ---------- ---------- ----------------------------------------
BOOTSTRAP DATAPATCH 12.1.0.2 RDBMS_12.1.0.2.0DBPSU_LINUX.X64_161210
BOOTSTRAP DATAPATCH 12.2.0.1 RDBMS_12.2.0.1.0_LINUX.X64_170125
19-SEP-17 05.18.47.947470 PM VIEW INVALIDATE 8289601 view invalidation
19-SEP-17 05.20.49.419414 PM UPGRADE SERVER 12.2.0.1.0 Upgraded from 12.1.0.2.0Verify the timezone upgrade
SQL> select * from v$timezone_file;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_26.dat 26 0
This SE2 DB had statspack installed. Upgrade the statspack to 12.2
SQL>@?/rdbms/admin/spup12102.sql
SQL>@?/rdbms/admin/spup12200.sql
The remote_listener parameter had the new scan name added to it automatically. It also included the old scan name as well.
remote_listener string prod-cluster-scan.rac.domain.net:1521, prod-cluster-scan.prod-cluster.rac.domain.net:1521
Finally upgrade the database compatibility parameter
SQL> alter system set compatible='12.2.0.1.0' scope=spfile sid='*';
Upgrading CDBThe component version and status on the CDB before the upgrade is as below
CON_ID COMP_ID COMP_NAME VERSION STATUS
---------- ---------- ----------------------------------- --------------- ----------
1 APEX Oracle Application Express 4.2.5.00.08 VALID
1 CATALOG Oracle Database Catalog Views 12.1.0.2.0 VALID
1 CATJAVA Oracle Database Java Packages 12.1.0.2.0 VALID
1 CATPROC Oracle Database Packages and Types 12.1.0.2.0 VALID
1 CONTEXT Oracle Text 12.1.0.2.0 VALID
1 JAVAVM JServer JAVA Virtual Machine 12.1.0.2.0 VALID
1 ORDIM Oracle Multimedia 12.1.0.2.0 VALID
1 OWM Oracle Workspace Manager 12.1.0.2.0 VALID
1 RAC Oracle Real Application Clusters 12.1.0.2.0 VALID
1 XDB Oracle XML Database 12.1.0.2.0 VALID
1 XML Oracle XDK 12.1.0.2.0 VALID
3 APEX Oracle Application Express 4.2.5.00.08 VALID
3 CATALOG Oracle Database Catalog Views 12.1.0.2.0 VALID
3 CATJAVA Oracle Database Java Packages 12.1.0.2.0 VALID
3 CATPROC Oracle Database Packages and Types 12.1.0.2.0 VALID
3 CONTEXT Oracle Text 12.1.0.2.0 VALID
3 JAVAVM JServer JAVA Virtual Machine 12.1.0.2.0 VALID
3 ORDIM Oracle Multimedia 12.1.0.2.0 VALID
3 OWM Oracle Workspace Manager 12.1.0.2.0 VALID
3 RAC Oracle Real Application Clusters 12.1.0.2.0 VALID
3 XDB Oracle XML Database 12.1.0.2.0 VALID
3 XML Oracle XDK 12.1.0.2.0 VALIDSimilar to CDB run the preupgrade.jar from console. The output will have a section for each container (root, pdb see and all the pdbs opened).
[oracle@rhel12c1 ~]$ $ORACLE_HOME/jdk/bin/java -jar preupgrade.jar TERMINAL
Report generated by Oracle Database Pre-Upgrade Information Tool Version
12.2.0.1.0
Upgrade-To version: 12.2.0.1.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: STDCDB
Container Name: CDB$ROOT
Container ID: 1
Version: 12.1.0.2.0
Compatible: 12.1.0.2.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 18
Database log mode: NOARCHIVELOG
Readonly: FALSE
Edition: SE
Oracle Component Upgrade Action Current Status
---------------- -------------- --------------
Oracle Server [to be upgraded] VALID
JServer JAVA Virtual Machine [to be upgraded] VALID
Oracle XDK for Java [to be upgraded] VALID
Real Application Clusters [to be upgraded] VALID
Oracle Workspace Manager [to be upgraded] VALID
Oracle Text [to be upgraded] VALID
Oracle XML Database [to be upgraded] VALID
Oracle Java Packages [to be upgraded] VALID
Oracle Multimedia [to be upgraded] VALID
Oracle Application Express [to be upgraded] VALID
==============
BEFORE UPGRADE
==============
Run /preupgrade_fixups_CDB_ROOT.sql to complete all
of the BEFORE UPGRADE action items below marked with '(AUTOFIXUP)'.
REQUIRED ACTIONS
================
+ Adjust TABLESPACE SIZES as needed.
Auto 12.2.0.1.0
Tablespace Size Extend Min Size Action
---------- ---------- -------- ---------- ------
SYSAUX 850 MB ENABLED 1690 MB None
SYSTEM 780 MB ENABLED 1274 MB None
TEMP 72 MB ENABLED 150 MB None
UNDOTBS2 370 MB ENABLED 400 MB None
Note that 12.2.0.1.0 minimum sizes are estimates.
If you plan to upgrade multiple pluggable databases concurrently,
then you must ensure that the UNDO tablespace size is equal to at least
the number of pluggable databases that you upgrade concurrently,
multiplied by that minimum. Failing to allocate sufficient space can
cause the upgrade to fail.
+ (AUTOFIXUP) Empty the RECYCLEBIN immediately before database upgrade.
The database contains 62 objects in the recycle bin.
The recycle bin must be completely empty before database upgrade.
RECOMMENDED ACTIONS
===================
+ Run 12.1.0.2.0 $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile invalid
objects. You can view the individual invalid objects with
SET SERVEROUTPUT ON;
EXECUTE DBMS_PREUP.INVALID_OBJECTS;
7 objects are INVALID.
There should be no INVALID objects in SYS/SYSTEM or user schemas before
database upgrade.
+ (AUTOFIXUP) Gather stale data dictionary statistics prior to database
upgrade in off-peak time using:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Dictionary statistics do not exist or are stale (not up-to-date).
Dictionary statistics help the Oracle optimizer find efficient SQL
execution plans and are essential for proper upgrade timing. Oracle
recommends gathering dictionary statistics in the last 24 hours before
database upgrade.
For information on managing optimizer statistics, refer to the 12.1.0.2
Oracle Database SQL Tuning Guide.
INFORMATION ONLY
================
+ Consider upgrading APEX manually, before the database upgrade.
The database contains APEX version 4.2.5.00.08 and will need to be
upgraded to at least version 5.0.4.00.12.
To reduce database upgrade time, you can upgrade APEX manually before
the database upgrade. Refer to My Oracle Support Note 1088970.1 for
information on APEX installation upgrades.
=============
AFTER UPGRADE
=============
Run /postupgrade_fixups_CDB_ROOT.sql to complete all
of the AFTER UPGRADE action items below marked with '(AUTOFIXUP)'.
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
+ Upgrade the database time zone version using the DBMS_DST package.
The database is using timezone datafile version 18 and the target
12.2.0.1.0 database ships with timezone datafile version 26.
Oracle recommends using the most recent timezone data. For further
information, refer to My Oracle Support Note 1585343.1.
+ (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
command:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Oracle recommends gathering dictionary statistics after upgrade.
Dictionary statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. After a
database upgrade, statistics need to be re-gathered as there can now be
tables that have significantly changed during the upgrade or new tables
that do not have statistics gathered yet.
Report generated by Oracle Database Pre-Upgrade Information Tool Version
12.2.0.1.0
Upgrade-To version: 12.2.0.1.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: STDCDB
Container Name: PDB$SEED
Container ID: 2
Version: 12.1.0.2.0
Compatible: 12.1.0.2.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 18
Database log mode: NOARCHIVELOG
Readonly: TRUE
Edition: SE
Oracle Component Upgrade Action Current Status
---------------- -------------- --------------
Oracle Server [to be upgraded] VALID
JServer JAVA Virtual Machine [to be upgraded] VALID
Oracle XDK for Java [to be upgraded] VALID
Real Application Clusters [to be upgraded] VALID
Oracle Workspace Manager [to be upgraded] VALID
Oracle Text [to be upgraded] VALID
Oracle XML Database [to be upgraded] VALID
Oracle Java Packages [to be upgraded] VALID
Oracle Multimedia [to be upgraded] VALID
Oracle Application Express [to be upgraded] VALID
==============
BEFORE UPGRADE
==============
Run /preupgrade_fixups_PDB_SEED.sql to complete all
of the BEFORE UPGRADE action items below marked with '(AUTOFIXUP)'.
REQUIRED ACTIONS
================
+ Adjust TABLESPACE SIZES as needed.
Auto 12.2.0.1.0
Tablespace Size Extend Min Size Action
---------- ---------- -------- ---------- ------
SYSAUX 495 MB ENABLED 1384 MB None
SYSTEM 270 MB ENABLED 759 MB None
TEMP 62 MB ENABLED 150 MB None
Note that 12.2.0.1.0 minimum sizes are estimates.
If you plan to upgrade multiple pluggable databases concurrently,
then you must ensure that the UNDO tablespace size is equal to at least
the number of pluggable databases that you upgrade concurrently,
multiplied by that minimum. Failing to allocate sufficient space can
cause the upgrade to fail.
RECOMMENDED ACTIONS
===================
+ Run 12.1.0.2.0 $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile invalid
objects. You can view the individual invalid objects with
SET SERVEROUTPUT ON;
EXECUTE DBMS_PREUP.INVALID_OBJECTS;
11 objects are INVALID.
There should be no INVALID objects in SYS/SYSTEM or user schemas before
database upgrade.
+ (AUTOFIXUP) Gather stale data dictionary statistics prior to database
upgrade in off-peak time using:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Dictionary statistics do not exist or are stale (not up-to-date).
Dictionary statistics help the Oracle optimizer find efficient SQL
execution plans and are essential for proper upgrade timing. Oracle
recommends gathering dictionary statistics in the last 24 hours before
database upgrade.
For information on managing optimizer statistics, refer to the 12.1.0.2
Oracle Database SQL Tuning Guide.
INFORMATION ONLY
================
+ Consider upgrading APEX manually, before the database upgrade.
The database contains APEX version 4.2.5.00.08 and will need to be
upgraded to at least version 5.0.4.00.12.
To reduce database upgrade time, you can upgrade APEX manually before
the database upgrade. Refer to My Oracle Support Note 1088970.1 for
information on APEX installation upgrades.
=============
AFTER UPGRADE
=============
Run /postupgrade_fixups_PDB_SEED.sql to complete all
of the AFTER UPGRADE action items below marked with '(AUTOFIXUP)'.
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
+ Upgrade the database time zone version using the DBMS_DST package.
The database is using timezone datafile version 18 and the target
12.2.0.1.0 database ships with timezone datafile version 26.
Oracle recommends using the most recent timezone data. For further
information, refer to My Oracle Support Note 1585343.1.
+ (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
command:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Oracle recommends gathering dictionary statistics after upgrade.
Dictionary statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. After a
database upgrade, statistics need to be re-gathered as there can now be
tables that have significantly changed during the upgrade or new tables
that do not have statistics gathered yet.
Report generated by Oracle Database Pre-Upgrade Information Tool Version
12.2.0.1.0
Upgrade-To version: 12.2.0.1.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: STDCDB
Container Name: STDPDB
Container ID: 3
Version: 12.1.0.2.0
Compatible: 12.1.0.2.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 18
Database log mode: NOARCHIVELOG
Readonly: FALSE
Edition: SE
Oracle Component Upgrade Action Current Status
---------------- -------------- --------------
Oracle Server [to be upgraded] VALID
JServer JAVA Virtual Machine [to be upgraded] VALID
Oracle XDK for Java [to be upgraded] VALID
Real Application Clusters [to be upgraded] VALID
Oracle Workspace Manager [to be upgraded] VALID
Oracle Text [to be upgraded] VALID
Oracle XML Database [to be upgraded] VALID
Oracle Java Packages [to be upgraded] VALID
Oracle Multimedia [to be upgraded] VALID
Oracle Application Express [to be upgraded] VALID
==============
BEFORE UPGRADE
==============
Run /preupgrade_fixups_STDPDB.sql to complete all
of the BEFORE UPGRADE action items below marked with '(AUTOFIXUP)'.
REQUIRED ACTIONS
================
+ Adjust TABLESPACE SIZES as needed.
Auto 12.2.0.1.0
Tablespace Size Extend Min Size Action
---------- ---------- -------- ---------- ------
SYSAUX 525 MB ENABLED 1411 MB None
SYSTEM 290 MB ENABLED 785 MB None
TEMP 62 MB ENABLED 150 MB None
Note that 12.2.0.1.0 minimum sizes are estimates.
If you plan to upgrade multiple pluggable databases concurrently,
then you must ensure that the UNDO tablespace size is equal to at least
the number of pluggable databases that you upgrade concurrently,
multiplied by that minimum. Failing to allocate sufficient space can
cause the upgrade to fail.
+ (AUTOFIXUP) Empty the RECYCLEBIN immediately before database upgrade.
The database contains 16 objects in the recycle bin.
The recycle bin must be completely empty before database upgrade.
RECOMMENDED ACTIONS
===================
+ Run 12.1.0.2.0 $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile invalid
objects. You can view the individual invalid objects with
SET SERVEROUTPUT ON;
EXECUTE DBMS_PREUP.INVALID_OBJECTS;
85 objects are INVALID.
There should be no INVALID objects in SYS/SYSTEM or user schemas before
database upgrade.
+ (AUTOFIXUP) Gather stale data dictionary statistics prior to database
upgrade in off-peak time using:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Dictionary statistics do not exist or are stale (not up-to-date).
Dictionary statistics help the Oracle optimizer find efficient SQL
execution plans and are essential for proper upgrade timing. Oracle
recommends gathering dictionary statistics in the last 24 hours before
database upgrade.
For information on managing optimizer statistics, refer to the 12.1.0.2
Oracle Database SQL Tuning Guide.
INFORMATION ONLY
================
+ Consider upgrading APEX manually, before the database upgrade.
The database contains APEX version 4.2.5.00.08 and will need to be
upgraded to at least version 5.0.4.00.12.
To reduce database upgrade time, you can upgrade APEX manually before
the database upgrade. Refer to My Oracle Support Note 1088970.1 for
information on APEX installation upgrades.
=============
AFTER UPGRADE
=============
Run /postupgrade_fixups_STDPDB.sql to complete all
of the AFTER UPGRADE action items below marked with '(AUTOFIXUP)'.
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
+ Upgrade the database time zone version using the DBMS_DST package.
The database is using timezone datafile version 18 and the target
12.2.0.1.0 database ships with timezone datafile version 26.
Oracle recommends using the most recent timezone data. For further
information, refer to My Oracle Support Note 1585343.1.
+ (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
command:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Oracle recommends gathering dictionary statistics after upgrade.
Dictionary statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. After a
database upgrade, statistics need to be re-gathered as there can now be
tables that have significantly changed during the upgrade or new tables
that do not have statistics gathered yet.
Preupgrade generated files:
/opt/app/oracle/cfgtoollogs/stdcdb/preupgrade/preupgrade_fixups.sql
/opt/app/oracle/cfgtoollogs/stdcdb/preupgrade/postupgrade_fixups.sqlAs seen from output above each section has it's own pre and post upgrade script. However the final pre and post upgrade scripts (preupgrade_fixups.sql and postupgrade_fixups.sql) mentioned have the same content as those individual container specific scripts. Moreover they have container specif sections that will only run on the specified container. These *upgrade_fixups scripts could be run in all the containers using catcon.pl as below.
$ cd /opt/app/oracle/product/12.1.0/dbhome_2/rdbms/admin/
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b preupgrade_fixups /opt/app/oracle/cfgtoollogs/stdcdb/preupgrade/preupgrade_fixups.sql
catcon: ALL catcon-related output will be written to preupgrade_fixups_catcon_30484.lst
catcon: See preupgrade_fixups*.log files for output generated by scripts
catcon: See preupgrade_fixups_*.lst files for spool files, if any
catcon.pl: completed successfully
Once the preupgrade scripts have completed, run dbua to begin CDB upgrade.
Select the PDBs upgraded at the same time as CDB. As this is a SE2 only single PDB is allwoed and that single PDB is upgraded along with the CDB.
The other steps are similar to that of non-CDB. Below is the pre-upgrade summary
Upgrade progress
Post upgrade summary
Run the post upgrade script on all containers using catcon.pl
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b postupgrade_fixups /opt/app/oracle/cfgtoollogs/stdcdb/preupgrade/postupgrade_fixups.sql
catcon: ALL catcon-related output will be written to [/opt/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/postupgrade_fixups_catcon_11556.lst]
catcon: See [/opt/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/postupgrade_fixups*.log] files for output generated by scripts
catcon: See [/opt/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/postupgrade_fixups*.lst] files for spool files, if any
catcon.pl: completed successfully
Component status and version after the upgrade
CON_ID COMP_ID COMP_NAME STATUS VERSION
---------- --------------- ----------------------------------- -------- ---------------
1 CATALOG Oracle Database Catalog Views VALID 12.2.0.1.0
1 CATPROC Oracle Database Packages and Types VALID 12.2.0.1.0
1 JAVAVM JServer JAVA Virtual Machine VALID 12.2.0.1.0
1 XML Oracle XDK VALID 12.2.0.1.0
1 CATJAVA Oracle Database Java Packages VALID 12.2.0.1.0
1 RAC Oracle Real Application Clusters VALID 12.2.0.1.0
1 XDB Oracle XML Database VALID 12.2.0.1.0
1 OWM Oracle Workspace Manager VALID 12.2.0.1.0
1 CONTEXT Oracle Text VALID 12.2.0.1.0
1 ORDIM Oracle Multimedia VALID 12.2.0.1.0
1 APEX Oracle Application Express VALID 5.0.4.00.12
3 CATALOG Oracle Database Catalog Views VALID 12.2.0.1.0
3 CATPROC Oracle Database Packages and Types VALID 12.2.0.1.0
3 JAVAVM JServer JAVA Virtual Machine VALID 12.2.0.1.0
3 XML Oracle XDK VALID 12.2.0.1.0
3 CATJAVA Oracle Database Java Packages VALID 12.2.0.1.0
3 RAC Oracle Real Application Clusters VALID 12.2.0.1.0
3 XDB Oracle XML Database VALID 12.2.0.1.0
3 OWM Oracle Workspace Manager VALID 12.2.0.1.0
3 CONTEXT Oracle Text VALID 12.2.0.1.0
3 ORDIM Oracle Multimedia VALID 12.2.0.1.0
3 APEX Oracle Application Express VALID 5.0.4.00.12Database registry history for each container
CON_ID ACTION NAMESPACE VERSION COMMENTS
---------- --------------- ---------- --------------- ----------------------------------------
1 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 VIEW INVALIDATE view invalidation
1 UPGRADE SERVER 12.2.0.1.0 Upgraded from 12.1.0.2.0
3 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 VIEW INVALIDATE view invalidation
3 UPGRADE SERVER 12.2.0.1.0 Upgraded from 12.1.0.2.0
Check the timezone on root and PDB
SQL> select * from v$timezone_file;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_26.dat 26 0
SQL> alter session set container=stdpdb;
SQL> select * from v$timezone_file;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_26.dat 26 0
If statspack is configured on PDB then upgrade it 12.2. Change the line
connect perfstat/&&perfstat_password
to
connect perfstat/&&perfstat_password@&&tns_alias
in both spup12102.sql and spup12200.sql. Then create a script with below content (replace password and tns alias values as needed) and run with catcon.pl. (Refer
2020285.1 for more)
cat statup.sql
define perfstat_password='perfstat'
define tns_alias='stdpdb'
@?/rdbms/admin/spup12102.sql;
@?/rdbms/admin/spup12200.sql;
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b statup -c stdpdb statup.sql
Same as CDB the remote_listener parameter had the new scan name added to it automatically. It also had the old scan name as well.
remote_listener string prod-cluster-scan.rac.domain.net:1521, prod-cluster-scan.prod-cluster.rac.domain.net:1521
Finally upgrade the compatible parameter on the CDB.
SQL> alter system set compatible='12.2.0.1.0' scope=spfile sid='*';
That concludes the upgrading of the databases.
Now that all the databases in the cluster are upgraded, increase the ASM related compatible parameters to 12.2
alter diskgroup fra SET attribute 'compatible.asm'='12.2.0.1.0';
alter diskgroup DATA SET attribute 'compatible.asm'='12.2.0.1.0';
alter diskgroup fra set attribute 'compatible.rdbms'='12.2.0.1.0';
alter diskgroup data set attribute 'compatible.rdbms'='12.2.0.1.0';
alter diskgroup clusfs SET attribute 'compatible.asm'='12.2.0.1.0';
alter diskgroup clusfs set attribute 'compatible.rdbms'='12.2.0.1.0';
alter diskgroup GIMR SET attribute 'compatible.asm'='12.2.0.1.0';
alter diskgroup GIMR set attribute 'compatible.rdbms'='12.2.0.1.0';
GROUP_NUMBER NAME VALUE
------------ ------------------------- ---------------
1 compatible.asm 12.2.0.1.0
1 compatible.rdbms 12.2.0.1.0
2 compatible.asm 12.2.0.1.0
2 compatible.rdbms 12.2.0.1.0
3 compatible.asm 12.2.0.1.0
3 compatible.rdbms 12.2.0.1.0
4 compatible.asm 12.2.0.1.0
4 compatible.rdbms 12.2.0.1.0As mentioned in
previous post, upgrade adds new oracle user accounts with default passwords. Change the default passwords.
Finally run
orachk -u -o post
to check post upgrade status.
Related Posts
Upgrading RAC from 11.2.0.4 to 12.2.0.1 - Database
Upgrading 11.2.0.3 (11gR2) Database to 12.1.0.1 (12c) Using DBUA
Upgrading from 11.2.0.3 to 12.1.0.1 RAC
Upgrade Oracle Database 12c1 from 12.1.0.1 to 12.1.0.2
Upgrading 12c CDB and PDB from 12.1.0.1 to 12.1.0.2
Upgrading RAC from 11.2.0.4 to 12.1.0.2 - Database
Upgrading RAC from 12.1.0.1 to 12.1.0.2 - Database
Upgrading RAC from 11.2.0.3 to 11.2.0.4 - Database
