How to Perform 3 Basic Oracle 12c Database Checks
There are several reasons your Oracle 12c database may hits a few road blocks before running smoothly. But don’t worry; there are easy fixes for these road blocks! Some of these reasons are related to hardware and others are related to the database itself. Here are three areas in your database to check to ensure your database runs smoothly:
Verify the database is running.
Verify Oracle Net functionality.
Perform a database connection.
How to run an Oracle 12c database instance
You should check whether the database is actually running because, sometimes, databases crash, fail to startup, or for whatever reason are not running when they should be running. Automated monitoring tools to detect if a database is not running are plentiful, but every DBA should know how to check if their database is indeed running.
Oracle database instances execute with different mandatory processes, such as PMON.
On Windows systems, go to Control Panel→Administrative Tools→Services to see whether the Oracle service has started. You can also look under Windows Task Manager to find similar information.
On Linux/UNIX systems, simply check for the PMON process. Without PMON, there’s no Oracle database instance running.
$ ps -ef|grep pmon oracle 8885 1 0 Jul20 ? 00:04:51 ora_pmon_dev12c
The PMON process is for dev12c, which is a running Oracle database. You could search for additional database processes, but if you know PMON is, you can safely assume the rest of the database instance is running too.
After you confirm a working basic network infrastructure and a connectable database server, you have to confirm the Oracle Net infrastructure is working so users can connect to the database.
Oracle Net functionality
Execute tnsping from the DOS or Linux command prompt. It uses the Oracle Net protocol to see whether it can connect to the database.
$ tnsping dev12c TNS Ping Utility for Linux: Version 22.214.171.124.0 - Production on 02-AUG-2013 17:37:36 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: /u01/app/grid/12.1.0/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS =
PROTOCOL = TCP)(HOST = oralinux1)(PORT = 1521))) (CONNECT
_DATA = (SERVICE_NAME = dev12c))) OK (40 msec)
Note how the output from a tnsping specifies the host, port, and SID information for that database. This information is valuable when troubleshooting errors.
If the output for host, port, or SID doesn’t match what you know to be correct, it is a clue.
If output for the tnsping doesn’t come back at all, it may be a network or server failure.
Depending on the Oracle error returned, a tnsping test may suggest an error with the database listener process.
How to perform a database connection check with Oracle 12c
When you know the database is up and you can establish an Oracle Net communications handshake, log in to see whether you can establish a database session.
Identify the problem tier that users are reporting.
In a client-server application, this tier is the workstation.
In a multi-tier architecture, this tier is likely the web application server.
From the tier where the problem exists, try logging in to the database via SQL*Plus, preferably as a typical user, to mimic the connection which is failing.
Here’s connecting as an application user to a remote database dev12c:
$ sqlplus dwilson@dev12c SQL*Plus: Release 126.96.36.199.0 Production on Fri Aug 2 17:38:32 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. Enter password: Last Successful login time: Sat Jul 20 2013 11:05:24 -04:00 Connected to: Oracle Database 12c Enterprise Edition Release 188.8.131.52.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> show user USER is "DWILSON"
The SQL*Plus attempt shows a successful connection to a remote database as an application user. You want to force the use of the Oracle Net infrastructure in this test. The @dev12c denotes that you’ll use Oracle Net to connect to the remote database rather than directly logging in if you’re already on the same server.
If you logged in, you’re done with your basic database checks; you confirmed a user can connect to the database. On the other hand, you may have encountered any of the following common errors:
You cannot archive the log file. If the archive dump destination is full, or for any other reason the archiver processes can’t properly write the archive log file, your login attempt fails. Oracle does this because even a login generates archive log information and Oracle guarantees it will track that information or it won’t perform the action.
Fix: Resolve that archiver problem.
You can always log in on the server itself with / as sysdba to perform maintenance.
The database is in a restricted session. The database may be running, but if it’s in a restricted session, then only users with RESTRICTED SESSION system privilege can log in. Generally, the database is in the state because some form of database maintenance is occurring and the DBA doesn’t want normal users in the system.
Fix: Determine why the database is in restricted session mode and take it out of that mode if appropriate. Or you can grant RESTRICTED SESSION to the user(s), but that usually defeats the purpose of having restricted session.
The login simply hangs. Sometimes the login attempt hangs and doesn’t immediately generate an error message. These can be tough to diagnose because you’re not getting any feedback.
Fix: Try connecting from a different tier. Also try logging in from the database server itself; see whether you can find where you can connect from and generate an actionable log message. Also revalidate your network, server, and system checks to confirm that they’re valid and then search for error messages.
Performing basic database checks is a way to confirm there’s nothing obviously wrong with the database, such as it isn’t running or you can’t connect to it.