AskTom

I decided to start new web site, where all posts are in czech language.

But all readers are able to translate it to corresponding language.

I dont need to duplicate all my posts, so I’ll put articles more frequently.

Hope you will enjoy reading.

Regards,

Tom

Hi,

today I had problem with one query.
Even I killed session from sqlplus by command ALTER SESSION KILL, the process still running in database.
The status where killed but still consumed CPU, so I had to kill it from OS.

1. Find the problemtic query

SQL> SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM   gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != ‘BACKGROUND’ and sid=287;

INST_ID      SID     SERIAL# SPID        USERNAME   PROGRAM
———- ———- ———- ———- ———- ———————————————
1      287       25938 18146        VPTS       SQL Developer

SQL> select username, status from v$session where status=’KILLED’;

USERNAME   STATUS
———- ——–
VPTS       KILLED

2. Now you can kill sessiion from OS

oracle@vserver1:~> ps -ef | grep 18146
oracle   18146     1 95 Mar15 ?        21:31:08 oracleprod (LOCAL=NO)
oracle   18418 14337  0 07:54 pts/0    00:00:00 grep 18146

oracle@vserver1:~>kill -9 18146

3. After than the process is gone

SQL> select username, status from v$session where status=’KILLED’;

no rows selected

SQL> SQL> SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM   gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != ‘BACKGROUND’ and sid=287;

no rows selected

Regards,

Tom

Hi,
today one of databases stopped. The reason was that ASM disk group were out of space.

Alert.log
=========
Mon Mar  1 11:57:45 2010
Errors in file /opt/app/oracle/admin/replica1/bdump/replica1_arc1_15884.trc:
ORA-19816: WARNING: Files may exist in db_recovery_file_dest that are not known to database.
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15041: diskgroup space exhausted

Sqlplus:
========

SQL> select a.name DiskGroup, b.disk_number Disk#, b.name DiskName, b.total_mb, b.free_mb, b.path, b.header_status
2  from v$asm_disk b, v$asm_diskgroup a
3  where a.group_number (+) =b.group_number
4  order by b.group_number, b.disk_number, b.name
5  /

DISKGROUP       DISK# DISKNAME                 TOTAL_MB      FREE_MB PATH       HEADER_STATU
————— —– ——————– ———— ———— ———————————– ————
DATA 0 DATA_0000                 148,256            0 /dev/raw/raw1                       UNKNOWN
***************                            ———— ————
sum                                             148,256            0

SYS@replica1 SQL> show parameter recovery

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_recovery_file_dest                string      +DATA
db_recovery_file_dest_size           big integer 106290M
recovery_parallelism                 integer     0

SOLUTION:
=========
backup and delete ald archivelog by RMAN command

RMAN> backup archivelog all delete input;

Starting backup at Mar 01 2010 11:59:06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
…

ls -ltr
…
-rw-r—–  1 oracle oinstall 1612922880 2010-02-17 10:16 38l67qh7_10_1
-rw-r—–  1 oracle oinstall   24561664 2010-02-17 10:16 39l67uoj_1_1
-rw-r—–  1 oracle oinstall 2147483648 2010-03-01 12:05 3cl7fmcu_1_1
-rw-r—–  1 oracle oinstall 2147484160 2010-03-01 12:05 3cl7fmcu_2_1

Regards,

Tom