cluvfy stage -pre dbinst -upgrade -src_dbhome /opt/app/oracle/product/11.2.0/dbhome_4 -dest_dbhome /opt/app/oracle/product/12.2.0/dbhome_1 -dest_version 12.2.0.1.0Create additional user groups that were introduced as part of 12c
# groupadd backupdba # groupadd dgdba # groupadd kmdba # groupadd racdba # usermod -g oinstall -G dba,oper,asmoper,asmdba,asmadmin,backupdba,dgdba,kmdba,racdba oracleInstall the 12.2.0.1 binaries by selecting the software only installation option. This installation is done out of place to current 11.2.0.4 oracle home.
chmod 770 $ORACLE_BASE/auditAlso a default listener is needed for DBUA upgrade to complete (this setup has a non-default listener name and port). DBUA will complain if the default listener is missing.
12.2 has a new preupgrade check tool, a jar file which is available in 12.2 $ORACLE_HOME/rdbms/admin. This could be run as a standalone jar. Running this check the upgrade readiness of the database
cp /opt/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/preupgrade.jar ~
[oracle@rhel6m1 ~]$ $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: STD11G2
Container Name: Not Applicable in Pre-12.1 database
Container ID: Not Applicable in Pre-12.1 database
Version: 11.2.0.4.0
Compatible: 11.2.0.4.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 14
Database log mode: ARCHIVELOG
Readonly: FALSE
Edition: R1
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 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 600 MB DISABLED 500 MB None
SYSTEM 700 MB ENABLED 914 MB None
TEMP 100 MB ENABLED 150 MB None
UNDOTBS1 375 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.
RECOMMENDED ACTIONS
===================
+ Backup the existing ACLs and their assignments for reference. Use the
new DBMS_NETWORK_ACL_ADMIN interfaces and dictionary views to administer
network privileges after upgrade.
The database contains network ACLs with privileges that will be migrated
to a new format in 12c.
Network access control list (ACL) privileges in 11g will be migrated to
a new format in 12c. As part of the migration, new
DBMS_NETWORK_ACL_ADMIN interfaces and dictionary views are provided, and
privileges in the existing ACLs will be converted to the new format with
new ACL names. The old ACL names, DBMS_NETWORK_ACL_ADMIN interfaces and
dictionary views may continue to be used but are deprecated and their
use is discouraged. For further information, refer to My Oracle Support
note number 2078710.1.
+ 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.
=============
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 14 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.
+ Gather statistics on fixed objects two weeks after the upgrade using the
command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
This recommendation is given for all preupgrade runs.
Fixed object statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. Those
statistics are specific to the Oracle Database release that generates
them, and can be stale upon database upgrade.
Preupgrade generated files:
/opt/app/oracle/cfgtoollogs/std11g2/preupgrade/preupgrade_fixups.sql
/opt/app/oracle/cfgtoollogs/std11g2/preupgrade/postupgrade_fixups.sql
SQL> @/opt/app/oracle/cfgtoollogs/std11g2/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-05-04 13:24:32
For Source Database: STD11G2
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 12.2.0.1.0
Fixup
Check Name Status Further DBA Action
---------- ------ ------------------
network_acl_priv Failed Manual fixup recommended.
underscore_events Failed Manual fixup recommended.
dictionary_stats Passed None
PL/SQL procedure successfully completed. Run the pre-upgrade fix script followed by the DBUA.SQL> @/opt/app/oracle/cfgtoollogs/std11g2/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-05-04 13:24:32
For Source Database: STD11G2
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 12.2.0.1.0
Fixup
Check Name Status Further DBA Action
---------- ------ ------------------
network_acl_priv Failed Manual fixup recommended.
underscore_events Failed Manual fixup recommended.
dictionary_stats Passed None
PL/SQL procedure successfully completed.Database registry component versions and status before the upgrade are shown below.COMP_ID COMP_NAME VERSION STATUS ---------- ---------------------------------------- --------------- ---------- CATJAVA Oracle Database Java Packages 11.2.0.3.0 REMOVED XML Oracle XDK 11.2.0.3.0 REMOVED JAVAVM JServer JAVA Virtual Machine 11.2.0.3.0 REMOVED RAC Oracle Real Application Clusters 11.2.0.4.0 VALID CATALOG Oracle Database Catalog Views 11.2.0.4.0 VALID OWM Oracle Workspace Manager 11.2.0.4.0 VALID XDB Oracle XML Database 11.2.0.4.0 VALID CATPROC Oracle Database Packages and Types 11.2.0.4.0 VALIDRun the DBUA to begin the database upgrade.
SQL> @/opt/app/oracle/cfgtoollogs/std11g2/preupgrade/postupgrade_fixups.sql
Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Package created.
No errors.
Package body created.
No errors.
Package created.
No errors.
Package body created.
No errors.
Executing Oracle POST-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 12.2.0.1.0 Build: 1
Generated on: 2017-05-04 13:27:35
For Source Database: STD11G2
Source Database Version: 11.2.0.4.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 None
fixed_objects Passed None
PL/SQL procedure successfully completed.
Session altered.The registry component statusCOMP_ID COMP_NAME VERSION STATUS ---------- ---------------------------------------- --------------- ---------- XML Oracle XDK 11.2.0.3.0 REMOVED CATJAVA Oracle Database Java Packages 11.2.0.3.0 REMOVED JAVAVM JServer JAVA Virtual Machine 11.2.0.3.0 REMOVED XDB Oracle XML Database 12.2.0.1.0 VALID OWM Oracle Workspace Manager 12.2.0.1.0 VALID RAC Oracle Real Application Clusters 12.2.0.1.0 VALID CATPROC Oracle Database Packages and Types 12.2.0.1.0 VALID CATALOG Oracle Database Catalog Views 12.2.0.1.0 VALIDRegistry history view shows upgrade to 12.2
ACTION_TIME ACTION NAMESPAC VERSION ID COMMENTS ------------------------------ -------------------- -------- ---------- ---------- ------------------------- 10-JUL-17 03.10.52.412402 PM APPLY SERVER 11.2.0.4 161018 PSU 11.2.0.4.161018 14-JUL-17 01.49.21.603390 PM VIEW INVALIDATE 8289601 view invalidation 14-JUL-17 02.03.18.931179 PM UPGRADE SERVER 12.2.0.1.0 Upgraded from 11.2.0.4.0Check the timezone file has been upgrade
SQL> select * from v$timezone_file; FILENAME VERSION CON_ID -------------------- ---------- ---------- timezlrg_26.dat 26 0This is a SE database which had statspack installed. Upgrade the statspack to 12.2 (refer 394937.1).
SQL>@?/rdbms/admin/spup112 SQL>@?/rdbms/admin/spup12200.sqlIf satisfied with the database upgrade set the compatibility parameter to 12.2. At the same time the compatibility values in disk group could also be set to 12.2.
SQL> alter system set compatible='12.2.0.1.0' scope=spfile sid='*';
srvctl stop database -d std11g2
alter diskgroup flash SET attribute 'compatible.asm'='12.2.0.1.0';
alter diskgroup DATA SET attribute 'compatible.asm'='12.2.0.1.0';
alter diskgroup flash set attribute 'compatible.rdbms'='12.2.0.1.0';
alter diskgroup data set attribute 'compatible.rdbms'='12.2.0.1.0';
alter diskgroup CLUSTER_DG SET attribute 'compatible.asm'='12.2.0.1.0';
alter diskgroup CLUSTER_DG3 SET attribute 'compatible.asm'='12.2.0.1.0';
alter diskgroup CLUSTER_DG set attribute 'compatible.rdbms'='12.2.0.1.0';
alter diskgroup CLUSTER_DG3 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';
select group_number,name,value from v$asm_attribute where name like 'compat%';
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.0
srvctl start database -d std11g2Upgrade also adds new oracle user accounts that are with default passwords to the database. Change the default passwords of these accountsGSMCATUSER REMOTE_SCHEDULER_AGENT DBSFWUSER SYSBACKUP GGSYS SYSRAC AUDSYS GSMADMIN_INTERNAL SYSKM SYS$UMF SYSDG GSMUSERFinally run
orachk -u -o postto check post upgrade status.
Related Posts
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
