仓酷云
标题:
MYSQL网页编程之oracle8i回滚段表空间呈现坏块的办理方...
[打印本页]
作者:
蒙在股里
时间:
2015-1-16 22:40
标题:
MYSQL网页编程之oracle8i回滚段表空间呈现坏块的办理方...
RDBMS并非没有局限性。它们难以扩展，需要大量的资源来配置和维护，比如时间、硬件和人力。同样，它们往往遵循峰值性能模型，这就要求系统按照峰值容量来配置可用性，而不考虑典型的数据使用情况。oracle|办理
明天早上刚到公司便接到网通客户的赞扬德律风，说网管数据库出成绩了，数据库有坏块，回滚段里的部分数据不克不及读取，必要协助办理。
我检察了一下swappALRT.log文件，发明有以下毛病：
TueSep2110:34:082004
ErrorsinfileE:oracleadminwappdumpwappSMON.TRC:
ORA-01578:ORACLEdatablockcorrupted(file#2,block#24497)
ORA-01110:datafile2:E:ORACLEORADATAWAPPRBS01.DBF
本来是回滚段表空间数据文件有坏块了。晓得了成绩的地点，立即办理，我已想好了思绪，就是新建一个回滚段表空间，把之前坏了的回滚段表空间drop失落，在新的回滚段表空间上建回滚段，所要建的回滚段和之前的一摸一样，让今后发生的回滚数据都写到新建的回滚段上。思绪明晰，立即入手下手举动了。
?起首停到listener,不同意有新的使用连到数据库上做操纵，然后down失落数据库，为了扫除失落已有的数据库会话毗连资本:
$lsnrctlstop
LSNRCTLforSolaris:Version8.1.7.3.0-Productionon21-SEP-200417:40:36
(c)Copyright1998OracleCorporation.?Allrightsreserved.
Connectingto(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ipasdb)(PORT=1521)))
Thecommandcompletedsuccessfully.
$sqlplusinternal/oracle
SQL*Plus:Release8.1.7.0.0-ProductiononTueSep2117:41:242004
(c)Copyright2000OracleCorporation.?Allrightsreserved.
Connectedto:
Oracle8iEnterpriseEditionRelease8.1.7.3.0-64bitProduction
WiththePartitioningoption
JServerRelease8.1.7.3.0-64bitProduction
SQL>shutdownimmediate
Databaseclosed.
Databasedismounted.
ORACLEinstanceshutdown.
SQL>startuprestrict(以受限形式启动数据库，为了避免其他用户上岸出去做相干操纵，这时候候只同意办理员上岸）
查找回滚段对应的表空间:
SQL>selecttablespace_name,statusfromdba_tablespaces;
TABLESPACE_NAME???????????????STATUS
---------------------------------------
SYSTEM????????????????????????ONLINE
TOOLS?????????????????????????ONLINE
RBS???????????????????????????ONLINE
TEMP??????????????????????????ONLINE
USERS?????????????????????????ONLINE
INDX??????????????????????????ONLINE
DRSYS?????????????????????????ONLINE
WACOS?????????????????????????ONLINE
NMS???????????????????????????ONLINE
TEST??????????????????????????ONLINE
FS????????????????????????????ONLINE
PERFSTAT??????????????????????ONLINE
12rowsselected.
回滚段表空间为RBS.
检察以后回滚段表空间里是不是有举动的事物:
SQL>SELECTs.username,t.xidusn,t.ubafil,t.ubablk,t.used_ublk?FROMv$sessions,v$transactiontWHEREs.saddr=t.ses_addr;
norowsselected.
没有举动事物，太好了，能够宁神的drop回滚段了，这恰是我想要的了局。
接上去查找回滚段存储参数信息:
SQL>coltablespace_nameformata10
SQL>colSEGMENT_NAMEformata12
SQL>setline120
SQL>selectSEGMENT_NAME,OWNER,TABLESPACE_NAME,initial_extent,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASEfromdba_rollback_segs;
SEGMENT_NAMEOWNER?TABLESPACEINITIAL_EXTENTNEXT_EXTENTMIN_EXTENTSMAX_EXTENTSPCT_INCREASE
---------------------------------------------------------------------------------------
SYSTEM??????SYS???SYSTEM?????????????57344??????57344??????????2????????505???????????0
RBS0????????PUBLICRBS01?????????????524288?????524288??????????8???????4096???????????0
RBS1????????PUBLICRBS01?????????????524288?????524288??????????8???????4096???????????0
RBS2????????PUBLICRBS01?????????????524288?????524288??????????8???????4096???????????0
RBS3????????PUBLICRBS01?????????????524288?????524288??????????8???????4096???????????0
RBS4????????PUBLICRBS01?????????????524288?????524288??????????8???????4096???????????0
RBS5????????PUBLICRBS01?????????????524288?????524288??????????8???????4096???????????0
RBS6????????PUBLICRBS01?????????????524288?????524288??????????8???????4096???????????0
RBS7????????PUBLICRBS01?????????????524288?????524288??????????8???????4096???????????0
RBS8????????PUBLICRBS01?????????????524288?????524288??????????8???????4096???????????0
RBS9????????PUBLICRBS01?????????????524288?????524288??????????8???????4096???????????0
RBS10???????PUBLICRBS01?????????????524288?????524288??????????8???????4096???????????0
RBS11???????PUBLICRBS01?????????????524288?????524288??????????8???????4096???????????0
RBS12???????PUBLICRBS01?????????????524288?????524288??????????8???????4096???????????0
RBS13???????PUBLICRBS01?????????????524288?????524288??????????8???????4096???????????0
RBS14???????PUBLICRBS01?????????????524288?????524288??????????8???????4096???????????0
RBS15???????PUBLICRBS01?????????????524288?????524288??????????8???????4096???????????0
RBS16???????PUBLICRBS01?????????????524288?????524288??????????8???????4096???????????0
RBS17???????PUBLICRBS01?????????????524288?????524288??????????8???????4096???????????0
RBS18???????PUBLICRBS01?????????????524288?????524288??????????8???????4096???????????0
RBS19???????PUBLICRBS01?????????????524288?????524288??????????8???????4096???????????0
RBS20???????PUBLICRBS01?????????????524288?????524288??????????8???????4096???????????0
RBS21???????PUBLICRBS01?????????????524288?????524288??????????8???????4096???????????0
RBS22???????PUBLICRBS01?????????????524288?????524288??????????8???????4096???????????0
RBS23???????PUBLICRBS01?????????????524288?????524288??????????8???????4096???????????0
RBS24???????PUBLICRBS01?????????????524288?????524288??????????8???????4096???????????0
RBS26???????PUBLICRBS01?????????????524288?????524288??????????8???????4096???????????0
RBS27???????PUBLICRBS01?????????????524288?????524288??????????8???????4096???????????0
RBS28???????PUBLICRBS01?????????????524288?????524288??????????8???????4096???????????0
RBS25???????PUBLICRBS01?????????????524288?????524288??????????8???????4096???????????0
APPRBS??????PUBLICRBS01????????????2097152???10485760?????????50??????32765???????????0
31rowsselected.
把initial_extent,next_extent,min_extents,max_extents,pct_increase的值都纪录上去，留做今后创立新的回滚段利用。
创立LMT办理体例的回滚段表空间(我的数据库是oracle817):
SQL>createtablespacerbs01datafile/opt/oracle/db02/oradata/ORCL/rbs01.dbfsize1024M?autoextendonnext1Mmaxsizeunlimitedextentmanagementlocal;
Tablespacecreated.
先在该表空间下创建一个回滚段rbs31做一个测试:
SQL>createpublicrollbacksegmentRBS31tablespacerbs01storage(initial524288next524288MINEXTENTS8MAXEXTENTS4096OPTIMAL4194304);
createpublicrollbacksegmentRBS31tablespacerbs01storage(initial524288next524288MINEXTENTS8MAXEXTENTS4096OPTIMAL4194304)
*
ERRORatline1:
ORA-25151:RollbackSegmentcannotbecreatedinthistablespace
堕落了，竟然没有建乐成,shit.
查了一下metalink发明关于oracle8i来说在LMT体例办理的表空间下不克不及创立回滚段，但9i办理了该成绩。
metalink上的注释:
Explanation
-----------
Rollbacksegmentscannotbecreatedinlocallymanagedtablespaces(anewfeatureinOracle8.1)withallocationtypeofAUTOALLOCATE.TheymustbecreatedinlocallymanagedtablespaceswithallocationtypeofUNIFORMorindictionarymanagedtablespaces.
NOTE:ThisrestrictionhasbeenliftedinOracle9.
接上去drop方才创建的rbs01表空间,从头创建rbs01表空间:
SQL>createtablespacerbs01datafile/opt/oracle/db02/oradata/ORCL/rbs01.dbfsize1024M
?autoextendonnext1Mmaxsizeunlimited;
Tablespacecreated.
SQL>selectEXTENT_MANAGEMENTfromdba_tablespaceswheretablespace_name=RBS01;
EXTENT_MAN
----------
DICTIONARY
这回表空间不是LMT的，是DMT的，呵呵！
上面才是真正入手下手要做的事情，总之两个字，仔细，由于是临盆库，不敢纰漏。
SQL>setfeedbackoff
SQL>setpages0
SQL>selectalterrollbacksegment||segment_name||offline;?fromdba_rollback_segs;
做一个剧本把除system回滚段之外的回滚段都offline失落，省的一个一个敲，剧本了局以下:
alterrollbacksegmentRBS0offline;
alterrollbacksegmentRBS1offline;
alterrollbacksegmentRBS2offline;
alterrollbacksegmentRBS3offline;
alterrollbacksegmentRBS4offline;
alterrollbacksegmentRBS5offline;
alterrollbacksegmentRBS6offline;
alterrollbacksegmentRBS7offline;
alterrollbacksegmentRBS8offline;
alterrollbacksegmentRBS9offline;
alterrollbacksegmentRBS10offline;
alterrollbacksegmentRBS11offline;
alterrollbacksegmentRBS12offline;
alterrollbacksegmentRBS13offline;
alterrollbacksegmentRBS14offline;
alterrollbacksegmentRBS15offline;
alterrollbacksegmentRBS16offline;
alterrollbacksegmentRBS17offline;
alterrollbacksegmentRBS18offline;
alterrollbacksegmentRBS19offline;
alterrollbacksegmentRBS20offline;
alterrollbacksegmentRBS21offline;
alterrollbacksegmentRBS22offline;
alterrollbacksegmentRBS23offline;
alterrollbacksegmentRBS24offline;
alterrollbacksegmentRBS25offline;
alterrollbacksegmentRBS26offline;
alterrollbacksegmentRBS27offline;
alterrollbacksegmentRBS28offline;
alterrollbacksegmentAPPRBSoffline;
然后做个drop回滚段的剧本:
SQL>?selectdroprollbacksegment||segment_name||;fromdba_rollback_segs;
droprollbacksegmentRBS0;
droprollbacksegmentRBS1;
droprollbacksegmentRBS2;
droprollbacksegmentRBS3;
droprollbacksegmentRBS4;
droprollbacksegmentRBS5;
droprollbacksegmentRBS6;
droprollbacksegmentRBS7;
droprollbacksegmentRBS8;
droprollbacksegmentRBS9;
droprollbacksegmentRBS10;
droprollbacksegmentRBS11;
droprollbacksegmentRBS12;
droprollbacksegmentRBS13;
droprollbacksegmentRBS14;
droprollbacksegmentRBS15;
droprollbacksegmentRBS16;
droprollbacksegmentRBS17;
droprollbacksegmentRBS18;
droprollbacksegmentRBS19;
droprollbacksegmentRBS20;
droprollbacksegmentRBS21;
droprollbacksegmentRBS22;
droprollbacksegmentRBS23;
droprollbacksegmentRBS24;
droprollbacksegmentRBS25;
droprollbacksegmentRBS26;
droprollbacksegmentRBS27;
droprollbacksegmentRBS28;
droprollbacksegmentAPPRBS;
剧本做好了，别忘了实行。
实行完后入手下手在新的回滚段表空间下建回滚段，存储参数和本来坚持分歧：
SQL>select?createpublicrollbacksegment||segment_name||tablespacerbs01storage(initial524288next524288MINEXTENTS8MAXEXTENTS4096OPTIMAL4194304);fromdba_rollback_segs;
也是做了个剧本，免的一个一个敲！
上面的年夜回滚段要独自建，总之，体系内里最好要有一个年夜的回滚段，有年夜事物的时分就派上用处了。
SQL>createpublicrollbacksegmentAPPRBStablespacerbs01storage(initial2097152next10485760MINEXTENTS50MAXEXTENTS32765);
Rollbacksegmentcreated.
检察新建的回滚段形态：
SQL>selectsegment_name,owner,status,tablespace_namefromdba_rollback_segs;
SYSTEM??????????????SYS???ONLINE??????????SYSTEM
RBS0????????????????PUBLICOFFLINE?????????RBS01
RBS1????????????????PUBLICOFFLINE?????????RBS01
RBS2????????????????PUBLICOFFLINE?????????RBS01
RBS3????????????????PUBLICOFFLINE?????????RBS01
RBS4????????????????PUBLICOFFLINE?????????RBS01
RBS5????????????????PUBLICOFFLINE?????????RBS01
RBS6????????????????PUBLICOFFLINE?????????RBS01
RBS7????????????????PUBLICOFFLINE?????????RBS01
RBS8????????????????PUBLICOFFLINE?????????RBS01
RBS10???????????????PUBLICOFFLINE?????????RBS01
RBS11???????????????PUBLICOFFLINE?????????RBS01
RBS12???????????????PUBLICOFFLINE?????????RBS01
RBS13???????????????PUBLICOFFLINE?????????RBS01
RBS14???????????????PUBLICOFFLINE?????????RBS01
RBS15???????????????PUBLICOFFLINE?????????RBS01
RBS16???????????????PUBLICOFFLINE?????????RBS01
RBS17???????????????PUBLICOFFLINE?????????RBS01
RBS18???????????????PUBLICOFFLINE?????????RBS01
RBS19???????????????PUBLICOFFLINE?????????RBS01
RBS20???????????????PUBLICOFFLINE?????????RBS01
RBS21???????????????PUBLICOFFLINE?????????RBS01
RBS22???????????????PUBLICOFFLINE?????????RBS01
RBS23???????????????PUBLICOFFLINE?????????RBS01
RBS24???????????????PUBLICOFFLINE?????????RBS01
RBS26???????????????PUBLICOFFLINE?????????RBS01
RBS27???????????????PUBLICOFFLINE?????????RBS01
RBS28???????????????PUBLICOFFLINE?????????RBS01
RBS25???????????????PUBLICOFFLINE?????????RBS01
APPRBS??????????????PUBLICOFFLINE?????????RBS01
30rowsselected.
除system,都是offline形态。
持续做剧本让除system外的回滚段online：
SQL>selectalterrollbacksegment||segment_name||online;?fromdba_rollback_segs;
alterrollbacksegmentRBS0online;
alterrollbacksegmentRBS1online;
alterrollbacksegmentRBS2online;
alterrollbacksegmentRBS3online;
alterrollbacksegmentRBS4online;
alterrollbacksegmentRBS5online;
alterrollbacksegmentRBS6online;
alterrollbacksegmentRBS7online;
alterrollbacksegmentRBS8online;
alterrollbacksegmentRBS9online;
alterrollbacksegmentRBS10online;
alterrollbacksegmentRBS11online;
alterrollbacksegmentRBS12online;
alterrollbacksegmentRBS13online;
alterrollbacksegmentRBS14online;
alterrollbacksegmentRBS15online;
alterrollbacksegmentRBS16online;
alterrollbacksegmentRBS17online;
alterrollbacksegmentRBS18online;
alterrollbacksegmentRBS19online;
alterrollbacksegmentRBS20online;
alterrollbacksegmentRBS21online;
alterrollbacksegmentRBS22online;
alterrollbacksegmentRBS23online;
alterrollbacksegmentRBS24online;
alterrollbacksegmentRBS26online;
alterrollbacksegmentRBS27online;
alterrollbacksegmentRBS28online;
alterrollbacksegmentRBS25online;
alterrollbacksegmentAPPRBSonline;
实行以上剧本后，删除本来的undo表空间RBS：
SQL>droptablespacerbsincludingcontents;
Tablespacedropped.
做到这里即完成了所请求的事情，好了，剩下的就留做数据测试了，出工，今天等数据库测试了局。
解决方案提供商应记住DBaaS通常仅仅是解决方案的一部分。客户之所以与他们的解决方案提供商协同工作，不仅是因为他们出售的产品，而且还因为他们所提供的服务。
作者:
仓酷云
时间:
2015-1-19 21:12
还不是性能有问题！否则面向对象的数据库早就实现了！建议使用CLR的地方一般是和应用的复杂程度或操作系统环境有很高的耦合度的场景。如你想构建复杂的算法，并且用到了大量的指针和高级数据模型。
作者:
乐观
时间:
2015-1-26 21:39
作了些试验，发现使用CLR的存储过程或函数在达到一定的阀值的时候，系统性能会呈指数级下滑！这是非常危险的！只使用几个可能没有问题，当一旦大规模使用会造成严重的系统性能问题！
作者:
小妖女
时间:
2015-2-4 20:59
having子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having条件显示特定的组，也可以使用多个分组标准进行分组。
作者:
飘飘悠悠
时间:
2015-2-10 10:29
是要和操作系统进行Socket通讯的场景。否则建议慎重！
作者:
金色的骷髅
时间:
2015-3-1 09:00
如果我们从集合论（关系代数）的角度来看，一张数据库的表就是一组数据元的关系，而每个SQL语句会改变一种或数种关系，从而产生出新的数据元的关系（即产生新的表）。
作者:
爱飞
时间:
2015-3-10 13:00
多加的系统视图和实时系统信息这些东西对DBA挑优非常有帮助，但是感觉粒度还是不太细。
作者:
只想知道
时间:
2015-3-17 08:04
需要注意的一点，也是我使用过程中发现的一个问题。在建立function->schema->table后，如果在现有的分区表上建立没有显式声明的聚集索引时，分区表会自动变为非分区表。这一点很让我纳闷。
欢迎光临 仓酷云 (http://ckuyun.com/)
Powered by Discuz! X3.2