Oracle® 19c Database is the latest release of a widely adopted automation database in the market and enterprises. Stability is an important element of the Oracle Database 19c component of the Oracle Database 12c (Release 12.2) family of products. In this two-part blog post series, I cover installing and upgrading version 19c.
This part of the two-part series focuses on upgrading Oracle Database from 220.127.116.11 to 19c in Windows®. This manual method does not use the Database Upgrade Assistant (DBUA).
For installation steps, refer to Part One of this series. I installed binaries on my 19c Oracle home directory, ORACLE_HOME=d:\app\product\19.0.0\dbhome_1.
Use the following steps to upgrade Oracle Database to 19c:
Note: You should have a valid backup before upgrading in case something goes wrong during the upgrade process.
Stage the 19.3 Relational Database Management System (RDBMS) install file so you can proceed with the upgrade.
Run the following steps to complete the pre-upgrade process:
Download the Oracle Database Pre-Upgrade Utility by using metalink note 884522.1. To run the pre-upgrade tool, run the following code:
set ORACLE_HOME=d:\app\product\18.104.22.168\dbhome_1 set ORACLE_BASE=d:\app set ORACLE_SID=ABC set PATH=%ORACLE_HOME%\bin;%PATH% %ORACLE_HOME%\jdk\bin\java -jar <top_dir>\preupgrade.jar TERMINAL TEXT -u sys -p <sys_password>
Check the output in d:\app\cfgtoollogs\ABC\preupgrade\preupgrade.txt, review the pre-upgrade log file and fix any errors.
You can run the pre-upgrade fixups script for all the parts with AUTOFIXUP in the logs. For example, to run d:\app\cfgtoologs\ABC\preupgrade\preupgrade_fixups.sql, execute the following code:
cd d:\app\cfgtoollogs\ABC\preupgrade sqlplus sys/
Review the output from the preupgrade_fixups.sql and perform any remaining manual steps.
Run the following command to take backup of pfile:
SQL> create pfile='d:\app\init_ABC.ora' from spfile;
Run the utlrp.sql script from SQL Plus to compile invalid objects. Make sure no invalid objects remain in sys/system schema. Save all other invalid objects in a separate table to match during the post-upgrade steps later on.
SQL>@?/rdbms/admin/utlrp.sql SQL> create table system.invalids_before_upgrade as select * From dba_invalid_objects;
Remove the EM repository by using the following steps:
Copy the emremove.sql script from 19c home to 11 g home:
copy d:\app\product\19.0.0\dbhome_1\rdbms\admin\emremove.sql d:\app\product\22.214.171.124\dbhome_1\rdbms\admin cd d:\app\product\126.96.36.199\dbhome_1\rdbms\admin sqlplus sys/<password> as sysdba SET ECHO ON; SET SERVEROUTPUT ON; @emremove.sql
Remove OLAP catalog by using the following steps:
cd d:\app\product\188.8.131.52\dbhome_1\olap\admin\ sqlplus sys/<password> as sysdba @catnoamd.sql
If you are not using Application Express (APEX), you can remove it by running the following commands:
cd d:\app\product\184.108.40.206\dbhome_1\apex sqlplus sys/<password> as sysdba @apxremov.sql drop package htmldb_system; drop public synonym htmldb_system;
Purge the DBA RECYCLEBIN by using the following command:
Gather Dictionary stats by using the following command:
Re-run the pre-upgrade tool to confirm that everything is ready.
Run the following upgrade steps to perform the upgrade:
Run the following steps to upgrade:
Shut down the Oracle 11g Database.
After shutting down the Oracle Database, open CMD with the admin option and remove all Oracle 11g Windows services by running the following steps from the command prompt:
set ORACLE_HOME=d:\app\product\19.0.0\dbhome_1 set PATH=%ORACLE_HOME%\bin;%PATH% set ORACLE_SID=ABC sc delete OracleJobSchedulerABC sc delete OracleMTSRecoveryService sc delete OracleServiceABC sc delete OracleVssWriterABC
Create the Oracle 19c Windows service by running the following commands:
d:\app\product\19.0.0\dbhome_1\bin\ORADIM -NEW -SID ABC -SYSPWD ********* -STARTMODE AUTO -PFILE D:\app\product\19.0.0\dbhome_1\database\INITABC.ORA
After the process creates the Oracle 19c windows services, start the services.
Start Oracle Database from 19C environment in upgrade mode.
After Oracle Database starts in the upgrade mode, perform the following steps:
Run the following command:
Execute the dbupgrade utility from the Windows command prompt.
After the upgrade completes, start the database and run the following command:
If the upgrade succeeds, run the post-upgrade fixup script:
d:\ cd d:\app\cfgtoollogs\ABC\preupgrade sqlplus sys/<password> as sysdba @postupgrade_fixups.sql
After you run the post-upgrade fixup scripts, run the following commands to upgrade the time zone:
sqlplus / as sysdba <<EOF -- Check current settings. SELECT * FROM v$timezone_file; SHUTDOWN IMMEDIATE; STARTUP UPGRADE; -- Begin upgrade to the latest version. 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; / SHUTDOWN IMMEDIATE; STARTUP; -- Do the upgrade. 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; / -- Validate time zone. SELECT * FROM v$timezone_file; COLUMN property_name FORMAT A30 COLUMN property_value FORMAT A20 SELECT property_name, property_value FROM database_properties WHERE property_name LIKE 'DST_%' ORDER BY property_name; exit; SQL> select TZ_VERSION from registry$database;
If the TZ_VERSION shows the old version, run the following commands:
SQL>update registry$database set TZ_VERSION = (select version FROM v$timezone_file); SQL>commit; SQL>select TZ_VERSION from registry$database; TZ_VERSION ---------- 32
Gather the fixed object stats by running the following commands:
sqlplus / as sysdba <<EOF EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; exit;
Gather dictionary statistics after the upgrade by running the following statement:
Run utlusts.sql to verify that no issues remain:
Match all invalid objects to the list you saved in Step 2.2.
To complete the upgrade, perform the following steps:
Copy listener.ora, tnsnames.ora, and sqlnet.ora from the Oracle 11g Oracle home directory to the Oracle 19c Oracle home directory and change the oracle_home parameters accordingly.
Place all these files in d:\app\product\19.0.0\dbhome_1\network\admin.
compatible=220.127.116.11 in case you need to downgrade the Oracle
Database to 11g.
The preceding steps help you easily upgrade Oracle Database in Windows version 18.104.22.168 to 19c.
Use the Feedback tab to make any comments or ask questions. You can also start a conversation with us.