[Oracle] Upgrade an Oracle Database from 12.1 to 12.2

Hi all, in this tutorial we are going to upgrade an Oracle database from 12.1 to 12.2 version.

Pre-upgrade activities

If you want a general overview of the database status before upgrade, just execute the following query:

select comp_id, schema, comp_name, version, status from dba_registry;

COMP_ID    SCHEMA          COMP_NAME                                          VERSION         STATUS
---------- --------------- -------------------------------------------------- --------------- ----------
OWB        OWBSYS          OWB                                                12.1.0.1.0      VALID
APEX       APEX_030200     Oracle Application Express                         3.2.1.00.12     VALID
AMD        OLAPSYS         OLAP Catalog                                       12.1.0.1.0      VALID
SDO        MDSYS           Spatial                                            12.1.0.1.0      VALID
ORDIM      ORDSYS          Oracle Multimedia                                  12.1.0.1.0      VALID
XDB        XDB             Oracle XML Database                                12.1.0.1.0      VALID
CONTEXT    CTXSYS          Oracle Text                                        12.1.0.1.0      VALID
EXF        EXFSYS          Oracle Expression Filter                           12.1.0.1.0      VALID
RUL        EXFSYS          Oracle Rules Manager                               12.1.0.1.0      VALID
OWM        WMSYS           Oracle Workspace Manager                           12.1.0.1.0      VALID
CATALOG    SYS             Oracle Database Catalog Views                      12.1.0.1.0      VALID
CATPROC    SYS             Oracle Database Packages and Types                 12.1.0.1.0      VALID
JAVAVM     SYS             JServer JAVA Virtual Machine                       12.1.0.1.0      VALID
XML        SYS             Oracle XDK                                         12.1.0.1.0      VALID
CATJAVA    SYS             Oracle Database Java Packages                      12.1.0.1.0      VALID
APS        SYS             OLAP Analytic Workspace                            12.1.0.1.0      VALID
XOQ        SYS             Oracle OLAP API                                    12.1.0.1.0      VALID
RAC        SYS             Oracle Real Application Clusters                   12.1.0.1.0      VALID

All components should be in VALID state.

Then, the first thing to do is to copy the password file and the init file from the old to the new home (if you have them stored on filesystem):

scp $OLD_ORACLE_HOME/dbs/orapwINSTANCE_NAME $NEW_ORACLE_HOME/dbs/
scp $OLD_ORACLE_HOME/dbs/initINSTANCE_NAME.ora $NEW_ORACLE_HOME/dbs/

If you have multiple nodes, just copy the password file and init file on all the nodes, changing the name of the instance. If your environment needs it, copy the tns entry of your database from the tnsnames.ora file in the old Oracle Home to the tnsnames.ora in the new Oracle Home.

Then, check the compatible parameter in the database:

show parameter compatible;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      12.1.0.2.0
noncdb_compatible                    boolean     FALSE

It should be at least 11.2.0.4.0.

Then, recompile all objects and check if some of them remain invalid:

@OLD_ORACLE_HOME/rdbms/admin/utlrp.sql
exec DBMS_PREUP.INVALID_OBJECTS;

If some Oracle objects are still invalid, do not proceed, but find a way to validate them. For example, if the object XDB.DBMS_RESCONFIG is invalid, check the DOC ID 1989793.1 on Oracle Support portal.

Next, gather statistics on system dictionary:

exec DBMS_STATS.GATHER_DICTIONARY_STATS;

Then, check user passwords:

select USERNAME, PASSWORD_VERSIONS from DBA_USERS;

USERNAME		       PASSWORD_VERSIONS
------------------------------ -----------------
TEST_USER		       10G 11G 12C

The password version must include 12C, if not just reset it before upgrading:

alter user TEST_USER identified by TEST_USER;

Then, remove Enterprise Manager related objects (if configured) and purge the recycle bin:

@NEW_ORACLE_HOME/rdbms/admin/emremove.sql;
purge dba_recyclebin;

Then, check materialized views, the result of the following query must be 0:

select o.name from sys.obj$ o, sys.user$ u, sys.sum$ s where o.type# = 42 AND bitand(s.mflags, 8) =8;  

Check also for files in backup mode, the result of the following query must also be 0:

select * from v$backup where status != 'NOT ACTIVE';

And finally check if there is the following patch installed on the system, in the remote possibility of a downgrade:

$OLD_ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | grep -i 20898997

20898997   23144544  Sun May 13 16:02:22 CEST 2018  XMLTYPESUP QCTOXSNLB SHOULD NOT CHECK AGAINST

Lastly remove the OLAP catalog, if present:

@OLD_ORACLE_HOME/olap/admin/catnoamd.sql 

Disable dataguard configuration – optional

If there is a dataguard configuration enabled, just do the following steps.

Stop the standby database:

srvctl stop database -d STANDBY_DATABASE_NAME

From the broker command line just disable the standby database and then the configuration:

dgmgrl /

disable database 'STANDBY_DATABASE_NAME';
disable configuration;

Lastly, just stop the broker in the primary database:

alter system set DG_BROKER_START=FALSE;

If you want you can take a backup of the dataguard broker configuration file, its location is specified by the database parameter dg_broker_config_file.

Upgrade

Before actually start the upgrade, run the Oracle pre-upgrade utility:

$OLD_ORACLE_HOME/jdk/bin/java -jar $NEW_ORACLE_HOME/rdbms/admin/preupgrade.jar FILE TEXT DIR LOG_DIR

Preupgrade generated files:
    $LOG_DIR/preupgrade.log
    $LOG_DIR/preupgrade_fixups.sql
    $LOG_DIR/postupgrade_fixups.sql

The utility generates three files:

• preupgrade.log: this is a summary of the checks that Oracle performs, just do all the suggested things before proceeding
• preupgrade_fixups.sql: this is a script to launch after having addressed all the previous problems. It performs additional check to be rewieved before proceeding with the upgrade
• postupgrade_fixups.sql: this is a script to launch after the upgrade has been performed

Next, change the cluster_database parameter to false:

alter system set cluster_database=FALSE scope=spfile sid='*';

Now, perform the upgrade with the following commands:

srvctl stop database -d DB_UNIQUE_NAME

export ORACLE_HOME=NEW_ORACLE_HOME
export PATH=$ORACLE_HOME/bin:$PATH

sqlplus / as sysdba
startup upgrade;
exit

cd $ORACLE_HOME/bin
./dbupgrade  

It can take a lot of time, even hours, to finish.

Post-upgrade activies

After the upgrade a few steps have to be performed.

First, launch the following query that produces an output similar to the displayed one:

@$NEW_ORACLE_HOME/rdbms/admin/utlu122s.sql

Oracle Database 12.2 Post-Upgrade Status Tool           02-18-2020 13:52:18

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

Oracle Server                          UPGRADED      12.2.0.1.0  00:07:14
JServer JAVA Virtual Machine           UPGRADED      12.2.0.1.0  00:03:10
Oracle Real Application Clusters       UPGRADED      12.2.0.1.0  00:00:00
Oracle Workspace Manager               UPGRADED      12.2.0.1.0  00:00:40
OLAP Analytic Workspace                UPGRADED      12.2.0.1.0  00:00:09
Oracle OLAP API                        UPGRADED      12.2.0.1.0  00:00:09
Oracle XDK                             UPGRADED      12.2.0.1.0  00:01:08
Oracle Text                            UPGRADED      12.2.0.1.0  00:00:31
Oracle XML Database                    UPGRADED      12.2.0.1.0  00:01:18
Oracle Database Java Packages          UPGRADED      12.2.0.1.0  00:00:09
Oracle Multimedia                      UPGRADED      12.2.0.1.0  00:01:07
Spatial                                UPGRADED      12.2.0.1.0  00:03:01
Oracle Application Express                VALID     5.1.2.00.09  00:00:02
Final Actions                                                    00:02:14
Post Upgrade                                                     00:00:06

Then, launch the following scripts:

@NEW_ORACLE_HOME/rdbms/admin/catuppst.sql
@NEW_ORACLE_HOME/rdbms/admin/utlrp.sql

If everything is ok, launching the same query launched at the beginning should produce an output similar to the next one:

select COMP_ID, COMP_NAME, VERSION, STATUS from dba_registry;

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
JAVAVM                         JServer JAVA Virtual Machine             12.2.0.1.0                     VALID
XML                            Oracle XDK                               12.2.0.1.0                     VALID
CATJAVA                        Oracle Database Java Packages            12.2.0.1.0                     VALID
APS                            OLAP Analytic Workspace                  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
ORDIM                          Oracle Multimedia                        12.2.0.1.0                     VALID
SDO                            Spatial                                  12.2.0.1.0                     VALID
XOQ                            Oracle OLAP API                          12.2.0.1.0                     VALID
APEX                           Oracle Application Express               5.1.2.00.09                    VALID

Next, launch the script generated just before the upgrade

@LOG_DIR/postupgrade_fixups.sql

It just checks if additional things have to be done, almost everything is optional.

If the following query returns rows:

select distinct owner, table_name
from dba_tab_cols
where data_upgraded = 'NO'
order by 1,2;

Execute the following script:

@NEW_ORACLE_HOME/rdbms/admin/utluptabdata.sql

Lastly, upgrade the database at the cluster level:

srvctl upgrade database -db DB_UNIQUE_NAME -o $NEW_ORACLE_HOME

And set the variable cluster_database to true again:

alter system set cluster_database = true scope = spfile;
srvctl stop database -d DB_UNIQUE_NAME
srvctl start database -d DB_UNIQUE_NAME

Eventually adjust the /etc/oratab file with the new Oracle Home.

Upgrade time zone – optional

If your time zone is too old you can upgrade it with the following steps. In this example the old time zone is 18 and it is going to be upgraded to 26

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

alter session set "_simple_view_merging"=TRUE;

exec DBMS_DST.BEGIN_PREPARE(26);

TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;

EXEC DBMS_DST.END_PREPARE;

shutdown immediate;

startup upgrade;

purge dba_recyclebin;

TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;

EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('upg_tzv');

alter session set "_with_subquery"=materialize;
alter session set "_simple_view_merging"=TRUE;

exec DBMS_DST.BEGIN_UPGRADE(26);

SELECT * FROM sys.dst$error_table;
no rows selected

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

shutdown immediate

startup

alter session set "_with_subquery"=materialize;
alter session set "_simple_view_merging"=TRUE;

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 => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/

SELECT * FROM sys.dst$error_table;

VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/

SELECT VERSION FROM v$timezone_file;

VERSION 
-------------
26

select TZ_VERSION from registry$database;

TZ_VERSION  
---------
18

update registry$database 
set TZ_VERSION = (select version 
                  FROM v$timezone_file);
commit;

Setting max_string_size=extended – optional

In Oracle 12.2 a new feature has been released that enables the 32767 byte limit for VARCHAR2, NVARCHAR2, and RAW data types in SQL. Activate this feature following the next steps:

alter system set cluster_database = false scope = spfile;
srvctl stop database -d DB_UNIQUE_NAME
STARTUP UPGRADE;
alter system set MAX_STRING_SIZE=EXTENDED scope=spfile sid='*';
SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
@?/rdbms/admin/utl32k.sql
alter system set cluster_database = true scope = spfile;
SHUTDOWN IMMEDIATE;
srvctl start database -d DB_UNIQUE_NAME

Upgrade standby database – optional

In order to upgrade also the standby site, follow these steps:

• If the dataguard configuration uses static listeners, update the corresponding listener.ora changing the OLD_ORACLE_HOME to the NEW_ORACLE_HOME
• Change the oratab file setting the NEW_ORACLE_HOME instead of the OLD_ORACLE_HOME
• If the database connects to the primary through a tns entry, make sure to set this entry in the tnsnames.ora of the NEW_ORACLE_HOME. Simply copy the tns entry from the tnsnames.ora in the OLD_ORACLE_HOME
• Copy the init.ora e password files from the OLD_ORACLE_HOME to the NEW_ORACLE_HOME, if they are stored on filesystem

After doing these steps, just start the database in mount mode and wait until all archived logs have been applied:

srvctl start database -db STANDBY_DATABASE_NAME -startoption mount

After that, simply enable the standby database and the dataguard configuration:

dgmgrl /

enable database 'STANDBY_DATABASE_NAME';
enable configuration;

And enable the broker on the primary database:

alter system set DG_BROKER_START=TRUE;

Upgrade RMAN catalog – optional

The process to upgrade the RMAN catalog, if you have configured it, is very simple. Just connect to you database and catalog:

rman
RMAN> connect target CONNECT_STRING_TO_DB
RMAN> connect catalog CONNECT_STRING_TO_CATALOG

Once connected, Oracle automatically recognize that your RMAN catalog needs to be upgraded. Just type two times upgrade catalog and everything is done:

connected to recovery catalog database
PL/SQL package RMAN_LOOK0ONP.DBMS_RCVCAT version 12.01.00.02. in RCVCAT database is too old

RMAN> upgrade catalog

recovery catalog owner is RMAN_OWNER
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> upgrade catalog

recovery catalog upgraded to version 12.02.00.01
DBMS_RCVMAN package upgraded to version 12.02.00.01
DBMS_RCVCAT package upgraded to version 12.02.00.01.

Switchover test – optional

To check that both primary and standby databases have been correctly upgraded, a switchover from primary to standby should also be performed. A dataguard configuration may look similar to the next one:

dgmgrl CONNECT_STRING_TO_DB

DGMGRL for Linux: Version 12.2.0.1.0 - 64bit Production

Copyright (c) 2000, 2016, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> show configuration

Configuration - CONFIGURATION_DG

  Protection Mode: MaxPerformance
  Members:
  DB_UNIQUE_NAME - Primary database
    STANDBY_DATABASE_NAME - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 30 seconds ago)

To perform the switchover just type the following command. If everything is fine, the following output should appear:

DGMGRL> switchover to 'STANDBY_DATABASE_NAME';
Performing switchover NOW, please wait...
Operation requires a connection to instance "STANDBY_DATABASE_NAME" on database "STANDBY_DATABASE_NAME"
Connecting to instance "STANDBY_DATABASE_NAME"...
Connected as SYSDBA.
New primary database "STANDBY_DATABASE_NAME" is opening...
Oracle Clusterware is restarting database "DB_UNIQUE_NAME" ...
Switchover succeeded, new primary is "STANDBY_DATABASE_NAME"

To switch back to the previous primary, just type the following command:

DGMGRL> switchover to 'DB_UNIQUE_NAME';

Gather statistics

Last thing to do is to recalculate statistics.

First, recalculate them on the dictionary:

exec DBMS_STATS.GATHER_DICTIONARY_STATS;

Then, on all application schemas in the database with the following command:

exec DBMS_STATS.GATHER_SCHEMA_STATS (
ownname => 'SCHEMA_NAME', 
estimate_percent => dbms_stats.auto_sample_size, block_sample => TRUE,
 method_opt => 'FOR ALL COLUMNS SIZE AUTO', 
degree => 32,
 granularity => 'ALL', 
cascade => TRUE,
 options => 'GATHER AUTO');

That’s all! See you in the next tutorial!