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 ~]$

Fixing Physical Standby after clonning Primary Database

This is a common scenario where the server of the Primary Database is clonned for testing purposes or to create a Dev environment for developers. When the server is clonned all the data is clonned but also all the parameters of the instance. Including the parameter "log_archive_dest_*" and the parameter "log_archive_config". When the new clonned database is started up, The database instance is started and the database is open with "OPEN RESETLOGS" this creates a new Incarnation in the clonned database. Once the database is open, it starts sending archived logs to the standby, at the same time that the original primary database is also sending archivelogs. The result of this is that the standby database gets the new incarnation and switch itself to be an standby database of the clonned database.

 

 

How to fix it?

These are the steps that I usually follow:

Remove data guard parameters from the clonned database instance:

alter system set dg_broker_start=false scope=both;
alter system reset log_archive_dest_2 ; (it can be other number)
alter system reset log_archive_config;

Removing data guard configuration files in the clonned database:

rm -rf /oracle/DB1/121/dbs/dr1DB1.dat
rm -rf /oracle/DB1/121/dbs/dr2DB1.dat

Rebooting the clonned database:

shutdown immediate;

startup;

Ensure the standby database is not open in read-only:

select open_mode from v$database;

List incarnations in the Standby Database:

 RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       DB1      1685885605       PARENT  1          20-APR-17
2       2       DB1      1685885605       CURRENT 10009258336 08-APR-22

Reset the incarnation in Standby Database:

RMAN> reset database to incarnation 1;

database reset to incarnation 1

Verify the current incarnation:

RMAN>  list incarnation;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       DB1      1685885605       CURRENT 1          20-APR-17
2       2       DB1      1685885605       ORPHAN  10009258336 08-APR-22

Reboot the Standby Database:

shutdown immediate;
startup nomount;
alter database mount standby database;