Understanding the terminology of common Oracle errors

Oracle has long been a name synonymous with corporate database systems. Though other competitors have moved into the market, Oracle retains a firm grasp with over 310,000 database customers. However, not every one of those companies has an on-staff database administrator with the necessary knowledge to write one of the old Oracle user guides that were so prevalent in the late ’90s. In fact, many users no longer even bother to purchase the guides as they credit search engines as their primary means of troubleshooting.

Some of you may have experienced this first-hand. Since there is such a wealth of free information available, dropping any amount of cash for a set of Oracle reference guides seems ludicrous. While the positive aspect is the savings, the negative side is that knowledge can often be assumed in a way that glosses over definitions. You may go to a search engine looking up a single error and, twenty minutes later, have a dozen tabs open explaining what exactly child-parent keys are in Oracle.

With that said, let us look at some of the more common Oracle database terminology and examine the practical meanings. There is such a varying level of skill amongst data managers in the field, so some of this may seem quite basic. Still, there are many managers out there who are cutting their teeth in the industry or perhaps want to know more about Oracle systems; this will therefore give some background on some of the terms they can expect to encounter.

Trace file and Alert Log – Before we delve into some of the different terminology related to specific problems and errors, let us first look at the trace file and alert log. These are both quintessential in solving Oracle errors.

Typically, when searching for where to start in resolving a lingering error, the first piece of advice found on the web will urge you to examine the alert log for more information on what has been occurring during the usage of your system. The alert log is a file that provides a chronological logging of all database messages and errors.

The trace file is a little different – it is an administrative file that contains specific diagnostic data used when working with problems and errors in the system. You can utilize various SELECT queries to initiate a search of trace files.

Both the alert log and trace files are stored in the ADR (Automatic Diagnostic Repository). The ADR is a file-based, hierarchical data storage unit used for managing information in Oracle such as the aforementioned network tracing and error logging. Starting with Oracle 11g, a command interpreter (ADRCI) was introduced that compiles the user view and monitor data as well as package incident information into a zip file to send off to Oracle support; it is a very helpful little tool.

Parallel query – Now that we know the basic terms associated with looking up error diagnostic and briefly mentioned queries, let us now turn our attention to a parallel query. One of the first concepts you learn in Oracle is how to initiate a query, which is an operation that retrieves data from views or tables.

So, what is a parallel query? First appearing in later versions of Oracle 7, a parallel query option allows multiple processes to request and receive data as well as perform operations simultaneously. This leads to an accelerated speed for the database system.

However, to achieve this kind of speed, query ‘slaves’ dedicated to specific operations and data pieces have to be used properly. Generally speaking, a good rule to follow is to double the amount of disks in which the operation is used and set that as your slave number. Typically, if this has not been done successfully, an ORA-12801 will be triggered (a catch-all for parallel query problems).

TNS/Listener – For anyone that has dealt with Oracle as part of a company network, it is inevitable that the term ‘listener’ has come up before. In many ways, it is exactly as it sounds.

The TNS (Transparent Network Substrate) is a proprietary Oracle networking technology that allows for peer-to-peer connection and is primarily used to connect Oracle databases. Within Oracle, a listener is a process that runs on the database server. It manages the traffic coming from incoming client connect requests and sends it to the database server.

As you could imagine, this is a very essential function. Every single time that a client requests a network session, the listener will receive the request, check that the client information matches the listener information and subsequently grant or deny the request. Of course, it is never as simple as that and you will run into plenty of errors, such as the ORA-12541, which can leave you pulling out your hair. But, for now, you will at least know what the error is referring to.

Foreign Key – Very briefly, let’s go over the previously mentioned parent-child feature. The foreign key in Oracle enforces referential integrity within your database. The table being referenced as the ‘parent table’ has its own primary key, which matches the foreign key in the ‘child table’. These two keys act in a way that references one another in a relational database. The purpose of this is to add integrity to the database and implement business rules across databases, which can be referenced at any given time. It really is as simple as that.

Mastering these terms will provide better insight into some more concepts that range in complexity. If you understand parameters like varchar2, you can complete essential work in Oracle. When you start grasping how the parent-child keys and TNS errors work, your ability to combat an entirely new range of errors will develop. The frequency with which these terms arise in Oracle all depends upon the way you use the database software. Hopefully, you now have a better understanding of where to start with your next ORA-12801 error and you can therefore minimize the amount of open browser tabs dedicated to searching for Oracle definitions.

Image: Peter Kaminski via Flickr.