ThatOracleDude: 2014

Thursday, 28 August 2014

How Oracle finds things in a data file - From the simplest of the Queries to the instance Startup

So, how does it do it?

An Oracle server process is nothing more then a large C program. On the other hand, the Oracle RDBMS is a multitude of processes, memory areas and files working and communicating in conjunction to serve a purpose.
As we all know, two of the main purposes of a Database System are to store and to retrieve data. When it comes to these two simple things there is a lot more than meets the the eye. If you are like me, you probably want to know the whys and hows of everything, you will most likely find this post of some use. Well, at least it can be used to either refresh your memory on the subject or to amuse someone at the pub. :)
For this article, we will be concentrating on a tiny spectacle of the "retrieving data" function of an Oracle database. We will be exposing the foremost part of the process, which is, how Oracle knows where to look at in a data file when it wants to find data for a particular object.

This may sound very simple as we all know that the DBA_SEGMENTS view exposes which data file a specific segment is and at which block it starts. However, as we dive deeper into this article, you will find answers for the following question and many more:

How did Oracle find the DBA_SEGMENTS in first place?

So, let's start with a very simple example, a query against a table called CUSTOMERS.

SQL> set linesize 200
SQL> col name for a30
SQL> select name
from customers
where customer_id=1;

NAME
------------------------------

Daniel Da Meda

Ok, the above query could not be simpler but it will serve as our starting point as we walk backwards to find our how Oracle locates things.

So, let's, put together a sequence of events taking place by the Oracle server process when it wants to locate the CUSTOMERS segment and its relative data.

Let's query the DBA_SEGMENTS view right?

SQL> select header_file, header_block, blocks, extents from dba_segments where segment_name='CUSTOMERS';

HEADER_FILE HEADER_BLOCK BLOCKS EXTENTS

----------- ------------ ---------- ----------

36 2050 8 1

The above shows that this specific segment has it's segment header stored on block 2050 of datafile 36. The segment header block will always have to be read either from disk or memory if already cached. Ok, there are special cases but let's simplify and assume that it will always have to read the segment header. By the way, is in the segment header that Oracle finds out how many blocks a specific segment has up to the High Water Mark, but this is for another article.

Further to it, we can drill down to the DBA_SEGMENTS view and display the information regarding the only extent that this table has

SQL> select file_id, block_id, blocks from dba_segments where segment_name='CUSTOMERS';

FILE_ID BLOCK_ID BLOCKS

---------- ---------- ----------

36 2048 8

From the above output, we can see that this segment's data is stored on data file 36, and it makes use of blocks from 2048 to 2055.

This is easy stuff. Or, is it? How did Oracle find the DBA_SEGMENTS view or its underlying tables? It must be stored somewhere. Where?

To answer this question we will need to have a look at the DBA_SEGMENTS view definition.

Once we look into the DBA_SEGMENTS view text, we realize that it is based on the following tables:

sys.user$
sys.obj$
sys.ts$
sys.sys_objects
sys.seg$
sys.file$

From the above views, there is one in particular which has location-related information - SYS.SEG$

The DBA_SEGMENTS gets location-related information from the SYS.SEG$ table which, by the way, is kept in the dictionary cache to avoid unnecessary recursive queries.

The SYS.SEG$ table is the source of location-related information for the oracle server process and will be constantly checked in order to know where to find things in the data file.

While table SYS.SEG$ acts like a GPS for the oracle server process, who tells the Oracle Server process where SYS.SEG$ is?

As it turns out, SYS.SEG$ is a special type of segment and its location is defined somewhere else.

We present you with the BOOTSTRAP$ object.

On my 12c database, there are only a few objects defined in this object:

SQL> select count(*) from sys.bootstrap$;

COUNT(*)

----------

The bootstrap$ segment contains DDL entries. We can actually see the CREATE TABLE statement that defines the SYS.SEG$ table.

This is how the record for SYS.SEG$ table look like:

SQL> desc sys.bootstrap$

Name Null? Type

----------------------------------------- -------- ----------------------------

LINE# NOT NULL NUMBER

OBJ# NOT NULL NUMBER

SQL_TEXT NOT NULL VARCHAR2(4000)

SQL> select sql_text from sys.bootstrap$ where sql_text like '%SEG$%';

SQL_TEXT

--------------------------------------------------------------------------------

CREATE TABLE SEG$("FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"TYPE#" NUMBE

R NOT NULL,"TS#" NUMBER NOT NULL,"BLOCKS" NUMBER NOT NULL,"EXTENTS" NUMBER NOT N

ULL,"INIEXTS" NUMBER NOT NULL,"MINEXTS" NUMBER NOT NULL,"MAXEXTS" NUMBER NOT NUL

L,"EXTSIZE" NUMBER NOT NULL,"EXTPCT" NUMBER NOT NULL,"USER#" NUMBER NOT NULL,"LI

STS" NUMBER,"GROUPS" NUMBER,"BITMAPRANGES" NUMBER NOT NULL,"CACHEHINT" NUMBER NO

T NULL,"SCANHINT" NUMBER NOT NULL,"HWMINCR" NUMBER NOT NULL,"SPARE1" NUMBER,"SPA

RE2" NUMBER) STORAGE ( OBJNO 14 TABNO 2) CLUSTER C_FILE#_BLOCK#(TS#,FILE#,BLOCK

From the above output, we can see that its storage attributes are defined as a CLUSTER with OBJNO 14 and TABNO 2 on a cluster named C_FILE#_BLOCK#.

So we now look at this cluster definition. Luckily, cluster C_FILE#_BLOCK# is also defined in the BOOTSTRAP$ object. Let's perform another query this time searching for the cluster:

SQL> select sql_text from sys.bootstrap$ where sql_text like '%C_FILE#_BLOCK#%';

CREATE CLUSTER C_FILE#_BLOCK#("TS#" NUMBER,"SEGFILE#" NUMBER,"SEGBLOCK#" NUMBER)

PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 24K NEXT 1024K

MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 8 EXTENTS (FILE 1 BLOCK

192)) SIZE 225

Tada! At least some progress. We finally got a data file number and a block number - (FILE 1 BLOCK 192).

So this tells Oracle where to find the SYS.SEG$ segment?

YES!!!

Wait a minute, isn't the BOOTSTRAP$ also a table? Who tells Oracle where to find it then?

The answer is simple. The location of the bootstrap$ object used to be located in the first block of data file number 1. However, you will have to take my word for it as I dumped the block multiple times and I cannot find this information in there anymore...

I have dumped all data file headers to a trace file as follows:

SQL> alter session set tracefile_identifier=file_headers;

Session altered.

SQL> alter session set events 'immediate trace name file_hdrs level 10';

Since I could not find that information in the header of any database file, I tried the controlfile. Also, to no avail:

SQL> alter session set tracefile_identifier=controlfile_header;

Session altered.

SQL> alter session set events 'immediate trace name controlf level 10';

So, I gave up. Perhaps the trace has been changed not to show that information?

UPDATE
=======

Thanks to Rodrigo Righetti from Enkitec, I know now where to find the address for the BOOTSTRAP$ object. Please check out his answer to this:

"The pointer to the bootstrap is represented by the "root dba" entry on the file header trace, which on 11.2 onwards should be:
root dba:0x00400208

Now the trick part

Each number in the address is represented with 4bits, in a total of 32 bits, like this:

0000 0000 0100 0000 0000 0010 0000 1000
0 0 4 0 0 2 0 8

Now to get the File ID, we use the first 10 bits: 0000000001

Which in decimal is: 1

And to get the Block Number we get the remaining 22 bits: 0000000000001000001000

Convert that to decimal and we get: 520

if you look on dba_segments:

select header_file, header_block from dba_segments where segment_name='BOOTSTRAP$';

1, 520

The other way is using DD and OD, if you really want to go deep, BBED, but lets leave that way

Regards
Rodrigo"

So, I guess I was looking at the right place for the wrong thing. I will perform another dump of the data file headers and see if I can find the root dba entry:

SQL> alter session set tracefile_identifier=file_headers;

Session altered.

SQL> alter session set events 'immediate trace name file_hdrs level 10';

Found it!

It is located in the trace file. You just have to search for the string root dba.
Since my database is 12.1, I get the same data block address as Rodrigo:

root dba:0x00400208

As demonstrated by him, this equates to data file 1 block 520

Now we know who tells Oracle where the bootstrap$ object is.

I hope you have enjoyed this small article about How Oracle finds stuff in a data file. Many thanks to Rodrigo Righetti who has come to my rescue helping us to put an end to this small article.

Please leave comments!

Daniel Da Meda

Wednesday, 27 August 2014

Data Redaction - See what you shouldn't see in 5 minutes

What Oracle has said about Data Redaction

I have taken the following text from the Oracle documentation and made some remarks in bold so that I do not wander away from what Data Redaction is really supposed to be used for:

"Oracle Data Redaction is useful for many different scenarios. It can be applied to a range of

packaged and custom applications to redact application screens, dashboards, and reports. It

helps you avoid making code changes in existing call center, human resources, sales, financial,

and healthcare applications. These applications frequently manage payment card (PCI),

protected health (PHI), or personally identifiable (PII) information that is subject to regulation.

Oracle Data Redaction is also useful for decision support systems that aggregate large and

diverse sets of data in a single repository for running analytics.

When an application issues a query, data is retrieved from the database and then the redaction

policy is applied. Redaction takes place immediately preceding the return of selected data, and

only at the top level of the SELECT list."

So, as we can see, the word "application" repeats itself over and over. Perhaps this is Oracle giving extreme emphasis to the fact that hell can break loose if you end user is the type of user who is familiar with the following prompt - SQL>

With this in mind, I am not implying that Oracle is overselling this feature since the text clearly states that this should be used to redact the data displayed to the end user of a packaged application.

In the following lines I will be depicting a very simple example of how one can, in a matter of seconds, get the real credit card number of a customer:

Ok, lets start with a table called Customers

SQL> create table customers (customer_id number(5) not null primary key,
2 name varchar2(40) not null,
3 creditcard_no varchar2(19));

Table created.

Inserting some hypothetical values:

SQL> insert into customers values (1,'Daniel Da Meda','4451-2172-9841-4368');

1 row created.

SQL> insert into customers values (2,'Alex Zaballa','5102-6342-4881-5211');

1 row created.

SQL> insert into customers values (3,'Antonio Mateus','4891-3311-0090-5005');

1 row created.

SQL> commit;

Commit complete.

col creditcard_no for a19
col name for a30
SQL> select * from customers;

CUSTOMER_ID NAME CREDITCARD_NO
----------- ------------------------------ -------------------
1 Daniel Da Meda 4451-2172-9841-4368
2 Alex Zaballa 5102-6342-4881-5211
3 Antonio Mateus 4891-3311-0090-5005

SQL> create public synonym customers for customers;

Synonym created.

Now, let's create a Data Redaction Policy to protect our customer's credit card number. For the sake of simplicity, we will protect our customers from one specific database user named watcher

SQL> create user watcher identified by icanseeyou default tablespace users temporary tablespace temp;

User created.

SQL> grant connect to watcher;

Grant succeeded.

SQL> grant select on customers to watcher;

Grant succeeded

SQL> connect watcher/icanseeyou@pdbdev

Connected.

col creditcard_no for a19
col name for a30
select * from customers;

CUSTOMER_ID NAME CREDITCARD_NO
----------- ------------------------------ -------------------
1 Daniel Da Meda 4451-2172-9841-4368
2 Alex Zaballa 5102-6342-4881-5211
3 Antonio Mateus 4891-3311-0090-5005

SQL> connect ddameda@pdbdev
Enter password:
Connected.

For this example, I will be using OEM Cloud Control since most posts out there show how to create using command prompt.

You can view the SQL code by clicking on the Show SQL push button.

Once you have reviewed the code, click OK to create the policy.

Testing the policy. First attempting to retrieve the values from the customers table as the owner of the policy - ddameda:

SQL> show user

USER is "DDAMEDA"

SQL> select creditcard_no from customers;

CREDITCARD_NO
--------------------------------------------------------------------------------
4451-2172-9841-4368
5102-6342-4881-5211
4891-3311-0090-5005

As expected, there is no redaction going on when the user executing the query is not named WATCHER. We now connect as WATCHER and see what happens:

SQL> show user
USER is "WATCHER"
SQL> select creditcard_no from customers;

CREDITCARD_NO
--------------------------------------------------------------------------------
************4368
************5211

************5005

The above proves that the policy is working as intended.

However, if the user watcher can run SQL statements directly to the database, he could still see the credit card numbers for all employees simply by running the following snippet:

SET TIMING ON
SET SERVEROUTPUT ON
DECLARE
vcreditcard_no CUSTOMERS.creditcard_no%TYPE;
vcustomer_name CUSTOMERS.name%TYPE;
BEGIN
FOR a in 1..19 LOOP
IF a NOT IN (5,10,15) THEN
FOR b in 0..9 LOOP
BEGIN
SELECT name
INTO vcustomer_name
FROM CUSTOMERS
WHERE customer_id=1
AND SUBSTR(creditcard_no,a,1) = b;
vcreditcard_no:=vcreditcard_no||b;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
END LOOP;
ELSE
vcreditcard_no:=vcreditcard_no||'-';
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Credit Card Number for '||vcustomer_name||' is: '||vcreditcard_no);
END;

/

Credit Card Number for Daniel Da Meda is: 4451-2172-9841-4368

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04

The above script uses the where clause to perform a brute force comparison and tests each numeric digit from 0 to 9. At the end, it delivers the credit card number. As you can see, the process and only takes 4 centiseconds to run.

Conclusion

What I learn from this is that Oracle Data Redaction is a good tool for implementing "visual" security on new and existing applications. However, this only remain valid if the end user does not have any sort of access to the database other then through the application interface. It is important to mention also that some application will allow advanced users or developers to run SQL or procedural code from the application. This is usually the case when the application has a report generator or an interface that allows a user to write some logic and run that on the database. These cases must be visited if you want to implement Data Redaction with the intention of protecting the business against data theft.

Wednesday, 6 August 2014

Oracle Extended Tracing

Definitions

A trace file is a file that contains diagnostic data used to investigate problems. Also, trace files can provide guidance for tuning applications or an instance.

Types of Trace Files

Each server and background process can periodically write to an associated trace file. The files contain information on the process environment, status, activities, and errors.

The SQL trace facility also creates trace files, which provide performance information on individual SQL statements. You can enable tracing for a client identifier, service, module, action, session, instance, or database in various ways.

Extended Tracing and Multitenancy

In a multitenant environment, running the ALTER SESSION SET EVENTS SQL statement from either the root or a pluggable database (PDB) enables tracing for the current user session. If you switch from one PDB to another PDB (by using the ALTER SESSION SET CONTAINER statement), then tracing is still enabled for the new PDB. You cannot enable tracing for a single PDB in a multitenant container database (CDB); it applies to all PDBs and the root.

Setting Trace Events

Extended SQL trace, namely the 10046 event can be started in multiple ways and using multiples methods. Here is a list of SQL commands and PL/SQL API's that can be used to activate the 10046 trace. Depending on what you need, you may want to use one or another.

alter session set events '10046 trace name context forever level 8';
DBMS_MONITOR: It is specially useful for tracing remote sessions and to enable what Oracle calls end-to-end tracing.
DBMS_SYSTEM

The last two are specially good if you want to trace a different sessions. However, if you want more granularity when tracing a specific session, one can make use of the new alter session set events syntax as follows:

To enable SQL tracing (10046) for a specific sql_id:

alter session set events ‘sql_trace [sql:jfkgokfh2v9s1]’;

alter session set events ‘sql_trace [sql:jfkgokfh2v9s1] off’;

To enable Optimizer tracing (10053) for a specific sql_id:

alter session set events 'trace[rdbms.SQL_Optimizer.*[sql:jfkgokfh2v9s1]';

alter session set events 'trace[rdbms.SQL_Optimizer.*[sql:jfkgokfh2v9s1] off';

According to Maria Colgan, Oracle Database 11g has introduced a new diagnostic events infrastructure. This simplifies the tracing exercise allowing you to trace a specific SQL statement instead of enabling the trace for the entire session just to work on a single one.

As you can see from the above examples, there are more trace event codes than just the SQL trace event. Many other events can be used to expose timings and flows regarding Oracle internal workings.

I will be listing below, some trace event codes that I am familiar with and will update it with further ones as I

Trace Events

Find below, a list of trace event codes that I have extracted from the message file that is shipped with any Oracle Database Home. The text message file I am referring to is the following one:

$ORACLE_HOME/rdbms/mesg/oraus.msg

10005, 00000, "trace latch operations for debugging"

// level 1 - trace latch gets and frees

// level 4 - trace multiple posts by processes when latch is freed

10021, 00000, "KST event to trace control file header writes and reads"

10022, 00000, "trace ktsgsp"

10027, 00000, "Specify Deadlock Trace Information to be Dumped"

10028, 00000, "Dump trace information during lock / resource latch cleanup"

// Trace level is a bitfield.

// 0x0001 Trace sort auto memory management requirements

// 0x0004 Trace use of new vs old sort

// 0x0020 trace and debug sort spill statistics

// 0x0080 Trace cluster-by-rowid (CBRID) sort

// Trace level is a bitfield.

// 0x01 Output sort statistics in the trace file at the end of sort.

10047, 00000, "trace switching of sessions"

10050, 00000, "sniper trace"

10051, 00000, "trace OPI calls"

10053, 00000, "CBO Enable optimizer trace"

10054, 00000, "CBO Enable optimizer trace for recursive statement (RPI)"

10074, 00000, "default trace function mask for kst"

10079, 00000, "trace data sent/received via SQL*Net"

// *Action: level 1 - trace network ops to/from client

// level 4 - trace network ops to/from dblink

10104, 00000, "dump hash join statistics to trace file"

// Trace level is a bit field.

// This event can be turned on globally by seting trace name sqlmem_tracing

10146, 00000, "enable Oracle TRACE collection"

// Oracle Trace may still have it enabled by mistake

10166, 00000, "trace long operation statistics updates"

/ At level 1, KQR will dump out latch clean-up debugging traces.

/ At level 2, KQR will dump out bootstrap debugging traces.

10224, 00000, "index block split/delete trace"

10226, 00000, "trace CR applications of undo for data operations"

10228, 00000, "trace application of redo by kcocbk"

// *Action: such blocks are skipped in table scans, and listed in trace files

// *Action: puts corrupted block in trace file

// information is logged to a trace file.

10246, 00000, "print trace of PMON actions to trace file"

10250, 00000, "Trace all allocate and free calls to the topmost SGA heap"

10254, 00000, "trace cross-instance calls"

10257, 00000, "trace shared server load balancing"

10266, 00000, "Trace OSD stack usage"

/ This event can limit the size of tracefile produced for such

10289, 00000, "Do block dumps to trace file in hex rather than fromatted"

10292, 00000, "hang analysis trace event"

// 0x1 - dump errors to trace file through ksdwrf

// 0x2 - dump errors to trace file as ORA-600 + 0x1

10293, 0000, "trace log switch media recovery checkpoints"

10299, 00000, "Trace prefetch tracking decisions made by CKPT"

// *Comment: This event will trace the history of KTD actions

// made by the CKPT process are traced. The trace output is

// written to that process' trace file.

10302, 00000, "trace create or drop internal trigger"

10303, 00000, "trace loading of library cache for internal triggers"

10304, 00000, "trace replication trigger"

10305, 00000, "trace updatable materialized view trigger"

10306, 00000, "trace materialized view log trigger"

10307, 00000, "trace RepCat execution"

10310, 00000, "trace synchronous change table trigger"

10318, 00000, "Trace extensible txn header movements"

10319, 00000, "Trace PGA statistics maintenance"

// 0x0001 dump message in trace file when PGA stats are diverging

// 0x0002 trace top-level PGA allocation and deallocation

10325, 0000, "Trace control file record section expand and shrink operations"

// *Action: trace control file record section expand and shrink operations to

10335, 00000, "trace database open status"

// *Action: should cause process to dump trace file and die

// *Action: Prints the checkpoint queue and buffers to the trace file

// *Comment: For testing only. Trace level bits can be used in combination:

// *Comment: trace level is a bitfield (see kkrp.h)

10390, 00000, "Trace parallel query slave execution"

// trace level is a bitfield

// 0x0800 Trace CRI random sampling

// 0x1000 Trace signals

// 0x2000 Trace PX granule operations

10391, 00000, "trace PX granule allocation/assignment"

// 0x0400 trace affinity module

// 0x0800 trace granule allocation during query execution

// 0x1000 trace object flush

10399, 00000, "trace buffer allocation"

// Level2: trace buffer init, chunk alloc, free and recovery

// Level3: trace buffer alloc & free

10420, 00000, "trace KSO OS-process operations"

// *Action: Trace KSO operations.

// trace file. This only works for non-OS Oracle errors.

// Usage: event="10423 trace name context forever, level <your-err>"

// event="10423 trace name context forever, level 28"

10425, 00000, "enable global enqueue operations event trace"

// *Action: Dump trace for global enqueue operations.

10426, 00000, "enable ges/gcs reconfiguration event trace"

// *Action: Dump trace for ges/gcs reconfiguration.

10427, 00000, "enable global enqueue service traffic controller event trace"

// *Action: Dump trace for global enqueue service traffic controller.

// *Action: Dump trace for global enqueue service cached resource activities.

// *Action: Dump trace for global enqueue service IPC calls.

10430, 00000, "enable ges/gcs dynamic remastering event trace"

// *Action: Dump trace for ges/gcs dynamic remastering.

// *Action: Dump trace for global cache service fusion calls.

// *Action: Dump trace for global enqueue service muliple LMS.

// *Action: Dump trace for global enqueue service deadlock detetction.

10437, 00000, "enable trace of global enqueue service S optimized resources"

10440, 00000, "enable global enqueue service inquire resource modes trace"

// *Action: Dump trace for global enqueue service resource inquiry messages.

10441, 00000, "enable diagnosibility daemon (DIAG) trace"

// *Action: Dump traces for DIAG.

10442, 00000, "enable trace of kst for ORA-01555 diagnostics"

// *Action: Dump trace for ORA-01555 diagnostics

// *Action: Dump trace for LMS priority management

10449, 00000, "enable trace of kst for undo manageability features diagnostics"

// *Action: Dump trace for undo manageability features diagnostics

10453, 00000, "Dump compression statistics to trace file"

// generation and dumps interesting information to trace

10472, 00000, "dump reading log buffer hit ratio histogram to lgwr trace file"

// to lgwr trace file.

10478, 00000, "DBW0 file identification trace event"

10490, 00000, "Trace OSM misc. events"

10491, 00000, "Trace OSM messaging (KFN) events"

10492, 00000, "Trace OSM metadata events"

10494, 00000, "Trace OSM metadata events"

10495, 00000, "Trace OSM metadata events"

10497, 00000, "Trace OSM metadata events"

10499, 00000, "Trace OSM metadata events"

10500, 00000, "turn on traces for SMON"

// Level: <=5 trace instance recovery

// > 5 trace posting of SMON

10507, 00000, "Trace bind equivalence logic"

10524, 00000, "CMON connection pool trace event"

// *Comment: enables trace messages from the lightweight thread module.

// *Comment: enables trace messages from defer SGA allocation

// *Cause: Check alert/trace files for more information

// level: 0x02 Print trace information of assigning CVs to undo tbs slave to trace file.

10569, 00000, "Trace datafile header writes"

10604, 00000, "trace parallel create index"

10606, 00000, "trace parallel create index"

10607, 00000, "trace index rowid partition scan"

// and puts more trace on errors in range scans

10608, 00000, "trace create bitmap index"

10609, 00000, "trace for array index insertion"

10610, 00000, "trace create index pseudo optimizer"

10622, 00000, "test or trace online index build or rebuild"

// during alter session set events immediate trace name treedump

10649, 00000, "Turn off/trace lob index freelist coalesce"

// *Cause: Will turn off or trace lob index freelist coalescing

// *Action: INternal system parameter (_ksi_trace) can be used in addition

// _ksi_trace = RTXA

10708, 00000, "print out trace information from the RAC buffer cache"

10710, 00000, "trace bitmap index access"

10711, 00000, "trace bitmap index merge"

10712, 00000, "trace bitmap index or"

10713, 00000, "trace bitmap index and"

10714, 00000, "trace bitmap index minus"

10715, 00000, "trace bitmap index conversion to rowids"

10716, 00000, "trace bitmap index compress/decompress"

10717, 00000, "trace bitmap index compaction trace for index creation"

10719, 00000, "trace bitmap index dml"

10720, 00000, "trace db scheduling"

// *Comment: traces db scheduling

10722, 00000, "trace server level database scheduling"

10724, 00000, "trace cross-instance broadcast"

// The trace level is a bitfield, so use it as you would a flag.

10730, 00000, "trace row level security policy predicates"

// *Comment: traces SQL statements generated to execute CURSOR expressions

// *Comment: Causes debug trace to be dumped for ksws routines

// Trace level is a bitfield.

// *Comment: Causes debug trace to be dumped for kywm routines

10741, 00000, "trace missing BRR generation"

// Trace level is a bit field.

// 0x0002 Trace missing BRR due to Oracle RAC pings

// trace missing BRR due to Oracle RAC pings

// Trace level is a bitfield.

// use level 5 to trace enabling and level 6 to trace disabling.

10780, 00000, "LogMiner API trace event"

10781, 00000, "LogMiner reader trace event"

10782, 00000, "LogMiner preparer trace event"

10783, 00000, "LogMiner builder trace event"

10784, 00000, "LogMiner dictionary trace event"

10785, 00000, "LogMiner trace event"

10787, 00000, "trace intra-instance broadcast"

10788, 00000, "trace call stacks"

10790, 00000, "LogMiner trace event"

10791, 00000, "Logical Standby swithover/failover trace event"

10792, 00000, "Logical Standby XDAT trace event"

10793, 00000, "Logical Standby trace event"

10794, 00000, "Logical Standby trace event"

10795, 00000, "VKTM Process trace event"

10796, 00000, "Elevate Scheduler Priority trace event"

// *Comment: Causes debug trace to be dumped for gsm routines

10809, 00000, "Trace state object allocate / free history"

// *Comment: This event will trace the history of KSS allocations / deletions.

10810, 00000, "Trace snapshot too old"

10811, 00000, "Trace block cleanouts"

10812, 00000, "Trace Consistent Reads"

10826, 00000, "enable upgrade/downgrade error message trace"

// *Action: Check the trace files and other relevant output

10830, 00000, "Trace group by sort row source"

// trace level is a bitfield

// 0x0001 trace adaptive fetch usage

// 0x0002 trace exceptions during aggregation

// 0x0004 trace use of one comparison for all key columns

10831, 00000, "Trace group by rollup row source"

// trace level is a bitfield

// 0x0001 trace use of one comparison for all key columns

10832, 00000, "Trace approximate NDV row source"

// trace level is a bitfield

// 0x0001 trace

// rollup pushdown is enabled. Trace level indicates the number of

// 0x0001 trace adaptive mode for partial rollup pushdown

10839, 00000, "trace / debug caching module (qesca.c)"

// 0x0001 trace / debug caching

10840, 00000, "trace / debug pl/sql caching module (kkxmInitCache)"

// 0x0001 trace / debug pl/sql caching

// state and the stack trace.

// See trace file for more details.

10880, 00000, "trace Java VM execution"

// related messages to trace files. This includes event

10907, 00000, "Trace extent management events"

10908, 00000, "Trace temp tablespace events"

10975, 00000, "trace execution of parallel propagation"

// > 0 trace the execution of sync procedural replication,

// > 1 trace the loading of meta-data into library cache for ip

10977, 00000, "trace event for RepAPI"

10979, 00000, "trace flags for join index implementation"

// > 1 Dump refresh expressions (SQL) to trace file.

// > 0 Echo input parameter values to the trace log.

// > 1 Display trigger debug info to the trace log also.

// trace file.

22700, 00000, "object access trace event number"

// *Action: If the trace event number is set to a level greater than 0 then

// *Action: Look for information in the session trace file and contact

// *Action: Event used to trace operation of AQ.

// *Action: Set this event in your environment to obtain trace information

// *Action: Event to trace KOH memory events

// bit 0x1: KOH_TRACE_LEVEL_MEM: trace memory alloc and free

// bit 0x2: KOH_TRACE_LEVEL_DUR: trace duration start & end

// bit 0x4: KOH_TRACE_LEVEL_ZEROCACHE: cache max & opt size = 0

// bit 0x8: KOH_TRACE_LEVEL_KOHIH: trace duration list mgmt

// bit 0x10: KOH_TRACE_LEVEL_INS: dump memory or instance

// *Action: Event to trace KOC cache events

// bit 0x1: KOC_TRACE_EVENT_PIN: trace pin operations

// bit 0x2: KOC_TRACE_EVENT_DUR: trace duration operations

// bit 0x4: KOC_TRACE_EVENT_TXN: trace transaction operations

// *Action: Event to trace KOD data events

// bit 0x1: KOD_TRACE_EVENT_PIC: trace pickle operations

// bit 0x2: KOD_TRACE_EVENT_UPIC: trace unpickle operations

// bit 0x4: KOD_TRACE_EVENT_TYPE: trace pickle/unpickle a type

// bit 0x00004000: KOD_TRACE_LEVEL_MEDIUM: generate medium amount of tracing

// bit 0x00008000: KOD_TRACE_LEVEL_HIGH: generate high amount of tracing

// *Action: Event to trace KOL collection events

// bit 0x1: KOL_TRACE_EVENT_PIC: trace pickle operations

// bit 0x2: KOL_TRACE_EVENT_UPIC: trace unpickle operations

// bit 0x40: KOL_TRACE_LEVEL_MEDIUM: generate medium amount of tracing

// bit 0x80: KOL_TRACE_LEVEL_HIGH: generate high amount of tracing

// *Action: Event to trace KOKC events

// bit 0x1: KOkc_TRACE_LEVEL_BASIC: basic dumps

// bit 0x2: KOKC_TRACE_LEVEL_TIME: dump time

// bit 0x4: KOKC_TRACE_LEVEL_KOKCD: dump kokcd structure

// bit 0x8: KOKC_TRACE_LEVEL_ASSERT: enforce asserts

// bit 0x1000: KOKC_TRACE_LEVEL_STACK: dump short stack

// bit 0x2000: KOKC_TRACE_LEVEL_LSTACK: dump full stack

// *Action: Event to trace KOPF operations (FDO)

// bit 0x1: KOPF_TRACE_EVENT_FDO: dump FDO's in kopfupdate()

// *Cause: Number of parameters specified in OCI trace event have exceeded

24917, 00000, "OCI notification client trace event"

// *Action: Look at the client trace files for additional information.

// *Action: Event used to trace EMON.

// to provide the trace file and information about reproducing the

// the status of these processes. Also check the trace file for more

// process. Also check the trace files for more information.

// and check the trace file for information about the object.

// *Action: Refer to trace file for more details.

// *Action: Check the error stack and trace file for error details.

// See trace file for more details.

// *Action: Check trace to see how the jobs were submitted and drop them.

// *Action: Check trace to see how the rows were inserted and call

// *Action: Check trace to see how the jobs were submitted and drop them.

// *Action: Check trace to see how the invalid GoldenGate, XStream or Streams type was created and

// *Action: Check trace to identify the problem and call

// *Action: Check trace for more detail.

// *Action: Check the corresponding split/merge row in DBA_STREAMS_SPLIT_MERGE view, alert log and trace file for details.

// *Action: check for trace file and contact Oracle Support

// *Action: check Oracle trace file, OS message files and

// *Action: check for trace file and contact Oracle Support

// *Action: Check for trace file and contact Oracle Support.

// *Action: Check the Oracle trace file for additional information on file name

// *Action: Consult the trace file for details

// *Action: Check additional error messages in the process trace file and

// trace file.

// If there is a trace file, report the errors in it to Oracle

// is a trace file, report the errors in it to Oracle Support

// the process trace file and alert log.

// Check the additional diagnostic information in the trace file and

// *Action: Check the alert log and process trace file for additional

// specified in the trace file cannot be corrected.

// Support Services along with alert log and trace files. If the cellsrv

// *Action: Check the additional diagnostic information in the trace file and

// Check the trace file for the name of the affected policy and

// *Action: Check the trace file and correct the errors.

// *Action: Review the trace file for detailed error information.

// *Action: Refer to the trace file for details.

// *Action: Set this event in your environment to obtain trace information

// *Action: Check the error. For more information on the error, check the trace

// to a trace (*.TRC) file for the ORACLE instance and to

// version incompatibility is logged in trace (*.TRC) files,

// trace file.

// Also check for additional information in log and trace

29712, 00000, "enable global enqueue service cancel event trace"

// *Action: Dump trace for global enqueue service cancel.

29713, 00000, "enable global enqueue service close event trace"

// *Action: Dump trace for global enqueue service close.

29714, 00000, "enable global enqueue service state object event trace"

// *Action: Dump trace for global enqueue service state object.

29717, 00000, "enable global resource directory freeze/unfreeze event trace"

// *Action: Dump trace for global resource directory freeze/unfreeze.

29718, 00000, "enable global enqueue service CGS interface event trace"

// *Action: Dump trace for global enqueue service CGS interface.

29720, 00000, "enable global enqueue service SCN service event trace"

// *Action: Dump trace for global enqueue service SCN service.

29722, 00000, "enable global enqueue service process death event trace"

// *Action: Dump trace for global enqueue service process death.

29724, 00000, "enable global enqueue heartbeat and CSS/GIPC event trace"

// *Action: Dump trace for global enqueue heartbeat operations and CSS/GIPC

// *Action: Enable trace and field levels for KA diagnostics

29733, 00000, "enable multiple recovery domains event trace"

// *Action: Dump trace for ges/gcs multiple recovery domains

// *Action: Check the trace files of other active instances in the cluster

// *Action: Check the alert log, LMON and LMHB trace files for any errors and

29750, 00000, "enable CGS name service event trace"

// *Action: Check the alert file and relevent trace files and

// *Action: Check the alert file and relevent trace files and contact

// *Action: Enable trace and field levels for GCR diagnostics.

// *Action: Follow up with the Oracle Support Services using the traces

// *Action: Increase shared_pool_size in Init.ora. Check relevant trace files to get more information about the current number of CQN registrations that caused this error message.

// 0x01 - trace loading of TT

// 0x02 - trace SQL statement generation

// 0x04 - trace RAC execution

// 0x08 - trace DBMS_DST procedures

30402, 00000, "access advisor debug/testing trace"

// occurred. The trace logs for each refresh operation describe the

// *Action: Check the trace logs for more information. Ensure that the

// the trace log for the failed process to see if there is any

// *Action: Check your system for problems. Check the trace file for the

// trace file for the master process for any additional information.

// unexpectedly. Check the log file for the operaton and the trace

// the trace log for the failed process to see if there is any

31902, 00000, "access advisor debug/testing trace"

31904, 00000, "Synchronous refresh debug or testing trace"

31929, 00000, "Synchronous refresh debug or testing trace"

// *Action: Check FMON trace files for errors.

// check FMON trace files for an ORA-32052 error.

// *Action: Check FMON trace files for errors.

// *Action: Check FMON and FMPUTL trace files for errors.

// *Action: Check foreground trace files for errors.

// Oracle DIAG trace files for errors.

// *Action: Check alert log and DIAG trace file for error detail

// *Action: Check error in DIAG trace file and issue the command again

// *Action: Check DIAG trace files for errors.

// *Action: Check the trace files.

// *Action: Check Trace file for debug info

32781, 00000, "Trace event for window functions"

// Trace level is a bitfield.

// 0x0001 trace window functions after ordering semantics rewrite

// 0x0002 trace statistics collected for window functions

// 0x0004 trace ordering groups generated for window functions

// 0x0008 trace dynamic sampling statistics expressions

// 0x0010 trace messages for slave-2-QC communication

// 0x0020 trace window consolidation rows

38344, 00000, "bulk load compression trace event"

// *Comment: Turn on/off various bulk load compression trace features.

// More information was dumped to the trace file.

// *Action: See trace files for details of the problem.

// *Action: Check the FLASHBACK DATABASE command trace file for a

// *Action: Check the trace log for the offline range of the data file.

// *Action: See trace files for details of the problem.

// the trace log for the failed process to see if there is any

39089, 00000, "ku trace event"

// *Comment: Turn on/off various ku trace and debug features (documentation

// check the trace log for the failed process, if one was created,

40288, 00000, "event to debug and trace mining model load"

// Level 2 - trace model load info

// *Action: Turn on related trace for details and report it as a bug.

// package, see kpbf.c for trace levels.

// *Action: Check trace message.

// wallet is on the token. Check trace message

// *Action: Check trace message

43822, 0000, "enable/disable/trace rejection heuristic"

// *Action : Causes kkdcCheckAcrMap to write debug information to the trace

45490, 00000, "rolling upgrade trace event"

// *Action : Check the database server traces and contact Oracle Support

// *Action: Check the trace files for the names of the invalid objects found

// *Action: Check the trace files for details regarding which sensitive

// *Action: Check the trace files for more details.

// *Action: Check the trace file for a list of sensitive columns that were

// the error stack or alert log or trace file that indicate

// *Action: Check the trace file for detailed error information.

// *Action: Check the trace files to identify the source of the error in the

// *Action: Check the PL/SQL error stack and trace files for a possible error.

// *Action: Check the trace files for the PDB information where the operation

// *Action: Check the trace file for more information.

// *Action: Check the trace file for more information and try again.

// exists and the operation is failing, check the trace file and

// *Action: Check the trace file for more information, create permissions on

// *Action: Check the trace file for more information, specify the correct

// *Action: Check trace files for more information. Delete the target keystore

// *Action: Check trace files for more information. If the target keystore is

// *Action: Check the contents of the trace file to identify the faulty master

// *Action: Check the trace file for errors and retry the command.

// *Action: Check the contents of the trace file for errors and retry the

// *Action: Check the contents of the trace file for more information on the

// *Action: Check the trace files for errors. Create the keys in PDBs where

// *Action: Check the trace files for errors. REKEY the keys in PDBs where

// *Action: Check the contents of the trace file for more information on the

// *Action: Check the trace files for errors. Activate the keys in PDBs where

// *Action: Check the trace files for errors. REKEY the PDBs where the master

// *Action: Check the trace file for more information and retry the operation.

// If there is no trace information and trying the operation again

// *Action: Check the trace file for more information and retry the operation.

// If there is no trace information and trying the operation again

// sgaetjen 12/30/05 - add trace event

/ husun 10/02/07 - add backup control file to trace message

/ hayu 09/21/06 - add message for trace merging

/ bdagevil 06/11/06 - error for parsing UTS Trace event

// *Action: Examine the thread trace file for more information.

54674, 0000, "Event to dump the R-tree node cache contents to trace file"

// *Action: Dump the R-tree node cache contents to trace file. Set this event

54675, 0000, "Event to enable diagnostic and informational messages to trace file"

// *Action: Enable or disable diagnostic and informational messages to trace

// *Action: Check the stack trace for additional information.

// *Action: Check the error message and the stack trace.

55500, 0000, "Event to be used for different trace levels"

55516, 0000, "Event to dump the LCR's seen by flashback transaction backout to trace file"

// Level 1 > Enable general temp undo debug traces

// Level 1 > Enable general parallel transaction debug traces

55570, 0000, "transaction layer debug trace event"

55703, 00000, "Trace min-act to MMON trace file"

// *Action: Dumps min-act-scn tracing to MMON trace file.

55901, 0000, "Secure file log Trace event"

// Check the trace files on remote instances for the underlying

// *Action: Review trace files for errors.

56801, 00000, "ksz trace event"

// *Comment: Turn on/off various ksz trace and debug features (documentation

// *Action: Check alert or trace file for error(s).

// *Action: Check alert or trace file for error(s) or complete upgrading of

// *Action: Check alert or trace files for error(s).

// Additional information in the trace file provides details

// *Action: Check additional information in the trace file.

// error. See alert or trace files in all instances for further

// *Action: None. This error is for understanding the trace file of XMLIndex rewrite.

// *Cause: There was no subprogram, error, or backtrace at the specified stack

// or backtrace.

Translate

Thursday, 28 August 2014

How Oracle finds things in a data file - From the simplest of the Queries to the instance Startup

So, how does it do it?

Wednesday, 27 August 2014

Data Redaction - See what you shouldn't see in 5 minutes

What Oracle has said about Data Redaction

Conclusion

Wednesday, 6 August 2014

Oracle Extended Tracing

Definitions

Types of Trace Files

Extended Tracing and Multitenancy

Setting Trace Events

Trace Events