Oracle Database Upgrade 12c to 19c step by step
In this article we have disscussion the steps for upgrade the single instance database 12c to 19c. Direct upgrade to 19c supported database versions are: 11.2.0.4, 12.1.0.2, 12.2.0.1, 18c. This tutorial we are going to show oracle database upgrade 12.2.0.1 to 19c.
Oracle database upgrade steps are as follow.
Step 1: Download and install oracle 19c software only
Step 2: Run preupgrade.jar
Step 3: Before upgrade required actions
Step 4: Database upgradation required actions
Step 5: After upgrade required actions
==============================================================
Step 1: Download and install oracle 19c software only
==============================================================
Download the oracle database 19c software and upload downloaded zip file into the oracle database server.
# Create Oracle Home for 19c database using following command
mkdir -p /u01/app/oracle/product/19.0.0/dbhome_1
unzip the 19c software file in 19c oracle home using following command
unzip LINUX.X64_193000_db_home.zip -d /u01/app/oracle/product/19.0.0/dbhome_1
[oracle@proddb dbhome_1]$ export ORACLE_SID=/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@proddb dbhome_1]$ ./runInstaller
Oracle 19c software only installation completed successfully.
================================
Step 2: Run preupgrade.jar
================================
Run preupgrade.jar into the 19c Oracle Home. preupgrade.jar provide pre and post upgradation recommanded steps so we should carefully go through the recommanded actions. Set original oracle home (12c) and run preupgrade.jar from 19c home
export ORACLE_HOME=/u01/app/oracle/product/12.0.0/dbhome_1
export ORACLE_SID=TESTDB
# Run preupgrade.jar
[oracle@proddb ~]$ /u01/app/oracle/product/19.0.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar TERMINAL TEXT
Command Output:
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-12-26T16:27:44
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: TESTDB
Container Name: TESTDB
Container ID: 0
Version: 12.2.0.1.0
DB Patch Level: No Patch Bundle applied
Compatible: 12.2.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 26
Database log mode: NOARCHIVELOG
Readonly: FALSE
Edition: EE
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] OPTION OFF
Oracle Workspace Manager [to be upgraded] VALID
OLAP Analytic Workspace [to be upgraded] VALID
Oracle Label Security [to be upgraded] VALID
Oracle Database Vault [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 Spatial [to be upgraded] VALID
Oracle OLAP API [to be upgraded] VALID
==============
BEFORE UPGRADE
==============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
1. (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.2.0.1
Oracle Database SQL Tuning Guide.
INFORMATION ONLY
================
2. To help you keep track of your tablespace allocations, the following
AUTOEXTEND tablespaces are expected to successfully EXTEND during the
upgrade process. Min Size
Tablespace Size For Upgrade
---------- ---------- -----------
SYSAUX 460 MB 500 MB
SYSTEM 810 MB 918 MB
TEMP 32 MB 150 MB
UNDOTBS1 70 MB 439 MB
Minimum tablespace sizes for upgrade are estimates.
3. Check the Oracle Backup and Recovery User's Guide for information on how
to manage an RMAN recovery catalog schema.
If you are using a version of the recovery catalog schema that is older
than that required by the RMAN client version, then you must upgrade the
catalog schema.
It is good practice to have the catalog schema the same or higher version
than the RMAN client version you are using.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database TESTDB
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/TESTDB/preupgrade/p
reupgrade_fixups.sql
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
4. Upgrade the database time zone file using the DBMS_DST package.
The database is using time zone file version 26 and the target 19 release
ships with time zone file version 32.
Oracle recommends upgrading to the desired (latest) version of the time
zone file. For more information, refer to "Upgrading the Time Zone File
and Timestamp with Time Zone Data" in the 19 Oracle Database
Globalization Support Guide.
5. To identify directory objects with symbolic links in the path name, run
$ORACLE_HOME/rdbms/admin/utldirsymlink.sql AS SYSDBA after upgrade.
Recreate any directory objects listed, using path names that contain no
symbolic links.
Some directory object path names may currently contain symbolic links.
Starting in Release 18c, symbolic links are not allowed in directory
object path names used with BFILE data types, the UTL_FILE package, or
external tables.
6. (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.
7. Gather statistics on fixed objects after the upgrade and when there is a
representative workload on the system 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.
For information on managing optimizer statistics, refer to the 12.2.0.1
Oracle Database SQL Tuning Guide.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database TESTDB
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/TESTDB/preupgrade/p
ostupgrade_fixups.sql
==================
PREUPGRADE SUMMARY
==================
/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/TESTDB/preupgrade/preupgrade.log
/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/TESTDB/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/TESTDB/preupgrade/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the preupgrade fixups
@/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/TESTDB/preupgrade/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/TESTDB/preupgrade/postupgrade_fixups.sql
Preupgrade complete: 2020-12-26T16:27:44
========================================
Step 3: Before Upgrade Required Actions
========================================
All of the issues in database TESTDB which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by executing the following
[oracle@proddb ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Dec 26 16:56:11 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL>@/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/TESTDB/preupgrade/preupgrade_fixups.sql
Script Output:
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2020-12-26 16:27:38
For Source Database: TESTDB
Source Database Version: 12.2.0.1.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
1. dictionary_stats YES None.
2. tablespaces_info NO Informational only.
Further action is optional.
3. rman_recovery_version NO Informational only.
Further action is optional.
The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade. To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.
PL/SQL procedure successfully completed.
==========================================
Step 4: Database Upgrade Required Actions
==========================================
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
cd $ORACLE_HOME/network/admin for 12c database home
[oracle@proddb admin]$ cp *.ora /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/
# Add this to $/u01/app/oracle/product/19.0.0/dbhome_1/network/admin//sqlnet.ora
# This should be temporary while you deal with old passwords.
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11
# Switch to the 19c listener.
[oracle@proddb ~]$ lsnrctl stop
[oracle@proddb ~]$ export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/dbhome_1
[oracle@proddb ~]$ export PATH=${ORACLE_HOME}/bin:$PATH
[oracle@proddb ~]$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 26-DEC-2020 17:13:28
@ Start the database using the 19c Oracle home, ready for the upgrade.
[oracle@proddb ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Dec 26 17:16:46 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 1543500144 bytes
Fixed Size 8896880 bytes
Variable Size 939524096 bytes
Database Buffers 587202560 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
# You can run the upgrade from 19c ORACLE_HOME
[oracle@proddb ~]$ $ORACLE_HOME/bin/dbupgrade
Script Output:
Argument list for [/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
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: [19.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417]
/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/19.0.0/dbhome_1]
/u01/app/oracle/product/19.0.0/dbhome_1/bin/orabasehome = [/u01/app/oracle/product/19.0.0/dbhome_1]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.0.0/dbhome_1]
Analyzing file /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catupgrd.sql
Log file directory = [/tmp/cfgtoollogs/upgrade20201226171944]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20201226171944/catupgrd_catcon_42108.lst]
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20201226171944/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20201226171944/catupgrd_*.lst] files for spool files, if any
Number of Cpus = 4
Database Name = TESTDB
DataBase Version = 12.2.0.1.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/TESTDB/upgrade20201226172009/catupgrd_catcon_42108.lst]
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/TESTDB/upgrade20201226172009/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/TESTDB/upgrade20201226172009/catupgrd_*.lst] files for spool files, if any
Log file directory = [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/TESTDB/upgrade20201226172009]
Parallel SQL Process Count = 4
Components in [TESTDB]
Installed [APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM SDO XDB XML XOQ]
Not Installed [APEX EM MGW ODM RAC WK]
------------------------------------------------------
Phases [0-107] Start Time:[2020_12_26 17:20:35]
------------------------------------------------------
------------------------------------------------------
Phases [0-107] End Time:[2020_12_26 18:27:37]
------------------------------------------------------
Grand Total Time: 4024s
LOG FILES: (/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/TESTDB/upgrade20201226172009/catupgrd*.log)
Upgrade Summary Report Located in:
/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/TESTDB/upgrade20201226172009/upg_summary.log
Grand Total Upgrade Time: [0d:1h:7m:4s]
========================================
Step 5: AFTER Upgrade Required Actions
=======================================
sql> shut immediate
sql>statup upgrade
SQL> SELECT * FROM v$timezone_file;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_26.dat 26 0
# Begin upgrade to the latest timezone version.
SQL> SET SERVEROUTPUT ON
DECLARE
l_tz_version PLS_INTEGER;
BEGIN
l_tz_version := DBMS_DST.get_latest_timezone_version;
DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
DBMS_DST.begin_upgrade(l_tz_version);
END;
/
SQL> 2 3 4 5 6 7 8 9
l_tz_version=32
An upgrade window has been successfully started.
PL/SQL procedure successfully completed.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
ORACLE instance started.
Total System Global Area 1543500144 bytes
Fixed Size 8896880 bytes
Variable Size 1157627904 bytes
Database Buffers 369098752 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
-- Do upgradation (Check the upgradation status)
SQL> SET SERVEROUTPUT ON
DECLARE
l_failures PLS_INTEGER;
BEGIN
DBMS_DST.upgrade_database(l_failures);
DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);
DBMS_DST.end_upgrade(l_failures);
DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);
END;
/
SQL> 2 3 4 5 6 7 8 9
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "DVSYS"."SIMULATION_LOG$"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
Number of failures: 0
DBMS_DST.upgrade_database : l_failures=0
An upgrade window has been successfully ended.
DBMS_DST.end_upgrade : l_failures=0
PL/SQL procedure successfully completed.
# Check timezone for 19c which version will be 32
SQL> SELECT * FROM v$timezone_file;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_32.dat 32 0
# Gather fixed object stats.
SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
PL/SQL procedure successfully completed.
# POST Upgrade sql
SQL>@/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/TESTDB/preupgrade/postupgrade_fixups.sql
Script Output:
Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Package created.
No errors.
Package body created.
PL/SQL procedure successfully completed.
No errors.
Package created.
No errors.
Package body created.
No errors.
Executing Oracle POST-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2020-12-26 16:27:43
For Source Database: TESTDB
Source Database Version: 12.2.0.1.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
4. old_time_zones_exist YES None.
5. dir_symlinks YES None.
6. post_dictionary YES None.
7. post_fixed_objects NO Informational only.
Further action is optional.
The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database upgrade is not
fully complete. To resolve the outstanding issues, start by reviewing
the postupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.
PL/SQL procedure successfully completed.
Session altered.
Database is upgraded 12c to 19c successfully. Lets enjoy 19c database.
Done!
Post a Comment
0 Comments