# .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文件权限