Oracle数据库面试高频问题详解助你轻松应对技术面试掌握核心知识点提升通过率从基础到进阶全面解析

威震华夏关云长

一、Oracle基础概念

1. Oracle数据库架构

Oracle数据库采用多进程架构，主要由实例(Instance)和数据库(Database)两部分组成。实例是内存结构和后台进程的集合，而数据库则是磁盘上的物理文件集合。

实例组件包括：

• SGA(System Global Area)：系统全局区，包括共享池(Shared Pool)、数据缓冲区(Database Buffer Cache)、重做日志缓冲区(Redo Log Buffer)等
• 后台进程：PMON(进程监控器)、SMON(系统监控器)、DBWn(数据库写入进程)、LGWR(日志写入进程)、CKPT(检查点进程)等

数据库文件包括：

• 数据文件(Data Files)：存储实际数据
• 控制文件(Control Files)：记录数据库物理结构信息
• 重做日志文件(Redo Log Files)：记录数据库变更
• 参数文件(Parameter Files)：配置数据库参数

面试中常问的问题：

2. Q: 简述Oracle数据库启动过程?
3. A: Oracle数据库启动分为三个阶段：
4. 1. NOMOUNT阶段：读取参数文件，分配SGA，启动后台进程
5. 2. MOUNT阶段：读取控制文件，获取数据文件和重做日志文件信息
6. 3. OPEN阶段：打开所有数据文件和重做日志文件，数据库可用

复制代码

2. 表空间与数据文件

表空间(Tablespace)是Oracle数据库的逻辑存储单元，由一个或多个数据文件组成。Oracle中的所有数据都存储在表空间中。

常见表空间类型：

• SYSTEM表空间：存储数据字典信息
• SYSAUX表空间：存储系统辅助信息
• UNDO表空间：存储回滚信息
• TEMP表空间：存储临时数据
• 用户表空间：存储用户数据

2. -- 创建表空间的示例
3. CREATE TABLESPACE users_tbs
4. DATAFILE '/u01/oradata/users01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 500M,
5.          '/u01/oradata/users02.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 500M
6. EXTENT MANAGEMENT LOCAL
7. SEGMENT SPACE MANAGEMENT AUTO;
9. -- 查看表空间使用情况
10. SELECT tablespace_name,
11.        ROUND(used_space*8192/1024/1024,2) "Used Space (MB)",
12.        ROUND(tablespace_size*8192/1024/1024,2) "Total Size (MB)",
13.        ROUND(used_space/tablespace_size*100,2) "Used Percent"
14. FROM dba_tablespace_usage_metrics;

复制代码

面试中常问的问题：

2. Q: 什么是本地管理的表空间?与字典管理的表空间有什么区别?
3. A: 本地管理的表空间(Locally Managed Tablespace)使用位图来跟踪空间使用情况，信息存储在表空间自身的数据文件头部。
4. 而字典管理的表空间(Dictionary Managed Tablespace)使用数据字典表(如SYS.UET$和SYS.FET$)来跟踪空间使用情况。
6. 区别：
7. 1. 本地管理表空间减少了数据字典争用，提高了性能
8. 2. 本地管理表空间避免了递归空间操作
9. 3. 本地管理表空间可以自动管理空间，减少碎片
10. 4. 本地管理表空间是Oracle推荐的方式，字典管理表空间已废弃

复制代码

二、SQL语言相关面试问题

1. 基本SQL查询

SQL(Structured Query Language)是与Oracle数据库交互的标准语言。面试中经常涉及各种SQL查询技巧。

2. -- 基本SELECT语句
3. SELECT employee_id, first_name, last_name, salary
4. FROM employees
5. WHERE department_id = 50
6. ORDER BY salary DESC;
8. -- 使用聚合函数
9. SELECT department_id,
10.        COUNT(*) employee_count,
11.        AVG(salary) avg_salary,
12.        MAX(salary) max_salary,
13.        MIN(salary) min_salary
14. FROM employees
15. GROUP BY department_id
16. HAVING AVG(salary) > 5000;
18. -- 多表连接
19. SELECT e.employee_id, e.first_name, e.last_name, d.department_name
20. FROM employees e
21. JOIN departments d ON e.department_id = d.department_id
22. WHERE e.salary > (SELECT AVG(salary) FROM employees);

复制代码

面试中常问的问题：

2. Q: 解释Oracle中的各种连接类型及其区别?
3. A: Oracle中的连接类型包括：
5. 1. 内连接(INNER JOIN)：只返回两个表中匹配条件的行
6.    SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;
8. 2. 左外连接(LEFT OUTER JOIN)：返回左表所有行，以及右表中匹配的行，右表不匹配的行显示NULL
9.    SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id;
11. 3. 右外连接(RIGHT OUTER JOIN)：返回右表所有行，以及左表中匹配的行，左表不匹配的行显示NULL
12.    SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id;
14. 4. 全外连接(FULL OUTER JOIN)：返回两个表中的所有行，不匹配的行显示NULL
15.    SELECT * FROM table1 FULL JOIN table2 ON table1.id = table2.id;
17. 5. 笛卡尔连接(CROSS JOIN)：返回两个表的笛卡尔积
18.    SELECT * FROM table1 CROSS JOIN table2;
20. 6. 自连接(SELF JOIN)：表与自身连接
21.    SELECT a.name, b.name as manager_name
22.    FROM employees a, employees b
23.    WHERE a.manager_id = b.employee_id;

复制代码

2. 高级查询技巧

Oracle提供了许多高级查询功能，如子查询、层次查询、分析函数等。

2. -- 子查询示例
3. SELECT employee_id, first_name, last_name, salary
4. FROM employees
5. WHERE salary > (SELECT AVG(salary) FROM employees);
7. -- 层次查询示例(组织结构)
8. SELECT LEVEL, LPAD(' ', 2*(LEVEL-1)) || last_name AS org_structure, employee_id, manager_id
9. FROM employees
10. START WITH manager_id IS NULL
11. CONNECT BY PRIOR employee_id = manager_id
12. ORDER SIBLINGS BY last_name;
14. -- 分析函数示例(员工薪资排名)
15. SELECT employee_id, first_name, last_name, salary,
16.        RANK() OVER (ORDER BY salary DESC) salary_rank,
17.        DENSE_RANK() OVER (ORDER BY salary DESC) dense_rank,
18.        ROW_NUMBER() OVER (ORDER BY salary DESC) row_num
19. FROM employees
20. WHERE department_id = 50;
22. -- 分组分析示例(部门内薪资排名)
23. SELECT department_id, employee_id, first_name, last_name, salary,
24.        RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) dept_rank
25. FROM employees;

复制代码

面试中常问的问题：

2. Q: 解释RANK(), DENSE_RANK()和ROW_NUMBER()的区别?
3. A: 这三个都是分析函数，用于排序，但处理并列值的方式不同：
5. 1. RANK(): 当有并列值时，会跳过后续排名。例如，如果有两个第1名，下一个排名将是第3名。
6.    示例：100(1), 100(1), 90(3), 80(4)
8. 2. DENSE_RANK(): 当有并列值时，不会跳过后续排名。例如，如果有两个第1名，下一个排名将是第2名。
9.    示例：100(1), 100(1), 90(2), 80(3)
11. 3. ROW_NUMBER(): 为每一行分配一个唯一的序号，不考虑并列值。
12.    示例：100(1), 100(2), 90(3), 80(4)
14. 使用场景：
15. - RANK()适用于需要跳过排名的场景，如比赛排名
16. - DENSE_RANK()适用于不跳过排名的场景，如百分位排名
17. - ROW_NUMBER()适用于需要唯一序号的场景，如分页查询

复制代码

三、数据库管理与优化

1. 索引管理

索引是提高数据库查询性能的重要工具，但也会增加DML操作的开销。

2. -- 创建B树索引
3. CREATE INDEX idx_emp_last_name ON employees(last_name);
5. -- 创建复合索引
6. CREATE INDEX idx_emp_dept_salary ON employees(department_id, salary);
8. -- 创建位图索引(适用于低基数列)
9. CREATE BITMAP INDEX idx_emp_gender ON employees(gender);
11. -- 创建函数索引
12. CREATE INDEX idx_emp_upper_name ON employees(UPPER(last_name));
14. -- 查看索引信息
15. SELECT index_name, index_type, uniqueness, status
16. FROM user_indexes
17. WHERE table_name = 'EMPLOYEES';
19. -- 重建索引
20. ALTER INDEX idx_emp_last_name REBUILD;
22. -- 监控索引使用情况
23. ALTER INDEX idx_emp_last_name MONITORING USAGE;
24. SELECT * FROM v$object_usage;

复制代码

面试中常问的问题：

2. Q: 什么情况下索引不会被使用?
3. A: 以下情况可能导致索引不被使用：
5. 1. 使用了NOT、!=、<>等否定操作符
6.    SELECT * FROM employees WHERE department_id != 50;
8. 2. 对索引列使用了函数
9.    SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';
11. 3. 对索引列进行了计算
12.    SELECT * FROM employees WHERE salary + 1000 > 5000;
14. 4. 使用了IS NULL或IS NOT NULL(普通B树索引不存储NULL值)
15.    SELECT * FROM employees WHERE commission_pct IS NULL;
17. 5. 使用了LIKE操作符且以通配符开头
18.    SELECT * FROM employees WHERE last_name LIKE '%ITH';
20. 6. 数据类型不匹配
21.    SELECT * FROM employees WHERE employee_id = '100';  -- employee_id是数字类型
23. 7. CBO优化器认为全表扫描更高效(如返回表中大部分数据)
25. 8. 索引失效或状态不正常

复制代码

2. 执行计划与SQL调优

理解执行计划是SQL调优的关键。Oracle提供了多种工具来查看和分析执行计划。

2. -- 使用EXPLAIN PLAN查看执行计划
3. EXPLAIN PLAN FOR
4. SELECT e.employee_id, e.first_name, d.department_name
5. FROM employees e
6. JOIN departments d ON e.department_id = d.department_id
7. WHERE e.salary > 5000;
9. -- 显示执行计划
10. SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
12. -- 使用AUTOTRACE查看执行计划和统计信息
13. SET AUTOTRACE ON EXPLAIN STATISTICS;
14. SELECT e.employee_id, e.first_name, d.department_name
15. FROM employees e
16. JOIN departments d ON e.department_id = d.department_id
17. WHERE e.salary > 5000;
18. SET AUTOTRACE OFF;
20. -- 使用SQL*Plus的TRACE功能
21. ALTER SESSION SET SQL_TRACE = TRUE;
22. -- 执行SQL语句
23. ALTER SESSION SET SQL_TRACE = FALSE;
25. -- 使用TKPROF格式化跟踪文件
26. -- 在操作系统上执行: tkprof tracefile.trc outputfile.txt sys=no
28. -- 使用10046事件进行更详细的跟踪
29. ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
30. -- 执行SQL语句
31. ALTER SESSION SET EVENTS '10046 trace name context off';

复制代码

面试中常问的问题：

2. Q: 解释Oracle中的表连接方法及其适用场景?
3. A: Oracle中的表连接方法主要有以下几种：
5. 1. 嵌套循环连接(Nested Loops Join)
6.    - 原理：对于外部表的每一行，扫描内部表找到匹配行
7.    - 适用场景：外部表较小，内部表上有高效索引；结果集较小
8.    - 提示：USE_NL
10. 2. 哈希连接(Hash Join)
11.    - 原理：将较小表构建为哈希表，然后扫描较大表进行哈希匹配
12.    - 适用场景：等值连接，一个表明显小于另一个表
13.    - 提示：USE_HASH
15. 3. 排序合并连接(Sort Merge Join)
16.    - 原理：对两个表按连接列排序，然后合并排序后的结果
17.    - 适用场景：非等值连接(如>、<、BETWEEN)，数据集较大且已排序
18.    - 提示：USE_MERGE
20. 4. 笛卡尔连接(Cartesian Join)
21.    - 原理：生成两个表的笛卡尔积
22.    - 适用场景：很少使用，通常是由于缺少连接条件导致的错误
23.    - 提示：USE_MERGE(无连接条件时)
25. 选择连接方法的考虑因素：
26. - 表的大小和分布
27. - 可用内存
28. - 是否有索引
29. - 连接类型(等值或非等值)
30. - 结果集大小

复制代码

四、备份与恢复

1. 备份策略

数据库备份是保障数据安全的重要手段。Oracle提供了多种备份方法。

2. -- 使用RMAN进行全库备份
3. RMAN> CONNECT TARGET /
4. RMAN> CONNECT CATALOG rman/rman@catdb
5. RMAN> RUN {
6. 2>    ALLOCATE CHANNEL c1 DEVICE TYPE DISK FORMAT '/backup/%U';
7. 3>    BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;
8. 4>    RELEASE CHANNEL c1;
9. 5> }
11. -- 使用RMAN进行增量备份
12. RMAN> RUN {
13. 2>    ALLOCATE CHANNEL c1 DEVICE TYPE DISK FORMAT '/backup/inc_%U';
14. 3>    BACKUP INCREMENTAL LEVEL 1 DATABASE;
15. 4>    RELEASE CHANNEL c1;
16. 5> }
18. -- 使用数据泵导出逻辑备份
19. EXPDP system/password DIRECTORY=backup_dir DUMPFILE=expdp_full.dmp FULL=Y LOGFILE=expdp_full.log
21. -- 验证备份
22. RMAN> VALIDATE DATABASE;
23. RMAN> RESTORE DATABASE VALIDATE;

复制代码

面试中常问的问题：

2. Q: 解释RMAN中的增量备份策略?
3. A: RMAN(Recovery Manager)中的增量备份策略包括：
5. 1. 增量备份级别：
6.    - 级别0(Level 0)：完整增量备份，包含所有数据块，是后续增量备份的基础
7.    - 级别1(Level 1)：只备份自上次同级或更低级别备份后更改的数据块
9. 2. 增量备份类型：
10.    - 差异增量备份(Differential Incremental Backup)：
11.      * 默认类型
12.      * 备份自上次级别0或级别1备份后更改的所有数据块
13.      * 恢复时需要应用最后一次级别0备份和之后的所有差异增量备份
14.      * 示例：BACKUP INCREMENTAL LEVEL 1 DATABASE;
16.    - 累积增量备份(Cumulative Incremental Backup)：
17.      * 备份自上次级别0备份后更改的所有数据块
18.      * 恢复时只需要应用最后一次级别0备份和最后一次累积增量备份
19.      * 比差异增量备份需要更多空间和备份时间，但恢复更快
20.      * 示例：BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;
22. 3. 增量更新备份(Incrementally Updated Backup)：
23.    - 使用"RECOVER COPY OF DATABASE WITH DELETE BACKUP"命令
24.    - 将增量备份应用到镜像副本，使镜像副本保持最新
25.    - 结合了镜像备份和增量备份的优点
27. 4. 块更改跟踪文件(Block Change Tracking)：
28.    - 启用后，数据库记录更改的数据块信息
29.    - 增量备份时无需扫描整个数据文件，只需读取更改跟踪文件
30.    - 显著提高增量备份性能
31.    - 启用命令：ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

复制代码

2. 恢复操作

数据库恢复是应对数据损坏或丢失的关键操作。

2. -- 使用RMAN进行完全恢复
3. RMAN> STARTUP MOUNT;
4. RMAN> RESTORE DATABASE;
5. RMAN> RECOVER DATABASE;
6. RMAN> ALTER DATABASE OPEN;
8. -- 使用RMAN进行表空间恢复
9. RMAN> SQL "ALTER TABLESPACE users OFFLINE";
10. RMAN> RESTORE TABLESPACE users;
11. RMAN> RECOVER TABLESPACE users;
12. RMAN> SQL "ALTER TABLESPACE users ONLINE";
14. -- 使用RMAN进行基于时间点的恢复
15. RMAN> RUN {
16. 2>    SET UNTIL TIME "TO_DATE('2023-06-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS')";
17. 3>    RESTORE DATABASE;
18. 4>    RECOVER DATABASE;
19. 5>    ALTER DATABASE OPEN RESETLOGS;
20. 6> }
22. -- 使用Flashback技术进行恢复
23. -- 查询可闪回的时间点
24. SELECT * FROM V$FLASHBACK_DATABASE_LOG;
26. -- 闪回表到指定时间点
27. FLASHBACK TABLE employees TO TIMESTAMP TO_TIMESTAMP('2023-06-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS');
29. -- 闪回删除的表
30. FLASHBACK TABLE dropped_employees TO BEFORE DROP;

复制代码

面试中常问的问题：

2. Q: 解释Oracle中的不同恢复方法及其适用场景?
3. A: Oracle中的恢复方法主要包括：
5. 1. 实例恢复(Instance Recovery)
6.    - 原理：使用重做日志(Online Redo Log)恢复未写入数据文件的更改
7.    - 触发条件：实例崩溃(如断电、关机)
8.    - 自动执行：无需DBA干预，SMON进程在实例启动时自动执行
9.    - 恢复范围：仅恢复未写入数据文件的已提交事务
11. 2. 介质恢复(Media Recovery)
12.    - 原理：使用备份文件和归档日志恢复损坏或丢失的数据文件
13.    - 触发条件：数据文件损坏、丢失或介质故障
14.    - 执行方式：需要DBA手动执行，使用RMAN或SQL*Plus
15.    - 恢复范围：可恢复整个数据库、表空间或数据文件
17. 3. 时间点恢复(Point-in-Time Recovery, PITR)
18.    - 原理：将数据库恢复到过去的特定时间点
19.    - 适用场景：用户错误(如误删数据)、逻辑错误
20.    - 实现方式：使用RMAN或Flashback Database
21.    - 恢复范围：整个数据库
23. 4. 表空间恢复(Tablespace Recovery)
24.    - 原理：只恢复特定的表空间，不影响其他表空间
25.    - 适用场景：特定表空间的数据文件损坏
26.    - 实现方式：使用RMAN
27.    - 恢复范围：特定表空间
29. 5. 表级恢复(Table Recovery)
30.    - 原理：只恢复特定的表，不影响其他表
31.    - 适用场景：特定表的数据丢失或损坏
32.    - 实现方式：使用RMAN表空间时间点恢复(TSPITR)、Flashback Table或数据泵
33.    - 恢复范围：特定表
35. 6. 块级恢复(Block Media Recovery)
36.    - 原理：只恢复损坏的数据块，而不是整个数据文件
37.    - 适用场景：少量数据块损坏(如块校验失败)
38.    - 实现方式：使用RMAN
39.    - 恢复范围：特定数据块
41. 7. 闪回技术(Flashback Technologies)
42.    - 原理：利用撤销数据(UNDO)或闪回日志快速恢复
43.    - 适用场景：用户错误、逻辑错误
44.    - 实现方式：Flashback Query、Flashback Table、Flashback Drop、Flashback Database等
45.    - 恢复范围：根据闪回技术不同，可恢复查询、表、删除的表或整个数据库
47. 选择恢复方法的考虑因素：
48. - 故障类型(实例故障、介质故障、用户错误)
49. - 恢复范围(整个数据库、表空间、表、数据块)
50. - 恢复时间目标(RTO)
51. - 数据丢失容忍度(RPO)
52. - 可用资源(备份、时间、存储)

复制代码

五、性能调优

1. 内存管理

Oracle数据库的内存管理对性能至关重要。Oracle提供了多种内存管理方法。

2. -- 查看SGA组件大小
3. SELECT pool, name, bytes/1024/1024 MB
4. FROM v$sgastat
5. WHERE pool IS NOT NULL
6. ORDER BY pool, name;
8. -- 查看PGA使用情况
9. SELECT name, value/1024/1024 MB
10. FROM v$pgastat
11. WHERE name IN('total PGA inuse', 'total PGA allocated', 'maximum PGA allocated');
13. -- 自动内存管理(AMM)
14. ALTER SYSTEM SET MEMORY_TARGET = 2G SCOPE=SPFILE;
15. ALTER SYSTEM SET MEMORY_MAX_TARGET = 3G SCOPE=SPFILE;
17. -- 自动共享内存管理(ASMM)
18. ALTER SYSTEM SET SGA_TARGET = 1G SCOPE=SPFILE;
19. ALTER SYSTEM SET SGA_MAX_SIZE = 2G SCOPE=SPFILE;
20. ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 500M SCOPE=SPFILE;
22. -- 手动内存管理
23. ALTER SYSTEM SET DB_CACHE_SIZE = 500M SCOPE=SPFILE;
24. ALTER SYSTEM SET SHARED_POOL_SIZE = 300M SCOPE=SPFILE;
25. ALTER SYSTEM SET LARGE_POOL_SIZE = 100M SCOPE=SPFILE;
26. ALTER SYSTEM SET JAVA_POOL_SIZE = 100M SCOPE=SPFILE;
27. ALTER SYSTEM SET STREAMS_POOL_SIZE = 100M SCOPE=SPFILE;
28. ALTER SYSTEM SET LOG_BUFFER = 10M SCOPE=SPFILE;

复制代码

面试中常问的问题：

2. Q: 解释Oracle中的共享池(Shared Pool)及其调优方法?
3. A: 共享池是SGA的重要组成部分，用于存储最近执行的SQL语句、PL/SQL代码和数据字典信息。
5. 共享池组成：
6. 1. 库缓存(Library Cache)：存储SQL语句、PL/SQL代码及其执行计划
7. 2. 数据字典缓存(Data Dictionary Cache)：存储数据字典信息
8. 3. 结果缓存(Result Cache)：存储查询结果(11g及以上版本)
10. 共享池调优方法：
12. 1. 监控共享池使用情况：
13.    SELECT name, value FROM v$sysstat WHERE name IN('parse count (hard)', 'parse count (total)');
14.    SELECT * FROM v$librarycache;
15.    SELECT * FROM v$rowcache;
17. 2. 确定适当的共享池大小：
18.    - 如果库缓存的命中率(pinhitratio)低于95%，可能需要增加共享池大小
19.    - 如果数据字典缓存的getmisses/gets比率高于10%，可能需要增加共享池大小
20.    - 使用V$SHARED_POOL_ADVICE视图获取建议大小
22. 3. 优化SQL语句以减少硬解析：
23.    - 使用绑定变量代替字面量
24.    - 共享SQL语句
25.    - 避免频繁的DDL操作
27. 4. 解决共享池碎片问题：
28.    - 使用DBMS_SHARED_POOL.KEEP过程固定常用对象
29.    - 调整SHARED_POOL_RESERVED_SIZE参数
30.    - 考虑使用CURSOR_SHARING参数(但需谨慎)
32. 5. 处理ORA-04031错误：
33.    - 增加共享池大小
34.    - 调整SHARED_POOL_RESERVED_SIZE
35.    - 使用DBMS_SHARED_POOL.KEEP固定大对象
36.    - 刷新共享池(ALTER SYSTEM FLUSH SHARED_POOL)作为临时解决方案
38. 6. 11g及以上版本的自动内存管理：
39.    - 使用MEMORY_TARGET或SGA_TARGET参数让Oracle自动管理共享池大小
40.    - 监控V$MEMORY_TARGET_ADVICE和V$SGA_TARGET_ADVICE视图获取建议
42. 共享池调优示例：
43. -- 检查库缓存命中率
44. SELECT namespace, gets, gethits, gethitratio
45. FROM v$librarycache
46. WHERE namespace IN('SQL AREA', 'TABLE/PROCEDURE');
48. -- 获取共享池大小建议
49. SELECT shared_pool_size_for_estimate, estd_lc_size, estd_lc_time_saved
50. FROM v$shared_pool_advice;
52. -- 固定常用包
53. EXEC DBMS_SHARED_POOL.KEEP('STANDARD');

复制代码

2. I/O与存储优化

I/O性能是数据库性能的关键因素之一。Oracle提供了多种优化I/O的方法。

2. -- 查看I/O统计信息
3. SELECT file_name, phyrds, phywrts, phyblkrd, phyblkwrt
4. FROM v$filestat f, dba_data_files d
5. WHERE f.file# = d.file_id;
7. -- 查看等待事件
8. SELECT event, total_waits, time_waited, average_wait
9. FROM v$system_event
10. WHERE wait_class = 'User I/O'
11. ORDER BY time_waited DESC;
13. -- 创建多路复用的重做日志
14. ALTER DATABASE ADD LOGFILE GROUP 4 ('/redo1/redo04.log', '/redo2/redo04.log') SIZE 100M;
15. ALTER DATABASE ADD LOGFILE GROUP 5 ('/redo1/redo05.log', '/redo2/redo05.log') SIZE 100M;
17. -- 创建大文件表空间(减少数据文件数量)
18. CREATE BIGFILE TABLESPACE bigfile_tbs
19. DATAFILE '/u01/oradata/bigfile_tbs01.dbf' SIZE 10G AUTOEXTEND ON;
21. -- 使用ASM(自动存储管理)
22. -- 创建ASM磁盘组
23. CREATE DISKGROUP data NORMAL REDUNDANCY
24. FAILGROUP controller1 DISK
25.   '/devices/diska1', '/devices/diska2'
26. FAILGROUP controller2 DISK
27.   '/devices/diskb1', '/devices/diskb2'
28. ATTRIBUTE 'au_size'='4M';
30. -- 在ASM上创建表空间
31. CREATE TABLESPACE asm_tbs DATAFILE '+data';

复制代码

面试中常问的问题：

2. Q: 解释Oracle中的多块读取(Multiblock Read)及其调优方法?
3. A: 多块读取(Multiblock Read)是Oracle数据库中的一种I/O优化技术，允许在一次I/O操作中读取多个数据块，从而减少I/O次数，提高全表扫描和索引快速全扫描的性能。
5. 多块读取的工作原理：
6. 1. 当执行全表扫描或索引快速全扫描时，Oracle会一次性读取多个连续的数据块
7. 2. 读取的块数由参数DB_FILE_MULTIBLOCK_READ_COUNT控制
8. 3. 多块读取主要通过直接路径读取(Direct Path Read)实现，绕过缓冲区缓存
10. 影响多块读取的因素：
11. 1. DB_FILE_MULTIBLOCK_READ_COUNT参数：
12.    - 定义每次I/O操作读取的最大块数
13.    - 默认值依赖于平台和数据库版本
14.    - 最大值受操作系统I/O能力和MAX_IO_SIZE参数限制
16. 2. 系统统计信息：
17.    - Oracle使用系统统计信息来计算成本
18.    - MBRC(Multiblock Read Count)统计信息影响优化器的决策
20. 3. 存储子系统：
21.    - 存储的I/O能力、条带化设置影响多块读取的效率
22.    - SSD存储通常能提供更好的多块读取性能
24. 多块读取的调优方法：
26. 1. 设置适当的DB_FILE_MULTIBLOCK_READ_COUNT：
27.    -- 查看当前设置
28.    SHOW PARAMETER DB_FILE_MULTIBLOCK_READ_COUNT
29.    
30.    -- 修改设置
31.    ALTER SYSTEM SET DB_FILE_MULTIBLOCK_READ_COUNT=128 SCOPE=SPFILE;
32.    
33.    -- 注意：不要设置过大，可能导致内存压力和I/O子系统争用
35. 2. 收集系统统计信息：
36.    -- 收集系统统计信息
37.    EXEC DBMS_STATS.GATHER_SYSTEM_STATS('START');
38.    -- 在系统负载高时等待一段时间
39.    EXEC DBMS_STATS.GATHER_SYSTEM_STATS('STOP');
40.    
41.    -- 或使用工作量统计信息
42.    EXEC DBMS_STATS.GATHER_SYSTEM_STATS('INTERVAL', INTERVAL=>30);
44. 3. 使用直接路径读取：
45.    -- 使用并行查询启用直接路径读取
46.    SELECT /*+ PARALLEL(employees 4) */ * FROM employees;
47.    
48.    -- 使用APPEND提示启用直接路径插入
49.    INSERT /*+ APPEND */ INTO employees SELECT * FROM new_employees;
51. 4. 优化存储配置：
52.    - 使用适当的条带化大小，通常为多块读取大小的倍数
53.    - 考虑使用ASM自动管理数据分布
54.    - 对于大型全表扫描，考虑使用大文件表空间
56. 5. 监控多块读取性能：
57.    -- 查看多块读取统计信息
58.    SELECT name, value FROM v$sysstat
59.    WHERE name IN('table scans (long tables)', 'table scan blocks gotten');
60.    
61.    -- 计算平均多块读取大小
62.    SELECT 'table scans (long tables)', value
63.    FROM v$sysstat
64.    WHERE name = 'table scans (long tables)';
65.    
66.    SELECT 'table scan blocks gotten', value
67.    FROM v$sysstat
68.    WHERE name = 'table scan blocks gotten';
69.    
70.    -- 平均多块读取大小 = table scan blocks gotten / table scans (long tables)
72. 多块读取调优注意事项：
73. 1. 增加DB_FILE_MULTIBLOCK_READ_COUNT会减少全表扫描的成本，可能导致优化器更倾向于全表扫描而非索引扫描
74. 2. 对于OLTP系统，通常需要较小的DB_FILE_MULTIBLOCK_READ_COUNT；对于数据仓库系统，可以使用较大的值
75. 3. 在Oracle 11g及以上版本，DB_FILE_MULTIBLOCK_READ_COUNT可以自动调整，通常无需手动设置
76. 4. 多块读取会增加PGA内存使用，特别是在并行操作中

复制代码

六、高级特性与新技术

1. 多租户架构(CDB与PDB)

Oracle 12c引入了多租户架构，允许在一个容器数据库(CDB)中托管多个可插拔数据库(PDB)。

2. -- 创建CDB
3. CREATE DATABASE cdb1
4. USER SYS IDENTIFIED BY sys_password
5. USER SYSTEM IDENTIFIED BY system_password
6. LOGFILE GROUP 1 ('/redo1/redo01a.log', '/redo2/redo01b.log') SIZE 100M BLOCKSIZE 512,
7.         GROUP 2 ('/redo1/redo02a.log', '/redo2/redo02b.log') SIZE 100M BLOCKSIZE 512,
8.         GROUP 3 ('/redo1/redo03a.log', '/redo2/redo03b.log') SIZE 100M BLOCKSIZE 512
9. MAXLOGHISTORY 1
10. MAXLOGFILES 16
11. MAXLOGMEMBERS 3
12. MAXDATAFILES 1024
13. CHARACTER SET AL32UTF8
14. NATIONAL CHARACTER SET AL16UTF16
15. EXTENT MANAGEMENT LOCAL
16. DATAFILE '/data/system01.dbf' SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
17. SYSAUX DATAFILE '/data/sysaux01.dbf' SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
18. DEFAULT TABLESPACE users
19. DATAFILE '/data/users01.dbf' SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
20. DEFAULT TEMPORARY TABLESPACE temp
21. TEMPFILE '/data/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
22. UNDO TABLESPACE undotbs1
23. DATAFILE '/data/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
24. ENABLE PLUGGABLE DATABASE
25. SEED
26. FILE_NAME_CONVERT = ('/data/', '/seed/')
27. SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
28. SYSAUX DATAFILES SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
29. USER_DATA TABLESPACE users
30. DATAFILE '/seed/users01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED;
32. -- 创建PDB
33. CREATE PLUGGABLE DATABASE pdb1
34. ADMIN USER pdb_admin IDENTIFIED BY pdb_password
35. ROLES = (DBA)
36. DEFAULT TABLESPACE pdb_users
37. DATAFILE '/data/pdb1/pdb_users01.dbf' SIZE 250M AUTOEXTEND ON
38. FILE_NAME_CONVERT = ('/seed/', '/data/pdb1/');
40. -- 打开PDB
41. ALTER PLUGGABLE DATABASE pdb1 OPEN;
43. -- 切换到PDB容器
44. ALTER SESSION SET CONTAINER = pdb1;
46. -- 在PDB中创建用户
47. CREATE USER app_user IDENTIFIED BY app_password DEFAULT TABLESPACE pdb_users;
49. -- 克隆PDB
50. CREATE PLUGGABLE DATABASE pdb2 FROM pdb1;
52. -- 插拔PDB
53. ALTER PLUGGABLE DATABASE pdb2 CLOSE;
54. ALTER PLUGGABLE DATABASE pdb2 UNPLUG INTO '/tmp/pdb2.xml';
55. DROP PLUGGABLE DATABASE pdb2 KEEP DATAFILES;
57. -- 插入PDB
58. CREATE PLUGGABLE DATABASE pdb2 USING '/tmp/pdb2.xml'
59. COPY
60. FILE_NAME_CONVERT = ('/data/pdb2/', '/data/pdb2_new/');

复制代码

面试中常问的问题：

2. Q: 解释Oracle多租户架构中的CDB和PDB，以及它们的优势?
3. A: Oracle多租户架构是Oracle 12c引入的重要特性，主要由容器数据库(CDB)和可插拔数据库(PDB)组成。
5. CDB(Container Database)：
6. - CDB是一个包含一个或多个PDB的数据库容器
7. - CDB包含系统数据文件、控制文件、重做日志和系统资源
8. - CDB有自己的实例和SGA
9. - CDB包含根容器(CDB$ROOT)和种子PDB(PDB$SEED)
10. - CDB$ROOT包含数据字典和元数据，这些元数据描述了CDB和所有PDB
12. PDB(Pluggable Database)：
13. - PDB是可插拔的数据库，包含用户数据和应用程序
14. - 每个PDB有自己的数据文件和临时文件
15. - PDB有自己的系统表空间、用户表空间和用户
16. - PDB共享CDB的控制文件、重做日志和后台进程
17. - PDB可以独立于其他PDB进行插拔、克隆、备份和恢复
19. 多租户架构的优势：
21. 1. 整合与资源利用：
22.    - 多个数据库可以整合到一个CDB中，共享系统资源
23.    - 减少硬件需求，降低成本
24.    - 提高服务器资源利用率
26. 2. 简化管理：
27.    - 只需管理一个CDB实例，而不是多个实例
28.    - 统一的补丁和升级：只需在CDB级别应用一次
29.    - 统一的备份和恢复：可以备份整个CDB或单个PDB
31. 3. 快速配置和部署：
32.    - 可以通过克隆PDB快速创建新数据库
33.    - PDB可以在几秒钟内插拔
34.    - 种子PDB(PDB$SEED)可以快速创建新的PDB
36. 4. 隔离与安全：
37.    - 每个PDB是独立的，有自己的用户和权限
38.    - PDB之间的数据是隔离的，一个PDB的问题不会影响其他PDB
39.    - 可以设置资源管理限制，防止单个PDB占用过多资源
41. 5. 灵活性和可移植性：
42.    - PDB可以轻松地在CDB之间移动
43.    - 支持标准数据库的所有功能
44.    - 可以在不影响其他PDB的情况下关闭或升级单个PDB
46. 6. 兼容性：
47.    - 应用程序无需修改即可在PDB中运行
48.    - 现有的Oracle工具和技术可以继续使用
49.    - 支持所有数据库特性，如RAC、Data Guard等
51. 多租户架构的使用场景：
52. - 企业整合多个数据库到单一平台
53. - 云服务提供商提供数据库即服务(DBaaS)
54. - 开发和测试环境快速部署
55. - 数据库迁移和升级
56. - 软件即服务(SaaS)应用程序部署

复制代码

2. In-Memory技术

Oracle In-Memory技术是Oracle 12c引入的革命性功能，通过双格式存储(行格式和列格式)显著提高分析查询性能。

2. -- 启用In-Memory选项
3. ALTER SYSTEM SET INMEMORY_SIZE = 10G SCOPE=SPFILE;
4. -- 重启数据库使参数生效
5. SHUTDOWN IMMEDIATE;
6. STARTUP;
8. -- 为表启用In-Memory存储
9. ALTER TABLE employees INMEMORY;
10. ALTER TABLE sales INMEMORY PRIORITY HIGH MEMCOMPRESS FOR QUERY LOW;
12. -- 为表空间启用In-Memory存储
13. ALTER TABLESPACE users DEFAULT INMEMORY;
15. -- 为特定列启用In-Memory存储
16. ALTER TABLE customers INMEMORY NO INMEMORY(customer_id) INMEMORY(customer_name, region);
18. -- 监控In-Memory使用情况
19. -- 查看In-Memory区域状态
20. SELECT * FROM V$INMEMORY_AREA;
22. -- 查看对象的In-Memory状态
23. SELECT table_name, inmemory, inmemory_priority, inmemory_compression
24. FROM user_tables
25. WHERE inmemory = 'ENABLED';
27. -- 查看In-Memory填充状态
28. SELECT segment_name, populate_status, bytes_not_populated
29. FROM v$im_segments;
31. -- 查看In-Memory查询统计信息
32. SELECT name, value FROM v$sysstat WHERE name LIKE 'In Memory%';
34. -- 强制使用In-Memory进行查询
35. SELECT /*+ INMEMORY(sales) */ product_id, SUM(quantity) total_quantity
36. FROM sales
37. GROUP BY product_id
38. ORDER BY total_quantity DESC;

复制代码

面试中常问的问题：

2. Q: 解释Oracle In-Memory技术的工作原理及其与传统存储的区别?
3. A: Oracle In-Memory技术是Oracle 12.1.0.2引入的创新功能，通过双格式存储和列式处理显著提高分析查询性能。
5. 工作原理：
7. 1. 双格式存储(Dual Format)：
8.    - 行格式(Row Format)：传统的存储方式，数据按行存储在数据文件中
9.    - 列格式(Column Format)：In-Memory存储方式，数据按列存储在内存中
10.    - 同一数据同时以两种格式存在，Oracle自动决定使用哪种格式
12. 2. In-Memory列存储(IM Column Store)：
13.    - 专门为分析查询优化的内存区域
14.    - 数据按列组织，每列单独存储
15.    - 使用高效压缩技术减少内存占用
16.    - 支持快速扫描和聚合操作
18. 3. 优化器集成：
19.    - 成本优化器自动决定使用行存储还是列存储
20.    - 分析查询倾向于使用列存储
21.    - 事务处理查询继续使用行存储
22.    - 可以使用提示强制使用In-Memory
24. 4. In-Memory表达式和连接：
25.    - 支持虚拟列和表达式在内存中物化
26.    - 支持连接组(Join Groups)优化星型查询
27.    - 支持向量化处理提高性能
29. 与传统存储的区别：
31. 1. 存储格式：
32.    - 传统存储：行格式，适合OLTP操作
33.    - In-Memory：列格式，适合OLAP操作
35. 2. 访问方式：
36.    - 传统存储：通过缓冲区缓存访问，可能涉及I/O操作
37.    - In-Memory：直接访问内存中的列数据，无需I/O
39. 3. 压缩技术：
40.    - 传统存储：基本压缩或高级压缩
41.    - In-Memory：专门为列存储设计的压缩算法，如字典编码、游程编码等
43. 4. 查询处理：
44.    - 传统存储：逐行处理，适合点查询和小范围查询
45.    - In-Memory：列式处理，适合全表扫描和聚合操作
47. 5. 事务处理：
48.    - 传统存储：直接支持DML操作
49.    - In-Memory：DML操作先在行存储上执行，然后异步更新到列存储
51. 6. 适用场景：
52.    - 传统存储：OLTP系统，高并发事务处理
53.    - In-Memory：OLAP系统，复杂分析查询，报表生成
55. In-Memory的优势：
57. 1. 性能提升：
58.    - 分析查询性能可提高10倍到100倍以上
59.    - 减少或消除全表扫描的I/O开销
60.    - 支持向量化处理和SIMD指令
62. 2. 透明性：
63.    - 应用程序无需修改
64.    - 现有SQL语句自动受益
65.    - 与所有Oracle功能兼容
67. 3. 灵活性：
68.    - 可以为特定表、列或表空间启用
69.    - 可以设置优先级控制填充顺序
70.    - 可以选择不同的压缩级别
72. 4. 实时性：
73.    - 数据在内存中保持最新
74.    - 支持实时分析
75.    - 无需ETL或数据仓库
77. In-Memory的适用场景：
78. - 混合工作负载(OLTP和OLAP)
79. - 实时分析
80. - 高速报表生成
81. - 复杂聚合查询
82. - 即席查询
83. - 数据挖掘
84. - 业务智能

复制代码

七、实战案例分析

1. 性能调优案例

以下是一个实际的Oracle数据库性能调优案例，展示了从问题识别到解决方案的完整过程。

问题描述：某公司的订单处理系统在高峰期响应缓慢，用户抱怨查询订单信息需要等待很长时间。系统运行在Oracle 19c数据库上。

问题诊断：

1. 收集性能数据：

2. -- 查看等待事件
3. SELECT event, total_waits, time_waited, average_wait
4. FROM v$system_event
5. WHERE wait_class != 'Idle'
6. ORDER BY time_waited DESC;
8. -- 查看Top SQL
9. SELECT sql_id, executions, elapsed_time/1000000 elapsed_sec,
10.        cpu_time/1000000 cpu_sec, disk_reads, buffer_gets
11. FROM v$sql
12. ORDER BY elapsed_time DESC;
14. -- 查看活动会话
15. SELECT sid, serial#, username, status, sql_id, event, blocking_session
16. FROM v$session
17. WHERE status = 'ACTIVE' AND username IS NOT NULL;

复制代码

1. 分析结果：发现主要等待事件是”db file sequential read”和”buffer busy waits”识别出几个执行频率高且响应时间长的SQL语句发现订单表(orders)和订单详情表(order_items)存在大量全表扫描
2. 发现主要等待事件是”db file sequential read”和”buffer busy waits”
3. 识别出几个执行频率高且响应时间长的SQL语句
4. 发现订单表(orders)和订单详情表(order_items)存在大量全表扫描

• 发现主要等待事件是”db file sequential read”和”buffer busy waits”
• 识别出几个执行频率高且响应时间长的SQL语句
• 发现订单表(orders)和订单详情表(order_items)存在大量全表扫描

解决方案：

1. 优化SQL语句：

2. -- 原始SQL(执行计划显示全表扫描)
3. SELECT o.order_id, o.order_date, c.customer_name,
4.        SUM(oi.quantity * oi.unit_price) total_amount
5. FROM orders o, customers c, order_items oi
6. WHERE o.customer_id = c.customer_id
7. AND o.order_id = oi.order_id
8. AND o.order_date BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD')
9.                       AND TO_DATE('2023-06-30', 'YYYY-MM-DD')
10. GROUP BY o.order_id, o.order_date, c.customer_name
11. ORDER BY o.order_date DESC;
13. -- 优化后的SQL(添加索引和提示)
14. -- 创建适当的索引
15. CREATE INDEX idx_orders_date ON orders(order_date);
16. CREATE INDEX idx_order_items_id ON order_items(order_id);
18. -- 使用提示优化执行计划
19. SELECT /*+ LEADING(o c oi) USE_NL(oi) INDEX(o idx_orders_date) INDEX(oi idx_order_items_id) */
20.        o.order_id, o.order_date, c.customer_name,
21.        SUM(oi.quantity * oi.unit_price) total_amount
22. FROM orders o, customers c, order_items oi
23. WHERE o.customer_id = c.customer_id
24. AND o.order_id = oi.order_id
25. AND o.order_date BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD')
26.                       AND TO_DATE('2023-06-30', 'YYYY-MM-DD')
27. GROUP BY o.order_id, o.order_date, c.customer_name
28. ORDER BY o.order_date DESC;

复制代码

1. 调整内存参数：

2. -- 增加缓冲区缓存大小
3. ALTER SYSTEM SET DB_CACHE_SIZE = 4G SCOPE=SPFILE;
5. -- 调整共享池大小
6. ALTER SYSTEM SET SHARED_POOL_SIZE = 2G SCOPE=SPFILE;
8. -- 调整PGA大小
9. ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 3G SCOPE=SPFILE;

复制代码

1. 优化存储配置：

2. -- 将频繁访问的表移动到更快的存储
3. ALTER TABLE orders MOVE TABLESPACE fast_disk_tbs;
4. ALTER TABLE order_items MOVE TABLESPACE fast_disk_tbs;
6. -- 重建索引
7. ALTER INDEX idx_orders_date REBUILD TABLESPACE fast_disk_tbs;
8. ALTER INDEX idx_order_items_id REBUILD TABLESPACE fast_disk_tbs;

复制代码

结果验证：

1. 监控性能改进：

2. -- 检查SQL执行统计
3. SELECT sql_id, executions, elapsed_time/1000000 elapsed_sec,
4.        cpu_time/1000000 cpu_sec, disk_reads, buffer_gets
5. FROM v$sql
6. WHERE sql_id = '7x9y8z6w5v4u3';
8. -- 检查等待事件
9. SELECT event, total_waits, time_waited, average_wait
10. FROM v$system_event
11. WHERE event IN ('db file sequential read', 'buffer busy waits');

复制代码

1. 结果：SQL执行时间从平均15秒降低到0.5秒“db file sequential read”等待事件减少了80%“buffer busy waits”等待事件几乎消失用户反馈系统响应明显改善
2. SQL执行时间从平均15秒降低到0.5秒
3. “db file sequential read”等待事件减少了80%
4. “buffer busy waits”等待事件几乎消失
5. 用户反馈系统响应明显改善

• SQL执行时间从平均15秒降低到0.5秒
• “db file sequential read”等待事件减少了80%
• “buffer busy waits”等待事件几乎消失
• 用户反馈系统响应明显改善

经验总结：

1. 性能调优应从全面诊断开始，收集足够的数据
2. 优先优化SQL语句，通常能带来最大的性能提升
3. 合理的索引设计对查询性能至关重要
4. 内存参数调整应根据系统负载和数据访问模式进行
5. 存储分层可以提高关键表的访问速度
6. 调优后应持续监控，确保改进效果持久

2. 数据迁移案例

以下是一个Oracle数据库迁移的实际案例，展示了从规划到执行的完整过程。

项目背景：某银行需要将其核心业务系统从Oracle 11g RAC迁移到Oracle 19c，同时将存储从传统SAN迁移到ASM。系统要求停机时间不超过4小时。

迁移规划：

1. 评估源系统：

2. -- 检查数据库版本和组件
3. SELECT * FROM v$version;
4. SELECT comp_name, version, status FROM dba_registry;
6. -- 检查数据库大小和对象数量
7. SELECT SUM(bytes)/1024/1024/1024 size_gb FROM dba_data_files;
8. SELECT COUNT(*) FROM dba_tables;
9. SELECT COUNT(*) FROM dba_indexes;
11. -- 检查无效对象
12. SELECT owner, object_name, object_type, status
13. FROM dba_objects
14. WHERE status = 'INVALID';
16. -- 检查兼容性
17. SELECT * FROM dba_registry_history;

复制代码

1. 设计目标系统架构：使用Oracle 19c RAC，3个节点使用ASM存储，配置DATA、FRA和RECO磁盘组采用多租户架构，每个业务模块一个PDB配置Data Guard用于灾备
2. 使用Oracle 19c RAC，3个节点
3. 使用ASM存储，配置DATA、FRA和RECO磁盘组
4. 采用多租户架构，每个业务模块一个PDB
5. 配置Data Guard用于灾备

• 使用Oracle 19c RAC，3个节点
• 使用ASM存储，配置DATA、FRA和RECO磁盘组
• 采用多租户架构，每个业务模块一个PDB
• 配置Data Guard用于灾备

迁移准备：

1. 准备目标环境：

2. -- 创建ASM磁盘组
3. CREATE DISKGROUP DATA NORMAL REDUNDANCY
4. FAILGROUP fg1 DISK '/dev/asm-disk1', '/dev/asm-disk2'
5. FAILGROUP fg2 DISK '/dev/asm-disk3', '/dev/asm-disk4'
6. ATTRIBUTE 'au_size'='4M', 'compatible.asm'='19.0';
8. CREATE DISKGROUP FRA NORMAL REDUNDANCY
9. FAILGROUP fg1 DISK '/dev/asm-disk5', '/dev/asm-disk6'
10. FAILGROUP fg2 DISK '/dev/asm-disk7', '/dev/asm-disk8'
11. ATTRIBUTE 'au_size'='4M', 'compatible.asm'='19.0';
13. -- 创建CDB
14. CREATE DATABASE cdb_bank
15. USER SYS IDENTIFIED BY sys_password
16. USER SYSTEM IDENTIFIED BY system_password
17. LOGFILE GROUP 1 ('+DATA/redo01.log') SIZE 1G,
18.         GROUP 2 ('+DATA/redo02.log') SIZE 1G,
19.         GROUP 3 ('+DATA/redo03.log') SIZE 1G
20. MAXLOGHISTORY 1
21. MAXLOGFILES 192
22. MAXLOGMEMBERS 3
23. MAXDATAFILES 1024
24. CHARACTER SET AL32UTF8
25. NATIONAL CHARACTER SET AL16UTF16
26. EXTENT MANAGEMENT LOCAL
27. DATAFILE '+DATA/system01.dbf' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED
28. SYSAUX DATAFILE '+DATA/sysaux01.dbf' SIZE 5G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED
29. DEFAULT TABLESPACE users
30. DATAFILE '+DATA/users01.dbf' SIZE 5G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED
31. DEFAULT TEMPORARY TABLESPACE temp
32. TEMPFILE '+DATA/temp01.dbf' SIZE 5G REUSE AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED
33. UNDO TABLESPACE undotbs1
34. DATAFILE '+DATA/undotbs01.dbf' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED
35. ENABLE PLUGGABLE DATABASE
36. SEED
37. FILE_NAME_CONVERT = ('+DATA/', '+DATA/seed/')
38. SYSTEM DATAFILES SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
39. SYSAUX DATAFILES SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

复制代码

1. 准备迁移脚本：

2. -- 创建PDB
3. CREATE PLUGGABLE DATABASE pdb_corebanking
4. ADMIN USER pdb_admin IDENTIFIED BY pdb_password
5. ROLES = (DBA)
6. DEFAULT TABLESPACE corebanking_data
7. DATAFILE '+DATA/corebanking_data01.dbf' SIZE 10G AUTOEXTEND ON NEXT 1G
8. FILE_NAME_CONVERT = ('+DATA/seed/', '+DATA/corebanking/');
10. -- 创建表空间
11. CREATE TABLESPACE corebanking_data DATAFILE '+DATA/corebanking_data01.dbf' SIZE 10G AUTOEXTEND ON NEXT 1G;
12. CREATE TABLESPACE corebanking_idx DATAFILE '+DATA/corebanking_idx01.dbf' SIZE 5G AUTOEXTEND ON NEXT 500M;
13. CREATE TABLESPACE corebanking_lob DATAFILE '+DATA/corebanking_lob01.dbf' SIZE 20G AUTOEXTEND ON NEXT 2G;

复制代码

执行迁移：

1. 数据导出：

2. # 使用数据泵导出源数据库
3. expdp system/password DIRECTORY=exp_dir DUMPFILE=corebanking_%U.dmp FILESIZE=10G FULL=Y PARALLEL=8 LOGFILE=expdp_corebanking.log

复制代码

1. 数据导入：

2. # 使用数据泵导入到目标PDB
3. impdp system/password@pdb_corebanking DIRECTORY=imp_dir DUMPFILE=corebanking_%U.dmp PARALLEL=8 TRANSFORM=SEGMENT_ATTRIBUTES:N TRANSFORM=STORAGE:N LOGFILE=impdp_corebanking.log

复制代码

1. 验证数据完整性：

2. -- 检查对象数量
3. SELECT object_type, COUNT(*) FROM dba_objects GROUP BY object_type;
5. -- 检查无效对象
6. SELECT owner, object_name, object_type, status
7. FROM dba_objects
8. WHERE status = 'INVALID';
10. -- 编译无效对象
11. EXEC DBMS_UTILITY.compile_schema(user => 'SCHEMA_NAME');
13. -- 收集统计信息
14. EXEC DBMS_STATS.gather_schema_stats('SCHEMA_NAME', cascade => TRUE);

复制代码

迁移后优化：

1. 配置高可用性：

2. -- 配置Data Guard
3. ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(cdb_bank,cdb_bank_std)';
4. ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=cdb_bank_std ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cdb_bank_std';
5. ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
6. ALTER SYSTEM SET FAL_SERVER=cdb_bank_std;
7. ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
9. -- 创建备用数据库
10. -- (在备用服务器上执行)
11. RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE;

复制代码

1. 性能优化：

2. -- 启用In-Memory选项
3. ALTER SYSTEM SET INMEMORY_SIZE=20G SCOPE=SPFILE;
5. -- 为关键表启用In-Memory
6. ALTER TABLE accounts INMEMORY PRIORITY HIGH;
7. ALTER TABLE transactions INMEMORY PRIORITY HIGH;
8. ALTER TABLE customers INMEMORY PRIORITY HIGH;
10. -- 配置自动内存管理
11. ALTER SYSTEM SET MEMORY_TARGET=60G SCOPE=SPFILE;
12. ALTER SYSTEM SET MEMORY_MAX_TARGET=80G SCOPE=SPFILE;

复制代码

迁移结果：

• 迁移过程耗时3.5小时，在要求的4小时停机窗口内完成
• 新系统性能提升30%，特别是在报表生成和分析查询方面
• 高可用性配置确保了业务连续性
• 存储管理简化，维护成本降低

经验总结：

1. 数据库迁移需要充分的规划和准备
2. 停机窗口评估应考虑缓冲时间
3. 数据泵是大规模数据迁移的有效工具
4. 迁移后验证和优化同样重要
5. 新版本数据库的新特性应充分利用
6. 高可用性配置是生产系统的必要条件

通过以上案例分析，我们可以看到Oracle数据库在实际应用中的复杂性和多样性，以及如何通过系统的方法解决实际问题。这些经验对于Oracle数据库管理员和开发人员都非常有价值。