MYSQL网页编程之Oracle数据库的转移与晋级

”由于MySQL已经是一个运行了众多知名Web2.0网站的数据，包括Craigslist、Digg、Wikipedia和Google等，或许我们可以说每一个Web2.0公司实质上是一个使用MySQL数据库的公司。oracle|数据|数据库
公司比来要上shopfloor体系,需将数据库从东莞转移过去,以下就是转移的经由…

东莞体系情况:

OS:Windows2000AdvancedServer+SP4

Oracle9istandardEditionVersion:9.0.1.1.1

吴江体系情况:

HPML570CPU:IntelXeonMPCPU3.0GRAM:2GB

OS:Windows2000AdvancedServer+SP4

Oracle9iStandardEditonVersion:9.2.0.1.0.

步骤:

1.安装Oracle9i,选择只安装Software,不创立数据库,安装历程略.

2.创立相干目次:d:oracleadminwjsfmsdump

d:oracleadminwjsfmscdump

d:oracleadminwjsfmsudump

d:oracleadminwjsfmscreate

d:oracleadminwjsfmspfile

3.Copy数据文件到E:OradataWJSFMS目次下,一时文件可不COPY,文件清单以下:

SYSTEM01.DBFNDOTBS01.DBFCWMLITE01.DBFDRSYS01.DBFEXAMPLE01.DBFINDX01.DBFTOOLS01.DBF
USERS01.DBFCSFIS01.ORASFIS01.ORARSFIS02.ORAHSFIS01.ORACINDX01.ORARINDX01.ORA
RINDX02.ORAHINDX01.ORAUNDOTBS2.ORA

4.COPY把持文件G:ControlFileWJSFMS,H:ControlFileWJSFMS,I:ControlFileWJSFMS

5.COPYRedo文件到G:RedoLogWJSFMS,H:RedoLogWJSFMS,I:RedoLogWJSFMS

6.新建实例,此SID必需跟本来的SID一样…

C:Oradim–NEW–SIDWJSFMS–STARTMODEm

7.创立暗码文件

C:orapwdfile=d:oracleora92databasepwdwjsfms.orapassword=passwordentries=5

8.修正初始参数文件INITwjsfms.ora,如没有参数文件,则可用oracle自带的参数文件举行修正,白色部分为修正部分:

…….

control_files=("G:ControlFileWJSFMSCONTROL01.CTL","H:ControlFileWJSFMSCONTROL02.CTL","I:ControlFileWJSFMSCONTROL03.CTL")

……………………..

background_dump_dest=d:oracleadminWJSFMSdump

core_dump_dest=d:oracleadminWJSFMScdump

timed_statistics=TRUE

user_dump_dest=d:oracleadminWJSFMSudump

……………………….

log_archive_dest=f:oraclewjsfmsarchivelog



假如是依据oracle自带的参数文件举行修正的,则还需修正db_name,instance_name

9.激活oracle

c:qlplus/nolog

sql>conn/assysdba

sql>startuppfile=d:oracleora92databaseinitwjsfms.ora

此时提醒以下信息(因为晋级时未将相干信息spool出来,故只能将alert文件中的信息show出来作为参考):

ORA-00218:blocksize4096ofcontrolfiledoesnotmatchDB_BLOCK_SIZE(8192)

ORA-00202:controlfile:G:ControlFileWJSFMSCONTROL01.CTL

呈现此毛病的缘故原由是本来的database的db_block_size跟如今的初始化参数设的纷歧样,将初始化参数中的db_block_size=8192改成4096便可

sql>shutdownimmediate

sql>startuppfile=d:oracleora92databaseinitwjsfms.ora

ORA-01157:cannotidentify/lockdatafile1-seeDBWRtracefile

ORA-01110:datafile1:D:ORACLEORADATAWJSFMSYSTEM01.DBF

ORA-27041:unabletoopenfile

Sql>selecta.namefromv$datafilea,v$recover_filebwherea.file#=b.file#;

NAME

--------------------------------------------------------------------------------

D:ORACLEORADATAWJSFMSYSTEM01.DBF

D:ORACLEORADATAWJSFMSUNDOTBS01.DBF

D:ORACLEORADATAWJSFMSCWMLITE01.DBF

D:ORACLEORADATAWJSFMSDRSYS01.DBF

D:ORACLEORADATAWJSFMSEXAMPLE01.DBF

D:ORACLEORADATAWJSFMSINDX01.DBF

D:ORACLEORADATAWJSFMSTOOLS01.DBF

D:ORACLEORADATAWJSFMSUSERS01.DBF

D:ORACLEORADATAWJSFMSCSFIS01.ORA

D:ORACLEORADATAWJSFMSRSFIS01.ORA

D:ORACLEORADATAWJSFMSRSFIS02.ORA

D:ORACLEORADATAWJSFMSHSFIS01.ORA

D:ORACLEORADATAWJSFMSCINDX01.ORA

D:ORACLEORADATAWJSFMSRINDX01.ORA

D:ORACLEORADATAWJSFMSRINDX02.ORA

D:ORACLEORADATAWJSFMSHINDX01.ORA

D:ORACLEORADATAWJSFMSUNDOTBS2.ORA

实行alterdatabaserenamefile

Sql>alterdatabaserenamefileD:ORACLEORADATAWJSFMSYSTEM01.DBFtoE:OradataWJSFMSYSTEM01.DBF;

alterdatabaserenamefileD:ORACLEORADATAWJSFMSUNDOTBS01.DBFtoE:OradataWJSFMSUNDOTBS01.DBF;

alterdatabaserenamefileD:ORACLEORADATAWJSFMSCWMLITE01.DBFtoE:OradataWJSFMSCWMLITE01.DBF;

alterdatabaserenamefileD:ORACLEORADATAWJSFMSDRSYS01.DBFtoE:OradataWJSFMSDRSYS01.DBF;

alterdatabaserenamefileD:ORACLEORADATAWJSFMSEXAMPLE01.DBFtoE:OradataWJSFMSEXAMPLE01.DBF;

alterdatabaserenamefileD:ORACLEORADATAWJSFMSINDX01.DBFtoE:OradataWJSFMSINDX01.DBF;

alterdatabaserenamefileD:ORACLEORADATAWJSFMSTOOLS01.DBFtoE:OradataWJSFMSTOOLS01.DBF;

alterdatabaserenamefileD:ORACLEORADATAWJSFMSUSERS01.DBFtoE:OradataWJSFMSUSERS01.DBF;

alterdatabaserenamefileD:ORACLEORADATAWJSFMSCSFIS01.ORAtoE:OradataWJSFMSCSFIS01.ORA;

alterdatabaserenamefileD:ORACLEORADATAWJSFMSRSFIS01.ORAtoE:OradataWJSFMSRSFIS01.ORA;

alterdatabaserenamefileD:ORACLEORADATAWJSFMSRSFIS02.ORAtoE:OradataWJSFMSRSFIS02.ORA;

alterdatabaserenamefileD:ORACLEORADATAWJSFMSHSFIS01.ORAtoE:OradataWJSFMSHSFIS01.ORA;

alterdatabaserenamefileD:ORACLEORADATAWJSFMSCINDX01.ORAtoE:OradataWJSFMSCINDX01.ORA;

alterdatabaserenamefileD:ORACLEORADATAWJSFMSRINDX01.ORAtoE:OradataWJSFMSRINDX01.ORA;

alterdatabaserenamefileD:ORACLEORADATAWJSFMSRINDX02.ORAtoE:OradataWJSFMSRINDX02.ORA;

alterdatabaserenamefileD:ORACLEORADATAWJSFMSHINDX01.ORAtoE:OradataWJSFMSHINDX01.ORA;

alterdatabaserenamefileD:ORACLEORADATAWJSFMSUNDOTBS2.ORAtoE:OradataWJSFMSUNDOTBS2.ORA;



sql>alterdatabaseopen;

alterdatabaseopen

*

ERRORatline1:

ORA-00313:openfailedformembersofloggroup1ofthread1

ORA-00312:onlinelog1thread1:E:ORACLEORADATAISCO12REDO01.LOG



再次实行alterdatabaserenamefile

sql>alterdatabaserenamefileD:ORACLEORADATAWJSFMSREDO01.LOGtoG:RedoLogWJSFMSREDO01.LOG;

alterdatabaserenamefileD:ORACLEORADATAWJSFMSREDO02.LOGtoH:RedoLogWJSFMSREDO02.LOG;

alterdatabaserenamefileD:ORACLEORADATAWJSFMSREDO03.LOGtoI:RedoLogWJSFMSREDO03.LOG;



此时实行alterdatabaseopen

sql>alterdatabaseopen;

此时提醒:

Errorsinfiled:oracleadminwjsfmsudumpwjsfms_ora_2516.trc:

ORA-10827:databasemustbeopenedwithMIGRATEoption

然后instance主动shutdown

呈现这个缘故原由是因为数据库版本纷歧致酿成的,本来版本是9.0.1.1.1,如今是9.2.0.1.0.

10.数据库晋级:

检察oracleonlinedocument,决意手工晋级数据库

sql>startupmigratepfile=d:oracleora92databaseinitwjsfms.ora

SQL>SPOOLupgrade.log



Runuold_release.sql,whereold_releasereferstothereleaseyouhadinstalledpriortoupgrading.SeeTable3-2tochoosethecorrectscript.Eachscriptprovidesadirectupgradefromthereleasespecifiedinthe"OldRelease"column.The"OldRelease"isthereleasefromwhichyouareupgrading.



Torunascript,enterthefollowing:



SQL>@uold_release.sql

Table3-2UpgradeScripts

OldReleaseRunScript

7.3.4u0703040.sql

8.0.6u0800060.sql

8.1.7u0801070.sql

9.0.1u0900010.sql

SeeAlso:

"DetermineYourUpgradePathtotheNewRelease"iftheoldreleaseyouhadinstalledpriortoupgradingisnotlistedinTable3-2



Makesureyoufollowtheseguidelineswhenyourunthescript:



Youmustusetheversionofthescriptsuppliedwiththenewrelease9.2installation.

Youmustrunthescriptinthenewrelease9.2environment.

Youonlyneedtorunonescript,evenifyourupgradespansmorethanonerelease.Forexample,ifyouroldreleasewas8.1.7,thenyouonlyneedtorunu0801070.sql.

Thescriptyouruncreatesandalterscertaindictionarytables.Italsorunsthecatalog.sqlandcatproc.sqlscriptsthatcomewiththenew9.2release,whichcreatethesystemcatalogviewsandallthenecessarypackagesforusingPL/SQL.



Thefollowingcomponentsareupgradedbyrunningtheuold_release.sqlscript:



Oracle9iCatalogViews

Oracle9iPackagesandTypes



Displaythecontentsofthecomponentregistrytodeterminewhichcomponentsneedtobeupgraded:

SQL>SELECTcomp_name,version,status

FROMdba_registry;



Thefollowingisanexampleoftheoutputyouwillseewhenissuingthisquery:



COMP_NAMEVERSIONSTATUS

---------------------------------------------------------

Oracle9iCatalogViews9.2.0.1.0VALID

Oracle9iPackagesandTypes9.2.0.1.0VALID

JServerJAVAVirtualMachine9.0.1LOADED

JavaPackages9.0.1LOADED

OracleXDKforJava9.0.1LOADED

OracleText9.0.1LOADED

OracleWorkspaceManager9.0.1.0.0LOADED

OracleinterMedia9.0.0.0.0LOADED

OracleSpatial9.0.0.0.0BETALOADED

Ultrasearch9.0.1.0.0LOADED

OLAPCatalog9.0.1.0.0LOADED



11rowsselected.





Runthecmpdbmig.sqlscripttoupgradecomponentsthatcanbeupgradedwhileconnectedwithSYSDBAprivileges:

SQL>@cmpdbmig.sql



Thefollowingcomponentsareupgradedbyrunningthecmpdbmig.sqlscript:



JServerJAVAVirtualMachine

Oracle9iJavaPackages

OracleXDKforJava

MessagingGateway

Oracle9iRealApplicationClusters

OracleWorkspaceManager

OracleDataMining

OLAPCatalog

OLAPAnalyticWorkspace

OracleLabelSecurity



Displaythecontentsofthecomponentregistrytodeterminewhichcomponentswereupgraded:

SQL>SELECTcomp_name,version,status

FROMdba_registry;



Thefollowingisanexampleoftheoutputyouwillseewhenissuingthisquery:



COMP_NAMEVERSIONSTATUS

--------------------------------------------------------

Oracle9iCatalogViews9.2.0.1.0VALID

Oracle9iPackagesandTypes9.2.0.1.0VALID

JServerJAVAVirtualMachine9.2.0.1.0VALID

Oracle9iJavaPackages9.2.0.1.0VALID

OracleXDKforJava9.2.0.2.0UPGRADED

OracleText9.0.1LOADED

OracleWorkspaceManager9.2.0.1.0VALID

OracleinterMedia9.0.0.0.0LOADED

OracleSpatial9.0.0.0.0BETALOADED

Ultrasearch9.0.1.0.0LOADED

OLAPCatalog9.2.0.1.0VALID

OLAPAnalyticWorkspace9.2.0.1.0LOADED



12rowsselected.



Turnoffthespoolingofscriptresultstothelogfile:

SQL>SPOOLOFF



Then,checkthespoolfileandverifythatthepackagesandprocedurescompiledsuccessfully.YounamedthespoolfileinStep13;thesuggestednamewasupgrade.log.Correctanyproblemsyoufindinthisfileandreruntheappropriateupgradescriptsifnecessary.Youcanrerunanyofthescriptsdescribedinthischapterasmanytimesasnecessary.



Shutdownandrestarttheinstancetoreinitializethesystemparametersfornormaloperation.Therestartwillalsoperformrelease9.2initializationforJServerJAVAVirtualMachineandothercomponents.

SQL>SHUTDOWNIMMEDIATE



Executingthiscleanshutdownflushesallcaches,clearsbuffers,andperformsotherhousekeepingactivities.ThesemeasuresareanimportantfinalsteptoensuretheintegrityandconsistencyofthenewlyupgradedOracle9idatabase.



Also,ifyouencounteredamessagelistingobsoleteinitializationparameterswhenyoustartedthedatabaseinStep11,thenremovetheobsoleteinitializationparametersfromtheinitializationparameterfilenow.



Upgradeanyremainingcomponentsthatexistedinthepreviousdatabase.See"UpgradingSpecificComponents".



Thefollowingcomponentsrequireseparateupgradesteps:



OracleText

OracleUltraSearch

OracleSpatial

OracleinterMedia

OracleVisualInformationRetrieval



Runutlrp.sqltorecompileanyremainingstoredPL/SQLandJavacode.

SQL>@utlrp.sql



Verifythatallexpectedpackagesandclassesarevalid:



SQL>SELECTcount(*)FROMdba_objectsWHEREstatus=INVALID;

SQL>SELECTdestinctobject_nameFROMdba_objectsWHEREstatus=INVALID;

Verifythatallcomponentsarevalidandhavebeenupgradedtorelease9.2:

SQL>SELECTcomp_name,version,status

FROMdba_registry;

Yourdatabaseisnowupgradedtothenew9.2release.CompletetheproceduresdescribedinChapter4,"AfterUpgradingaDatabase".

11.创立SPFILE

sql>createspfilefrompfile;

12.检察报警日记文件,发明tempfile找不到:

Errorsinfiled:oracleadminwjsfmsdumpwjsfms_dbw0_2200.trc:

ORA-01186:file201failedverificationtests

ORA-01157:cannotidentify/lockdatafile201-seeDBWRtracefile

ORA-01110:datafile201:D:ORACLEORADATAWJSFMSTEMP01.DBF



WedNov1010:29:222004

File201notverifiedduetoerrorORA-01157

WedNov1010:29:222004

Errorsinfiled:oracleadminwjsfmsdumpwjsfms_dbw0_2200.trc:

ORA-01157:cannotidentify/lockdatafile202-seeDBWRtracefile

ORA-01110:datafile202:D:ORACLEORADATAWJSFMSTEMP2.ORA

ORA-27041:unabletoopenfile

Sql>selecta.name,b.file#,b.namefromts$a,v$tempfilebwherea.ts#=b.ts#;

NAMEFILE#name

------------------------------------------------------------------------------------------------------------

TEMP1D:ORACLEORADATAWJSFMSTEMP01.DBF


TEMP22D:ORACLEORADATAWJSFMSTEMP02.DBF

SQL>alterdatabasetempfile1dropincludingdatafiles;

SQL>alterdatabasetempfile2dropincludingdatafiles;

SQL>altertablespacetemp

addtempfilee:oradatawjsfms        emp01.dbfsize100mautoextendonnext1024kmaxsize1024m;

SQL>altertablespacetemp2

addtempfilee:oradatawjsfms        emp02.dbfsize100mautoextendonnext1024kmaxsize1024m;

至此数据库转移终了
DBaaS系统其实具有更大的市场机遇：像其他云服务一样，DBaaS意味着更短的销售周期，更少的启动费用，持续不断的收入，也意味着比之前更多的客户。

索引视k就有。但是2005对其效率作了一些改进但是schema.viewname的作用域真是太限制了它的应用面。还有一大堆的环境参数和种种限制都让人对它有点却步。

其中最有名的应该是row_number了。这个终于解决了用临时表生成序列号的历史，而且SQLServer2005的row_number比Oracle的更先进。因为它把Orderby集成到了一起，不用像Oracle那样还要用子查询进行封装。

Mirror可以算是SQLServer的Dataguard了。但是能不能被大伙用起来就不知道了。

是否碎片会引发效率问题？这都是需要进一步探讨的东西。varbinary(max)代替image也让SQLServer的字段类型更加简洁统一。

比如日志传送、比如集群。。。

大家注意一点。如下面的例子：

无法深入到数据库系统层面去了解和探究

只能告诉你，学好数据库语言和原理，多见识几种数据库软件，比一棵树上吊死要好。