After seeing how to install Oracle and install a Loopback Adapter in order to use Oracle on a laptop or a workstation without a network connection, I will present in this article some tips and tricks very useful when you need to manage the complex database that can be sometimes (or most times) Oracle..

For example, there is nothing more frustrating than having a problem during the installation of Oracle, and not be able to uninstall it. It’s very hard to get rid of it: it is even more difficult to remove it than to install it.

We’ll see also some basic tools and commands to verify that Oracle is working properly.

Check that Oracle is correctly installed

Once Oracle has completed its installation, you can check that you now have a menu to access various configuration tools for Oracle software administration and for the database.

Remember: our database is called JPORA11 and it is located with the Oracle software in a ‘ORACLE_HOME’, which represents the installation directory.

We can also verify that the directory that contains the executable binaries of Oracle is in our path, using the command of the same name in a MS-DOS window.

If we open the Services window, we can also see that some Oracle services are ‘Started’:

• For the administration console.
• For the listener which, as its name suggests, listens if a request is made to the database.
• For the database itself (JPORA11).

If you have a problem with Oracle, you may be asked to check if the database or the listener are ‘Started’.

Check that Oracle is working properly

You use a software that works with your Oracle database, and you encounter an error. What could be its origin? What is the problem? The software itself or Oracle?

The first thing to do is to verify that Oracle is working properly.

The tnsping command allows you to ‘ping’ the database by sending a message. As you can see in the example above, tnsping JPORA11 returns:

• The location of the ‘sqlnet.ora’ file used to trace the database.
• The parameters of the ‘tnsnames.ora’ file with the name of the database, the server ‘host = JPFPC’ (my computer) and the port 1521 to access it.

Another tool that can check a connection: SQLPLUS. This is an SQL utility included with Oracle for querying the database. It is very basic, but still allows to perform some consultation when no other tool is available, and in this case, make sure that Oracle is working properly.

In a DOS window, the command sqlplus /nolog launch sqlplus without any login.

The cursor ‘SQL>’ indicates that we are into sqlplus.

The command connect allows us to connect with a user login and password: the user ‘system’ in this example. Remember that we created this user with the Oracle installation.

It is possible to launch sqplus by connecting directly with the command ‘sqlplus user/password’, for example sqlplus system/system. But in case of error, we can not say if it comes from Oracle or simply from a login error. So again, if you are seeking help for a problem with your database, just indicate that a ‘sqlplus /nolog‘ works (or not) in order to limit the possible causes of error and facilitates the search for a solution.

ORA-12560 – TNS: protocol adapter error

You could encounter an ‘ORA-12560’ error and believe you have a protocol error. This is a typical example of an Oracle error message that (often) has nothing to do with the real cause of the problem. Indeed, the exact syntax of the connection string to Oracle is ‘user/password@dbname’. Remember it because we will need it when we will install SONAR.

If you work from your computer to a database on a remote server, or if you have more than one database installed, then sqlplus cannot know which database to use or where it is located. So it will consider it can not connect and therefore indicates a protocol error.

In the following example, the sqlplus /nolog command works correctly. But when we try to connect with only the user/password system/system, we get an ‘ORA-12560’ error. We have to indicate the database name in the connection string: system/system@JPORA11 in order to connect without any error message.

Obviously, if the error persists, is that the problem lies elsewhere. Make sure that your Oracle services are started and that a tnsping works.

Uninstall Oracle

Finally, how to uninstall Oracle. This is not a common operation, but against, quite painful when you have to make it.

The first thing to do is try to uninstall Oracle itself. To do this, we will use the Oracle Installer, which can be used to add or remove some modules, install a patch, upgrade version, and also uninstall Oracle.

If you had a problem with during the installation and your Oracle does not work properly, then it is likely that the uninstall cannot be compled. In this case, the actions to take are:

• Delete any reference to Oracle in your registry. You have to launch the program ‘regedit.exe‘ and search all Oracle configuration keys in ‘HKEY_LOCAL_MACHINE/SOFTWARE/Oracle’. Notice that on my Win7 64 bits, thse ‘keys’ are located in ‘HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node/Oracle’.
• Search and delete also any reference to Oracle service in ‘HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/’. You should find a lot of folders with a name like ‘Oraclexxx‘, as for instance ‘OracleServiceJPORA11’, ‘OracleDBConsoleJPORA11’, etc.
• Reboot.
• Delete the Oracle installation directory (‘C:\Oracle’ for instance, or ‘C:\ORA11’ in my case).
• Delete the ‘C:\Program Files (x86)\Oracle’ or ‘C:\Program Files\Oracle’, depending if your Windows is 64 bits or 32 bits.
• Delete the ‘C:\temp’ directory.
• And finally, delete the content of your Recycle Bin.

Voilá, There remains no trace of Oracle on your machine.

We will still have to see how to create an Oracle user for SONAR in order to proceed with the SONAR installation. This will be the subject of a future post.

See you soon.

This post is also available in Leer este articulo en castellano and Lire cet article en français.