1 2 Previous Next 17 Replies Latest reply: Feb 6, 2013 5:59 AM by 989463 RSS

    DBMS scheduler

    846231
      Hi All,

      Ora 11.2.0.1

      I have a batch job that drops and recreates all db_links in our monitoring server.

      We have 1 monitoring server(windows) with 1 database. This has db_blinks to all 50+ databases located on other machines, used for development purposes.
      Once the DEV project is completed the counterpart database is dropped and replaced by new DEV projects db. So the monitoring db needs to be refreshed periodically
      its dblinks. Hence I have this batch program that drops and recreates everyday at 12 midnight . The monitoring server is windows so I used the windows scheduled task and .bat file.


      I have there RefreshDblink.bat file
      =====================
      REM *****This program automates DbLink refresh.
      REM =============================
      
      set oracle_home=d:\oracle\product\11.2.0\db_1
      set path=%oracle_home%\bin;%path%
      set oracle_sid=mdcapex
      
      sqlplus mdc_sc/mdc_sc$  @spoolDrop.sql
      sqlplus mdc_sc/mdc_sc$  @drop.sql
      sqlplus mdc_sc/mdc_sc$  @spoolCreate.sql
      sqlplus mdc_sc/mdc_sc$  @create.sql
      
      REM ***** end of program *******
      1. spoolDrop.sql - this creates a sql script that drop the db links which is executed by the drop.sql
      set echo off
      set heading off
      set feedback off
      set verify off
      set term off
      set pages 0
      spool d:\batch\drop.sql
      prompt spool d:\batch\drop_db_link.lst
      prompt set echo on 
      select 'drop database link '||db_link||';' from user_db_links;
      prompt exit
      spool off
      exit
      2. drop.sql - The output of the above is the following, which being run by the next job.
      spool d:\batch\drop_db_link.lst
      set echo on
      drop database link ACCDB;                                                       
      drop database link ADWEADB;                                                     
      drop database link AURODB;                                                      
      drop database link BGAISDV;                                                     
      drop database link CAPGEMDV;                                                    
      drop database link COADV231;                                                    
      drop database link COADVST;                                                     
      drop database link CONEDDV;                                                     
      drop database link DLCDV;                                                       
      drop database link DLCMDMDV;
      exit
      3. SpoolCreate.sql
      set echo off
      set heading off
      set feedback off
      set verify off
      set term off
      set pages 0
      set lines 100
      spool d:\batch\create.sql
      prompt spool d:\batch\create_db_link.lst
      prompt set echo on 
      
      select distinct 'create database link '||tns_name||' connect to system identified by '||decode(passwd,null,'manager1',passwd)||' using '''||Lower(tns_name)||''';' from mdc_inventory 
      where tns_name in (select tns_name from mdc_db_backup_details2) and DATABASE_BKP_ENABLED='YES';
      
      prompt exit
      spool off
      exit
      4. Create.sql
      spool d:\batch\create_db_link.lst
      set echo on
      create database link MDCAPEX connect to system identified by manager1 using 'mdcapex';             
      create database link MDCDB connect to system identified by manager1 using 'mdcdb';                 
      create database link ENERDV connect to system identified by manager1 using 'enerdv';               
      create database link ENERGDB connect to system identified by manager1 using 'energdb';             
      create database link AURODB connect to system identified by manager1 using 'aurodb';               
      create database link COADVST connect to system identified by manager1 using 'coadvst';             
      create database link DTOSTG connect to system identified by manager1 using 'dtostg';               
      create database link GSTONEDV connect to system identified by manager1 using 'gstonedv';           
      create database link HYDRODV connect to system identified by manager1 using 'hydrodv';             
      create database link MDADEDV connect to system identified by manager1 using 'mdadedv';   
      exit
      My boss ask me to use the DBMS_SCHEDULER instead of windows task scheduler.

      Can you help me please how to do it?


      Thanks a lot,

      Kinz

      Edited by: KinsaKaUy? on 13-Nov-2012 03:03
        • 1. Re: DBMS scheduler
          Osama_Mustafa
          begin
          dbms_scheduler.create_job (
          job_name =>'DROP_DB_LINK',
          job_type =>'executable',
          job_action => '<PATH>.bat >',
          start_date      => SYSTIMESTAMP,
           repeat_interval => freq=daily; byhour=0; byminute=0; bysecond=0;
          enabled => true,
          );
          commit;
          end;
          /
          • 2. Re: DBMS scheduler
            Osama_Mustafa
            also its useful to read
            http://psoug.org/reference/dbms_scheduler.html
            • 3. Re: DBMS scheduler
              Fran
              Please check this links:
              http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sched.htm#CIHDJEEB
              http://www.oracle-base.com/articles/10g/scheduler-10g.php
              • 4. Re: DBMS scheduler
                asifkabirdba
                http://www.morganslibrary.org/reference/pkgs/dbms_scheduler.html


                Regards
                Asif Kabir
                • 5. Re: DBMS scheduler
                  846231
                  I thank you all,

                  @osama -
                  begin
                  dbms_scheduler.create_job (
                  job_name =>'DROP_DB_LINK',
                  job_type =>'executable',
                  job_action => '<PATH>.bat >',
                  start_date      => SYSTIMESTAMP,
                   repeat_interval => freq=daily; byhour=0; byminute=0; bysecond=0;
                  enabled => true,
                  );
                  commit;
                  end;
                  / 
                  I am confused. Do you mean the dbms_sched can read OS files? I do not need to convert my scripts to a PL/SQL?
                  • 6. Re: DBMS scheduler
                    asifkabirdba
                    Yes it can read OS files.


                    Regards
                    Asif Kabir
                    • 7. Re: DBMS scheduler
                      Fran
                      dbms_scheduler can run .sql, .exe, .bat, ..... Your scripts has sql code so you won't have problems.
                      • 8. Re: DBMS scheduler
                        Veeresh.S
                        it should be something like this.........................

                        BEGIN
                        SYS.DBMS_SCHEDULER.CREATE_JOB(
                        job_name => 'refresh_link',
                        job_type => 'EXECUTABLE',
                        job_action => 'C:\WINDOWS\system32\cmd.exe',
                        start_date => SYSTIMESTAMP,
                        repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0',
                        number_of_arguments => 2,
                        end_date => NULL,
                        enabled => TRUE,
                        comments => 'refresh db links');
                        SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
                        job_name => 'refresh_link', argument_position => 1, argument_value => '/c');
                        SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
                        job_name => 'refresh_link', argument_position => 2, argument_value => '"d:\RefreshDblink.bat"');
                        END;
                        /
                        • 9. Re: DBMS scheduler
                          846231
                          I thanks you all,

                          I can see that the Windows task scheduler is very easy to use than the DBMS_SCHED which is complicated. :(

                          What is the advantage of DBMS_SCHED against the Windows task scheduler?
                          • 10. Re: DBMS scheduler
                            Osama_Mustafa
                            I am confused. Do you mean the dbms_sched can read OS files? I do not need to convert my scripts to a PL/SQL?
                            so what is the benefit when you use it , the difference between DBMS_SCHEDULER and windows SCHEDULER that when you server is down and Job time has been passed On windows you have to run it manually but on Oracle DBMS_SCHEDULER you don't have to do that it will start automatically .
                            • 11. Re: DBMS scheduler
                              Osama_Mustafa
                              I can see the Windows task scheduler are very easy to use the the DBMS_SCHED is complicated.
                              its not complicated you just need to read oracle documentation about it .
                              • 12. Re: DBMS scheduler
                                846231
                                I thank you all :)


                                So what I need to do is create this one and no other else? Then off I GO!
                                BEGIN
                                SYS.DBMS_SCHEDULER.CREATE_JOB(
                                job_name => 'refresh_link',
                                job_type => 'EXECUTABLE',
                                job_action => 'C:\WINDOWS\system32\cmd.exe',
                                start_date => SYSTIMESTAMP,
                                repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0',
                                number_of_arguments => 2,
                                end_date => NULL,
                                enabled => TRUE,
                                comments => 'refresh db links');
                                SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
                                job_name => 'refresh_link', argument_position => 1, argument_value => '/c');
                                SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
                                job_name => 'refresh_link', argument_position => 2, argument_value => '"d:\RefreshDblink.bat"');
                                END;
                                /
                                Sorry I do not have time to read docs as I have deadline 2 hrs from now :(
                                • 13. Re: DBMS scheduler
                                  Osama_Mustafa
                                  Sorry I do not have time to read docs as I have deadline 2 hrs from now :(
                                  we all have time to do something , Somewhere you need to start depend on your self By reading Oracle Documentation . ;)

                                  Don't forget to mark this thread as answered please
                                  • 14. Re: DBMS scheduler
                                    Veeresh.S
                                    yup...............do necessary changes and try running it.........it should execute successfully with the message procedure created.

                                    upon successful creation you can test the job by using below sql statement

                                    BEGIN
                                    DBMS_SCHEDULER.RUN_JOB( 'refresh_link');
                                    END;
                                    /

                                    for job status you can look in dba_scheduler_job_log, make sure you filter it with job_name='refresh_link';

                                    ref : http://mikesmithers.wordpress.com/2012/06/12/running-a-windows-batch-file-from-dbms_scheduler
                                    and http://docs.oracle.com/cd/B28359_01/server.111/b28310/schedover004.htm#BGBHJCHH

                                    Edited by: Veeresh.S on Nov 13, 2012 5:15 PM
                                    1 2 Previous Next