Sooner or later, every DBA will need to address how to manage storage capacity in their databases. Nearly every database needs more storage as time goes on, and without attention, the database will fill up, and new data/inserts can’t happen. It’s been my experience that bad space mangement is the root cause of a high percentage of database downtime incidents.

In the Oracle RDBMS, every segment (table/table partition, index/index partition, queue) must be assigned to a particular tablespace. Each tablespace is composed of one or more datafiles. Datafiles do have a maximum size, depending on the blocksize for traditional smallfile tablespaces; for a tablespace with a block size of 8k, the maximum size of a datafile is just under 32Gb. So, each tablespace is typically comprised of many datafiles. Oracle has several tablespaces that are built-in; these include the SYSTEM, SYSAUX, TEMP, and UNDO/ROLLBACK tablespaces.

Let’s say that you wanted to make the work of the next DBA who manages a database you control as hard as possible. One of the easiest ways to do this is to create a database with LOTS of tablespaces; you could even create 1 tablespace per segment. This means that instead of worrying about one ‘pool’ of storage, the next DBA will need to manage dozens, or hundreds, of storage pools. Because this management is mission-critical (if ANY tablespace fills up, your application may not work), it is far more likely that one will be overlooked, and fill up. Using lots of tablespaces will also make it far more likely that a lot of space is wasted in each tablespace, because every tablespace will need to maintain a certain amt of free space to be ’safe’ from future increases in growth.  If a segment is dropped or shrunk, the space released will be only in that one tablespace, and won’t be able to be used by segments assigned to all of the other tablespaces.

If you wanted to make the the work of the next DBA who manages a database you control the easiest, you’d create just one tablespace (which could be a BIGFILE tablespace, which can use larger files than SMALLFILE tablespaces), and stick all of your segments into there. There would only be one tablespace to manage; when segments are shrunk or removed in the course of business, that space can immediately be used by any other segment. To manage extent sizes, you could simply use the EXTENT MANAGEMENT LOCAL AUTOALLOCATE feature, which automatically gives you near-to-optimimum sizes.

Prior to Oracle 10g, there were many legitimate reasons to use multiple tablespaces and there still are good reasons to segregate your segments into separate tablespaces. Some of these reasons are:

• You may need different block sizes for different segments for best performance; since each tablespace has one block size, to use different ones you need different tablespaces.
• If you aren’t using a SAN, or ASM, or similar storage solution, you may wish to segregate segments on differnet LUNs/drives for better performance. Given the size of today’s databases, and the need for high IOPS storage solutions, most non-trivial databases are using SANs/ASM/other storage solutions these days. Using these, it doesn’t give you better performance by segregating tablespaces.
• If you need to use the ‘transportable tablespaces’ feature to move large quantities of data between Oracle databases,
• If you use Oracle’s direct-path load feature, which loads data above the high-water mark of each datafile directly for faster data loading (by bypassing the internal SQL engine), then you may wish to continually create new tablespaces for this purpose.
• If you have several separate users in your database, and wish to ’split’ the database in the future so that you have one databases each for each user, then you may wish to create a dedicated tablespace for each user. This would allow you to clone half of the database using RMAN on a new machine, and OFFLINE DROP the datafiles you do not want.

In conclusion, one easy way to make your oracle database use space more efficiently, be easier to manage, and therefore be less likely to fill up, is to use fewer, larger application tablespaces, rather than more.

Oracle over the years has come out with a bewildering array of features to minimise downtime for databases.  In fact, the number of methods to accomplish this goal is at least a dozen.  Over the years of experience with Oracle databases, there is one feature, far more than any others, that has proved itself again and again in the real world; I speak of course, of the Oracle Physical Standby feature.

The Oracle Physical Standby feature is not new at all; I was using it back in 1995 when I first started working as an Oracle DBA with version 7; perhaps that’s why it’s as reliable as it is.  Although there have been cases where standby databases do encounter bugs, with proper monitoring (for example, with the monitoring done by Database Rx) this is not an issue.

So, why am I recommending Oracle Physical Standby databases over the other methods you can use to insure uptime?  What makes it so special?

• An Oracle Physical Standby database has nothing shared with the primary database; it (typically) runs on a separate server, using its own storage, it’s own memory, and it’s own network interface; machines/disks/memory/network cards all break sooner or later (it’s not “if”, it’s “when”).  Some folks say that RAC serves this purpose, but in fact it does not; all nodes in a RAC cluster read and write to the same storage subsystem, so that is a potential single point of failure.
• If you can afford the network connection necessary, you can physically place your physical standby database in a separate data center, so even if your primary data center burns up, your data is safe.
• This feature is available in all versions of Oracle, though for Standard Edition and lower, you will need to come up with scripts to move archivelogs and apply them on the standby, as we do for our Remote DBA clients.
• For Standard Edition, you can tune it so that you will lose a maximum of one archived redo log worth of data worst-case, if your primary database disappears.
• If using the Enterprise Edition, you can have Oracle itself manage it, and in fact use advanced automatic failover methods using a feature called Dataguard, that can switch primary/standby database roles very quickly and easily.
• If using the Enterprise Edition, you can set parameters to delay the application of data from the primary to the standby by a certain amount of time.  This would be useful if you suffered logical corruption on the primary database (ie someone dropping a table) and could catch it fast enough.
• If using the Enterprise Edition, you can use the Flashback Database feature to allow the standby to recover from logical errors on the primary database even easier,
• Also, if using the Enterprise Edition, you can actually make the standby stay up-to-date with the primary database in near-real-time, by making the log-writer (LGWR) process on the primary, ship redo directly to the standby, even before that redo is archived.
• Although there are restrictions, you can actually open up a standby database in read-only mode for reporting.  In versions before Oracle 11g, the standby cannot apply changes from the primary while it is open this way; however, there is a new feature in 11G called ‘Active Data Guard’ that allows this.
• In my experience, Oracle Logical standby databases are not as reliable as compared with Physical Standbys.
• Another advantage; you can use your physical standby database to do RMAN backups from, which can dramatically reduce the load on your primary database.

The main disadvantage to setting up a standby database, is that you will double your hardware cost; you will need a server/disk able to handle your peak production loads.  It will also cost you an extra Oracle license for that instance.  This will double the amount of space in your datacenter used, and also double the amount of electricity/heat produced.  For many businesses though, this cost is far less then the cost incurred by a lengthy database recovery.

There is nothing more relieving than when a primary database malfunctions, and you are able to continue production with failover times usually measured in under 10 minutes, rather than potentially waiting days/weeks for new hardware and a full restore from backup.  There is no other feature that as dramatically improves the potential uptime of an Oracle Database as much, as using a Physical Standby database.

When you happen across a table with lots of indexes, what’s the first thought that crosses your mind? 

Okay, having gotten that bit of venting out of the way, what’s the second thought?  Are all of these indexes actually used?  That’s a legitimate question and one that comes up from time to time in our remote Oracle DBA practice.  Unneeded indexes not only waste space but also introduce unnecessary overhead to table updates.  So let’s talk about what can and should be done about it.

Index usage monitoring is a fairly common subject of chatter in the various Oracle-related discussion boards and blogs.  There are scripts available, some free and some for a price, which boast the ability to identify unused indexes easily enough and allow you to just drop them, thereby releasing tons of space and cutting update overhead dramatically.  Our take on the matter – not so fast!

Determining whether an index is a necessary part of the database is not as easy as turning on monitoring and running a few scripts.  Sure, turning on monitoring is easily done with the ALTER INDEX statement and then checking the V$OBJECT_USAGE view will tell you whether the index has been accessed since monitoring was turned on.  But there are a couple of gotchas with that approach:

• In versions of 10g prior to 10.2.0.5 and 11gR1 gathering stats on a table with the CASCADE option set will mark an index as “used” in the V$OBJECT_USAGE table if monitoring is on.  See Bug # 430034.1 in Metalink.
• Indexes which support foreign keys are not flagged as used when a relevant update occurs even though the index was indeed used behind the scenes to avoid a table lock.

Thus indexes might be flagged as used even though they are not necessary and may not be flagged when they are indeed a vital part of the database.

Some alternatives exist to the V$OBJECT_USAGE view, such as querying AWR or Statspack data to determine whether an index was part of an execution plan.  But this approach has a separate downfall in that an index might have been incorrectly used by the optimizer.  Perhaps the index was used but is its existence justified?  Is it really necessary?  You might end up dropping an index that should have been used in favor of one that should not have been.

So what’s the point?  The point is that sometimes there is no good shortcut for diligence.  Let us keep the cart behind the horses and talk to the data modelers and application developers about whether indexes are necessary.  It is easy enough to identify redundant indexes such as those which are subsets of others on the same table so we could certainly make recommendations, but throwing the application into a tizzy by dropping indexes we declared useless through the running of our scripts is a good way to insure taking up residence in the proverbial dog house.

As Oracle consultants we often get caught up in the esoteric areas of performance, such as contention, complex execution plans, and obscure parameters.  But we forget that sometimes it’s the little things that count, many of which we learned in the DBA101 phase of our careers.

I was reminded of this recently at one of our remote DBA clients.  The customer was having issues with flashback queries in a RAC cluster.  We discovered that the undo_retention setting on one instance was much less than on the others.  At first we couldn’t figure out how this was happening; the settings were correct in the spfile.  Then we looked in the $ORACLE_HOME/dbs directory of the instance and saw that there was a pfile (aka, an init.ora file) in the directory, with different settings for undo_retention.

As is typical in RAC installations, there was supposed to be a pfile whose only contents were a pointer to the spfile (because the spfile was in ASM).  But what had happened was that someone had executed the command “create pfile from spfile” in order to read or copy the spfile settings.  Of course, this overwrote the pfile which pointed to the spfile.  It didn’t cause an issue until the next time the instance was restarted; when that was done the instance then had a setting for undo_retention from an earlier time.

For this reason, my personal best practice for creating an editable pfile from an spfile is to put it into the /tmp directory–

 create pfile='/tmp/something.ora' from spfile;

That way I can read it, edit it for whetever use (such as making a clone), or whatever I want without risking having it used unexpectedly at some time in the future.  Others use

 create pfile='?/dbs/init_<not_the_sid>.ora' from spfile; 

There are many options, but the point is to not use the default which results when you don’t specify a pfile name.

Of course, one could say “the DBA should always check and clean up everything after their tasks”, which is true.  But avoiding the need for the cleanup by following a standard practice is a more thorough solution, in my opinion.

Not everything we do as DBAs is complex, convoluted, or esoteric.  Taking care of the little things can be as important as being able to optimize a 300 line query.

As an implementer of commercial and open source database monitoring tools I find DatabaseRX has capabilities that clearly differentiate it from the rest of the market:

• Scalability - hundreds of databases and instances can be added with trivial monitoring overhead, due in part to a highly normalized data model, message based architecture and modular design (loader,analyzer,notifier).
• Security - DatabaseRX agents don’t require root/oracle or DBA privileges to fully report operational and trending issues.
• Network Access Control -  DatababaseRX does not expose databases externally - it only requires a periodic encrypted SMTP push from the database or proxy host.
• Documentation and Global Search - Delaying  problem resolution for lack of information is inexcusable - the DatabaseRX monitoring portal includes everything related to escalation contacts, vendor support, access control, prior events and reports.

The last point - “one place to go” for problem identification, resource engagement, resolution and root cause documentation has proven to me during live events that there can be significant reductions in unexpected downtime simply based on that efficiency.

There was education—everything from RAC and Exadata to SQL Developer and Java Server Faces.

There was food—everything from Prosciutto and Salmon to Biscotti and Chocolate-Dipped Strawberries.

There were prizes and giveaways—books from Oracle Press, duffel bags, Iron Man 2 posters, DVDs of the Oracle Database Appliance, and more.

There was networking—an opportunity to meet and greet Oracle luminaries such as Graham Wood, one of the key players in the creation of Statspack, and Kris Rice, the architect of SQL Developer.

A great time was had by all. We hope to see everybody again at the summer conference on August 19 at Chevron in San Ramon.

See the pictures

Download the NoCOUG Journal

On 12th May Oracle updated the 11.1.0.7 April CPU 2010 security patch (9369783) on all platforms.  The original April 13th patch causes 239 invalid objects in the database if the OLAP option is NOT installed.  So, if you were planning to apply this patch you should download the 12th May version.  You can find out the details in metalink note 1060969.1 “Critical Patch Update April 2010 Known Issues”

The presence of the invalid objects does not cause problems however ending up with an extra 239 invalids at the end of the patch that are not discussed in the patch notes would be a concern.  Of course, that is why Oracle have  document 1060969.1 to catch these things. 

If you applied the patch between its original release on April 13th and the update of the known issues on 12th May you should check for the presence of these invalids.  Metalink note 1091952.1 lists them and says ”As long as OLAP was not in use it is safe to drop all of the above objects”. 

I have not tested the April PSU patch (9352179), at the moment it has no similar update in its known issue document, which is note 1061315.1.

It hasn’t been easy trying to classify DatabaseRX.   Is it a monitoring, capacity planning, analysis, reporting, forensics, dashboard, KPIs, delta comparison tool - yes.  Is it a database management console or query tool - no.  As such I’d like to start with a simple area but one that’s easy to get wrong.

Alert Log Scanning

Very few tools do this well out of the box - and those that attempt to perform the task require significant setup or proprietary transport agents.  And to my knowledge with 11gR2 ADR  you still can’t add a syslog host destination for an instance alert log (talk to the folks at TimesTen for this enhancement Oracle).

DatabaseRX has some impressive features for alert log monitoring:

• Transport and consolidation are built in to a periodic passive e-mail send framework.
• Events of interest are rated for severity.
• Pre and post lines are displayed around the alert.
• RegEx style strings can exclude events on an instance-by-instance basis.

To the last point all events are monitored and you add exceptions over time.  While there could be more noise-to-signal in the beginning, you will not miss an unusual but critical new event.

I’ve setup various alert log monitoring systems in the past for RAC/non-RAC databases and what DatabaseRX does exceeds those tools - with far fewer resources.  One personal metric? I log into production hosts fewer times to investigate events and that not only saves time but helps keep the auditors happy.

As a database administer, it’s very important not to forget one of our main responsibilities; that being insuring that the database(s) we’re responsible for, can be recovered in the case of an emergency.  Read my short presentation here, for thoughts on this important topic.

The Importance of Testing Database Recovery, by Jay Stanley

Dear NoCOUG members and friends,

Happy New Year; may it be productive for all of us! The next NoCOUG conference will be held on Thursday, February 11 from 9 A.M. to 5 A.M. at the CarrAmerica conference center in Pleasanton. Top billing this time goes to Dr. Neil Gunther, the world’s foremost expert in database performance analysis and capacity planning. He is known internationally for developing the open-source performance modeling software Pretty Damn Quick and is the subject of an entire Wikipedia article.

Dr. Gunther will deliver a keynote address titled “Why Are There No Giants?” and a technical presentation titled “Performance Analysis For Those Who Can’t Wait.” The full agenda of the conference has been posted at http://www.nocoug.org/next.html. Please RSVP at http://www.nocoug.org/rsvp.html.

Most Oracle professionals will benefit a lot from attending a NoCOUG conference in 2010. However, the following categories will not benefit much:

1. Those Oracle professionals who are at the very top of their game already. This group is limited to a select handful of individuals such as Tom Kyte, Jonathan Lewis, Cary Millsap, Steven Feuerstein, and Craig Shallahamer.
2. Those Oracle professionals who believe that Oracle’s goal in buying Sun is to replace Oracle Database with MySQL. This is probably a very small group.
3. Those Oracle professionals who are leaning towards the “NoSQL” camp and now believe that databases are only needed for storing key-value pairs. Another small group.
4. Those Oracle professionals who believe that every man is an island and don’t want to network with other Oracle professionals. Gilligan and Robinson Crusoe come to mind.
5. Those Oracle professionals who don’t subscribe to the seventh habit of highly effective people taught by Stephen Covey in his bestseller The 7 Habits of Highly Effective People. The seventh habit is “Sharpen the Saw” which means preserving and enhancing the greatest asset you have—you. We haven’t met anybody who admits to belonging in this group.

Most of us don’t fall in the above categories so we fully expect that there will be heavy attendance as usual at NoCOUG conferences in 2010. Please forward this message to your colleagues and we hope to see you on February 11.

All the very best,

Hanan Hit,
NoCOUG President

Randy Samberg,
Director of Conference Programming

Iggy Fernandez,
NoCOUG Journal Editor

NoCOUG conferences and the NoCOUG Journal are made possible in part by the generous support of companies such as Database Specialists. Call Database Specialists when you need remote DBA services or onsite support for your mission-critical Oracle database systems. Arrange a free consultation with a senior Database Specialists team member to find out how they can help increase your uptime, improve performance, minimize risk, and reduce costs. Visit their website for no-cost resources, white papers, conference presentations, and handy scripts.