Wednesday, July 23, 2014

How to Create an Oracle Database In Two Words: "CREATE DATABASE"

About five years ago, I wrote my first blog on this site, and titled it "DBA Minimalism".  Based upon the fact that I can count the hits on all my digits with room to spare, it was not well received.  Still, I thought it an interesting concept - how can I create an Oracle database in a minimum amount of time, in very few steps?

Since that time, a lot has changed in the Oracle infrastructure.  We now have ASM prevalent as a file system, managed systems are a big deal, OEM now has database administrative features including the ability to create a database, and Oracle's own tool for DB creation (dbca) has very much matured.

Still, there is an elegance to doing a complex task as simply as possible.  With many of these standard tools, you get many unnecessary add-on "features" in the form of schemas and applications (for example, scott/tiger, XDB and Oracle Spatial) that you may never use, need nor want.

While my original method will still work, a client recently tried it with an ASM file system and ran into a seeming roadblock.  Apparently, you cannot create a database in a single pass without an spfile on the ASM disks.  What this means is that you must first use my skeletal pfile and execute command "create spfile from pfile" while the database is in an unmounted state.  Then you can shutdown, startup (nomount) and issue the create database command with parameters for things like control files and undo tablespaces on the ASM diskgroups.

I don't really think Oracle planned this out very well.  You should not have had to make allowances for a different file system back end in order for a standard syntax like "create database" to work.  On the other hand, perhaps at the time, ASM was considered a nonstandard luxury option, and an add-on that required a little forward thinking.

I am still unsure about the whole move to ASM only databases that Oracle seems to be gravitating towards.   I suppose there's a benefit, certainly with engineered systems.  I do think we'll see the day when databases can be created, out of the box, with a default ASM setup.  This is not in 12c yet, as far as I can tell.  Now, 12c has a new feature (pluggable databases) which may make of creating new databases less and less of an issue.  With this feature, you can have a "multi-tenant" database topology where several sets of data housed in different DB instances in the past may now share overhead.

Time will tell how this feature works out.  I am doing research on it and may publish my findings in the future.