阿里巴巴数据库操作手册.docx
《阿里巴巴数据库操作手册.docx》由会员分享,可在线阅读,更多相关《阿里巴巴数据库操作手册.docx(114页珍藏版)》请在冰豆网上搜索。
![阿里巴巴数据库操作手册.docx](https://file1.bdocx.com/fileroot1/2023-2/23/551fcb24-7125-42f1-b780-7a72413946ab/551fcb24-7125-42f1-b780-7a72413946ab1.gif)
阿里巴巴数据库操作手册
01-建表
一、目的
明确建表操作的风险及标准流程,最大限度避免建表操作带来的故障。
二、适用范围
l项目预发布新建表
l项目正式发布新建表
l不包含数据订正所建临时表
l不包含导数据所建的中间表
三、风险评估
l登录到错误的schema下,导致表建到错误的schema里,而应用无法访问。
l忽略了TABLESPACE参数,导致表建到了默认表空间,导致后续空间增长和维护困难。
l对于未来增量较快的表选择了一个空间规划不足的表空间,导致后续空间增长和维护困难。
l脚本末尾缺少分号,导致该表没有被创建上,而执行DDL的过程又不会报错。
l其他原因漏建了表,导致应用访问错误。
l所建的表定义(表名、字段名、字段定义、字段个数、字段顺序)跟测试环境不一致,导致应用访问错误。
l同步库没有及时创建相应的表,或者没有更新同步配置,导致同步及应用出问题。
四、操作流程
1.准备工作
a)在项目需求分析阶段,跟数据库设计人员一起明确新表所存放的数据库。
具体设计原则本文不繁述。
b)准备发布脚本时,检查tablespace定义,检查tablespace剩余空间,参考表空间自身负荷及新表的预期负荷,为每个新建的表选择合适的表空间,并在建表语句中添加tablespace的配置。
c)定发布计划时,跟开发接口人一起商定好建表操作的时间点。
如小需求没有发布计划评审,则必须在提交测试时(即表结构冻结时)即开始与开发接口人确定建表时间点。
如果发生计划外的发布建表需求,则要追究项目跟进的应用DBA沟通不力的责任。
d)以目前的认知,仅建表操作本身不会对数据库造成任何风险,故操作的时间点可以放宽:
在变更时间窗口内,均可以执行建表操作。
e)建表操作属于预授权变更,在做之前必须在ITIL中提交相应的变更申请。
2.执行过程
a)用应用账户登录数据库,SHOWUSER检查是否连接到正确的schema。
严禁使用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缓存:
有些表在应用层面做了缓存,制定订正计划的时候要考虑到订正后是否需要更新缓存。
四、操作流程
1.准备工作
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的姓名每个字的首字母,如果超长了,则将原表名缩减。
创建人有责任定期删除创建时间超过一个月以上的备份表。
2.执行过程
a)如果需要,按照备份脚本备份数据。
b)执行订正脚本。
查看订正进度,使用如下脚本:
selectclient_infofromv$sessionwhereclient_infoisnotnull;
–这个脚本必须配合前面描述的“进度报告”脚本执行。
c)检查undo消耗:
@undo
d)检查表空间消耗:
@tbs
e)检查归档空间
f)检查同步延时是否异常。
g)如果需要刷新应用缓存,在订正结束后通知应用刷新缓存。
3.验证方案
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不能超过这个值,如果有可能超过,需要跟开发确认。
四、操作流程
1.准备工作
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。
相差的值可以根据增长预期进行调整。
2.执行过程
a)标准新建脚本:
CREATESEQUENCEseq_tablenameSTARTWITH1CACHE100;
命名规范:
seq_tablename
默认不指定recycle和maxvalue。
b)标准重建脚本:
DROPSEQUENCEseq_tablename;
CREATESEQUENCEseq_tablenameSTARTWITH1CACHE100;
为了尽量缩短sequence不可用时间,这两个语句一起放在SecureCRT的chartWindow中一起执行。
c)标准修改cache脚本:
ALTERSEQUENCEseq_tablenameCACHE200;
d)标准赋权脚本:
GRANTSELECTONseq_tablenametousername;
3.验证方案
a)@dbcheck检查是否有失效对象
b)通知应用验证是否可以正常访问sequence
五、核心对象风险
高并发对象重建时短暂不可访问;
04_增加、删除唯一约束
一、目的
明确增删唯一约束操作的风险及标准流程,最大限度避免增删唯一约束操作带来的故障。
二、适用范围
l项目发布新建表的增删唯一约束
l对于旧表的增删唯一约束
三、风险评估
l对现有表新增唯一约束的操作,会堵塞包括查询在内的所有操作,风险很大,请谨慎使用,尽量在新建表时和开发讨论后增加。
l没有指定index,系统自动创建了index,删除约束时,自动创建的index同时删除了。
l在高峰期创建,导致大量的librarycachelock/pin的等待
l有同步的应用,先要在源端加,后在目标端加。
l表里有重复的数据,导致操作失败。
四、操作流程
1.准备工作
a)检查唯一建字段上是否存在index。
没有的话,需首先创建index(步骤详见增加index手册)。
b)检查唯一键上是否有重复数据,如有,需和开发讨论如何处理。
c)根据应用的需求和数据库的负载情况,确定操作的时间点。
对于数据量和访问量较大的表,变更时间点要谨慎选择.
d)检查字段上是否已经有了约束。
e)增加和删除唯一约束属于标准变更,需要开发在ITIL中提交事件单,应用dba提交变更单,有技术经理审批后执行。
f)对现有表新增约束,如果使用validate这个参数,会导致该表上连查询在内的所有操作都被锁住,风险非常大;如果使用novalidate参数,这个参数会导致数据字典不一致(及导致sqlldr的时候会导入重复数据)。
两者相比,故通常情况下用validate的风险更大,默认必须使用novalidate参数。
g)约束名与所依赖索引名一致。
2.执行过程
a)用应用账户登录数据库,SHOWUSER检查是否连接到正确的schema。
b)执行增加或删除的命令。
命令模板如下:
ALTERTABLE表名ADDCONSTRAINT表名_ukunique(字段名)USINGINDEX索引名NOVALIDATE;
ALTERTABLE表名DROPCONSTRAINT约束名KEEPINDEX;
如有otter同步,要注意执行顺序:
先在源数据库端加后在目标端增加。
c)查看过程若无报错,退出当前登录。
若有报错,找出报错的地方,修改确认再执行,直至全部执行通过,最后退出当前登录。
3.验证方案
a)常规检查:
@dbcheck
b)检查表定义是否与测试库一致:
execpkg_check.CompareObject(’user‘,’TABLE_NAME‘);
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等等。
有些表很多部门都用,需要沟通约定时间一起变更。
如果有同步方案,同步方案的变更也要考虑。
四、操作流程
1.准备工作
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
Altertriggertrg_t1_synccompile;
@connzzzzzz/aaa
Alterproceduresp_testcompile;
后面3个trigger的编译可以开三个窗口同时进行。
另开一个窗口,在admin用户下查看当前失效对象
@dbcheck
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;
Commit;
Dbms_application_info.set_client_info(‘sp_dml0214‘||v_cnt||‘rows!
’);
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
3.验证方案
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)。
三、风险评估
l新增字段的类型、长度(精度)是否合适
解决方法:
跟应用明确加字段和改字段的风险,确认新增字段类型正确、长度(精度)合适。
是否有默认值?
以及跟应用明确老数据是否要订正?
如何订正?
l新增字段的非空属性、默认值以及老数据问题。
新增字段是NOTNULL的,则一定要有默认值,否则老应用的insert代码可能报错。
表如果存在老数据,带上默认值的时候会导致oracle去订正老的数据行的新增列。
如果老数据非常多,表的并发访问高,很有可能导致大面积的阻塞等待以及产生大事务,甚至有可能导致undo耗尽。
倘若回滚,还会因为回滚产生的并发会话导致load飙升。
解决方法:
先不带notnull不带默认值加上列,再更改列默认值,再批量订正老数据,然后再加上notnull属性。
如果是大表,并且并发访问很高的表,则新增列不允许为NOTNULL,以简化后面变更步骤,降低风险!
l新增字段导致依赖对象失效、sql游标失效问题。
表的DML并发很高的时候,如果表上面还有依赖对象,新增字段会导致依赖对象失效。
默认访问这些依赖对象的会话(如DML会话,或者应用调存储过程等)会尝试去自动编译这个依赖对象(9i所有会话都会尝试去编译,10g以后只有一个会话去主动编译,其他等待),此时很可能会出现大面积的librarycachepin。
应用会话的连接时间会加长,进而导致出现后续应用报不能取得连接池错误。
应用服务器load由此飙升。
表新增字段也会导致跟该表有关的SQL的游标失效,如果SQL的并发很高(查询SQL或者DMLSQL),失效后SQL会重新解析,此时也可能会