Oracle Spatial Error ORA-29903: error in executing ODCIIndexFetch() routine

After upgrading an Oracle database from 12cR1 to 19c release, this database using Oracle Spatial component. The developers noticed many of their spatial queries are failing and the following error is thrown:

ORA-29903: error in executing ODCIIndexFetch() routine

What is the cause of this issue ?

check the value of this parameter ” SPATIAL_VECTOR_ACCELERATION ” in your database

SQL> show parameter SPATIAL_VECTOR_ACCELERATION

if its set to “FALSE” , then enable it to “TRUE”

SQL> ALTER SYSTEM SET SPATIAL_VECTOR_ACCELERATION = TRUE;

And this will fix the problem. I was under the impression that ” SPATIAL_VECTOR_ACCELERATION” parameter is “optional” parameter that will improve your queries against GIS data, but it seems starting with Oracle 12cR2 and beyond this parameter should be set to TRUE !

Oracle 21c in Oracle Cloud ATP – Default Tablespace Encryption Algorithm

Tablespace encryption is very important database security feature where data at rest is encrypted for protection against any attempt of physical access to copy data file levels. Of course Oracle cloud provides security protection and encrypts tablespaces. In Oracle 21c a new security parameter feature called “ Default Tablespace Encryption Algorithm” this parameter will set the default/required encryption algorithm for newly created tablespaces…..let us briefly explore it:

The following query will list encrypted tablespaces:

SQL> select * from v$encrypted_tablespaces;

The following is the new parameter and its default value

SQL> SHOW PARAMETER TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM

its worth stating that in Oracle ATP you can’t execute create tablespace sql command: https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/appendix-restrictions-sql.html#GUID-791E7112-07F7-46F0-BD81-777C8FAD83A0

Oracle datapatch error PLS-00201: identifier ‘DBMS_LOB’

The following errors were generated in the “datapatch” part of applying January CPU 2021 on Oracle 19c database:

Validating logfiles…done

Patch 32218454 apply: WITH ERRORS

logfile: /orcl/db22/cfgtoollogs/sqlpatch/32218454/24018797/32218454_apply_db22_2021Feb12_01_13_32.log (errors)

-> Error at line 28715: script rdbms/admin/prvtgwmpl.plb

  - Warning: Package Body created with compilation errors.

-> Error at line 28722: script rdbms/admin/prvtgwmpl.plb

  - 3422/8   PL/SQL: Statement ignored

-> Error at line 28723: script rdbms/admin/prvtgwmpl.plb

  - 3422/50  PLS-00201: identifier 'DBMS_RANDOM' must be declared

-> Error at line 28724: script rdbms/admin/prvtgwmpl.plb

  - 5631/9   PL/SQL: Statement ignored

-> Error at line 28725: script rdbms/admin/prvtgwmpl.plb

  - 5631/9   PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28726: script rdbms/admin/prvtgwmpl.plb

  - 5688/9   PL/SQL: Statement ignored

-> Error at line 28727: script rdbms/admin/prvtgwmpl.plb

  - 5688/9   PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28728: script rdbms/admin/prvtgwmpl.plb

  - 6477/4   PL/SQL: Statement ignored

-> Error at line 28732: script rdbms/admin/prvtgwmpl.plb

  - 6477/4   PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28733: script rdbms/admin/prvtgwmpl.plb

  - 6534/4   PL/SQL: Statement ignored

-> Error at line 28734: script rdbms/admin/prvtgwmpl.plb

  - 6534/4   PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28735: script rdbms/admin/prvtgwmpl.plb

  - 6625/6   PL/SQL: Statement ignored

-> Error at line 28736: script rdbms/admin/prvtgwmpl.plb

  - 6625/17  PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28737: script rdbms/admin/prvtgwmpl.plb

  - 6895/14  PL/SQL: Statement ignored

-> Error at line 28738: script rdbms/admin/prvtgwmpl.plb

  - 6895/14  PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28742: script rdbms/admin/prvtgwmpl.plb

  - 6930/14  PL/SQL: Statement ignored

-> Error at line 28743: script rdbms/admin/prvtgwmpl.plb

  - 6930/14  PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28744: script rdbms/admin/prvtgwmpl.plb

  - 6932/14  PL/SQL: Statement ignored

-> Error at line 28745: script rdbms/admin/prvtgwmpl.plb

  - 6932/14  PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28746: script rdbms/admin/prvtgwmpl.plb

  - 7048/6   PL/SQL: Statement ignored

-> Error at line 28747: script rdbms/admin/prvtgwmpl.plb

  - 7048/6   PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28769: script rdbms/admin/prvtgwmpl.plb

  - 3422/8   PL/SQL: Statement ignored

-> Error at line 28770: script rdbms/admin/prvtgwmpl.plb

  - 3422/50  PLS-00201: identifier 'DBMS_RANDOM' must be declared

-> Error at line 28771: script rdbms/admin/prvtgwmpl.plb

  - 5631/9   PL/SQL: Statement ignored

-> Error at line 28772: script rdbms/admin/prvtgwmpl.plb

  - 5631/9   PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28773: script rdbms/admin/prvtgwmpl.plb

  - 5688/9   PL/SQL: Statement ignored

-> Error at line 28774: script rdbms/admin/prvtgwmpl.plb

  - 5688/9   PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28775: script rdbms/admin/prvtgwmpl.plb

  - 6477/4   PL/SQL: Statement ignored

-> Error at line 28779: script rdbms/admin/prvtgwmpl.plb

  - 6477/4   PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28780: script rdbms/admin/prvtgwmpl.plb

  - 6534/4   PL/SQL: Statement ignored

-> Error at line 28781: script rdbms/admin/prvtgwmpl.plb

  - 6534/4   PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28782: script rdbms/admin/prvtgwmpl.plb

  - 6625/6   PL/SQL: Statement ignored

-> Error at line 28783: script rdbms/admin/prvtgwmpl.plb

  - 6625/17  PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28784: script rdbms/admin/prvtgwmpl.plb

  - 6895/14  PL/SQL: Statement ignored

-> Error at line 28785: script rdbms/admin/prvtgwmpl.plb

  - 6895/14  PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28789: script rdbms/admin/prvtgwmpl.plb

  - 6930/14  PL/SQL: Statement ignored

-> Error at line 28790: script rdbms/admin/prvtgwmpl.plb

  - 6930/14  PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28791: script rdbms/admin/prvtgwmpl.plb

  - 6932/14  PL/SQL: Statement ignored

-> Error at line 28792: script rdbms/admin/prvtgwmpl.plb

  - 6932/14  PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28793: script rdbms/admin/prvtgwmpl.plb

  - 7048/6   PL/SQL: Statement ignored

-> Error at line 28794: script rdbms/admin/prvtgwmpl.plb

  - 7048/6   PLS-00201: identifier 'DBMS_LOB' must be declared

Please refer to MOS Note 1609718.1 and/or the invocation log

/orcl/db22/cfgtoollogs/sqlpatch/sqlpatch_76633_2021_02_12_01_13_10/sqlpatch_invocation.log

for information on how to resolve the above errors.

To fix this problem:

the above errors generated because multiple built-in oracle pacakges are revoked from “PUBLIC” as best security practice
if you query dba_registry_sqlpatch you will find that the patch is applied with ERRORS ….to overcome this and esnure success
patches being applied…grant these packages temporarly to public:

sqlplus / as sysdba

SQL> grant execute on DBMS_RANDOM to PUBLIC;

SQL> grant execute on UTL_FILE to PUBLIC;

SQL> grant execute on UTL_HTTP to PUBLIC;

SQL> grant execute on dbms_lob to PUBLIC;

SQL> grant execute on dbms_sql to PUBLIC;

SQL> grant execute on dbms_job to PUBLIC;

SQL> exit;

then, re-run datapatch again:

cd $ORACLE_HOME/OPatch

./datapatch -verbose

after that revoke the permissions:

SQL> revoke execute on DBMS_RANDOM from PUBLIC;

SQL> revoke execute on UTL_FILE from PUBLIC;

SQL> revoke execute on UTL_HTTP from PUBLIC;

SQL> revoke execute on dbms_lob from PUBLIC;

SQL> revoke execute on dbms_sql from PUBLIC;

SQL> revoke execute on dbms_job from PUBLIC;

then perform re-compilation:

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

SQL> select comp_name,status from dba_registry;

Oracle 19c upgrade error ORA-40362: invalid object SYS.ODCIPARTINFOLIST specified in the statement

As its known by now Oracle 19c database release is the long-term release for Oracle database system and by now your Infrastructure should be based on it.

One of the database systems while upgrading it from Oracle 12cR1 to 19c, the below errors generated (this was the first upgrade failure I have ever faced since my journey with 19c upgrade for the last 1 year).

Serial   Phase #:105  [orcl] Files:1    Time: 3s

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

Serial   Phase #:107  [orcl] Files:1     Time: 34s

——————————————————

Phases [0-107]         End Time:[2021_01_22 11:17:33]

——————————————————

Grand Total Time: 1359s

*** WARNING: ERRORS FOUND DURING UPGRADE ***

1. Evaluate the errors found in the upgrade logs

    and determine the proper action.

2. Rerun the upgrade when the problem is resolved

REASON:

      ERRORS FOUND: During Upgrade

         FILENAME: /orcl/export/upgrade_19c_log//catupgrd0.log AT LINE NUMBER: 802204

——————————————————

Identifier CATPROC 21-01-22 10:59:28

SCRIPT    = [/orcl/ora/product/19.3/rdbms/admin/catodci.sql]

ERROR    = [ORA-40362: invalid object SYS.ODCIPARTINFOLIST specified in the statement]

STATEMENT = [GRANT EXECUTE ON ODCIPartInfoList TO PUBLIC]

——————————————————

——————————————————

Identifier CONTEXT 21-01-22 11:08:54

SCRIPT    = [/orcl/ora/product/19.3/ctx/admin/t1202000.sql]

ERROR    = [ORA-06550: line 4, column 22: PLS-00905: object CTXSYS.TEXTINDEXMETHODS is invalid

ORA-06550: line 4, column 6:

PL/SQL: Statement ignored

]

STATEMENT = [alter indextype context using TextIndexMethods]

——————————————————

——————————————————

Identifier CONTEXT 21-01-22 11:08:54

SCRIPT    = [/orcl/ora/product/19.3/ctx/admin/t1202000.sql]

ERROR    = [PLS-00905: object CTXSYS.TEXTINDEXMETHODS is invalid ORA-06550: line 4, column 6:

PL/SQL: Statement ignored

]

STATEMENT = [as above]

——————————————————

——————————————————

Identifier CONTEXT 21-01-22 11:08:54

SCRIPT    = [/orcl/ora/product/19.3/ctx/admin/t1202000.sql]

ERROR    = [ORA-06550: line 4, column 6: PL/SQL: Statement ignored

]

STATEMENT = [as above]

——————————————————

——————————————————

Identifier CONTEXT 21-01-22 11:08:54

SCRIPT    = [/orcl/ora/product/19.3/ctx/admin/t1202000.sql]

ERROR    = [PL/SQL: Statement ignored]

STATEMENT = [as above]

——————————————————

——————————————————

Identifier CONTEXT 21-01-22 11:08:54

SCRIPT    = [/orcl/ora/product/19.3/ctx/admin/dr0itype.sql]

ERROR    = [ORA-06552: PL/SQL: Declaration ignored ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

]

STATEMENT = [create or replace operator contains binding

  (varchar2, varchar2) return number

     with index context, scan context TextIndexMethods

     compute ancillary data without column data using ctx_contains.Textcontains

The solution of this error is the following:

modify/edit the following file:

vi $ORACLE_HOME/rdbms/admin/catodci.sql

replace this statement: GRANT EXECUTE ON ODCIPartInfoList TO PUBLIC

with

begin

  execute immediate ‘alter type ODCIPartInfo compile’;

  execute immediate ‘alter type ODCIPartInfoList compile’;

  begin

  execute immediate ‘GRANT EXECUTE ON ODCIPartInfo TO PUBLIC’;

  exception

  when others then

  null;

  end;

  begin

  execute immediate ‘GRANT EXECUTE ON ODCIPartInfoList TO PUBLIC’;

  exception

  when others then

  null;

  end;

  begin

  execute immediate ‘GRANT EXECUTE ON ODCIPartInfoList TO PUBLIC’;

  exception

  when others then

  null;

  end;

  begin

  execute immediate ‘GRANT EXECUTE ON ODCIPartInfoList TO PUBLIC’;

  exception

  when others then

  null;

  end;

exception

  when others then

  null;

end;

/

After that, re-run the upgrade command

Oracle PDB and when is DAPTATCH required to be executed

Here I am going to show three scenarios to illustrate when datapatch utility is required to be executed.

In my simulation the source CDB environment version is 19.3 , the destination CDB environment is 19.9 (patched with October 2020 RU).

Scenario 1: moving PDB from a lower version (source CDB) to a higher destination CDB

SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;

SQL> ALTER PLUGGABLE DATABASE PDB2 CLOSE;

SQL> ALTER PLUGGABLE DATABASE PDB2 UNPLUG INTO ‘D:\19c_vm\OracleDatabase\19.3.0\PDB2_UNLPLUG.xml’;

Then I will drop the database from the source CDB:

SQL> DROP PLUGGABLE DATABASE PDB2;

After that copy the datafiles and .XML file to the destination server hosting the destination CDB:

SQL> CREATE PLUGGABLE DATABASE NEW_PDB

  USING ‘/opt/oracle/product/19c/dbhome_1/dbs/PDB2_UNLPLUG.xml’ MOVE

 SOURCE_FILE_NAME_CONVERT=  (‘D:\19C_BASE\ORADATA\ORACLE19C\PDB2\’, ‘/opt/oracle/oradata/ORCLCDB/NEW_PDB/’);

// open the database and check the dictionary:

alter pluggable database NEW_PDB open;

alter session set container=NEW_PDB;

select * from dba_registry_sqlpatch;

There are NO rows resulted from dba_registry_sqlpatch , this means we will need to run datapatch to update the dictionary of the newly plugged database (always download the latest OPatch utility version):

cd $ORACLE_HOME/OPatch

[oracle@oracle-19c]$ ./datapatch -verbose

SQL Patching tool version 19.9.0.0.0 Production on Mon Nov 30 15:40:32 2020

Copyright (c) 2012, 2020, Oracle.  All rights reserved.

Log file for this invocation: /opt/oracle/cfgtoollogs/sqlpatch/sqlpatch_9900_2020_11_30_15_40_32/sqlpatch_invocation.log

Connecting to database…OK

Gathering database info…done

Note:  Datapatch will only apply or rollback SQL fixes for PDBs

       that are in an open state, no patches will be applied to closed PDBs.

       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation

       (Doc ID 1585822.1)

Bootstrapping registry and package to current versions…done

Determining current state…done

Current state of interim SQL patches:

Interim patch 31668882 (OJVM RELEASE UPDATE: 19.9.0.0.201020 (31668882)):

  Binary registry: Installed

  PDB CDB$ROOT: Applied successfully on 06-NOV-20 08.17.57.037468 PM

  PDB NEW_PDB: Not installed

  PDB PDB$SEED: Applied successfully on 06-NOV-20 08.17.58.718557 PM

  PDB PDB1: Applied successfully on 06-NOV-20 08.18.00.702663 PM

  PDB PDB2: Applied successfully on 06-NOV-20 08.18.02.342712 PM

Current state of release update SQL patches:

  Binary registry:

    19.9.0.0.0 Release_Update 200930183249: Installed

  PDB CDB$ROOT:

    Applied 19.9.0.0.0 Release_Update 200930183249 successfully on 06-NOV-20 08.59.54.481954 PM

  PDB NEW_PDB:

    No release update patches installed

After that check dba_registry_sqlpatch view ……the metadata is updated reflecting the patch version and SQL code modifications.

Scenario 2: creating a new pluggable database in a patched CDB

 SQL> CREATE PLUGGABLE DATABASE “PDB33” ADMIN USER “admin” IDENTIFIED BY “admin_123”

  FILE_NAME_CONVERT=( ‘/opt/oracle/oradata/ORCLCDB/’, ‘/opt/oracle/oradata/ORCLCDB/PDB33/’ )

  STORAGE UNLIMITED TEMPFILE REUSE;

So, NO need to run datapatch for any “newly” created PDB in CDB container !

The pluggable database created is based on the template database PDB$SEED

Scenario 3: migrating a PDB to a lower version CDB

ALTER SESSION SET CONTAINER=CDB$ROOT;

ALTER PLUGGABLE DATABASE NEW_PDB CLOSE;

ALTER PLUGGABLE DATABASE NEW_PDB UNPLUG INTO ‘/vagrant/NEW_PDB_UNLPLUG.xml’;

Then I will drop the database from the source CDB:

DROP PLUGGABLE DATABASE NEW_PDB;

// I will plug the database now in the destination CDB

alter session set container=CDB$ROOT;

CREATE PLUGGABLE DATABASE NEW_PDB

  USING ‘D:\19c_base\oradata\ORACLE19C\NEW_PDB\NEW_PDB_UNLPLUG.xml’ MOVE

 SOURCE_FILE_NAME_CONVERT=  (‘/opt/oracle/oradata/ORCLCDB/ORCLCDB/1D59E472CA974170BFC1A471E133E3C9/datafile/’, ‘D:\19c_base\oradata\ORACLE19C\NEW_PDB\’);

After successfully plugging the database….now checking the registry:

alter session set container=NEW_PDB;

select PATCH_ID,DESCRIPTION from dba_registry_sqlpatch;

Dictionary is reflecting that the PDB is patched with 19.9 patches

What will happen if I run “datapatch -verbose” command ?

The patches will be rolled back (as expected) !

The datapatch perl script executes the package DBMS_QOPATCH that compares the patch level between the binaries and the database system metadata.

select action,action_time,description from dba_registry_sqlpatch;

This blog post was inspired from youtube video by Connor McDonald titled “Do I need to run DataPatch” ?

Oracle 19c New Feature: Automatic Database Diagnostic Monitor (ADDM) in Pluggable Database Level

Oracle 19c has introduced new feature (capability) which is generating ADDM report from a PDB-level, this was not possible in previous releases, as ADDM was generated from a CDB (Container Level).

The following are the steps to enable ADDM in a PDB:

SQL> alter session set container=PDB1;

Session altered.

SQL> ALTER SYSTEM SET AWR_PDB_AUTOFLUSH_ENABLED=TRUE;

System altered.

//configure snapshot interval for AWR

SQL> EXEC dbms_workload_repository.modify_snapshot_settings(interval=>60);

PL/SQL procedure successfully completed.

// take manual snapshot

SQL> BEGIN

   DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

END;

/

// to check snapshsot details for PDB1 database

SQL> select * from DBA_HIST_SNAPSHOT where CON_ID=3;

As HR user I will create a new table and insert dummy data:

sqlplus hr/hr_123@PDB1

CREATE  TABLE DUMMY_SEPT (sales  number(10));

BEGIN

    FOR v_LoopCounter IN 1..3000 LOOP

 insert into DUMMY_SEPT values (‘4000’);   

COMMIT;

    END LOOP;

END;

/

commit;

select *  from DUMMY_SEPT;

// as SYS or SYSTEM account execute the following to take manual snapshot:

SQL> BEGIN

   DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

END;

/

Now, I will generate ADDM Report for PDB1 database:

SQL> alter session set container=PDB1;

Session altered.

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

Current Instance

~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance

———– ———— ——– ————

 2381533563 ORCLCDB             1 ORCLCDB

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  DB Id      Inst Num   DB Name      Instance     Host

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

  2381533563     1      ORCLCDB      ORCLCDB      oracle-19c-v

Using 2381533563 for database Id

Using          1 for instance number

Specify the number of days of snapshots to choose from

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed.  Pressing <return> without

specifying a number lists all completed snapshots.

Listing the last 3 days of Completed Snapshots

Instance     DB Name      Snap Id       Snap Started    Snap Level

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

ORCLCDB      ORCLCDB              1  24 Sep 2020 12:38    1

                                  2  24 Sep 2020 14:00    1

                                  3  24 Sep 2020 14:25    1

                                  4  24 Sep 2020 14:30    1

                                  5  24 Sep 2020 14:50    1

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 4

Begin Snapshot Id specified: 4

Enter value for end_snap: 5

End   Snapshot Id specified: 5

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is addmrpt_1_4_5.txt.  To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name: ADDM_PDB1_SEPT24.txt

Using the report name ADDM_PDB1_SEPT24.txt

Running the ADDM analysis on the specified pair of snapshots …

Generating the ADDM report for this analysis …

          ADDM Report for Task ‘TASK_15’

          ——————————

Analysis Period

—————

AWR snapshot range from 4 to 5.

Time period starts at 24-SEP-20 02.30.46 PM

Time period ends at 24-SEP-20 02.50.05 PM

Analysis Target

—————

Database ‘ORCLCDB’ with DB ID 2381533563.

Database version 19.0.0.0.0.

ADDM performed an analysis of instance ORCLCDB, numbered 1 and hosted at

oracle-19c-vagrant.

ADDM detected that the system is a PDB.

Activity During the Analysis Period

———————————–

Total database time was 6 seconds.

The average number of active sessions was .01.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

There are no findings to report.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

          Additional Information

          ———————-

Miscellaneous Information

————————-

There was no significant database activity to run the ADDM.

End of Report

Report written to ADDM_PDB1_SEPT24.txt

Patching Perl in Oracle database home binaries – CVE-2018-20843 and CVE-2019-15903

In Oracle Critical Patch Update (CPU) released in October 2020, Oracle has changed the advisory page ” non-exploitable vulnerabilities in each product family will be identified separately under each risk matrix, and the total vulnerability counts will no longer include non-exploitable vulnerabilities in third-party components.” according to Oracle: https://blogs.oracle.com/security/october-2020-critical-patch-update-released

So, basically Oracle will provides fixes for third party products such as Perl for example even though the vulnerabilities will not directly exploit the Oracle product…in our case here “Oracle database system”.

to update to the latest Perl build, its applicable to Oracle 18c,12cR2, and 12cR1….19c database binaries already shipped with latest Perl build that mitigates (CVE’s CVE-2018-20843 , CVE-2019-15903)

To patch Perl….download it from Oracle Support first.

Then, check the current version:

$ORACLE_HOME/perl/bin/perl -v

This is perl 5, version 22, subversion 0 (v5.22.0) built for x86_64-linux-thread-multi

Copyright 1987-2015, Larry Wall

Perl may be copied only under the terms of either the Artistic License or the

GNU General Public License, which may be found in the Perl 5 source kit.

Complete documentation for Perl, including FAQ lists, should be found on

this system using “man perl” or “perldoc perl”.  If you have access to the

Internet, point your browser at http://www.perl.org/, the Perl Home Page.

Go to the directory where you have download and unzipped the patch

cd /patches/Perl_patches/18c_Patch/31225444

using OPatch utility apply the patch (you need to check that you have the latest version of Opatch utility):

$ORACLE_HOME/OPatch/opatch apply

check the Perl version again:

$ORACLE_HOME/perl/bin/perl -v

This is perl 5, version 28, subversion 2 (v5.28.2) built for x86_64-linux-thread-multi

Copyright 1987-2019, Larry Wall

Perl may be copied only under the terms of either the Artistic License or the

GNU General Public License, which may be found in the Perl 5 source kit.

Complete documentation for Perl, including FAQ lists, should be found on

this system using “man perl” or “perldoc perl”.  If you have access to the

Internet, point your browser at http://www.perl.org/, the Perl Home Page.

Oracle database export datapump fails with ORA-39374 and ORA-01732

One of the databases I have recently upgraded to Oracle 12cR2 , the export datapump was failing and never continues to finish. This was wired….the following errors were thrown in the export log file:

ORA-39374: Statistics failed to export. Failing error is
ORA-01732: data manipulation operation not legal on this view

To troubleshoot this problem:

Try taking full database export datapump with the clause “EXCLUDE=STATISTICS

if this doesn’t fix your problem then check the parameter size of streams_pool_size it should be at least 300 MB. changing this parameter value actually fixed my problem !