Oracle File watcher - diagnostic

Filewatcher is a new scheduler object with job triggered by the arrival new file in specified location.
I don't want describe configuration, but how to get more information about how it's work.
Normally, there is no information why sometimes files are not processed. I have not found the table with logs.

For more information, we must enable trace level on database: 

alter system set events '27401 trace name context forever, level 262144';

After that our instance will create trace files for all new files in filewatcher location.

To diable this trace we have to do:

alter system set events '27401 trace name context off';

Audit queries on Read Only standby

I was asked about the possibility of audit queries on the physical standby database
 open in read mode.

Just turn on the audit select on primary  database:

AUDIT ALL BY TEST_USER BY ACCESS;
AUDIT SELECT TABLE BY TEST_USER BY ACCESS;

Standby is open in read only mode  and audit trails can't be written to the database since it's read only. So, where?

Audit trail automaticly switch to OS files when database is open in read only mode.

SQL> show parameter audit_trail

NAME                                 TYPE        VALUE
audit_trail                          string      OS
 

 Here is a example of audit trail file:

Thu May 16 11:57:09 2013 +03:00
LENGTH: "253"
SESSIONID:[10] "4294967295" ENTRYID:[2] "13" STATEMENT:[1] "8" USERID:[2] "TEST_USER" USERHOST:[12] "ro_standby" TERMINAL:[5] "pts/0" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$N
AME:[4] "DUAL" OS$USERID:[6] "oracle" DBID:[10] "1234567890"

Thu May 16 11:57:30 2013 +03:00
LENGTH: "257"
SESSIONID:[10] "4294967295" ENTRYID:[2] "14" STATEMENT:[2] "11" USERID:[2] "TEST_USER" USERHOST:[12] "ro_standby" TERMINAL:[5] "pts/0" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$
NAME:[7] "X$KCCDI" OS$USERID:[6] "oracle" DBID:[10] "1234567890"

Thu May 16 11:57:30 2013 +03:00
LENGTH: "258"
SESSIONID:[10] "4294967295" ENTRYID:[2] "15" STATEMENT:[2] "11" USERID:[2] "TEST_USER" USERHOST:[12] "ro_standby" TERMINAL:[5] "pts/0" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$
NAME:[8] "X$KCCDI2" OS$USERID:[6] "oracle" DBID:[10] "1234567890"

Thu May 16 11:57:30 2013 +03:00
LENGTH: "262"
SESSIONID:[10] "4294967295" ENTRYID:[2] "16" STATEMENT:[2] "11" USERID:[2] "TEST_USER" USERHOST:[12] "ro_standby" TERMINAL:[5] "pts/0" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$
NAME:[11] "GV$DATABASE" OS$USERID:[6] "oracle" DBID:[10] "1234567890"

Thu May 16 11:57:30 2013 +03:00
LENGTH: "261"
SESSIONID:[10] "4294967295" ENTRYID:[2] "17" STATEMENT:[2] "11" USERID:[2] "TEST_USER" USERHOST:[12] "ro_standby" TERMINAL:[5] "pts/0" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$
NAME:[10] "V$DATABASE" OS$USERID:[6] "oracle" DBID:[10] "1234567890"

Thu May 16 11:57:30 2013 +03:00
LENGTH: "262"
SESSIONID:[10] "4294967295" ENTRYID:[2] "18" STATEMENT:[2] "11" USERID:[2] "TEST_USER" USERHOST:[12] "ro_standby" TERMINAL:[5] "pts/0" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$
NAME:[11] "V_$DATABASE" OS$USERID:[6] "oracle" DBID:[10] "1234567890"

Thu May 16 11:57:32 2013 +03:00
LENGTH: "225"
SESSIONID:[10] "4294967295" ENTRYID:[1] "1" USERID:[2] "TEST_USER" ACTION:[3] "101" RETURNCODE:[1] "0" LOGOFF$PREAD:[1] "5" LOGOFF$LREAD:[3] "125" LOGOFF$LWRITE:[1] "0" LOGOFF$DEAD:[1] "0" DBID:[10
] "1234567890" SESSIONCPU:[1] "1"

11.2.0.2 - high 'cursor: pin s wait on x'

Recently I've got problem with high wait event "cursor: pin s wait on x" a high CPU utilization on CPU. On database we see many child cursors (~300) . Query is very complex (explain plan has ~1000 lines) and generate about 20 different execution plans (bind variables are in use) and sometimes execution plan is very slow.

In V$SQL_SHARED_CURSOR I checked mismatch and on two columns (AUTH_CHECK_MISMATCH and INSUFF_PRIVS) value is 'Y'.
I query is used SYS_CONTEXT function and I though that can be problem with VPD but no.
I also checking by trace 10046 where database spend time but here I found that parse time elapsed is nearly 97% ( but probably through  recursive sql database badly count time).

After some investigation on metalink I found BUG 11930680

"If optimizer_secure_view_merging is enabled then some SQL statements may
not be shared due to AUTH_CHECK_MISMATCH / INSUFF_PRIVS even if the
SQL is issued repeatedly by the same user. This can cause excess shared
pool memory use and other contention issues due to the high child cursor
count."


and after patch installation problem has been solved - one child cursor.

Incremental statistics

In 11g Oracle introduce incremental gathering statistics for partitioned tables. For huge tables gathering statistics takes a lot of time, but if incremental statistic is enabled on table, of course gathering will be faster.
Below is a query when we can check which  tables don't have enabled incremental statistics:

SELECT UNIQUE TABLE_OWNER,TABLE_NAME,
  SUM(NUM_ROWS)
FROM DBA_TAB_PARTITIONS
WHERE TABLE_OWNER not in ('SYS','SYSTEM')
AND table_name NOT IN
  (SELECT table_name
  FROM DBA_TAB_STAT_PREFS
  WHERE PREFERENCE_NAME='INCREMENTAL'
  AND PREFERENCE_VALUE ='TRUE'
  )
GROUP BY TABLE_OWNER,TABLE_NAME
ORDER BY 3;

and here is script to enable incremental statistcs for table:


EXEC dbms_stats.set_table_prefs('SCHEMA1', 'TABLE1', 'INCREMENTAL', 'TRUE');
EXEC DBMS_STATS.set_table_prefs('SCHEMA1', 'TABLE1', 'ESTIMATE_PERCENT', DBMS_STATS.AUTO_SAMPLE_SIZE);
EXEC dbms_stats.set_table_prefs('SCHEMA1', 'TABLE1', 'GRANULARITY', 'AUTO');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA1', 'TABLE1');

Data Guard - Real Time Apply

In a normal configuration Data Guard , applying changes I  turn on by command:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;


But in this mode, standby database apply changes after switching redolog group.

In order to applying changes in real time need to use the command:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

and now in v$managed_standby on standby, status for MRP0 (managed recovery process) process is APPLYING_LOG

How to move segments

Here are a few commands in one place to move segments:

In first step we move tables without partitions and subpartitions:

SELECT 'alter table '
  ||OWNER
  ||'.'
  ||TABLE_NAME
  ||' move tablespace '
  ||TABLESPACE_NAME
  ||';'
FROM DBA_TABLES
WHERE TABLESPACE_NAME IN ('USERS')
AND TABLE_NAME NOT    IN
  ( SELECT TABLE_NAME FROM DBA_TAB_PARTITIONS WHERE TABLESPACE_NAME IN ('USERS')
  UNION ALL
  SELECT TABLE_NAME
  FROM DBA_TAB_SUBPARTITIONS
  WHERE TABLESPACE_NAME IN ('USERS')
  );

for partitions and subpartitions...

SELECT 'alter table '
  ||TABLE_OWNER
  ||'.'
  ||TABLE_NAME
  ||' move partition '
  ||PARTITION_NAME
  ||' tablespace '
  ||TABLESPACE_NAME
  ||' UPDATE  INDEXES;'
FROM DBA_TAB_PARTITIONS
WHERE TABLESPACE_NAME IN ('USERS') and SUBPARTITION_COUNT=0;;

SELECT 'alter table '
  ||TABLE_OWNER
  ||'.'
  ||TABLE_NAME
  ||' move subpartition '
  ||SUBPARTITION_NAME
  ||' tablespace '
  ||TABLESPACE_NAME
  ||' UPDATE  INDEXES;'
FROM dba_tab_subpartitions
WHERE tablespace_name IN ('USERS');

and for LOB objects

SELECT 'alter table '
  ||owner
  ||'.'
  ||table_name
  ||' move lob('
  ||column_name
  ||') store as (tablespace '
  ||TABLESPACE_NAME
  ||');'
FROM dba_lobs
WHERE TABLESPACE_NAME IN ('USERS');
 
and for LOB subpartitions objects

SELECT 'ALTER TABLE '||TABLE_OWNER ||'."'|| TABLE_NAME || '" MOVE SUBPARTITION '|| SUBPARTITION_NAME ||'  TABLESPACE '||TABLESPACE'|| LOB ('||
COLUMN_NAME||') STORE AS (TABLESPACE '||TABLESPACE_NAME||');'
 FROM DBA_LOB_SUBPARTITIONS WHERE TABLESPACE_NAME='USERS';

After that we have to rebuild indexes:

SELECT 'alter index '
  ||owner
  ||'.'
  ||SEGMENT_NAME
  ||' rebuild online;'
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME IN ('USERS')
AND segment_type       ='INDEX'; 

SELECT 'ALTER INDEX '
  ||INDEX_OWNER
  ||'.'
  || INDEX_NAME
  ||' rebuild subpartition '
  || SUBPARTITION_NAME
  ||' online;'
FROM DBA_IND_SUBPARTITIONS
WHERE STATUS='UNUSABLE';

SELECT 'ALTER INDEX '
  ||INDEX_OWNER
  ||'.'
  || index_name
  ||' rebuild partition '
  || PARTITION_NAME
  ||' online;'
FROM DBA_IND_PARTITIONS
WHERE status='UNUSABLE'; 

Sometimes we want to move subpartitions to another tablespace, but information about tablespace is also on the partition level (despite the fact that there is no segment): 

ALTER TABLE USER1.TABLE1  MODIFY DEFAULT ATTRIBUTES FOR PARTITION DATA_1 TABLESPACE USER_DATA_2;
 

Oracle AQ - pl/sql notification does not work

 I fought  with the issue of pl/sql notification in the Advanced Queuing. Sending a message to the queue, received the status of "READY". Clearing the queue did not help. The problem was only with the  PL/SQL notification and for mail notification, everything worked without problems.

I noticed that the automatic job AQ$_PLSQL_NTFN calling the callback function waits and locks row in the table  SYS.AQ$AQ_SRVNTFN_TABLE_1.

Helped clear the table by a modified procedure  from http://riyazmsm.blogspot.com:

Appropriate queue name  for pl/sql notification you can check using:
select * from sys.AQ_SRVNTFN_TABLE_1 


EXEC DBMS_AQADM.START_QUEUE('AQ$_AQ_SRVNTFN_TABLE_1_E',false, true); 

DECLARE
  dequeue_options DBMS_AQ.dequeue_options_t;
  message_properties DBMS_AQ.message_properties_t;
  dq_msgid RAW(16);
  payload RAW(1);
  no_messages EXCEPTION;
  pragma exception_init (no_messages, -25263);
  msg_count NUMBER(2);
  CURSOR c_msg_ids
  is
    SELECT MSG_ID FROM sys.AQ$AQ_SRVNTFN_TABLE_1 WHERE queue = 'AQ$_AQ_SRVNTFN_TABLE_1_E';
BEGIN
  dequeue_options.wait         := DBMS_AQ.NO_WAIT;
  dequeue_options.navigation   := DBMS_AQ.FIRST_MESSAGE;
  dequeue_options.dequeue_mode := dbms_aq.remove_nodata;
  FOR v_msg_id                 IN c_msg_ids
  LOOP
    dequeue_options.msgid := v_msg_id.msg_id;
    MSG_COUNT             := 0;
    DBMS_AQ.DEQUEUE(queue_name => 'sys.AQ$_AQ_SRVNTFN_TABLE_1_E', dequeue_options => dequeue_options, message_properties => message_properties, payload => payload, msgid => dq_msgid);
    dbms_output.put_line('Message id : '||v_msg_id.msg_id||' removed');
    msg_count                  := msg_count + 1;
    dequeue_options.msgid      := NULL;
    dequeue_options.navigation := DBMS_AQ.NEXT_MESSAGE;
  END LOOP;
commit;
EXCEPTION
WHEN no_messages THEN
  DBMS_OUTPUT.PUT_LINE ('No of Messages Removed: '||msg_count);
  COMMIT;
END;
/

or

 EXEC DBMS_AQADM.START_QUEUE('AQ_SRVNTFN_TABLE_Q_1',false, true);  

DECLARE
  dequeue_options DBMS_AQ.dequeue_options_t;
  message_properties DBMS_AQ.message_properties_t;
  dq_msgid RAW(16);
  payload RAW(1);
  no_messages EXCEPTION;
  pragma exception_init (no_messages, -25263);
  msg_count NUMBER(2);
  CURSOR c_msg_ids
  is
    SELECT MSG_ID FROM sys.AQ$AQ_SRVNTFN_TABLE_1 WHERE queue = 'AQ_SRVNTFN_TABLE_Q_1';
BEGIN
  dequeue_options.wait         := DBMS_AQ.NO_WAIT;
  dequeue_options.navigation   := DBMS_AQ.FIRST_MESSAGE;
  dequeue_options.dequeue_mode := dbms_aq.remove_nodata;
  FOR v_msg_id                 IN c_msg_ids
  LOOP
    dequeue_options.msgid := v_msg_id.msg_id;
    MSG_COUNT             := 0;
    DBMS_AQ.DEQUEUE(queue_name => 'sys.AQ_SRVNTFN_TABLE_Q_1', dequeue_options => dequeue_options, message_properties => message_properties, payload => payload, msgid => dq_msgid);
    dbms_output.put_line('Message id : '||v_msg_id.msg_id||' removed');
    msg_count                  := msg_count + 1;
    dequeue_options.msgid      := NULL;
    dequeue_options.navigation := DBMS_AQ.NEXT_MESSAGE;
  END LOOP;
commit;
EXCEPTION
WHEN no_messages THEN
  DBMS_OUTPUT.PUT_LINE ('No of Messages Removed: '||msg_count);
  COMMIT;
END;
/

After that database should be restarted.

It is interesting that in this table were the messages until the queue to work properly.