3 Replies Latest reply on Dec 5, 2013 2:47 AM by Robinson

    export scheduler job but chain step and chain rule failed with ORA-24150 ORA-06512 during executed sql script.

    Robinson

      Hi Folks,

       

      I used expdp utility to export all Oracle scheduler jobs and chains with below method, after that generate sql script by impdp, later on executing sql script encountered some errors.

      Only chain step and chain rule for executing script. Does anyone bright me some light? Thanks!

      My env: Oracle 11g + Oracle Linux 5.5

      My steps as below:

      1. export(expdp) oracle scheduler job(chain)

      2. generate sql script by impdp.

      3. remove orginal scheduler job(chain)

      4. execute sql script

      5. job with no chain well but job with chain failed

       

       

      
      [oracle@linux1 ~]$ expdp scott/tiger directory=db_dump_dir dumpfile=scott_job.dmp include=procobj:\" in \(select \
      > name from sys.obj$ where type\# in \(46,59,66,67,68,69,72,74,79\)\)\"  schemas=scott
      Export: Release 11.2.0.1.0 - Production on Tue Dec 3 17:42:31 2013
      Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
      Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
      With the Partitioning, Real Application Clusters, OLAP, Data Mining
      and Real Application Testing options
      Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** directory=db_dump_dir dumpfile=scott_job.dmp include=procobj:" in (select name from sys.obj$ where type# in (46,59,66,67,68,69,72,74,79))" schemas=scott 
      Estimate in progress using BLOCKS method...
      Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
      Total estimation using BLOCKS method: 0 KB
      Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
      Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
      ******************************************************************************
      Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
        /u03/database/usbo/BNR/dump/scott_job.dmp
      Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 17:42:54
      [oracle@linux1 ~]$ impdp scott/tiger sqlfile=scott_job.sql directory=db_dump_dir dumpfile=scott_job.dmp logfile=imp_scott_job.log
      Import: Release 11.2.0.1.0 - Production on Tue Dec 3 17:43:04 2013
      Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
      Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
      With the Partitioning, Real Application Clusters, OLAP, Data Mining
      and Real Application Testing options
      Master table "SCOTT"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
      Starting "SCOTT"."SYS_SQL_FILE_FULL_01":  scott/******** sqlfile=scott_job.sql directory=db_dump_dir dumpfile=scott_job.dmp logfile=imp_scott_job.log 
      Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
      Job "SCOTT"."SYS_SQL_FILE_FULL_01" successfully completed at 17:43:07
      [oracle@linux1 ~]$ more /u03/database/usbo/BNR/dump/scott_job.
      scott_job.dmp  scott_job.sql  
      [oracle@linux1 ~]$ more /u03/database/usbo/BNR/dump/scott_job.sql
      -- CONNECT SCOTT
      ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
      ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
      ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
      ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
      ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
      ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
      -- new object type path: SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
      BEGIN 
      BEGIN
      dbms_rule_imp_obj.import_rule('"SCOTT"','"CHAIN_RULE_1"','1=1',NULL, 'First link in the chain.',0,NULL);
      END; 
      COMMIT; 
      END; 
      / 
       
      BEGIN 
      BEGIN
      dbms_rule_imp_obj.import_rule('"SCOTT"','"CHAIN_RULE_2"',':"CHAIN_STEP_1".COMPLETED = ''TRUE''',NULL, 'Second link in the chain.',0,NULL);
      END; 
      COMMIT; 
      END; 
      / 
       
      BEGIN 
      BEGIN
      dbms_rule_imp_obj.import_rule('"SCOTT"','"CHAIN_RULE_3"',':"CHAIN_STEP_2".COMPLETED = ''TRUE''',NULL, 'Third link in the chain.',0,NULL);
      END; 
      COMMIT; 
      END; 
      / 
       
      BEGIN 
      BEGIN
      dbms_rule_imp_obj.import_rule('"SCOTT"','"CHAIN_RULE_4"',':"CHAIN_STEP_3".COMPLETED = ''TRUE''',NULL, 'End of the chain.',0,NULL);
      END; 
      COMMIT; 
      END; 
      / 
       
      BEGIN 
      BEGIN
      dbms_rule_imp_obj.import_rule_set('"SCHED_RULESET$1"','"SCHED_EV_CTX$1"',NULL, 0);
      END; 
      COMMIT; 
      END; 
      / 
       
      BEGIN 
      dbms_scheduler.create_program('"TEST_PROC_1"','PLSQL_BLOCK',
      'BEGIN
                               INSERT INTO tb_schduler (id, descr, cr_date)
                               VALUES (tb_schduler_seq.NEXTVAL, ''test_proc_1'', SYSDATE);
                               COMMIT;
                             END;'
      ,0, TRUE,
      'Program for first link in the chain.'
      );
      COMMIT; 
      END; 
      / 
       
      BEGIN 
      dbms_scheduler.create_program('"TEST_PROC_3"','PLSQL_BLOCK',
      'BEGIN
                               INSERT INTO tb_schduler (id, descr, cr_date)
                               VALUES (tb_schduler_seq.NEXTVAL, ''test_proc_3'', SYSDATE);
                               COMMIT;
                             END;'
      ,0, TRUE,
      'Program for last link in the chain.'
      );
      COMMIT; 
      END; 
      / 
       
      BEGIN 
      dbms_scheduler.create_program('"TEST_PROC_2"','PLSQL_BLOCK',
      'BEGIN
                               INSERT INTO tb_schduler (id, descr, cr_date)
                               VALUES (tb_schduler_seq.NEXTVAL, ''test_proc_2'', SYSDATE);
                               COMMIT;
                             END;'
      ,0, TRUE,
      'Program for second link in the chain.'
      );
      COMMIT; 
      END; 
      / 
       
      BEGIN 
      dbms_scheduler.create_chain('"TEST_CHAIN_1"', evaluation_interval=>NULL, comments=>'A test chain.'
      , rule_set_name=>'"SCHED_RULESET$1"   '
      );
      dbms_scheduler.define_chain_step('"TEST_CHAIN_1"', step_name=>'"CHAIN_STEP_1"', program_name=>'"TEST_PROC_1"');
      dbms_scheduler.define_chain_step('"TEST_CHAIN_1"', step_name=>'"CHAIN_STEP_2"', program_name=>'"TEST_PROC_2"');
      dbms_scheduler.define_chain_step('"TEST_CHAIN_1"', step_name=>'"CHAIN_STEP_3"', program_name=>'"TEST_PROC_3"');
      COMMIT; 
      END; 
      / 
       
      BEGIN 
      dbms_scheduler.create_job('"TEST_CHAIN_1_JOB"',
      job_type=>'CHAIN', job_action=>
      'test_chain_1'
      , number_of_arguments=>0,
      start_date=>TO_TIMESTAMP_TZ('03-DEC-2013 05.38.56.718161000 PM +08:00','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=> 
      'freq=minutely; interval=2'
      , end_date=>TO_TIMESTAMP_TZ('03-DEC-2013 06.08.56.000000000 PM +08:00','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'),
      job_class=>'"DEFAULT_JOB_CLASS"', enabled=>FALSE, auto_drop=>TRUE,comments=>
      NULL
      );
      COMMIT; 
      END; 
      / 
      [oracle@linux1 ~]$ export ORACLE_SID=usbo
      [oracle@linux1 ~]$ sqlplus / as sysdba
      SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 3 17:44:43 2013
      Copyright (c) 1982, 2009, Oracle.  All rights reserved.
      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
      With the Partitioning, Real Application Clusters, OLAP, Data Mining
      and Real Application Testing options
      sys@USBO> show parameter db_name
      NAME                                 TYPE                              VALUE
      ------------------------------------ --------------------------------- ------------------------------
      db_name                              string                            usbo
      sys@USBO> conn scott/tiger;
      Connected.
      --remove job and chain.
      scott@USBO> EXEC DBMS_SCHEDULER.drop_job(job_name => 'test_chain_1_job');
      EXEC DBMS_SCHEDULER.drop_chain (chain_name  => 'test_chain_1');
      EXEC DBMS_SCHEDULER.drop_program (program_name  => 'test_proc_1');
      EXEC DBMS_SCHEDULER.drop_program (program_name  => 'test_proc_2');
      EXEC DBMS_SCHEDULER.drop_program (program_name  => 'test_proc_3');
      PL/SQL procedure successfully completed.
      scott@USBO> @/u03/database/usbo/BNR/dump/scott_job.sql
      Session altered.
      Session altered.
      Session altered.
      Session altered.
      Session altered.
      Session altered.
      PL/SQL procedure successfully completed.
      PL/SQL procedure successfully completed.
      PL/SQL procedure successfully completed.
      PL/SQL procedure successfully completed.
      BEGIN
      *
      ERROR at line 1:
      ORA-24150: evaluation context SCOTT.SCHED_EV_CTX$1 does not exist
      ORA-06512: at "SYS.DBMS_RULEADM_INTERNAL", line 28
      ORA-06512: at "SYS.DBMS_RULE_IMP_OBJ", line 40
      ORA-06512: at line 3
      PL/SQL procedure successfully completed.
      PL/SQL procedure successfully completed.
      PL/SQL procedure successfully completed.
      BEGIN
      *
      ERROR at line 1:
      ORA-24141: rule set SCOTT.SCHED_RULESET$1 does not exist
      ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
      ORA-06512: at "SYS.DBMS_ISCHED", line 1694
      ORA-01403: no data found
      ORA-06512: at "SYS.DBMS_SCHEDULER", line 1638
      ORA-06512: at line 5
      PL/SQL procedure successfully completed.
      
        • 1. Re: export scheduler job but chain step and chain rule failed with ORA-24150 ORA-06512 during executed sql script.
          DK2010

          Hi,

           

           

          have you tried with to change the name of rule_set and evaluation context, You can Query using the following views

           

           

          dba_rule_sets

          dba_evaluation_contexts

          • 2. Re: export scheduler job but chain step and chain rule failed with ORA-24150 ORA-06512 during executed sql script.
            Anar Godjaev

            Hi,

             

            What is you database version?

             

            From Metalink: ORA-24150 evaluation context %s.%s does not exist (Doc ID 194600.1)

             

            Cause: The evaluation context of the given name does not exist
            Action: create the evaluation context or specify one that exists

             

             

            • 3. Re: export scheduler job but chain step and chain rule failed with ORA-24150 ORA-06512 during executed sql script.
              Robinson

              Thanks all of you!

              Hi DK2010,

               

              I took some test that the data dict(dba_rule_sets/dba_evaluation_contexts) no any data returned after I had removed the job.

              So I tried to create evaluation context and re-executed script(only exception setion.) the first error has gone. For the second still have some issue.

              ---->no any returned
              scott@USBO> select * from dba_rule_sets where rule_set_owner='SCOTT';        
              
              
              no rows selected
              
              
              scott@USBO> select * from dba_evaluation_contexts WHERE evaluation_context_owner='SCOTT';
              
              
              no rows selected
              
              
              -->add new EVALUATION CONTEXT
              scott@USBO> exec DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT('SCOTT.SCHED_EV_CTX$1');
              
              
              PL/SQL procedure successfully completed.
              
              
              --->now it looks fine
              scott@USBO> BEGIN 
                2  BEGIN
                3  dbms_rule_imp_obj.import_rule_set('"SCHED_RULESET$1"','"SCHED_EV_CTX$1"',NULL, 0);
                4  END; 
                5  
                6  COMMIT; 
                7  END; 
                8  / 
              
              
              PL/SQL procedure successfully completed.
              
              
              --->add new rule set, it prompt aleady exists
              scott@USBO> exec DBMS_RULE_ADM.CREATE_RULE_SET('SCOTT.SCHED_RULESET$1')  
              BEGIN DBMS_RULE_ADM.CREATE_RULE_SET('SCOTT.SCHED_RULESET$1'); END;
              
              
              *
              ERROR at line 1:
              ORA-24153: rule set SCOTT.SCHED_RULESET$1 already exists
              ORA-06512: at "SYS.DBMS_RULEADM_INTERNAL", line 28
              ORA-06512: at "SYS.DBMS_RULE_ADM", line 138
              ORA-06512: at line 1
              
              
              -->chain rule still could not find.
              scott@USBO> @job_chain_rules.sql
              
              
              no rows selected
              
              
              -->rerun 
              scott@USBO> BEGIN 
                2  dbms_scheduler.create_chain('"TEST_CHAIN_1"', evaluation_interval=>NULL, comments=>'A test chain.'
                3  , rule_set_name=>'"SCHED_RULESET$1"   '
                4  );
                5  dbms_scheduler.define_chain_step('"TEST_CHAIN_1"', step_name=>'"CHAIN_STEP_1"', program_name=>'"TEST_PROC_1"');
                6  dbms_scheduler.define_chain_step('"TEST_CHAIN_1"', step_name=>'"CHAIN_STEP_2"', program_name=>'"TEST_PROC_2"');
                7  dbms_scheduler.define_chain_step('"TEST_CHAIN_1"', step_name=>'"CHAIN_STEP_3"', program_name=>'"TEST_PROC_3"');
                8  COMMIT; 
                9  END; 
               10  / 
              BEGIN
              *
              ERROR at line 1:
              ORA-27477: "SCOTT.TEST_CHAIN_1" already exists  
              ORA-06512: at "SYS.DBMS_ISCHED", line 1148
              ORA-06512: at "SYS.DBMS_SCHEDULER", line 1598
              ORA-06512: at line 2
              
              
              -->drop chain
              scott@USBO> exec dbms_scheduler.drop_chain('TEST_CHAIN_1');
              BEGIN dbms_scheduler.drop_chain('TEST_CHAIN_1'); END;
              
              
              *
              ERROR at line 1:
              ORA-27479: Cannot drop "SCOTT.TEST_CHAIN_1" because other objects depend on it
              ORA-06512: at "SYS.DBMS_ISCHED", line 1319
              ORA-06512: at "SYS.DBMS_ISCHED", line 1222
              ORA-06512: at "SYS.DBMS_SCHEDULER", line 1854
              ORA-06512: at line 1
              
              
              scott@USBO> exec dbms_scheduler.drop_chain('TEST_CHAIN_1',force=>TRUE);
              
              
              PL/SQL procedure successfully completed.
              
              
              scott@USBO> BEGIN 
                2  dbms_scheduler.create_chain('"TEST_CHAIN_1"', evaluation_interval=>NULL, comments=>'A test chain.'
                3  , rule_set_name=>'"SCHED_RULESET$1"   '
                4  );
                5  dbms_scheduler.define_chain_step('"TEST_CHAIN_1"', step_name=>'"CHAIN_STEP_1"', program_name=>'"TEST_PROC_1"');
                6  dbms_scheduler.define_chain_step('"TEST_CHAIN_1"', step_name=>'"CHAIN_STEP_2"', program_name=>'"TEST_PROC_2"');
                7  dbms_scheduler.define_chain_step('"TEST_CHAIN_1"', step_name=>'"CHAIN_STEP_3"', program_name=>'"TEST_PROC_3"');
                8  COMMIT; 
                9  END; 
               10  / 
              BEGIN
              *
              ERROR at line 1:
              ORA-24141: rule set SCOTT.SCHED_RULESET$1 does not exist   --->still returned no rule set
              ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
              ORA-06512: at "SYS.DBMS_ISCHED", line 1694
              ORA-01403: no data found
              ORA-06512: at "SYS.DBMS_SCHEDULER", line 1638
              ORA-06512: at line 5
              
              
              scott@USBO> exec DBMS_RULE_ADM.CREATE_RULE_SET('SCOTT.SCHED_RULESET$1')
              
              
              PL/SQL procedure successfully completed.
              
              
              scott@USBO> BEGIN 
                2  dbms_scheduler.create_chain('"TEST_CHAIN_1"', evaluation_interval=>NULL, comments=>'A test chain.'
                3  , rule_set_name=>'"SCHED_RULESET$1"   '
                4  );
                5  dbms_scheduler.define_chain_step('"TEST_CHAIN_1"', step_name=>'"CHAIN_STEP_1"', program_name=>'"TEST_PROC_1"');
                6  dbms_scheduler.define_chain_step('"TEST_CHAIN_1"', step_name=>'"CHAIN_STEP_2"', program_name=>'"TEST_PROC_2"');
                7  dbms_scheduler.define_chain_step('"TEST_CHAIN_1"', step_name=>'"CHAIN_STEP_3"', program_name=>'"TEST_PROC_3"');
                8  COMMIT; 
                9  END; 
               10  / 
              BEGIN
              *
              ERROR at line 1:
              ORA-27477: "SCOTT.TEST_CHAIN_1" already exists
              ORA-06512: at "SYS.DBMS_ISCHED", line 1148
              ORA-06512: at "SYS.DBMS_SCHEDULER", line 1598
              ORA-06512: at line 2
              
              
              
              
              scott@USBO> exec dbms_scheduler.drop_chain('TEST_CHAIN_1',force=>TRUE);
              
              
              PL/SQL procedure successfully completed.
              
              
              scott@USBO> BEGIN 
                2  dbms_scheduler.create_chain('"TEST_CHAIN_1"', evaluation_interval=>NULL, comments=>'A test chain.'
                3  , rule_set_name=>'"SCHED_RULESET$1"   '
                4  );
                5  dbms_scheduler.define_chain_step('"TEST_CHAIN_1"', step_name=>'"CHAIN_STEP_1"', program_name=>'"TEST_PROC_1"');
                6  dbms_scheduler.define_chain_step('"TEST_CHAIN_1"', step_name=>'"CHAIN_STEP_2"', program_name=>'"TEST_PROC_2"');
                7  dbms_scheduler.define_chain_step('"TEST_CHAIN_1"', step_name=>'"CHAIN_STEP_3"', program_name=>'"TEST_PROC_3"');
                8  COMMIT; 
                9  END; 
               10  / 
              BEGIN
              *
              ERROR at line 1:
              ORA-24141: rule set SCOTT.SCHED_RULESET$1 does not exist
              ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
              ORA-06512: at "SYS.DBMS_ISCHED", line 1694
              ORA-01403: no data found
              ORA-06512: at "SYS.DBMS_SCHEDULER", line 1638
              ORA-06512: at line 5
              

               

              Would you like to give me more clue?

               

              Thanks again.