自动化脚本编写：批量访问Oracle数据库并导出用户信息为 Excel

本文主要写批量访问数据库的脚本编写和其中遇到的一点点小问题“连接不上数据库”

Google 的运维被称作 SRE SRE 的全称是 Site Reliability Engineer;, 这个职位其实是 Google 首创发明的。Goole称一个好的运维团队，应该首先是一批好的开发工程师，当软件系统变得越来越复杂时，主动承担起架构调整，系统调优，自动化等工作来帮助降低系统复杂度，优化系统架构，提高系统可用性;

其中 SRE 有几个关键点：

一是提高工程化运维。不仅仅是监控系统、处理故障，更重要的是通过编写代码和自动化工具来提升系统的可靠性。这种工程化的思维方式使得运维工作变得更具前瞻性和主动性。

二是早期介入开发项目。在优秀的项目中，SRE 团队通常会在项目开发的早期就介入。这种做法能够确保在系统设计阶段就考虑到运维的需求，避免了后期对系统进行重大调整，毕竟开发阶段只占到了项目周期的20%，绝大多是是需要运维来维护。内部经验表明，成功的项目通常都在早期引入了 SRE 团队，这样可以在系统上线之前就优化其可靠性和性能。

三是根本原因分析。SRE 团队特别重视根本原因分析（Root Cause Analysis）。在故障发生时，SRE 会深入分析问题的根源，而不仅仅是解决表面症状。这种方法可以帮助团队从根本上改进系统，防止类似问题的再次发生。

四是Walk the Plank。“Walk the plank” 是 SRE 团队常用的一种实践方式，即鼓励团队成员主动走出舒适区，探索新的技术解决方案和实践方法。这种精神有助于推动系统的不断进步和创新。

上个月公司要求统计在维的Oracle数据库中用户的信息，这么多节点和用户我们逐一去统计，不仅做一些重复性的劳动没有价值，而且也非常浪费时间，也很容易出错。为了提高效率并减少错误,今天从自动化运维的角度出发，尝试编写一个批量访问数据库的脚本。

一、脚本编写

目标： 编写一个批量访问数据库的脚本，获取 Oracle 数据库中的用户信息，并将其导出为 Excel 文件。

1.需求分析

• 远程访问数据库: 批量访问多个数据库节点，获取其中的用户信息。
• 记录日志: 记录每次访问的日志信息，包括成功与失败的数据库连接，便于排查问题。
• 导出为excel: 将获取到的用户信息整理并导出为 Excel 格式，以便后续的分析和使用。

2.技术选型

• Shell: 用于操作 Linux 环境，编写批量访问数据库的主脚本，调度 SQL 查询操作。
• SQL: 用于操作 Oracle 数据库，查询用户信息。
• Python: 用于处理 CSV 文件并将其转换为 Excel 格式，Python 中的 pandas 库能够方便地实现这一转换。

3.写代码

1）初始代码

通过 sqlplus 工具批量访问多个数据库，执行 SQL 查询，获取用户信息并导出为 CSV 文件

db_config.txt：数据库连接信息，每行放一个需要访问数据库的信息，

格式为：IP:PORT:SID:USERNAME:PASSWORD

用一个单机11g和RAC19c一个节点测试，配置如下

192.168.66.101:1521:ORCL:SYS:oracle
192.168.66.141:1521:ORCL:SYS:oracle

一般来说访问用户密码都是一样的，Ctrl+D 改一下IP和SID就可以，Todo：支持IP段？

脚本 serch_db.sh：

#!/bin/bash
echo "####################################################################################"
echo "##Function   	: Oracle 11g/12c/18c/19c(Single and Rac) install on Linux 6/7/8"
echo "####################################################################################"
echo "#执行脚本前："
echo "#    1. 把脚本、db_config.txt创建并配置好
echo "#    2. db_config.txt 每行放一个需要访问数据库的信息
echo "#    3. db_config.txt 格式为：IP:PORT:SID:USERNAME:PASSWORD
echo "####################################################################################"
####################################################################################
# author: codepanda
# porpose: 批量访问数据库并导出用户信息为 CSV
####################################################################################

# 配置文件路径
CONFIG_FILE="db_config.txt"
OUTPUT_FILE="db_info.csv"
LOG_FILE="db.log"

# 清空日志文件
> $LOG_FILE

# 添加 CSV 表头
echo "USERNAME,ACCOUNT_STATUS,PROFILE,EXPIRY_DATE,CREATED" >> $OUTPUT_FILE

# 并发处理的后台任务数组
declare -a PIDS

# 读取配置文件中的每一行
while IFS=: read -r IP PORT SID USERNAME PASSWORD; do
    echo "Processing $IP..." | tee -a $LOG_FILE

    # 生成临时SQL文件
    SQL_FILE=$(mktemp --suffix=".sql")
    cat <<EOF > $SQL_FILE
SET PAGESIZE 50000
SET LINESIZE 200
SET FEEDBACK OFF
SET ECHO OFF

-- 设置列分隔符为逗号，生成CSV格式
SET COLSEP ","
SPOOL account_info_$IP.csv

SELECT 
   USERNAME,
   ACCOUNT_STATUS,
   PROFILE,
   TO_CHAR(EXPIRY_DATE, 'YYYY-MM-DD') AS EXPIRY_DATE,
   TO_CHAR(CREATED, 'YYYY-MM-DD') AS CREATED
FROM 
    DBA_USERS;

SPOOL OFF
EXIT;
EOF

    # 检查数据库连接
    CONNECTION_TEST=$(sqlplus -s "$USERNAME/$PASSWORD@$IP:$PORT/$SID" as sysdba <<EOF
EXIT;
EOF
)
    if [[ "$CONNECTION_TEST" == *"ORA-"* ]]; then
        echo "Connection failed for $IP:$PORT/$SID" | tee -a $LOG_FILE
        rm $SQL_FILE
        continue
    fi

    # 连接到Oracle数据库并执行SQL脚本
    ( sqlplus -s "$USERNAME/$PASSWORD@$IP:$PORT/$SID" as sysdba @"$SQL_FILE" > /dev/null 2>>$LOG_FILE &&

    # 检查是否生成了结果文件，并追加结果到输出文件
    if [ -f "account_info_$IP.csv" ]; then
        tail -n +2 account_info_$IP.csv >> $OUTPUT_FILE
        rm account_info_$IP.csv
        echo "Data retrieved for $IP" | tee -a $LOG_FILE
    else
        echo "Failed to retrieve data for $IP" | tee -a $LOG_FILE
    fi

    # 删除临时SQL文件
    rm $SQL_FILE ) &

    # 保存后台任务的PID
    PIDS+=($!)

done < $CONFIG_FILE

# 等待所有后台任务完成
for PID in "${PIDS[@]}"; do
    wait $PID
done

echo "Account information collection completed. Results saved in $OUTPUT_FILE." | tee -a $LOG_FILE

执行脚本：`sh serch_dbinfo.sh`

查看输出结果发现每个数据库都带着字段信息，还需要自己手动调整，而且排列也不是很整齐，给人的视觉不是很好，作为一个懒蛋是不允许这样的事情出现的，继续优化。

初始效果图：

目前的问题：

1. 再次执行脚本查询信息，会追加到输出文件里，和前面的重复了，而且也不知道查询的哪个数据库的用户信息

> $OUTPUT_FILE 每次查询时清空输出文件

至于数据库信息，去v$instance视图里拿就好了，记得加上转义字符。

SELECT instance_number, instance_name,host_name version,startup_time,status,database_type from v$instance;

如果不要数据库信息和列名，可以设置成 set heading off;

1. 字段排列参差不齐

通过oracle中sql*plus内置功能就可以实现这里有不知道的吗，博主也是刚刚学习Oracle知识，做一个扩展吧（文章补充知识点，见最后）

扩展一：Oracle中sql*plus命令

1. 有些字段在不同数据库的版本名字可能不同或者不存在，需要通过动态SQL和条件判断来处理这种情况 。

去v$instance拿到数据库版本信息，加一段判断数据库版本的逻辑

2）优化后的版本：

自动化脚本`serch_dbinfo.sh`会读取配置文件 db_config.txt 中的多个数据库节点信息，逐一访问并获取用户数据，获取的用户信息将会汇总到 CSV 文件中，将每次数据库访问的结果记录在日志文件 db.log 中，便于后续查看执行结果和排查故障。

#!/bin/bash
echo "####################################################################################"
echo "#执行脚本前："
echo "#    1. 把脚本、db_config.txt创建并配置好"
echo "#    2. db_config.txt 每行放一个需要访问数据库的信息"
echo "#    3. db_config.txt 格式为：IP:PORT:SID:USERNAME:PASSWORD"
echo "####################################################################################"
####################################################################################
# author: codepanda
# porpose: 批量访问数据库并导出用户信息为 CSV
####################################################################################

# 配置文件路径
CONFIG_FILE="db_config.txt"
OUTPUT_FILE="db_info.csv"
LOG_FILE="db.log"

# 清空日志文件，但保留输出文件（如果已经存在）
> $LOG_FILE
> $OUTPUT_FILE

# 如果输出文件不存在，则添加 CSV 表头
if [ ! -f "$OUTPUT_FILE" ]; then
    echo "USERNAME,ACCOUNT_STATUS,PROFILE,EXPIRY_DATE,CREATED,LAST_LOGIN" >> $OUTPUT_FILE
fi

# 并发处理的后台任务数组
declare -a PIDS

# 读取配置文件中的每一行
while IFS=: read -r IP PORT SID USERNAME PASSWORD; do
    echo "Processing $IP..." | tee -a $LOG_FILE

    # 生成临时SQL文件
    SQL_FILE=$(mktemp --suffix=".sql")

    # 获取数据库版本
    DB_VERSION=$(sqlplus -s "$USERNAME/$PASSWORD@$IP:$PORT/$SID" as sysdba <<EOF
SET FEEDBACK OFF;
SET HEADING OFF;
SELECT version FROM v\$instance;
EXIT;
EOF
)


    # 根据数据库版本生成不同的SQL语句
    if [[ "$DB_VERSION" == *"19"* ]]; then
        # 19c版本数据库，包含LAST_LOGIN字段
        cat <<EOF > $SQL_FILE
SET PAGESIZE 50000
SET LINESIZE 200
SET FEEDBACK OFF
SET ECHO OFF
col USERNAME for a28
col ACCOUNT_STATUS for a20
col PROFILE for a20
col EXPIRY_DATE for a20
col CREATED for a20
col LAST_LOGIN for a50

SPOOL account_info_$IP.txt

SELECT instance_number, instance_name,host_name version,startup_time,status,database_type from v\$instance;

SELECT
   USERNAME,
   ACCOUNT_STATUS,
   PROFILE,
   EXPIRY_DATE,
   CREATED,
   DECODE(LAST_LOGIN, NULL,'0', LAST_LOGIN) AS LAST_LOGIN
FROM
    DBA_USERS;

	
	
	
	
SPOOL OFF
EXIT;
EOF
    else
        # 11g版本数据库，没有LAST_LOGIN字段
        cat <<EOF > $SQL_FILE
SET PAGESIZE 50000
SET LINESIZE 200
SET FEEDBACK OFF
SET ECHO OFF
col USERNAME for a28
col ACCOUNT_STATUS for a20
col PROFILE for a20
col EXPIRY_DATE for a20
col CREATED for a20

SPOOL account_info_$IP.txt

SELECT instance_number, instance_name,host_name version,startup_time,status from v\$instance;

SELECT
   USERNAME,
   ACCOUNT_STATUS,
   PROFILE,
   EXPIRY_DATE,
   CREATED
FROM
    DBA_USERS;

SPOOL OFF
EXIT;
EOF
    fi

    # 检查数据库连接
    CONNECTION_TEST=$(sqlplus -s "$USERNAME/$PASSWORD@$IP:$PORT/$SID" as sysdba <<EOF
EXIT;
EOF
)
    if [[ "$CONNECTION_TEST" == *"ORA-"* ]]; then
        echo "Connection failed for $IP:$PORT/$SID" | tee -a $LOG_FILE
        rm $SQL_FILE
        continue
    fi

    # 连接到Oracle数据库并执行SQL脚本
    ( sqlplus -s "$USERNAME/$PASSWORD@$IP:$PORT/$SID" as sysdba @"$SQL_FILE" > /dev/null 2>>$LOG_FILE &&

    # 检查是否生成了结果文件，并追加结果到输出文件
    if [ -f "account_info_$IP.txt" ]; then
        tail -n +2 account_info_$IP.txt >> $OUTPUT_FILE
        rm account_info_$IP.txt
        echo "Data retrieved for $IP" | tee -a $LOG_FILE
    else
        echo "Failed to retrieve data for $IP" | tee -a $LOG_FILE
    fi

    # 删除临时SQL文件
    rm $SQL_FILE ) &

    # 保存后台任务的PID
    PIDS+=($!)

done < $CONFIG_FILE

# 等待所有后台任务完成
for PID in "${PIDS[@]}"; do
    wait $PID
done

echo "Account information collection completed. Results saved in $OUTPUT_FILE." | tee -a $LOG_FILE
# 注：在脚本前后加上 `set -x` ` set +x` 可以进行shell脚本的调试

输出结果：

CSV文件也稍微顺眼点了：

3）转化成excel

可以调用 Python 脚本将 CSV 文件转换为 Excel 文件，调用 pandas 包读文件 to_excel函数转换一下

这里就不演示了，方法还有很多，也可以自己手工将CSV另存为Excel.

4.优化点/扩展点

1. ⭐复用代码、提取公共逻辑

其中有很多地方都可以复用，提取为模板方法，Shell不是很熟悉，要不可以把公共方法作为一个类或者方法提取出来，包括其中的核心，批量访问数据库？既然批量访问数据库有了，那后续把SQL查询信息改一下，不就变成批量访问数据库并导出XXX信息为 Excel，

能访问数据库了，那批量访问Linux呢？可不可以通过 SSH 来执行命令并收集信息。

1. 异常处理机制： 数据库连接失败、SQL 查询失败、文件操作失败怎么捕获，怎么处理 （trap 命令捕获错误，或者使用 $? 来检查上一条命令的执行状态 ）
2. 日志记录，SQL 查询日志、执行时间、错误的栈追踪信息

一个好的项目或脚本异常处理器和日志记录是要完善的，能更方便快速定位问题，或者封装内部错误，返回脱敏的信息。

        3.能不能支持多种类型数据库，是不是把访问数据库的方式换一下？这个能封装成一个静态工厂或者策略模式 ， 根据不同的数据库类型，动态创建相应的数据库连接实例

二、监听故障

昨天刚刚学完关于监听的故障排查，今天就遇到了，虽然有些地方掌握的不是很熟悉，包括日志分析、日志跟踪也没有实践过，但是跟着昨天的方法论走还是在5分钟内解决了。

具体流程参考上一篇文章中第五段部分关于"总结 — Oracle 客户端无法连接之监听的分析流程"

“LOCAL_LISTENER”参数导致业务无法连接数据库，文末附Oracle连接故障检查的排查流程思维导图 - 墨天轮

问题背景

在执行上述脚本时，把第一个库信息查出来之后，访问第二个数据库信息报错，报错信息如下：

SYS			       OPEN				DEFAULT 					  24-AUG-13
MGMT_VIEW		       EXPIRED & LOCKED 		DEFAULT 		       24-AUG-13	  24-AUG-13
OUTLN			       EXPIRED & LOCKED 		DEFAULT 		       24-AUG-13	  24-AUG-13
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Processing 192.168.66.141...

SQL*Plus: Release 11.2.0.4.0 Production on Sun Aug 18 08:54:51 2024

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-12541: TNS:no listener

就是说，在尝试连接第二个库时，ORA-12541: TNS:no listener，

问题排查

检查监听状态信息

[oracle@rac1:/home/oracle]$ 
[oracle@rac1:/home/oracle]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 18-AUG-2024 08:55:28

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused

ORA-12541: TNS:no listener是Oracle数据库中的一个常见错误，它表示客户端无法连接到数据库服务器。这个错误通常出现在试图使用Oracle客户端连接数据库时，但无法找到可用的监听器。

查看一下local_listener

SQL> show parameter list

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
forward_listener		     string
listener_networks		     string
local_listener			     string	  (ADDRESS=(PROTOCOL=TCP)(HOST=
						 192.168.66.143)(PORT=1521))
remote_listener 		     string	  racscan1:1521

发现问题了，我在脚本里访问的是public IP 141，这里只注册了vip 143

[oracle@rac1:/home/oracle]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
#public ip
192.168.66.141  rac1
192.168.66.142  rac2
#private ip
192.168.5.141 rac1priv
192.168.5.142 rac2priv

这套19c rac环境装完还没怎么用过，不知道为什么会注册到vip 143上

解决方案

将public IP和VIP都注册进去

SQL> alter system set local_listener=' (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.66.141)(PORT=1521)),(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.66.143)(PORT=1521))';
System altered.
SQL> show parameter list;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
forward_listener		     string
listener_networks		     string
local_listener			     string	  (ADDRESS=(PROTOCOL=TCP)(HOST=
						 192.168.66.141)(PORT=1521)),(A
						 DDRESS=(PROTOCOL=TCP)(HOST=192
						 .168.66.143)(PORT=1521))
remote_listener 		     string	  racscan1:1521

没有服务，我们手工注册一下alter system register;

[oracle@rac1:/home/oracle]$ lsnrctl start

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 18-AUG-2024 08:59:28

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/19.3.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Log messages written to /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                18-AUG-2024 08:59:28
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1521)))
The listener supports no services
The command completed successfully

SQL> alter system register;
System altered.

监听起来了

[oracle@rac1:/home/oracle]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 18-AUG-2024 09:00:03

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                18-AUG-2024 08:59:28
Uptime                    0 days 0 hr. 0 min. 34 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=rac1)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/19.3.0/dbhome_1/admin/orcl/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "146683feeb830d91e0638e42a8c0ed56" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "pdb" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac1:/home/oracle]$ 

最后脚本执行成功，将两个库的信息都统计出来了。

上面是11g的，下面是19c的信息，成功连进去了。

扩展一：Oracle中常用的sql*plus命令

1. 执行脚本

   • @脚本名：执行指定脚本。脚本的路径可以是相对路径或绝对路径。

   • @@脚本名：执行嵌套脚本（当脚本位于当前脚本所在的目录时使用）。

2. 执行操作系统命令

   • ! 操作系统命令：在 SQL*Plus 中执行操作系统命令。例如 ! ls 在 Unix 系统下列出目录内容。

3. 执行上一条SQL命令

   • /：重复执行上一条SQL命令。

4. 登录到数据库

   • sqlplus 用户名/密码@数据库名：登录到指定数据库。

   • 不指定数据库名则默认连接到本地数据库，/ 表示连接到本地数据库，使用OS身份验证。

5. 输出重定向

   • spool 1.log;：将显示的内容输出到 1.log 文件中。

   • spool off;：关闭输出重定向，结束 spool 操作。如果不关闭 spool off，不会生成日志文件。

6. 查看表结构

   • desc 表名;：查看表的结构信息，包括列名、数据类型等。

格式化输出

1. 列格式化

   • col 字段 format a60;：将某一列的字符类型格式化为60个字符宽度。

   • col 字段 format 9999;：格式化数字字段。如果数字宽度超出指定范围，可能会显示为 ####，需要根据实际数据调整格式宽度。

2. 列别名

   • col 字段 heading 别名;：为列设置别名，以便在输出时显示自定义的列头。

3. 控制表头显示

   • set heading off;：关闭表头显示，不显示列名。

   • set heading on;：打开表头显示，显示列名。

4. 控制行宽

   • set linesize 200;：设置每行的最大字符数，使得输出更加整齐，避免内容换行。

5. 控制页面大小

   • set pagesize 200;：设置每页最大显示行数，避免分页过多干扰查看结果。

时间与性能显示

1. 显示SQL执行时间

   • set timing on;：开启 SQL 语句的执行时间显示。

   • set timing off;：关闭 SQL 语句的执行时间显示。

2. 显示系统时间

   • set time on;：开启系统时间显示。

   • set time off;：关闭系统时间显示。

显示环境变量及数据库信息

1. 显示当前环境变量

   • show all;：显示当前所有环境变量的值，如 linesize, pagesize, timing 等。

2. 显示错误信息

   • show error;：显示上次操作的错误信息，用于调试SQL或PL/SQL代码。

3. 显示数据库参数

   • show parameter;：显示数据库的所有初始化参数。

   • show parameter undo;：显示与 undo 相关的数据库参数。

4. 显示数据库版本

   • show rel;：显示 Oracle 数据库版本信息。

5. 显示 SGA（System Global Area）信息

   • show sga;：显示 SGA 的大小信息，用于了解数据库内存分配情况。

6. 显示当前用户

   • show user;：显示当前会话所使用的用户名。

SQL*Plus还内置了很多函数，这里只列出来了比较常见的.

​---------------------------------------------------------------------------------------------------------------------------------

                                                                                                                欢迎关注作者

                                                                                                                           公众号：panda博客