OraNA :: Oracle News Aggregator

With the announcement Exadata X3, Oracle has introduced a new feature called “FlashCache Writeback” to allow writes to cell Flash Cache (aka Exadata Smart FlashCache) in WriteBack mode. Earlier with WriteThrough mode, writes were not written to FlashCache, instead they were written directly to cell disks. Exadata software used to decide whether to cache these writes back into FlashCache or not. In WriteBack mode, writes are written to cell FlashCache and acknowledgement is given back to calling process as soon as data is written to flashcache. Exadata Server software de-stages the dirty writes in flashcache to spinning disks in the (more...)

I learned something new today, so thought i would share on my blog here. Did you know that you can specify multiple tnsalias (or net service name link , as Oracle documentation likes to call it) for a single entry. Its not documented as is the case with many cool hidden features. I searched Oracle documentation and Oracle Support site, but could not find any reference to this feature. Google gave this link to Quest’s toad bug with search string of “comma separated tns alias”.

Usually tns entry is made in tnsnames.ora file as per below syntax (in its (more...)

Earlier i used to maintain different sql script files for one function for every database version. As some of the older release dont have some of columns available in new release’s view/tables. I started updating my scripts to maintain a single script using Tanel Poder’s  snapper script’s idea of using SQL*Plus variables to automatically comment out the version specific code in the script (if its not applicable for logged in database version), so that it does not generate the syntax error.

So initialize the variables (_IF_ORA_9iR2_OR_HIGHER, _IF_ORA_10gR1_OR_HIGHER etc) with single line comment “–” and if version of logged in database (more...)

After patching Exadata cell to 11.2.2.4.0, on running CheckHWnFwProfile or Exachk it reports the firmware version for Flash Card is not same as expected.

# /opt/oracle.SupportTools/CheckHWnFWProfile
[WARNING] The hardware and firmware are not supported. See details below

[PCISlot:HBA:LSIModel:LSIhw:MPThw:LSIfw:MPTBios:DOM:OSDevice:DOMMake:DOMModel:DOMfw:CountAuraCountDOM]
Requires:
   AllSlots_AllHBAs SAS1068E B3orC0 105 011b5c00 06.26.00.00 AllDOMs_NotApplicable MARVELL SD88SA02 D20Y 4_16
Found:
   AllSlots_AllHBAs SAS1068E B3orC0 105 011b5b00 06.26.00.00 AllDOMs_NotApplicable MARVELL SD88SA02 D20Y 4_16

[WARNING] The hardware and firmware are not supported. See details above

CheckHWnFWProfile script is expecting firmware version to be 011b5c00 where it is found to be 011b5b00 (more...)

Following issue has been identified in Exadata Storage Server software patch 11.2.2.4.0. It only affects compute node minimal pack on X2-2 and X2-8 racks sporting a 10GigE network ports. It does not affect the V1 or V2 models as they dont have 10GigE network ports. For more information please refer to MOS Note 1348647.1

Critical Issues Discovered Post-Release

1) Bug 13083530 - 10Gb Ethernet network interfaces shut down unexpectedly.

For environments configured with 10GigE Ethernet for the database server hosts running Oracle Linux, do NOT apply the 11.2.2.4 minimal pack to the (more...)

When you have a Exadata machine in the lab and you are testing lot of different things or giving hand-ons training to production DBA on lab Exadata to familiarize them with Exadata patching, one has to frequently start from scratch i.e . a particular storage/compute node image. Wouldn’t it be nice if you could write a script to re-image the servers? But alas!!! that is not possible with Exadata. One has to do all the reimaging manually. Even with the manual process, one has to insert the external USB into each storage/compute node and then remote it before reboot to (more...)

Non-interactive shell issue for Database Host minimal pack 

Recently i set about patching Exadata Storage Server software to from 11.2.2.x.x to 11.2.2.4.0, which is the latest patch from Oracle Corporation. I was testing and documenting the process for one of my client and wanted to automate this as much as possible, as in past people actually executing the commands had missed running few commands on certain nodes. As with any Exadata storage server software patch, there is cell node component of patch which is patched using patchmgr either in rolling or non-rolling (more...)

As  most of you are aware, Oracle does not publish the Exadata documentation. Apparently this is protect their software from competitors. But in my opinion, Oracle could increase their Exadata sales by making their documentation freely available for everyone to read, just like their other product’s documentation.  Wouldn’t it be nice, when someone asks you regarding a feature of Exadata, if you had the ability to direct them to a link of Exadata documentation for that feature and they could be read at their leisure in detail.

I feel if more and more people know about Exadata, they would be (more...)

Just a quick note to say that RedHat submitted certification test result of Oracle 11gR2 on RHEL6 to Oracle Corporation on 09-Aug-2011, so we should expect the formal certification around last week of Sep-2011.

More news at - http://www.redhat.com/about/news/blog/Red-Hat-Submits-Oracle-11gR2-on-Red-Hat-Enterprise-Linux-6-Certification-Test-Results-to-Oracle

[Update: 17-Apr-2012]

Oracle has finally certified 11gR2 on OEL6 and RHEL6. See the announcement here

In 10g, serial full table scans for “large” tables used to always go through cache (by default). If table was small it was placed at most recently used (MRU) end of the buffer cache. If table is large it is placed at least recently used (LRU) end of the buffer cache.

In 11g, full table scan do not always go through the buffer cache. Decision to read via direct path or through cache is based on the size of the table, buffer cache and various other stats. Table is considered to be small or large based value of _small_table_threshold (more...)

I was going to write a new post about change in behaviour of Guaranteed Restore Point and Flashback mode. But looks like someone has already written about it at given below link. Its seems behaviour has changed in 11gR2, but documentation still refers to old behaviour.

http://asanga-pradeep.blogspot.com/2011/06/restore-point-prerequisites-change-in.html

I have raised a Oracle Service Request to correct the documentation. Documentation Bug – 12763133.

Oracle documentation states that you can not use the FLASHBACK database clause (i.e turn it ON or OFF) while database is OPEN. Flashback mode can be turned ON only when database is in MOUNT mode. This is true in Oracle version 10.1, 10.2, 11.1. But in 11gR2, Oracle actually allows you to turn on the flashback even in OPEN mode, even though documentation says otherwise. I have raised an Oracle Service Request with Oracle to get this documentation bug fixed.

[Update 19-Jul-2011]

Following bug has been raised by Oracle Support.

Bug 12768808: FLASHBACK DATABASE DO NOT (more...)

This afternoon on a Oracle 11.2.0.2 installation, i was trying to run the Oracle RMAN utility. Rman cli was just hanging and not going into RMAN.

[oracle@linux2 ~]$ . oraenv
ORACLE_SID = [oracle] ? 11202
The Oracle base has been set to /opt/oracle/product/rhel4/database/11.2.0.2
[oracle@linux2 ~]$ rman
 

After entering above rman command, nothing happened. It just hung on the command prompt. I did not receive any error at all. Hmm… whats happening? I issued an CTRL-C to cancel it and tried again. Still same result. Okay lets trace it using Linux strace utility.

oracle@linux2 ~]$ strace rman
 (more...)

Many times i find myself comparing various Oracle Exadata models. It becomes tedious to compare various data sheets for various Exadata models. So i have put together this spreadsheet (Exadata Model Comparison) for comparing various models in one glance.

It gives following details and more.

• At Exadata level,

• number/model of compute nodes and cell nodes.
• number of Infiniband switches and their port count.
• Key capabilities ( as per data sheets)

• Server Model
• CPU Type, CPU Speed, CPU Cores, CPU Threads
• RAM
• Network ports – type and count
• Flash RAM (Smart (more...)

If you have ever wondered what are the various predefined internal Oracle system schemas, you can find the list of such accounts and their use in Oracle® Database 2 Day + Security Guide
11g Release 2 (11.2) at this link. This list comes in handy, when you want to do some analysis on database objects, but want to exclude all internal Oracle provided system accounts and want to focus only on application level accounts.

Here is the list of schemas:-

ANONYMOUS
APEX_PUBLIC_USER
CTXSYS
DBSNMP
DIP
EXFSYS
FLOWS_%
FLOWS_FILES
LBACSYS
MDDATA
MDSYS
MGMT_VIEW
OLAPSYS
ORACLE_OCM
ORDDATA
ORDPLUGINS
ORDSYS
OUTLN
 (more...)

RHEL 5.3 x64 disks

On a RHEL OS using LVM volume manager, you can extend the underlying disk and its associated filesytem (non-root) can be extended while its mounted.

• Extend the underlying virtual/SAN disks.

• Check the size of disk

#   fdisk -l

• Force the re-scan of disk, so that OS kernel can see the new size

# echo 1 > /sys/block/sdb/device/rescan

where disk extended was /dev/sdb

• Extend LVM physical volume size.

# pvs

# pvresize /dev/sdb

# pvs

• Extend LVM logical volume

# lvdisplay

# lvextend /dev/ora_vg/ora /dev/sdb

# lvdisplay

• Extend filesystem (online)

# df –h

# (more...)

Normally in RAC, database instance spfile file is stored on ASM so that parameters remain consistent across the instance and all instance use the same spfile.

For this sometimes init.ora has following parameter

# cat $ORACLE_HOME/dbs/init<ORACLE_SID>.ora
SPFILE='+DATA/ORACLESID/spfileORACLESID.ora'
 

Now if you want to move spfile between ASM and filesystem, here how you can do it.

From ASM -> Filesystem

</span>
<pre>SQL> CREATE pfile='/tmp/initORACLESID.ora' from spfile='+DATA/ORACLESID/spfileORACLESID.ora';
SQL> CREATE SPFILE='/tmp/spfileORACLESID.ora' from pfile='/tmp/initORACLESID.ora';</pre>
<span style="font-family: Times New Roman; font-size: small;"> 

From Filesystem -> ASM

</span>
<pre>SQL> CREATE pfile='/tmp/initORACLESID.ora' from spfile='$ORACLE_HOME/dbs/spfileORACLESID.ora';
SQL> CREATE SPFILE='+DATA/ORACLESID/spfileORACLESID.ora' from pfile='/tmp/initORACLESID.ora';</pre>

Normally DBAs and SAs have a tendency to create the auto start/stop scripts like following.

# cat /etc/init.d/oracle
case $1 in
start)
 /opt/oracle/product/11.1.0/bin/dbstart;
 ;;
stop)
 /opt/oracle/product/11.1.0/bin/dbshut;
 ;;
esac

# ln -s /etc/init.d/Oracle /etc/rc.d/rc0.d/K01oracle
# ln -s /etc/init.d/Oracle /etc/rc.d/rc3.d/S99oracle
# ln -s /etc/init.d/Oracle /etc/rc.d/rc4.d/S99oracle
# ln -s /etc/init.d/Oracle /etc/rc.d/rc5.d/S99oracle
# ln -s /etc/init.d/Oracle /etc/rc.d/rc6.d/K01oracle

This works fine on non-Linux operating systems. But on Linux operating system, there is an requirement to for start/stop script to create/remove a /var/lock/subsys/ file, where is (more...)

In case you wanted to know which Oracle version a particular SQL hint is applicable in or was introduced in. You can query V$SQL_HINT introduced in 11g for that. It holds even historical information. Column “version” gives oracle version in which a particular hint was introduced and version_outline probably gives upto which version upto which it is applicable.

[Update:2009-05-03 : Jonathan Lewis mentioned in a email to me that version_outline gives version in which a particular hint can be used in an outline. ]

This even has a column to give you inverse of an hint.

(more...)

Here are some interesting articles regarding log file writing and associated I/O waits.

Jonanthan Lewis – Log File Write

Riyaj Shamsudeen – Log file sync tuning

Christian Bilien – Log file sync wait