This discussion is archived
8 Replies Latest reply: Dec 24, 2012 1:08 AM by RRSOra? RSS

Oracle 10g R2 Export DBMS_SCHEDULER

RRSOra? Newbie
Currently Being Moderated
Hi,

I am using DBMS_SCHEDULER based on database multi-threading concept. So Scheduler objects like JOBS, CHAINS, RULES, RULESETS and PROGRAMS are created every second. My DBA is facing issues while exporting the database. I found the following information related to EXPORT/IMPORT and DBMS_SCHEDULER.

Import/Export and the Scheduler+
You must use the Data Pump utilities (impdp and expdp) to export Scheduler objects. You cannot use the earlier import/export utilities with the Scheduler. Also, Scheduler objects cannot be exported while the database is in read-only mode.+
An export generates the DDL that was used to create the Scheduler objects. All attributes are exported. When an import is done, all the database objects are recreated in the new database. All schedules are stored with their time zones, which are maintained in the new database. For example, schedule "Monday at 1 PM PST in a database in San Francisco" would be the same if it was exported and imported to a database in Germany+

Can you elaborate on this and give me some idea as to what are the specific steps needed to Export a Database that has scheduler objects created almost every second? I am using Oracle 10g R2.
  • 1. Re: Oracle 10g R2 Export DBMS_SCHEDULER
    Niket Kumar Pro
    Currently Being Moderated
    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).

     Also, Scheduler objects cannot be exported while the database is in read-only mode 
    you database must be in open mode to take export of Scheduler objects.
  • 2. Re: Oracle 10g R2 Export DBMS_SCHEDULER
    RRSOra? Newbie
    Currently Being Moderated
    Hi,

    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.

    Many thanks

    Edited by: 920445 on Dec 19, 2012 3:33 AM
  • 3. Re: Oracle 10g R2 Export DBMS_SCHEDULER
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    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=

    Regards,
    Harry
  • 4. Re: Oracle 10g R2 Export DBMS_SCHEDULER Quiesced
    RRSOra? Newbie
    Currently Being Moderated
    Hi,

    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?

    Many thanks,

    Edited by: RRSOra? on Dec 20, 2012 1:40 AM
  • 5. Re: Oracle 10g R2 Export DBMS_SCHEDULER Quiesced
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    Datapump has to actually create a table as one of the first tasks it does (to track its work) - if the database is in a state where this can't happen (i.e. quiesced, read only) then datapump cannot work.

    Regards,
    Harry
  • 6. Re: Oracle 10g R2 Export DBMS_SCHEDULER Quiesced
    RRSOra? Newbie
    Currently Being Moderated
    Hi,

    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?

    i)Expdp parfile=expdp_xx.par

    Where expdp_xx.par contains
    Directory=directory_name
    Dumpfile=expdp_xx.dmp
    Logfile=expdp_xx.log
    Schemas=SCHEMA_NAME
    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
  • 7. Re: Oracle 10g R2 Export DBMS_SCHEDULER Quiesced
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    You could try something like this....

    https://blogs.oracle.com/UPGRADE/entry/exclude_dbms_scheduler_jobs_from

    so end up with

    exclude=procobj:"IN (SELECT NAME FROM sys.OBJ$ WHERE TYPE# IN (46,59))"

    perhaps?

    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
    WHERE s.obj#=o.obj#
    and bitand(s.xpflags, 8388608) = 8388608),
    'MATERIALIZED VIEW'),
    43, 'DIMENSION',
    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',
    'UNDEFINED'),

    Cheers,
    Harry
  • 8. Re: Oracle 10g R2 Export DBMS_SCHEDULER Quiesced
    RRSOra? Newbie
    Currently Being Moderated
    Hi,

    Is your EXCLUDE clause a metadata filter? If so is excluding anything from export a bad practice? Kindly give me more information in terms of the pros and cons of using EXCLUDE clause. How relevant is it with respect to Oracle 11g and 12c?

    Many thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points