How To Apply Data Pump Recommended Proactive Patches

arcsdegeoLeave a comment

Datapump bundle patches are special patches for datapump component of the Oracle database system, and can be applied “AFTER” the main database patch is applied…The RU (release update) patch needs to be already applied on the Oracle DB System.

its important to mention that this patch is considered “Non-Binary Online Patch” which means you can apply it while database is up and running fine. However, you need to make sure no datapump jobs are currently running while applying the patch.

The benefits of applying this patch are many…for example bug fixes for datapump component will be fixed, it will speed up datapump operations…etc.

First, check there are No pre-requisite conflicts:

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Then, go to the directory where the datapump patch is located:

cd /var/DATAPUMP_BUNDLE_PATCH_19.19.0.0.0/35261302

After that, apply the patch through OPATCH utility (use latest one)

[oracle@oracle-19c]$ $ORACLE_HOME/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.37
Copyright (c) 2023, Oracle Corporation. All rights reserved.

Oracle Home : /opt/oracle/product/19c/dbhome_1
Central Inventory : /opt/oracle/oraInventory
from : /opt/oracle/product/19c/dbhome_1/oraInst.loc
OPatch version : 12.2.0.1.37
OUI version : 12.2.0.7.0
Log file location : /opt/oracle/product/19c/dbhome_1/cfgtoollogs/opatch/opatch2023-04-20_15-38-36PM_1.log

Verifying environment and performing prerequisite checks…
OPatch continues with these patches: 35261302

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Backing up files…
Applying interim patch ‘35261302’ to OH ‘/opt/oracle/product/19c/dbhome_1’

Patching component oracle.rdbms, 19.0.0.0.0…

Patching component oracle.rdbms.dbscripts, 19.0.0.0.0…
Patch 35261302 successfully applied.
Log file location: /opt/oracle/product/19c/dbhome_1/cfgtoollogs/opatch/opatch2023-04-20_15-38-36PM_1.log

OPatch succeeded.

Then, go to the OPATCH directory location and run datapatch perl script:

cd $ORACLE_HOME/OPatch

./datapatch -verbose

Log file for this invocation: /opt/oracle/cfgtoollogs/sqlpatch/sqlpatch_4829_2023_04_21_11_13_37/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
Error: prereq checks failed!
verify_queryable_inventory returned ORA-20001: Latest xml inventory is not loaded into table
Prereq check failed, exiting without installing any patches.

Please refer to MOS Note 1609718.1 and/or the invocation log
/opt/oracle/cfgtoollogs/sqlpatch/sqlpatch_4829_2023_04_21_11_13_37/sqlpatch_invocation.log

And, that’s it….its online operation without the need to be done in maintenance windows (database services shutdown activity).

Advertisement

Oracle Database 23c Schema-Level Privileges

arcsdegeoLeave a comment

Oracle has introduced a very good and much needed feature which is the ability to grant object level permissions on schema level scope. In the past if you have a schema with many tables under it and you want to grant the application account SELECT permission on these tables…you have 2 options:

Option 1:  performing the grant select explicitly on each table in the schema…so if you have 300 tables in your schema “HR”…you will execute : GRANT SELECT on HR.XXXX to HR_APP for each table. And any newly introduced set of tables will require the re-grant operation against them.

Option 2:  granting the application account “HR_APP” select any table permissions….which is very excessive permission and violates the security boundary if you are in a multi-hosting environment as the account will have access to tables in other schemas.

Let us explore 23c feature:

I will create two tables in HR schema and populate it with dummy data.

create table HR.EMPLOYEES ( fname varchar2(10), lname varchar2 (10) , ID number, retired BOOLEAN , deptno varchar2 (10));

insert into HR.EMPLOYEES values(‘John’,’Smith’,13912,FALSE,’IT-1′);

commit;

insert into HR.EMPLOYEES values(‘Sarah’,’Richardson’,71918,FALSE,’HR-1′);

commit;

insert into HR.EMPLOYEES values(‘Ricardo’,’Lopez’,150,TRUE,’Finance-1′);

commit;

create table HR.DEPARTMENTS ( deptname varchar2(10), orgcode varchar2 (10) , no_employees number , deptno varchar2 (10));

insert into HR.DEPARTMENTS values(‘IT’,’CG981258′,320,’IT-1′);

commit;

insert into HR.DEPARTMENTS values(‘FINANCE’,’CG96251′,166,’FIN-1′);

commit;

insert into HR.DEPARTMENTS values(‘AUDIT’,’CG400311′,320,’AUD-1′);

commit;

Now, using the new SQL command feature:

SQL> grant select any table on schema HR to HR_APP;

When accessing the database using application account HR_APP , the account will be able to view the data for the two existing tables:

This is great….and this will be applied automatically to newly created tables….I will create a dummy table based on departments table and HR_APP will be able to view the data:

SQL> create table HR.DUMMY as select * from HR.DEPARTMENTS;

Storing Oracle AWR snapshots in User Defined Tablespace (Non-SYSAUX tablespace)

arcsdegeoLeave a comment

In Oracle database there is a component called “Automatic Workload Repository” (AWR). AWR collects, processes, and maintains performance statistics for the database.

Moreover, AWR is taken by default on hourly basis and these snapshots are by default stored in SYSAUX tablespace. One of the reasons why you would like to offload AWR to a different tablepsace is because you might have many components in your database and they are installed in SYSAUX so you would like to have a separate space for it.

In this blog post, I will illustrate how can you store AWR in user-tablespace instead of SYSAUX:

First, I will create a user tablespace and will call it TS_AWR

  SQL> CREATE TABLESPACE TS_AWR DATAFILE

  ‘/opt/oracle/oradata/ORCLCDB/PDB_TEMP/TS_AWR.DBF’ SIZE 20M

  LOGGING ONLINE PERMANENT BLOCKSIZE 8192

  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT

 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;

Then, execute the following dbms_workload_repository package to change database snapshot settings, to find DBID for a pluggable database [select dbid from v$pdbs]:

SQL> exec dbms_workload_repository.modify_snapshot_settings(dbid =>3048656388, tablespace_name=> ‘TS_AWR’);

PL/SQL procedure successfully completed.

Now, to Test it….manually execute a snapshot:

SQL> EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

Checking the new tablespace TS_AWR…clearly AWR tables and objects were created successfully:

Oracle Database DATAPATCH Parameters and Pre-requisite Check (sanity_checks)

arcsdegeoLeave a comment

Oracle database patching is one of the major processes/actions if you are a DBA/System Admin handling huge oracle database platform…patching is a must to protect your organizations data as serious security vulnerabilities will be fixed/mitigated with these patches.  Oracle releases quarterly patches (every 3 months) and they are called RU (release updates) and they are cumulative patches.  So, if you “miss”one the next one will contain fixes from previous patches. And recently Oracle release MRP’s (Monthly Recommended Patches) on top of your base RU for fixing operational bugs that you might face afterwards.

For Non-DBA readers the process of patching takes two phases:

Phase 1: Patching the database on binary level

Phase 2: Patching the database it-self from SQL level

Of course there are pre-requisite steps such as downloading the patch itself,  downloading the latest OPatch utility (java based utility)…I have blogged many times about “patching process”and you can check my old posts about this topic:

Patching Oracle 21c database – October 2021 CPU [21.4.0.0.211019]
Patching Oracle 19c Database in Windows OS

The new thing I want to shed-light about in this blog/article is the datapatch parameters (really useful) that most technical analysts are not aware of….since by default and based on standard procedure in “Phase 2” we run the following datapatch command while the database is up and running:

cd $ORACLE_HOME/OPatch

./datapatch -verbose

Instead run the help command:

./datapatch –help

And there is a long list of parameters with their explanation….one parameter that is really helpful and interesting is sanity_checks

It will report to you if your database environment is ready for SQL patching or not….this is great to know in advance before any maintenance window you are planning to patch your database system in.

cd $ORACLE_HOME/OPatch

./datapatch -sanity_checks

A long list of output will be generated and displayed and will report to you warning messages …for example in my sandbox environment PDB2 pluggable database is in “mount”state :

Interesting part also, it will check third party security products that are used as for multiple purposes such as DAM (database activity monitoring) and VA (Vulnerability Assessment Scans) and they are IBM Guardium and Imperva

Oracle Database Listener Log Rotation

arcsdegeoLeave a comment

Oracle database listener is the TCP/IP communication protocol that end-connections from different sources (Applications, Services, Client Apps….etc) use to connect to the back-end Oracle database system.

The listener logs every authentication taking place in a listener log file (XML, and TEXT) formats. The listener log text format accumulates in size over time and with the growth of the size of this file this…..it might impact your database authentication performance. In addition, Listener log file is a good source for “forensic” investigation . So, it must be retained some time based on your internal policies and needs.

How to rotate your listener log file ?

Add the following 2 parameters in your listener.ora file ( located traditional in the following path $ORACLE_HOME/network/admin )

LOG_FILE_SIZE_listener_name

LOG_FILE_NUM_listener_name

Reference Documentation for further insights:

https://docs.oracle.com/en/database/oracle/oracle-database/19/netrf/oracle-net-listener-parameters-in-listener-ora.html#GUID-FF94A234-A29C-46AA-8770-4CA1BFB5C27C

Its worth stating that database listener will not rotate automatically….A listener restart is required (not reload).

Oracle data patch error Archived patch directory is empty

arcsdegeoLeave a comment

After you apply patches on the binary-level, the next step is to run “data patch” to apply the patches on SQL-level. However, I faced a situation where when running “data patch” an error was raised as shown below with full output:

Interim patch 33808367 (OJVM RELEASE UPDATE: 19.15.0.0.220419 (33808367)):

Binary registry: Installed

SQL registry: Not installed

Current state of release update SQL patches:

Binary registry:

19.16.0.0.0 Release_Update 220703022223: Installed

SQL registry:

Applied 19.15.0.0.0 Release_Update 220331125408 successfully on 27-MAY-22 01.20.51.384386 AM

Adding patches to installation queue and performing prereq checks…done

Installation queue:

The following interim patches will be rolled back:

33561310 (OJVM RELEASE UPDATE: 19.14.0.0.220118 (33561310))

Patch 34133642 (Database Release Update : 19.16.0.0.220719 (34133642)):

Apply from 19.15.0.0.0 Release_Update 220331125408 to 19.16.0.0.0 Release_Update 220703022223

The following interim patches will be applied:

33808367 (OJVM RELEASE UPDATE: 19.15.0.0.220419 (33808367))

Error: prereq checks failed!

patch 33561310: Archived patch directory is empty

Prereq check failed, exiting without installing any patches.

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

/opt/db/cfgtoollogs/sqlpatch/sqlpatch_2555891_2022_09_02_01_11_38/sqlpatch_invocation.log

for information on how to resolve the above errors.

Why This Happened ?

when you apply JAVA Patch (JVM) the tool will automatically roll-back the previous patch first and then apply the new one, the tool couldn’t find metadata information regarding previous patch for JVM component under sqlpatch directory (it seems it was deleted !) so the error thrown was:

Archived patch directory is empty

so what should you in this case ?

my recommendation is to roll-back the current java patch that is installed on the binary-level (you can check that using $ORACLE_HOME/OPatch/opatch lsinventory command to find the patch id OR using dba_registry_sqlpatch view)

after that run the rollback-command:

$ORACLE_HOME/OPatch/opatch rollback -id xxxxxx

Then, re-apply the “old” JVM patch, and the new JVM together at one shot on the binary level (of course applying the old JVAM patch on the binary level will the create the missing metadata directory under “sqlpatch”) ……after that re-run data patch:

cd $ORACLE_HOME/OPatch

./datapatch -verbose

I Hope This Helps !

Installing Workspace Manager in Oracle 19c

arcsdegeoLeave a comment

**** Installation Step:

sqlplus / as sysdba

SQL> @$ORACLE_HOME/rdbms/admin/owminst.plb

**** Installation Verification:

SQL> select dbms_wm.getWorkspace from dual;

GETWORKSPACE

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

LIVE

SQL> select comp_name,status from dba_registry where comp_name like ‘%Workspace%’;

COMP_NAME

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

STATUS

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

Oracle Workspace Manager

VALID

Oracle 19c Spatial and ORA-600 [kdsgrp1-kdsgrp] error

arcsdegeoLeave a comment

if your database system is based on Oracle 19c version (which should be by now since 19c is the long term supported release) and your database system is using Oracle Spatial component (to check if its installed is through querying DBA_REGISTERY view) you might be hitting ORA-600 error.

if you check your alert log and trace files you might be hitting: ORA-600 [kdsgrp1-kdsgrp]

Oracle is releasing “Spatial patch bundles” that gets released AFTER the regular RU (Release Update) that gets released on quarterly basis.

This error ORA-600 [kdsgrp1-kdsgrp] is an indication of potential index corruption (domain index). So you will need t apply the latest Spatial Patch bundle associated with your current version and then rebuild indexes.

of course its always a good idea to open a case with Oracle with ORA-600 related errors, and for reference: Doc ID 2514624.1

How to View/List All ORA-XXXX Errors in your Oracle Database System

arcsdegeoLeave a comment

As you might know Oracle database system throws exception through ORA-XXXX message formats which has a meaning for the problem/issue you are facing.

Ever wondered how can you view/list all of these predefined exceptions in Oracle….I will show you by giving you an example of account being in a Locked state and trying to authenticate/connect against it you will face the famous ORA-28000 error exception:

The list of ORA-XXXX errors are defined in a file called “oraus.msg” under $ORACLE_HOME/rdbms/mesg directory:

Oracle Database Immutable Tables

arcsdegeoLeave a comment

Immutable tables is a new Oracle database feature introduced in 19c lately and they are “read-only” tables that prevent unauthorized data modifications. The objective of having immutable tables is to have “insert only” table to protect against insider threat (someone with access attempting to modify data) and external hackers. Moreover, immutable table must have retention period both for the immutable table and for rows within the immutable table. Rows become obsolete after the specified row retention period. Only obsolete rows can be deleted from the immutable table.

If you are wondering by now what is the difference between Blockchain tables and Immutable Tables, you can first read my blog post about blockchain tables here : https://geodatamaster.com/2021/05/08/oracle-database-19c-blockchain-tables/

Also, Oracle has a good summarized table comparison: https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-tables.html#GUID-F537DD20-58ED-450A-837C-372E3BDA12F9

Example of creating immutable table (that can’t be dropped for 15 days, and inserted row can’t be deleted until 20 days are past from the time of insertion):

SQL> CREATE IMMUTABLE TABLE HR.trade_ledger (id NUMBER, luser VARCHAR2(40), value NUMBER)

       NO DROP UNTIL 15 DAYS IDLE    

       NO DELETE UNTIL 20 DAYS AFTER INSERT;

SQL> insert into HR.trade_ledger values (‘100′,’Emad’,’2210′);

SQL> commit;

If you attempt to delete records from the table, as expected an error will be thrown:

SQL> delete from HR.trade_ledger ;

ORA-05715: operation not allowed on the blockchain or immutable table

You can change/modify retention period of a table to a “higher” value:

SQL> ALTER TABLE HR.trade_ledger NO DROP UNTIL 20 DAYS IDLE;