数据库容灾实施手册

'中国电力财务有限公司Oracle数据库容灾方案实施手册----------技能提升11月份 更改说明：序更改版本号更改人日期备注11.0版杨远征2010-11-30初始版本 基于oracleredolog的逻辑复制方式使用这种方式的主要有一些第三方的软件，以及oracle自己的DATAGUARD中的logicalStandby。1）使用oracle以外的独立进程，捕捉redologfile的信息，将其翻译成sql语句，再通过网络传输到目标端数据库，在目标端数据库执行同样的sql。2）如果其进程赶不上oracle日志切换，也可以捕捉归档日志中的内容。也有的产品在源端以事务为单位，当一个事务完成后，再把它传输到目标端。3）所有的产品一般都是以表为单位进行复制，同时也支持大部分DDL的复制4）基于其实现原理及多个队列文件的使用，复制环境可以提供网络失败、数据库失败、主机失败的容错能力；5）因为这类软件复制的只是sql语句或事务，所以他可以完全支持异构环境的复制，硬件的型号，oracle的版本，操作系统的种类、版本等都没有要求。这种方式还可以支持多种复制方式，比如数据集中、分发、对等复制、或者多层次的复制等。由于传输的内容只是redolog或archivelog中的一部分，所以对网络资源的占用很小，可以实现不同城市之间的远程复制基于redolog的逻辑复制产品有很多的优势，但跟上面提到过的其他方案比较起来，也有一些缺点：1）数据库的吞吐量太大时，会有较大的延迟，当数据库每天的日量达到60G或更大时，这种方案的可行性较差；2）复制环境建立起来以后，对数据库结构上的一些修改需要按照规定的操作流程进行，有一定的维护成本。一、DataGuard的技术实现1.DataGuard的技术实现Oracle数据库通过Redo日志机制实现数据库的事务重演以及可恢复性，Oracle的DataGuard技术就是通过在主备节点之间传输日志实现数据同步。 在出现故障时，备用数据库可以被启用来提供业务服务，也可以作为容灾备份站点来保障数据安全。下图是OracleDataGuard技术的简要实现机制说明，Redo日志传输与应用是DataGuard技术的关键：1.DataGuard技术的实现模式DataGuard技术主要有三种实现模式：1.最大保护模式-MaximumProtection最大保护模式可以认为是DataGuard的同步保护机制，Redo需要同时写入主、备数据库事务才能完成，这种模式可以保证0数据损失，但是要求网络高度稳定可靠。2.最大可用模式-Maximumavailability这种方式在Redo机制上和最大保护模式相同，但是当网络中断时，主库会放弃向从库写日志，继续主库的事务处理。这种方式可以避免因为一些网络问题而影响主库运行。3.最大性能模式-Maximumperformance这种模式是目前采用最多的一种，最大性能模式在主库发生日志归档时才向备用数据库传输日志，从而将DataGuard对主库的性能影响降到最低。这种模式的缺点是，如果主库发生灾难性故障，日志全部损失，则从库可能和主库出现一个左右的日志信息差异。当然，在这种方式下，我们可以通过设置主库增加归档频率来缩小可能的数据损失。2.实施内容及步骤在本方案的具体实施过程中，涉及以下步骤和环节：1.基本环境准备 在实施方案之前，需要配置准备好相关业务服务器、存储环境，实施DataGuard的主机，需要采用相同版本的操作系统，主机与存储正确配置；网络环境需要就绪，并且经过稳定性、连续性测试，提供相关数据整理及报告1.数据库信息收集收集现有数据库系统的完备性能数据、基础数据、负荷及业务周期数据此过程需要收集至少4周数据，可以与其他环节并行。2.数据库安装及升级根据需要，在新的环境中安装Oracle10g数据库，使用Oracle10.2.0.4版本，在迁移实施中对数据库进行升级，升级到最新Oracle版本，此过程需要进行足够测试与完善备份。为顺利完成升级过程，需要调整数据库运行在归档模式下，每个数据库升级准备工作需要至少2天时间，升级需要大约6小时停机时间，升级之后需要观察至少1星期。升级工作需要串行，观察工作可以并行。3.系统测试通过备份搭建环境之后，进行运行测试，收集相关数据如果设备及环境允许进行测试，则进行至少连续2周测试，此步骤需要在数据库升级之后进行。测试环境搭建好之后，进行各种异常演练，构建完善处理文档。4.正式割接经过测试验证后，对生产系统进行DataGuard环境搭建，使生产系统运行在主备模式。此工作对于不同数据库应当串行，每次割接需要2天准备时间以及至少6个小时停机时间。5.运行及监控通过一定周期的运行监控及问题调整，使系统运行在稳定的主备模式下，验收交付用户使用。观察监控周期至少4周，需要记录完善数据供以后运行参考。1.数据库备份实施步骤备份实施是本项目的核心内容，以下步骤是经过实践的实施方案，详细记录备用及参考。1.明确主备数据库首先收集主备数据库相关信息，这些信息应当包括主备数据库的主机地址、主机名称、主备数据库的数据库名称。以下示范用于确认主机信息、IP地址等：$uname-aSunOStestdbserver.hurray.com.cn5.8Generic_108528-25sun4usparcSUNW,Sun-Fire-280R$ifconfig-alo0:flags=1000849mtu8232index1inet127.0.0.1netmaskff000000eri0:flags=1000843mtu1500index2inet172.16.33.50netmaskffffff00broadcast172.16.33.255以下是示范主从数据库信息记录：DatabaseDB_UNIQUE_NAMEOracleNetServiceNamePrimaryCuugCuug PhysicalStandbycuugStdby1.启用强制日志模式设置主库为Force_logging模式：SQL>selectforce_loggingfromv$database;FOR---NOSQL>alterdatabaseforcelogging;Databasealtered.SQL>selectforce_loggingfromv$database;FOR---YES2.更改数据库的归档模式SQL>archiveloglist;DatabaselogmodeNoArchiveModeAutomaticarchivalDisabledArchivedestinationUSE_DB_RECOVERY_FILE_DESTOldestonlinelogsequence1Currentlogsequence3SQL>shutdownimmediate;Databaseclosed.Databasedismounted.ORACLEinstanceshutdown.SQL>startupmount;ORACLEinstancestarted.TotalSystemGlobalArea1258291200bytesFixedSize1978336bytesVariableSize318771232bytesDatabaseBuffers922746880bytesRedoBuffers14794752bytesDatabasemounted.SQL>alterdatabasearchivelog;Databasealtered.SQL>alterdatabaseopen;Databasealtered.SQL>archiveloglist; DatabaselogmodeArchiveModeAutomaticarchivalEnabledArchivedestinationUSE_DB_RECOVERY_FILE_DESTOldestonlinelogsequence1Nextlogsequencetoarchive3Currentlogsequence31.配置网络服务信息以下分别列举主从数据库配置信息，主库的TNSNAMES.ORA文件配置：CUUG=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.33.50)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=CUUG)))STDBY=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.33.97)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=CUUG)))主库的监听器配置（listener.ora文件）：LISTENER=(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.33.50)(PORT=1521)))))SID_LIST_LISTENER=(SID_LIST=(SID_DESC= (GLOBAL_DBNAME=CUUG)(ORACLE_HOME=/data2/ora10g/10.2.0)(SID_NAME=CUUG)))接下来需要通过tnsping测试一下网络服务的连通情况，主库上的tnsping测试示范如下：bash-2.03$tnspingCUUGTNSPingUtilityforSolaris:Version10.2.0.1.0-Productionon06-AUG-200714:18:47Copyright(c)1997,2005,Oracle.Allrightsreserved.Usedparameterfiles:/data2/ora10g/10.2.0/network/admin/sqlnet.oraUsedTNSNAMESadaptertoresolvethealiasAttemptingtocontact(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.33.50)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=CUUG)))OK(10msec)bash-2.03$tnspingstdbyTNSPingUtilityforSolaris:Version10.2.0.1.0-Productionon06-AUG-200714:18:49Copyright(c)1997,2005,Oracle.Allrightsreserved.Usedparameterfiles:/data2/ora10g/10.2.0/network/admin/sqlnet.oraUsedTNSNAMESadaptertoresolvethealiasAttemptingtocontact(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.33.97)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=CUUG)))从库的TNSNAMES.ORA文件配置：CUUG=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.33.50)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=CUUG)))STDBY= (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.33.97)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=CUUG)))从库的监听器配置（listener.ora文件）：LISTENER=(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.33.97)(PORT=1521))))SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(GLOBAL_DBNAME=CUUG)(ORACLE_HOME=/opt/oracle/product/10.2.0)(SID_NAME=CUUG)))从库上通过tnsping的测试：bash-3.00$tnspingCUUGTNSPingUtilityforSolaris:Version10.2.0.1.0-Productionon06-AUG-200714:52:51Copyright(c)1997,2005,Oracle.Allrightsreserved.Usedparameterfiles:/opt/oracle/product/10.2.0/network/admin/sqlnet.oraUsedTNSNAMESadaptertoresolvethealiasAttemptingtocontact(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.33.50)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=CUUG)))OK(60msec)bash-3.00$tnspingstdbyTNSPingUtilityforSolaris:Version10.2.0.1.0-Productionon06-AUG-200714:52:55Copyright(c)1997,2005,Oracle.Allrightsreserved. Usedparameterfiles:/opt/oracle/product/10.2.0/network/admin/sqlnet.oraUsedTNSNAMESadaptertoresolvethealiasAttemptingtocontact(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.33.97)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=CUUG)))1.确定参数文件参数文件中需要设置关键的一些参数，以下是主库的相关参数设置示范（以primary和standby为例）：*.log_archive_config="DG_CONFIG=(primary,standby)"*.log_archive_dest_1="LOCATION=USE_DB_RECOVERY_FILE_DESTARCHMANDATORYREOPEN=5VALID_FOR=(all_logfiles,all_roles)"*.log_archive_dest_2="SERVICE=standbyLGWRASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=standby"*.fal_client="PRIMARY"*.fal_server="STANDBY"从库的一些参数文件设置如下：*.fal_client="STANDBY"*.fal_server="PRIMARY"*.job_queue_processes=10*.log_archive_config="DG_CONFIG=(primary,standby)"*.log_archive_dest_1="LOCATION=USE_DB_RECOVERY_FILE_DESTARCHMANDATORYREOPEN=5VALID_FOR=(all_logfiles,all_roles)"*.log_archive_dest_2="SERVICE=primaryLGWRASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=primary"*.open_cursors=300*.pga_aggregate_target=408944640*.processes=500*.remote_login_passwordfile="EXCLUSIVE"*.sessions=555*.sga_max_size=10737418240*.sga_target=3221225472*.db_file_name_convert="/home/oracle/oradata/ORA10G/datafile","/home/oracle/oradata/STANDBY/datafile"*.standby_file_management="AUTO"*.undo_management="AUTO"*.undo_tablespace="UNDOTBS1"*.user_dump_dest="/home/oracle/admin/ora10g/udump"2.使用RMAN对主库进行备份此处需要进行全备份，使用如下命令：backupfulldatabaseincludecurrentcontrolfileforstandby;以下是示范输出： bash-2.03$rmantarget/RecoveryManager:Release10.2.0.1.0-ProductiononWedAug809:28:362007Copyright(c)1982,2005,Oracle.Allrightsreserved.connectedtotargetdatabase:CUUG(DBID=1447940999)RMAN>backupfulldatabaseincludecurrentcontrolfileforstandby;Startingbackupat08-AUG-07usingchannelORA_DISK_1channelORA_DISK_1:startingfulldatafilebackupsetchannelORA_DISK_1:specifyingdatafile(s)inbackupsetinputdatafilefno=00005name=/data2/ora10g/oradata/CUUG/datafile/o1_mf_CUUG_3cfmh0nw_.dbfinputdatafilefno=00001name=/data2/ora10g/oradata/CUUG/datafile/o1_mf_system_3bf9chk8_.dbfinputdatafilefno=00002name=/data2/ora10g/oradata/CUUG/datafile/o1_mf_undotbs1_3bf9chtk_.dbfinputdatafilefno=00003name=/data2/ora10g/oradata/CUUG/datafile/o1_mf_sysaux_3bf9chn2_.dbfinputdatafilefno=00004name=/data2/ora10g/oradata/CUUG/datafile/o1_mf_users_3bf9chxk_.dbfchannelORA_DISK_1:startingpiece1at08-AUG-07channelORA_DISK_1:finishedpiece1at08-AUG-07piecehandle=/data2/ora10g/flash_recovery_area/CUUG/backupset/2007_08_08/o1_mf_nnndf_TAG20070808T104424_3clcj9k0_.bkptag=TAG20070808T104424comment=NONEchannelORA_DISK_1:backupsetcomplete,elapsedtime:00:02:06channelORA_DISK_1:startingfulldatafilebackupsetchannelORA_DISK_1:specifyingdatafile(s)inbackupsetincludingstandbycontrolfileinbackupsetincludingcurrentSPFILEinbackupsetchannelORA_DISK_1:startingpiece1at08-AUG-07channelORA_DISK_1:finishedpiece1at08-AUG-07piecehandle=/data2/ora10g/flash_recovery_area/CUUG/backupset/2007_08_08/o1_mf_ncsnf_TAG20070808T104424_3clcn8p7_.bkptag=TAG20070808T104424comment=NONEchannelORA_DISK_1:backupsetcomplete,elapsedtime:00:00:05Finishedbackupat08-AUG-071.传输备份将备份后的备份集传输到备用主机：#mkdir-p/data2/ora10g/flash_recovery_area/CUUG/backupset/2010_11_08/ #cd/data2/ora10g/flash_recovery_area/CUUG/backupset/2010_11_08/#ls#sftporacle@172.16.33.50Connectingto172.16.33.50...oracle@172.16.33.50"spassword:sftp>cd/data2/ora10g/flash_recovery_area/CUUG/backupset/2010_11_08/sftp>ls...o1_mf_ncsnf_TAG20070808T093757_3cl7rn0d_.bkpo1_mf_ncsnf_TAG20070808T104424_3clcn8p7_.bkpo1_mf_nnndf_TAG20070808T093757_3cl7nb03_.bkpo1_mf_nnndf_TAG20070808T104424_3clcj9k0_.bkpsftp>mget*Fetching/data2/ora10g/flash_recovery_area/CUUG/backupset/2010_11_08/o1_mf_ncsnf_TAG20070808T093757_3cl7rn0d_.bkptoo1_mf_ncsnf_TAG20070808T093757_3cl7rn0d_.bkpFetching/data2/ora10g/flash_recovery_area/CUUG/backupset/2010_11_08/o1_mf_ncsnf_TAG20070808T104424_3clcn8p7_.bkptoo1_mf_ncsnf_TAG20070808T104424_3clcn8p7_.bkpFetching/data2/ora10g/flash_recovery_area/CUUG/backupset/2010_11_08/o1_mf_nnndf_TAG20070808T093757_3cl7nb03_.bkptoo1_mf_nnndf_TAG20070808T093757_3cl7nb03_.bkpaaFetching/data2/ora10g/flash_recovery_area/CUUG/backupset/2010_11_08/o1_mf_nnndf_TAG20070808T104424_3clcj9k0_.bkptoo1_mf_nnndf_TAG20070808T104424_3clcj9k0_.bkpsftp>quit1.执行恢复完成备份传输之后，可以在主库连接备用数据库进行恢复：$rmantarget/RecoveryManager:Release10.2.0.1.0-ProductiononWedAug813:37:082007Copyright(c)1982,2005,Oracle.Allrightsreserved.connectedtotargetdatabase:CUUG(DBID=1447940999)RMAN>connectauxiliarysys/oracle@stdbyconnectedtoauxiliarydatabase:CUUG(notmounted)RMAN>duplicatetargetdatabaseforstandby;StartingDuplicateDbat08-AUG-07usingchannelORA_AUX_DISK_1contentsofMemoryScript:{restoreclonestandbycontrolfile; sqlclone"alterdatabasemountstandbydatabase";}executingMemoryScriptStartingrestoreat08-AUG-01channelORA_AUX_DISK_1:startingdatafilebackupsetrestorechannelORA_AUX_DISK_1:restoringcontrolfilechannelORA_AUX_DISK_1:readingfrombackuppiece/data2/ora10g/flash_recovery_area/CUUG/backupset/2010_11_08/o1_mf_ncsnf_TAG20070808T104424_3clcn8p7_.bkpchannelORA_AUX_DISK_1:restoredbackuppiece1piecehandle=/data2/ora10g/flash_recovery_area/CUUG/backupset/2010_11_08/o1_mf_ncsnf_TAG20070808T104424_3clcn8p7_.bkptag=TAG20070808T104424channelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:03outputfilename=/data2/ora10g/oradata/STDBY/controlfile/o1_mf_3clr03bp_.ctloutputfilename=/data2/ora10g/flash_recovery_area/STDBY/controlfile/o1_mf_3clr04gj_.ctlFinishedrestoreat08-AUG-07sqlstatement:alterdatabasemountstandbydatabasereleasedchannel:ORA_AUX_DISK_1contentsofMemoryScript:{setnewnameforclonetempfile1tonew;switchclonetempfileall;setnewnameforclonedatafile1tonew;setnewnameforclonedatafile2tonew;setnewnameforclonedatafile3tonew;setnewnameforclonedatafile4tonew;setnewnameforclonedatafile5tonew;restorecheckreadonlyclonedatabase;}executingMemoryScriptexecutingcommand:SETNEWNAMErenamedtemporaryfile1to/data2/ora10g/oradata/STDBY/datafile/o1_mf_temp_%u_.tmpincontrolfile executingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEStartingrestoreat08-AUG-07allocatedchannel:ORA_AUX_DISK_1channelORA_AUX_DISK_1:sid=155devtype=DISKchannelORA_AUX_DISK_1:startingdatafilebackupsetrestorechannelORA_AUX_DISK_1:specifyingdatafile(s)torestorefrombackupsetrestoringdatafile00001to/data2/ora10g/oradata/STDBY/datafile/o1_mf_system_%u_.dbfrestoringdatafile00002to/data2/ora10g/oradata/STDBY/datafile/o1_mf_undotbs1_%u_.dbfrestoringdatafile00003to/data2/ora10g/oradata/STDBY/datafile/o1_mf_sysaux_%u_.dbfrestoringdatafile00004to/data2/ora10g/oradata/STDBY/datafile/o1_mf_users_%u_.dbfrestoringdatafile00005to/data2/ora10g/oradata/STDBY/datafile/o1_mf_CUUG_%u_.dbfchannelORA_AUX_DISK_1:readingfrombackuppiece/data2/ora10g/flash_recovery_area/CUUG/backupset/2010_11_08/o1_mf_nnndf_TAG20070808T104424_3clcj9k0_.bkpchannelORA_AUX_DISK_1:restoredbackuppiece1piecehandle=/data2/ora10g/flash_recovery_area/CUUG/backupset/2010_11_08/o1_mf_nnndf_TAG20070808T104424_3clcj9k0_.bkptag=TAG20070808T104424channelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:05:10Finishedrestoreat08-AUG-07contentsofMemoryScript:{switchclonedatafileall;}executingMemoryScriptdatafile1switchedtodatafilecopyinputdatafilecopyrecid=6stamp=630080591filename=/data2/ora10g/oradata/STDBY/datafile/o1_mf_system_3clr0roy_.dbfdatafile2switchedtodatafilecopyinputdatafilecopyrecid=7stamp=630080591 filename=/data2/ora10g/oradata/STDBY/datafile/o1_mf_undotbs1_3clr0rpr_.dbfdatafile3switchedtodatafilecopyinputdatafilecopyrecid=8stamp=630080591filename=/data2/ora10g/oradata/STDBY/datafile/o1_mf_sysaux_3clr0rrt_.dbfdatafile4switchedtodatafilecopyinputdatafilecopyrecid=9stamp=630080591filename=/data2/ora10g/oradata/STDBY/datafile/o1_mf_users_3clr0rt4_.dbfdatafile5switchedtodatafilecopyinputdatafilecopyrecid=10stamp=630080591filename=/data2/ora10g/oradata/STDBY/datafile/o1_mf_CUUG_3clr0ro4_.dbfFinishedDuplicateDbat08-AUG-011.完成备份环境构建恢复完成之后，可以将从库关闭，修改参数文件中的控制文件名称，修改主从数据库网络归档路径，然后启动从库实例，最后以Standby模式启动数据库：bash-3.00$sqlplus"/assysdba"SQL*Plus:Release10.2.0.1.0-ProductiononWedAug814:37:402007Copyright(c)1982,2005,Oracle.Allrightsreserved.Connectedto:OracleDatabase10gEnterpriseEditionRelease10.2.0.1.0-64bitProductionWiththePartitioningandDataMiningoptionsSQL>shutdownimmediate;ORA-01109:databasenotopenDatabasedismounted.ORACLEinstanceshutdown.SQL>startupnomount;ORACLEinstancestarted.TotalSystemGlobalArea1258291200bytesFixedSize1978336bytesVariableSize318771232bytesDatabaseBuffers922746880bytesRedoBuffers14794752bytesSQL>alterdatabasemountstandbydatabase;Databasealtered.ALTERDATABASEADDSTANDBYLOGFILEgroup4"/oracle/oradata/cuug/redo04"SIZE52428800;ALTERDATABASEADDSTANDBYLOGFILEgroup5"/oracle/oradata/cuug/redo05"SIZE52428800; ALTERDATABASEADDSTANDBYLOGFILEgroup6"/oracle/oradata/cuug/redo06"SIZE52428800;SQL>alterdatabaserecovermanagedstandbydatabasedisconnectfromsession;Databasealtered.观察从库的日志应用：WedAug814:49:222007RFS[1]:ArchivedLog:"/data2/ora10g/flash_recovery_area/STDBY/archivelog/2007_08_08/o1_mf_1_72_3clsvl86_.arc"WedAug814:49:232007Clearingonlineredologfile3completeMediaRecoveryLog/data2/ora10g/flash_recovery_area/STDBY/archivelog/2007_08_08/o1_mf_1_67_3clr58pk_.arcWedAug814:49:372007MediaRecoveryLog/data2/ora10g/flash_recovery_area/STDBY/archivelog/2007_08_08/o1_mf_1_68_3clr5tqx_.arcWedAug814:50:012007MediaRecoveryLog/data2/ora10g/flash_recovery_area/STDBY/archivelog/2007_08_08/o1_mf_1_69_3clr6555_.arcWedAug814:50:302007MediaRecoveryLog/data2/ora10g/flash_recovery_area/STDBY/archivelog/2007_08_08/o1_mf_1_70_3clr6rcv_.arcaWedAug814:50:542007MediaRecoveryLog/data2/ora10g/flash_recovery_area/STDBY/archivelog/2007_08_08/o1_mf_1_71_3clr74jb_.arcWedAug814:51:082007MediaRecoveryLog/data2/ora10g/flash_recovery_area/STDBY/archivelog/2007_08_08/o1_mf_1_72_3clsvl86_.arcMediaRecoveryWaitingforthread1sequence73至此，备用数据库创建完成。1.常用切换管理命令在日常管理中，以下常用命令可供参考：1．如果是使用ARCH传递redo数据执行以下命令，可以检查是否有gaparchiveSELECTTHREAD#,LOW_SEQUENCE#,HIGH_SEQUENCE#FROMV$ARCHIVE_GAP;如果有则registerALTERDATABASEREGISTERPHYSICALLOGFILE"filespec1";实行Failover：ALTERDATABASERECOVERMANAGEDSTANDBYDATABASECANCEL;ALTERDATABASEACTIVATESTANDBYDATABASE;ALTERDATABASEMOUNT; ALTERDATABASEOPEN;1．如果是使用LGWR传递redo数据可以执行以下命令，检查是否有gaparchiveSELECTTHREAD#,LOW_SEQUENCE#,HIGH_SEQUENCE#FROMV$ARCHIVE_GAP;如果有则registerALTERDATABASEREGISTERPHYSICALLOGFILE"filespec1";3.使用如下过程完成切换Primary和Standby的工作主数据库端：ALTERDATABASECOMMITTOSWITCHOVERTOPHYSICALSTANDBY;SHUTDOWNIMMEDIATE;STARTUPNOMOUNT;ALTERDATABASEMOUNTSTANDBYDATABASE;备用数据库端：ALTERDATABASECOMMITTOSWITCHOVERTOPRIMARY;SHUTDOWNIMMEDIATE;STARTUP;主数据库端：ALTERDATABASERECOVERMANAGEDSTANDBYDATABASEDISCONNECTFROMSESSION;ALTERDATABASEADDSTANDBYLOGFILEgroup4"/oradata/cuug/redo04.log"SIZE52428800;ALTERDATABASEADDSTANDBYLOGFILEgroup5"/oradata/cuug/redo05.log"SIZE52428800;ALTERDATABASEADDSTANDBYLOGFILEgroup6"/oradata/cuug/redo06.log"size52428800;二、最大性能模式一，第一次同步，只考数据文件或者用RMAN,裸设备SQL>shutdownimmediate;1)在主库上（36）实现共享#vi/etc/dfs/dfstabshare/u01/oracle#/etc/init.d/nfs.serverstart#showmount-e192.168.8.36exportlistfor192.168.8.36:/u01/oracle(everyone) 2)在standby数据库上创建共享目录并且进行拷贝主库的数据文件#mkdir/shared#showmount-e192.168.8.36exportlistfor192.168.8.36:/u01/oracle(everyone)#mount192.168.8.36:/u01/oracle/shared#df-kFilesystemkbytesusedavailcapacityMountedon/dev/dsk/c0t0d0s052395582163116302404742%//proc0000%/procmnttab0000%/etc/mnttabfd0000%/dev/fdswap643424486433761%/var/runswap643472966433761%/tmp/dev/dsk/c0t0d0s73611678320211337344990%/demo192.168.8.36:/u01/oracle139151236874445690152750%/shared#mkdir/u01#chmodoracle:dba/u01$cd/u01$mkdir-poracle/oradata/standby注：生产库是哪个路径在standby上也拷到那个地方，oracle做建个新的目录。2.1)在主库上更改数据文件的权限因为缺省的为其他人没有读的权限。$cd/u01/oracle/oradata/cuug$ls-ltotal1347232-rw-r-----1oracledba22437888Jun1620:31control01.ctl-rw-r-----1oracledba10486272Jun1617:42redo01.log-rw-r-----1oracledba10486272Jun1617:42redo01a.log-rw-r-----1oracledba10486272Jun1617:42redo02.log-rw-r-----1oracledba10486272Jun1617:42redo02a.log-rw-r-----1oracledba10486272Jun1620:26redo03.log-rw-r-----1oracledba10486272Jun1620:26redo03a.log-rw-r-----1oracledba52436992Jun1618:09rollbs01.dbf-rw-r-----1oracledba125837312Jun1618:08statpack_tbs01.dbf-rw-r-----1oracledba20979712Jun713:46suntest_ts.dbf-rw-r-----1oracledba314580992Jun1620:26system01.dbf-rw-r-----1oracledba52436992Jun1414:41temp01.dbf-rw-r-----1oracledba52436992Jun1620:26undorbs01.dbf -rw-r-----1oracledba5251072Jun1618:08userdata.dbf$chmod644./*2.2）在standby库上执行拷贝。$cp/shared/oradata/cuug/*.dbf/u01/oracle/oradata/standby/如果其他地方有文件也要考不要遗漏。二，拷贝初始化参数文件3.1）在备库上$cd/shared/product/9i/dbs$cp./initcuug.ora/demo/oracle/product/9204/dbs/initstandby.ora3.2）修改主库归档为stdby$cd/u01/oracle/product/9i/dbs$viinitcuug.oralog_archive_dest_2="SERVICE=stdby"log_archive_dest_state_2="ENABLE"归档时本地还是远程，默认是ENABLE3.3)修改备库的初始化参数文件$cd/demo/oracle/product/9204/dbs$viinitstandby.oraremote_archive_enable=TRUEstandby_archive_dest="/u01/oracle/standby_arch"db_file_name_convert=("/u01/oracle/oradata/cuug","/u01/oracle/oradata/standby")log_file_name_convert=("/u01/oracle/oradata/cuug","/u01/oracle/oradata/standby")standby_file_management=AUTO备库上一定是AUTO如果生产库的结构发生改变会自动给备库加上否则是导致事务失败。background_dump_dest=/u01/oracle/admin/standby/bdumpuser_dump_dest=/u01/oracle/admin/standby/udumpcore_dump_dest=/u01/oracle/admin/standby/cdumpcontrol_files=(/u01/oracle/oradata/cuug/control01.ctl)把控制文件修改为备库上的位置control_files=(/u01/oracle/oradata/standby/control01.stdby)$cd/u01/oracle$mkdir-padmin/standby/udump$mkdir-padmin/standby/bdump$mkdir-padmin/standby/cdump$mkdir-pstandby_arch四，拷贝控制文件，建standby控制文件。4.1）在主库创建产生standby控制文件. $cd/u01/oracle/admin/cuug/bdump$tail-falert_cuug.log$sqlplus"/assysdba"SQL>startupSQL>alterdatabasecreatestandbycontrolfileas"control01.stdby";SQL>quit$pwd$ls这时产生的控制文件就在当前目录下。inittest.orasqlnet.logcontrol01.stdbyinittest.ora.old4.2)在备库把产生的控制文件拷贝到相应的目录下.$cd/shared/product/9i/dbs$cpcontrol01.stdby/u01/oracle/oradata/standby/五，主库配TNS,STANDBY上配监听1）在备库配置监听$cdproduct/9204/network/admin/$vilistener.ora(SID_DESC=(SID_NAME=standby)(ORACLE_HOME=/demo/oracle/product/9204)(GLOBAL_DBNAME=standby))$lsnrctlstartService"standby"has1instance(s).Instance"standby",statusUNKNOWN,has1handler(s)forthisservice...Thecommandcompletedsuccessfully2)主库配置TNS$vi/u01/oracle/product/9i/network/admin/tnsnames.orastandby=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.8.27)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=standby)))$tnspingstandby六，启动standby数据库1）打开警告日志文件 $cd/u01/oracle/admin/standby/bdump$tail-falert_standby.log２）启动standby数据库到MOUNT状态。$exportORACLE_SID=standby$sqlplus"/assysdba"SQL>startupnomount;SQL>alterdatabasemountstandbydatabase;Databasealtered.如果在初始化参数文件里有remote_login　　则可以屏蔽掉或者创建密码文件$orapwd$orapwdfile=orapwstdbypassword=oracleentries=33）把数据库改变为recover状态。SQL>alterdatabaserecovermanagedstandbydatabasedisconnectfromsession;4）创建standby数据库的redolog文件。4.1）查看主库当前的日志文件SQL>select*fromv$log;SQL>setlinesize180;SQL>/GROUP#THREAD#SEQUENCE#BYTESMEMBERSARCSTATUSFIRST_CHANGE#FIRST_TIM-------------------------------------------------------------------------------------------1132104857602YESINACTIVE102776416-JUN-092134104857602NOCURRENT107152017-JUN-093133104857602YESINACTIVE104798416-JUN-09在主库上有3个redolog文件，所以在standby上也要建3个redolog文件。从警告日志发现：ALTERDATABASEADDSTANDBYLOGFILE"srl1.f"SIZE10485760;ALTERDATABASEADDSTANDBYLOGFILE"srl2.f"SIZE10485760;ALTERDATABASEADDSTANDBYLOGFILE"srl3.f"SIZE10485760; ALTERDATABASEADDSTANDBYLOGFILE"srl4.f"SIZE10485760;4.2)创建备库的日志文件（最大保护时需要该文件）SQL>alterdatabaseaddstandbylogfilegroup4"/u01/oracle/oradata/standby/redo04.log"size10485760;SQL>alterdatabaseaddstandbylogfilegroup5"/u01/oracle/oradata/standby/redo05.log"size10485760;SQL>alterdatabaseaddstandbylogfilegroup6"/u01/oracle/oradata/standby/redo06.log"size10485760;七，测试最大性能模式1）在主库上执行产生日志SQL>select*fromv$log;GROUP#THREAD#SEQUENCE#BYTESMEMBERSARCSTATUSFIRST_CHANGE#FIRST_TIM-------------------------------------------------------------------------------------------1132104857602YESINACTIVE102776416-JUN-092134104857602NOCURRENT107152017-JUN-093133104857602YESINACTIVE104798416-JUN-09SQL>altersystemarchivelogcurrent;SQL>/SQL>select*fromv$log;GROUP#THREAD#SEQUENCE#BYTESMEMBERSARCSTATUSFIRST_CHANGE#FIRST_TIM-------------------------------------------------------------------------------------------1141104857602YESINACTIVE107538318-JUN-092140104857602YESINACTIVE107498318-JUN-093142104857602NOCURRENT107538818-JUN-091.1)查看备库日志是否可以同步，同步则OK.SQL>select*fromv$log;GROUP#THREAD#SEQUENCE#BYTESMEMBERSARCSTATUS FIRST_CHANGE#FIRST_TIM-------------------------------------------------------------------------------------------1141104857602YESCLEARING107538318-JUN-092140104857602YESCLEARING107498318-JUN-093142104857602YESCLEARING_CURRENT107538818-JUN-092)查看数据库保护模式SQL>selectDATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVELfromv$database;DATABASE_ROLEPROTECTION_MODEPROTECTION_LEVEL--------------------------------------------------------PHYSICALSTANDBYMAXIMUMPERFORMANCEMAXIMUMPERFORMANCE三、最大性能模式standby数据库处于最大保护模式，把生产数据库关闭，打开standby数据库，验证表记录的一致性。1）首先在主库强制切换几个日志这样让当前不包含事物的日志都归档到远程，因为在做最大保护模式时归档的模式不一样到时就无法归档了。SQL>altersystemarchivelogcurrent;SQL>/多切换几次SQL>shutdownimmediate;2)添加standbyredologfile首先停止standby的自动恢复状态SQL>alterdatabaserecovermanagedstandbydatabasefinish;Databasealtered.如果没有停止自动恢复状态就添加standbylogfile，会报错：ORA-01156:recoveryinprogressmayneedaccesstofiles 从警告日志发现：ALTERDATABASEADDSTANDBYLOGFILE"srl1.f"SIZE10485760;ALTERDATABASEADDSTANDBYLOGFILE"srl2.f"SIZE10485760;ALTERDATABASEADDSTANDBYLOGFILE"srl3.f"SIZE10485760;ALTERDATABASEADDSTANDBYLOGFILE"srl4.f"SIZE10485760;SQL>alterdatabaseaddstandbylogfilegroup4"/u01/oracle/oradata/standby/redo04.log"size10485760;SQL>alterdatabaseaddstandbylogfilegroup5"/u01/oracle/oradata/standby/redo05.log"size10485760;SQL>alterdatabaseaddstandbylogfilegroup6"/u01/oracle/oradata/standby/redo06.log"size10485760;注意standbylogfile的group名不能和primary的redologfilegroup重复，因为我的primay已经有3组日志了，这里添加的三组standbylogfile从group4开始。同时standbyredologfile的大小和primary的redologfile保持一致。SQL>shutdownimmediatge3）设置standby的归档路径$vi/demo/oracle/product/9204/dbs/initstandby.oralog_archive_dest_1="location=/u01/oracle/arch"standby_archive_dest="/u01/oracle/standby_arch"$mkdir-p/u01/oracle/arch4）在primary上修改为用LGWR传送日志SQL>altersystemsetlog_archive_dest_2="service=standbylgwrasyncaffirm";或者vi/u01/oracle/product/9i/dbs/initcuug.oralog_archive_dest_2="SERVICE=standbylgwrasyncaffirm"5)启动standby数据$exportORACLE_SID=standby$sqlplus"/assysdba"SQL>startupnomount;SQL>alterdatabasemountstandbydatabase; SQL>alterdatabaserecovermanagedstandbydatabasedisconnectfromsession;SQL>archiveloglist;DatabaselogmodeArchiveModeAutomaticarchivalEnabledArchivedestination/u01/oracle/archOldestonlinelogsequence193Nextlogsequencetoarchive0Currentlogsequence1956）启动主库查看归档模式SQL>startupSQL>showparameterlog;NAMETYPEVALUE-----------------------------------------------------------------------------db_create_online_log_dest_1stringdb_create_online_log_dest_2stringdb_create_online_log_dest_3stringdb_create_online_log_dest_4stringdb_create_online_log_dest_5stringdblink_encrypt_loginbooleanFALSElog_archive_deststringlog_archive_dest_1stringlocation=/u01/oracle/archlog_archive_dest_10stringlog_archive_dest_2stringSERVICE=standbylgwrasyncaffirmSQL>selectPROTECTION_MODE,PROTECTION_LEVEL,DATABASE_ROLEfromv$database;PROTECTION_MODEPROTECTION_LEVELDATABASE_ROLE--------------------------------------------------------MAXIMUMPERFORMANCEMAXIMUMPERFORMANCEPRIMARYSQL>select*fromv$log;SQL>altersystemarchivelogcurrent;Systemaltered. SQL>/Systemaltered.SQL>/7）standby数据库查看归档日志已经过来了。SQL>select*fromv$log;8）把生产数据库关闭，打开standby数据库，验证表记录的一致性。SQL>insertintoscott.empselect*fromscott.emp;13rowscreated.SQL>/26rowscreated.SQL>selectcount(*)fromscott.emp;COUNT(*)----------52SQL>shutdownimmediate;备库打开到只读状态查看发现数据一致。SQL>alterdatabaserecovermanagedstandbydatabasefinish;Databasealtered.SQL>alterdatabaseopenreadonly;Databasealtered.SQL>selectcount(*)fromscott.emp;COUNT(*)----------52'