Oracle Database Upgradation to 19c Using DBCA

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

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

1.              parameter_min_val                          NO                              Manual fixup
2.              em_present                                      NO                              Manual fixup
3.              amd_exists                                      NO                               Manual fixup
4.              apex_manual_upgrade                   NO                               Manual fixup
5.              dictionary_stats                              YES                                   NONE
6.              trgowner_no_admndbtrg                YES                                   NONE
7.             pre_fixed_objects                            YES                                   NONE
8.             tablespaces_info                              NO                             Informational only Further action is optional.
9.             exf_rul_exists                                   NO                             Informational only Further action is optional.
10.             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

1. parameter_min_val     NO     Manual fixup recommended.
2. em_present       NO     Manual fixup
3.  amd_exists      NO      Manual fixup
4. apex_manual_upgrade       NO          Manual fixup recommended

Recommendation 1: parameter_min_val

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

Oracle Enterprise Manager 12c Database Control Release 12.0.0.0 Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved. Stopping Oracle Enterprise Manager 11g Database Control …

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

<output truncated>

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

1.              parameter_min_val                 YES                   NONE
2.              em_present                             YES                   NONE
3.              amd_exists                              YES                  NONE
4.              apex_manual_upgrade           YES                  NONE
5.              dictionary_stats                       YES                  NONE
6.              trgowner_no_admndbtrg         YES                  NONE
7.              pre_fixed_objects                    YES                  NONE
8.              tablespaces_info                       NO          Informational Further action is optional.
9.              exf_rul_exists                            NO          Informational Further action is optional.
10.              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>

4.  Upgrading the database

Start the database from 19c ORACLE_HOME and start the upgrade.

$ set ORACLE_HOME=oracle 19c home location

$ set PATH=oracle 19c home location/bin

$ dbua

set old ORACLE_HOME and PATH

$netca

Delete listener

Set 19c ORACLE_HOME and PATH

$netca

Create new listener

5.  Performing the post-upgrade actions

 After the upgrade:

 Log into the database and execute the postupgrade fixups CONNECT TO SQLPLUS

SQL> @Loaction/postupgrade_fixups.sql

SQL> select name, open_mode, version from v$database, v$instance;

NAME       OPEN_MODE             VERSION

ORADEV   READ WRITE            19.0.0.0.0

SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

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’;