Lovely Oracle

Exadata database server Drive Configuration Verification

2012-01-26T20:10:00.000-08:00

To verify the database server physical drive configuration, use the following command:

/opt/MegaRAID/MegaCli/MegaCli64 PDList -aALL | grep "Firmware state"

The output should be similar to for the disks:

Firmware state: Online, Spun Up

To verify the database server virtual drive configuration, use the following command:

/opt/MegaRAID/MegaCli/MegaCli64 CfgDsply -aALL | grep "Virtual Drive:";/opt/MegaRAID/MegaCli/MegaCli64 CfgDsply -aALL | grep "Number Of Drives";/opt/MegaRAID/MegaCli/MegaCli64 CfgDsply -aALL | grep "^State"

The output should be similar to:

Virtual Drive: 0 (Target Id: 0)
Number Of Drives :
State : Optimal

Exadata [WARNING] This update is not applicable for Not Available BP12

2012-01-23T01:02:00.000-08:00

During the application of BP12 on Exadata half rack V2, during the phase of application of db minimal patch, one compute node refused to get patched.

As the ./install.sh script was run, it failed with :

[WARNING] This update is not applicable for Not Available.

Of course it wasn't of much help, so we fished around and then found that because dmidecode command was not returning the system product name at that node, whereas the same command was working at the other nodes, where the patching was successful.

[root@exanode db_minimal_patch]# dmidecode -s system-product-name
Not Available

This was resolved with not that much fanfare. We merely had to reset iLOM from an SSH terminal.Though it took several hours to get there. So instrumentation is the key for debugging.

genclntsh: Could not locate shrept.lst during PSU 11.2.0.2.4

2012-01-13T17:47:00.000-08:00

What to do when you are applying PSU 11.2.0.2.4 on your Oracle 11g 11.2.0.2.0 database, and you get following error while running the opatch apply:

Make failed to invoke "/usr/bin/make -f ins_net_client.mk

client_sharedlib ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

"....'genclntsh: genclntsh: Could not locate

/u01/app/oracle/product/11.2.0/db_1/network/admin/shrept.lst
make: *** [client_sharedlib] Error 1
'
Make failed to invoke "/usr/bin/make -f ins_rdbms.mk

client_sharedlib ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

"....'genclntsh: genclntsh: Could not locate

/u01/app/oracle/product/11.2.0/db_1/network/admin/shrept.lst
make: *** [client_sharedlib] Error 1
'

The following make actions have failed :

Re-link fails on target "client_sharedlib".
Re-link fails on target "client_sharedlib".

Solution:

Just proceed with the patch installation, it is likely to get completed

successfully with warnings.

After that, go to $ORACLE_HOME/network/admin and see if there is a file shrept.lst?

If there exists a file, make sure that it's permission are set to 644.

If shrept.lst doesn't exist, then create a new file like this:

cd $ORACLE_HOME/network/admin

touch shrept.lst

chmod 644 shrept.lst

Then open the file shrept.lst in text editor, and paste the following lines:

network : snaumihi_inithostinfo
network : snaumbg_gmt
network : naedpwd_encrypt
network : naumbsb_bld_singlebyte
network : ztapis
network : nlgh

Save and exit the file.

Now you have to run the relink all command, but before that no oracle process

is running out of that ORACLE_HOME:

cd $ORACLE_HOME/bin

./relink all

Now you should be good to go.

Cheers.

Reference: MOS Note ID 340978.1

Oracle Connects Big Data to Medium and Small Data

2012-01-12T19:08:00.000-08:00

With the announcement of the Oracle Big Data Appliance, Oracle also comes up with some really cool technology stack which is being termed as Oracle Big Data Connectors (OBDC). This piece of software can be used with both Oracle Big Data Appliance and other Apache Hadoop-based systems.

Among other things in OBDC, There is this Oracle Loader for Hadoop which uses MapReduce processing to load data efficiently into Oracle Database 11g and speeds up the loading by using the Oracle’s internal formats. This should be the big incentive and relief and a very solid business rationale for the decision makers to try out No SQL technology with their existing infrastructure.

The main reason of fast adoption of Exadata (Medium data) in the businesses is it’s ease and speed by which it gets connected to the OLTP (Small Data). Both of them run the same Oracle 11g. Now with these connectors, its’ easy to integrate the NoSQL (Big Data) with these small and medium data sources.

Integration with scalability and interoperability are the keywords here.

lsnrctl: symbol lookup error: lsnrctl: undefined symbol: nlgh after PSU 11.2.0.2.4

2012-01-11T19:08:00.000-08:00

Applying PSU 11.2.0.2.4 on Oracle 11.2.0.2.0 is often a breezing experience. But sometimes you get a mild jolt kind of thing when at the last step, after a beautifully successful patching session you get following error, while trying to restart the listener:

/u01/app/oracle> lsnrctl start
lsnrctl: symbol lookup error: lsnrctl: undefined symbol: nlgh

If you panic and start getting bad impressions, you are so human.

But don't worry, just relink all the binaries and try again. But before that, you must shutdown all the databases and there should be no Oracle binary running.

After making sure that nothing Oracle is running, relink:

/u01/app/oracle/product/11.2.0/db_1/bin> ./relink all
writing relink log to: /u01/app/oracle/product/11.2.0/db_1/install/relink.log
/u01/app/oracle/product/11.2.0/db_1/bin> lsnrctl start

And listener should start.

Reason 442 : Failed to enable Virtual Adapter in Windows 7 CISCO VPN Client

2012-01-04T19:36:00.000-08:00

If all of a sudden, you start receiving the error:

Reason 442 : Failed to enable Virtual Adapter

while you try to access the VPN network through the CISCO VPN client, you might want to disable the Internet sharing for other users. It should work from there.

There are some other things to check if above doesn't work and that is to run the diagnostic in the virtual adapter, or to disable any virtualbox or VMware adapter, or reseting the adapter.

Hope that helps.

Step by Step Implementation of SQL Profiles in Oracle

2012-01-02T15:23:00.000-08:00

Original Problematic Query:

SELECT *
FROM TEMP_TAB A2,TEMPOFF A1
WHERE A2.TEMP1=A1.TEMP1
AND (A2.TEMP13=1 OR A2.TEMP13=0 OR A2.TEMP13=0)
AND A2.STEMPDATE>=:B3
AND A2.STEMPDATE<:b2 a2.TEMPTEMP5<='' and=''>000
AND A2.TEMPTEMP5<>000
AND A2.TEMPTEMP5<>0
AND A2.TEMPTEMP5<>0
AND A1.TEMPTEMP6=:B1
AND A1.TEMPTEMP6<>0
GROUP BY A1.TEMPTEMP6,A1.TEMPTEMP7,TRUNC(A2.STEMPDATE,'MONTH')

===================
Plan of above query:

Plan hash value: 2803934278

------------------------------------------------------------------------------------------------------------------
| Id | Operation                         | Name         | Rows | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |              |       |       | 18133 (100)|          |       |       |
|   1 | HASH GROUP BY                    |              |    15 |   720 | 18133 (39)| 00:00:47 |       |       |
|* 2 |   FILTER                          |              |       |       |            |          |       |       |
|* 3 |    HASH JOIN                      |              |   781K|    35M| 17906 (38)| 00:00:47 |       |       |
|   4 |     MAT_VIEW ACCESS BY INDEX ROWID| TEMPOFF        |   104 | 1352 |    54   (0)| 00:00:01 |       |       |
|* 5 |      INDEX RANGE SCAN             | TEMPOFF_TEMPTEMP6    |   104 |       |     1   (0)| 00:00:01 |       |       |
|   6 |     PARTITION RANGE ITERATOR      |              | 9176K|   306M| 17549 (37)| 00:00:46 |   KEY |   KEY |
|* 7 |      TABLE ACCESS FULL            | TEMP_TAB | 9176K|   306M| 17549 (37)| 00:00:46 |   KEY |   KEY |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter((:B1<>0 AND :B3<:b2))
   3 - access("A2"."TEMP1"="A1"."TEMP1")
   5 - access("A1"."TEMPTEMP6"=:B1)
       filter("A1"."TEMPTEMP6"<>0)
   7 - filter((INTERNAL_FUNCTION("A2"."TEMP13") AND "A2"."STEMPDATE">=:B3 AND "A2"."TEMPTEMP5"<>0 AND
              "A2"."TEMPTEMP5"<>0 AND "A2"."TEMPTEMP5"<>0 AND "A2"."TEMPTEMP5"<>112 AND "A2"."STEMPDATE"<:b2))

===================

Rationale: As the filter at line 7 in plan is using Full Table Scan (FTS) of partition(s) of TEMPH.TEMP_TAB table, and it is likely that index access would speed up the query.(Also we know that the earlier optimal runs of the query were using index), so we need to enforce index access at line 7.

Table structure:

SQL> desc TEMPH.TEMP_TAB
Name                                                  Null?    TEMP13
----------------------------------------------------- -------- ------------------------------------
TEMP1                                                   NOT NULL NUMBER
TEMPTEMP5                                                   NOT NULL NUMBER
STEMPDATE                                                 NOT NULL DATE
BTEMPTEMP7                                                  NOT NULL NUMBER
TEMPCOUNT                                                         NUMBER
TMPTEMP13                                                  NOT NULL NUMBER
TMPSUID                                                          NUMBER
TEMP8_TEMP10_NO                                                 NUMBER

========================

Existing Composite indexes on table TEMPH.TEMP_TAB:

1-      PK_TEMP_TAB(STEMPDATE,TEMP1,TEMP13,BTEMPTEMP7,TEMPTEMP5)
2-      STEMPH_TEMP11(TEMP1,STEMPDATE,TEMP13,TEMPTEMP5)
3-      STEMPH_SSTO (TEMPTEMP5,STEMPDATE,TEMP13,TEMP1)
4-      TEMP_TAB_TEMP8_TEMP10_NO (TEMP8_TEMP10_NO,STEMPDATE) --> not applicable because columns are not in the filter
5-      TEMP_TAB_SHH_TEMP9EMPTEMP5_KEY(SHH_TEMP9EMPTEMP5_KEY,STEMPDATE)    --> not applicable because columns are not in the filter

====================

Filter from the plan on lin 7 using FTS:

filter((INTERNAL_FUNCTION("A2"."TEMP13") AND "A2"."STEMPDATE">=:B3 AND "A2"."TEMPTEMP5"<>0 AND
              "A2"."TEMPTEMP5"<>0 AND "A2"."TEMPTEMP5"<>0 AND "A2"."TEMPTEMP5"<>112 AND "A2"."STEMPDATE"<:b2))

The columns of TEMPH.TEMP_TAB table used in this filter are TEMP13,STEMPDATE,TEMPTEMP5.

Anyway, here we know that TEMPH.STEMPH_TEMP11 index should be used from the TEMPHorical runs and also the test run with bind variable values confirms that.

Now we have to force this query to use the index TEMPH.STEMPH_TEMP11. We can do it with hints, but since we cannot TEMP10 the query, so we are employing the SQL Profile here to enforce the usage of Index.

Now comes implementation of SQL profile:

In order to get string for the SQL profile, first run the query with the hint, and then get the plan with outline information, and then from outline information just get the string, as shown below:

SQL> set lines 100
SQL> set pages 100
SQL> set timing on
SQL> SELECT /*+index(s STEMPH_TEMP11)*/ *
2 FROM TEMPH.TEMP_TAB S, TEMPOFF O
3 WHERE S.TEMP1 = O.TEMP1 AND S.TEMP13 IN (1, 3, 5) AND S.STEMPDATE >= to_date('11/01/11','mm/dd/yy') AND S.STEMPDATE < to_date('12/01/11','mm/dd/yy')
4 AND S.TEMPTEMP5 NOT IN (0, 112, 0 , 0)
5 AND (O.TEMPTEMP6 = 0 ) AND O.TEMPTEMP6 NOT IN (0) GROUP BY O.TEMPTEMP6, O.TEMPTEMP7, TRUNC (S.STEMPDATE, 'MONTH');

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED +ALLSTATS'));

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / S@SEL$1
   5 - SEL$1 / O@SEL$1
   6 - SEL$1 / O@SEL$1
   8 - SEL$1 / S@SEL$1

Outline Data
-------------

/*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      OPT_PARAM('_optimizer_skip_scan_enabled' 'false')
      OPT_PARAM('optimizer_index_cost_adj' 90)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "O"@"SEL$1" ("TEMPOFF"."TEMPTEMP6"))
--->>     INDEX(@"SEL$1" "S"@"SEL$1" ("TEMP_TAB"."TEMP1" "TEMP_TAB"."STEMPDATE" "TEMP_TAB"."TEMP13" "TEMP_TAB"."TEMPTEMP5")) <<------ SQL PROFILE String
      LEADING(@"SEL$1" "O"@"SEL$1" "S"@"SEL$1")
      USE_NL(@"SEL$1" "S"@"SEL$1")
      END_OUTLINE_DATA
*/

==================================

-->> From the outline section above, get the string and create SQL profile for problem query as follows:

begin dbms_sqltune.import_sql_profile (sql_text => 'SELECT * FROM "TEMP_TAB" "A2","TEMPOFF" "A1" WHERE "A2"."TEMP1"="A1"."TEMP1" AND ("A2"."TEMP13"=1 OR "A2"."TEMP13"=3 OR "A2"."TEMP13"=5) AND "A2"."STEMPDATE">=:B3 AND "A2"."STEMPDATE"<:b2 "a2"."TEMPTEMP5"<='' and=''>0 AND "A2"."TEMPTEMP5"<>112 AND "A2"."TEMPTEMP5"<>0 AND "A2"."TEMPTEMP5"<>0 AND "A1"."TEMPTEMP6"=:B1 AND "A1"."TEMPTEMP6"<>0 GROUP BY "A1"."TEMPTEMP6","A1"."TEMPTEMP7",TRUNC("A2"."STEMPDATE",''MONTH'')',
profile => sqlprof_attr('INDEX(@"SEL$1" "A2"@"SEL$1" ("TEMP_TAB"."TEMP1" "TEMP_TAB"."STEMPDATE" "TEMP_TAB"."TEMP13" "TEMP_TAB"."TEMPTEMP5"))'),
name=>'FIX_1',
force_match=>false);
end;
/

Now check the latest plan for the session running the query and verify that index is being used in plan.

Afterwards, the profile can be dropped as:

exec dbms_sqltune.drop_sql_profile('FIX_1');

sundiag Exadata

2011-12-07T22:10:00.000-08:00

Exadata is greatest but it's not right out of Utopia so expect problems. One of the problems which sometimes arise are related to physical disks. But as usual Oracle has got some nifty and pertinent tools for the diagnostics. Sundiag is one such tool. Sundiag is used to diagnose the problems in the disks in Exadata. This script is run as the root user.

Sundiag is run by following command as root:

# /opt/oracle.SupportTools/sundiag.sh

Upon completion the above command will create a date stamped tar.bz2 file in /tmp/sundiag_/tar.bz2

For example, I ran the following from a compute (db) node:

[root@firstnode tmp]# /opt/oracle.SupportTools/sundiag.sh

Success in AdpEventLog

Exit Code: 0x00
sundiag_2011_12_08_05_47/
sundiag_2011_12_08_05_47/messages
sundiag_2011_12_08_05_47/messages.1
sundiag_2011_12_08_05_47/messages.2
sundiag_2011_12_08_05_47/messages.3
sundiag_2011_12_08_05_47/messages.4
sundiag_2011_12_08_05_47/firstnode_dmesg_2011_12_08_05_47.out
sundiag_2011_12_08_05_47/firstnode_imageinfo-all_2011_12_08_05_47.out
sundiag_2011_12_08_05_47/firstnode_lspci_2011_12_08_05_47.out
sundiag_2011_12_08_05_47/firstnode_lspci-xxxx_2011_12_08_05_47.out
sundiag_2011_12_08_05_47/firstnode_lsscsi_2011_12_08_05_47.out
sundiag_2011_12_08_05_47/firstnode_fdisk-l_2011_12_08_05_47.out
sundiag_2011_12_08_05_47/firstnode_sel-list_2011_12_08_05_47.out
sundiag_2011_12_08_05_47/firstnode_megacli64-AdpAllInfo_2011_12_08_05_47.out
sundiag_2011_12_08_05_47/CmdTool.log
sundiag_2011_12_08_05_47/MegaSAS.log
sundiag_2011_12_08_05_47/firstnode_megacli64-PdList_short_2011_12_08_05_47.out
sundiag_2011_12_08_05_47/firstnode_megacli64-GetEvents-all_2011_12_08_05_47.out
sundiag_2011_12_08_05_47/firstnode_megacli64-FwTermLog_2011_12_08_05_47.out
sundiag_2011_12_08_05_47/firstnode_megacli64-CfgDsply_2011_12_08_05_47.out
sundiag_2011_12_08_05_47/firstnode_megacli64-BbuCmd_2011_12_08_05_47.out
sundiag_2011_12_08_05_47/firstnode_megacli64-LdPdInfo_2011_12_08_05_47.out
sundiag_2011_12_08_05_47/firstnode_megacli64-PdList_long_2011_12_08_05_47.out
sundiag_2011_12_08_05_47/firstnode_megacli64-LdInfo_2011_12_08_05_47.out
sundiag_2011_12_08_05_47/firstnode_megacli64-status_2011_12_08_05_47.out
==============================================================================
Done the report files are in bzip2 compressed /tmp/sundiag_2011_12_08_05_47.tar.bz2
==============================================================================

Often, Oracle support would ask you to run the sundiag on multiple nodes. It's time-consuming and cumbersome to generate sundiag outputs from multiple nodes and then perhaps bring them to one node, again tar and zip them and ftp to the Oracle support. dcli, my favorite command at Exadata, comes to rescue there and runs sundiag for you on all the nodes and then by using dcli you can bring the sundiag output files created on all the nodes to a single node. For example, below sundiag has been run from the first node by using dcli:

1. Run the sundiag from first node with dcli:

[root@firstnode onecommand]# dcli -g all_group -l root /opt/oracle.SupportTools/sundiag.sh 2&>1

2. Verify there is output in /tmp on each node:

[root@firstnode onecommand]# dcli -g all_group -l root --serial 'ls -l /tmp/sundiag* '

3. Copy the files to first node and before that sort them by hostname into directories, as they will likely mostly have the same filename with the same date stamp:

[root@firstnode onecommand]# for H in `cat all_group`; do mkdir /tmp/sundiagdir/$H ; scp -p $H:/tmp/sundiag*.tar.bz2 /tmp/sundiagdir/$H ; done

4. Verify that files have arrived at first node:

[root@firstnode onecommand]# cd /tmp/sundiagdir
[root@firstnode ~]# ls -l

5. Now tar and zip the files

[root@firstnode ~]# tar cvf sundiag.tar.bz2 *

6. Upload this file to the Service Request through ftp.oracle.com

ORA-4031 Tool by Oracle

2011-11-10T22:48:00.001-08:00

Whenever I get the dreaded ORA-600 or ORA-7445 error, my first stop is the ORA-600 troubleshooting tool at the My Oracle Support:

153788.1 - Troubleshoot an ORA-600 or ORA-7445 Error Using the Error Lookup Tool

Just give the first parameter given along with the ORA-600 tool, and get the initial information about the bug, its patch or the workaround, and you would be more informed in opening the SR with the Oracle, if need be.

Another not-so-pleasant error to deal with for the DBAs and the Developers is ORA-4031 memory error. This error also throws itself out of database with different parameters and often with different memory pools. Some bloggers out there have good use cases in resolving these issues, but it seems that they are not still enough, and more often than not even SR get stale while resolving these errors.

Increasing the shared pool and/or throwing more memory might, I would repeat, might resolve the issue for a brief period, but it would return for sure. Proper memory configuration at the Operating system level (think huge pages) and the database level coupled with tuned SQL is the way to go. But that is a broad area, and this newly introduced tool by Oracle, the ORA-4031 is a good step in narrowing down this issue.

But it is very nascent and innocent right now but very promising. Search "ORA-4031 Troubleshooting Tool" in MOS, and it should be the top most find.

How to become a RockStar DBA? Sydney Illuminated by Paul Vallee

2011-10-24T20:38:00.000-07:00

Starting a company focussed solely on database administration, weathering the ups and downs of the industry, hiring and retaining the top notch industry DBA talent, servicing clients' data in every industry for over an decade is not something to talk lightly about.

Who better to let us know as how to become a rockstar DBA than Paul Vallee, the man who founded, directed and sustained and managed the Pythian, a cluster of tightly integrated DBAs working for world's most busy databases round the clock.

Sydney Oracle Meetup is having Paul Vallee for some thrilling 2 hours. If you are in Syndey and even remotely relate to database administration, drop by:

More details here.

From Oracle Meetup site:

Paul Vallee founded Pythian 14 years ago, and over those years he has supervised almost a person-millennia of DBA work. Today about 100 of the world's most talented DBAs work in his company across 15 countries. Paul is visiting Sydney in October and this is a wonderful opportunity to hear his experiences and opinions on what skills, practices and disciplines a modern DBA should master to gain a success in this demanding and rewarding career. The intended audience is DBAs, Apps DBAs, Systems Administrators, Systems Architects, Operations Managers and IT Managers. We will have an open follow-up discussion after Paul’s presentation where any related questions will be on-topic.

Difference Between Exadata and Oracle Database Appliance

2011-09-30T00:36:00.000-07:00

It is a pre-configured combination of hardware and software that provides a infrastructure for running Oracle Database. It consists of a database layer and a storage layer connected through an infiniband network. The database layer consists of Sun servers and runs Oracle 11gR2 software and normally Oracle RAC is deployed on database node. The database layer uses ASM to map to the storage layer. The storage layer also consists of Sun Servers and runs Oracle Storage server software. Exadata's exclusive features are smart scan, Hybrid Columnar Compression, and Storage Indexes.

Oracle Unbreakable Database Appliance (OUDA) is a completely integrated highly available (HA) database system in a single box. OUDA is an appliance and it contains storage, servers, operating System, and web-based monitoring tool (database control) within one box. It provides a ready-to-use, clustered database solution that includes both hardware and software optimized to work together.

Exadata is not an appliance, OUDA is. A server appliance is a computer for specific tasks, such as handling email or storing data, and a database appliance is just that; a computer to handle database. It is meant to be bought, brought into data center, plugged in, data loaded, and then gave access to the user, as you would do with any other appliance. But courtesy to Oracle's inherent architecture, you can customize, tweak and tune the appliance parameter according to your requirement.

OUDA is geared towards small and medium- sized business, whereas Exadata is for the big guns.

ORA-29516: Aurora assertion failure: Assertion failure

2011-08-15T00:33:00.000-07:00

From Oracle 11g release 1 (11.1), Oracle introduces just-in-time(JIT) compiler for Oracle JVM environment, which results in faster execution because invalidation, recompilation, and storage of code is done dynamically.

JIT is controlled by parameter java_jit_enabled, and if it is set to TRUE then the Java methods are automatically compiled to native code by the JIT compiler and made available for use by all sessions.

But you can get the error Ora-29516 Aurora assertion failure on Linux x64bit platform, and the workaround to overcome that error is turning off the JIT compiler:

alter system set java_jit_enabled=false;

Profiled in Peer-to-Peer section of Oracle Magazine

2011-08-13T03:06:00.000-07:00

Thanks to Oracle Magazine, I was profiled in the Peer-to-Peer section of Oracle Magazine.Vanessa Simmons, the Director of Marketing at Pythian has blogged about it here.

Pythian’s Oracle ACE, Fahd Mirza appears in this month’s Community: Peer-to-Peer review “In With the New“, as published in the September/October 2011 issue of Oracle Magazine.

Thanks Oracle and Pythian.

Script to start Oracle stats gathering in background from shell in Linux

2011-08-05T23:22:00.000-07:00

Following is the script to gather stats for test.foo table. You can create a file at OS level and paste the following script:

touch gatherstats.sh
chmod a+x gatherstats.sh

then paste:

#!/bin/sh
sqlplus <<-EOF
/ as sysdba
set echo on timing on
exec dbms_stats.gather_table_stats(ownname=>'test',tabname=>'foo', ESTIMATE_PERCENT=>.01, METHOD_OPT=> 'FOR ALL INDEXED COLUMNS',CASCADE=>TRUE,DEGREE=>6);
exit
EOF
echo -e "Stat completed for table FOO." | mail -s "Stat completed"

Now you can run the script in background:

nohup ./gatherstats.sh &

The script will email you upon completion.

ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists Statistics Gathering

2011-08-05T00:23:00.000-07:00

You might encounter the following error in your alert log file:

ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists

*** 2011-08-04 22:54:30.487
DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"owner"','"tablename"','""', ...)
DBMS_STATS: ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists

On investigation, you tend to see that both the table and the dbms_stats objects exist and the statistics job failed just for this table. This might be a bug or more likely a transient hiccup with the statistics gathering job. You can verify that by just gathering the statistics manually for that table.

exec dbms_stats.gather_table_stats(ownname=>'',tabname=>'', ESTIMATE_PERCENT=>.01, METHOD_OPT=> 'FOR ALL INDEXED COLUMNS',CASCADE=>TRUE,DEGREE=>6);

If it fails again, then you need to look into the accompanying trace file and log an SR for a likely bug.

Log Buffers of July

2011-07-31T00:14:00.000-07:00

Pythian's celebrated Blog Carnival marches on in July.

Log Buffer #231, A Carnival of the Vanities for DBAs

Log Buffer #230, A Carnival of the Vanities for DBAs

Log Buffer #229, A Carnival of the Vanities for DBAs

Log Buffer #228, A Carnival of the Vanities for DBAs

Log Buffer #227, A Carnival of the Vanities for DBAs

Companies Standing in Queue to Get Exadata

2011-06-27T20:13:00.000-07:00

"Computer is the Network" seems to be the mantra now as the server side technologies cross another level of advancement. Oracle Exadata is the premier database appliance winning the show by offering a truly working magic of servers, storage, networking and software enclosed in one box.

So much so is the success of this fabulous piece of wonder that companies are flocking to make a queue to buy it, like people use to stand in queue to buy iPad or iPod. Oracle announces that more than 1,000 Oracle® Exadata Database Machines are installed at customer sites globally,

Now that is some doing.

Mapping of Physical Disk, LUNS, Cell Disks, and Grid Disks

2011-06-27T09:03:00.000-07:00

Exadata machine comprises of compute nodes and the cell nodes. Compute nodes host the RDBMS and ASM instances along with Oracle homes, whereas the cell nodes contain data. This data is stored under several layers of abstraction starting from physical disks to LUN to cell disks, to grid disks to asm disks to asm disk groups.

From cell node, from cellcli utility you can manage physical disks, LUNs, cell disks and the grid disks.

From compute node, you can manage the asm disks and the asm disk groups.

Following is the mapping of the storage layers in the Exadata.

First comes physical disk, then LUN, then cell disk, then griddisk, and then asm disks and then asm diskgroups. There is one to one mapping between physical disk, LUN, and cell disk. so that means that for each physical disk there is one LUN, and for that LUN there is one celldisk. Then comes griddisk. Many griddisks can comprise of many cell disks. Griddisk and asm disks are synonymous with each other in exadata contest, so they are same thing. Then comes asm diskgroup, and an asm diskgroup comprises of multiple griddisks. We have three disk groups SYSTEMDG, DATA, and RECO.

List the griddisk:

CellCLI> list griddisk DATA_TT09_test1test0 detail
         name:                   DATA_TT09_test1test0
         availableTo:
         cellDisk:               TT09_test1test0
         comment:
         creationTime:           0000-07-14T02:15:39+00:00
         diskType:               HardDisk
         errorCount:             1
         id:                     00000129-000c-6341-0000-000000000000
         offset:                 32M
         size:                   430G
         status:                 active

List the celldisk of the griddisk:

CellCLI> list celldisk TT09_test1test0 detail
         name:                   TT09_test1test0
         comment:
         creationTime:           0000-07-14T02:12:14+00:00
         deviceName:             /dev/test
         devicePartition:        /dev/test
         diskType:               HardDisk
         errorCount:             2
         freeSpace:              0
         id:                     00000129-0009-4275-0000-000000000000
         interleaving:           none
         lun:                    0_9
         raidLevel:              0
         size:                   557.859375G
         status:                 normal

List the LUN of the celldisk:

CellCLI> list lun 0_9 detail
         name:                   0_9
         cellDisk:               TT09_test1test0
         deviceName:             /dev/test
         diskType:               HardDisk
         id:                     0_9
         isSystemLun:            FALSE
         lunAutoCreate:          FALSE
         lunSize:                557.861328125G
         lunUID:                 0_9
         physicalDrives:         20:9
         raidLevel:              0
         lunWriteCacheMode:      "WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU"
         status:                 normal

List the Physical disk of the LUN:

CellCLI> list physicaldisk 20:9 detail
         name:                   20:9
         deviceId:               17
         diskType:               HardDisk
         enclosureDeviceId:      20
         errMediaCount:          23
         errOtherCount:          0
         foreignState:           false
         luns:                   0_9
         makeModel:              "TEST ST360057SSUN600G"
         physicalFirmware:       0805
         physicalInsertTime:     0000-03-24T22:10:19+00:00
         physicalInterface:      sas
         physicalSerial:         E08XLW
         physicalSize:           558.9109999993816G
         slotNumber:             9
         status:                 normal

There can be M:M relationship between celldisks and the griddisk, but in following, there is 1:M relationship between celldisk and griddisks.

List griddisks in one celldisk:

CellCLI> list griddisk where cellDisk like 'TT09_test1test0'
         DATA_TT09_test1test0         active
         RECO_TT09_test1test0         active
         SYSTEMDG_TT09_test1test0     active

List griddisks in one celldisk (in detail with wild card):

CellCLI> list griddisk where cellDisk like 'TT09.*' detail
         name:                   DATA_TT09_test1test0
         availableTo:
         cellDisk:               TT09_test1test0
         comment:
         creationTime:           0000-07-14T02:15:39+00:00
         diskType:               HardDisk
         errorCount:             1
         id:                     00000129-000c-6341-0000-000000000000
         offset:                 32M
         size:                   430G
         status:                 active

         name:                   RECO_TT09_test1test0
         availableTo:
         cellDisk:               TT09_test1test0
         comment:
         creationTime:           0000-07-14T02:15:55+00:00
         diskType:               HardDisk
         errorCount:             1
         id:                     00000129-000c-a1d6-0000-000000000000
         offset:                 430.046875G
         size:                   98.6875G
         status:                 active

         name:                   SYSTEMDG_TT09_test1test0
         availableTo:
         cellDisk:               TT09_test1test0
         comment:
         creationTime:           0000-07-14T02:13:02+00:00
         diskType:               HardDisk
         errorCount:             0
         id:                     00000129-0009-fd52-0000-000000000000
         offset:                 528.734375G
         size:                   29.125G
         status:                 active

Listing that a griddisk only belongs to one celldisk in this case:

CellCLI> list griddisk DATA_TT09_test1test0 detail
         name:                   DATA_TT09_test1test0
         availableTo:
         cellDisk:               TT09_test1test0
         comment:
         creationTime:           0000-07-14T02:15:39+00:00
         diskType:               HardDisk
         errorCount:             1
         id:                     00000129-000c-6341-0000-000000000000
         offset:                 32M
         size:                   430G
         status:                 active

CellCLI> list griddisk RECO_TT09_test1test0 detail
         name:                   RECO_TT09_test1test0
         availableTo:
         cellDisk:               TT09_test1test0
         comment:
         creationTime:           0000-07-14T02:15:55+00:00
         diskType:               HardDisk
         errorCount:             1
         id:                     00000129-000c-a1d6-0000-000000000000
         offset:                 430.046875G
         size:                   98.6875G
         status:                 active

CellCLI> list griddisk SYSTEMDG_TT09_test1test0 detail
         name:                   SYSTEMDG_TT09_test1test0
         availableTo:
         cellDisk:               TT09_test1test0
         comment:
         creationTime:           0000-07-14T02:13:02+00:00
         diskType:               HardDisk
         errorCount:             0
         id:                     00000129-0009-fd52-0000-000000000000
         offset:                 528.734375G
         size:                   29.125G
         status:                 active

Hang Analysis in Exadata

2011-06-23T22:31:00.000-07:00

In half rack of Exadata machine, from node got the following error:

PMON failed to acquire latch, see PMON dump

First I checked that whether the database was hung or not.

Database was up and all the four instance were up too along with ASM instances.

Database was also accepting the logins.

The above error occurred just once, so that was a relief and seemed a temporary

glitch.

Checked the PMON dump, and it showed that a process temporarily blocked PMON

as it held the lock on child library cache.

PMON unable to acquire latch 60106398 Child shared pool level=7 child#=2

But wanted to make sure that things are fine, so used the blocking was temporary

and the database is not hung or having serious problem, used the oradebug utility

to do the hang analysis.

In summary, the hang analysis suggested that there were no cycles (true blocking

at the internal kernel level). There were also not any session which was blocking

many (10) sessions. There were also no Open Chains (many sessions in waiting).

There have been mention of waiting of sessions on the Mutex.

Did the hang analysis as follows:

Opened two putty sessions and fired SQL Plus on both of them with the node:

In Session 1: (gathering hang analysis)

SQL> oradebug setmypid
SQL> oradebug unlimit;
SQL> oradebug hanganalyze 3

Hang Analysis in /u01/app/oracle/diag/rdbms/test/test4/trace/test4_ora_20189.trc

In Session 2: (gathering system state)

SQL> oradebug setmypid
SQL> oradebug unlimit;
SQL> oradebug dump systemstate 266

Then wait for at least 1 minute, and then again do following for comparison:

In Session 1: (gathering hang analysis again)

SQL> oradebug hanganalyze 3

Hang Analysis in /u01/app/oracle/diag/rdbms/test/test4/trace/test4_ora_20189.trc

In Session 2: (gathering system state again)

SQL> oradebug setmypid
SQL> oradebug unlimit;
SQL> oradebug dump systemstate 266

/u01/app/oracle/diag/rdbms/test/test4/trace/test4_ora_20189.trc
contains the hang analysis and you should check for cycles, blockers, open chains.

Reference: MOS [ID 452358.1]

ORA-00494: enqueue [CF] held for too long

2011-06-17T22:28:00.000-07:00

There was a high load at the Linux (RHAS4) due to the backups of three 11.1.0.6 databases going on simultaneously. So much was the load, that server became unresponsive, and then one of the instances crashed with the following error in the alert log file:

Fri Jun 17 02:20:26 2011
System State dumped to trace file

/u01/app/oracle/admin/orcl/bdump/orcl_lgwr_1.trc
Killing enqueue blocker (pid=191) on resource

CF-00000000-00000000 by killing session 56.1

Found a metalink note: ID 779552.1

This note states about kill blocker interface, a mechanism through which Oracle kills any blocking process during high loads. If that blocking process is background process, then the instances crashes too. Oracle prefers crashing the instance, instead of a hang situation. This behavior can be override in two ways. Both ways take help of hidden parameters. In one case, kill blocker interface can be disabled altogether and in the other case, kill blocker interface can be prevented from killing background processes.

So at the time of this instance crash, there was very high load at the server due to RMAN backups, and so that is why the kill blocker interface killed the background process and so the instance crashed.

Please Vote for Oracle OpenWorld Suggest-a-Session on Oracle Mix

2011-06-05T02:12:00.000-07:00

Back by popular demand, Oracle OpenWorld Suggest-a-Session on Oracle Mix

Please vote for my session (Administration of Automatic Diagnostic Repository )for the Oracle OpenWorld 2011

To vote, click here.

I am not a genius, but I am still 11 year Oracle DBA

2011-05-26T21:49:00.000-07:00

Becoming an Oracle DBA is not some mythical thing. Excelling in this field, sustaining in this field, covering milestones is not something out of this world. You really don't have to be an Einstein to become a world class Database Administrator in Oracle technologies.

True lots of skills are needed to be a world class DBA like having sound understanding of architecture and features of various releases of Oracle databases, dexterity in advanced technologies like RAC, ASM, Data Guard, RMAN, RAT etc. Also needed is solid foundation knowledge of various operating systems like Linux, Solaris, other flavors of Unix, Window. Networking and storage information is also needed. Besides of these hard skills, soft skills like communication, documentation, risk management, priority handling, team playing, solo flight, crisis management are also very much needed.

But they can be acquired, mastered and polished and improved. Learning is a life long experience and excelling is personal. To acquire and master all those skills, you don't have to be genius or super-person. You should just persevere, go through it, do it and keep doing it and one day you wake up with feeling that at last you know a little bit about this DBA thing, and then suddenly you realize that you know nothing.

Remember, the more you know , the more you realize how little you know.

The Oracle ACE Program Newsletter | May 2011

2011-05-25T19:53:00.000-07:00

They May 2011 edition if Oracle ACE Program newsletter is out and as usual it contains nicely summarized information about the activities performed by Oracle ACEs around the globe and the new entrants in this club.

You can subscribe to the Oracle newsletters here.

Lovely Oracle has been mentioned in this newsletter too, twice :)

awrgrpt.sql in Exadata

2011-05-23T03:01:00.000-07:00

Oracle RAC is just a part of Oracle Exadata, and if you need to have AWR reports generated from all the instances, which is often the case if you are working Oracle support, then you can use the awrgrpt.sql script. You just have to supply the starting/ending snap ids and the location, and just from one db node you can get the AWR report from all the instances. You can also get report from just one instance by using awrgrpti.sql script.

This newer awrgrpt.sql script has replaced the older server performance script the spawrrac.sql.

Exadata Patching Funfare

2011-05-19T20:35:00.000-07:00

Last weekend was a patching galore. Implemented one cell patch and two one-off patches to two Exadata machines. These were applied on top of Bundle Pack 9 (BP9) patch and more or less the patching was smooth, and it was good to have the world's top database appliance patched to the latest offered by the Oracle.

Will post very soon the tidbits about the cell and compute nodes patching.