Linux系统下如何通过Shell脚本高效调用SQLPlus执行Oracle数据库查询与维护任务并处理常见连接错误

引言:Shell脚本与SQLPlus的完美结合
在Linux系统管理中,自动化是提高效率的关键。当我们需要频繁与Oracle数据库交互时,通过Shell脚本调用SQLPlus成为了一种高效的解决方案。这种方法不仅可以减少人工操作,还能确保任务的定时执行和错误处理的自动化。本文将详细介绍如何在Linux环境下编写Shell脚本来调用SQLPlus执行查询和维护任务,并重点讲解如何处理常见的连接错误。
为什么选择Shell脚本+SQLPlus?
轻量级
:无需额外安装复杂的ETL工具
灵活性
:可以轻松整合Linux系统命令和Oracle操作
可维护性
:脚本易于理解和修改
原生支持
:Oracle客户端自带SQLPlus,无需额外组件
基础环境配置
1. Oracle客户端安装
首先确保系统已安装Oracle Instant Client或完整版Oracle客户端:
# 检查Oracle客户端是否安装
rpm -qa | grep oracle-instantclient

# 或者检查sqlplus命令是否存在
which sqlplus
2. 环境变量配置
配置必要的环境变量是成功连接数据库的前提:
# 编辑用户profile文件(如.bash_profile)
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/client_1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK # 防止中文乱码

# 使配置生效
source ~/.bash_profile
3. 测试基础连接
在编写脚本前,先手动测试连接:
sqlplus username/password@//hostname:port/service_name

# 示例
sqlplus scott/tiger@//dbserver:1521/ORCL
Shell脚本基础模板
1. 标准脚本结构
#!/bin/bash
# 脚本名称: oracle_query.sh
# 功能描述: 执行Oracle查询并处理结果
# 作者: Your Name
# 创建日期: 2023-01-01

# 设置错误处理
set -euo pipefail

# 日志函数
log() {
echo “[$(date ‘+%Y-%m-%d %H:%M:%S’)] $1”
}

# 错误处理函数
error_exit() {
log “ERROR: $1”
exit 1
}

# 主逻辑
main() {
log “脚本开始执行”
# 数据库操作将在这里进行
log “脚本执行完成”
}

# 脚本入口
main “$@”
2. 连接信息管理
安全建议
:不要在脚本中硬编码密码,推荐使用以下方法:
方法1:使用Oracle钱包(推荐)
# 配置Oracle钱包
mkdir -p $ORACLE_HOME/network/admin
cat > $ORACLE_HOME/network/admin/sqlnet.ora << EOF WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = $ORACLE_HOME/network/admin))) SQLNET.WALLET_OVERRIDE = TRUE SSL_CLIENT_AUTHENTICATION = FALSE SSL_VERSION = 0 EOF # 将密码存入钱包 mkstore -wrl $ORACLE_HOME/network/admin -createCredential ORCL_DB username password # 脚本中使用 sqlplus /@ORCL_DB 方法2:使用环境变量(开发环境适用) # 在脚本中读取环境变量 DB_USER=${DB_USER:-"scott"} DB_PASSWORD=${DB_PASSWORD:-"tiger"} DB_CONNECT="${DB_USER}/${DB_PASSWORD}@//dbserver:1521/ORCL" 方法3:使用加密的密码文件(生产环境适用) # 创建加密密码文件(仅限当前用户可读) echo "encrypted_password" > ~/.dbpass
chmod 600 ~/.dbpass

# 脚本中解密使用(需要安装openssl)
DB_PASSWORD=$(openssl enc -aes-256-cbc -d -in ~/.dbpass -pass pass:”your_key”)
执行SQL查询的多种方式
1. 直接执行SQL语句
#!/bin/bash

# 数据库连接信息
DB_CONNECT=”scott/tiger@//dbserver:1521/ORCL”

# 执行简单查询
execute_query() {
local sql=”$1″
sqlplus -s “$DB_CONNECT” << EOF SET PAGESIZE 0 SET FEEDBACK OFF SET VERIFY OFF SET HEADING OFF SET LINESIZE 1000 $sql EXIT; EOF } # 示例:查询员工数量 emp_count=$(execute_query "SELECT COUNT(*) FROM emp;") log "员工总数: $emp_count" 2. 执行SQL文件 #!/bin/bash # 执行SQL文件 execute_sql_file() { local sql_file="$1" local output_file="${2:-/tmp/sql_output.log}" sqlplus -s "$DB_CONNECT" @"$sql_file" > “$output_file” 2>&1

# 检查执行结果
if [ $? -eq 0 ]; then
log “SQL文件执行成功,输出保存到: $output_file”
return 0
else
error_exit “SQL文件执行失败,请检查日志: $output_file”
fi
}

# 示例SQL文件内容(保存为query.sql)
cat > query.sql << 'EOF' SET PAGESIZE 0 SET FEEDBACK OFF SET VERIFY OFF SET HEADING ON COLUMN ename FORMAT A20 COLUMN job FORMAT A15 SELECT ename, job, sal FROM emp WHERE deptno = 10; EXIT; EOF # 执行 execute_sql_file "query.sql" "result.log" 3. 使用HEREDOC动态生成SQL #!/bin/bash # 动态生成SQL并执行 dynamic_query() { local deptno="$1" local min_sal="$2" sqlplus -s "$DB_CONNECT" << EOF SET PAGESIZE 0 SET FEEDBACK OFF SET VERIFY OFF SET HEADING OFF -- 使用绑定变量防止SQL注入 VAR dept_id NUMBER; VAR min_salary NUMBER; EXEC :dept_id := $deptno; EXEC :min_salary := $min_sal; SELECT COUNT(*) FROM emp WHERE deptno = :dept_id AND sal > :min_salary;

EXIT;
EOF
}

# 调用示例
count=$(dynamic_query 10 2000)
log “部门10中薪资超过2000的员工数: $count”
4. 处理查询结果
方法1:直接捕获输出
result=$(execute_query “SELECT sysdate FROM dual;”)
log “当前数据库时间: $result”
方法2:处理多行结果
#!/bin/bash

# 处理多行结果
process_multi_rows() {
local sql=”SELECT ename, sal FROM emp WHERE deptno = 10″

# 使用awk处理结果
sqlplus -s “$DB_CONNECT” << EOF | awk '{print "员工:" $1 " 薪资:" $2}' SET PAGESIZE 0 SET FEEDBACK OFF SET HEADING OFF $sql EXIT; EOF } # 调用 process_multi_rows 方法3:将结果存入数组 #!/bin/bash # 将查询结果存入数组 readarray -t employees < <(execute_query "SELECT ename FROM emp;" | grep -v '^$') log "查询到的员工:" for emp in "${employees[@]}"; do log " - $emp" done 常见连接错误及处理方案 1. ORA-12154: TNS解析错误 错误原因 :无法解析连接描述符 解决方案 : #!/bin/bash # 检查TNS配置 check_tns() { local tns_admin="${TNS_ADMIN:-$ORACLE_HOME/network/admin}" if [ ! -f "$tns_admin/tnsnames.ora" ]; then error_exit "tnsnames.ora文件不存在: $tns_admin/tnsnames.ora" fi # 验证TNS条目 local connect_string="$1" local tns_entry=$(grep -i "^${connect_string##*@}" "$tns_admin/tnsnames.ora") if [ -z "$tns_entry" ]; then error_exit "TNS条目不存在: $connect_string" fi } # 使用完整连接字符串避免TNS解析 DB_CONNECT="scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))" 2. ORA-12514: TNS监听器未知服务 错误原因 :监听器不知道请求的服务名 解决方案 : #!/bin/bash # 检查监听器状态 check_listener() { local host="$1" local port="${2:-1521}" # 使用tnsping检查 if ! tnsping "$host:$port" >/dev/null 2>&1; then
error_exit “无法连接到监听器 $host:$port”
fi

# 检查服务是否注册
lsnrctl status << EOF | grep -i "Service.*$service_name" CONNECT $host:$port EOF } # 在脚本中调用 check_listener "dbserver" "1521" "ORCL" 3. ORA-01017: 无效的用户名/密码 错误原因 :凭证错误或账户锁定 解决方案 : #!/bin/bash # 测试连接并处理认证错误 test_connection() { local max_attempts=3 local attempt=1 while [ $attempt -le $max_attempts ]; do log "尝试连接数据库 (第 $attempt 次)..." # 使用临时文件捕获错误 local temp_file=$(mktemp) sqlplus -s "$DB_CONNECT" << EOF > “$temp_file” 2>&1
EXIT;
EOF

if grep -q “ORA-01017” “$temp_file”; then
log “认证失败,请检查用户名和密码”
# 可以在这里触发密码更新逻辑
return 1
elif grep -q “ORA-28000” “$temp_file”; then
log “账户已被锁定,请联系DBA解锁”
return 1
elif [ $? -eq 0 ]; then
log “连接成功”
rm -f “$temp_file”
return 0
fi

rm -f “$temp_file”
sleep 2
((attempt++))
done

error_exit “连接失败,已尝试 $max_attempts 次”
}

# 调用
if ! test_connection; then
# 发送告警邮件
echo “数据库连接失败” | mail -s “告警” admin@example.com
exit 1
fi
4. ORA-12541: 无监听器
错误原因
:监听器未启动或网络不通
解决方案
:
#!/bin/bash

# 检查网络连通性和监听器
check_network() {
local host=”$1″
local port=”$2″

# 检查网络连通性
if ! nc -z -w 5 “$host” “$port” 2>/dev/null; then
error_exit “无法连接到 $host:$port,网络不通或监听器未启动”
fi

# 检查监听器响应
if ! echo “quit” | nc “$host” “$port” | grep -q “OK”; then
error_exit “监听器未正常响应”
fi
}

# 在连接前检查
check_network “dbserver” “1521”
5. ORA-12537: 网络会话终止
错误原因
:网络问题或监听器配置限制
解决方案
:
#!/bin/bash

# 设置超时和重试
execute_with_retry() {
local sql=”$1″
local max_retries=3
local retry_delay=5

for ((i=1; i<=max_retries; i++)); do log "执行SQL (尝试 $i/$max_retries)" # 设置SQLPlus超时 timeout 60 sqlplus -s "$DB_CONNECT" << EOF SET SQLPROMPT "" SET TIME ON $sql EXIT; EOF local exit_code=$? if [ $exit_code -eq 0 ]; then return 0 elif [ $exit_code -eq 124 ]; then log "执行超时,等待重试..." sleep $retry_delay else log "执行失败,错误码: $exit_code" sleep $retry_delay fi done error_exit "SQL执行失败,已重试 $max_retries 次" } 6. 综合错误处理框架 #!/bin/bash # 综合错误处理 execute_sql_safe() { local sql="$1" local output_file="${2:-/tmp/sql_output_$(date +%s).log}" local timeout="${3:-60}" # 创建临时文件 local temp_output=$(mktemp) local exit_code=0 # 执行SQL并捕获所有输出 timeout $timeout sqlplus -s "$DB_CONNECT" << EOF > “$temp_output” 2>&1
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
WHENEVER SQLERROR EXIT SQL.SQLCODE
WHENEVER OSERROR EXIT OSERROR
$sql
EXIT;
EOF

exit_code=$?

# 分析错误
case $exit_code in
0)
log “SQL执行成功”
cp “$temp_output” “$output_file”
;;
124)
error_exit “SQL执行超时”
;;
*)
# 检查特定的Oracle错误
if grep -q “ORA-00001” “$temp_output”; then
error_exit “唯一约束冲突”
elif grep -q “ORA-02292” “$temp_output”; then
error_exit “子记录存在,无法删除”
elif grep -q “ORA-12154” “$temp_output”; then
error_exit “TNS解析错误,请检查连接字符串”
elif grep -q “ORA-12514” “$temp_output”; then
error_exit “服务名错误或监听器问题”
else
# 输出详细错误信息
cat “$temp_output” >&2
error_exit “SQL执行失败,退出码: $exit_code”
fi
;;
esac

# 清理临时文件
rm -f “$temp_output”

# 返回输出文件路径
echo “$output_file”
}
高级应用:维护任务自动化
1. 数据库备份脚本
#!/bin/bash

# 数据库导出脚本
backup_database() {
local backup_dir=”/backup/oracle/$(date +%Y%m%d)”
local log_file=”$backup_dir/backup.log”

mkdir -p “$backup_dir”

# 使用数据泵导出(需要配置)
expdp system/password@ORCL \
DIRECTORY=BACKUP_DIR \
DUMPFILE=backup_$(date +%Y%m%d_%H%M%S).dmp \
LOGFILE=export.log \
SCHEMAS=SCOTT

# 或者使用传统exp
exp system/password@ORCL file=”$backup_dir/scott.dmp” owner=scott log=”$log_file”

# 压缩备份
gzip “$backup_dir/*.dmp”

# 清理旧备份(保留7天)
find /backup/oracle -type f -name “*.dmp.gz” -mtime +7 -delete

log “备份完成: $backup_dir”
}
数据清理脚本
#!/bin/bash

# 清理历史数据
cleanup_old_data() {
local days_to_keep=90
local batch_size=1000

# 使用SQLPlus执行删除
sqlplus -s “$DB_CONNECT” << EOF SET SERVEROUTPUT ON DECLARE v_deleted NUMBER := 0; v_total NUMBER := 0; BEGIN LOOP DELETE FROM transaction_log WHERE transaction_date < SYSDATE - $days_to_keep AND ROWNUM <= $batch_size; v_deleted := SQL%ROWCOUNT; v_total := v_total + v_deleted; DBMS_OUTPUT.PUT_LINE('已删除: ' || v_deleted || ' 条记录'); EXIT WHEN v_deleted = 0; COMMIT; END LOOP; DBMS_OUTPUT.PUT_LINE('总共删除: ' || v_total || ' 条记录'); END; / EXIT; EOF } 3. 监控告警脚本 #!/bin/bash # 监控表空间使用率 check_tablespace() { local threshold=80 sqlplus -s "$DB_CONNECT" << EOF | while read -r tablespace used; do SET PAGESIZE 0 SET FEEDBACK OFF SET HEADING OFF SELECT tablespace_name, ROUND(used_percent, 2) FROM ( SELECT a.tablespace_name, ROUND((a.bytes - b.bytes) / a.bytes * 100, 2) used_percent FROM (SELECT tablespace_name, SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name ) WHERE used_percent > $threshold;
EXIT;
EOF
if [ -n “$tablespace” ] && [ -n “$used” ]; then
log “告警: 表空间 $tablespace 使用率达 ${used}%”
# 发送告警
echo “表空间 $tablespace 使用率达 ${used}%” | mail -s “数据库告警” dba@example.com
fi
done
}
性能优化与最佳实践
1. SQLPlus性能优化
#!/bin/bash

# 优化SQLPlus性能的设置
optimize_sqlplus() {
sqlplus -s “$DB_CONNECT” << EOF -- 禁用回显以提高性能 SET NOLOGFILE SET NOAPPEND SET ARRAYSIZE 1000 SET MAXDATA 60000 SET SQLPROMPT "" SET TIME OFF SET TIMING OFF -- 优化查询 ALTER SESSION SET SORT_AREA_SIZE = 10485760; ALTER SESSION SET HASH_AREA_SIZE = 20971520; -- 你的SQL语句 ... EXIT; EOF } 2. 并行执行任务 #!/bin/bash # 并行执行多个SQL任务 parallel_sql() { local tasks=( "SELECT COUNT(*) FROM table1" "SELECT COUNT(*) FROM table2" "SELECT COUNT(*) FROM table3" ) # 使用后台进程并行执行 for task in "${tasks[@]}"; do ( result=$(execute_query "$task") echo "$task: $result" >> /tmp/parallel_results.log
) &
done

# 等待所有后台任务完成
wait

log “并行任务完成,结果保存在 /tmp/parallel_results.log”
}
3. 日志与审计
#!/bin/bash

# 带审计的日志函数
audit_log() {
local message=”$1″
local log_file=”/var/log/oracle_scripts/audit_$(date +%Y%m%d).log”

mkdir -p “$(dirname “$log_file”)”

echo “$(date ‘+%Y-%m-%d %H:%M:%S’) | $USER | $0 | $message” >> “$log_file”

# 同时输出到标准输出
echo “$message”
}

# 使用示例
audit_log “开始执行数据库维护任务”
audit_log “任务完成,影响行数: $affected_rows”
完整的生产级脚本示例
#!/bin/bash
# 生产级Oracle数据库维护脚本
# 功能:每日自动执行数据库维护任务

set -euo pipefail

# 配置区域
readonly SCRIPT_NAME=$(basename “$0”)
readonly SCRIPT_DIR=$(cd “$(dirname “$0″)” && pwd)
readonly LOG_DIR=”/var/log/oracle_scripts”
readonly LOG_FILE=”$LOG_DIR/${SCRIPT_NAME%.*}_$(date +%Y%m%d).log”
readonly LOCK_FILE=”/tmp/${SCRIPT_NAME%.*}.lock”

# 数据库配置(建议从环境变量读取)
DB_USER=”${DB_USER:-system}”
DB_PASSWORD=”${DB_PASSWORD:-}”
DB_CONNECT=”${DB_USER}/${DB_PASSWORD}@//dbserver:1521/ORCL”

# 日志函数
log() {
local level=”$1″
shift
local message=”$*”
local timestamp=$(date ‘+%Y-%m-%d %H:%M:%S’)

echo “[$timestamp] [$level] $message” | tee -a “$LOG_FILE”
}

# 错误处理
error_exit() {
log “ERROR” “$1”
cleanup
exit 1
}

# 清理函数
cleanup() {
rm -f “$LOCK_FILE”
log “INFO” “脚本清理完成”
}

# 检查锁
check_lock() {
if [ -f “$LOCK_FILE” ]; then
local pid=$(cat “$LOCK_FILE”)
if kill -0 “$pid” 2>/dev/null; then
error_exit “脚本已在运行 (PID: $pid)”
else
log “WARN” “发现僵尸锁文件,已清理”
rm -f “$LOCK_FILE”
fi
fi
echo $$ > “$LOCK_FILE”
}

# 检查依赖
check_dependencies() {
local deps=(“sqlplus” “nc” “gzip”)
for dep in “${deps[@]}”; do
if ! command -v “$dep” >/dev/null 2>&1; then
error_exit “缺少依赖: $dep”
fi
done
}

# 数据库连接测试
test_db_connection() {
log “INFO” “测试数据库连接…”

local temp_file=$(mktemp)
local exit_code=0

timeout 30 sqlplus -s “$DB_CONNECT” << EOF > “$temp_file” 2>&1
EXIT;
EOF

exit_code=$?

if [ $exit_code -ne 0 ]; then
cat “$temp_file” >> “$LOG_FILE”
rm -f “$temp_file”
error_exit “数据库连接测试失败”
fi

rm -f “$temp_file”
log “INFO” “数据库连接测试成功”
}

# 执行维护任务
run_maintenance() {
log “INFO” “开始执行维护任务”

# 1. 清理旧数据
log “INFO” “清理30天前的日志数据…”
execute_sql_safe ”
DELETE FROM application_log WHERE log_date < SYSDATE - 30; COMMIT; " > /dev/null

# 2. 收集统计信息
log “INFO” “收集统计信息…”
execute_sql_safe ”
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SCOTT’, estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
” > /dev/null

# 3. 检查表空间
log “INFO” “检查表空间使用情况…”
local ts_report=$(execute_sql_safe ”
SET PAGESIZE 50
SET LINESIZE 120
COLUMN tablespace_name FORMAT A20
COLUMN used_mb FORMAT 999,999
COLUMN free_mb FORMAT 999,999
COLUMN pct_used FORMAT 999.99

SELECT
a.tablespace_name,
ROUND(a.bytes_alloc / 1024 / 1024, 2) used_mb,
ROUND((a.bytes_alloc – nvl(b.bytes_free,0)) / 1024 / 1024, 2) free_mb,
ROUND((a.bytes_alloc – nvl(b.bytes_free,0)) / a.bytes_alloc * 100, 2) pct_used
FROM
(SELECT f.tablespace_name, SUM(f.bytes) bytes_alloc
FROM dba_data_files f GROUP BY f.tablespace_name) a,
(SELECT f.tablespace_name, SUM(f.bytes) bytes_free
FROM dba_free_space f GROUP BY f.tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+)
ORDER BY 4 DESC;
“)

log “INFO” “表空间报告:\n$ts_report”

# 4. 备份关键数据
log “INFO” “备份关键表数据…”
local backup_dir=”/backup/oracle/daily/$(date +%Y%m%d)”
mkdir -p “$backup_dir”

exp system/password@ORCL file=”$backup_dir/important_tables.dmp” tables=”(orders,customers)” log=”$backup_dir/backup.log”

if [ $? -eq 0 ]; then
gzip “$backup_dir/important_tables.dmp”
log “INFO” “备份完成: $backup_dir/important_tables.dmp.gz”
else
log “WARN” “备份失败,检查备份日志”
fi

log “INFO” “维护任务执行完成”
}

# 主函数
main() {
# 初始化
mkdir -p “$LOG_DIR”
log “INFO” “========== 脚本启动: $SCRIPT_NAME ==========”

# 检查
check_lock
check_dependencies

# 验证配置
if [ -z “$DB_PASSWORD” ]; then
error_exit “数据库密码未配置,请设置 DB_PASSWORD 环境变量”
fi

# 测试连接
test_db_connection

# 执行任务
run_maintenance

# 清理
cleanup

log “INFO” “========== 脚本成功结束 ==========”
}

# 错误捕获
trap ‘error_exit “脚本被中断”‘ INT TERM
trap ‘error_exit “发生未知错误”‘ ERR

# 脚本入口
if [[ “${BASH_SOURCE[0]}” == “${0}” ]]; then
main “$@”
fi
总结
通过Shell脚本调用SQLPlus执行Oracle数据库任务是一种高效、灵活的自动化方案。关键要点包括:
环境配置
:确保Oracle客户端正确安装和配置
安全连接
:使用Oracle钱包或加密方式管理凭证
错误处理
:建立完善的错误检测和重试机制
日志记录
:详细记录操作过程便于审计和排查问题
性能优化
:合理设置SQLPlus参数和并行处理
掌握这些技巧后,您可以构建出稳定、可靠的数据库自动化运维体系,大幅提升工作效率。
# Linux系统下如何通过Shell脚本高效调用SQLPlus执行Oracle数据库查询与维护任务并处理常见连接错误
引言:Shell脚本与SQLPlus的完美结合
在Linux系统管理中,自动化是提高效率的关键。当我们需要频繁与Oracle数据库交互时,通过Shell脚本调用SQLPlus成为了一种高效的解决方案。这种方法不仅可以减少人工操作,还能确保任务的定时执行和错误处理的自动化。本文将详细介绍如何在Linux环境下编写Shell脚本来调用SQLPlus执行查询和维护任务,并重点讲解如何处理常见的连接错误。
为什么选择Shell脚本+SQLPlus?
轻量级
:无需额外安装复杂的ETL工具
灵活性
:可以轻松整合Linux系统命令和Oracle操作
可维护性
:脚本易于理解和修改
原生支持
:Oracle客户端自带SQLPlus,无需额外组件
基础环境配置
1. Oracle客户端安装
首先确保系统已安装Oracle Instant Client或完整版Oracle客户端:
# 检查Oracle客户端是否安装
rpm -qa | grep oracle-instantclient

# 或者检查sqlplus命令是否存在
which sqlplus
2. 环境变量配置
配置必要的环境变量是成功连接数据库的前提:
# 编辑用户profile文件(如.bash_profile)
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/client_1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK # 防止中文乱码

# 使配置生效
source ~/.bash_profile
3. 测试基础连接
在编写脚本前,先手动测试连接:
sqlplus username/password@//hostname:port/service_name

# 示例
sqlplus scott/tiger@//dbserver:1521/ORCL
Shell脚本基础模板
1. 标准脚本结构
#!/bin/bash
# 脚本名称: oracle_query.sh
# 功能描述: 执行Oracle查询并处理结果
# 作者: Your Name
# 创建日期: 2023-01-01

# 设置错误处理
set -euo pipefail

# 日志函数
log() {
echo “[$(date ‘+%Y-%m-%d %H:%M:%S’)] $1”
}

# 错误处理函数
error_exit() {
log “ERROR: $1”
exit 1
}

# 主逻辑
main() {
log “脚本开始执行”
# 数据库操作将在这里进行
log “脚本执行完成”
}

# 脚本入口
main “$@”
2. 连接信息管理
安全建议
:不要在脚本中硬编码密码,推荐使用以下方法:
方法1:使用Oracle钱包(推荐)
# 配置Oracle钱包
mkdir -p $ORACLE_HOME/network/admin
cat > $ORACLE_HOME/network/admin/sqlnet.ora << EOF WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = $ORACLE_HOME/network/admin))) SQLNET.WALLET_OVERRIDE = TRUE SSL_CLIENT_AUTHENTICATION = FALSE SSL_VERSION = 0 EOF # 将密码存入钱包 mkstore -wrl $ORACLE_HOME/network/admin -createCredential ORCL_DB username password # 脚本中使用 sqlplus /@ORCL_DB 方法2:使用环境变量(开发环境适用) # 在脚本中读取环境变量 DB_USER=${DB_USER:-"scott"} DB_PASSWORD=${DB_PASSWORD:-"tiger"} DB_CONNECT="${DB_USER}/${DB_PASSWORD}@//dbserver:1521/ORCL" 方法3:使用加密的密码文件(生产环境适用) # 创建加密密码文件(仅限当前用户可读) echo "encrypted_password" > ~/.dbpass
chmod 600 ~/.dbpass

# 脚本中解密使用(需要安装openssl)
DB_PASSWORD=$(openssl enc -aes-256-cbc -d -in ~/.dbpass -pass pass:”your_key”)
执行SQL查询的多种方式
1. 直接执行SQL语句
#!/bin/bash

# 数据库连接信息
DB_CONNECT=”scott/tiger@//dbserver:1521/ORCL”

# 执行简单查询
execute_query() {
local sql=”$1″
sqlplus -s “$DB_CONNECT” << EOF SET PAGESIZE 0 SET FEEDBACK OFF SET VERIFY OFF SET HEADING OFF SET LINESIZE 1000 $sql EXIT; EOF } # 示例:查询员工数量 emp_count=$(execute_query "SELECT COUNT(*) FROM emp;") log "员工总数: $emp_count" 2. 执行SQL文件 #!/bin/bash # 执行SQL文件 execute_sql_file() { local sql_file="$1" local output_file="${2:-/tmp/sql_output.log}" sqlplus -s "$DB_CONNECT" @"$sql_file" > “$output_file” 2>&1

# 检查执行结果
if [ $? -eq 0 ]; then
log “SQL文件执行成功,输出保存到: $output_file”
return 0
else
error_exit “SQL文件执行失败,请检查日志: $output_file”
fi
}

# 示例SQL文件内容(保存为query.sql)
cat > query.sql << 'EOF' SET PAGESIZE 0 SET FEEDBACK OFF SET VERIFY OFF SET HEADING ON COLUMN ename FORMAT A20 COLUMN job FORMAT A15 SELECT ename, job, sal FROM emp WHERE deptno = 10; EXIT; EOF # 执行 execute_sql_file "query.sql" "result.log" 3. 使用HEREDOC动态生成SQL #!/bin/bash # 动态生成SQL并执行 dynamic_query() { local deptno="$1" local min_sal="$2" sqlplus -s "$DB_CONNECT" << EOF SET PAGESIZE 0 SET FEEDBACK OFF SET VERIFY OFF SET HEADING OFF -- 使用绑定变量防止SQL注入 VAR dept_id NUMBER; VAR min_salary NUMBER; EXEC :dept_id := $deptno; EXEC :min_salary := $min_sal; SELECT COUNT(*) FROM emp WHERE deptno = :dept_id AND sal > :min_salary;

EXIT;
EOF
}

# 调用示例
count=$(dynamic_query 10 2000)
log “部门10中薪资超过2000的员工数: $count”
4. 处理查询结果
方法1:直接捕获输出
result=$(execute_query “SELECT sysdate FROM dual;”)
log “当前数据库时间: $result”
方法2:处理多行结果
#!/bin/bash

# 处理多行结果
process_multi_rows() {
local sql=”SELECT ename, sal FROM emp WHERE deptno = 10″

# 使用awk处理结果
sqlplus -s “$DB_CONNECT” << EOF | awk '{print "员工:" $1 " 薪资:" $2}' SET PAGESIZE 0 SET FEEDBACK OFF SET HEADING OFF $sql EXIT; EOF } # 调用 process_multi_rows 方法3:将结果存入数组 #!/bin/bash # 将查询结果存入数组 readarray -t employees < <(execute_query "SELECT ename FROM emp;" | grep -v '^$') log "查询到的员工:" for emp in "${employees[@]}"; do log " - $emp" done 常见连接错误及处理方案 1. ORA-12154: TNS解析错误 错误原因 :无法解析连接描述符 解决方案 : #!/bin/bash # 检查TNS配置 check_tns() { local tns_admin="${TNS_ADMIN:-$ORACLE_HOME/network/admin}" if [ ! -f "$tns_admin/tnsnames.ora" ]; then error_exit "tnsnames.ora文件不存在: $tns_admin/tnsnames.ora" fi # 验证TNS条目 local connect_string="$1" local tns_entry=$(grep -i "^${connect_string##*@}" "$tns_admin/tnsnames.ora") if [ -z "$tns_entry" ]; then error_exit "TNS条目不存在: $connect_string" fi } # 使用完整连接字符串避免TNS解析 DB_CONNECT="scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))" 2. ORA-12514: TNS监听器未知服务 错误原因 :监听器不知道请求的服务名 解决方案 : #!/bin/bash # 检查监听器状态 check_listener() { local host="$1" local port="${2:-1521}" # 使用tnsping检查 if ! tnsping "$host:$port" >/dev/null 2>&1; then
error_exit “无法连接到监听器 $host:$port”
fi

# 检查服务是否注册
lsnrctl status << EOF | grep -i "Service.*$service_name" CONNECT $host:$port EOF } # 在脚本中调用 check_listener "dbserver" "1521" "ORCL" 3. ORA-01017: 无效的用户名/密码 错误原因 :凭证错误或账户锁定 解决方案 : #!/bin/bash # 测试连接并处理认证错误 test_connection() { local max_attempts=3 local attempt=1 while [ $attempt -le $max_attempts ]; do log "尝试连接数据库 (第 $attempt 次)..." # 使用临时文件捕获错误 local temp_file=$(mktemp) sqlplus -s "$DB_CONNECT" << EOF > “$temp_file” 2>&1
EXIT;
EOF

if grep -q “ORA-01017” “$temp_file”; then
log “认证失败,请检查用户名和密码”
# 可以在这里触发密码更新逻辑
return 1
elif grep -q “ORA-28000” “$temp_file”; then
log “账户已被锁定,请联系DBA解锁”
return 1
elif [ $? -eq 0 ]; then
log “连接成功”
rm -f “$temp_file”
return 0
fi

rm -f “$temp_file”
sleep 2
((attempt++))
done

error_exit “连接失败,已尝试 $max_attempts 次”
}

# 调用
if ! test_connection; then
# 发送告警邮件
echo “数据库连接失败” | mail -s “告警” admin@example.com
exit 1
fi
4. ORA-12541: 无监听器
错误原因
:监听器未启动或网络不通
解决方案
:
#!/bin/bash

# 检查网络连通性和监听器
check_network() {
local host=”$1″
local port=”$2″

# 检查网络连通性
if ! nc -z -w 5 “$host” “$port” 2>/dev/null; then
error_exit “无法连接到 $host:$port,网络不通或监听器未启动”
fi

# 检查监听器响应
if ! echo “quit” | nc “$host” “$port” | grep -q “OK”; then
error_exit “监听器未正常响应”
fi
}

# 在连接前检查
check_network “dbserver” “1521”
5. ORA-12537: 网络会话终止
错误原因
:网络问题或监听器配置限制
解决方案
:
#!/bin/bash

# 设置超时和重试
execute_with_retry() {
local sql=”$1″
local max_retries=3
local retry_delay=5

for ((i=1; i<=max_retries; i++)); do log "执行SQL (尝试 $i/$max_retries)" # 设置SQLPlus超时 timeout 60 sqlplus -s "$DB_CONNECT" << EOF SET SQLPROMPT "" SET TIME ON $sql EXIT; EOF local exit_code=$? if [ $exit_code -eq 0 ]; then return 0 elif [ $exit_code -eq 124 ]; then log "执行超时,等待重试..." sleep $retry_delay else log "执行失败,错误码: $exit_code" sleep $retry_delay fi done error_exit "SQL执行失败,已重试 $max_retries 次" } 6. 综合错误处理框架 #!/bin/bash # 综合错误处理 execute_sql_safe() { local sql="$1" local output_file="${2:-/tmp/sql_output_$(date +%s).log}" local timeout="${3:-60}" # 创建临时文件 local temp_output=$(mktemp) local exit_code=0 # 执行SQL并捕获所有输出 timeout $timeout sqlplus -s "$DB_CONNECT" << EOF > “$temp_output” 2>&1
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
WHENEVER SQLERROR EXIT SQL.SQLCODE
WHENEVER OSERROR EXIT OSERROR
$sql
EXIT;
EOF

exit_code=$?

# 分析错误
case $exit_code in
0)
log “SQL执行成功”
cp “$temp_output” “$output_file”
;;
124)
error_exit “SQL执行超时”
;;
*)
# 检查特定的Oracle错误
if grep -q “ORA-00001” “$temp_output”; then
error_exit “唯一约束冲突”
elif grep -q “ORA-02292” “$temp_output”; then
error_exit “子记录存在,无法删除”
elif grep -q “ORA-12154” “$temp_output”; then
error_exit “TNS解析错误,请检查连接字符串”
elif grep -q “ORA-12514” “$temp_output”; then
error_exit “服务名错误或监听器问题”
else
# 输出详细错误信息
cat “$temp_output” >&2
error_exit “SQL执行失败,退出码: $exit_code”
fi
;;
esac

# 清理临时文件
rm -f “$temp_output”

# 返回输出文件路径
echo “$output_file”
}
高级应用:维护任务自动化
1. 数据库备份脚本
#!/bin/bash

# 数据库导出脚本
backup_database() {
local backup_dir=”/backup/oracle/$(date +%Y%m%d)”
local log_file=”$backup_dir/backup.log”

mkdir -p “$backup_dir”

# 使用数据泵导出(需要配置)
expdp system/password@ORCL \
DIRECTORY=BACKUP_DIR \
DUMPFILE=backup_$(date +%Y%m%d_%H%M%S).dmp \
LOGFILE=export.log \
SCHEMAS=SCOTT

# 或者使用传统exp
exp system/password@ORCL file=”$backup_dir/scott.dmp” owner=scott log=”$log_file”

# 压缩备份
gzip “$backup_dir/*.dmp”

# 清理旧备份(保留7天)
find /backup/oracle -type f -name “*.dmp.gz” -mtime +7 -delete

log “备份完成: $backup_dir”
}
数据清理脚本
#!/bin/bash

# 清理历史数据
cleanup_old_data() {
local days_to_keep=90
local batch_size=1000

# 使用SQLPlus执行删除
sqlplus -s “$DB_CONNECT” << EOF SET SERVEROUTPUT ON DECLARE v_deleted NUMBER := 0; v_total NUMBER := 0; BEGIN LOOP DELETE FROM transaction_log WHERE transaction_date < SYSDATE - $days_to_keep AND ROWNUM <= $batch_size; v_deleted := SQL%ROWCOUNT; v_total := v_total + v_deleted; DBMS_OUTPUT.PUT_LINE('已删除: ' || v_deleted || ' 条记录'); EXIT WHEN v_deleted = 0; COMMIT; END LOOP; DBMS_OUTPUT.PUT_LINE('总共删除: ' || v_total || ' 条记录'); END; / EXIT; EOF } 3. 监控告警脚本 #!/bin/bash # 监控表空间使用率 check_tablespace() { local threshold=80 sqlplus -s "$DB_CONNECT" << EOF | while read -r tablespace used; do SET PAGESIZE 0 SET FEEDBACK OFF SET HEADING OFF SELECT tablespace_name, ROUND(used_percent, 2) FROM ( SELECT a.tablespace_name, ROUND((a.bytes - b.bytes) / a.bytes * 100, 2) used_percent FROM (SELECT tablespace_name, SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name ) WHERE used_percent > $threshold;
EXIT;
EOF
if [ -n “$tablespace” ] && [ -n “$used” ]; then
log “告警: 表空间 $tablespace 使用率达 ${used}%”
# 发送告警
echo “表空间 $tablespace 使用率达 ${used}%” | mail -s “数据库告警” dba@example.com
fi
done
}
性能优化与最佳实践
1. SQLPlus性能优化
#!/bin/bash

# 优化SQLPlus性能的设置
optimize_sqlplus() {
sqlplus -s “$DB_CONNECT” << EOF -- 禁用回显以提高性能 SET NOLOGFILE SET NOAPPEND SET ARRAYSIZE 1000 SET MAXDATA 60000 SET SQLPROMPT "" SET TIME OFF SET TIMING OFF -- 优化查询 ALTER SESSION SET SORT_AREA_SIZE = 10485760; ALTER SESSION SET HASH_AREA_SIZE = 20971520; -- 你的SQL语句 ... EXIT; EOF } 2. 并行执行任务 #!/bin/bash # 并行执行多个SQL任务 parallel_sql() { local tasks=( "SELECT COUNT(*) FROM table1" "SELECT COUNT(*) FROM table2" "SELECT COUNT(*) FROM table3" ) # 使用后台进程并行执行 for task in "${tasks[@]}"; do ( result=$(execute_query "$task") echo "$task: $result" >> /tmp/parallel_results.log
) &
done

# 等待所有后台任务完成
wait

log “并行任务完成,结果保存在 /tmp/parallel_results.log”
}
3. 日志与审计
#!/bin/bash

# 带审计的日志函数
audit_log() {
local message=”$1″
local log_file=”/var/log/oracle_scripts/audit_$(date +%Y%m%d).log”

mkdir -p “$(dirname “$log_file”)”

echo “$(date ‘+%Y-%m-%d %H:%M:%S’) | $USER | $0 | $message” >> “$log_file”

# 同时输出到标准输出
echo “$message”
}

# 使用示例
audit_log “开始执行数据库维护任务”
audit_log “任务完成,影响行数: $affected_rows”
完整的生产级脚本示例
#!/bin/bash
# 生产级Oracle数据库维护脚本
# 功能:每日自动执行数据库维护任务

set -euo pipefail

# 配置区域
readonly SCRIPT_NAME=$(basename “$0”)
readonly SCRIPT_DIR=$(cd “$(dirname “$0″)” && pwd)
readonly LOG_DIR=”/var/log/oracle_scripts”
readonly LOG_FILE=”$LOG_DIR/${SCRIPT_NAME%.*}_$(date +%Y%m%d).log”
readonly LOCK_FILE=”/tmp/${SCRIPT_NAME%.*}.lock”

# 数据库配置(建议从环境变量读取)
DB_USER=”${DB_USER:-system}”
DB_PASSWORD=”${DB_PASSWORD:-}”
DB_CONNECT=”${DB_USER}/${DB_PASSWORD}@//dbserver:1521/ORCL”

# 日志函数
log() {
local level=”$1″
shift
local message=”$*”
local timestamp=$(date ‘+%Y-%m-%d %H:%M:%S’)

echo “[$timestamp] [$level] $message” | tee -a “$LOG_FILE”
}

# 错误处理
error_exit() {
log “ERROR” “$1”
cleanup
exit 1
}

# 清理函数
cleanup() {
rm -f “$LOCK_FILE”
log “INFO” “脚本清理完成”
}

# 检查锁
check_lock() {
if [ -f “$LOCK_FILE” ]; then
local pid=$(cat “$LOCK_FILE”)
if kill -0 “$pid” 2>/dev/null; then
error_exit “脚本已在运行 (PID: $pid)”
else
log “WARN” “发现僵尸锁文件,已清理”
rm -f “$LOCK_FILE”
fi
fi
echo $$ > “$LOCK_FILE”
}

# 检查依赖
check_dependencies() {
local deps=(“sqlplus” “nc” “gzip”)
for dep in “${deps[@]}”; do
if ! command -v “$dep” >/dev/null 2>&1; then
error_exit “缺少依赖: $dep”
fi
done
}

# 数据库连接测试
test_db_connection() {
log “INFO” “测试数据库连接…”

local temp_file=$(mktemp)
local exit_code=0

timeout 30 sqlplus -s “$DB_CONNECT” << EOF > “$temp_file” 2>&1
EXIT;
EOF

exit_code=$?

if [ $exit_code -ne 0 ]; then
cat “$temp_file” >> “$LOG_FILE”
rm -f “$temp_file”
error_exit “数据库连接测试失败”
fi

rm -f “$temp_file”
log “INFO” “数据库连接测试成功”
}

# 执行维护任务
run_maintenance() {
log “INFO” “开始执行维护任务”

# 1. 清理旧数据
log “INFO” “清理30天前的日志数据…”
execute_sql_safe ”
DELETE FROM application_log WHERE log_date < SYSDATE - 30; COMMIT; " > /dev/null

# 2. 收集统计信息
log “INFO” “收集统计信息…”
execute_sql_safe ”
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SCOTT’, estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
” > /dev/null

# 3. 检查表空间
log “INFO” “检查表空间使用情况…”
local ts_report=$(execute_sql_safe ”
SET PAGESIZE 50
SET LINESIZE 120
COLUMN tablespace_name FORMAT A20
COLUMN used_mb FORMAT 999,999
COLUMN free_mb FORMAT 999,999
COLUMN pct_used FORMAT 999.99

SELECT
a.tablespace_name,
ROUND(a.bytes_alloc / 1024 / 1024, 2) used_mb,
ROUND((a.bytes_alloc – nvl(b.bytes_free,0)) / 1024 / 1024, 2) free_mb,
ROUND((a.bytes_alloc – nvl(b.bytes_free,0)) / a.bytes_alloc * 100, 2) pct_used
FROM
(SELECT f.tablespace_name, SUM(f.bytes) bytes_alloc
FROM dba_data_files f GROUP BY f.tablespace_name) a,
(SELECT f.tablespace_name, SUM(f.bytes) bytes_free
FROM dba_free_space f GROUP BY f.tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+)
ORDER BY 4 DESC;
“)

log “INFO” “表空间报告:\n$ts_report”

# 4. 备份关键数据
log “INFO” “备份关键表数据…”
local backup_dir=”/backup/oracle/daily/$(date +%Y%m%d)”
mkdir -p “$backup_dir”

exp system/password@ORCL file=”$backup_dir/important_tables.dmp” tables=”(orders,customers)” log=”$backup_dir/backup.log”

if [ $? -eq 0 ]; then
gzip “$backup_dir/important_tables.dmp”
log “INFO” “备份完成: $backup_dir/important_tables.dmp.gz”
else
log “WARN” “备份失败,检查备份日志”
fi

log “INFO” “维护任务执行完成”
}

# 主函数
main() {
# 初始化
mkdir -p “$LOG_DIR”
log “INFO” “========== 脚本启动: $SCRIPT_NAME ==========”

# 检查
check_lock
check_dependencies

# 验证配置
if [ -z “$DB_PASSWORD” ]; then
error_exit “数据库密码未配置,请设置 DB_PASSWORD 环境变量”
fi

# 测试连接
test_db_connection

# 执行任务
run_maintenance

# 清理
cleanup

log “INFO” “========== 脚本成功结束 ==========”
}

# 错误捕获
trap ‘error_exit “脚本被中断”‘ INT TERM
trap ‘error_exit “发生未知错误”‘ ERR

# 脚本入口
if [[ “${BASH_SOURCE[0]}” == “${0}” ]]; then
main “$@”
fi
总结
通过Shell脚本调用SQLPlus执行Oracle数据库任务是一种高效、灵活的自动化方案。关键要点包括:
环境配置
:确保Oracle客户端正确安装和配置
安全连接
:使用Oracle钱包或加密方式管理凭证
错误处理
:建立完善的错误检测和重试机制
日志记录
:详细记录操作过程便于审计和排查问题
性能优化
:合理设置SQLPlus参数和并行处理
掌握这些技巧后,您可以构建出稳定、可靠的数据库自动化运维体系,大幅提升工作效率。