Yet another DBA blog

Invisible Indexes in Oracle 11g

noreply@blogger.com (Unknown) — Tue, 30 Aug 2011 10:56:00 +0000

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 dropping the index we can make it invisible and its effect.

One can speed up operations by creating invisible indexes for infrequent scenarios. Invisible index will make sure that the overall performance of the application is not affected.

Gives you the flexibility to have both b-tree (to guarantee unique PK) as well as bitmap indexes (on FK columns) in a data warehouse application.

How to create?

Multiple options – either mention “INVISIBLE” clause at the time of index creation or use ALTER command to make an index “INVISIBLE”.

CREATE INDEX emp_ename ON emp(ename)

TABLESPACE users

STORAGE (INITIAL 20K

NEXT 20k

PCTINCREASE 75)

INVISIBLE;

ALTER INDEX index INVISIBLE;

To make the Index “VISIBLE”

ALTER INDEX index VISIBLE;

A new column “VISIBILITY” is available in *_INDEXES data dictionary views to know if an index is visible or invisible.

Example

SQL> create index indx_job on emp1(job);

Index created.

SQL> explain plan for select * from emp1 where job='CLERK';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3449298850

----------------------------------------------------------------------------------------
| Id | Operation                   | Name     | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     4 |   348 |     2   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| EMP1     |     4 |   348 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | INDX_JOB |     4 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

SQL> explain plan for select * from emp1 where job='CLERK';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2226897347

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     4 |   348 |     3   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP1 |     4 |   348 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL> ALTER SESSION SET optimizer_use_invisible_indexes=TRUE;

Session altered.

SQL> select index_name,visibility from user_indexes where table_name='EMP1';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
INDX_JOB                       INVISIBLE

SQL> explain plan for select * from emp1 where job='CLERK';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3449298850

----------------------------------------------------------------------------------------
| Id | Operation                   | Name     | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     4 |   348 |     2   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| EMP1     |     4 |   348 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | INDX_JOB |     4 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Oracle Database Firewall - What is it?

noreply@blogger.com (Unknown) — Fri, 22 Jul 2011 10:18:00 +0000

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 -

Monitors and blocks SQL traffic on the network with white list, black list and exception list policies
Protects against application bypass, SQL injection and similar threats
Reports on database activity for SOX, PCI, HIPAA and other regulations, choosing from dozens of out-of-the-box reports
Supports other Databases as well - MS SQL Server, IBM DB2 , and Sybase

The Database Firewall joins other database-security products offered by Oracle such as Oracle Advanced Security, Audit Vault, Database Vault, Secure backup etc.

Oracle Database firewall comes in 2 components:-

Database Firewall:

Record and analyze SQL transaction requests and responses from one or more Oracle, Microsoft SQL Server, or Sybase databases, and Sybase SQL Anywhere.
Categorizes SQL transactions
Enforces data policies
Enables real-time alerting and event propagation

Database Firewall Management Server:

Aggregates SQL data from one or more Database Firewalls
Serves as a reporting platform for business reports
Centralizes the distribution of data control policies, but still enables the use of different policies for specific databases
Stores and manages log files, including archiving and restoring the log files
Remotely manages all Database Firewalls to which it connects
Integrates with third-party applications, such as Crystal Reports

However there are some key issues that it does not address and hence would need use of other security options such as Audit Vault, VPD etc. For example, Privileged users can login to the OS directly and make local connections to the database. This bypasses the database firewall.

Pricing

The two components are priced separately. The Database Firewall comes at a cost of $5,000 per processor and Database Firewall Management Server component is priced at $57,500 per processor.

Pre-packaged Oracle VM's for Developers

noreply@blogger.com (Unknown) — Thu, 14 Jul 2011 10:44:00 +0000

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 -

Java Development
Database App Development
SOA & BPM Development
Oracle WebLogic Server Hands-on
Oracle WebCenter Portal Framework 11g Hands-on
Oracle Solaris 11 Express Developer
Oracle Solaris 11 Express Network Virtualization
Oracle Solaris 10 9/10
Enterprise PHP Development
Oracle Tuxedo Web Application Server Demo

Refer to the following link for components that have been bundled in the above listed VM's and download them. Go download and speed up your development effort.

http://www.oracle.com/technetwork/community/developer-vm/index.html

In a previous post, I had covered the Oracle VM images with pre-installed Oracle Database (10g and 11g) and Oracle RAC (10g, 11g, and 11gR2) software.

Oracle Pre-upgrade utility

noreply@blogger.com (Unknown) — Fri, 08 Jul 2011 05:33:00 +0000

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 (taken from Metalink note 884522.1) explains which script should be used based on the version you are on and the version you are intending to upgrade to. You can download the scripts from the same metalink note as well.

You can find these scripts under $ORACLE_HOME/rdbms/admin directory of the version you are planning to upgrade to.

The following snapshot gives a sample output of the script executed on a 10g database.

While you will refer to an upgrade guide / companion available from Oracle to note down all the pre-requisite steps and get them rectified. This script gives you a consolidated output of the pre-requisites and one can fix and re-run the script to check if it complies with most.

Database Upgrade Guide – 10g to 11g

noreply@blogger.com (Unknown) — Tue, 05 Jul 2011 10:19:00 +0000

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.

It’s very handy guide for anyone who wants to migrate from 10g to 11gR2.

Virtathon - A virtual conference for Oracle community

noreply@blogger.com (Unknown) — Thu, 23 Jun 2011 06:42:00 +0000

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 the latest and listen to experts in your domain. And the best part is that it’s FREE. So just go and register yourself for VirtaThon.

Dates – 16^th to 21^st July

Venue – Online

Cost – Registration is Free

Where to Register – http://www.brainsurface.com/virtathon/sign-up

Speakers (names listed in alphabetical order) – Arup Nanda, Brian Huff, Colin Charles, Dan Hotka, Dario Laverde, David Koelle, Dr. Bert Scalzo, Guy Harrison, Lewis Cunningham, Matt Warman, Mike Ault, Riyaj Shamsudeen, Syed Jaffar Hussain, Tariq Farooq, Vinod Haval

Topics being covered – I’m listing only the topics that would be of interest for a DBA.

Oracle (Database, Cloud Computing, Virtualization, Oracle Linux, Data Modeling and Exadata etc.)
MySQL (Replication and Scale out, High availability and Clustering, Enterprise database administration and Security, Database Performance Tuning, Storage Engine Development and Optimization, Partition Strategies, Cloud computing etc.)
Java (details can be found on the VirtaThon site)

Being a virtual conference, you don't have to travel anywhere to attend the sessions; you can simply attend from the comfort of your home or office. This is not a webcasts rather much like normal conferences you can participate by asking questions, interact via chat and have follow ups after talks. All you need is a computer with an internet connection.

Request all to participate, contribute and learn from this conference. As far as I know, this is one of the first Oracle-centric virtual conference. Look forward to more such online oriented conferences. Great effort and move by BrainSurface; Hats off to entire group and Tariq Farooq - the main man behind Brainsurface and this conference.

Pre-packaged Oracle VM Images for Oracle Database and RAC

noreply@blogger.com (Unknown) — Mon, 20 Jun 2011 05:25:00 +0000

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 the template VM’s. However you do need to provide some basic information such IP (DHCP or static), passwords etc. and you will have a fully installed / configured Oracle environment ready without having to install products from scratch.

Currently, I could find the following templates for Oracle Database and RAC on both 32-bit and 64-bit (x86 platforms). All of these are based on Oracle Enterprise Linux version 5.2 onward. No pre-packaged VM’s are available for Windows or other UNIX flavors.

Database VM Templates for x86 (32-bit)

URL - https://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=10551276

RAC VM templates for x86 (32-bit)

URL - https://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=12356456

RAC 11gR2 Templates for x86 (32 bit)

URL - https://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=12918773

Database VM Templates for x86 (64-bit)

URL - https://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=10551278

RAC VM templates for x86 (64-bit)

URL - https://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=12356458

RAC 11gR2 Templates for x86 (64 bit)

URL - https://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=12918775

There are pre-installed/configured VM’s for other products as well e.g. Grid control, Fusion middle-ware, E-business suite etc. Get them @ Oracle E Delivery - https://edelivery.oracle.com/EPD/GetUserInfo/get_form?caller=LinuxWelcome

RAC One Node changes in 11.2.0.2

noreply@blogger.com (Unknown) — Thu, 16 Jun 2011 09:11:00 +0000

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 "Omotion", "raconestatus", "raconeinit", "racone2rac" etc.

So move/relocate and convert your database using SRVCTL

Dataguard Broker is RAC One aware

I thought to quickly cover the changes since I had previously posted about RAC One. You can also refer to Metalink note 1232802.1 for details.

Oracle Database on Amazon Cloud - Now Available

noreply@blogger.com (Unknown) — Wed, 25 May 2011 11:23:00 +0000

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 2^nd 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 licensing scheme –

BYOL (Bring Your Own License) – Basically allows you to bring and reuse your existing licenses. Start from $0.11 per hour, this is mainly for the underlying hardware.
License Included – No upfront investment/commitment. Start from $0.16 per hour, this includes underlying hardware and oracle database license cost.

Important to note that only “Standard Edition One” is available under “License included” model which means if you need Enterprise features then you need to go via BYOL which has upfront cost.

Options you see on launching a database on Amazon RDS -

In terms of features, you need not worry about general DBA tasks, backup, patching, monitoring (using CloudWatch) etc. All these tasks will be Amazon’s responsibility. As per the Amazon site, parameter control is available via DB Parameter but on my first glance couldn’t find any editable parameters (maybe I’m missing something). Replication (similar to what’s available with MySQL) is not yet ready. What I liked is that there is no additional charge for backup storage up to 100% of your provisioned database storage for an active DB Instance. You have to pay for it as soon as you terminate the database instance.

Currently only one version, Oracle 11.2.0.2, is available. It remains to be seen on the choice of version that will be made available in future.

Refer to Amazon RDS site for more details around cost and features.

How to Configure Oracle Restart on Standalone Server

noreply@blogger.com (Unknown) — Wed, 11 May 2011 05:37:00 +0000

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 to be manually added (In case DB is installed first and Grid later).

The Oracle grid infrastructure for a standalone server is the Oracle software that provides system support for an Oracle database including volume management, file system, and automatic restart capabilities. Basically it combines “Oracle Restart” and “ASM” into Grid binaries. So, to use Oracle Restart or ASM, installing grid infrastructure is a MUST (which of course I don’t like). Is there license implication?

Further, Oracle Restart can only manage 11.2 resources. However, Oracle database releases prior to 11.2 can coexist on the same server but without being managed by Oracle Restart.

Is it a separate binary?

Yes, a separate binary available for download - http://download.oracle.com/otn/linux/oracle11g/R2/linux_11gR2_grid.zip

However if you download the latest version 11.2.0.2 (from metalink) then you do not need to download the above. Grid is part of the installable. What I don’t like is installing Grid even for “Oracle Restart” only.

How to install?

There are 2 options. When you start installing the grid, it throws the following screen; if you just want to install Oracle Restart then choose “Install Grid Infrastructure Software Only” else choose “Install and Configure Grid Infrastructure for a Standalone Server” which will ask for ASM configuration details.Ensure that the grid infrastructure components are installed in a separate Oracle home.

To continue the installation, just follow the screenshots below; the above screenshot is the first step when you run "./runInstaller" from installable folder.

At the end of the installation you are required to run the “root.sh” script to successfully complete the installation. Please note that you need to run the script as “root” user.

#/u01/app2/product/11.2.0/grid/root.sh

What do I need to Configure?

Next is to run “roothas.pl” script to ensure to configure Grid Infrastructure for a stand-alone server. Run the following command as the root user:

#/u01/app2/product/11.2.0/grid/perl/bin/perl -I/u01/app2/product/11.2.0/grid/perl/lib -I/u01/app2/product/11.2.0/grid/crs/install /u01/app2/product/11.2.0/grid/crs/install/roothas.pl

One last step before we can term “Oracle Restart” configuration to be complete and add components to it.

# cd $ORA_GRID_HOME/bin

# crsctl enable has

So that’s it! “Oracle Restart” is now configured on your standalone server.

How to add components to Oracle Restart

1. Add the existing database to “Oracle Restart”

$ srvctl add database –d -o <$ORACLE_HOME> -p -s -t

$ srvctl add database -d testdb -o /u01/app/oracle/product/11.2.0/dbhome_1

2. Add listener

$ srvctl add listener –l LISTENER -o /u01/app/oracle/product/11.2.0/dbhome_1

Can I turn off Oracle Restart?

Yes; first check the current status of auto start and then simply run the command (as “root” user) to turn off the autostart option.

#crsctl config has

CRS-4622: Oracle High Availability Services autostart is enabled.

#crsctl disable has

CRS-4621: Oracle High Availability Services autostart is disabled.

Conclusion

That completes the Oracle restart installation and configuration.It's nice feature to have on a standalone instance/server. The best part is the manual procedures we used to adopt to start/stop all components of database and the shell scripting required is gone. And of course, get braced up for they way things work in a RAC environment. Let me know your feedback.

crs_stat deprecated in 11gR2

noreply@blogger.com (Unknown) — Thu, 28 Apr 2011 12:05:00 +0000

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

noreply@blogger.com (Unknown) — Wed, 20 Apr 2011 14:43:00 +0000

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 much needed resource to those requiring it. CPU allocation decisions such as this are made solely by the operating system; the user generally has no control over them.

Instance caging in 11gR2 is a method to cage or bound the instance to use a certain no of CPU’s instead hogging all the available CPU’s. All we have to do is set an initialization parameter to control the CPU resources an instance can use. It basically limits the no of CPU’s an instance can use e.g. I can set this parameter to “1” on a 4 CPU machine. It’s very simple (silly :)) to set it –

ALTER SYSTEM SET CPU_COUNT = 4;

However, it works with resource manager feature so you will need to enable RM and create a resource plan first before running the above command.

Instance caging can be useful on larger box with multiple instances running – some development and a few production allowing CPU and resource allocation be done effectively. Apart from this, it can also be said that it’s Oracle’s way (alternate option) of giving CPU resource allocation control to a DBA rather than leveraging a logical/physical partitioning of a large box resorting to usage of tools/technologies such as VMware, AIX LPAR or Sun containers etc. In a nut shell, it’s the simple way to control the CPU consumption of each database instance.

Maybe sometime later, I will try to post an example. Thanks for reading and appreciate your comments / thoughts.

Oracle Restart - a 11g New Feature

noreply@blogger.com (Unknown) — Wed, 06 Apr 2011 06:37:00 +0000

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 or software failures and machine reboots. The components that can be restarted are –

Database instance
Oracle Listener
ASM instance
ONS (Oracle notification service)

It continuously monitors all the Oracle services started via it and restarts any of the failing services. It ensures that the components are starts in proper order e.g. starting the ASM instance before starting the database instance. What’s important is that Oracle restart is integrated with tools like SQL*Plus, LSNRCTL, and ASMCMD; so when you shut down the database or listener with the respective utilities, Oracle Restart does not interpret it as a failure and hence does not attempt to restart.

Much like clusterware, Oracle Restart also comes with a server control utility (SRVCTL); you should start/stop the components with SRVCTL.

In my opinion, this is a pretty useful tool and option to have. Gets rid of any additional scripting that would have required in day-to-day operations. Also serves as an introduction to how to manage services in a RAC which is a good thing for junior DBA's. The only problem the tool still looks for “ORA_CRS_HOME”, which is a bit weird given that it is supposedly for single (non-RAC) instance databases.

Oracle Database on Amazon RDS

noreply@blogger.com (Unknown) — Thu, 17 Mar 2011 12:10:00 +0000

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 choose from the licensing options for running the Oracle Database on Amazon RDS ->

Bring Your Own License – (BYOL): Customers with existing Oracle Database licenses can run Oracle Databases on Amazon RDS with no additional software licensing or support cost. Oracle will provide the technical support required in case BYOL DB instances.
On-Demand Database Instances: This is a pay-by-the-hour licensing option with no up-front licensing fee or long-term commitment required. One simply pays by usage depending on the database edition and size of the instance. Brings it on par with other AWS offerings. Technical support for On-demand instances will be provided by AWS.
Reserved DB Instances: Make a commitment and pay a one-time fee to get a DB instance with a significant discount on the hourly usage charges. The commitment required would be 1 or 3 years. Technical support for On-demand instances will be provided by AWS.

While you can still choose to install Oracle on an AWS EC2 instance but it had a few drawbacks –>

Licensing fee – Oracle is an expensive product and it acted as a deterrent to use Oracle on a cloud hosting platform such as Amazon.
High availability – currently setting up of Oracle RAC is not possible on AWS EC2 hence one cannot achieve high-availability

Given the above mentioned points it definitely was not possible for startups, SMB segments to consider Oracle as the database of their choice. So I believe making Oracle available on RDS is a very good move. However a few things to watch out for –

Upgrades – Can I choose to opt out of any future upgrades because I don’t want to be forced for an upgrade. E.g. they have similar option available for MySQL so will it be extended to Oracle as well?
High availability – Would I get RAC? Will it be made available on-demand or forced choice?
Data security – Database on shared infrastructure adds the same security concern that’s being on people’s mind for quite some time. Admin controls being with Amazon could be another concern.
Manageability – How much control do I get and the need to learn a new API to manage the databases.
TCO – Don’t just go by the hourly usage charges as Amazon will also charge for storage (backup and data), bandwidth / data transfer etc.

It would be good to hear about others opinion and experience.

Edition based redefinition

noreply@blogger.com (Unknown) — Tue, 08 Mar 2011 09:58:00 +0000

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 for pointing out Gary Myers]. However, we all know Oracle’s capability to address unplanned downtimes with features such as – Real Applications Cluster (RAC), Physical / logical standby database, Streams etc.

With 11gR2, Oracle claims to have addressed the gap with edition based redefinition. Oracle now supports multiple versions (editions) of a given object. So now, the objects are called as “editioned object” or “non-editioned objects” based on whether an object can have multiple versions or not. While one version of the object is used by the live application, the other version can be used to carry out changes and tested before making it permanent. However, the editionable feature is available for the following database objects ->

Synonym
View
Procedure
Function
Package and Package Body pe
Trigger
Type and Type Body
Library

The all-important and most used object – table and indexes are missing from the above list. So if you look at the list, you will feel that the versioning of code is now extended to database engine. Only view (though that’s also a piece of SQL code) and synonyms have been added. However, one can work around table by creating views and making them editionable but again only simple views without the use of functions etc. can be editioned. As a change, the developer needs to make his/her code work against editionable objects.

The other important thing is data within the table can’t be versioned; a lot of changes happen to be DML in nature so this is another important thing missing though there are workaround.

So, in my opinion this feature is useful in the following scenario =>

If you make changes to procedures, function etc.
Changes are done to physical structure of tables rather than data

Otherwise I feel this is not as useful yet. Also it’s a relatively a new feature and yet to mature. I'm sure future releases of Oracle will bring in some enhancements.

Oracle Critical Patch Update - April 2010

noreply@blogger.com (Unknown) — Wed, 14 Apr 2010 10:55:00 +0000

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.

http://www.oracle.com/technology/deploy/security/critical-patch-updates/cpuapr2010.html

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

FAQ - http://www.oracle.com/technology/deploy/security/cpu/cpufaq.htm

Oracle RAC One Node - What is it?

noreply@blogger.com (Unknown) — Wed, 07 Apr 2010 16:33:00 +0000

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 feature Or let me know if this post has any wrong/misleading information. It will be a healthy discussion.

What is Oracle RAC One Node?

Oracle introduced a new option called RAC One Node with the release of 11gR2 in late 2009. This option is available with Enterprise edition only. Basically, it provides a cold failover solution for Oracle databases. It’s a single instance of Oracle RAC running on one node of the cluster while the 2nd node is in a cold standby mode. If the instance fails for some reason, then RAC One Node detects it and first tries to restart the instance on the same node. The instance is relocated to the 2nd node in case there is a failure or fault in 1st node and the instance cannot be restarted on the same node. The benefit of this feature is that it automates the instance relocation without any downtime and does not need a manual intervention. It uses a technology called Omotion, which facilitates the instance migration/relocation. “RAC one” is Oracle’s answer or solution to OS clustering solution like Veritas Storage Foundation, Sun Solaris cluster, IBM HACMP, and HP Service guard etc.

Purpose

Its Oracle’s attempt to tie customers to a single vendor by eliminating the need to buy 3rd party OS cluster solutions. First, it introduced Oracle Clusterware with 10g and stopped the need to rely on 3rd party cluster software and now it intends to conquer the rest who are still using HACMP, Sun Solaris cluster etc. for cold failover.

Benefits

The Oracle RAC One node provides the following benefits:

• Built-in cluster failover for high availability

• Rolling patches for single instance database

• Proactive migration / failover of the instance

• Live migration of instances across servers

• Online upgrade to RAC

The rolling upgrade is really useful. Upgrade to the OS, and Database can be done without any downtime unless upgrade requires some scripts to be run against the database. With RAC One Node, the DBA’s and Sys admins can be proactive and migrate/failover the instance to another node to perform any critical maintenance activity.

What it's not suited for

According to me the RAC one node is not a viable or recommended solution in the following scenarios:

• To load balance unlike regular RAC

• A true high availability solution

• As a DR solution; Data guard best suits the bill

• For mission critical applications

Cost

It is definitely not FREE. Oracle has priced RAC one at par with Active Data Guard. The RAC One node is priced separately and costs $10,000 per processor as against $23,000 for regular RAC. The licensing cost is required for ONE node only (in a 2-node setup). RAC one node is eligible for 10-day rule, allowing a customer to migrate to another without the need to buy additional license up to 10-days in a calendar year. People arguing against paying a license fee for resources they are not using will still lament.

Conclusion

I am still not very convinced on the usefulness of RAC one node. I think customers invest in RAC for their mission critical applications and achieving high availability and load balancing at the same time. Those who don’t go for RAC rely on Data Guard and now with 11g, on Active Data Guard. So don’t see a huge requirement for RAC One except seamless failover within a data center. The licensing is a bit disappointing; they are making clients pay $10 K. Moreover RAC is free with Standard edition though one doesn’t get enterprise features and limited to 4 CPU sockets only. So, thinking RAC One will be popular among customers who are currently using standard edition and want to switch to enterprise will be wrong. However, this is still a very new feature and as more people adopt it, we will get more clarity on its’ usability. I am planning to do a POC on it and would publish the installation steps and any findings (goods things and not so good things) of my POC.

Future of Relational Database

noreply@blogger.com (Unknown) — Mon, 16 Feb 2009 06:12:00 +0000

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, Drizzle, Mongo etc. However, all these services (both cloud and non-cloud) are still in beta or alpha phase.

My 2 cents - Personally, I think these emerging database models have a long way to go before then can match feature richness of RDBMS; Scalability can not be the only criteria as described in RWW article. With RDBMS like Oracle adopting to cloud, it can get tougher for key/value database models.

Pour in your thoughts...

Oracle Critical Patch Update - October 2008

noreply@blogger.com (Unknown) — Thu, 16 Oct 2008 03:55:00 +0000

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

noreply@blogger.com (Unknown) — Thu, 25 Sep 2008 04:07:00 +0000

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 a serious contender to Teradata and Netezza. The Exadata Storage server includes two Intel processors, each with four cores, with up to 12 terabytes of raw storage. Here is what James Kobielus of Forrester Research has to say at his blog.

Refer to the following links for more about Exadata and Database machine.

HP Oracle Exadata Storage Server
HP Oracle Database Machine

CURSOR_SPACE_FOR_TIME to be deprecated in future releases

noreply@blogger.com (Unknown) — Tue, 22 Jul 2008 17:50:00 +0000

According to a recently published metalink note (565424.1) the parameter "CURSOR_SPACE_FOR_TIME" is going to be deprecated starting release 11.1.0.7 onwards. In fact Oracle's next patch set release for 10g - 10.2.0.5 will also de-support this parameter. We will have to remove this parameter whenever these 2 patch sets - 10.2.0.5 for 10g and 11.1.0.7 for 11g are released and we decided to migrate. CURSOR_SPACE_FOR_TIME was introduced to reduce latch contention; This parameter may no longer be required with introduction of cursor mutexes hence Oracle has decided to deprecate this in future releases.

Oracle 11g New Features – PL/SQL enhancements Part-II

noreply@blogger.com (Unknown) — Mon, 21 Jul 2008 06:09:00 +0000

This is the concluding part of the 2 part series on PL/SQL enhancements in 11g. Here are few more useful enhancements in PL/SQL :-

1. Usage of Named and mixed notation with PL/SQL subprograms in a SQL statement

Prior to 11g, to use a sub-program (e.g. function) in a SELECT statement you have to provide the real parameters in positional notation. Let’s look at an example using both 10g and 11g to understand it better.

Here is a small piece of code which calculates the years of employment of an employee :-
CREATE OR REPLACE FUNCTION cal_employment_duration (
empid IN NUMBER,
as_on IN DATE
)
RETURN NUMBER
IS
vduration NUMBER;
BEGIN
SELECT MONTHS_BETWEEN (as_on, hiredate) / 12
INTO vduration
FROM emp
WHERE empno = empid;

RETURN ROUND (vduration);
END;
/
Now I call this function using a SELECT statement in Oracle 10g. Observe the difference when I use named and mixed notation :-

SIPRAS@ORA10G> SELECT empno, hiredate,
2 cal_employment_duration (empno, SYSDATE) || ' Years' "Employee Tenure"
3 FROM emp
4 WHERE empno = 7788
5 /

EMPNO HIREDATE Employee Tenure
---------- --------- ----------------------------------------------
7788 19-APR-87 21 Years

SIPRAS@ORA10G> SELECT empno, hiredate,
2 cal_employment_duration (empid => empno,
3 as_on => SYSDATE
4 )
5 || ' Years' "Employee Tenure"
6 FROM emp
7 WHERE empno = 7788;
cal_employment_duration (empid => empno,
*
ERROR at line 2:
ORA-00907: missing right parenthesis

Now lets use the same example in Oracle 11g :-

SIPRAS@11glab> SELECT empno, hiredate,
2 cal_employment_duration (empno, SYSDATE) || ' Years' "Employee Tenure"
3 FROM emp
4 WHERE empno = 7788;

EMPNO HIREDATE Employee Tenure
---------- --------- ----------------------------------------------
7788 19-APR-87 21 Years

Now lets use named notations and see if it works or not:-

SIPRAS@11glab> SELECT empno, hiredate,
2 cal_employment_duration (empid => empno,
3 as_on => SYSDATE
4 )
5 || ' Years' "Employee Tenure"
6 FROM emp
7 WHERE empno = 7788;

EMPNO HIREDATE Employee Tenure
---------- --------- ----------------------------------------------
7788 19-APR-87 21 Years

SIPRAS@11glab> SELECT empno, hiredate,
2 cal_employment_duration (empno, as_on => SYSDATE)
3 || ' Years' "Employee Tenure"
4 FROM emp
5 WHERE empno = 7788;

EMPNO HIREDATE Employee Tenure
---------- --------- ----------------------------------------------
7788 19-APR-87 21 Years

Great! it works, except in the following case; Note the error message :-

SIPRAS@11glab> SELECT empno, hiredate,
2 cal_employment_duration (empid => empno,
3 SYSDATE)
4 || ' Years' "Employee Tenure"
5 FROM emp
6 WHERE empno = 7788;
cal_employment_duration (empid => empno,
*
ERROR at line 2:
ORA-06553: PLS-312: a positional parameter association may not follow a named
association

Not sure why this was left out of the enhancements.

2. Trigger Enhancements: A couple of enhancements have been done to triggers. I have captured all of them with examples below.

i) You can now ENABLE or DISABLE triggers at creation time e.g
CREATE TRIGGER test_trig
BEFORE INSERT ON EMP
DISABLED/ENABLED

ii) Compound trigger: A new trigger type called “compound” has been introduced. It basically implements actions for all of the DML timing points in a single trigger. Action for each timing point “BEFORE STATEMENT”, “AFTER STATEMENT”, “BEFORE EACH ROW” and “AFTER EACH ROW” can now be written in a single trigger. Here is an example of :-

This trigger tracks updates to the “sal” column; It (a) assigns an update_id, notes start and end time for each update statement (b) keeps old and new salary for every row updated. All of this done with a single trigger.

CREATE OR REPLACE TRIGGER track_sal_upd
FOR UPDATE OF sal ON emp
COMPOUND TRIGGER
max_sal_upd_id number(4);
BEFORE STATEMENT IS
BEGIN
select nvl(max(sal_upd_id),0) into max_sal_upd_id from sal_update_log;
max_sal_upd_id := max_sal_upd_id + 1;
insert into sal_update_log values(max_sal_upd_id,null,null,null,systimestamp,'salary update process '||max_sal_upd_id||' started');
END BEFORE STATEMENT;

AFTER EACH ROW IS
BEGIN
insert into sal_update_log values(max_sal_upd_id,:old.empno,:old.sal,:old.sal,systimestamp,'updated by process '||max_sal_upd_id);
END AFTER EACH ROW;

AFTER STATEMENT IS
BEGIN
insert into sal_update_log values(max_sal_upd_id,null,null,null,systimestamp,'salary update process '||max_sal_upd_id||' finished');
END AFTER STATEMENT;
END track_sal_upd;
/

iii) Ordering of triggers: You can now control the order in which the triggers on a table would get fired. Oracle 11g has introduced a new clauses “FOLLOWS” to implement this feature. It will allow you to control the order in which the triggers fire when you have multiple triggers of same type on the same table. Oracle randomly picks up the triggers (if multiple triggers of same type on same table exist) if FOLLOWS clause is not used, which was the case prior to 11g.

Here an example which uses FOLLOWS clause. In this example, we have 2 triggers – first one “check_sal” is only for updates to SAL column whereas second one “check_update_job” will fire on updates to JOB & SAL column. Mark the FOLLOWS clause on “check_sal” trigger. It states that check_sal trigger should fire only after “check_update_job” has fired..

CREATE OR REPLACE TRIGGER check_update_job
BEFORE UPDATE OF job,sal
ON emp
FOR EACH ROW
DECLARE
v_start_range NUMBER (7, 2);
v_end_range NUMBER (7, 2);
BEGIN
IF :OLD.job IN ('CLERK','SALESMAN') THEN
IF :NEW.JOB != 'MANAGER' THEN
raise_application_error (num => -20001,
msg => 'Cannot change job to ' || :NEW.job
);
END IF;
END IF;
END;
/

CREATE OR REPLACE TRIGGER check_sal
BEFORE UPDATE OF sal
ON emp
FOR EACH ROW
FOLLOWS check_update_job
DECLARE
v_start_range NUMBER (7, 2);
v_end_range NUMBER (7, 2);
BEGIN
SELECT start_range, end_range
INTO v_start_range, v_end_range
FROM sal_range
WHERE job = :NEW.job;
IF :NEW.sal NOT BETWEEN v_start_range AND v_end_range
THEN
raise_application_error (num => -20000,
msg => 'Salary is not in the prescribed range'
);
END IF;
END;
/

SIPRAS@11glab> select empno,job,sal from emp where empno=7369;

EMPNO JOB SAL
---------- --------- ----------
7369 CLERK 800

SIPRAS@11glab> update emp set sal=500, job='PRESIDENT' where empno=7369;
update emp set sal=500, job='PRESIDENT' where empno=7369
*
ERROR at line 1:
ORA-20001: Cannot change job to PRESIDENT
ORA-06512: at "SIPRAS.CHECK_UPDATE_JOB", line 7
ORA-04088: error during execution of trigger 'SIPRAS.CHECK_UPDATE_JOB'
See the above error, it fired “check_update_job” first. In releases prior to Oracle 11g, you would not be able to control this.

3. Enhancements to PL/SQL Native compilation

Prior to Oracle 11g, initialization parameters were used to setup native compilation of PL/SQL code. However starting 11g, only one parameter “PLSQL_CODE_TYPE” needs to be set. It can be set to “INTERPRETED” (default value) or “NATIVE”. This parameter can be set at session level (ALTER SESSION), system level (ALTER SYSTEM) and for specific subprograms too using ALTER PROCEDURE. We all know, native compilation help improve the speed of PL/SQL programs as it compiles them to native code. It is of great help when your code consists of lot of loops, calculations, branches etc.

The trigger enhancements are really cool! I particularly liked the compound trigger feature the most. Share your thoughts on what you like…This was the concluding part of the PL/SQL enhancement series. Here is the link to first post in this series.

Technorati Tags: Oracle 11g,PL/SQL New Features,11g New Features,Trigger Enhancements

Oracle Critical Patch Update July 2008

noreply@blogger.com (Unknown) — Wed, 16 Jul 2008 08:33:00 +0000

Oracle has release the 3rd critical patch update for 2008 (CPU July 2008). Critical patch updates mostly contain fixes to security vulnerabilities plus it would have non-security fixes too. This critical patch update contains 45 new security fixes across all products which includes14 for Oracle database. 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/cpujul2008.html

Also refer to Metalink note id 579278.1 for Oracle database and Fusion middleware products.

The next critical patch update (CPU) would be coming in October 2008.

Happy patching!

Technorati Tags: Oracle,CPU 2008,CPU,Patch Update

Oracle 11g New Features – PL/SQL enhancements Part-I

noreply@blogger.com (Unknown) — Mon, 30 Jun 2008 17:50:00 +0000

I was going through the enhancements made for PL/SQL in Oracle 11g and learn that there are quite a few. I am going to post on these new features in two parts. Here goes the first part.

1. Usage of sequences in PL/SQL expressions

Allows you to assign a sequence value in an expression that is, you do not need to use a SQL query to generate sequence value and assign it to variable. Here is an example :-

SELECT seq_name.nextval INTO variable FROM dual; –> this was how we used to generate sequence values inside PL/SQL

From 11g you can simply do this

variable := seq_name.nextval; –> isn’t this great!

Similarly “currval” can also be used in PL/SQL expression.

2. CONTINUE statement in PL/SQL

CONTINUE is the new loop control statement in 11g. We have used “EXIT” in order to exit out of the loop on certain condition, however CONTINUE would allow us to exit the current iteration in the loop and the control would be passed to next iteration. Here is a small example of find out out even numbers :-

BEGIN
   FOR x IN 1 .. 10
   LOOP
      IF MOD (x, 2) = 0
      THEN
         DBMS_OUTPUT.put_line ('Even number');
         DBMS_OUTPUT.put_line (x);
      ELSE
         CONTINUE;
         DBMS_OUTPUT.put_line (x);
      END IF;
   END LOOP;
END;
/

3. CONTINUE-WHEN statement

It’s purpose is to replace IF…THEN…CONTINUE. Lets re-write the above example using CONTINUE-WHEN :-

BEGIN
   FOR x IN 1 .. 10
   LOOP
      CONTINUE WHEN MOD (x, 2) = 1 ;
         DBMS_OUTPUT.put_line ('Even number');
         DBMS_OUTPUT.put_line (x);
   END LOOP;
END;
/

4. New Datatypes – SIMPLE_INTEGER, SIMPLE_FLOAT and SIMPLE_DOUBLE

SIMPLE_INTEGER supports values ranging –2147483648 to 2147483648 and does not include null values which means it comes with a “NOT NULL” constraint. Apart from the fact that it’s never checked for nulls, overflow checking is also not necessary for SIMPLE_INTEGER. Due to these facts it gives better performance than PLS_INTEGER.

SIMPLE_FLOAT and SIMPLE_DOUBLE are new subtypes of BINARY_FLOAT and BINARY_DOUBLE with “NOT NULL” feature.

5. Changes to Regular expression built-ins

The new REGEXP_COUNT built-in returns the number of times a pattern is found in an input string. The built-ins REGEXP_SUBSTR and REGEXP_INSTR have been improved to return the occurrence you want to find.

I will be covering a few more PL/SQL enhancements in my next post. Do post your comments if you have any suggestions.

Technorati Tags: Oracle,Oracle 11g,PL/SQL,Oracle New Features

Capital of India hosts its first ever Blog Camp

noreply@blogger.com (Unknown) — Fri, 23 May 2008 17:25:00 +0000

I just learned that Delhi (Capital of India) is hosting its first ever Blog Camp. I was waiting for this event to happen but not sure if I can make it or not but I will make an attempt though.

Hats off to its organizers!!!

When
24th May 2008 9:30 am IST

Where
Microsoft Corp
5th Floor, Eros Towers
Nehru Place, New Delhi
Map

More details at http://www.barcamp.org/BlogCampDelhi