印刻数据库

外观

Oracle Schema变更管理 ​

概述 ​

Schema变更是Oracle数据库运维中的常见操作，包括表结构变更、索引创建、存储过程修改等。合理的Schema变更管理可以确保变更的安全性、可靠性和可追溯性，减少变更风险，保障数据库系统的稳定运行。

Schema变更分类 ​

1. 按变更对象分类 ​

变更对象示例风险级别
表结构新增字段、修改字段类型、删除字段中高
索引创建索引、修改索引、删除索引中
约束添加约束、修改约束、删除约束中高
视图创建视图、修改视图、删除视图中
存储过程创建存储过程、修改存储过程、删除存储过程中
函数创建函数、修改函数、删除函数中
触发器创建触发器、修改触发器、删除触发器中高
包创建包、修改包、删除包中

2. 按变更影响分类 ​

影响类型示例特点
数据无影响添加非空字段（带默认值）、创建索引不影响现有数据
数据有影响修改字段类型、删除字段、添加非空约束影响现有数据
性能影响创建大表索引、修改大表结构可能影响数据库性能
应用影响修改表结构、修改存储过程可能影响应用程序

Schema变更流程 ​

1. Schema变更申请 ​

申请内容 ​

Schema变更申请应包含以下信息：

  • 变更标题和编号
  • Schema变更类型和范围
  • 变更原因和目的
  • 当前Schema结构和目标Schema结构
  • 变更影响范围和风险评估
  • 变更执行时间和持续时间
  • 变更申请人和责任人
  • 变更执行步骤和回滚方案
  • 验证标准和验收条件

申请模板 ​

markdown
# Schema变更申请单

## 基本信息
- **变更编号**：ORACLE-SCHEMA-CHG-20250101-001
- **变更标题**：用户表添加手机号字段
- **变更对象**：表结构
- **变更范围**：生产环境SCOTT.USERS表

## 变更详情
- **变更原因**：业务需求，需要存储用户手机号
- **当前表结构**：
  ```sql
  CREATE TABLE SCOTT.USERS (
    USER_ID NUMBER PRIMARY KEY,
    USER_NAME VARCHAR2(50) NOT NULL,
    EMAIL VARCHAR2(100) NOT NULL,
    CREATE_TIME DATE DEFAULT SYSDATE
  );
  • 目标表结构：
    sql
    CREATE TABLE SCOTT.USERS (
  USER_ID NUMBER PRIMARY KEY,
  USER_NAME VARCHAR2(50) NOT NULL,
  EMAIL VARCHAR2(100) NOT NULL,
  PHONE VARCHAR2(20),
  CREATE_TIME DATE DEFAULT SYSDATE
);
  • 影响范围：用户管理系统

风险评估 ​

  • 风险级别：低
  • 风险描述：添加非空字段，默认值为NULL，不影响现有数据
  • 缓解措施：在非业务高峰期执行，准备回滚方案

执行计划 ​

  • 执行时间：2025-01-01 22:00-23:00
  • 执行步骤：
    1. 连接到数据库
    2. 备份当前表结构
    3. 执行ALTER TABLE语句添加PHONE字段
    4. 验证字段添加成功
    5. 测试应用功能

回滚计划 ​

  • 回滚条件：字段添加失败或影响应用功能
  • 回滚步骤：
    1. 连接到数据库
    2. 执行ALTER TABLE语句删除PHONE字段
    3. 验证字段删除成功
    4. 测试应用功能恢复正常

验证标准 ​

  • 预期结果：成功添加PHONE字段，不影响现有数据和应用功能
  • 验证方法：
    1. 检查表结构
    2. 查询现有数据
    3. 测试应用功能
    4. 检查数据库日志

责任人 ​

  • 申请人：张三 (开发工程师)
  • 审批人：李四 (DBA)
  • 执行人：王五 (DBA)
  • 验证人：赵六 (测试工程师)

### 2. Schema变更审批

#### 审批层级

| 变更类型 | 风险级别 | 审批层级 |
|---------|---------|---------|
| 低风险变更 | 低 | DBA团队内部审批 |
| 中风险变更 | 中 | DBA团队 + 开发负责人审批 |
| 高风险变更 | 高 | DBA团队 + 开发负责人 + 运维经理审批 |
| 核心Schema变更 | 高 | DBA团队 + 开发负责人 + 运维经理 + 业务负责人审批 |

#### 审批要点

审批人在审批Schema变更时应重点关注：

- 变更的必要性和合理性
- 变更的SQL语句是否正确
- 变更对现有数据的影响
- 变更对数据库性能的影响
- 变更对应用程序的影响
- 回滚方案是否可行

### 3. Schema变更执行

#### 执行前准备

1. **备份相关数据**：
   ```sql
   -- 备份表结构
   CREATE TABLE SCOTT.USERS_BAK AS SELECT * FROM SCOTT.USERS;
   
   -- 备份表结构定义
   SET LONG 1000000
   SELECT DBMS_METADATA.GET_DDL('TABLE', 'USERS', 'SCOTT') FROM DUAL;

  1. 验证变更SQL：在测试环境验证变更SQL的正确性

  2. 分析变更影响：使用Oracle提供的工具分析变更影响

    sql
    -- 使用DBMS_SQLPA分析SQL性能影响
EXEC DBMS_SQLPA.CREATE_ANALYSIS_TASK(sql_text => 'ALTER TABLE SCOTT.USERS ADD (PHONE VARCHAR2(20))', task_name => 'schema_change_task');
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'schema_change_task');
EXEC DBMS_SQLPA.REPORT_ANALYSIS_TASK(task_name => 'schema_change_task');

  3. 通知相关人员：提前通知受影响的应用团队和用户

执行过程 ​

  1. 表结构变更：

    sql
    -- 添加字段
ALTER TABLE SCOTT.USERS ADD (PHONE VARCHAR2(20));

-- 修改字段类型
ALTER TABLE SCOTT.USERS MODIFY (EMAIL VARCHAR2(200));

-- 删除字段
ALTER TABLE SCOTT.USERS DROP COLUMN PHONE;

-- 添加约束
ALTER TABLE SCOTT.USERS ADD CONSTRAINT UK_USERS_EMAIL UNIQUE (EMAIL);

-- 修改约束
ALTER TABLE SCOTT.USERS MODIFY CONSTRAINT UK_USERS_EMAIL RELY DISABLE;

-- 删除约束
ALTER TABLE SCOTT.USERS DROP CONSTRAINT UK_USERS_EMAIL;

  2. 索引变更：

    sql
    -- 创建索引
CREATE INDEX SCOTT.IDX_USERS_EMAIL ON SCOTT.USERS(EMAIL) ONLINE;

-- 修改索引
ALTER INDEX SCOTT.IDX_USERS_EMAIL RENAME TO SCOTT.IDX_USERS_EMAIL_NEW;

-- 删除索引
DROP INDEX SCOTT.IDX_USERS_EMAIL_NEW;

  3. 存储过程变更：

    sql
    -- 创建或替换存储过程
CREATE OR REPLACE PROCEDURE SCOTT.SP_GET_USER(
  p_user_id IN NUMBER,
  p_user_name OUT VARCHAR2
) AS
BEGIN
  SELECT USER_NAME INTO p_user_name FROM SCOTT.USERS WHERE USER_ID = p_user_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    p_user_name := NULL;
END;
/

-- 编译存储过程
ALTER PROCEDURE SCOTT.SP_GET_USER COMPILE;

-- 删除存储过程
DROP PROCEDURE SCOTT.SP_GET_USER;

  4. 大表变更优化：

    sql
    -- 使用在线操作减少锁定时间
ALTER TABLE SCOTT.LARGE_TABLE ADD (NEW_COLUMN VARCHAR2(50)) ONLINE;

-- 使用并行操作提高大表变更速度
ALTER TABLE SCOTT.LARGE_TABLE MODIFY (COLUMN_NAME VARCHAR2(100)) PARALLEL 4;

-- 变更后恢复正常并行度
ALTER TABLE SCOTT.LARGE_TABLE NOPARALLEL;

执行记录 ​

执行过程中应详细记录每一步操作和结果：

时间操作内容执行结果执行人
22:00备份USERS表到USERS_BAK成功王五
22:05执行ALTER TABLE添加PHONE字段成功王五
22:10验证字段添加成功成功王五
22:15测试应用功能正常王五
22:20检查数据库日志无错误王五

4. Schema变更验证 ​

验证内容 ​

  1. Schema结构验证：验证Schema结构是否符合预期
  2. 数据完整性验证：验证现有数据是否完整
  3. 应用功能验证：验证应用程序是否正常工作
  4. 性能验证：验证变更是否影响数据库性能
  5. 约束验证：验证约束是否正常工作

验证方法 ​

  1. Schema结构验证：

    sql
    -- 检查表结构
DESCRIBE SCOTT.USERS;

-- 检查索引状态
SELECT INDEX_NAME, STATUS FROM ALL_INDEXES WHERE TABLE_NAME = 'USERS' AND OWNER = 'SCOTT';

-- 检查约束状态
SELECT CONSTRAINT_NAME, STATUS FROM ALL_CONSTRAINTS WHERE TABLE_NAME = 'USERS' AND OWNER = 'SCOTT';

  2. 数据完整性验证：

    sql
    -- 检查数据行数
SELECT COUNT(*) FROM SCOTT.USERS;
SELECT COUNT(*) FROM SCOTT.USERS_BAK;

-- 检查数据一致性
SELECT * FROM SCOTT.USERS MINUS SELECT * FROM SCOTT.USERS_BAK;
SELECT * FROM SCOTT.USERS_BAK MINUS SELECT * FROM SCOTT.USERS;

  3. 应用功能验证：

    • 执行应用程序的功能测试
    • 检查应用程序日志
    • 验证用户访问是否正常

  4. 性能验证：

    • 监控数据库性能指标
    • 检查SQL执行计划
    • 比较变更前后的性能差异

5. Schema变更完成 ​

完成内容 ​

  1. 更新Schema文档：将变更后的Schema结构更新到Schema文档中
  2. 清理备份数据：根据备份策略清理临时备份数据
  3. 关闭变更请求：在变更管理系统中关闭变更请求
  4. 通知相关人员：通知相关人员变更完成情况
  5. 归档变更文档：将变更申请、审批、执行记录等归档保存

Schema变更最佳实践 ​

1. 表结构变更最佳实践 ​

添加字段 ​

  • 优先添加可空字段，避免添加非空字段
  • 添加非空字段时应提供默认值
  • 大表添加字段时使用ONLINE选项减少锁定时间
  • 避免在业务高峰期添加大表字段

修改字段 ​

  • 避免修改字段数据类型，特别是从长类型改为短类型
  • 修改字段长度时应确保不截断现有数据
  • 大表修改字段时使用ONLINE和PARALLEL选项
  • 修改字段前应备份相关数据

删除字段 ​

  • 删除字段前应确认该字段不再被使用
  • 删除字段前应备份相关数据
  • 考虑使用标记删除代替物理删除
  • 大表删除字段时使用ONLINE选项

2. 索引变更最佳实践 ​

创建索引 ​

  • 优先考虑使用ONLINE选项创建索引
  • 大表创建索引时使用PARALLEL选项提高速度
  • 创建索引后收集统计信息
  • 定期检查索引使用情况，删除无用索引

修改索引 ​

  • 避免频繁修改索引结构
  • 修改索引前应备份索引定义
  • 修改索引时考虑使用REBUILD ONLINE选项

删除索引 ​

  • 删除索引前应确认该索引不再被使用
  • 删除索引前应备份索引定义
  • 考虑使用ALTER INDEX ... INVISIBLE代替删除

3. 约束变更最佳实践 ​

添加约束 ​

  • 添加约束前应验证数据满足约束条件
  • 大表添加约束时使用NOVALIDATE选项减少锁定时间
  • 添加外键约束时考虑使用DISABLE NOVALIDATE选项

修改约束 ​

  • 修改约束前应备份约束定义
  • 修改约束时考虑使用ENABLE/DISABLE选项
  • 避免频繁修改约束状态

删除约束 ​

  • 删除约束前应确认该约束不再被使用
  • 删除约束前应备份约束定义

4. 存储过程变更最佳实践 ​

创建和修改存储过程 ​

  • 使用CREATE OR REPLACE语句创建或修改存储过程
  • 存储过程中应包含异常处理
  • 存储过程修改后应重新编译
  • 存储过程修改后应进行功能测试

删除存储过程 ​

  • 删除存储过程前应确认该存储过程不再被使用
  • 删除存储过程前应备份存储过程定义
  • 删除存储过程后应检查应用程序是否正常

19c与21c版本Schema变更差异 ​

1. Schema变更功能差异 ​

特性Oracle 19cOracle 21c
在线表重组支持基本在线表重组增强的在线表重组功能，支持更多操作
并行DDL操作支持并行DDL增强的并行DDL支持，提高大表变更速度
在线添加列支持在线添加列增强的在线添加列功能，支持更多数据类型
在线修改列有限支持在线修改列增强的在线修改列功能，支持更多操作
大表变更优化基本优化增强的大表变更优化，减少锁定时间

2. Schema变更工具差异 ​

工具Oracle 19cOracle 21c
DBMS_METADATA基本支持增强的DBMS_METADATA，支持更多对象类型
DBMS_SQLPA基本支持增强的DBMS_SQLPA，支持更多分析功能
SQL Developer基本Schema管理功能增强的Schema管理功能，支持可视化变更
Enterprise Manager基本Schema变更功能增强的Schema变更功能，支持自动化变更

3. Schema变更性能差异 ​

特性Oracle 19cOracle 21c
大表变更速度一般更快的大表变更速度
变更锁定时间较长更短的变更锁定时间
并行处理能力基本并行支持增强的并行处理能力
在线操作支持有限支持增强的在线操作支持

常见问题(FAQ) ​

Q: 如何处理Schema变更导致的应用程序错误？ ​

A: Schema变更导致应用程序错误的处理流程：

  1. 立即停止当前变更操作
  2. 执行回滚方案，恢复到变更前的Schema结构
  3. 通知相关人员应用程序错误情况
  4. 分析错误原因，找出Schema变更与应用程序的不兼容之处
  5. 修改变更方案，重新进行测试和验证
  6. 重新提交变更申请

Q: 如何减少大表Schema变更的锁定时间？ ​

A: 减少大表Schema变更锁定时间的方法：

  1. 使用ONLINE选项进行在线变更
  2. 使用PARALLEL选项提高变更速度
  3. 在业务低峰期进行变更
  4. 将大表变更拆分为多个小变更
  5. 使用分区表，对单个分区进行变更
  6. 考虑使用Oracle提供的大表变更工具

Q: 如何验证Schema变更对应用程序的影响？ ​

A: 验证Schema变更对应用程序影响的方法：

  1. 在测试环境中进行完整的应用程序功能测试
  2. 使用Oracle提供的工具分析Schema变更对SQL性能的影响
  3. 监控应用程序的日志和性能指标
  4. 邀请业务人员进行业务功能验证
  5. 进行压力测试，验证Schema变更对系统性能的影响

Q: 如何处理Schema变更导致的数据丢失？ ​

A: 处理Schema变更导致数据丢失的流程：

  1. 立即停止当前变更操作
  2. 使用备份数据恢复丢失的数据
  3. 分析数据丢失的原因，找出变更方案中的问题
  4. 修改变更方案，添加数据保护措施
  5. 重新进行测试和验证
  6. 重新提交变更申请

Q: 如何管理多个Schema变更的依赖关系？ ​

A: 管理多个Schema变更依赖关系的方法：

  1. 分析Schema变更之间的依赖关系，确定变更顺序
  2. 将有依赖关系的Schema变更组合成一个变更请求
  3. 在变更申请中明确说明变更顺序和依赖关系
  4. 在测试环境中按顺序测试所有Schema变更
  5. 在生产环境中按顺序执行所有Schema变更
  6. 每执行一个变更就进行验证，确保依赖关系正确

Q: 如何自动化Schema变更管理？ ​

A: 自动化Schema变更管理的方法：

  1. 使用Oracle提供的自动化工具，如SQL Developer、Enterprise Manager
  2. 使用第三方Schema管理工具，如Liquibase、Flyway
  3. 开发自定义的Schema变更管理脚本
  4. 集成Schema变更管理到CI/CD流程中
  5. 实现Schema变更的自动测试和验证
  6. 建立Schema变更的版本控制机制

总结 ​

Oracle Schema变更管理是数据库运维的重要组成部分，合理的Schema变更流程和规范可以确保Schema变更的安全性、可靠性和可追溯性。通过本文的介绍，相信您已经掌握了Oracle Schema变更的基本概念、变更流程、最佳实践和常见问题处理方法，能够有效地进行Oracle Schema变更管理，保障数据库系统的稳定运行。