#!/bin/bash
# daily_maintenance.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`
# 检查数据库状态
check_db_status() {
status=$(disql SYSDBA/SYSDBA << EOF select status from vinstance; exit; EOF ) echo "数据库状态: $status" } # 检查表空间使用情况 check_tablespace() { echo "表空间使用情况:" disql SYSDBA/SYSDBA << EOF select tablespace_name, round(total_space/1024/1024, 2) as total_mb, round(used_space/1024/1024, 2) as used_mb, round((used_space/total_space)*100, 2) as usage_percent from v\$tablespace; exit; EOF } # 检查会话情况 check_sessions() { echo "活跃会话情况:" disql SYSDBA/SYSDBA << EOF select count(*) as active_sessions from v\$session where state = 'ACTIVE'; exit; EOF } # 执行维护操作 echo "开始日常维护操作..." check_db_status check_tablespace check_sessions echo "日常维护操作完成!"

# 启动数据库实例
[dmdba@fgedu ~]$ /dm/app/bin/dmserver /dm/fgdata/fgedudb/dm.ini &
# 查看数据库状态
[dmdba@fgedu ~]$ disql SYSDBA/SYSDBA
SQL> select status from vinstance;
STATUS
—————————-
OPEN
# 输出信息
1 rows got

# 正常关闭数据库
[dmdba@fgedu ~]$ disql SYSDBA/SYSDBA
SQL> shutdown normal;
# 输出信息
操作已执行
# 查看数据库状态
[dmdba@fgedu ~]$ ps -ef | grep dmserver

# 创建用户
SQL> create user fgedu identified by “fgedu123”;
# 输出信息
操作已执行
# 授予权限
SQL> grant resource, connect to fgedu;
# 输出信息
操作已执行

# 修改用户密码
SQL> alter user fgedu identified by “fgedu456”;
# 输出信息
操作已执行 风哥提示：

# 查看系统负载
[dmdba@fgedu ~]$ top
# 输出信息
top – 10:00:00 up 10 days, 2:00, 2 users, load average: 0.50, 0.40, 0.30
Tasks: 100 total, 1 running, 99 sleeping, 0 stopped, 0 zombie
%Cpu(s): 5.0 us, 2.0 sy, 0.0 ni, 93.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
MiB Mem : 16384.0 total, 8192.0 free, 4096.0 used, 4096.0 buff/cache
MiB Swap: 4096.0 total, 4096.0 free, 0.0 used. 11264.0 avail Mem

# 查看数据库性能指标
SQL> select * from v\$sysstat where name in (‘physical reads’, ‘physical writes’, ‘logical reads’);
NAME VALUE
——————– ———–
physical reads 1000
physical writes 500
logical reads 10000
# 输出信息
3 rows got

4.4 空间管理与维护

空间管理与维护是确保数据库系统正常运行的重要环节，以下是具体的操作步骤：

4.4.1 查看表空间使用情况

学习交流加群风哥微信: itpux-com

# 查看表空间使用情况
SQL> select tablespace_name, round(total_space/1024/1024, 2) as total_mb,
round(used_space/1024/1024, 2) as used_mb,
round((used_space/total_space)*100, 2) as usage_percent
from v\$tablespace;
TABLESPACE_NAME TOTAL_MB USED_MB USAGE_PERCENT
———————— ———– ———– ————
SYSTEM 1024.00 512.00 50.00
ROLL 512.00 256.00 50.00
TEMP 512.00 128.00 25.00
MAIN 2048.00 1024.00 50.00
FGEDUTBS 1024.00 512.00 50.00
# 输出信息
5 rows got

# 扩展表空间
SQL> alter tablespace fgedutbs add datafile ‘/dm/fgdata/fgedudb/fgedutbs02.dbf’ size 1024M;
# 输出信息
操作已执行
# 查看扩展后的表空间
SQL> select tablespace_name, round(total_space/1024/1024, 2) as total_mb
from v\$tablespace where tablespace_name = ‘FGEDUTBS’;
TABLESPACE_NAME TOTAL_MB
———————— ———–
FGEDUTBS 2048.00
# 输出信息
1 rows got