Database Software

All,

Thank you for reviewing this.

BACKGROUND_: I'm running Oracle 11g v11.2.0.1.0 on Windows 2003 (Standard) v5.2.3790. The server presently hosts 4-databases; all run the same version of Oracle.

On a nightly basis, a dump of each database is created using "EXP". The dump file is zipped and copied to an archive server. The process is implemented using a (DOS) batch file, which calls EXP, ZIP, and XCOPY respectively. The fragment that calls EXP appears as follows:

SET ORACLE_SID=MyDatabase
EXP System/SysPassword FULL=Y FILE=MyDatabase.dmp CONSISTENT=Y LOG=MyDatabase_exp.log

ISSUE_: EXP intermittently freezes (stops) when creating a dump file as part of this nightly batch. The issue has occured with different databases on the server. When the issue occurs, the backup script doesn't fail rather it hangs waiting for EXP.

a) The dump file is being created on a volume with approximately 187G free. Of the four databases on this server, none of them is larger than 3.75G.
b) No errors in the event log.
c) No errors in the subject database's alert log.

If I login to a database exhibiting this problem as SYSTEM, I can see there is a session for (what I presume is) the errant export attempt.


INST_ID: 1
SID: 73
SERIAL#: 13209
SPID: 24852
USERNAME: SYSTEM
PROGRAM: exp.exe

The session remains until the instance is restarted -- even if I attempt to kill it with an "ALTER SESSION DISCONNECT ... IMMEDIATE;". All subsequent attempts to create a dump file fail until the errant session is killed. When I look at the session's SQL text, I see the following:

"BEGIN
:1 := "SYS"."DBMS_SERVER_ALERT_EXPORT".system_info_exp(:2,:3,:4,:5);
END;
BEGIN
dbms_ha_alerts_prvt.clear_instance_resources(:dbdomain, :dbuniquename, :instance_name, :event_time);
END;"

I've attempted to recompile objects in the SYS and SYSTEM schemas using. That hasn't correct a problem once it occured. I can't confirm if it has or has not prevented the issue.

There is nothing in the export log file, and the dump file has some size to it (e.g., it isn't 0 bytes).

Any thoughts on what is causing this issue and-or how to resolve?

Thankful,
Adam