This discussion is archived
4 Replies Latest reply: Dec 21, 2012 5:00 AM by Srini Chavali-Oracle RSS

Expdp Scheduler objects Open mode Redo logs

RRSOra? Newbie
Currently Being Moderated
Hi,

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
  • 1. Re: Expdp Scheduler objects Open mode Redo logs
    spajdy Pro
    Currently Being Moderated
    ad i/ there is no advanced option. expdp (data pump) is "new verison" of exp. Exp doesn't care about scheduler object so it don't export then. Expdp does.
    ad ii/ no, restricted mode only restrict login to DB to user who have granted system privilege RESTRICTED SESSION and prevent job from runnig

    yes when you call dbms_scheduler procedures they do commit inside. Whan do you mead read-only mode ? You need DB in open mode to perform export.
  • 2. Re: Expdp Scheduler objects Open mode Redo logs
    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.
  • 3. Re: Expdp Scheduler objects Open mode Redo logs
    spajdy Pro
    Currently Being Moderated
    When you creating job every second, then you don't need export them.
    So add EXCLUDE to par file.
    exclude=RULE SET,RULE,JOB,PROGRAM,JOB CLASS,WINDOW,WINDOW GROUP, SCHEDULE, CHAIN
    this exclude all Scheduler objects

Legend

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