Oracle Snapshot

V$SQL

2008-08-28T00:14:00.019+01:00

We are told that all of the V$ database views are dynamic, so this one is fairly obvious but very handy. Lets say we have a situation where, for example, somebody has kicked off a monster SELECT statement and you need to know how far the query has got. One way of checking is via the V$SQL view where it turns out that the ROWS_PROCESSED column is updated real time.

For example:

So here, we can see that by running this query several times we can track the number of rows processes (returned) by the SQL in question. If you have up to date optimizer stats, you can compare this against NUM_ROWS column from DBA_TABLES to see how far the query has to run before completion.

Controlling Data Guard with DGMGRL.

2008-08-10T22:16:00.018+01:00

The obvious benefit of Dataguard Broker is the ease of which switchover and failover operations can be performed. The other benefits include managing Data Guard operations via Grid Control plus the general reporting features. Having said that, a lot of DBA's (including myself) still like to use the command line interface to manage our databases. With that in mind, here is a quick guide to the most common commands used to manage Standby and Primary databases via the Data Guard Broker DGMGRL command line tool.

Manage the database state.
Your Dataguard configuration will consist of a Primary database and of course one or more Standby databases. For my setup, I have the following databases in place. Obviously your going to have to substitute your own database names in here:

1 x Primary Oracle database: "ORCL_DG1"
1 x Standby Oracle database: "ORCL_DG2"

Once up and running, DG Broker Primary databases can be in one of the following known states:

"ONLINE", "OFFLINE" or "LOG-TRANSPORT-OFF".

The Standby can also be deemed either:

"READ-ONLY", "OFFLINE", "ONLINE" or "LOG-APPLY-OFF ".

At some point, you will need to switch either the Primary or Standby states or settings:

To shut down the Standby, and alter the Primary's transportation settings to reflect:

DGMGRL> edit database 'ORCL_DG2' set state='OFFLINE';

To Shut down the Primary database:

DGMGRL> edit database 'ORCL_DG1' set state='OFFLINE';

To open the Standby database in READ ONLY mode:

DGMGRL> edit database 'ORCL_DG1' set state='READ-ONLY';

To switch the Standby database back to online recovery mode:

DGMGRL> edit database 'ORCL_DG2' set state='ONLINE';

To switch off Redo Transportation from the Primary to the Standby:

DGMGRL> edit database 'ORCL_DG1' set state='LOG-TRANSPORT-OFF';

To switch redo transportation back on:

DGMGRL> edit database 'ORCL_DG1' set state='ONLINE';

To edit specific database properties, for example, set the database archive lag target:

DGMGRL> edit database 'ORCL_DG1' set property 'ArchiveLagTarget'=1200;

To disable the entire broker configuration:

DGMGRL> disable configuration;

To enable the entire configuration:

DGMGRL> enable configuration;

Dataguard reporting.
One thing I do like about Dataguard broker is its reporting capabilities. By default, Grid Control provides some great graphs for visualising performance and configuration. In addition, DGMGRL provides some pretty helpful detail too:

To view the basic broker configuration:

DGMGRL> show configuration;

To view basic detail about a specific database in the configuration:

DGMGRL> show database ORCL_DG1;

DGMGRL> show database ORCL_DG2;

To view in depth detail about a specific database and its current state:

DGMGRL> show database verbose ORCL_DG1;

DGMGRL> show database verbose ORCL_DG2;

To display the current status and any configuration errors:

DGMGRL> show database 'ORCL_DG1' StatusReport;

DGMGRL> show database 'ORCL_DG2' StatusReport;

To provide detail of transportation and apply operations, state and any delays:

DGMGRL> show database 'ORCL_DG1' SendQEntries;

DGMGRL> show database 'ORCL_DG2' RecvQEntries;

To view the current database Top Wait Events:

DGMGRL> show database 'ORCL_DG1' TopWaitEvents;

DGMGRL> show database 'ORCL_DG2' TopWaitEvents;

Role reversal / failover.
Hopefully, you will not need to perform too many of these, but if you do, then the commands are pretty straight forward (which is what we need!):

To perform a Role Reversal:

DGMGRL> switchover to 'ORCL_DG2';

To perform a Failover:

DGMGRL> failover to 'ORCL_DG2';

Getting help.
Aside from the manuals, help can be provided by the aptly named HELP command. Apart from using it to look up all of that lengthy syntax, I find a combination of the help command plus cut and paste is a good way to avoid any DGMGRL related RSI problems....

Locking schema statistics.

2008-07-23T22:44:00.010+01:00

One nice Oracle 10g feature is automatic optimizer statistics gathering.

Out of the box, by calling the GATHER_STATS_JOB during maintenance windows Oracle gathers fresh optimizer statistics for tables where it deems that the current statistics are "stale" (statistics are labelled stale once 10% or more of underlying table rows have been modified in some way). Overall, this is pretty neat. You can even modify the maintenance window start / stop times via Grid Control to suit your specific needs.

However, what about if you want Oracle to perform this automatic statistics gathering for all but a few of your database tables? You may have a few special cases that get loaded / truncated at certain times and therefore you wish to control statistics gathering at certain times, based on your application requirements.

Luckily, the DBMS_STATS package allows us to lock statistics for specific tables or even an entire schema. If you are interested in this, then take a look at the following procedures:

DBMS_STATS.LOCK_SCHEMA_STATS
DBMS_STATS.LOCK_TABLE_STATS
DBMS_STATS.UNLOCK_SCHEMA_STATS
DBMS_STATS.UNLOCK_TABLE_STATS

DBMS_STATS allows you (the DBA or Developer) to unlock a table(s) statistics, gather fresh statistics and then finally lock them up again.

With this kind of approach, the GATHER_STATS_JOB job will automatically gather statistics for most of your database tables, but skip the ones with the locked statistics. This can help protect against any unwanted statistic related performance surprises, something I am always keen to avoid.

Wait event classifications.

2008-07-14T21:15:00.013+01:00

This mini investigation stemmed from a discussion I was having the other day with one of my DBA colleagues:

"Can any of the Data Guard wait events be classified as Idle ?"

Oracle 10g has a total of 878 wait events. Further more, each wait event has been assigned to one of the following classification groups:

Concurrency
System I/O
User I/O
Administrative
Other
Configuration
Scheduler
Cluster
Application
Idle
Network

The data dictionary view, V$EVENT_NAME provides us with all of the wait event names and their related group information. So, to list all of the "Idle" events, we can issue the following SQL:

SELECT name FROM v$event_name
WHERE wait_class='Idle';

As it turns out, there are 62 Idle events, three of which are related to Data Guard:

LNS ASYNC archive log,
LNS ASYNC dest activation,
LNS ASYNC end of log.

Of the 810 remaining "non-Idle" events, just one is classified as "Commit" (log file sync), 590 are classified as "Other" and finally, the most painful sounding is the RAC related "Wait for split-brain resolution" - ouch!

As a side note, Oracle 9i does not have wait event classifications defined by the database itself. However, the Statspack utility does provide similar detail via its STATS$IDLE_EVENT lookup table (which is typically owned by the Perfstat user).

Enterprise Manager Hang analysis.

2008-07-01T22:38:00.027+01:00

In the past, I have seen database locking cause very serious problems. In several cases, databases have become unusable as hundreds of sessions hang, waiting on one or more culprits who are holding a lock on an application "master" table. As a DBA, you need to delve in quickly and work out what is going on as soon as possible. I normally have a bunch of hand written "DBA SQL" scripts ready to use for such occasions. However, there is another (in my opinion, better) approach, the Grid Control "Hang Analysis" page.

Firstly, things have improved over the last few years with this issue in mind. Grid Control can alert us when locking/blocking thresholds are hit. However with the Hang Analysis page, we can quickly see which user session is holding the lock and take action.

To access the Hang Analysis page, connect up to Grid Control, select the database in question, and then select the Performance tab. Scroll to the bottom of the screen and you will find the link labeled "Hang Analysis". When all is good with your system, the page will look fairly plain and boring:

However, if you have a locking issue on your hands, then you will begin to see the page (which is automatically refreshed) provide details of the session holding the lock, plus all of those waiting:

Here, we can see that SID 132 is causing all of our problems and the victims are 149,131,136 and 134 - they are just waiting for 132 to release his/her/its lock(s) before they can continue processing.

At this stage, the Hang Analysis page will highlight the sessions in green, yellow or red. Red being the most serious situation (meaning the sessions are actually hung). At this point, we can click on the red blocker icon for SID 132 and gain the detail we really need and take some action:

We can click on "View Session Details" to find out a extra myriad of detail such as DB User Name, OS Process ID's, Wait States etc:

Finally, by clicking on "Previous SQL" link we can see what on earth our blocker is running:

Having looked at all of that useful information, we can now either call up our user and politely ask them to commit or rollback. More than likely though, if your database has ground to a halt, then return to the previous screen and hit the big nice "Kill Session" button. Most useful!

Now whoever said that us DBA's aren't a ruthless bunch!

The database black box.

2008-06-16T00:36:00.017+01:00

This has to be one of the craziest setups I have heard of in a while...

One of my developer mates was telling me about a new company he is working for and predictably, I had to ask him which database system they are using. As it turns out, his new shop develops under Microsoft SQL Server, but release into Oracle for production. Wow! Now to me, that's just plain madness! It's also pushing what's known as the "Database Black Box" concept to the limit!

Now, I could rant on for a few pages here about why you should have some idea of what is actually going on in your databases engine room, but I would rather point you to a great book on the subject, "Effective Oracle by Design", by Tom Kyte.

As we all know, Tom is a legend in terms Oracle and always provides us with great information and advice. Ultimately, you can take Tom's word for it. Alternatively, you can spend thousands and thousands of Pounds, Euros and Dollars on us DBA's, asking us to come in and solve all of your production performance problems...... Hang on a minute, actually, forget what I said about the book, its erm, just gone out of print.... :)

ORA-1000 maximum open cursors exceeded.

2008-06-13T00:45:00.015+01:00

This is a fairly common Oracle error with one easy fix - up the open_cursors parameter limit in your init.ora or spfile and then go get on with something a bit more interesting. By doing this though, you may be masking over possible greater issues which may come back to bite, normally on a Friday afternoon at around 4:30pm.....

Its always worth delving a little deeper and investigate why the session in question has hit this limit. In the case I was dealing with today, the session in question was running a mass of un-sharable SQL. In fact it had around 7000 SQL statements stored up in the Shared Pool, all syntactically the same except for the final AND clause.

Something along the lines of:

SELECT * FROM apptab WHERE dept=:B1 and ID=543543
SELECT * FROM apptab WHERE dept=:B1 and ID=543544
SELECT * FROM apptab WHERE dept=:B1 and ID=543545

and so on.

The fix was easy, implement another bind variable into the code and pass in the literal values on each execution, such as:

SELECT * FROM apptab WHERE dept=:B1 and ID=:B2

However, the main point is that if I had bumped the open_cursors parameter up and beyond 7000, my problem would appear to go away. However the real issue would be left undiscovered, chewing up and fragmenting my Shared Pool, eventually leading on to those dreaded ORA-04031's.

Ultimately, that's why I like being a DBA - investigating and digging around is the name of the game - One could almost say its a job where you have to be like Columbo (well, almost, perhaps without the dodgy rain coat and cigars).

Being a DBA = fun!

2008-06-12T01:15:00.010+01:00

Now and again, something crops up in the life of a DBA that makes you grin. This little gem popped up today, and I though it was more than worthy of a quick post.

I had a developer send a chat message claiming that he was having problems adding a new constraint to a table. "OK" I said, "send me the SQL and the error, and I'll take a look".

Here's what I got in return:

INSERT INTO user_constraints VALUES
('GL_ARCH',
'GL_VH_PK',
'PRIMARY KEY',
'GL_VERSION_HISTORY',
null, null, null, null, null, null, null, null, null, null, null, null, null, null, null)

ORA-01031: insufficient privileges

Erm, right.... wasn't entirely sure where to start to answer this one.

Dataguard ORA-16072

2008-06-12T00:50:00.004+01:00

Here's a nice Data Guard gotcha I ran into last week, one worth keeping in mind if you ever have to decommission a Standy database:

In this situation, the aim was to remove a Standy database as a part of a server decommission. Easy enough I thought to myself, remove the Archive log destination from the Primary, shut down the Standby and delete - job done. Well not quite....

The following day, the Primary database failed to come back after a cold backup. With the following message in the alert log:

ORA-16072: a minimum of one standby database destination is required Instance terminated by LGWR

What a nice surprise!

Apparently, according to Metalink note 245731.1 this was caused by that fact that there was still some kind of reference in my Primary databases data dictionary, best described by the note itself:

"The Primary Database has still the Protection Mode stored in the Data Dictionary. The Protection Mode requires a running connection to a Standby Database with LGWR SYNC as Log-Transportation Mode. Since the standby is not available anymore,LGWR of the primary terminates the instance."

As luck would have it, the fix is pretty straight forward:

Startup Mount your database,

Issue the following:

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

Shutdown your database,

Start up again.

There you have it, mystery solved....

Luckily for me this was in development, so not too much damage done - well accept for a few angry developers who in the end were easily bribed down our local pub....

SPFile vs PFiles

2008-05-02T17:40:00.006+01:00

Now then, am I one of the few DBA's out there who actually does not like using spfiles ? I was having a discussion about this the other day with one of my DBA chums....

Obviously, spfiles are the future, we have to use them now for fancy Oracle options such as Dataguard Broker, but for some reason, I just like being able to view, edit, change and add comments in a text file.

Further more, this proves that you cannot live without a good old fashion pfile just yet:

ALTER SYSTEM SET control_files=('/u01/oradata/ORCL/control01.ctl','/u02/oradata/ORCL/control02.ctl') scope = spfile;

SQL>ORA-02095: specified initialization parameter cannot be modified

What's that all about then?

To my surprise, values of the control_files parameter cannot be changed directly when you are using an spfile. Here is a work around:

1) Create a good old pfile from spfile,
2) Edit the pfile and change / add / delete my Control file locations,
3) Shutdown database,
4) Move / add / delete the physical control files to reflect the required change,
5) Backup the current spfile,
6) Create a new spfile from the pfile,
7) Startup the database.

Now that's what I call automation :)

VMWare speed

2008-03-04T22:43:00.007Z

I am a great fan of VMware, its a excellent product for DBA's. It allows us to create one or more virtual machines for testing and learning current or new Oracle features without any risk. I use it to play about with Dataguard, RAC, new Oracle versions, Grid Control, RMAN, Partitioning - just about anything is possible. Running it indoors on your home PC (typically with XP as the host O/S) can lead to some challenging performance issues, so here are my top tips for VMWare speed:

1) Memory (yes obvious!) the more you have, the faster its gonna run. I have 4GB installed and tend to set each of my Virtual Machines up with 1GB RAM. With this kind of config, you can happily run 3 virtual database servers at the same time. I have found that if you try to run any more than 3 virtual servers, things start to really slow down.

2) CPU (again, obvious) but with Dual Core and Quad core now coming into play, the more power the better. As a minimum, you really need a top end Pentium 4 to get things going.

3) Tune your host O/S. Now I'm no XP expert, but I have found that switching off features such as visual desktop effects can boost performance. At the end of the day, your machine will be spending CPU cycles on VMWare rather than making your mouse pointer arrow look great.

4) If you do not need your guest O/S to be running X Windows, then switch it off. After installing Oracle and creating my database, I find that setting the default run level to run level 3 really speeds things up. Can't remember how this is done ? Edit the /etc/inittab file as root, and change the following line:

from:

id:5:initdefault:

to:

id:3:initdefault:

5) If you do need to run X Windows, then run it in full screen mode. Running your virtual machine in a Window seems to slow things down quite a bit.

6) Strip out *all* unused services from your guest server - be harsh! As root, run system-config-services and strip out everything you do not need. Services such as sendmail (which is installed by default) can really slow your virtual server down, especially on start up. The less services you have running, the more CPU you will have left to play with. Other services to look at include cups, isdn, kudzu, anything nfs related and the rhnsd service to name but a few.

7) Install VMWare tools on the guest server. This is particularly good for speeding up X Windows if you just can't live without it.

8) Finally, once you are happy with your new virtual machine, shut it down and back it up. You can the use this backup "Golden Image" to create brand new virtual machines in the future without having to go through the pain of reinstalling Linux and the Oracle binaries onto your new target server - this can be a real time saver!

What type of rollback?

2008-02-13T19:28:00.008Z

Several days back I was investigating a database with a slightly unusual performance issue. It was switching redo log files every minute, sometimes twice a minute, which in Oracle terms, is pretty heavy going. Having run a Statspack report for an hour on this database, something else stood out that I had not seen before.

The database statistic "User Rollbacks" were very low, something like 4 an hour (nothing unusual here), however "Transaction Rollbacks" seemed to be very high, around the 250,000 mark. Finally, user commits running at around 500,000 per hour.

My immediate thoughts on this were that although redo generation was high, a large amount of the database transactions were being rolled back too. These rollbacks on their own would be generating a whole load of redo. So, what on earth is the difference between a "User Rollback" and a "Transaction Rollback" ? Surley a rollback is a rollback is a rollback ? Not quite....

Having taken a look at the database redo log files using Log miner, it became apparent that a large amount of insert were failing due to Primary Key checks. Basically, this particular database forms a component of a "message bus" system. and, due to application requirements and message flow, the same messages (or inserts in our Oracle terms) can arrive from several different application sources.

Rather than check to see if the row in question already exists in database with a select statement, the developers of the system had opted to attempt to insert all rows (including the duplicates) and handle the exception of the primary key violations within their PL/SQL code.

Although the main redo log switch issue is still outstanding here (code being reviewed, redo logs getting resized etc etc) I have learned the meaning of a new database statistic:

a "User Rollback" is recorded when a user issues an explicit "ROLLBACK" statement. An "Application Rollback" is recorded when Oracle implicitly performs a rollback, either on behalf of internal operations, or due to an INSERT failures due to constraints or other issues such as tables unable to extend.

Dataguard alternatives.

2008-01-04T20:42:00.000Z

Dataguard has to be my favorite Oracle option. The idea that I have a standby database being kept in sync with my production database gives me an extra level of confidence. Today, Dataguard is becoming very popular, however many small companies avoid it due to cost.

Dataguard is a feature of Oracle Enterprise Edition. Without going into the depths of Oracle licensing, you are required to license your Standby database by the same method used to license your Primary. For example, if you are using the per CPU model on your Primary then you must license your Standby using the per CPU model too. Effectively by using Dataguard, you are doubling your licensing costs for the sake of an insurance that you will hopefully never need to use.

The other cost aspect associated with Datagaurd is the fact that your Standby database server has to be good enough to take on the primary role should the time come. If you have a large Primary database server with many CPU's your costs could be even higher.

However, its not all doom and gloom. There are several other ways that you can put Dataguard to use. Maybe some of these ideas you can help persuade your head of finance that Dataguard is worth the extra spend and really should be implemented as soon as possible at your site!

Reporting
9i and 10g databases can be opened in Read Only mode. Whilst open, changes from production are still received by your standby - they are just not being applied. You can open you Standby in the morning and run all of your business reports against it without effecting the performance of production. At the end of the day, the Standby can then be put back into managed recovery mode. At this point, all of your production transactions will be applied. Effectively this provides an up to date version of your live database for reporting with out having to worry about large import / exports or ETL routines. It also allows you to make use of that extra hardware that you have purchased to run your Dataguard database.

Datapump exports
This is great. With Oracle 10g, you can use your Dataguard database as a data source for imports. For example if you need to refresh a development database with up to date copy of live you could schedule exports to run out of hours, copy them over night to your development server and then finally import the data in question into your development database.

Alternatively, using Oracle 10g, we can open our Standby at any point during the day and use it as a data source, importing data directly into our target database across the network using the NETWORK_LINK Datapump feature. This save a lot of time, especially when combined with the Datapump parallel capabilities.

Backups
Backups (using RMAN) can be shifted from your Primary database to your standby. Although this method (by default) requires the use of an RMAN catalog, it allows you to shift the burden of a large ongoing backup routine away from production, freeing up CPU for your production services.

11g Active Dataguard
11g brings new Dataguard capabilities. In a nutshell a Physical Standby can be opened in read only mode whilst changes are being received from the Primary and applied. This feature allows your reporting to be bang up to date without disrupting production performance. Please note though, this feature does not come free out of the box. It is another licensable option.

Database reallocation
Now and then, every DBA needs to relocate a mission critical production database to another server. Dataguard can help you achieve this and minimize your downtime. The idea here is that you create a standby database on you new target production database server. When the time comes to switch servers just shut down your original Production database and perform a fail over on your Standby (not role reversal). This can avoid hours of downtime as standby databases can be created from hot database backups. Finally, although the standby has been failed over to become your new primary, the original primary is still available as a back out option if you run into any unforeseen issues with your new server.

Hopefully this note has given you a few ideas of alternative uses of Dataguard which can help justify the extra cost. Dataguard could save your business in the event of total disaster at your primary site. It could also save you a lot of time in several other areas as well.

One for the Christmas party.

2007-12-09T19:14:00.000Z

I like this!!!!

Obviously this is totally unrelated to Oracle but hey, its the festive season -time for fun!

While sitting at your desk, lift your right foot off the floor and make clockwise circles.
Now, while doing this, draw the number "6" in the air with your right hand.
Your foot will change direction and there's nothing you can do about it.

Weird!

VMWare RAC goes nuts.

2007-12-09T18:25:00.000Z

Here is a quick hint.

Today, I have been attempting to make some use of 10g RAC running under VMWare on my Windows XP host PC. I had set all of this up some time back, but this has been the first chance that I have had to play with it.

After a few minutes, I hit big issue. Shortly after starting everything up my VMware servers have ground to a halt.

The exact symptoms are:

1) 100% maxed out host CPU, shortly after startup.
2) Virtual Nodes become "virtually" unusable.
3) One of the virtual Linux nodes bails out from the cluster
(the node panic's).

If you are attempting to use VMWare + RAC for learning purposes and you are hitting this problem, here is a work around - with the usual caveat that you should never apply this kind of fix to a production node.

Edit the following file on each node:
/etc/init.d/init.cssd

There will be a SLEEP entry below the following comment in the file:

# Poll every second to see which of our children has gone away.

Change the value of SLEEP from 1 to 60, thus reading:

# Poll every second to see which of our children has gone away.
$SLEEP 60

Having set this on both nodes, restart the cluster and performance will greatly improve - well, it will become usable at least!

If you are going to be using VMWare and Oracle RAC for learning, ideally, you should install on dual core machine with a minimum of 2GB RAM. I have also found that for the extra speed boost, installing Linux as the host machine rather than XP improves the situation too. Obviously, if all else fails, its a good time to ask Santa for that Sun E25K you were always after :)

10.2.0.4.0

2007-11-26T15:33:00.000Z

I came across this note under the Certification section of Metalink today, suggesting that 10.2.0.4.0 will be out by the end of the year.

I guess this release will contain all of the usual bug fixes etc. Word on the street is that it will also include the Real Application Testing recording feature, back ported from 11g.

This has the implication that we will be able to record workload from our production 10g databases, and play it back in our 11g testing environments. Interesting!

Oracle technology workshops.

2007-11-23T21:20:00.000Z

Well everybody, today I took a day out from the world of DBA'ing and ventured out to a free Oracle technical workshop. The idea behind these technology workshops are that they provide customers with a chance to get a hands on introduction to various technology areas such as High Availability, Security, Partitioning, Business Intelligence and todays title, Oracle 10g Data Warehousing.

The workshops all seem to follow the same general format:

Introductions.
Half an hour of PowerPoint preview of the technology in question.
The rest of the day working through a hands on workshop using the technology in question.

The workshops are introduced and run by Oracle instructors who are normally members of the Oracle pre-sales team. Once the opening presentation has been delivered, the instructors remain available throughout the rest of the day to help out and answer all of your Oracle technical conundrums!

Aside from the subject matter, I have to say that these workshop were responsible for my current interest in Visualization - the idea that we can create and run several virtual server machines on our desktops to play with and break without causing any harm. The most impressive configuration I have seen to date consisted of a two node RAC cluster, Database control and Swing Bench all running along happily on a single laptop. Impressive!

As far as todays workshop went, we took a good look at Oracle Warehouse Builder, a brief overview of OLAP, a look at Oracle BI tools and finally an introduction to a really interesting area (which is new to myself) Data Mining - something I really want to investigate further over the next few weeks.

The only dampener for the entire day was the journey home on the London's orbital motorway, the dreaded M25 - the worlds largest car park..... Still, I'll be able to get over that with some counseling! In the mean time, 10 out of 10 to Oracle for a great day.

Oracle VM

2007-11-13T10:30:00.000Z

This is exciting - I have been a fan of visualization for a while now, specifically, I think it is a great tool to use when learning new Oracle features (particularly the ones that require several servers, such as Dataguard, RAC and Grid Control etc).

Oracle has just announced that it is launching its own virtualization software "Oracle VM". You will be able to download it for review from tomorrow. Check this link for further details.

Real App Testing.

2007-11-11T00:52:00.000Z

Most people who are keeping up with the new 11g features will be aware of Real Application Testing. For those of you who have not had a chance to see this yet, this feature allows DBA's to record production database workload into binary files, copy the files over to a development database server and then replay the transactions in exactly the same way as they were applied in live. Obviously this feature is going to be great, especially for platform migrations, hardware change testing , security patch set and database patch set version testing etc.

A few weeks ago, I was attending an Oracle 11g technical overview in Reading and made a couple of interesting notes about RAT which may be of use:

Note 1.
Apparently, the load recording facility of RAT is going to be back ported to Oracle database 10.2.0.4.0. This has two implications:

1) Workloads can be recorded from a 10g production system and then replayed in your 11g test environment. This means that this facility will be available to DBA's who are going to be working on new 10g to 11g upgrade projects.

2) The keen eyed DBA will notice that the latest version of 10g is 10.2.0.3.0. Apparently 10.2.0.4.0 will be out "sometime soon". I guess the back port of RAT functionality would explain why its taken a while for 10.2.0.4.0 to hit the streets.

Note 2.
Real Application Testing is going to be a cost option. Other new features which are also at extra cost include: Active Dataguard, Advanced Compression and Total Recall. I guess this was to be expected, but I will be interested in seeing what the best "free" new feature of 11g is going to be (when I say free, I mean included within the cost of the initial Database license).

Finally, DBA's must be looking forward to bamboozling Project Managers with the new 11g terminology.

A prize goes out to the first DBA to mention "RAT RAC" in a project update meeting.... :-)

Manage 9i with Grid Control.

2007-11-09T15:38:00.000Z

Obviously, there are quite a few shops out there still running 9i. I guess there are several reasons for this, the main one (from my experience) is the cost of testing a new database version and the cost of the upgrade downtime, although 11g is going to rescue us from all of that tedious load testing with the fantastically named RAT! (Real Applications Testing for those who are not in the know yet).

If you are still running 9i, one of the things that you may or may not know is that you can still manage your 9i Oracle database with 10g Grid Control. Once you finally get around to upgrading, Grid Control can easily be modified to take care of your new 10g database.

Here is a quick overview of how all of this works:

How to manage a 9i database with 10g Grid Control.

Step 1.
Start a 10g listener process on your 9i database server. The only feasible way of doing this (that I know of) is to install the 10g database software in a separate ORACLE_HOME, and then use the 10g binaries to start the 10g listener. Initially, this may seem a bit of an overkill, but you will eventually be using the 10g binaries for the database upgrade process.

Step 2.
Install the Oracle 10g Grid Control agent on your 9i server. Once the agent is up and running, your 9i database can be viewed and managed by your 10g Grid Control configuration. Obviously, not all the same features are available as a 10g managed target, but for alerting and general maintenance, this kind of setup is still ideal. Lets face it, you really don't want to be using the old 9i Java based OEM do you?!!!

After you have upgraded your 9i database to 10g
Once you have upgraded your managed target database from 9i to 10g, the following steps can be taken to inform Grid Control of your change:

1. Stop the Grid Control Agent which is running on you managed server.

2. Edit the following file, which is stored under your agents ORACLE_HOME on your managed server:

ORACLE_HOME/sysman/emd/targets.xml

For the database in question, edit the following following line to reflect your new 10g ORACLE_HOME :

(Obviously substituting the value of your own 10g ORACLE_HOME)

3. Start up your Grid Control Agent.

4. Go get a cup of coffee at this point - the new changes take a few minutes to filter through.

5. Log into Grid Control, and check that your old 9i database is now being recognized as a 10g database.

Direct I/O and Redo writes.

2007-11-08T11:13:00.000Z

Here is a subject that has bothered me for some time. When you make a database change and then issue a subsequent COMMIT, how can you guarantee that the change has been recorded and written to disk? I guess the real question is: Are you really sure your transactions are safe?

If we take a look at the Oracle database architecture, we are told that when we issue a commit, the change which has already been recorded in the Redo Log buffer is flushed to disk.

Linux likes to utilize all available memory, so for example if the O/S reports having free, then a good chunk of this will be used for O/S I/O caching. We can verify this with the free command:

free -m
total used free shared buffers cached
Mem: 6022 5983 38 0 26 3736

Notice here that my Linux machine is using over 3GB of the physical 6GB for I/O caching.

Caching at the O/S level is generally a good idea, however when we specifically look at Oracle, this mechanism has the affect of double caching our database data blocks and skewing our disk write time performance statistics. Database block writes (via DBWR) are no longer disk writes, they become cached disk writes.

The follow up question that springs into mind may also be: What about LGWR ? Does this also imply that when a user commits, the redo log entry is written to the Linux I/O cache before being written to disk? If so, surly I can then loose changes, even after they have been posted to disk..... This is something I needed to pursue with Oracle for peace of mind.

According to Oracle, the LGWR process uses an O/S option called "Direct IO". Direct IO instructs Linux to write directly to the disk I/O subsystem. Basically, bypassing the O/S I/O buffering mechanism. As a DBA, this makes me feel better. But we are not there yet.

Further investigations in this area (this time via Redhat) provides the final piece of the jigsaw. Although Direct IO does bypass the O/S cache, it cannot instruct your storage devices to bypass any physical caching that is taking place. In my case, I have direct attached storage with a controller which is caching reads and writes. By default, I also have physical disk read/write caching too! - All of these options are configurable. But, out of the box, in my environment, I have 2 levels of caching for LGWR, and 3 levels for DBWR ( DBWR does not implement DIRECT I/O by default*)

Ultimately, this investigation demonstrates that we DBA's do need to understand the O/S and Hardware that we are using. Secondly, we should not take everything we hear at face value.

I have heard several times (even on DBA training courses) that once a commit is issued, the change is guaranteed to be written to disk. However, if you have not thought about the extra possible levels of software and hardware caching there will be a split second when Oracle thinks it has written your valuable change to disk, but in fact it will be hanging around in a hardware cache - some place between your database server and the physical disk platter , just waiting for that inevitable power cut!

* Incidentally, DIRECT I/O can be enabled for DBWR by using the filesystemio_options database parameter. This, I will save for another entry.

Primary startup trouble after removal of Dataguard.

2007-10-26T22:44:00.000+01:00

Here's a bit of a unique problem that I ran into the other day. One of those moments that can raise your heart rate until you realise what is going on!

I had a Primary production database and a Standby, which was running in Maximum Availability mode. The Standby database configuration was no longer required, and so it was totally shutdown and deleted. The Primary LOG_ARCHIVE_DEST_n parameters were reset. As far as I was concerned, the Standby was deleted for good. End of story.

Then, some time later, the Primary database was shut down for maintenance. When it was started up again, I received a nasty surprise:

ORA-03113: End-of-file on Communication Channel - What!!!???

Having looked at the alert log, there are further error messages:

ORA-16072: a minimum of one standby database destination is required Instance terminated by LGWR

ORA-03113: End-of-file on Communication Channel

Hmmm, this is interesting. Despite the Standby database being decommissioned, my Primary is still expecting it to be there. After some scouting around on Metalink, it turns out that the protection mode of my primary database is still stored in my data dictionary.

The protection mode that I was using still expects my Primary to be talking to a Standby using LGWR SYNC log writer modes.

If I issue the following SQL, I can then start up my Primary Production without any issue:

SQL> STARTUP MOUNT;
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

I have to say that you would be unlucky to run into this problem as it does require a certain set of circumstances, but its the kind of thing you can do without early on a Saturday morning database restart!

For further details, here is the Metalink note ID: 245731.1

I'm back!! (plus Dataguard tip).

2007-10-24T12:00:00.000+01:00

Yes Web fans, after a few hectic months I am back updating my blog...!

Since the last update, I have been seriously busy, although have enjoyed sneaking out to a few Oracle UK events such as the 11g launch day and 11g tech days in Reading.

I have to say that 11g looks great, but I am not as amazed by it as I was when 10g arrived on the scene. I guess 10g really made a change to how DBA's work. Some of the 10g feature's such as Datapump, AWR and Grid Control generally have made our jobs a lot more interesting and improved the DBA role for the good.

Some DBA's I have spoken to even hold the view that 11g should have just been 10gR3. I'm still in two minds about that, although I have to say I really like the new 11g Dataguard features. I guess we will only be able to make that claim either way after we see what is on offer in 11gR2.

Anyway, if you get a moment, Check the new Dataguard features out here: http://www.oracle.com/technology/deploy/availability/index.html

On with the tip......

Previously with Dataguard, I had always assumed that the file system layout of your Standby database had to match your Primary. For example, if your database datafiles on your Primary server reside under /u01, /u02, u03, /u04 and u05, then I though that the Standby disk layout should be exactly the same. Turns out, this is not the case.

After the first stage of a recent disk migration project, my Primary database datafiles were merged together onto a new RAID 1+0 volume known as "/u01". My Standby database datafiles were stored under their original file system layout on the Standby server, u01 .. u05. This has no effect on Dataguard whatsoever, which I have found really useful.

First of all, this allows me to run the disk migration over several days, rather than having a "big bang" approach. Secondly, I also have a quick back out plan if I run into any serious issues with my new storage or disk layout - I still have the option of failing over to my up-to-date Standby database with the added bonus that it is still residing under the original disk configuration.

There is one area you need to be aware of if you are going to be running with this mixed disk environment and that is the value set for your standby_file_management parameter. If this is set to "auto" and you add a datafile on your Primary database to a directory that does not exist on your Standby, then you are going to run into problems.

As long as you keep this in mind, you should not run into any issues whilst running your Primary and Standby databases under different file system layouts for an interim period. Very Handy.

Session Stats.

2007-08-17T12:55:00.000+01:00

A V$ dictionary table that I find very useful is V$MYSTAT. When joined onto V$STATNAME, it can be used to capture over 300 statistics about your current session. Using these two tables, I like to:

1. Capture baseline session statistics,
2. Run a piece of SQL that I am investigating,
3. Re-capture the V$MYSTAT statistics,
4. View the difference between these two "snapshots".

Here is an example of how this works. For the sake of keeping this simple, I suspect that my test SQL has a problem:

SELECT * FROM big_tab WHERE id=243213;

(The name "big_tab" could be a bit of a give away)

First of all, I am going to create a temporary table to store my session statistics. The reason I am using a temporary table is because writing to this table type causes very little over head. In other words, capturing my session statistics should not taint my result sets. The other thing to say is that the rows held in my temporary table will be cleared out once I quit my session.

CREATE GLOBAL TEMPORARY TABLE MY_STATS
(RUN_ID NUMBER,
NAME VARCHAR2(64),
VALUE NUMBER)
ON COMMIT PRESERVE ROWS;

Each time I gather my session statistics, I am going to give that set of statistics a "Run ID". This will allow me to compare the difference between the values gathered by Run ID 1 and Run ID 5 for example.

Now, I am going to capture the set of statistics from my session to form a baseline. This will be Run ID 1. As mentioned above, there are over 300 statistics available from V$MYSTAT, so you can modify this capture script to suit your own investigations:

INSERT INTO my_stats(
SELECT
&Run_id,
sn.name,
ms.value
FROM v$mystat ms,v$statname sn
WHERE ms.statistic# = sn.statistic#
AND sn.name in('session logical reads',
'recursive cpu usage',
'CPU used by this session',
'DB time',
'user I/O wait time',
'physical read total IO requests',
'physical read IO requests',
'physical read total multi block requests',
'physical read total bytes',
'db block gets',
'db block gets from cache'
'redo synch writes',
'redo synch time',
'redo log space requests',
'redo log space wait time',
'physical reads',
'enqueue timeouts',
'enqueue waits',
'physical writes',
'physical writes direct',
'physical writes from cache',
'physical write bytes',
'physical write total IO requests',
'DBWR undo block writes',
'DBWR make free requests',
'DBWR checkpoints',
'commit cleanouts',
'commit cleanouts successfully completed',
'redo buffer allocation retries')
);

My "Populate Stats" script above will prompt me for a Run_ID, for the baseline, I will give it "1" as a value. I can now run my poorly tuned SQL that is under investigation:

SELECT * FROM big_tab WHERE id=243213;

I then re-run my "Populate Stats" script, and provide a Run_ID of 2. Finally, I run another simple SQL. This one is going to look at the statistics gathered by my first and second Run_ID's and display the difference between the two sets of statistics:

Set linesize 132
Set pagesize 100
col START for 999,999,999,999
col END for 999,999,999,999
col DIFF for 999,999,999,999
SELECT a.name,
a.value "START"
b.value "END",
b.value - a.value "DIFF"
FROM my_stats a,my_stats b
WHERE a.run_id=&start_run_id
AND b.run_id=&end_run_id
AND a.name=b.name;

Here is an example output from this run:


NAME                            START     END          DIFF
------------------------------------------------------------
recursive cpu usage             12       12           0
session logical reads           1,468    62,472       61,004
CPU used by this session        14       158          144
DB time                         20       757          737
user I/O wait time              7        657          650
enqueue timeouts                0        0            0
enqueue waits                   0        0            0
physical read total IO requests 29       7,652        7,623
physical read total mbrs        0        7,623        7,623
physical read total bytes       237,568  499,785,728  499,548,160
physical write total IO requsts 0        0            0
db block gets                   4        11           7
db block gets from cache        4        11           7
physical reads                  29       61,009       60,980
physical read IO requests       29       7,652        7,623
physical writes                 0        0            0
physical writes direct          0        0            0
physical writes from cache      0        0            0
physical write bytes            0        0            0
DBWR undo block writes          0        0            0
DBWR make free requests         0        0            0
DBWR checkpoints                0        0            0
redo synch writes               3        3            0
redo synch time                 0        0            0
commit cleanouts                1        1            0
commit cleanouts suc completed  1        1            0
redo buffer allocation retries  0        0            0
redo log space requests         0        0            0
redo log space wait time        0        0            0

In this case, the number that stands out is the value for "Physical Reads Total Bytes". A further investigation would show that "BIG_TAB" is missing an index from its ID column. Using this kind of approach helps me investigate bad SQL.

The more interesting aspect is that with multiple runs, you can compare a bad piece SQL with a re-written piece of SQL, and demonstrate how much better the code is after the re-write.

Here I am going to add an index to "BIG_TAB" and reproduce the statistics for comparison. Lets take a look at the statistic results:


NAME                            START        END          DIFF
------------------------------------------------------------
recursive cpu usage             40            40            0
session logical reads           82,540        82,551        11
CPU used by this session        924           925           1
DB time                         1,870         1,873         3
user I/O wait time              361           363           2
enqueue timeouts                0             0             0
enqueue waits                   0             0             0
physical read total IO requests 24,049        24,051        2
physical read total mbrs        7,624         7,624         0
physical read total bytes       634,159,104   634,175,488   16,384
physical write totalIO requests 2,438         2,438         0
db block gets                   13,071        13,078        7
db block gets from cache        1,202         1,209         7
physical reads                  77,412        77,414        2
physical read IO requests       24,049        24,051        2
physical writes                 26,619        26,619        0
physical writes direct          26,619        26,619        0
physical writes from cache      0             0             0
physical write bytes            218,062,848   218,062,848   0
DBWR undo block writes          0             0             0
DBWR make free requests         0             0             0
DBWR checkpoints                0             0             0
redo synch writes               1             1             0
redo synch time                 1             1             0
commit cleanouts                96            96            0
commit cleanouts succ completed 96            96            0
redo buffer allocation retries  35            35            0
redo log space requests         6             6             0
redo log space wait time        35            35            0

Now I can see what a difference the index has made and prove that the number of physical reads has been greatly improved by my fix. The "Difference" (DIFF) column show that the new SQL only reads 16,384 bytes to satisfy the query rather than the previous full table scan, which read close to 500MB.

Download 11g

2007-08-10T08:21:00.000+01:00

At last Oracle database 11g is available for download. At this point in time, it is available for Linux users at:

http://www.oracle.com/technology/software/products/database/index.html?rssid=rss_otn_soft

Also download the documentation here:

http://www.oracle.com/technology/documentation/database11gR1.html

Thats going to keep us busy for a while !

Trouble with Control file auto backups.

2007-07-31T15:03:00.000+01:00

Here is a trap to avoid when using a Flash Recovery Area and Control File Automatic backups.

I had a scenario in development where one of my databases was performing a Control File Automatic backup, but always writing it out to my $ORACLE_HOME/dbs directory rather that my Flash Recovery Area:

backup piece handle=/opt/app/oracle/product/10.2.0.3.0/dbs/c-923992598-20070731-00

I decided to review my RMAN settings. I could not see anything obviously wrong, although I suspected the problem was being caused by my CONTROLFILE AUTOBACKUP FORMAT setting:

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';

I compared this to a working auto backup and could not see any differences. What was going on here?

I understood that if my CONTROLFILE AUTOBACKUP FORMAT has been set to the default value, and I have a Flash Recovery Area, then my Control File automatic backups should be written to my Flash Recovery disk location.

My next move was to reset CONTROLFILE AUTOBACKUP FORMAT as I suspected that something had gone wrong here. Maybe some kind of corruption (yes a bit of a long shot) :

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;

Immediately, this solved my problem - my automatic control file backups are now correctly. They are now being written to the correct disk area (my Flash Recovery Area).

See if you spot the difference between the broken and working versions of my CONTROLFILE AUTOBACKUP FORMAT settings:

Broken: CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
Working: CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

There is not too much difference!

Shared pool, did you know......

2007-07-27T11:27:00.000+01:00

In Oracle 9i and 10g, the Shared Pool can be divided into subpools. Each subpool can be seen as a "mini" Shared Pool, having its own set of free lists, memory structure entries and LRU list. This allows Oracle to scale much better on larger machines. Under the previous model, you would have one entire Shared Pool created for your instance no matter how much memory and CPU was available.

In 9i and 10g, Oracle calculates how many subpools your instance will have as follows:

First, a subpool must be at least 128MB in 9i and at least 256MB in 10g.

Second, there can be one subpool for every four CPUs on your server with a maximum of up to 7 subpools.

You can view the current number of subpools on your system by issuing the following SQL:

SQL> SELECT MAX(KGHLUSHRPOOL) FROM x$kghlu;

The number of subpools can be changed on your system by using the hidden parameter _kghdsidx_count. I would not recommend changing this value unless explicitly told to do so by Oracle support.

List patches and CPU's from SQL.

2007-07-25T13:43:00.000+01:00

To quickly find out which database patches and Critical Patch Updates have been applied to your system, run the following SQL:

SELECT
action_time,
action,
namespace,
version,
comments
FROM dba_registry_history;

Here is a sample output:

ACTION_TIME ACTION NAMESPACE VERSION COMMENTS
------------------------------------------------------------
08-JUN-07 08.43.54.614748 UPGRADE SERVER 10.2.0.3.0
Upgraded from 9.2.0.6.0

06-MAR-07 13.20.39.149920 CPU SERVER 10.2.0.3.0 CPUJan2007

25-JUL-07 12.46.49.261502 CPU SERVER 10.2.0.3.0 CPUJul2007

Flashback database and archive logs.

2007-07-23T23:20:00.000+01:00

I hit an interesting problem last week (on a Friday, just before going on leave for a week - typical). A developer asked me to Flashback a development database to two weeks in the past. The database DB_FLASHBACK_RETENTION_TARGET was set to equate to 31 days.

As usual, I shutdown and mounted the database and issued the Flashback command:

SQL> FLASHBACK DATABASE TO SCN 954635;

Normally, Flashback database runs without any issues, but this time I was hit with:

ORA-38754: FLASHBACK DATABASE not started; required redo log is not available.

Cause: A FLASHBACK DATABASE command did not start. A redo log needed for the recovery part of FLASHBACK DATABASE could not be found or accessed.

Action: See trace files for details of the problem.

At this point I was worried! I had never hit this problem before. Upon investigation, it appeared that the database was looking for an Archive log file to compliment the Flashback operation.

I knew that to use the Flashback database feature I needed to enable ARCHIVELOG mode, but I had never made the connection that Flashback Database required both the Flashback database logs and Archive Log files to perform a Flashback operation.

As this was a development database, my RMAN backup scripts delete Archive log files once they are backed up. Luckily, my Archive Log file backups are stored on disk in my database Flash Recover Area. Using RMAN, I manually restored the missing Archive Log files, then successfully ran my Flashback Database command.

Having got out of this mess I decided to investigate further. Here are my findings so far:

10g Backup and recover reference.
The Oracle Backup and recover reference guide states that:

"RMAN uses flashback logs to undo changes to a point before the target time or SCN, and then uses archived redo logs to recover the database forward to make it consistent. RMAN automatically restores from backup any archived logs that are needed."

I found this not to be the case - I had to manually restore my Archive Log files before my Flashback operation would work.

"The earliest SCN that can be used for a Flashback Database operation depends on the setting of the DB_FLASHBACK_RETENTION_TARGET initialization parameter, and upon the actual retention of flashback logs permitted by available disk space"

True, but the document fails to mention "and the retention of your Archive Log files".

I still have some questions around all of this, which I am going to research and post as soon as possible:

- As far as Flashback Database is concerned, How far back should I keep my Archive Log files? I now believe this should at least equal my DB_FLASHBACK_RETENTION_TARGET.

- Why did Oracle not automatically restore my Archive Log files? I am going to have to revisit my trace file and possibly open an SR.

Ultimately, I think Flashback database is excellent. It saves a lot of time. Our developers run test upgrade scripts and then I Flashback the database. They can then re-run their scripts after they have fixed any bugs. This is much faster than my previous 9i restore and recover operations. However, I will be needing the answers to my questions to feel 100% happy with Flashback database!

10g RAC interface check.

2007-07-15T01:31:00.000+01:00

If you need details regarding your RAC server network card configuration, you may be interested in the "Oracle Interface Configuration tool" ("oifcfg" for short).

To detail your servers interfaces, run:

oifcfg iflist
eth0 192.168.2.0
eth1 10.10.10.0

To view further information, run:

oifcfg getif
eth0 192.168.2.0 global public
eth1 10.10.10.0 global cluster_interconnect

As you can see, the "getif" switch can be useful, as it details which NIC is being used as a public facing interface and which NIC is being used for the Cluster Interconnect.

10g Logins per second.

2007-07-12T17:07:00.000+01:00

How many logins per second does your database service? How can you work this out? Well, you could do something funky with Unix/Linux shell commands and your listener log file, or you could run the following SQL:

SELECT
to_char(begin_time,'DD-MON-YYYY HH24:MI:SS') START_TIME,
to_char(end_time,'DD-MON-YYYY HH24:MI:SS') END_TIME,
metric_name,
value
FROM
v$sysmetric
WHERE metric_name='Logons Per Sec'
ORDER BY begin_time;

10g now keeps this information in the V$SYSMETRIC view. You will receive two rows back. One row details how many logins per second your database has endured over the last 15 seconds, the other gives details from a 60 second period. This information is also available in Enterprise Manager, but real DBA's don't go near the GUI stuff, right??!*

*Actually, I have to say that 10g Grid control is great. Until 10g, I would not use a GUI to manage any aspect of an Oracle database, but I now find Enterprise Manager and SQL Developer really usefull.

Write once, run many.

2007-07-09T23:06:00.000+01:00

Here is a quick way of running the same SQL query or command for each database running on a particular database server. I find this useful in development, where I have about 10 separate databases running on the same machine. This can save quite a lot of typing!

At your Unix / Linux shell prompt, run:

cut -d : -f1 /etc/oratab
while read db
do
export ORACLE_SID=$db
echo $db
sqlplus -s / as sysdba <<EOF
SELECT created FROM v\$database;
exit
EOF
done

The command:

- Grabs each database name from your /etc/oratab file
- Loops through each database, setting the ORACLE_SID
- Starts up SQL*PLUS without the banner, and connects into each database in turn
- Runs the SQL (highlighted in blue).

Note that if you need to use a dollar sign in your SQL, then you need to proceed it with a "\". This prevents it from being interpreted by your Unix / Linux shell, and messing up your script.

I also find this useful if I need to quickly run an RMAN command on each database, such as:

RMAN> report need backup;

Improve your passwords.

2007-06-26T21:48:00.001+01:00

One of the things you may know about Oracle is that by default, users are allowed to have a one-character password, for example:

SQL>ALTER USER scott IDENTIFIED BY a;

This is obviously a security risk (although great for your users if they do no like typing...).

How can this be fixed?

The answer is to create a password function and combine the function with user profiles. Oracle provide an example script for creating a password verify function, stored under your $ORACLE_HOME/rdbms/admin directory called utlpwdmg.sql

This script creates a function which ensures that when your users change their password:

Their username and password are not the same,
their password is >4 characters in length,
simple passwords (like oracle and password) are not allowed,
their password contains at least one letter, one digit and one punctuation mark,
their password differs from the previous password by at least 3 letters.

I am not going to go through the script here in fine detail, but the idea is that utlpwdmg.sql provides you with a starting point. It is easy to edit, enter your own password rules or strip out the rules that you do not need.

The script should be run by the SYS user account. By default, it will create a password verify function called "verify_function". Again, the function can be renamed if you like.

Once the function has been created, it can be called by a Oracle user profile. Every database user is assigned a database profile at creation. If the DBA does not assign a profile, then Oracle will assign the user the default profile (called DEFAULT).

Here is an example of the creation of a new profile called "dev_profile":

SQL>CREATE PROFILE dev_profile
LIMIT
failed_login_attempts 3
password_life_time 60
password_reuse_max 3
password_grace_time 5
password_reuse_time 30
password_verify_function verify_function;

The new profile is then assigned to a database user, SCOTT:

SQL> ALTER USER scott PROFILE dev_profile;

Now, the user SCOTT is governed by my new database profile "dev_profile". Whenever SCOTT tries to change his database password, the "verify_function" function will ensuring that the new password remains within the rules specified. If not, SCOTT will receive an error message, for example:

ERROR:
ORA-28003: password verification for the specified password failed
ORA-20002: Password length less than 8 chars

As you can see, a combination of database profiles and password functions can be very useful. You can assign different profiles to different groups of users depending on their business function (for example one profile for developers, one for report writers etc).

Finally, there are several limits that you can assign to the profiles, such as the "failed_login_attempts" listed above.

I will run through these limits in my next post.

Move CLOB index segment (Redefine online part 2).

2007-06-23T01:09:00.000+01:00

Here is another use for DBMS_REDEFINITION:

Suppose I have a table called "X".
A column called DETAILS, which is a CLOB.
Both the table and CLOB index segments are stored in a tablespace called "USERS"
And I want to move my CLOB index storage to a tablespace called "USERS2":

I create my table X, and a temporary table called X_TEMP.
X_TEMP has a storage clause for my CLOB index:

--This ends up in my default tablespace users.
SQL> CREATE TABLE x
( id NUMBER PRIMARY KEY,
details CLOB);

SQL> CREATE TABLE x_temp
( id NUMBER PRIMARY KEY,
details CLOB) STORE AS (tablespace users2)
TABLESPACE users;

I check that my X table is currently storing the index LOB in the USERS tablespace with:

SQL> SELECT tablespace_name
FROM user_segments
WHERE segment_name=
( SELECT index_name
FROM user_indexes
WHERE table_name='X'
AND index_type='LOB' );

TABLESPACE_NAME
------------------------------
USERS

I then use DBMS_REDEFINITION to swap the two tables
(you can do all of this with the tables online and in use).

SQL> exec dbms_redefinition.START_REDEF_TABLE('SCOTT','X','X_TEMP');
exec dbms_redefinition.FINISH_REDEF_TABLE('SCOTT','X','X_TEMP');

At this point, my original table X becomes X_TEMP. X_TEMP becomes X.

I now re-check my index CLOB storage for my table X:

SQL> SELECT tablespace_name
FROM user_segments
WHERE segment_name=
( SELECT index_name
FROM user_indexes
WHERE table_name='X'
AND index_type='LOB' );

TABLESPACE_NAME
------------------------------
USERS 2

All done!

Interestingly, this is the only way it seems that you can move a CLOB index segment once the table has been created and it is in use. There are no fancy ALTER TABLE MOVE.... options available here.

11g is on its way.

2007-06-20T12:07:00.001+01:00

Its does not seem that long ago since the arrival of Oracle 10gR1 - although it was released four years ago back in 2003!

Now it is time for us DBA's to get back onto the learning curve as the release date for Oracle database 11g has been set for July 11th 2007 - Exciting!

Unfortunately I have not had time to join the 11g beta program - I have been heavily involved with 10g upgrades. Now that they are out of the way, I am looking forward to getting my hands on a copy of 11g as soon as it becomes available next month.

Last minute Larry.

2007-06-08T08:28:00.000+01:00

Sometime back, somebody told me that Oracle 10g was to be originally called Oracle 10i, until a last minute name change by Larry. I'm not sure how true this story really was, but whilst upgrading a 9i database to 10g this morning, I noticed this message in my alert log:

ALERT: Compatibility of the database is changed from pre-10i to 10.2.0.3.0.

Hmmm, maybe there is some truth in this story after all.... I bet the marketing guys were pleased!

Right, that's it for now, myself and my family are off to Atlanta, in the US of A for a well deserved holiday. Don't worry, I will be back updating in a couple of weeks! :)

Get SCN.

2007-06-05T16:59:00.000+01:00

Here is a quick one for you. If you ever need to find out your current System Change Number (SCN), you can run the following query:

10g:
SQL> SELECT current_scn FROM V$database;

9i:
SQL> SELECT dbms_flashback.get_system_change_number FROM dual;

Most people will know about the 10g query, but the 9i one is quite useful. This raises another point - Did you know that Flashback Query was actually available in Oracle database 9i. The rest of the Flashback features arrived in 10g.

Redefine online.

2007-06-03T22:43:00.001+01:00

Several days back I had a developer ask me how he could change a column datatype from a NUMBER to a VARCHAR2 in production without any downtime. As far as I can see, there are several ways of doing this:

We could use: ALTER TABLE xyz MODIFY mycolumn VARCHAR2 (30); But the problem with this is that the column in question would have to be empty - zero rows.....

We could add a new column, migrate the data then drop the old column - but that's messy. The column position would change in the table and I would end up locking the table for a long time whilst each row is updated.

Enter one of my favourite Oracle supplied packages, DBMS_REDEFINITION .

DBMS_REDEFINITION allows you make changes to online tables beyond the scope of the ALTER TABLE .... MOVE syntax. For example, I can change a column's attributes, change a Heap (standard table type) table into an Index Organised Table, transform table data or modify storage.

In this tip, and my next, I am going to give two examples of how DBMS_REDEFINITION has helped me avoid application downtime during the day.

The general procedure is to create a new replacement table (with the new definition), begin the redefinition process (at which point Oracle copies data from my target table into the new table), and finally perform the actual table switch (at which point Oracle switches the two physical tables online).

There are a few questions to ask yourself at this time such as:

What about all my dependencies (constraints, synonyms etc)?
How can this happen online?
What about locking when the switch is made?

Do not fear, DBMS_REDEFINITION is going to take care of all these issues.

Here, I have a (simple for the sake of example) table, which is loaded with data, and in use:

SQL> DESC CUSTOMER

Name Null? Type
-----------------------------------------------------
ID NOT NULL NUMBER
CUSTOMER_NO NUMBER
COMPANY_NAME VARCHAR2(30)

I wish to change my CUSTOMER_NO column from a NUMBER datatype to a VARCHAR2.

First of all, I create my "interim" table called INTERIM_CUSTOMER. This has my new column definition. This table is going to have my CUSTOMER_NO column defined as a VARCHAR2.

SQL> CREATE TABLE interim_customer(
id NUMBER PRIMARY KEY,
customer_no VARCHAR2(30),
company_name VARCHAR2(30)
);

table created.

Note that this table has a Primary Key. In 9i, if you wanted to use DBMS_REDEFINITION on a table, it had to have a Primary Key. However, 10g does offer a way around this.

Next, I use DBMS_REDEFINITION to check that it can work with my CUSTOMER target table.

SQL> begin
dbms_redefinition.can_redef_table('scott','customer');
end;
/

If this procedure returns no errors, then we are in business.

Next, I am going to begin the redefinition process. At this point, Oracle will copy (not move) all of my rows from CUSTOMER to INTERIM_CUSTOMER. The "col_mapping" parameter allows me to specify how columns are to be mapped between my old and newly defined table. I can also convert data on the move (in this case, the blue text demonstrates that I have converted my NUMBER data using the TO_CHAR function).

SQL> begin
dbms_redefinition.start_redef_table(
uname=>'scott',
orig_table=>'customer',
int_table=>'interim_customer',
col_mapping=>'id id, to_char(customer_no) customer_no,
company_name company_name');
END;
/

The next step is to ask Oracle to sync over any table dependencies (such as constraints, indexes etc). I could do this manually after I have created my interim table, but instead, I am going to let Oracle do all of the hard work:

SQL> DECLARE
num_errors PLS_INTEGER;
BEGIN
dbms_redefinition.copy_table_dependents(
UNAME=>'scott',
ORIG_TABLE=>'customer',
INT_TABLE=>'interim_customer',
IGNORE_ERRORS=>true,
NUM_ERRORS=>num_errors);
END;
/

Oracle has now copied my data and built my table dependencies. I can either perform another data sync or make the switch. When the table is switched, there is a brief point at which the target and interim tables are locked. The idea of the data sync is to update my interim table with any new rows from my target. When the switch is made, Oracle will also run a final sync. The less data there is to sync, the less time the tables are locked. You could choose to create the interim table over night, and manually run the syncs during the day, right up to the point of the swap. Here is the data sync syntax:

SQL> begin
dbms_redefinition.sync_interim_table ('scott','customer','interim_customer');
end;
/

OK, now I am ready to go. I am going to ask Oracle to switch the tables (online). Once this is complete, CUSTOMER will become INTERIM_CUSTOMER and INTERIM_CUSTOMER will become CUSTOMER.

CUSTOMER will have my new VARCHAR2 column, replacing my old NUMBER column. Finally, all of my Synonyms will point to CUSTOMER, Indexes will exist, and constraints will still be in place - I can confirm this really does work, after lots of testing and several production runs!

SQL> begin
dbms_redefinition.finish_redef_table('scott','customer','interim_customer');
end;
/

SQL> DESC CUSTOMER

Name Null? Type
---------------------------------------
ID NOT NULL NUMBER
CUSTOMER_NO VARCHAR2 (30)
COMPANY_NAME VARCHAR2(30)

Next time, I will run through how a LOB INDEX segment can be moved to another tablespace with DBMS_REDEFINITION, again, all online. This is something that you cannot do with any standard SQL.

High Availability LOL.

2007-05-31T15:54:00.001+01:00

As much as I love Oracle, I had to laugh at this..... whilst roaming around Technet today, I tried to enter the "High Availability" section:
http://www.oracle.com/technology/deploy/availability/index.html

and I got:

I guess even the best have a bad day.......

10g Dataguard status view.

2007-05-30T09:54:00.000+01:00

Here's a quick tip of the day if you are using Oracle 10g and have a Standby Dataguard database. The V$DATAGUARD_STATS view (not to be confused with V$DATAGUARD_STATUS!) lists up to date details on how quickly your Standby database could be opened, and how much redo would need to be applied to bring it up to date:

SQL> col NAME for a30
SQL> col VALUE for a30
SQL> set linesize 132
SQL> SELECT * FROM v$dataguard_stats;

NAME VALUE UNIT TIME_COMPUTED
----------------------------------------------------------------------------
apply finish time day(2) to second(1) interval 30-MAY-2007 09:54:31
apply lag +00 00:17:48 day(2) to second(0) interval 30-MAY-2007 09:54:31
estimated startup time 12 second 30-MAY-2007 09:54:31
standby has been open Y 30-MAY-2007 09:54:31
transport lag +00 00:00:00 day(2) to second(0) interval 30-MAY-2007 09:54:31

Here, Dataguard is telling me that I am almost 18 minutes behind my production database and it will take approximately 12 seconds to apply the extra redo required to bring my Standby up to date. Finally, the view informs me that I have not specified any transport lag between my Primary and Standby, and that my standby has been previously opened.

The missing redo is sitting in my Standby Database Standby Redo Logs and will be applied when I open the standby for read only, role reversal, or fail over. If I was using the new 10g Real Time Apply Dataguard feature, Oracle would be applying redo to my Standby Database as soon as it hits my Standby Redo Log files. This feature further reduces my recovery time on my Standby by keeping the redo apply gap between my Stanbdy and Primary commits to a minimum.

Very useful!

Oracle speed installs.

2007-05-15T09:12:00.000+01:00

If, like myself, you need to install Oracle software on several target servers, then you may be interested in the Oracle installers recording functionality. In this case, I have around 20 target machines to install the Oracle client software. Once I have downloaded the software, I install it onto my first machine using the Oracle Universal Installer (OUI) in "record" mode:

./runInstaller -record -destinationFile /public/dist/contrib/Oracle/Oracle-10.2.0-client/client
/response/10gR2Client.rsp

Using this command, I am telling Oracle to create a "response file" which contains all of the actions that I have taken for this particular installation (this will include the Oracle Home that I choose, the software options I select, etc).

Once the installation is complete, my 10gR2Client.rsp file can be used to automate (skip the Java GUI) the next installation. At my site, I have a software repository which is shared out via NFS to all target servers. Therefore, I include my 10gR2Client.rsp file in that area. I can then log onto the server as the Oracle user, run my script and leave the install to do its work, so for example:

mkdir -p /opt/app/oracle (create my new Oracle Home)
/public/dist/contrib/Oracle/Oracle-10.2.0-client/client/runInstaller -silent
-responseFile /public/dist/contrib/Oracle/Oracle-10.2.0-client/client/
response/10gR2Client.rsp

The key parameters here are:
silent: Meaning do not start the GUI installer
responseFile: Gives the name of my previously create response file.

Finally, once the install is complete, Oracle will prompt me to run the root.sh file as the Root user (as it always does).

The good news about all this method, is that you can use it to install patch sets, and the Oracle database software itself. I find this has helped me avoid hours of time messing about with the GUI Oracle Universal Installer, and allows me to get on with something more interesting.

Datapump go faster.

2007-05-11T12:16:00.000+01:00

In addition to the database parameter changes made in my Datapump article, Metalink note 376969.1 suggests that to improve performance, you should set the following parameters in your target database before running the import job:

_spin_count = 4000
session_cached_cursors = 800
cursor_space_for_time = true
db_cache_advice = OFF

Therefore, when you are performing a Datapump import, review and give appropriate values to all of the following parameters in your target database.

# The following are set larger for datapump import
pga_aggregate_target=specifictoyoursite
db_cache_size=specifictoyoursite
large_pool_size=specifictoyoursite
shared_pool_size=specifictoyoursite
_spin_count = 4000
session_cached_cursors = 800
cursor_space_for_time = true
db_cache_advice = OFF
statistics_level=basic
cursor_sharing=EXACT
# Comment out when import is complete

Obviously set them back to their original values before restarting the database for normal use.

I found that the parameters suggested by note 376969.1 brought my 4 hour export/import job down by 15 minutes - every little helps!

Datapump from standby.

2007-05-08T16:41:00.000+01:00

If (like myself) you spend a large slice of your DBA time transferring schemas and table data from production to development, then the new features of 10g Datapump are here to save your day (well, hours of work anyway).

Scenario
We have a 50GB production database with around 25 live schema's. They cross reference each other with Foreign Keys, triggers, procedures and packages. Not massive, but quite complex. On a regular basis, one particular project requires around 15 of the schemas to be transferred to a development database. This note will show you how things used to get done (pre 10g), and what benefits can be gained by using the new Oracle data transfer utility, Datapump. In particular, I will be looking at the new network link capabilities, and the possibility of exporting data from an Oracle standby database.

For this example, some schemas in my production database consist of data and audit tables. I will be taking the data, the structure of the audit tables, but not the audit data itself. For reference, I will be calling this type of export a "partial" export. With the remaining schemas, I will be taking all of the schema structures and table data. Again, for the purpose of this note, I will be calling this a "full" export.

Here is the general procedure I would have used, when running under Oracle 9i.

1) Clear out the old data from my target development database.

2) Run the exports from Production one evening (not during the day for performance reasons):

One export for all of the schema's with full data sets.
An export of data for each partial schema.
An export of the schema metadata (schema object definitions) for each partial schema.

3) The following day, import the "full" export into my target development database.

4) Load in the metadata definitions into my partial schemas.

5) Manually disable all of the constraints and triggers for my partial schemas.

6) Load the partial table data into the partial schemas.

7) Manually re-enable all of the constraints and triggers.

8) Check each schema against live to make sure the imports are looking good.

Finally, after running a backup, gathering optimizer statistics gathering, running some data cleanup scripts (for security) and resizing my SGA, the entire task can take up to a day to complete. I will call this the "Manual Labour Refresh" method.

Enter Datapump
The first thing to say about Datapump is that it is fast, very fast. The second is that when using it in combination with the Network Link feature, you can export data directly out of your Standby (Dataguard) database, directly into your target database - no dump files required!

Here's how it works....

Prepare the standby database
The first thing to do is open my Standby database in read only mode. In addition, I will be adding a temporary tablespace. This can be achieved via the usual syntax:

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL>ALTER DATABASE OPEN;
SQL>ALTER TABLESPACE temp ADD TEMPORARY TEMPFILE '/u01/oradata/PROD/temp01.dbf' size 1G;

I will also be making a modification to the parallel_max_servers database parameter, to avoid an Oracle bug (see Metalink note number: 392689.1):

SQL> ALTER DATABASE SET parallel_max_servers=0;

At this point, my Standby database is ready to go. One thing to note is that if my redo changes are being transferred from my Primary database to my Standby using Log Writer, the transfer will still continue.

My standby will continue to create archive log files, they will just not be applied whilst the database is in read only mode. This is good news. It does mean that my Standby would take longer to open as a Primary in an emergency, but I still have all of my redo sent over from Production ready to be applied.

In a nutshell, read only mode does not compromise my recoverability.

Prepare the target development database
Next, I need to prepare my target development database and server. First of all, I need to set up an entry in my TNSNAMES.ora file to enable connection to my Standby database.

Once completed, I then need to create a Database Link in my target development database to point to my Standby. This is going to be the link which allows Datapump to drag the data out from the standby, and import it into my target:

SQL>CREATE DATABASE LINK standby CONNECT TO SYSTEM IDENTIFIED BY xyz USING 'STANDBY_LIVE';

In my case, I have run this as the System user. Of course if you are worried about security, you can create a special Datapump user, and grant the appropriate EXP_FULL_DATABASE / IMP_FULL_DATABASE roles.

The connect string 'STANDBY_LIVE' should be the same connect string name as the one found in my TNSNAMES.ora file.

Next, I need to create a Datapump database directory. Even though I will not be using dump files for the import, I still need a directory for Datapump to be able to write its log files.

$ mkdir -p /opt/backup/exports/datapump

SQL> CREATE DIRECTORY data_pump_files AS '/opt/backup/exports/datapump';

The next step is optional, but I like to resize my SGA as large as possible to speed up the import. Another trick is to add larger redo log files to my target database. The larger my redo log files, the less log switches, the faster things will run.

Next, for my development database, I will be setting my CURSOR_SHARING parameter equal to 'EXACT'. Again, this is to avoid a Datapump related bug (see Metalink note number: 416238.1).

Finally, I take my target database out of Archive Log mode. Again this will help speed things up once the import is underway.

Run the Datapump import.
This is where the fun begins. I like to create a parameter file (parfile) for Datapump. This will save me time if I ever need to re-run all of these tasks again. Here is a sample parameter file for this particular job:

NETWORK_LINK=STANDBY_LIVE.mydomain.com
DIRECTORY=DATA_PUMP_FILES
PARALLEL=8
SCHEMAS=HR,GL,WIKI,CMS,FINANCE,FINAPP,SCOTT
JOB_NAME=imp_full
LOGFILE=imp_full.log
QUERY=finance.AUDIT_LOG:"WHERE ID <0"

NETWORK_LINK allows me to specify the Standby database as my data source. Here, I am giving the name specified by my database link created earlier on.

PARALLEL is a new feature which, divides the import task between x number of worker processes. This is a great new feature of Datapump which really does give me a speed boost. Pre-10g, I only ever had one process working on an import job, no matter how large the task, even if I had an 8 CPU machine.

SCHEMAS is fairly self explanatory. A list of schema's to export and import.

JOB_NAME specifies a name for my Datapump job. This allows me to disconnect from the job, and reconnect later on and check the job status.

QUERY is the other really useful feature. Here I am telling Oracle that I want my finance.AUDIT_LOG table, but without any rows with an ID greater than 0 (in other words, I want the table structure without any data).

You can specify multiple QUERY entries in your Datapump parfile, enabling you to pick and choose the data you want.

Let's go!
All that is left to do now is to run the import. For this, I use the impdp command, and specify my parfile name:

$> impdp parfile=full_imp.par

Oracle will prompt me for a username and password, and then spring into life. The Datapump utility build its own internal "Action list" table based on my parameter file, and then start dragging the data out of my standby, across the network and into my target!

Sit back and relax.

Final tasks

Once my Datapump job is complete, I will need to put my Standby Database back into managed recover mode. Dataguard will apply any newly created archive log files, and recover my standby database. Then, Dataguard with continue to apply any new redo as it arrives from my primary site - just as it ever did.

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

I then need to downsize the SGA on my development database, reset the CURSOR_SHARING parameter, re-enable Archive Log mode, and drop my database link. Once all of this is complete, my new development database is ready for use (I will also be running my security scripts and performing a database backup after all that hard work).

Final word

So to summarise, at my site, 10g Datapump reduces a day of manual labour down to 4 hours of automated work. The NETWORK_LINK capability allows me to run my Datapump job during normal working hours, without disrupting the performance of my Production database. The Query feature helps me avoid the multiple imports and exports I used before. Finally, the PARALLEL feature gives Datapump that well needed power boost over the old import / export utilities.

I hope this gives you some new idea's of how 10g Datapump can improve your daytime DBA duties!

Don't get undone by 10g Undo.

2007-05-02T16:39:00.000+01:00

Here's and interesting thing I discovered after upgrading our 9i production databases to 10g....

I was using a 2GB Undo tablespace for my 9i production database. My undo_retention parameter was set to 3600 (1hr) and this was sufficient during daytime and evening processing.

Less than a day after upgrading to 10g, my Undo tablespace became full. I added another datafile bringing it up to 2.5GB; Again, it was full again within about 1 hour. This required investigation. Why such an increase of Undo usage for 10g?

It seemed I had become a 'victim' of the new 10g automatic undo management feature. Here are the details of what I discovered.

Under 9i, the database would try and honour the undo_retention parameter. Obviously if the Undo tablespace free space became under pressure, Oracle would start reusing Undo segments that had been retained retained for consistency, but not in use by active transaction. Things are different with 10g, and the main contributing factor to how things work is the status of autoextend mode setting for your Undo tablespace datafiles.

No autoextend.
If you are using no-autoextend (the setting I still prefer), Oracle will ignore your undo_retention parameter unless you have your Undo tablespace set up for retention guarantee.

Without retention guarantee, Oracle will just use as much space as possible for Undo retention.
I experienced my Undo tablespace hit 97% full (with all of the EM alerts that come with this territory). After a while, usage dropped back down and become stable at 95%. With no autoextend set for your Undo tablespace, the more space you give Oracle, the more it takes. This makes sense really, but was not the case in 9i.

With autoextend.
Oracle will use the undo_retention parameter as its "lowest threshold" point. It still calculates its own value for undo retention and applies this value to your Undo tablespace. What this means is that once the database has made up its mind about how much Undo it thinks it will need to satisfy all of your long running queries and flashback features, your Undo tablespace datafile will begin to grow (and grow, and grow)!
In this situation, you need to monitor, and make sure you have lots of free disk space ready.

You can view Oracle's self calculated retention value by examining at the value held in the TUNED_UNDORETENTION Column of the V$UNDOSTAT view. Another useful tool is the "undo Management" page in Grid Control. Here we can view a graph that shows how much free space in your Undo tablespace. How much space is taken up by active transactions, and finally, how much is taken up by "Retained for Use by Queries or Flashback". In my situation, 99% of my free space from my Undo tablespace was taken up by "Retained for Use by Queries or Flashback". In other words, Oracle was retaining as much undo data as possible to satisfy my possible need for flashback features, or data point in time consistency.

To summarise, here is my advice in this area.

Use the autoextend feature for your Undo tablespace datafiles with caution. Make sure you have lots of free disk space, as this will grow until Oracle thinks it has hit the correct size to satisfy your current transactions, and undo retention. If you are unsure, do not enable autoextend for this tablespace.
If you are going to switch off autoextend, then monitor your Undo tablespace with Grid Control and / or the V$UNDOSTAT view.
Keep an eye open for ORA-01555's "snapshot too old" errors. This could be a sign that your database cannot read enough "retained" undo to satisfy a long running query.
If you are leaving autoextend switched off, then it is usual under 10g for Oracle to use up as much space as possible from your Undo tablespace. Monitor that you have enough space for "Active transactions" and you are not encountering ORA-01555's.

Fire on Moorgate!

2007-04-25T17:13:00.000+01:00

A slightly worrying event occurred this morning - I was in the middle of logging an SR with Oracle when I was rudely interrupted by next doors office catching fire.... Not the kind of thing that happens every day....

We were evacuated for several hours from our office whilst the Fire brigade arrived to sort things out. Luckily, everyone was OK, but I think the insurance bill is going to be pretty damn big.

Too many session_cached_cursors are bad for your health.

2007-04-25T09:35:00.000+01:00

In Oracle 9.2.0.5.0, a new database parameter became available called SESSION_CACHED_CURSORS. At the time, the other important cursor related parameter was OPEN_CURSORS.

In Oracle versions 9.2.0.4.0 and lower, OPEN_CURSORS controlled the maximum amount of cursors any one particular session could open. If the value for this parameter has been set too low, your applications could run into trouble. Because of this, many DBA's were cautious, and over sized the value of OPEN_CURSORS in their init.ora's . The side effect of the OPEN_CURSORS parameter was that it also set the size of each sessions PL/SQL cursor cache (which was allocated from the Shared Pool).

Since 9.2.0.4.0, the SESSION_CACHED_CURSORS parameter arrived as a part of the 9.2.0.5.0 patch set. Oracle describes this parameter as "Specifies the number of session cursors to cache. Repeated parse calls of the same SQL statement cause the session cursor for that statement to be moved into the session cursor cache". Basically, this parameter allows DBA's to have better control over the size of each sessions PL/SQL cursor cache.

A simple mistake to make with SESSION_CACHED_CURSORS is to set the value too high. Most applications should be fine with a starting point value of 50 (the default is 0 in 10g).

If the value for SESSION_CACHED_CURSORS is set too high, for example, equal to the value of OPEN_CURSORS, you will be over estimating the number of cached cursors required, and creating a larger cursor cache for each database session.

So, why does all this matter? - Well, as each session allocates its cursor cache from the Shared Pool, you can run into a situation where the Shared Pool becomes fragmented and / or the free memory can become exhausted.

With 10g, this problem becomes even more apparent, as there is an increase in the amount SQL battling for space in the Shared Pool. I have found that this is caused by the new performance monitoring features brought by the AWR and ADDM features.

A combination of a poorly set SESSION_CACHED_CURSORS parameter and the extra space pressure on the Shared Pool brought by the new monitoring functionality can push the Shared Pool over the edge. This in turn leads to the dreaded "ORA-04031, unable to allocate X amount of bytes of shared memory" error messages.

In most cases, ORA-04031's cause your applications to fail, and can eventually lead to database downtime - the last thing us DBA's need!

So, to summarize: be careful with the SESSION_CACHED_CURSORS parameter - it can lead to trouble if set incorrectly. If you wish to use it, start with a value of 50, and monitor. This will keep you and your database out of trouble.

Read! Read! Read!

2007-04-21T11:09:00.000+01:00

Unless (unlike mine) your company has a mega Oracle training budget, I find the only way I can really keep on top of Oracle is by reading as much about the subject as possible. That's why I have added a book list on my Blog. These books are the ones that I really recommend reading. Most of them contain information that you must know to do your job. I guess the 10g certification ones are not essential, but if your thinking about getting certified, then stick to the Sybex books. They are defiantly the best.

In reality, I think it would be almost impossible to know everything about Oracle. Even if you specialise. So my main moto is to remember that the learning never ends!

Aside from reading, here are my other tips on how to learn about Oracle and Oracle products:

Download Oracle software and play.
Get certified (Oracle, not mentally).
Work on as many different Oracle projects as possible.
Read websites such as technet, oracle.com and asktom.
Participate in Oracle forums (helping people).
Attend Oracle University.
Read the Oracle documentation.

Orable ORA-07445's

2007-04-19T23:35:00.000+01:00

Yes, I got some of these in my 10g production db today. I think over the years this error has to be one of the top ten common Oracle Alert log errors that I have seen. Generally, these turn out to be caused an Oracle bug or some crazy application coding.

An ORA-07445 is a pretty serious internal database error, and is enough to send shivers down the spine of even the most hardened Oracle DBA. Your users will most probably suffer when you encounter one of these beauties, and in extream cases, your instance may crash or freeze up. But keep a level head, its time to be cool and investigate as soon as possible.

Here's a few tips:

1) Log onto metalink , click on the knowledge tab, "database" in the product category nav, then "ORA-600 / ORA7445 Error Lookup by Argument". This will give you a lookup tool which can give you some idea as to what's going on.

2) Do an search for the failing function in the "advanced search" feature on metalink. For example if your getting:

ORA-07445: exception encountered: core dump [xsiofmgrMTSOpen()+35] [SIGSEGV]
[Address not mapped to object] [0x18] [] []

in your alert log, then "xsiofmgrMTSOpen" is know as the "failing function"

3) Open up an SR with Oracle - its likely your going to need some help with this one.

4) Upload your alert log and trace files to Oracle.

5) Upload the output from RDA. I'm gonna do another post about RDA for beginners as this is a very usful tool...

6) Check your application log files, there may be further information that is not being presented in your alert.log

7) Have a look through the trace file created by the core dump. You will find them stored under your $ORACLE_HOME/udump directory.

8) Think about a work around. For example, today's error on our database was breaking Shared server connections, so I have the option to temporarily switch those users to dedicated connections until the problem is resolved. Not great, but it keeps my database alive.

9) Think about what else is going on at the time of the error. Any unusual batch runs? New application modules, patches, more users that usual?

10) Grab a statspack snapshot or AWR snapshot and investigate. It may be that your database is under unusual load, and that is what is bringing the database bug to life.

11) Employee a human firewall. Your helpdesk or boss - someone, anyone to take the flack from your userbase whilst you get down to fixing the problem.

12) Keep everyone informed, and prepare them for possible down time. You may need to apply a patch to get around this problem.

Good luck with your investigations.

Running RAC at home.

2007-04-18T23:24:00.000+01:00

OK, so like me, you probably haven't got yourself a spare pair of servers a storage array and a bunch of switches just hanging around in your garage, so how are you ever going to get to mess around with 10g RAC ? Well, the answer to all of your problems is a combination of VMware, Oracle Enterprise Linux and Oracle 10g database.

Check out this link on Oracle Technet to find out the fine details of how you can get RAC installed and up and running on your bog standard Windows XP PC:

http://www.oracle.com/technology/pub/articles/chan-ubl-vmware.html

Here is a (very) brief overview to give you an idea of how it all works:

VMware.
VMware allows your PC (or server) to host one or more additional operating systems (posing as physical servers). In this case, I can use VMware to host two or more Oracle databases servers. Pretty cool. You basically have to download, register and then install VMware on your XP machine. The next step is to specify the physical attributes (spec) of your first virtual server. How many disks? Network cards? How much memory? etc.

Note that the disks you specify map to actual physical files on your PC's hard drive, and the memory is allocated from your host OS. So, at this point you may be getting the picture - performance is not too hot (in fact at some points of this install its painfully slow).... but hey, were not running our 10,000 tps OLTP system here, this is just a bit of fun and learning!

OK, once your guest server has been defined, and you have started it up - the real fun begins!

Oracle Enterprise Linux.
Next, download Oracle Enterprise Linux and install. Why Oracle Enterprise Linux? Well, its basically a complete clone of RedHat Linux with new badges on the front. Why is it better than Redhat? Well in my opinion, its not (yet), but it is free to download and use, so its great for this setup.

Install Enterprise Linux on VM server number 1 & 2.
Once you have completed the install on VM server number 1, you can then shut it down, and copy the physical VMware files to "clone". Your clone copy of VM server 1 is used to quickly create a second virtual server. Yes you guessed it, were going to be creating a two node Oracle RAC cluster here.

Once both virtual Linux servers up and running, you can move onto the next stage, installing Oracle 10g. Obviously I have brushed over the details of how you create a clustered file system, set up the multiple network interfaces to be used for your public and private networks etc etc - all of this is very well documented by the link above.

Install Oracle.
OK, the final steps involve configuring the ASM disks, OCFS disks, installing Oracle Clusterware, installing Oracle 10g and finally creating a clustered database. Once completed, you will find you have the following to play with (all running under Window XP, which I still find crazy!):

Oracle Enterprise Linux,
Oracle 10g,
Oracle Real Application Clusters,
ASM,
Enterprise Manager Database Control.

OK, so I can confirm that the procedure from the Oracle website works a treat, but here are my additional tips:

1) Set aside a few days to do this, because it is damn slow! Yes, XP is hardly the most performant O/S, but when it is running all the other stuff, you need patience...

2) Ram in the RAM. I had 2.5GB of RAM installed on my PC. I gave each Virtual server 1GB and left 500MB for the host. I would say 2GB is the minimum. Honestly.

3) Set up your PC to manually start the VMware services when you need them rather than automatically. If your just going to use your day to day XP stuff, the VMware background services can slow things down, so don't leave them running.

4) Back it up. Yes, I know your not at work now, but it does take hours to set this stuff up, so do yourself a favour!

5) Once up and running, shut down X on both of the virtual VM servers. This generally speeds things up a bit.

6) Don't make the mistake of setting up your ASM disks in a 3 way mirror (High Redundancy). Remember that this is all mapping back to one physical disk on your PC.

7) If you can, install another hard drive in your PC (I did). That way you can have your virtual servers running off of one disk, and leave XP to do its stuff on the other.

And that's it! Finally, I really do think VMware is a great tool. Think what else you can use this for. Dataguard, RMAN testing, impressing your other DBA mates, the list is endless.

Hello everybody....

2007-04-18T22:59:00.000+01:00

Welcome everyone to my Blog, which will hopefully overtake Oracle Metalink in popularity over the next coming weeks :)

I will be mainly posting tips and (my own) views on Oracle RDBMS and life as an Oracle DBA. In addition, I will be adding other less IT related stuff to liven things up a bit, and maybe some of my photos for you to check out from a future project I have in mind called "Lunchtime London" - which may get off the ground in about 12 years from now if I ever find the time ;-)

OK, so a brief intro about myself: I am an Oracle DBA and have been working with Oracle products for over 8 years now. My platform of choice is Unix, and of course the ever popular Linux. Having worked on Oracle since the days of Oracle 7, I hope to post tips I have picked up over the years, and of course learn from your great comments and suggestions.

OK, well enough of the intro, lets go!!!