oracle dba

expdp job is failing with the below error:

ORA-39097: Data Pump job encountered unexpected error -21780
ORA-39065: unexpected master process exception in DISPATCH
ORA-21780: Maximum number of object durations exceeded.
ORA-06512: at “SYS.KUPM$MCP”, line 14382
ORA-06512: at “SYS.KUPM$MCP”, line 12886
ORA-06512: at “SYS.KUPM$MCP”, line 12565
ORA-06512: at “SYS.KUPM$MCP”, line 15267
ORA-06512: at “SYS.KUPM$MCP”, line 12619
ORA-06512: at “SYS.KUPM$MCP”, line 15267
ORA-06512: at “SYS.KUPM$MCP”, line 12619
ORA-06512: at “SYS.KUPM$MCP”, line 15267
ORA-06512: at “SYS.KUPM$MCP”, line 12619
ORA-06512: at “SYS.KUPM$MCP”, line 15267
ORA-06512: at “SYS.KUPM$MCP”, line 12619
ORA-06512: at “SYS.KUPM$MCP”, line 15267
ORA-06512: at “SYS.KUPM$MCP”, line 12619
ORA-06512: at “SYS.KUPM$MCP”, line 15267
ORA-06512: at “SYS.KUPM$MCP”, line 12619
ORA-06512: at “SYS.KUPM$MCP”, line 15267
ORA-06512: at “SYS.KUPM$MCP”, line 12619
ORA-06512: at “SYS.KUPM$MCP”, line 15267
ORA-06512: at “SYS.KUPM$MCP”, line 12619
ORA-06512: at “SYS.KUPM$MCP”, line 15267
ORA-06512: at “SYS.KUPM$MCP”, line 12619
ORA-06512: at “SYS.KUPM$MCP”, line 15267
ORA-06512: at “SYS.KUPM$MCP”, line 12619
ORA-06512: at “SYS.KUPM$MCP”, line 15267
ORA-06512: at “SYS.KUPM$MCP”, line 12619
ORA-06512: at “SYS.KUPM$MCP”, line 15267
ORA-06512: at “SYS.KUPM$MCP”, line 12619
ORA-06512: at “SYS.KUPM$MCP”, line 15267
ORA-06512: at “SYS.KUPM$MCP”, line 12619
ORA-06512: at “SYS.KUPM$MCP”, line 15267
ORA-06512: at “SYS.KUPM$MCP”, line 12619

The solution is to add the option exclude=STATISTICS/MARKER .

It can also be because temp file is missing from the temp tablespace

If you are getting this error every minutes or so

2021-06-29T03:06:00.070956+02:00
Exception [type: SIGSEGV, Invalid permissions for mapped object] [ADDR:0x18] [PC:0x10396E150, IPRA.$kkqjpdpvpd()+656] [flags: 0x0, count: 1]
Errors in file /app/oracle/diag/rdbms/uvd2dol/UVD2DOL/trace/UVD2DOL_ora_48562594.trc (incident=78750):
ORA-07445: exception encountered: core dump [kkqjpdpvpd()+656] [SIGSEGV] [ADDR:0x18] [PC:0x10396E150] [Invalid permissions for mapped object] []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

This error is because you have optimizer_features_enable set to 4 position instead of 3.

When setting this parameter on Oracle Database 18c or later, you must specify three numeric values separated by periods, such as 18.1.0 or 19.1.0.

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/OPTIMIZER_FEATURES_ENABLE.html#GUID-E193EC9E-B642-4C01-99EC-24E04AEA1A2C

Solution:
alter system set optimizer_features_enable=’19.1.0′;

It is possible to resize a tablespace datafile is there is some free space at the end of the datafile. This is the quick and easy method. If you have data at the end of the datafile, you will need to move the objects so that segment will be recreated.

View tablespace size with free space

select  df.tablespace_name "Tablespace", df.totalspace "Total MB", totalusedspace "Used MB", (df.totalspace - tu.totalusedspace) "Free MB", round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free" from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name) df, (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name from dba_segments group by tablespace_name) tu where df.tablespace_name = tu.tablespace_name order by 2 desc ;

Generate command to reduce tablespace (specify block size and tablespace name)

select 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents where tablespace_name= '&&tbs_name' 
group by file_id ) b
where a.file_id = b.file_id(+) and a.tablespace_name= '&&tbs_name'
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0;

Q Processes take more and more memory and does not release the memory.
pga_aggregate_limit=0 allows pga to grow unlimited hence the Q processes can consume all server memory.

Temporary workaround is to reset the AQ slaves so that it is not necessary to shutdown/startup the db instance

show parameter aq_tm_processes

--Reset AQ slaves:

connect / as sysdba
alter system set aq_tm_processes=0 scope=memory sid='*' ;

--delete AQ SESSIONS

SELECT 'ALTER SYSTEM KILL SESSION ' || CHR(39) || SID || ',' || SERIAL# || CHR(39) || ' IMMEDIATE;'
FROM V$SESSION 
WHERE TYPE = 'BACKGROUND' AND MODULE = 'Streams' AND ACTION LIKE 'EMON%' AND EVENT = 'EMON slave idle wait'
ORDER BY SID, SERIAL#;

--Start back AQ slaves:

connect / as sysdba
alter system set aq_tm_processes=1 scope=memory sid='*';

Queries to Monitor Q Processes.

--PGA Memory Stat
SELECT name, round(((value/1000)/1024),2) as value_mb, round((((value/1000)/1024)/1024),2) as value_gb FROM V$PGASTAT;

--Find the Q Processes
SELECT SID,SERIAL#,OSUSER,PROCESS,MACHINE,PROGRAM,TYPE,MODULE,ACTION,EVENT 
FROM V$SESSION 
WHERE TYPE = 'BACKGROUND' AND MODULE = 'Streams' AND ACTION LIKE 'EMON%' AND EVENT = 'EMON slave idle wait'
ORDER BY SID, SERIAL#;

--ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
SELECT 'ALTER SYSTEM KILL SESSION ' || CHR(39) || SID || ',' || SERIAL# || CHR(39) || ' IMMEDIATE;'
FROM V$SESSION 
WHERE TYPE = 'BACKGROUND' AND MODULE = 'Streams' AND ACTION LIKE 'EMON%' AND EVENT = 'EMON slave idle wait'
ORDER BY SID, SERIAL#;

--03-memory-usage-in-details.sql - Q Processes
select
    to_char(ssn.sid, '999999')                           as session_id,
    ssn.serial#                                          as session_serial,
    nvl(ssn.username, nvl(bgp.name, 'background'))
    || '::'
    || nvl(lower(ssn.machine), ins.host_name)            as process_name,
    to_char(prc.spid, '999999999')                       as pid_thread,
    to_char((se1.value / 1024) / 1024, '999g999g990d00') as current_size_mb,
    to_char((se2.value / 1024) / 1024, '999g999g990d00') as maximum_size_mb
from
    v$statname    stat1,
    v$statname    stat2,
    v$session     ssn,
    v$sesstat     se1,
    v$sesstat     se2,
    v$bgprocess   bgp,
    v$process     prc,
    v$instance    ins
where
    stat1.name         = 'session pga memory'
    and stat2.name     = 'session pga memory max'
    and se1.sid        = ssn.sid
    and se2.sid        = ssn.sid
    and se2.statistic# = stat2.statistic#
    and se1.statistic# = stat1.statistic#
    and ssn.paddr      = bgp.paddr (+)
    and ssn.paddr      = prc.addr  (+)
    and ssn.sid in (
        select sid
        from v$session
        --where username = 'MY_USERNAME' -- your user/schema name
        where type = 'BACKGROUND' and module = 'Streams' and action like 'EMON%' and event = 'EMON slave idle wait'
    )
order by
    maximum_size_mb desc;

Check also that AQ_SRVNTFN_TABLE_1 is set to dequeue. It is possible that DEQUEUE_ENABLED is set to NO.

AQ_SRVNTFN_TABLE_1
ENQUEUE_ENABLE=YES
DEQUEUE_ENABLED=NO

confirm the status of the queue:

-- Workaround to apply on all CDB and PDB
select ENQUEUE_ENABLED, DEQUEUE_ENABLED from dba_queues where NAME = 'AQ_SRVNTFN_TABLE_Q_1';

select owner, name, queue_table, ENQUEUE_ENABLED, DEQUEUE_ENABLED 
from dba_queues where name = 'AQ_SRVNTFN_TABLE_Q_1' and owner='SYS';

exec dbms_aqadm.stop_queue( 'AQ_SRVNTFN_TABLE_Q_1', true , true );
exec dbms_aqadm.start_queue( 'AQ_SRVNTFN_TABLE_Q_1' , true, true );

Last, times, we noticed high cpu and memory usage on our database server. Top memory consuming process was some ora_j000_* process which are spawned as part of execution of auto task scheduler job . It was using also lots of swap.

After reviewing, we found that the process was running “Optimizer statistics advisor” task name AUTO_STATS_ADVISOR_TASK ( job name ORA$AT_OS_OPT_SY_<…>)

And it was executing following recursive SQL:

SQL_ID: as2dr3ag24gay
select not_stale.obj# from (select s.obj# obj#, count(*) cnt, max(timestamp) max_time from
optstat_snapshot$ s, wri$_optstat_tab_history t where s.obj# = t.obj# and bitand(s.flags, :gather_stats_flag) > 0 and
t.analyzetime = (select max(analyzetime) from wri$_optstat_tab_history where analyzetime < s.timestamp) and
dbms_stats_internal.is_stale(s.obj#, null, null, s.inserts + s.updates + s.deletes, t.rowcnt, 0) is null group by s.obj#) not_stale,
(select obj# obj#, count(*) cnt from optstat_snapshot$ where bitand(flags, :gather_stats_flag) > 0 group by obj#) total,
(select obj#, max(timestamp) max_time from optstat_snapshot$ group by obj#) max where not_stale.obj# = total.obj# and
not_stale.cnt > 0.5 * total.cnt and not_stale.obj# = max.obj# and not_stale.max_time = max.max_time and
dbms_stats_internal.check_advisor_obj_filter(:rule_id, not_stale.obj#, :exec_mode) = 'T'

On oracle support site, we found that the issue is related to the following bug:
Bug 34201470 – JNNN PROCESSES CONSUMING 100% CPU AND HIGH MEMORY DURING AUTOTASK EXECUTION

To resolve the issue, you can apply patch for Bug 34201470
(Please note that this fix above does the same thing the workaround does below, it only turns off AUTO_STATS_ADVISOR_TASK)

 To workaround:  exec dbms_stats.set_global_prefs(‘AUTO_STATS_ADVISOR_TASK’, ‘FALSE’);

Please note that the Statistics Preference AUTO_STATS_ADVISOR_TASK is available ONLY when patch 26749785 is present otherwise, there is nothing that can be done.

Below a little script that will allow to create a user like another one in an easy way

begin
-- This pl/sql block creates a user like another one


declare v_username varchar2(30) := '<username_like>';
v_username_new varchar2(30) := '<new_user>';
v_pwd varchar2(30):= 'ChangeMe123___';
defroles varchar2(500);
user_exists integer;
v_stmt varchar2(500);

begin  


select count(*) into user_exists from dba_users where username=v_username;
execute immediate 'drop user ' || v_username_new;

if user_exists> 0 then

select 'create user ' || v_username_new || '  identified by "' || v_pwd || '" default tablespace '||default_tablespace|| ' temporary tablespace '||temporary_tablespace||' profile '|| profile || ' PASSWORD EXPIRE' into v_stmt
from sys.dba_users
where username = upper(v_username);
dbms_output.put_line (v_stmt);
execute immediate v_stmt;

-- grant roles
begin
for c0 in (select 'grant '||granted_role||' to ' || v_username_new || ' '|| 
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION') as stmt
from sys.dba_role_privs
where grantee = upper(v_username))
loop 
dbms_output.put_line ('v_stmt2' || c0.stmt);
execute immediate c0.stmt;
end loop;
exception when no_data_found then 
dbms_output.put_line ('no data found');
end;

-- grant system privs
begin
for c0 in  (select 'grant '||privilege||' to '|| v_username_new || ' ' ||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION') as stmt
from sys.dba_sys_privs
where grantee = upper(v_username))
loop
dbms_output.put_line (c0.stmt);
 execute immediate c0.stmt;
end loop;
exception when no_data_found then 
dbms_output.put_line ('');
end;

-- grant table privs
begin
for c0 in (select 'grant '||privilege||' on '||owner||'.'||table_name||' to ' || v_username_new  as stmt
from sys.dba_tab_privs
where grantee = upper(v_username)
and type != 'DIRECTORY')
loop
dbms_output.put_line (c0.stmt);
execute immediate c0.stmt;
end loop;
exception when no_data_found then 
dbms_output.put_line ('');
end;

-- grant directory privs
begin
for c0 in (select 'grant '||privilege||' on directory '||owner||'.'||table_name||' to ' || v_username_new  as stmt
from sys.dba_tab_privs
where grantee = upper(v_username)
and type = 'DIRECTORY')
loop
dbms_output.put_line (c0.stmt);
execute immediate c0.stmt;
end loop;
exception when no_data_found then 
dbms_output.put_line ('');
end;

-- tbs quotas
begin
for c0 in (select 'alter user '||username||' quota '||
decode(max_bytes, -1, 'UNLIMITED', max_bytes)||
' on '||tablespace_name  as stmt
from sys.dba_ts_quotas
where username = upper(v_username))
loop
dbms_output.put_line (c0.stmt);
execute immediate c0.stmt;
end loop;
exception when no_data_found then 
dbms_output.put_line ('');
end;

--default role

for c1 in (select granted_role as grrole from dba_role_privs 
             where grantee = upper(v_username)
             and default_role = 'YES'
            )
  loop
     execute immediate 'alter user ' || v_username_new || ' ' || ' default role '|| c1.grrole;
  end loop;
 
 end if;
end;
end;
/

The DBV-201 error is a new error due to ‘soft corruption’ which is caused by invalid redo application (eg: after application of NOLOGGING redo).

To find the object related to the corruption:

Take one of the DBV outputs and find block and file number with DBA block specified:

exec dbms_utility.data_block_address_file(37754116); -- block number
exec dbms_utility.data_block_address_block(37754116); -- file number

Using the file and block number, you can execute the query:

SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id = <file#>
and <block#> between block_id AND block_id + blocks - 1;

use file and block number from previous step.

This will give you the segment/object name.

From the object(s) returned in previous query, if they are tables:

select owner, table_name, logging
from dba_tables
where table_name in
('<list of tables from previous query>');

If indexes:

select owner, index_name, logging, table_owner, table_name
from dba_indexes
where index_name in ('<list of indexes from previous query>');

The LOGGING=NO means the object is using the NOLOGGING feature and as expected, the recovery performed on the objects led to the DBV-201 output.

If you were to query the objects, you would get something like:

ORA-01578: ORACLE data block corrupted (file #9 , block # 121658)
ORA-26040: Data block was loaded using the NOLOGGING option

Solution: The object will need to be recreated.

If you want to ensure the soft corruption does not occur again, ensure tables or indexes are altered so that LOGGING=YES .

WMSYS.LTUTIL is invalid with this error

select owner, name, type, text, line, position from dba_errors where owner = 'WMSYS';
OWNER NAME TYPE TEXT LINE POSITION

WMSYS LTUTIL PACKAGE BODY PL/SQL: ORA-00942: Table ou vue inexistante 7828 22
WMSYS LTUTIL PACKAGE BODY PL/SQL: SQL Statement ignored 7828 7
WMSYS LTUTIL PACKAGE BODY PL/SQL: ORA-00942: Table ou vue inexistante 7838 18
WMSYS LTUTIL PACKAGE BODY PL/SQL: SQL Statement ignored 7838 7 

To fix this issue, simply grant insert delete on noexp$ to WMSYS user and recompile

grant insert,delete on sys.noexp$ to wmsys;
@?/rdbms/admin/utlrp.sql
select * from dba_objects where status='INVALID' and owner='WMSYS';
select * from dba_registry;

RMAN may fail with error RMAN-10002: ORACLE error: ORA-00096: invalid value SIGNATURE for parameter remote_dependencies_mode, must be from among NO_OVERFETCH, OVERFETCH.

The error message is confusing here as remote_dependencies_mode can only take two values: TIMESTAMP or SIGNATURE. The suggested values may be anything else from another parameter.

When running RMAN command, it tries to do an “alter session set remote_dependencies_mode=signature” but fails to do so. This most probably indicates a parameter corruption in memory.

Check current value for remote_dependencies_mode

show parameter remote_dependencies_mode
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
remote_dependencies_mode              string     TIMESTAMP

Try to change the value manually:

alter system set remote_dependencies_mode=signature;

If this is a parameter corruption issue, you will get the same error:

ORA-00096: invalid value SIGNATURE for parameter remote_dependencies_mode,must be from among NO_OVERFETCH, OVERFETCH.

In this case, the solution is to restart the database. This will fix the corruption and you will be able to run rman again.

When starting linux OS and that fsck fails with fsck.ext4: No such file or directory while trying to open /dev/mapper/myvg_mylv, usually the disks are mounted in read only mode.

To solve this issue, you’ll have cancel fsck and login to the console.

Remount / filestem as read only with mount -n -o remount,rw /

After that, you will be able to modify /etc/fstab and remove the entry that does not exists.

Reboot the OS and wait for fsck to finish.