Oracle Database 常用运维指令手册

适用版本：Oracle Database 19c / 21c / 23ai（23.x）/ Oracle AI Database 26ai（23.26.x） 约定：# root 或 oracle 用户，$ 普通用户，SQL> sqlplus 提示符，RMAN> RMAN 提示符，<xxx> 为占位符。 差异标注：正文用 > **版本差异** 引用块标注跨版本不一致之处。 更新时间：2026-11

目录

1. 版本与支持周期
2. 下载地址与安装
3. 实例启停与服务管理
4. 监听器与网络配置
5. SQL*Plus 与客户端连接
6. 参数与 SPFILE
7. 多租户：CDB 与 PDB
8. 用户、权限与表空间配额
9. 表空间与数据文件
10. 对象日常操作
11. 归档模式与日志
12. RMAN 备份与恢复
13. Data Pump 逻辑备份
14. Data Guard 与 ADG
15. 日志体系与告警
16. 性能诊断：AWR / ASH / 锁
17. 空间与容量管理
18. 升级与迁移
19. 高频故障速查
20. 危险命令与避坑清单
21. 附录：巡检脚本与视图速查

1. 版本与支持周期

1.1 版本格局

关键：Oracle 的版本号与实际发行名不一致。26ai 的内部版本号是 23.26.x（例如 23.26.3.0.0），sqlplus 里看到的是 23.x，不要误判为旧版本。 19c 是最稳妥的存量升级目标；新建系统选 26ai（免费版可用）。

1.2 版本识别

SQL> SELECT * FROM v$version;
SQL> SELECT banner_full FROM v$version;
SQL> SELECT version_full FROM v$instance;
SQL> SELECT instance_name, host_name, version, status, database_status FROM v$instance;

$ORACLE_HOME/OPatch/opatch lspatches          # 已打的补丁（RU / CPU）
$ORACLE_HOME/OPatch/opatch lsinventory | head -40

-- 补丁与组件版本
SELECT * FROM registry$history ORDER BY action_time DESC;
SELECT comp_name, version, status FROM dba_registry;

1.3 免费版限制（Oracle AI Database Free）

2. 下载地址与安装

2.1 官方入口总表

国内下载通常需要 Oracle 账号登录，企业环境一般通过 MOS 或内部制品库分发。

2.2 Oracle Linux / RHEL 安装（RPM 方式，26ai 示例）

# 1) 预安装包：自动创建 oracle 用户、组、内核参数、目录、limits
dnf install -y oracle-database-preinstall-26ai
# 19c 对应 oracle-database-preinstall-19c

# 2) 安装数据库软件（RPM 内含 dbca/netca 所需组件）
dnf install -y oracle-ai-database-ee-26ai-1.0-1.el9.x86_64.rpm

# 3) 配置环境变量
cat >> /home/oracle/.bash_profile <<'EOF'
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product/26ai/dbhome_1
export ORACLE_SID=ORCLCDB
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
EOF
source /home/oracle/.bash_profile

# 4) 建库（静默，CDB + 1 个 PDB）
dbca -silent -createDatabase \
  -templateName General_Purpose.dbc \
  -gdbName ORCLCDB -sid ORCLCDB \
  -createAsContainerDatabase true \
  -numberOfPDBs 1 -pdbName ORCLPDB1 \
  -sysPassword 'Sys#Str0ng2026' \
  -systemPassword 'Sys#Str0ng2026' \
  -characterSet AL32UTF8 \
  -totalMemory 4096 \
  -emConfiguration NONE \
  -datafileDestination /opt/oracle/oradata

注意：-totalMemory 单位 MB。免费版上限 2GB 内存，设置超过会被拒绝或运行异常。

2.3 ZIP 方式（runInstaller）

# 解压到 ORACLE_HOME
mkdir -p /opt/oracle/product/26ai/dbhome_1
cd /opt/oracle/product/26ai/dbhome_1
unzip -q LINUX.X64_2326100_db_home.zip

# 静默安装（响应文件方式）
./runInstaller -silent -waitforcompletion -responseFile /tmp/db_install.rsp

# 按提示用 root 执行配置脚本
/opt/oracle/oraInventory/orainstRoot.sh
/opt/oracle/product/26ai/dbhome_1/root.sh

响应文件关键项：

UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/opt/oracle/oraInventory
ORACLE_HOME=/opt/oracle/product/26ai/dbhome_1
ORACLE_BASE=/opt/oracle
oracle.install.option=INSTALL_DB_SWONLY
ORACLE_HOSTNAME=ora01
oracle.install.db.InstallEdition=EE
oracle.install.db.config.starterdb.type=GENERAL_PURPOSE
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
DECLINE_SECURITY_UPDATES=true

2.4 Docker 部署（最快上手）

docker pull container-registry.oracle.com/database/free:latest

docker run -d --name oracle-free \
  --restart unless-stopped \
  -p 1521:1521 -p 5500:5500 \
  -e ORACLE_PWD='Sys#Str0ng2026' \
  -e ORACLE_CHARACTERSET=AL32UTF8 \
  -v /data/oracle/oradata:/opt/oracle/oradata \
  container-registry.oracle.com/database/free:latest

# 首次启动建库需要几分钟
docker logs -f oracle-free

# 连接
docker exec -it oracle-free sqlplus sys/Sys#Str0ng2026@localhost:1521/FREEPDB1 as sysdba

2.5 安装后必做

# 1) 确认监听与实例
lsnrctl status
sqlplus -S / as sysdba <<'SQL'
SELECT instance_name, status FROM v$instance;
SELECT name, open_mode, cdb FROM v$database;
SQL

# 2) 设置归档（生产必须）
sqlplus / as sysdba

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
SQL> ARCHIVE LOG LIST;

# 3) 配置开机自启（见第 3 章）

3. 实例启停与服务管理

3.1 SQL*Plus 启停（最基础）

sqlplus / as sysdba          # OS 认证，要求当前用户在 dba 组
sqlplus sys/password as sysdba
sqlplus system/password@ORCLPDB1

SQL> STARTUP;                       -- 启动到 OPEN
SQL> STARTUP MOUNT;                 -- 到 MOUNT（改归档、恢复时用）
SQL> STARTUP NOMOUNT;               -- 只启动实例（建库、恢复控制文件）
SQL> STARTUP FORCE;                 -- 异常时强制重启
SQL> STARTUP RESTRICT;              -- 限制模式，只允许有 RESTRICTED SESSION 权限的用户

SQL> SHUTDOWN NORMAL;               -- 等所有会话主动退出（几乎不用，会挂住）
SQL> SHUTDOWN TRANSACTIONAL;        -- 等当前事务结束
SQL> SHUTDOWN IMMEDIATE;            -- 回滚未提交事务后关闭（日常首选）
SQL> SHUTDOWN ABORT;                -- 直接终止，下次启动走实例恢复

危险：SHUTDOWN ABORT 不会损坏数据（有 redo 保护），但下次启动需要实例恢复，且未提交事务丢失。非紧急不用。

3.2 状态查询

SQL> SELECT instance_name, status, database_status, active_state FROM v$instance;
SQL> SELECT name, open_mode, database_role, protection_mode FROM v$database;
SQL> SELECT log_mode FROM v$database;
SQL> SELECT status FROM v$instance;

状态含义：

3.3 监听器

lsnrctl status
lsnrctl status LISTENER
lsnrctl start
lsnrctl stop
lsnrctl reload           # 重读 listener.ora，不断连接
lsnrctl services
lsnrctl version

# 动态注册之外，手工注册
sqlplus / as sysdba
SQL> ALTER SYSTEM REGISTER;

3.4 开机自启（systemd）

Oracle 自带的 dbstart/dbshut 依赖 /etc/oratab：

# /etc/oratab 最后一段从 N 改成 Y 才会被 dbstart 拉起
ORCLCDB:/opt/oracle/product/26ai/dbhome_1:Y

# /etc/systemd/system/oracle-db.service
[Unit]
Description=Oracle Database Startup
After=network.target

[Service]
Type=oneshot
RemainAfterExit=yes
User=oracle
Environment=ORACLE_HOME=/opt/oracle/product/26ai/dbhome_1
ExecStart=/opt/oracle/product/26ai/dbhome_1/bin/dbstart /opt/oracle/product/26ai/dbhome_1
ExecStop=/opt/oracle/product/26ai/dbhome_1/bin/dbshut /opt/oracle/product/26ai/dbhome_1
TimeoutStartSec=600

[Install]
WantedBy=multi-user.target

# 监听器单独一个单元
# /etc/systemd/system/oracle-listener.service
[Unit]
Description=Oracle Net Listener
After=network.target

[Service]
Type=forking
User=oracle
Environment=ORACLE_HOME=/opt/oracle/product/26ai/dbhome_1
ExecStart=/opt/oracle/product/26ai/dbhome_1/bin/lsnrctl start
ExecStop=/opt/oracle/product/26ai/dbhome_1/bin/lsnrctl stop
Restart=on-failure

[Install]
WantedBy=multi-user.target

systemctl daemon-reload
systemctl enable --now oracle-listener oracle-db

注意：dbstart 对多租户 CDB 只会启动到 CDB，PDB 的打开状态由 PDB_SAVE_STATE 决定，见 7.4。

3.5 多实例（同一主机）

export ORACLE_SID=ORCL2
export ORACLE_HOME=/opt/oracle/product/26ai/dbhome_2
sqlplus / as sysdba

每个 SID 在 /etc/oratab 各占一行，监听器可通过 SID_LIST_LISTENER 静态注册多个实例。

4. 监听器与网络配置

4.1 三个文件的分工

echo $TNS_ADMIN
ls $ORACLE_HOME/network/admin/

4.2 listener.ora 示例

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.11)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC = (GLOBAL_DBNAME = ORCLCDB)(ORACLE_HOME = /opt/oracle/product/26ai/dbhome_1)(SID_NAME = ORCLCDB))
  )

ADR_BASE_LISTENER = /opt/oracle

4.3 tnsnames.ora 示例

ORCLPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLPDB1)
    )
  )

ORCLCDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.11)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = ORCLCDB))
  )

4.4 sqlnet.ora 常用项

NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)
SQLNET.INBOUND_CONNECT_TIMEOUT = 60
SQLNET.EXPIRE_TIME = 10                 # 每 10 分钟探活，清理死连接
SQLNET.ENCRYPTION_SERVER = required
SQLNET.ENCRYPTION_TYPES_SERVER = (AES256)
SQLNET.CRYPTO_CHECKSUM_SERVER = required

4.5 连通性排查

# 1) 监听在不在
lsnrctl status
ss -lntp | grep 1521

# 2) 服务名解析对不对
tnsping ORCLPDB1
tnsping 10.0.0.11:1521/ORCLPDB1

# 3) 直连测试（绕过 tnsnames）
sqlplus system/pass@//10.0.0.11:1521/ORCLPDB1

# 4) 看监听日志
tail -100 $ORACLE_BASE/diag/tnslsnr/$(hostname -s)/listener/trace/listener.log

常见错误码：ORA-12541 无监听；ORA-12514 监听不认识该服务名（动态注册未完成或 service_names 不含它）；ORA-12154 客户端解析不到别名（TNS_ADMIN 或 tnsnames.ora 路径错）；ORA-12545 主机名解析失败。

5. SQL*Plus 与客户端连接

5.1 基本用法

sqlplus / as sysdba                                   # OS 认证
sqlplus sys/pass@ORCLCDB as sysdba
sqlplus system/pass@ORCLPDB1
sqlplus -S system/pass@ORCLPDB1 <<'SQL'               # 静默执行，脚本常用
SELECT sysdate FROM dual;
EXIT
SQL

sqlplus system/pass@ORCLPDB1 @/tmp/script.sql         # 执行脚本
sqlplus -S system/pass@ORCLPDB1 @script.sql > out.txt

5.2 会话设置（脚本必备）

SQL> SET LINESIZE 200
SQL> SET PAGESIZE 200
SQL> SET LONG 100000
SQL> SET LONGCHUNKSIZE 10000
SQL> SET SERVEROUTPUT ON SIZE UNLIMITED
SQL> SET TIMING ON
SQL> SET AUTOTRACE ON EXPLAIN STATISTICS
SQL> SET FEEDBACK ON
SQL> SET ECHO OFF
SQL> COL name FOR a30
SQL> COL value FOR a60
SQL> SPOOL /tmp/report.txt
...
SQL> SPOOL OFF

5.3 常用数据字典命令

SQL> SELECT table_name FROM dict WHERE table_name LIKE 'DBA_%' AND ROWNUM < 20;
SQL> DESC dba_tablespaces;
SQL> SELECT * FROM tab;                 -- 当前用户对象
SQL> SELECT object_name, object_type, status FROM user_objects;
SQL> SHOW PARAMETER sga;
SQL> SHOW ALL;
SQL> DEFINE;

5.4 错误码查询

oerr ora 12514
oerr ora 01555
oerr rman 1234

SQL> SELECT * FROM v$diag_info;          -- 各类诊断文件路径

6. 参数与 SPFILE

6.1 SPFILE 与 PFILE

SQL> SHOW PARAMETER spfile;
SQL> CREATE PFILE='/tmp/pfile_bak.ora' FROM SPFILE;
SQL> CREATE SPFILE FROM PFILE='/tmp/pfile_bak.ora';       -- 需重启
SQL> SELECT name, value, isspecified FROM v$spparameter WHERE isspecified='TRUE';

6.2 修改参数

SQL> ALTER SYSTEM SET sga_target=8G SCOPE=BOTH;            -- 内存 + SPFILE
SQL> ALTER SYSTEM SET processes=1000 SCOPE=SPFILE;         -- 只写文件，重启生效
SQL> ALTER SYSTEM SET db_recovery_file_dest_size=500G SCOPE=BOTH;
SQL> ALTER SYSTEM RESET open_cursors SCOPE=BOTH;
SQL> ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';

SQL> SELECT name, value, isdefault FROM v$parameter WHERE name IN
     ('sga_target','pga_aggregate_target','processes','sessions','open_cursors',
      'db_block_size','compatible','undo_tablespace','db_recovery_file_dest_size');

注意：compatible、db_block_size、db_name 等属于静态参数，只能 SCOPE=SPFILE 并重启。SGA_TARGET 在 AMM 开启时与 MEMORY_TARGET 不能同时设置。

6.3 内存管理三种模式

-- 1) AMM（自动内存管理，11g 起）
SQL> ALTER SYSTEM SET memory_target=12G SCOPE=SPFILE;
SQL> ALTER SYSTEM SET memory_max_target=12G SCOPE=SPFILE;
-- 需重启，且需 /dev/shm 足够大

-- 2) ASMM（自动共享内存管理，当前主流）
SQL> ALTER SYSTEM SET sga_target=8G SCOPE=BOTH;
SQL> ALTER SYSTEM SET pga_aggregate_target=4G SCOPE=BOTH;
SQL> ALTER SYSTEM SET memory_target=0 SCOPE=SPFILE;

-- 3) 手工管理（一般不用）
SQL> ALTER SYSTEM SET sga_max_size=8G SCOPE=SPFILE;

SQL> SELECT component, current_size/1024/1024 AS mb FROM v$sga_dynamic_components;
SQL> SELECT name, value/1024/1024 AS mb FROM v$pgastat WHERE name='aggregate PGA target parameter';

6.4 关键参数基线

7. 多租户：CDB 与 PDB

12c 之后的核心架构变化：一个 CDB（容器数据库）下挂多个 PDB（可插拔数据库），运维必须明确"当前在哪个容器里"。

7.1 基本概念

7.2 切换容器

SQL> SHOW CON_NAME;
SQL> SELECT sys_context('USERENV','CON_NAME') FROM dual;
SQL> ALTER SESSION SET CONTAINER = ORCLPDB1;
SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;

# 直接连到 PDB
sqlplus system/pass@ORCLPDB1

7.3 PDB 生命周期

-- 查看
SQL> SELECT con_id, name, open_mode, restricted FROM v$pdbs;
SQL> SELECT pdb_name, status FROM cdb_pdbs;

-- 创建（从种子克隆）
SQL> CREATE PLUGGABLE DATABASE apppdb ADMIN USER appadmin IDENTIFIED BY 'App#2026'
     FILE_NAME_CONVERT=('/opt/oracle/oradata/ORCLCDB/pdbseed/','/opt/oracle/oradata/ORCLCDB/apppdb/');

-- 打开 / 关闭
SQL> ALTER PLUGGABLE DATABASE apppdb OPEN;
SQL> ALTER PLUGGABLE DATABASE apppdb OPEN READ ONLY;
SQL> ALTER PLUGGABLE DATABASE apppdb CLOSE IMMEDIATE;
SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

-- 保存打开状态，重启后自动打开
SQL> ALTER PLUGGABLE DATABASE apppdb SAVE STATE;
SQL> ALTER PLUGGABLE DATABASE apppdb DISCARD STATE;

-- 删除（必须先关）
SQL> ALTER PLUGGABLE DATABASE apppdb CLOSE IMMEDIATE;
SQL> DROP PLUGGABLE DATABASE apppdb INCLUDING DATAFILES;

-- 克隆 PDB
SQL> CREATE PLUGGABLE DATABASE apppdb2 FROM apppdb
     FILE_NAME_CONVERT=('/oradata/apppdb/','/oradata/apppdb2/');

-- 插拔：拔出到 XML
SQL> ALTER PLUGGABLE DATABASE apppdb UNPLUG INTO '/tmp/apppdb.xml';
-- 插入到另一个 CDB
SQL> CREATE PLUGGABLE DATABASE apppdb USING '/tmp/apppdb.xml'
     COPY FILE_NAME_CONVERT=('/old/path/','/new/path/');

7.4 服务与连接

SQL> SELECT name, pdb FROM v$services ORDER BY name;
SQL> ALTER SESSION SET CONTAINER=apppdb;
SQL> SHOW PARAMETER service_names;

注意：应用连接串必须写 PDB 的 service_name，不是 SID。连 CDB$ROOT 的应用会看不到业务表，这是 12c 之后最常见的"表不存在"原因。

8. 用户、权限与表空间配额

8.1 创建用户

-- 在 PDB 内建本地用户
SQL> ALTER SESSION SET CONTAINER = apppdb;
SQL> CREATE USER app IDENTIFIED BY 'App#Str0ng2026'
     DEFAULT TABLESPACE app_data
     TEMPORARY TABLESPACE temp
     QUOTA UNLIMITED ON app_data
     PROFILE DEFAULT;

-- 公共用户（CDB 级，必须以 C## 开头）
SQL> CREATE USER c##dba IDENTIFIED BY 'Dba#2026' CONTAINER=ALL;
SQL> GRANT DBA TO c##dba CONTAINER=ALL;

-- 修改
SQL> ALTER USER app IDENTIFIED BY 'New#2026';
SQL> ALTER USER app QUOTA 10G ON app_data;
SQL> ALTER USER app ACCOUNT LOCK;
SQL> ALTER USER app ACCOUNT UNLOCK;
SQL> ALTER USER app DEFAULT TABLESPACE app_data;

-- 删除
SQL> DROP USER app CASCADE;          -- 危险，连带删除所有对象

8.2 权限授予

-- 系统权限
SQL> GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE TO app;
SQL> GRANT CREATE PROCEDURE, CREATE TRIGGER TO app;
SQL> GRANT UNLIMITED TABLESPACE TO app;        -- 慎用，绕过配额
SQL> GRANT SELECT ANY TABLE TO app;            -- 高危，可读全库
SQL> GRANT DBA TO app;                         -- 极高危

-- 对象权限
SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON app.orders TO report;
SQL> GRANT SELECT ON app.v_orders TO report;
SQL> GRANT EXECUTE ON app.p_calc TO app;

-- 角色
SQL> CREATE ROLE role_read;
SQL> GRANT SELECT ON app.orders TO role_read;
SQL> GRANT role_read TO report;

-- 查看
SQL> SELECT * FROM dba_sys_privs WHERE grantee='APP';
SQL> SELECT * FROM dba_tab_privs WHERE grantee='APP';
SQL> SELECT * FROM dba_role_privs WHERE grantee='APP';
SQL> SELECT * FROM session_privs;               -- 当前会话拥有的权限

8.3 PROFILE 与密码策略

SQL> CREATE PROFILE app_profile LIMIT
     FAILED_LOGIN_ATTEMPTS 5
     PASSWORD_LIFE_TIME 90
     PASSWORD_REUSE_MAX 5
     PASSWORD_GRACE_TIME 7
     SESSIONS_PER_USER 50
     IDLE_TIME 60
     CONNECT_TIME UNLIMITED;

SQL> ALTER USER app PROFILE app_profile;
SQL> SELECT username, profile, account_status, expiry_date FROM dba_users;
SQL> SELECT * FROM dba_profiles WHERE profile='APP_PROFILE' ORDER BY resource_name;

注意：FAILED_LOGIN_ATTEMPTS 触发后账号会变成 LOCKED(TIMED)，需要 ALTER USER app ACCOUNT UNLOCK;。默认 PROFILE 的 PASSWORD_LIFE_TIME 在 11g 是 180 天，很多老系统突然"密码过期"就是这个。

8.4 表空间配额

SQL> SELECT username, tablespace_name, bytes/1024/1024 AS mb, max_bytes/1024/1024 AS max_mb
     FROM dba_ts_quotas WHERE username='APP';

SQL> ALTER USER app QUOTA 20G ON app_data;
SQL> ALTER USER app QUOTA UNLIMITED ON app_data;

-- 用户对象占用
SQL> SELECT segment_type, SUM(bytes)/1024/1024 AS mb
     FROM dba_segments WHERE owner='APP' GROUP BY segment_type;

9. 表空间与数据文件

9.1 表空间类型

9.2 常用操作

-- 创建永久表空间
SQL> CREATE TABLESPACE app_data
     DATAFILE '/opt/oracle/oradata/ORCLCDB/apppdb/app_data01.dbf'
     SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 32G
     EXTENT MANAGEMENT LOCAL AUTOALLOCATE
     SEGMENT SPACE MANAGEMENT AUTO;

-- 加数据文件
SQL> ALTER TABLESPACE app_data ADD DATAFILE
     '/opt/oracle/oradata/ORCLCDB/apppdb/app_data02.dbf' SIZE 2G AUTOEXTEND ON NEXT 100M MAXSIZE 32G;

-- 扩展现有文件
SQL> ALTER DATABASE DATAFILE '/opt/oracle/oradata/ORCLCDB/apppdb/app_data01.dbf' RESIZE 4G;

-- 改自动扩展
SQL> ALTER DATABASE DATAFILE '...' AUTOEXTEND ON NEXT 200M MAXSIZE 32G;

-- 创建 TEMP
SQL> CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '...' SIZE 2G AUTOEXTEND ON NEXT 100M MAXSIZE 16G;
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

-- 创建 UNDO
SQL> CREATE UNDO TABLESPACE undotbs2 DATAFILE '...' SIZE 2G AUTOEXTEND ON NEXT 100M MAXSIZE 16G;
SQL> ALTER SYSTEM SET undo_tablespace='UNDOTBS2' SCOPE=BOTH;

-- 删除（永久表空间必须先空）
SQL> DROP TABLESPACE app_data INCLUDING CONTENTS AND DATAFILES;   -- 危险
SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

9.3 使用率查询

-- 表空间使用率（含 AUTOEXTEND 后的极限）
SELECT t.tablespace_name,
       ROUND(t.used_space*t.block_size/1024/1024/1024, 2) AS used_gb,
       ROUND(t.tablespace_size*t.block_size/1024/1024/1024, 2) AS total_gb,
       ROUND(t.used_percent, 2) AS used_pct
FROM dba_tablespace_usage_metrics t
ORDER BY t.used_percent DESC;

-- 数据文件与自动扩展
SELECT tablespace_name, file_name,
       ROUND(bytes/1024/1024/1024,2) AS size_gb,
       autoextensible,
       ROUND(maxbytes/1024/1024/1024,2) AS max_gb
FROM dba_data_files ORDER BY tablespace_name, file_name;

-- 临时表空间
SELECT tablespace_name, ROUND(bytes_used/1024/1024,1) AS used_mb,
       ROUND(bytes_free/1024/1024,1) AS free_mb
FROM v$temp_space_header;

告警阈值：永久表空间 > 80% 开始关注，> 90% 立即处理。注意 used_percent 是基于当前文件大小算的，若开启了 AUTOEXTEND 且 MAXSIZE 很大，实际风险要看 MAXSIZE 与磁盘余量。

9.4 段与碎片

-- 最大的段
SELECT owner, segment_name, segment_type,
       ROUND(bytes/1024/1024/1024,2) AS gb
FROM dba_segments
ORDER BY bytes DESC FETCH FIRST 20 ROWS ONLY;

-- 表空间碎片（空闲空间不连续）
SELECT tablespace_name, count(*) AS free_chunks,
       ROUND(SUM(bytes)/1024/1024,1) AS free_mb,
       ROUND(MAX(bytes)/1024/1024,1) AS largest_mb
FROM dba_free_space GROUP BY tablespace_name;

-- 表实际占用 vs 高水位
SELECT segment_name, ROUND(bytes/1024/1024,1) AS mb, blocks
FROM dba_segments WHERE segment_name='ORDERS';

-- 收缩（10g+ 支持 ASSM 表空间在线收缩）
SQL> ALTER TABLE app.orders ENABLE ROW MOVEMENT;
SQL> ALTER TABLE app.orders SHRINK SPACE COMPACT;
SQL> ALTER TABLE app.orders SHRINK SPACE;
-- 或 MOVE 重建（会失效索引，需 rebuild）
SQL> ALTER TABLE app.orders MOVE TABLESPACE app_data;
SQL> ALTER INDEX app.idx_orders REBUILD ONLINE;

10. 对象日常操作

10.1 表

SQL> CREATE TABLE app.orders (
       id         NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
       user_id    NUMBER NOT NULL,
       amount     NUMBER(12,2) DEFAULT 0 NOT NULL,
       status     NUMBER(2) DEFAULT 0 NOT NULL,
       created_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
     ) TABLESPACE app_data;

SQL> CREATE INDEX app.idx_orders_user ON app.orders(user_id, created_at) TABLESPACE app_idx ONLINE;
SQL> ALTER TABLE app.orders ADD (remark VARCHAR2(200));
SQL> ALTER TABLE app.orders MODIFY (amount NUMBER(14,2));
SQL> ALTER TABLE app.orders RENAME COLUMN remark TO note;
SQL> ALTER TABLE app.orders DROP COLUMN note;

SQL> TRUNCATE TABLE app.orders;                    -- 危险，不可回滚
SQL> DROP TABLE app.orders PURGE;                  -- PURGE 不进回收站
SQL> FLASHBACK TABLE app.orders TO BEFORE DROP;    -- 从回收站恢复（未 PURGE 时）

SQL> SELECT * FROM user_recyclebin;
SQL> PURGE RECYCLEBIN;

重要：DROP TABLE 默认只是移入回收站（占用空间不释放），必须 PURGE 或 DROP ... PURGE 才真正删。但 TRUNCATE 不可闪回（除非有备份）。

10.2 分区表

SQL> CREATE TABLE app.logs (
       id NUMBER, created_at DATE, msg VARCHAR2(4000)
     ) PARTITION BY RANGE (created_at) (
       PARTITION p202610 VALUES LESS THAN (TO_DATE('2026-11-01','YYYY-MM-DD')),
       PARTITION p202611 VALUES LESS THAN (TO_DATE('2026-12-01','YYYY-MM-DD')),
       PARTITION pmax    VALUES LESS THAN (MAXVALUE)
     );

-- 增删分区
SQL> ALTER TABLE app.logs ADD PARTITION p202612 VALUES LESS THAN (TO_DATE('2027-01-01','YYYY-MM-DD'));
SQL> ALTER TABLE app.logs DROP PARTITION p202610 UPDATE GLOBAL INDEXES;
SQL> ALTER TABLE app.logs TRUNCATE PARTITION p202610 UPDATE GLOBAL INDEXES;

-- 分区交换（归档常用，秒级）
SQL> ALTER TABLE app.logs EXCHANGE PARTITION p202610 WITH TABLE app.logs_202610 INCLUDING INDEXES;

-- 查看分区
SQL> SELECT partition_name, high_value, num_rows FROM user_tab_partitions WHERE table_name='LOGS';

注意：DROP/TRUNCATE PARTITION 不加 UPDATE GLOBAL INDEXES 会让全局索引失效（UNUSABLE），必须随后 ALTER INDEX ... REBUILD。这是分区表运维最常见的坑。

10.3 统计信息

-- 收集（推荐用 DBMS_STATS，不要用 ANALYZE）
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'APP', tabname => 'ORDERS',
    cascade => TRUE, degree => 4,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO');
END;
/

-- 收集整个 schema
BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'APP', cascade => TRUE, degree => 4);
END;
/

-- 查看统计信息时间
SELECT table_name, last_analyzed, num_rows, stale_stats
FROM user_tab_statistics WHERE table_name='ORDERS';

-- 锁定统计信息（防止变化）
BEGIN DBMS_STATS.LOCK_TABLE_STATS('APP','ORDERS'); END;
/

11. 归档模式与日志

11.1 归档模式

SQL> ARCHIVE LOG LIST;
SQL> SELECT log_mode FROM v$database;

-- 开启归档（需 MOUNT）
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

-- 归档路径与空间
SQL> SHOW PARAMETER db_recovery_file_dest;
SQL> SHOW PARAMETER db_recovery_file_dest_size;
SQL> SELECT * FROM v$recovery_file_dest;
SQL> SELECT * FROM v$flash_recovery_area_usage;

致命点：db_recovery_file_dest_size 满后归档写不进去，数据库会挂起（所有 DML 卡住）。必须监控 v$recovery_file_dest.space_used_percent，超过 80% 就清理或扩容。

11.2 redo 日志组

SQL> SELECT group#, thread#, sequence#, bytes/1024/1024 AS mb, members, status, archived
     FROM v$log;

SQL> SELECT group#, member, status FROM v$logfile ORDER BY group#;

-- 加组 / 加成员（多路复用，生产必备）
SQL> ALTER DATABASE ADD LOGFILE GROUP 4 ('/oradata/redo04a.log','/oradata2/redo04b.log') SIZE 512M;
SQL> ALTER DATABASE ADD LOGFILE MEMBER '/oradata2/redo01b.log' TO GROUP 1;

-- 删除组（必须先 INACTIVE）
SQL> ALTER DATABASE DROP LOGFILE GROUP 4;

-- 手工切换与检查点
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM CHECKPOINT;

原则：至少 3 组、每组至少 2 个成员（放不同物理盘）。组太少会导致频繁 checkpoint，组太小会导致 checkpoint not complete 等待。

11.3 日志切换频率检查

-- 每小时切换次数，超过 6 次/小时要考虑加大 redo
SELECT TO_CHAR(first_time,'YYYY-MM-DD HH24') AS hour, COUNT(*) AS switches
FROM v$log_history
WHERE first_time > SYSDATE - 7
GROUP BY TO_CHAR(first_time,'YYYY-MM-DD HH24')
ORDER BY 1;

12. RMAN 备份与恢复

12.1 准备工作

# 建备份目录与恢复目录（可选）
mkdir -p /backup/rman

-- 归档目标与保留策略
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/rman/cf_%F';
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/rman/%U';

-- 快速恢复区大小
SQL> ALTER SYSTEM SET db_recovery_file_dest_size=500G SCOPE=BOTH;

12.2 全备

rman target /

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;                 -- 差异增量
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;       -- 累积增量
RMAN> BACKUP DATAFILE 4;
RMAN> BACKUP TABLESPACE app_data;
RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT;                   -- 备完删归档，释放空间
RMAN> BACKUP VALIDATE DATABASE;                             -- 只校验不写备份

-- 查看备份
RMAN> LIST BACKUP SUMMARY;
RMAN> LIST BACKUP OF DATABASE;
RMAN> REPORT OBSOLETE;
RMAN> REPORT NEED BACKUP;

-- 交叉校验与清理
RMAN> CROSSCHECK BACKUP;
RMAN> DELETE OBSOLETE;
RMAN> DELETE EXPIRED BACKUP;

12.3 恢复

-- 完全恢复（数据文件损坏，有归档）
RMAN> RESTORE DATAFILE 4;
RMAN> RECOVER DATAFILE 4;
RMAN> ALTER DATABASE OPEN;

-- 整库恢复
RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN;

-- 需要 resetlogs 时
RMAN> ALTER DATABASE OPEN RESETLOGS;

12.4 时间点恢复（PITR）

RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;

RMAN> RUN {
  SET UNTIL TIME "TO_DATE('2026-11-05 10:15:00','YYYY-MM-DD HH24:MI:SS')";
  RESTORE DATABASE;
  RECOVER DATABASE;
}

RMAN> ALTER DATABASE OPEN RESETLOGS;

注意：OPEN RESETLOGS 之后原来的归档与备份链断裂，必须立即做一次全备。PITR 前建议先 RESTORE ... PREVIEW 确认所需备份都在。

12.5 控制文件与 SPFILE 恢复

-- 控制文件全丢的恢复流程
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
RMAN> ALTER DATABASE MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN RESETLOGS;

-- SPFILE 丢失
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE SPFILE FROM AUTOBACKUP;
RMAN> STARTUP FORCE;

12.6 备份脚本模板

#!/usr/bin/env bash
# 每日 01:00 全备 + 归档，保留 14 天
export ORACLE_SID=ORCLCDB
export ORACLE_HOME=/opt/oracle/product/26ai/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
LOG=/backup/rman/full_$(date +%F).log

rman target / log=$LOG <<'EOF'
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG DELETE INPUT;
DELETE NOPROMPT OBSOLETE;
CROSSCHECK BACKUP;
DELETE NOPROMPT EXPIRED BACKUP;
EOF

grep -i -E 'error|fail' $LOG && echo "BACKUP FAILED" || echo "BACKUP OK"

13. Data Pump 逻辑备份

13.1 目录对象

SQL> CREATE OR REPLACE DIRECTORY dpump_dir AS '/backup/dpump';
SQL> GRANT READ, WRITE ON DIRECTORY dpump_dir TO system;
SQL> SELECT * FROM dba_directories;

mkdir -p /backup/dpump && chown oracle:oinstall /backup/dpump

13.2 导出

# 整库（CDB 用 full=y 需在 CDB 内执行）
expdp system/pass@ORCLCDB DIRECTORY=dpump_dir DUMPFILE=full_%U.dmp \
  FULL=Y PARALLEL=4 COMPRESSION=ALL LOGFILE=full_exp.log

# 按 schema
expdp system/pass@ORCLPDB1 DIRECTORY=dpump_dir DUMPFILE=app_%U.dmp \
  SCHEMAS=APP PARALLEL=4 COMPRESSION=ALL LOGFILE=app_exp.log

# 按表
expdp system/pass@ORCLPDB1 DIRECTORY=dpump_dir DUMPFILE=orders.dmp \
  TABLES=APP.ORDERS LOGFILE=orders_exp.log

# 只导结构 / 只导数据
expdp ... CONTENT=METADATA_ONLY
expdp ... CONTENT=DATA_ONLY

# 按查询条件导出（11g+）
expdp ... TABLES=APP.ORDERS QUERY="WHERE created_at >= DATE '2026-01-01'"

13.3 导入

# 导入到目标 schema
impdp system/pass@ORCLPDB1 DIRECTORY=dpump_dir DUMPFILE=app_%U.dmp \
  REMAP_SCHEMA=APP:APP_NEW REMAP_TABLESPACE=app_data:app_data_new \
  PARALLEL=4 LOGFILE=app_imp.log

# 只导入元数据，先看 DDL
impdp ... CONTENT=METADATA_ONLY SQLFILE=ddl.sql

# 表已存在时追加数据
impdp ... TABLE_EXISTS_ACTION=APPEND

# 忽略错误继续
impdp ... IGNORE=Y

注意：impdp 默认 TABLE_EXISTS_ACTION=SKIP，表已存在会静默跳过，看起来"导入了但没数据"。覆盖要用 REPLACE（会 DROP 重建）。

13.4 监控与中断处理

SQL> SELECT job_name, operation, job_mode, state FROM dba_datapump_jobs;
SQL> SELECT * FROM dba_datapump_sessions;

# 交互模式（Ctrl+C 进入 export> 提示符）
Export> STATUS
Export> KILL_JOB
Export> CONTINUE_CLIENT

残留任务：Data Pump 任务异常终止会留下 master table（SYS_IMPORT_* / SYS_EXPORT_*），需 DROP TABLE ... PURGE 清理，否则同名任务无法重建。

14. Data Guard 与 ADG

14.1 角色查询

SQL> SELECT database_role, open_mode, protection_mode, switchover_status FROM v$database;

-- 备库
SQL> SELECT process, status, sequence#, thread# FROM v$managed_standby;
SQL> SELECT * FROM v$dataguard_stats;
SQL> SELECT name, value, unit, time_computed FROM v$dataguard_stats WHERE name LIKE '%lag%';

14.2 日志传输

-- 主库
SQL> SELECT dest_id, status, destination, error FROM v$archive_dest WHERE status <> 'INACTIVE';
SQL> SELECT dest_id, sequence#, applied, error FROM v$archive_dest_status;

-- 备库应用
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

14.3 切换与故障转移

-- Switchover（计划内，无数据丢失）
-- 主库
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
-- 原备库
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
SQL> ALTER DATABASE OPEN;

-- Failover（主库不可用，可能丢数据）
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
SQL> ALTER DATABASE OPEN;

注意：Failover 后原主库不能直接回切，必须重建为备库（RMAN DUPLICATE 或 dbms_rolling）。这是 DG 运维最容易低估的成本。

15. 日志体系与告警

15.1 诊断文件位置

SQL> SELECT * FROM v$diag_info;

# alert log（11g 之后推荐用 adrci 或 XML 版）
tail -100 $ORACLE_BASE/diag/rdbms/orclcdb/ORCLCDB/trace/alert_ORCLCDB.log

# adrci 工具
adrci
adrci> SHOW HOMES
adrci> SET HOME diag/rdbms/orclcdb/ORCLCDB
adrci> SHOW ALERT -TAIL 50
adrci> SHOW INCIDENT
adrci> IPS GENERATE PACKAGE IN /tmp

15.2 关键日志项

grep -i -E 'ORA-|error|fail' alert_ORCLCDB.log | tail -40
grep -i 'ORA-01555' alert_ORCLCDB.log
grep -i 'ORA-00257' alert_ORCLCDB.log      # 归档区满，最常见
grep -i 'checkpoint not complete' alert_ORCLCDB.log
grep -i 'Tablespace' alert_ORCLCDB.log | grep -i 'full'

15.3 监听日志

tail -100 $ORACLE_BASE/diag/tnslsnr/$(hostname -s)/listener/trace/listener.log
lsnrctl status
lsnrctl services

15.4 清理日志

# 清理 trace 与 incident（保留 7 天）
adrci
adrci> SET HOME diag/rdbms/orclcdb/ORCLCDB
adrci> PURGE -AGE 10080 -TYPE TRACE
adrci> PURGE -AGE 10080 -TYPE INCIDENT
adrci> SHOW SPACEUSAGE

# 监听日志
adrci> SET HOME diag/tnslsnr/$(hostname -s)/listener
adrci> PURGE -AGE 10080 -TYPE TRACE

16. 性能诊断：AWR / ASH / 锁

16.1 一眼看现状

SQL> SELECT instance_name, status, startup_time FROM v$instance;
SQL> SELECT name, value FROM v$sysstat WHERE name IN
     ('session logical reads','physical reads','physical writes','user commits','user rollbacks','parse count (total)','parse count (hard)');
SQL> SELECT metric_name, value FROM v$sysmetric
     WHERE metric_name IN ('Average Active Sessions','Database CPU Time Ratio','Executions Per Sec','User Transaction Per Sec')
     AND group_id=2;

-- 当前会话
SQL> SELECT sid, serial#, username, status, machine, program, sql_id, event, wait_class, seconds_in_wait
     FROM v$session WHERE type='USER' AND status='ACTIVE' ORDER BY seconds_in_wait DESC;

-- 会话数按用户
SQL> SELECT username, COUNT(*) FROM v$session WHERE type='USER' GROUP BY username ORDER BY 2 DESC;

16.2 AWR 报告

-- 快照设置（默认 1 小时一次，保留 8 天）
SQL> SELECT * FROM dba_hist_wr_control;
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 30, retention => 20160);

-- 手工快照
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

-- 生成报告（交互式）
SQL> @?/rdbms/admin/awrrpt.sql

-- 快照列表
SELECT snap_id, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME FROM dba_hist_snapshot ORDER BY snap_id DESC FETCH FIRST 20 ROWS ONLY;

-- 直接生成指定区间报告（RAC 或脚本化更方便）
SELECT output FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
  (SELECT dbid FROM v$database), 1, 1001, 1002));

AWR 必看的五个部分：

1. Load Profile：每秒逻辑读、事务数、硬解析比例（Hard Parses / Executes > 2% 要查绑定变量）。
2. Top 10 Foreground Events：等待事件占比，DB CPU 之外的第一名通常是瓶颈。
3. SQL ordered by Elapsed Time：总耗时 TOP SQL。
4. Instance Efficiency：Buffer Hit % 应 > 95%，Library Hit % 应 > 99%。
5. Segments by Physical Reads：热点表/索引。

16.3 ASH（会话级历史）

-- 最近 30 分钟活跃会话分布
SELECT session_state, event, COUNT(*) AS samples
FROM v$active_session_history
WHERE sample_time > SYSTIMESTAMP - INTERVAL '30' MINUTE
GROUP BY session_state, event
ORDER BY samples DESC FETCH FIRST 20 ROWS ONLY;

-- 按 SQL 聚合
SELECT sql_id, COUNT(*) AS samples
FROM v$active_session_history
WHERE sample_time > SYSTIMESTAMP - INTERVAL '30' MINUTE
GROUP BY sql_id ORDER BY samples DESC FETCH FIRST 10 ROWS ONLY;

16.4 SQL 诊断

-- 按 SQL_ID 查执行统计
SELECT sql_id, plan_hash_value, executions, elapsed_time/1e6 AS elapsed_s,
       cpu_time/1e6 AS cpu_s, buffer_gets, disk_reads, rows_processed, sql_text
FROM v$sql WHERE sql_id='&sqlid';

-- 历史执行计划变化
SELECT sql_id, plan_hash_value, timestamp, executions, elapsed_time/1e6 AS sec
FROM dba_hist_sqlstat WHERE sql_id='&sqlid' ORDER BY timestamp DESC FETCH FIRST 20 ROWS ONLY;

-- 执行计划
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sqlid', 0, 'ALLSTATS LAST'));

-- 找高负载 SQL（按总耗时）
SELECT sql_id, executions, ROUND(elapsed_time/1e6,1) AS total_sec,
       ROUND(elapsed_time/executions/1000,1) AS avg_ms, SUBSTR(sql_text,1,80) AS sql
FROM v$sql
WHERE executions > 0
ORDER BY elapsed_time DESC FETCH FIRST 20 ROWS ONLY;

-- 找硬解析多的 SQL（未用绑定变量）
SELECT substr(sql_text,1,60) AS sql, COUNT(*) AS versions
FROM v$sql GROUP BY substr(sql_text,1,60) HAVING COUNT(*) > 20 ORDER BY 2 DESC;

-- 清共享池中的某条 SQL（谨慎）
SQL> EXEC DBMS_SHARED_POOL.PURGE('0000000A1B2C3D4E,1234567890', 'C');

16.5 锁与阻塞

-- 阻塞关系（10g+）
SELECT blocking_session, sid, serial#, username, event, seconds_in_wait, sql_id
FROM v$session
WHERE blocking_session IS NOT NULL
ORDER BY seconds_in_wait DESC;

-- 阻塞树
SELECT LEVEL, sid, serial#, username, event, blocking_session, sql_id
FROM v$session
WHERE blocking_session IS NOT NULL
START WITH blocking_session IS NULL AND sid IN (SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL)
CONNECT BY PRIOR sid = blocking_session;

-- 锁明细
SELECT s.sid, s.serial#, s.username, s.osuser, l.type, l.lmode, l.request, l.id1, l.id2,
       o.object_name, s.status
FROM v$lock l
JOIN v$session s ON s.sid = l.sid
LEFT JOIN dba_objects o ON o.object_id = l.id1
WHERE l.type IN ('TM','TX')
ORDER BY l.sid;

-- 被锁对象
SELECT object_name, object_type, session_id, oracle_username, locked_mode
FROM v$locked_object lo JOIN dba_objects o ON o.object_id = lo.object_id;

-- 终止会话（先查 serial#，RAC 需 INST_ID）
SQL> ALTER SYSTEM KILL SESSION '123,45678' IMMEDIATE;
SQL> ALTER SYSTEM DISCONNECT SESSION '123,45678' IMMEDIATE;   -- 更彻底，释放资源
-- RAC
SQL> ALTER SYSTEM KILL SESSION '123,45678,@2' IMMEDIATE;

区别：KILL SESSION 只是标记，等会话自己回滚；DISCONNECT SESSION IMMEDIATE 直接断开进程。大事务回滚可能持续数小时，v$session.status 会显示 KILLED。

16.6 等待事件

-- 当前等待 TOP
SELECT event, wait_class, COUNT(*) AS sessions, SUM(seconds_in_wait) AS total_wait
FROM v$session WHERE state='WAITING' AND type='USER'
GROUP BY event, wait_class ORDER BY sessions DESC;

-- 系统级等待
SELECT event, total_waits, time_waited/100 AS waited_sec, average_wait/100 AS avg_ms
FROM v$system_event
WHERE wait_class <> 'Idle'
ORDER BY time_waited DESC FETCH FIRST 15 ROWS ONLY;

常见等待事件对照：

16.7 服务器层

top -H -p $(pgrep -f ora_pmon | cut -d' ' -f1)
iostat -x 1 5
vmstat 1 5
free -h

SQL> SELECT * FROM v$osstat;
SQL> SELECT * FROM v$sgastat ORDER BY bytes DESC FETCH FIRST 15 ROWS ONLY;
SQL> SELECT name, value/1024/1024 AS mb FROM v$pgastat WHERE name IN
     ('aggregate PGA target parameter','aggregate PGA auto target','total PGA allocated','over allocation count');

17. 空间与容量管理

17.1 快速盘点

-- 库总大小
SELECT ROUND(SUM(bytes)/1024/1024/1024, 2) AS total_gb FROM dba_data_files;

-- 按表空间
SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024/1024,2) AS gb
FROM dba_data_files GROUP BY tablespace_name ORDER BY 2 DESC;

-- 按 schema
SELECT owner, ROUND(SUM(bytes)/1024/1024/1024,2) AS gb
FROM dba_segments GROUP BY owner ORDER BY 2 DESC FETCH FIRST 15 ROWS ONLY;

-- 按对象
SELECT owner, segment_name, segment_type, ROUND(bytes/1024/1024/1024,2) AS gb
FROM dba_segments ORDER BY bytes DESC FETCH FIRST 20 ROWS ONLY;

17.2 空间告警

-- 表空间 > 80%
SELECT tablespace_name, ROUND(used_percent,1) AS pct
FROM dba_tablespace_usage_metrics WHERE used_percent > 80 ORDER BY 2 DESC;

-- 临时表空间使用
SELECT tablespace_name,
       ROUND(SUM(bytes_used)/1024/1024,1) AS used_mb,
       ROUND(SUM(bytes_free)/1024/1024,1) AS free_mb
FROM v$temp_space_header GROUP BY tablespace_name;

-- UNDO 使用与最长查询
SELECT BEGIN_TIME, END_TIME, MAX_QUERY_LEN, MAX_CONCURRENCY
FROM v$undostat ORDER BY BEGIN_TIME DESC FETCH FIRST 10 ROWS ONLY;

-- 归档区使用
SELECT * FROM v$recovery_file_dest;
SELECT file_type, percent_space_used, percent_space_reclaimable FROM v$flash_recovery_area_usage;

17.3 空间回收

-- 1) 表收缩
SQL> ALTER TABLE app.big_logs ENABLE ROW MOVEMENT;
SQL> ALTER TABLE app.big_logs SHRINK SPACE COMPACT;
SQL> ALTER TABLE app.big_logs SHRINK SPACE;

-- 2) 索引重建
SQL> ALTER INDEX app.idx_big REBUILD ONLINE TABLESPACE app_idx;

-- 3) 数据文件缩小（必须先确认文件尾部无数据）
SQL> ALTER DATABASE DATAFILE '/oradata/app_data01.dbf' RESIZE 2G;

-- 4) 归档清理
RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';
RMAN> CROSSCHECK ARCHIVELOG ALL;
RMAN> DELETE EXPIRED ARCHIVELOG ALL;

-- 数据文件可缩小到的最小值
SELECT file_id, file_name,
       ROUND((blocks*block_size)/1024/1024,2) AS size_mb,
       ROUND((blocks*block_size - NVL(hwm,0)*block_size)/1024/1024,2) AS can_shrink_mb
FROM dba_data_files df
LEFT JOIN (SELECT file_id, MAX(block_id+blocks-1) hwm FROM dba_extents GROUP BY file_id) e
  ON df.file_id = e.file_id;

注意：RESIZE 小于高水位会报 ORA-03297。先按上面查询算安全值，或用 SHRINK SPACE 降低高水位后再缩文件。

18. 升级与迁移

18.1 升级路径

11.2 → 12.2 → 19c → 23ai/26ai

规则：

1. 不能跨大版本直跳（11g 不能直接升 19c，需经 12.2 或使用 DBMS_PREUP + 全库导出导入）。
2. 升级前必须跑 预检查工具：

-- 19c 起内置
SQL> @?/rdbms/admin/preupgrade.sql
SQL> @?/rdbms/admin/preupgrade_fixups.sql
SQL> @?/rdbms/admin/utluppkg.sql

-- 12c 用 dbupgdiag.sql 或 MOS 的 preupgrade 脚本

# 自动升级工具（19c+）
$ORACLE_HOME/bin/dbupgrade -s ORCLCDB

18.2 就地升级（DBUA / 手工）

# 1) 全备（必须）
rman target / <<'EOF'
BACKUP DATABASE PLUS ARCHIVELOG;
EOF

# 2) 记录基线
sqlplus / as sysdba <<'SQL'
SPOOL /backup/pre_upgrade_info.txt
SELECT * FROM v$version;
SELECT name, value FROM v$parameter WHERE name IN ('compatible','optimizer_features_enable');
SELECT owner, object_type, COUNT(*) FROM dba_objects WHERE status='INVALID' GROUP BY owner, object_type;
SPOOL OFF
SQL

# 3) 停库、切到新 ORACLE_HOME
# 4) STARTUP UPGRADE，跑 catupgrd.sql（传统方式）
# 5) 或直接用 DBUA / dbupgrade 工具

版本差异：12c 起提供了 catupgrd.sql 的一键式升级与 dbupgrade 包装脚本；19c 起推荐 dbupgrade 或 DBUA。升级后必须检查 dba_registry 中所有组件状态为 VALID，并重编无效对象 @?/rdbms/admin/utlrp.sql。

18.3 逻辑迁移（跨平台/跨云）

# 源库导出（全库，含元数据）
expdp system/pass@SRCDB DIRECTORY=dpump_dir DUMPFILE=full_%U.dmp FULL=Y PARALLEL=8 COMPRESSION=ALL

# 目标库导入
impdp system/pass@DSTDB DIRECTORY=dpump_dir DUMPFILE=full_%U.dmp FULL=Y PARALLEL=8

# 跨版本/跨 endian 平台用 Data Pump + 目标端重建，不要用可传输表空间

18.4 升级后检查

SQL> SELECT comp_name, version, status FROM dba_registry;         -- 全 VALID
SQL> SELECT COUNT(*) FROM dba_objects WHERE status='INVALID';     -- 应为 0
SQL> SELECT * FROM utl_recomp_errors;                              -- 空
SQL> SELECT name, open_mode FROM v$database;
SQL> SELECT * FROM v$timezone_file;                                -- 时区版本
SQL> SELECT * FROM registry$history ORDER BY action_time DESC FETCH FIRST 5 ROWS ONLY;

# 无效对象重编译
sqlplus / as sysdba @?/rdbms/admin/utlrp.sql

19. 高频故障速查

19.1 连不上

# 一分钟定位
echo $ORACLE_SID $ORACLE_HOME
lsnrctl status
ps -ef | grep -E 'pmon|tnslsnr' | grep -v grep
sqlplus -S / as sysdba <<< "SELECT status FROM v\$instance;"

19.2 表空间满

SQL> SELECT tablespace_name, ROUND(used_percent,1) FROM dba_tablespace_usage_metrics;

-- 急救：加数据文件或开自动扩展
SQL> ALTER TABLESPACE app_data ADD DATAFILE '/oradata/app_data03.dbf' SIZE 2G AUTOEXTEND ON NEXT 100M MAXSIZE 32G;
SQL> ALTER DATABASE DATAFILE '/oradata/app_data01.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE 32G;

若报 ORA-01653: unable to extend table，说明表空间确实满了；若报 ORA-01654: unable to extend index，是索引所在表空间满。

19.3 归档区满（ORA-00257）

SQL> SELECT * FROM v$recovery_file_dest;
SQL> SELECT * FROM v$flash_recovery_area_usage;

# 1) 先清过期归档（确认已备份）
rman target / <<'EOF'
CROSSCHECK ARCHIVELOG ALL;
DELETE EXPIRED ARCHIVELOG ALL;
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-3';
EOF

# 2) 或扩大上限
sqlplus / as sysdba <<'SQL'
ALTER SYSTEM SET db_recovery_file_dest_size=800G SCOPE=BOTH;
SQL

注意：归档区满会导致数据库挂起，所有 DML 等待。恢复后要把 db_recovery_file_dest_size 与磁盘余量一起规划，并配归档清理的定时任务。

19.4 会话数/进程数满

SQL> SHOW PARAMETER processes;
SQL> SELECT COUNT(*) FROM v$session;
SQL> SELECT COUNT(*) FROM v$process;

-- 临时清理空闲会话
SELECT sid, serial#, username, machine, last_call_et
FROM v$session WHERE status='INACTIVE' AND last_call_et > 3600 AND username IS NOT NULL;

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- 永久调整（需重启）
ALTER SYSTEM SET processes=2000 SCOPE=SPFILE;
-- sessions 会自动按 1.1*processes+5 推导

报错对应：ORA-00020: maximum number of processes exceeded（进程满）、ORA-12516: TNS:listener could not find available handler（监听找不到可用服务进程，通常也是进程数满）。

19.5 表被锁 / DDL 卡住

SQL> SELECT blocking_session, sid, serial#, username, event, seconds_in_wait, sql_id
     FROM v$session WHERE blocking_session IS NOT NULL ORDER BY seconds_in_wait DESC;

-- 找根源会话在做什么
SQL> SELECT sid, sql_id, sql_text FROM v$session s JOIN v$sql q USING (sql_id)
     WHERE s.sid = &blocking_sid;

-- 处理根源（优先让它提交/回滚，不要直接杀）
SQL> ALTER SYSTEM KILL SESSION '&sid,&serial' IMMEDIATE;

19.6 数据库挂起（Hang）

SQL> SELECT event, COUNT(*) FROM v$session WHERE state='WAITING' GROUP BY event ORDER BY 2 DESC;

# 生成 hang analyze（MOS 标准动作）
sqlplus / as sysdba
SQL> ORADEBUG SETMYPID
SQL> ORADEBUG DUMP HANGANALYZE 3
# 输出在 trace 目录，或直接连跑三次做对比

-- 系统状态转储（重量级，非必要不用）
SQL> ORADEBUG DUMP SYSTEMSTATE 266

19.7 ORA-01555 快照过旧

SQL> SHOW PARAMETER undo_retention;
SQL> SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024/1024,2) gb FROM dba_data_files
     WHERE tablespace_name LIKE 'UNDO%' GROUP BY tablespace_name;
SQL> SELECT MAX(MAXQUERYLEN) FROM v$undostat WHERE BEGIN_TIME > SYSDATE-1;

处理方向：加大 UNDO 表空间、提高 undo_retention、优化跑得久的查询、避免在 UNDO 上开 RETENTION GUARANTEE 之外的不当设置。

19.8 监听异常

lsnrctl status
# TNS-12541 / TNS-12560 → 监听没起
lsnrctl start
# TNS-00511 → 端口被占
ss -lntp | grep 1521
# 监听日志
tail -100 $ORACLE_BASE/diag/tnslsnr/$(hostname -s)/listener/trace/listener.log

20. 危险命令与避坑清单

20.1 变更前检查清单

# 1) 确认实例与容器
sqlplus -S / as sysdba <<'SQL'
SELECT instance_name, host_name, status FROM v$instance;
SELECT name, open_mode, cdb FROM v$database;
SELECT sys_context('USERENV','CON_NAME') FROM dual;
SQL

# 2) 备份
expdp system/pass@ORCLPDB1 DIRECTORY=dpump_dir DUMPFILE=pre_change.dmp SCHEMAS=APP

# 3) 看对象大小
sqlplus -S system/pass@ORCLPDB1 <<'SQL'
SELECT segment_name, ROUND(bytes/1024/1024) mb FROM dba_segments WHERE owner='APP' ORDER BY bytes DESC FETCH FIRST 10 ROWS ONLY;
SQL

# 4) 看锁与长事务
sqlplus -S / as sysdba <<'SQL'
SELECT sid, status, event, seconds_in_wait FROM v$session WHERE status='ACTIVE' AND type='USER';
SELECT * FROM v$transaction WHERE used_ublk > 10000;
SQL

# 5) 观察 DG 延迟
sqlplus -S / as sysdba <<'SQL'
SELECT name, value, unit FROM v$dataguard_stats WHERE name LIKE '%lag%';
SQL

21. 附录：巡检脚本与视图速查

21.1 日常巡检脚本

#!/usr/bin/env bash
# Oracle 日常巡检，输出到 /tmp/oracle_health_$(date +%F).txt
export ORACLE_SID=${ORACLE_SID:-ORCLCDB}
export ORACLE_HOME=${ORACLE_HOME:-/opt/oracle/product/26ai/dbhome_1}
export PATH=$ORACLE_HOME/bin:$PATH
OUT=/tmp/oracle_health_$(date +%F).txt

sqlplus -S / as sysdba > $OUT <<'SQL'
SET LINESIZE 220 PAGESIZE 200 FEEDBACK OFF
COL name FOR a40
COL value FOR a50

PROMPT ========== 实例与数据库 ==========
SELECT instance_name, host_name, version, status, TO_CHAR(startup_time,'YYYY-MM-DD HH24:MI') AS startup
FROM v$instance;
SELECT name, dbid, open_mode, log_mode, database_role, cdb FROM v$database;

PROMPT ========== 表空间使用率 ==========
SELECT tablespace_name, ROUND(used_percent,2) AS pct
FROM dba_tablespace_usage_metrics ORDER BY used_percent DESC;

PROMPT ========== 归档区 ==========
SELECT name, ROUND(space_limit/1024/1024/1024,2) AS limit_gb,
       ROUND(space_used/1024/1024/1024,2) AS used_gb,
       ROUND(space_used/NULLIF(space_limit,0)*100,2) AS pct
FROM v$recovery_file_dest;

PROMPT ========== 会话与进程 ==========
SELECT COUNT(*) AS sessions, (SELECT COUNT(*) FROM v$process) AS processes,
       (SELECT value FROM v$parameter WHERE name='processes') AS max_processes
FROM v$session;

PROMPT ========== 活跃会话 TOP ==========
SELECT sid, username, event, wait_class, seconds_in_wait, status
FROM v$session WHERE status='ACTIVE' AND type='USER'
ORDER BY seconds_in_wait DESC FETCH FIRST 10 ROWS ONLY;

PROMPT ========== 阻塞 ==========
SELECT blocking_session, sid, username, event, seconds_in_wait
FROM v$session WHERE blocking_session IS NOT NULL;

PROMPT ========== 无效对象 ==========
SELECT owner, object_type, COUNT(*) FROM dba_objects WHERE status='INVALID'
GROUP BY owner, object_type;

PROMPT ========== 高负载 SQL TOP5 ==========
SELECT sql_id, executions, ROUND(elapsed_time/1e6,1) AS total_sec,
       ROUND(elapsed_time/NULLIF(executions,0)/1000,1) AS avg_ms
FROM v$sql WHERE executions > 0 ORDER BY elapsed_time DESC FETCH FIRST 5 ROWS ONLY;

PROMPT ========== 备份状态（近 3 天） ==========
SELECT session_key, input_type, status,
       TO_CHAR(start_time,'YYYY-MM-DD HH24:MI') AS start_time,
       ROUND(output_bytes/1024/1024/1024,2) AS out_gb
FROM v$rman_backup_job_details
WHERE start_time > SYSDATE - 3 ORDER BY start_time DESC;

PROMPT ========== DG 延迟 ==========
SELECT name, value, unit FROM v$dataguard_stats WHERE name LIKE '%lag%';
SQL

echo "报告已生成：$OUT"
grep -i -E 'ORA-|ERROR' $OUT && echo "存在错误，请检查" || echo "无 ORA- 错误"

21.2 常用视图速查

21.3 关键路径与端口

附：与工作区其他手册的配合

• 操作系统层见 CentOS-7-10-常规运维指令手册.md / Ubuntu-常用运维指令手册.md
• 放行端口：firewall-cmd --add-port=1521/tcp --permanent
• Oracle 对内核参数有硬要求（shmmax、shmall、sem、nofile），预安装 RPM 会自动配置；手工安装需按官方文档调 /etc/sysctl.conf 与 /etc/security/limits.conf
• 与 MySQL/PG 的核心差异：多租户容器概念、必须开归档才能做在线备份与 PITR、RMAN 是唯一可靠的全备方式、db_recovery_file_dest_size 满会导致全库挂起