"Application": Shell crontab periodically generates data from Oracle table to TXT file and uploads to FTP

I. Description of my environment
1. Oracle server is installed on Win7 32-bit, Oracle version is 10.2.0.1.0
2, Linux for centos6.5 32-bit, installed on the Oracle VM VirtualBox virtual machine
3. Win7 with FTP service

Ii. Description of function implementation
Using the shell's crontab command to execute a. sh file on a timed basis, the functionality of this file has been implemented to generate Oracle table data to a local TXT file and upload it to FTP, where necessary, to log execution logs.

Third, step
1. Install Oracle Client and configure environment variables in CentOS

2. Install the FTP client in CentOS

3, write the shell, query the Oracle database table, and write to the local TXT file

4, write the shell, upload files to FTP

5, modify the shell, print the execution log
The print log functions are as follows:

function loginfo {  Time_flag= ' date +'%y-%m-%d%h:%m:%s  '" [${time_flag}] [INFO]-- $ " }

View Code

　　

6. All Shell Code

data table structure See "Writing shell, Implementing querying Oracle Database tables, and writing to local TXT file"

A, the Print log function is encapsulated into testlog.sh, the code is as follows:

#!/bin/bash#######################################################################################function: Define Log printing function#usage: loginfo "Log Content"#version: 0.1 #crazyMyWay #日期:#Description: Set up the first edition######################################################################################functionLoginfo {Time_flag= ' Date +"%y-%m-%d%h:%m:%s"' echo"[${time_flag}] [INFO]-- $"}

View Code

B, through the FTP upload file code encapsulated into testftptool.sh, as follows:

#!/bin/bash#######################################################################################Features: FTP upload/download files#usage: The first parameter put (upload) or get (download) file, the second parameter is the FTP server IP, the third to fourth parameter is the user name and password#The fifth parameter is the working directory on the FTP, the sixth is the local directory, and the seventh is the file name of the operation.#Example: testftptool.sh put|get ip_address ftp_user ftp_password ftp_dir local_dir filename#version: 0.1 #crazyMyWay #日期:#Description: Set up the first edition######################################################################################e_notroot=67#output Help information, usage:./testftptool.sh-hif[ $#-eq 1-a "$" = "-H"]Then Echo"Usage: $ put|get ip_address ftp_user ftp_password ftp_dir local_dir filename"Echo"Example: $ put|get FTP Service IP FTP user name FTP password FTP directory local directory file name"Exit$E _notrootfi#If the parameter is not equal to 7if[ $#! = 7]Then Echo"Param error:usage: $ put|get ip_address ftp_user ftp_password ftp_dir local_dir filename"Exit$E _notrootfi#FTP operation, ftp command interpretation please refer to the relevant informationFTP-V-N <<!Open $21stUser $ $4PROMPTEPSV4 OFFCD $BINLCD$6 $ $7quit!

View Code

C, the main code teststudent.sh, as follows:

#!/bin/bash#######################################################################################function: Querying data from Oracle (T_student), and writing to TXT file, named Student_yyyymmdd.txt,#finally uploaded to the ftp#usage: Scheduled execution, daily 2 o'clock in the morning execution (or direct execution)#Notice changes: User/[email protected],#introduce the paths of testlog.sh and testftptool.sh,#Ftp-ip, Ftp-username, Ftp-password, FTP upload directory, local directory##version: 0.1 #crazyMyWay #日期: 2015-03-29#Description: Set up the first edition#######################################################################################Add the user environment variable, if you do not execute this sentence, then when using the cron command,#the user's environment variables are not loaded automatically, and if a command such as Sqlplus is used, an error occurs. ~/. Bash_profile#definition file suffix name yyyymmddfilename_postfix= ' Date +"%y%m%d"`#Defining related VariablesOracle_user="CentOS"Oracle_pass="CentOS"oracle_id="WIN7ORCL"Current_file_dir="/ljxd/shell-demo/oracle/"Current_file_name="Student_${filename_postfix}.txt"Common_file_dir="/ljxd/shell-demo/oracle/"ftp_ip="192.168.56.101"Ftp_user="Student"Ftp_pass="Student"Ftp_upload_path="/student"#introducing the log file.${common_file_dir}Testlog.shloginfo"generate Data Start ....."#Connect to Oracle, set relevant parameters, and output data to TXT fileSqlplus-s${oracle_user}/${oracle_pass}@${oracle_id}<<eof >${current_file_dir}${current_file_name}Set pages0set feed offset heading offset feedback offset verify offset linesize1000Select T.id| | '## # ' | | t.name| | ' # # # ' | | To_char (T.birthday, ' Yyyy-mm-dd hh24:mi:ss ') from t_student t;EOF#push data to FTPLoginfo"transfer data to ftp ....". ${common_file_dir}Testftptool.sh put${ftp_ip} ${ftp_user} ${ftp_pass} ${ftp_upload_path} ${current_file_dir} ${current_file_name}#EndLoginfo"Generate Data End ... .."

View Code

Note The directory structure, modify the relevant parameters, run./teststudent.sh

The test results are as follows:

　　　　　　

7, crontab command to achieve scheduled tasks

Crontab-e|-l|-r

-e|-l|-r for editing, viewing, deleting scheduled tasks, each user will have a crontab configuration file, detailed instructions please consult the relevant information.

Scheduled execution 2 o'clock in the morning every day, and append the output log to the Student.log file, the crontab command is as follows:

　　　　

Save and exit the editor, the timer takes effect immediately.

　　　　

"Application": Shell crontab periodically generates data from Oracle table to TXT file and uploads to FTP