[20161123]failover与会话参数.txt
--前几天itpub上有人问的问题，链接http://www.itpub.net/thread-2071933-1-1.html，开始我以为是会话没有退出。
--实际上对方已经重启过数据库，不过后来我估计对方打开登录审计，从结果上猜测是正确的，不过我的问题是我想了解
--会话在支持failover时，重新连接上时会话参数是否还是原来的，还有审计是否还有效(在已经关闭相关审计的情况下).
--测试是最好的证明，还是通过例子来说明问题。
1.环境：
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> create table t as select rownum id , lpad('a',10,'a') name from dual connect by level<=10;
Table created.
--打开审计：
SCOTT@book> AUDIT update ON t by session WHENEVER NOT SUCCESSFUL ;
Audit succeeded.
2.建立连接串：
--//参考链接http://blog.itpub.net/267265/viewspace-761721/
78G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 1521))
)
(SDU = 32768)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = book)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)
--//连接数据库
SCOTT@78G> @ &r/spid
SID SERIAL# SPID PID P_SERIAL# C50
---------- ---------- ------ ------- ---------- --------------------------------------------------
46 249 19216 28 125 alter system kill session '46,249' immediate;
SCOTT@78G> column service_name format a30
SCOTT@78G> select SERVICE_NAME,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER from v$session where sid in ( select sid from v$mystat where rownum<=1);
SERVICE_NAME FAILOVER_TYPE FAILOVER_M FAI
------------------------------ ------------- ---------- ---
book SELECT BASIC NO
SCOTT@78G> show parameter optimizer_index_cost_adj
NAME TYPE VALUE
------------------------ -------- -------------
optimizer_index_cost_adj integer 100
--//修改会话参数optimizer_index_cost_adj=50;
SCOTT@78G> alter session set optimizer_index_cost_adj=50;
Session altered.
SCOTT@78G> update t set name=lpad('b',11,'b') where id=1;
update t set name=lpad('b',11,'b') where id=1
*
ERROR at line 1:
ORA-12899: value too large for column "SCOTT"."T"."NAME" (actual: 11, maximum: 10)
SCOTT@78G> select sql_text from DBA_AUDIT_OBJECT where obj_name='T';
SQL_TEXT
------------------------------------------------------------
update t set name=lpad('b',11,'b') where id=1
--//可以发现update失败审计是生效的。
3.先测试取消审计看看：
--选择另外的会话登录取消审计。
SCOTT@book> noAUDIT update ON t ;
Noaudit succeeded.
--回到原来会话执行：
SCOTT@78G> update t set name=lpad('b',12,'b') where id=1;
update t set name=lpad('b',12,'b') where id=1
*
ERROR at line 1:
ORA-12899: value too large for column "SCOTT"."T"."NAME" (actual: 12, maximum: 10)
SCOTT@78G> select sql_text from DBA_AUDIT_OBJECT where obj_name='T';
SQL_TEXT
------------------------------------------------------------
update t set name=lpad('b',11,'b') where id=1
--//可以发现对于审计只要发出了取消，对于当前会话也会生效，看来这个是我错误的观点。
4.测试重启数据库会话参数是否有效。
SCOTT@78G>commit ;
Commit complete.
--//注意要提交前面的事务。
SCOTT@78G> select * from dba_objects;
...
--//在输出时打开另外的会话关闭数据库，加一点点极端执行的是。
SYS@book> shutdown abort;
ORACLE instance shut down.
SYS@book> startup
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
Database mounted.
Database opened.
SYS@book>
--你可以发现会话hang在那里。数据库启动完成后 ，session重新连上继续执行显示输出。等待结束。
SCOTT@78G> @ &r/spid
SID SERIAL# SPID PID P_SERIAL# C50
---------- ---------- ------ ------- ---------- --------------------------------------------------
244 1 19383 22 3 alter system kill session '244,1' immediate;
SCOTT@78G> select SERVICE_NAME,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER from v$session where sid in ( select sid from v$mystat where rownum<=1);
SERVICE_NAME FAILOVER_TYPE FAILOVER_M FAI
------------------------------ ------------- ---------- ---
book SELECT BASIC YES
--对比前面可以发现sid，spid都发生了变化。FAILED_OVER='yes',表示出现了failover。
SCOTT@78G> show parameter optimizer_index_cost_adj
NAME TYPE VALUE
------------------------- -------- -------------
optimizer_index_cost_adj integer 100
--可以发现会话参数发生了变化。optimizer_index_cost_adj又变成了100(开始是50).
5.再重复一次：
SCOTT@78G> alter session set cursor_sharing=force ;
Session altered.
SCOTT@78G> alter session set optimizer_index_cost_adj=30;
Session altered.
SCOTT@78G> select * from dba_objects;
...
SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@book> startup
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
Database mounted.
Database opened.
SCOTT@78G> @&r/spid
SID SERIAL# SPID PID P_SERIAL# C50
---------- ---------- ------ ------- ---------- --------------------------------------------------
100 5 19539 9 3 alter system kill session '100,5' immediate;
SCOTT@78G> select SERVICE_NAME,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER from v$session where sid in ( select sid from v$mystat where rownum<=1);
SERVICE_NAME FAILOVER_TYPE FAILOVER_M FAI
------------------------------ ------------- ---------- ---
book SELECT BASIC YES
SCOTT@78G> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ ---------------------------------------- ---------------
cursor_sharing string EXACT
SCOTT@78G> show parameter optimizer_index_cost_adj
NAME TYPE VALUE
------------------------------------ ---------------------------------------- --------
optimizer_index_cost_adj integer 100
总结：
1.可以发现failover后会话参数会变化。
2.审计取消后对当前会话也取消相关审计。