生产环境数据库操作规范手册-广州天凯科技多年数据库维护经验分享

生产数据库维护操作规范手册

文档编号

ML-XX-XQ-001（公司名－项目名－文档类别－序号）

修订历史

1
前言

当业务系统上线一段时间，期间数据库运行相对稳定，性能良好。然而开发人员在运作过程中偶尔会有代码功能新增或调整等重要操作，如没有按照严格的规范进行，会导致系统资源不足甚至系统崩溃，给客户带来重大损失！为了提高DBA或开发人员的操作规范和风险意识，避免数据的误操作，降低公司的运营风险，现制定数据库操作规范手册，以进一步完善数据库维护制度。

2
操作规范

2.
建表

(1
目的

明确建表操作的风险及标准流程，最大限度避免建表操作带来的故障。

(2
适用范围

1
项目预发布新建表

2
项目正式发布新建表

3
生产环境临时备份表

4
不包含数据订正所建临时表

5
不包含导数据所建的中间表

(3
风险评估

1
登录到错误的schema下，导致表建到错误的schema里，而应用无法访问。

2
指定了错误的TABLESPACE参数，导致表建到了其他表空间，导致后续空间增长和维护困难。

3
对于未来增量较快的表选择了一个空间规划不足的表空间，导致后续空间增长和维护困难。

4
脚本末尾缺少分号，导致该表没有被创建上，而执行DDL的过程又不会报错。

5
其他原因漏建了表，导致应用访问错误。

6
所建的表定义（表名、字段名、字段定义、字段个数、字段顺序）跟测试环境不一致，导致应用访问错误。

7
CTAS（create table as select * from tab;）形式创建备份表，如果表很大，可能会造成IO高负荷，影响数据库的正常使用。

(4
操作流程

1
准备工作

a
在项目需求分析阶段，跟数据库设计人员一起明确新表所存放的数据库。

b
准备发布脚本时，检查tablespace定义，检查tablespace剩余空间，参考表空间自身负荷及新表的预期负荷，为每个新建的表选择合适的表空间，并在建表语句中添加tablespace的选项。

c
仅建表操作本身不会对数据库造成任何风险，故操作的时间点可以放宽：在变更时间窗口内，均可以执行建表操作。

2
执行过程

a
用DBA或应用账户登录数据库，SHOW USER检查是否连接到正确的schema，如果用DBA账号执行，注意前缀一定要正确指定。

b
执行建表脚本。若一次建表个数较多，注意用分号区分好，避免出错。

c
查看过程若无报错，退出当前登录。若有报错，找出报错的地方，修改确认再执行，直至全部执行通过，最后退出当前登录。

d
对于临时备份表，如表很大，仅备份需要的数据，加限制条件CTAS

3
验证方案

a) 检查应用有无异常；

b) 检查DG对端的表定义是否一致。

2.
数据订正

(1
目的

明确【数据订正】操作的种类、风险，并根据各种类型的数据订正制定完善的步骤和回退方案，最大限度减少此类操作带来的故障。

(2
适用范围

1
新建表数据初始化

2
现有表新增数据

3
现有表删除数据

4
现有表上新增字段初始化

5
现有表上现有字段值修改

(3
风险评估

1
业务风险：订正本身所包含的业务不正确，导致给客户给公司带来损失。

2
程序风险：订正本身业务正确，但是应用程序无法兼容订正的数据，导致应用出错。

3
数据库风险：订正本身业务正确，应用程序也可以兼容，但是订正速度过快、订正并发压力过大，导致数据库无法正常提供服务。通常会造成表空间耗尽、undo消耗过快、archive增长过快、备库恢复压力大等问题。

4
沟通风险：在业务方-开发接口人-DBA三方的沟通交流过程中，信息传递错误或者不及时，导致最终订正的数据没有达到预期的目的。

5
回滚风险：主要是因为业务方的原因，订正完成一段时间后要求回退，若在订正前没有备份原始数据，则可能导致无法顺利回退或者回退难度极大，给客户给公司带来损失。

6
同步风险：各类同步架构下，数据订正可能导致同步堆积和同步延时，影响对端正常同步业务，这里主要指OGG或DG对端的同步影响。

(4
操作流程

1
准备工作

a) 需求分析阶段确认项目涉及的数据订正范围和数据量。

b) 跟开发人员确定订正后是否涉及到对缓存的刷新和订正。

c) 根据数据量评估对数据同步的影响，决定是否屏蔽同步。（应用DBA必须熟悉同步采用的技术、正常情况下的同步量和延时、可以容忍的同步延时、屏蔽同步的具体方法，这里主要指OGG和SQL SERVER复制）

d) 注意规划订正速度，尽量批量提交事务，以防undo消耗殆尽。

e) 订正脚本：

i
开发接口人直接提供可执行的SQL脚本，DBA只负责拷贝执行。

ii
开发接口人提供订正逻辑，由DBA翻译为批量提交SQL脚本。

iii
需定期重复执行的订正脚本，如修复库存脚本等。

iv
严禁仅用一个事务来处理大规模订正（影响的记录数超过1万笔）。超过一万笔的订正必须分段提交。

v
确认订正脚本的执行计划正确。

vi
发布前确定订正速度和并发度，确定订正时间段，预估订正总时长，若涉及量较大，需要跨天做订正，则应规划好每日订正的数据量和时间段。

f) 备份要求：

i
新建表初始化：无需备份，回退时直接truncate即可。

ii
现有表新增数据：新建备份表记录下新增记录的主键，或者在新增记录中特定字段标识区分出订正所新增的数据，回退时定向delete这些记录。

iii
现有表删除数据：新建备份表记录下删除数据的完整记录，回退时直接从备份表中取出数据insert到原表。

iv
现有表上新增字段初始化：无需备份，回退时将该字段update为NULL或者开发接口人要求的值。不得将删除字段作为回退手段。

v
现有表上现有字段值修改：新建备份表记录下所改动记录的主键及所改动字段的原始值，回退时将改动过的字段按照主键更新到原表（若应用程序在回滚前已经修改了记录，则要根据具体业务具体分析回滚方案）。

vi
备份表：备份表统一命名为 table_name_bak_mmdd_operator，最后的operator为操作DBA的姓名每个字的首字母，如果超长了，则将原表名缩减。

创建人有责任定期删除创建时间超过一个月以上的备份表。

2
执行过程

a
如果需要，按照备份脚本备份数据。

b
执行订正脚本期间。查看订正进度，可参考附录3.1脚本。

c
如执行大事务脚本，需定时检查undo消耗情况，可参考附录3.2脚本。

d
如执行大事务脚本，需定时检查表空间使用情况。

e
如执行大事务脚本，需定时检查归档空间是否足够，可查看v$recovery_area_usage视图。

f
如执行大事务脚本，需时刻关注数据库的并发性能，等待事件，可查EM顶级会话，并准备好应急脚本。

g
检查OGG/DG同步端延时是否异常。

3
验证方案

a) 以应用验证为主，数据库辅助做一些count等验证。

2.
创建、删除、修改sequence

(1
目的

明确定义对于sequence对象的操作风险及步骤。

(2
适用范围

1
项目发布创建新sequence。

2
以删除、重建的方式修改sequence的起始值。

3
在线修改sequence的cache值。

(3
风险评估

1
Sequence命名与应用程序中不一致，导致应用无法正常访问sequence。

2
删除、重建sequence的过程中，应用无法访问sequence，高并发的应用可能会产生故障。

3
删除、重建sequence之后没有对sequence的权限进行恢复，导致原本访问该sequence的其他schema无法正常访问。

4
Sequence的cache设置不合理，设置过小会导致大量的系统相关等待，反之则导致sequence生成值断层过多浪费严重。

(4
操作流程

1
准备工作

a) 默认使用表名+seq生成的sequence名称，如果要修改，必须跟开发人员沟通一致。

b) 与开发人员、项目发布负责人沟通变更时间点。对于删除、重建的操作必须明确告诉他们其间会有短暂的无法访问，如果是高并发的应用则选择在系统访问量最低的时候执行，规避风险。

c) 根据并发数确定cache值，默认为100（现有大多是20），如遇特殊需求，酌情调整。

d) 删除、重建的操作，事先检查是否有其他schema拥有对于该sequence的访问权限：

SELECT grantee, owner, table_name, privilege

FROM dba_tab_privs

WHERE table_name = upper(’重建的对象名‘);

e) 全面考虑同步的风险（这里针对OGG），这里主要是需考虑OGG是否需要同步该序列，如序列并发读取较频繁，需在OGG同步中屏蔽该序列，同时准备好相应的序列重建脚本，以备高可用切换时重建。

2
执行过程

a) 标准新建脚本：

CREATE SEQUENCE seq_tablename START WITH 1 CACHE 100;

命名规范： seq_tablename

默认不指定recycle和max value。

b) 标准重建脚本：

DROP SEQUENCE seq_tablename ;

CREATE SEQUENCE seq_tablename START WITH 1 CACHE 100;

为了尽量缩短sequence不可用时间，这两个语句一起放在SecureCRT的chartWindow中一起执行。

c) 标准修改cache脚本：

ALTER SEQUENCE seq_tablename CACHE 200;

d) 标准赋权脚本：

GRANT SELECT ON seq_tablename to username;

3
验证方案

a) 检查db是否有失效对象

b) 通知应用验证是否可以正常访问sequence

2.
增加、删除唯一约束

(1
目的

明确增删唯一约束操作的风险及标准流程，最大限度避免增删唯一约束操作带来的故障。

(2
适用范围

1
项目发布新建表的增删唯一约束

2
对于旧表的增删唯一约束

(3
风险评估

1
对现有表新增唯一约束的操作，会堵塞包括查询在内的所有操作，风险很大，请谨慎使用，尽量在新建表时和开发讨论后增加。

2
没有指定index,系统自动创建了index，删除约束时，自动创建的index同时删除了。

3
在高峰期创建,导致大量的library cache lock/pin的等待

4
表里有重复的数据，导致操作失败。

(4
操作流程

1
准备工作

a) 检查唯一建字段上是否存在index。没有的话,需首先创建index.

b) 检查唯一键上是否有重复数据，如有，需和开发讨论如何处理。

c) 根据应用的需求和数据库的负载情况，确定操作的时间点。对于数据量和访问量较大的表，变更时间点要谨慎选择.

d) 检查字段上是否已经有了约束。

e) 对现有表新增约束，如果使用validate这个参数，会导致该表上连查询在内的所有操作都被锁住，风险非常大；如果使用novalidate参数，这个参数会导致数据字典不一（及导致sqlldr的时候会导入重复数据）。两者相比，故通常情况下用validate的风险更大，默认必须使用novalidate参数。

f) 约束名与所依赖索引名一致。

2
执行过程

a) 用应用账户登录数据库，SHOW USER检查是否连接到正确的schema。

b) 执行增加或删除的命令。命令模板如下：

ALTER TABLE 表名 ADD CONSTRAINT 表名_uk unique (字段名) USING INDEX 索引名 NOVALIDATE;

ALTER TABLE 表名 DROP CONSTRAINT 约束名 KEEP INDEX;

c) 查看过程若无报错，退出当前登录。若有报错，找出报错的地方，修改确认再执行，直至全部执行通过，最后退出当前登录。

3
验证方案

a) 检查表定义是否与OGG对端一致：

b) 检查约束是否加上或删除：

select   *   from dba_cons_columns where table_name=upper(‘table_name’)

2.
加字段

(1
目的

阐述表变更的风险及其步骤，降低对应用的影响和避免故障。

(2
适用范围

1
所有在使用的表的加字段

(3
风险评估

1
新增字段的类型、长度（精度）是否合适

Ø解决方法：

跟应用明确加字段和改字段的风险，确认新增字段类型正确、长度（精度）合适。以及跟应用明确老数据是否要订正？如何订正？新增列是否非空？是否有默认值等等。

2
新增字段的非空属性、默认值以及老数据问题。

新增字段如果是NOT NULL的，则一定要有默认值，否则老应用的insert代码可能报错(这里由开发评估确认是否有影响，是否要加默认值)。表如果存在老数据，带上默认值的时候会导致oracle去订正老的数据行的新增列。如果老数据非常多，表的并发访问高，很有可能导致大面积的阻塞等待以及产生大事务，甚至有可能导致undo耗尽。倘若回滚，还会因为回滚产生的并发会话导致load飙升。

Ø解决方法：

先不带not null不带默认值加上列，再更改列默认值，再批量订正老数据，然后再加上not null属性。如果是大表，并且并发访问很高的表，则新增列不允许为NOT NULL，以简化后面变更步骤，降低风险！

3
新增字段导致依赖对象失效、sql游标失效问题。

当表的DML并发很高的时候，如果表上面还有依赖对象，新增字段会导致依赖对象失效。默认其他DML会话会尝试去自动编译这个依赖对象，此时很可能会出现大面积的library cache pin。应用会话的连接时间会加长，进而导致出现后续应用报不能取得连接池错误。应用服务器load由此飙升。表新增字段也会导致跟该表有关的SQL的游标失效，如果SQL的并发很高（查询SQL或者DML SQL），失效后SQL会重新解析，此时也可能会出现大量的library cache pin & library cache lock

Ø解决方法：

执行前需要严谨分析执行表相关的SQL的并发情况，详情可参考附录3.3的SQL查看表的并发情况。如监控到该表总的执行次数和当前并发很高，需要选择在业务低峰期执行（这个需要跟应用方沟通，了解该表相关SQL的运作情况和频率），同时在实施执行前要再一次查看确认该表的并发情况。

如果该表经监控一直保持高并发状态，需协调应用选择一个合适的时机停止该服务，并告知应用端所带来的影响，同时在对表完成DDL操作后，通知应用端把相关服务开启。

4
表的依赖对象是否要相应调整。

如表上面的依赖对象如果有存储过程或触发器等，逻辑是否需要相应调整，这个需要提醒业务开发人员注意。

(4
操作流程

1
准备工作

a
该表的数据量以及大小，以及数据变更量

b
统计该表的并发访问数，以及频率最高的几种sql的访问方式，并把该SQLID相应的批量查杀脚本准备好，可参考附录3.4 SQL。

2
执行过程

以表T1 加字段 col2为例。T1的数据量非常大，访问频率很高。

a) 打开EM，对数据库性能页面进行实时监控，如在执行过程中发现负载有持续增长趋势，需立刻用准备阶段准备好的脚本进行排查。

b) 同时准备好该DDL操作会话层的KILL -9脚本，如发现EM刷新不可控，应立刻在操作系统层面进行进程终止操作。

c) 老数据订正

–如果需要默认值，加上默认值

Alter table t1 modify col2 default ‘Y’;

–数据订正存储过程

详情可参考附录3.5

d) 订正完后加上NOT NULL属性

Alter table t1 add col2 not null;

3
验证方案

a) 验证表结构已成功修改

b) 验证无失效依赖对象，可查看dba_objects视图

(5
核心对象风险

核心对象风险指的是业务上重要的表，并且数据量很大或表大小很大或并发访问数很高时，或者变更表为SQL发布项目，变更的潜在风险。前面已经阐述。

(6
回退方案

1. 大表的新增字段不允许回滚。因为回滚即删掉字段，会导致锁表，持续时间很长进而导致一序列的严重问题。

2. 普通表的新增字段如果是发布失败，后续还会有二次发布，也不考虑回滚。或者只是将not null属性拿掉，下次发布再加上。

3. 普通表如果应用有足够的理由要求回滚，则回滚。

2.
not null字段

(1
目的

阐述表变更的风险及其步骤，降低对应用的影响和避免故障。

(2
适用范围

1
所有在使用的表的加not null字段，但核心表（并发访问高的大表不允许加not null）。

(3
风险评估

1
新增字段的类型、长度（精度）是否合适

Ø解决方法：

跟应用明确加字段和改字段的风险，确认新增字段类型正确、长度（精度）合适。是否有默认值？以及跟应用明确老数据是否要订正？如何订正？

2
新增字段的非空属性、默认值以及老数据问题。

新增字段是NOT NULL的，则一定要有默认值，否则老应用的insert代码可能报错。表如果存在老数据，带上默认值的时候会导致oracle去订正老的数据行的新增列。如果老数据非常多，表的并发访问高，很有可能导致大面积的阻塞等待以及产生大事务，甚至有可能导致undo耗尽。倘若回滚，还会因为回滚产生的并发会话导致load飙升。

Ø解决方法：

先不带not null不带默认值加上列，再更改列默认值，再批量订正老数据，然后再加上not null属性。如果是大表，并且并发访问很高的表，则新增列不允许为NOT NULL，以简化后面变更步骤，降低风险！

3
新增字段导致依赖对象失效、sql游标失效问题。

表的DML并发很高的时候，如果表上面还有依赖对象，新增字段会导致依赖对象失效。默认访问这些依赖对象的会话（如DML会话，或者应用调存储过程等）会尝试去自动编译这个依赖对象（9i所有会话都会尝试去编译，10g以后只有一个会话去主动编译，其他等待），此时很可能会出现大面积的library cache pin。应用会话的连接时间会加长，进而导致出现后续应用报不能取得连接池错误。应用服务器load由此飙升。表新增字段也会导致跟该表有关的SQL的游标失效，如果SQL的并发很高（查询SQL或者DML SQL），失效后SQL会重新解析，此时也可能会出现大量的library cache pin & library cache lock。

Ø解决方法：

执行前需要严谨分析执行表相关的SQL的并发情况，详情可参考附录3.3 SQL查看表的并发情况。如监控到该表总的执行次数和当前并发很高，需要选择在业务低峰期执行（这个需要跟应用方沟通，了解该表相关SQL的运作情况和频率），同时在实施执行前要再一次查看确认该表的并发情况。

如果该表经监控一直保持高并发状态，需协调应用选择一个合适的时机停止该服务，并告知应用端所带来的影响，同时在对表完成DDL操作后，通知应用端把相关服务开启。

(4
操作流程

1
准备工作

a) 该表的数据量以及大小，以及数据变更量

b) 统计该表的并发访问数，以及频率最高的几种sql的访问方式，并把该SQLID相应的批量查杀脚本准备好，可参考附录SQL。

2
执行过程

以表T1 加字段 col2为例。T1的数据量非常大，访问频率很高。

a) 打开EM，对数据库性能页面进行实时监控，如在执行过程中发现负载有持续增长趋势，需立刻用准备阶段准备好的脚本进行排查。

b) 同时准备好该DDL操作会话层的KILL -9脚本，如发现EM刷新不可控，应立刻在操作系统层面进行进程终止操作。

c) 老数据订正

–如果需要默认值，加上默认值

Alter table t1 modify col2 default ‘Y’;

–数据订正存储过程

详情可参考附录3.5

e) 订正完后加上NOT NULL属性(核心表不要做了)，风险和步骤详情参见文档：4.增加、删除唯一约束

Alter table t1 modify col2 not null;

3
验证方案

a) 验证表结构已成功修改

b) 验证无失效依赖对象，可查看dba_objects视图

(5
核心对象风险

核心对象风险指的是业务上重要的表，并且数据量很大或表大小很大或并发访问数很高时，变更的潜在风险。前面已经阐述。

(6
回退方案

1. 大表的新增字段不允许回滚。因为回滚即删掉字段，会导致锁表，持续时间很长进而导致一序列的严重问题。

2. 普通表的新增字段如果是发布失败，后续还会有二次发布，也不考虑回滚。或者只是将not null属性拿掉，下次发布再加上。

3. 普通表如果应用有足够的理由要求回滚，则回滚。

2.
赋权

(1
目的

明确常用赋权操作标准流程，以及赋权过程中可能产生的风险，最大限度避免赋权操作带来的系统故障。

(2
适用范围

1
对数据库对象的授权操作，数据库对象包括表、存储过程、同义词、视图和序列等。授权类型包括查询、增删改、执行。

2
对数据库用户的系统授权操作。

(3
风险评估

1
对数据库用户进行系统授权时，需要根据实际情况进行，避免因对用户授予过高的系统权限或角色，进而使该用户存在误操作引发数据库或应用故障的风险。

2
对于存储机密数据的表的授权，需要慎重。以免泄露机密数据。

3
对于涉及同步的数据库，需要分别在同步的两端数据库执行相同的授权操作。

4
10G之前版本，grant操作需要获得Exclusive级别的library cache lock/pin。其风险主要针对于procedure、function等，对table基本无影响。若procedure正在执行时，对其本身或者其依赖的procedure、function进行授权，将阻塞其他要执行此procedure或其依赖procedure、function的会话，直到授权前正在执行的procedure结束。

5
对数据库对象授权时，不会引起依赖对象失效，但会导致library cache中与授权对象有依赖关系的游标失效，进而产生硬解析。如果对象的依赖游标过多，或执行频率较高，可能会对系统造成较大的冲击，造成CPU繁忙，latch争用严重，最常引起的latch争用有 shared pool、library cache还会有library cache pin、cursor pin s:wait x等争用出现。如果争用比较严重，甚至可能导致数据库crash。为避免此类情况出现，对于新建对象，应尽可能的先把权限授予给可能会使用到的用户；对于在使用的对象，应充分评估对象依赖游标的个数和执行次数，选择执行低峰进行操作。

6
对于grant any table，或者grant DBA/ EXP_FULL_DATABASE等涉及大量对象的系统授权操作，应该作为重大变更对待，此类操作的风险极大，务必在业务低峰期进行操作。

(4
操作流程

1
准备工作

a) 确认此次授权是否属于正常的业务需要。

b) 若赋予的为系统权限，禁止使用with admin option选项。

c) 若赋予的为对象权限，请确认此对象在数据库中缓存的游标个数，以及每个游标在不同时段的执行频率，根据具体的情况选择合适的变更时间窗口进行授权。

d) 准备授权脚本。

e) 在用对象的授权或涉及大量对象的系统授权需要走一般变更或重大变更流程。

2
执行过程

a) 以赋权对象所在的用户或DBA账号登录数据库，SHOW USER检查是否连接到正确的schema。

b) 监控EM，并准备好该操作的KILL -9脚本.

c) 执行赋权脚本。

d) 查看过程若无报错，退出当前登录。

3
验证方案

以下列举两种验证方式：

i. 验证对象权限：

select owner,grantee,table_name,privilege

from user_tab_privs

where grantee=’&USER_NAME’

and table_name=’&object_name’;

ii. 验证系统权限：select username,privilege from user_sys_privs;

(5
核心对象风险

核心对象上的依赖sql往往较多，而且执行频率较高，授权操作会导致对象依赖的游标失效，进而导致硬解析风暴。应该尽量选择业务低峰期来进行核心表的赋权操作。

(6
回退方案

我们遭遇的授权操作的最大风险第一是导致的硬解析风暴，第二是授权操作涉及数据字典的修改，甚至可能会导致row cache lock的出现。对于硬解析风暴的风险，回退的方案不是revoke对象的权限，而是等待硬解析风暴过去。对于赋权操作引发的问题，要根据具体的情况而定。提前把方案一定要整理好，慎重选择变更的时间，避免出现问题。

2.
修改字段类型或长度

(1
目的

阐述表变更的风险及其步骤，降低对应用的影响和避免故障。

(2
适用范围

1
所有在使用的表修改字段长度，具体是number型和varchar2型，只允许范围扩大。

2
如果是修改字段数据类型，则只能是varchar2到nvarchar2，严禁其他数据类型之间的转换。

3
根据经验，纯粹的字段类型长度的增加的DDL速度较快，但字段类型转换这个消耗的时间较长，持锁造成的解析阻塞影响较大，需要特别注意这种操作，需严格按照下面的风险评估进行。

(3
风险评估

1
相关表的长度是否一并修改

当该表某个字段长度加长后，可能有关联的表的数据来自于该表，那么那个关联的表的相应字段也应该加长。这点由应用去评估。

2
该表上如果有物化视图，则物化视图的基表的对应的字段长度也要加长。

该表上如果有存储过程、触发器、package，里面的代码中跟该字段有关的变量如果声明的是具体的长度，则也要加长。正确的声明方式是col%type。

3
修改字段导致依赖对象失效、sql游标失效问题。

表的DML并发很高的时候，如果表上面还有依赖对象，修改字段长度会导致依赖对象失效。默认其他DML会话会尝试去自动编译这个依赖对象，此时很可能会出现大面积的library cache pin。应用会话的连接时间会加长，进而导致出现后续应用报不能取得连接池错误。应用服务器load由此飙升。

表修改字段长度也会导致跟该表有关的SQL的游标失效，如果SQL的并发很高（查询SQL或者DML SQL），失效后SQL会重新解析，此时也可能会出现大量的library cache pin & library cache lock。

Ø解决方法：

执行前需要严谨分析执行表相关的SQL的并发情况，详情可参考附录SQL查看表的并发情况。如监控到该表总的执行次数和当前并发很高，需要选择在业务低峰期执行（这个需要跟应用方沟通，了解该表相关SQL的运作情况和频率），同时在实施执行前要再一次查看确认该表的并发情况。

如果该表经监控一直保持高并发状态，需协调应用选择一个合适的时机停止该服务，并告知应用端所带来的影响，同时在对表完成DDL操作后，通知应用端把相关服务开启。

(4
操作流程

1
准备工作

a) 该表的数据量以及大小，以及数据变更量。

b) 实施前需在模拟环境进行DDL操作测试，评估消耗时间。

c) 统计该表的并发访问数，以及频率最高的几种sql的访问方式，并把该SQLID相应的批量查杀脚本准备好，可参考附录SQL。

2
执行过程

以表T1 修改字段 col2为例。T1的数据量非常大，访问频率很高。

a) 打开EM，对数据库性能页面进行实时监控，如在执行过程中发现负载有持续增长趋势，需立刻用准备阶段准备好的脚本进行排查。

b) 同时准备好该DDL操作会话层的KILL -9脚本，如发现EM刷新不可控，应立刻在操作系统层面进行进程终止操作。

3
验证方案

a) 验证OGG源端和目标端的表结构已成功修改

b) 验证无失效依赖对象，可查看dba_objects视图

(5
核心对象风险

核心对象风险指的是业务上重要的表，并且数据量很大或表大小很大或并发访问数很高时，变更的潜在风险。前面已经阐述。

2.
新建索引

(1
目的

明确新建索引操作的风险及标准流程，最大限度避免新建索引操作带来的故障。

(2
适用范围

1
项目预发布新建索引

2
项目正式发布新建索引

3
优化sql执行计划新建索引

4
删除索引插入数据后的新建索引， 不包括Impdp导入时候的索引新建

5
分区表上的索引基本上使我们常用的local index

6
无特殊说明，本手册只是针对常规B-tree索引，以及我们常用到的Local Partitioned Indexes和函数索引， 不涉及Bitmap类型,domain类型,临时表上的索引及其他复杂索引。

(3
命名规则

普通索引 ： IX_表名称_字段名

函数索引： FIX_表名称_字段名

(4
风险评估

1
登录到错误的schema下，导致索引建到错误的schema里，而应用无法访问，建议登到own schema 再创建索引。

2
没有指定索引表空间，导致建到了默认表空间，导致后续空间增长和性能问题。

3
为加快创建速度，并行创建索引完成后， 需要将索引的并行度重新改回1. 以免后来对该索引的使用开启不必要的并行， 对于小的索引， 不建议用并行创建。

4
不允许设置nologging选项创建索引。

5
单个索引条目的大小（索引键值的定义长度总和）不能超过6398字节（db_block_size

为8k）， 在建组合索引的时候需要注意到这一点。

6
新建索引时没有指定online， 结果阻塞表上的DML操作，影响应用。

7
新建大索引时，需要注意排序的空间， 保证临时表空间足够可用。

(5
操作流程

5.
创建新表上的索引 （预授权变更）

Ø准备工作

a) 在项目SQL审核阶段，跟设计人员一起明确新索引的设计方案，包括建何种索引，在什么字段新建索引，如何设置前导列。

b) 准备发布脚本时，检查表空间定义，检查表空间剩余空间，估算新建索引需要占用的空间，参考表空间自身负荷及新索引的预期负荷，为每个新建的索引选择合适的表空间，通常与新建的表分开存放，并在建索引语句中添加tablespace的配置。

c) 新建表上的索引操作，可与新建表同时操作，即与开发接口人一起商定好建表以及建索引操作的时间点。如果发生计划外的发布建索引需求，则要追究项目跟进的应用DBA沟通不力的责任。

d) 以目前的认知，在新建表上建操作本身不会对数据库造成风险（前提是新建索引后面必须带online选项），故操作的时间点可以放宽：在变更时间窗口内，均可以执行建索引操作。

Ø执行过程

a) 用DBA或应用账户登录数据库，SHOW USER检查是否连接到正确的schema。

b) 查看过程若无报错，退出当前登录。若有报错，找出报错的地方，修改确认再执行，直至全部执行通过，最后退出当前登录。

Ø验证方案

a) 查看执行计划，判断sql中的sql执行计划是否正确。

b) 检查OGG/DG对端的索引是否创建成功。

5.
创建已有表的索引 （一般变更或重大变更）

Ø准备工作

a) 创建已有表的索引通常有两种情况， 一种为项目新上SQL需要在已有表上创建索引，则需在项目SQL审核阶段，跟设计人员一起明确索引的设计方案。另外一种是DBA自发的为优化sql或者解决数据库性能问题而需要添加索引，同样需要考虑建何种索引，在什么字段新建索引，如何设置前导列，以及新建索引预期效果和可能会影响的SQL范围。

b) 准备新建索引脚本时，检查表空间定义，检查表空间剩余空间，估算新建索引需要占用的空间，参考表空间自身负荷及新索引的预期负荷，为每个新建的索引选择合适的表空间，通常与新建的表分开存放，并在建索引语句中添加tablespace的配置，目前我们已为每个应用账户准备索引表空间，但需要在创建索引语句指定该索引表空间选项。

c) 在已有表上新建索引操作会对数据库造成一定的风险，故操作的时间需要注意：由DBA发起的操作， 尽量安排在低峰期操作，新上项目发布新建操作可以与项目发布同时（重大变更除外）。

Ø执行过程

a) 用DBA或应用账户登录数据库，SHOW USER检查是否连接到正确的schema。

b) 查看过程若无报错，退出当前登录。若有报错，找出报错的地方，修改确认再执行，直至全部执行通过，最后退出当前登录。

Ø验证方案

a) 查看执行计划，判断sql中的sql执行计划是否正确。

b) 检查OGG/DG对端的索引是否创建成功。

(6
核心对象风险

1） 由于核心对象通常比较大， sql关联比较复杂，访问比较频繁， 新建index的变更很可能会超过十分钟，风险比较大，所以务必要在业务低峰期进行，并且必须加online选项。同时所制定的方案需要经过经理同意。

2） 在核心对象上新建索引，需要关注新索引影响的范围是单个sql还是会影响其他sql，检查并整理出可能影响到的sql，如果新建后导致其他sql执行计划发生了意想不到的变化， 需要考虑后续方案或者回退方案。

3） 大致估算新建索引大小，需要考虑用于排序的临时表空间是否足够，如果可能会不够的话，加大临时表空间。考虑索引所在的表空间是否足够，是否预加空间。

4） 11g以前在创建索引的时候要确保长事务已经提交。

(7
回退方案

新建索引的过程存在一定风险，建成后也可能会导致单条或多条sql改变执行计划，直接或间接影响到数据库和应用， 必须准备好回退方案。

若索引已建成，但是没有达到预期效果 需要回滚。

1） 直接删除索引

Drop index   indexname ;

2) 设置索引不可用 (Oracle 11g以前)，索引更新停止，需要rebuild才能重新使用， 不推荐使用。

Alter index indexname unusable;

3) 设置索引不可见（Oracle 11g开始），索引正常更新，但会被CBO忽略 .除非显式的设置OPTIMIZER_USE_INVISIBLE_INDEXES参数为true

Create / Alter index indexname invisible;

3) 若create index online 长时间没有响应，系统仍可控的话， 不要轻易尝试cancel ,试着找出阻塞的原因， 如果kill后遇到数据字典不一致， 或者等待index (re)build online cleanup，需用SYS登录执行附录3.6修复脚本。

2.
重建索引

(1
目的

明确新建索引操作的风险及标准流程，最大限度避免新建索引操作带来的故障。

(2
适用范围

1
索引本身失效， 或者由于其他操作导致索引失效的情况

2
有索引unusable需要改为可用的情况

3
由于数据删除率过大， B-tree高度过高等导致的索引效率降低， 空间浪费的情况， 但是我们总是建议在需要的时候才去重建索引

4
移动索引至其他表空间的情况（一般是使用move方式实现）

5
无特殊说明，本手册只是针对常规B-tree索引，以及我们常用到的Local Partitioned Indexes和函数索引， 不涉及Bitmap类型和unique类型，临时表上的索引及其他复杂索引， 重建的方式为rebuild online。

(3
风险评估

1
登录到错误的schema下，导致索引建到错误的schema里，而应用无法访问，建议登陆到own schema 再创建索引。

2
没有指定索引表空间，导致建到了默认表空间，导致后续空间增长和性能问题。

3
为加快创建速度，并行创建索引完成后， 需要将索引的并行度重新改回1. 以免后来对该索引的使用开启不必要的并行， 对于小的索引， 不建议用并行创建，可能会浪费空间。

4
不允许设置nologging选项创建索引。

5
重建索引时没有指定online， 结果阻塞表上的DML操作，影响应用， 但是在11G前，rebuild online 在开始和结束阶段还是会申请类型为4的TM锁，然后再做锁转化，如果当时有DML没有提交的的话， 还是会发生堵塞，所以在重建索引的时候，尽量避免并发的DML，设置online rebuild时， 在该表上将无法进行并行的DML操作。

6
移动lob的数据对象， 会自动将对lob对象所对应的lob类型的索引移动到同一个表空间， 需关注该表空间空余空间大小。

(4
操作流程

Ø准备工作

a) 首先要明确因何种原因重建索引，根据原因确定重建方案和时间，如果不是需要紧急处理的情况，重建索引应该尽量安排在深夜后实施。

b) 准备重建索引脚本或命令，可用explain大致估算重建后索引需要占用的空间，检查目标表空间定义和剩余空间，检查临时表空间是否足够。

c) 如果是核心业务表，准备好重建后运行该表的统计信息收集脚本。

Ø执行过程

a) 用DBA或应用账户登录数据库，SHOW USER检查是否连接到正确的schema。

b) 索引重建过程中， 需要监控temp表空间的消耗，目标表空间的消耗， 以及通过v$session_longops 等其他视图查看重建的进度。

c) 查看过程若无报错，退出当前登录。若有报错，找出报错的地方，修改确认再执行，直至执行通过，最后退出当前登录。

Ø验证方案

a) 查看执行计划，判断sql中的sql执行计划是否正确。

b) 检查OGG对端的索引是否创建成功。

(5
核心对象风险

1） 由于核心对象通常比较大， sql关联比较复杂，访问比较频繁， 重建index的变更很可能会超过十分钟，风险比较大，所以务必要在业务低峰期进行，并且须加online选项。

2） 对于核心对象的索引重建， 事先需要跟应用沟通一下，所制定的方案需要在同版本的测试库上做完整的测试，确认下的方案需要经过经理同意。

3） 在核心对象上重建索引，需要关注新索引影响的范围是单个sql还是会影响其他sql，应该事前检查并整理出可能影响到的sql，如果重建后导致其他sql执行计划发生了意想不到的变化， 需要考虑后续方案或者回退方案。

4） 需要考虑用于排序的临时表空间是否足够，如果可能会不够的话， 建议创建一个大的临时表空间用来重建索引，重建完索引后在drop掉该表空间。考虑索引所在的表空间是否足够，是否预加空间。

(6
回退方案

重建索引的过程存在一定风险，重建后也可能会导致单条或多条sql改变执行计划，直接或间接影响到数据库和应用， 必须准备好回退方案。

1）若在rebuild过程中失败了，在目标表空间创建的是temporary 类型的segment， 系统会自动清理， 对原来的索引并没有影响， 但为了确保Smon已经被唤醒并清理完毕， 再一次的重建最好在一个小时之后在进行。

2）若rebuild index online 长时间没有响应，系统仍可控的话， 不要轻易尝试cancel ,试着找出阻塞的原因， 如果cancel 或kill后遇到数据字典不一致(Bug 3805539)， 如果不着急可以等待pmon自己去过清理， 如果着急的话， 可以尝试手动清理清理（10GR2后），详情可参考附录3.6

3）若rebuild index已经完全结束，则无法回滚到原先的索引， 对此引发的突发问题， 需要根据遇到的实际场景， 进行有效地决策。

2.
删除索引删除索引

(1
目的

明确删除索引操作的风险及标准流程，最大限度避免删除索引操作带来的故障。

(2
适用范围

1
由于新建了索引，冗余的索引可以删除的情况。

2
存在的索引没有在被使用或者并没有加快查询，浪费空间和降低DML效率的情况。

3
由于SQL走错执行计划，通过删除索引来调整执行计划的情况。

4
在IMP导入或者大数据量修订的时候，不允许先删除索引之后再重建的方式， 而是先设置索引不可用。

5
无特殊说明，本手册只是针对常规B-tree索引，以及我们常用到的Local Partitioned Indexes和函数索引， 不涉及Bitmap类型和domain类型的索引及其他复杂索引。

6
在11G中， 如果不是为了节约空间的考虑， 可以选择将index设置成不可见，索引正常更新，但会被CBO忽略，观察三天没有问题的话再删除。

(3
风险评估

1
登陆错了schema，没有经过检查就删除，导致该schema下的同名的索引被删除。

2
试图删除没有在使用的索引前， 没有monitor一段时间， 结果发现该索引还是有在被使用的， 导致改sql走错执行计划。

3
试图删除关联强制唯一或主键的索引或者尝试删除local index的一个索引分区会报错。

4
如果索引正在被使用，尝试删除索引的话因为申请不到类型为6的TM锁，直接抛出resource busy的错误

5
在IMP等导入数据的时候删除索引， 因索引缺失引发的其他sql走错执行计划的风险。

6
索引删除后shared_pool中的与这个表相关的sql都会被aged out，必须重新分析,这个带来了潜在的风险。

7
尝试删除系统维护的Lob字段将会抛错， 只能对对应的lob字段进行操作。

(4
操作流程

1
准备工作

a) 首先要明确因何种原因删除索引，根据原因确定删除方案和时间，如果不是需要紧急处理的情况，删除索引应该安排在业务低峰期完成。

b) 准备删除索引脚本或命令，如果是删除不会被用到的索引，需要确保没有sql引用该索引， 我们通常的方式是：

1） monitoring index usage 至少一个星期， 以索引所在的schema然后查看V$OBJECT_USAGE

2） 通过查看EM TOP SQL，dba_hist_sql_plan，v$segment_statistics，AWR报告等方式确认。

c) 删除索引前必须准备好回滚方案， copy出索引的完整定义放置变更单中， 以便能及时回滚， 并且备份索引的统计信息。

2
执行过程

a) 用DBA或应用账户登录数据库，SHOW USER检查是否连接到正确的schema。

b) 查看过程若无报错，退出当前登录。若有报错，找出报错的地方，修改确认再执行，直至执行通过，最后退出当前登录。

3
验证方案

a) 查看执行计划，判断sql中的sql执行计划是否正确。

b) 检查OGG/DG对端的索引是否创建成功。

(5
核心对象风险

1） 由于核心对象通常比较大， sql关联比较复杂，访问比较频繁， 删除index的变更风险比较大，所以务必要在业务低峰期进行。

2） 对于核心对象的索引删除， 事先需要跟应用沟通一下，所制定的方案需要在同版本的测试库上做完整的测试，确认下的方案需要经过经理同意。

3） 在核心对象上删除索引，需要关注影响的范围是单个sql还是会影响其他sql，应该事前检查并整理出可能影响到的sql，如果删除后导致其他sql执行计划发生了意想不到的变化， 需要考虑后续方案或者回退方案。

(6
回退方案

删除索引的过程存在一定风险，重建后也可能会导致单条或多条sql改变执行计划，直接或间接影响到数据库和应用， 必须准备好回退方案。

1）因事先保存了索引创建的完整定义，回滚的第一选择是创建一个和原先一样的索引，以便进行回滚。

2）若按照原来的定义创建后问题还是没有解决，则无法回滚到原先的索引，对此引发的突发问题，需要根据遇到的实际场景，进行有效地决策。

2.
Truncate

(1
目的

明确truncate表操作的风险及标准流程，最大限度避免truncate表操作带来的故障。本文涉及到truncate table,truncate partition两个操作。

(2
适用范围

1
临时数据清理

2
定时truncate表数据清理

(3
风险评估

1
登录到错误的数据库实例或schema下，导致truncate到错误的schema里的表，而应用无法访问。

2
脚本末尾缺少分号，导致该truncate没有被执行上，而执行DDL的过程又不会报错。

3
其他原因truncate错了表，导致应用访问错误。

(4
操作流程

1
准备工作

a) Truncate操作分为两类：定时truncate和临时数据清理。

操作之前，请明确操作类型，然后选择合适的truncate操作。

b) 定时truncate：

i. 明确告知开发部门truncate操作是不可逆操作；

ii. 确认数据是定期build生成且是可重复操作；

iii. truncate操作，必须要有exception处理，防止诸如表上有事务等，truncate不成功的情况发生。进而影响整个脚本的执行；

iv. 模板：

Begin
Execute Immediate ‘truncate table table_name’;
Exception When Others Then
–which error info you want to do?
…….;
End;

c) 临时数据清理

i.   确保OGG对端，对执行此TRUNCATE操作，是否会带来报错，提前避免OGG报错。

iii. 和应用部门协商所清理数据，是否需要备份。因为Truncate操作是不可逆的,需要进一步确认.

1
若需要备份：truncate表主要是为了节省空间，因此推荐使用EXPDP的方法导到本地，然后TAR打包后传至备份服务器的方式处理。此外还可以使用create table .. as ..的方法进行备份；

2
不需要备份，则直接处理。

d) Truncate语法说明：

i. Truncate 表：

Truncate table table_name；

ii. Truncate 分区：

Alter Table table_name Truncate Partition partition_name;

e) Truncate操作，会把UNUSABLE的索引变成VALID。这点需要注意，目前规范中不允许把索引置为UNUSABLE。因此该风险理论上不存在，但需要注意。

f) Truncate操作，不需要维护相关依赖。

g) Truncate操作尽量安排在变更窗口执行，若是定时build数据清理，则结合应用处理时间及变更窗口，综合评估。

2
执行过程

a) 用DBA或应用账户登录数据库，SHOW USER检查是否连接到正确的schema。

b) 请再次确认truncate所操作表名，分区名是否正确。

c) Truncate命令必须一条条粘贴，只有确认上一条命令执行成功后，方可执行下一条命令。

d) 查看过程若无报错，退出当前登录。若有报错，找出报错的地方，修改确认再执行，直至全部执行通过，最后退出当前登录。

(5
核心对象风险

核心对象严禁truncate操作，防止数据无法回滚。

(6
回退方案

Truncate操作回滚方案分为有备份和无备份两类：

有备份：直接从手工备份里恢复数据。

无备份：把RMAN的备份传至异地进行不完全恢复（前提是有RMAN备份），然后对该表数据进行还原。

2.
删除表

(1
目的

说明删除表的前置条件、操作步骤，降低对对应用造成的影响及避免故障

(2
适用范围

1
所有生产数据库

(3
风险评估

1
应用评估不完善，仍有部分应用访问该表，删除后应用报错。

2
跨库依赖关系没有整理，删除表后导致其他库上的应用报错。

3
误操作，登陆到其他schema下，导致删除其他表。

4
删除前未进行备份，后续无法及时进行有效的恢复。

(4
操作流程

1
准备工作

a) 整理依赖该表的对象（存储过程、视图、同义词等）及授权情况：可查看dba_dependencie视图，在其他库上查找访问该表所在库的dblink，如果存在dblink，根据dblink名字查跨库依赖该表的对象：

select * from dba_dependencies where REFERENCED_LINK_NAME = ‘dblink名‘ and referenced_name=‘删除的表‘;

b) 根据表和其依赖对象，配合开发整理应用，对应用进行改造、下线处理。

c) 检查数据库上的定时任务，以确实是否有访问该表及其依赖对象。如果有，跟开发确认后停掉。

d) 步骤a、b实施后，如表有依赖对象，先将依赖对象进行提变更将表进行重命名，请开发配合监控应用一周以上.

2
执行过程

a) 备份该表

c) 删除表

3
验证方案

a) 在表所在库及其dblink关联库检查失效对象

b) 通知开发检查应用

(5
核心对象风险

对于核心表，关联的应用比其他表要多，风险主要在排查应用上，一般情况下核心表极少有删除需求。

(6
回退方案

a) 用逻辑备份进行恢复

2.
物化视图

(1
目的

说明物化视图的常用功能和限制条件

(2
适用范围

1
所有线上库

(3
风险评估

1
on commit模式创建的物化视图会基表上的分布式事务失败。

2
创建物化视图时查询里使用了*代替基表所有列，后续基表加字段时，物化视图需要重建。

3
创建支持快速刷新的物化视图，后续遗忘刷新增量数据，导致物化视图log积压很多。

4
多表join的物化视图增量刷新时有重复数据，谨慎使用。

5
经验证明，不要用DBLINK方式创建物化视图，刷新会偶尔出现异常情况。

(4
操作流程

1
准备工作

a) 检查用户权限，物化视图的所有者需要CREATE MATERIALIZED VIEW和create table权限，以及基表的查询权限。如果创建支持on commit刷新的物化视图，需要对基表有ON COMMIT REFRESH权限。如果创建支持query rewrite的物化视图，需要对基表有QUERY REWRITE权限。

b) 检查用户的表空间配额(quota)，物化视图的数据需要独立存储空间。

c) 如果基表跟物化视图不在一个库上，需要创建dblink。

d) 准备物化视图的查询sql，根据需要调整sql结构，保证执行计划正常。

e) 如果创建支持快速刷新的物化视图，需要保证：

Ø物化视图不能包含对不重复表达式的引用，如SYSDATE和ROWNUM

Ø物化视图不能包含对LONG和LONG RAW数据类型的引用

Ø基表必须有主键

ØFROM语句列表中的所有表必须建立基于ROWID类型的物化视图日志

Ø如果物化视图基表是通过dblink来访问的，查询子句中多表的连接条件必须是主键。

2
执行过程

a) 创建物化视图日志：（创建非快速刷新的物化视图不需要此步骤）

物化视图的关键点在with子句部分，下面oracle文档上with子句的语法结构：

object id：仅当基表是对象类型时，with 子句可以此项，一般极少使用。

primary key：默认情况下，主键是包含在物化视图日志里，可以不指定此项。

rowid：指定时，物化视图日志里会记录rowid信息。

sequence：指定时，物化视图日志里多一列sequence$$，给每个操作一个SEQUENCE号，从而保证刷新时按照顺序进行刷新。

column：指定哪些列的数据存放在物化视图日志里，注意这些列不能是主键列。

new_value_clause：有两个选项。

默认是excluding new values，即物化视图日志里不记录数据变更时的新值

including new value：即物化视图日志里记录数据变更前后的新旧值，即同

一个主键id会对应两条日志记录。

下面是几个创建物化视图的示例：

CREATE MATERIALIZED VIEW LOG ON customers WITH PRIMARY KEY, ROWID;

CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID, SEQUENCE(amount_sold,

time_id, prod_id)INCLUDNG NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON order_items WITH (product_id);

b) 创建物化视图：

创建物化视图的重点在刷新方式上，刷新方式的语法参照下面：

fast：使用快速刷新，需要先做步骤一，这种刷新方式使用物化视图日志做增量，刷

时间短。

complete：每次都是全量刷新，全量刷新时先truncate物化视图现有数据，重新

从基表生成数据，刷新时间比较长，对应用影响大，一般应用有实时要求的

不能采用这种方式。

force：这种刷新方式会先尝试fast刷新，如果不支持fast，则用complete方式数据，

不指定时，默认是这个。

on commit：物化视图的基表数据发生修改时实时刷新物化视图，这种方式对应用

事务稍有影响，一般不建议使用。

on demand：根据需要刷新，一般采用这个方式，用job或者crontab或者应用程序主动调用dbms_mview.refresh方法来刷新数据。不指定的情况下，默认是这个。

build immediate：创建物化视图的同时做一次全量刷新，一般采用这种方式。不指定时默认是这个。

build deferred：创建物化视图时不生成数据，后续第一次刷新时进行全量刷新。不指定时默认是这个。

enable/disable query rewrite：是否支持查询重写，如果是enable，oracle优化器会对一些sql使用该物化视图进行优化，这样来减少查询时间。

3
验证方案：

a) 检查物化视图刷新机制是否正常：

－－快速刷新指定的物化视图

execute dbms_mview.refresh(‘物化视图名字‘,‘f’);

－－全量刷新指定的物化视图

execute dbms_mview.refresh(‘物化视图名字‘,‘c’);

b) 对物化视图的基表做数据修改，然后使用物化视图的查询子句创建一个临时表。

create table tmp_物化视图名 as 物化视图的查询子句。

然后分别按照上面方法手工刷新物化视图，刷新后核对物化视图和临时表的数据量是否一致。

(5
核心对象风险

1
物化视图可以用来对核心表进行数据迁移，以及支持核心表的数据抽取。

2
由于在数据量比较大的情况下，全量刷新耗时比较久，核心表上物化视图尽量使用fast refresh模式。

3
核心表访问量大，数据变更频繁，其上的物化视图不能使用on commit模式创建。

4
创建跨dblink的且多表join的物化视图时，如果join条件不是主键，不支持fast refresh，尽量不要在核心表创建这种物化视图，目前我们环境禁止这种DBLINK方式的物化视图。

(6
回退方案

创建物化视图失败时：

1
如果基表上第一次创建物化视图，使用包dbms_mview.explain_mview检查创建失败原因，如果是物化视图日志不符合要求，删除物化视图日志即可。

2
如果不确定基表上是否有其他物化视图，检查基表上是否有其他物化视图，如果没有，直接删除物化视日志。

3
如果存在，需要评估对其他物化视图的影响。

4
检查基表上依赖的物化视图：

select a.owner, a.name, a.mview_site

from dba_registered_mviews a, dba_base_table_mviews b

where a.mview_id = b.mview_id

and b.master = ‘基表名‘;

2.
发布存储过程、触发器、视图

(1
目的

明确发布存储过程、触发器、视图操作的风险及标准流程，最大限度避免发布存储过程、触发器、视图操作带来的故障。

(2
适用范围

1
创建存储过程、触发器、视图

2
删除存储过程、触发器、视图

3
修改存储过程、触发器、视图

(3
风险评估

1
登录到错误的schema下，导致代码建到错误的schema里，而应用无法访问。

2
漏发代码，导致应用无法访问。

3
提取了错误代码，导致业务逻辑错误。

4
代码编译不通过，导致脚本无法调用。

5
代码逻辑错误，导致数据不正确。

6
代码中有SQL性能差，引起数据库压力高。

7
没有备份旧的代码，导致无法及时回滚。

8
变更的对象有大量并发访问，变更时出现大量library cache pin/lock，导致数据库无法提供服务。

9
DBA在复制代码中出错，如中文出现乱码等情况，导致应用出错。

10
登录触发器语法错误，导致后续的会话全部登录失败（此时需立刻禁用该触发器）。

(4
操作流程

1
准备工作

a) 熟悉要变更对象的代码逻辑，与应用人员沟通新发布脚本的具体功能，发布到哪个数据库环境，及变更对业务的影响。

b) 整理要发布的对象脚本，保存为一个文件，如果代码较多则保存为多个文件。

c) 如果是修改或删除存储过程或视图，到线上检查是否有依赖的对象，变更后依赖对象是否能编译通过，如果有依赖对象需要在变更方案中增加依赖对象的编译脚本，如果有依赖对象不能编译通过，应与应用人员反馈，并修改代码，线下调试通过。

d) 如果是删除视图或存储过程，需与应用人员确认对象可以删除，并且从v$sql中检查对象是否还有调用，如果对象还有调用则不能删除。

e) 修改或删除对象需要分析对象的调用逻辑及时间点，如果对象运行的时间很长，则需要选择一个对象没有运行的时间点操作，以免编译锁等待。

g) 如果编译存储过程中遇到编译阻塞，要及时杀掉编译会话的SID。

2
执行过程

a) 使用PL/SQL工具连接到线上库，在命令窗口环境下，用应用账户登录数据库，SHOW USER检查是否连接到正确的schema。

b) 记录下执行变更会话的SID，以便遇到阻塞时进行回滚KILL操作。

c) 执行对象变更脚本。

e) 如果是新建存储过程或视图对象脚本编译出错，检查出错原因，修改确认再执行，如果不能找到出错原因，或者是代码本身有逻辑错误，则联系应用人员进行修改，线下验证后再重新发布。

f) 如果是变更存储过程或视图对象脚本编译出错，先回滚代码，然后检查出错原因，修改确认再执行，如果不能找到出错原因，或者是代码本身有逻辑错误，则联系应用人员进行修改，线下验证后再重新发布。

3
验证方案

a) 检查更新后的存储过程是否有失效现象，如果有需根据出错提示找出编译错误原因。

b) 如果应用验证有问题，则与应用沟通是否需要回滚，变更是否成功以应用测试结果为准。对于定时执行的对象，应与应用跟踪定时执行的结果，确实变更是否成功。

(5
核心对象风险

变更的对象有大量访问，变更时出现大量library cache pin，导致数据库无法提供服务。

3
附录

3.
查看大事务执行进度

select username,SID, v$session_longops.serial#

round(sofar*100 / totalwork,0) || '%' as progress,time_remaining,sql_text

from v$session_longops , v$sql

where time_remaining <> 0 and sql_address = address and sql_hash_value = hash_value ;

3.
查看活动会话的UNDO的使用情况

select a.username,b.name,c.used_ublk

from v$session a,v$rollname b,v$transaction c

where a.saddr=c.ses_addr and b.usn=c.xidusn;

3.
查找当前并发较高的对象SQL

Select Distinct a.Sql_Text, a.Sql_Id, Executions

From V$sql a

Join V$open_Cursor b

On a.Sql_Id = b.Sql_Id

Where User_Name = 'MBS7_PCM'

And Upper(a.Sql_Text) Like '%WI_WARE%'

And Last_Active_Time > Sysdate - 3 / 1440 --AND cursor_type=open

3.
查杀同一个SQLID的会话

select 'alter system kill session '''||c.sid||','||c.SERIAL# ||''';' from v$session c where sql_id='1vnbgy700u1cu' ;

3.
指定数据订正存储过程

Create or replace procedure sp_dml0214

As

Cursor c1 is select rowed rid, id, col2 from t1 where col2 is null;

V_cnt number := 0;

Begin

For rec_c1 in c1 loop

V_cnt := v_cnt + 1;

Update t1 set col2=’Y’ where rowed = rec_c1.rid and id=rec_c1.id;

If mod(v_cnt,500)=0 then

Commit;

Dbms_application_info.set_client_info(‘sp_dml0214 ‘ || v_cnt || ‘ rows!’);

End if;

End loop;

Commit;

Dbms_application_info.set_client_info(‘sp_dml0214 ‘ || v_cnt || ‘ rows!’);

End;

/

Exec sp_dml0214;

3.
索引数据字典不一致的修复

DECLARE

RetVal BOOLEAN;

OBJECT_ID BINARY_INTEGER;

WAIT_FOR_LOCK BINARY_INTEGER;

BEGIN

OBJECT_ID := 608365; 索引的OBJECT_ID

WAIT_FOR_LOCK := NULL;

RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN ();

COMMIT;

END;

/

3.
物化视图的示例

CREATE MATERIALIZED VIEW sales_mv

BUILD IMMEDIATE

REFRESH FAST ON DEMAND

AS SELECT t.calendar_year, p.prod_id,

SUM(s.amount_sold) AS sum_sales

FROM times t, products p, sales s

WHERE t.time_id = s.time_id AND p.prod_id = s.prod_id

GROUP BY t.calendar_year, p.prod_id;

CREATE MATERIALIZED VIEW sales_by_month_by_state

TABLESPACE example

PARALLEL 4

BUILD IMMEDIATE

REFRESH COMPLETE

ENABLE QUERY REWRITE

AS SELECT t.calendar_month_desc, c.cust_state_province,

SUM(s.amount_sold) AS sum_sales

FROM times t, sales s, customers c

WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id

GROUP BY t.calendar_month_desc, c.cust_state_province;

下面这个是基于dblink的物化视图，查询子句中连接条件不是主键，因此不支持fast

refresh。

CREATE MATERIALIZED VIEW TPCRM_LEADS_INFO

BUILD IMMEDIATE

REFRESH COMPLETE

SELECT o.customer_id as id,

o.gmt_create,

o.gmt_modified,

o.owner_1,

c.country,

c.member_id

FROM caesar.caesar_customer_ggs@crmg c, caesar.caesar_opportunity@crmg o

WHERE c.is_deleted = ‘n’

AND o.is_deleted = ‘n’

AND c.cus_id = o.customer_id;