Thursday, July 12, 2012

Enterprise Manager SQL Script Job errors

I use the Enterprise Manager Job System very often, such a convenient way of getting data from different database sources (even more useful via emcli into a common output location).

Some errors that come up doing that frequently include:

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 12 18:57:32 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

SQL> SQL> SQL> SQL> ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0


SP2-0584: EXIT variable "SQLCODE" was non-numeric
Usage: { EXIT | QUIT } [ SUCCESS | FAILURE | WARNING | n |
| : ] [ COMMIT | ROLLBACK ]

Error from SQLPLUS: 1
This is a fairly easy one - trying to query a Data Guard standby database which is of course not open as user who is not SYSDBA.
Just don't execute the job on standby database targets or - if the task you want to be done can be done in mount state normally - execute it as SYSDBA user.

Next one is a little more tricky:

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 12 18:57:32 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

SQL> SQL> SQL> SQL> ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0


SP2-0584: EXIT variable "SQLCODE" was non-numeric
Usage: { EXIT | QUIT } [ SUCCESS | FAILURE | WARNING | n |
| : ] [ COMMIT | ROLLBACK ]

Error from SQLPLUS: 1

You'll get this one when your database target is registered to the wrong agent (e.g. in a failover environment). The job system does not connect to the database directly by any kind of sqlnet usage - it logs on to the server and executes the local sqlplus binary. That's also why you have to specify host credentials for an SQL Script job, in case you ever wondered.

emcli relocate (also described here) to the correct agent solves that behavior.

1 comment: