Sudden ORA-12578: TNS:wallet open failed when logging in as SYS

ORA-12578: TNS:wallet open failed when logging in as SYS

This blog post covers a possible cause of a "ORA-12578: TNS:wallet open failed" error when trying to log into your database using 

>sqlplus / as sysdba

I have seen this issue a few times with DB 19.x.  I noticed the behavior changed with AI DB 26 and is much less likely to happen.

What causes this ?

The most likely reason why you would suddenly see this error code when trying to log in using "sys as sysdba" is a change to the sqlnet.ora file.

When logging in using "sys as sysdba", the sqlnet.ora file used by your environment will be parsed as part of the authentication process.  If the sqlnet.ora in your environment has any issues during the parsing, this will cause your login using "sys as sysdba" to fail with the above error.

Fortunately, this does not happen in AI DB 26.

How to test for the sqlnet.ora being the cause

The easiest way to test is by using the TNS_ADMIN environmental variable setting.

The steps I would follow are.

1. cd to your $ORACLE_HOME/network/admin directory on the server
2. Execute mkdir to create a new directory named "test"
3. cd to that new directory "test"
4. set TNS_ADMIN with "export TNS_ADMIN=$ORACLE_HOME/network/admin/test"
5. Try logging in using "/ as sysdba"

Since there is no sqlnet.ora file in this new directory, if you can successfully login we have proven that the issue is the sqlnet.ora file.

Now that we have proven it is the sqlnet.ora (or ruled it out sorry I couldn't help), we can look at the causes.

Finding the issue

Now that you have a new directory, $ORACLE_HOME/network//admin/test, we can start working through the possible causes.

Step 1- copy the sqlnet.ora from the default location to this new directory so that we can update it and find the issue without affecting other users. "cp ../*.ora ."

Below is my sqlnet.ora that I am showing different issues with.

# sqlnet3189722425551944721.ora Network Configuration File: /tmp/sqlnet3189722425551944721.ora
# Generated by Oracle configuration tools.

SQLNET.WALLET_OVERRIDE = true

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

WALLET_LOCATION =
 (SOURCE =
 (METHOD = FILE)
 (METHOD_DATA =
 (DIRECTORY = /opt/oracle/admin/ORCLCDB/wallet1/server_seps)
 )
)

Cause 1 - Wallet file

The first possible cause is that the location of the wallet location is not correct. The same issue will most likely occur if you are setting the encryption_wallet_location in the sqlnet.ora file.

Both of these must be true when looking at the wallet file.

1. The directory in the sqlnet.ora file MUST exist. If the directory location is incorrect, you will have an issue opening the wallet
2. There must be a wallet file in that directory. Not only must the directory exist, but there must also be a wallet file within the directory to read.

Cause 2 - Syntax in sqlnet.ora file

When the database parses the sqlnet.ora file, it can be sensitive to any issues within the sqlnet.ora file.  Simple issues can cause parsing failures, and cause your login to fail.

Some of the most common issues are:

1. Hidden characters in the file. This can happen when copying across platforms (windows to Linux for example). If there are any characters in the file that cause parsing to fail, your login will fail.
2. Missing "(" or ")". This can cause parsing to fail, and your login will also fail.
3. Starting "(" in the first column.  Unfortunately this causes a parsing failure. This can be the most annoying, and difficult to find cause. 

Below is an example of a sqlnet.ora file that fails, and you can compare to my sqlnet.ora at the beginning of this blog.

# sqlnet3189722425551944721.ora Network Configuration File: /tmp/sqlnet3189722425551944721.ora
# Generated by Oracle configuration tools.

SQLNET.WALLET_OVERRIDE = true

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

WALLET_LOCATION =
 (SOURCE =
 (METHOD = FILE)
 (METHOD_DATA =
(DIRECTORY = /opt/oracle/admin/ORCLCDB/wallet/server_seps)
 )
)

Can you spot the difference ? 

...

...

It's the "(" before the word "DIRECTORY". When it is in the first column, parsing fails.

Prevention-

What I tell customers to avoid any issues like this is the following:

• NEVER change the default sqlnet.ora for all databases. This is the copy that is stored in the $ORACLE_HOME/network/admin directory.
• ALWAYS set the WALLET_ROOT parameter in the database. This is interpreted first by the database, and replaces the encryptioin_wallet_location in the sqlnet.ora file.
• ALWAYS put the SEPS wallet for Real-time redo with the ZDLRA in the {WALLET_ROOT}/server_seps directory. Even if it is a symbolic link.
• ALWAYS use TNS_ADMIN when it is necessary to customize the sqlnet.ora.  When backing up using the ZDLRA I recommend customers create a customized sqlnet.ora file and use TNS_ADMIN when executing backup/restore scripts.

This will prevent any issues with the shared sqlnet.ora that may cause unexpected issues with logging in as sys.

SUMMARY:

If you suddenly receive a ORA-12578: TNS:wallet open failed when logging in as SYS the first place to look would be your sqlnet.ora file for any parsing errors.