MSSQL教程之Transportable Tablespaces

使为了数据安全，我们搭建了主从。但实时主从备份只能防止硬件问题，比如主库的硬盘损坏。但对于误操作，则无能为力。比如在主库误删一张表，或者一个update语句没有指定where条件，导致全表被更新。FROM:http://www.idevelopment.info/data/Oracle/DBA_tips/Oracle8i_New_Features/ORA8i_17.shtml
TransportableTablespaces
byJeffHunter,Sr.DatabaseAdministrator

Contents
OverviewIntroductiontoTransportableTablespacesUsingTransportableTablespaces

OverviewOraclesTransportableTablespaceisoneofthosemuchawaitedfeaturesthatwasintroducedinOracle8i(8.1.5)andiscommonlyusedinDataWarehouses(DW).Usingtransportabletablespacesismuchfasterthanusingotherutilitieslikeexport/import,SQL*Pluscopytables,orbackupandrecoveryoptionstocopydatafromonedatabasetoanother.
Thisarticleprovidesabriefintroductionintoconfiguringandusingtransportabletablespaces.
IntroductiontoTransportableTablespacesBeforecoveringthedetailsofhowtosetupandusetransportabletablespaces,letsfirstdiscusssomeoftheterminologyandlimitationstoprovideuswithanintroduction.
Theuseoftransportabletablespacesaremuchfasterthanusingexport/import,SQL*Pluscopytables,orbackupandrecoveryoptionstocopydatafromonedatabasetoanother.

Atransportabletablespacesetisdefinedastwocomponents:
Allofthedatafilesthatmakeupthetablespacesthatwillbemoved.
AND

Anexportthatcontainsthedatadictionaryinformationaboutthosetablespaces.

COMPATIBLEmustbesetinboththesourceandtargetdatabasetoatleast8.1.

WhentransportingatablespacefromanOLTPsystemtoadatawarehouseusingtheExport/Importutility,youwillmostlikelyNOTneedtotransportTRIGGERandCONSTRAINTinformationthatisassociatedwiththetablesinthetablespaceyouareexporting.Thatis,youwillsettheTRIGGERSandCONSTRAINTSExportutilityparametersequalto"N".

Thedatainadatawarehouseisinsertedandalteredunderverycontrolledcircumstancesanddoesnotrequirethesameusageofconstraintsandtriggersasatypicaloperationalsystemdoes.

ItiscommonandrecommendedthoughthatyouusetheGRANTSoptionbysettingittoY.

TheTRIGGERSoptionisnewinOracle8iforusewiththeexportcommand.Itisusedtocontrolwhethertriggerinformation,associatedwiththetablesinatablespace,areincludedinthetablespacetransport.
LimitationsofTransportableTablespaces:
Thetransportablesetmustbeself-contained.

BoththesourceandtargetdatabasemustberunningOracle8.1orhigherrelease.

Thetwodatabasesdonothavetobeonthesamerelease

Thesourceandtargetdatabasesmustbeonthesametypeofhardwareandoperating-systemplatform.

Thesourceandtargetdatabasesmusthavethesamedatabaseblocksize.

Thesourceandtargetdatabasesmusthavethesamecharacterset.

Atablespacewiththesamenamemustnotalreadyexistinthetargetdatabase.

Materializedviews,function-basedindexes,scopedREFs,8.0compatibleadvancedqueueswithmultiple-recipients,anddomainindexescantbetransportedinthismanner.(AsofOracle8i)

Userswithtablesintheexportedtablespaceshouldexistinthetargetdatabasepriortoinitiatingtheimport.Createtheuserreportedbytheerrormessage.
Explanation:Themetadataexportedfromthetargetdatabasedoesnotcontainenoughinformationtocreatetheuserinthetargetdatabase.Thereasonisthat,ifthemetadatacontainedtheuserdetails,itmightoverwritetheprivilegesofanexistinguserinthetargetdatabase.
(i.e.Iftheuserbythesamenamealreadyexistsinthetargetdatabase)
Bynotmaintainingtheuserdetails,wepreservethesecurityofthedatabase.
UsingTransportableTablespacesInthissection,wefinallygettoseehowtousetransportabletablespaces.Hereisanoverviewofthestepswewillperforminthissection:
Verifythatthesetofsourcetablespacesareself-containedGenerateatransportabletablespaceset.TransportthetablespacesetImportthetablespacessetintothetargetdatabase.

Inthisexample,wewillbetransportingthetablespaces,"FACT1,FACT2,andFACT_IDX"fromadatabasenamedDWDBtoREPORTDB.Theuserthatownsthesetableswillbe"DW"andpassword"DW".

VerifySelf-ContainedStatuswiththeDBMS_TTSPackageToverifythatalltablespacestotransportareself-contained,wecanusetheTRANSPORT_SET_CHECKprocedurewithintheDBMS_TTSPL/SQLPackage.Thefirstparametertothisprocedureisalistofthetablespacestotransport.Keepinmindthatallindexesforatable,partitions,andLOBcolumnsegmentsinthetablespacemustalsoresideinthetablespaceset.Thesecondparametertothisprocedureisabooleanvaluethatindicateswhetherornottocheckforreferentialintegrity.

SQL>connectsys/change_on_install@dwdbassysdbaSQL>execDBMS_TTS.TRANSPORT_SET_CHECK(fact1,fact2,TRUE);SQL>SELECT*FROMTRANSPORT_SET_VIOLATIONS;VIOLATIONS--------------------------------------------------------------------------------IndexDW.DEPT_PKintablespaceFACT_IDXenforcesprimaryconstriantsoftableDW.DEPTintablespaceFACT1IndexDW.EMP_PKintablespaceFACT_IDXenforcesprimaryconstriantsoftableDW.EMPintablespaceFACT1
OOOPS!Aswecanseefromtheaboveexample,Iforgottoincludealltablespacesthatwillmakethisself-contained.Inthisexample,IforgottoincludetheFACT_IDXtablespace.Letscorrectthat:
SQL>execDBMS_TTS.TRANSPORT_SET_CHECK(fact1,fact2,fact_idx,TRUE);SQL>SELECT*FROMTRANSPORT_SET_VIOLATIONS;norowsselected

GenerateaTransportableTablespaceSetTogenerateaTransportableTablespaceSet,youwillneedtoperformthefollowing:
PlacealltablespacewithinthetablespacesetinREADONLYmode.UseExporttogathertablespacedata-dictionaryinformation.Copydatafilesandtheexportdumpfromthesourcelocationtothetargetlocation.PlacealltablespacewithinthetablespacesetbacktoREAD/WRITE.
%sqlplus"sys/change_on_install@dwdbassysdba"SQL>ALTERTABLESPACEfact1READONLY;SQL>ALTERTABLESPACEfact2READONLY;SQL>ALTERTABLESPACEfact_idxREADONLY;SQL>exit%expuserid="sys/change_on_install@dwdbassysdba"transport_tablespace=ytablespaces=fact1,fact2,fact_idxtriggers=yconstraints=ygrants=yfile=fact_dw.dmp%cp/u10/app/oradata/DWDB/fact1_01.dbf/u10/app/oradata/REPORTDB/fact1_01.dbf%cp/u10/app/oradata/DWDB/fact2_01.dbf/u10/app/oradata/REPORTDB/fact2_01.dbf%cp/u09/app/oradata/DWDB/fact_idx01.dbf/u09/app/oradata/REPORTDB/fact_idx01.dbf%sqlplus"sys/change_on_install@dwdbassysdba"SQL>ALTERTABLESPACEfact1READWRITE;SQL>ALTERTABLESPACEfact2READWRITE;SQL>ALTERTABLESPACEfact_idxREADWRITE;SQL>exit

TransporttheTablespaceSetToactuallytransportthetablespace,thisisnothingmorethancopying(orFTPing)alltablespacesetdatafilestobeputintheirproperlocationonthetargetdatabase.Inthesectionprevioustothis,wedidthatwiththecpcommandinUNIX.
Insomecasesthiswouldbenecessaryifthefileswherecopiedofftoastagingareainthepreviousstep.

ImporttheTablespaceSetBeforeactuallyimportingthetablespace(s)intothetargetdatabase,youwillneedtoensurethatallusersthatownsegmentsintheimportedtablespacesexist.Forthisexample,theonlyuserthatownssegmentsintheexportedtablespacesisDW.Iwillcreatethisuser:
%sqlplus"sys/change_on_install@reportdbassysdba"SQL>createuserdwidentifiedbydwdefaulttablespaceusers;SQL>grantdba,resource,connecttodw;SQL>exit

WenowusetheImportutilitytobringthetablespacesetsdata-dictionaryinformationintothetargetdatabase.
ThetworequiredparametersareTRANSPORT_TABLESPACE=YandDATAFILES=...asinthefollowingexample:
%impuserid="sys/change_on_install@reportdbassysdba"transport_tablespace=ydatafiles=/u10/app/oradata/REPORTDB/fact1_01.dbf,/u10/app/oradata/REPORTDB/fact2_01.dbf,/u09/app/oradata/REPORTDB/fact_idx01.dbffile=fact_dw.dmp

FinalCleanupWhenthetablespacesaresuccessfullyimportedintothetargetdatabase,theyareinREADONLYmode.IfyouintendtousethetablespacesforREADWRITE,youwillneedtomanuallyalterthem:
%sqlplus"sys/change_on_install@reportdbassysdba"SQL>ALTERTABLESPACEfact1READWRITE;SQL>ALTERTABLESPACEfact2READWRITE;SQL>ALTERTABLESPACEfact_idxREADWRITE;SQL>exitPageCount:261

这章描述如何检查和处理在MySQL数据库中的数据损坏。如果你的表损坏很多，你应该尝试找出其原因!见G.1调试一个MySQL服务器。

你觉得我的非分区索引无法对起子分区，你可以提醒我一下呀！没有任何的提醒，直接就变成了非分区表。不知道这算不算一个bug。大家也可以试试。

微软对CLR作了大篇幅的宣传，这是因为数据库产品终于融入.net体系中。最开始我们也是狂喜，感觉对象数据库的一些概念可以实现了。

一直以来个人感觉SQLServer的优化器要比Oracle的聪明。SQL2005的更是比2k聪明了不少。（有次作试验发现有的语句在200万级时还比50万级的相同语句要快show_text的一些提示没有找到解释。一直在奇怪。）

而写到本地,我又考虑到效率问题.大家来讨论讨论吧,分数不打紧,就给10分,十全十美,没啥对错,各抒己见,但是要有说服力的哦~

外键的级联更能扩展可能大部分的同行在设计OLTP系统的时候都不愿意建立外键，都是通过程序来控制父子数据的完整性。

varchar(max)\\\\nvarchar(max)类型的引入大大的提高了编程的效率，可以使用字符串函数对CLOB类型进行操作，这是一个亮点。

可能有的朋友会抱怨集成的orderby，其实如果使用ranking函数,Orderby是少不了的。如果担心Orderby会影响效率，可以为orderby的字段建立聚集索引，查询计划会忽略orderby操作（因为本来就是排序的嘛）。

对一张百万级别的表建游标，同时又没有什么过滤条件，取得游标效率是如果直接SQL查询百万条数据；如果再对每条记录做处理，耗时将更长。