阿里巴巴数据库操作手册Word下载.docx

阿里巴巴数据库操作手册Word下载.docx

《阿里巴巴数据库操作手册Word下载.docx》由会员分享，可在线阅读，更多相关《阿里巴巴数据库操作手册Word下载.docx（114页珍藏版）》请在冰豆网上搜索。

严禁使用sys、system等用户建表。

b）执行建表脚本。

若一次建表个数超过三个以上，要求将脚本事先保存为文本文件，上传至数据库服务器，执行时使用@create_table_ddl.sql的方式直接执行。

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

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

3.验证方案

a）常规检查：

@dbcheck

b）检查表定义是否与测试库一致：

execpkg_check.CompareObject（’user‘,’TABLE_NAME‘）;

c）立即联系开发接口人进行应用测试，【建表】变更是否成功以应用测试结果为准。

d）同步库若建表，也需要执行a）和b）两个步骤。

02-数据订正

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

l新建表数据初始化

l现有表新增数据

l现有表删除数据

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

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

l业务风险：

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

l程序风险：

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

l数据库风险：

订正本身业务正确，应用程序也可以兼容，但是订正速度过快、订正并发压力过大，导致数据库无法正常提供服务。

通常会造成表空间耗尽、undo消耗过快、archive增长过快、备库恢复压力大等问题。

l沟通风险：

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

l回滚风险：

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

l同步风险：

各类同步架构下，数据订正可能导致同步堆积和同步延时，影响正常同步业务，所以有些大规模订正必须要正确屏蔽同步，并在多个库分别执行相同的订正脚本。

l缓存：

有些表在应用层面做了缓存，制定订正计划的时候要考虑到订正后是否需要更新缓存。

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

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

c）根据数据量评估对数据同步的影响，决定是否屏蔽同步。

（应用DBA必须熟悉同步采用的技术、正常情况下的同步量和延时、可以容忍的同步延时、屏蔽同步的具体方法。

）

d）注意规划订正速度，以防undo消耗殆尽。

e）订正脚本：

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

ii.开发接口人提供主键及更新字段新值列表，由DBA导入数据库，写SQL脚本关联原表批量订正。

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

iv.订正脚本要求可断点续跑，可反复执行。

v.严禁仅用一个事务来处理大规模订正（影响的记录数超过1万笔）。

超过一万笔的订正必须分段提交。

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

vii.脚本中加入“进度报告”，即调用如下包（但是对于trigger中判断client_info的不允许这样处理。

）：

Dbms_Application_Info.set_client_info（n||‘rowscommit.’）;

–n为变量，累加，表示当前订正的总记录数。

f）开发阶段跟开发接口人确认数据订正逻辑，完成订正脚本，并跟开发接口人确认脚本是否正确，同时按照需求准备备份脚本。

g）测试阶段在测试库执行订正脚本，由开发接口人和测试人员验证订正的正确性，应用DBA协助验证。

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

i）备份要求：

i.新建表初始化：

无需备份，回退时直接truncate即可。

ii.现有表新增数据：

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

iii.现有表删除数据：

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

iv.现有表上新增字段初始化：

无需备份，回退时将该字段update为NULL或者开发接口人要求的值。

不得将删除字段作为回退手段。

v.现有表上现有字段值修改：

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

vi.备份表：

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

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

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

b）执行订正脚本。

查看订正进度，使用如下脚本：

selectclient_infofromv$sessionwhereclient_infoisnotnull;

–这个脚本必须配合前面描述的“进度报告”脚本执行。

c）检查undo消耗:

@undo

d）检查表空间消耗:

@tbs

e）检查归档空间

f）检查同步延时是否异常。

g）如果需要刷新应用缓存，在订正结束后通知应用刷新缓存。

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

以应用验证通过为操作成功标准。

五、核心对象风险

l考虑到对erosa和otter的影响，严禁数据订正更新主键值。

六、回退方案

按照备份时所做的各种不同的回退方案进行回退，回退之后也要要求应用做验证。

03-创建、删除、修改sequence

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

l项目发布创建新sequence。

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

l在线修改sequence的cache值。

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

l双向同步的库，多库创建同名sequence，起始值和步长值设置不合理，导致生成的值在表中对应主键值同步产生冲突。

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

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

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

lJava程序的int16数据类型只能容纳最大21亿，所以sequence不能超过这个值，如果有可能超过，需要跟开发确认。

a）默认使用变更系统生成的sequence名称，如果要修改，必须跟开发人员沟通一致。

b）与开发人员、项目发布负责人沟通变更时间点。

对于删除、重建的操作必须明确告诉他们其间会有短暂的无法访问，如果是高并发的应用则选择在系统访问量最低的时候执行，规避风险。

c）根据并发数确定cache值，默认为100，如遇特殊需求，酌情调整。

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

SELECTgrantee,owner,table_name,privilege

FROMdba_tab_privs

WHEREtable_name=upper（’重建的对象名‘）;

e）全面考虑同步的风险，确定同步环节中各个数据库的同名sequence起始值及步长，保证不会发生冲突，通常有如下两种做法：

i.起始值相差不大，步长值等于数据库个数。

以双库同步为例，起始值分别设为1和2，步长均设为2。

ii.起始值相距较大，步长值相同。

以双库同步为例，A库起始值设为1，B库起始值设为2亿，步长均设为1。

相差的值可以根据增长预期进行调整。

a）标准新建脚本：

CREATESEQUENCEseq_tablenameSTARTWITH1CACHE100;

命名规范：

seq_tablename

默认不指定recycle和maxvalue。

b）标准重建脚本：

DROPSEQUENCEseq_tablename;

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

c）标准修改cache脚本：

ALTERSEQUENCEseq_tablenameCACHE200;

d）标准赋权脚本：

GRANTSELECTONseq_tablenametousername;

a）@dbcheck检查是否有失效对象

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

高并发对象重建时短暂不可访问；

04_增加、删除唯一约束

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

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

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

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

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

l在高峰期创建,导致大量的librarycachelock/pin的等待

l有同步的应用，先要在源端加，后在目标端加。

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

a）检查唯一建字段上是否存在index。

没有的话,需首先创建index（步骤详见增加index手册）。

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

c）根据应用的需求和数据库的负载情况，确定操作的时间点。

对于数据量和访问量较大的表，变更时间点要谨慎选择.

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

e）增加和删除唯一约束属于标准变更，需要开发在ITIL中提交事件单，应用dba提交变更单，有技术经理审批后执行。

f）对现有表新增约束，如果使用validate这个参数，会导致该表上连查询在内的所有操作都被锁住，风险非常大；

如果使用novalidate参数，这个参数会导致数据字典不一致（及导致sqlldr的时候会导入重复数据）。

两者相比，故通常情况下用validate的风险更大，默认必须使用novalidate参数。

g）约束名与所依赖索引名一致。

b）执行增加或删除的命令。

命令模板如下：

ALTERTABLE表名ADDCONSTRAINT表名_ukunique（字段名）USINGINDEX索引名NOVALIDATE;

ALTERTABLE表名DROPCONSTRAINT约束名KEEPINDEX;

如有otter同步，要注意执行顺序：

先在源数据库端加后在目标端增加。

c）检查约束是否加上或删除：

select*fromdba_cons_columnswheretable_name=upper（‘table_name’）

1.核心表访问量大，数据量大。

增加唯一约束时会短暂出现librarycachepin/lock。

执行时间要订在核心表访问的低峰期。

1.执行前需准备好回退的脚本。

2.回退时需得到开发的确认，并确认回退的时间点。

05-加字段

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

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

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

解决方法：

跟应用明确加字段和改字段的风险，确认新增字段类型正确、长度（精度）合适。

以及跟应用明确老数据是否要订正？

如何订正？

新增列是否非空？

是否有默认值等等。

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

新增字段如果是NOTNULL的，则一定要有默认值，否则老应用的insert代码可能报错。

表如果存在老数据，带上默认值的时候会导致oracle去订正老的数据行的新增列。

如果老数据非常多，表的并发访问高，很有可能导致大面积的阻塞等待以及产生大事务，甚至有可能导致undo耗尽。

倘若回滚，还会因为回滚产生的并发会话导致load飙升。

先不带notnull不带默认值加上列，再更改列默认值，再批量订正老数据，然后再加上notnull属性。

如果是大表，并且并发访问很高的表，则新增列不允许为NOTNULL，以简化后面变更步骤，降低风险！

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

表的DML并发很高的时候，如果表上面还有依赖对象，新增字段会导致依赖对象失效。

默认其他DML会话会尝试去自动编译这个依赖对象，此时很可能会出现大面积的librarycachepin。

应用会话的连接时间会加长，进而导致出现后续应用报不能取得连接池错误。

应用服务器load由此飙升。

表新增字段也会导致跟该表有关的SQL的游标失效，如果SQL的并发很高（查询SQL或者DMLSQL），失效后SQL会重新解析，此时也可能会出现大量的librarycachepin&

librarycachelock。

选择在业务低峰期发布，同时在数据库级别开启trigger禁用客户端程序自动编译功能，字段加完后再禁用该trigger。

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

表上面的依赖对象如果有存储过程或触发器等，逻辑是否需要相应调整。

l是否涉及到同步。

同步中的表需要两地都要变更。

涉及到erosa的要更新一下数据字典。

Erosa需要重启一下。

l是否要通知其他关联的部门。

如DW,ASC或CRM等等。

有些表很多部门都用，需要沟通约定时间一起变更。

如果有同步方案，同步方案的变更也要考虑。

a）该表的数据量以及大小，以及数据变更量（按日/时/分/秒等）

b）该表的并发访问数，以及频率最高的几种sql的访问方式

2.执行过程

以表T1加字段col2为例。

T1的数据量非常大，访问频率很高。

a）在sysdba下开启trigger禁用客户端自动编译功能。

（可选）

AltertriggerSYS.ddl_trigger_for_databaseenable;

b）变更字段

以下加字段同编译失效对象连着执行。

编译时先编译trigger再编译存储过程或package等

@connzzzzzz/aaa

Altertablet1addcol2varchar2（20）;

Altertriggertrg_t1_searchcompile;

@connretl/rrr

Altertriggertrg_t1_synccompile;

@connbopsretl/bbb

Alterproceduresp_testcompile;

后面3个trigger的编译可以开三个窗口同时进行。

另开一个窗口，在admin用户下查看当前失效对象

c）老数据订正

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

Altertablet1modifycol2default‘Y’;

–数据订正存储过程

Createorreplaceproceduresp_dml0214

As

Cursorc1isselectrowedrid,id,col2fromt1wherecol2isnull;

V_cntnumber:

=0;

Begin

Forrec_c1inc1loop

V_cnt:

=v_cnt+1;

Updatet1setcol2=’Y’whererowed=rec_c1.ridandid=rec_c1.id;

Ifmod（v_cnt,500）=0then

Commit;

Dbms_application_info.set_client_info（‘sp_dml0214‘||v_cnt||‘rows!

’）;

Endif;

Endloop;

End;

/

Execsp_dml0214;

另开一个窗口，查看订正进度

colmachinefora19

colstatusfora12

colclient_Infofora50

selectsid,serial#,status,machine,client_Info,sql_hash_valuefromv$sessionwhereclient_Infoisnotnull;

d）订正完后加上NOTNULL属性

Altertablet1addcol2notnull;

e）（国际站可选）中美都变更，erosa重启

–更新erosa数据字典

./getDict.sh

–erosa重启

./erctlstop

./erctlstart

a）验证sys下的trigger已经禁用

Selectowner,trigger_name,statusfromdba_triggerswhereownerin（‘SYS’）andtrigger_name=’ddl_trigger_for_databaseenable’;

b）验证结构正确

Desczzzzzz.t1

c）验证无失效依赖对象

@dbcheck

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

前面已经阐述。

1.大表的新增字段不允许回滚。

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

2.普通表的新增字段如果是发布失败，后续还会有二次发布，也不考虑回滚。

或者只是将notnull属性拿掉，下次发布再加上。

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

06-加notnull字段

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

是否有默认值？

新增字段是NOTNULL的，则一定要有默认值，否则老应用的insert代码可能报错。

默认访问这些依赖对象的会话（如DML会话，或者应用调存储过程等）会尝试去自动编译这个依赖对象（9i所有会话都会尝试去编译，10g以后只有一个会话去主动编译，其他等待），此时很可能会出现大面积的librarycachepin。

表新增字段也会导致跟该表有关的SQL的游标失效，如果SQL的并发很高（查询SQL或者DMLSQL），失效后SQL会重新解析，此时也可能会