[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.審計取消後對當前會話也取消相關審計。

[20161123]failover與會話引數.txt

相關文章