{"id":73140,"date":"2015-12-10T11:38:46","date_gmt":"2015-12-10T11:38:46","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/oracle-data-pump-in-rac\/"},"modified":"2021-07-14T13:07:19","modified_gmt":"2021-07-14T13:07:19","slug":"oracle-data-pump-in-rac","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/oracle-data-pump-in-rac\/","title":{"rendered":"Oracle Data Pump in RAC"},"content":{"rendered":"<p>Oracle Data Pump was introduced in Oracle Database 10g to enable very high-speed transfer of data and metadata between databases. A salient feature of Data Pump is that it can parallelize the export and import jobs for maximum performance.<\/p>\n<p>However, prior to Oracle Database 11<em>g <\/em>release 2 (11.2), if a Data Pump job is executed in parallel on an instance of an Real Application Cluster (RAC) database, the parallelism is employed only on that instance without utilizing the potentially valuable idle resources of other instances.<\/p>\n<p>From Oracle Database 11.2 onwards, parallelism is no longer confined to a single instance. Rather worker processes can be distributed across multiple Oracle RAC instances to better utilize Oracle RAC resources. The newly-introduced client parameter CLUSTER can be employed to specify whether you want to use the cluster resources. When CLUSTER=Y (default) is specified, Data Pump attempts to use all active Oracle RAC instances.<\/p>\n<p>To exercise more control over the instances on which the job should run, the SERVICE_NAME parameter can be mentioned where SERVICE_NAME must be a valid known service of the database. In this case, the Data Pump job will run only on those instance where the service is available.<\/p>\n<p>If any Oracle RAC instance where the job that is running dies or leaves the cluster, the job aborts and can be restarted at some future time.<\/p>\n<p>In this article, I will demonstrate:<\/p>\n<ul>\n<li><strong>Case-I<\/strong>: Parallelization of an export job across all active Oracle RAC instances<\/li>\n<li><strong>Case-II<\/strong>: Parallelization of an export job across a subset of Oracle RAC instances<\/li>\n<\/ul>\n<h2>Current scenario:<\/h2>\n<ul>\n<li>Name of the cluster: cluster01<\/li>\n<li>Number of nodes: 3 (host01, host02, host03)<\/li>\n<li>RAC Database version: 11.2.0.3<\/li>\n<li>Name of RAC database: orcl<\/li>\n<li>Number of instances: 3<\/li>\n<li>Names of instances: orcl1, orcl2, orcl3<\/li>\n<\/ul>\n<p>In order that the Data Pump job is distributed across multiple instances of an RAC database, the following prerequisites should be met:<\/p>\n<ul>\n<li>The directory object should point to shared storage that is accessible by any Oracle RAC instances designated to run Data Pump worker processes.<\/li>\n<li>The PARALLEL parameter should be more than 1 and less than or equal to the number of files in the dump file set.<\/li>\n<li>To specify enough dump files by specifying DUMPFILE parameter, the preferred approach is to use the wildcard option by using a substitution variable (%U) in the filename.<\/li>\n<li>LOGFILE parameter should point to a location on the local file system. Log file is created on the node hosting the instance where master process is running.<\/li>\n<\/ul>\n<h2>Demonstration<\/h2>\n<h3>Case-I: Export job distributed across all active Oracle RAC instances<\/h3>\n<ul>\n<li>First, let\u2019s create a directory object which points to shared storage and is accessible by all the three instances of the database:<\/li>\n<\/ul>\n<pre>SQL&gt;drop directory dp_shared_dir;\r\n\r\nSQL&gt;create directory DP_SHARED_DIR as '+DATA\/orcl\/';\r\n\r\nSQL&gt;grant read, write on directory dp_shared_dir to public;\r\n<\/pre>\n<ul>\n<li>Issue the command to export SH schema in parallel across all active Oracle RAC instances:<\/li>\n<\/ul>\n<ul>\n<li>Degree of parallelism = 6<\/li>\n<li>Dumpfile &#8211; Create in the newly created directory DP_SHARED_DIR with names expsh%U.dmp beginning with 01 for %U, then using 02, 03, and so on.<\/li>\n<li>Logfile \u2013 Create in the location on local file system pointed to by the directory object DATA_PUMP_DIR<\/li>\n<\/ul>\n<pre>[oracle@host01 root]$ expdp system\/oracle@orcl schemas=sh directory=dp_shared_dir parallel=6 cluster=y logfile=data_pump_dir:expsh.log dumpfile='expsh%U.dmp' reuse_dumpfiles=y\r\n\r\nExport: Release 11.2.0.3.0 - Production on Thu Dec 3 12:49:58 2015\r\n\r\nCopyright (c) 1982, 2011, Oracle and\/or its affiliates. All rights reserved.\r\n\r\nConnected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production\r\nWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,\r\nData Mining and Real Application Testing options\r\nStarting \"SYSTEM\".\"<span style=\"color: red;\"><strong>SYS_EXPORT_SCHEMA_06<\/strong><\/span>\": system\/********@orcl schemas=sh directory=dp_shared_dir parallel=6 cluster=y logfile=data_pump_dir:expsh.log dumpfile=expsh%U.dmp reuse_dumpfiles=y\r\nEstimate in progress using BLOCKS method...\r\nProcessing object type SCHEMA_EXPORT\/TABLE\/TABLE_DATA\r\nTotal estimation using BLOCKS method: 273.8 MB\r\nProcessing object type SCHEMA_EXPORT\/USER\r\nProcessing object type SCHEMA_EXPORT\/SYSTEM_GRANT\r\nProcessing object type SCHEMA_EXPORT\/ROLE_GRANT\r\nProcessing object type SCHEMA_EXPORT\/DEFAULT_ROLE\r\nProcessing object type SCHEMA_EXPORT\/PRE_SCHEMA\/PROCACT_SCHEMA\r\n\r\n. . exported \"SH\".\"CUSTOMERS\" 9.853 MB 55500 rows\r\n\r\n. . exported \"SH\".\"COSTS\":\"COSTS_Q2_1998\" 79.52 KB 2397 rows\r\n\r\n. . exported \"SH\".\"COSTS\":\"COSTS_Q2_1999\" 132.5 KB\r\n\r\n\u2026.<\/pre>\n<ul>\n<li>Verify that the Data Pump job is distributed across 6 worker processes on all the three instances of the RAC database:<\/li>\n<\/ul>\n<pre>SQL&gt; select inst_id, session_type from dba_datapump_sessions;\r\n\r\n   INST_ID SESSION_TYPE\r\n\r\n---------- --------------\r\n\r\n         <span style=\"color: red;\"><strong>3 WORKER<\/strong><\/span>\r\n         <span style=\"color: red;\"><strong>3 WORKER<\/strong><\/span>\r\n         <span style=\"color: red;\"><strong>2 WORKER<\/strong><\/span>\r\n         <span style=\"color: red;\"><strong>2 WORKER<\/strong><\/span>\r\n         1 DBMS_DATAPUMP\r\n         1 MASTER\r\n         <span style=\"color: red;\"><strong>1 WORKER<\/strong><\/span>\r\n         <span style=\"color: red;\"><strong>1 WORKER<\/strong><\/span><\/pre>\n<ul>\n<li>While the export is running, let us abort the database instance on host01 to verify that the Data Pump job can be restarted in case any instance where the job is running aborts.<\/li>\n<\/ul>\n<pre>[root@<strong><span style=\"color: red;\">host01<\/span><\/strong> log]# ps -ef |grep pmon\r\ngrid 5597 1 0 08:25 ? 00:00:00 asm_pmon_+ASM1\r\n<strong><span style=\"color: red;\">oracle 27612 1 0 09:46 ? 00:00:00 ora_pmon_orcl1<\/span><\/strong>\r\nroot 30103 8495 0 11:39 pts\/1 00:00:00 grep pmon\r\n\r\n[root@host01 log]# kill -9 27612<\/pre>\n<p>As the instance orcl1 has crashed, the Data Pump job aborts.<\/p>\n<pre>\u2026\u2026\r\n\r\n. . exported \"SH\".\"SALES\":\"SALES_Q2_1999\" 1.754 MB 54233 rows\r\n\r\n. . exported \"SH\".\"COSTS\":\"COSTS_Q1_1999\" 183.5 KB 5884 rows\r\n\r\n. . exported \"SH\".\"COSTS\":\"COSTS_Q1_1998\" 139.5 KB 4411 rows\r\n\r\n. . exported \"SH\".\"SALES\":\"SALES_Q2_2000\" 1.802 MB 55515 rows\r\n\r\n<span style=\"color: red;\"><strong>UDE-03113: operation generated ORACLE error 3113<\/strong><\/span>\r\n<span style=\"color: red;\"><strong>ORA-03113: end-of-file on communication channel<\/strong><\/span>\r\n<span style=\"color: red;\"><strong>Process ID: 20597<\/strong><\/span>\r\n<span style=\"color: red;\"><strong>Session ID: 51 Serial number: 233<\/strong><\/span>\r\n\r\n<span style=\"color: red;\"><strong>UDE-03114: operation generated ORACLE error 3114<\/strong><\/span>\r\n<span style=\"color: red;\"><strong>ORA-03114: not connected to ORACLE<\/strong><\/span><\/pre>\n<ul>\n<li>Let us restart the job by\u00a0<strong>ATTACHing <\/strong>to the job using the name of the job (as identified in expdp command output) as a parameter to the expdp command:<\/li>\n<\/ul>\n<pre>[oracle@host01 root]$ expdp system\/oracle@orcl <span style=\"color: red;\"><strong>ATTACH=SYS_EXPORT_SCHEMA_06<\/strong><\/span>\r\n\r\nExport: Release 11.2.0.3.0 - Production on Thu Dec 3 12:53:06 2015\r\n\r\nCopyright (c) 1982, 2011, Oracle and\/or its affiliates. All rights reserved.\r\n\r\nConnected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production\r\nWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,\r\nData Mining and Real Application Testing options\r\n\r\n<span style=\"color: red;\"><strong>Job: SYS_EXPORT_SCHEMA_06<\/strong><\/span>\r\n\tOwner: SYSTEM\r\n\tOperation: EXPORT\r\n\tCreator Privs: TRUE\r\n\tGUID: 25FA37F1818A5075E053B7C909C0116F\r\n\tStart Time: Thursday, 03 December, 2015 12:53:11\r\n\tMode: SCHEMA\r\n\tInstance: orcl3\r\n\tMax Parallelism: 6\r\n\tEXPORT Job Parameters:\r\n\tParameter Name Parameter Value:\r\n\t<span style=\"color: red;\"><strong>CLIENT_COMMAND system\/********@orcl schemas=sh directory=dp_shared_dir parallel=6 cluster=y logfile=data_pump_dir:expsh.log dumpfile=expsh%U.dmp reuse_dumpfiles=y <\/strong><\/span>\r\n\r\n\tState: IDLING\r\n\tBytes Processed: 27,664,048\r\n\tPercent Done: 23\r\n\tCurrent Parallelism: 6\r\n\tJob Error Count: 0\r\n\tDump File: +DATA\/orcl\/\/expsh%u.dmp\r\n\tDump File: +DATA\/orcl\/\/expsh01.dmp\r\n\t\tbytes written: 4,096\r\n\tDump File: +DATA\/orcl\/\/expsh02.dmp\r\n\t\tbytes written: 27,385,856\r\n\tDump File: +DATA\/orcl\/\/expsh03.dmp\r\n\t\tbytes written: 147,456\r\n\tDump File: +DATA\/orcl\/\/expsh04.dmp\r\n\t\tbytes written: 192,512\r\n\tDump File: +DATA\/orcl\/\/expsh05.dmp\r\n\t\tbytes written: 4,096\r\n\tDump File: +DATA\/orcl\/\/expsh06.dmp\r\n\t\tbytes written: 4,096\r\n\r\nWorker 1 Status:\r\n\tProcess Name: DW00\r\n\tState: UNDEFINED\r\n\tObject Schema: SH\r\n\tObject Type: SCHEMA_EXPORT\/DEFAULT_ROLE\r\n\tCompleted Objects: 1\r\n\tTotal Objects: 1\r\n\tWorker Parallelism: 1\r\n\r\nWorker 2 Status:\r\n\tProcess Name: DW00\r\n\tState: UNDEFINED\r\n\tObject Schema: SH\r\n\tObject Name: COSTS\r\n\tObject Type: SCHEMA_EXPORT\/TABLE\/TABLE_DATA\r\n\tCompleted Objects: 1\r\n\tTotal Objects: 66\r\n\tWorker Parallelism: 1\r\n\r\nWorker 3 Status:\r\n\tProcess Name: DW00\r\n\tState: UNDEFINED\r\n\r\nWorker 4 Status:\r\n\tProcess Name: DW03\r\n\tState: UNDEFINED\r\n\r\nWorker 5 Status:\r\n\tProcess Name: DW01\r\n\tState: UNDEFINED\r\n\tObject Schema: SH\r\n\tObject Name: COSTS\r\n\tObject Type: SCHEMA_EXPORT\/TABLE\/TABLE_DATA\r\n\tCompleted Objects: 1\r\n\tTotal Objects: 66\r\n\tWorker Parallelism: 1\r\n\r\nWorker 6 Status:\r\n\tProcess Name: DW01\r\n\tState: UNDEFINED\r\n\r\nExport&gt;<\/pre>\n<ul>\n<li>We will restart the job with a different degree of parallelism, say 4 (earlier it was 6):<\/li>\n<\/ul>\n<pre>Export&gt; parallel=4\r\n\r\nExport&gt; START_JOB<\/pre>\n<p>It can be seen that four worker processes have been started on instances 2 and 3 as instance 1 has been aborted.<\/p>\n<pre>SQL&gt; select inst_id, session_type from dba_datapump_sessions;\r\n\r\n   INST_ID SESSION_TYPE\r\n---------- --------------\r\n         3 DBMS_DATAPUMP\r\n         3 MASTER\r\n         <span style=\"color: red;\"><strong>3 WORKER<\/strong><\/span>\r\n         <span style=\"color: red;\"><strong>3 WORKER<\/strong><\/span>\r\n         <span style=\"color: red;\"><strong>3 WORKER<\/strong><\/span>\r\n         <span style=\"color: red;\"><strong>2 WORKER<\/strong><\/span>\r\n\r\n6 rows selected.<\/pre>\n<p>To show progress again, let us issue the <strong>CONTINUE_CLIENT<\/strong>\u00a0command:<\/p>\n<pre>Export&gt; continue_client\r\n\r\n<span style=\"color: red;\"><strong>Job SYS_EXPORT_SCHEMA_06 has been reopened <\/strong><\/span>at Thursday, 03 December, 2015 12:53\r\nRestarting \"SYSTEM\".\"SYS_EXPORT_SCHEMA_06\": system\/********@orcl schemas=sh directory=dp_shared_dir parallel=6 cluster=y logfile=data_pump_dir:expsh.log dumpfile=expsh%U.dmp reuse_dumpfiles=y\r\n. . exported \"SH\".\"COSTS\":\"COSTS_Q1_2000\" 120.6 KB 3772 rows\r\n. . exported \"SH\".\"COSTS\":\"COSTS_Q1_2001\" 227.8 KB 7328 rows\r\n. . exported \"SH\".\"SALES\":\"SALES_Q3_2000\" 1.909 MB 58950 rows\r\n. . exported \"SH\".\"SALES\":\"SALES_Q2_2001\" 2.051 MB 63292 rows\r\n\r\n. . .\r\n\r\n. . .\r\n\r\n. . exported \"SH\".\"SALES\":\"SALES_Q4_2002\" 0 KB 0 rows\r\n. . exported \"SH\".\"SALES\":\"SALES_Q4_2003\" 0 KB 0 rows\r\nProcessing object type SCHEMA_EXPORT\/PRE_SCHEMA\/PROCACT_SCHEMA\r\n. . exported \"SH\".\"SALES\":\"SALES_Q3_1998\" 1.633 MB 50515 rows\r\nProcessing object type SCHEMA_EXPORT\/TABLE\/TABLE\r\nProcessing object type SCHEMA_EXPORT\/TABLE\/GRANT\/OWNER_GRANT\/OBJECT_GRANT\r\nProcessing object type SCHEMA_EXPORT\/TABLE\/COMMENT\r\nProcessing object type SCHEMA_EXPORT\/TABLE\/INDEX\/INDEX\r\nProcessing object type SCHEMA_EXPORT\/TABLE\/CONSTRAINT\/CONSTRAINT\r\nProcessing object type SCHEMA_EXPORT\/TABLE\/INDEX\/STATISTICS\/INDEX_STATISTICS\r\nProcessing object type SCHEMA_EXPORT\/VIEW\/VIEW\r\nProcessing object type SCHEMA_EXPORT\/TABLE\/CONSTRAINT\/REF_CONSTRAINT\r\nProcessing object type SCHEMA_EXPORT\/TABLE\/INDEX\/BITMAP_INDEX\/INDEX\r\nProcessing object type SCHEMA_EXPORT\/TABLE\/INDEX\/STATISTICS\/BITMAP_INDEX\/INDEX_STATISTICS\r\nProcessing object type SCHEMA_EXPORT\/TABLE\/STATISTICS\/TABLE_STATISTICS\r\nProcessing object type SCHEMA_EXPORT\/TABLE\/INDEX\/DOMAIN_INDEX\/INDEX\r\nProcessing object type SCHEMA_EXPORT\/MATERIALIZED_VIEW\r\nProcessing object type SCHEMA_EXPORT\/DIMENSION\r\nMaster table \"SYSTEM\".\"SYS_EXPORT_SCHEMA_06\" successfully loaded\/unloaded\r\n******************************************************************************\r\nDump file set for SYSTEM.SYS_EXPORT_SCHEMA_06 is:\r\n\t+DATA\/orcl\/\/expsh01.dmp\r\n\t+DATA\/orcl\/\/expsh02.dmp\r\n\t+DATA\/orcl\/\/expsh03.dmp\r\n\t+DATA\/orcl\/\/expsh04.dmp\r\n<span style=\"color: red;\"><strong>Job \"SYSTEM\".\"SYS_EXPORT_SCHEMA_06\" successfully completed at 12:56:29<\/strong><\/span><\/pre>\n<p>Thus, a Data Pump job:<\/p>\n<ul>\n<li>Can be parallelized across all the available instances of a RAC database.<\/li>\n<li>Restarted in future if any instance, where the job is running, aborts.<\/li>\n<\/ul>\n<h3>Case-II: Parallelization of export job across a subset of Oracle RAC instances<\/h3>\n<p>In case we want to run the job against a subset of instances, we can specify the SERVICE_NAME parameter which points to a service of the database.<\/p>\n<ul>\n<li>In order to demonstrate this, let us first create and start a service called orcls for the database with preferred instances orcl1, orcl2 and available instance orcl3. It can be observed that the newly-created service orcls is currently available on instances orcl1 and orcl2.<\/li>\n<\/ul>\n<pre>[oracle@host01 root]$ srvctl add service -s orcls -d orcl -r orcl1,orcl2 -a orcl3\r\n\r\n[oracle@host01 root]$ srvctl status service -s orcls -d orcl\r\n\r\nService orcls is not running.\r\n\r\n[oracle@host01 root]$ srvctl start service -s orcls -d orcl\r\n\r\n[oracle@host01 root]$ srvctl status service -s orcls -d orcl\r\n\r\n<span style=\"color: red;\"><strong>Service orcls is running on instance(s) orcl1,orcl2<\/strong><\/span><\/pre>\n<ul>\n<li>Let us once again export the SH schema with a parallelism of 6 as earlier, but this time I am going to specify an additional parameter of SERVICE_NAME on the command line with points to service orcls, which is currently running on orcl1 and orcl2.<\/li>\n<\/ul>\n<pre>[oracle@host01 root]$ expdp system\/oracle@orcl schemas=sh directory=dp_shared_dir parallel=6 cluster=y logfile=data_pump_dir:expsh.log dumpfile='expsh%U.dmp' reuse_dumpfiles=y <span style=\"color: red;\"><strong>service_name=orcls<\/strong><\/span>\r\n\r\nExport: Release 11.2.0.3.0 - Production on Fri Dec 4 14:48:22 2015\r\n\r\nCopyright (c) 1982, 2011, Oracle and\/or its affiliates. All rights reserved.\r\n\r\nConnected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production\r\nWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,\r\nData Mining and Real Application Testing options\r\n<span style=\"color: red;\"><strong>Starting \"SYSTEM\".\"SYS_EXPORT_SCHEMA_09\"<\/strong><\/span>: system\/********@orcl schemas=sh directory=dp_shared_dir parallel=6 cluster=y logfile=data_pump_dir:expsh.log dumpfile=expsh%U.dmp reuse_dumpfiles=y service_name=orcls\r\nEstimate in progress using BLOCKS method...\r\nProcessing object type SCHEMA_EXPORT\/TABLE\/TABLE_DATA\r\nTotal estimation using BLOCKS method: 273.8 MB\r\nProcessing object type SCHEMA_EXPORT\/USER\r\nProcessing object type SCHEMA_EXPORT\/SYSTEM_GRANT\r\nProcessing object type SCHEMA_EXPORT\/ROLE_GRANT\r\nProcessing object type SCHEMA_EXPORT\/DEFAULT_ROLE\r\nProcessing object type SCHEMA_EXPORT\/PRE_SCHEMA\/PROCACT_SCHEMA\r\n. . exported \"SH\".\"COSTS\":\"COSTS_Q1_1998\" 139.5 KB 4411 rows\r\n. . exported \"SH\".\"COSTS\":\"COSTS_Q2_1998\" 79.52 KB 2397 rows\r\n. . exported \"SH\".\"COSTS\":\"COSTS_Q2_1999\" 132.5 KB 4179 rows\r\n\r\n\u2026..<\/pre>\n<ul>\n<li>While the job is running, it can be verified that this time parallelism has been constrained across instances orcl1 and orcl2 only where the service is currently available.<\/li>\n<\/ul>\n<pre>SQL&gt; select inst_id, session_type from dba_datapump_sessions;\r\n\r\n   INST_ID SESSION_TYPE\r\n---------- --------------\r\n         <span style=\"color: red;\"><strong>1 WORKER<\/strong><\/span>\r\n         <span style=\"color: red;\"><strong>1 WORKER<\/strong><\/span>\r\n         2 DBMS_DATAPUMP\r\n         2 MASTER\r\n         <span style=\"color: red;\"><strong>2 WORKER<\/strong><\/span>\r\n         <span style=\"color: red;\"><strong>2 WORKER<\/strong><\/span>\r\n         <span style=\"color: red;\"><strong>2 WORKER<\/strong><\/span>\r\n         <span style=\"color: red;\"><strong>2 WORKER<\/strong><\/span>\r\n\r\n8 rows selected.<\/pre>\n<ul>\n<li>Abort the RAC database instance on one of the nodes, say host01, where Data Pump export is currently running:<\/li>\n<\/ul>\n<pre> [root@<span style=\"color: red;\"><strong>host01<\/strong><\/span> ~]# ps -ef |grep pmon\r\ngrid      5598    1   0 13:07 ?         00:00:00 asm_pmon_+ASM1\r\noracle    6157    1   0 13:08 ?         00:00:00 ora_pmon_orcl1\r\nroot      7178 6735   0 13:14 pts\/1     00:00:00 grep pmon\r\n\r\n[root@host01 ~]# kill -9 6157<\/pre>\n<ul>\n<li>As the instance aborts, the export also gets terminated:<\/li>\n<\/ul>\n<pre>. . exported \"SH\".\"COSTS\":\"COSTS_Q2_1999\" 132.5 KB 4179 rows\r\n. . exported \"SH\".\"COSTS\":\"COSTS_Q2_2000\" 119.0 KB 3715 rows\r\n. . exported \"SH\".\"CUSTOMERS\" 9.853 MB 55500 rows\r\n. . exported \"SH\".\"COSTS\":\"COSTS_Q2_2001\" 184.5 KB 5882 rows\r\n\r\n<span style=\"color: red;\"><strong>UDE-03113: operation generated ORACLE error 3113<\/strong><\/span>\r\n<span style=\"color: red;\"><strong>ORA-03113: end-of-file on communication channel<\/strong><\/span>\r\n<span style=\"color: red;\"><strong>Process ID: 9617<\/strong><\/span>\r\n<span style=\"color: red;\"><strong>Session ID: 52 Serial number: 5<\/strong><\/span>\r\n\r\n<span style=\"color: red;\"><strong>UDE-03114: operation generated ORACLE error 3114<\/strong><\/span>\r\n<span style=\"color: red;\"><strong>ORA-03114: not connected to ORACLE<\/strong><\/span><\/pre>\n<ul>\n<ul>\n<li>Restart the export by ATTACHing to the job by specifying its name:<\/li>\n<\/ul>\n<\/ul>\n<p>&nbsp;<\/p>\n<pre>[oracle@host01 root]$ expdp system\/oracle@orcl <span style=\"color: red;\"><strong>ATTACH=SYS_EXPORT_SCHEMA_09<\/strong><\/span>\r\n\r\nExport: Release 11.2.0.3.0 - Production on Fri Dec 4 14:49:10 2015\r\n\r\nCopyright (c) 1982, 2011, Oracle and\/or its affiliates. All rights reserved.\r\n\r\nConnected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production\r\nWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,\r\nData Mining and Real Application Testing options\r\n\r\n<span style=\"color: red;\"><strong>Job: SYS_EXPORT_SCHEMA_09<\/strong><\/span>\r\n\tOwner: SYSTEM\r\n\tOperation: EXPORT\r\n\tCreator Privs: TRUE\r\n\tGUID: 260EFD3967B32591E053B7C909C0C0D4\r\n\tStart Time: Friday, 04 December, 2015 14:49:19\r\n\tMode: SCHEMA\r\n\tInstance: orcl1\r\n\tMax Parallelism: 6\r\n\tEXPORT Job Parameters:\r\n\tParameter Name Parameter Value:\r\n\t\t<span style=\"color: red;\"><strong>CLIENT_COMMAND system\/********@orcl schemas=sh directory=dp_shared_dir parallel=6 cluster=y logfile=data_pump_dir:expsh.log dumpfile=expsh%U.dmp reuse_dumpfiles=y service_name=orcls <\/strong><\/span>\r\n\tState: IDLING\r\n\tBytes Processed: 11,277,592\r\n\tPercent Done: 4\r\n\tCurrent Parallelism: 6\r\n\tJob Error Count: 0\r\n\tDump File: +DATA\/orcl\/\/expsh%u.dmp\r\n\tDump File: +DATA\/orcl\/\/expsh01.dmp\r\n\t\tbytes written: 4,096\r\n\tDump File: +DATA\/orcl\/\/expsh02.dmp\r\n\t\tbytes written: 10,473,472\r\n\tDump File: +DATA\/orcl\/\/expsh03.dmp\r\n\t\tbytes written: 827,392\r\n\tDump File: +DATA\/orcl\/\/expsh04.dmp\r\n\t\tbytes written: 4,096\r\n\tDump File: +DATA\/orcl\/\/expsh05.dmp\r\n\t\tbytes written: 4,096\r\n\r\nWorker 1 Status:\r\n\tProcess Name: DW00\r\n\tState: UNDEFINED\r\n\tObject Schema: SH\r\n\tObject Type: SCHEMA_EXPORT\/DEFAULT_ROLE\r\n\tCompleted Objects: 1\r\n\tTotal Objects: 1\r\n\tWorker Parallelism: 1\r\n\r\nWorker 2 Status:\r\n\tProcess Name: DW00\r\n\tState: UNDEFINED\r\n\tObject Schema: SH\r\n\tObject Name: COSTS\r\n\tObject Type: SCHEMA_EXPORT\/TABLE\/TABLE_DATA\r\n\tCompleted Objects: 1\r\n\tTotal Objects: 66\r\n\tWorker Parallelism: 1\r\n\r\nWorker 3 Status:\r\n\tProcess Name: DW02\r\n\tState: UNDEFINED\r\n\r\nWorker 4 Status:\r\n\tProcess Name: DW01\r\n\tState: UNDEFINED\r\n\tObject Schema: SH\r\n\tObject Name: COSTS\r\n\tObject Type: SCHEMA_EXPORT\/TABLE\/TABLE_DATA\r\n\tCompleted Objects: 1\r\n\tTotal Objects: 66\r\n\tWorker Parallelism: 1\r\n\r\nWorker 5 Status:\r\n\tProcess Name: DW04\r\n\tState: UNDEFINED\r\n\r\nWorker 6 Status:\r\n\tProcess Name: DW02\r\n\tState: UNDEFINED\r\n\tObject Schema: SH\r\n\tObject Name: COSTS\r\n\tObject Type: SCHEMA_EXPORT\/TABLE\/TABLE_DATA\r\n\tCompleted Objects: 1\r\n\tTotal Objects: 66\r\n\tWorker Parallelism: 1\r\n\r\n<strong>Export&gt; start_job<\/strong>\r\n\r\n<strong>Export&gt; continue_client<\/strong>\r\n\r\n<span style=\"color: red;\"><strong>Job SYS_EXPORT_SCHEMA_09 has been reopened at Friday, 04 December, 2015 14:49 <\/strong><\/span>\r\nRestarting \"SYSTEM\".\"SYS_EXPORT_SCHEMA_09\": system\/********@orcl schemas=sh directory=dp_shared_dir parallel=6 cluster=y logfile=data_pump_dir:expsh.log dumpfile=expsh%U.dmp reuse_dumpfiles=y service_name=orcls\r\nProcessing object type SCHEMA_EXPORT\/PRE_SCHEMA\/PROCACT_SCHEMA\r\n. . exported \"SH\".\"COSTS\":\"COSTS_Q1_1999\" 183.5 KB 5884 rows\r\n. . exported \"SH\".\"COSTS\":\"COSTS_Q4_1998\" 144.7 KB 4577 rows\r\n. . exported \"SH\".\"COSTS\":\"COSTS_Q4_1999\" 159.0 KB 5060 rows\r\n. . exported \"SH\".\"COSTS\":\"COSTS_Q4_2000\" 160.2 KB 5088 rows\r\n. . exported \"SH\".\"COSTS\":\"COSTS_Q4_2001\" 278.4 KB 9011 rows\r\n\u2026\u2026\r\n\u2026.\r\n\u2026.\r\n\r\nProcessing object type SCHEMA_EXPORT\/TABLE\/TABLE\r\nProcessing object type SCHEMA_EXPORT\/TABLE\/GRANT\/OWNER_GRANT\/OBJECT_GRANT\r\nProcessing object type SCHEMA_EXPORT\/TABLE\/COMMENT\r\nProcessing object type SCHEMA_EXPORT\/TABLE\/INDEX\/INDEX\r\nProcessing object type SCHEMA_EXPORT\/TABLE\/CONSTRAINT\/CONSTRAINT\r\nProcessing object type SCHEMA_EXPORT\/TABLE\/INDEX\/STATISTICS\/INDEX_STATISTICS\r\nProcessing object type SCHEMA_EXPORT\/VIEW\/VIEW\r\nProcessing object type SCHEMA_EXPORT\/TABLE\/CONSTRAINT\/REF_CONSTRAINT\r\nProcessing object type SCHEMA_EXPORT\/TABLE\/INDEX\/BITMAP_INDEX\/INDEX\r\nProcessing object type SCHEMA_EXPORT\/TABLE\/INDEX\/STATISTICS\/BITMAP_INDEX\/INDEX_STATISTICS\r\nProcessing object type SCHEMA_EXPORT\/TABLE\/STATISTICS\/TABLE_STATISTICS\r\nProcessing object type SCHEMA_EXPORT\/TABLE\/INDEX\/DOMAIN_INDEX\/INDEX\r\nProcessing object type SCHEMA_EXPORT\/MATERIALIZED_VIEW\r\nProcessing object type SCHEMA_EXPORT\/DIMENSION\r\nMaster table \"SYSTEM\".\"SYS_EXPORT_SCHEMA_09\" successfully loaded\/unloaded\r\n******************************************************************************\r\nDump file set for SYSTEM.SYS_EXPORT_SCHEMA_09 is:\r\n\t+DATA\/orcl\/\/expsh01.dmp\r\n\t+DATA\/orcl\/\/expsh02.dmp\r\n\t+DATA\/orcl\/\/expsh03.dmp\r\n\t+DATA\/orcl\/\/expsh04.dmp\r\n\t+DATA\/orcl\/\/expsh05.dmp\r\n\t+DATA\/orcl\/expsh06.dmp\r\n<strong>Job \"SYSTEM\".\"SYS_EXPORT_SCHEMA_09\" successfully completed at 14:50:49<\/strong><\/pre>\n<p>Thus, a Data Pump job:<\/p>\n<ul>\n<li>Can be parallelized across a subset of the available instances of a RAC database by specifying the SERVICE_NAME parameter.<\/li>\n<li>Restarted in future if any instance, where the job is running, aborts.<\/li>\n<\/ul>\n<p><strong>Note:<\/strong> Since there is some overhead involved with distributing Data Pump processes across an Oracle RAC environment, there will be cases where there is no performance benefit. In those cases, you can<\/p>\n<p>specify CLUSTER=N to force all Data Pump processes to run only on the instance where the job is started.<\/p>\n<h2>Summary:<\/h2>\n<ul>\n<li>Prior to Oracle Database 11<em>g <\/em>release 2 (11.2), if a Data Pump job is executed in parallel on an instance of a RAC database, the parallelism is employed only on that instance without utilizing the potentially valuable idle resources of other instances.<\/li>\n<li>From Oracle Database 11.2 onwards, CLUSTER parameter can be employed to distribute the worker processes across multiple Oracle RAC instances to better utilize Oracle RAC resources.<\/li>\n<li>To exercise more control over the instances on which the job should run, SERVICE_NAME parameter can be specified so that the Data Pump job runs only on those instances where the service is available.<\/li>\n<li>If any Oracle RAC instance where the job is running dies or leaves the cluster, the job aborts and can be restarted at some future time.<\/li>\n<li>For more information, Oracle have a <a href=\"http:\/\/www.oracle.com\/technetwork\/database\/datapump11gr2-twp-rac-132795.pdf\">white paper on the subject<\/a>.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Oracle Data Pump was introduced in Oracle Database 10g to enable very high-speed transfer of data and metadata between databases. A salient feature of Data Pump is that it can parallelize the export and import jobs for maximum performance. However, prior to Oracle Database 11g release 2 (11.2), if a Data Pump job is executed in parallel on an instanc&hellip;<\/p>\n","protected":false},"author":316217,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[48388,48485],"coauthors":[],"class_list":["post-73140","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","tag-data-pump","tag-rac"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73140","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/316217"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73140"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73140\/revisions"}],"predecessor-version":[{"id":91636,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73140\/revisions\/91636"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73140"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73140"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73140"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73140"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}