Sunday, April 27, 2008

Oracle Metalink getting a new look

Oracle metalink is getting a facelift. A lot of flash has been used. Here is the cool new look :-

It is yet to replace the existing interface though You can still see the new look via or Click "Software Configuration Manager" tab on

Thursday, April 17, 2008

Oracle Critical Patch Update April 2008

Oracle has released critical patch update for Apr 2008. Please review the following URL to see if the product you are using requires the patch or not.


Thursday, April 10, 2008

11g New Features - Automatic Memory Management

Memory management in Oracle database is getting better rather I would say is becoming easier now. There was a time when each memory component of the Oracle had to be defined using parameters. Oracle first introduced dynamic memory resizing in 9i, moved to Automatic Shared memory management in 10g reducing the number of major parameters to size memory to 2 - SGA was controlled using a single parameter (SGA_TARGET) and PGA portion controlled by PGA_AGGREGATE_TARGET. Now oracle has gone one step ahead - 11g gives you the option of defining only one parameter "MEMORY_TARGET" to control both SGA and PGA. Now Oracle can dynamically exchange memory between SGA and PGA. Isn't that great...

Starting 11g, Oracle by default uses this new memory management feature known as "Automatic Memory Management". Now you should not be worried about whether PGA got over-allocated or has SGA got over-allocated; You can simply set MEMORY_TARGET and relax!!!

SIPRAS@11glab> show parameter memory

------------------------------------ ----------- -------
hi_shared_memory_address integer 0
memory_max_target big integer 608M
memory_target big integer 608M
shared_memory_address integer 0

You can also set maximum target too with the help of "MEMORY_MAX_TARGET" as was the case with "SGA_MAX_SIZE" . Some new (useful) views in 11g pertaining to automatic memory management :-

V$MEMORY_DYNAMIC_COMPONENTS -> find out how much has been allocated to each component along with minimum and maximum values it touched
V$MEMORY_TARGET_ADVICE -> will give you tuning advice for the MEMORY_TARGET

SIPRAS@11glab> SELECT component, current_size, min_size, max_size, last_oper_type FROM v$memory_dynamic_components;

------------------------------ ------------ ---------- ---------- -------------
shared pool 150994944 71303168 150994944 GROW
large pool 4194304 4194304 4194304 STATIC
java pool 12582912 4194304 12582912 GROW
streams pool 0 0 0 STATIC
SGA Target 381681664 381681664 381681664 STATIC
DEFAULT buffer cache 209715200 209715200 297795584 SHRINK
KEEP buffer cache 0 0 0 STATIC
RECYCLE buffer cache 0 0 0 STATIC
DEFAULT 2K buffer cache 0 0 0 STATIC
DEFAULT 4K buffer cache 0 0 0 STATIC
DEFAULT 8K buffer cache 0 0 0 STATIC
DEFAULT 16K buffer cache 0 0 0 STATIC
DEFAULT 32K buffer cache 0 0 0 STATIC
Shared IO Pool 0 0 0 STATIC
PGA Target 16777216 16777216 16777216 STATIC
ASM Buffer Cache 0 0 0 STATIC

MEMORY_TARGET is a dynamic parameter whereas MEMORY_MAX_TARGET is not. If you set SGA_TARGET and PGA_AGGREGATE_TARGET with automatic memory management and they are less than MEMORY_TARGET then those values will act as the minimum values for SGA and PGA respectively. In case you set it to more than MEMORY_TARGET, then you will get the error "ORA-00844: Parameter not taking MEMORY_TARGET into account, see alert log for more information".

Reference :- Oracle® Database Administrator's Guide 11g Release 1 (11.1) Part Number B28310-04

Un-documented and Un-used privilege

A recent question on Oracle Forums about a privilege caught my attention (got a few others interested too) and I started looking for an answer...And in the end it turned out to be un-documented and un-utilized privilege. The privilege in question is "UNDER ANY TABLE". The Privilege does exists in the database; I checked in the database (DBA_SYS_PRIVS and ROLE_SYS_PRIVS) but its not documented anywhere. I searched 9i, 10g and 11g documentation but no luck.

SIPRAS@orademo> select * from ROLE_SYS_PRIVS where privilege like 'UNDER%';

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

The other 2 privileges "UNDER ANY VIEW" and "UNDER ANY TYPE" are documented. Here is the link.

One of the respondent mentioned that :- In 2002 someone else asked this question on the Metalink forums and support replied, "This privilege is currently not used today. It was added for future functionality." May be oracle thought of it but did not use it.

My search lead me to "IBM Informix Dynamix Server". They have a UNDER privilege for TABLE and here is an excerpt from the documentation -

Under Privileges for Typed Tables (IDS)

You can grant or revoke the Under privilege to control whether users can use a typed table as a supertable in an inheritance hierarchy. The Under privilege is granted to PUBLIC automatically when a table is created (except in ANSI-compliant databases). In an ANSI-compliant database, the Under privilege on a table is granted to the owner of the table. To restrict which users can define a table as a supertable in an inheritance hierarchy, you must first revoke the Under privilege for PUBLIC and then specify the users to whom you want to grant the Under privilege. For example, to specify that only a limited group of users can use the employee table as a supertable in an inheritance hierarchy, you might execute the following statements:


TO johns, cmiles, paulz
Sounds like an ANSI compliant feature and something to do with "User-defined types" and "Nested tables" in Oracle; May be Oracle wanted to use for something similar but did not implement it hence the privilege is still sticking around without any use...or May be the "UNDER ANY TYPE" is sufficing the requirement but the "UNDER ANY TABLE" has not be taken out...

Wednesday, April 9, 2008

yaahoo....10g RAC expert...

....I have something to cheer about.....having cleared 10g RAC Expert exam today....feeling relieved and proud too :)

Monday, April 7, 2008

11g New Features - Incident packaging using "adrci" utility

As an Oracle DBA, you interface with Oracle support many a times; specially when you are faced with some critical errors such ORA-600 or ORA-7445 etc. And each time you interact with Oracle support, you have to provide quite few logs (alert log, trace files etc.) so that support can assist you in resolving the problem. At times the SR goes back and forth just to additional information - such as providing additional trace files etc. since you may have missed it earlier.

But starting 11g, your life will become a little easy thanks to a new utility called "ADRCI" - ADR Command Interpreter. Now you don't have to dig around trace files and other files to determine the files that Support would require for analysis. You can simply use "adrci" to package all the files that would be required for analysis with a few simple commands.

Apart from the packaging ability, one can use adrci to :-

a. view alert log (show alert)
b. view other trace files
c. view health check reports
Viewing alert log and trace files is easy but as I said, the best use of this utility is to package incident / problems encountered so that can the packaged file can be easily be uploaded to Oracle support when needed. You do not have to search through trace files and other files to determine the files that are required for analysis.

Here is a quick demonstration of how useful this utility can be when you have to upload files ( required to analyze a specific problem) to Oracle support :-

1. Launch adrci

$ adrci

ADRCI: Release - Beta on Mon Apr 7 16:11:06 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

ADR base = "/app/oracle"

2. Check the incidents reported in alert log

adrci> show incident

ADR Home = /app/oracle/diag/rdbms/11GLAB:
-------------------- ----------------------------------------------------------- ----------------------------------------
21746 ORA 4031 2008-04-07 16:57:11.039525 +05:30
21745 ORA 4031 2008-04-07 16:57:00.356082 +05:30
21715 ORA 4031 2008-04-07 16:57:16.796655 +05:30
21714 ORA 4031 2008-04-07 16:57:07.883365 +05:30
21713 ORA 4031 2008-04-07 16:57:00.694116 +05:30
5 rows fetched

3. Identify the specific incident for which you want to create a package so that you can upload it to Oracle support

Created package 2 based on incident id 21713, correlation level typical

This creates a pacakge of the incident 21713 in "incpkg" directory; you can then add diagnostic data to the package.

4. Finally generate the package for the incident, which then can be uploaded to metalink while seeking support from Oracle.

adrci> ips generate package 3
Generated package 3 in file /app/oracle/product/11.1.0/db_1/dbs/, mode complete

5. As reported above a zip file has been created with all relevant logs. Now you can upload this zip file to Oracle support and seek their help in resolving your problem.

You can also view the information generated in "incpkg" directory

$cd /app/oracle/diag/rdbms/11GLAB/incpkg/pkg_3/seq_1
$ ls -ltr
-rw-r----- 1 oracle oinstall 499 Apr 7 17:14 metadata.xml
-rw-r----- 1 oracle oinstall 21968 Apr 7 17:14 manifest_3_1.xml
-rw-r----- 1 oracle oinstall 26270 Apr 7 17:14 manifest_3_1.txt
-rw-r----- 1 oracle oinstall 20064 Apr 7 17:14 manifest_3_1.html
drwxr-xr-x 2 oracle oinstall 4096 Apr 7 17:14 export
drwxr-xr-x 2 oracle oinstall 4096 Apr 7 17:14 crs
-rw-r----- 1 oracle oinstall 62789 Apr 7 17:14 config.xml

We used to achieve the same earlier by running some scripts or collecting the logs/trace files manually. But with adrci, this task is pretty simplified; I think this is surely going to reduce the time to diagnose and resolve any problem.

References :- Oracle® Database Administrator's Guide 11g Release 1 (11.1) Part Number B28310-04
Oracle® Database Utilities 11g Release 1 (11.1) Part Number B28319-02 - ADRCI: ADR Command Interpreter

Friday, April 4, 2008

11g New Features - ADR

This is a big change in 11g. Now you will not find "alertSID.log" in it's familiar location i.e admin/SID/bdump. It has now got a new location; In fact, Oracle has standardized to store all logs/trace at a single base directory instead of scattering them all over. It's called "Automatic Diagnostic Repository" or "ADR". The parameters "background_dump_dest" and "user_dump_dest" are now deprecated and being replaced by a new parameter called "DIAGNOSTIC_DEST". What more - the alert.log is also available in an xml; Don't worry the text version will still be available.

So from now onwards, you should use "diagnostic_dest" parameter to specify the destination for logs/traces. Oracle creates a sub-directory called "diag" beneath "diagnostic_dest" directory where it will keep all logs/traces. When I say all logs, it is all logs/trace files for all its products/tools including ASM, CRS, Listener etc. This is very great move specifically when CRS stack etc are used; the CRS logs are scattered all over the place.

Here is the new directory structure where you can find all log and trace files :-


Note: I have just expanded and explained key folders in rdbms sub-directory which stores all log and trace files for Oracle database.

sweep -->
stage -->
incpkg -->
incident --> incident dump files
hm --> health monitor files
cdump --> core_dump_dest
metadata --> incident metadata files
lck -->
ir -->
alert --> location for the alert log in xml format
trace --> location for all trace files and text version of alert.log


Some other major improvements are :-
  • Now each critical errors such as ORA-600, ORA-7445 etc. are treated as incidents. Tags the logs with incident id's; Incident alerts are sent to EM and makes entry into alert.log as usual.
  • Incident Packaging service :- IPS enables you to automatically and easily gather the diagnostic data—traces, dumps, health check reports, and more—pertaining to a critical error and package the data into a zip file for transmission to Oracle Support. Awesome indeed.
  • Automatic capture of diagnostic data upon failure
  • Health checks on detecting a critical error
  • V$DIAG_INFO is a new view which lists all ADR locations for the database instance
  • There is a new column called "TRACEFILE" in V$PROCESS which specifies the trace file name for each process.
Above all, 11g comes with a new command line utility called "adrci" to view alert/trace files, package incident trace files so that uploading to oracle support is easy, view health check reports etc. I will explain about this tool in my next post. So all said, the ADR is a great improvement, a great step forward to standardize diagnostic information.

References :- Oracle® Database Administrator's Guide 11g Release 1 (11.1) Part Number B28310-04