oracle service with “PREFERRED” instance test

客户需要将一个原本健康的oracle 10g rac更改成为单节点运行,理由是app并未在rac的环境设计、测试过,各个节点的global cache有点多,存在性能的问题。需求就是要求RAC前端的所有session连接,都只连接到一个指定的节点上,也就是将"load balance"关闭掉。如果通过oracle service的方式也可以达到这样的效果,无需修改tnsnames中的loadbalance项和rac端的remote_listener参数,下面是一个实验的环境,仔细观察。

1.create the service named xjapan

//add a service named xjapan,rac1 as pre_list member and rac2 as avail_list member, the TAF policy is BASIC

srvctl add service -d rac -s xjapan -r "rac1" -a "rac2" -P BASIC

[oracle@node1 ~]$ srvctl add service -d rac -s xjapan -r "rac1" -a "rac2" -P BASIC

[oracle@node1 ~]$ crs_stat -t

Name           Type           Target    State     Host

————————————————————

ora….E1.lsnr application    ONLINE    ONLINE    node1

ora.node1.gsd  application    ONLINE    ONLINE    node1

ora.node1.ons  application    ONLINE    ONLINE    node1

ora.node1.vip  application    ONLINE    ONLINE    node1

ora….E2.lsnr application    ONLINE    ONLINE    node2

ora.node2.gsd  application    ONLINE    ONLINE    node2

ora.node2.ons  application    ONLINE    ONLINE    node2

ora.node2.vip  application    ONLINE    ONLINE    node2

ora.rac.db     application    ONLINE    ONLINE    node2

ora….c1.inst application    ONLINE    ONLINE    node1

ora….c2.inst application    ONLINE    ONLINE    node2

ora….ice2.cs application    ONLINE    ONLINE    node1

ora….ac1.srv application    ONLINE    ONLINE    node2

ora….apan.cs application    OFFLINE   OFFLINE

ora….ac1.srv application    OFFLINE   OFFLINE

 

 

Continue reading “oracle service with “PREFERRED” instance test”

orakill和alter system kill session

 

在客户那遇到一例问题,一条开发人员的update没有commit,造成了资源的持有不放,阻塞了其他的session,而由于开发人员与db server之间有防火墙,相应的session已经被firewall重定向了,连commit或rollback的机会都没有了,db中跑着这个”僵尸”进程,通常情况下,这类问题就是定位相应的sql,然后kill掉,而windows平台上,由于所有与oracle相关的进程都仅仅体现在oracle.exe这一个进程中,想获得更详细的thread信息,一般需要借助微软提供的工具,例如:procexp64.exe。

另外,如果仅仅是alter system kill session来处理的话,相应的会话仅仅是被标识为”killed”状态,资源并不会立刻的释放。它需要等待pmon进程来做一些清理工作,没准还需要SQL>oradebug wakeup <pmon_pid>,来手工唤醒pmon来做清理工作,但实际的效果一般,其中pmon_pid是v$process.pid,所以在实际的问题处理环境中,还是建议直接orakill吧,见效快。
——————————————找到想要orakill的spid信息————————————————–
SQL> select s.sid,s.status,s.username,s.machine,p.spid,p.username from v$session s,v$process p where s.paddr=p.addr;

SID STATUS USERNAME MACHINE SPID USERNAME
———- ——– —————————— ———————————– ———— —————
170     ACTIVE     JACK     756        SYSTEM
169     ACTIVE     JACK      3148      SYSTEM
168     ACTIVE      JACK      3304     SYSTEM
167    ACTIVE      JACK       2672     SYSTEM
166    ACTIVE      JACK       3452     SYSTEM
165    ACTIVE      JACK       1844     SYSTEM
164     ACTIVE     JACK       3868     SYSTEM
163     ACTIVE     JACK       1824     SYSTEM
162     ACTIVE     JACK       2880    SYSTEM
161      ACTIVE    JACK       1224     SYSTEM
160     ACTIVE    JACK       1676     SYSTEM

SID      STATUS      USERNAME      MACHINE           SPID         USERNAME
———- ——– —————————— ———————————– ———— —————
113      ACTIVE      SYS      WORKGROUPJACK      2652      SYSTEM
154      ACTIVE     JACK                                           3316       SYSTEM
152      ACTIVE      JACK                                          1460       SYSTEM
128    INACTIVE  JIANKONG     NetGain                  2600        SYSTEM
149     INACTIVE JIANKONG     NetGain                 2636        SYSTEM
131      INACTIVE JIANKONG      NetGain                1580        SYSTEM
125     INACTIVE JIANKONG     NetGain                  1544        SYSTEM
135      INACTIVE JIANKONG      NetGain                3856        SYSTEM
130     INACTIVE   SCOTT         WORKGROUPJACK    2720    SYSTEM
143       INACTIVE SCOTT             HQ200708TMBG      716   SYSTEM
129        ACTIVE      JACK                                                        3228     SYSTEM

22 rows selected.
//定位了sid=143,spid=716的session,就是想要orakill的session

//用主机上的orakill命令,开始orakill操作
SQL> host
Microsoft Windows [版本 5.2.3790]
(C) 版权所有 1985-2003 Microsoft Corp.

C:Documents and SettingsAdministrator>orakill crdprodb 716

Kill of thread id 716 in instance crdprodb successfully signalled.

C:Documents and SettingsAdministrator>

//orakill成功了,查看被kill窗口的状态。

——————orakill v$process.spid之后,被kill的会话session窗口的现象。—————————–

C:Userstumin>sqlplus scott/tiger@192.168.201.123:1521/crdprodb

SQL*Plus: Release 10.2.0.1.0 – Production on 星期四 1月 17 16:33:33 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from user_tables;
select * from user_tables
*
第 1 行出现错误:
ORA-03113: 通信通道的文件结束

———————————————————————————————————–

可见,orakill的效果比较迅速、直接,建议在实际的问题处理中使用orakill。

v$session读取的授权以及v$gv$v_$解释

开发人员需要读取v$session中的信息,以便定位问题和日常的诊断。初步的想法是将v$session的select权限给开发人员的db user。而v$session是个synonym,直接授权会报错的,如下:

SQL> grant select on sys.v$session to test1;
grant select on sys.v$session to test1
*
第 1 行出现错误:
ORA-02030: 只能从固定的表/视图查询

报错提示很明显,被授权的对象必须是”固定表/视图”,查看一下v$session这个对象的来历,追溯其血缘。

SQL> select owner,object_type from dba_objects where object_name=’V$SESSION’;

OWNER                       OBJECT_TYPE
—————————— ——————-
PUBLIC                         SYNONYM

可以看到v$session是”public”的同义词,而这个同义词指向table_name=v_$session的视图

SQL> desc dba_synonyms;
名称 是否为空? 类型
—————————————————————————————– ——– ————–
OWNER                           NOT NULL VARCHAR2(30)
SYNONYM_NAME     NOT NULL VARCHAR2(30)
TABLE_OWNER          VARCHAR2(30)
TABLE_NAME              NOT NULL VARCHAR2(30)
DB_LINK                        VARCHAR2(128)

 

SQL> select owner,synonym_name,table_name from dba_synonyms where synonym_name=’V$SESSION’;

OWNER                    SYNONYM_NAME                   TABLE_NAME
—————————— —————————— ——————————
PUBLIC                   V$SESSION                                    V_$SESSION

SQL> select owner,object_type from dba_objects where object_name=’V_$SESSION’;

OWNER                OBJECT_TYPE
—————————— ——————-
SYS                                VIEW

 

为了达到开发人员的需求目的,可以用sys将v_$session的select权限给用户即可
SQL> show user;
USER 为 “SYS”
SQL> grant select on sys.v_$session to test1;

授权成功。

SQL> conn test1/test1
已连接。
SQL> show user;
USER 为 “TEST1”
SQL> select count(*) from v$session;

COUNT(*)
———-
17

可以看到,授权成功了,test1这个普通用户(resource,connect角色)能够查询v$session中的信息了。

 

另外一个话题:通常查询的v$session类似动态性能的视图,都是如何构造的?到底源于底层的哪些对象呢?

dba查询类似的v$session动态性能视图,可以按照如下的方式追溯,会涉及gv$,v$,v_$,x$等等..

//oracle中的动态性能视图构成可以参考如下
sys : x$(内存对象))—>v$(内存对象)—->v_$(view)—–>v$(public sysnonym)
而gv$开通的意思是”全局”的意思,例如RAC和单实例的区别。

//做如下的查询来进一步研究,看看v_$session这个视图究竟是由什么组成的?

SQL> select owner,view_name,text from dba_views where view_name=’V_$SESSION’;

OWNER VIEW_NAME TEXT
—————————— —————————— ——————————————————————————–
SYS V_$SESSION select “SADDR”,”SID”,”SERIAL#”,”AUDSID”,”PADDR”,”USER#”,”USERNAME”,”COMMAND”,”OW

//text字段比较长,截取比较关键的部分查看,可以看到最后还是select v$session这个视图
select “SADDR”,”SID”,”SERIAL#”,”AUDSID”,”PADDR”,
“USER#”,”USERNAME”,”COMMAND”,”OWNERID”,”TADDR”,
“LOCKWAIT”,”STATUS”,”SERVER”,”SCHEMA#”,”SCHEMANAME”,
“OSUSER”,”PROCESS”,”MACHINE”,”TERMINAL”,”PROGRAM”,”TYPE”,
“SQL_ADDRESS”,”SQL_HASH_VALUE”,”SQL_ID”,”SQL_CHILD_NUMBER”,
“PREV_SQL_ADDR”,”PREV_HASH_VALUE”,”PREV_SQL_ID”,
“PREV_CHILD_NUMBER”,”MODULE”,”MODULE_HASH”,”ACTION”,
“ACTION_HASH”,”CLIENT_INFO”,”FIXED_TABLE_SEQUENCE”,
“ROW_WAIT_OBJ#”,”ROW_WAIT_FILE#”,”ROW_WAIT_BLOCK#”,
“ROW_WAIT_ROW#”,”LOGON_TIME”,”LAST_CALL_ET”,”PDML_ENABLED”,
“FAILOVER_TYPE”,”FAILOVER_METHOD”,”FAILED_OVER”,
“RESOURCE_CONSUMER_GROUP”,”PDML_STATUS”,”PDDL_STATUS”,”PQ_STATUS”,
“CURRENT_QUEUE_DURATION”,”CLIENT_IDENTIFIER”,”BLOCKING_SESSION_STATUS”,
“BLOCKING_INSTANCE”,”BLOCKING_SESSION”,”SEQ#”,”EVENT#”,”EVENT”,”P1TEXT”,
“P1″,”P1RAW”,”P2TEXT”,”P2″,”P2RAW”,”P3TEXT”,”P3″,”P3RAW”,”WAIT_CLASS_ID”,
“WAIT_CLASS#”,”WAIT_CLASS”,”WAIT_TIME”,”SECONDS_IN_WAIT”,”STATE”,
“SERVICE_NAME”,”SQL_TRACE”,”SQL_TRACE_WAITS”,”SQL_TRACE_BINDS” from v$session
//查询v$fixed_table来定位v$session这个对象

SQL> select * from v$fixed_table where name =’V$SESSION’;

NAME              OBJECT_ID        TYPE        TABLE_NUM
—————————— ———- —– ———-
V$SESSION   4294950919     VIEW        65537

//看看v$session这个fixed对象的构成
select * from v$fixed_view_definition where view_name=’V$SESSION’;
V$SESSION
select SADDR , SID , SERIAL# , AUDSID , PADDR , USER# , USERNAME , COMMAND
, OWNERID, TADDR , LOCKWAIT , STATUS , SERVER , SCHEMA# , SCHEMANAME ,OSUSER
, PROCESS , MACHINE , TERMINAL , PROGRAM , TYPE , SQL_ADDRESS , SQL_HASH_VALUE,
SQL_ID, SQL_CHILD_NUMBER , PREV_SQL_ADDR , PREV_HASH_VALUE , PREV_SQL_ID,
PREV_CHILD_NUMBER , MODULE , MODULE_HASH , ACTION , ACTION_HASH , CLIENT_INFO ,
FIXED_TABLE_SEQUENCE , ROW_WAIT_OBJ# , ROW_WAIT_FILE# , ROW_WAIT_BLOCK# ,
ROW_WAIT_ROW# , LOGON_TIME , LAST_CALL_ET , PDML_ENABLED , FAILOVER_TYPE ,
FAILOVER_METHOD , FAILED_OVER, RESOURCE_CONSUMER_GROUP, PDML_STATUS, PDDL_STATUS,
PQ_STATUS, CURRENT_QUEUE_DURATION, CLIENT_IDENTIFIER, BLOCKING_SESSION_STATUS,
BLOCKING_INSTANCE,BLOCKING_SESSION,SEQ#, EVENT#,EVENT,P1TEXT,P1,P1RAW,P2TEXT,P2,P2RAW,
P3TEXT,P3,P3RAW,WAIT_CLASS_ID, WAIT_CLASS#,WAIT_CLASS,WAIT_TIME, SECONDS_IN_WAIT,STATE,
SERVICE_NAME, SQL_TRACE, SQL_TRACE_WAITS, SQL_TRACE_BINDS from GV$SESSION where inst_id = USERENV(‘Instance’)

可以看到最后一段部分的”from GV$SESSION where inst_id = USERENV(‘Instance’)”,v$session是通过查询gv$session然后指定当前instance得来的。

//看看gv$session的定义
select * from v$fixed_view_definition where view_name=’GV$SESSION’;
GV$SESSION
select s.inst_id,s.addr,s.indx,s.ksuseser,s.ksuudses,s.ksusepro,
s.ksuudlui,s.ksuudlna,s.ksuudoct,s.ksusesow, decode(s.ksusetrn,hextoraw(’00’),null,s.ksusetrn),
decode(s.ksqpswat,hextoraw(’00’),null,s.ksqpswat),decode(bitand(s.ksuseidl,11),1,’ACTIVE’,0,
decode(bitand(s.ksuseflg,4096),0,’INACTIVE’,’CACHED’),2,’SNIPED’,3,’SNIPED’, ‘KILLED’),
decode(s.ksspatyp,1,’DEDICATED’,2,’SHARED’,3,’PSEUDO’,’NONE’), s.ksuudsid,s.ksuudsna,s.ksuseunm,
s.ksusepid,s.ksusemnm,s.ksusetid,s.ksusepnm, decode(bitand(s.ksuseflg,19),17,’BACKGROUND’,1,’USER’,2,
‘RECURSIVE’,’?’), s.ksusesql, s.ksusesqh, s.ksusesqi, decode(s.ksusesch, 65535, to_number(null), s.ksusesch),
s.ksusepsq, s.ksusepha, s.ksusepsi, decode(s.ksusepch, 65535, to_number(null), s.ksusepch), s.ksuseapp,
s.ksuseaph, s.ksuseact, s.ksuseach, s.ksusecli, s.ksusefix, s.ksuseobj, s.ksusefil, s.ksuseblk, s.ksuseslt,
s.ksuseltm, s.ksusectm,decode(bitand(s.ksusepxopt, 12),0,’NO’,’YES’),decode(s.ksuseft, 2,’SESSION’, 4,’SELECT’,8,’TRANSACTIONAL’,
‘NONE’),decode(s.ksusefm,1,’BASIC’,2,’PRECONNECT’,4,’PREPARSE’,’NONE’),decode(s.ksusefs, 1, ‘YES’, ‘NO’),
s.ksusegrp,decode(bitand(s.ksusepxopt,4),4,’ENABLED’,decode(bitand(s.ksusepxopt,8),8,’FORCED’,’DISABLED’)),
decode(bitand(s.ksusepxopt,2),2,’FORCED’,decode(bitand(s.ksusepxopt,1),1,’DISABLED’,’ENABLED’)),
decode(bitand(s.ksusepxopt,32),32,’FORCED’,decode(bitand(s.ksusepxopt,16),16,’DISABLED’,’ENABLED’)),
s.ksusecqd, s.ksuseclid, decode(s.ksuseblocker,4294967295,’UNKNOWN’, 4294967294, ‘UNKNOWN’,4294967293,
‘UNKNOWN’,4294967292,’NO HOLDER’, 4294967291,’NOT IN WAIT’,’VALID’),decode(s.ksuseblocker, 4294967295,
to_number(null),4294967294,to_number(null), 4294967293,to_number(null), 4294967292,to_number(null),4294967291,
to_number(null),bitand(s.ksuseblocker, 2147418112)/65536),decode(s.ksuseblocker, 4294967295,to_number(null),
4294967294,to_number(null), 4294967293,to_number(null), 4294967292,to_number(null),4294967291,
to_number(null),bitand(s.ksuseblocker, 65535)),s.ksuseseq, s.ksuseopc,e.kslednam, e.ksledp1,
s.ksusep1,s.ksusep1r,e.ksledp2, s.ksusep2,s.ksusep2r,e.ksledp3,s.ksusep3,s.ksusep3r,e.ksledclassid,
e.ksledclass#, e.ksledclass, decode(s.ksusetim,0,0,-1,-1,-2,-2, decode(round(s.ksusetim/10000),0,-1,round(s.ksusetim/10000))),
s.ksusewtm,decode(s.ksusetim, 0, ‘WAITING’, -2, ‘WAITED UNKNOWN TIME’, -1, ‘WAITED SHORT TIME’,
decode(round(s.ksusetim/10000),0,’WAITED SHORT TIME’,’WAITED KNOWN TIME’)),s.ksusesvc, decode(bitand(s.ksuseflg2,32),32,
‘ENABLED’,’DISABLED’),decode(bitand(s.ksuseflg2,64),64,’TRUE’,’FALSE’),decode(bitand(s.ksuseflg2,128),128,’TRUE’,’FALSE’)
from x$ksuse s, x$ksled e where bitand(s.ksspaflg,1)!=0 and bitand(s.ksuseflg,1)!=0 and s.ksuseopc=e.indx

//可以看到gv$session视图是由一堆x$对象的查询集合,这也验证了如下的一个”血缘”

sys : x$(内存对象))—>gv$(内存对象)–>v$(内存对象)—->v_$(view)—–>v$(public sysnonym)