--========================

-- 啟用用戶進程跟蹤

--========================

?

一、用戶進程跟蹤文件

??? 用戶跟蹤文件在根據需要跟蹤會話實際操作的時候根據要求產生

??? 通常用于幫助調整應用程序 , 比如檢查由 SQL 的不良寫法所致的相關問題等等

??? 由用戶進程發出，服務器進程產生該類文件

??? 包含跟蹤 SQL 命令的統計信息、包含用戶的錯誤信息

??? 缺省情況下當用戶出現會話錯誤時產生

??? 位置由 user_dump_dest 設定

??? 文件大小由 max_dump_file_size 決定

??? 可以設定記錄會話的所有信息

??? 分為基于會話級別和基于實例級別 , 大多數情況下 , 在 session 級別進行跟蹤

???

??? 在專用服務器模式中

??????? 僅僅需要標識該會話并為該會話啟用跟蹤 ( 專用模式為一對一模式，即一個用戶進程對應一個服務器進程 )

??? 在共享模式中

??????? 對任何一個會話的跟蹤會分布到每個共享服務器進程所生成的跟蹤文件內

?

二、不同級別的跟蹤 ?

??? instance level ：

??????? alter system set sql_trace = true ;

???????

??? session level :

??????? 使用 alter session 命令啟用跟蹤

??????? alter session set sql_trace = true ;

??????? 使用 dbms 包來啟用跟蹤

??????? dbms_system . SET_SQL_TRACE_IN_SESSION

???????????

??? 上述兩類級別跟蹤文件位置由 user_dump_dest 設定，且生成的跟蹤文件名為 < SID > _ora_ < SPID >. trc

??? 其中 SID 為實例名稱， SPID 為系統進程號 ( Unix ) 或線程號 ( Windows )

???

??? 1. 演示基于實例級別的跟蹤

??????? SQL > show parameter user_dump ?? -- 查看用戶跟蹤文件所在的位置

?

??????? NAME ???????????????????????????????? TYPE ??????? VALUE

??????? ------------------------------------ ----------- ------------------------------

??????? user_dump_dest ?????????????????????? string ????? / u01 / app / oracle / admin / orcl / udu

??????????????????????? ??????????????????????????????? ? mp

??????? SQL > ho rm - f / u01 / app / oracle / admin / orcl / udump /* ? -- 清除跟蹤文件夾下先前產生的跟蹤文件 */

?

??????? SQL > alter system set sql_trace = true ; ?????????? -- 設置 sql_trace 參數啟用實例級別的跟蹤

?

??????? System altered . ????

???????

??????? -- 開啟一個會話連接

??????? SQL > conn scott / tiger@orcl

??????? Connected .

?

??????? SQL > select s . username , s . sid , p . spid from v$session s , v$process p

??????? ? 2 ? where s . paddr = p . addr and s . username = 'SCOTT' ; -- 獲取需要跟蹤的特定 SPID

?

??????? USERNAME ????????????????????????????? SID SPID

??????? ------------------------------ ---------- ------------

??????? SCOTT ????????????? ??????????????????? 134 7826

?

??????? [oracle@robinson ~]$ ls $ORACLE_BASE / admin / orcl / udump

??????? orcl_ora_4457 . trc ? orcl_ora_4462 . trc ? orcl_ora_6283 . trc ? orcl_ora_7826 . trc

??????? orcl_ora_4459 . trc ? orcl_ora_5027 . trc ? orcl_ora_6524 . trc ? orcl_ora_7833 . trc

???????

??????? -- 使用 tail -f 逐步跟蹤會話文件

??????? [oracle@robinson ~]$ tail - f $ORACLE_BASE / admin / orcl / udump / orcl_ora_7826 . trc

??????? =====================

??????? PARSING IN CURSOR #3 len = 40 dep = 0 uid = 54 oct = 3 lid = 54 tim = 1253678938645444 hv = 3933222116 ad = '2dbb8df8'

??????? SELECT DECODE ( 'A' , 'A' , '1' , '2' ) FROM DUAL

??????? END OF STMT

??????? PARSE #3 : c = 1000 , e = 1076 , p = 0 , cr = 0 , cu = 0 , mis = 1 , r = 0 , dep = 0 , og = 1 , tim = 1253678938645434

??????? EXEC #3 : c = 0 , e = 41 , p = 0 , cr = 0 , cu = 0 , mis = 0 , r = 0 , dep = 0 , og = 1 , tim = 1253678938645588

??????? FETCH #3 : c = 0 , e = 20 , p = 0 , cr = 0 , cu = 0 , mis = 0 , r = 1 , dep = 0 , og = 1 , tim = 1253678938645673

??????? STAT #3 id = 1 cnt = 1 pid = 0 pos = 1 obj = 0 op = 'FAST DUAL ? (cr=0 pr=0 pw=0 time=8 us)'

??????? XCTEND rlbk = 0 , rd_only = 1

??????? XCTEND rlbk = 0 , rd_only = 1

??????? *** 2010 - 09 - 06 18 : 04 : 56.482

??????? =====================

??????? PARSING IN CURSOR #4 len = 44 dep = 0 uid = 54 oct = 3 lid = 54 tim = 1253679195783434 hv = 24946478 ad = '2d43cb14'

??????? select * from scott . emp where ename = 'SCOTT'

??????? END OF STMT

??????? PARSE #4 : c = 1999 , e = 2262 , p = 0 , cr = 0 , cu = 0 , mis = 1 , r = 0 , dep = 0 , og = 1 , tim = 1253679195783423

??????? EXEC #4 : c = 0 , e = 52 , p = 0 , cr = 0 , cu = 0 , mis = 0 , r = 0 , dep = 0 , og = 1 , tim = 1253679195783643

??????? FETCH #4 : c = 0 , e = 208 , p = 0 , cr = 7 , cu = 0 , mis = 0 , r = 1 , dep = 0 , og = 1 , tim = 1253679195783930

??????? FETCH #4 : c = 0 , e = 73 , p = 0 , cr = 1 , cu = 0 , mis = 0 , r = 0 , dep = 0 , og = 1 , tim = 1253679195826698

??????? STAT #4 id = 1 cnt = 1 pid = 0 pos = 1 obj = 51148 op = 'TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=194 us)' ???

???????

??????? -- 在 scott 登陸的會話中執行下面的語句，則跟蹤文件 orcl_ora_7826.trc 同步顯示所執行的數據操縱語句

??????? SQL > select * from scott . emp where ename = 'SCOTT' ;

?

??????????? ? EMPNO ENAME ??????????????? JOB ?????????????????????? MGR HIREDATE

??????? ---------- -------------------- ------------------ ---------- -------------

??????????? ?? SAL ?????? COMM ???? DEPTNO

??????? ---------- ---------- ----------

??????????? ? 7788 SCOTT ??????????????? ANALYST ????????????????? 7566 19 - 4 月 - 87

??????????? ? 3100 ??????????????????? 20 ???

???????

??????? -- 由上可知，整個語句的執行詳細處理步驟，最后提示使用了全表掃描

??????? 關于實例級別的跟蹤 , 專用服務器模式每個服務器進程都會生成自己的跟蹤信息流

??????????? 共享模式則每個共享服務器都存在一個跟蹤文件 , 且跟蹤文件包含的跟蹤信息與該共享服務器為其服務的所有會話

??????????? 而執行的所有 SQL 語句相關

???????

??????? -- 關閉實例級別的跟蹤

??????? SQL > alter system set sql_trace = false ;

?

??????? System altered .

???????

??? 2. 演示基于會話級別的跟蹤

??????? 一個會話自身能夠交互地啟用針對該會話的跟蹤 , 其它會話也可以編程啟用針對該會話進行跟蹤

??????? -- 清理上次跟蹤的會話文件，且同樣可以使用上面的方式來進行跟蹤，只不過修改跟蹤的參數不同而已

??????? SQL > ho rm - f / u01 / app / oracle / admin / orcl / udump /* ?????? --*/

???????

??????? SQL > conn system / redhat

??????? Connected .

??????? SQL > alter session set sql_trace = true ;

?

??????? Session altered .

?

??????? -- 執行 SQL 語句則開始跟蹤，跟蹤完畢后執行下面的語句關閉跟蹤

??????? -- 此處跟蹤的方法同實例級別跟蹤

?

??????? SQL > alter session set sql_trace = false ;

?

??????? Session altered .

?

??????? -- 開啟另一會話使用 dbms 包進行跟蹤

??????? SQL > conn system / redhat

??????? Connected .

??????? SQL > select sid , serial# from v$session where username = 'SYSTEM' ;

?

??????????? ?? SID ??? SERIAL#

??????? ---------- ----------

??????????? ?? 133 ?????? 1141

??????????? ??

??????? SQL > exec dbms_monitor . session_trace_enable ( session_id => 133 , serial_num => 1141 );

?

??????? PL / SQL procedure successfully completed .

?

??????? SQL > update scott . emp set sal = sal + 500 where ename = 'SCOTT' ;

?

??????? 1 row updated .

?

??????? SQL > exec dbms_monitor . session_trace_disable ( session_id => 133 , serial_num => 1141 );

?

??????? PL / SQL procedure successfully completed .

?

?

??????? SQL > select s . username , s . sid , p . spid from v$session s , v$process p

??????? ? 2 ?? where s . paddr = p . addr and s . sid = 133 ; ????????

?

??????? USERNAME ????????????????????????????? SID SPID

??????? ------------------------------ ---------- ------------

??????? SYSTEM ??????????????????????????????? 133 10170

?

??????? SQL > ho cat $ORACLE_BASE / admin / orcl / udump / orcl_ora_10170 . trc

??????? / u01 / app / oracle / admin / orcl / udump / orcl_ora_10170 . trc

??????? Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

??????? With the Partitioning , OLAP and Data Mining options

??????? ORACLE_HOME = / u01 / app / oracle / 10g

??????? System name : ??? Linux

??????? Node name : ????? robinson

??????? Release : ??????? 2.6.18 - 164.el5

??????? Version : ??????? #1 SMP Tue Aug 18 15 : 51 : 54 EDT 2009

??????? Machine : ??????? i686

??????? Instance name : orcl

??????? Redo thread mounted by this instance : 1

??????? Oracle process number : 18

??????? Unix process pid : 10170 , image : oracle@robinson ( TNS V1 - V3 )

?

??????? *** 2010 - 09 - 06 19 : 49 : 33.906

??????? *** ACTION NAME :() 2010 - 09 - 06 19 : 49 : 33.903

??????? *** MODULE NAME :( SQL * Plus ) 2010 - 09 - 06 19 : 49 : 33.903

??????? *** SERVICE NAME :( SYS$USERS ) 2010 - 09 - 06 19 : 49 : 33.903

??????? *** SESSION ID :( 133.1141 ) 2010 - 09 - 06 19 : 49 : 33.903

??????? =====================

??????? PARSING IN CURSOR #2 len = 81 dep = 0 uid = 5 oct = 47 lid = 5 tim = 1253685326077891 hv = 1816613408 ad = '2dbf7d40'

??????? BEGIN dbms_monitor . session_trace_enable ( session_id => 133 , serial_num => 1141 ); END ;

??????? END OF STMT

??????? EXEC #2 : c = 0 , e = 466 , p = 0 , cr = 0 , cu = 0 , mis = 1 , r = 1 , dep = 0 , og = 1 , tim = 1253685326077881

??????? WAIT #2 : nam = 'SQL*Net message to client' ela = 4 driver id = 1650815232 #bytes = 1 p3 = 0 obj# =- 1 tim = 1253685326080388

??????? WAIT #2 : nam = 'SQL*Net message from client' ela = 1823 driver id = 1650815232 #bytes = 1 p3 = 0 obj# =- 1 tim = 1253685326082309

??????? WAIT #0 : nam = 'SQL*Net message to client' ela = 2 driver id = 1650815232 #bytes = 1 p3 = 0 obj# =- 1 tim = 1253685326082495

??????? *** 2010 - 09 - 06 19 : 50 : 20.709

??????? WAIT #0 : nam = 'SQL*Net message from client' ela = 45704475 driver id = 1650815232 #bytes = 1 p3 = 0 obj# =- 1 tim = 1253685371787005

??????? =====================

??????? PARSING IN CURSOR #1 len = 58 dep = 0 uid = 5 oct = 6 lid = 5 tim = 1253685371793390 hv = 3123987885 ad = '2dbf7aa4'

??????? update scott . emp set sal = sal + 500 where ename = 'SCOTT'

??????? END OF STMT

??????? PARSE #1 : c = 4000 , e = 6173 , p = 0 , cr = 0 , cu = 0 , mis = 1 , r = 0 , dep = 0 , og = 1 , tim = 1253685371793377

??????? EXEC #1 : c = 999 , e = 1381 , p = 0 , cr = 7 , cu = 3 , mis = 0 , r = 1 , dep = 0 , og = 1 , tim = 1253685371794945

??????? WAIT #1 : nam = 'SQL*Net message to client' ela = 3 driver id = 1650815232 #bytes = 1 p3 = 0 obj# =- 1 tim = 1253685371795058

??????? WAIT #1 : nam = 'SQL*Net message from client' ela = 4984 driver id = 1650815232 #bytes = 1 p3 = 0 obj# =- 1 tim = 1253685371800090

??????? STAT #1 id = 1 cnt = 0 pid = 0 pos = 1 obj = 0 op = 'UPDATE ? EMP (cr=7 pr=0 pw=0 time=993 us)'

??????? STAT #1 id = 2 cnt = 1 pid = 1 pos = 1 obj = 51148 op = 'TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=272 us)'

??????? WAIT #0 : nam = 'SQL*Net message to client' ela = 2 driver id = 1650815232 #bytes = 1 p3 = 0 obj# =- 1 tim = 1253685371800388

??????? *** 2010 - 09 - 06 19 : 50 : 53.414

??????? WAIT #0 : nam = 'SQL*Net message from client' ela = 31924456 driver id = 1650815232 #bytes = 1 p3 = 0 obj# =- 1 tim = 1253685403724882

??????? =====================

??????? PARSING IN CURSOR #2 len = 82 dep = 0 uid = 5 oct = 47 lid = 5 tim = 1253685403729071 hv = 3536340123 ad = '2dbf7744'

??????? BEGIN dbms_monitor . session_trace_disable ( session_id => 133 , serial_num => 1141 ); END ;

??????? END OF STMT

??????? PARSE #2 : c = 1000 , e = 4016 , p = 0 , cr = 0 , cu = 0 , mis = 1 , r = 0 , dep = 0 , og = 1 , tim = 1253685403729061

??? ??? EXEC #2 : c = 0 , e = 150 , p = 0 , cr = 0 , cu = 0 , mis = 0 , r = 1 , dep = 0 , og = 1 , tim = 1253685 ???

?

三、啟用會話跟蹤查看 show parameter sga 的源碼

??????? SQL > conn system / redhat ? -- 使用 system 帳戶連接

??????? Connected .

??????? SQL > alter session set sql_trace = true ; ? -- 啟用會話跟蹤

?

??????? Session altered .

?

??????? SQL > show parameter sga ?????????????????? -- 查看 sga

?

??????? NAME ???????????????????????????????? TYPE ??????? VALUE

??????? ------------------------------------ ----------- ------------------------------

??????? lock_sga ???????????????????????????? boolean ???? FALSE

??????? pre_page_sga ?????????????????? ?????? boolean ???? FALSE

??????? sga_max_size ???????????????????????? big integer 448M

??????? sga_target ?????????????????????????? big integer 448M

?

??????? SQL > alter session set sql_trace = false ; ? -- 關閉會話跟蹤

?

??????? Session altered .

?

??????? SQL > select s . username , s . sid , p . spid from v$session s , v$process p

??????? ? 2 ? where s . paddr = p . addr and s . username = 'SCOTT' ; -- 獲取需要跟蹤的特定 SPID

?

??????? USERNAME ????????????????????????????? SID SPID

??????? ------------------------------ ---------- ------------

??????? SYSTEM ??????????????????????????????? 159 3365

???????

??? ??? SQL > ho cat $ORACLE_BASE / admin / orcl / udump / orcl_ora_3365 . trc -- 查看跟蹤文件

???

??????? -- 以下內容為執行 show parameter sga 的實際操作語句 , 實質上是查詢 v$parameter 視圖

??????????? SELECT NAME NAME_COL_PLUS_SHOW_PARAM ,

??????????????? DECODE ( TYPE , 1 , 'boolean' , 2 , 'string' , 3 , 'integer' , 4 , 'file' , 5 , 'number' , 6 , 'big integer' , 'unknown' ) TYPE ,

??????????????? DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM

??????????? FROM V$PARAMETER WHERE UPPER ( NAME ) LIKE UPPER ( '%sga%' )

??????????? ORDER BY NAME_COL_PLUS_SHOW_PARAM , ROWNUM

?

四、啟用實例級別會話跟蹤產生的問題 ?????????

??? 使用下面的語句啟用實例級別會話跟蹤后

??????? alter system set sql_trace = true ;

??? 再將其關閉后，重新啟動實例出現下列提示

???

??????? SQL > startup ??? -- 啟動實例

??????? ORA - 32004 : obsolete and/or deprecated parameter ( s ) specified

??????? ORACLE instance started .

?

??????? Total System Global Area ? 469762048 bytes

??????? Fixed Size ????????????????? 1220048 bytes

??????? Variable Size ???????????? 180355632 bytes

??????? Database Buffers ????????? 285212672 bytes

??????? Redo Buffers ??????????????? 2973696 bytes

??????? Database mounted .

??????? Database opened .

?

??????? [oracle@robinson ~]$ oerr ora 32004 ? -- 查看具體的錯誤信息描述有參數設置不妥

??????? 32004 , 00000 , "obsolete and/or deprecated parameter(s) specified"

??????? // * Cause : ? One or more obsolete and/or parameters were specified in

??????? // ????????? the SPFILE or the PFILE on the server side .

??????? // * Action : See alert log for a list of parameters that are obsolete .

??????? // ????????? or deprecated . Remove them from the SPFILE or the server

??????? // ????????? side PFILE .

???????

??????? -- 查看告警日志

??????? SQL > ho cat $ORACLE_BASE / admin / orcl / bdump / alert_orcl . log

???????????

??????????? Deprecated system parameters with specified values :

??????????????????? ? sql_trace ???? -- 描述為 sql_trace ????????????

??????????????????? End of deprecated system parameter listing ?

???????????????????

??????? SQL > show parameter sql_trace ; ? -- 查看該參數已經置為 false

?

??????? NAME ???????????????????????????????? TYPE ??????? VALUE

??????? ------------------------------------ ----------- ------------------------------

??????? sql_trace ??????????????????????????? boolean ???? FALSE ?????

?

??????? -- 使用 reset 將其從參數文件中清除后啟動正常

??????? SQL > alter system reset sql_trace scope = spfile sid = '*' ;

?

??????? System altered .

?

??????? SQL > startup force ;

??????? ORACLE instance started .

?

??????? Total System Global Area ? 469762048 bytes

??????? Fixed Size ????????????????? 1220048 bytes

??????? Variable Size ???????????? 184549936 bytes

??????? Database Buffers ????????? 281018368 bytes

??????? Redo Buffers ??????????????? 2973696 bytes

??????? Database mounted .

??????? Database opened .

???????

五、更多 ???????

???

Oracle 冷備份

?

SPFILE 錯誤導致數據庫無法啟動

?

Oracle 用戶、對象權限、系統權限

?

Oracle 角色、配置文件

?

? Oracle 聯機重做日志文件(ONLINE LOG FILE)

?

? Oracle 控制文件(CONTROLFILE)

?

? Oracle 表空間與數據文件

?

Oracle 歸檔日志

?

啟用用戶進程跟蹤