Oracle Aesthete: Upgrade 12c Database to 19c

Upgrade 12c Database to 19c - on WINDOWS Platform

Step 1

Install java , JAVA is needed to execute preupgrade.jar from 19c home

C:\java\jre

=====================================================================

Step 2

Install 19c database software

D:\19c\app\product\db_home

=====================================================================

Step 3

Source 12c home environment to complete the precheck

Set oracle_home=D:\app\oracledb\product\12.1.0\dbhome_1

Set path=%oracle_home%/bin;%path%

Set oracle_sid=WCSRV06

====================================================================

Step 4

Ensure backup is complete before upgrade

SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

=====================================================================

Step 5

Empty Recycle bin.

PURGE DBA_RECYCLEBIN;

=====================================================================

Step 6

Run Gather statistics to finish upgrade soon.

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

=====================================================================

Step 7

Stop the running 12c listener

lsnrctl stop LISTENER

=====================================================================

Step 8

Execute preupgrade tool and fixups

It is strongly recommended to run the Pre-Upgrade Information Tool before starting the upgrade with DBUA, even though DBUA runs the Pre-Upgrade Information Tool as part of the pre-requisite checks, it is considered as a good practice to execute preupgrade.jar.

Connect to 12.1.0.2 database environment as SYS,

Set oracle_home=D:\app\oracledb\product\12.1.0\dbhome_1

set path=%oracle_home%/bin;%path%

Set oracle_sid=WCSRV06

From 12c environment we will run the preupgrade.jar in 19c home

C:\Windows\system32>C:\java\jre\bin\java.exe -jar D:\19c\app\product\db_home\rdbms\admin\preupgrade.jar TEXT TERMINAL

Report generated by Oracle Database Pre-Upgrade Information Tool Version

19.0.0.0.0 Build: 1 on 2021-03-23T09:47:58

Upgrade-To version: 19.0.0.0.0

=======================================

Status of the database prior to upgrade

=======================================

Database Name: WCSRV06

Container Name: WCSRV06

Container ID: 0

Version: 12.1.0.2.0

DB Patch Level: No Patch Bundle applied

Compatible: 12.1.0.2.0

Blocksize: 8192

Platform: Microsoft Windows x86 64-bit

Timezone File: 18

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

================

1. Contact Oracle Support for instructions on how to resolve this error.

ERROR: ORA-29548 ORA-29548: Java system class reported: release of Java

system classes in the database (12.1.0.0.0 1.6) does not match that of

the oracle executable (12.1.0.2.190115 1.6)

There is a problem with the JAVAVM component and database upgrade cannot

be performed until it is corrected.

JAVAVM component must be functioning properly before database upgrade.

RECOMMENDED ACTIONS

===================

2. 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;

62 objects are INVALID.

There should be no INVALID objects in SYS/SYSTEM or user schemas before

database upgrade.

3. Upgrade Oracle Application Express (APEX) manually before the database upgrade.

The database contains APEX version 4.2.5.00.08. Upgrade APEX to at least version 18.2.0.00.12.

Starting with Oracle Database Release 18, APEX is not upgraded

automatically as part of the database upgrade. Refer to My Oracle Support

Note 1088970.1 for information about APEX installation and upgrades.

INFORMATION ONLY

================

4. 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 1310 MB 1376 MB

SYSTEM 810 MB 1158 MB

Minimum tablespace sizes for upgrade are estimates.

5. 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.

=============

AFTER UPGRADE

=============

REQUIRED ACTIONS

================

None

RECOMMENDED ACTIONS

===================

6. Upgrade the database time zone file using the DBMS_DST package.

The database is using time zone file version 18 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.

7. 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.

8. (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.

9. 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.1.0.2

Oracle Database SQL Tuning Guide.

ORACLE GENERATED FIXUP SCRIPT

=============================

All of the issues in database WCSRV06

which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by

executing the following

SQL>@D:\app\oracledb\cfgtoollogs\WCSRV06\preupgrade/postupgrade_fixups.sql

==================

PREUPGRADE SUMMARY

==================

D:\app\oracledb\cfgtoollogs\WCSRV06\preupgrade\preupgrade.log

D:\app\oracledb\cfgtoollogs\WCSRV06\preupgrade\preupgrade_fixups.sql

D:\app\oracledb\cfgtoollogs\WCSRV06\preupgrade\postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade:

Log into the database and execute the preupgrade fixups

@D:\app\oracledb\cfgtoollogs\WCSRV06\preupgrade\preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups

@D:\app\oracledb\cfgtoollogs\WCSRV06\preupgrade\postupgrade_fixups.sql

Preupgrade complete: 2021-03-23T09:47:58

C:\Windows\system32>

=====================================================================

Step 9

Let’s resolve the BEFORE UPGRADE < Required Actions > highlighted.

1. Contact Oracle Support for instructions on how to resolve this error.

ERROR: ORA-29548 ORA-29548: Java system class reported: release of Java system classes in the database (12.1.0.0.0 1.6) does not match that of the oracle executable (12.1.0.2.190115 1.6)

SOLUTION:-

SQL> select dbms_java.get_jdk_version() from dual;

select dbms_java.get_jdk_version() from dual

ERROR at line 1:

ORA-29548: Java system class reported: release of Java system classes in the database (12.1.0.0.0 1.6) does not match that of the oracle executable (12.1.0.2.190115 1.6)

SQL> select patch_id from dba_registry_sqlpatch;

no rows selected

There were Patches applied to Oracle home which are not registered within the database. Let’s register the PSU & OJVM patches applied to Oracle Home within the database.

SQL> shut immediate

SQL> startup upgrade

SQL> D:\app\oracledb\product\12.1.0\dbhome_1\OPatch>datapatch -verbose

SQL> select patch_id from dba_registry_sqlpatch;

PATCH_ID

----------

28810679

28994063

SQL> select dbms_java.get_jdk_version() from dual;

DBMS_JAVA.GET_JDK_VERSION()

------------------------------------------------------ 1.6.0_211

SQL> shut immediate

SQL> startup

2. 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;

62 objects are INVALID.

SOLUTION:-

@?/rdbms/admin/utlrp.sql

SQL> select count(*) from dba_invalid_objects;

COUNT(*)

----------

3. Upgrade Oracle Application Express (APEX) manually before the database upgrade.

We are not using Apex here. Can upgrade it later.

=====================================================================

Step 10

Let’s rerun the Script again.

C:\java\jre\bin\java.exe -jar D:\19c\app\product\db_home\rdbms\admin\preupgrade.jar TEXT

Report generated by Oracle Database Pre-Upgrade Information Tool Version

19.0.0.0.0 Build: 1 on 2021-03-23T10:32:48

--------

---------------------------

-------------------------

==============

BEFORE UPGRADE

==============

REQUIRED ACTIONS

================

None

RECOMMENDED ACTIONS

===================

--------

-----------------

ORACLE GENERATED FIXUP SCRIPT

=============================

All of the issues in database WCSRV06 which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by executing the following

SQL>@D:\app\oracledb\cfgtoollogs\WCSRV06\preupgrade/postupgrade_fixups.sql

==================

PREUPGRADE SUMMARY

==================

D:\app\oracledb\cfgtoollogs\WCSRV06\preupgrade\preupgrade.log D:\app\oracledb\cfgtoollogs\WCSRV06\preupgrade\preupgrade_fixups.sql

D:\app\oracledb\cfgtoollogs\WCSRV06\preupgrade\postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade:

Log into the database and execute the preupgrade fixups

@D:\app\oracledb\cfgtoollogs\WCSRV06\preupgrade\preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups

@D:\app\oracledb\cfgtoollogs\WCSRV06\preupgrade\postupgrade_fixups.sql

Preupgrade complete: 2021-03-23T10:32:48

===================================================================

Step 11

Run the preupgrade_fixup.sql

C:\Windows\system32>set oracle_home=D:\app\oracledb\product\12.1.0\dbhome_1

C:\Windows\system32>set path=%oracle_home%/bin;%path%

C:\Windows\system32>set oracle_sid=WCSRV06

sqlplus / as sysdba

SQL> @D:\app\oracledb\cfgtoollogs\WCSRV06\preupgrade\preupgrade_fixups.sql

Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script

Version: 19.0.0.0.0 Build: 1

Generated on: 2021-03-23 10:32:40

For Source Database: WCSRV06

Source Database Version: 12.1.0.2.0

For Upgrade to Version: 19.0.0.0.0

Preup Preupgrade

Action Issue Is

Number Preupgrade Check Name Remedied Further DBA Action

------ ------------------------ ---------- --------------------------------

1. Apex_manual_upgrade NO Manual fixups recommended.

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 ofthe 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 12

Start UPGRADE:

At this stage, we are still connected to 12c database which is up and running.

Now run the dbua utility from 19c oracle_home location

Set oracle_home=D:\19c\app\product\db_home

Set path=%oracle_home%\bin;%path%

Set oracle_sid=WCSRV06

dbua

Error in preupgrade tool execution.

ERROR - Unable to run preupgrade due to:

ERROR: ORA-12557: TNS: protocol adapter not loadable SP2-0751: Unable to connect to Oracle. Exiting SQL*Plus

Solution

è Create a tns for WCSRV06 the database to be upgraded under 19c home.

è Exit the dbua console

è Close the command prompt console.

è Open new command prompt and direct hit dbua.

è Issue was with PATH variable.

è I had sourced 19c home from the same console where I had previously sourced 12c. So the PATH variable had 12c home binaries linked as well.

Define a tns entry into 19c home for the database to be upgraded.

Upgrade Completed!!!

======================================================================

Step 13

Check the upgrade Version

SQL> select comp_name, version from dba_registry;

Oracle Database Catalog Views 19.0.0.0.0

Oracle Database Packages and Types 19.0.0.0.0

JServer JAVA Virtual Machine 19.0.0.0.0

Oracle XDK 19.0.0.0.0

Oracle Database Java Packages 19.0.0.0.0

OLAP Analytic Workspace 19.0.0.0.0

Oracle Real Application Clusters 19.0.0.0.0

Oracle XML Database 19.0.0.0.0

Oracle Workspace Manager 19.0.0.0.0

Oracle Text 19.0.0.0.0

Oracle Multimedia 19.0.0.0.0

Spatial 19.0.0.0.0

Oracle OLAP API 19.0.0.0.0

Oracle Label Security 19.0.0.0.0

Oracle Application Express 4.2.5.00.08

Oracle Database Vault 19.0.0.0.0

SQL> Select banner_full from v$version;

BANNER_FULL

---------------------------------------------------------------------------

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

======================================================================

Step 14

Execute post upgrade script from the upgraded DB

@D:\app\oracledb\cfgtoollogs\WCSRV06\preupgrade\postupgrade_fixups.sql

C:\Windows\system32>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 23 12:39:46 2021

Version 19.3.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> @D:\app\oracledb\cfgtoollogs\WCSRV06\preupgrade\postupgrade_fixups.sql

Session altered.

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.

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: 2021-03-23 10:32:47

For Source Database: WCSRV06

Source Database Version: 12.1.0.2.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.

thank you😊

Oracle Aesthete

Tuesday, 23 March 2021

Upgrade 12c Database to 19c - WINDOWS

2 comments:

Popular Posts