Oracle Database 11g -- Extent Map for a Datafile

Problem

You want to see the contiguous blocks of used and free space of a datafile, to estimate if it worth to move objects from the end of a datafile to perform datafile shrinking 

Solution

Use the below query to see the contiguous free and used blocks in a datafile:

select group_id block_id, type, sum(blocks) blocks, sum(blocks)*8192 bytes
from (
    select nvl(group_id , lag(group_id ignore nulls) over (order by block_id)) group_id, type, blocks
    from (
        select case when type = prev_type and c1 = 0 then null else block_id end group_id, t.* 
        from (
            select type, block_id, blocks, lag(type, 1, type) over (order by block_id) prev_type, block_id  - lag(next_block_id) over (order by block_id) c1
            from (
                select 'XXXX' type, block_id, blocks, block_id+blocks next_block_id from dba_extents where file_id=<file_id>
                union all
                select 'OOOO', block_id, blocks, block_id+blocks bext_block_id from dba_free_space where file_id=<file_id>
            ) where rownum>0
        ) t where rownum>0
    ) t where rownum>0
) t 
group by group_id, type
order by group_id ;

XXXX - will stand for used blocks

OOOO - will stand for free blocks 

Oracle Database 12c -- SYS.AQ_SRVNTFN_TABLE_1 Top Space Consumer

Problem

Segment SYS.AQ_SRVNTFN_TABLE_1 has grown too much, and is top space consumer of the SYSTEM tablespace

Solution

SYS.AQ_SRVNTFN_TABLE_1 is a queue table, used by system queue SYS.AQ_SRVNTFN_TABLE_Q_1. This queue is used for AQ PL/SQL notifications.

When there is a PL\SQL procedure subscription for an application queue, Oracle will place a notification message in the SYS.AQ_SRVNTFN_TABLE_Q_1 queue, and will call a callback procedure.

Verify if there are any subscriptions:

SQL>  select * from dba_subscr_registrations;

Check if the subscriber is getting notifications, counters should increment:

SQL> select * from v$subscr_registration_stats;  

Check if  SYS.AQ_SRVNTFN_TABLE_Q_1 grows with:

SQL> select * from v$aq;

There are two reasons for the SYS.AQ_SRVNTFN_TABLE_Q_1 queue to grow:

1) the callback procedure does not work properly and does not dequeue messages from the application queue

2) there is unsynchronization between messages in the application queue and in the SYS.AQ_SRVNTFN_TABLE_Q_1

To fix the problem follow the steps:

1) Unregister the callback procedure, that is, remove the notification subscription using DBMS_AQ.UNREGISTER procedure

2) Start purging the notification queue as SYS user:

DECLARE
   po dbms_aqadm.aq$_purge_options_t;
BEGIN
   po.block := FALSE;
   DBMS_AQADM.PURGE_QUEUE_TABLE(
     queue_table     => 'AQ_SRVNTFN_TABLE_1',
     purge_condition => NULL,
     purge_options   => po);
END;
/ 

3) At this time, the purging might get blocked by the dbms_scheduler jobs, identify those and kill:

SQL> select * from dba_scheduler_running_jobs where job_name like 'AQ$_PLSQL_NTFN%'; 

4) Once blocking jobs killes, the purging from step 2) will succeed 

 

 

 

Oracle Database 19c -- Enable LREG Process Trace

Problem

You want to enable tracing for LREG process to identify the issues with dynamic registration of services with the listener

Solution

To enable tracing: 

alter system set events 'trace[LREG] disk highest';

alter system set events = 'immediate trace name LREG_STATE level 3';

To disable tracing:

alter system set events 'trace[LREG] disk disable';

 

Oracle Database 12c -- V$RECOVERY_FILE_DEST shows wrong results

Problem

Column SPACE_USED of V$RECOVERY_FILE_DEST shows wrong information, either more or less than the actual size of the files on the file system

Solution

To reset the information in V$RECOVERY_FILE_DEST use:

SQL> alter session set events 'immediate trace name kra_options level 1';

Optionally after that, execute:

SQL> execute dbms_backup_restore.refreshagedfiles;

 

SSL Connection Fails with SSL routines:final_renegotiate:unsafe legacy renegotiation disabled

Problem

SSL connection fails with and error: 

SSL routines:final_renegotiate:unsafe legacy renegotiation disabled:ssl/statem/extensions.c:948

openssl is used

Solution

Save the below script as a file and use it when making an SSL connection:

openssl_conf = default_conf
[ default_conf ]
ssl_conf = ssl_sect
[ssl_sect]
system_default = ssl_default_sect
[ssl_default_sect]
Options = UnsafeLegacyRenegotiation

OPENSSL_CONF=/path/to/the/config/file/above <command>

 

Oracle Database 19c -- Enable Tracing for an impdp Job

Problem

When an impdp job fails with an error, you want to identify the failing statement

Solution

Enable tracing for the impdp job with: 

SQL> ALTER SYSTEM SET EVENTS '14401 trace name errorstack level 3';
SQL> ALTER SYSTEM set MAX_DUMP_FILE_SIZE= unlimited ;
SQL> ALTER SYSTEM set TIMED_STATISTICS = true; 

Re-run the impdp job.

To disable tracing:

SQL> ALTER SYSTEM SET EVENTS '14401 trace name errorstack off';

 

Oracle Database 19c -- impdp fails with ORA-14401 inserted partition key is outside specified partition

Problem

Importing data into an Oracle Database 19c with impdp fails with the errors like:

ORA-31693: Table data object <table> failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-14401: inserted partition key is outside specified partition 

The table is an interval-range-partitioned table

Solution

Use the  DATA_OPTIONS=DISABLE_APPEND_HINT option of impdp

Oracle Restart 19 -- roothas.sh fails with CLSRSC-752: incorrect invocation of script

Problem

Deconfiguring (roothas.sh -deconfig -force) or configuring (roothas.sh) Oracle Restart might fails with the error like:

CLSRSC-752: incorrect invocation of script roothas.pl called on a Grid Infrastructure cluster node

Died at <GI HOME>/crs/install/crsutils.pm line <line_no>

Solution

A probable cause of the issue might be the leftover checkpoint file from the previous operations: 

$ORACLE_BASE/crsdata/<HOSTNAME>/crsconfig/ckptGridHA_<HOSTNAME>.xml

Remove, if exists, that file and try to deconfigure/configure again

Oracle Restart 19 -- Managing a Database Fails with PRCR-1055 : Cluster membership check failed

Problem

When using Oracle Restart, and trying to add, start, stop, or check status of an Oracle Database, you get the errors below:

$ srvctl status database -db <db_unique_name>
PRCD-1024 : Failed to retrieve instance list for database <db_unique_name>
PRCR-1055 : Cluster membership check failed for node <hostname> 

Solution

Most probably the cause of the error is that the HOSTING_MEMBERS attribute is set:

# $GRID_HOME/grid/bin/crsctl stat res ora.<db_unique_name>.db -f | grep HOSTING_MEMBERS

HOSTING_MEMBERS=<some_hostname>

To fix the issue, reset the attribute:

# $GRID_HOME/grid/bin/crsctl modify resource "ora.<db_unique_name>.db" -attr "HOSTING_MEMBERS=" -unsupported

 

Oracle Restart 19 -- roothas.sh fails with CLSRSC-762: Empty site GUID for the local site name

Problem

After cloning Oracle Restart to another host, reconfiguration might fail with the errors: 

# $GRID_HOME/grid/crs/install/roothas.sh

Using configuration parameter file: /u01/app/19.0.0.0/grid/crs/install/crsconfig_params
The log of current session can be found at:
  /u01/app/grid/crsdata/<host>/crsconfig/roothas_2025-05-23_10-47-04AM.log
2025/05/23 10:47:06 CLSRSC-363: User ignored prerequisites during installation
2025/05/23 10:47:07 CLSRSC-762: Empty site GUID for the local site name <host>.
Died at /u01/app/19.0.0.0/grid/crs/install/crsutils.pm line 16443.
The command '/u01/app/19.0.0.0/grid/perl/bin/perl -I/u01/app/19.0.0.0/grid/perl/lib -I/u01/app/19.0.0.0/grid/crs/install -I/u01/app/19.0.0.0/grid/xag /u01/app/19.0.0.0/grid/crs/install/roothas.pl ' execution failed

Solution

The common reason for this error, that file 

$GRID_HOME/grid/crs/install/crsgenconfig_params

stores references for the older host, and reconfiguration is happening on the newer host

As a solution, remove the file and try configuration again:

# rm -f $GRID_HOME/grid/crs/install/crsgenconfig_params