AskTom

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

Hi,

today I installed Oracle VM Server and Oracle VM Manager from images provided by Oracle.
Everything goes fine. I setup my VM machines but when I tried to create Server Pool trought VM Manager I’ve got an error

OVM-1011 OVM Manager communication with <hostname> for operation Pre-check cluster root for Server Pool failed: <Exception: Cluster root not found.>

So I did new repository and after than I was able to create Server Pool.

This is my solution:

[root@ovs ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2              29G   26G  1.3G  96% /
/dev/sda1              99M   45M   49M  48% /boot
tmpfs                 272M     0  272M   0% /dev/shm
[root@ovs ~]# cd /opt/ovs-agent-2.3/utils/
[root@ovs utils]# ./repos.py –new /dev/sda4
Mount point: /tmp/ovsrepo_qlGMC0
Error: ['mount', '/dev/sda4', '/tmp/ovsrepo_qlGMC0'] => mount: you must specify the filesystem type

[root@ovs utils]# mkfs.ocfs2 -L sdilenapar -b 4K -C 4K -J size=64M -N 16 /dev/sda4
mkfs.ocfs2 1.4.3
Cluster stack: classic o2cb
Filesystem label=sdilenapar
Block size=4096 (bits=12)
Cluster size=4096 (bits=12)
Volume size=286363119616 (69912871 clusters) (69912871 blocks)
2168 cluster groups (tail covers 14119 clusters, rest cover 32256 clusters)
Journal size=67108864
Initial number of node slots: 16
Creating bitmaps: done
Initializing superblock: done
Writing system files: done
Writing superblock: done
Writing backup superblock: 5 block(s)
Formatting Journals: done
Formatting slot map: done
Writing lost+found: done
mkfs.ocfs2 successful

[root@ovs utils]# ./repos.py –new /dev/sda4
[ NEW ] 5ce04f38-20af-48ec-ab2f-1afd138047a7 => /dev/sda4

[root@ovs utils]# ./repos.py –list
[   ] 5ce04f38-20af-48ec-ab2f-1afd138047a7 => /dev/sda4
[root@ovs utils]# ./repos.py –root 5ce04f38-20af-48ec-ab2f-1afd138047a7
[ R ] 5ce04f38-20af-48ec-ab2f-1afd138047a7 => /dev/sda4

Regards,

Tom