The Arup Nanda Blog

Streams Pool is only for Streams? Think Again!

2013-04-19T09:52:00.000-04:00

If you don’t use the automatic SGA (i.e. set the sga_target=0) - something I frequently do - and don’t use Streams, you probably have set the parameter streams_pool_size to 0 or not set it at all, since you reckon that the pool is used for Streams alone and therefore would be irrelevant in your environment wasting memory.

But did you know that the Streams Pool is not just for Streams and it is used for other tools some of which are frequently used in almost any database environment? Take for instance, Data Pump. It uses Streams Pool, contrary to conventional wisdom. If Streams Pool is not defined, it is dynamically allocated by stealing that much memory from the buffer cache. And the size is not reset back to zero after the demand for the pool is over. You should be aware of this lesser known fact as it reduces the buffer cache you had allocated to the instance earlier.

Demonstration

Let’s examine this with an example. First, let’s check the various pools defined in the database instance right now:

SQL> show parameter sga_target

NAME TYPE VALUE
------------------------------------ ----------- -----
sga_target big integer 0

SQL> show parameter db_cache_size

NAME TYPE VALUE
------------------------------------ ----------- -----
db_cache_size big integer 300M

SQL> show parameter streams_pool_size

NAME TYPE VALUE
------------------------------------ ----------- -----
streams_pool_size big integer 0

Note carefully the values of the following parameters:

sga_target = 0 --> this means the SGA is not auto tuned.
db_cache_size = 300M --> this is the buffer cache
streams_pool_size = 0 --> this is the stream pool, set to 0 as expected

Now kick off a Data Pump Export (expdp) job:

$ expdp directory=DATA_FILE_DIR tables=arup.t1

... output truncated ...

Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 14:49:16

After the Data Pump job is complete, check the size of the buffer cache again:

SQL> show parameter db_cache_size

NAME TYPE VALUE
------------------------------------ ----------- -----
db_cache_size big integer 280M

The buffer cache got compressed from 300 MB earlier to 280 MB. But you didn’t do that; Oracle did it.

Well, where did the 20 MB of missing memory go? Now, check the size of the Streams Pool:

SQL> show parameter streams_pool_size

NAME TYPE VALUE
------------------------------------ ----------- -----
streams_pool_size big integer 20M

The Streams Pool was 0 earlier, as you intended it to be; but Oracle allocated 20 MB to it by stealing that much memory from the buffer cache. The reason: the Streams Pool was used for the Data Pump Export job, even though it does not sound intuitive. If you check the alert log, you will see the activity recorded there:

$ adrci

ADRCI: Release 11.2.0.1.0 - Production on Thu Apr 18 14:44:46 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

ADR base = "/opt/oracle"
adrci> set homepath diag/rdbms/d112d2/D112D2
adrci> show alert -tail –f

Here are the excerpts from the alert log:

2013-04-18 14:48:45.581000 -04:00
streams_pool_size defaulting to 20971520. Trying to get it from Buffer Cache for process 27378.

The next question you may be wondering about is – why did Oracle decide to give only 20 MB to the Streams Pool? Why not 100 MB, or 10 MB? Is it dependent on the size of the table being exported? The answer is no.

Oracle by default gives 10% of the size of the shared pool to the Streams Pool. Let me find out the size of the shared pool:

SQL> show parameter shared_pool_size

NAME TYPE VALUE
------------------------------------ ----------- -----
shared_pool_size big integer 200M

The shared pool is 200 MB. 10% of that is 20 MB, which is how much was assigned to the Streams Pool. That size is not dependent on the size of the exported data; but the size of the shared pool.

It's important to understand that the shared pool is used to compute the default size of the streams pool; the actual memory is carved out of buffer cache; not the shared pool.

If you check the database’s operations, you will be able to confirm Oracle’s adjustment of the pools:

SQL> select component, oper_type, parameter, initial_size, target_size, final_size
2 from v$sga_resize_ops
3 order by start_time;

COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE
--------------- ------------- -------------------- ------------ ----------- ----------
DEFAULT buffer STATIC db_cache_size 0 314572800 314572800
cache
DEFAULT buffer SHRINK db_cache_size 314572800 293601280 293601280
cache
streams pool GROW streams_pool_size 0 20971520 20971520

The output has been truncated to show only the relevant records. From the output you can see clearly that the buffer cache was defined statically as 314572800, or 300 MB initially. Later the buffer cache shrank from 314572800 to 293601280 (about 280 MB). The amount of shrinkage was 314572800 - 293601280 = 20971520 (or, 20 MB), the exact amount the streams_pool_size was allocated.

Why this is a problem? Well, the biggest problem is that the buffer cache size is now reduced without your knowledge. The buffer cache lost 10% of the shared pool. But systems with large shared pool, it could be substantial. Worse, the amount allocated to Streams Pool remains there; it is not returned to the buffer cache as you might expect. You have to manually give it back:

SQL> alter system set streams_pool_size = 0;

In case of a RAC database, it’s possible that only one instance sees this change in Streams Pool size; the other instances will be unaffected.

It would be prudent to note here that this surprise occurs when you do not use automatic SGA settings. When auto SGA is used, i.e. sga_target is set to a non-zero value, you give up complete control to Oracle to manipulate the memory structures. In that case Oracle juggles the memory between various pools – including Streams Pool - without your control anyway.

While it is not very well known, this behavior is not undocumented. It’s mentioned in the Utilities Guide at http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_perf.htm#SUTIL973.

Conclusion

Just because you haven’t defined the streams_pool_size parameter as you don’t use Streams doesn't mean that Oracle will not assign some memory to Streams Pool. Data Pump, which is frequently used in many databases, uses the Streams Pool and Oracle will assign it as 10% of the size of the shared pool and reduce the buffer cache by that amount to fund the memory for the Streams Pool. So you should configure the Streams Pool, even if you don’t use Streams, so that Data Pump can use a precisely allocated pool it rather than stealing it from the Buffer Cache. If you don’t do that now, or don’t intend to do it, then regularly check the streams_pool_size value and set it to zero if it is not so.

Application Design is the only Reason for Deadlocks? Think Again

2013-04-18T13:16:00.000-04:00

[Updated on 4/20/2013 after feedback from Charles Hooper, Jonathan Lewis, Laurent Schneider and Mohamed Houri and with some minor cosmetic enhancements of outputs]

Have you ever seen a message “ORA-00060: Deadlock detected” and automatically assumed that it was an application coding issue? Well, it may not be. There are DBA-related issues and you may be surprised to find out that INSERTs may cause deadlock. Learn all the conditions that precipitate this error, how to read the "deadlock graph" to determine the cause, and most important: how to avoid it.

Introduction

I often get a lot of questions in some form or the other like the following:

What's a Deadlock
How can I prevent it
Why would an INSERT cause deadlock
Why would I need to index FK columns
Is ON DELETE CASCADE FK constraint a good idea?

Deadlock is one of those little understood and often misinterpreted concepts in the Oracle Database. The word rhymes with locking, so most people assume that it is some form of row locking. Broadly speaking, it’s accurate; but not entirely. There could be causes other than row level locking. This is also often confused by people new to Oracle technology since the term deadlock may have a different meaning in other databases. To add to the confusion, Oracle’s standard response to the problem is that it’s an application design issue and therefore should be solved through application redesign. Well, in a majority of cases application design is a problem; but not in all cases. In this post, I will describe:

Why Deadlocks Occur
Primer on Oracle Latching, Locking
How to Interpret Deadlock Traces
Various Cases of Deadlocks
Some Unusual Cases from My Experience

Deadlocks Explained

With two Oracle sessions each locking the resource requested by the other, there will never be a resolution because both will be hanging denying them the opportunity to commit ot rollback and therefore releasing the lock. Oracle automatically detects this deadly embrace and breaks it by forcing one statement to roll back abruptly (and releasing the lock) and letting the other transaction to continue.

Here is how a deadlock occurs. Two sessions are involved, doing updates on different rows, as shown below:

Step Session 1           Session 2
---- ------------------- -----------------
1.   Update Row1
     (Does not Commit)
2.                       Update Row2
                        (Does not Commit)
3.   Update Row2
4.   Waits on TX Enqueue
5.                       Update Row1

At the step 5 above since Row1 is locked by session1, session2 will wait; but this wait will be forever, since session1 is also waiting and can’t perform a commit or rollback until that wait is over. But session 1's wait will continue to exist until session 2 commits or rollback - a Catch 22 situation. This situation is a cause of deadlock and Oracle triggers the ststement at Step 3 to be rolled back (since it detected that deadlock). Note that only the statement that detected the deadlock is rolled; the previous statements stay. For instance, update row1 in Step 1 stays.

This is the most common cause of deadlocks and is purely driven by application design and can only be solved by reducing the possibility of occurence of that scenario. Now that you understand how a deadlock occurs, we will explore some other causes of deadlocks. But before that, we will explore different types of locks in Oracle.

Types of Locks

Database locks are queue-based, i.e. the session first waiting for the lock will get it first, before another session which started waiting for the same resource after the first session. The requesters are placed in a queue, hence locks are also called Enqueues. There are several types of enqueues; but we will focus on row locking, and specifically only two type of them:

TM – this is related to database structural changes. Suppose someone is executing some query against a table, such as SELECTing from it. The table structure should remain the same in that period. TM locks protect the table structure so that someone does not add a column during that query. TM locks allow multiple queries and DMLs, but not DDL against the table.
TX – this is the row level locking. When a row is locked by a session, this type of lock is acquired.

Anatomy of a Deadlock Trace

When a deadlock occurs and one of the statements gets rolled back, Oracle records the incident in the alert log. Here is an example entry:

 ORA-00060: Deadlock detected. More info in file
/opt/oracle/diag/rdbms/odba112/ODBA112/trace/ODBA112_ora_18301.trc.

Along with the alert log entry, the incident creates a tracefile (as shown above). The trace file shows valuable information on the deadlock and should be your first stop in diagnosis. Let's see the various sections of the tracefile:

Deadlock Graph

The first section is important; it shows the deadlock graph. Here are the various pieces of information on the graph. Deadlock graph tells you which sessions are involved, what types of locks are being sought after, etc. Let's examine the deadlock graph, shown in the figure below:

Row Information

The next critical section shows the information on the rows locked during the activities of the two sessions. From the tracefile you can see the object ID. Using that, you can get the object owner and the name from the DBA_OBJECTS view. The information in on rowID is also available here. You can get primary key information from the object using that rowID.

Process Information

The tracefile also shows the Oracle process information which displays the calling user. That information is critical since the schema owner may not be the one that issued the statement.

With the information collected from various sections of the deadlock graph, you now know the following:

The session that caused it
The session that was the victim
The Oracle SID and process ID of the sessions
The object (the table, materialized view, etc.) whose row was in the deadlock
The exact row that was so popular to cause the deadlock.
The SQL statement that caused the deadlock.
The machine the session came from with the module, program (e.g. SQL*Plus) and userid information

Now it is a cinch to know the cause of that deadlock and which specific part of the application you need to address to fix it.

Other Causes

The case described above is just one type of locking scenario causing deadlocks; but this is not the only one. Other types of locks also cause deadlocks. These scenarios are usually difficult to identify and diagnose and are often misinterpreted. Well, not for you.You will learn how to diagnose these other causes in this post. These causes include:

ITL Waits
Bitmap Index Update
Direct Path Load
Overlapping PK Values

Deadlocks due to ITL Shortage

You can read how ITL works in another of my blogposts - How Oracle Locking Works. In summary, when a session locks a row, it does not go to a central lock repository and get a lock from there. Instead, the session puts the information on the lock in the header of the block, called Interested Transaction List (ITL). Each ITLslot takes up 24 bytes. Figure 1 below shows an empty block with just one ITL slot. When rows are inserted, from bottom of the block upwards, the free space gradually drops.

When a session - session1 - wants to lock the row1, it uses the slot#1 of the ITL, as shown in Figure 3 below. Later, another session – session2 – updates row2. Since there is no more ITL slot, Oracle creates a new slot – slot#2 – for this transaction. However, at this stage, the block is almost packed. If a third transaction comes in, there will be no more room for a third ITL slot to be created; causing the session to wait on ITL. Remember, this new session wants to lock row3, which is not locked by anyone and could have been locked by the session; but it’s artificially prevented from being locked due to the absence of an ITL slot.

Checking for ITL Shortage

You can check for ITL shortage by issuing this query:

select owner, object_name, value
from v$segment_statistics
where statistic_name = 'ITL waits'
and value > 0

Here is a sample output:

OWNER       OBJECT_NAME                    VALUE
----------- ------------------------- ----------
SYSMAN      MGMT_METRICS_1HOUR_PK             19
ARUP        DLT2                              23
ARUP        DLT1                             131

If you check the EVENT column of V$SESSION to see which sessions are experiencing it right now, you will see that the sessions are waiting with the event: enq: TX - allocate ITL entry.

Deadlock Scenario

Here is the scenario where two sessions cause a deadlock due to ITL shortage. Imagine two rows – row1 and row2 – are in the same block. The block is so tightly packed that only two ITL slots can be created.

Step	Session1	Session2
1	Update Table1 Row1 (1 ITL slot is used; no more free ITL slots and no room in the block to create one)
2		Update Table2 Row1 (One ITL slot is gone. There are no more free ITL slots and no room to create one)
3	Update Table2 Row2 (Lack of ITL slots; so this will hang)
4		Update Table1 Row2 (Lack of ITL slots will make this hang as well. Deadlock!)

At Step 4 Session 2's hang can't be resolved until session 1 releases the lock, which is not possible since it itself is hanging. This never ending situation is handled by Oracle by detecting it as a deadlock and killing one of the sessions.

Deadlock Graph

To identify this scenario as the cause of deadlock, look at the deadlock graph. This is how a deadlock graph looks like when caused by ITL waits.

The absence of row information on one of the sessions is a dead giveaway that this is a block level issue; not related to specific rows. Here are the clues in this deadlock graph:

The lock type is TX (row lock) for both the sessions
The holders held the lock in "X" (exclusive) mode (this is expected for TX locks)
However, only one of the waiters is waiting in the "X" mode. The other is waiting with the "S" (shared) mode, indicating that it's not really a row lock the session is waiting for.
One session has the row information; the other doesn't.

These clues give you the confirmation that this is an ITL related deadlock; not because of the application design. Further down the tracefile we see:

As you can see, it’s not 100% clear from the tracefile that the deadlock was caused by ITL. However by examining the tracefile we see that the locks are of TX type and the wait is in the “S” (shared) mode. This usually indicates ITL wait deadlock. You can confirm that is the case by checking the ITL shortages on that segment from the view V$SEGMENT_STATISTICS as shown earlier.

Update on 4/19/2013: [Thanks, Jonathan Lewis] Occasionally you may see two rows here as well, as a result of a previous wait (e.g. buffer busy wait) on the block which has not been cleaned out yet. In such a case you will see information on two rows; but there are some other clues that may point to this cause. The row portion of the rowid will be 0, meaning it was not a row but the block. The other clue might be that the row information points to a row that has nothing to do with the SQL statement. For instance, you may find the row information pointing to a row in table Table1 whereas the SQL statement is "update Table2 set col2 = 'X' where col1 = 2".

The solution is very simple. Just increase the INITRANS value of the table. INITRANS determines the initial number of ITL slots. Please note, this value will affect only the new blocks; the old ones will still be left with the old values. To affect the old ones you can issue ALTER TABLE TableName MOVE to move the tables to nw blocks and hence new structure.

Deadlock due to Foreign Key

This is a really tricky one; but not impossible to identify. When a key value in parent table is updatd or a row is deleted, Oracle attempts to takes TM lock on the entire child table. If an index is present on the foreign key column, then Oracle locates the corresponding child rows and locks only those rows. The documentation in some versions may not very clear on this. There is a documentation bug (MOS Bug# 2546492). In the absense of the index, a whole table TM lock may cause a deadlock. Let's see the scenario when it happens.

Scenario

Here is the scenario when this deadlock occurs.

Step	Session1	Session2
1	Delete Chaild Row1
2		Delete Child Row2
3	Delete Parent Row1 (Waits on TM Enqueue)
4		Delete Parent Row2 (Waits on TM Enqueue) Deadlock!

Deadlock Graph

This is how the deadlock graph looks like when caused by unindexed foreign key. As you can see, the deadlock graph does not clearly say that the issue was to do with Foreign Key columns not being indexed.Instead, the clues here are:

TM locks for both the sessions, instead of TX. Remember: TM are metadata related, as opposed to TX, which is a row related lock.
The lock type of holders is Share Exclusive (SX) as opposed to Exclusive (X)
Sessions do not show any row information

These three clues together show that this deadlock is due to FK contention rather than the conventional row locks.

So, what do you do? Simple - create the indexes on those FKs and you will not see this again. As a general rule you should have indexes on FKs anyway; but there are exceptions, e.g. a table whose parent key is never updated or deleted infrequently (think a table with country codes, state codes or something pervasive like that). If you see a lot of deadlocks in those cases, perhaps you should create indexes on those tables anyway.

Deadlock due to Direct Load

Direct Load is the fastest way to load data into a table from another source such as a table or a text file. It can be effected in two ways – the APPEND hint in INSERT statement ( insert /*+ append */ ) or by using DIRECT=Y option in SQL*Loader. When a table is loaded with Direct Path, the entire table is locked from further DMLs, until committed. This lock may cause deadlocks, when two sessions try to load into the same table, as shown by the scenario below.

Scenario

Step	Session1	Session2
1	Direct Path Load into Table1
2		Direct Path Load into Table2
3	Direct Path Load into Table2 (Hangs with TM Enqueue; since Session2 has the lock)
4		Direct Load into Table1 TM lock on Table1 prevents this operation Deadlock!

Deadlock Graph

As usual, the deadlock graph confirms this condition. Here is how the deadlock graph looks like:
Both sessions do not show any row information; and subsequent parts of the tracefile do not show any other relevant information. The key to identify this deadlock as caused by Direct Path is to look for the type of lock mode – X. This type of lock mode exists for row level locking as well. However the deadlock graph shows row information in that case. So, the clues for this type of deadlock are:

Lock type is TM (as shown in the Resource Name)
Lock mode for both the holders and waiters is X (indicating a row lock)
No row information (since it is not really row-related)

Deadlock due to Bitmap Index Contention

Bitmap Index is a special type of index that stores bitmaps of actual values and compare bitmaps to bitmaps, e.g. instead of comparing literals such as "A" = "A", Oracle converts the value to a bitmap and compares against the stored bitmap values. For instance “A” might be represented as "01011"; so the comparison will be “01011” = "01011". Index searches are way faster compared to literal comparison.

However, there is a price to pay for this performance. Unlike a regular b*tree index, when a row is updated, the index piece of the bitmap index is locked until the transaction is committed. Therefore udates to any of the rows covered by that index piece hangs. When two sessions update two different rows covered by the same index piece, they wait for each other. Here is the scenario when this condition arises.

Scenario

Step	Session1	Session2
1	Update Row 1 (Bitmap index piece is locked)
2		Update Row2 (Hangs for TX Row Lock)
3	Update Row2 (Hangs as bitmap index piece is locked by session2 and can't release until it commits) Deadlock!

Deadlock Graph

You can confirm this occurrence from readling the deadlock graph.

The clues that show this type of deadlock:

The lock type is TX (as shown in the Resource Name)
The lock wait mode is “S” (shared) but the type of lock is TX rather than TM.
The waiter waits with mode "S" instead of "X"
The row information is available but the object ID is not the ID of the table; but the bitmap index.

The solution to this deadlock is really simple – just alter the application logic in such a way that the two updates will not happen in sequence without commits in between. If that’s not possible, then you have to re-evaluate the need for a bitmap index. Bitmap indexes are usually for datawarehouse only; not for OLTP.

Deadlock due to Primary Key Overlap

This is a very special case of deadlock, which occurs during inserts; not updates or deletes. This is probably the only case where inserts cause deadlocks. When you insert a record into a table but not commit it, the record goes in but a further insert with the same primary key value waits. This lock is required for Oracle because the first insert may be rolled back, allowing the second one to pass through. If the first insert is committed, then the second insert fails with a PK violation. But in the meantime-before the commit or rollback is issued-the transaction causes the second insert to wait and that causes deadlock. Let's examine the scenario:

Scenario

Step	Session1	Session2
1	Insert PK Col value = 1 (Doesn't commit)
2		Insert PK Col value = 2 (Doesn't commit)
3	Insert PK Col = 2 (Hangs, until Session2 commits)
4		Insert PK Col = 1 (Hangs and Deadlock)

Deadlock Graph

The deadlock graph looks like the following.

The key clues are:

The lock type is TX (row lock)
The holders are holding the lock in "X" (exclusive) mode
The waiters are waiting for locks in “S” mode, even when the locks type TX.
The subsequent parts of the tracefile don’t show any row information.

However, the latter parts of the tracefile shows the SQL statement, which should be able to point to the cause of the deadlock as the primary key deadlock. Remember, this may be difficult to diagnose first since there is no row information. But this is probably normal since the row is not formed yet (it's INSERT, remember?).

Special Cases

I have encountered some very interesting cases of deadlocks which may be rather difficult to diagnose. Here are some of these special cases.

Autonomous Transactions

Autonomous transactions are ones that are kicked off form inside another transaction. The autonomous one follows its own commit, i.e. it can commit independently of the outer transaction. The autonomous transaction may lock some records the parent transaction might be interested in and vice versa – a perfect condition for deadlocks. Since the autonomous transactions is triggered by its parent, the deadlocks are usually difficult to catch.

Here is how the deadlock graph looks like (exceprted from the tracefile)

---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-0005002d-00001a40 17 14 X 17 14 X
session 14: DID 0001-0011-00000077
session 14: DID 0001-0011-00000077
Rows waited on:
Session 14: obj - rowid = 000078D5 - AAAHjVAAHAAAACOAAA
(dictionary objn - 30933, file - 7, block - 142, slot - 0)
Information on the OTHER waiting sessions:
End of information on OTHER waiting sessions.

Here are the interesting things about this deadlock graph, which are clues to identifying this type of deadlock:

The lock type is TX (row lock) and the mode is "X", which is exclusive. This indicates a simple row lock.
Remember, deadlocks are always as a result of two transactions; not one. However, the deadlock graph shows only one session. The other session information is not even there.The presence of only one session indicates that the other transaction originated from the same session - hence only one session was recorded. The only way two transactions could have originated from the same session is when the transaction is an autonomous one.
The row information is not there because the autonomous transaction acts independently of the parent.

If you see a deadlock graph like this, you can be pretty much assured that autonomous transactions are to blame.

Update on 4/19/2013. [Thanks, Mohamed Houri] The above cause is not limited to TX locks; it could happen in TM locks as well. The diagnosis remains the same.

Deadlocks among the PQ slaves

Consider a procedural logic like this:

 LOOP

   SELECT /*+ PARALLEL */ … FOR UPDATE

 END LOOP

This code locks the rows selected by the parallel query slaves. Since the select is done in parallel, the PQ slaves distribute the rows to be selected. Therefore the locking is also distributed among the PQ slaves. Since no two rows are updated by the same PQ slave (and hence the same session), there is no cause for deadlocks.

However, assume the code is kicked off more than once concurrently. This kicks off several PQ slaves and many query coordinators. In this case there is no guarantee that two slaves (from different coordinators) will not pick up the same row. In that case, you may run into deadlocks.

Triggers firing Autonomous Transactions

If you have triggers firing Autonomous Transactions, they may cause deadlocks, in the same line described in the section on autonomous transactions.

Freelists

In case of tablespaces defined with manual segment space management, if too many process freelists are defined, it's possible to run out of transaction freelists, causing deadlocks.

In Conclusion

The most common cause of deadlocks is the normal row level locking, which is relatively easy to find. But that's not the only reason. ITL Shortage, Bitmap Index Locking, Lack of FK Index, Direct Path Load, PK Overlap are also some of the potential causes. You must check the tracefile and interpret the deadlock graph to come to a definite conclusion on the cause of the deadlock. Some of the causes, e.g. ITL shortage, are to do with the schema design; not application design and are quite easy to solve. Some causes, as in the case of the PK overlap case, INSERTs cause deadlocks.

I hope you found it useful in diagnosing the deadlock conditions in your system. As always, your feedback is very much appreciated.

Exadata Article as NYOUG's Article of the Year 2012

2013-04-09T00:18:00.000-04:00

Exadata Article as NYOUG's Article of the Year 2012The Editors of New York Oracle User Group (NYOUG) publication - TechJournal - chose my article Exadata Demystified as the Article of the Year. Here is the snippet from the Editorial:

And the Award Goes To …

The Editor’s Choice Award for 2013 (for papers written and/or presented in 2012) is awarded to Arup Nanda, author of the paper, Exadata Demystified, published in the current issue of the NYOUG Tech Journal. Arup presented this topic at the December 2012 NYOUG User Group meeting. A long-time DBA (17 years, so far), Arup is a consummate database professional, two-time recipient of Oracle Magazine’s annual excellence awards (DBA of the year, 2003, and Technologist of the Year, 2012), prolific author (coauthor of 4 books and author of more than 300 articles), and a tireless mentor. Arup consistently delivers well-researched and engaging papers and presentations, and is a marvelous educator.
His attention to detail and clear expository style help to make each one of his articles an informative read and his presentations an enjoyable educational experience. If Arup’s Exadata paper had not been chosen for the Editor’s Choice award, his paper, Partitioning: What, When, Why and How, (also published in the current issue) would have taken its place. Whether you agree with my choice of which of his papers is actually more useful for your (or general) purposes, I think you will agree that both are well worth a read, and a re-read, and a forwarding-on. It is a pleasure to have Arup Nanda associated with the NYOUG.

Thank you, per usual, for all of your huge contributions to the Oracle community.

Thank you, Melanie Caffrey - the Editor of TechJournal. I am humbled and without words. Your recognition of my work is very much appreciated.

If you are not a member of NYOUG, would you like to read the paper? Well, Melanie (and NYOUG) has graciously provided the permission for me to reproduce this article on my blog. Here it is. Please feel to download and read. As always, I would very much like to know what youthought.

While on that topic, you may also want to check out my four article series on Exadata Command Reference on Oracle Technology Network. It describes various tools, utilities and commands to become an expert Database Machine Administrator (DMA) of Exadata.

Switching Back to Regular Listener Log Format

2013-04-05T21:40:00.000-04:00

Did you ever miss the older listener log file format and want to turn off the ADR-style log introduced in 11g? Well, it's really very simple.

Problem

Oracle introduced the Automatic Diagnostic Repository (ADR) with Oracle 11g Release 1. This introduced some type of streamlining of various log and trace files generated by different Oracle components such as the database, listener, ASM, etc. this is why you didn't find the alert log in the usual location specified by the familiar background_dump_dest initialization parameter but in a directory specified by a diferent parameter - ADR_BASE. Similarly listener logs now go in this format:

$ADR_BASE/tnslsnr//listener/alert/log.xml

Remember, this is in the XML format; not the usual listener.log. The idea was to present the information in the listener log in a consistent, machine readable format instead of the usually cryptic inconsistent older listener log format. Here is an example of the new format:

<msg  time='2013-03-31T13:17:22.633-04:00' org_id='oracle' comp_id='tnslsnr'
  type='UNKNOWN' level='16' host_id='oradba2'
  host_addr='127.0.0.1' version='1'
>
  <txt>31-MAR-2013 13:17:22 * service_update * D112D2 * 0</txt>
</msg>
<msg> time='2013-03-31T13:17:25.317-04:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='oradba2'
 host_addr='127.0.0.1'
>
 <txt>WARNING: Subscription for node down event still pending </txt>
</msg>

Being in XML format, many tools now can be made to read the files unambiguously since the data is now enclosed within meaningful tags. Additionally the listener log files (the XML format) is now rotated. After reaching a certain threshold value the file is renamed to log_1.xml and a new log.xml is created - somewhat akin to the archived log concept in the case of redo log files.
While it proved useful for new tools, there was also the presence of myriads of tools that read the older log format perfectly. So Oracle didn't stop the practice of writing to the old format log. The old format log was still called listener.log but the directory it is created in is different - $ADR_BASE/tnslsnr/Hostname/listener/trace. Unfortunately there is no archiving scheme for this file so this simply kept growing.
In the pre-11g days you could temporarily redirect the log to a different location and archive the old one by setting the following parameter in listener.ora:

log_directory = tempLocation

However, in Oracle 11g R1 and beyond, this will not work; you can't set the location of the log_directory.

Solution

So, what's the solution? Simple. Just set the following parameter in listener.ora:

diag_adr_enabled_listener = off

This will disable the ADR style logging for the listener. Now, suppose you want to set the directory to /tmp and log file name to listener_0405.log, add the following into listener.ora (assuming the name of the listener is "listener"; otherwise make the necessary change below):

log_file_listener = listener_0405.log
log_directory_listener = /tmp

That's it. the ADR style logging will be permanently be gone and you will be reunited with your highly missed pre-11g style logging. You can confirm it:

LSNRCTL> status
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     listener
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                26-NOV-2012 16:50:58
Uptime                    129 days 15 hr. 33 min. 31 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/11.2.0/grid/network/admin/listener.ora
Listener Log File         /tmp/listener_0405.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oradba2)(PORT=1521)))

Services Summary...

Service "+ASM" has 1 instance(s).

... output truncated ...

Happy logging.

P.S. By the way, you can also change the values by issuing set commands from LSNRCTL command prompt:

LSNRCTL> set log_file '/tmp'

However, if you have heeded my advice earlier, you might have set admin_restrictions to ON; so can't use the set command. Instead, you would put the value in listener.ora and reload the listener for the desired effect.

My Sessions in RMOUG 2013

2013-02-15T20:30:00.002-05:00

Many thanks for attending my sessions (I had a whopping four of them!) ar Rocky Mountain Oracle User Group Training Days 2013. I was pleasantly surprised to find some attending all four. Thank you. You all made my day.

Here you can download the slides and the demo scripts I used. Sorry about the delay. I didn't get a chance to post these earlier.

Exadata for Oracle DBAs
RAC for Beginners
Beginning Performance Tuning
Stats with Confidence
Stats with Intelligence

As always, your feedback will be greatly appreciated.

Boston DBA SIG Feb 6, 2013 Meeting Materials

2013-02-07T12:02:00.000-05:00

Thank you all for coming to my session - Exadata from Beginner to Advanced in 3 Hours - on an evening in the dead of winter and staying for 4 hours. It was an amazing experience for me to see the sheer volume of interaction, which can only mean the depth of engagement of the attendees - a dream for any speaker. Thank you.

Here is the deck for the session which contains the scripts I used in the demo. Hope you enjoy them. As always, I will appreciate any feedback - good, ugly and anything in between.

Congratulation to those who won the upcoming book from Apress: Expert Exadata Recipes by John Clarke. It's the latest, and arguably the best book on Exadata so far. No; I am not the author and I have no incentive to promote this book in any way. But I have had the privilege to be the technical reviewer and I have first hand account of the quality of the book.

Thanksgiving Thoughts

2012-11-22T20:28:00.000-05:00

Today is Thanksgiving Day in the US. For those who are not familiar with American holidays and traditions, Thanksgiving is a really tribute to cooperation and camaraderie. There are many theories and folklore surrounding the concept; but I think one trumps over the rest. More than four scores ago when the pilgrims from England set foot in North America and decided to call it their home, they didn't encounter a Welcome to America sign at JFK airport. Harsh New England weather was just one of the many sobering reminders of the fate of things to come for the visitors. The pilgrims didn't have a Walmart and a credit card to swipe for potatoes and cereal; they had to grow their own food. Well, they did that in their own country; but they had no idea how to do that in this strange land.

Fortunately for them, they got help from the Native Americans who taught them farming in this unfamiliar terrain. Had it not been for those helpful locals, the pilgrims would have perished in the first winter cold and perhaps there would not have been a United States of America later. To show their appreciation, the newly minted “Americans” (of course, a term yet to be used) organized a feast for their comrades and called it a symbol of giving their thanks. It would take hundred and fifty more years before George Washington, the first president of the new nation called USA formally declare the Thanksgiving Holidays as a firmly footed American tradition; but the spirit of the Thanksgiving has been in American hearts since early 17th century. This is time of the year we explicitly give thanks to all those who we benefited from, derive our joys from and to those who define who and what we are today.

I was not born in this country. I came when I was 22 - young and stupid; with a sense of adventure and trepidation at the same time - in some ways like the pilgrims. I was yet another immigrant into the melting pot called the USA; but there is a not a day goes by I think about the wonderful people who helped me through, made me feel at ease - taking me to grocery shopping to ballgames, regaling me with stories of hunting and trekking, and dropping down in the foot-deep snow to change the flat tire of my car. All little to big acts - but all hallmarks of this great land and the people who live in it. I am thankful to all those who have have held my hand in the darkest of the times and sweetest of the moments - and made me who I am today; are doing it and I am sure will continue to do it for the rest of my life.

Happy Thanksgiving!

Presentations at Philadelphia Area Oracle User Group 2012

2012-11-16T23:43:00.001-05:00

Thank you all those who came to my sessions at PHLOUG in November. You can download the presentations and the demo scripts I used here.

OOW12: Beginning Performance Tuning

2012-09-30T13:58:00.000-04:00

Thank you very much for coming to my session "Beginning Performance Tuning" on the #IOUG track at #OOW12 Oracle Open World 2012. It makes the day for any speaker to see the room filled to capacity even at 9 AM on a Sunday morning. Much, much appreciated.

Here are the slides and the scripts I used in the demos. Please feel free to reuse the slides and scripts for for any purpose. All I ask is to give due credit to me. I imply no warranty and support for the materials. Use your discretion while using.

Quiz: Mystery of Create Table Statement

2012-06-08T00:00:00.000-04:00

Happy Friday! I thought I would jumpstart your creative juices with this little, really simple quiz. While it's trivial, it may not be that obvious to many. See if you can catch it. Time yourself exactly 1 minute to get the answer. Tweet answer to me @arupnanda

Here it goes. Database is 11.2.0.3. Tool is SQL*Plus.

The user ARUP owns a procedure that accepts an input string and executes it. Here is the procedure.

create or replace procedure manipulate_arup_schema
(
        p_input_string  varchar2
)
is
begin
        execute immediate p_input_string;
end;
/

The user ARUP have granted EXECUTE privileges on this to user SCOTT. The idea is simple: SCOTT can create and drop tables and other objects in ARUP's schema without requiring the dangerous create any table system privilege.

With this, SCOTT tries to create a table in the ARUP schema:

SQL> exec arup.manipulate_arup_schema ('create table abc (col1 number)')

PL/SQL procedure successfully completed.

The table creation was successul. Now SCOTT tries to create the table in a slightly different manner:

SQL> exec arup.manipulate_arup_schema ('create table abc1 as select * from dual');

It fails with an error:

BEGIN bus_schema.manipulate_bus_schema ('create table abc as select * from dual'); END;

*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'
ORA-06512: at "ARUP.MANIPULATE_ARUP_SCHEMA", line 18
ORA-06512: at line 1

Huh? After checking you did confirm that the user indeed doesn't have the quota on tablespace USERS, so the error is genuine; but how did the first table creation command go through successfully?

Tweet me the answer @arupnanda. Aren't on Twitter? Just post the answer here as a comment. I will post the answer right here in the evening. Let's see who posts the first answer. It shouldn't take more than 5 minutes to get the answer.

Have fun.

Update at the end of the Day. Here is the answer:

Oracle 11g R2 introduced a new feature called deferred segment creation. Segments are stored data objects such as tables, views and materialized views. Prior to Oracle 11gR2, when you created a table, a segment was automatically created. The segment was empty; but created it was. From 11gR2, the table is created only in data dictionary, if there is no data. In the second case, the create table statement used create table as select format, which pulled the data from dual to create the table. However the user didn't have quota on tablespace users; so the statement failed. In the first case, the create table statement merely created the table in dictionary; not the segment. Since there was no segment, there was no space consumption; so the unavailability of quota in the tablespace didn't matter and the statement was successful.

It was a simple puzzle; but I have seen many DBAs, even seasoned ones, stumble over. Eventually they get it; but, well..., they should have taken just a few minutes. From all the responses I got - on twitter and this blog - Yasin Baskan (@yasinbaskan) was the first one to get back with correct answer. Several others did eventually; but Yasin takes the honor of being the first one.

Congratulations, Yasin and thank you all who twitted and posted comments here.

What to Learn from LinkedIn Password Hack as an Oracle DBA

2012-06-07T01:34:00.000-04:00

One of the major news today was the hacking and resultant publishing of passwords in LinkedIn. Didn't hear about it? Well, read it here. In summary, someone smart but with head screwed a little askew decided to pull passwords from LinkedIn account using a little known flaw in the LinkedIn iOS app. LinkedIn later confirmed that leak and asked users to change the password. This created a major ripple effect all over the world. The news competed for attention with others such as Spain's economic reforms; but in the end it managed to rise to the top since many professionals and executives are members of the LinkedIn site and were affected.

Well, what is that to do with being an Oracle DBA - you may ask. Fair question. You see, there is a very important lesson to be learned here from this incident - a lesson commonly ignored by many DBAs, developers, architects and pretty much all users of an Oracle database. Let's see what that is.

Mechanics of the LinkedIn Password Hack

Adversaries (a.k.a. hackers) obtain password in many ways. Some use brute force approach of guessing the password and trying to login until they succeed. However, many systems employ a simple mechanism of locking out the user when more than a threshold number of incorrect attempts are made - not very effective. There is another type of attack, where the adversary simply gets the password stored in the servers of the site. But, wait, shouldn't that password be encrypted?

Yes, they generally are. But here is where a twist comes. The passwords may not be really "encrypted"; but merely "hashed". There is a huge difference. Encryption generally requires a key that is used to encrypt a value; hashing does not. Hashing sort of transforms the value but not in a predictable way; so you can't reverse the hasing process to get the source value. Let's see how it works.

Hashing

Here is a simple example. Suppose you are negotiating a rate for your baby sitter and you agreed on an amount - $123. Now you asked the sitter to tell your spouse that amount. Well, how do you make sure she would mention that very amount? After all, she has incentive to say a higher amount, doesn't see? She could say that you agreed on $125 or even $150; your spouse will not be able to ascertain that. (Imagine for a moment that you don't have access to normal modern technology like a cellphone, etc. for you to communicate directly with your spouse). So you develop a simple strategy - you come up with a formula that creates a number from the amount. It could be as simple as, say, the total of all digits. So your amount - $123 becomes:

1 + 2 + 3 = 6

You write that down on a paper, seal it in an envelope and ask the sitter to give it to your spouse in addition to mentioned the agreed upon amount. You and your spouse both know this formula; but the sitter doesn't. Suppose she fudges the amount you agreed on to make it to, say $125. Upon her telling your spouse computes the magic number:

1 + 2 + 5 = 8

Your spouse will compare this with the number inside the sealed envelope and immediately come to the conclusion that the amount agreed by you was something different; not $125. The authenticity of the value is now definitively established to be false.

This process is called hashing and this magic number is called a hash value. Of course the hashing process is much more complex than merely adding the digits. I just wanted to show the concept with a very simple example. The mechanics of the process, which was simply ading up the digits, is known as the hashing algorithm.

Here are some properties of this hashing process:

(1) The process is one-way. You can determine the hashvalue by adding the digits (1+2+3); but you can't determine the source number from the hashvalue (6). You spouse can't determine from the hashvalue mentioned by the sitter what amount you agreed on. So, it's not the same as encryption, which allows you to decrypt and come up with the source number.

(2) The purpose is not to store values. It's merely to establish the authenticity. In this example, your spouse determines that the amount mentioned by the baby sitter ($125) must be wrong, because its hashvalue would have been 8, not 6. After that authenticity is established (or rejected, as in this case) the purpose of the hashvalue cease to exist.

(3) The hashing function is deterministic, i.e. it will always come up with the same value everytime it is invoked against the same source value.

(4) What if the baby sitter had mentioned $150? The hashvalue, in that case, would have been 1+5+0 = 6, exactly the hashvalue computed by you. In that case, your spouse would have determined the value $150 to be authentic, which would have been wrong. So, it's important that the hashvalue is somewhat unique, to reduce possibility of two different numbers producing the same result. This is known as "collision" of hashvalues.

The algorithm is the key to make sure the possibilty of collisions is reduced. There are several algortihms in use. Two very common ones are MD5 (message digest) and SHA-1 (secure hash algorithm).

Since the source value can't be computed back from hash value, this is considered by some as a more secure process than encryption. This process is useful in situations where the reverse computation of values is not necessary; merely the matching of hashvalues is needed. One such example is passwords. If you want to establish that the password entered by the user matches the stored password, all you have to do is generate the hashvalue and match that with the hashvalue stored in the database. If they match, you establish that the password is correct; if not then, well, it's not. This has an inherent security advantage. If someone somehow manages to read the passwords, all that will be exposed will be the hashvalues of the passwords; not the actual values of the passwords themselves. As we saw earlier, it will be impossible to decipher the original password from the hashvalue. That's why it is common in password storage.

Salt

So, that's great, with some higher degree of security for password store. What's the problem?

The problem is that the hashvalues are way too predictable. Recall from the previous section that the hashvalue of a specific input value is always the same value. Considering the simpel hashfunction (adding digits), the input value $123 will always return 6 as the hashvalue. Consider this: an adversary can see the hashvalue and guess the value, as shown below.

Is the input value $120? The hash value is 1+2+0 = 3, which does not match "6", so it must not be the correct number.

Is it $121? Hash value of 121 is 1+2+1=4, different from 6; so this is not correct either.

Is it $122? Hashvalue of 122 is 5; so not correct.

Is it $123? Hashvalue is 6. Bingo! The adversary now knows the input value.

In just 4 attempts the adversary figured out the input value from the hashvalue. Consider this scenario for passwords. The adversary can see the password hash (from which he can't decipher the password); but he can generate hashes from multiple nmput strings and check which one matches the stored password hashvalue. Using the computing power of modern computers this turns almost trivial. So a hash value is not inherently secure.

What is the solution, then? What if the hash value was not as predictable? If the hash value generated from an input value were different, it would have been impossible to match it against some stored value. This element of randomness to an otherwise deterministic function is briught by introducing a modifier to the process, called a "salt". Like its real-life namesake, salt adds spice to the hashvalue to give it a unique "flavor", or a different vlaue. Here is an example where we are storing the password value "Secret":

hash("Secret") = "X"

hash("Secret") + salt = "Y"

hash("Secret") + salt = "Z"

Everytime the salt is added, a different value is produced. It will not allow the matching of passwords.

In case of LinkedIn, the passwords were stored without salt. Therefore it was easy for the adversary to guess the passwords by creating SHA-1 hash values from known words and comparing against the stored value. Here is a rough pseudo-code:

for w in ( ... list of words ... ) loop
   l_hash := hash(w);
   if l_hash != stored_value
       continue;
   else
       show "Bingo! The password is '||w
   end if;

Lesson for Oracle DBAs

In Oracle database (as of 11g R2 and all prior versions), the passwords are stored in the database in a table called USER$. There is a column called PASSWORD which stores the SHA-1 hashvalue. Using the algorithm mentioned above, an adversary can pass a very long listof words, perhaps the entire Oxford English Dictionary and crack open the password. You may argue that this process is cumbersome and time consuming. Actually, it's quite trivial for a resonably fast computer.

In Oracle, the passwords are not hashed alone. The userid is combined with the password to produce the hash, e.g. suppose SCOTT's password is TIGER. The hash function is applied as:

hash('SCOTTTIGER')

Let's see how Oracle stores the password with an example. Take a look at the password column in the view DBA_USERS:

select username, password
from dba_users
where username = 'SCOTT';

USERNAME PASSWORD
-------- ----------------
SCOTT    F894844C34402B67

The password is hashed and thus undecipherable, but we know that SCOTT’'s password is "tiger." Therefore, the hash value for "tiger" when userid is "SCOTT" is F894844C34402B67. Now, if SCOTT’'s password changes, this hash value also changes. You can then confirm in the view DBA_USERS to see if SCOTT’s password matches this hash value, which will verify the password as "tiger".

So how can an adversary use this information? It's simple. If he creates the user SCOTT with the password TIGER, he will come to know the hash values of stored in the password column. Then he can build a table of such accounts and the hashed values of the passwords and compare them against the password hashes stored in the data dictionary. What's worse: he can create this user in any Oracle database; not necessarly the one he is attacking right now.

This is why you must never use default passwords and easily guessed passwords.

Protection

Now that you know how the adversaries use the password hash to guess passwords. you should identify all such users and expire them, or force them to change passwords. How can you get a list of such users?

In Oracle Database 11g, this is easy, almost to the point of being trivial. The database has a special view, dba_users_with_defpwd, that lists the usernames with the default passwords. Here is an example usage:

select * from dba_users_with_defpwd;

USERNAME
------------------------------
DIP
MDSYS
XS$NULL
SPATIAL_WFS_ADMIN_USR
CTXSYS
OLAPSYS
OUTLN
OWBSYS
SPATIAL_CSW_ADMIN_USR
EXFSYS
ORACLE_OCM
… output truncated …

The output clearly shows the usernames that have the default password. You can join this view with DBA_USERS to check on the status of the users:

select d.username, account_status
from dba_users_with_defpwd d, dba_users u
where u.username = d.username;

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
PM                             EXPIRED & LOCKED
OLAPSYS                        EXPIRED & LOCKED
BI                             EXPIRED & LOCKED
SI_INFORMTN_SCHEMA             EXPIRED & LOCKED
OWBSYS                         EXPIRED & LOCKED
XS$NULL                        EXPIRED & LOCKED
ORDPLUGINS                     EXPIRED & LOCKED
APPQOSSYS                      EXPIRED & LOCKED
… output truncated …

Oracle 10g

What if you don't have Oracle 11g?

In January 2006, Oracle made a downloadable utility available for identifying default passwords and their users. This utility, available via a patch 4926128 is available on My Oracle Support as described in the document ID 361482/1. As of this writing, the utility checks a handful of default accounts in a manner similar to that described above; by the time you read this, however, its functionality may well have expanded.

Security expert Pete Finnigan has done an excellent job of collecting all such default accounts created during various Oracle and third-party installations, which he has exposed for public use in his website, petefinnigan.com. Rather than reinventing the wheel, we will use Pete's work and thank him profusely. I have changed his original approach a little bit, though.

First, create the table to store the default accounts and default password:.

CREATE TABLE osp_accounts (
product VARCHAR2(30),
security_level NUMBER(1),
username VARCHAR2(30),
password VARCHAR2(30),
hash_value VARCHAR2(30),
commentary VARCHAR2(200)
);

Then you can load the table using data collected by Pete Finnigan from many sources. (Download the script script here.) After the table is loaded, you are ready to search for default passwords. I use a very simple SQL statement to find out the users:

col password format a20
col account_status format a20
col username format a15
select o.username, o.password, d.account_status
from dba_users d, osp_accounts o
where o.hash_value = d.password
/

USERNAME        PASSWORD             ACCOUNT_STATUS
--------------- -------------------- --------------------
CTXSYS          CHANGE_ON_INSTALL    OPEN
OLAPSYS         MANAGER              OPEN
DIP             DIP                  EXPIRED & LOCKED
DMSYS           DMSYS                OPEN
EXFSYS          EXFSYS               EXPIRED & LOCKED
SYSTEM          ORACLE               OPEN
WMSYS           WMSYS                EXPIRED & LOCKED
XDB             CHANGE_ON_INSTALL    EXPIRED & LOCKED
OUTLN           OUTLN                OPEN
SCOTT           TIGER                OPEN
SYS             ORACLE               OPEN

Here you can see some of the most vulnerable of situations, especially the last line, which where the username says is SYS and the password is "ORACLE" (as is that of SYSTEM)!! It may not be "change_on_install,", but it's just as predictable.

Action Items

Now that you know how one adversary used the salt-less hashing algorithm to guess passwords, you have some specific actions to take.

(1) Advocate the use of non-dictionary words. Remember, the adversary can create passwords and compare the resultant hash against the stored hash to see if they match. Making it impossible for him to guess the list of such input values makes it impossible to generate has values.
(2) Immediately check in the database for users with default passwords. Either change the passwords, or Expire and Lock them.
(3) Whenever you use hashing (and not encryption), use salt, to make sure it is diffcult, if not impossible for the adversary to guess.

Collaborate 2012 Sessions and Select Article

2012-04-25T21:48:00.003-04:00

Thank you all who came to my sessions at #IOUG Collaborate 2012 #C12LV on April 22-24 in Las Vegas. I had four full sessions, two panels and one bootcamp. Quite a busy schedule, as you can see. I also worked on some urgent performance issues at work during the week.

You can download the the slides and scripts here. They are available from the IOUG site but I thought I would put them for download here as well.

Rise of the Machines slides
Secure Your Database in a Single Day slides scripts
Beginning Oracle Performance Tuning slides scripts
How Oracle Locking Works slides

My article Cache Fusion Demystified on SELECT Journal won the Editor's Choice award for 2011. This article is available for free download here.

I hope you like them. As always, feedback - good, bad and something in between - will be appreciated.

Update as of April 29th, 2012

I usually add more stuff to my slides after the session is over. This may result from a direct attendee feedback that some content was not clear, inadequate, misleading or even incorrect. This time is no exception. I added some more content to make the concepts clearer. Also many thanks to Charles Hooper for pointing out an Oracle documentation bug which makes two scripts in my session incorrect. I have since corrected the slides and re-uploaded. Please redownload the slides for How Oracle Locking Works and specifically look at the slides 14, 18, 24, 25 and 26. Also please download the scripts for Beginning Oracle Performance Tuning. More specifically, the script lock1.sql has changed.

The Arup Nanda Blog: AIOUG Webcast:Methodical Performance Tuning Part 2

2012-02-24T08:42:00.000-05:00

The Arup Nanda Blog: AIOUG Webcast:Methodical Performance Tuning Part 2

AIOUG Webcast:Methodical Performance Tuning Part 2

2012-02-24T08:41:00.000-05:00

Thank you all for attending the Part 2 of the Methodical Performance Tuning series. I hope you got something out of the 1 hour long session. You can download the slides and the scripts I used during the demo here.

As always, I will appreciate any feedback which helps me in designing future content.

Revived Boston Area DBA SIG Meeting

2011-11-20T18:15:00.001-05:00

The DBA SIG of the Northeast Oracle User Group has been revived (thank you, Lyson and Jeane) and I was honored to be the speaker of the first session of what I hope will be a long list of very successful like the old days.

I started at 7 PM and finished at midnight - a solid 5 hours later! Thank you for your patience. It just made my day to have you in the audience that late. I hope you found it useful.

Here is the slide deck and the scripts I used during my session. As in the past, I cherish the moments and will highly appreciate to have your feedback.

Revived NOUG DBA SIG Events

2011-11-10T15:41:00.001-05:00

Congratulations to North East Oracle User Group in the Boston area who has restarted the DBA SIG. This was a highly successful program that used to be held after work hours on a weekday in the Oracle building in Burlington. I was privileged to have presented there from 2004 until its sad demise in 2009. Now, I am honored to be invited to be the first speaker in the revived program. I am presenting the session "Addressing Performance Issues during Change with Real Application Testing, Intelligent Stats and SQL Plan Baselines with Live Demos". More information here.

When: Nov 16th 6:30 PM to 9:00 PM
Where: Doubletree by Hilton at 5400 Computer Drive, Westborough, MA 01581
Food and drinks will be served.
The event is free to all NOUG members.

I will give away several Oracle books at the event for the best questions, etc.

If you plan on attending this, the organizers respectfully request that you RSVP to treasurer@noug.com immediately. They need some reasonably accurate headcount to order food and drinks, which is yet another reason to attend.

Abstract: Change is inevitable - be it applying a patchset or creating an index. In this session you will learn how to harness the power of three major features of Oracle database to improve the performance during any types of change, or at any other time. You will learn, with plenty of demos, how to configure and use Database Replay and SQL Performance Analyzer to predict performance, use extended statistics to make the optimizer more intelligent and use SQL Plan Baselines to make the performance consistent but open to further improvements.

As it has been the norm, I plan to explain these concepts and techniques with lots of live demos. If you are in the Boston area, I sincerely hope to see you all there. I have nothing but pleasant memories every one of the 5 times I have presented in that venue and expect nothing less this time.

AIOUG Webcast: Methodical Performance Tuning

2011-10-18T01:00:00.000-04:00

A big thank you to all those you attended my session today. I sincerely hope you got something out of it. Here are the scripts I used in the demo. And, here is the slide deck, if you are interested.

Remember, this was just the beginner's session. We will have intermediate and advanced ones in near future. Stay tuned through the AIOUG site.

Migration to Exadata Session at #OOW11

2011-10-06T20:40:00.001-04:00

Considering it was the last session of #OOW11 I was surprised to see a sizable number of folks showing up for my 3rd and final session slated for 3 to 4 PM on Thursday. Thank you for attending and for your questions.

Here is the slide deck. Note: please do not click on the link. Instead, right click on it, save the file and open it. It's a Powerpoint show; not a PPT. You can download free Powerpoint player to watch it, if you don't have Powerpoint installed.

Rise of the Machines

2011-10-05T23:03:00.001-04:00

This is the penultimate day of #OOW11 and I am here at the hotel lobby trying to put some order around the myriads of nuggets of information I have had over the last several days.

The announcements this year have been centered around introduction of various new products from Oracle - Oracle Database Cloud, Cloud Control, Database Appliance, Big Data Appliance, Exalytics, T4 Super cluster and so on. One interesting pattern that emerges from the announcements that is different from all the previous years is the introduction of several engineered and assembled systems that perform some type of task - specialized or generic. In the past Oracle announced machines too; but not so many at the same time, leading to an observation by April Sims (Executive Editor, Select Journal) that this year can be summed up in one phrase - Rise of the Machines.

But many of the folks I met in person or online were struggling to put their head around the whole lineup. It's quite clear that they were very unclear (no pun intended) how these are different and what situation each one would fit in. It's perfectly normal to be little confused about the sweet spots of each product considering the glut of information on them and seemingly overlapping functionalities. In the Select Journal Editorial Board meeting we had earlier this morning, I committed to writing about the differences between the different systems announced at #OOW11 and their usages in Select Journal 2012 Q1 edition. I didn't realize at that time what a tall order that is. I need to reach out to several product managers and executives inside Oracle to understand the functionality differences in these machines. Well, now that I have firmly put my feet in mouth, I will have to do just that. [Update on 4/29/2012: I have done that. Please see below]

In the demogrounds I learned about Oracle Data Loader for Hadoop and Enterprise-R, two exciting technologies that will change the way we collect and analyze large data sets, especially unstructured ones. Another new technology, centered around Cloud Control, was the Data Subsetting. It allows you to pull a subset of data from the source system to create test data, mask it if necessary and even find sensitive data based on some format. The tool was due for quite some time.

Again, I really need to collect my thoughts and sort through all that information overload I was subjected to at OOW. This was the best OOW ever.

Update on April 29th, 2011

I knew I had to wrap my head around these announcements and sort through the features available in the engineered machines. And I did exactly that. I presented a paper in the same name - Rise of the Machines - in Collaborate 2012, the annual conference of the Independent Oracle Users Group. Here is the presentation. In that session I explained the various features of 6 machines - Oracle Database Appliance, Exadata, Exalogic, Sparc Super Cluster, Exalytics and Big Data Appliance, the differences between them and where each one should be used. Please download the session if you want to know more about the topic.

Unicode Migration Assistant for Oracle

2011-10-04T03:03:00.001-04:00

When you want to convert a database created in the default characterset to a multibyte characterset, there were two basic approaches - the safe export/import and the not-for-the-faint-of-the-heart alter database convert internal. In either case you had to follow a string of activities - checking the presence of incompatible values by running csscan, etc.

There is a new tool from Oracle to make the process infinitesimally simpler - Migration Assistant for Unicode. It's a GUI tool that you can install on the client. A server side API (installed via a patch) does all the heavy lifting with the client GUI providing a great intuitive interface. You have the steps pretty much laid out for you. But the main strength of the tool is not that. There are two primary differentiators for the tool.

When you do have a bad character, what can you really do? You can truncate the part of the data. But how do you know how much to truncate? If you truncate aggressively, you may shave off a chunk and lose valuable data; but be miserly and you risk having the bad data in place. This tool will show the data in a separate window allowing you to correct only the affected data; nothing less, nothing more.
When users copy and paste data from some unicode compliant system to Oracle, e.g. from MS Word to a VARCHAR2 field in the database, the characters may look garbled; but given proper characterset they become meaningful. This tool allows you to see the data in many charactersets to identify which one was used to create it in the first place. After that it's a simple matter to reproduce that characters in the proper characterset.

With these two differentiators in place, the tool has great future. Check out everything on this tool at http://www.oracle.com/technetwork/database/globalization/dmu/overview/index-330958.html or just visit the booth at #OOW Demogrounds in Moscone South.

Oh, did I mention that the tool is free?

OOW11 Session #2 Exadata Management

2011-10-04T02:23:00.001-04:00

Thank you all for attending my second session in #OOW11 - Exadata Management. You can download the slide deck here. Important: DO NOT CLICK on this link; instead, right click on this link, save the file and then open it.

Here is the slide deck.

Here are the resources I referred to in the presentation. Please note: URLs could change without my knowledge.

•My Papers

–5-part Linux Commands article series http://bit.ly/k4mKQS

–4-part Exadata Command Reference article series http://bit.ly/lljFl0

•OTN Page on Exadata

–http://www.oracle.com/technetwork/database/exadata/index.html

•Tutorials

–http://www.oracle.com/technetwork/tutorials/index.html

•OTN Exadata Forum

–https://forums.oracle.com/forums/forum.jspa?forumID=829

Thanks for attending. As always, your feedback will be highly appreciated.

OOW11 Presentation: Exadata for Oracle DBAs

2011-10-02T22:06:00.000-04:00

Thank you very much for attending my session "Exadata for Oracle DBAs" at #OOW11 (Oracle Open World), 2011. Here are the links to I mentioned in the presentation:

5-part Linux Commands article series http://bit.ly/k4mKQS

4-part Exadata Command Reference article series http://bit.ly/lljFl0

You can download the Powerpoint show here http://bit.ly/nH0rpK (please don't click. Right click and download it to watch)

Update: If you have downloaded the slides earlier, please redownload. I corrected a small inaccuracy - the compression is handled by compute nodes; decompression can be offloaded to the cells. Thanks to Greg Rahn for pointing it out.

Of course, please remember that your feedback - good or bad - is always valued.

Setting Up Oracle Connection Manager

2011-08-29T19:24:00.000-04:00

The Problem

It seems really simple. We have an Oracle database (on all nodes of a full rack Exadata, to be exact), which a lot of end-users connect to through apps designed in a rather adhoc and haphazard manner - on Excel spreadsheets, Access forms, TOAD reports and other assorted tools. We want to control the access from these machines and streamline them.

The database machine sits behind a firewall. To allow the adhoc tools accessing the database from the client machines mean we have to change the firewall rules. Had it been one or two clients, it would have been reasonable; but with 1000+ client machines, it becomes impractical. So I was asked to provide an alternative solution.

The Solution

This is not a unique problem; it's the same problem when the machines need to access resources that exist across firewalls. The easy solution is to punch a hole through the firewall to allow that access; but is not desirable for obvious security reasons. A better solution, often implemented, is to have a proxy server. The proxy sits between the two layers of access and can access the servers behind the firewall. Clients make the request to the proxy which it passes on to the server.

Such a proxy solves the problem; but we are looking for a simpler solution. Does one exist?

Yes, it does. The answer is Connection Manager from Oracle. Among its many functions, one stands out - it acts as a proxy between the different layers of access and passes through the request. It's not a separate product; but is an option in the Oracle Client software (not the database or grid infrastructure software). This option is not automatically installed. When installing the client software, choose "Custom" and explicitly select "Connection Manager" from the list.

The Architecture

Let's quickly go through the architecture of tool. Assume there are three hosts:

client1 - the machine where the client runs and wants to connect to the database
dbhost1 - the machine where the database instance runs
cmhost1 - the machine where the Connection Manager process runs

Here is a rough network diagram of the three machines

From client1 you can reach cmhost1 but not dbhost1:

C:\>ping dbhost1
Ping request could not find host dbhost1. Please check the name and try again.

Or, if the host is known but not reachable, you will notice a message like this:

C:\>ping dbhost1

Pinging dbhost1 [192.168.104.31] with 32 bytes of data:

Request timed out.

However, you can ping the dbhost1 from cmhost1 and cmhost1 from client1.

Normally the TNS entry at the client machine would have looked like this:

TNS_REG =

(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)(HOST = dbhost1)(PORT = 1521)
)
(CONNECT_DATA =
(SERVICE_NAME=srv1)
)
)

However, this will not work since the client does not even know the routing for dbhost1. Instead the client connects to the CM host. The connection manager has two processes

The Connection Manager Admin Process (cmadmin)
One or more Connection Manager Gateway Processes (cmgw)

The CMGW processes allows the client connections to come in through them. The admin process manages the gateways. We will cover more on that later.

After setting up the CM processes, you will need to rewrite the TNSNAMES.ORA in the following way:

TNS_CM =
(DESCRIPTION =
(SOURCE_ROUTE = YES)
(ADDRESS =
(PROTOCOL = TCP)(HOST = cmhost1)(PORT = 1950)
)
(ADDRESS =
(PROTOCOL = TCP)(HOST = dbhost1)(PORT = 1521)
)
(CONNECT_DATA =
(SERVICE_NAME=srv1)
)
)

How it Works

Note the special parameter:

SOURCE_ROUTE = YES

This tells the client connection request to attempt the first address listed first and only then attempt the next one. This is different from load balance setups where you would expect the client tool to pick one of the addresses at random. So the client attempts this address first:

(PROTOCOL = TCP)(HOST = cmhost1)(PORT = 1950)

This is the listener for Connection Manager. The clients are allowed to connect to the port 1950 (the port where CM listener listens on) on the host cmhost1.

After that the connection attempts the second address

(PROTOCOL = TCP)(HOST = dbhost1)(PORT = 1521)

However, it will fail since the client does not have access to the port 1521 of the host dbhost1. This is where CM comes in. The client does not make the request; CM does on behalf of the client connection that just came in. The connection manager (running on cmhost1) makes the request with that address. Since the host cmhost1 can access dbhost1 on port 1521, that connection request goes through successfully. When the response comes back from the database, CM passes it back to the original client.

A single CM connection can handle many client connection requests.

Setting Up

Now that you know how CM works, let's see how to enable it, step by step.

(1) Install CM, if you don't have already. Check for a file cmctl under $ORACLE_HOME/bin. If you have it, CM may have been installed already. If not, install CM by running the installer from Oracle Client (not Database or Grid Infra) software. Choose Custom Install and explicitly choose Connection Manager.

(2) Go to $OH/network/admin (remember the $OH of the client software home; not the database home)

(3) You need to create a configuration file called cman.ora. Instead of creating it from scratch, go to the samples subdirectory and copy the cman.ora sample file back into the admin directory.

(4) In the file cman.ora, make the changes to the following lines. Of course, I assumed cmhost1 as the server running Connection Manager process. Substitute by whatever name you choose for the CM server. I also assumed you would use port 1950 for the CM listener. It does not have to be. Whatever you choose will need to be opened in the firewall.

The lines you are changing will be at the beginning and the end of the cman.ora file.

cman_cmhost1 =

(configuration=

(address=(protocol=tcp)(host=cmhost1)(port=1950))

(parameter_list =

...

# conn_stats = connect_statistics

(rule_list=

(rule=

(src=*)(dst=*)(srv=*)(act=accept)

(action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))

)

Keep the remaining lines as is, for now. I will explain the meaning of these parameters later.

(5) Start the CM command line interface by executing "cmctl"

# cmctl

CMCTL for Linux: Version 11.2.0.1.0 - Production on 29-AUG-2011 15:16:01

Welcome to CMCTL, type "help" for information.

CMCTL>

This will show a prompt "CMCTL>". Here you will enter different admin commands for the CM processes, much like LSNRCTL command line interpreter.

(6) Start the administration process by typing "administer"

CMCTL> administer

Current instance CMAN_cmhost1 is not yet started

Connections refer to (address=(protocol=tcp)(host=cmhost1)(port=1950)).

The command completed successfully.

CMCTL:CMAN_cmhost1>

Note how the prompt changed showing the name of the CM.

(7) Start the connection manager processes by issuing "startup"

CMCTL> startup

Starting Oracle Connection Manager instance CMAN_cmhost1.proligence.com. Please wait...

TNS-04077: WARNING: No password set for the Oracle Connection Manager instance.

CMAN for Linux: Version 11.2.0.1.0 - Production

Status of the Instance

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

Instance name cman_cmhost1.proligence.com

Version CMAN for Linux: Version 11.2.0.1.0 - Production

Start date 29-AUG-2011 17:25:48

Uptime 0 days 0 hr. 0 min. 9 sec

Num of gateways started 2

Average Load level 0

Log Level OFF

Trace Level OFF

Instance Config file /opt/oracle/product/11gR2/client1/network/admin/cman.ora

Instance Log directory /opt/oracle/product/11gR2/client1/network/log

Instance Trace directory /opt/oracle/product/11gR2/client1/network/trace

The command completed successfully.

(8) If everything goes well, you should see the CM status

CMCTL:CMAN_cmhost1> show status

Status of the Instance

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

Instance name cman_cmhost1

Version CMAN for Linux: Version 11.2.0.1.0 - Production

Start date 30-JUN-2011 13:26:16

Uptime 60 days 2 hr. 15 min. 10 sec

Num of gateways started 2

Average Load level 1

Log Level ADMIN

Trace Level OFF

Instance Config file /opt/oracle/product/11gR2/client1/network/admin/cman.ora

Instance Log directory /opt/oracle/product/11gR2/client1/network/log

Instance Trace directory /opt/oracle/product/11gR2/client1/network/trace

If it does not start, refer to the troubleshooting section later in this blog.

(9) Make the TNSNAMES.ORA file change at the client as shown earlier.

(10) Make the connection using this new TNS connection alias:

C:\> sqlplus arup/arup@TNS_CM

You should be able to connect to the database server now. Note, you still can't access the database host directly. If you use the regular TNS connect string - TNS_REG - you will fail. This new connection was established through the connection manager.

(11) Check the number of connections coming through the CM, using CMCTL tool:

CMCTL:CMAN_cmhost1> show connections

Number of connections: 1.

The command completed successfully.

The output shows there is one connection through the CM listener. As you connect more, you will see the number next to "Number of connections:" increasing.

That's it. You have successfully configured Connection Manager interface.

Fine Tuning

In the previous setup I asked you to enter some values without really explaining the significance of them. Let's go through them.

One of the powerful features of the CM interface is to act as sort of a firewall, i.e. allow connections from/to certain hosts and for specific services. You can define these inside the RULES_LIST section as shown below:

(rule_list=

(rule=

(src=x)(dst=x)(srv=*)(act=accept)

(action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))

)

Here are the parameters and what they mean:

src = the source server where the connection request would come from. If you want to leave it unrestricted, use "*", as a wildcard.
dst = the destination server, which is probably the database server the request would go to. Again, unrestricted access would be given as "*".
srv = the service. enter "*" for all types of services.
act = the action, e.g. accept, reject or drop the request

On src and dst parameters you can give hostnames, IP addresses as well as wildcards. You would use this section to allow or deny the access between different servers, making it a really powerful firewall-like tool.

The action_list parameter allows you to fine tune the actions on the connection.

aut = whether the Oracle Advanced Security Option authentication filter should be applied. The value shown here is OFF, means this is not to be applied.
moct = after how long the outbound connection established should timeout. The value set here is 0, means the outbound connection is never to be timed out.
mct = after how long the session should disconnect. The value is 0, i.e. never.
mit = the timeout duration for idle connections
conn_stats = whether the connection statistics be maintained.

Note the use of parentheses. You can use different rules and actions for each combination of sources and destinations. It allows you to finetune the access. For instance, database D1 is highly secure and you would want ASO filter; but not database D2. For the request coming from the same client, you can have a different set of actions for each destination. For D1, the more secure database host, you can establish various timeouts.

CMCTL Primer

Now that you know about the CMAN.ORA file, let's see the activities you can perform in CMCTL. The first command you should explore should be "help".

CMCTL> help

The following operations are available

An asterisk (*) denotes a modifier or extended command:

administer close* exit quit

reload resume* save_passwd set*

show* shutdown sleep startup

suspend*

You can get help on a specific command as well:

CMCTL> help administer

administer [] [using ] - Sets up a context for administering the given Oracle Connection Manager instance

Two commands you will be using a lot are - SHOW and SET.

CMCTL> help SHOW

The following operations are available after show

An asterisk (*) denotes a modifier or extended command:

all connections defaults events

gateways parameters rules services

status version

CMCTL> help set

The following operations are available after set

An asterisk (*) denotes a modifier or extended command:

aso_authentication_filter connection_statistics

event idle_timeout

inbound_connect_timeout log_directory

log_level outbound_connect_timeout

password session_timeout

trace_directory trace_level

Most of these modifiers are self explanatory, e.g. show status will show you the status of CM; show connections will show connections established through CM, etc.

Troubleshooting

Of course things may not go well the first time. Don't despair. You can perform extensive diagnostics and enable logging and tracing.

The most common error may come during the startup:

TNS-04012: Unable to start Oracle Connection Manager instance.

Unfortunately it's a generic, catch-all error. The most common reason is an incorrectly constructed CMAN.ORA file, e.g. with non-identifiable hostnames and port numbers, log/trace directories that do not yet exist, invalid parameters and values or even mismatched parantheses. It's difficult to guess what caused the issue. The best option for you is to copy the sample CMAN.ORA file and replace the values with your own, paying special attention to the directory names.

Another cause of this error is not using Fully Qualified Names. For instance, you must use cmhost1.proligence.com instead of "cmhost1".

Sometimes the issues still linger. You can perform extensive diagnostics by using extended tracing and logging. To enable logging and tracing, you have to set the parameters in CMAN.ORA:

log_level : set to SUPPORT

trace_level : set to SUPPORT

The default for both is OFF, means no logging and tracing.

When enabled, CM emits tracings and logging in the appropriate directories that may lead to the source of the problem. Here is an excerpt from the log:

(LOG_RECORD=(TIMESTAMP=29-AUG-2011 16:22:26)(EVENT=CMAN.ORA contains no rule for local CMCTL connection)(Add (rule=(src=cmhost1)(dst=127.0.0.1)(srv=cmon)(act=accept)) in rule_list)

This shows clearly the issue. We have to make the appropriate entry to make CM work. You can also enable these dynamically

CMCTL:CMAN_cmhost1.proligence.com> set trace_level support

CMAN_cmhost1.proligence.com parameter trace_level set to support.

The command completed successfully.

Takeaways

Connection Manager is a great product form Oracle Network family of products that can, among many things, perform as a connection concentrator from multiple client requests, act as a rule based mini-firewall for the database requests and act as a proxy between different access domains. Here you learned how to set it up, fine tune the parameters and manage it effectively. Hope you liked it. As always, please provide your feedback.

Difference between Select Any Dictionary and Select_Catalog_Role

2011-07-24T23:27:00.000-04:00

When you want to give a user the privilege to select from data dictionary and dynamic performance views such as V$DATAFILE, you have two options:

grant select any dictionary to ;
grant select_catalog_role to ;

Did you ever wonder why there are two options for accomplishing the same objective? Is one of them redundant? Won't it make sense for Oracle to have just one privilege? And, most important, do these two privileges produce the same result?

The short answer to the last question is -- no; these two do not produce the same result. Since they are fundamentally different, there is a place of each of these. One is not a replacement for the other. In this blog I will explain the subtle but important differences between the two seemingly similar privileges and how to use them properly.

Create the Test Case

First let me demonstrate the effects by a small example. Create two users called SCR and SAD:

SQL> create user scr identified by scr;
SQL> create user sad identified by sad;

Grant the necessary privileges to these users, taking care to grant a different one to each user.

SQL> grant create session, select any dictionary to sad;

Grant succeeded.

SQL> grant create session, select_catalog_role to scr;

Grant succeeded.

Let's test to make sure these privileges work as expected:

SQL> connect sad/sad

Connected.

SQL> select * from v$session;

... a bunch of rows come here ...

SQL> connect scr/scr

Connected.

SQL> select * from v$datafile;

... a bunch of rows come here ...

Both users have the privilege to select from the dictionary views as we expected. So, what is the difference between these two privileges? To understand that, let's create a procedure on the dictionary tables/views on each schema. Since we will create the same procedure twice, let's first create a script which we will call p.sql. Here is the script:

create or replace procedure p as
l_num number;
begin
select count(1)
into l_num
from v$session;
end;
/

The procedure is very simple; it merely counts the number of connected sessions by querying V$SESSION. When you connect as SAD and create the procedure by executing p.sql:

SQL> @p.sql

Procedure created.

The procedure was created properly; but when you connect as SCR and execute the script:

SQL> @p.sql

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE P:

LINE/COL ERROR
-------- ------------------------------------------------
4/2 PL/SQL: SQL Statement ignored
6/7 PL/SQL: ORA-00942: table or view does not exist

That must be perplexing. We just saw that the user has the privilege to select from the V$SESSION view. You can double check that by selecting from the view one more time. So, why did it report ORA-942: table does not exist?

Not All Privileges have been Created Equal

The answer lies in the way Oracle performs compilations. To compile a code with a named object, the user must have been granted privileges by direct grants; not through the roles. Selecting or performing DML statements do not care how the privileges were received. The SQL will work as long as the privileges are there. The privilege SELECT ANY DICTIONARY is a system privilege, similar to create session or unlimited tablespace. This is why the user SAD, which had the system privilege, could successfully compile the procedure P.

The user SCR had the role SELECT_CATALOG_ROLE, which allowed it to SELECT from V$SESSION but not to create the procedure. Remember, to create another object on the base object, the user must have the direct grant on the base object; not through a role. Since SCR had the role not the direct grant on V$DATAFILE, it can't compile the procedure.

So while both the privileges allow the users to select from v$datafile, the role does not allow the users to create objects; the system privilege does.

Why the Role?

Now that you know how the privileges are different, you maybe wondering why the role is even there. It seems that the system grant can do everything and there is no need for a role. Not quite.The role has a very different purpose. Roles provide privileges; but only when they are enabled. To see what roles are enabled in a session, use this query:

SQL> connect scr/oracle
Connected.
SQL> select * from session_roles
2 /

ROLE
------------------------------
SELECT_CATALOG_ROLE
HS_ADMIN_SELECT_ROLE

2 rows selected.

We see that two roles - SELECT_CATALOG_ROLE and HS_ADMIN_SELECT_ROLE - have been enabled in the session. The first one was granted to the user. The other one is granted to the first one; so that was also enabled.

Just because a role was granted to the user does not necessarily mean that the role would be enabled. The roles which are marked DEFAULT by the user will be enabled; the others will not be. Let's see that with an example. As SYS user, execute the following:

SQL> alter user scr default role none;

User altered.

Now connect as SCR user and see which roles have been enabled:

SQL> connect scr/oracle

SQL> select * from session_roles;

no rows selected

None of the roles have been enabled. Why? That's because none of the roles are default for the user (effected by the alter user statement by SYS). At this point when you select from a dynamic performance view:

SQL> select * from v$datafile;

select * from v$datafile

ERROR at line 1:

ORA-00942: table or view does not exist

You will get this error because the role is not enabled, or active. Without the role the user does not have any privilege to select from the data dictionary or dynamic performance view. To enable the role, the user has to execute the SET ROLE command:

SQL> set role SELECT_CATALOG_ROLE;

Role set.

Checking the enabled roles:

SQL> select * from session_roles;

ROLE

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

SELECT_CATALOG_ROLE

HS_ADMIN_SELECT_ROLE

2 rows selected.

Now the roles have been enabled. Since the roles are not default, the user must explicitly enable them using the SET ROLE command. This is a very important characteristic of the roles. We can control how the user will get the privilege. Merely granting a role to a user will not enable the role; the user's action is required and that can be done programmatically. In security conscious environments, you may want to take advantage of that property. A user does not always have the to have to privilege; but when needed it will be able to do so.

The SET ROLE command is an SQL*Plus command. To call it from SQL, use this:

begin

dbms_session.set_role ('SELECT_CATALOG_ROLE');

end;

You can also set a password for the role. So it will be set only when the correct password is given;

SQL> alter role SELECT_CATALOG_ROLE identified by l
2 /

Role altered.

To set the role, you have to give the correct password:

SQL> set role SELECT_CATALOG_ROLE identified by l;

Role set.

If you give the wrong password:

SQL> set role SELECT_CATALOG_ROLE identified by fl

2 /

set role SELECT_CATALOG_ROLE identified by fl

ERROR at line 1:

ORA-01979: missing or invalid password for role 'SELECT_CATALOG_ROLE'

You can also revoke the execute privilege on dbms_session from public. After that the user will not be able to use it to set the role. You can construct another wrapper procedure to call it. Inside the wrapper, you can have all sort of checks and balances to make sure the call is acceptable.

We will close this discussion with a tip. How do you know which roles are default? Simply use the following query:

SQL> select GRANTED_ROLE, DEFAULT_ROLE
2 from dba_role_privs
3 where GRANTEE = 'SCR';

GRANTED_ROLE DEF
------------------------------ ---
SELECT_CATALOG_ROLE NO

Update

Thanks to Randolph Geist (http://www.blogger.com/profile/13463198440639982695) and Pavel Ruzicka (http://www.blogger.com/profile/04746480312675833301) for pointing out yet another important difference. SELECT ANY DICTIONARY allows select from all SYS owner tables such as TAB$, USER$, etc. This is not possible in the SELECT_CATALOG_ROLE. This difference may seem trivial; but is actually quite important in some cases. For instance, latest versions of Oracle do not show the password column from DBA_USERS; but the hashed password is visible in USER$ table. It's not possible to reverse engineer the password from the hash value; but it is possible to match it to a similar entry and guess the password. A user with the system privilege will be able to do that; but a user with the role will not be.

Conclusion

In this blog entry I started with a simple question - what is the difference between two seemingly similar privileges - SELECT ANY DICTIONARY and SELECT_CATALOG_ROLE. The former is a system privilege, which remains active throughout the sessions and allows the user to create stored objects on objects on which it has privileges as a result of the grant. The latter is not a system grant; it's a role which does not allow the grantee to build stored objects on the granted objects. The role can also be non-default which means the grantee must execute a set role or equivalent command to enable it. The role can also be password protected, if desired.

The core message you should get from this is that roles are different from privileges. Privileges allow you to build stored objects such as procedures on the objects on which the privilege is based. Roles do not.

Who Manages the Exadata Machine?

2011-07-24T18:05:00.000-04:00

For organizations that just procured an Exadata machine, one of the big questions is bound to be about the group supporting it. Who should it be - the DBAs, Sys Admins, Network Admins, or some blend of multiple teams?

The conventional Oracle database system is a combination of multiple distinct components - servers, managed by system admins; storage units, managed by SAN admins; network components such as switches and routers, managed by network admins; and, of course, the database itself, managed by the DBAs. Exadata has all those components - servers, storage (as cell servers), infiniband network, ethernet network, flash disks, the whole nine yards; but packaged inside a single physical frame representing a single logical unit - a typical engineered system. (For a description of the components inside the Exadata system, please see my 4-part article series on Oracle Technology Network) None of these conventional technology groups posses the skillsets to the manage all these components. That leads to a difficult but important decision - how the organization should assign the operational responsibilities.

Choices

There are two choices for organizations to assign administrative responsibilities.

Distributed - Have these individual groups manage the respective components, e.g. Sys Admins managing the Linux servers, the storage admins managing the storage cells, network admins managing the network components and finally DBAs managing the database and the cluster.
Consolidated - Create a specialized group - Database Machine Administrator (DMA) and have one of these groups expand the skillset to include the other non-familiar areas.

Each option has its own pros and cons. Let's examine them and see if we can get the right fit for our specific case.

Distributed Management

Under this model each component of Exadata is managed as an independent entity by a group traditionally used to manage that type of infrastructure. For instance, the system admins would manage the Linux OS, overseeing all aspects of it such as creation of users to applying the patches and RPMs. The storage and database would be managed likewise by the specialist teams.

The benefit of this solution is its seeming simplicity - components are managed by their respective specialists without a need for advanced training. The only need for training is for storage, where the Exadata Storage Server commands are new and specific to Exadata.

While this approach seems a nobrainer on surface, it may not be so in reality. Exadata is not just something patched up from these components; it is an engineered system. There is a huge meaning behind that qualifier. These components are not designed to act alone; they are put together to make the entire structure a better database machine. And, note the stress here - not an application server, not a fileserver, not a mail server; not a general purpose server - but a database machine alone. This means the individual components - the compute nodes, the storage servers, the disks, the flashdisk cards and more - are tuned to achieve that overriding objective. Any incremental tuning in any specific component has to be within the framework of the entire frame; otherwise it may fail to produce the desired result, or worse, produce undesirable result.

For instance the disks where the database resides are attached to the storage cell servers; not the database compute nodes. The cell servers, or Cells run Oracle Enterprise Linux, which is very similar to Red Hat Linux. Under this model of administration, the system admins are responsible for managing the operating system. A system admin looks at the host and determines that it is under tuned since the filesystem cache is very low. In a normal Linux system, that would have been a correct observation; but in Exadata, the database is in ASM and a filesystem cache is less important. On the other hand, the Cells need the memory to place the Storage Indexes on the disk contents. Placing a large filesystem cache not only produce nothing to help the filesystem; but actually hurt the performance for the paging of Storage Indexes.

This is just one example of how the engineered systems are closely interrelated. Assuming they are separate and assigning multiple groups with different skillsets may not work effectively.

Database Machine Administrator

This is leads to the other approach - making a single group responsible for the entire frame from storage to the database. The single group would be able to understand the impact of the changes in one component to the overall effectiveness of the rack and will be in a better position to plan and manage. The single role that performs the management of Exadata is known as Database Machine Administrator (DMA).

I can almost hear the questions firing off inside your brain. The most likely question probably is whether it is even possible to have a single skillset that encompasses storage, system, database and network.

Yes, it definitely is. Remember, the advantages of an engineered system do not stop at being a carefully coordinated individual components. Another advantage is the lack of controls in those components. There are less knobs to turn on each component in an Exadata system. Take for instance the Operating System. There are two types of servers - the compute nodes and the cells. In the cells, the activity performed by a system admin is severely limited - almost to the point of being none. On the compute nodes, the activities are limited as well. The only allowable activities are - setting up users, setting up email relays, possibly setting up an NFS mount and handful of more. This can easily be done by a non-expert. One does not have to a System Admin to manage the servers.

Consider storage, the other important component. Traditionally storage administrators perform critical functions such as adding disks, carving out LUNs, managing replication for DR and so on. These functions are irrelevant in Exadata. For instance, the disks are preallocated in Exadata, the LUNs are created at installation time, there is no replication since the DR is by Data Guard which at the Oracle database level. One need not be a storage expert to the perform the tasks in Exadata. Additionally the Storage Admins are experts in the specific brand of storage, e.g. EMC VMax or IBM XiV. In Exadata, the storage is different from all the other brands your storage admins may be managing. They have to learn about the Exadata storage anyway; so why not have someone else, specifically the DMA learn?

Consider Network. In Exadata the network components are very limited since it is only for the components inside the rack. This reduces the flexibility of the configuration compared to a regular general purpose network configuration. the special kind of hardware used in Exadata - Infiniband - requires some special skills which the network ops folks may have to learn anyway. So, why not the DMAs instead of them? Besides, Oracle already provides a lot of tools to manage this layer.

That leaves the most visible component - the database which is, after all, the heart and soul of Exadata. This layer is amenable to a considerable degree of tuning and the depth of skills in this layer is vital to managing Exadata effectively. Transferring the skills needed here to a non-DBA group or individual is difficult, if not impossible. This makes the DBA group the most natural choice for evolving into the DMA role after absorbing the relevant other skills. The other skills are not necessarily at par with the administrator of the respective components. For instance the DMA does not need to be a full scale Linux system admin; but just needs to know a few relevant concepts, commands and tools to perform the job well. Network management is Exadata is a fraction of the skills expected from a network admin. The storage management in cell servers are new to any group; so the DMA will find that as easy as any other group, if not easier.

By understanding the available knobs on all the constituent components of Exadata, the DMA can be better prepared to be an effective administrator of the Exadata system; not by divvying up the activities to individual groups which are generally autonomous. The advantages are particularly seen when troubleshooting or patching Exadata. Hence, I submit here for your consideration - a new role called DMA (Database Machine Administrator) for the management of Exadata. The role should have the following skillsets:

60% Database Administration
20% Cell Administration
15% Linux Administration
5% Miscellaneous (Infiniband, network, etc.)

I have written an article series on Oracle Technology Network - Linux for Oracle DBAs. This 5-part article series has all the commands an concepts the Oracle DBA should understand about Linux. I have also written a 4 part article series - Commanding Exadata - for DBAs to learn the 20% cell administration. With these two , you will have everything you need to be a DMA. Scroll down to the bottom of this page and click on "Collection of Some of My Very Popular Web Articles" to locate all these articles and more.

Summary

In this blog entry, I argued for creating a single role to manage the Exadata system instead of multiple groups managing individual parts. Here are the reasons in a nutshell:

Exadata is an engineered system where all the components play collaboratively instead of as islands. Managing them separately may be ineffective and detrimental.
The support organizations of components such as Systems, storage, DBA, etc. in an organizations are designed with a generic purpose in mind. Exadata is not generic. Its management needs unprecedented close coordination among various groups which may be new to the organization and perhaps difficult to implement.
The needed skillsets are mostly database centric; other components have very little to manage.
These other skills are easy to add to the DBA skills making the natural transition to the DMA role.

Best of luck in becoming a DMA and implementing Exadata.