This content has been marked as final. Show 8 replies
you need to use expdp and Impdp export utilities which are like exp/imp but have more advance options and also now expdp/impdp are used more then exp/imp.(do almost same work export database objects).
you database must be in open mode to take export of Scheduler objects.
Also, Scheduler objects cannot be exported while the database is in read-only mode
Thanks for replying. However your reply has raised 3 more questions.
i) What are the advanced options of expdp that permit exporting database scheduler objects and prevent exp from exporting database scheduler objects?
ii)If the database has to be in Open mode, does RESTRICTED or NON-RESTRICTED mode have any significance here?
AskTom says that DBMS_SCHEDULER commits when scheduling a job. Hence I guess redo logs are generated each time a job is scheduled using DBMS_SCHEDULER. Hence I guess export cannot be done in read-only mode.
Edited by: 920445 on Dec 19, 2012 3:33 AM
DBMS_SCHEDULER came it at oracle 10g the same time as expdp. exp is only maintained for compatibility purposes so they didnt add in new functionality to be able to extract new style scheduler jobs.
restricted/non restricted shouldnt matter to extract the jobs.
What is the actual error the DBA is getting doing the expdp? Is the issue that jobs are being changed during the expdp and oracle doesn;t like it (i know you get errors if tables are changed during the expdp process).
Do you actually need the jobs in the expdp? they can easily be excluded using exclude=
I will get back to you with more information. In my implementation, I am trying to check multi-threading concept in Oracle Database. Since this is the first time we are using DBMS_SCHEDULER, we have not used EVENTS and Advanced Queuing. As part of my implementation, I am generating JOBS, PROGRAMS and CHAINS every second to check whether a particular event has occured (An insertion into a table). From the DBA report, it seems this is hampering the export process. I will get back to you with more information as to whether DBA is using EXPDP in Open mode and if so why can't he exclude the DBA_SCHEDULER* tables while doing the export.
In the meantime, can you kindly indicate to me if DBA can export and backup using DATAPUMP utility in Quiesced state?
Edited by: RRSOra? on Dec 20, 2012 1:40 AM
Here is the scenario mentioned by my DBA. We are using Oracle 10g R2. Maybe we will upgrade to Oracle 11g. My implementation in Oracle 10g is based on DBMS_SCHEDULER.
i)Are you always using Data Pump Utility for Export? Kindly provide me the exact command or statement used if you are executing from command-line. Do you specify EXCLUDE parameter if you want to exclude any of the tables or objects? Do you use MetaData filters available in Data Pump?
ii)Regarding the database mode during export or backup, do you use read-only mode or open mode restricted or quiesced mode?
iii)Kindly specify the exact error message displayed during export.
iv)Based on the database mode used and the error displayed, is it related to redo logs or changes in tables during export using expdp?
Where expdp_xx.par contains
parallel=4 <- we tried with or without this parameter as well.
ii) Database is running in regular open mode (open for business for all users) while we are running the exp or expdp process.
iii). No error displayed. It just keep running with no end in site.
iv). No.. Oracle tracing saw a repeated sql running on the a table created by expdp process for RULE/RULE SET Objects.
Kindly suggest the steps required to handle the creation of RULE/RULE SET Objects during expdp process. These RULE/RULE SET Objects are created during the creation of CHAIN_RULE objects. My implementation is verifying multi-threading in DBMS_SCHEDULER. We want to check the basic functionalities provided by DBMS_SCHEDULER first. Hence EVENT_CONDITION and QUEUE_SPEC are ruled out. Instead of EVENT_CONDITION, we are using condition attribute of DEFINE_CHAIN_STEP. So jobs are created every second verifying whether the condition attribute of DEFINE_CHAIN_STEP is satisfied. The jobs create chains and hence rules and rule set objects are created.
Given the above scenario, kindly indicate to me how to complete expdp process and how to avoid the RULE/RULE SET Objects creation hampering the expdp process.
Edited by: RRSOra? on Dec 21, 2012 1:13 AM
You could try something like this....
so end up with
exclude=procobj:"IN (SELECT NAME FROM sys.OBJ$ WHERE TYPE# IN (46,59))"
you can find the value of the type# value from the definition of DBA_OBJECTS - I pasted part of that below:
(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
32, 'INDEXTYPE', 33, 'OPERATOR',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
42, NVL((SELECT 'REWRITE EQUIVALENCE'
FROM sum$ s
and bitand(s.xpflags, 8388608) = 8388608),
44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION', 52, 'LOCATION',
55, 'XML SCHEMA', 56, 'JAVA DATA',
57, 'EDITION', 59, 'RULE',
60, 'CAPTURE', 61, 'APPLY',
62, 'EVALUATION CONTEXT',
66, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS', 69, 'WINDOW',
72, 'SCHEDULER GROUP', 74, 'SCHEDULE', 79, 'CHAIN',
81, 'FILE GROUP', 82, 'MINING MODEL', 87, 'ASSEMBLY',
90, 'CREDENTIAL', 92, 'CUBE DIMENSION', 93, 'CUBE',
94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS',
100, 'FILE WATCHER', 101, 'DESTINATION',