Is the database up and running?

Dear Readers,

My name is Franz Devantier, creator of this blog.  I am an Oracle Certified Professional (OCP DBA 11g) Security DBA.  I will be sharing with you the basic duties of an Oracle DBA, and also some of the undocumented, and not so well known tasks. 

Is the database up and running?

Have you noticed that whenever there is an issue, one of the first questions is to ask if the database is up and running?

One trick that some DBA's use, is to logon to the database and execute this:

SQL> select * from dual;

D

-

X

If they get an 'X' back, they are prepared to stick there neck out and say that the database is up and running, and that there is nothing wrong with the database.  This is obviously not the way to go, you will need to investigate further, before taking a position, if you want to remain a DBA for a long period of time.

Firstly if you get a string of characters back from dual, it probably indicates that the database is mounted and not open.  Again our cowboy DBA's will rush in and declare that there is corruption.  This is based on the assumption, that if the database was opening and was stuck on the mounted stage, then it obviously could not open, because the instance could not connect to certain datafiles, and therefore there must be corruption, and we need media recovery.  Again a very dangerous assumption, please don't fall into this simplistic trap.

So although selecting * from dual, may in fact provide a quick reference as to whether the database is up or not, don't rely too much on this.

What has made it easier to rely on this mechanism is the fact that if you can connect to the database from a remote logon, then the network is in place, and the OS and the hardware are up and running.

So we have to agree, in the end, that a quick "select * from dual;", does have some advantage, if you suspect that the database may not be up and running.  Keep in mind that if you have logged in as sysdba, that this will not reveal the fact that the archive log directory has run out of space, and that the database is currently hanging.  Which means that nobody else can currently log in.

So if we are going to use "select * from dual;", then we will need to follow this up with some other checks, to make it a more useful and reliable check.  One of the first things to check after this, would be to make sure that there is available space on the operating system level.  This will confirm that the archive logs can still write to disk, and the tablespaces can grow if you have them in auto-extend mode.

Then you will go and have a look at the alert log.  in UNIX you can do something like:

> tail -400 alert<sid>.log|grep ORA-

to see if there are any recent errors in the alert log.

In windows you can open the alert log up in notepad or similar.  Scroll to the bottom of the find.  Do a search going backwards, looking for "ORA-".  If you find such an error, then look at the date-time stamp, to decide if the error was recent or not.  You see we are looking for recent errors here.  So a few days back is still recent.  A few weeks or months back is not recent, and so for the purposes of this exercise, where we are determining if the database is up and running, we will ignore them for now.

Now lets go back to the SQL prompt, and do a few more checks.  Lets make sure that we are looking at the right database.

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE

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

<DBName>  READ WRITE

Is this database on the correct host?

SQL> select instance_name, host_name, version, startup_time, status from v$instance;

INSTANCE_NAME    HOST_NAME    VERSION      STATUS

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

<Instance_name>         <host_name>        10.2.0.4.0        OPEN

So we have answered the question now with some level of certainty.  The database is up and running, and you can connect to it.  There are no recent errors in the alert logs, and at first look there seems to be enough OS space.  However we have not determined if we are about to run out of tablespace, and other potential issues, but that was not part of the purpose of this exercise.

Franz Devantier,

Need a database health check?

devantierf@gmail.com

Income stabilizer (win-win opportunity)

Join the carefully selected and tested cash-flow generating program below to potentially create a long-term residual or annuity type income enhancer for yourself.

Traffic Wave - Free Report:  The report will give you all the information you need to start making a nice long-term residual income stream for yourself.