数据库教程FGMT06‑Oracle数据库日常管理维护操作 ## 前言 数据库部署完成只是工作的起点，绝大多数DBA的日常工作集中在标准化运维管理。我是风哥，在大量项目落地过程中，很多重大业务故障并非由软件BUG引发，而是源于日常巡检缺失、参数基线漂移、表空间耗尽、长事务锁阻塞、告警日志报错长期无人处置，小隐患逐步演变为生产停机事故。风哥 itpux-com 本文基于Oracle19c单机非CDB数据库，硬件规格为**单节点64G内存、8CPU**，数据库实例与数据库名称`fgedudb`，业务操作用户`fgedu`，本地软件路径全部统一替换为`/fgedudb`。教程完整覆盖实例生命周期管理、spfile/pfile参数管理、存储管理（表空间、UNDO、REDO、归档、FRA快速恢复区）、用户权限Profile资源管控、会话锁与等待事件排查、告警日志分析、AWR/ASH性能诊断、闪回回收站技术，配套大量可落地Shell、SQL实战命令，建立完整标准化数据库运维工作流程，为后续备份恢复、迁移、集群运维打下坚实基础。 **内容大纲：** 1. Oracle标准化运维工作体系介绍，64G/8CPU实例基线参数说明 2. 理论部分：实例启停三阶段、四种关闭模式、spfile/pfile参数原理；存储层表空间、undo、redo、归档、FRA原理；用户、角色、Profile资源限制；会话锁、等待事件；告警日志、AWR/ASH性能工具；闪回回收站技术原理，网上搜索风哥教程可以学习全套数据库教程 3. 实战操作：实例启停、参数文件维护、表空间/undo/temp/redo/归档全套管理；用户角色Profile管理；会话阻塞锁排查；告警日志定位；AWR/ASH报告生成；闪回回收站操作；完整自动化巡检Shell脚本编写 4. 生产运维风险点说明、变更管控要点 5. 全文总结，数据库日常运维最佳实践 ## 一、Oracle数据库日常管理维护基础理论 ### 1.1 Oracle标准化运维工作体系 标准化Oracle运维分为五大模块：例行巡检、变更管控、故障处置、性能监控、数据保护。 - **例行巡检**：实例状态、表空间使用率、归档与FRA快速恢复区、告警日志异常ORA报错、会话与锁、参数基线比对、失效对象检查； - **变更管控**：参数修改、DDL对象变更、账号权限调整，变更必须在测试环境完成验证，预留业务变更窗口； - **故障处置**：依托告警日志、动态性能视图快速定位故障根因； - **性能监控**：借助AWR、ASH、等待事件分析数据库性能瓶颈； - **数据保护**：归档、undo、闪回、备份策略协同保障数据安全。风哥教程 113257174 > > 基线硬件规格64G内存、8CPU，实例`fgedudb`关键spfile参数基线： > > > | 参数名称 | 参数值 | 参数说明 | > | --- | --- | --- | > | memory_target | 48G | 实例总内存，预留16G内存供操作系统、后台进程使用 | > | processes | 2000 | 最大并发进程数，支撑业务大量并发会话 | > | open_cursors | 500 | 单会话最大打开游标，规避游标泄露ORA‑01000 | > | session_cached_cursors | 300 | 会话游标缓存，降低SQL软解析CPU开销 | > | undo_retention | 900 | UNDO数据最小保留时间，单位秒，保障一致性读、闪回查询 | > | parallel_max_servers | 16 | 并行执行进程上限，适配8CPU硬件 | > | db_recovery_file_dest_size | 30G | FRA快速恢复区总容量，存放归档、备份、闪回日志 | > | recyclebin | ON | 回收站功能开启，支持DROP对象闪回恢复 | ### 1.2 实例启停与参数文件理论 Oracle实例启动分为三个严格阶段： 1. **NOMOUNT阶段**：读取参数文件spfile/pfile，分配SGA内存，拉起所有后台进程，**不访问控制文件**，仅用于重建控制文件场景； 2. **MOUNT阶段**：读取控制文件，加载数据文件、重做日志元数据，不打开业务数据，适合开启/关闭归档、介质恢复操作； 3. **OPEN阶段**：打开全部数据文件与联机重做日志，数据库对外提供业务访问。 数据库四种关闭模式： 1. `SHUTDOWN IMMEDIATE`：**生产标准关闭方式**，拒绝新连接，回滚未提交事务，干净一致性关闭实例； 2. `SHUTDOWN NORMAL`：等待所有用户主动断开会话，生产极少使用； 3. `SHUTDOWN TRANSACTIONAL`：等待现有事务提交完成后断开会话； 4. `SHUTDOWN ABORT`：强制终止实例，不回滚事务，下次启动触发实例崩溃恢复，**仅限紧急故障场景，禁止日常维护使用**。 参数文件分为两类： 1. **SPFILE**：二进制服务器参数文件，生产环境首选，通过`ALTER SYSTEM`修改，禁止vi直接编辑二进制文件； 2. **PFILE**：文本格式参数文件，多用于实例应急启动，可手动编辑。 可以互相转换：`CREATE PFILE FROM SPFILE`、`CREATE SPFILE FROM PFILE`。风哥数据库教程 itpux-com ### 1.3 存储层运维理论：表空间、UNDO、REDO、归档、FRA 1. **表空间**：Oracle逻辑存储容器，是数据文件的上层封装，分为持续表空间、UNDO回滚表空间、TEMP临时表空间；需要持续监控使用率，设置合理自动扩展上限，防止磁盘耗尽业务中断。 2. **UNDO回滚表空间**：保存事务修改前旧版本数据，支撑事务回滚、多版本一致性读、闪回查询；出现`ORA‑01555快照过旧`、`ORA‑30036`无法扩展undo错误，需要扩容undo表空间。 3. **REDO联机重做日志**：记录全部DML变更，实例崩溃恢复核心；循环复用，生产至少配置3组，每组2‑4G；频繁日志切换会产生大量`log file sync`等待事件。 4. **ARCHIVELOG归档模式**：联机日志切换时生成归档日志，介质恢复、ADG容灾依赖归档；生产库必须开启归档，归档磁盘占满会直接挂起全部DML业务。NOARCHIVELOG非归档模式仅用于测试环境。 5. **FRA快速恢复区**：集中存放归档日志、控制文件自动备份、闪回日志；必须监控使用率，空间到达阈值会停止归档生成。 ### 1.4 用户、角色、Profile资源管控理论 - 用户是数据库访问账号；权限分为系统权限、对象权限；角色是权限集合，简化批量授权操作； - Profile资源配置文件：限制会话CPU、IO、会话连接时长、密码有效期、密码错误锁定策略；生产遵循最小权限原则，业务账号严禁直接授予DBA角色； - 账号状态分为OPEN、LOCKED、EXPIRED，需要定期巡检过期锁定业务账号。 ### 1.5 会话、锁、等待事件理论 `V$SESSION`动态视图记录数据库全部会话信息；DML操作产生行级锁，**行锁不会阻塞普通SELECT查询**；长时间未提交事务会持续持有行锁，引发业务会话阻塞等待。 等待事件是性能故障诊断的核心依据，高频关键等待事件：`log file sync`日志刷盘等待、`buffer busy waits`缓冲区冲突、`enq: TX‑row lock contention`行锁冲突。 ### 1.6 告警日志、AWR、ASH性能工具理论 1. **告警日志alert log**：数据库第一诊断日志，记录实例启停、ORA报错、日志切换、参数变更，故障排查首要查阅文件；路径由`background_dump_dest`参数控制。 2. **AWR自动负载信息库**：自动采集数据库性能快照，默认每小时生成一次，快照默认保留8天；生成AWR报告用于分析一段时间整体数据库性能。 3. **ASH活动会话历史**：每秒采集活跃会话样本，适合分析瞬时突发性能故障。 ### 1.7 闪回与回收站理论 回收站recyclebin：普通DROP表不会立刻物理删除，对象重命名移入回收站，可以执行`FLASHBACK TABLE ... TO BEFORE DROP`恢复误删除表；`PURGE`命令彻底清除回收站对象。 闪回查询依靠UNDO数据读取历史时间点数据；闪回表可以将表恢复至过去时间点；闪回数据库需要开启闪回日志，依赖FRA存储。所有闪回能力受undo保留时间、闪回日志存储空间约束，**闪回属于应急恢复手段，不能替代RMAN物理备份**。 ## 二、生产环境完整实战操作 > > 说明：操作系统RHEL7，硬件规格64G内存8CPU；数据库实例`fgedudb`，全部路径替换`/fgedudb`；oracle用户执行数据库操作，root执行操作系统操作；全部脚本务必先在测试环境验证，生产执行前完成变更评审。 ### 2.1 数据库登录环境准备 ``` su - oracle export ORACLE_BASE=/fgedudb/app export ORACLE_HOME=/fgedudb/app/product/19.0.0/dbhome_1 export ORACLE_SID=fgedudb sqlplus / as sysdba ``` ### 2.2 实例启停与参数文件管理实战 #### 2.2.1 数据库分步启停 ``` --生产标准干净关闭 SHUTDOWN IMMEDIATE; --分步启动 nomount → mount → open STARTUP NOMOUNT; STARTUP MOUNT; ALTER DATABASE OPEN; --一步完整启动 STARTUP; ``` #### 2.2.2 spfile与pfile互相转换（应急操作） ``` --由spfile生成文本pfile CREATE PFILE='/fgedudb/app/product/19.0.0/dbhome_1/dbs/initfgedudb.ora' FROM SPFILE; --由pfile重建二进制spfile CREATE SPFILE FROM PFILE='/fgedudb/app/product/19.0.0/dbhome_1/dbs/initfgedudb.ora'; --查看当前正在使用的参数文件 SHOW PARAMETER spfile; ``` #### 2.2.3 修改系统参数，适配64G/8CPU基线 scope取值说明：`spfile`仅写入参数文件，重启实例生效；`memory`当前内存即时生效；`both`内存与spfile同时修改。 ``` ALTER SYSTEM SET undo_retention=900 SCOPE=BOTH; ALTER SYSTEM SET parallel_max_servers=16 SCOPE=SPFILE; ALTER SYSTEM SET processes=2000 SCOPE=SPFILE; ALTER SYSTEM SET db_recovery_file_dest_size=30G SCOPE=BOTH; ALTER SYSTEM SET recyclebin=ON SCOPE=SPFILE; --查看参数 SHOW PARAMETER undo_retention; SHOW PARAMETER parallel_max_servers; SHOW PARAMETER recyclebin; ``` ### 2.3 表空间、UNDO、TEMP、REDO、归档运维实战 #### 2.3.1 查询全部表空间使用率 ``` SELECT    t.tablespace_name,    round(SUM(d.bytes)/1024/1024,2) total_mb,    round(SUM(d.bytes‑COALESCE(f.bytes,0))/1024/1024,2) used_mb,    round((SUM(d.bytes‑COALESCE(f.bytes,0))/SUM(d.bytes))*100,2) used_pct FROM dba_tablespaces t LEFT JOIN dba_data_files d ON t.tablespace_name=d.tablespace_name LEFT JOIN dba_free_space f ON d.tablespace_name=f.tablespace_name AND d.file_id=f.file_id GROUP BY t.tablespace_name ORDER BY used_pct DESC; ``` #### 2.3.2 创建业务表空间（ASM磁盘组+DATA） ``` CREATE TABLESPACE fgedu_biz DATAFILE '+DATA/fgedudb/fgedu_biz01.dbf' SIZE 2G AUTOEXTEND ON NEXT 512M MAXSIZE 15G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; ``` #### 2.3.3 表空间扩容，新增数据文件 ``` ALTER TABLESPACE fgedu_biz ADD DATAFILE '+DATA/fgedudb/fgedu_biz02.dbf' SIZE 2G AUTOEXTEND ON NEXT 512M MAXSIZE 15G; ``` #### 2.3.4 UNDO回滚表空间检查 ``` SELECT tablespace_name,status FROM dba_undo_tablespaces; SELECT status,SUM(bytes)/1024/1024 mb FROM v$undo_extents GROUP BY status; ``` #### 2.3.5 TEMP临时表空间管理 ``` SELECT tablespace_name,file_name,bytes/1024/1024 size_mb FROM dba_temp_files; ``` #### 2.3.6 REDO联机日志、归档、FRA快速恢复区查询 ``` --查看redo日志组状态 SELECT group#,thread#,bytes/1024/1024 size_mb,status FROM v$log; SELECT group#,member FROM v$logfile; --查看归档模式 SELECT name,log_mode,open_mode FROM v$database; --FRA快速恢复区使用率 SELECT file_type,percent_space_used,percent_space_reclaimable,number_of_files FROM v$flash_recovery_area_usage; --归档日志列表 SELECT sequence#,first_time,next_time,name,applied FROM v$archived_log ORDER BY sequence# DESC; ``` > > 归档磁盘满数据库挂起应急提示：禁止直接操作系统rm删除归档文件；rm后数据库元数据仍然记录归档存在，需要进入RMAN执行`crosscheck archivelog all; delete expired archivelog all;`。 ### 2.4 用户、角色、Profile资源配置实战 #### 2.4.1 用户账号管理，业务用户fgedu ``` --创建业务用户 CREATE USER fgedu IDENTIFIED BY Fgedu@123 DEFAULT TABLESPACE fgedu_biz TEMPORARY TABLESPACE temp; --最小权限原则授权 GRANT CREATE SESSION,CREATE TABLE,CREATE VIEW,CREATE SEQUENCE TO fgedu; GRANT CONNECT,RESOURCE TO fgedu; --回收权限 REVOKE CREATE VIEW FROM fgedu; --锁定、解锁账号 ALTER USER fgedu ACCOUNT LOCK; ALTER USER fgedu ACCOUNT UNLOCK; --修改账号密码 ALTER USER fgedu IDENTIFIED BY Fgedu@New123; ``` #### 2.4.2 Profile资源配置文件创建与分配 ``` --查看默认profile配置 SELECT resource_name,limit FROM dba_profiles WHERE profile='DEFAULT'; --自定义业务profile，管控密码策略与资源 CREATE PROFILE prof_fgedu LIMIT PASSWORD_LIFE_TIME 180 FAILED_LOGIN_ATTEMPTS 10 PASSWORD_LOCK_TIME 1 SESSIONS_PER_USER 50 CPU_PER_SESSION UNLIMITED; --用户指定profile ALTER USER fgedu PROFILE prof_fgedu; ``` #### 2.4.3 用户权限字典查询 ``` SELECT username,default_tablespace,temporary_tablespace,account_status FROM dba_users WHERE username='FGEDU'; SELECT grantee,privilege FROM dba_sys_privs WHERE grantee='FGEDU'; ``` ### 2.5 会话、锁、阻塞故障排查实战 #### 2.5.1 查询当前全部业务会话 ``` SELECT s.sid,s.serial#,s.username,s.machine,s.program,s.status,s.event,s.sql_id FROM v$session s WHERE s.username IS NOT NULL; ``` #### 2.5.2 查询阻塞会话与被阻塞等待会话 ``` SELECT    s1.sid block_sid,s1.serial# block_serial,s1.username block_user,s1.machine block_machine,    s2.sid wait_sid,s2.serial# wait_serial,s2.username wait_user,s2.event wait_event,s2.sql_id wait_sqlid FROM v$session s1 JOIN v$session s2 ON s1.sid=s2.blocking_session WHERE s1.blocking_session IS NULL AND s2.blocking_session IS NOT NULL; ``` #### 2.5.3 杀掉阻塞会话 ``` --语法 ALTER SYSTEM KILL SESSION 'sid,serial#'; ALTER SYSTEM KILL SESSION '145,31246'; ``` #### 2.5.4 查看锁对象信息 ``` SELECT sid,type,lmode,request,id1,id2 FROM v$lock WHERE TYPE IN('TX','TM'); ``` ### 2.6 告警日志查看定位故障 ``` --查询告警日志文件路径 SHOW PARAMETER background_dump_dest; ``` 操作系统层面，示例路径`/fgedudb/app/diag/rdbms/fgedudb/fgedudb/trace/alert_fgedudb.log` ``` #实时跟踪告警日志输出 tail -f /fgedudb/app/diag/rdbms/fgedudb/fgedudb/trace/alert_fgedudb.log #过滤搜索ORA错误信息 grep ORA‑ /fgedudb/app/diag/rdbms/fgedudb/fgedudb/trace/alert_fgedudb.log ``` ### 2.7 AWR、ASH性能报告生成实战 登录sqlplus / as sysdba执行内置脚本，报告输出至当前工作目录。 ``` --生成AWR快照性能报告 @?/rdbms/admin/awrrpt.sql --AWR对比报告，对比两个时间段性能差异 @?/rdbms/admin/awrddrpt.sql --ASH活动会话报告，处理瞬时突发故障 @?/rdbms/admin/ashrpt.sql ``` 查询AWR快照列表，确认时间点 ``` SELECT snap_id,startup_time,begin_interval_time,end_interval_time FROM dba_hist_snapshot ORDER BY snap_id DESC FETCH FIRST 20 ROWS ONLY; ``` ### 2.8 回收站与闪回技术实战 ``` SHOW PARAMETER recyclebin; --模拟业务用户删除表 CONNECT fgedu/Fgedu@123; CREATE TABLE t_fgedu_test(id NUMBER); INSERT INTO t_fgedu_test VALUES(100); COMMIT; DROP TABLE t_fgedu_test; --查看回收站对象 SELECT object_name,original_name,drop_time FROM user_recyclebin; --闪回恢复被DROP删除的表 FLASHBACK TABLE t_fgedu_test TO BEFORE DROP; --清除单张表回收站记录 PURGE TABLE t_fgedu_test; --清空当前用户全部回收站 PURGE RECYCLEBIN; --闪回查询，读取5分钟之前的数据 SELECT * FROM t_fgedu_test AS OF TIMESTAMP SYSDATE‑5/24/60; --闪回表至指定时间点，需要开启行移动 ALTER TABLE t_fgedu_test ENABLE ROW MOVEMENT; FLASHBACK TABLE t_fgedu_test TO TIMESTAMP SYSDATE‑10/24/60; ALTER TABLE t_fgedu_test DISABLE ROW MOVEMENT; ``` ### 2.9 Oracle单机综合自动化巡检Shell脚本 保存脚本文件`/fgedudb/soft/oracle_daily_check_fgedudb.sh` ``` #!/bin/bash export ORACLE_BASE=/fgedudb/app export ORACLE_HOME=/fgedudb/app/product/19.0.0/dbhome_1 export ORACLE_SID=fgedudb export PATH=$ORACLE_HOME/bin:$PATH echo "================实例基础信息================" sqlplus -S / as sysdba <1<1EOF set pagesize 120 linesize 160 SELECT instance_name,host_name,version,status,startup_time FROM v\$instance; SELECT name,log_mode,open_mode FROM v\$database; SHOW PARAMETER memory_target; SHOW PARAMETER processes; EOF echo "================表空间使用率================" sqlplus -S / as sysdba <1<1EOF set pagesize 120 linesize 160 SELECT    t.tablespace_name,    round(SUM(d.bytes)/1024/1024,2) total_mb,    round(SUM(d.bytes‑COALESCE(f.bytes,0))/1024/1024,2) used_mb,    round((SUM(d.bytes‑COALESCE(f.bytes,0))/SUM(d.bytes))*100,2) used_pct FROM dba_tablespaces t LEFT JOIN dba_data_files d ON t.tablespace_name=d.tablespace_name LEFT JOIN dba_free_space f ON d.tablespace_name=f.tablespace_name AND d.file_id=f.file_id GROUP BY t.tablespace_name ORDER BY used_pct DESC; EOF echo "================FRA快速恢复区、REDO日志================" sqlplus -S / as sysdba <1<1EOF SELECT group#,bytes/1024/1024 size_mb,status FROM v\$log; SELECT file_type,percent_space_used FROM v\$flash_recovery_area_usage; EOF echo "================失效对象检查================" sqlplus -S / as sysdba <1<1EOF SELECT object_name,object_type,status FROM dba_objects WHERE status!='VALID'; EOF echo "================监听状态================" lsnrctl status echo "================操作系统内存CPU信息================" free -g lscpu ``` 赋予执行权限，运行巡检脚本 ``` chmod +x /fgedudb/soft/oracle_daily_check_fgedudb.sh ./fgedudb/soft/oracle_daily_check_fgedudb.sh ``` ## 三、总结 Oracle数据库日常运维，不是简单执行启停命令，而是一套完整标准化运维体系。我是风哥，在大量项目实施过程中，很多生产事故的根源，都来自巡检缺位：表空间持续上涨无人处理、归档磁盘占满业务挂起、长事务持有锁引发大面积阻塞、告警日志ORA报错长期被忽略。风哥 itpux-com 本文基于硬件规格**64G内存、8CPU**的Oracle19c单机实例，全部路径替换为`/fgedudb`，数据库实例`fgedudb`，业务用户`fgedu`。完整覆盖实例生命周期管理、spfile/pfile参数、各类存储组件运维、账号权限Profile管控、会话锁阻塞排查、告警日志、AWR/ASH性能诊断、闪回回收站技术，配套完整可直接落地的自动化巡检脚本。 这里有几条必须严格遵守的生产运维关键点： 1. 数据库关闭优先使用`SHUTDOWN IMMEDIATE`，`SHUTDOWN ABORT`只用于极端故障场景，禁止日常维护使用；二进制spfile文件**禁止vi直接编辑**，统一使用`ALTER SYSTEM`命令修改参数； 2. 生产业务库务必开启`ARCHIVELOG`归档模式，定期巡检表空间、FRA快速恢复区使用率，磁盘告警阈值提前设置； 3. 处理锁阻塞故障优先定位源头阻塞会话，不要盲目批量kill会话；网上搜索风哥教程可以学习全套数据库教程 4. 告警日志是故障排查第一手材料，出现ORA报错要第一时间分析根因，不能只做临时恢复； 5. AWR、ASH用于性能分析，但闪回、回收站属于应急手段，**绝对不能替代RMAN物理备份**； 6. 所有参数修改、DDL变更严格执行变更管控流程，先测试环境完整验证，评估锁、事务、性能风险之后再上线。风哥教程 113257174 日常运维工作重点是防患于未然，例行巡检、变更管控、故障演练三者缺一不可。本教程为单机运维基础，后续RAC集群运维、RMAN备份恢复、ADG容灾、补丁升级都建立在这套运维能力之上。DBA不能只会复制脚本执行，需要读懂每一条动态视图输出背后含义，理解数据库内部运行机制，才能从容应对各类真实线上故障。风哥数据库教程 itpux-com