MSSQL网站制作之Dataguard设置Step by Step

优化的SQL查询算法，有效地提高查询速度link:http://www.eygle.com/ha/dataguard-step-by-step.htm1.主节点备份并天生备用数据库把持文件
设置主节点为forceLogging形式(为了双向切换,倡议备用节点也设置为forcelogging形式)
ALTERDATABASEFORCELOGGING;

设置主节点为回档形式

上岸主节点,举行数据库备份，并天生备用数据库把持文件




Lastlogin:MonAug916:46:472004from172.16.32.65[root@standbyroot]#su-oracle[oracle@standbyoracle]$sqlplus"/assysdba"SQL*Plus:Release9.2.0.4.0-ProductiononMonAug1610:16:182004Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.Connectedtoanidleinstance.SQL>startupORACLEinstancestarted.TotalSystemGlobalArea135337420bytesFixedSize452044bytesVariableSize109051904bytesDatabaseBuffers25165824bytesRedoBuffers667648bytesDatabasemounted.Databaseopened.SQL>selectnamefromv$datafile;NAME------------------------------------------------------------/opt/oracle/oradata/primary/system01.dbf/opt/oracle/oradata/primary/undotbs01.dbf/opt/oracle/oradata/primary/users01.dbfSQL>shutdownimmediateDatabaseclosed.Databasedismounted.ORACLEinstanceshutdown.SQL>exitDisconnectedfromOracle9iEnterpriseEditionRelease9.2.0.4.0-ProductionWiththePartitioningoptionJServerRelease9.2.0.4.0-Production[oracle@standbyoracle]$lsadmindictionary.orajreoradataoraInventoryouiproductsoft[oracle@standbyoracle]$tar-cvforadata.taroradataoradata/oradata/primary/oradata/primary/archive/oradata/primary/control01.ctloradata/primary/control02.ctloradata/primary/control03.ctloradata/primary/redo01.logoradata/primary/redo02.logoradata/primary/redo03.logoradata/primary/system01.dbforadata/primary/undotbs01.dbforadata/primary/temp01.dbforadata/primary/users01.dbf[oracle@standbyoracle]$ls-l*.tar-rw-r--r--1oracledba576512000Aug1610:22oradata.tar[oracle@standbyoracle]$iduid=800(oracle)gid=800(dba)groups=800(dba)[oracle@standbyoracle]$hostnamestandby[oracle@standbyoracle]$sqlplus"/assysdba"SQL*Plus:Release9.2.0.4.0-ProductiononMonAug1610:27:542004Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.Connectedtoanidleinstance.SQL>startupORACLEinstancestarted.TotalSystemGlobalArea135337420bytesFixedSize452044bytesVariableSize109051904bytesDatabaseBuffers25165824bytesRedoBuffers667648bytesDatabasemounted.Databaseopened.SQL>archiveloglist;DatabaselogmodeArchiveModeAutomaticarchivalEnabledArchivedestination/opt/oracle/oradata/primary/archiveOldestonlinelogsequence88Nextlogsequencetoarchive90Currentlogsequence90SQL>alterdatabasecreatestandbycontrolfileas/opt/oracle/stdcotrl.ctl;Databasealtered.SQL>!ls[oracle@standbyoracle]$lsadmindictionary.orajreoradataoradata.taroraInventoryouiproductsoftstdcotrl.ctl








2.从主节点创立pfile文件



SQL>createpfilefromspfile;

Filecreated.

SQL>!

[oracle@standbyoracle]$cd$ORACLE_HOME/dbs
[oracle@standbydbs]$ls
initdw.orainit.orainitprimary.oralkPRIMARYorapwprimaryspfileprimary.orasqlnet.log







3.上岸备用节点,ftp取得数据库文件、备用把持文件及参数文件






Lastlogin:MonAug1608:47:582004from172.16.32.65[root@eygleroot]#su-oracle[oracle@eygleoracle]$lsadmindocjreoradataoraInventoryouiproduct[oracle@eygleoracle]$df-kFilesystem1K-blocksUsedAvailableUse%Mountedon/dev/sda151548523360600153239669%//dev/sda7101089257447012627%/home/dev/sda541270762686152123128069%/optnone51529605152960%/dev/shm/dev/sda241271082218172169928857%/usr/dev/sda6206350410774418509406%/var[oracle@eygleoracle]$ftp172.16.33.58Connectedto172.16.33.58(172.16.33.58).220(vsFTPd1.2.0)Name(172.16.33.58:root):oracle331Pleasespecifythepassword.Password:230Loginsuccessful.ftp>ls227EnteringPassiveMode(172,16,33,58,222,252)150Herecomesthedirectorylisting.drwxr-xr-x38008004096Jun3007:02admin-rw-r--r--18008005422222Jul1311:58dictionary.ora-rw-r--r--18008001165Aug1602:51initprimary.oradrwxrwxr-x48008004096Jun3006:29jredrwxrwxr-x128008004096Jun3006:44oraInventorydrwxr-xr-x38008004096Jul0106:15oradata-rw-r--r--1800800576512000Aug1602:22oradata.tardrwxrwxr-x68008004096Jun3006:29ouidrwxr-xr-x38008004096Jun3005:18productdrwxr-xr-x68008004096Jun3004:24soft-rw-r-----18008001662976Aug1602:37stdcotrl.ctl226DirectorysendOK.ftp>bin200SwitchingtoBinarymode.ftp>mgetoradata.tarmgetoradata.tar?y227EnteringPassiveMode(172,16,33,58,238,132)150OpeningBINARYmodedataconnectionfororadata.tar(576512000bytes).226FilesendOK.576512000bytesreceivedin49.2secs(1.1e+04Kbytes/sec)ftp>mget*.ctlmgetstdcotrl.ctl?y227EnteringPassiveMode(172,16,33,58,73,35)150OpeningBINARYmodedataconnectionforstdcotrl.ctl(1662976bytes).226FilesendOK.1662976bytesreceivedin0.14secs(1.2e+04Kbytes/sec)ftp>mgetinitprimary.oramgetinitprimary.ora?y227EnteringPassiveMode(172,16,33,58,194,239)150OpeningBINARYmodedataconnectionforinitprimary.ora(1165bytes).226FilesendOK.1165bytesreceivedin0.000325secs(3.5e+03Kbytes/sec)ftp>bye221Goodbye.[oracle@eygleoracle]$lsadmindocinitprimary.orajreoradataoradata.taroraInventoryouiproductstdcotrl.ctl[oracle@eygleoracle]$mvinitprimary.ora$ORACLE_HOME/dbs[oracle@eygleoracle]$cd$ORACLE_HOME/dbs[oracle@eygledbs]$lsa.sqlinitdw.orainit.orainitprimary.orainitrac1.orainitrac2.orainitrac.oraorapworapwrac1orapwrac2spfilerac.ora解包数据文件[oracle@eygleoracle]$lsadmindocjreoradataoradata.taroraInventoryouiproductstdcotrl.ctl[oracle@eygleoracle]$tar-xvforadata.taroradata/oradata/primary/oradata/primary/archive/oradata/primary/control01.ctloradata/primary/control02.ctloradata/primary/control03.ctloradata/primary/redo01.logoradata/primary/redo02.logoradata/primary/redo03.logoradata/primary/system01.dbforadata/primary/undotbs01.dbforadata/primary/temp01.dbforadata/primary/users01.dbf修正initprimary.ora文件修正把持文件称号及路径(假如和原设置分歧)，增添几个参数,修正后以下:[oracle@eygledbs]$catinitprimary.ora*.aq_tm_processes=1*.background_dump_dest=/opt/oracle/admin/primary/bdump*.compatible=9.2.0.0.0*.control_files=/opt/oracle/oradata/primary/stdcotrl.ctl*.core_dump_dest=/opt/oracle/admin/primary/cdump...*.log_archive_dest_1=LOCATION=/opt/oracle/oradata/primary/archive*.log_archive_dest_2=*.log_archive_format=%t_%s.dbf*.log_archive_start=true...*.user_dump_dest=/opt/oracle/admin/primary/udump*.utl_file_dir=/opt/oracle*.standby_archive_dest=/opt/oracle/oradata/primary/stdarch*.fal_server=PRIMARY*.fal_client=STANDBY*.standby_file_management=AUTO创立需要的目次[oracle@eygleoracle]$cd$ORACLE_BASE/admin[oracle@eygleadmin]$mkdirprimary[oracle@eygleadmin]$lsprimaryrac[oracle@eygleadmin]$cdprimary/[oracle@eygleprimary]$ls[oracle@eygleprimary]$mkdirbdumpcdumpudump




4.设置主节点监听器及tnsnames.ora文件
设置后以下:



[oracle@standbyoracle]$cd/opt/oracle/product/9.2.0/network/admin/[oracle@standbyadmin]$catlistener.ora#LISTENER.ORANetworkConfigurationFile:/opt/oracle/product/9.2.0/network/admin/listener.ora#GeneratedbyOracleconfigurationtools.LISTENER=(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=standby)(PORT=1521)))))SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=PLSExtProc)(ORACLE_HOME=/opt/oracle/product/9.2.0)(PROGRAM=extproc))(SID_DESC=(GLOBAL_DBNAME=primary)(ORACLE_HOME=/opt/oracle/product/9.2.0)(SID_NAME=primary)))[oracle@standbyadmin]$cattnsnames.ora#TNSNAMES.ORANetworkConfigurationFile:/opt/oracle/product/9.2.0/network/admin/tnsnames.ora#GeneratedbyOracleconfigurationtools.STANDBY=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.33.46)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=primary)))PRIMARY=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.33.58)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=primary)))[oracle@standbyadmin]$lsnrctlstartLSNRCTLforLinux:Version9.2.0.4.0-Productionon16-AUG-200410:46:31Copyright(c)1991,2002,OracleCorporation.Allrightsreserved.Starting/opt/oracle/product/9.2.0/bin/tnslsnr:pleasewait...TNSLSNRforLinux:Version9.2.0.4.0-ProductionSystemparameterfileis/opt/oracle/product/9.2.0/network/admin/listener.oraLogmessageswrittento/opt/oracle/product/9.2.0/network/log/listener.logListeningon:(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))Listeningon:(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standby)(PORT=1521)))Connectingto(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))STATUSoftheLISTENER------------------------AliasLISTENERVersionTNSLSNRforLinux:Version9.2.0.4.0-ProductionStartDate16-AUG-200410:46:31Uptime0days0hr.0min.0secTraceLeveloffSecurityOFFSNMPOFFListenerParameterFile/opt/oracle/product/9.2.0/network/admin/listener.oraListenerLogFile/opt/oracle/product/9.2.0/network/log/listener.logListeningEndpointsSummary...(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standby)(PORT=1521)))ServicesSummary...Service"PLSExtProc"has1instance(s).Instance"PLSExtProc",statusUNKNOWN,has1handler(s)forthisservice...Service"primary"has1instance(s).Instance"primary",statusUNKNOWN,has1handler(s)forthisservice...Thecommandcompletedsuccessfully




5.设置备用数据库监听器及tnsnames.ora文件

设置后文件以下:




[oracle@eygleadmin]$cd$ORACLE_HOME/network/admin[oracle@eygleadmin]$catlistener.ora#LISTENER.ORANetworkConfigurationFile:/opt/oracle/product/9.2.0/network/admin/listener.ora#GeneratedbyOracleconfigurationtools.LISTENER=(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=eygle)(PORT=1521)))))SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(GLOBAL_DBNAME=primary)(ORACLE_HOME=/opt/oracle/product/9.2.0)(SID_NAME=primary)))[oracle@eygleadmin]$cattnsnames.ora#TNSNAMES.ORANetworkConfigurationFile:/opt/oracle/product/9.2.0/network/admin/tnsnames.ora#GeneratedbyOracleconfigurationtools.STANDBY=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.33.46)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=primary)))PRIMARY=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.33.58)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=primary)))[oracle@eygleadmin]$




6.在主备节点用tnsping测试收集连通性




[oracle@standbyadmin]$tnspingstandbyTNSPingUtilityforLinux:Version9.2.0.4.0-Productionon16-AUG-200410:46:50Copyright(c)1997OracleCorporation.Allrightsreserved.Usedparameterfiles:/opt/oracle/product/9.2.0/network/admin/sqlnet.oraUsedTNSNAMESadaptertoresolvethealiasAttemptingtocontact(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.33.46)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=primary)))OK(10msec)[oracle@standbyadmin]$tnspingprimaryTNSPingUtilityforLinux:Version9.2.0.4.0-Productionon16-AUG-200410:46:55Copyright(c)1997OracleCorporation.Allrightsreserved.Usedparameterfiles:/opt/oracle/product/9.2.0/network/admin/sqlnet.oraUsedTNSNAMESadaptertoresolvethealiasAttemptingtocontact(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.33.58)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=primary)))OK(0msec)[oracle@eygleadmin]$tnspingprimaryTNSPingUtilityforLinux:Version9.2.0.4.0-Productionon16-AUG-200410:10:01Copyright(c)1997OracleCorporation.Allrightsreserved.Usedparameterfiles:/opt/oracle/product/9.2.0/network/admin/sqlnet.oraUsedTNSNAMESadaptertoresolvethealiasAttemptingtocontact(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.33.58)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=primary)))OK(50msec)[oracle@eygleadmin]$tnspingstandbyTNSPingUtilityforLinux:Version9.2.0.4.0-Productionon16-AUG-200410:10:06Copyright(c)1997OracleCorporation.Allrightsreserved.Usedparameterfiles:/opt/oracle/product/9.2.0/network/admin/sqlnet.oraUsedTNSNAMESadaptertoresolvethealiasAttemptingtocontact(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.33.46)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=primary)))OK(10msec)




7.启动备用数据库



[oracle@eygleprimary]$hostname
eygle
[oracle@eygleprimary]$sqlplus"/assysdba"

SQL*Plus:Release9.2.0.4.0-ProductiononMonAug1611:09:402004

Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.

Connectedtoanidleinstance.

SQL>startupnomount;
ORACLEinstancestarted.

TotalSystemGlobalArea135337420bytes
FixedSize452044bytes
VariableSize109051904bytes
DatabaseBuffers25165824bytes
RedoBuffers667648bytes
SQL>alterdatabasemountstandbydatabase;

Databasealtered.

SQL>alterdatabaserecovermanagedstandbydatabasedisconnectfromsession;

Databasealtered.









8.在主节点设置回档路径



SQL>altersystemsetlog_archive_dest_2=service=standbymandatoryreopen=60;

Systemaltered.

SQL>altersystemswitchlogfile;

Systemaltered.

SQL>/

Systemaltered.

SQL>

在备用节点察看日记

[oracle@eyglebdump]$tail-falert_primary.log
MRP0:BackgroundManagedStandbyRecoveryprocessstarted
Startingdatafile1recoveryinthread1sequence90
Datafile1:/opt/oracle/oradata/primary/system01.dbf
Startingdatafile2recoveryinthread1sequence90
Datafile2:/opt/oracle/oradata/primary/undotbs01.dbf
Startingdatafile3recoveryinthread1sequence90
Datafile3:/opt/oracle/oradata/primary/users01.dbf
MediaRecoveryWaitingforthread1seq#90
MonAug1611:10:502004
Completed:alterdatabaserecovermanagedstandbydatabasedi
MonAug1611:13:342004
MediaRecoveryLog/opt/oracle/oradata/primary/stdarch/1_90.dbf
MediaRecoveryWaitingforthread1seq#91
MediaRecoveryLog/opt/oracle/oradata/primary/stdarch/1_91.dbf
MediaRecoveryWaitingforthread1seq#92
MonAug1612:09:382004
MediaRecoveryLog/opt/oracle/oradata/primary/stdarch/1_92.dbf







9.在主节点举行一样的设置，以便切换后持续日记传送



[oracle@standbyoracle]$ls
admindictionary.orainitprimary.orajreoradataoradata.taroraInventoryouiproductsoftstdcotrl.ctl
[oracle@standbyoracle]$cdoradata
[oracle@standbyoradata]$ls
primary
[oracle@standbyoradata]$cdprimary/
[oracle@standbyprimary]$ls
archivecontrol02.ctlredo01.logredo03.logtemp01.dbfusers01.dbf
control01.ctlcontrol03.ctlredo02.logsystem01.dbfundotbs01.dbf
[oracle@standbyprimary]$mkdirstdarch
[oracle@standbyprimary]$exit
exit


SQL>altersystemsetstandby_archive_dest=/opt/oracle/oradata/primary/stdarch;

Systemaltered.







10.中断主数据库,启用备用数据库




SQL>alterdatabasecommittoswitchovertophysicalstandby;Databasealtered.SQL>shutdownimmediateORA-01507:databasenotmountedORACLEinstanceshutdown.在备用形式启用主数据SQL>startupnomount;ORACLEinstancestarted.TotalSystemGlobalArea135337420bytesFixedSize452044bytesVariableSize109051904bytesDatabaseBuffers25165824bytesRedoBuffers667648bytesSQL>alterdatabasemountstandbydatabase;Databasealtered.SQL>selectname,open_mode,PROTECTION_MODE,DATABASE_ROLEfromv$database;NAMEOPEN_MODEPROTECTION_MODEDATABASE_ROLE-------------------------------------------------------PRIMARYMOUNTEDMAXIMUMPERFORMANCEPHYSICALSTANDBYSQL>ALTERDATABASERECOVERMANAGEDSTANDBYDATABASEDISCONNECTFROMSESSION;Databasealtered.翻开备用数据库[oracle@eygleoracle]$sqlplus"/assysdba"SQL*Plus:Release9.2.0.4.0-ProductiononMonAug1612:11:112004Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.Connectedto:Oracle9iEnterpriseEditionRelease9.2.0.4.0-ProductionWiththePartitioning,RealApplicationClusters,OLAPandOracleDataMiningoptionsJServerRelease9.2.0.4.0-ProductionSQL>alterdatabasecommittoswitchovertoprimary;Databasealtered.SQL>shutdownimmediate;ORA-01507:databasenotmountedORACLEinstanceshutdown.SQL>startupORACLEinstancestarted.TotalSystemGlobalArea135337420bytesFixedSize452044bytesVariableSize109051904bytesDatabaseBuffers25165824bytesRedoBuffers667648bytesDatabasemounted.Databaseopened.SQL>altersystemswitchlogfile;Systemaltered.在主库上察看日记使用情形[oracle@standbybdump]$tail-falert_primary.logStartingdatafile2recoveryinthread1sequence93Datafile2:/opt/oracle/oradata/primary/undotbs01.dbfStartingdatafile3recoveryinthread1sequence93Datafile3:/opt/oracle/oradata/primary/users01.dbfMediaRecoveryLog/opt/oracle/product/9.2.0/dbs/arch1_93.dbfMonAug1615:08:432004MediaRecoveryLog/opt/oracle/oradata/primary/stdarch/1_94.dbfMediaRecoveryWaitingforthread1seq#95MediaRecoveryLog/opt/oracle/oradata/primary/stdarch/1_95.dbfMediaRecoveryWaitingforthread1seq#96




11.举行数据修正




SQL>createtabletasselect*fromdba_users;Tablecreated.SQL>altersystemswitchlogfile;Systemaltered.在从库上以readonly翻开数据库，实行查询SQL>selectusernamefromt;selectusernamefromt*ERRORatline1:ORA-01219:databasenotopen:queriesallowedonfixedtables/viewsonlySQL>ALTERDATABASERECOVERMANAGEDSTANDBYDATABASECANCEL;Databasealtered.SQL>ALTERDATABASEOPENREADONLY;Databasealtered.SQL>selectusernamefromt;USERNAME------------------------------SYSSYSTEMDBSNMPOUTLNWMSYSSQL>ALTERDATABASERECOVERMANAGEDSTANDBYDATABASEDISCONNECTFROMSESSION;Databasealtered.SQL>




12.把数据库切换回到主节点




在主节点SQL>alterdatabasecommittoswitchovertophysicalstandby;Databasealtered.SQL>shutdownimmediateORA-01507:databasenotmountedstatORACLEinstanceshutdown.SQL>startupnomount;ORACLEinstancestarted.TotalSystemGlobalArea135337420bytesFixedSize452044bytesVariableSize109051904bytesDatabaseBuffers25165824bytesRedoBuffers667648bytesSQL>alterdatabasemountstandbydatabase;Databasealtered.SQL>ALTERDATABASERECOVERMANAGEDSTANDBYDATABASEDISCONNECTFROMSESSION;Databasealtered.在备用节点SQL>alterdatabasecommittoswitchovertoprimary;Databasealtered.SQL>shutdownimmediate;ORA-01507:databasenotmountedORACLEinstanceshutdown.SQL>startupORACLEinstancestarted.TotalSystemGlobalArea135337420bytesFixedSize452044bytesVariableSize109051904bytesDatabaseBuffers25165824bytesRedoBuffers667648bytesDatabasemounted.Databaseopened.




完成自在切换
为多种编程语言提供了API。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。

分区表效率问题肯定是大家关心的问题。在我的试验中，如果按照分区字段进行的查询（过滤）效率会高于未分区表的相同语句。但是如果按照非分区字段进行查询，效率会低于未分区表的相同语句。

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

是要和操作系统进行Socket通讯的场景。否则建议慎重！

不过话说回来了，绝大多数的性能优化准则与对sqlserver存储的结构理解息息相关

财务软件要用SQL也只是后台的数据库而已,软件都是成品的,当然多学东西肯定是有好处的..

呵呵，这就是偶想说的

所以你总能得到相应的升级版本，来满足你的需求。

两个月啃那本sqlserver2005技术内部-存储引擎，花了几个月啃四本书