Oracle 数据库面试题

答案：

Oracle 数据库体系结构由**数据库（Database）和实例（Instance）**两大部分构成，采用经典的客户端-服务器架构，在物理上由存储在磁盘上的数据文件集合组成，在内存中由内存结构与后台进程共同协作。

核心组成：

实例与数据库的对应关系：

• 单实例单库（Single Instance）：1 个 Instance 对应 1 个 Database，常见于中小型生产或开发环境。
• RAC（Real Application Clusters）：多个 Instance 同时挂载并打开同一个 Database，所有实例共享存储，节点间通过 Cache Fusion 机制同步 SGA 中的数据块。
• Data Guard：通过主库（Primary）的 Redo 传输到备库（Standby）实现数据保护，备库可挂载为物理备库（Physical Standby，受 Redo Apply 应用）或逻辑备库（Logical Standby，受 SQL Apply 应用）。

多租户架构（Multitenant Architecture，12c+）：

12c 引入 CDB（Container Database）+ PDB（Pluggable Database）架构，23ai 起 CDB 为唯一架构（非 CDB 不再支持）。1 个 CDB 包含 1 个根容器（CDB$ROOT）、1 个种子容器（PDB$SEED）和 0 到 N 个应用 PDB。CDB 统一管理内存、进程与系统表空间，PDB 之间通过插拔（Plug/Unplug）实现快速迁移，多租户显著降低单位 PDB 的资源消耗与运维成本。

典型连接路径：

graph LR
    A["Client (sqlplus/JDBC)"] -->|"tnsnames.ora"| B["Listener"]
    B -->|"spawn"| C["Server Process"]
    C --> D["SGA / PGA"]
    D --> E["Datafiles / Redo / Controlfile"]

答案：

表空间是 Oracle 逻辑存储的最高层抽象，由一个或多个物理**数据文件（Datafile）**组成。表空间作为段（Segment）、区（Extent）、块（Block）的逻辑容器，屏蔽了底层物理文件分布的复杂性，简化了存储管理与空间配额控制。

存储层次结构：

表空间类型：

关键管理操作：

-- 创建表空间
CREATE TABLESPACE tbs_app
    DATAFILE '/u01/oradata/ORCL/tbs_app01.dbf' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 32G
    EXTENT MANAGEMENT LOCAL
    SEGMENT SPACE MANAGEMENT AUTO;

-- 增加数据文件
ALTER TABLESPACE tbs_app ADD DATAFILE '/u01/oradata/ORCL/tbs_app02.dbf' SIZE 10G;

-- 重命名数据文件（MOUNT 状态下）
ALTER DATABASE RENAME FILE '/old/path.dbf' TO '/new/path.dbf';

-- 在线/离线表空间
ALTER TABLESPACE tbs_app ONLINE;
ALTER TABLESPACE tbs_app OFFLINE NORMAL;  -- 正常离线前做检查点

SYSTEM 与 SYSAUX 表空间不可 OFFLINE，普通表空间通过 OFFLINE NORMAL/IMMEDIATE 切换状态（IMMEDIATE 不做检查点，恢复时需介质恢复）。

答案：

Oracle 实例的内存由 **SGA（System Global Area，系统全局区）**和 **PGA（Program Global Area，程序全局区）**两大部分组成。SGA 是实例共享内存，所有 Server Process 共享访问；PGA 是 Server Process 私有内存，每个会话独占。

SGA 核心组件：

PGA 关键组成：

自动内存管理：

• AMM（Automatic Memory Management，11g+）：MEMORY_TARGET 统一管理 SGA + PGA，Oracle 自动调优组件比例。
• ASMM（Automatic Shared Memory Management）：SGA_TARGET 设定后由 Oracle 自动调整 Buffer Cache、Shared Pool、Large Pool、Java Pool 内部比例（需为非零）。
• Automatic PGA Management：PGA_AGGREGATE_TARGET 设定后由 Oracle 自动按需分配工作区，替代 _PGA_MAX_SIZE 手动控制。

-- 启用 ASMM
ALTER SYSTEM SET SGA_TARGET = 8G SCOPE=SPFILE;
ALTER SYSTEM SET SHARED_POOL_SIZE = 0;  -- 0 表示由 ASMM 自动管理

-- 启用 AMM（需使用 /dev/shm 或 HUGE 页）
ALTER SYSTEM SET MEMORY_TARGET = 16G SCOPE=SPFILE;
ALTER SYSTEM SET MEMORY_MAX_TARGET = 16G SCOPE=SPFILE;

OLTP 与 DSS 系统内存分配差异：

OLTP 系统以Buffer Cache为最大开销（数据随机访问频繁），DSS/数据仓库系统以Large Pool + PGA Work Area为最大开销（大规模排序与 Hash Join）。

答案：

Oracle 关键后台进程通过协作完成事务持久化与实例恢复两大核心职责，是 Oracle 写入路径的核心引擎。

进程职责：

写入路径与协作：

sequenceDiagram
    participant U as User Process
    participant S as Server Process
    participant LB as Redo Log Buffer
    participant LG as LGWR
    participant BC as Buffer Cache
    participant DB as DBWn
    participant DF as Datafile
    participant OL as Online Redo Log

    U->>S: COMMIT
    S->>LB: 写入 Redo 条带
    S->>LG: 触发 LGWR
    LG->>OL: 写入 Online Redo Log
    LG-->>S: 写盘成功（Log Force at Commit）
    S-->>U: COMMIT 成功
    Note over DB,DF: 脏块由 DBWn 异步写回
    DB->>BC: 扫描脏块
    DB->>DF: 写入数据文件

关键协作点：

1. LGWR 写入先行（Write-Ahead Logging）：DBWn 写脏块前，对应 Redo 必须先由 LGWR 写入 Online Redo Log，确保实例恢复可通过 Redo 重做。
2. CKPT 触发 DBWn：CKPT 触发检查点后将更新数据文件头与控制文件的检查点 SCN（DBA_RCHIVE_LOG、V$DATABASE），减少实例恢复时间。
3. ARCn 接力归档：当 LGWR 切换 Log Group 时（Log Switch），ARCn 将已满的 Redo Log 复制到归档目录，是时间点恢复（PITR）的前提。

答案：

**Redo Log（重做日志）**与 **Undo（撤销数据）是 Oracle 事务持久化与读一致性的两大基石，分别承担前滚（Roll Forward）与回滚（Roll Back）**职责，二者缺一不可。

Redo Log 机制：

Undo 机制：

两者协作实现实例恢复：

graph TD
    A["实例崩溃"] --> B["SMON 执行实例恢复"]
    B --> C["Cache Recovery（缓存恢复）"]
    C --> D["重做（Roll Forward）
应用 Redo Log"]
    D --> E["回滚（Roll Back）
应用 Undo 回滚未提交事务"]
    E --> F["数据库一致性"]

• 前滚（Cache Recovery）：用 Redo Log 重做已提交但未写入数据文件的事务。
• 回滚（Transaction Recovery）：用 Undo 回滚未提交的事务，恢复一致性。

闪回技术（Flashback）基于 Undo：

答案：

锁（Lock）与闩锁（Latch）是 Oracle 并发控制的两个层级，前者面向业务数据，保证事务级 ACID，由队列管理；后者面向内存结构，保护内存数据结构的互斥访问，粒度极轻、持续时间极短。

对比分析：

锁的类型：

闩锁争用诊断：

-- 顶级闩锁争用统计
SELECT latch#, name, gets, misses, sleeps, immediate_misses, immediate_gets
FROM v$latch
ORDER BY (misses - immediate_misses) DESC
FETCH FIRST 10 ROWS ONLY;

-- Buffer Busy Waits（闩锁级）热点块
SELECT objd, object_name, file#, dbablk, count(*)
FROM v$bh b JOIN dba_objects o ON b.objd = o.data_object_id
WHERE b.class# = 1
GROUP BY objd, object_name, file#, dbablk
ORDER BY count(*) DESC;

答案：

Oracle 通过Undo-based MVCC实现语句级与事务级读一致性，查询不会阻塞写入，写入也不会阻塞读，这是 Oracle 在高并发场景下保持高吞吐的核心机制。

核心原理：

当一个查询在某一时刻启动时，Oracle 记录当前 SCN（System Change Number），查询过程中访问每个数据块时都校验块的 SCN：

• 数据块 SCN ≤ 查询 SCN：块中数据可见，直接返回。
• 数据块 SCN > 查询 SCN：块在查询启动后被修改，Oracle 通过**一致性读（Consistent Read）**机制构造 CR Copy（Consistent Read Copy），从 Undo 中逐层回滚到查询 SCN 对应的前镜像。

两种读一致性级别：

Flashback Query 可通过 as of timestamp/as of scn 显式指定读一致性时间点。

关键争用与错误：

• ORA-01555: Snapshot too old：Undo 已被覆盖，CR Copy 无法构造。调大 UNDO_RETENTION、启用 RETENTION GUARANTEE、优化大查询减少 Undo 访问。
• ORA-30036: unable to extend segment in undo tablespace：Undo 表空间不足，无法为新事务分配 Undo Segment。
• 事务回滚段（Rollback）竞争：高频小事务在 Undo 表空间内分配大量 Segment，导致空间碎片与 undo segment tx slot 争用。

对比其他数据库：

答案：

**AWR（Automatic Workload Repository）**与 **ASH（Active Session History）**是 Oracle 内置的性能数据仓库，是企业级性能诊断的事实标准，由 MMON/MMNL 进程自动收集与维护。

核心组件：

AWR 关键视图：

核心报告：

-- AWR 报告（指定快照区间）
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
-- 输入 begin_snap、end_snap、report_type (HTML/TEXT)

-- ASH 报告（默认采样区间）
@$ORACLE_HOME/rdbms/admin/ashrpt.sql

-- ADDM 报告
@$ORACLE_HOME/rdbms/admin/addmrpt.sql

-- SQL 详细报告
@$ORACLE_HOME/rdbms/admin/sqlrpt.sql

关键性能指标：

典型诊断流程：

1. AWR Top 5 Event → 定位主导等待事件（db file sequential read、latch: shared pool、enq: TX）。
2. ASH → 拉取特定时间段内的活跃会话 + SQL 文本。
3. SQL 报告 → 分析执行计划、Buffer Gets、Disk Reads。
4. Segment 统计 → 定位热点对象（DBA_HIST_SEG_STAT_OBJ）。

答案：

**RMAN（Recovery Manager）**是 Oracle 官方推荐的备份恢复工具，深度集成数据库内核，支持增量备份、块级损坏检测、自动化恢复脚本与时间点恢复（PITR），是企业级数据保护的事实标准。

核心概念：

常用备份策略：

# 全量备份 + 归档
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;

# Level 0 增量基线
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;

# Level 1 差异增量（累积：CUMULATIVE 或差异：DIFFERENTIAL）
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE PLUS ARCHIVELOG;

典型恢复场景：

最佳实践：

1. 启用 Block Change Tracking 加速增量。
2. 配置 FRA 统一管理备份空间。
3. 使用 RMAN Catalog 集中管理多库。
4. 定期演练异机恢复，验证备份完整性。
5. 备份完成后执行 RESTORE VALIDATE 与 VALIDATE BACKUPSET 验证可恢复性。

答案：

Data Guard是 Oracle 企业级高可用与灾备（HADR）核心方案，通过 Redo 传输与应用实现 Primary 与 Standby 的实时同步，提供**Switchover（计划内切换）与Failover（故障切换）**两大灾难保护能力。

角色与拓扑：

Redo 传输与服务：

Data Guard Broker：

DGMGRL（Data Guard Broker CLI）将 Primary / Standby / Observer 抽象为统一配置（DGConfig），支持快速 Switchover / Failover、自动化健康监控与 FSFO（Fast-Start Failover）。

DGMGRL> CREATE CONFIGURATION dg_config AS PRIMARY DATABASE IS primary CONNECT IDENTIFIER IS primary_tns;
DGMGRL> ADD DATABASE standby AS CONNECT IDENTIFIER IS standby_tns MAINTAINED AS PHYSICAL;
DGMGRL> ENABLE CONFIGURATION;
DGMGRL> SWITCHOVER TO standby;
DGMGRL> FAILOVER TO standby;  -- 需启用 Fast-Start Failover

Active Data Guard 优势：

• 备库实时查询分担主库读负载。
• 备库持续应用 Redo，RPO 接近 0。
• 与 RMAN 集成在备库做备份（BACKUP ... FROM ACTIVE DATABASE），减轻主库负担。

答案：

Oracle GoldenGate（OGG）是 Oracle 战略级的逻辑复制产品，基于Redo Log 挖掘捕获增量变更，通过 TCP/IP 实时投递到目标端并应用，支持同构 / 异构、一对一 / 多对一 / 一对多 / 双向等复杂拓扑。

核心组件：

复制模式：

双向复制与冲突处理：

-- 双向复制（Active-Active）需要为每张表补充主键
-- 并配置冲突检测与解决（CDR：Conflict Detection and Resolution）

-- 配置 CDR
TABLE hr.employee, COLMAP (resolved_by = "GG_RESOLV_COLUMN"),
  KEYCOLS (employee_id),
  FILTER (resolve_conflict(employ_id, employee_id, employee_id_delta, ...));

异构复制：

• Oracle → MySQL / PostgreSQL / Kafka（用作 CDC 数据管道）
• MySQL → Oracle
• SQL Server → Oracle
• Kafka Connect Connector for Oracle（基于 OGG）

OGG 已成为大型企业实现实时数据集成、容灾备份、数据湖实时入湖的核心中间件。

答案：

Oracle SQL 优化是综合性工程，需要从执行计划、统计信息、索引设计、SQL 改写、Hint 控制、参数调优等多维度切入，遵循"先诊断、后优化、再验证“的工程化方法。

优化器与执行计划：

执行计划获取：

-- EXPLAIN PLAN（预估）
EXPLAIN PLAN FOR
SELECT * FROM orders o JOIN customers c ON o.cust_id = c.id WHERE c.region = 'APAC';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL +ADAPTIVE'));

-- 实际执行计划（更准确）
SELECT /*+ GATHER_PLAN_STATISTICS */ ...
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

-- AWR 历史执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(sql_id => 'abc123', plan_hash_value => NULL));

核心执行计划操作：

统计信息管理：

-- 收集表 / Schema 统计信息
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'HR',
    tabname => 'ORDERS',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO',
    cascade => TRUE,
    degree => 4
  );
END;
/

-- 收集直方图（数据分布偏斜）
DBMS_STATS.GATHER_TABLE_STATS(..., method_opt => 'FOR COLUMNS SIZE 254 region');

-- 动态采样（统计信息缺失时）
SELECT /*+ DYNAMIC_SAMPLING(o 4) */ ...

SQL 改写核心技巧：

Hint 进阶：

SELECT /*+ INDEX(o idx_orders_region) USE_NL(o c) PARALLEL(o 4) */
       o.order_id, c.cust_name
FROM orders o JOIN customers c ON o.cust_id = c.id
WHERE o.region = 'APAC';

答案：

表分区（Table Partitioning）是 Oracle 处理海量数据的核心技术，通过将大表按规则拆分为多个物理 Segment（Partition），在查询裁剪（Partition Pruning）、并行执行、数据维护三大场景下显著提升性能与可维护性。

分区类型：

RANGE 分表示例：

CREATE TABLE orders (
    order_id     NUMBER,
    order_date   DATE,
    cust_id      NUMBER,
    amount       NUMBER
)
PARTITION BY RANGE (order_date) (
    PARTITION p202401 VALUES LESS THAN (TO_DATE('2024-02-01','YYYY-MM-DD')),
    PARTITION p202402 VALUES LESS THAN (TO_DATE('2024-03-01','YYYY-MM-DD')),
    PARTITION p202403 VALUES LESS THAN (TO_DATE('2024-04-01','YYYY-MM-DD')),
    PARTITION p_max VALUES LESS THAN (MAXVALUE)
)
ENABLE ROW MOVEMENT  -- 分区键更新时自动迁移行
PARALLEL 4;

-- INTERVAL 自动分区（11g+）
CREATE TABLE orders_interval (
    ...
)
PARTITION BY RANGE (order_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(PARTITION p_init VALUES LESS THAN (TO_DATE('2024-01-01','YYYY-MM-DD')));

性能优势：

关键运维操作：

-- 新增分区
ALTER TABLE orders ADD PARTITION p202405 VALUES LESS THAN (TO_DATE('2024-06-01','YYYY-MM-DD'));

-- 分区 SPLIT（拆分）
ALTER TABLE orders SPLIT PARTITION p_max AT (TO_DATE('2024-12-01','YYYY-MM-DD')) INTO (PARTITION p202412, PARTITION p_max);

-- MERGE 合并
ALTER TABLE orders MERGE PARTITIONS p202401, p202402 INTO PARTITION p2024_q1;

-- 交换分区（极速加载：归档表 → 分区表）
ALTER TABLE orders EXCHANGE PARTITION p202401 WITH TABLE orders_archive INCLUDING INDEXES;

-- DROP 分区（秒级）
ALTER TABLE orders DROP PARTITION p202401;

-- TRUNCATE 分区（保留表结构）
ALTER TABLE orders TRUNCATE PARTITION p202401;

索引策略：

-- LOCAL 索引
CREATE INDEX idx_orders_cust ON orders(cust_id) LOCAL PARALLEL 4;

-- 分区索引（不与表分区一致）
CREATE INDEX idx_orders_global ON orders(order_id) GLOBAL PARTITION BY RANGE (order_id) (...);

答案：

Oracle 字符集体系涵盖数据库字符集与国家字符集两个维度，决定了数据的存储编码与跨库、跨语言交互的正确性。配置错误将导致字符乱码、数据截断、导入导出失败等长期隐患。

字符集分级：

常见字符集：

AL32UTF8 与 UTF8 的区别：

• AL32UTF8（推荐）：完整 Unicode，支持 4 字节字符（如 emoji）。
• UTF8（已弃用）：仅支持 1-3 字节，无法存储部分辅助平面字符。

字符集迁移：

# 完整字符集迁移（WE8ISO8859P1 -> AL32UTF8）
csscan CHARACTER_SET WE8ISO8859P1 FULL=Y TOCHAR=AL32UTF8 LOG=csscan.log

# CSSCAN 输出无 Convertible / Truncation 错误后执行迁移
csalter CHARACTER_SET AL32UTF8 ASSM=TRUE

# 跨字符集导入导出（避免乱码）
expdp system/*** DIRECTORY=dp DUMPFILE=exp.dmp LOGFILE=exp.log \
      CONTENT=ALL
# 客户端设置 NLS_LANG 与源库一致

NLS 体系（National Language Support）：

NLS 三层设置：

1. 数据库初始化参数（SPFILE 中）：所有会话默认。
2. 实例 ALTER SESSION：会话级覆盖。
3. 客户端 NLS_LANG 环境变量：客户端转换行为。

-- 实例级修改 NLS
ALTER SYSTEM SET NLS_LANGUAGE = 'SIMPLIFIED CHINESE' SCOPE = SPFILE;
ALTER SYSTEM SET NLS_TERRITORY = 'CHINA' SCOPE = SPFILE;

-- 会话级
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_SORT = 'SCHINESE_PINYIN_M';  -- 按拼音排序

常见故障与排查：

答案：

Oracle RAC（Real Application Clusters）是 Oracle 集群数据库方案，多个实例（Instance）同时挂载并打开同一个 Database（共享存储），对外表现为单一逻辑数据库，提供水平扩展与高可用能力。

核心组件：

Cache Fusion 工作机制：

sequenceDiagram
    participant N1 as Instance-1 (持有块)
    participant N2 as Instance-2 (请求块)
    participant D as 共享存储

    N2->>N1: 请求块（consistent read / current read）
    N1->>N2: 通过 LMS 私有网络传送块（Cache-to-Cache）
    Note over N2: 块到达后获取对应模式的锁
    N1->>D: 块被写脏后写回磁盘

GCS 三种资源模式：

GES 锁类型（PCM 锁）：

RAC 高可用能力：

RAC vs Data Guard 对比：

RAC 调优要点：

1. 私有网络（Interconnect）：建议 10Gbps+ RDMA（RoCE / iWARP），延迟 < 1ms。
2. DRM（Dynamic Resource Management）：缓存融合请求路径优化。
3. LMS 进程数：GCS_SERVER_PROCESSES 控制，建议 4-8 个（高争用环境增加）。
4. 大块大小：减少 LMS 消息数量，建议 DB_BLOCK_SIZE = 16K 或 32K。
5. Service 设计：不同业务（OLTP / Batch / DSS）拆分 Service，绑定到专属实例避免相互干扰。