数据库容灾实施手册.doc

【自动充值】百度文库财富值充值 中国电力财务有限公司Oracle数据库容灾方案实施手册-技能提升11月份更改说明：序更改版本号更改人日期备注11.0版杨远征2010-11-30初始版本基于oracle redo log的逻辑复制方式使用这种方式的主要有一些第三方的软件，以及oracle自己的DATAGUARD 中的logical Standby。1）使用oracle以外的独立进程，捕捉redo log file 的信息，将其翻译成sql语句，再通过网络传输到目标端数据库，在目标端数据库执行同样的sql。2）如果其进程赶不上oracle日志切换，也可以捕捉归档日志中的内容。也有的产品在源端以事务为单位，当一个事务完成后，再把它传输到目标端。3）所有的产品一般都是以表为单位进行复制，同时也支持大部分DDL的复制4）基于其实现原理及多个队列文件的使用，复制环境可以提供网络失败、数据库失败、主机失败的容错能力；5）因为这类软件复制的只是sql语句或事务，所以他可以完全支持异构环境的复制，硬件的型号，oracle的版本，操作系统的种类、版本等都没有要求。这种方式还可以支持多种复制方式，比如数据集中、分发、对等复制、或者多层次的复制等。由于传输的内容只是redolog 或archive log中的一部分，所以对网络资源的占用很小，可以实现不同城市之间的远程复制基于redolog的逻辑复制产品有很多的优势，但跟上面提到过的其他方案比较起来，也有一些缺点：1）数据库的吞吐量太大时，会有较大的延迟，当数据库每天的日量达到60G或更大时，这种方案的可行性较差；2）复制环境建立起来以后，对数据库结构上的一些修改需要按照规定的操作流程进行，有一定的维护成本。一、DataGuard的技术实现1. DataGuard的技术实现Oracle数据库通过Redo日志机制实现数据库的事务重演以及可恢复性，Oracle的DataGuard技术就是通过在主备节点之间传输日志实现数据同步。在出现故障时，备用数据库可以被启用来提供业务服务，也可以作为容灾备份站点来保障数据安全。下图是Oracle DataGuard技术的简要实现机制说明，Redo日志传输与应用是DataGuard技术的关键：2. DataGuard技术的实现模式DataGuard技术主要有三种实现模式：1. 最大保护模式-Maximum Protection最大保护模式可以认为是DataGuard的同步保护机制，Redo需要同时写入主、备数据库事务才能完成，这种模式可以保证0数据损失，但是要求网络高度稳定可靠。2. 最大可用模式-Maximum availability这种方式在Redo机制上和最大保护模式相同，但是当网络中断时，主库会放弃向从库写日志，继续主库的事务处理。这种方式可以避免因为一些网络问题而影响主库运行。3. 最大性能模式-Maximum performance这种模式是目前采用最多的一种，最大性能模式在主库发生日志归档时才向备用数据库传输日志，从而将DataGuard对主库的性能影响降到最低。这种模式的缺点是，如果主库发生灾难性故障，日志全部损失，则从库可能和主库出现一个左右的日志信息差异。当然，在这种方式下，我们可以通过设置主库增加归档频率来缩小可能的数据损失。3. 实施内容及步骤在本方案的具体实施过程中，涉及以下步骤和环节：1. 基本环境准备在实施方案之前，需要配置准备好相关业务服务器、存储环境，实施DataGuard的主机，需要采用相同版本的操作系统，主机与存储正确配置；网络环境需要就绪，并且经过稳定性、连续性测试，提供相关数据整理及报告2. 数据库信息收集收集现有数据库系统的完备性能数据、基础数据、负荷及业务周期数据此过程需要收集至少4周数据，可以与其他环节并行。3. 数据库安装及升级根据需要，在新的环境中安装Oracle10g数据库，使用Oracle 版本，在迁移实施中对数据库进行升级，升级到最新Oracle版本，此过程需要进行足够测试与完善备份。为顺利完成升级过程，需要调整数据库运行在归档模式下，每个数据库升级准备工作需要至少2天时间，升级需要大约6小时停机时间，升级之后需要观察至少1星期。升级工作需要串行，观察工作可以并行。4. 系统测试通过备份搭建环境之后，进行运行测试，收集相关数据如果设备及环境允许进行测试，则进行至少连续2周测试，此步骤需要在数据库升级之后进行。测试环境搭建好之后，进行各种异常演练，构建完善处理文档。5. 正式割接经过测试验证后，对生产系统进行DataGuard环境搭建，使生产系统运行在主备模式。此工作对于不同数据库应当串行，每次割接需要2天准备时间以及至少6个小时停机时间。6. 运行及监控通过一定周期的运行监控及问题调整，使系统运行在稳定的主备模式下，验收交付用户使用。观察监控周期至少4周，需要记录完善数据供以后运行参考。4. 数据库备份实施步骤备份实施是本项目的核心内容，以下步骤是经过实践的实施方案，详细记录备用及参考。1. 明确主备数据库首先收集主备数据库相关信息，这些信息应当包括主备数据库的主机地址、主机名称、主备数据库的数据库名称。以下示范用于确认主机信息、IP地址等：$ uname -aSunOS 5.8 Generic_108528-25 sun4u sparc SUNW,Sun-Fire-280R$ ifconfig -alo0: flags=1000849 mtu 8232 index 1 inet netmask ff000000 eri0: flags=1000843 mtu 1500 index 2 inet 0 netmask ffffff00 broadcast 55以下是示范主从数据库信息记录：DatabaseDB_UNIQUE_NAMEOracle Net Service NamePrimaryCuugCuugPhysical StandbycuugStdby2. 启用强制日志模式设置主库为Force_logging模式：SQL select force_logging from v$database;FOR-NOSQL alter database force logging;Database altered.SQL select force_logging from v$database;FOR-YES3. 更改数据库的归档模式SQL archive log list;Database log mode No Archive ModeAutomatic archival DisabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 1Current log sequence 3SQL shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL startup mount;ORACLE instance started.Total System Global Area 1258291200 bytesFixed Size 1978336 bytesVariable Size 318771232 bytesDatabase Buffers 922746880 bytesRedo Buffers 14794752 bytesDatabase mounted.SQL alter database archivelog;Database altered.SQL alter database open;Database altered.SQL archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 1Next log sequence to archive 3Current log sequence 34. 配置网络服务信息以下分别列举主从数据库配置信息，主库的TNSNAMES.ORA文件配置：CUUG = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 0)(PORT = 1521) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CUUG) ) )STDBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 7)(PORT = 1521) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CUUG) ) )主库的监听器配置（listener.ora文件）：LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 0)(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$ tnsping CUUGTNS Ping Utility for Solaris: Version .0 - Production on 06-AUG-2007 14:18:47Copyright (c) 1997, 2005, Oracle. All rights reserved.Used parameter files:/data2/ora10g/10.2.0/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 0)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CUUG)OK (10 msec)bash-2.03$ tnsping stdbyTNS Ping Utility for Solaris: Version .0 - Production on 06-AUG-2007 14:18:49Copyright (c) 1997, 2005, Oracle. All rights reserved.Used parameter files:/data2/ora10g/10.2.0/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 7)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CUUG)从库的TNSNAMES.ORA文件配置：CUUG = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 0)(PORT = 1521) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CUUG) ) )STDBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 7)(PORT = 1521) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CUUG) ) )从库的监听器配置（listener.ora文件）：LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 7)(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$ tnsping CUUGTNS Ping Utility for Solaris: Version .0 - Production on 06-AUG-2007 14:52:51Copyright (c) 1997, 2005, Oracle. All rights reserved.Used parameter files:/opt/oracle/product/10.2.0/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 0)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CUUG)OK (60 msec)bash-3.00$ tnsping stdbyTNS Ping Utility for Solaris: Version .0 - Production on 06-AUG-2007 14:52:55Copyright (c) 1997, 2005, Oracle. All rights reserved.Used parameter files:/opt/oracle/product/10.2.0/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 7)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CUUG)5. 确定参数文件参数文件中需要设置关键的一些参数，以下是主库的相关参数设置示范（以primary和standby为例）：*.log_archive_config=DG_CONFIG=(primary,standby)*.log_archive_dest_1=LOCATION=USE_DB_RECOVERY_FILE_DEST ARCH MANDATORY REOPEN=5 VALID_FOR=(all_logfiles,all_roles)*.log_archive_dest_2=SERVICE=standby LGWR ASYNC VALID_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_DEST ARCH MANDATORY REOPEN=5 VALID_FOR=(all_logfiles,all_roles)*.log_archive_dest_2=SERVICE=primary LGWR ASYNC VALID_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/udump6. 使用RMAN对主库进行备份此处需要进行全备份，使用如下命令：backup full database include current controlfile for standby;以下是示范输出：bash-2.03$ rman target /Recovery Manager: Release .0 - Production on Wed Aug 8 09:28:36 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.connected to target database: CUUG (DBID=1447940999)RMAN backup full database include current controlfile for standby;Starting backup at 08-AUG-07using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00005 name=/data2/ora10g/oradata/CUUG/datafile/o1_mf_CUUG_3cfmh0nw_.dbfinput datafile fno=00001 name=/data2/ora10g/oradata/CUUG/datafile/o1_mf_system_3bf9chk8_.dbfinput datafile fno=00002 name=/data2/ora10g/oradata/CUUG/datafile/o1_mf_undotbs1_3bf9chtk_.dbfinput datafile fno=00003 name=/data2/ora10g/oradata/CUUG/datafile/o1_mf_sysaux_3bf9chn2_.dbfinput datafile fno=00004 name=/data2/ora10g/oradata/CUUG/datafile/o1_mf_users_3bf9chxk_.dbfchannel ORA_DISK_1: starting piece 1 at 08-AUG-07channel ORA_DISK_1: finished piece 1 at 08-AUG-07piece handle=/data2/ora10g/flash_recovery_area/CUUG/backupset/2007_08_08/o1_mf_nnndf_TAG20070808T104424_3clcj9k0_.bkp tag=TAG20070808T104424 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:02:06channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetincluding standby control file in backupsetincluding current SPFILE in backupsetchannel ORA_DISK_1: starting piece 1 at 08-AUG-07channel ORA_DISK_1: finished piece 1 at 08-AUG-07piece handle=/data2/ora10g/flash_recovery_area/CUUG/backupset/2007_08_08/o1_mf_ncsnf_TAG20070808T104424_3clcn8p7_.bkp tag=TAG20070808T104424 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:05Finished backup at 08-AUG-077. 传输备份将备份后的备份集传输到备用主机：# mkdir -p /data2/ora10g/flash_recovery_area/CUUG/backupset/2010_11_08/# cd /data2/ora10g/flash_recovery_area/CUUG/backupset/2010_11_08/# ls# sftp oracle0Connecting to 0.oracle0s password: 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_.bkp to o1_mf_ncsnf_TAG20070808T093757_3cl7rn0d_.bkpFetching /data2/ora10g/flash_recovery_area/CUUG/backupset/2010_11_08/o1_mf_ncsnf_TAG20070808T104424_3clcn8p7_.bkp to o1_mf_ncsnf_TAG20070808T104424_3clcn8p7_.bkpFetching /data2/ora10g/flash_recovery_area/CUUG/backupset/2010_11_08/o1_mf_nnndf_TAG20070808T093757_3cl7nb03_.bkp to o1_mf_nnndf_TAG20070808T093757_3cl7nb03_.bkpaaFetching /data2/ora10g/flash_recovery_area/CUUG/backupset/2010_11_08/o1_mf_nnndf_TAG20070808T104424_3clcj9k0_.bkp to o1_mf_nnndf_TAG20070808T104424_3clcj9k0_.bkpsftp quit8. 执行恢复完成备份传输之后，可以在主库连接备用数据库进行恢复：$ rman target /Recovery Manager: Release .0 - Production on Wed Aug 8 13:37:08 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.connected to target database: CUUG (DBID=1447940999)RMAN connect auxiliary sys/oraclestdbyconnected to auxiliary database: CUUG (not mounted)RMAN duplicate target database for standby;Starting Duplicate Db at 08-AUG-07using channel ORA_AUX_DISK_1contents of Memory Script: restore clone standby controlfile; sql clone alter database mount standby database;executing Memory ScriptStarting restore at 08-AUG-01channel ORA_AUX_DISK_1: starting datafile backupset restorechannel ORA_AUX_DISK_1: restoring control filechannel ORA_AUX_DISK_1: reading from backup piece /data2/ora10g/flash_recovery_area/CUUG/backupset/2010_11_08/o1_mf_ncsnf_TAG20070808T104424_3clcn8p7_.bkpchannel ORA_AUX_DISK_1: restored backup piece 1piece handle=/data2/ora10g/flash_recovery_area/CUUG/backupset/2010_11_08/o1_mf_ncsnf_TAG20070808T104424_3clcn8p7_.bkp tag=TAG20070808T104424channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03output filename=/data2/ora10g/oradata/STDBY/controlfile/o1_mf_3clr03bp_.ctloutput filename=/data2/ora10g/flash_recovery_area/STDBY/controlfile/o1_mf_3clr04gj_.ctlFinished restore at 08-AUG-07sql statement: alter database mount standby databasereleased channel: ORA_AUX_DISK_1contents of Memory Script: set newname for clone tempfile 1 to new; switch clone tempfile all; set newname for clone datafile 1 to new; set newname for clone datafile 2 to new; set newname for clone datafile 3 to new; set newname for clone datafile 4 to new; set newname for clone datafile 5 to new; restore check readonly clone database ;executing Memory Scriptexecuting command: SET NEWNAMErenamed temporary file 1 to /data2/ora10g/oradata/STDBY/datafile/o1_mf_temp_%u_.tmp in control fileexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEStarting restore at 08-AUG-07allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: sid=155 devtype=DISKchannel ORA_AUX_DISK_1: starting datafile backupset restorechannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to /data2/ora10g/oradata/STDBY/datafile/o1_mf_system_%u_.dbfrestoring datafile 00002 to /data2/ora10g/oradata/STDBY/datafile/o1_mf_undotbs1_%u_.dbfrestoring datafile 00003 to /data2/ora10g/oradata/STDBY/datafile/o1_mf_sysaux_%u_.dbfrestoring datafile 00004 to /data2/ora10g/oradata/STDBY/datafile/o1_mf_users_%u_.dbfrestoring datafile 00005 to /data2/ora10g/oradata/STDBY/datafile/o1_mf_CUUG_%u_.dbfchannel ORA_AUX_DISK_1: reading from backup piece /data2/ora10g/flash_recovery_area/CUUG/backupset/2010_11_08/o1_mf_nnndf_TAG20070808T104424_3clcj9k0_.bkpchannel ORA_AUX_DISK_1: restored backup piece 1piece handle=/data2/ora10g/flash_recovery_area/CUUG/backupset/2010_11_08/o1_mf_nnndf_TAG20070808T104424_3clcj9k0_.bkp tag=TAG20070808T104424channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:05:10Finished restore at 08-AUG-07contents of Memory Script: switch clone datafile all;executing Memory Scriptdatafile 1 switched to datafile copyinput datafile copy recid=6 stamp=630080591 filename=/data2/ora10g/oradata/STDBY/datafile/o1_mf_system_3clr0roy_.dbfdatafile 2 switched to datafile copyinput datafile copy recid=7 stamp=630080591 filename=/data2/ora10g/oradata/STDBY/datafile/o1_mf_undotbs1_3clr0rpr_.dbfdatafile 3 switched to datafile copyinput datafile copy recid=8 stamp=630080591 filename=/data2/ora10g/oradata/STDBY/datafile/o1_mf_sysaux_3clr0rrt_.dbfdatafile 4 switched to datafile copyinput datafile copy recid=9 stamp=630080591 filename=/data2/ora10g/oradata/STDBY/datafile/o1_mf_users_3clr0rt4_.dbfdatafile 5 switched to datafile copyinput datafile copy recid=10 stamp=630080591 filename=/data2/ora10g/oradata/STDBY/datafile/o1_mf_CUUG_3clr0ro4_.dbfFinished Duplicate Db at 08-AUG-019. 完成备份环境构建恢复完成之后，可以将从库关闭，修改参数文件中的控制文件名称，修改主从数据库网络归档路径，然后启动从库实例，最后以Standby模式启动数据库：bash-3.00$ sqlplus / as sysdbaSQL*Plus: Release .0 - Production on Wed Aug 8 14:37:40 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning and Data Mining optionsSQL shutdown immediate;ORA-01109: database not openDatabase dismounted.ORACLE instance shut down.SQL startup nomount;ORACLE instance started.Total System Global Area 1258291200 bytesFixed Size 1978336 bytesVariable Size 318771232 bytesDatabase Buffers 922746880 bytesRedo Buffers 14794752 bytesSQL alter database mount standby database;Database altered.ALTER DATABASE ADD STANDBY LOGFILE group4 /oracle/oradata/cuug/redo04 SIZE 52428800;ALTER DATABASE ADD STANDBY LOGFILE group5 /oracle/oradata/cuug/redo05 SIZE 52428800;ALTER DATABASE ADD STANDBY LOGFILE group6 /oracle/oradata/cuug/redo06 SIZE 52428800;SQL alter database recover managed standby database disconnect from session;Database altered.观察从库的日志应用：Wed Aug 8 14:49:22 2007RFS1: Archived Log: /data2/ora10g/flash_recovery_area/STDBY/archivelog/2007_08_08/o1_mf_1_72_3clsvl86_.arcWed Aug 8 14:49:23 2007Clearing online redo logfile 3 completeMedia Recovery Log /data2/ora10g/flash_recovery_area/STDBY/archivelog/2007_08_08/o1_mf_1_67_3clr58pk_.arcWed Aug 8 14:49:37 2007Media Recovery Log /data2/ora10g/flash_recovery_area/STDBY/archivelog/2007_08_08/o1_mf_1_68_3clr5tqx_.arcWed Aug