Oracle RAC Install Troubleshooting

Over the past year and a half, and especially in the past three months, I have been in complete RAC installation and upgrade mode.  Inevitably, when such projects come along, problems requiring detailed and exhaustively tedious troubleshooting will come up.  It's been my experience that the largest set of books, or even the internet, can contain the exact sequence of problems you may encounter.  It's not that they aren't there, just not in the condition and set of circumstances that meet your situation.

Just recently, a colleague asked for assistance doing the simplest step in the upgrade process from RAC 11.2.0.2 to 11.2.0.3, the software installation.  The following message was received before the guts of the installation could begin:

"[INS-40418] The installer has detected that Oracle Clusterware is not running on local node.  oracle recommends that you upgrade Oracle Clusterware in a rolling manner."

Now, needless to say, the above was untrue.  We verified, using:

crsctl stat res -t -init

And also:

ps -ef | grep ora (which shows oracle owned processes at the linux level )
ps -ef | grep grid (which shows grid owned processes in linux)

So the clusterware and associated processes WERE running on the local node at the time the installation was attempted.  We understand this is necessary so the upgrade process can collect information for the configuration of the new version.  We had just done several installs, had a 100+ page detailed procedure which we developed to assist and assure things were done consistently, and yet this condition had never occurred!

Luckily, this is a testing platform where a delay at least does not impact customers or revenue.  Still, at times when even experienced professionals are exasperated and clueless, Oracle support must be contacted.  This assures that the latest information on the software is available, and verifies we're using appropriate methods of installation and configuration.  In order to get the issue resolved expediently, we entered an SR (Service Request) on the Oracle Support website (support.oracle.com) at level 2, business impact, but workarounds are available.  The support analyst was prompt, but asked for an awful lot of information about the environment in a short period of time.  Luckily, Oracle now has diagnostic tools such as rdalite and diagcollection.pl to collect the data quickly.  An additional piece of luck  -   this was only a two-node RAC. We have installations at this site with up to 26 nodes!  But still, deadlines are deadlines, and critical test by the QA team was dependent upon this environment.

After two days of inquisition by an Oracle analyst, including a live webcast and deep-dive into the entire RAC infrastructure, the problem was still unsolved, until, my colleague Jayanth was skimming through the original installation and configuration to collect more data for Oracle, and stumbled across the actual problem.  The host file will not be published to protect the client's configuration security.

You had to really look for it, and neither the DBA in charge, this writer, the Oracle analyst or the various diagnostic scripts that Oracle recommends caught it, but there are TWO ENTRIES for ip xxx.xx.xx.xx in the hosts file.  Even though the Oracle message from their installation program says nothing about it, that is the TRUE REASON why the installation could not precede.

All if this makes me wonder if diagnostic messages are ever of any use at all!  They are often wrong, or simple misdirection, causing you to take even longer to do a relatively simple task.   The good news is, after this issue was finally discovered, the upgrade and patching was done in only a few hours, which is a very short period of time in RACworld!

I've gone on a lot longer about this particular issue than I meant.   I hope it someday helps someone else.   My next post will involve more detail on how to engage support, and what to do while you're working with them, and what your backup plan should be.  Good day!

Dropping an Uncooperative Oracle Tablespace

We recently had a client database that was under some space pressure.  After several steps to reorganize data and segregate applications by tablespace for ease of administration and management, a couple of "apperently" empty tablespaces remained.  The next step in the process was to drop these tablespaces and datafiles to recover space.  This is usually a relatively trivial matter, and can often be done expediently.  

However, every now and then, unusual problems can arise.  In this one case, a problematic tablespace, with no segments listed in the DBA_SEGMENTS view or the "Tablespaces" tab of Oracle Enterprise Manager, failed to drop, as follows:

SQL> drop tablespace team_index01 including contents and datafiles;
drop tablespace team_index01 including contents and datafiles
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key


SQL> select owner, segment_name
  2  from dba_extents
  3  where tablespace_name = 'TEAM_INDEX01';

no rows selected

SQL> !oerr ora 2429
02429, 00000, "cannot drop index used for enforcement of unique/primary key"
// *Cause: user attempted to drop an index that is being used as the
//         enforcement mechanism for unique or primary key.
// *Action: drop the constraint instead of the index.

The above led me to conclude that there is still some logical, though likely not physical, connection to this tablespace and a current database object, so:

SQL> select constraint_name, index_owner, index_name
  2  from dba_constraints
  3  where index_name in
  4  (select index_name from dba_indexes
  5    where tablespace_name = 'TEAM_INDEX01');

CONSTRAINT_NAME                INDEX_OWNER
------------------------------ ------------------------------
INDEX_NAME
------------------------------
PK_NOTE_ED_PAYLOAD             MRA
PK_NOTE_ED_PAYLOAD

The following verifies the object above is designated to be on the TEAM_INDEX01 tablespace:

SQL> set long 4000

SQL> select dbms_metadata.get_ddl('INDEX','PK_NOTE_ED_PAYLOAD','MRA') from dual;

DBMS_METADATA.GET_DDL('INDEX','PK_NOTE_ED_PAYLOAD','MRA')

--------------------------------------------------------------------------------

  CREATE UNIQUE INDEX "MRA"."PK_NOTE_ED_PAYLOAD" ON "MRA"."NOTE_ED_PAYLOAD" ("NO

TE_ID")

  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

  TABLESPACE "TEAM_INDEX01"

The following command corrects this (luckily it's a rather small index):

SQL> alter index  "MRA"."PK_NOTE_ED_PAYLOAD" rebuild tablespace mra_index01 online;

Index altered.

Then the reverification confirms:

SQL> select dbms_metadata.get_ddl('INDEX','PK_NOTE_ED_PAYLOAD','MRA') from dual;

DBMS_METADATA.GET_DDL('INDEX','PK_NOTE_ED_PAYLOAD','MRA')

--------------------------------------------------------------------------------

  CREATE UNIQUE INDEX "MRA"."PK_NOTE_ED_PAYLOAD" ON "MRA"."NOTE_ED_PAYLOAD" ("NO

TE_ID")

  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

  TABLESPACE "MRA_INDEX01"

So now the DROP command works:

SQL> drop tablespace team_index01 including contents and datafiles;

Tablespace dropped.

I am a bit mystified as to how this might have happened.  I am inclined to believe this object was recreated in this tablespace after having been moved earlier.  I'd be interested in hearing any logical explanations from a reader so inclined to respond.

Oracle Wallet Security Feature Review

Once again I have run into what is a fairly new technology involving the Oracle database.  I must admit I missed this one, because it's been around since at least version 10.2, as part of advanced security features.  The basic concept is similar to https security for websites - you want to make sure the client partner attempting to connect or access data is authentic by validating the connection with passwords and a "handshake".  You can find the complete technical description of the concepts from these sources:

http://docs.oracle.com/cd/B19306_01/network.102/b14268/asowalet.htm#i1010563

How To Create Database Wallet Used For SSL Authentication (Doc ID 1573911.1)

Application DBAs and developers who've had extensive experience with Oracle's 

business applications and fusion middleware would be more likely to encounter this technology. A core DBA, even working on these systems, might only install a wallet based upon a set of instructions without having much debugging or troubleshooting experience, since it really is an application-specific feature, and won't cause operational problems for the database itself if misconfigured.

Additionally, a second type of security authentication involves encryption within the database, and using a wallet to decrypt.  You can get more information on this topic from these Oracle support resources (or do an internet search for them if you don't have an account):

Note 317311.1 - 10g R2 New Feature TDE : Transparent Data Encryption
Note 1251597.1 – Quick and Dirty TDE Setup and FAQ
Note 1228046.1 - Master Note For Transparent Data Encryption (TDE)
Note 432776.1 - 11g New Feature: Transparent Data Encryption at Tablespace Level

One of the biggest issues surrounding the wallet is the fact that it has a password, usually different from database passwords, as this is recommended.  In one major client installation, this password was not preserved, resulting in the potential loss of data should the wallet files be lost or changed at the OS level, leaving the authentication and validation processes unable to be executed.  Additionally, if the wallet is optionally paired with a third-party certificate, the certificate file can also be lost, damaged, and can expire.  In such cases, The only solution is to create a new wallet and link it to a new certificate, with the exact same credentials, before expiration.  

There is an executable to manage the wallet interactively, called "owm", which is included with the Oracle database software for 11gr2.  This tool gives you the ability to do any and all wallet processing.  An older utility, at command line level, is orapki.  This can be a life-saver on machines where x-windows processing is not an option.  Here is an example of using orapki to display the contents and expiration information about a wallet:

orapki wallet export -wallet /u01/app/grid/acfsmounts/racdb_data_acfsvol1/ssl_wallet -dn 'CN=server-name,OU=Company Name,O=Organization,L=City,ST=State,C=Country' -cert /tmp/mycert


orapki cert display -cert /tmp/mycert -summary

Above will present wallet internal names, certificate names, date of implementation, and expiration date.  A very common catch-all error can occur when attempting to access the database through a connection secured by a wallet:

ORA-28860:  Fatal SSL Error

This error hints at an inconsistency in wallet configuration.  
Carefully check that the wallet files themselves exist on both 
source and target, that they are in the appropriate directory, 
and that they are current.  There are pointers to the wallet files 
in tnsnames.ora, listener.ora and sqlnet.ora for the database, 
and these must also be consistent.  
Any deviations, which are especially possible in a multiple client 
and/or multiple node RAC configuration, can result in sporadic 
connectivity issues that are difficult to troubleshoot.

I hope you enjoyed this brief overview.  I would prefer to hear comments 

from those who know quite a bit more, or would like to know more.  

I'll try to find the answers or make corrections to any inconsistencies 

seen here.  We had a two-month project to replace wallets with expiring 

certificates without passwords, and we barely completed it on time!

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.