oracle数据库落地成本地文件

使用场景：把Oracle数据库落地为本地文件。

1、安装oracle client

  rpm -ivh oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm 
  rpm -ivh oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm

配置环境变量命令：vim /etc/profile
配置如下：
export ORACLE_HOME=/usr/lib/oracle/11.2/client64  #通过whereis oracle 查看oracle安装路径
export PATH=/root/jdk1.8.0_121/bin:$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
保存之后,执行：source /etc/profile 

2、修改oracle配置文件，配置连接数据库

cd /usr/lib/oracle
编辑配置文件：vim tnsnames.ora
 
DATABASENAME =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = servicename)
    )
  )
 
SERVICE_NAME查询方法
show parameter service_names;

3、测试是否连接成功

sqlplus username/password@service_name

4、布置脚本，每天生成一个ORACLE的最新文件（不适用与增量采集）

#! /bin/bash

new_date=`date +%Y%m%d`
export NLS_LANG=american_america.AL32UTF8
sql="SELECT * FROM SERVER_DETAIL_${new_date}"

sqlplus dbname/passwd@service_names >>/app/oracleData/${new_date}'.log' << EOF
set heading off
set line 4000
set numwidth 12
set feedback on
set colsep','
set WRAP OFF
${sql};
exit
EOF
sed -i 1,12d /app/oracleData/${new_date}'.log'
sed -i -e '/selected/,$d' /app/oracleData/${new_date}'.log'
sed -i -e '/Disconnected/,$d' /app/oracleData/${new_date}'.log'
sed -i '$d' /app/oracleData/${new_date}'.log'
sed -i '/^$/d' /app/oracleData/${new_date}'.log'
cat /app/oracleData/${new_date}'.log' |awk -F ',' '{print $1,$2,$3,$4,$5,$6,$7,$8,$9}' |awk '{sub("^ *","");sub(" *$","");print $1,$2,$3,$4,$5,$6,$7,$8,$9}' > /app/oracleData/data/${new_date}'.log'
rm -f /app/oracleData/${new_date}'.log'

如果乱码执行：

export NLS_LANG=american_america.AL32UTF8