Re: dbms_monitor.SERV_MOD_ACT_TRACE_ENABLE cannot trace service in job class

From: Steve Howard <stevedhoward_at_gmail.com>
Date: Mon, 17 May 2010 04:06:26 -0700 (PDT)
Message-ID: <49150513-2cdf-4a19-8829-3ba37116a163_at_c7g2000vbc.googlegroups.com>

On May 16, 6:48 am, lsllcm <lsl..._at_gmail.com> wrote:
> Hi All,
>
> I have a issue about dbms_monitor.SERV_MOD_ACT_TRACE_ENABLE. It can
> trace sqlplus query. But it cannot trace service in job class.
>
> I am appreciated if anyone can help.
>
> Below is test steps
>
> 1. add service
> srvctl add service -d rdb -s oltp -r rdb1 -a rdb2
>
> 2. startup service
> srvctl start service -d rdb -s oltp
>
> 3. using service in tnsnames.ora
> oltp =
>   (DESCRIPTION =
>     (LOAD_BALANCE = ON)
>     (FAILOVER = ON)
>     (ADDRESS = (PROTOCOL = TCP)(HOST = rdb1)(PORT = 1521))
>     (ADDRESS = (PROTOCOL = TCP)(HOST = rdb2)(PORT = 1521))
>     (CONNECT_DATA =
>       (SERVICE_NAME = OLTP)
>       (FAILOVER_MODE =
>         (TYPE = SELECT)
>         (METHOD = BASIC)
>         (RETRIES = 20)
>         (DELAY = 1)
>       )
>     )
>   )
>
> 4. open trace
> dbms_monitor.SERV_MOD_ACT_TRACE_ENABLE('oltp');
>
> 5. run one query and check trace file
>
> sqlplus /nolog
> conn sys/sys_at_oltp as sysdba
> select * from test;
>
> in $ORACLE_BASE/admin/rdb/udump/rdb1_ora_22960.trc
> There is the query: select * from test
>
> 6. create job class with service
> BEGIN
>   DBMS_SCHEDULER.create_job_class(
>     job_class_name => 'OLTP_JOB_CLASS',
>     service        => 'OLTP');
> END;
> /
>
> 7. create job
>
> BEGIN
>   DBMS_SCHEDULER.create_job (
>     job_name        => 'my_job',
>     job_type        => 'PLSQL_BLOCK',
>     job_action      => 'insert into test values (sysdate);',
>     start_date      => SYSTIMESTAMP,
>     repeat_interval => 'FREQ=MINUTELY; INTERVAL=10;',
>     job_class       => 'OLTP_JOB_CLASS',
>     end_date        => SYSDATE + 7,
>     enabled         => TRUE,
>     comments        => 'Job linked to the OLTP_JOB_CLASS.');
> END;
> /
>
> 8. very result the job
>
> select to_char(c1, 'YYYY-MM-DD HH24:MI') from test;
> -------------------------------------
> 2010-05-16 17:17
> 2010-05-16 17:27
>
> 9. check trace file
> in $ORACLE_BASE/admin/rdb/udump/ directory, there is no query like
> insert into test values (sysdate);
>
> Thanks in advance
> Jacky

Hi Jacky,

What version? If 10.2.0.3, see...

Bug 5658143 DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE cannot set trace

...on MOS.

HTH, Steve Received on Mon May 17 2010 - 06:06:26 CDT