Today, I found something interesting. All DBA's would have been more familiar with OERR command, If you have any Oracle error it gives unique error code and if you pass the error code, we will get the cause and action, like below.
$ oerr ora 12521
12521, 00000, "TNS:listener does not currently know of instance requested in connect descriptor"
// *Cause: The listener received a request to establish a connection to a
// database or other service. The connect descriptor received by the listener
// specified in addition to the service name an instance name for an instance
// (usually a database instance) that either has not yet dynamically registered
// with the listener or has not been statically configured for the listener.
// This may be a temporary condition such as after the listener has started,
// but before the database instance has registered with the listener.
// *Action:
// - Wait a moment and try to connect a second time.
// - Check which instances are currently known by the listener by executing:
// lsnrctl services <listener name>
// - Check that the INSTANCE_NAME parameter in the connect descriptor specifies
// an instance name known by the listener.
// - Check for an event in the listener.log file.
But today, I was eager to find out where it is trying to get all these message. OERR is an awk command, which fetches the error from oracle file and it is available only for UNIX for windows, there are many perl programmes.
I started with strace command to find out what are the libraries it is invoking.
strace -f -o abc01.log oerr ora 12521
Now you might see abc01.log with the output of Oracle libraries files it is trying to use
First line of the file show that we are using oerr with the parameters what we are passing.
16189 execve("/opt/app/oracle/product/11203/bin/oerr", ["oerr", "ora", "12521"], [/* 61 vars */]) = 0
Somewhere in the log you can see the awk command is used...
16197 execve("/bin/awk", ["awk", "-F:", "{\n\t\tif (index ($3, \"*\") == 0)\n\t\t"], [/* 59 vars */]) = 0
Now say Hello to this line oraus.msg file, try opening this file BINGO! you will see all the errors used by Oracle.
25344 stat("/opt/app/oracle/product/11203/rdbms/mesg/oraus.msg", {st_mode=S_IFREG|0644, st_size=4923359, ...}) = 0
All Oracle Errors from oraus.msg file... ;)
00000, 00000, "normal, successful completion"
// *Cause: Normal exit.
// *Action: None.
00001, 00000, "unique constraint (%s.%s) violated"
// *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
// For Trusted Oracle configured in DBMS MAC mode, you may see
// this message if a duplicate entry exists at a different level.
// *Action: Either remove the unique restriction or do not insert the key.
/0002 reserved for v2 compatibility (null column)
/0003 reserved for v2 compatibility (column value truncated)
/0004 reserved for v2 compatibility (end-of-fetch)
/0009 reserved for v2 compatibility
/
/ 10 - 49 user session and session switching errors
/
00017, 00000, "session requested to set trace event"
// *Cause: The current session was requested to set a trace event by another
// session.
// *Action: This is used internally; no action is required.
00018, 00000, "maximum number of sessions exceeded"
// *Cause: All session state objects are in use.
// *Action: Increase the value of the SESSIONS initialization parameter.
Evil's are also part of these error code inside Oracle...
10668, 00000, "Inject
Evil Identifiers"
// *Cause: event 10668 is set to some number > 0, causing 1/(value-1) of all
// identifiers to be replaced by a maximum amount of x's. It is
// common for an identifier to be parsed once with a max of 30 bytes,
// then reparsed later with a max of 4000, so it may not be possible
// to inject such an identifier without the aid of this event. A
// value of 1 causes no identifiers to be corrupted.
// *Action: never set this event
Happy DBA... ;)
