ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes

Environment
10.2.0.4.0 Oracle Database on OEL 4 platform.

Problem
After I executed following expdp command, I immediately received following errors:

expdp system/******** tables=user1.table1 directory=expimp dumpfile=expdp_user1_table1.dmp logfile=expdp_user1_table1_`date +"%Y-%m-%d-%H%M"`.log parallel=4 tables=user1.table1


ORA-31693: Table data object "user1"."table1" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-39077: unable to subscribe agent ORA-39077: unable to subscribe agent KUPC$A_1_20110225

According to this link, I reattached to the job and modified my expdp command like following:
1 Check the expdp job name:

select job_name,state from dba_datapump_jobs;
JOB_NAME STATE
—————————— ——————————
SYS_EXPORT_TABLE_01 NOT RUNNING
2 Re-attach to the job and kill it
expdp system/xxx attach=SYS_EXPORT_TABLE_01

Export> kill_job

3 Redo export
expdp system/******** tables=user1.table1 directory=expimp dumpfile=expdp_user1_table1_%U.dmp logfile=expdp_user1_table1_`date +"%Y-%m-%d-%H%M"`.log parallel=4 tables=user1.table1



The above execution returned following new errors:

ORA-31693: Table data object "USER1"."TABLE1
ORA-29913: error in executing ODCIEXTTABLEOPEN callout .....
ORA-39077: unable to subscribe agent ORA-39077:........



Solution
Remove "parallel=4", it just works. I learned from my experience doing expdp/impdp in RAC environment, the "parallel" option is tricky, in 10g or 11g RAC, I had to add one more option of "cluster=N" in addition to "parallel=M", or it will fail.

References:

• Data pump export error ORA-39095 with FILESIZE and PARALLEL clauses
• Using Datapump Restart Capability for ORA-39095

DataPump Export/Import in Rac Instance Alters SERVICE_NAMES

Environment
2 nodes 11.2.0.2 Oracle RAC on OEL 5.5 platform. 


Problem
Yesterday while other DBA was doing DataPump Import on the same node, I was working on Grid Control agent installation and noticed that the agent host's service name was changed to something like "SYS$SYS.KUPC$C_1_20060315133546.xxx.yyyy.COM". In the alert log file, it recorded that:

ALTER SYSTEM SET service_names='SYS$SYS.KUPC$S_1_20110207110835.node.hostname.COM','SYS$SYS.KUPC$C_1_20110207110835.node.hostname.COM','SYS$SYS.KUPC$C_1_20110207111059.node.hostname.COM' SC
OPE=MEMORY SID='node1';
ALTER SYSTEM SET service_names='SYS$SYS.KUPC$C_1_20110207111059.node.hostname.COM','SYS$SYS.KUPC$S_1_20110207110835.node.hostname.COM','SYS$SYS.KUPC$C_1_20110207110835.node.hostname.COM','S
YS$SYS.KUPC$S_1_20110207111059.node.hostname.COM' SCOPE=MEMORY SID='node1';


In the $ORACLE_BASE/sysman/emd/targets.xml file in the agent host, there are several similar service name entries, which causing Grid Control server to connect to the agent. 


Solution
According to Metalink note 363396.1 - DataPump Export: Rac Instance Startup/Shutdown Alters SERVICE_NAMES [ID 363396.1]: 
"DataPump doesn't change existing service name but adds new ones for its own queue operation.

Assuming you started instance with service name TEST01 and TEST02, it will add another one at the start of operation and put the original value back at the end of operation.

There is no need to be concerned of DataPump alerting SERVICE_NAMES.

It adds new services on top of existing ones and removes the added one at the end of operation."

According to the note, I didn't change the service name back to its original value, instead I modified the targets.xml file to replace the newly created service name to its original one like "node1.hostname.com". 

When the DataPump Import finished at 16:51, the alert log file recorded following actions:

Mon Feb 07 16:51:09 2011
ALTER SYSTEM SET service_names='SYS$SYS.KUPC$S_1_20110207164733.node1.hostname.COM' SCOPE=MEMORY SID='racflo1';
ALTER SYSTEM SET service_names='node1.hostname.com' SCOPE=MEMORY SID='racflo1';



Related links

• 10g RAC 节点1 exp 后 service_names='SYS$SYS.KUPC$S_1_2010013113

After suspend trigger to send out email alert to DBA about resumable space suspend

Environment
2-Nodes 11.2.0.2 Oracle RAC on OEL 5.5x64 bit platform.


Problem
During Datapump Import, when a tablespace runs out of space, the import job will be in "ORA-39171: Job is experiencing a resumable wait" state. By default, the database will not send out any alert to DBA about this problem unless DBA is actively monitoring this import job or check the alert log file. 


Solution
To address this problem and free DBAs from constantly monitoring the import job, a "AFTER SUSPEND System Event Trigger" will be created in the database, which will then email DBAs when a "resumable wait" problem happens in the database. 
1 Check system resumable_timeout setting
SQL> show parameters resumable_timeout


2 Set resumable_timeout value
If the above query shows resumable_timeout=0, change it to a desired seconds:
SQL> alter system set resumable_timeout=3600 scope=both;


Note: to understand the usage of this parameter, this is a good information page - Oracle Resumable Space Allocation

3 Enable UTL_MAIL package in order to use utl_mail to send out alert emails
3.1 created the UTL_MAIL package by running the following script under SYS schema

 SQL> @?/rdbms/admin/utlmail.sql

SQL> @?/rdbms/admin/prvtmail.plb

3.2 Grants the execute on UTL_MAIL privilege either to PUBLIC or to the user 
   which will use the package, running one of this statement from SYS:
SQL> GRANT EXECUTE ON UTL_MAIL TO PUBLIC;
     -or-
SQL> GRANT EXECUTE ON UTL_MAIL TO ;

Note: 

-- How to Use the UTL_MAIL Package [ID 269375.1]

-- How to Send Any Type of Attachment with UTL_MAIL [ID 946811.1]

3.3 Make sure the SMTP_OUT_SERVER parameter is defined in the database

SQL> show parameters smtp_out_server

Configure it if it's not set:

SQL>alter system set smtp_out_server=mail.hostname.com scope=both;

Note:

--UTL_MAIL Package

3.4 Test the utl_mail.send procedure to make sure it works

CREATE OR REPLACE PROCEDURE send_email AS
--DECLARE
   CRLF        CHAR(2) := CHR(10) || CHR(13);
BEGIN
   UTL_MAIL.SEND(
        sender => 'yi.lin@wealthengine.com'
       ,recipients => 'yi.lin@wealthengine.com'
       ,cc => NULL
       ,bcc =>  NULL
       ,subject => 'Test Email'
       ,message => 'Test line1' || CRLF || CRLF
       || 'Test line2'
       ,mime_type => 'text/plain; charset=us-ascii'
       ,priority => 1
   );


   EXCEPTION
   WHEN OTHERS THEN
    --  dbms_output.put_line('Fehler');
     raise_application_error(-20001,'The following error has occured: ' || sqlerrm);
   END;
   /
SHOW ERRORS


exec send_email;


4 After successful testing of the utl_mail.send procedure, create a "AFTER SUSPEND System Event Trigger" in the database:

CREATE OR REPLACE TRIGGER resumable_alert
AFTER SUSPEND
ON DATABASE
DECLARE
CRLF        CHAR(2) := CHR(10) || CHR(13);
BEGIN
   UTL_MAIL.SEND(
        sender => 'sting@wealthengine.com'
       ,recipients => 'yi.lin@wealthengine.com'
       ,cc => NULL
       ,bcc =>  NULL
       ,subject => 'Space resumable alert found in the database'
       ,message => 'Space resumable alert found in the database' || CRLF || CRLF
       || ' Iusse '       ,mime_type => 'text/plain; charset=us-ascii'       ,priority => 1   );   EXCEPTION   WHEN OTHERS THEN    --  dbms_output.put_line('Fehler');     raise_application_error(-20001,'The following error has occured: ' || sqlerrm);   END;   /SHOW ERRORSNote: --AFTER SUSPEND Trigger--RESUMABLE SPACE ALLOCATION in Oracle 10g--A Simple Example On AFTER SUSPEND System Event Trigger [ID 258827.1]

11gR2 DataPump Import hang during "Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX" stage

Environment
2-Nodes 11.2.0.2 Oracle RAC on OEL 5.5x64bit platform

Problem
I kicked off Datapump Import around 10 PM at night on 12/29/10, 12 hours later, the import log file shows that it's doing "Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX", the last time the log file was updated was at 23:31 on 12/29/10, so it means the import job has been waiting on this "Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX" for almost 11 hours.

Cause
1 Check v$session
select username,machine,module,sid,state,event,seq#,seconds_in_wait from v$session where type<>'BACKGROUND' and username='FPP'

Or
2 Query v$session_wait view

SELECT sid, event, seconds_in_wait 

FROM v$session_wait WHERE sid = $SID;


Or
3 Query v$system_event or v$session_event view

SELECT event, total_waits, time_waited FROM v$system_event WHERE event like '%suspend%';

select sid, event, total_waits, time_waited FROM v$session_event WHERE event like '%suspend%';

4 Check dba_resumable view to see when did the suspend happened

SQL> select user_id,session_id, status, start_time, suspend_time,resume_time, error_msg from dba_resumable;

So FPPI tablespace is out of space.

5 Check FPPI tablespace usage to check its status.

SQL> select tablespace_name, file_name, maxbytes/1024/1024, user_bytes/1024/1024 from dba_data_files where tablespace_name='FPPI';

So based on the above views, the FPPI tablespace is out of space, add more datafile to this tablespace would resume the Datapump Import job.


Solution
Add more datafiles to FPPI tablespace will resume the Datapump Import job.
Before adding more datafile:

SQL> alter tablespace fppi add datafile '/u08/oradata/racflo/FPPI04.dbf' size 1g autoextend on;


Tablespace altered.

After the above command:

Related Links

Resumable Space Management - (Oracle 9i) by Jeff Hunter

11gR2 DataPump Import Errors: ORA-31693/ORA-31640/ORA-19505/ORA-27037

Environment
2-nodes Oracle 11.2.0.2 RAC on OEL 5.5-64bit platform

Problem
Oracle DataPump Import failed with following errors:
impdp system/******** directory=expimp dumpfile=expdp_fpp_xxx2racflo_%U.dmp logfile=impdp_fpp_xxx2racflo-2010-12-28-1707.log job_name=impdp_xxx2racflo_fpp parallel=4


ORA-31693: Table data object "FPP"."FPP_WALT_MATCH" failed to load/unload and is being skipped due to error:
ORA-31640: unable to open dump file "/opt/DBfileLoad/expdp_fpp_blink2racflo_04.dmp" for read
ORA-19505: failed to identify file "/opt/DBfileLoad/expdp_fpp_blink2racflo_04.dmp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


Cause
According to Oracle Support/Metalink Note 1071373.1 - Cannot run DATAPUMP With PARALLEL > 1 ON 11.2 RAC  , "Datapump on 11.2 RAC with PARALLEL > 1 still hits the following errors though the unpublished Bug 8415620 is fixed into 11.2.",  and "From 11.2, Datapump new parameter CLUSTER is introduced". 


Solution
1
"To force Data Pump to use only the instance where the job is started and to replicate pre-Oracle Database 11g release 2 (11.2) behavior, specify CLUSTER=N."

Example:
$ impdp system/******** directory=expimp dumpfile=expdp_fpp_xxx2racflo_%U.dmp logfile=impdp_fpp_xxx2racflo-2010-12-28-1707.log job_name=impdp_xxx2racflo_fpp CLUSTER=N PARALLEL=4


2 Other recommended solutions by Oracle for database between 10.2.0.4 to 11.1.0.7: Impdp Fails with ORA-31693 ORA-29913 ORA-29400 KUP-11010 if Parallel > 1 is Used in RAC Database [ID 762475.1]


2.1. Run the Data Pump import job with parallel=1 (default)

OR

2.2. Run the Data Pump import job if only one instance is started.