Basic user-logon auditing

Oracle 8i introduced logon-triggers which could be used for auditing.

To start, create a table which will store your audit-logs (I usually do this as SYSTEM):

1
2
3
4
5
6
7
8
9
10

CREATE TABLE audit$user_logs
 (
    user_id           varchar2(30),
    session_id        number(8),
    host              varchar2(30),
    logon_day         date,
    logon_time        varchar2(10)
 );
 
TABLE created.

Next, create the trigger to capture the data:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

CREATE OR REPLACE TRIGGER
 logon_audit_trigger
 AFTER LOGON ON DATABASE
 BEGIN
 INSERT INTO audit$user_logs VALUES(
    user,
    sys_context('USERENV','SESSIONID'),
    sys_context('USERENV','HOST'),
    sysdate,
    to_char(sysdate, 'hh24:mi:ss')
 );
 END;
 /
 
TRIGGER created.

To list the audit-data:

1
2
3
4
5
6
7

SQL> SELECT * FROM audit$user_logs;
 
USER_ID         SESSION_ID HOST               LOGON_DAY LOGON_TIME
--------------- ---------- ------------------ --------- ----------
DBSNMP              123716 HOST               01-OCT-08 10:21:32
SYSTEM              123717 DOMAIN\PCNUMBER    01-OCT-08 10:21:53
SYSMAN                   0 HOST               01-OCT-08 10:21:58

To disable and enable the auditing:

1
2
3
4

ALTER TRIGGER SYSTEM.LOGON_AUDIT_TRIGGER DISABLE
/
ALTER TRIGGER SYSTEM.LOGON_AUDIT_TRIGGER ENABLE
/

To purge audit-data:

1

TRUNCATE TABLE audit$user_logs

October 1st, 2008 | 0 Comments
Category: Auditing | Tags: , ,

Recovering a tablespace

The situation:

The database was not shutdown cleanly and a tablespace needed recovery because of an outstanding transaction that wasn’t committed.

If you simply STARTUP the database, the tablespace will still be in RECOVER mode and won’t be available.

First, startup the database in restrict mode:

1

SQL> startup restrict

List all tablespaces and check the ONLINE_STATUS:

1
2
3
4
5
6
7
8
9
10
11

SQL> SELECT tablespace_name, online_status FROM dba_data_files;
 
TABLESPACE_NAME                ONLINE_STATUS
------------------------------ ---------------
USERS                          ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
SYSTEM                         SYSTEM
TEST                           RECOVER
 
5 rows selected.

In this case, the TEST tablespace requires recovery:

1
2
3

SQL> RECOVER TABLESPACE TEST;
 
Media recovery complete.

Check tablespaces again:

1
2
3
4
5
6
7
8
9
10
11

SQL> SELECT tablespace_name, online_status FROM dba_data_files;
 
TABLESPACE_NAME                ONLINE_STATUS
------------------------------ --------------
USERS                          ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
SYSTEM                         SYSTEM
TEST                           OFFLINE
 
5 rows selected.

The tablespace doesn’t need further recovery at this stage and can be placed ONLINE:

1
2
3

SQL> ALTER TABLESPACE TEST ONLINE;
 
Tablespace altered.

Get the database out of restrict mode:

1
2
3

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
 
System altered.

September 30th, 2008 | 0 Comments
Category: Recovery | Tags:

Forcing a checkpoint

A checkpoint makes sure that all changes to the database (that are still in buffers) are written to the datafiles.

1
2
3

SQL> ALTER SYSTEM CHECKPOINT;
 
System altered.

September 30th, 2008 | 0 Comments
Category: General | Tags:

List all database-links

To list all db-links (must be a user with access to sys.dba_db_links) in a database:

1

SELECT * FROM DBA_DB_LINKS;

Sample output:

1
2
3
4
5
6

SQL> SELECT * FROM DBA_DB_LINKS;
 
OWNER   DB_LINK    USERNAME   HOST    CREATED
------- ---------- ---------- ------- ----------
SYSTEM  TEST_LINK  SCOTT      TST11   26-SEP-08
SCOTT   HR_LINK    HR         TST11   26-SEP-08

September 26th, 2008 | 0 Comments
Category: General | Tags:

Recycle listener.log

Oracle stores a file under $ORACLE_HOME/network/log called listener.log that keeps track of all connections to the database.

Oracle keeps this file as an open file-stream, so even if you rename the file, it still keeps on growing. This method will let you recycle/truncate/purge listener.log without resorting to taking the listener down which would cause a disruption to users.

Turn log_status off, create a new file, turn log_status on again

1
2
3
4

$ lsnrctl set log_status off
$ mv listener.log listener.log.old
$ touch listener.log
$ lsnrctl set log_status on

September 23rd, 2008 | 0 Comments
Category: General | Tags:

Recompile invalid objects in schema

To compiles all procedures, functions, packages, and triggers in a specific schema:

1

EXEC DBMS_UTILITY.compile_schema(schema => 'SCHEMA_NAME');

September 23rd, 2008 | 0 Comments
Category: General | Tags: