Oraclue

Oracle internals, debugging and undocumented features

Category Archives: Internals

Oracle Diagnostic Events in 11g

This was presented at HOTSOS 2011

Oracle 11g has a completely new low-level kernel diagnostics and tracing infrastructure built into it. This presentation will explore new diagnostic events. It covers topics like: how to find Oracle internal documentation about events, sample cases, demos, etc.

You can download it from these links:

PDF file:

Oracle_Diagnostic_Events_in_11g

Presentation:

Oracle Diagnostic Events

Advertisements

How to disable flush of ASH data to AWR?

MMON process will periodically flush ASH data into AWR tables.
Oracle introduced WF enqueue which is used to serialize the flushing of snapshots.

If for any reason ( space issue, bugs, hanging etc..) you need to disable flushing the run time statistics for
particular table than following procedure needs to be done.

First, locate the exact AWR Table Info (KEW layer):

SQL>  select table_id_kewrtb, table_name_kewrtb from x$kewrtb order by 1

TABLE_ID_KEWRTB TABLE_NAME_KEWRTB
————— —————————————————————-
0 WRM$_DATABASE_INSTANCE
1 WRM$_SNAPSHOT
2 WRM$_BASELINE
3 WRM$_WR_CONTROL

—-

TABLE_ID_KEWRTB TABLE_NAME_KEWRTB
————— —————————————————————-
99 WRH$_RSRC_PLAN
100 WRM$_BASELINE_DETAILS
101 WRM$_BASELINE_TEMPLATE
102 WRH$_CLUSTER_INTERCON
103 WRH$_MEM_DYNAMIC_COMP
104 WRH$_IC_CLIENT_STATS
105 WRH$_IC_DEVICE_STATS
106 WRH$_INTERCONNECT_PINGS

107 rows selected.

1st option : Read more of this post

EVAL – Oracle Golden Gate internal command

I had a chance to take a quick look at Golden Gate  executable GGSCI.

If you run HELP command you will get a list of all ( or not ) options that you can use:
-bash-3.2$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.0.0 Build 078
Linux, x64, 64bit (optimized), Oracle 11 on Jul 28 2010 13:13:42

Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.

GGSCI (demo) 1> help

GGSCI Command Summary:

Object:          Command:
SUBDIRS          CREATE
ER               INFO, KILL, LAG, SEND, STATUS, START, STATS, STOP
EXTRACT          ADD, ALTER, CLEANUP, DELETE, INFO, KILL,
LAG, SEND, START, STATS, STATUS, STOP
EXTTRAIL         ADD, ALTER, DELETE, INFO
GGSEVT           VIEW
MANAGER          INFO, SEND, START, STOP, STATUS
MARKER           INFO
PARAMS           EDIT, VIEW
REPLICAT         ADD, ALTER, CLEANUP, DELETE, INFO, KILL, LAG, SEND,
START, STATS, STATUS, STOP
REPORT           VIEW
RMTTRAIL         ADD, ALTER, DELETE, INFO
TRACETABLE       ADD, DELETE, INFO
TRANDATA         ADD, DELETE, INFO
CHECKPOINTTABLE  ADD, DELETE, CLEANUP, INFO

Commands without an object:
(Database)       DBLOGIN, LIST TABLES, ENCRYPT PASSWORD
(DDL)            DUMPDDL
(Miscellaneous)  FC, HELP, HISTORY, INFO ALL, OBEY, SET EDITOR, SHELL,
SHOW, VERSIONS, ! (note: you must type the word
COMMAND after the ! to display the ! help topic.)
i.e.: GGSCI (sys1)> help ! command

For help on a specific command, type HELP <command> <object>.

Example: HELP ADD REPLICAT

However , like any other Oracle executable development team will reserve few “hidden” commands for internal use.

So here is one that I just discovered:  EVAL

GGSCI (demo) 2> EVAL Read more of this post

Oracle network encrypted data exposed.

For this exercise I will use Oracle Advanced Security Option ( ASO) with following parameters:

SQLNET.ORA file:

/* ASO Encryption */

SQLNET.CRYPTO_SEED = “fsdfsdfsdfsdfsdfsdfsdfsdfwrewrwerwerwesdfsdfsdfsd”
SQLNET.ENCRYPTION_SERVER = REQUIRED
SQLNET.ENCRYPTION_CLIENT = REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER = (RC4_256)
SQLNET.ENCRYPTION_TYPES_CLIENT = (RC4_256)

/* ASO Checksumm */

SQLNET.CRYPTO_CHECKSUM_SERVER = REQUIRED
SQLNET.CRYPTO_CHECKSUM_CLIENT = REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (MD5)
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (MD5)

* Tracing */

TRACE_LEVEL_CLIENT = 16
TRACE_UNIQUE_CLIENT=TRUE
TRACE_TIMESTAMP_CLIENT=TRUE
TRACE_FILE_CLIENT = sqlnet
TRACE_DIRECTORY_CLIENT =C:\TRACE

My sqlnet.ora file has parameters related to encryption, checksum and client tracing.

Using sqlplus I will log into database:

C:\>sqlplus demo@demo

SQL> /

SPID   SID  USERNAME
—————-
28079   134 DEMO

quick check for this session

set linesize 145
col network_service_banner format a110
select sid,network_service_banner  from v$session_connect_info
where sid=134

SID NETWORK_SERVICE_BANNER
———- ————————————————————————————————————–
134 TCP/IP NT Protocol Adapter for Linux: Version 11.1.0.7.0 – Production
134 Oracle Advanced Security: encryption service for Linux: Version 11.1.0.7.0 – Production
134 Oracle Advanced Security: RC4_256 encryption service adapter for Linux: Version 11.1.0.7.0 – Produc
134 Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.1.0.7.0 – Production
134 Oracle Advanced Security: MD5 crypto-checksumming service adapter

5 rows selected.

and from session SID 134 I will run alter statement:

SQL> alter user demo identified by demo;

Looking at  sqlnet_3872.trc file I can see      Read more of this post

Patch utility interface

This is also undocumented feature.As name suggest this utility is used to install patches or to be more precise online patches without shooting down instance ( as you know Oracle  now offers online patching ).

This patch utility can be invoked by running oradebug command:

oradebug patch

Here is list of all available options which are self descriptive:

[list|install|remove|disable|enable]  <file> <temp>
temp = (disabled at restart
default=perm)
file = (in $ORACLE_HOME/hpatch/)

$ORACLE_HOME/hpatch directory contains shared libraries used by online patching. Read more of this post