Oracle database housekeeping methods – adump purge

March 21, 2014 by 3 Comments 

Since Oracle Database 11g, all traces, incident dumps and packages, the alert log, Health Monitor reports, core dumps, and more files are stored in the ADR, a file-based Automatic Diagnostic Repository for simplified database diagnostic data management. In spite of that new 11g feature, the housekeeping or purging of those files got not considerably simplified. Unfortunately the new Oracle utility ADRCI can not purge all the files that Oracle database generates in ADR. Thus, you should use the other Unix OS methods and tools to accomplish Oracle 11g database housekeeping. I’ve already described the Unix log rotation mechanism. This time we will talk about Oracle System Audit files and how to purge them regularly.

SYS, SYSDBA or SYSOPER connections to the Oracle database are always audited. Sometimes this can lead to the creation of an excessive number of audit files. When this is considered to be a problem it cannot be solved at the database side and it must be investigated why the ‘client’ applications including Oracle Enterprise Manager (OEM) Components and agents are connecting so frequently as SYSDBA/SYSOPER. So this type of auditing mandatory in the Oracle database and can NOT be turned off. However the amount of audited information depends on AUDIT_SYS_OPERATIONS parameter that allows the addition audit of all statements issued by SYS/SYSDBA/SYSOPER in the same OS audit trail file.  In case the parameter AUDIT_SYS_OPERATIONS=TRUE Oracle audits not only SYS/SYSDBA/SYSOPER connection details but also their SQL operations. In any case the audit files with the name like <sid>_ora_<spid >_<instance#>.aud are created in audit_file_dest location on Unix.

sql*plus> show parameter audit
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /opt/app/oracle/admin/orcl/ adump
audit_sys_operations                 boolean     FALSE

I share below one of the methods of purging those audit files on Unix using a simple Unix command based on following: Read more »

Oracle performance tuning with Real World Performance Group

February 24, 2014 by Leave a comment 

Oracle Real World Performance Tour seminar in Munich

Oracle Real World Performance Tour seminar in Munich

I was a part of a unique Oracle training seminar that happened in Munich, Germany last week.
This is a Day of Real World Performance Tour seminar given by: Andrew Holdsworth, Tom Kyte, Graham Wood. These 3 well known Oracle experts are key members of the Real World Performance Team that consist of about 25 database experts who focus on Oracle database performance tuning, creating live demos and teaching customers how Oracle perform and how they should write the optimal code.

The format of this Oracle event is very unique. The entire seminar day Andrew, Tom and Graham are continuously on stage sharing in the conversational format very unique technical material debating (sometimes in a funny form) with each other and the audience. There are three screens in the seminar room and every instructor drives his own projector. They show the quite interesting live demos loading terabytes of data and manipulating with billions of rows in just several minutes on their own Exadata systems.

Interesting enough that these guys consider performance tuning aspects of the same problem from different angels sometimes contradicting each other but finally giving concrete solutions and best practices to follow. After those tests and lessons learned you definitely change your mind in terms of database and application performance tuning.

This training seminar makes interesting not only the unique technical material but also all three very different personalities of the instructors and their backgrounds. Tom has never been a DBA but he represents mainly Read more »

How CASE expression manipulates with NULL in Oracle SQL

February 4, 2014 by Leave a comment 

My next post is an answer to the below question about Oracle SQL CASE expression and the way how it manipulates with NULL values compared to DECODE function. This topic in fact is very common in Oracle database developers’ area.

===============
Hi Kirill,

Could you please help me to clarify below mention doubt.

DECODE:-

select decode (null,null,’true’,’false’) as value from dual

OUTPUT:- TRUE

CASE:-

select case null when null then ‘true’ else  ‘false’ end as Case_Test from dual

OUTPUT :- FALSE

Why above mention queries are giving different output  and what is the difference between case and decode?

==============

CASE expression treats NULLs in Oracle database SQL a bit differently compared to DECODE. You have to understand that the Boolean expressions usually result in TRUE or FALSE , but NULLs introduce a third possible result which is : UNKNOWN . At the same time NULL is not the same as UNKNOWN. See an example below:

sum + NULL results in NULL (this is a Scalar expression.)
NULL = NULL results in UNKNOWN (this is a Boolean expression.)
sum < NULL results in UNKNOWN (this is a Boolean expression.)

Ok. Now let’s explore the Oracle SQL CASE expression itself.

Oracle database SQL CASE expression

Oracle database SQL – CASE expression

CASE introduces two ways of conditional expressions: Simple CASE and Searched CASE. Read more »

Oracle database housekeeping methods – Unix log rotation

January 10, 2014 by Leave a comment 

Since Oracle Database 11g, all traces, incident dumps and packages, the alert log, Health Monitor reports, core dumps, and more files are stored in the ADR, a file-based Automatic Diagnostic Repository for simplified database diagnostic data management. In spite the housekeeping or purging of those files got not simplified, unfortunately the new Oracle utility ADRCI can not purge all the files that Oracle database generates in ADR. Thus, you should use the other OS methods and tools to accomplish Oracle database housekeeping.

This time I share a method of rotating Oracle database log files using Unix logrotate mechanism. The below example will rotate the Oracle alert.log and listener.log files in the following way:

– Rotation is based on the file size (you can use “M” or “k” for Mb and Kb)
– Unix Logrotate utility creates a copy of the original log file, then truncates it
– The rotated files will be compressed with Unix gzip utility with the following name <logfile>-yyyymmdd.gz
– Only recent 20 rotated log files are kept
– Rotation frequency is default = weekly
– No errors generated if the log file is missing
Read more »

Oracle 11g files housekeeping methods

January 3, 2014 by 2 Comments 

Since Oracle Database 11g, all traces, incident dumps and packages, the alert log, Health Monitor reports, core dumps, and more files are stored in the ADR, a file-based Automatic Diagnostic Repository for simplified database diagnostic data management. However housekeeping or purging of those files got not simplified. Unfortunately the new Oracle utility ADRCI can not purge all the files Oracle database generates in ADR. Thus, you should use the other OS tools to accomplish the same. Below I list common Oracle files that must be cleaned periodically with associated housekeeping methods I usually use on Linux servers.

Logs rotation

– Linux log rotate mechanism used
– alert.log, listener.log, backup logs and any other log files affected
– Files are purged and zipped for archiving purpose

Read more about Oracle database housekeeping using Unix logrotate mechanism.
Read more »

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email