YourInfoBucket
  • Home
  • Business
  • Technology
  • Health
  • Fashion
  • Travel
  • More
    • Sports
    • Education
    • Entertainment
    • Food & Nutrition
No Result
View All Result
Write For Us
  • Home
  • Business
  • Technology
  • Health
  • Fashion
  • Travel
  • More
    • Sports
    • Education
    • Entertainment
    • Food & Nutrition
No Result
View All Result
Write For Us
No Result
View All Result

Oracle Database Upgradation to 19c Using DBCA

by Simran
March 26, 2021
in Education
Reading Time: 6 mins read
0
Oracle database Upgrade
25
SHARES
132
VIEWS

Table of Contents

  • 1.  Installing Oracle 19c.
  • 2.  Executing the pre-upgrade jar tool
    • Check Loaction/preupgrade.log for fixing the issues.
      •  Before upgrade:
  • 3.  Performing the pre-upgrade actions
  • Recommendation 1: parameter_min_val
    • Action: Increase the process parameter
  • Recommendation 2: Remove the EM repositor
  • Recommendation 3: Remove OLTP catalog
  • Recommendation 4: Upgrade Oracle Application Express (APEX) manually.
    • Note: No Actions required from Recommendation 5, 6 and 7
  • Recommendation: Check invalid objects
    • (If object_name are related to APEX… then follow below steps)
    • (for OLAPSYS invalid objects then follow below steps)
  • Reccomendation : No event should be there
    • Now re-run the preupgrade_fixups.sql
  • 4.  Upgrading the database
  • 5.  Performing the post-upgrade actions
    •  After the upgrade:

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

12c to 19c 1 12c to 19c 2 12c to 19c 3 12c to 19c 4 12c to 19c 5 12c to 19c 6 12c to 19c 7 12c to 19c 8 12c to 19c 9

 

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

Tags: 19coraclesqlupgrade

Related Posts

Programming Language
Education

Which Programming Languages Should You Learn in 2022?

by Simran
September 30, 2022
DevOps course
Education

What Career Opportunities will be there After Completing the DevOps Course?

by Simran
December 9, 2022
How Image to Text Converter Technology Can Help Students in Assignment Writing
Education

How Image to Text Converter Technology Can Help Students in Assignment Writing

by Simran
August 16, 2022
school curriculum
Education

Dennis Bonnen Shares 5 Reasons Why Schools Should Have Flexibility In Creating Their Curriculums

by Simran
August 25, 2022
Repair a Corrupt MS SQL Database
Education

Want to Repair a Corrupt MS SQL Database? Here is the Easy Way!

by Simran
August 2, 2022

About Us

Yourinfobucket is a platform where you can keep yourself updated with the latest happenings in the field of Business, Technology, Food, Health, Entertainment and Fashion. The purpose of YourInfoBucket is to helping the reader to expand their knowledge globally and sharing their experiences through it.

Categories

  • Business
  • Education
  • Entertainment
  • Fashion
  • Food $ Nutrition
  • Health
  • Menu Price
  • Sports
  • Technology
  • Travel

Important Page

  • About Us
  • Write For Us
  • Contact Us
  • Privacy Policy
  • Terms & Conditions
  • Disclaimer
  • Sitemap
  • HTML Sitemap

Recent Posts

  • Understanding Employee Compensation Packages
  • What You Need to Know About Living in Knoxville
  • How to Establish an Account-Based Strategy

© 2022 YourInfoBucket.com

No Result
View All Result
  • Home
  • Business
  • Technology
  • Health
  • Fashion
  • Travel
  • Sports
  • Education
  • Entertainment
  • Food & Nutrition
Write For Us

© 2022 YourInfoBucket.com