1. Oracle Database Schedulers jobs not running

Database scheduler jobs are not running after the Linux database server date change. Seems there was an internal lock happens in DB. to Fix disable the dbms Jobs with oracle internal parameter and enabled it and check.

Make a Note of the current value of parameter “job_queue_processes”
show parameter job_queue_processes
1000

Disable ALL DBMS_JOBS
alter system set job_queue_processes=0 scope=both sid=’*’;

Enable ALL DBMS_JOBS
alter system set job_queue_processes=1000 scope=both sid=’*’

For DBMS_SCHEDULER JOBS do below
select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE;
— Make a note of above
Disable ALL DBMS_SCHEDULER JOBS
exec dbms_scheduler.set_scheduler_attribute(‘SCHEDULER_DISABLED’,’TRUE’);

Enable ALL DBMS_SCHEDULER JOBS
exec dbms_scheduler.set_scheduler_attribute(‘SCHEDULER_DISABLED’,’FALSE’);

2. ORA-29548: Java system class reported: release of Java system classes in the database

Error while running java after oracle 11g database upgrade on OCI DBaas Instance

SELECT dbms_java.longname(‘TEST’) long_name FROM dual;

1)Check the current value of _system_trig_enabled : connect / as sysdba select x.ksppinm name, y.ksppstvl value, ksppdesc description from x$ksppi x, x$ksppcv y where x.inst_id = userenv(‘Instance’) and y.inst_id = userenv(‘Instance’) and x.indx = y.indx and x.ksppinm = ‘_system_trig_enabled’; 2)Check the current value of java_jit_enabled show parameter java_jit_enabled 3)Now execute the below step as sysdba : alter system set “_system_trig_enabled” = false scope=memory; alter system set java_jit_enabled=false; create or replace java system / 4)Revert the value of _system_trig_enabled and java_jit_enabled. 5)Run utlrp.sql to validate any invalid Java objects. @?/rdbms/admin/utlrp.sql

3. Unable to compile Package

Select * from dba_blockers;

SELECT s.sid,s.serial#,
l.lock_type,
l.mode_held,
l.lock_id1,S.SECONDS_IN_WAIT
FROM dba_lock_internal l, v$session s
WHERE s.sid = l.session_id
AND UPPER(l.lock_id1) LIKE ‘%&package_name%’
AND l.lock_type = ‘Body Definition Lock’ order by S.SECONDS_IN_WAIT;

select ‘alter system kill session ”’ || s.SID || ‘,’ || s.SERIAL#||””||’ immediate;’||’ ‘||s.seconds_in_wait
FROM dba_lock_internal l, v$session s
WHERE s.sid = l.session_id
AND UPPER(l.lock_id1) LIKE ‘%&package_name%’
AND l.lock_type = ‘Body Definition Lock’
ORDER BY S.SECONDS_IN_WAIT
;

4,ORA-04030: out of process memory when trying to allocate 201847728 bytes (kxs-heap-w,krvxlogact)

Solution

Oracle MOS:
PLSQL Procedure Causing ORA-04030: (pga heap,control file i/o buffer) And ORA-04030:
(koh-kghu sessi,pmuccst: adt/record) or ORA-04030: (koh-kghucall ,pmucalm coll) Errors (Doc ID 1325100.1)

Either Change the page count (memory map entries per process) limit at the OS level, or adjust realfree heap pagesize at the database level:
Change the page count at the OS level:
more /proc/sys/vm/max_map_count
sysctl -w vm.max_map_count=262144 (for example)

Adjust the realfree heap pagesize within the database by setting the following parameters in the init/spfile and restart the database.

For versions 11.2.0.4 and lower:
_use_realfree_heap=TRUE
_realfree_heap_pagesize_hint = 262144

For 12.1 and higher:
_use_realfree_heap=TRUE
_realfree_heap_pagesize = 262144

5. ORA-00604: error occurred at recursive SQL level 1 ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 8 ORA-01086: savepoint ‘XXXXX’ never established in this session or is invalid ORA-06512: at “XXXX.XXXXX, line 4324 ORA-01591: lock held by in-doubt distributed transaction 11.28.2257973 ORA-06512: at line 1

Solution

Preparing rollback commands for pending processes:

select ‘rollback force ”’||local_tran_id||”’;’ from DBA_2PC_PENDING where state=’prepared’;

rollback force ‘11.28.2257973’;

execute as root user

Preparing commit commands for pending operations:

select ‘rollback commit ”’||local_tran_id||”’;’ from DBA_2PC_PENDING where state=’prepared’;

—– If no transaction is there in DBA_2PC_PENDING may be the transaction is stuck not cleared the internal tables and check X$KTUXE

if row coming u can follow below steps to clear

DELETE FROM SYS.PENDING_TRANS$ WHERE LOCAL_TRAN_ID = ‘11.28.2257973’;
DELETE FROM SYS.PENDING_SESSIONS$ WHERE LOCAL_TRAN_ID =’11.28.2257973′ ;
DELETE FROM SYS.PENDING_SUB_SESSIONS$ WHERE LOCAL_TRAN_ID = ‘11.28.2257973’;
COMMIT;

ROLLBACK FORCE ‘11.28.2257973’;

If no rows got deleted then only continue to follow the steps mentioned below

— Inserting some dummy transaction to the table and clear
ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY;

INSERT INTO PENDING_TRANS$ (LOCAL_TRAN_ID, GLOBAL_TRAN_FMT, GLOBAL_ORACLE_ID, STATE, STATUS, SESSION_VECTOR, RECO_VECTOR, TYPE#, FAIL_TIME,RECO_TIME)
VALUES
(
‘11.28.2257973’,
306206,
‘XXXXXXX.12345.1.2.3’,
‘prepared’,’P’,
hextoraw( ‘00000001’ ),
hextoraw( ‘00000000’ ),
0, sysdate, sysdate
);

INSERT INTO PENDING_SESSIONS$
VALUES
(
‘11.28.2257973’,
1, hextoraw(‘05004F003A1500000104’),
‘C’, 0, 30258592, ”,
146
);
commit;

Then u can find some dummy records —-

SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID, STATE, MIXED, COMMIT# FROM DBA_2PC_PENDING;

LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE MIX COMMIT#
11.20.3518273 XXXXXXX.12345.1.2.3 prepared no

DELETE FROM SYS.PENDING_TRANS$ WHERE LOCAL_TRAN_ID = ‘11.28.2257973’;
DELETE FROM SYS.PENDING_SESSIONS$ WHERE LOCAL_TRAN_ID =’11.28.2257973′ ;
DELETE FROM SYS.PENDING_SUB_SESSIONS$ WHERE LOCAL_TRAN_ID = ‘11.28.2257973’;
–optional

ROLLBACK FORCE ‘11.28.2257973’;
COMMIT FORCE ‘11.28.2257973’;
EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(‘11.28.2257973’);

ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY;
ALTER SYSTEM SET “_smu_debug_mode” = 4;
COMMIT;
EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(‘11.28.2257973’);
ALTER SYSTEM SET “_smu_debug_mode” = 0;
COMMIT;

SELECT * FROM X$KTUXE WHERE KTUXEUSN=11 AND KTUXESLT=28 AND KTUXESQN =2257973;
select * from DBA_2PC_PENDING where state=’prepared’;

Now both query should return no records

6. How to check OMF is enabled or not

There exists a hidden initialization parameter _OMF that can be either enabled or disabled

col Parameter format a40
col “Instance Value” format a20
select KSPPINM “Parameter”,
c.ksppstvl “Instance Value”
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and a.ksppinm like ‘%_omf%’;

Parameter Instance Value
_omf enabled

col name format a25
col value format a10
select x.ksppinm name, y.kspftctxvl value, y.kspftctxdf isdefault, decode(bitand(y.kspftctxvf,7),1,’MODIFIED’,4,’SYSTEM_MOD’,’FALSE’) ismod,
decode(bitand(y.kspftctxvf,2),2,’TRUE’,’FALSE’) isadj from sys.x$ksppi x, sys.x$ksppcv2 y where x.inst_id = userenv(‘Instance’)
and y.inst_id = userenv(‘Instance’) and x.indx+1 = y.kspftctxpn
and x.ksppinm like ‘%omf%’ ;

NAME VALUE ISDEFA ISMOD ISADJ
_omf enabled TRUE FALSE FALSE