Blogs › inforadar's blog

Oracle: Common Problems and Oracle Connectivity issues

Submitted by inforadar on Thu, 06/03/2010 - 22:33
Tags:

Trouble in Installing/Re-Installing Oracle Client Software?

1. What to do if you face any of these problems:

  • My Oracle client installation hangs at xx% and nothing happens.
  • Oracle client installation shows successful but I get an error while trying to run SQL*Plus that some DLLs are missing.

2. I am not able to successfully reinstall the oracle client software.

Possible Solution:

  1. Identify the oracle client versions installed in your computer. The installation paths are usually in c:\oracle or D:\Oracle with sub-directory name as Oracle client version.
  2. Go to Start –>Settings –>Control Panel–>Administrative Tools –> Services and look for the service names starting with Oracle and stop these services (Right Click on service name and you get a menu item to stop the service).
  3. Go Start –>Program Files–>{Identify the Program Name starting with Oracle}–>Oracle Installation Products –> Universal Installer–> Select ‘Deinstall Products’ and select the Oracle Home to start deinstall process.
  4. Delete all the sub-directories under c:\oracle or d:\oracle whichever is appropriate in your case.
  5. Delete the directory called “Inventory” under c:\Program Files\Oracle\.
  6. Start a fresh Oracle Client Installation. Set up TNS_ADMIN environment variable and test the installation.
  7. After Installation is done, please test the connectivity as mentioned in the “How to test Connectivity to Oracle Database?” section.

How to Test Connectivity to the Oracle Database?

  1. On your windows computer, click on Start –> Run, type cmd and click OK to start the command Prompt.
  2. On the command Prompt, enter tnsping <DB Name>
  • If you get an error “TNS COULD NOT RESOLVE SERVICE NAME” or similar error message, then verify that you have tnsnames.ora and sqlnet.ora in <ORACLE_HOME>/NETWORK/ADMIN
  • If you get error while running sqlplus or tnsping command from command prompt as “Command Not recognized” or similar, please refer to “Steps to modify the PATH variable”

Example:

C:\Documents and Settings\milind>sqlplus

’sqlplus’ is not recognized as an internal or external command, operable program or batch file.

Troubleshooting Oracle ODBC Connectivity

If you have problem with the Oracle ODBC connectivity, you need to ensure that you have the following things verified:

  1. Follow the instructions under “How to Test Connectivity to the Oracle Database” to test the simple sqlplus connectivity to the database.
  2. When you created the System or User DSN, select the Oracle ODBC driver “Oracle in xxxxxxxx” instead of “Microsoft ODBC for Oracle”.
  3. On the next screen, enter your username and password and the TNS Service Name in the Database name. ex. DSSC or DSST , hit “Test” button to test the connectivity. If you receive any error, note the error message as it would help the Oracle DBA to resolve the issue faster.

Steps to Modify Path Variable (For Advanced Users only)

Some times if the Oracle Client directory is not included in your PATH variable, you may get errors such as “Sqlplus/tnsping”  is not recognized as an internal or external command. In this Case, we can make sure that the Path to Oracle client binaries in included in the path variable. The typical bin directory path can be : c or d :\orcle\<oracle client version>\bin

Example: c:\oracle\920\bin or C:\oracle\product\10.2.0\BIN

To view/modify the PATH variable follow these instructions: (Assumption: We have the 9i client and path is c:\oracle\920\bin)

  • Right-Click on My Computer and click on properties.
  • Go to “Advanced”  tab and click on “Environment Variables” which would open a new window. Then scroll down to “PATH” and then click on edit.
  • Make sure that the Oracle Client bin directory is included in the PATH. If not you can include it as shown. The entries in PATH variable as separated by “;”

Related Oracle Error Messages

ORA-12154:TNS:could not resolve the connect identifier specified

Cause: A connection to a database or other service was requested using a connect identifier, and the connect identifier specified could not be resolved into a connect descriptor using one of the naming methods configured. For example, if the type of connect identifier used was a net service name then the net service name could not be found in a naming method repository, or the repository could not be located or reached.

Action: You might be using a local tnsnames.ora which is not updated automatically with any configuration changes. You can replace this file with the latest tnsnames.ora file from shared drive (Z:\CARES\Databases\tnsnames) or point your TNS_ADMIN variable to the shared drive and try connecting to the database again. If the problem persists, contact Oracle DBA.

ORA-12541:TNS:no listener

ORA-12224: TNS: no listener

Cause: The connection request could not be completed because the listener is not running.

Action: Check with your DBAs if they are working on the server. If its is not down due to some maintenance activity, page/call DBA to investigate further on this issue.

ORA-12545: Connect failed because target host or object does not exist

ORA-12203: TNS: unable to connect to destination

ORA-12154: TNS:could not resolve service name

ORA-12505: TNS: listener could not resolve SID given in connection description.

Cause: Oracle suggest that this error indicates that the address specified (in the alias definition) is not valid.

Action: You might be using a local tnsnames.ora which is not updated automatically with any configuration changes. You can replace this file with the latest tnsnames.ora file from shared drive (Z:\CARES\Databases\tnsnames) or point your TNS_ADMIN variable to the shared drive and try connecting to the database again. If the problem persists, contact Oracle Primary.

ORA-04031: unable to allocate … shared memory

Cause: More shared memory is needed than was allocated. SGA private memory has been exhausted. Fragmentation of shared pool memory is a common problem and ORA-04031 is commonly a result of such fragmentation. Application programmers usually get this error while attempting to load a big package or while executing a very large procedure and there is not sufficient contiguous free memory available in the shared pool. This may be due to fragmentation of the shared pool memory or insufficient memory in the shared pool.

Action: Notify the Oracle DBA as soon as possible. Work with application Development team to re-estimate the SGA parameters.

ORA-01034: ORACLE not available

Possible Causes:

Oracle indicates the following possible causes:

The SGA requires more space than was allocated for it.

The operating system variable pointing to the instance is improperly defined.

Action:

You might be using a local tnsnames.ora which is not updated automatically with any configuration changes. You can replace this file with the latest tnsnames.ora file from shared drive (Z:\CARES\Databases\tnsnames) or point your TNS_ADMIN variable to the shared drive and try connecting to the database again. If the problem persists, contact Oracle DBA.

ORA-01017: Invalid username/password

Cause: You are entering incorrect username and password for that database.

Action: Contact Oracle DBA and ask them to reset your password or unlock your account.