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.