Oracle Error Messages You Don't See Everyday:  ORA-03111

I've been working with Oracle software for 25 years now.  It is still amazing to me, that after all this time, I will run into error messages which are not new, but are new to me.  Once example of this is my recent exposure to an ORA-03111.  Anecdotal evidence from the web indicates this may be a pre-Oracle8 error.   

Related to the network interface layer of Oracle (SQL*Net), the short explanation for this error from Oracle makes it seem even more obscure:

[oracle@MSSNKAODB015 ~]$ oerr ora 03111

03111, 00000, "break received on communication channel"

// *Cause:

// *Action:

In layman's terms, anytime there is interference in communications in a three-tiered transaction, bad things usually result.  In this case, a Java application was passing a larger than average packet size from a unix client to the database server.  Sometimes this can be mitigated with changes to the client configuration (see http://royontechnology.blogspot.com/2009/06/mysterious-ora-03111-error.html ), other times it can be handled with a database SQL*Net Configuration change, or a client SQL*Net configuration change.  The database software configuration changes are discussed in detail in Oracle Support note ID 44694.1.  In summary these are changes to sqlnet.ora on the server's listener.ora configuration file, as follows:

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
        (SDU = 8192)         <- 8192="" br="" for="" sdu="" setting="" sid="" this="" to="">         (TDU = 8192)         <- br="" nbsp="" position="">         (SID_NAME = V112)
        (ORACLE_HOME = /oracle/product/11.2.0)))

Or the client sqlnet.ora configuration file:

TEST = 
(DESCRIPTION =
(SDU=8192)
(TDU=8192)
(ADDRESS=(PROTOCOL=TCP)(HOST=bill.johndoe.com)(PORT=1521))
(CONNECT_DATA=(SID=V112)))

Ideally, an application will be analyzed and possibly modified so that it does not exceed network standards and default values for these parameters.  In this case, the application was third party, making doing so quite difficult, so a change to the client sqlnet.ora did the trick.

An Oracle Tuning Exercise Followup

After my last post, I took some good-natured heat from my colleagues for avoiding the deep, probing look into tuning for which  they all seem to yearn.  I believe they missed my point - that the exercise itself must be defined before you can begin to search for problems.  Lo and behold, I have again been asked to do a tuning assessment, and I will be doing yet another assignment to evaluate a client database for performance.  This one I know much less about, so it's even more important that a professional approach be taken.  Hopefully I will post about that project sometime soon.


In the meantime, I thought it only fair that I follow-up on my prior post, since so many people have asked about the result.  In summary, we had several queries which showed up as high-resource consumers in an AWR (automatic warehouse repository).  Their purpose was to show critical measurement metrics for a business, using some complex algorithms and computations.  While one would think the computations were the problem, I assure you that with today's modern hardware and software, they seldom are.


What did happen was that a parameter being passed to the queries, as a bind variable, was defined as a varchar(2) string in the database, yet the application was passing a number.  Because of this datatype difference, an index was not used, and several full table scans per query resulted.  This can be a common problem, but is often difficult to spot.   When running an "explain plan" on the queries with the bind variables in place, it showed that the indices would be used.  An AWR  listing will only show the bind variable representations (:1, :2, :3 etc) as well.  To see the actual values of these variable, you must capture the query in memory, and then query dictionary view v$sql_bind_capture to see the actual values being used.  When we actually caught the queries running live in the system, and detected the actual bind variable values, we saw that they were not quoted strings, but numbers.


For example, a query on this simple table....

create table test_table
(pk_col varchar2(5));

With the following (indexed) primary key....

alter table test_table add primary key  (pk_col);

Such as....

select *
from test_table
where primary_key = 5;

...would do a full table scan!


The reason is that the number 5 is not a character string, and the column is defined as a character string.  This particular set of data was zero filling all numeric values.  If there is a row in the table with a value of "00005", Oracle will do the interpretation and pass the number back, but has to examine and compare each row to do so.  If there were millions of rows, the results would not be returned quickly!

The best solution for this, it was decided, was to change the application code so that a 5-position quoted string was always passed in the variable.  Sometimes changing the code is not an option.  In such a case, another way to go would have been to create a "function-based index", such as:

create index abcidx on test_table to_char(lpad(pk_col,5,'0'));

This index, on the same column, uses two oracle FUNCTIONS to assure the resulting compare field is:
a) Zero-filled to the left and,
b) Always five character positions.

This will result in an index being used.


So much for the details on my little encounter.  I am all about keeping things as simple and understandable as possible when discussing the most highly technical of subjects.  In this case, luckily, I could!   There were also a slew of Oracle parameters that we will be changing to aid performance, and I made those recommendations to those in charge to implement, but they are quite dry, involve SGA size, and are outside the scope of my discussion for today.  When the time comes, we will discuss removing the nasty AMM (automatic memory management) from your Oracle database, as it almost never helps at all!

Oracle Database Tuning Approach

I have been co-opted to do this tuning exercise in my "spare time".  I was chosen since this was the database for the project I had worked on during my first assignment with this client.  This was a mere five months ago, so I suppose my knowledge of the application, data model and environment would give me an "edge". 

The assumption was that yours truly would be quickly able to narrow down the options and focus on the exact problem.  The client was desperate for a quick solution, and therefore was urging me in that direction.


This "silver bullet methodology", as I'll call it, works real well in Werewolf Horror Movies - with database tuning, not so much!


There are several very expert blogs out there to cover exactly HOW to tune a database.  I will refer to my colleagues such as Kerry Osborne http://kerryosborne.oracle-guy.com/ and Cary Milsap http://carymillsap.blogspot.com/ for the exact technical process to follow.  Follow their lead, and you can't go wrong.  But where do you start, especially when assumptions and analysis by key players have already been made, and you are expected to act on these?




This leads me to my (unofficial) pragmatic, political database tuning approach (with apologies to  the experts):

 1)  Assume Nothing.

 This covers the thought that I could somehow walk right in and fix something immediately because I'd worked on it before.  I must admit that vanity comes into play here.  A good, professional DBA can easily make the same mistake.  Don't assume problems which occurred before, on this system or any other, are the same ones you're seeing today.  

This also covers cases where a new, faster machine or an improved set of disks simply MUST make the database run faster.

 2)  Believe No One.

 This is also covered by #1 above.  It is a strange assumption to believe that just because someone fixed something once, they already have the solution.  It may mean they are competent, or even, an expert.  In this particular case, I've already been lead to some assumed causes and solutions (from the recent past) which are already proving to be dead ends!  

 3)  Objectively measure.

 Don't believe it when people say performance is "bad".  Bad is a relative, subjective term.  It is not a technical term.  Before declaring something bad and worth addressing, quantify it in clear terms.

 4)  Use standard tools.

 This covers the developer or DBA who comes running at you with a slow SQL statement he ran yesterday at 3pm that the explain plan says costs a gazillion dollars.  Using Grid Control or an AWR report is not only more precise, objective and standard, these are supported tools that you've already paid a lot of money to have, and will be useful if you must report the problem to the vendor.

 5)  Review and confirm configuration.

 This could also be covered under item #1, but is a step that should be taken before assuming a cluster is really clustering, or a memory management setting is actually implemented as intended.  I've already determined that something is not right with the SGA, and the DBAs flatly denied the configuration was ever meant to be the way it is.  Now how could that have happened?  I would never have found out if I accepted what I was told (see #2).

 6)  Define measurable goals.

 So as not to insult the client, I did not list this first.  However, before "fixing" anything, someone needs to define what "fixed" is.  You can't always make a database run fast enough to satisfy everyone, so define a reasonable goal.  Not doing this may result in you no longer being invited to future tuning parties, but doing it eloquently is important as well. 

 7)  Document methodology and results.

I am going to skip the tuning specifics.  Once you've done all of the above, these will be self-evident.  Present your results in a professional, easy to read manner.  Sometimes, the fixes do not involve the database itself at all.  This can become a political issue if the recommendations cross organizational boundaries (the disks or networks are not configured properly, or the application code really blows!!!).  

Dropping a Database in Oracle

In my last post, I documented the syntax for creating a database using as brief a syntax as possible. Beginning in Oracle 10g, it is now possible to merely enter "create database" in sqlplus, and successfully create an instance. Along with this sparse syntax comes a new, even more esoteric concept: The ability to DROP a database in the same manner.

In prior versions of Oracle, removing a database was something of a manual endeavor, and was therefore error-prone and often incomplete. It involved removing files and directories tied to an instance by hand, and might leave tattered evidence of a previous database's existence that Grid Control and other monitoring tools might pick up and report on. In a worst case scenario, I've seen these databases come to life in a zombie-like fashion after certain system events like reboots, simply because someone failed to remove a parameter from a file which was setup as the "root" user during installation, and therefore not addressed during the manual drop.

The new command attempts to address all such issues. By merely saying "drop database" while connected to the appropriate software and with the ORACLE_SID setting to match, a database "goes away". This is, of course, a destructive operation! It requires the database to be in a restricted mode, and it cannot easily be recovered from, once executed (which leaves it a good time to mention that before ANY command is executed against an Oracle database, a recovery plan should always be considered).

Command script:

SQLPLUS> connect / as sysdba
SQLPLUS> shutdown abort;
SQLPLUS> startup mount restrict;
SQLPLUS> drop database;

It's as simple as that!

Check around your system for the last vestiges of the database you just dropped. While this command will address everything directly attached (datafiles, online logs, controlfiles, alert logs, etc), it will only do so for locations defined in the database at the time the command was entered. This might mean that logs or scripts that were relocated at different points of time would not be removed.

While I think this command is an excellent tool for cleaning up, at least a cursory look around the system after execution is certainly a must. And don't forget grid control, if you're using it. Monitor this server after a period of time and all remnants of it should be gone.

DBA Minimalism

DBA Minimalism


I went to an Oracle seminar once, and the instructor told us that as of version 10g, it was possible to create a database in Oracle from the sqlplus command line with the sparse verbiage “create database;” and nothing else. He then challenged us to figure out how it could be done. Keep in mind, that as an Oracle DBA for about 20 years, I have seen “create database” clauses, on certain platforms, with certain options, that ran for pages of SQL in a single statement. Specifications for the command in the oracle manual are here:

http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_5004.htm

The complete description, including all options for CREATE DATABASE covers 21 pages of the manual!!!

There is also a new command called “drop database;” and both of these were likely implemented to make oracle configuration simpler and more consistent with the other RDBMS. So I took up the challenge, and was successful in creating a database in this manner. I did this as an exercise, and because I believe it teaches some interesting concepts as to exactly how oracle works. It could also be useful to quickly stand up an instance to verify the software install, meet a tight deadline, or validate available resources for a more complex build later on.


MINIMAL REQUIRED SETUP

Starting an Oracle instance still requires, at a minimum, a valid initialization file, with certain specifications, sufficient system memory and disk space, and system rights to create files and access system memory. Your user session (usually called “oracle” on most systems, needs ENV settings as well. Many shops use the “oratab” file to set ORACLE_HOME and ORACLE_SID and other settings, so be sure to add this database to that file, which is something that the Oracle installer does anyway.

I called my database, for these purposes “mynewdb”. Here is the sparsest initmynewdb.ora initialization file possible to create a database:

control_files='/u02/oradata/mynewdb/sys1mynewdb.ctl','/u02/oradata/mynewdb/sys2mynewdb.ctl','/u02/oradata/mynewdb/sys3mynewdb.ctl'
db_block_size=8192
sga_max_size=500000000
shared_pool_size=150m
db_name='mynewdb'
undo_management='AUTO'
undo_retention=25200
undo_tablespace='SYS_UNDOTS'
db_create_file_dest='/u02/oradata/mynewdb'

Some of these parameters are required, depending upon your system specifications, and others just make the database more useful and self documenting (such as the bdump, cdump and udump directories). The above assumes that directories specified for control_files and db_create_file_dest already exist. Before trying this, create them and check carefully that they are spelled correctly. If not, your startup will fail.

After running “create database;” with the above, a running database exists. However, the catalog and dictionary still need to be updated to make it truly useful. This is done by running the traditional catalog and catproc scripts:

Sqlplus > @?/rdbms/admin/catalog
Sqlplus > @?/rdbms/admin/catproc


In the log above, I also create a temporary tablespace, a users tablespace (both considered minimal best practices), and make a list of users. As you can see, 7 are created by the Oracle binaries. An additional step would be to make those tablespaces the temporary and default respectively for each of thoese users. This can be easily scripted.
Of course, if you have requirements for options or features such as partitioning or statspack, scripts to create those optional objects supporting them will also need to be run. Your default database is also minimally secure, and should probably have default passwords for users changed, but that is considered out of the scope of this discussion. Even though more work may need to be done, you can say your database is up and running!


HINTS AND TROUBLESHOOTING

I’d be lying if I said this worked the first time. On most systems, the storage parameter defaults are insufficient to support opening the database. That is why parameters such as sga_max_size and shared_pool_size are specified for the solaris 64-bit example above. If your first attempt fails, be sure to remove controlfiles and any directories generated by the failed creation attempt.

My next document will highlight the new “DROP DATABASE” syntax. While it sounds as simple as pie, like anything else Oracle, there are caveats and a moderately difficult recipe to follow as well. Maybe you can even figure it out on your own?

Good luck!






PROOF THAT IT WORKS (please do try this at home!):

> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Sep 14 14:47:00 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 503316480 bytes
Fixed Size 1268340 bytes
Variable Size 427820428 bytes
Database Buffers 67108864 bytes
Redo Buffers 7118848 bytes
SQL> create database;

Database created.

SQL> set termout off
SQL> @?/rdbms/admin/catalog
SQL> @?/rdbms/admin/catproc
SQL> set termout on
SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u02/oradata/mynewdb/MYNEWDB/datafile/o1_mf_system_5bx7gc70_.dbf
/u02/oradata/mynewdb/MYNEWDB/datafile/o1_mf_sys_undo_5bx7hgn9_.dbf
/u02/oradata/mynewdb/MYNEWDB/datafile/o1_mf_sysaux_5bx7hhj9_.dbf

3 rows selected.

SQL> create temporary tablespace temp1 tempfile;

Tablespace created.

SQL> select username from dba_users;

USERNAME
------------------------------
OUTLN
SYS
SYSTEM
TSMSYS
DIP
ORACLE_OCM
DBSNMP

7 rows selected.

SQL> create tablespace users datafile;

Tablespace created.

SQL> exit

On The Road Again

For the second time in a year, to keep food on the table, I have taken a short-term consulting job out of town. While I have traveled for work in the past, I had forgotten what a real pain it could be. While my kids are grown now, I still miss my wife and home, and the normal daily routines. Furthermore, the government and the airlines have conspired to remove any of the joy and excitement involved in the trip itself, with rules for security that have morphed into revenue enhancing moves by the airlines. For example - reducing the amounts and types of items that can be carried on board, and then charging for checked bags.

While I still enjoy the challenges of my job, being in an unfamiliar environment can cause difficulties in concentration. I almost wonder how people who travel all the time for a living (including professional athletes) can do it.

That's all for today. Back to work!