Warning: log write broadcast wait time NNNms (SCN 0x0.XXXXXXXXXX)

Issue:

Warning: log write broadcast wait time NNNms (SCN 0x0.XXXXXXXXXX)

Explanation:

Broadcast on commit could fail to process pending broadcasts in a
timely manner in RAC, potentially affecting performance.

Unusually long broadcast ack warning messages are dumped to trace files:

“Warning: log write broadcast wait time …”

and/or foreground Log File Sync timeouts occur rather frequently
using Broadcast on Commit (default).

If this message occurring few times it can be ignored.


HTI (AGGREGATION[2]): resizing from XX slots to YY slots

HTI (AGGREGATION[2]): resizing from XX slots to YY slots

Issue:

The following message appears on Oracle trace file(s):

HTI (AGGREGATION[2]): resizing from XX slots to YY slots

Explanation:

This is just a stray message.

There is no indication that it has any impact on a system.

Oracle use this message for diagnostic purposes.

It simply documents a resize of an internal memory structure, over which application has no control (no ability to make use of
the information.) It should be ignored.


Understanding of locks, reading Oracle trace.

Understanding of locks, reading Oracle trace.

In this case, A session in 1 instance is initiating a transaction will hold exclusive mode lock on a TX resource, and a
session(instance 2) waiting to modify the row locked by the first transaction will request exclusive mode lock on the lock holder’s TX resource.

Testcase
————–

SQL> select instance_name from v$instance;

INSTANCE_NAME
—————-
apex1

SQL> select
sys_context(‘USERENV’,’SID’)
from dual; 2 3

SYS_CONTEXT(‘USERENV’,’SID’)
——————————————————————————–
329
SQL> show user
USER is “TEST”

SQL> CREATE TABLE test (n1 NUMBER , v1 VARCHAR2(100));
INSERT INTO test
SELECT n1, lpad (n1, 100,’SIDDHU’)
FROM
( SELECT level n1 FROM dual CONNECT BY level <=500
);
COMMIT;
Table created.

SQL> 2 3 4 5
500 rows created.

SQL>

Commit complete.

SQL>
SQL>

SQL> EXEC dbms_stats.gather_table_stats ( USER, ‘test’, CASCADE =>true);

PL/SQL procedure successfully completed.
Update a Single Row
—————–
SQL> update test set n1=n1 where n1=100;

SQL> select dbms_Transaction.LOCAL_TRANSACTION_ID from dual;

LOCAL_TRANSACTION_ID
——————————————————————————————————————————————————————————————————-
8.31.3741 >>>>>>>>>>>>>>>> A
From another Instance:
SQL> select instance_name from v$instance;

INSTANCE_NAME
—————-
apex2
SQL> select
sys_context(‘USERENV’,’SID’)
from dual; 2 3

SYS_CONTEXT(‘USERENV’,’SID’)
——————————————————————————————————————————————————————————————————-
16

SQL> update test set n1=n1 where n1=100; <<Hangs>>

—————————————————————-
SQL> SELECT sid, type, id1, id2, lmode, request
FROM gv$lock WHERE (type, id1, id2) IN
(SELECT type, id1, id2 FROM gv$lock WHERE request>0) ; 2 3

SID TY ID1 ID2 LMODE REQUEST
———- — ———- ———- ———- ———-
16 TX 524319 3741 0 6 >>> Requester
329 TX 524319 3741 6 0 >>> Holder
The combination of enqueue types id1 and id2 uniquely identifies the transaction.

SQL> col state format A10
SQL> col pid format 99999999
SQL> set serveroutput on size 100000
SQL> begin
2 print_Table (‘
3 with dl as (
4 SELECT inst_id, resource_name1, grant_level, request_level,
5 transaction_id0, which_queue, state, pid, blocked ,
6 blocker
7 FROM gv$ges_blocking_enqueue
8 )
9 SELECT dl.inst_id, dl.resource_name1, dl.grant_level,
10 dl.request_level, dl.state, s.sid, sw.event,
11 sw.seconds_in_wait sec
12 FROM dl,
13 gv$process p, gv$session s, gv$session_wait sw
14 WHERE (dl.inst_id = p.inst_id AND dl.pid = p.spid)
15 AND (p.inst_id = s.inst_id AND p.addr = s.paddr)
16 AND (s.inst_id = sw.inst_id AND s.sid = sw.sid)
17 ORDER BY sw.seconds_in_wait DESC
18 ‘);
19 end;
20 /
INST_ID : 2
RESOURCE_NAME1 : [0x8001f][0xe9d],[TX][ext 0x0, >>>>>>> B
GRANT_LEVEL : KJUSERNL
REQUEST_LEVEL : KJUSEREX
STATE : OPENING
SID : 16
EVENT : enq: TX – row lock contention
SEC : 81

—————–
INST_ID : 1
RESOURCE_NAME1 : [0x8001f][0xe9d],[TX][ext 0x0,
GRANT_LEVEL : KJUSEREX
REQUEST_LEVEL : KJUSEREX
STATE : GRANTED
SID : 329
EVENT : ges remote message
SEC : 0

—————–

PL/SQL procedure successfully completed.
Transaction_id is used to coin a unique resource_name [0x8001f][0xe9d],[TX] in GRD. The first part of
string [0x8001f] is the concatenation of strings 0x8 and 01f. 0x8 is a hexadecimal representation of decimal
8, and 01f is the hexadecimal representation of 31. The second part of string 0xe9d is the hexadecimal
representation of 3741 . These three parts, 8.31.3741 , combined together constitute the transaction_id we queried
using dbms_transaction package call(A=B).

Apart from that, with the queries that were provided earlier, you can get the GES LOCK BLOCKERS/WAITERS etc.

SQL> col state format a10
col inst_id format 99 head Inst
col owner_node format 99 head ‘Owner|Node’
SELECT inst_id, resource_name1, pid, state,
owner_node , grant_level, request_level
FROM gv$ges_enqueue
WHERE resource_name1 LIKE ‘[0x8001f][0xe9d],[TX]%’ ;SQL> SQL> SQL> SQL> 2 3 4

Owner
Inst RESOURCE_NAME1 PID STATE Node GRANT_LEV REQUEST_L
—- —————————— ——— ———- —– ——— ———
1 [0x8001f][0xe9d],[TX][ext 0x0, 49385 GRANTED 0 KJUSEREX KJUSEREX
1 [0x8001f][0xe9d],[TX][ext 0x0, 0 GRANTED 1 KJUSERNL KJUSEREX
2 [0x8001f][0xe9d],[TX][ext 0x0, 53226 OPENING


Oracle OEM performance charts don’t render.

There are a few reasons, why OEM can stop rendering performance charts.

This post addresses one of the possible solutions.

Follow carefully the below steps:

1- Take a backup of the following file (from the problematic database):

ORACLE_HOME/oc4j/j2ee/oc4j_applications/applications/em/em/WEB-INF/uix-config.xml

2- Open the file for editing and locate the following entry:

<!– An alternate configuration that disables accessibility features –>
<default-configuration>
<accessibility-mode>inaccessible</accessibility-mode>
</default-configuration>

3- Change the value of the “<accessibility-mode>” property from “inaccessible” to “accessible”.

4- Save the file and restart the DBConsole.

5- Check the status of the issue.


What’s the minimum target Oracle version that OEM 12c will be able to monitor?

Certified target database (for OMS/Agent 12.1.0.4.) are the following:
10.1.0.5.0 / 10.2.0.4.0 / 10.2.0.5.0 / 11.1.0.7.0 / 11.2.0.x / 12.x


How To Write A CV: Leverage the NoSQL boom

201301-NoSQL-CV


Different behavior of impdp between Oracle 11.1.0.6 and Oracle 11.2.0.4.

Different behavior of impdp between Oracle 11.1.0.6 and Oracle 11.2.0.4.

Issue:

Assume we have some table MYTAB in the DB schema SCHEMA_A on Oracle DB instance INST_A.

And we’d like to expdp and impdp it to the schema SCHEMA_B on Oracle DB instance INST_B.

In Oracle 11.1.0.6 we run the following:

impdp SCHEMA_B/SOME_PWD_B@INST_B tables=MYTAB dumpfile=tables_MYTAB_20140707_0000.dmp remap_schema=SCHEMA_A:SCHEMA_B directory=EXPDP_DIR

In Oracle 11.2.0.4 running the same will get the following error:

ORA-39002: invalid operation
ORA-39166: Object SCHEMA_B.MYTAB was not found.

Workaround:

Specify the fully-qualified table table (tables=SCHEMA_A.”MYTAB”).

impdp SCHEMA_B/SOME_PWD_B@INST_B tables=SCHEMA_A.”MYTAB” dumpfile=tables_MYTAB_20140707_0000.dmp remap_schema=SCHEMA_A:SCHEMA_B directory=EXPDP_DIR


High reliable message waits on Oracle RAC 11.2.0.4

High reliable message waits on Oracle RAC 11.2.0.4

Issue:

High reliable message waits on Oracle RAC 11.2.0.4

Root Cause:

There are a few common scenarios when we can expect to see this wait event “reliable message”:

1. Within an instance:

When a message is sent using intra-instance broadcast service, the message publisher waits on
this wait-event until all subscribers have consumed the ‘reliable message’ just sent. The publisher
waits on this wait-event for three seconds and then re-tests if all subscribers have consumed the
message, or until posted.

2. RAC environment:

When CR blocks or current blocks are sent to a remote node, the sender actually sends another
reliable message to the requester, because the CR block or current block being shipped could be
lost. For example, a node sends 100 CR blocks to another node, the sender node then may send
a message saying ‘I’ve sent 100 blocks’ in a single message.

Solution:

The wait event “reliable message” can be ignored, since it has no impact to the system.

 


TFA Collector – Tool for Enhanced Diagnostic Gathering.

TFA Collector – Tool for Enhanced Diagnostic Gathering.

Issue:

When opening service request in Oracle Support, you have to collect diagnostic data, as much as you can.

Solution:

Use TFA Collector.

Details:

TFA Collector- The Preferred Tool for Automatic or ADHOC Diagnostic Gathering Across All Cluster Nodes.

TFA Collector is the preferred method for collecting data in a RAC/Clustered environment.

Trace File Analyzer Collector (TFA) is a diagnostic collection utility to simplify diagnostic data collection on Oracle Clusterware/Grid Infrastructure, RAC and Single Instance Database systems. TFA is similar to the diagcollection utility packaged with Oracle Clusterware in the fact that it collects and packages diagnostic data however TFA is MUCH more powerful than diagcollection with its ability to centralize and automate the collection of diagnostic information.

Starting with 11.2.0.4 Grid Infrastructure Patchset, TFA is installed with Grid Infrastructure.  However, TFA is written outside of the GI/RAC/RDBMS product lines and as such could be used for any trace data, and is version agnostic.

Example:

Login as a root user

su –

cd /u01/app/11.2.0.4/grid/tfa/bin

./tfactl diagcollect


2014/06/30 21:26:49 UTC : Collecting ADR incident files…
2014/06/30 21:26:56 UTC : Total Number of Files checked : 17257
2014/06/30 21:26:58 UTC : Total Size of all Files Checked : 2.4GB
2014/06/30 21:26:58 UTC : Number of files containing required range : 416
2014/06/30 21:26:58 UTC : Total Size of Files containing required range : 184MB
2014/06/30 21:26:58 UTC : Number of files trimmed : 23
2014/06/30 21:26:58 UTC : Total Size of data prior to zip : 162MB
2014/06/30 21:26:58 UTC : Saved 106MB by trimming files
2014/06/30 21:26:58 UTC : Zip file size : 26MB
2014/06/30 21:26:58 UTC : Total time taken : 112s
2014/06/30 21:26:58 UTC : Completed collection of zip files.

Logs are collected to:
/u01/app/grid/tfa/repository/collection_Mon_Jun_30_21_25_02_UTC_2014_node_all/myora1.tfa_Mon_Jun_30_21_25_02_UTC_2014.zip
/u01/app/grid/tfa/repository/collection_Mon_Jun_30_21_25_02_UTC_2014_node_all/myora2.tfa_Mon_Jun_30_21_25_02_UTC_2014.zip

More Info:

http://support.oracle.com

TFA Collector – Tool for Enhanced Diagnostic Gathering (Doc ID 1513912.1)


How To Monitor ASM Disk Usage?

How To Monitor ASM Disk Usage?

Issue:

How To Check and Monitor ASM Disk Usage?

Solution:

SELECT
name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, total_mb total_mb
, (total_mb – free_mb) used_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM
v$asm_diskgroup
ORDER BY
name
;

Example:

Sample Linux script to monitor ASM Disk Usage for Oracle DB.

asm_disk_usage_monitoring.sh

#!/bin/bash

#################################################################
#
# Script to monitor ASM disk usage on your Oracle DB
#
# Desc: Script to monitor ASM disk usage on your Oracle DB
#
# Date: 30-Jun-2014
#
#################################################################

. ~/.bash_profile
OP_TIMESTAMP=`date +%Y%m%d_%H%M`
CONNECT_STRING=system/mypwd@myora
ENVIRO=”Production”
REP_NAME=”Monitor Space Used by ASM disks (My Environment)”
EMAIL_LIST1=”someone@somewhere.net”
EMAIL_SUBJ=”$REP_NAME – $OP_TIMESTAMP”
REP_FILE_NAME=monitor_space_used_by_asm_disks_$OP_TIMESTAMP.out
OUTPUT_PLACE=/tmp/monitor_space_used_by_asm_disks_$OP_TIMESTAMP.out
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
PATH=$PATH:$ORACLE_HOME/bin
export NLS_LANG=.AL32UTF8
export from=myschema@myora
export ORACLE_HOME PATH

sqlplus -s $CONNECT_STRING <<!

set markup html on
set verify off feedback off heading on echo off pages 100 termout off

set lines 1200
spool $OUTPUT_PLACE

SELECT
name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, total_mb total_mb
, (total_mb – free_mb) used_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM
v\$asm_diskgroup
ORDER BY
name
;

set termout on
exit
!

(
echo “To: $EMAIL_LIST1”
echo From: oracle@10.20.30.40
echo “Content-Type: text/html; “
echo Subject: “$EMAIL_SUBJ”
echo
echo “<center><b>Monitor Space Used by ASM disks</b></center><br/>”
cat $OUTPUT_PLACE
) | /usr/sbin/sendmail -t

#################################################################
#
# The End
#
#################################################################