Site Logo

IzzySoft


Das inoffizielle Android-HandbuchDas inoffizielle Android-Handbuch
Für 16,99 € bei Amazon kaufen
Das inoffizielle Android-SystemhandbuchDas inoffizielle Android-Systemhandbuch
Für 6,99 € bei Amazon kaufen
Die besten Android-Apps: Android-Systemtools. Fotografie & Freizeit. Büro-Tools, Schule und StudiumDie besten Android-Apps: Android-Systemtools. Fotografie & Freizeit. Büro-Tools, Schule und Studium
Für 2,94 € bei Amazon kaufen
Stand: 2024-03-28 04:07
Preis & Verfügbarkeit können sich geändert haben.

Miscellaneous stuff

ADRCI: XML Alert Log handling, trace files and more

Already wondered where to the alert_log went with the new-and-shiny Oracle 11g database? It went XML. Ouch? Right, maybe that's the first thing coming to ones mind (it did to mine). How to read that?

Maybe after discovering the right tool for it, one can make friends with the XML log. And it ships right with the installation – it only needs to be found (who reads all the docs?). So in short: adrci is the command. He? Which letter for the XML? None: ADR Command Interpreter. It's more than a simple log file reader. So what's ADR? Automatic Diagnostic Repository.

Reading the log file

Purge log files

Remember the times when the disk ran out of free space, and you discovered the 2.755 trace files plus one huuuuuge alert_log summing up to … OMG??? Oracle 11g and higher take care for their cleanup automatically – by default only the last 720 (short policy) or 8720 (long policy) hours are kept. Again adrci can be used to adjust these:

adrci> set control (SHORTP_POLICY = 360)
adrci> set control (LONGP_POLICY = 720)

SHORTP_POLICY is applied to TRACE, CDUMP, UTSCDMP, IPS – while LONGP_POLICY is applied to ALERT, INCIDENT, SWEEP, STAGE, HM.

More than one database on the same server? Want different policies for each? No problem: The policy is valid for the current home. So you can just cycle through the Oracle Homes and set the parameters as wanted:

adrci> show homes
diag/rdbms/rac112/RAC1122
diag/rdbms/rac112/TEST
adrci> set home diag/rdbms/rac112/RAC1122
adrci> show control
ADR Home = /u01/app/oracle/diag/rdbms/rac112/RAC1122:
*************************************************************************
ADRID       SHORTP_POLICY  LONGP_POLICY  LAST_MOD_TIME        LAST_AUTOPRG_TIME    LAST_MANUPRG_TIME  ADRDIR_VERSION  ADRSCHM_VERSION  ADRSCHMV_SUMMARY  ADRALERT_VERSION  CREATE_TIME
----------- -------------- ------------- -------------------- -------------------- ------------------ --------------- ---------------- ----------------- ----------------- --------------------
1122254562  720            8760          2012-02-21 12:38:54  2012-03-10 13:07:33                     1               2                76                1                 2012-02-21 12:38:54
1 rows fetched
adrci> set control (SHORTP_POLICY = 360)
adrci> set control (LONGP_POLICY = 720)

Other useful examples can e.g. be found in the article Useful ADRCI commands in Oracle – including e.g. how to use incident packages and more.

Shrink Temp Tablespace

That was fun! Developer made a nice script which ran havoc, and then the disk was suddenly full. And we had a … really big temporary tablespace. Big fun with Oracle 10g and below:

Keeps a DBA occupied for a while. And how with Oracle 11g and higher?

ALTER TABLESPACE temp
  SHRINK SPACE
  [ KEEP 1024 M ];

What's this doing? It simply deallocates all unused segments. However: without the optional KEEP parameter, the datafiles still remain full-sized, so it's not really a benefit. But with it, as in the example, the tablespace will really be reduced to the specified size, and the disk space freed.

Compound triggers

Some complex tasks required special treatment – and for one simple thing there were a bunch of triggers. With the database "evolving", it's easy to lose track. But with Oracle 11g and higher, it becomes easier to bundle at least some of those triggers belonging to a single task:

CREATE OR REPLACE TRIGGER mycompound
  FOR UPDATE OF special_column
  ON super_table
  COMPOUND TRIGGER
    /* local variable declarations go here */
  BEFORE STATEMENT IS
    BEGIN
      /* code to be executed before the statement */
    END BEFORE STATEMENT;
  BEFORE EACH ROW IS
    BEGIN
      /* code to be executed before every single row processed */
    END BEFORE EACH ROW;
  AFTER EACH ROW IS
    BEGIN
      /* code to be executed after every single row processed */
    END AFTER EACH ROW;
  AFTER STATEMENT IS
    BEGIN
      /* code to be executed after the statement */
    END AFTER STATEMENT;
END mycompound;
/

That is four triggers combined in one! Given the statement

UPDATE super_table
  SET special_column=17
WHERE id BETWEEN 1 AND 3;

this trigger would fire …

See another advantage? As it is possible to store local variables within the trigger, they can be transferred between the several stages.

Move AUDIT trails out of the SYSTEM tablespace

Sure, sometimes auditing is unavoidable. And sometimes company policies produce huge audit trails, as somebody wants every poo be monitored. A horror for the DBA, who doesn't like to have the SYSTEM tablespace blown up! Luckily, with Oracle 11gR2 and up a solution exists:

dbms_audit_mgmt.set_audit_trail_location(
  audit_trail_type => dbms_audit_mgmt.<ttype>,
  audit_trail_location_value => 'other_tablespace'
);

where <ttype> can have one of the following values:

Calculate overall database size

Sometimes you want to know just how big your database is (the contents, not the files) – and which are the top-10 biggest consumers:

set lines 200 pages 100

prompt Overall Database Segment size
select round(sum(bytes)/1024/1024/1024) gbytes from dba_segments;

prompt Top 10 space consuming users
col owner for a20
select owner,round(sum(bytes)/1024/1024/1024) gbytes from dba_segments group by owner order by 2 desc;

prompt Top 10 space consuming objects
col segment_name for a30
select * from (
  select max(owner) as owner,segment_name,round(sum(bytes)/1024/1024/1024) gbytes
    from dba_segments
   where owner in (select username from dba_users where oracle_maintained='N')
   group by segment_name order by 3 desc
) a where rownum <10;

Analyzing heavy UNDO operations

Under some rare conditions (e.g. after a „crashed datapump import“ the database might be heavily busy with transaction recovery. In some cases this can even cause massive trace file generation (1..2 GB per minute). To figure out what's going on, and how long it's going to be going on, a few queries might prove helpful:

spool /tmp/transaction_recovery.html
set markup html on
set time on
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';

select sysdate from dual;

-- Identify parallel rollback mode
-- (FALSE: no parallel rollback; LOW: max 2*CPU_COUNT; HIGH: max 4*CPU_COUNT)
show parameter fast_start_parallel_rollback

-- Identify Dead Transactions and their Sizes (USN: UndoSegmentNumber; size is in blocks)
select ktuxeusn USN, ktuxeslt Slot, ktuxesqn Seq, ktuxesta State, ktuxesiz Undo
  from x$ktuxe
 where ktuxesta <> 'INACTIVE' and ktuxecfl like '%DEAD%'
 order by ktuxesiz asc;

-- Estimate Time required for transaction recovery to complete
select usn, state, undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo",
       decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400))
       "Estimated time to complete"
  from v$fast_start_transactions;

-- number of parallel Recovery Slaves
select * from v$fast_start_servers;

select ktuxeusn, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Time", ktuxesiz, ktuxesta
  from x$ktuxe
 where ktuxecfl = 'DEAD';

set markup html off
spool off

For more details, also see: MOS DocId 1494886.1 (Troubleshooting Database Transaction Recovery) and Doc ID 1951738.1 (Transaction Recovery or Rollback of Dead Transactions).

2021-08-18