Invisible Indexes in Oracle 11g


Always wanted this – the ability to create an index on production without impacting the queries being fired by application but at the same time test the impact an index creation can cause. Invisible indexes are useful alternative to making an index unusable or to drop it. 
The optimizer ignores the index that are marked “Invisible” unless you set the initialization parameter “OPTIMIZE_USE_INVISIBLE_INDEXES” to TRUE. This parameter can be set both at a session level as well as system level.
Usage of Invisible Indexes
One can use invisible index for testing the impact of removing an index. Instead of (more...)

Oracle Database Firewall – What is it?


It all started with acquisition of Secerno, a database firewall vendor, in 2010. Secerno's product “DataWall” helped analyze how databases are accessed so that DBA’s can set up policies to control the access.
The database firewall has the ability to analyze SQL statements sent from database clients and determine whether to pass, block, log, alert, or substitute SQL statements, based on a defined policy. Users can set whitelist and blacklist policies to control the firewall. It can detect injected SQL’s and block them.  According to Oracle, the database firewall can do the following -

Pre-packaged Oracle VM’s for Developers

Interesting. I remember that in order to make development effort faster, we used to make images of our development boxes with all the required software / applications installed on it. This was pre-virtualization era. But with virtualization, it became a common norm and easier. Now Oracle has also started packaging different development stack on a pre-built Oracle VM which one can simply download and start using it.

All you need to do is install VirtualBox to get these pre-built VM's working. Currently Oracle has the following development stacks bundled in a VM with more coming in future -

Oracle Pre-upgrade utility


The Pre-upgrade tool provides a list of items which should be reviewed before upgrading the database (just like a pre-requisites checklist). Basically it reports about the database configuration and parameters etc. that need attention prior to upgrade. The best thing is this script can be run while the database is running on the existing version that means no shutdown required.  This allows you to properly plan your upgrade process and avoid unnecessary down time due to pre-requisites missed for the upgrade.
Note: - A few registry$ tables will be created and data would be inserted into them.
The snapshot (more...)

Database Upgrade Guide – 10g to 11g


I came across this useful upgrade advisor/guide on Metalink(ID 251.1) so thought I should share this. I think it was available earlier as well but in some crude format. It’s a nice step-by-step guide / reference for anyone who wants to upgrade to 11g. It explains you the benefits of 11g and guides you through a 6-step approach (Evaluate, Plan, Configure, Test, Implement and Accept) to get to 11g. It explains each phase with expected deliverables/outcomes and lists a host of referenceable material – documents / guides, ppts, multimedia trainings, metalink notes etc. one can refer to.

Virtathon – A virtual conference for Oracle community


BrainSurface is organizing an online, virtual conference for the Oracle community called “VirtaThon” which includes Java and MySQL communities as well. I like the idea of online and virtual conference since personally I have not attended many conferences like Oracle Open World or IOUG etc. being in a part of the world where such conferences happen very less. I have always rued that I haven’t been able to attend such conferences organized for my community but now I’m very excited and looking forward to participate in VirtaThon.
I feel this is definitely a great way to learn about (more...)

Pre-packaged Oracle VM Images for Oracle Database and RAC


Oracle has made available pre-configured virtual machines containing pre-installed Oracle enterprise software stacks. These Oracle VM templates can be downloaded from Oracle's E-Delivery site.
Pre-packaged VM’s would greatly useful if you need to quickly test out or do a POC etc. You would anyways download the software so why not download a pre-installed VM image and get started immediately as opposed to going through painstaking process of installing it unless your POC is about installation and configuration.
All you would need is Oracle VM already installed on your server/desktop. Then you can simply download the VM images, import and deploy (more...)

RAC One Node changes in 11.2.0.2

I have covered Oracle RAC One Node in one of my previous post. RAC One Node is a single instance of an Oracle RAC database running on node in a cluster. There have been significant changes in the way a RAC One database is administered in version 11.2.0.2 compared to earlier versions. I have briefly summarized the changes herein –
  • OUI has a new option to select RAC One Installation (look at the screenshot below)
  • You can now create and configure RAC One database using DBCA
  • Configure and administer RAC One database using SRVCTL instead of using  (more...)

Oracle Database on Amazon Cloud – Now Available


Amazon has kept its promise of making Oracle 11g available on per hour billing. Finally, after its announcement in Feb 2011, Oracle 11g database is now available on Amazon RDS. Which means you can use Oracle database on cloud with the same per hour billing rates (pay-as-you-go model) you pay for other resources.
Amazon RDS brings Oracle Standard One, Standard and Enterprise editions on RDS. It’s now the 2nd database to be available on RDS; MySQL was available for quite some time now. As I had mentioned in my earlier post, Oracle will be available under 2 difference (more...)

How to Configure Oracle Restart on Standalone Server


In one of my previous post, I had talked about Oracle Restart – a new feature in 11gR2 that enhances availability in case of single/standalone instances. In this post, I am briefly describing the steps to install and use this great feature for an existing database installation.
Oracle Restart is part of Oracle Grid Infrastructure which needs to be installed without which Oracle Restart cannot be used. One either installs Grid infrastructure first and database later or vice-versa. The difference being that the components either gets automatically added to Oracle restart configuration (if Grid is installed first) or need (more...)

crs_stat deprecated in 11gR2

The most used to command "crs_stat -t" is gone (deprecated) on 11gR2. Instead you would use - 

crsctl status resource [the output will be status of all resources]

You can still use the "-t" option to get a tabular report though ;)

crsctl status resource -t

Specify resource name "ora.mydb.vip" to get the status of any particular resource -
crsctl status resource ora.mydb.vip

In fact, quite a few commands have been deprecated in 11gR2; For the complete list refer to http://download.oracle.com/docs/cd/E11882_01/rac.112/e16794/crsref.htm#CHDHBECE

Instance Caging

Many of you would already be aware of this one while I discover it now but still putting down my thoughts on this 11g new feature.
Most of the times, we end up running multiple instances on a single box for developmental effort. Or even on Production by buying a reasonably big box to save on licensing cost and also in the name of consolidation exercise :). But doing so, throws up the challenge for a DBA in terms of allocating resources to each instance. Some low priority activity on an instance eating up CPU resources and thus depriving the (more...)

Oracle Restart – a 11g New Feature

Oracle Restart is a new feature introduced to enhance the availability of Oracle database. I wasn't aware of this till recently. It basically allows various components of Oracle to restart automatically in a stand-alone (non-clustered) installation. This functionality is similar to what Clusterware does in a RAC setup. On Windows and Linux, we were able to achieve all this via creating a service or writing a script. But now it’s not required. So in a way, it also makes the age old practice of writing scripts to restart redundant.

Oracle instances and its dependent components restart automatically after any hardware (more...)

Oracle Database on Amazon RDS

Recently, Amazon and Oracle announced that they are going to make "Oracle 11g Database" available on Amazon AWS as a service. It's being brought into AWS's RDS feature which currently offers "MySQL database as a service".

Amazon RDS is a web service that allows you to set up, operate, and scale a relational database in the cloud. You can provision a relational database (currently only MySQL) on RDS in just a few minutes. Amazon RDS will also manage database administration tasks including continuous backups, software patching etc.
When launched (sometime in Q2 of 2011), you will have the option to (more...)

Edition based redefinition


Oracle 11g brought in a new feature called Edition based redefinition (EBS) basically aimed at reducing the planned downtime during application releases/upgrades etc. In this changing and dynamic world, application also undergoes numerous enhancements; it’s in these situations that one cannot afford to take a downtime and looks for options to minimize or eliminate them. While the application code and the software provide some options, the Oracle database engine had no such option.  

Till recently, Oracle database had only few options – such as online index rebuild and table redefinition to address planned downtime [Also has Workspace manager; Thanks (more...)

Oracle Critical Patch Update – April 2010


The 2nd critical patch update for 2010 (CPU April 2010) has been released by Oracle yesterday. Critical patch updates mostly contain fixes to security vulnerabilities plus it would have non-security fixes too. This critical patch update contains 47 new security fixes across all products which includes 7 for Oracle database. For the first time, CPU includes patches/fixes for Sun products. Please review the following URL to see if the product you are using requires this patch or not.


The next critical patch update (CPU) would be coming in July 2010.


Oracle RAC One Node – What is it?


I am reviving my blog after a long time. Hope I will be much regular. Some of my posts have been popular and received good feedback. Thanks to all of you who visited this blog and I am glad that the information has been useful.

I was looking for some information on Oracle RAC One node, early this year and did not find much. I thought to do a post on my views/opinion on this latest option introduced by Oracle. I am yet to get hands-on with this option. Please leave your comments/feedback if you have different opinion/experience with this (more...)

Future of Relational Database

I read an interesting article on RWW about future of relational database; thought it might be of interest to you too!

This article talks about the emerging database (key/value database) and compares it to RDBMS. One of the interesting things being that you may not be able to perform JOIN operation. It is being described as the suitable model for cloud service provides (and pay-as-you-go service providers) and big players like Amazon (SimpleDB), Google (AppEngine Datastore), Microsoft (SQL Data services) have already started the offering. There are non-cloud providers too like - CouchDB (more...)

Oracle Critical Patch Update – October 2008

Oracle released critical patch update for October 2008 yesterday; this is the last CPU for 2008. There have been 36 new fixes across all products including 15 new security fixes for database products. Please review the following URL to see if the product you are using requires this patch or not.
http://www.oracle.com/technology/deploy/security/critical-patch-updates/cpuoct2008.html

You can also refer to MetaLink Note 735216.1 for more details on database fixes.

Oracle announces Storage server and Database machine

SAN FRANCISCO - SEPTEMBER 24:  The new Oracle ...Larry Ellison announced Oracle's foray into hardware arena with the launch of storage server and database machine at the ongoing Oracle Open World 2008 in his keynote address. Oracle has partnered with HP for this. Read the press release.

Oracle has announced a new storage server called "Oracle Exadata" and a database machine partnering with HP. The database machine is named "HP Oracle Database Machine". It's a pre-configured machine with Oracle Enterprise Linux consisting 8 database servers, 64 Intel processor cores, grid of 14 Oracle Exadata storage servers,. It's specially been designed for data warehousing market. It's being seen as (more...)