Table of Contents
- 1. Installing Oracle 19c.
- 2. Executing the pre-upgrade jar tool
- 3. Performing the pre-upgrade actions
- Recommendation 1: parameter_min_val
- Recommendation 2: Remove the EM repositor
- Recommendation 3: Remove OLTP catalog
- Recommendation 4: Upgrade Oracle Application Express (APEX) manually.
- Recommendation: Check invalid objects
- Reccomendation : No event should be there
- 4. Upgrading the database
- 5. Performing the post-upgrade actions
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
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’;
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>
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’;