Oracle Database Commands and Practical Examples: May 2022

Upgrade 11.2.0.4.0 to 19.3.0.0.0

Operating system : Red Hat Enterprise Linux Server release 7.8 (OEL)

Primary database name: oradb11g

Copy listener.ora , tnsnames.ora to 19c network/admin location.

Copy 12c spfile , pfile and password to new 19c dbs home location.

Run preupgrade.jar and fix the requirements

preupgrade.log

preupgrade_fixups.sql

postupgrade_fixups.sql

Create restore point before upgrade or take rman consistent backup

Set oracle sid , home and start instance in upgrade mode

Run dbupgrade at os level

run utlusts.sql any time before or after you complete the upgrade

Run utlrp.sql after upgrad for compling invalid objects

Upgrade timezone from 14 to 32 run script below:

NOTE : Disbale block change tracking before upgrade

NOTE : Check aud$ table details should be in system tablespace

/u01/app/oracle/product/19.3/db_home/rdbms/admin/utltz_countstats.sql

/u01/app/oracle/product/19.3/db_home/rdbms/admin/utltz_countstar.sql

/u01/app/oracle/product/19.3/db_home/rdbms/admin/utltz_upg_check.sql

/u01/app/oracle/product/19.3/db_home/rdbms/admin/utltz_upg_apply.sql

Delete restore point created before upgrade

change the compatible parameter 12c to 19c

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

STEP 1 : Run preupgrade.jar for pre - upgrade stage

[oracle@srv7 ~]$ /u02/app/oracle/product/11.2.0.4/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.3/db_home/rdbms/admin/preupgrade.jar DIR /u02/preupgrade/

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

PREUPGRADE SUMMARY

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

/u02/preupgrade/preupgrade.log

/u02/preupgrade/preupgrade_fixups.sql

/u02/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade:

Log into the database and execute the preupgrade fixups

@/u02/preupgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups

@/u02/preupgrade/postupgrade_fixups.sql

Preupgrade complete: 2022-05-10T12:04:48

[oracle@srv7 ~]$ pwd

/home/oracle

[oracle@srv7 ~]$ cd /u02/app/oracle/

[oracle@srv7 oracle]$ cd product/11.2.0.4/dbhome_1/

STEP 2 : Check status of patch status of 11g database before upgrade

[oracle@srv7 OPatch]$ ./opatch lsinventory

Oracle Interim Patch Installer version 11.2.0.3.4

Oracle Home : /u02/app/oracle/product/11.2.0.4/dbhome_1

Central Inventory : /u01/app/oraInventory

from : /u02/app/oracle/product/11.2.0.4/dbhome_1/oraInst.loc

OPatch version : 11.2.0.3.4

OUI version : 11.2.0.4.0

Log file location : /u02/app/oracle/product/11.2.0.4/dbhome_1/cfgtoollogs/opatch/opatch2022-05-10_12-10-52PM_1.log

Lsinventory Output file location : /u02/app/oracle/product/11.2.0.4/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2022-05-10_12-10-52PM.txt

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

Installed Top-level Products (1):

Oracle Database 11g 11.2.0.4.0

There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

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

OPatch succeeded.

STEP 3 : Preupgrade sql script information preupgrade_fixups.sql

[oracle@srv7 OPatch]$ cd /u02/preupgrade/

[oracle@srv7 preupgrade]$ ll

total 692

-rw-r--r-- 1 oracle oinstall 1 May 10 12:04 checksBuffer.tmp

-rw-r--r-- 1 oracle oinstall 41134 May 10 12:04 components.properties

-rw-r--r-- 1 oracle oinstall 15085 May 10 12:04 dbms_registry_extended.sql

drwxr-xr-x 3 oracle oinstall 4096 May 10 12:04 oracle

-rw-r--r-- 1 oracle oinstall 14016 May 10 12:04 parameters.properties

-rw-r--r-- 1 oracle oinstall 10940 May 10 12:04 postupgrade_fixups.sql

-rw-r--r-- 1 oracle oinstall 7884 May 10 12:04 preupgrade_driver.sql

-rw-r--r-- 1 oracle oinstall 16781 May 10 12:04 preupgrade_fixups.sql

-rw-r--r-- 1 oracle oinstall 13571 May 10 12:04 preupgrade.log

-rw-r--r-- 1 oracle oinstall 100166 May 10 12:04 preupgrade_messages.properties

-rw-r--r-- 1 oracle oinstall 455876 May 10 12:04 preupgrade_package.sql

drwxr-xr-x 3 oracle oinstall 4096 May 10 12:04 upgrade

[oracle@srv7 preupgrade]$ more preupgrade_fixups.sql

REM

REM Oracle PRE-Upgrade Fixup Script

REM

REM Auto-Generated by: Oracle Preupgrade Script

REM Version: 19.0.0.0.0 Build: 1

REM Generated on: 2022-05-10 12:04:45

REM

REM Source Database: ORADB11G

REM Source Database Version: 11.2.0.4.0

REM For Upgrade to Version: 19.0.0.0.0

REM

REM Setup Environment

REM

SET ECHO OFF SERVEROUTPUT ON FORMAT WRAPPED TAB OFF LINESIZE 200;

DECLARE

db_name V$DATABASE.NAME%TYPE;

con_name VARCHAR2(128);

fixup_result BOOLEAN := TRUE;

recyclebin_cleaned BOOLEAN := TRUE;

check_result_xml VARCHAR2(32767);

BEGIN

-- Gather the current execution context

EXECUTE IMMEDIATE

'SELECT name FROM v$database' INTO db_name;

EXECUTE IMMEDIATE

'SELECT dbms_preup.get_con_name FROM sys.dual' INTO con_name;

-- Now validate that the current execution context

-- matches the context when this script was generated.

IF db_name <> 'ORADB11G' THEN

dbms_output.put_line('WARNING - This script was generated ' ||

'for database ORADB11G.');

END IF; -- if db_name is ORADB11G

dbms_output.put_line('Executing Oracle PRE-Upgrade Fixup Script');

dbms_output.put_line(' ');

dbms_output.put_line('Auto-Generated by: Oracle Preupgrade Script');

dbms_output.put_line(' Version: 19.0.0.0.0 Build: 1');

dbms_output.put_line('Generated on: 2022-05-10 12:04:45 ');

dbms_output.put_line(' ');

dbms_output.put_line('For Source Database: ORADB11G');

dbms_output.put_line('Source Database Version: 11.2.0.4.0');

dbms_output.put_line('For Upgrade to Version: 19.0.0.0.0');

dbms_output.put_line(' ');

-- Starting DB ORADB11G

dbms_output.put_line('Preup Preupgrade ');

dbms_output.put_line('Action Issue Is ');

dbms_output.put_line('Number Preupgrade Check Name Remedied Further DBA Action');

dbms_output.put_line('------ ------------------------ ---------- --------------------------------');

-- CHECK/FIXUP name: em_present

-- The call to run_fixup below will test whether

-- the following issue originally identified by

-- the preupgrade tool is still present

-- and if so, it will attempt to perform the action

-- necessary to resolve it.

-- ORIGINAL PREUPGRADE ISSUE:

-- 1. Remove the EM repository.

-- - Copy the $ORACLE_HOME/rdbms/admin/emremove.sql script from the target

-- 19 ORACLE_HOME into the source 11.2.0.4.0 ORACLE_HOME.

-- Step 1: If database control is configured, stop EM Database Control,

-- using the following command

-- $> emctl stop dbconsole

-- Step 2: Connect to the database using the SYS account AS SYSDBA

-- SET ECHO ON;

-- SET SERVEROUTPUT ON;

-- @emremove.sql

-- Without the set echo and serveroutput commands, you will not be able to

-- follow the progress of the script.

-- The database has an Enterprise Manager Database Control repository.

-- Starting with Oracle Database 12c, the local Enterprise Manager Database

-- Control does not exist anymore. The repository will be removed from your

-- database during the upgrade. This step can be manually performed before

-- the upgrade to reduce downtime.

fixup_result := dbms_preup.run_fixup('em_present',1) AND fixup_result;

-- CHECK/FIXUP name: amd_exists

-- The call to run_fixup below will test whether

-- the following issue originally identified by

-- the preupgrade tool is still present

-- and if so, it will attempt to perform the action

-- necessary to resolve it.

-- ORIGINAL PREUPGRADE ISSUE:

-- 2. Remove OLAP Catalog by running the 11.2.0.4.0 SQL script

-- $ORACLE_HOME/olap/admin/catnoamd.sql script.

-- The OLAP Catalog component, AMD, exists in the database.

-- Starting with Oracle Database 12c, the OLAP Catalog (OLAP AMD) is

-- desupported and will be automatically marked as OPTION OFF during the

-- database upgrade if present. Oracle recommends removing OLAP Catalog

-- (OLAP AMD) before database upgrade. This step can be manually performed

-- before the upgrade to reduce downtime.

fixup_result := dbms_preup.run_fixup('amd_exists',2) AND fixup_result;

-- CHECK/FIXUP name: apex_manual_upgrade

-- The call to run_fixup below will test whether

-- the following issue originally identified by

-- the preupgrade tool is still present

-- and if so, it will attempt to perform the action

-- necessary to resolve it.

-- ORIGINAL PREUPGRADE ISSUE:

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

-- upgrade.

-- The database contains APEX version 3.2.1.00.12. 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.

fixup_result := dbms_preup.run_fixup('apex_manual_upgrade',3) AND fixup_result;

-- CHECK/FIXUP name: mv_refresh

-- The call to run_fixup below will test whether

-- the following issue originally identified by

-- the preupgrade tool is still present

-- and if so, it will attempt to perform the action

-- necessary to resolve it.

-- ORIGINAL PREUPGRADE ISSUE:

-- 4. Please make sure that all the MVs are refreshed and sys.sumdelta$ becomes

-- empty before doing upgrade, unless you have strong business reasons not

-- to do so. You can use dbms_mview.refresh() to refresh the MVs except

-- those stale ones to be kept due to business need. If there are any stale

-- MVs depending on changes in sys.sumdelta$, do not truncate it, because

-- doing so will cause wrong results after refresh.

-- There is one or more non-fresh MV in the database or sumdelta$ is not

-- empty.

-- Oracle recommends that all materialized views (MV's) are refreshed before

-- upgrading the database because this will clear the MV logs and the

-- sumdelta$ table, and make the UPGRADE process faster. If you choose to

-- not refresh some MVs, the change data for those MV's will be carried

-- through the UPGRADE process. After UPGRADE, you can refresh the MV's and

-- MV incremental refresh should work in normal cases.

fixup_result := dbms_preup.run_fixup('mv_refresh',4) AND fixup_result;

-- CHECK/FIXUP name: dictionary_stats

-- The call to run_fixup below will test whether

-- the following issue originally identified by

-- the preupgrade tool is still present

-- and if so, it will attempt to perform the action

-- necessary to resolve it.

-- ORIGINAL PREUPGRADE ISSUE:

-- 5. (AUTOFIXUP) Gather stale data dictionary statistics prior to database

-- upgrade in off-peak time using:

-- EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

-- Dictionary statistics do not exist or are stale (not up-to-date).

-- Dictionary statistics help the Oracle optimizer find efficient SQL

-- execution plans and are essential for proper upgrade timing. Oracle

-- recommends gathering dictionary statistics in the last 24 hours before

-- database upgrade.

-- For information on managing optimizer statistics, refer to the 11.2.0.4

-- Oracle Database Performance Tuning Guide.

fixup_result := dbms_preup.run_fixup('dictionary_stats',5) AND fixup_result;

-- CHECK/FIXUP name: trgowner_no_admndbtrg

-- The call to run_fixup below will test whether

-- the following issue originally identified by

-- the preupgrade tool is still present

-- and if so, it will attempt to perform the action

-- necessary to resolve it.

-- ORIGINAL PREUPGRADE ISSUE:

-- 6. (AUTOFIXUP) Directly grant ADMINISTER DATABASE TRIGGER privilege to the

-- owner of the trigger or drop and re-create the trigger with a user that

-- was granted directly with such. You can list those triggers using: SELECT

-- OWNER, TRIGGER_NAME FROM DBA_TRIGGERS WHERE

-- TRIM(BASE_OBJECT_TYPE)='DATABASE' AND OWNER NOT IN (SELECT GRANTEE FROM

-- DBA_SYS_PRIVS WHERE PRIVILEGE='ADMINISTER DATABASE TRIGGER').

-- There is one or more database triggers whose owner does not have the

-- right privilege on the database.

-- The creation of database triggers must be done by users granted with

-- ADMINISTER DATABASE TRIGGER privilege. Privilege must have been granted

-- directly.

fixup_result := dbms_preup.run_fixup('trgowner_no_admndbtrg',6) AND fixup_result;

-- CHECK/FIXUP name: mv_refresh

-- The call to run_fixup below will test whether

-- the following issue originally identified by

-- the preupgrade tool is still present

-- and if so, it will attempt to perform the action

-- necessary to resolve it.

-- ORIGINAL PREUPGRADE ISSUE:

-- 7. Please make sure that all the MVs are refreshed and sys.sumdelta$ becomes

-- empty before doing upgrade, unless you have strong business reasons not

-- to do so. You can use dbms_mview.refresh() to refresh the MVs except

-- those stale ones to be kept due to business need. If there are any stale

-- MVs depending on changes in sys.sumdelta$, do not truncate it, because

-- doing so will cause wrong results after refresh.

-- There is one or more non-fresh MV in the database or sumdelta$ is not

-- empty.

-- Oracle recommends that all materialized views (MV's) are refreshed before

-- upgrading the database because this will clear the MV logs and the

-- sumdelta$ table, and make the UPGRADE process faster. If you choose to

-- not refresh some MVs, the change data for those MV's will be carried

-- through the UPGRADE process. After UPGRADE, you can refresh the MV's and

-- MV incremental refresh should work in normal cases.

fixup_result := dbms_preup.run_fixup('mv_refresh',7) AND fixup_result;

-- CHECK/FIXUP name: pre_fixed_objects

-- The call to run_fixup below will test whether

-- the following issue originally identified by

-- the preupgrade tool is still present

-- and if so, it will attempt to perform the action

-- necessary to resolve it.

-- ORIGINAL PREUPGRADE ISSUE:

-- 8. (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade.

-- None of the fixed object tables have had stats collected.

-- Gathering statistics on fixed objects, if none have been gathered yet, is

-- recommended prior to upgrading.

-- For information on managing optimizer statistics, refer to the 11.2.0.4

-- Oracle Database Performance Tuning Guide.

fixup_result := dbms_preup.run_fixup('pre_fixed_objects',8) AND fixup_result;

-- CHECK/FIXUP name: tablespaces_info

-- The call to run_fixup below will test whether

-- the following issue originally identified by

-- the preupgrade tool is still present

-- and if so, it will attempt to perform the action

-- necessary to resolve it.

-- ORIGINAL PREUPGRADE ISSUE:

-- 9. 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 520 MB 751 MB

-- SYSTEM 750 MB 1178 MB

-- TEMP 29 MB 150 MB

-- UNDOTBS1 110 MB 446 MB

-- Minimum tablespace sizes for upgrade are estimates.

fixup_result := dbms_preup.run_fixup('tablespaces_info',9) AND fixup_result;

-- CHECK/FIXUP name: exf_rul_exists

-- The call to run_fixup below will test whether

-- the following issue originally identified by

-- the preupgrade tool is still present

-- and if so, it will attempt to perform the action

-- necessary to resolve it.

-- ORIGINAL PREUPGRADE ISSUE:

-- 10. Run $ORACLE_HOME/rdbms/admin/catnoexf.sql located in the new Oracle

-- Database Oracle home to remove both EXF and RUL.

-- Expression Filter (EXF) or Rules Manager (RUL) exist in the database.

-- Starting with Oracle Database release 12.1, the Expression Filter (EXF)

-- and Database Rules Manager (RUL) features are desupported, and are

-- removed during the upgrade process. This step can be manually performed

-- before the upgrade to reduce downtime.

fixup_result := dbms_preup.run_fixup('exf_rul_exists',10) AND fixup_result;

-- CHECK/FIXUP name: rman_recovery_version

-- The call to run_fixup below will test whether

-- the following issue originally identified by

-- the preupgrade tool is still present

-- and if so, it will attempt to perform the action

-- necessary to resolve it.

-- ORIGINAL PREUPGRADE ISSUE:

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

fixup_result := dbms_preup.run_fixup('rman_recovery_version',11) AND fixup_result;

-- clean recyclebin in case any of the above fixups left stuff there.

recyclebin_cleaned := dbms_preup.run_fixup_only('purge_recyclebin', check_result_xml);

IF fixup_result = FALSE THEN

dbms_output.put_line('');

dbms_output.put_line('The fixup scripts have been run and resolved what they can. However,');

dbms_output.put_line('there are still issues originally identified by the preupgrade that');

dbms_output.put_line('have not been remedied and are still present in the database.');

dbms_output.put_line('Depending on the severity of the specific issue, and the nature of');

dbms_output.put_line('the issue itself, that could mean that your database is not ready');

dbms_output.put_line('for upgrade. To resolve the outstanding issues, start by reviewing');

dbms_output.put_line('the preupgrade_fixups.sql and searching it for the name of');

dbms_output.put_line('the failed CHECK NAME or Preupgrade Action Number listed above.');

dbms_output.put_line('There you will find the original corresponding diagnostic message');

dbms_output.put_line('from the preupgrade which explains in more detail what still needs');

dbms_output.put_line('to be done.');

END IF;

END;

[oracle@srv7 preupgrade]$

STEP 4 : Run preupgrade_fixups.sql script and full fill the requirement before upgrade

SQL> @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: 2022-05-10 12:04:45

For Source Database: ORADB11G

Source Database Version: 11.2.0.4.0

For Upgrade to Version: 19.0.0.0.0

Preup Preupgrade

Action Issue Is

Number Preupgrade Check Name Remedied Further DBA Action

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

1. em_present NO Manual fixup recommended.

2. amd_exists NO Manual fixup recommended.

3. apex_manual_upgrade NO Manual fixup recommended.

4. mv_refresh NO Informational only.

Further action is optional.

5. dictionary_stats YES None.

6. trgowner_no_admndbtrg YES None.

7. mv_refresh NO Informational only.

Further action is optional.

8. pre_fixed_objects YES None.

9. tablespaces_info NO Informational only.

Further action is optional.

10. exf_rul_exists NO Informational only.

Further action is optional.

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

the 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 5 : Dictionary stats gather at database

SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

STEP 6 : Increase db_recovery_file_dest_size as per requirement

SQL> alter system set db_recovery_file_dest_size=15G scope=both;

System altered.

STEP 7 : Check systam (minimum 2g free ) and sysaux (minimum 1g free ) tablespace have sufficient space and increase accordingly

SQL> @/home/oracle/tablespace

NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME

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

ORADB11G READ WRITE PRIMARY oradb11g

Tablespace Allocated Current Used Available %Used (vs)

Name Size(GB) Size(GB) Size(GB) Size(GB) (Allocated)

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

EXAMPLE 32.00 .34 .30 31.70 .94

SYSAUX 32.00 .51 .48 31.52 1.50

SYSTEM 32.00 .74 .73 31.27 2.28

UNDOTBS1 32.00 .11 .11 31.89 .33

USERS 32.00 .00 .00 32.00 .01

SQL> select name from v$datafile;

NAME

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

/u01/app/oracle/oradata/oradb11g/system01.dbf

/u01/app/oracle/oradata/oradb11g/sysaux01.dbf

/u01/app/oracle/oradata/oradb11g/undotbs01.dbf

/u01/app/oracle/oradata/oradb11g/users01.dbf

/u01/app/oracle/oradata/oradb11g/example01.dbf

SQL> alter database datafile '/u01/app/oracle/oradata/oradb11g/system01.dbf' resize 2g;

Database altered.

SQL> alter database datafile '/u01/app/oracle/oradata/oradb11g/sysaux01.dbf' resize 2g;

Database altered.

SQL> alter database datafile '/u01/app/oracle/oradata/oradb11g/undotbs01.dbf' resize 2g;

Database altered.

SQL> @/home/oracle/tablespace

NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME

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

ORADB11G READ WRITE PRIMARY oradb11g

Tablespace Allocated Current Used Available %Used (vs)

Name Size(GB) Size(GB) Size(GB) Size(GB) (Allocated)

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

EXAMPLE 32.00 .34 .30 31.70 .94

SYSAUX 32.00 2.00 .48 31.52 1.50

SYSTEM 32.00 2.00 .73 31.27 2.28

UNDOTBS1 32.00 2.00 .11 31.89 .33

USERS 32.00 .00 .00 32.00 .01

STEP 8 : Purge recyclebin on database

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

STEP 9 : Check current timezone version of 11g database

SQL> select version from v$timezone_file;

VERSION

----------

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

ORDER BY PROPERTY_NAME;

2 3 4

PROPERTY_NAME VALUE

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

DST_PRIMARY_TT_VERSION 14

DST_SECONDARY_TT_VERSION 0

DST_UPGRADE_STATE NONE

SQL>

STEP 10 : Run RMAN backup before upgrade , We can use in case of rollback

run

{

allocate channel ch1 type disk;

backup incremental level 0 tag 'BEFORE_UPGRADE' database format '/u02/rmanbkp/oradb11g/%d_%U';

backup tag 'CONTROL_FILE' current controlfile format '/u02/rmanbkp/oradb11g/%d_%T_%s_%p_CONTROL';

release channel ch1;

}

[oracle@srv7 preupgrade]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue May 10 12:38:07 2022

connected to target database: ORADB11G (DBID=3532244430)

RMAN> run

{

allocate channel ch1 type disk;

backup incremental level 0 tag 'BEFORE_UPGRADE' database format '/u02/rmanbkp/oradb11g/%d_%U';

backup tag 'CONTROL_FILE' current controlfile format '/u02/rmanbkp/oradb11g/%d_%T_%s_%p_CONTROL';

release channel ch1;

}2> 3> 4> 5> 6> 7>

using target database control file instead of recovery catalog

allocated channel: ch1

channel ch1: SID=48 device type=DISK

Starting backup at 2022-05-10:12:39:42

channel ch1: starting incremental level 0 datafile backup set

channel ch1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/oradb11g/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/oradb11g/sysaux01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/oradb11g/undotbs01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/oradb11g/example01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/oradb11g/users01.dbf

channel ch1: starting piece 1 at 2022-05-10:12:39:42

channel ch1: finished piece 1 at 2022-05-10:12:40:27

piece handle=/u02/rmanbkp/oradb11g/ORADB11G_010t59ce_1_1 tag=BEFORE_UPGRADE comment=NONE

channel ch1: backup set complete, elapsed time: 00:00:45

channel ch1: starting incremental level 0 datafile backup set

channel ch1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ch1: starting piece 1 at 2022-05-10:12:40:28

channel ch1: finished piece 1 at 2022-05-10:12:40:29

piece handle=/u02/rmanbkp/oradb11g/ORADB11G_020t59dr_1_1 tag=BEFORE_UPGRADE comment=NONE

channel ch1: backup set complete, elapsed time: 00:00:01

Finished backup at 2022-05-10:12:40:29

Starting backup at 2022-05-10:12:40:30

channel ch1: starting full datafile backup set

channel ch1: specifying datafile(s) in backup set

including current control file in backup set

channel ch1: starting piece 1 at 2022-05-10:12:40:31

channel ch1: finished piece 1 at 2022-05-10:12:40:32

piece handle=/u02/rmanbkp/oradb11g/ORADB11G_20220510_3_1_CONTROL tag=CONTROL_FILE comment=NONE

channel ch1: backup set complete, elapsed time: 00:00:01

Finished backup at 2022-05-10:12:40:32

released channel: ch1

RMAN>

[oracle@srv7 dbs]$ cp initoradb11g.ora spfileoradb11g.ora orapworadb11g /u01/app/oracle/product/19.3/db_home/dbs/

STEP 11 : Shutdown 11g database and listener before upgrade

[oracle@srv7 preupgrade]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue May 10 12:44:44 2022

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing optionsS

SQL> shu immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

STEP 12 : Set new Oracle Home for 19c database and startup database with upgrade mode

[oracle@srv7 dbs]$ export ORACLE_SID=oradb11g

[oracle@srv7 dbs]$ export ORACLE_HOME=/u01/app/oracle/product/19.3/db_home

[oracle@srv7 dbs]$ export PATH=/u01/app/oracle/product/19.3/db_home/dbs:$PATH

[oracle@srv7 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue May 10 12:49:09 2022

Connected to an idle instance.

SQL> startup upgrade

ORACLE instance started.

Total System Global Area 3154113400 bytes

Fixed Size 8901496 bytes

Variable Size 637534208 bytes

Database Buffers 2499805184 bytes

Redo Buffers 7872512 bytes

Database mounted.

Database opened.

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

[oracle@srv7 dbs]$ cd /u01/app/oracle/product/19.3/db_home/bin

[oracle@srv7 bin]$ export ORACLE_SID=oradb11g

[oracle@srv7 bin]$ ls -lrt dbupgrade

-rwxr-x---. 1 oracle oinstall 3136 Apr 17 2019 dbupgrade

STEP 13 : Use Utility dbupgrade or use catctl.pl fro upgrading components

[oracle@srv7 bin]$ ./dbupgrade

Argument list for [/u01/app/oracle/product/19.3/db_home/rdbms/admin/catctl.pl]

For Oracle internal use only A = 0

Run in c = 0

Do not run in C = 0

Input Directory d = 0

Echo OFF e = 1

Simulate E = 0

Forced cleanup F = 0

Log Id i = 0

Child Process I = 0

Log Dir l = 0

Priority List Name L = 0

Upgrade Mode active M = 0

SQL Process Count n = 0

SQL PDB Process Count N = 0

Open Mode Normal o = 0

Start Phase p = 0

End Phase P = 0

Reverse Order r = 0

AutoUpgrade Resume R = 0

Script s = 0

Serial Run S = 0

RO User Tablespaces T = 0

Display Phases y = 0

Debug catcon.pm z = 0

Debug catctl.pl Z = 0

catctl.pl VERSION: [19.0.0.0.0]

STATUS: [Production]

BUILD: [RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417]

/u01/app/oracle/product/19.3/db_home/rdbms/admin/orahome = [/u01/app/oracle/product/19.3/db_home]

/u01/app/oracle/product/19.3/db_home/bin/orabasehome = [/u01/app/oracle/product/19.3/db_home]

catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.3/db_home]

Analyzing file /u01/app/oracle/product/19.3/db_home/rdbms/admin/catupgrd.sql

Log file directory = [/tmp/cfgtoollogs/upgrade20220510125226]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20220510125226/catupgrd_catcon_9800.lst]

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20220510125226/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20220510125226/catupgrd_*.lst] files for spool files, if any

Number of Cpus = 1

Database Name = oradb11g

DataBase Version = 11.2.0.4.0

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19.3/db_home/cfgtoollogs/oradb11g/upgrade20220510125237/catupgrd_catcon_9800.lst]

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.3/db_home/cfgtoollogs/oradb11g/upgrade20220510125237/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.3/db_home/cfgtoollogs/oradb11g/upgrade20220510125237/catupgrd_*.lst] files for spool files, if any

Log file directory = [/u01/app/oracle/product/19.3/db_home/cfgtoollogs/oradb11g/upgrade20220510125237]

Parallel SQL Process Count = 4

Components in [oradb11g]

Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT EM JAVAVM ORDIM OWM SDO XDB XML XOQ]

Not Installed [DV MGW ODM OLS RAC WK]

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

Phases [0-107] Start Time:[2022_05_10 12:52:48]

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

*********** Executing Change Scripts ***********

Serial Phase #:0 [oradb11g] Files:1 Time: 70s

*************** Catalog Core SQL ***************

Serial Phase #:1 [oradb11g] Files:5 Time: 44s

Restart Phase #:2 [oradb11g] Files:1 Time: 1s

*********** Catalog Tables and Views ***********

Parallel Phase #:3 [oradb11g] Files:19 Time: 18s

Restart Phase #:4 [oradb11g] Files:1 Time: 3s

************* Catalog Final Scripts ************

Serial Phase #:5 [oradb11g] Files:7 Time: 15s

***************** Catproc Start ****************

Serial Phase #:6 [oradb11g] Files:1 Time: 12s

***************** Catproc Types ****************

Serial Phase #:7 [oradb11g] Files:2 Time: 9s

Restart Phase #:8 [oradb11g] Files:1 Time: 1s

**************** Catproc Tables ****************

Parallel Phase #:9 [oradb11g] Files:67 Time: 29s

Restart Phase #:10 [oradb11g] Files:1 Time: 0s

************* Catproc Package Specs ************

Serial Phase #:11 [oradb11g] Files:1 Time: 54s

Restart Phase #:12 [oradb11g] Files:1 Time: 2s

************** Catproc Procedures **************

Parallel Phase #:13 [oradb11g] Files:94 Time: 11s

Restart Phase #:14 [oradb11g] Files:1 Time: 1s

Parallel Phase #:15 [oradb11g] Files:120 Time: 17s

Restart Phase #:16 [oradb11g] Files:1 Time: 1s

Serial Phase #:17 [oradb11g] Files:22 Time: 4s

Restart Phase #:18 [oradb11g] Files:1 Time: 1s

***************** Catproc Views ****************

Parallel Phase #:19 [oradb11g] Files:32 Time: 25s

Restart Phase #:20 [oradb11g] Files:1 Time: 2s

Serial Phase #:21 [oradb11g] Files:3 Time: 10s

Restart Phase #:22 [oradb11g] Files:1 Time: 1s

Parallel Phase #:23 [oradb11g] Files:25 Time: 119s

Restart Phase #:24 [oradb11g] Files:1 Time: 2s

Parallel Phase #:25 [oradb11g] Files:12 Time: 67s

Restart Phase #:26 [oradb11g] Files:1 Time: 2s

Serial Phase #:27 [oradb11g] Files:1 Time: 0s

Serial Phase #:28 [oradb11g] Files:3 Time: 4s

Serial Phase #:29 [oradb11g] Files:1 Time: 0s

Restart Phase #:30 [oradb11g] Files:1 Time: 2s

*************** Catproc CDB Views **************

Serial Phase #:31 [oradb11g] Files:1 Time: 3s

Restart Phase #:32 [oradb11g] Files:1 Time: 2s

Serial Phase #:34 [oradb11g] Files:1 Time: 0s

***************** Catproc PLBs *****************

Serial Phase #:35 [oradb11g] Files:293 Time: 20s

Serial Phase #:36 [oradb11g] Files:1 Time: 0s

Restart Phase #:37 [oradb11g] Files:1 Time: 1s

Serial Phase #:38 [oradb11g] Files:6 Time: 5s

Restart Phase #:39 [oradb11g] Files:1 Time: 1s

*************** Catproc DataPump ***************

Serial Phase #:40 [oradb11g] Files:3 Time: 36s

Restart Phase #:41 [oradb11g] Files:1 Time: 1s

****************** Catproc SQL *****************

Parallel Phase #:42 [oradb11g] Files:13 Time: 77s

Restart Phase #:43 [oradb11g] Files:1 Time: 1s

Parallel Phase #:44 [oradb11g] Files:11 Time: 12s

Restart Phase #:45 [oradb11g] Files:1 Time: 1s

Parallel Phase #:46 [oradb11g] Files:3 Time: 3s

Restart Phase #:47 [oradb11g] Files:1 Time: 0s

************* Final Catproc scripts ************

Serial Phase #:48 [oradb11g] Files:1 Time: 8s

Restart Phase #:49 [oradb11g] Files:1 Time: 0s

************** Final RDBMS scripts *************

Serial Phase #:50 [oradb11g] Files:1 Time: 17s

************ Upgrade Component Start ***********

Serial Phase #:51 [oradb11g] Files:1 Time: 2s

Restart Phase #:52 [oradb11g] Files:1 Time: 1s

********** Upgrading Java and non-Java *********

Serial Phase #:53 [oradb11g] Files:2 Time: 373s

***************** Upgrading XDB ****************

Restart Phase #:54 [oradb11g] Files:1 Time: 2s

Serial Phase #:56 [oradb11g] Files:3 Time: 24s

Serial Phase #:57 [oradb11g] Files:3 Time: 6s

Parallel Phase #:58 [oradb11g] Files:10 Time: 5s

Parallel Phase #:59 [oradb11g] Files:25 Time: 8s

Serial Phase #:60 [oradb11g] Files:4 Time: 8s

Serial Phase #:61 [oradb11g] Files:1 Time: 0s

Serial Phase #:62 [oradb11g] Files:32 Time: 6s

Serial Phase #:63 [oradb11g] Files:1 Time: 0s

Parallel Phase #:64 [oradb11g] Files:6 Time: 9s

Serial Phase #:65 [oradb11g] Files:2 Time: 17s

Serial Phase #:66 [oradb11g] Files:3 Time: 56s

**************** Upgrading ORDIM ***************

Restart Phase #:67 [oradb11g] Files:1 Time: 1s

Serial Phase #:69 [oradb11g] Files:1 Time: 4s

Parallel Phase #:70 [oradb11g] Files:2 Time: 39s

Restart Phase #:71 [oradb11g] Files:1 Time: 1s

Parallel Phase #:72 [oradb11g] Files:2 Time: 4s

Serial Phase #:73 [oradb11g] Files:2 Time: 5s

***************** Upgrading SDO ****************

Restart Phase #:74 [oradb11g] Files:1 Time: 0s

Serial Phase #:76 [oradb11g] Files:1 Time: 27s

Serial Phase #:77 [oradb11g] Files:2 Time: 6s

Restart Phase #:78 [oradb11g] Files:1 Time: 0s

Serial Phase #:79 [oradb11g] Files:1 Time: 18s

Restart Phase #:80 [oradb11g] Files:1 Time: 2s

Parallel Phase #:81 [oradb11g] Files:3 Time: 67s

Restart Phase #:82 [oradb11g] Files:1 Time: 2s

Serial Phase #:83 [oradb11g] Files:1 Time: 7s

Restart Phase #:84 [oradb11g] Files:1 Time: 1s

Serial Phase #:85 [oradb11g] Files:1 Time: 10s

Restart Phase #:86 [oradb11g] Files:1 Time: 1s

Parallel Phase #:87 [oradb11g] Files:4 Time: 95s

Restart Phase #:88 [oradb11g] Files:1 Time: 3s

Serial Phase #:89 [oradb11g] Files:1 Time: 4s

Restart Phase #:90 [oradb11g] Files:1 Time: 1s

Serial Phase #:91 [oradb11g] Files:2 Time: 10s

Restart Phase #:92 [oradb11g] Files:1 Time: 0s

Serial Phase #:93 [oradb11g] Files:1 Time: 2s

Restart Phase #:94 [oradb11g] Files:1 Time: 1s

******* Upgrading ODM, WK, EXF, RUL, XOQ *******

Serial Phase #:95 [oradb11g] Files:1 Time: 20s

Restart Phase #:96 [oradb11g] Files:1 Time: 0s

*********** Final Component scripts ***********

Serial Phase #:97 [oradb11g] Files:1 Time: 3s

************* Final Upgrade scripts ************

Serial Phase #:98 [oradb11g] Files:1 Time: 172s

******************* Migration ******************

Serial Phase #:99 [oradb11g] Files:1 Time: 36s

*** End PDB Application Upgrade Pre-Shutdown ***

Serial Phase #:100 [oradb11g] Files:1 Time: 2s

Serial Phase #:101 [oradb11g] Files:1 Time: 0s

Serial Phase #:102 [oradb11g] Files:1 Time: 49s

***************** Post Upgrade *****************

Serial Phase #:103 [oradb11g] Files:1 Time: 25s

**************** Summary report ****************

Serial Phase #:104 [oradb11g] Files:1 Time: 3s

*** End PDB Application Upgrade Post-Shutdown **

Serial Phase #:105 [oradb11g] Files:1 Time: 2s

Serial Phase #:106 [oradb11g] Files:1 Time: 0s

Serial Phase #:107 [oradb11g] Files:1 Time: 35s

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

Phases [0-107] End Time:[2022_05_10 13:24:18]

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

Grand Total Time: 1892s

LOG FILES: (/u01/app/oracle/product/19.3/db_home/cfgtoollogs/oradb11g/upgrade20220510125237/catupgrd*.log)

Upgrade Summary Report Located in:

/u01/app/oracle/product/19.3/db_home/cfgtoollogs/oradb11g/upgrade20220510125237/upg_summary.log

Grand Total Upgrade Time: [0d:0h:31m:32s]

[oracle@srv7 bin]$

STEP 14 : Start the database in normal mode as ctuppt.sql script shoud be run in non upgrade mode

[oracle@srv7 bin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue May 10 16:29:42 2022

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 3154113400 bytes

Fixed Size 8901496 bytes

Variable Size 738197504 bytes

Database Buffers 2399141888 bytes

Redo Buffers 7872512 bytes

Database mounted.

Database opened.

SQL> @/home/oracle/d

NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN

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

ORADB11G oradb11g oradb11g READ WRITE PRIMARY NO 0

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

[oracle@srv7 bin]$ export ORACLE_SID=oradb11g

[oracle@srv7 bin]$ export PATH=/u01/app/oracle/product/19.3/db_home/dbs:$PATH

[oracle@srv7 bin]$ export ORACLE_HOME=/u01/app/oracle/product/19.3/db_home

[oracle@srv7 bin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue May 10 16:30:50 2022

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> exit

STEP 15 : Run catuppst.sql
You must run this script, either through DBUA or manually, if you perform a manual upgrade.
DBUA automatically runs catuppst.sql. You only must run this script separately for manual upgrades.
Do not run this in UPGRADE mode. Run catuppst.sql, located in the ORACLE_HOME/rdbms/admin directory, to perform remaining upgrade actions that do not require the database to be in UPGRADE mode. If an Oracle bundle patch or patch set update (PSU or BP) is installed in the Oracle home, then this script automatically applies that patch set update to the database.
Caution: If you perform a manual upgrade, and you do not run catuppst.sql,, then your database suffers performance degradation over time.

[oracle@srv7 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue May 10 16:31:57 2022

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> @catuppst.sql

Session altered.

PL/SQL procedure successfully completed.

Session altered.

System altered.

PL/SQL procedure successfully completed.

Session altered.

TIMESTAMP

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

COMP_TIMESTAMP DBRESTART 2022-05-10 16:32:07

DBUA_TIMESTAMP DBRESTART FINISHED 2022-05-10 16:32:07

DBUA_TIMESTAMP DBRESTART NONE 2022-05-10 16:32:07

TIMESTAMP

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

DBUA_TIMESTAMP CATUPPST STARTED 2022-05-10 16:32:07

TIMESTAMP

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

COMP_TIMESTAMP POSTUP_BGN 2022-05-10 16:32:07

DBUA_TIMESTAMP POSTUP_BGN FINISHED 2022-05-10 16:32:07

DBUA_TIMESTAMP POSTUP_BGN NONE 2022-05-10 16:32:07

TIMESTAMP

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

COMP_TIMESTAMP CATREQ_BGN 2022-05-10 16:32:07

DBUA_TIMESTAMP CATREQ_BGN FINISHED 2022-05-10 16:32:07

DBUA_TIMESTAMP CATREQ_BGN NONE 2022-05-10 16:32:07

PL/SQL procedure successfully completed.

catrequtlmg: b_StatEvt = TRUE

catrequtlmg: b_SelProps = FALSE

catrequtlmg: b_UpgradeMode = FALSE

catrequtlmg: b_InUtlMig = FALSE

PL/SQL procedure successfully completed.

TIMESTAMP

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

COMP_TIMESTAMP CATREQ_END 2022-05-10 16:32:07

DBUA_TIMESTAMP CATREQ_END FINISHED 2022-05-10 16:32:07

DBUA_TIMESTAMP CATREQ_END NONE 2022-05-10 16:32:07

catuppst: Dropping library DBMS_DDL_INTERNAL_LIB

PL/SQL procedure successfully completed.

catuppst: Dropping view _CURRENT_EDITION_OBJ_MIG

PL/SQL procedure successfully completed.

catuppst: Dropping view _ACTUAL_EDITION_OBJ_MIG

PL/SQL procedure successfully completed.

catuppst: Dropping view DBA_PART_KEY_COLUMNS_V$_MIG

PL/SQL procedure successfully completed.

catuppst: Dropping view DBA_SUBPART_KEY_COLUMNS_V$_MIG

PL/SQL procedure successfully completed.

catuppst: Dropping table OBJ$MIG

catuppst: Dropping table USER$MIG

catuppst: Dropping table COL$MIG

catuppst: Dropping table CLU$MIG

catuppst: Dropping table CON$MIG

catuppst: Dropping table BOOTSTRAP$MIG

catuppst: Dropping table TAB$MIG

catuppst: Dropping table TS$MIG

catuppst: Dropping table IND$MIG

catuppst: Dropping table ICOL$MIG

catuppst: Dropping table LOB$MIG

catuppst: Dropping table COLTYPE$MIG

catuppst: Dropping table SUBCOLTYPE$MIG

catuppst: Dropping table NTAB$MIG

catuppst: Dropping table REFCON$MIG

catuppst: Dropping table OPQTYPE$MIG

catuppst: Dropping table ICOLDEP$MIG

catuppst: Dropping table VIEWTRCOL$MIG

catuppst: Dropping table ATTRCOL$MIG

catuppst: Dropping table TYPE_MISC$MIG

catuppst: Dropping table LIBRARY$MIG

catuppst: Dropping table ASSEMBLY$MIG

catuppst: Dropping table TSQ$MIG

catuppst: Dropping table FET$MIG

PL/SQL procedure successfully completed.

TIMESTAMP

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

COMP_TIMESTAMP POSTUP_END 2022-05-10 16:32:08

DBUA_TIMESTAMP POSTUP_END FINISHED 2022-05-10 16:32:08

DBUA_TIMESTAMP POSTUP_END NONE 2022-05-10 16:32:08

TIMESTAMP

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

COMP_TIMESTAMP CATUPPST 2022-05-10 16:32:08

DBUA_TIMESTAMP CATUPPST FINISHED 2022-05-10 16:32:08

DBUA_TIMESTAMP CATUPPST NONE 2022-05-10 16:32:08

Session altered.

STEP 16 : Run utlrp.sql script

Use utlrp.sql to recompile stored PL/SQL and Java code. DBUA runs this script automatically. When you upgrade manually, you must run this script.

SQL> select count(*) from dba_objects where status='INVALID';

COUNT(*)

----------

9429

SQL> @utlrp.sql

Session altered.

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_BGN 2022-05-10 16:33:01

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid

DOC> objects in the database. Recompilation time is proportional to the

DOC> number of invalid objects in the database, so this command may take

DOC> a long time to execute on a database with a large number of invalid

DOC> objects.

DOC>

DOC> Use the following queries to track recompilation progress:

DOC>

DOC> 1. Query returning the number of invalid objects remaining. This

DOC> number should decrease with time.

DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

DOC>

DOC> 2. Query returning the number of objects compiled so far. This number

DOC> should increase with time.

DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;

DOC>

DOC> This script automatically chooses serial or parallel recompilation

DOC> based on the number of CPUs available (parameter cpu_count) multiplied

DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).

DOC> On RAC, this number is added across all RAC nodes.

DOC>

DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel

DOC> recompilation. Jobs are created without instance affinity so that they

DOC> can migrate across RAC nodes. Use the following queries to verify

DOC> whether UTL_RECOMP jobs are being created and run correctly:

DOC>

DOC> 1. Query showing jobs created by UTL_RECOMP

DOC> SELECT job_name FROM dba_scheduler_jobs

DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>

DOC> 2. Query showing UTL_RECOMP jobs that are running

DOC> SELECT job_name FROM dba_scheduler_running_jobs

DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>#

PL/SQL procedure successfully completed.

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_END 2022-05-10 16:40:11

DOC> The following query reports the number of invalid objects.

DOC>

DOC> If the number is higher than expected, please examine the error

DOC> messages reported with each object (using SHOW ERRORS) to see if they

DOC> point to system misconfiguration or resource constraints that must be

DOC> fixed before attempting to recompile these objects.

DOC>#

OBJECTS WITH ERRORS

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

DOC> The following query reports the number of exceptions caught during

DOC> recompilation. If this number is non-zero, please query the error

DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors

DOC> are due to misconfiguration or resource constraints that must be

DOC> fixed before objects can compile successfully.

DOC> Note: Typical compilation errors (due to coding errors) are not

DOC> logged into this table: they go into DBA_ERRORS instead.

DOC>#

ERRORS DURING RECOMPILATION

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

Function created.

PL/SQL procedure successfully completed.

Function dropped.

PL/SQL procedure successfully completed.

SQL>

STEP 17 : Change Compatible parameter to 19c

SQL> show parameter compatible;

NAME TYPE VALUE

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

compatible string 11.2.0.4.0

noncdb_compatible boolean FALSE

SQL> ALTER SYSTEM SET COMPATIBLE = '19.3.0.0.0' SCOPE=SPFILE;

System altered.

SQL> show parameter compatible;

NAME TYPE VALUE

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

compatible string 11.2.0.4.0

noncdb_compatible boolean FALSE

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 3154113400 bytes

Fixed Size 8901496 bytes

Variable Size 738197504 bytes

Database Buffers 2399141888 bytes

Redo Buffers 7872512 bytes

Database mounted.

Database opened.

SQL> show parameter compatible;

NAME TYPE VALUE

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

compatible string 19.3.0.0.0

noncdb_compatible boolean FALSE

SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

STEP 18 Change time zone version from 14 to 32

[oracle@srv7 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue May 10 16:48:48 2022

Connected to an idle instance.

SQL> startup upgrade;

ORACLE instance started.

Total System Global Area 3154113400 bytes

Fixed Size 8901496 bytes

Variable Size 738197504 bytes

Database Buffers 2399141888 bytes

Redo Buffers 7872512 bytes

Database mounted.

Database opened.

SQL> SET SERVEROUTPUT ON

DECLARE

l_tz_version PLS_INTEGER;

BEGIN

l_tz_version := DBMS_DST.get_latest_timezone_version;

DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);

DBMS_DST.begin_upgrade(l_tz_version);

END;

/SQL> 2 3 4 5 6 7 8

l_tz_version=32

An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

SQL> select version from v$timezone_file;

VERSION

----------

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

[oracle@srv7 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue May 10 16:51:44 2022

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 3154113400 bytes

Fixed Size 8901496 bytes

Variable Size 738197504 bytes

Database Buffers 2399141888 bytes

Redo Buffers 7872512 bytes

Database mounted.

Database opened.

SQL> sho parameter compatible

NAME TYPE VALUE

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

compatible string 19.3.0.0.0

noncdb_compatible boolean FALSE

SQL> SET SERVEROUTPUT ON

DECLARE

l_failures PLS_INTEGER;

BEGIN

DBMS_DST.upgrade_database(l_failures);

DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);

DBMS_DST.end_upgrade(l_failures);

DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);

END;

/SQL> 2 3 4 5 6 7 8 9

Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"

Number of failures: 0

Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_S"

Number of failures: 0

Table list: "IX"."AQ$_ORDERS_QUEUETABLE_S"

Number of failures: 0

Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_L"

Number of failures: 0

Table list: "IX"."AQ$_ORDERS_QUEUETABLE_L"

Number of failures: 0

Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"

Number of failures: 0

Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"

Number of failures: 0

DBMS_DST.upgrade_database : l_failures=0

An upgrade window has been successfully ended.

DBMS_DST.end_upgrade : l_failures=0

PL/SQL procedure successfully completed.

SQL>

SQL> @/home/oracle/d

NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN

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

ORADB11G oradb11g oradb11g READ WRITE PRIMARY NO 0

STEP 19 POST Upgrade Steps

SQL> @/u02/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.

Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script

Version: 19.0.0.0.0 Build: 1

Generated on: 2022-05-10 12:04:47

For Source Database: ORADB11G

Source Database Version: 11.2.0.4.0

For Upgrade to Version: 19.0.0.0.0

Preup Preupgrade

Action Issue Is

Number Preupgrade Check Name Remedied Further DBA Action

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

12. depend_usr_tables YES None.

13. old_time_zones_exist YES None.

14. dir_symlinks YES None.

15. post_dictionary YES None.

16. post_fixed_objects NO Informational only.

Further action is optional.

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

SQL> SQL> SELECT * FROM v$timezone_file;

FILENAME VERSION CON_ID

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

timezlrg_32.dat 32 0

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

NAME VERSION OPEN_MODE

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

ORADB11G 19.0.0.0.0 READ WRITE

Oracle Database Commands and Practical Examples

HOME

Sunday, 22 May 2022

Block change Tracking -Enable -disable

Upgrade 11.2.0.4.0 to 19.3.0.0.0 Standalone database