Oracle Database 12c -- ORA-01549: tablespace not empty, use INCLUDING CONTENTS option

Problem

While dropping a tablespace, you receive the ORA-01549: tablespace not empty, use INCLUDING CONTENTS option error. 

At the same time, you check for objects in the tablespace with DBA_SEGMENTS, and see no objects.

Solution

There might be objects assigned to the tablespace with DEFERRED SEGMENT CREATION option. In this case, segments for objects have not yet been created, but still bound to the tablespace

To identify those objects use the query below:

select * from dba_objects 
where object_id in (
    select object_id from sys.sys_objects 
    where ts_number = (
        select ts# from v$tablespace where name = '<tbs_name>'
    )
);

 

Oracle Database 12c -- WRH$_LATCH_CHILDREN top consumer in SYSAUX

Problem

Objects WRH$_LATCH_CHILDREN_PK and WRH$_LATCH_CHILDREN takes considerable amount of space, and top space consumers of the SYSAUX tablespace, yet keep growing 

Solution

1) Check the statistics_level parameter, probably set to ALL. In this case huge amount of data being gathered and kept, and hence the objects' growth

Set the parameter to TYPICAL

alter system set statistics_level=typical scope=both;

2) AWR retention period. Large periods keep more data. Reduce the retention to the minimum required:

select * from dba_hist_wr_control 
where dbid = (select dbid from v$database);

declare
    p_retention_days number := 14;
    p_retention_minutes number := p_retention_days*24*60;
begin
    DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( 
        retention => p_retention_minutes 
    );   
end;

3) Purge old snapshots which fall outside the newly set retention policy:

Identify min and max snaphosts: 

select min(snap_id), max(snap_id)
from dba_hist_snapshot where dbid = (select dbid from v$database)
and end_interval_time < sysdate - 15;

Purge the snapshot range:

begin
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
   low_snap_id    =><min_snap_id>,
   high_snap_id   =><max_snap_id>);
end;

 

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