Profiling oerr commands in Oracle

Profiling oerr commands in Oracle

The commands in Oralce are very rich. The oerr command is a good auxiliary tool. Many error codes that do not seem to have an eyebrow purpose can help DBAs quickly identify the cause of the problem, we don't need to remember the ORA errors at all, unless they are completely inseparable from your work.

Most of the commands are in binary format. For example, sqlplus is a shell script, and the implementation principle is not hard. Let's analyze it, let's look at the design idea of this tool.

First, this tool is located under $ Oracle_HOME/bin. It cannot be seen as a shell script.

[Oracle @ db117 ~] $ Ll $ ORACLE_HOME/bin/oerr
-Rwxr-xr-x 1 oracle oinstall 2567 Apr 23 2014/U01/app/oracle/product/11.2.0.4/bin/oerr
However, I just like to do so, so I accidentally opened this file. Note the script as follows:
# Usage: oerr facility error
#
# This shell script is used to get the description and the cause and action
# Of an error from a message text file when a list of error numbers are passed
# To it. It supports different language environments and errors from different
# Facilities.
Of course, there are not many lines of complete script content. Let's take a simple look, there is a configuration file facility. lis
Facilities_File = $ ORACLE_HOME/lib/facility. lis
The corresponding component needs to be obtained from this configuration file. For example, if we use the command oerr ora 12345, ora is the first parameter. Based on this parameter, we can obtain the component value that needs to be used next.
The script contains the following line of similar content. We can import the parameter ora, of course, you can also enter tns and so on.
Fac_Info = 'grep-I "^ $ {Facility}:" $ Facilities_File 2>/dev/null'
After conversion, we can see that the component is rdbms.
[Oracle @ db117 ~] $ Grep-I "^ ora:" $ ORACLE_HOME/lib/facility. lis
Ora: rdbms :*:
The explanation of this line can be easily found in the file.
#
# The entries in this file are colon separated defining, for each
# Facility (field 1), the component name (field 2), the "real" facility
# Name for facilities with aliases (field 3) with a value of "*"
# Facilities without renamings and an optional facility description
# (Field 4)
#
# Facility: component: rename: description
#

The content is as follows:
Acfs: usm :*:
Acpsk: usm :*:
Advm: usm: acfs:
Advmk: usm :*:
Amd: cwmlite :*:
Amdu: rdbms :*:
Of course, this is only the beginning. The error message file is in a specified file. For example, if component is rdbms, the error message file is $ ORACLE_HOME/rdbms/mesg.
[Oracle @ db117 ~] $ Cd $ ORACLE_HOME/rdbms/mesg
The file with the error message is also regular, and a file related to ora, oraus. msg is also in the general format.
[Oracle @ db117 mesg] $ ll ora * us * msg
-Rw-r -- 1 oracle oinstall 4976647 Apr 23 2014 oraus. msg

For example, we moved to the error message file and found the following two lines of error information.
64477,000 00, "Multiple token tables are not supported ."
// * Cause: An attempt was made to create a new token table. If encountered
// During an import, a critical patch was possibly missing on
// Export database.
// * Action: Use the default token table. If encountered during an import,
// Apply the appropriate patch on the export database and try
// Export and import again.

64621,000 00, "SQL statement length (% s) exceeds maximum allowed length (% s )"
// * Cause: An attempt was made to issue a SQL statement that exceeded
// Maximum allowed length of a statement.
// * Action: Modify the SQL statement or the views to which it refers to fit
// Within the maximum length or split the SQL statement.
//
How can I parse and read this file at this time,
The first step is to parse the error code. For example, if the input error code is 64621, we can also enter 064621. In this case, we will use sed to convert it first to ensure that the exact matching content can be found.
Code = 'echo 64621 |/bin/sed's/^ [0] * //''
[Oracle @ db117 bin] $ echo 64621 |/bin/sed's/^ [0] * //'
64621
[Oracle @ db117 bin] $ echo 064621 |/bin/sed's/^ [0] * //'
64621
Let's assume that we can read the error 64621 at this time. At this time, we can use awk to read the file content. The calling method of Oracle is similar to the following.
Code = 64621
[Oracle @ db117 bin] $ awk "BEGIN {found = 0 ;}
/^ [0] * $ Code/{found = 1; print; next ;}
/^ \/\ // {If (found) {print;} next ;}
{If (found) {exit ;}} "$ ORACLE_HOME/rdbms/mesg/oraus. msg
64621,000 00, "SQL statement length (% s) exceeds maximum allowed length (% s )"
// * Cause: An attempt was made to issue a SQL statement that exceeded
// Maximum allowed length of a statement.
// * Action: Modify the SQL statement or the views to which it refers to fit
// Within the maximum length or split the SQL statement.
//
Therefore, we can follow this method to customize the content that meets our needs.