Home » RDBMS Server » Server Utilities » Excluding jobs/ scheduler jobs /dblinks from impdp (Oracle all versions)
icon14.gif  Excluding jobs/ scheduler jobs /dblinks from impdp [message #649249] Sat, 19 March 2016 03:25 Go to next message
satya405
Messages: 4
Registered: March 2016
Location: Hyderabad
Junior Member
As an APPDBA, we use our admin account for performing datapump operations . Jobs are getting imported while performing impdp in prod to prod environments and so we want to exclude them in the impdp parfile.

For db links and jobs we can use exclude=job but still the scheduler jobs are getting imported everytime. I googled about that and all I can find is we should add exclude=procobj in the par file. If we add that in par file, it is excluding ALL PL/SQL related.

Is there a way to import only jobs, scheduler jobs and db links if needed after schema (both data and metadata) import?

Note : we don't have SYS privileges to perform any SYS related operations. We need to use either our admin account or schema owner account or datapump only for any operations.

This is for exp/imp from one oracle environment to another oracle environment regardless of version.
Re: Excluding jobs/ scheduler jobs /dblinks from impdp [message #649256 is a reply to message #649249] Sat, 19 March 2016 12:19 Go to previous messageGo to next message
BlackSwan
Messages: 26585
Registered: January 2009
Location: SoCal
Senior Member
>Jobs are getting imported while performing impdp
I am confused since above contradicts what is stated below.
>This is for exp/imp from one oracle environment to another oracle environment regardless of version.

expdp & impdp are different utilities from exp & imp utilities.

It would be helpful if you posted the actual & complete command that produced the export file you are trying to import.
Re: Excluding jobs/ scheduler jobs /dblinks from impdp [message #649258 is a reply to message #649256] Sat, 19 March 2016 21:56 Go to previous messageGo to next message
satya405
Messages: 4
Registered: March 2016
Location: Hyderabad
Junior Member
I mean to say expdp and impdp only. Not the traditional exp/imp.

EXPDP PARFILE :

userid=account@DB --- our admin account@Source Database
schemas=xx
directory=xx
logfile=xx_expdp.log
job_name=expdp_xx
flashback_scn=xxxx
parallel=4
dumpfile=x.dmp
exclude=AUDIT_OBJ
exclude=TABLE/AUDIT_OBJ
exclude=SCHEMA_EXPORT/TABLE/AUDIT_OBJ
content=all

Impdp parfile :

userid=account@DB -- our admin account@Target Database
schemas=xx
directory=xx
logfile=xx_impdp.log
job_name=impdp_xx
exclude=db_link
exclude=job
dumpfile=x.dmp
parallel=4

For excluding db links and jobs, we use exclude=job and exclude=db_link. But scheduler jobs are getting imported even though we exclude job.
I googled about that and all I can find is we should add exclude=procobj in the par file. If we add that in par file, it is excluding ALL PL/SQL related.

so my question is - After performing the impdp using the above parfile, if db_links,jobs and scheduler jobs are needed in target prod environment , Is there a way to import only jobs, scheduler jobs and db links later??

Note: I am not asking about a particular trial, I just posted a sample par file we use to explain the situation better. Please advise what should be the best procedure to follow in a general scenario for importing only jobs, scheduler jobs and db links using datapump.

[Updated on: Sat, 19 March 2016 22:11]

Report message to a moderator

Re: Excluding jobs/ scheduler jobs /dblinks from impdp [message #649261 is a reply to message #649249] Sun, 20 March 2016 04:10 Go to previous messageGo to next message
Michel Cadot
Messages: 66515
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Interesting problem, here's a solution:
SQL> drop user test cascade;

User dropped.

SQL> grant create session, create procedure, create job to test identified by test;

Grant succeeded.

SQL> grant read, write on directory DATA_PUMP_DIR to test;

Grant succeeded.

SQL> connect test/test
Connected.
TEST> create or replace procedure p is begin null; end;
  2  /

Procedure created.

TEST> begin
  2    dbms_scheduler.create_job (
  3      job_name        => 'test_p',
  4      job_type        => 'PLSQL_BLOCK',
  5      job_action      => 'BEGIN P; END;',
  6      start_date      => SYSTIMESTAMP,
  7      repeat_interval => 'freq=hourly; byminute=0',
  8      end_date        => NULL,
  9      enabled         => TRUE);
 10  end;
 11  /

PL/SQL procedure successfully completed.

TEST> commit;

Commit complete.

TEST> col object_name format a20
TEST> select object_name, object_type from user_objects;
OBJECT_NAME          OBJECT_TYPE
-------------------- -------------------
P                    PROCEDURE
TEST_P               JOB

2 rows selected.

TEST> @c
Connected.
SQL> host expdp michel/michel schemas=test exclude="PROCOBJ:\"IN (SELECT NAME FROM sys.OBJ$ WHERE TYPE# IN (66,67,68,69,72,74,79))"\"

Export: Release 11.2.0.4.0 - Production on Dim. Mars 20 10:03:43 2016

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "MICHEL"."SYS_EXPORT_SCHEMA_01":  michel/******** schemas=test exclude=PROCOBJ:"IN (SELECT NAME FROM sys.OBJ$ WHERE TYPE# IN (66,67,68,69,72,74,79))"
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/AUDIT_OBJ
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Master table "MICHEL"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MICHEL.SYS_EXPORT_SCHEMA_01 is:
  E:\ORACLE\ADMIN\MIKB2\DPDUMP\EXPDAT.DMP
Job "MICHEL"."SYS_EXPORT_SCHEMA_01" successfully completed at Dim. Mars 20 10:04:21 2016 elapsed 0 00:00:36

SQL> drop user test cascade;

User dropped.

SQL> host impdp michel/michel schemas=test

Import: Release 11.2.0.4.0 - Production on Dim. Mars 20 10:05:01 2016

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "MICHEL"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "MICHEL"."SYS_IMPORT_SCHEMA_01":  michel/******** schemas=test
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/AUDIT_OBJ
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Job "MICHEL"."SYS_IMPORT_SCHEMA_01" successfully completed at Dim. Mars 20 10:05:05 2016 elapsed 0 00:00:03

SQL> select object_name, object_type from dba_objects where owner='TEST';
OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
P                              PROCEDURE

1 row selected.

Type#: 66=JOB, 67=PROGRAM, 68=JOB CLASS, 69=WINDOW, 72=WINDOW SCHEDULER GROUP, 74=SCHEDULE, 79=CHAIN
Maybe I missed some types, further tests are needed, tell us if you find some.
Note I had to do the exclusion on export as this does not work on import, at least in my version, I don't know why.
Re: Excluding jobs/ scheduler jobs /dblinks from impdp [message #649265 is a reply to message #649261] Sun, 20 March 2016 22:32 Go to previous messageGo to next message
satya405
Messages: 4
Registered: March 2016
Location: Hyderabad
Junior Member
Thanks Michel.

Looks like it is a bug and I found something useful in MYORACLE SUPPORT .

Reference : How To Exclude Scheduler Job From Data Pump Export (expdp) (Doc ID 1414478.1)



[Edit MC: Remove MOS note content which is copyrighted materials]

[Updated on: Mon, 21 March 2016 04:10] by Moderator

Report message to a moderator

Re: Excluding jobs/ scheduler jobs /dblinks from impdp [message #649266 is a reply to message #649265] Sun, 20 March 2016 22:45 Go to previous messageGo to next message
BlackSwan
Messages: 26585
Registered: January 2009
Location: SoCal
Senior Member
Alternatively you could try DBMS_METADATA.GET_DDL instead of expdp.
Re: Excluding jobs/ scheduler jobs /dblinks from impdp [message #649267 is a reply to message #649266] Sun, 20 March 2016 22:56 Go to previous messageGo to next message
satya405
Messages: 4
Registered: March 2016
Location: Hyderabad
Junior Member
Our target is to exclude them from import. So the approach advised by Michel and as per the doc, I've tested and it worked.

>Alternatively you could try DBMS_METADATA.GET_DDL instead of expdp.

You mean to say - If they're needed in target, later we can use DBMS_METADATA.GET_DDL for creating them manually right?
Re: Excluding jobs/ scheduler jobs /dblinks from impdp [message #649274 is a reply to message #649265] Mon, 21 March 2016 01:16 Go to previous message
Michel Cadot
Messages: 66515
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I should think to search on MOS this will prevent me from spending time working on it. Smile

I don't know what type#=71 refer to.
Is there someone knowing which object is it?

Previous Topic: Load a CSV file into a GTT from a PL/SQL procedure
Next Topic: Gathering Statastics after Data Import
Goto Forum:
  


Current Time: Tue Aug 20 06:42:53 CDT 2019