1.例子利用oracle 11g 的dbms_scheduler包执行perl脚本加载数据文件,其中主要用到三个过程分别为SET_JOB_ARGUMENT_VALUE,CREATE_JOB,RUN_JOB三个过程,其中三个过程的参数说明如下:
create_job参数:
SET_JOB_ARGUMENT_VALUE参数:
RUN_JOB参数:
由于本例中是调用操作系统的sqlldr命令去实现数据文件的加载,所以要用到create_job过程创建的job_type为'EXECUTABLE
'的job去实现,其中job_type含义如下
-
'PLSQL_BLOCK
'
This specifies that the job is an anonymous PL/SQL block. Job or program arguments are not supported when the job or program type is?PLSQL_BLOCK
. In this case,the number of arguments must be 0.
-
'STORED_PROCEDURE'
This specifies that the job is a PL/SQL or Java stored procedure,or an external C subprogram. Only procedures,not functions with return values,are supported.
-
'EXECUTABLE'
This specifies that the job is external to the database. External jobs are anything that can be executed from the command line of the operating system.?Anydata
?arguments are not supported with a job or program type of?EXECUTABLE
. The job owner must have the?CREATE
?EXTERNAL
?JOB
?system privilege before the job can be enabled or run.
-
'CHAIN
'
This specifies that the job is a chain. Arguments are not supported for a chain,so?number_of_arguments
?must be 0.
2.由于用到dbms_scheduler包创建'EXECUTABLE'类型的job,需要对操作系统用户及数据库用户配置,以ETL(操作系统用户),ETL_TEST(数据库用户)为例进行配置,实验环境为Redhat5.5+Oracle11G(11.2.3)+Perl(5.8.8)
a.创建操作系统用户
[root@ETL ~]# useradd -d /home/etl/ -m etl
[root@ETL ~]# passwd etl
Changing password for user etl.
New UNIX password:?
BAD PASSWORD: it is based on a dictionary word
Retype new UNIX password:?
passwd: all authentication tokens updated successfully.
注明:在linux系统中如果没有指定创建用户的组,系统会默认创建一个与用户名一致的用户组
b.配置用户ETL的环境变量(/home/etl/.bash_profile),其中红色字体与Oracle用户保持一致即可
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
? ? ? ? . ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
export ORACLE_BASE=/u01/app/oracle ?
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=ETL?
export ORACLE_TERM=xterm ?
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"?
export ORA_NLS33=$ORACLE_HOME/common/nls/admin/data?
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/lib?
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib?
export LD_LIBRARY_PATH ?
export PATH=$PATH:$ORACLE_HOME/bin
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
? ? ? ? . ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
export ORACLE_BASE=/u01/app/oracle ?
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=ETL?
export ORACLE_TERM=xterm ?
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"?
export ORA_NLS33=$ORACLE_HOME/common/nls/admin/data?
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/lib?
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib?
export LD_LIBRARY_PATH ?
export PATH=$PATH:$ORACLE_HOME/bin
PATH=$PATH:$HOME/bin
c.配置脚本及数据文件相关路径
[root@ETL /]# mkdir /ETL
[root@ETL /]# cd ETL
[root@ETL ETL]# mkdir bad
[root@ETL ETL]# mkdir log
[root@ETL ETL]# mkdir loader
[root@ETL ETL]# mkdir control
[root@ETL ETL]# mkdir data
[root@ETL ETL]# mkdir backup
[root@ETL ETL]# mkdir sh
[root@ETL ETL]# mkdir perl
[root@ETL ETL]# cd ..
[root@ETL /]# chown -R etl:etl /ETL
[root@ETL /]# chmod -R 777 /ETL
(目录说明:bad(sqlldr加载数据文件被拒的记录),log(sqlldr加载数据文件日志),loader(加载数据文件的perl脚本),control(sqlldr加载数据文件所用到的控制文件),data(sqlldr加载的数据文件,backup(数据文件的备份目录),sh(shell脚本目录),per(perl脚本目录).
d.因为此次实验是用ETL_TEST(数据库用户)调用dbms_schduler包以ETL用户身份加载数据(sqlldr加载),以下为执行'EXECUTABLE
'的job相关配置?
? ? ? 1.查看$ORACLE_HOME/rdbms/admin/externaljob.ora 权限
[root@ETL ~]# su - oracle
[oracle@ETL ~]$ cd $ORACLE_HOME
[oracle@ETL dbhome_1]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@ETL dbhome_1]$ exit
logout
[root@ETL ~]# cd /u01/app/oracle/product/11.2.0/dbhome_1
[root@ETL dbhome_1]# cd rdbms
[root@ETL rdbms]# cd admin
[root@ETL admin]# ls -al|grep externaljob.ora
-rw-r----- ?1 root ? oinstall ? ?1536 Jan 30 13:28 externaljob.ora
(其中文件权限必须和上面一致)
? ? 2.配置$ORACLE_HOME/rdbms/admin/externaljob.ora,将run_user=etl run_group=etl 具体如下:
[root@ETL admin]# vi externaljob.ora
# $Header: externaljob.ora 16-dec-2005.20:47:13 rramkiss Exp $
#
# Copyright (c) 2005,Oracle. All rights reserved.
# NAME
# ? externaljob.ora
# FUNCTION
# ? This configuration file is used by dbms_scheduler when executing external
# ? (operating system) jobs. It contains the user and group to run external
# ? jobs as. It must only be writable by the owner and must be owned by root.
# ? If extjob is not setuid then the only allowable run_user
# ? is the user Oracle runs as and the only allowable run_group is the group
# ? Oracle runs as.
#
# NOTES
# ? For Porters: The user and group specified here should be a lowly privileged
# ? ? ? ? ? ? ? ?user and group for your platform. For Linux this is nobody
# ? ? ? ? ? ? ? ?and nobody.
# MODIFIED
# ? ? rramkiss ? 12/09/05 - ?Creation
#
##############################################################################
# External job execution configuration file externaljob.ora
#
# This file is provided by Oracle Corporation to help you customize
# your RDBMS installation for your site. ?Important system parameters
# are discussed,and default settings given.
#
# This configuration file is used by dbms_scheduler when executing external
# (operating system) jobs. It contains the user and group to run external
# jobs as. It must only be writable by the owner and must be owned by root.
# If extjob is not setuid then the only allowable run_user
# is the user Oracle runs as and the only allowable run_group is the group
# Oracle runs as.
run_user =etl ? ?
run_group =etl ? ?
? ? 3.查看$ORACLE_HOME/bin/extjob文件权限
[root@ETL admin]# su - oracle
[oracle@ETL ~]$ cd $ORACLE_HOME
[oracle@ETL dbhome_1]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@ETL dbhome_1]$ exit
logout
[root@ETL admin]# cd /u01/app/oracle/product/11.2.0/dbhome_1
[root@ETL dbhome_1]# ls -al|grep extjob
[root@ETL dbhome_1]# cd bin
[root@ETL bin]# ls -al|grep extjob
-rwsr-x--- ?1 root ? oinstall ? 1249595 Jan 18 00:53 extjob
-rwx------ ?1 oracle oinstall ? 1249595 Jan 18 00:53 extjobo
-rwxr-xr-x ?1 oracle oinstall ? 1249958 Sep 17 ?2011 extjobO
-rwxr-xr-x ?1 oracle oinstall ? 1249958 Sep 17 ?2011 extjoboO
(注明:extjob文件权限必须与上面保持一致)
e.创建ETL_TEST(数据库用户),并给相应权限
? 1.创建 ETL_TEST用户
create user etl_test identified by etl_test
default tablespace users
temporary tablespace temp;
? 2.赋于相关的系统和对象权限
grant connect,resource to etl_test;
grant select on sys.v_$session to etl_test;
grant select on sys.v_$process to etl_test;
grant create job to etl_test;
grant create any job to etl_test;
grant create external job to etl_test;
grant MANAGE SCHEDULER to etl_test;
grant alter system to etl_test;
grant execute on DBMS_LOCK to etl_test;
grant execute on DBMS_PIPE to etl_test;
grant execute on UTL_FILE to etl_test;
grant execute on DBMS_SCHEDULER to etl_test;
grant all on DBMS_SCHEDULER to etl_test;
grant execute on DBMS_CRYPTO to etl_test;
grant create any directory to etl_test;
grant debug any procedure,debug connect session to etl_test;?
grant select on sys.dba_free_space to etl_test;?
grant select on sys.dba_data_files to etl_test;
? 3.创建Oracle的Directory并赋权
create or replace directory RWA_FILE_DATA as '/ETL/data';
create or replace directory RWA_FILE_BAD as '/ETL/bad';
create or replace directory RWA_FILE_LOG as '/ETL/log';
create or replace directory RWA_FILE_CONTROL as '/ETL/control';
create or replace directory RWA_FILE_LOADER as '/ETL/loader';
create or replace directory RWA_FILE_SH as '/ETL/sh';
create or replace directory RWA_FILE_BACKUP as '/ETL/backup';
create or replace directory RWA_FILE_PERL as '/ETL/perl';
grant read,write on directory RWA_FILE_DATA to etl_test;
grant read,write on directory RWA_FILE_PERL to etl_test;
grant read,write on directory RWA_FILE_BAD to etl_test;
grant read,write on directory RWA_FILE_LOG to etl_test;
grant read,write on directory RWA_FILE_CONTROL to etl_test;
grant read,write on directory RWA_FILE_LOADER to etl_test;
grant read,write on directory RWA_FILE_SH to etl_test;
grant read,write on directory RWA_FILE_BACKUP ?to etl_test;
f.加载数据文件
? 1.加载数据的表
create table F_MUREX_GL
(
? data_dt ?DATE,
? areano ? VARCHAR2(10),
? currency VARCHAR2(10),
? apcode ? VARCHAR2(20),
? orgcde ? VARCHAR2(20),
? damount ?NUMBER,
? camount ?NUMBER,
? remark ? VARCHAR2(1000)
);
? 2.加载数据的控制文件,数据文件,shell脚本,perl脚本如下
? ? a.RWA_EDW_PLEDGE_IMPAWN_INFO.ctl -- sqlldr控制文件 目录:/ETL/control
[etl@ETL control]$ more RWA_EDW_PLEDGE_IMPAWN_INFO.ctl
load data
TRUNCATE into table F_MUREX_GL
fields terminated by X'01'?
trailing nullcols
(DATA_DT DATE'yyyy-mm-dd',AREANO,CURRENCY,APCODE,ORGCDE,DAMOUNT,CAMOUNT,REMARK)
? ?b.RWA_EDW_PLEDGE_IMPAWN_INFO.sh -- 加载数据的shell文件 目录:/ETL/loader
[etl@ETL loader]$ more RWA_EDW_PLEDGE_IMPAWN_INFO.sh
#!/bin/sh
. /home/etl/.bash_profile
vOraPwd=$1
sqlldr userid=etl_test/$vOraPwd@ETL control=/ETL/control/RWA_EDW_PLEDGE_IMPAWN_INFO.ctl data=/ETL/data/RWA_EDW_RWA_PLEDGE_IMPAWN_INFO_20140630_001.txt log=/ETL/log/RWA
_EDW_PLEDGE_IMPAWN_INFO.log bad=/ETL/bad/RWA_EDW_PLEDGE_IMPAWN_INFO.bad
? c.RWA_EDW_PLEDGE_IMPAWN_INFO.pl ?-- 调用加载数据文件的shell脚本(RWA_EDW_PLEDGE_IMPAWN_INFO.sh)
#! /usr/bin/perl
########################################################
# @name :RWA_EDW_PLEDGE_IMPAWN_INFO.pl
# @parameter : db user pasaword?
# @description : run RWA_EDW_PLEDGE_IMPAWN_INFO.sh and load data to table F_MUREX_GL
# ? ? ? ? ? ? ? ??
# @create_date :2015-02-09
# @author :Tux
# @version :1.0.0
# @source :
# @target :
# @modify :
# @copyright :
####################################################################
use strict;
my $passwd;
my $clm_shell = '/ETL/loader/RWA_EDW_PLEDGE_IMPAWN_INFO.sh';
$passwd = $ARGV[0];
# run shell script?
eval {
? ? system("sh $clm_shell $passwd");
};
if ($@ ne '') {
? ? die "execute sqlldr ?script failedn";
}
else?
{
print ?"the sqlldr script run sucessessfull !!n";
}
d.RWA_EDW_RWA_PLEDGE_IMPAWN_INFO_20140630_001.txt ?-- 数据文件
[etl@ETL data]$ more RWA_EDW_RWA_PLEDGE_IMPAWN_INFO_20140630_001.txt
2014-06-30^^^^
2014-06-30 ? 00350AED0232 ? ?6114 ? ? ? ? ? ? ? ? ?0.000 ? ? ? ?1000000.000 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00350AUD0148 ? ?6107 ? ? ? ? ? ?4538300.000 ? ? ? ? ? ? ?0.000 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00350AUD0110 ? ?6107 ? ? ? ? ? ?1526300.000 ? ? ? ? ? ? ?0.000 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00350AUD0971 ? ?6107 ? ? ? ? ? ?8006100.000 ? ? ? ? ? ? ?0.000 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00350AUD0158 ? ?6107 ? ? ? ? ? ? ? ?154.430 ? ? ? ? ? ? ?0.000 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00350AUD5497 ? ?6108 ? ? ? ? ? ? ? ? ?0.000 ? ? ? ? ?15200.000 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00350AUD0155 ? ?6108 ? ? ? ? ? ?1000000.000 ? ? ? ? ? ? ?0.000 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00350CAD0239 ? ?6107 ? ? ? ? ? ? ? ? ?0.000 ? ? ? ? 950000.000 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00350CAD0247 ? ?6107 ? ? ? ? ? ? ? ? ?0.000 ? ? ? ? 950000.000 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00350CAD9317 ? ?6107 ? ? ? ? ? ? 262222.000 ? ? ? ? ? ? ?0.000 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00350CAD0123 ? ?6114 ? ? ? ? ? ?1000000.000 ? ? ? ? ? ? ?0.000 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00350CHF0971 ? ?6107 ? ? ? ? ? ?2383200.000 ? ? ? ? ? ? ?0.000 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00036CNY9867 ? ?6118 ? ? ? ? ? ? 572590.240 ? ? ? ? ? ? ?0.000 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00350CNY7066 ? ?6118 ? ? ? ? ? ?9000000.000 ? ? ? ? ? ? ?0.000 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00036CNY7048 ? ?6118 ? ? ? ? 7546536516.090 ? ? ? ? ? ? ?0.000 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00036CNY6814 ? ?6118 ? ? ? ? ? ?1323765.700 ? ? ? ? ? ? ?0.000 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00350CNY5512 ? ?6107 ? ? ? ? ? ? ? ? ?0.000 ? ? 1089729877.740 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00350CNY9861 ? ?6118 ? ? ? ? ? ? 248471.230 ? ? ? ? ? ? ?0.000 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00350CNY9887 ? ?6118 ? ? ? ? ? ? ? 2666.660 ? ? ? ? ? ? ?0.000 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00036CNY5433 ? ?6104 ? ? ? ? ? ? ? ? ?0.000 ? ? ? ? 130676.080 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00350CNY5453 ? ?6107 ? ? ? ? ?500495181.380 ? ? ? ? ? ? ?0.000 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00350CNY5562 ? ?6107 ? ? ? ? ? ? ? ? ?0.000 ? ? ? 50571245.930 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00350CNY7591 ? ?6124 ? ? ? ? ? 69524032.280 ? ? ? ? ? ? ?0.000 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00036CNY9971 ? ?6104 ? ? ? ? ? ?4313539.170 ? ? ? ? ? ? ?0.000 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00036CNY6025 ? ?6121 ? ? ? ? ? ? ? ?105.860 ? ? ? ? ? ? ?0.000 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00350CNY5149 ? ?6118 ? ? ? ? ? ? ? ? ?0.000 ? ? ? ? 108304.850 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00350CNY5605 ? ?6110 ? ? ? ? ? ? ? ? ?0.000 ? ? ? ?1790434.050 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00350CNY7274 ? ?6110 ? ? ? ? ? 78709183.050 ? ? ? ? ? ? ?0.000 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00350CNY7272 ? ?6110 ? ? ? ? ? ?1000000.000 ? ? ? ? ? ? ?0.000 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00350CNY9322 ? ?6107 ? ? ? ? ? ? ? ? ?0.000 ? ? ?316363894.130 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00350CNY8804 ? ?6120 ? ? ? ? ? ? 328682.870 ? ? ? ? ? ? ?0.000 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00350CNY0961 ? ?6116 ? ? ? ? ? ? 330410.960 ? ? ? ? ? ? ?0.000 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00350CNY0845 ? ?6116 ? ? ? ? ? ? ? ? ?0.000 ? ? ? ?3041470.520 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00350CNY0745 ? ?6116 ? ? ? ? ? ?3041470.520 ? ? ? ? ? ? ?0.000 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00350CNY0975 ? ?6116 ? ? ? ? ?200000000.000 ? ? ? ? ? ? ?0.000 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00350CNY8578 ? ?6110 ? ? ? ? ? ? ? ? ?0.000 ? ? ? 98844709.540 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00350CNY7492 ? ?6110 ? ? ? ? ? ?3501396.240 ? ? ? ? ? ? ?0.000 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00350CNY8635 ? ?6110 ? ? ? ? ? 98844709.540 ? ? ? ? ? ? ?0.000 ? ? ? ? ? ? ? ? ? ? ??
2014-06-30 ? 00350EUR9875 ? ?6112 ? ? ? ? ? ? ? ? ?0.000 ? ? ? ?3000000.000?
3.创建加载数据文件的EXCUTABLE类型job,执行并查看日志
-- 创建executable job
begin
? ? ? ?dbms_scheduler.create_job(
? ? ? ?job_name => 'LF_PERL',
? ? ? ?job_type => 'EXECUTABLE',
? ? ? ?job_action => '/ETL/loader/RWA_EDW_PLEDGE_IMPAWN_INFO.pl',
? ? ? ?start_date => systimestamp,
? ? ? ?number_of_arguments => 1,
? ? ?-- ?job_class ? ? ? => 'no_logging_class',
? ? ? ?auto_drop => true,
? ? ? ?comments => 'LF_PERL');
end;
-- 传入参数
begin?
? ? dbms_scheduler.set_job_argument_value(job_name => 'LF_PERL',
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? argument_position =>1,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? argument_value =>'etl_test' );
end;
-- 执行
begin?
? ?dbms_scheduler.run_job(job_name =>'LF_PERL');
end;
-- 删除job
begin?
? dbms_scheduler.drop_job(job_name => 'LF_PERL');
end;
-- 清除job日志
begin?
? dbms_scheduler.purge_log(job_name => 'LF_PERL');
end;
-- 查看JOB
select * from user_scheduler_jobs t
where job_name = 'LF_PERL'
;
-- 查看JOB执行情况 select * from user_scheduler_job_run_details t where job_name = 'LF_PERL'