Oracle Database Immutable Tables

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;

Oracle datapump error ORA-39077: unable to subscribe agent KUPC$A_1_083325450472000

In an Oracle 19c database, I noticed export datapump is failing…after inspecting the logs, the following were the errors:

Oracle datapump error ORA-39077: unable to subscribe agent KUPC$A_1_083325450472000

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

ORA-31626: job does not exist

ORA-31638: cannot attach to job HOT_DB_EXPORT for user SYS

ORA-06512: at “SYS.KUPV$FT”, line 1142

ORA-06512: at “SYS.KUPV$FT”, line 1744

ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95

ORA-06512: at “SYS.KUPV$FT_INT”, line 498

ORA-39077: unable to subscribe agent KUPC$A_1_083325450472000 to queue “KUPC$C_1_20220308083324_0”

ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95

ORA-06512: at “SYS.KUPC$QUE_INT”, line 294

ORA-00972: identifier is too long

ORA-06512: at “SYS.DBMS_AQADM_SYS”, line 9306

ORA-06512: at “SYS.DBMS_PRVTAQIS”, line 1873

ORA-06512: at “SYS.DBMS_PRVTAQIS”, line 3802

ORA-06512: at “SYS.DBMS_RULE_ADM”, line 296

ORA-06512: at “SYS.DBMS_RULEADM_INTERNAL”, line 106

The solution is to recreate datapump related catalogs manually:

connect to the database as SYSDB

sqlplus / as sysdba

SQL> @$ORACLE_HOME/rdbms/admin/catdph.sql

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

SQL> @$ORACLE_HOME/rdbms/admin/catdpb.sql

SQL> @$ORACLE_HOME/rdbms/admin/dbmspump.sql

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

Oracle 19c Upgrade Error ORA-00119: invalid specification for system parameter DISPATCHERS

After upgrading many databases to Oracle 19c, I have faced an interesting error when I reached to the step of switch my environment variables to 19c binaries and starting the database in upgrade mode:

sqlplus / as sysdba

SQL> startup upgrade;

ORA-00119: invalid specification for system parameter DISPATCHERS

ORA-00109: invalid value for attribute PRESENTATION: oracle.aurora.server.SGiopServer

after checking I found out that currently this is the parameter vaule:

SQL> show parameter dispatchers

NAME                                 TYPE        VALUE

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

dispatchers                          string      (PROTOCOL=TCP)(PRE=oracle.auro

                                                 ra.server.SGiopServer)

basically DISPATCHERS configures dispatcher processes in the shared server architecture: https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DISPATCHERS.html

in 19c the expected value is ‘(PROTOCOL=TCP)

so, either use alter system command or update your init.ora file and startup the database again in upgrade mode !

SQL> ALTER SYSTEM SET dispatchers = ‘(PROTOCOL=TCP)’;

Oracle 21c New Feature in SQL*Plus – set jsonprint

New sqlplus feature is introduced in Oracle 21c release sql plus for json output format, to illustrate:

SQL> show jsonprint

jsonprint NORMAL

SQL> select json(‘[{fname:”Emad”},{lname:”al-mousa”}]’) from dual;

JSON(‘[{FNAME:”EMAD”},{LNAME:”AL-MOUSA”}]’)

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

[{“fname”:”Emad”},{“lname”:”al-mousa”}]

SQL> set jsonprint pretty

SQL> show jsonprint

jsonprint PRETTY

SQL> select json(‘[{fname:”Emad”},{lname:”al-mousa”}]’) from dual;

JSON(‘[{FNAME:”EMAD”},{LNAME:”AL-MOUSA”}]’)

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

[

  {

    “fname” : “Emad”

  },

  {

    “lname” : “al-mousa”

  }

]

Patching Oracle 21c database – October 2021 CPU [21.4.0.0.211019]

Oracle 21c has been released on-premise as you might already know and the base version is 21.3, the first RU released for 21c is in October CPU 2021. [21.4.0.0.211019]

First observation when you download the RU for 21c is the following statement in Oracle portal (there is NO separate OVJM patch anymore…its combined in one patch in DB RU)

I have downloaded the latest Opatch utility 12.2.0.1.27 and copied it under my Oracle home in $ORACLE_HOME/OPatch/ directory

scp -r /opt/Opatch_12.2.0.1.27/OPatch/* $ORACLE_HOME/OPatch/

Shutdown your database and listener

Then go the directory where your patch is downloaded

Then execute the OPatch utility:

$ORACLE_HOME/OPatch/opatch apply

After successfully patching the database binaries, we need to perform post patch activity (datapatch):

sqlplus / as sysdba

SQL> startup

SQL> alter pluggable database all open;

SQL> exit;

cd $ORACLE_HOME/OPatch

./datapatch -verbose

Its highly recommend to run utlrp.sql script against CDB$ROOT and in each pluggable database:

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

To check status of your patch activity:

SQL> select status,action,description from dba_registry_sqlpatch;

Remark: As you know starting from 21c only CDB architecture is supported.

And last step is to patch your JDK binaries, to check your current binaries JDK build number:

// to check current version:

$ORACLE_HOME/jdk/bin/java -version

// then replace it with the new released JDK version
 
cd $ORACLE_HOME
 
mv jdk jdk.original
 
mkdir jdk
 
scp -r /opt/JDK-PATCH-OCT-2021/33197565/files/jdk/* $ORACLE_HOME/jdk/
 
$ORACLE_HOME/jdk/bin/java -version

you can then remove the temporary jdk directory: rm -rf $ORACLE_HOME/jdk.original

INS-08101 Unexpected error while executing the action at state: ‘SupportedOSCheck’

The following error was thrown while installing Oracle 19c database in red hat 8:

INS-08101 Unexpected error while executing the action at state: ‘SupportedOSCheck’

The solution:

in your .profile add the following environment variable entry:

export CV_ASSUME_DISTID=’OL7′

This is because 19c is an older released and was compatible with red hat 7 at the time.

location of Oracle SQL Developer Connections File

This is a quick tip…..if you would like to know the location of your Oracle SQL Developer connections, there is a connection file (in JSON format) located:

C:\Users\YOUR_ACCOUNT\AppData\Roaming\SQL Developer\system21.2.0.187.1842\o.jdeveloper.db.connection

in my case the Oracle SQL Developer version is the latest (at the time of writing this blog version 21.2) so you will find system21.2 folder name is referenced in the above path.

File name:

Patching Oracle 19c Database in Windows OS

Here I am going to illustrate the procedure to patch oracle 19c database in windows enthronement, to apply APRIL 2021 CPU patches (19.11) RU (release update).

Download Windows Database Bundle Patch (19c) 19.11 from Oracle Support website.

Download the latest OPatch utility:

Ensure your run CMD as administrator:

Check your environment variables:

set PATH=%ORACLE_HOME%\perl\bin;%PATH%

set ORACLE_HOME=D:\WINDOWS.X64_193000_db_home

set PERL5LIB=

Stop Distributed Transaction Coordinator:

net stop msdtc

Go to the directory where the patch is downloaded

D:

cd D:\19.11.windows_patch\32409154

Apply the patches using the downloaded Opatch utility and copy it under your $ORACLE_HOME/OPatch directory:

D:\WINDOWS.X64_193000_db_home\OPatch\opatch.bat apply

Startup the Oracle services again:

Post-Patch activity:

sqlplus / as sysdba

SQL> startup

SQL> alter pluggable database all open;

SQL> exit;

In CMD session (run it as administrator):

D:

cd D:\WINDOWS.X64_193000_db_home\OPatch\

datapatch -verbose

Then, check the registry that the patch is successfully applied:

select * from dba_registry_sqlpatch;

Oracle Database 19c Blockchain Tables

First, to explore blockchain table in 19c release, you will need to apply the latest patches and change compatibility parameter value (which is unusual), the blockchain feature was introduced initially in 21c and back-ported to 19c with 19.10 RU (release update) in January 2021 CPU. Here I am going to explore it after patching the database to 19.11 (April 2021 CPU).

To raise the compatibility parameter:

I have shutdown the database

SQL> shutdown immediate;

Then, edited the init.ora file and changed compatibility parameter value:

compatible=’19.11.0′

Then, I started up the database:

SQL> startup pfile=’D:\WINDOWS.X64_193000_db_home\dbs\init.ora’

Then, checking the compatibility parameter value:

SQL> SELECT name, value, description FROM v$parameter WHERE name = ‘compatible’;

Let us explore it:

In pluggable database PDB1

CREATE TABLESPACE ledgertbs;

CREATE USER auditor identified by audit_123 DEFAULT TABLESPACE ledgertbs;

GRANT create session, create table, unlimited tablespace TO auditor;

GRANT execute ON sys.dbms_blockchain_table TO auditor;

Connecting to PDB1 database as user “auditor”:

I will create a blockchain table named AUDITOR.LEDGER_EMP that will maintain a tamper-resistant ledger of current and historical transactions in PDB1. Rows can never be deleted in the AUDITOR.LEDGER_EMP blockchain table. The blockchain table can be dropped only after 3 days of inactivity.

SQL> CREATE BLOCKCHAIN TABLE ledger_emp (employee_id NUMBER, employee_name varchar2(15), salary NUMBER)

                     NO DROP UNTIL 3 DAYS IDLE

                     NO DELETE LOCKED

                     HASHING USING “SHA2_512” VERSION “v1”;

Checking user_blockchain_tables to list blockchain table information:

SQL> SELECT *  FROM   user_blockchain_tables ;

I will insert 1 record:

SQL> INSERT INTO ledger_emp VALUES (106,’EMAD’,3000);

SQL> COMMIT;

If I attempt to drop the table:

SQL> drop table ledger_emp;

ORA-05723: drop blockchain or immutable table LEDGER_EMP not allowed

If I attempt to delete all records from the table:

SQL> delete from auditor.ledger_emp;

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

To inspect the row hash value:

SELECT ORABCTAB_CHAIN_ID$ “Chain ID”, ORABCTAB_SEQ_NUM$ “Seq Num”,

               to_char(ORABCTAB_CREATION_TIME$,’dd-Mon-YYYY hh-mi’) “Chain date”,

               ORABCTAB_USER_NUMBER$ “User Num”, ORABCTAB_HASH$ “Chain HASH”,

               employee_id, salary

        FROM   ledger_emp;

There is a parameter called BLOCKCHAIN_TABLE_MAX_NO_DROP

basically this parameter controls the amount of idle time the table must be inactive before

it can be dropped, this parameter is useful if you want to protect from anyone by mistake setting the nodrop option to very high value.

the default value is NONE, based on documentation:

https://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/BLOCKCHAIN_TABLE_MAX_NO_DROP.html#GUID-26AF15B2-5621-4602-AA6E-D92842E4285C

from PDB level:

sqlplus / as sysdba

SQL> alter session set container=PDB1;

SQL> alter system set blockchain_table_max_no_drop=0;

alter system set blockchain_table_max_no_drop=0

*

ERROR at line 1:

ORA-01031: insufficient privileges

from CDB level:

sqlplus / as sysdba

SQL> alter system set blockchain_table_max_no_drop=0;

System altered.

Even though, its stated in the documentation that you can modify it from PDB level ?! (is this a documentation bug)

select

    name,type,value,ISPDB_MODIFIABLE,ISINSTANCE_MODIFIABLE,ISMODIFIED,ISSYS_MODIFIABLE,DESCRIPTION

from

    v$parameter

where

name=’blockchain_table_max_no_drop’;

Now, after setting the parameter to 0 if you attempt to create the block-chain table with number of days more than zero:

SQL> CREATE BLOCKCHAIN TABLE auditor.ledger_emp_n (employee_id NUMBER, employee_name varchar2(15), salary NUMBER)

                         NO DROP UNTIL 4 DAYS IDLE

                         NO DELETE LOCKED

                         HASHING USING “SHA2_512” VERSION “v1”;

CREATE BLOCKCHAIN TABLE auditor.ledger_emp_n (employee_id NUMBER, employee_name varchar2(15), salary NUMBER)

*

ERROR at line 1:

ORA-05747: maximum retention time too high, should be less than or equal to 0