--

Tuesday, August 30, 2011

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 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 |
----------------------------------------------------------------------------------------

Friday, July 22, 2011

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 -
  • 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. 

Thursday, July 14, 2011

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 -

  • 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.

Friday, July 8, 2011

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 (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.

Tuesday, July 5, 2011

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.
It’s very handy guide for anyone who wants to migrate from 10g to 11gR2.




Thursday, June 23, 2011

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 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 – 16th to 21st July 
Venue – Online
Cost – Registration is Free
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.
Enhanced by Zemanta

Monday, June 20, 2011

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 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)

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

Thursday, June 16, 2011

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  "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.

Wednesday, May 25, 2011

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 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.

Wednesday, May 11, 2011

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 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.

Thursday, April 28, 2011

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

Wednesday, April 20, 2011

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 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.

Wednesday, April 6, 2011

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 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.

Thursday, March 17, 2011

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 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.

Tuesday, March 8, 2011

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 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.
Enhanced by Zemanta