19 | 03 | 2024
Latest Articles
Popular Articles

Administration

Datapump feature : Attach to and Manage a running Datapump job

User Rating:  / 3
PoorBest 

Datapump feature : Attach to and Manage a running Datapump job

 

Datapump

Datapump : Enhanced granularity with "EXCLUDE" and "INCLUDE"

Datapump Consistent

Datapump new features in Oracle 11G



In session 1 we invoke the Datapump export

[oracle@pcguy ~]$ expdp system directory=backup dumpfile=expdp_SILVER.DMP full=y logfile=expdp_SILVER.log

Export: Release 11.2.0.2.0 - Production on Tue May 3 14:22:08 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** directory=backup dumpfile=expdp_SILVER.DMP full=y logfile=expdp_SILVER.log
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 482.3 MB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/CONTEXT
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA

In session 2 we stop the job.


[oracle@pcguy ~]$ expdp system attach=SYS_EXPORT_FULL_01

Export: Release 11.2.0.2.0 - Production on Tue May 3 14:23:12 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: SYS_EXPORT_FULL_01
Owner: SYSTEM                         
Operation: EXPORT                         
Creator Privs: TRUE                           
GUID: A25F3284E270301EE040007F01001CB8
Start Time: Tuesday, 03 May, 2011 14:22:48
Mode: FULL                           
Instance: SILVER
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name      Parameter Value:
CLIENT_COMMAND        system/******** directory=backup dumpfile=expdp_SILVER.DMP full=y logfile=expdp_SILVER.log
State: EXECUTING                      
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /mnt/work/Data/Backup/expdp_SILVER.DMP
bytes written: 4,096

Worker 1 Status:
Process Name: DW00
State: EXECUTING                      
Object Schema: USER_ADMIN
Object Type: DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Completed Objects: 11
Total Objects: 11
Worker Parallelism: 1

Export> help  
------------------------------------------------------------------------------

The following commands are valid while in interactive mode.
Note: abbreviations are allowed.

ADD_FILE
Add dumpfile to dumpfile set.

CONTINUE_CLIENT
Return to logging mode. Job will be restarted if idle.

EXIT_CLIENT
Quit client session and leave job running.

FILESIZE
Default filesize (bytes) for subsequent ADD_FILE commands.

HELP
Summarize interactive commands.

KILL_JOB
Detach and delete job.

PARALLEL
Change the number of active workers for current job.

REUSE_DUMPFILES
Overwrite destination dump file if it exists [N].

START_JOB
Start or resume current job.
Valid keyword values are: SKIP_CURRENT.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

STOP_JOB
Orderly shutdown of job execution and exits the client.
Valid keyword values are: IMMEDIATE.


Export> stop_job
Are you sure you wish to stop this job ([yes]/no): yes

 


Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Job "SYSTEM"."SYS_EXPORT_FULL_01" stopped by user request at 14:23:58





in session 2 we increase parallelism and start the job again


[oracle@pcguy ~]$ expdp system attach=SYS_EXPORT_FULL_01

Export: Release 11.2.0.2.0 - Production on Tue May 3 14:25:56 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: SYS_EXPORT_FULL_01
Owner: SYSTEM                         
Operation: EXPORT                         
Creator Privs: TRUE                           
GUID: A25F3284E270301EE040007F01001CB8
Start Time: Tuesday, 03 May, 2011 14:26:00
Mode: FULL                           
Instance: SILVER
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name      Parameter Value:
CLIENT_COMMAND        system/******** directory=backup dumpfile=expdp_SILVER.DMP full=y logfile=expdp_SILVER.log
State: IDLING                         
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /mnt/work/Data/Backup/expdp_SILVER.DMP
bytes written: 868,352

Worker 1 Status:
Process Name: DW00
State: UNDEFINED                      

Export> parallel=4

Export> start_job

Export> status

Job: SYS_EXPORT_FULL_01
Operation: EXPORT                         
Mode: FULL                           
State: EXECUTING                      
Bytes Processed: 142,878,192
Percent Done: 32
Current Parallelism: 4
Job Error Count: 0
Dump File: /mnt/work/Data/Backup/expdp_SILVER.DMP
bytes written: 143,753,216

Worker 1 Status:
Process Name: DW00
State: EXECUTING                      

Worker 2 Status:
Process Name: DW01
State: EXECUTING                      
Object Schema: APEX_030200
Object Name: WWV_FLOW_STEP_ITEMS
Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Completed Objects: 2
Total Objects: 1,185
Worker Parallelism: 1

Worker 3 Status:
Process Name: DW02
State: EXECUTING                      
Object Schema: TUNING
Object Name: T_ORDER_OR
Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 1,185
Worker Parallelism: 1

Worker 4 Status:
Process Name: DW03
State: EXECUTING                      
Object Schema: APEX_030200
Object Name: WWV_FLOW_PAGE_PLUGS
Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 1,185
Worker Parallelism: 1