Tracing Enhancements Using DBMS_MONITOR

这篇文章主要介绍dbms_monitor包中新增加的跟踪方法。新增加的跟踪过程可以基于特定的客户端标识或服务名,模块名与操作名的组合来启用诊断与工作量管理。跟踪也可以在会话级别启用。在有些情况下可以产生多个跟踪文件(例如,当对一个模块跟踪服务级别时)。trcsess工具可以用来扫描所有跟踪文件并将它们合并成一个跟踪文件。在合并之后可以使用标准跟踪文件分析方法比如tkprof。对客户端标识或服务/模块/操作的跟踪状态是永久的可以跨会话的断开与数据库的关闭,并且可以应用于所有实例。跟踪直到使用dbms_monitor禁用之前都是启用状态。

如何查看是否启用跟踪
当客户端与服务/模块/操作跨会话断开与数据库关闭永久存在时,有一种方法来判断是否启用了跟踪。当启用跟踪时,跟踪信息会被记录到dba_enabled_traces中。

SQL> select trace_type, primary_id, qualifier_id1, waits, binds from dba_enabled_traces;
TRACE_TYPE            PRIMARY_ID                              QUALIFIER_ID1                   WAITS BINDS
--------------------- --------------------------------------- ------------------------------- ----- -----
SERVICE_MODULE        SYS$USERS                               SQL*Plus                       TRUE  FALSE
CLIENT_ID             HUGO                                                                   TRUE  FALSE
SERVICE               v101_DGB                                                               TRUE  FALSE  

可以看到三个不同的跟踪被启用。
第一行:跟踪所有由SQL*Plus所执行的SQL语句
第二行:跟踪所有客户端标识符为’HUGO’的所有会话
第三行:跟踪通过服务’v101_DGB’连接到数据库的所有程序

session_trace_enable函数
session_trace_enable对本地实例的指定会话启用SQL跟踪,语法如下:
启用跟踪

dbms_monitor.session_trace_enable(session_id=>x,serial_num=>y,waits=>(TRUE|FALSE),binds=>(TRUE|FALSE));

禁用跟踪

dbms_monitor.session_trace_disable(session_id=>x,serial_num=>y);

缺省情况下跟踪对于等待为true,而绑定变量为false

通过查询v$session得到会话与serial号

SQL> select sid, serial#,username from  v$session;

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
         2       3257 INSUR_CHANGDE
         4      45331
        41      20923 INSUR_CHANGDE
        42      19225 INSUR_CHANGDE
        77          1
        78       2191 CARD_DB
       115          1
       118      47221 YBCX
       153          1
       157      25173 INSUR_CHANGDE
       191          1

执行下面的命令开始跟踪

SQL> execute dbms_monitor.session_trace_enable(157,25173);

PL/SQL procedure successfully completed.

需要注意的是在dba_enabled_traces视图中没有记录,因为跟踪并没有经历数据库关闭。

可以通过查询v$session得到被跟踪会话列表:

SQL> select sid,serial#,username,sql_trace_waits,sql_trace_binds,sql_trace_plan_stats
  2  from   v$session 
  3  where  sql_trace = 'enabled'; 

no rows selected

当会话断羡慕或执行以下命令可以停止跟踪

SQL> execute dbms_monitor.session_trace_disable(157,25173);

PL/SQL procedure successfully completed.

client_id_trace_enable函数
在多层架构环境中,来自终端用户的一个请求将会通过中间层被路由到不同的数据库会话。这意味着在终端客户端与数据库会话之间不存在静态关联。10g之前的版本,没有一种简单方法来跨不同数据库会话对客户端进行跟踪。通过引入新的属性client_identifier使用端对端的跟踪成为可能,它用来唯一标识一个指定的终端。客户端标识被记录在v$session视图的client_identifier列中。还可以通过系统上下文来查看。语法如下:
启用跟踪

execute dbms_monitor.client_id_trace_enable ( client_id =>'client_id', waits => (TRUE|FALSE), binds => (TRUE|FALSE) );

禁用跟踪

execute dbms_monitor.client_id_trace_disable ( client_id =>'client_id');

缺省情况下跟踪对于等待为true,绑定变量为false

下面通过使用dbms_session.set_identifier过程来设置client_identifier

SQL> execute dbms_session.set_identifier('JY');

PL/SQL procedure successfully completed.

可以通过两种方法来找到客户端标识
1.在实际会话中

SQL> select sys_context('USERENV','CLIENT_IDENTIFIER') client_id from dual;

CLIENT_ID
--------------------------------------------------------------------------------
JY

2.从不同的会话中

SQL> select client_identifier client_id from v$session where sid =18;
CLIENT_ID
----------------------------------------------------------------
JY

对客户端标识为’JY’的所有会话启用跟踪

SQL> execute dbms_monitor.client_id_trace_enable('JY');

PL/SQL procedure successfully completed.

现在这个跟踪可以跨越数据库的关闭

SQL>  select trace_type, primary_id, qualifier_id1, waits, binds from dba_enabled_traces;
TRACE_TYPE            PRIMARY_ID                                                       QUALIFIER_ID1                                                    WAITS BINDS
--------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ----- -----
CLIENT_ID             JY                                                                                                                                TRUE  FALSE

为了禁用跟踪,执行以下命令:

SQL> execute dbms_monitor.client_id_trace_disable('JY');

PL/SQL procedure successfully completed.


SQL>  select trace_type, primary_id, qualifier_id1, waits, binds from dba_enabled_traces;
TRACE_TYPE            PRIMARY_ID                                                       QUALIFIER_ID1                                                    WAITS BINDS
--------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ----- -----

这可能造成有时生成多个跟踪文件。例如,当使用共享服务器时,不同的共享服务器进程可以执行SQL语句。这将导致生成多个跟踪文件。对于RAC来说也同样会生成多个跟踪文件。后面将介绍如何使用trcsess工具将多个跟踪文件合并成一个跟踪文件。

SERV_MOD_ACT_TRACE_ENABLE函数
端到端的跟踪对于有效管理与使用services,module与action来计算应用程序工作量很有用。可以使用serv_mod_act_trace_enable函数来对服务名,模块名与操作名特定的组合对全局或特定实例启用SQL跟踪。

通过查询v$session视图的service_name,module和action列可以查看服务名,模块与操作名。语法如下:
启用跟踪

execute dbms_monitor.serv_mod_act_trace_enable('Service1', 'Module1', 'Action1', waits => (TRUE|FALSE), binds => (TRUE|FALSE), instance_name => 'ORCL' );

禁用跟踪

execute dbms_monitor.serv_mod_act_trace_disable('Service1', 'Module1', 'Action1');

缺省情况下跟踪对于等待为true,对于绑变量为false。缺省实例名为null。

示例
下面跟踪通过SQL*Plus与缺省服务SYS$USERS所执行的所有SQL语句

SQL> execute dbms_monitor.serv_mod_act_trace_enable('SYS$USERS', 'SQL*Plus' );

PL/SQL procedure successfully completed.

检查是否启用跟踪

SQL> select primary_id, qualifier_id1, waits, binds from  dba_enabled_traces where trace_type = 'SERVICE_MODULE';
PRIMARY_ID                                                       QUALIFIER_ID1                                                    WAITS BINDS
---------------------------------------------------------------- ---------------------------------------------------------------- ----- -----
SYS$USERS                                                        SQL*Plus                                                         TRUE  FALSE

禁用跟踪

SQL> execute dbms_monitor.serv_mod_act_trace_disable('SYS$USERS', 'SQL*Plus');

PL/SQL procedure successfully completed.


SQL> select primary_id, qualifier_id1, waits, binds from  dba_enabled_traces where trace_type = 'SERVICE_MODULE';
PRIMARY_ID                                                       QUALIFIER_ID1                                                    WAITS BINDS
---------------------------------------------------------------- ---------------------------------------------------------------- ----- -----

使用trcsess合并跟踪文件
有些跟踪操作会生成多个跟踪文件。 trcsess可以根据特定会话或客户端标识来合并跟踪文件。
语法如下:

trcsess [output=] [session=] [clientid=] [service=] [action=] [module=] 

会话1:

SQL> execute dbms_session.set_identifier('JY');

PL/SQL procedure successfully completed.


SQL> execute dbms_monitor.client_id_trace_enable('JY');

PL/SQL procedure successfully completed.

SQL> select 'session 1' from dual;

'SESSION1
---------
session 1

SQL> execute dbms_monitor.client_id_trace_disable('JY');

PL/SQL procedure successfully completed.

会话2:

SQL> execute dbms_session.set_identifier('JY');

PL/SQL procedure successfully completed.

SQL> execute dbms_monitor.client_id_trace_enable('JY');

PL/SQL procedure successfully completed.

SQL> select 'session 2' from dual;

'SESSION2
---------
session 2

SQL> execute dbms_monitor.client_id_trace_disable('JY');

PL/SQL procedure successfully completed.

使用trcsess合并跟踪文件

[oracle@jyrac1 trace]$ trcsess output=trcsess_Jy_Trace.txt clientid='JY'  *.trc
[oracle@jyrac1 trace]$ ls -lrt *Jy*.txt
-rw-r--r-- 1 oracle oinstall 97786 Mar  2 15:17 trcsess_Jy_Trace.txt

dbms_application_info
dbms_application_info.set_x_info过程在会话开始前调用可以用来注册并命名事务/客户端信息/模块为以后的性能检查所使用。
dbms_application_info包含以下过程:
set_client_info(client_info in varchar2)
set_action(action_name in varchar2)
set_module(module_name in varchar2,action_name in varchar2)

SQL> begin
  2  dbms_application_info.set_module(module_name => 'add_employee',action_name => 'insert into emp');
  3  insert into scott.emp (ename, empno, sal, mgr, job, hiredate, comm, deptno )
  4  values ( 'scott', 9998, 1000, 7698,'clerk', sysdate,0, 10);
  5  dbms_application_info.set_module(null,null); 
  6  end;
  7  /

PL/SQL procedure successfully completed.

下面通过使用module与action列作为查询条件来查询v$sqlarea视图来获得上面执行的SQL语句

SQL> select sql_text from v$sqlarea where module = 'add_employee' and action = 'insert into emp';

SQL_TEXT
--------------------------------------------------------------------------------
INSERT INTO SCOTT.EMP (ENAME, EMPNO, SAL, MGR, JOB, HIREDATE, COMM, DEPTNO ) VAL
UES ( 'scott', 9998, 1000, 7698,'clerk', SYSDATE,0, 10)

也可以执行以下过程来获得信息

SQL> set serveroutput on
SQL> declare
  2  l_clinent varchar2(100);
  3  l_mod_name varchar2(100);
  4  l_act_name varchar2(100);
  5  begin
  6  dbms_application_info.set_client_info('my client');
  7  dbms_application_info.read_client_info(l_clinent);
  8  dbms_output.put_line('client='||l_clinent);
  9  dbms_application_info.set_module('my mod','inserting');
 10  dbms_application_info.read_module(l_mod_name,l_act_name);
 11  dbms_output.put_line('mod_name='||l_mod_name);
 12  dbms_output.put_line('act_name='||l_act_name);
 13  end;
 14  /
client=my client
mod_name=my mod
act_name=inserting
PL/SQL procedure successfully completed

Oracle 12C 跨网络传输数据库

跨网络传输数据库,可以通过使用network_link参数来执行导入操作,导入操作将使用数据库链路,不需要生成dump文件。操作步骤如下:
1.在目标数据库中创建链接到源数据库的数据链路。执行导入操作的用户必须要有datapump_imp_full_database权限,并且连接到源数据库的数据链路也必须连接到一个有datapump_exp_full_database角色的用户。在源数据库中用户不能有sysdba管理权限。

2.在源数据库上将所有用户表空间置为只读模式

3.将源数据库中所有用户表空间相关的数据文件传输到目标数据库。如果源平台与目标平台的字节编码不同,那么查询v$transportable_platform视图来进行查看。并且将可以使用以下一种方法来转换数据文件:
.使用dbms_file_transfer包中的get_file或put_file过程来传输数据文件。这些过程会自动将数据文件的字节编码转换为目标平台的字节编码。

.使用rman的convert命令来将数据文件的字节编码转换为目标平台的字节编码。

4.在目标数据库上执行导入操作。使用Data Pump工具来导入所有用户表空间的元数据与管理表空间的元数据与真实数据。确保以下参数正确设置:
.transportable=always
.transport_datafiles=list_of_datafiles
.full=y
.network_link=database_link
.version=12
如果源数据库为11.2.0.3或11g之后的版本,那么必须设置version=12。如果源数据库与目标数据库都是12c,那么version参数不用设置。

如果源数据库包含任何加密表空间或表空间包含加密列,那么你必须指定encryption_pwd_prompt=yes或指定encryption_password参数。

Data Pump跨网络导入将会复制所有用户表空间所存储对象的元数据与管理表空间中的元与用户对象的真实数据。当导入完成后,用户表空间将会置于读写模式。

5.可选操作将源数据库中的所有用户表空间置为读写模式。

下面的例子是将源数据库jyrac传输到目标数据库jypdb
1.在目标数据库中以sys用户来创建链接到源数据库的数据链路。源数据库中的用户为jy

SQL> conn sys/xxzx7817600@jypdb as sysdba
Connected.


SQL> create public database link jyrac_link
  2    connect to jy identified by "jy"
  3    using '(DESCRIPTION =
  4      (ADDRESS_LIST =
  5        (ADDRESS = (PROTOCOL = TCP)(HOST =10.138.130.153)(PORT = 1521))
  6      )
  7      (CONNECT_DATA =
  8        (SERVER = DEDICATED)
  9        (SERVICE_NAME =jyrac)
 10      )
 11    )';

Database link created.

2.在源数据库上将所有用户表空间置为只读模式

SQL>  select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
UNDOTBS2                       ONLINE
EXAMPLE                        ONLINE
TEST                           ONLINE

8 rows selected.

SQL> alter tablespace test read only;

Tablespace altered.

SQL> alter tablespace users read only;

Tablespace altered.

SQL> alter tablespace example read only;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          READ ONLY
UNDOTBS2                       ONLINE
EXAMPLE                        READ ONLY
TEST                           READ ONLY

8 rows selected.

3.在目标数据库中使用dbms_file_transfer包中的get_file过程将源数据库中所有用户表空间相关的数据文件传输到目标数据库上
在源数据库中创建目录tts_datafile(存储数据文件)

SQL> create or replace directory tts_datafile as '+datadg/jyrac/datafile/';

Directory created.

SQL> grant execute,read,write on directory tts_datafile to public;

Grant succeeded.

在目标数据库中创建目录tts_datafile(存储数据文件)

SQL> create or replace directory tts_datafile as '+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/';

Directory created.

SQL> grant execute,read,write on directory tts_datafile to public;

Grant succeeded.

在目标数据库中执行dbms_file_transfer.get_file过程将源数据库中所有用户表空间所相关的数据文件传输到目标数据库中

SQL> exec dbms_file_transfer.get_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'test01.dbf',source_database =>'jyrac_link',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'test01.dbf');

PL/SQL procedure successfully completed.

SQL> exec dbms_file_transfer.get_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'example.260.930413057',source_database =>'jyrac_link',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'example01.dbf');

PL/SQL procedure successfully completed.

SQL> exec dbms_file_transfer.get_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'users.263.930413057',source_database =>'jyrac_link',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'users01.dbf');

PL/SQL procedure successfully completed.
ASMCMD [+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile] > ls -lt
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  N    users01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.298.945620417
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  N    test01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.300.945620337
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  N    example01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.299.945620391
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    FILE_TRANSFER.300.945620337
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    FILE_TRANSFER.299.945620391
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    FILE_TRANSFER.298.945620417
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  N    testtb01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/TESTTB.295.944828399
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    UNDO_2.277.939167063
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    UNDOTBS2.278.945029905
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    UNDOTBS1.273.939167015
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    TESTTB.295.944828399
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    SYSTEM.274.939167015
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    SYSAUX.275.939167015

4.在目标数据库上执行导入操作。使用Data Pump工具来导入所有用户表空间的元数据与管理表空间的元数据与真实数据。

[oracle@jytest1 tts]$ impdp system/xxzx7817600@JYPDB_175 full=y network_link=jyrac_link transportable=always transport_datafiles='+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/test01.dbf','+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/example01.dbf','+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/users01.dbf' version=12 directory=TTS_DUMP_LOG logfile=import.log

Import: Release 12.2.0.1.0 - Production on Fri Jun 2 16:30:40 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@JYPDB_175 full=y network_link=jyrac_link transportable=always transport_datafiles=+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/test01.dbf,+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/example01.dbf,+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/users01.dbf version=12 directory=TTS_DUMP_LOG logfile=import.log
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/PLUGTS_FULL/FULL/PLUGTS_TABLESPACE
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
.......
Processing object type DATABASE_EXPORT/SCHEMA/DIMENSION
Processing object type DATABASE_EXPORT/END_PLUGTS_BLK
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
ORA-39082: Object type PROCEDURE:"APEX_030200"."F" created with compilation warnings

ORA-39082: Object type PROCEDURE:"APEX_030200"."APEX_ADMIN" created with compilation warnings

ORA-39082: Object type PROCEDURE:"APEX_030200"."HTMLDB_ADMIN" created with compilation warnings

Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1689 error(s) at Fri Jun 2 16:43:47 2017 elapsed 0 00:13:03


传输完成后我们抽查用户jy的dba_tables表的数据在传输后是否与源数据库中的数据一致。
源数据库

SQL> conn sys/xxzx7817600@jyrac as sysdba
Connected.

SQL> select count(*) from jy.dba_tables;

  COUNT(*)
----------
      2141

目标数据库

SQL> conn sys/xxzx7817600@jypdb as sysdba
Connected.

SQL> select count(*) from jy.dba_tables;

  COUNT(*)
----------
      2141

查询传输后用户表空间的状态是否为online,可以看到test,example,users表空间状态为online

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
UNDO_2                         ONLINE
USERS                          ONLINE
TESTTB                         ONLINE
TEMP2                          ONLINE
TEMP3                          ONLINE
EXAMPLE                        ONLINE
TEST                           ONLINE
UNDOTBS2                       ONLINE

12 rows selected.

5.将源数据库中的所有用户表空间设置为读写模式

SQL> alter tablespace test read write;

Tablespace altered.

SQL> alter tablespace example read write;

Tablespace altered.

SQL> alter tablespace users read write;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
UNDOTBS2                       ONLINE
EXAMPLE                        ONLINE
TEST                           ONLINE

8 rows selected.

到此通过网络执行完整数据库传输的操作就完成了。

Proudly powered by WordPress | Indrajeet by Sus Hill.