How to live with the Standard Edition of Oracle

With the advent of open source relational database platforms, many organizations must be looking at Oracle Enterprise Edition(EE) pricing and calling it outrageous.  Not to mention the drying up of budgets in this economy.  You can’t do anything for the sunk cost of the existing EE licenses.  However, even for new purchases, switching to an entirely different database platform can be impractical.  Some companies make compromises and go with Standard Edition(SE), or for single-node installations, Standard Edition ONE.

What are the pitfalls of using Oracle SE?  Here are a few considerations to be taken when choosing to use Standard Edition:

1. Release Management. Strict release procedures should be in place.  Database changes should not be released to a production environment running SE without shutting down the application first.  This is particularly true for applications that have a lot of data.  The limitations of SE such as the inability to build index online makes releasing schema changes on-the-fly very difficult.
2. Application Testing. The database should best be running only a few key, well-tested applications, rather than a big assortment of different applications.  The lack of Database Resource Manager in SE means that applications are left to consume resources(cpu cycles, etc) freely.  A big assortment of different applications particularly those firing ad-hoc or dynamically composed SQL statements is prone to give you grief.
3. Datawarehouses most likely shouldn’t be hosted on an SE database due to the lack of bitmap indexes on SE.
4. Applications that have a lot of data also are not good candidates to run on SE.  SE’s lack of multi-channel RMAN backup makes RMAN backups very time consuming for big databases.  You can consider other ways of backing up big databases, such as using SAN snapshots.  However, RMAN is an essential part of the Oracle backup strategy.  So this comes down to hosting only small datasets on SE.  How small should it be?  Roughly the databases would best be less than 300GB.  Otherwise, RMAN full backups will take a long long time.
5. Transportable Tablespaces is not available with SE.  So as you sync production data to a development database, you’ll have to rely on other utilities such as data pump export/import or the legacy export/import.  Those utilities take a long time to dump out and import data.
6. Parallel Operations. All parallel operations are disabled on SE.  This applies to parallel query, parallel index creation, parallel data pump export/import, etc, etc.
7. Virtual Private Database(VPD) / Fine Grained Security are not available for SE.  This means it’s harder to allow a broader user base and use that feature to automatically make only a subset of data visible based on the users’ credentials/profile.
8. All Options are not available. Options are only available for EE only.  So no partitioning, active data guard, advanced compression, etc, etc.

In essence, anything that allows the database to scale is taken off on SE.  To be able to live with SE, you have to practice discipline, testing your applications thoroughly and making sure that SQL statements fired at the SE database are not resource-hogging surprises(this one may be tough to do).  Manage the release management procedures, shutdown the applications first, then apply database schema changes and build indexes.  Keep things simple and small.  Huge tables or indexes will definitely give you a lot of problems down the road for SE.

If you go with open source relational database platforms, those certainly have their own limitations.  Their pros and cons vary.  Some of their features are comparable to Oracle EE.  For example, Postgresql can do online index creation whereas Oracle SE cannot.  Some are even weaker than Oracle SE.  For example, MySQL cannot do a true hotbackup w/o blocking ongoing DMLs.

When a company can afford to purchase Enterprise Edition licenses, functionally it is really the best you can get.  But if it’s not feasible and you choose to stick with Oracle by using SE, then you’ll need to prepare to be strict on your procedures and don’t expect you’d have easy solutions when problems happen.  Tuning into that mindset would make using SE a lot easier.  You really do get only what you paid for.