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
adrci> show alert
- on first call gives a selection on available alert log files
- then displays the entire selected log as if
cat
was used
adrci> show alert -tail 10
- shows only the last 10 lines
adrci> show alert -p "module_id='DBMS_SCHEDULER'"
- filter the log: restrict it e.g. to a specified module.
adrci> describe alert_ext
- show available filter options
adrci> spool outfile.txt
adrci> show tracefile
- …
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:
- create a new temp tablespace (where? Disk full!)
- make the new temp tablespace the default temp tablespace for all users
- drop the blown-up temp tablespace
- optionally re-create the original temp tablespace with decent values, and drop the new one
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 …
- once at the very beginning with the BEFORE STATEMENT
- then 3 times, one for each id 1,2,3, with BEFORE EACH ROW and AFTER EACH ROW code
- and finally, once at the very end with the AFTER STATEMENT
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:
- for
AUD$
: audit_trail_aud_std - for
FGA_LOG$
: audit_trail_fga_std
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).