Hello Everyone,
In this article , we will learn how to Attach and de-attach the expdp/impdp datapump jobs.
EXPDP and IMPDP is used to take the logical backup of full database, tablespace ,schema,table and query level.
we can attach or deattach the datapump jobs using job names.
In the first step we will start full database expdp job using below parameters
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
[oracle@oracle pump]$ expdp directory=pump dumpfile=TB_users.dmp logfile=TB_users.log full=y job_name=FULL_ATGCORE parallel=10 reuse_dumpfiles=y Export: Release 12.1.0.2.0 - Production on Thu Jan 21 10:05:09 2021 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Starting "SYS"."FULL_ATGCORE": /******** AS SYSDBA directory=pump dumpfile=TB_users.dmp logfile=TB_users.log full=y job_name=FULL_ATGCORE parallel=10 reuse_dumpfiles=y Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 301 MB . . exported "PM"."ONLINE_MEDIA" 7.754 MB 9 rows . . exported "SH"."CUSTOMERS" 10.27 MB 55500 rows . . exported "SH"."COSTS":"COSTS_Q1_1999" 183.7 KB 5884 rows . . exported "SH"."COSTS":"COSTS_Q1_1998" 139.6 KB 4411 rows . . exported "SH"."COSTS":"COSTS_Q1_2000" 120.7 KB 3772 rows . . exported "SH"."COSTS":"COSTS_Q2_1998" 79.68 KB 2397 rows . . exported "SH"."COSTS":"COSTS_Q1_2001" 228.0 KB 7328 rows . . exported "SH"."COSTS":"COSTS_Q2_1999" 132.7 KB 4179 rows . . exported "SH"."COSTS":"COSTS_Q2_2000" 119.1 KB 3715 rows . . exported "SH"."COSTS":"COSTS_Q2_2001" 184.7 KB 5882 rows . . exported "SH"."COSTS":"COSTS_Q3_1998" 131.3 KB 4129 rows . . exported "SH"."COSTS":"COSTS_Q3_1999" 137.5 KB 4336 rows . . exported "SH"."COSTS":"COSTS_Q3_2000" 151.6 KB 4798 rows . . exported "SH"."COSTS":"COSTS_Q3_2001" 234.6 KB 7545 rows . . exported "SH"."COSTS":"COSTS_Q4_1998" 144.8 KB 4577 rows . . exported "SH"."COSTS":"COSTS_Q4_1999" 159.2 KB 5060 rows . . exported "SH"."COSTS":"COSTS_Q4_2000" 160.4 KB 5088 rows |
How to check the status of Datapump job :
While expdp job running fine , we can check status of datapump job using below statement.
|
1 2 3 4 5 6 |
SQL> SELECT owner_name,job_name,operation,job_mode,state,degree,attached_sessions FROM dba_datapump_jobs ORDER BY owner_name,job_name; Owner Name Job Name Operation Job Mode State Degree Attached Sessions ------------ ------------ --------- ---------- ---------- ---------------- --------- SYS FULL_ATGCORE EXPORT FULL EXECUTING 10 1 |
Now we can Pause our job by using CTRL+C, then it will prompt EXPORT>>
|
1 2 3 4 |
. . exported "SH"."SALES":"SALES_Q4_2003" 0 KB 0 rows Processing object type TABLE_EXPORT/TABLE/TABLE ^C EXPORT> |
How to Stop a Datapump job :
We can stop the datapump job using STOP_JOB or STOP_JOB=immediate
|
1 2 3 4 |
Export> stop_job=immediate Are you sure you wish to stop this job ([yes]/no): yes [oracle@oracle pump]$ |
If we stop a datapump job the status of the job is STOP PENDING –> STOPPING –> NOT RUNNING
How to Attach a stopped Datapump job :
We can attach datapump stopped jobs using ATTACH parameter.
|
1 2 3 4 5 6 7 |
[oracle@oracle ~]$ expdp attach=FULL_ATGCORE Export: Release 12.1.0.2.0 - Production on Thu Jan 21 13:11:11 2021 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options bytes written: 19,857,408 |
Once we give attach with user credentials , datapump will display all details regarding our job.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
Job: FULL_ATGCORE Owner: SYS Operation: EXPORT Creator Privs: TRUE GUID: B96DFBA782BA95F8E0533200A8C0F4C7 Start Time: Thursday, 21 January, 2021 13:11:16 Mode: FULL Instance: atgcore Max Parallelism: 10 Timezone: +00:00 Timezone version: 18 Endianness: LITTLE NLS character set: WE8MSWIN1252 NLS NCHAR character set: AL16UTF16 EXPORT Job Parameters: Parameter Name Parameter Value: CLIENT_COMMAND /******** AS SYSDBA directory=pump dumpfile=full_01.dmp logfile=full_01.log full=y job_name=full_atgcore parallel=10 reuse_dumpfiles=y State: IDLING Bytes Processed: 19,807,880 Percent Done: 22 Current Parallelism: 10 Job Error Count: 0 Dump File: /u01/oradata/atgcore/pump/full_01.dmp bytes written: 19,857,408 |
To start a job we can use a parameter START_JOB
|
1 |
Export> start_job |
Once we start the job the status of the job will become IDLEING –> EXECUTING
|
1 2 3 4 5 6 7 8 9 |
SELECT owner_name,job_name,operation,job_mode,state,degree,attached_sessions FROM dba_datapump_jobs ORDER BY owner_name,job_name; Owner Name Job Name Operation Job Mode State Degree Attached Sessions ------------------------- -------------------- ---------- ---------- --------------- ------- ----------------- SYS FULL_ATGCORE EXPORT FULL IDLING 10 1 SQL> / Owner Name Job Name Operation Job Mode State Degree Attached Sessions ------------------------- -------------------- ---------- ---------- --------------- ------- ----------------- SYS FULL_ATGCORE EXPORT FULL EXECUTING 10 1 |
Thank you for giving your valuable time to read the above information.
Follow us on
Website : www.ktexperts.com
Facebook Page : KTexperts
Linkedin Page : KT EXPERTS
Loading...
