Hi all,
As you know, this week we had the 1st GUORS Meetup from 2019 at TecnoPUC – Porto Alegre/RS. It was a great afternoon with great discussions, one of them proposed by our college author here on Grepora: Maiquel Oliveira.
Great Session, Maiquel!
Hi all,
So, I got contacted by a client in emergency because no more sessions were being opened on database with error ORA-02002. Even worse, everything stopped working and started raising this error.
Unusual, right?
So , checking on this trace generated for error here is what I found:
ORA-02002: error while writing to audit trail ORA-00604: error occurred at recursive SQL level 1 ORA-01653: unable to extend table SYS.AUD$ by 8 in tablespace AUDIT_TBS
Seems client has moved the AUD$ to another tablespace to avoid filling SYSTEM, just like described in this post. The tablespace got full, however, due bad monitoring, it got full.
As the auditing facility was unable to write to the AUDIT_TRAIL table, SQL statements that were currently being audited also failed.
What to do?
To avoid application back and forth, I did it in restrict mode, until get all fixed.
To avoid this sort of issue:
Hope it helps,
Cheers!
Hi all,
Stay sharp for the first event on GUORS this year!
The event is tacking place next April 24th, Thursday, at 13h15 in TecnoPUC – Porto Alegre/RS. The topics are:
• Value of Data in Companies – Renato Eric – Oracle
• Implementing #BigData #Analytics #DataScience with Oracle GoldenGate – Maiquel de Oliveira – Sicredi
• Virtualizing Oracle Databases with ACFS/gDBClone – Ricardo Martins – Dell
Subscribe Now! See you there!
Hi all,
Just sharing an experience here. Recently in a migrated 12.2 environment I created a Scheduler Job and it simply disappeared. How come?
Well, seems since 12.2 the AUTO_DROP attribute is set to true by default. I absolutely don’t know why. So I had to recreate the job and set auto_drop to false, simple like this.
I couldn’t find any related bug on MOS, but I’d relate this as a bug, once the default behavior have changed with no prior information.
So, now on, I strongly suggest you to check on your job AUTO_DROP attribute after job creation.
Here is a test case that reproduces the problem:
In a 12.2 database:
begin
DBMS_SCHEDULER.create_job (
job_name => 'TEST_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN null; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'SYSTIMESTAMP + INTERVAL ''10'' SECOND',
end_date => NULL,
enabled => false,
comments => 'Test job');
end;
/
select ENABLED, AUTO_DROP, STATE, RUN_COUNT, FAILURE_COUNT,DEFERRED_DROP from dba_scheduler_jobs where job_name = 'TEST_JOB';
-- Job should appear here
begin
dbms_scheduler.enable('TEST_JOB');
dbms_scheduler.run_job('TEST_JOB',false);
end;
/
select ENABLED, AUTO_DROP, STATE, RUN_COUNT, FAILURE_COUNT,DEFERRED_DROP from dba_scheduler_jobs where job_name = 'TEST_JOB';
-- There is no job now in 12.2.
You can take the AUTO_DROP easily from the query above, or using getting the job_ddl and you’ll see the AUTO_DROP set to true, as in the bottom of this page by Tim Hall.
Hope it helps!
Cheers!
Hi all,
So this week I started receiving this error in a client environment. This was happening due Bug 26803191 – Getting ORA-00600 [ipc_recreate_que_2] instead of ORA-27515 – superseded (Doc ID 26803191.8)
In summary, failures due to low memlock limit are leading ORA-00600 [ipc_recreate_que_2] instead of ORA-27515, patching ensure that ORA-27515 is raised on failures due to low memlock limit instead of this ORA-600.
As workaround, the memlock limited can be changed, as also described in MOS ORA-00600: Internal Error Code, Arguments: [ipc_recreate_que_2] When Running Exachk (Doc ID 2480088.1).
Before:
[boesing@exa1dbadm01 trace]$ grep memlock /etc/security/limits.conf |grep oracle # - memlock - max locked-in-memory address space (KB) oracle soft memlock 237809520 oracle hard memlock 237809520
After:
[boesing@exa1dbadm01 ~]$ grep memlock /etc/security/limits.conf # - memlock - max locked-in-memory address space (KB) oracle soft memlock unlimited oracle hard memlock unlimited * soft memlock 32768 * hard memlock 32768
Hope it helps,
Cheers!
Hi all,
So a couple weeks ago I was facing the following on a client environment. It was appearing every 10 mins on alert.log:
ORA-00600: internal error code, arguments: [13011], [7907], [12679954], [1], [13461738], [0], [], [], [], [], [], [] ORA-01403: no data found ORA-06512: at "SYS.DBMS_AQ_INV", line 1248
I could find several references to ORA-600 [13011], always related to some internal corruption. As this table is related to Advanced Queueing, decided to check on Scheduler Job table structures:
SQL> analyze table SYS.SCHEDULER$_EVENT_LOG validate structure cascade; Table analyzed. SQL> analyze table SYS.SCHEDULER$_JOB_RUN_DETAILS validate structure cascade; analyze table SYS.SCHEDULER$_JOB_RUN_DETAILS validate structure cascade * ERROR at line 1: ORA-01499: table/index cross reference failure - see trace file SQL> analyze table SYS.SCHEDULER$_JOB_RUN_DETAILS validate structure; Table analyzed.
Ahá!
Also on the generated trace file:
2019-03-01 22:26:37.736 SESSION ID:(39.32751) 2019-03-01 22:26:37.736 CLIENT ID) 2019-03-01 22:26:37.736 SERVICE NAME:(SYS$USERS) 2019-03-01 22:26:37.736 MODULE NAME:(sqlplus.exe) 2019-03-01 22:26:37.736 CLIENT DRIVER:(SQL*PLUS) 2019-03-01 22:26:37.736 ACTION NAME) 2019-03-01 22:26:37.736 CONTAINER ID:(1) 2019-03-01 22:26:37.736 Table/Index row count mismatch table 273184 : index 275017, 1832 Index root = tsn: 1 rdba: 0x00c0128a
So ANALYZE on table SCHEDULER$_JOB_RUN_DETAILS fails with CASCADE but succeeds without CASCADE. This means that there is a problem with one of the index of this table. It has gone out of sync with the table.
SQL> select 'alter index '||owner||'.'||index_name||' rebuild;' from dba_indexes where table_name='SCHEDULER$_JOB_RUN_DETAILS'; 'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILD;' -------------------------------------------------------------------------------- alter index SYS.I_SCHEDULER_JOB_RUN_DETAILS rebuild; SQL> alter index SYS.I_SCHEDULER_JOB_RUN_DETAILS rebuild online; Index altered.
After this, error solved and no more recurrence of that ORA-600.
Also note I couldn’t find any document about this on MOS, so this is kind of exclusive by now. 🙂
Cheers!