oracle audit log configuration

This post is about controlling the audit log on your system, at least the ones that goes into your database. The audit trails located on your fs is not covered here. Your vendor will normally set up an audit  configuration, but don't count on it. Worst case, they might not set up anything at all. A default oracle 11g install will set up standard audit trail to log at  least logon / logoff events, and even this will add up quite a bit over time. By default your audit logs will be added to your system tablespace, and really,  they should not be there.

So, if you have a running database, that has been there a while, this post might be worth while reading. If you have a brand new database, or your audit data is lets say, less than a GB, you can more or less  just follow the instructions.

If you have large amounts of audit data, many GB, you might need to do this in steps. Be aware that you can get extensive archive logging. As a rule of thumb, around the size of the portion you are cutting away. So use the queries below to do the math. Post me questions if you like :)

This example only keep audit logs online for 90 days. If you need to go back more than 90 days you must restore a copy of the database to cover you needs.


First of all, you need a good overview of your existing audit data.

select segment_name,round(bytes/1024/1024) from dba_segments where segment_name in ('AUD$', 'FGA_LOG$');

select table_name, tablespace_name from dba_tables where table_name in ('AUD$', 'FGA_LOG$') ORDER BY table_name;

NOTE! for test db's you might see entries in the audit log belonging to other dbid's. Purging will only affect rows belonging to the active dbid.

-- select dbid,count(*) from aud$ group by dbid;

// Find the oldest entries
-- select dbid,min(NTIMESTAMP#) from aud$ group by dbid;

// Check out historic number of rows per month and use this to calculate the space you need to allocate for the future.
-- select dbid,to_char(NTIMESTAMP#, 'yyyy-mm'), count(*) from aud$ group by dbid,to_char(NTIMESTAMP#, 'yyyy-mm') order by 1,2;

// Check out recent events trapped in the audit log
column terminal format a20
column action_name format a10
column userhost format a30
column os_username format a20
-- column extended_timestamp format a40
select action_name,returncode,username,count(*)
from dba_audit_session where timestamp>(sysdate-7) group by action_name,returncode,username;

NOTE! Use the size from the first query and create new tablespace with initial 2 x the size of AUD$

-- create tablespace audit_aux datafile '/u02/oradata/your-db/audit_aux01.dbf' size 200m autoextend on next 100m maxsize 4000m;

/* The following will physically move the AUD$ table from what you had ( default system ) to your new tablespace. */

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    audit_trail_location_value => 'AUDIT_AUX');
END;
/

/* The following will physically move the FGA_LOG$ table from what you had ( default system ) to your new tablespace. */

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
    audit_trail_location_value => 'AUDIT_AUX');
END;
/

// Check if already initialized, if not do it.
set pagesize 150
set linesize 200
column parameter_name format a30
column parameter_value format a20
SELECT * FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;

SET SERVEROUTPUT ON
BEGIN
  IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
    DBMS_OUTPUT.put_line('YES');
  ELSE
    DBMS_OUTPUT.put_line('NO');
  END IF;
END;
/

SET SERVEROUTPUT ON
BEGIN
  IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD) THEN
    DBMS_OUTPUT.put_line('YES');
  ELSE
    DBMS_OUTPUT.put_line('NO');
  END IF;
END;
/

/* This will take both types located in db, aud$ and fga_log$
NOTE! if you have not moved the the audit tables to the new database, this will trigger a move. The destination will be as specified by: SELECT * FROM DBA_AUDIT_MGMT_CONFIG_PARAMS; */

BEGIN
  DBMS_AUDIT_MGMT.init_cleanup(
    audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
    default_cleanup_interval => 24 /* hours */);
END;
/

// Check to see if "last archived audit timestamp" is set
-- select * FROM dba_audit_mgmt_last_arch_ts;

// If not, set it, 90 will preserve audit logs online for three months
BEGIN
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    last_archive_time => SYSTIMESTAMP-90);
END;
/

BEGIN
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
    last_archive_time => SYSTIMESTAMP-90);
END;
/

// Create jobs to purge the audit logs

begin
   dbms_audit_mgmt.create_purge_job (
   audit_trail_type            => dbms_audit_mgmt.audit_trail_aud_std,
   audit_trail_purge_interval  => 24,
   audit_trail_purge_name      => 'std_audit_trail_purge_job',
   use_last_arch_timestamp     => TRUE
   );
end;
/

begin
   dbms_audit_mgmt.create_purge_job (
   audit_trail_type            => dbms_audit_mgmt.audit_trail_fga_std,
   audit_trail_purge_interval  => 24,
   audit_trail_purge_name      => 'fga_audit_trail_purge_job',
   use_last_arch_timestamp     => TRUE
   );
end;
/

// Create a job to alter the preserved date to 90 days
BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'audit_last_archive_time',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'DECLARE
      l_days NUMBER := 90;
      BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.audit_trail_aud_std, TRUNC(SYSTIMESTAMP)-l_days);
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.audit_trail_fga_std, TRUNC(SYSTIMESTAMP)-l_days);
      END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Automatically set audit last archive time.');
END;
/

// if you ever need it, jobs can be kicked off manually
begin
   dbms_scheduler.run_job (job_name => 'audit_last_archive_time');
end;
/

// to list the audit jobs only
select * from DBA_AUDIT_MGMT_CLEANUP_JOBS;

// to list all jobs
select owner||'.'||job_name||'.'||start_date||'.'||next_run_date||'.'||state from dba_scheduler_jobs where enabled='TRUE';

Script already running - bash

For scripts with long execution time it can be a good idea to check if the script is already running. This to avoid mess and unnecessary load on server caused by multiple instances of the same script.

Add the following code to the script header:

already=`ps -ef | grep "$0" | grep -v grep|wc -l`
if [ $already -gt 1 ]
then
   echo "$0 already running"
   exit 1
fi

Clickable headers in PHP

This is a tiny example of how to make header columns clickable. The code is listing the content of the table my_table in a MySQL database called myDb. It can be a good idea to add a link to your main menu  as well, to avoid getting lost :)


my.php:

<!DOCTYPE html>
<html>
<head>
<style>
table, th, td {
    border: 1px solid black;
}
</style>
</head>
<body>
<?php

$servername = "myServer";
$username = "me";
$password = "myPw";
$dbname = "myDb";
$nbo = 0;

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

if (!$conn->set_charset("utf8")) {
    printf("Error loading character set utf8: %s\n", $mysqli->error);
    exit();
}
// to prevent getting totally lost
echo '<a href="index.html">HOME</a>';
$column='LastLogon';
$sort='desc';

if(isset($_GET['sorting'])) {
  if($_GET['sorting']=='asc')
     $sort='desc';
  else
     $sort='asc';
}

if($_GET['column']=='Name')
   $column = "Name";
elseif($_GET['column']=='Company')
   $column = "Company";
elseif($_GET['column']=='LastLogon')
   $column = "LastLogon";

$sql = "select Name,Company,Department,LastLogon from my_table order by $column $sort";
$result = $conn->query($sql);
// now, instead of having just the column name in the table header, you must add a link and pass the column name
if ($result->num_rows > 0) {
    echo '<table><tr> <th><a href="my.php?sorting='.$sort.'&column=Name">Name</a></th> <th><a href="my.php?sorting='.$sort.'&column=Company">Company</a></th>  <th>Department</th> <th> <a href="my.php?sorting='.$sort.'&column=LastLogon">LastLogon</a> </th> </tr>';
    // output data of each row
    while($row = $result->fetch_assoc()) {
        $nbo++;
        echo "<tr><td>" . $row["Name"]. "</td><td>" . $row["Company"] . "</td><td>" . $row["Department"] .  "</td><td>" . $row["LastLogon"] . "</td></tr>";
    }
    echo "</table>";
    echo "<br>" . $nbo . " rows";
} else {
    echo "0 results";
}

$conn->close();
?>
</body>
</html>

Troubleshoot oracle em 12c agent

If your oracle em agent refuse to start on your server and you see error "out of heap/memory", try to move all files under the state directory to a temp directory.

• make sure the agent is all dead, no java procs or anything 
• mkdir /tmp/agent_state.bad 
• mv $ORACLE_BASE/product/em/sysman/emd/state/* /tmp/agent_state.bad 
• now start the agent again, as root: /etc/init.d/gcstartup start
• use emctl if you have the oms server running on the same server

Expanding an oracle standby database

If you have an application using both a primary and a secondary database you might experienced the error  ORA-279. This can occur if you expand your primary database with new data files without updating your standby database.

When the archive log containing the expansion gets applied to your standby database you will see the following error in your alert log:

Parallel Media Recovery started
ORA-279 signalled during: ALTER DATABASE RECOVER  database until time '2018-04-23:09:12:17' using backup controlfile  ...
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Media Recovery Log /archive-file
Mon Apr 23 09:44:37 2018
File #72 added to control file as 'UNNAMED00072'. Originally created as:
'newly-added-file.dbf'
Recovery interrupted!

The fix to this problem is to tell the standby database about the new file.

Standby database> sqlplus / as sysdba

• alter database create datafile 'UNNAMED00072' as 'newly-added-file.dbf';

And now re-run the recover process. It should pass with no errors. The UNNAMED# will of course vary for each install. Use your alert log for the exact info.

Oracle read only account

Create a new read only user or grant rights to existing user

• create user new-user identified by pwd;
• grant connect, create session, select any table to new-user;

Now you can select any object by prefixing with owner. If you want to browse dictionary objects you also need

• grant select any dictionary to new-user;

For permanent users I always prefer to give specific access only to the objects needed and create synonyms to point to these objects. This gives a more generic source and also gives a better documentation of what this account is used for. And of course revoke the select any table right.