ORA-62009 After enabling Supplemental Logging at Database Level

While configuring an Oracle database with supplemental logging for a tool that replicates data to Snowflake, the users reported the following error that was appearing in the application randomly:

ORA-00604: Error occurred at recursive SQL Level 1

ORA-62009: cannot encode an invalid XML character 0x0 in supplemental log. 

I found that the errors happen in some couple of tables.

The error happens when an INSERT is being executed against one of the problematic tables:

insert into "SCHEMA"."TABLE1"  (q_name, msgid, corrid, priority, state, delay, expiration,   time_manager_info, local_order_no, chain_no, enq_time, step_no, enq_uid,   enq_tid, retry_count, exception_qschema, exception_queue, recipient_key,   dequeue_msgid, user_data, sender_name, sender_address, sender_protocol,   user_prop, cscn, dscn)   values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, 0, :15,         :16, :17, :18, :19, :20, :21, :22, :23, :24, :25)

After enabling tracing to understand what's happening, I was able to find that the error actually is because Oracle is not able to handle the data sent to the column dequeue_msgid, that column is of data type "RAW(16)".

 Bind#18
  oacdty=121 mxl=656(656) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0001 frm=00 csi=00 siz=656 off=0
toid ptr value=2D5AB06D0 length=16
B93D8C7894170953E04019AC227A1D
  kxsbbbfp=7f7ea14d4e08  bln=656  avl=16  flg=15
  value=Unhandled datatype (121) found in kxsbndinf
Dumping '' addr=0x7f7ea14d4e08 size=16 bytes
Dump of memory from 0x7f7ea14d4e08 to 0x7f7ea14d4e18
7F7EA14D4E00                   173E4A00 00000000          [.J>.....]
7F7EA14D4E10 A14D4E18 00007F7E                    [.NM.~...]      

I didn't find any registered bug for this in Oracle Metalink.  

Solution:

Disable Supplemental Logging at Database Level and Enable it at table level for only those tables where you need it. 

How to update parallelism in a resource manager plan

If you have a Resource Manager Plan and you need to limit the usage of parellism  for the users in a  consumer group, you can use the following piece of code.

For this example, I will modify the following resource manager configuration where the consumer group "USER01" is set to use Parallelism = 2 (Check the last column)

 

BEGIN
 dbms_resource_manager.clear_pending_area();
 dbms_resource_manager.create_pending_area();
 
 
 dbms_resource_manager.UPDATE_PLAN_DIRECTIVE (
 plan => 'MY_PLAN',
 group_or_subplan => 'USER01',
 new_mgmt_p1 => 60,
 new_comment  => 'Limit parallel 1 and cpu 60 percent',
 new_parallel_degree_limit_p1 => 1
 );
 
 dbms_resource_manager.validate_pending_area();
 dbms_resource_manager.submit_pending_area();
end;
/ 

 

Verify that the new value has been set successfully (Check the last column):

How to delete a database in silent mode with dbca in Oracle 21c

 

 The following statement can be used to delete a database in Oracle 21c using DBCA in silent mode.

[oracle@localhost ~]$ dbca -silent -deleteDatabase -sourceDB orcl sysDBAUserName sys -sysDBAPassword ********

[WARNING] [DBT-19202] The Database Configuration Assistant will delete the Oracle instances and datafiles for your database. All information in the database will be destroyed.
Prepare for db operation
32% complete
Connecting to database
35% complete
39% complete
42% complete
45% complete
48% complete
52% complete
65% complete
Updating network configuration files
68% complete
Deleting instance and datafiles
84% complete
100% complete
Database deletion completed.
Look at the log file "/u01/app/oracle/product/19.0.0/db_base/cfgtoollogs/dbca/orcl/orcl.log" for further details.
[oracle@localhost ~]$

DBCA will also remove the entry in the file /etc/oratab for that specific database:

[oracle@localhost ~]$ cat /etc/oratab |egrep orcl
[oracle@localhost ~]$