Oracle Database Upgradation to 19c Using DBCA

adminMarch 26, 2021March 26, 2021

1. Installing Oracle 19c.

Follow the document

2. Executing the pre-upgrade jar tool

The pre-upgrade information tool is used to determine the instance readiness before upgrading the database. The pre-upgrade script will generate the fix for many issues before you upgrade to the new Oracle home.

The Pre-upgrade Information Tool is available in the new release Oracle home, in path ORACLE_HOME/rdbms/admin/preupgrade.jar

$>cd /d $ORACLE_HOME/rdbms/admin

$>java -jar preupgrade.jar Run the pre-upgrade tool

Make sure to run the tool from source ORACLE_HOME. Loaction/preupgrade.log Loaction/preupgrade_fixups.sql Loaction/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Check Loaction/preupgrade.log for fixing the issues.

Before upgrade:

Log into the database and execute the pre-upgrade fixups @Loaction/preupgrade_fixups.sql

3. Performing the pre-upgrade actions

Run the preupgrade_fixups.sql

SQL> location/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: 2020-11-07 22:23:20

For Source Database: ORADEV Source Database Version: 12

For Upgrade to Version: 19.0.0.0.0

Preup Preupgrade

Action Issue Is

Number Pre upgrade Check Name Remedied FurtherDBA Action

—————————————————————————————————————————————————————-

parameter_min_val NO Manual fixup
em_present NO Manual fixup
amd_exists NO Manual fixup
apex_manual_upgrade NO Manual fixup
dictionary_stats YES NONE
trgowner_no_admndbtrg YES NONE
pre_fixed_objects YES NONE
tablespaces_info NO Informational only Further action is optional.
exf_rul_exists NO Informational only Further action is optional.
rman_recovery_version NO Informational only Further action is optional.

The preupgrade_fixups.sql output lists multiple recommendations which have to be fixed manually. We will fix the below recommendations manually and re-run the preupgrade_fixups.sql

parameter_min_val NO Manual fixup recommended.
em_present NO Manual fixup
amd_exists NO Manual fixup
apex_manual_upgrade NO Manual fixup recommended

Recommendation 1: parameter_min_val

Update NUMERIC INITIALIZATION PARAMETERS to meet estimated This action may be done now or when starting the database in upgrade mode using the 19 ORACLE HOME.

Parameter Currently 19 minimum

——————————————————————————-

processes 150 300

The database upgrade process requires certain initialization parameters to meet minimum values. The Oracle upgrade process itself has minimum values which may be higher and are marked with an asterisk. After upgrading, those asterisked parameter values may be reset if needed.

Action: Increase the process parameter

surplus “/as sysdba”

SQL>alter system set processes=300 scope=spfile; shutdown immediate;

SQL>startup;

Recommendation 2: Remove the EM repositor

Stop the em dbconsole emctl stop dbconsole

… Stopped.

Copy emremove.sql from target 19c ORACLE_HOME/rdbms/admin to source 12c ORACLE_HOME/rdbms/admin

Connect to the database using SYS user and run emremove.sql

$ sqlplus Connected to:

Oracle Database 12c Enterprise Edition Release 12.0.0.0- 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @?/rdbms/admin/emremove.sql

old 70: IF (upper(‘&LOGGING’) = ‘VERBOSE’)

new 70: IF (upper(‘VERBOSE’) = ‘VERBOSE’)

PL/SQL procedure successfully completed.

SQL>

Recommendation 3: Remove OLTP catalog

Connect to the database as an SYS user and remove OLAP Catalog by running the 12.0.0.0 SQL script under $ORACLE_HOME/olap/admin/catnoamd.sql script.

SQL> @$ORACLE_HOME/olap/admin/catnoamd.sql Synonym dropped.

Synonym dropped.

Type dropped.

PL/SQL procedure successfully completed. Role dropped.

PL/SQL procedure successfully completed. 1 row deleted.

SQL>

Recommendation 4: Upgrade Oracle Application Express (APEX) manually.

Upgrading APEX

Check the current APEX Version

SQL> Select Comp_name, status, Version From Dba_Registry where comp_id=’APEX’;

COMP_NAME STATUS VERSION

Oracle Application Express VALID 3.2.1.00.12

SQL>

From the directory which holds the APEX unzipped software, connect to sqlplus as SYS user and run apexins.sql

cd /software/db-soft/apex sqlplus /as sysdba

SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/ Check the upgraded APEX Version

SQL> Select Comp_name, status, Version From Dba_Registry where comp_id=’APEX’;

COMP_NAME STATUS VERSION

Oracle Application Express VALID 20.2.0.00.20 SQL>

Note: No Actions required from Recommendation 5, 6 and 7

Recommendation: Check invalid objects

SQL> select count(1) from dba_objects where status=’INVALID’; SQL> @?/rdbms/admin/utlrp.sql

SQL> select count(1) from dba_objects where status=’INVALID’;

SQL> select owner,object_name from dba_objects where status=’INVALID’;

(If object_name are related to APEX… then follow below steps)

SELECT username, ‘drop user ‘ || username || ‘ cascade;’ AS remove_statement FROM dba_users

WHERE (username LIKE ‘FLOWS_%’ OR username LIKE ‘APEX_%’)

AND username NOT IN (‘FLOWS_FILES’, ‘APEX_PUBLIC_USER’, ‘APEX_LISTENER’, ‘APEX_REST_PUBLIC_USER’, ‘APEX_INSTANCE_ADMIN_USER’)

AND username NOT IN (SELECT schema s

FROM dba_registry WHERE comp_id = ‘APEX’);

So cleaning up can be done as easily as this:

drop user APEX_030200 cascade;

(for OLAPSYS invalid objects then follow below steps)

SQL> select ‘drop public synonym ‘ || synonym_name || ‘;’ from dba_synonyms

where owner=’PUBLIC’ and table_owner=’OLAPSYS’; Now run all the commands from the output of above query.

SQL> select count(1) from dba_objects where status=’INVALID’;

Count should be 0

Reccomendation : No event should be there

SQL> create pfile from spfile;

Edit init.ora file (remove the *event=” parameter) SQL> shu immediate

Delete spfile

SQL> startup

SQL>create spfile fom pfile;

Now re-run the preupgrade_fixups.sql

SQL> location/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: 2020-11-07 22:23:20

For Source Database: ORADEV Source Database Version: 12

For Upgrade to Version: 19.0.0.0.0

Preup Preupgrade

Action Issue Is

Number Preupgrade Check Name Remedied Further DBA Action

parameter_min_val YES NONE
em_present YES NONE
amd_exists YES NONE
apex_manual_upgrade YES NONE
dictionary_stats YES NONE
trgowner_no_admndbtrg YES NONE
pre_fixed_objects YES NONE
tablespaces_info NO Informational Further action is optional.
exf_rul_exists NO Informational Further action is optional.
rman_recovery_version NO Informational 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 an 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 comp

SQL>