Oracle Database

Few Days ago I faced this error when I am trying to connect to my Oracle Database :-

bash-3.2$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 25 14:58:31 2013

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Enter user-name: /as sysdba
ERROR:
ORA-09817: Write to audit file failed.
IBM AIX RISC System/6000 Error: 28: No space left on device
Additional information: 12
ORA-01075: you are currently logged on

After this I checked space available in the FS. I got this :-

bash-3.2$ df -g

Filesystem GB blocks Free %Used Iused %Iused Mounted on

/dev/hd4 10.38 3.88 63% 12155 2% /

/dev/hd2 12.25 6.76 45% 97364 6% /usr

/dev/hd9var 5.50 5.03 9% 11606 1% /var

/dev/hd3 5.12 2.52 51% 438 1% /tmp

/dev/hd1 8.12 0.00 100% 76570 29% /home

/dev/hd11admin 1.12 1.12 1% 5 1% /admin

/proc - - - - - /proc

/dev/hd10opt 5.50 1.69 70% 32636 8% /opt

/dev/livedump 0.25 0.25 1% 4 1% /var/adm/ras/livedump

/dev/fs1v0disk1 200.00 183.22 9% 10805 1% /disk1

/dev/fs1v0disk2 200.00 183.39 9% 3655 1% /disk2

/dev/fs1v0disk3 100.00 39.36 61% 5251 1% /disk3

/dev/fs1v0archive 200.00 199.90 1% 12 1% /archive_log

/dev/fs1v0redo 15.00 15.00 1% 4 1% /log_redo_group_1

/dev/fs1v0oracle 15.00 0.00 100% 42003 41% /oracle

/dev/fs1v0pacs 100.00 96.45 4% 17900 1% /amit

/dev/fs1v0other 100.00 98.98 2% 5 1% /other

/dev/fs1v0backup 100.00 99.86 1% 5 1% /backup

I found out that /oracle mount point is 100% full where oracle writes its audit logs.

SQL> show parameter audit

NAME TYPE VALUE
------------------------------ ----------- ------------------------------
audit_file_dest string /oracle/oracle/admin/amit/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB

After further findings I found out that listener.log file is around 4 gb in size and Alert log folder is 3.5 GB in size. PFB steps to reduce size of both:-

1. How to empty/remove Listener.log file :-

bash-3.2$ cd /oracle/oracle/diag/tnslsnr/amit3/listener/trace
bash-3.2$ ls -ltr

total 379432
-rw-r----- 1 oracle oinstall 4193857778 Feb 25 15:48 listener.log

bash-3.2$ lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production on 25-FEB-2013 15:50:25

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> set log_status OFF
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "log_status" set to OFF
The command completed successfully
LSNRCTL>exit

bash-3.2$ pwd
/oracle/oracle/diag/tnslsnr/amit3/listener/trace
bash-3.2$ mv listener.log listener_bak.log
bash-3.2$ touch listener.log
bash-3.2$ls -ltr

total 379432
-rw-r----- 1 oracle oinstall 4193857778 Feb 25 15:48 listener_bak.log

-rw-r----- 1 oracle oinstall 0 Feb 25 15:48 listener.log

bash-3.2$ cp listener_bak.log /disk1

bash-3.2$ rm listener_bak.log

bash-3.2$ lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production on 25-FEB-2013 15:50:25

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> set log_status ON

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "log_status" set to ON
The command completed successfully
LSNRCTL> exit

bash-3.2$

2. Remove old alert log files from Alert log folder :-

bash-3.2$ cd /oracle/oracle/diag/rdbms/amit/amit/trace

bash-3.2$ pwd

/oracle/oracle/diag/rdbms/pacs/pacs/trace

bash-3.2$ ls -ltr|wc -l

1018

bash-3.2$ ls -ltr |tail

-rw-r----- 1 oracle oinstall 1207 Feb 25 12:26 pacs_ora_45547758.trc

-rw-r----- 1 oracle oinstall 63 Feb 25 12:29 pacs_ora_23920872.trm

-rw-r----- 1 oracle oinstall 1231 Feb 25 12:29 pacs_ora_23920872.trc

-rw-r----- 1 oracle oinstall 63 Feb 25 12:33 pacs_ora_45547548.trm

-rw-r----- 1 oracle oinstall 1207 Feb 25 12:33 pacs_ora_45547548.trc

-rw-r----- 1 oracle oinstall 63 Feb 25 12:40 pacs_ora_40435750.trm

-rw-r----- 1 oracle oinstall 1232 Feb 25 12:40 pacs_ora_40435750.trc

-rw-r----- 1 oracle oinstall 104 Feb 25 15:01 pacs_ckpt_7602350.trm

-rw-r----- 1 oracle oinstall 1762 Feb 25 15:01 pacs_ckpt_7602350.trc

-rw-r----- 1 oracle oinstall 1381033 Feb 25 16:00 alert_amit.log

Except alert_amit.log remove all alert log files to a dfferent mount point where you have space available.

After alert log files have been removed again check FS space status :-

bash-3.2$ df -g

Filesystem GB blocks Free %Used Iused %Iused Mounted on

/dev/hd4 10.38 3.90 63% 12154 2% /

/dev/hd2 12.25 6.76 45% 97364 6% /usr

/dev/hd9var 5.50 5.03 9% 11606 1% /var

/dev/hd3 5.12 2.52 51% 439 1% /tmp

/dev/hd1 8.12 0.00 100% 76570 29% /home

/dev/hd11admin 1.12 1.12 1% 5 1% /admin

/proc - - - - - /proc

/dev/hd10opt 5.50 1.69 70% 32636 8% /opt

/dev/livedump 0.25 0.25 1% 4 1% /var/adm/ras/livedump

/dev/fs1v0disk1 200.00 183.22 9% 10805 1% /disk1

/dev/fs1v0disk2 200.00 183.39 9% 3655 1% /disk2

/dev/fs1v0disk3 100.00 39.36 61% 5251 1% /disk3

/dev/fs1v0archive 200.00 199.90 1% 12 1% /archive_log

/dev/fs1v0redo 15.00 15.00 1% 4 1% /log_redo_group_1

/dev/fs1v0oracle 15.00 7.87 48% 42012 3% /oracle

/dev/fs1v0pacs 100.00 96.45 4% 17900 1% /amit

/dev/fs1v0other 100.00 98.98 2% 5 1% /other

/dev/fs1v0backup 100.00 99.86 1% 5 1% /backup

bash-3.2$

bash-3.2$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 25 16:02:00 2013

Enter user-name: /as sysdba

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

I hope this article helped you.

Regards,

Amit Rath

Killing Oracle session :-

TO kill a oracle session you have to be very sure which session you want o kill otherwise you may kill any other session which is useful to you.

To Find your session and machine from which you are connected

SQL> select sid,serial#,status,machine,osuser,to_char(LOGON_TIME, 'DD-MON-YYYY hh24:mi:ss') as LOGON_TIME from v$session where username='USERNAME' order by logon_time;

Alter system kill session 'sid,serial#' immediate;

Disconnecting Oracle sessions :-

Disconnecting a session is similar to kill a session . Unlike Kill session asks session to kill itself, disconnect session kill the dedicated server process equivalent to killing from OS level.
Syntax wise disconnect has a additional clause called POST_TRANSACTION, it waits for ongoing transactions to complete before disconnecting the session while IMMEDIATE clause disconnects the session and ongoing transactions are rolled back immeiately.

Alter system disconnect session 'sid,serial#' POST_TRANSACTION;
Alter system disconnect session 'sid,serial#' IMMEDIATE;

When in our database we have multiple inactive sessions and we want to kill all of them, then we can generate a script to kill all of them.

Small Script to kill multiple oracle sessions where status is INVALID:-

SQL> select 'alter system kill session ''' ||sid|| ',' || serial#|| ''' immediate;' from v$session where status='INACTIVE'

Small Script to kill multiple oracle sessions of a particular user :-

SQL> select 'alter system kill session ''' ||sid|| ',' || serial#|| ''' immediate;' from v$session where username='USERNAME' ;

Finding how much a session is executed

SQL>col OPNAME for a20
SQL>col USERNAME for a18
SQL> col START_TIME for a25
SQL> select sid,serial#,opname,sofar,totalwork,username,to_char(start_time,'dd-mon-yyyy hh24:mi:ss') as "START_TIME",time_remaining from v$session_longops where username='SYSTEM' and time_remaining!=0;

I hope this article helped you.

Regards,
Amit Rath

Oracle Database

About Me

Monday, February 25, 2013

ORA-09817: Write to audit file failed

How to generate script to kill multiple Oracle sessions

Popular Posts

Followers

Labels