Rich Headrick's Oracle blog

Blogging of all things Oracle…and especially Exadata

Ever log into cellcli and get no response from any commands, and get an error like this when you first log in?

CELL-01528: Unable to create the log file in directory /opt/oracle/cell11. Error: Couldn’t get lock for /opt/oracle/cell11.

Have a look at Oracle Doc ID 1338915.1. It says to restart all services, but I found that simply restarting MS worked.


August 26, 2013 Posted by | Exadata technical | Leave a comment

Exadata networking

It continues to amaze me how a company can spend big bucks on a very complex system such as an Oracle Exdata, and expect a single DBA to administer it.  In my opinion, it is at least a 2.5 person job:

1)  DBA to administer Oracle databases, ASM and grid infrastructure

2)  Systems administrator to maintain Linux/Solaris OS, users, local files systems, security, etc..

3)  Network administrator.  This is probably the most over looked responsibility.  Exadata networking is quite complex and requires a competent network guru.

November 21, 2012 Posted by | Uncategorized | Leave a comment

New book on SQLT

Congratulations to Stelios Charalambides on his new book about SQLT to be published.  You can pre-order here:
Carlos Sierra has done a masterful job in creating the tool and maintaining it through the years.  Although I haven’t seen Stelios’ book yet, my guess is that he will dissect SQLT and share some tips and tricks on how to get the most out it.

November 19, 2012 Posted by | Uncategorized | Leave a comment

How to create in 5 minutes a SQL Tuning Test Case using SQLTXPLAIN

Cool video from Carlos Sierra on using SQLT test cases.


How to create in 5 minutes a SQL Tuning Test Case using SQLTXPLAIN.

May 8, 2012 Posted by | Uncategorized | Leave a comment

Exadata Smart Flash Logging

I’ve had the privilege of applying BP12 and working with the new Smart Flash Logging feature.  As some of you may already know, this feature allows for asynch redo writes to both spinning disks and flash cache for redo writes.  The first write to complete wins and processing continues.  Some key things to know about Smart Flash Logging are:

  • You must be on cell patch
  • You must be using Oracle RDBMS BP11 or later although support for BP9 is there with a hidden parameter.
  • It is not a redo log mirror.  It only mirrors the tail end of the redo logs.  This allows for a small foot print at 512MB
  • It is not a read cache. Redo log reads are done solely from spinning disks.
  • Each 512MB log is divided into 32MB chunks across all 16 flash disks.
  • Is used as a circular buffer and reused after data is verified to have been written to disk.

cellcli syntax:

CREATE FLASHLOG [ALL [FLASHDISK]] [attribute=value] [,attribute=value] ...

LIST FLASHLOG [attibute_list] [DETAIL]



Note:  Attributes can be name, cellDisk, creationTime, degradedCelldisks, effectiveSize, efficiency, id, size, status

Note:  Using the FORCE option should be used with caution, and only if DROP FLASHLOG fails due to the existence of saved redo.


As you might imagine, there are also new metrics which can be viewed via cellcli LIST METRIC CURRENT where OBJECTTYPE=’FLASHLOLG’

If you are using this new feature, please do let us know about your experiences.



November 17, 2011 Posted by | Uncategorized | Leave a comment

Replacing an Exadata disk…things you need to check

I recently assisted a customer who had a predictive disk failure in his Exadata.  A new disk was sent out and the failing disk was removed and the new disk inserted.  The whole process should be as easy as that, but in this case it wasn’t.  The celldisks, and griddisks that were part of the old disk had been recreated automatically, but the newly created griddisks were not added back to the ASM diskgroup.  Working through Doc ID 1281395.1 for manually adding back celldisks and griddisks did the trick.  We only needed to modify the ASM diskgroups and add the disks back in as follows:

alter diskgroup DATA_DG01 add disk ‘o/*/DATA_DG01_CD_05_hr01cel02’ rebalance power 11;
alter diskgroup FRA_DG01 add disk ‘o/*/FRA_DG01_CD_05_hr01cel02’ rebalance power 11;
alter diskgroup SYSTEMDG add disk ‘o/*/SYSTEMDG_CD_05_hr01cel02’ rebalance power 11;

We did this on 3 separate ASM instances so the rebalance would not be serialized.  We also chose to do it at a time of day when database resource needs were the least.

Of course you should also check the progress of the rebalance by querying gv$asm_operation

November 14, 2011 Posted by | Exadata technical, RAC | Leave a comment

Pet peeves I have with housekeeping

I cannot stand it when people do not take the time to clean up old trace files, trim excessive alert logs, audit logs, cores, etc….

On a test server I just reviewed I found gigabytes of junk:

[oracle@testdb01 trace]$ pwd
[oracle@testdb01 trace]$ du -h .
1.3M    ./cdmp_20110819001755
1.4M    ./cdmp_20110911034245
1.6M    ./cdmp_20110929142949

I think some common sense housekeeping is in order(especially since the internal disk on Exadata db nodes are not particularly large).
[oracle@testdb01 trace]$ crontab -l
# General housekeeping
# Rotate the listener log
00 00 1 * * /usr/local/home/oracle/tools/lsnr_log_arch.ksh 2>&1
# Cleanup ASM trace files, core files and audit trails
13 02 * * * (cd /u01/app/oracle/diag/asm/+asm/+ASM1/trace && find . -name \*.trc -type f -mtime +90 -exec rm -f {} \;)
14 02 * * * (cd /u01/app/oracle/diag/asm/+asm/+ASM1/trace && find . -mtime +90 -type f -exec rm -f {} \;)
15 02 * * * (cd /u01/app/11.2.0/grid/rdbms/audit && find . -name \*.aud -type f -mtime +90 -exec rm -f {} \;)


This is by no means a complete housekeeping crontab, but it gives you the idea of what I’m talking about.  Do you really need trace files older than 90 days?  I’ve never seen a valid case for keeping them longer than a few weeks in my experiences.  Your audit requirements might prevent you from removing audit logs, but you could probably sweep them to an archive destination on an NFS mount or send them to tape  for longer term storage.

Just keep it clean.  When Oracle Support asks for you to upload your alert logs and trace files, I doubt they want ALL of your trace files in an 11GB hairball/tar ball.

November 8, 2011 Posted by | Uncategorized | 1 Comment

Expert Oracle Exadata….a must read.


If you haven’t yet purchased a copy of Expert Oracle Exadata, you are missing out. This is a well written book by Kerry Osborne, Randy Johnson and Tanel Poder with commentary from Kevin Closson. I’ve read most of it and find the content to be very relevant with excellent examples and even free scripts used throughout.

November 8, 2011 Posted by | Exadata technical | Leave a comment

crs_stat crsstat and crsctl

I’m still seeing a lot of folks using the Oracle supplied scripts for checking on the status of grid services.  They work well, but I find them difficult to read due to formatting:


[root@testdb01 richtmp]# crs_stat -t
Name           Type           Target    State     Host
 dbfs_mount     local_resource ONLINE    OFFLINE ora....t2.type ONLINE    OFFLINE
 ora.DATA.dg    ora....up.type ONLINE    ONLINE    richtmp02
 ora....ER.lsnr ONLINE    ONLINE    richtmp02
 ora....N1.lsnr ONLINE    ONLINE    richtmp02
 ora....N2.lsnr ONLINE    ONLINE    richtmp02
 ora....N3.lsnr ONLINE    ONLINE    richtmp02
 ora....DP.lsnr ONLINE    ONLINE    richtmp02
 ora.RECO.dg    ora....up.type ONLINE    ONLINE    richtmp02
 ora....EMDG.dg ora....up.type ONLINE    ONLINE    richtmp02
 ora....NG.lsnr ONLINE    ONLINE    richtmp02
 ora.asm        ora.asm.type   ONLINE    ONLINE    richtmp02
 ora....vc1.svc ora....ce.type ONLINE    OFFLINE
 ora....vc2.svc ora....ce.type ONLINE    OFFLINE
 ora.coe.db ONLINE    ONLINE    richtmp02
 ora.coedb.db ONLINE    OFFLINE
 ora.dbfs.db ONLINE    OFFLINE
 ora....svc.svc ora....ce.type ONLINE    OFFLINE
 ora.dbm.db ONLINE    OFFLINE ora....t1.type ONLINE    ONLINE    richtmp02
 ora....SM2.asm application    ONLINE    ONLINE    richtmp02
 ora....02.lsnr application    ONLINE    ONLINE    richtmp02
 ora....02.lsnr application    ONLINE    ONLINE    richtmp02
 ora....02.lsnr application    ONLINE    ONLINE    richtmp02
 ora....b02.gsd application    OFFLINE   OFFLINE
 ora....b02.ons application    ONLINE    ONLINE    richtmp02 ora....t1.type ONLINE    ONLINE    richtmp02
 ora.eons       ora.eons.type  OFFLINE   OFFLINE
 ora.gsd        ora.gsd.type   OFFLINE   OFFLINE ora....rk.type ONLINE    ONLINE    richtmp02 ora....rk.type ONLINE    ONLINE    richtmp02
 ora.oc4j       ora.oc4j.type  OFFLINE   OFFLINE
 ora.ons        ora.ons.type   ONLINE    ONLINE    richtmp02
 ora....ry.acfs ora....fs.type ONLINE    OFFLINE ONLINE    OFFLINE  ora....ip.type ONLINE    ONLINE    richtmp02  ora....ip.type ONLINE    ONLINE    richtmp02  ora....ip.type ONLINE    ONLINE    richtmp02

 Not very friendly is it?  Let's try something a bit better:

[root@testdb01 richtmp]# ./crsstat
HA Resource                                   Target     State
 -----------                                   ------     -----
 dbfs_mount                                    ONLINE     OFFLINE                        ONLINE     OFFLINE
 ora.DATA.dg                                   ONLINE     ONLINE on richtmp02
 ora.LISTENER.lsnr                             ONLINE     ONLINE on richtmp02
 ora.LISTENER_SCAN1.lsnr                       ONLINE     ONLINE on richtmp02
 ora.LISTENER_SCAN2.lsnr                       ONLINE     ONLINE on richtmp02
 ora.LISTENER_SCAN3.lsnr                       ONLINE     ONLINE on richtmp02
 ora.LISTENER_SDP.lsnr                         ONLINE     ONLINE on richtmp02
 ora.RECO.dg                                   ONLINE     ONLINE on richtmp02
 ora.SYSTEMDG.dg                               ONLINE     ONLINE on richtmp02
 ora.TESTING.lsnr                              ONLINE     ONLINE on richtmp02
 ora.asm                                       ONLINE     ONLINE on richtmp02
 ora.coe.coesvc1.svc                           ONLINE     OFFLINE
 ora.coe.coesvc2.svc                           ONLINE     OFFLINE
 ora.coe.db                                    ONLINE     ONLINE on richtmp02
 ora.coedb.db                                  ONLINE     OFFLINE
 ora.dbfs.db                                   ONLINE     OFFLINE
 ora.dbfs.dbfssvc.svc                          ONLINE     OFFLINE
 ora.dbm.db                                    ONLINE     OFFLINE                             ONLINE     ONLINE on richtmp02
 ora.richtmp02.ASM2.asm                        ONLINE     ONLINE on richtmp02
 ora.richtmp02.LISTENER_DMORLDB02.lsnr         ONLINE     ONLINE on richtmp02
 ora.richtmp02.LISTENER_SDP_DMORLDB02.lsnr     ONLINE     ONLINE on richtmp02
 ora.richtmp02.TESTING_DMORLDB02.lsnr          ONLINE     ONLINE on richtmp02
 ora.richtmp02.gsd                             OFFLINE    OFFLINE
 ora.richtmp02.ons                             ONLINE     ONLINE on richtmp02                             ONLINE     ONLINE on richtmp02
 ora.eons                                      OFFLINE    OFFLINE
 ora.gsd                                       OFFLINE    OFFLINE                              ONLINE     ONLINE on richtmp02                              ONLINE     ONLINE on richtmp02
 ora.oc4j                                      OFFLINE    OFFLINE
 ora.ons                                       ONLINE     ONLINE on richtmp02
 ora.registry.acfs                             ONLINE     OFFLINE                                   ONLINE     OFFLINE                                 ONLINE     ONLINE on richtmp02                                 ONLINE     ONLINE on richtmp02                                 ONLINE     ONLINE on richtmp02

This is much easier on the eyes.  Thanks to a simple script as follows:

[root@testdb01 richtmp]# cat crsstat
 # Sample 11g CRS resource status query script
 # Description:
 #    - Returns formatted version of crs_stat -t, in tabular
 #      format, with the complete rsc names and filtering keywords
 #   - The argument, $RSC_KEY, is optional and if passed to the script, will
 #     limit the output to HA resources whose names match $RSC_KEY.
 # Requirements:
 #   - $ORACLE_HOME should be set in your environment.  I'm using the ASM1 home/Grid Home for this.
 AWK=/usr/bin/awk    # if not available use /usr/xpg4/bin/awk
# Table header:echo ""
 $AWK \
 'BEGIN {printf "%-45s %-10s %-18s\n", "HA Resource", "Target", "State";
 printf "%-45s %-10s %-18s\n", "-----------", "------", "-----";}'

# Table body:
$ORACLE_HOME/bin/crs_stat $QSTAT | $AWK \
‘BEGIN { FS=”=”; state = 0; }
$1~/NAME/ && $2~/’$RSC_KEY’/ {appname = $2; state=1};
state == 0 {next;}
$1~/TARGET/ && state == 1 {apptarget = $2; state=2;}
$1~/STATE/ && state == 2 {appstate = $2; state=3;}
state == 3 {printf “%-45s %-10s %-18s\n”, appname, apptarget, appstate; state=0;}’

Do your comrades a favor and provide them useful and readable output from your commands.  Formatting is important.  Don’t forget to use a fixed font like courier new also.

October 31, 2011 Posted by | RAC | Leave a comment

Exadata and non-RAC

I have a client who is choosing to not use RAC on their Exadata system. They have asked what should be done with the first 2 cell disks, which are smaller than the other 10 on each cell. I advised that they should recreate the celldisks to be of equal sizes for balance.

You just never know what might happen on an Exadata system.

October 30, 2011 Posted by | Uncategorized | | 2 Comments