ORACLE之Data-Guard部署

1、Oracle之Data Guard部署序言Oracle灾备计划分为三种：集群服务冷备、RAC热备和Data Guard主备服务。前两种都需要共享磁盘阵列，是适合于本地灾备，而Data Guard没有地域限制，甚至可以主服务器放在上海，备服务器放在北京。备服务器主服务器集群方式：磁盘为两台服务器都能看到，通常主服务器工作，使用磁盘中的数据文件，集群软件（第三方软件，Windows2003就带）检测主服务器上的ORACLE进程，一旦异常，它就停主服务器并启动备用服务器上ORACLE实例，由它接管数据文件。磁盘阵列RAC方式：磁盘为两台服务器都能看到，服务器同时工作，服务器之间通过某种机制来管理锁，

2、保证不会同时访问文件冲突和事务的一致性。可以是多台，每台都在工作状态，有载荷。一台服务器损坏退出，不影响服务器组中的其他服务器磁盘阵列服务器二服务器一如上图DataGuard是通过重做日志来实现的，主服务器的任何数据变化都会在重做日志里留下记录（便于数据恢复，通常有两组日志，一组满就切换到另一组），主服务器必须工作在归档模式下，因为重做日志推荐精选在切换后就把以前的日志覆盖了，如果在归档模式下，发生切换时会把重做日志保存到文件，通常由物理备份文件+归档重做日志+重做日志可以将数据库恢复到物理备份后的任一时间点状态。归档日志会通过TNS网络传递到备用服务器，备用服务器应用这些归档的重做日志，就使

3、备用服务器模拟主用服务器发生同样的变化，使备用服务器上的数据和主用服务器完全一致。主备用服务器只要打一条命令就可以方便的进行角色转换，主变备，备变主。DataGuard的准备1.主库和备库oracle版本一致，运行在相同平台如linux，服务器硬件可以不一样。2.Primary 数据库必须运行于归档模式，并且务必确保在primary 数据库上打开FORCE LOGGING，以避免用户通过nologging 等方式不写redo 造成对应的操作无法传输到standby 数据库。3.Primary 和standby 数据库均可应用于单实例或RAC 架构下，并且同一个data guard 配置可以混合

4、使用逻辑standby 和物理standby。4.建议数据库必须采用相同的存储架构。比如存储采用ASM/OMF 的话，那不分primarty 或是standby也都需要采用ASM/OMF。5.standby库的环境和primary一致。二、调整主库1. 确保数据库运行在archivelog模式下SQL> archive log list; Database log mode No Archive ModeAutomatic archival DisabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log seq

5、uence 2Current log sequence 4把数据库调整为archivelog模式下SQL> shutdown immediateSQL> startup mountSQL> alter database archivelog ;SQL> alter database open;2.将主数据库改为强制产生日志模式alter database force logging;推荐精选3.查看当前日志文件组SQL>select group#,member from v$logfile order by group#; GROUP# STATUS TYPE M

6、EMBER IS_ - - - - - 1 ONLINE D:APPADMINISTRATORORADATAJRWXREDO01.LOG NO 2 ONLINE D:APPADMINISTRATORORADATAJRWXREDO02.LOG NO 3 ONLINE D:APPADMINISTRATORORADATAJRWXREDO03.LOG NO4.在主库上创建备用日志 （为切换而用）SQL>alter database recover managed standby database cancel;#未执行SQL>ALTER DATABASE ADD STANDBY LOGFI

7、LE GROUP 4 ('D:APPADMINISTRATORORADATAJRWXSTAN04.LOG') SIZE 50M;SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('D:APPADMINISTRATORORADATAJRWXSTAN05.LOG') SIZE 50M;SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('D:APPADMINISTRATORORADATAJRWXSTAN06.LOG') SIZE 50M;SQL>

8、;alter database recover managed standby database disconnect from session;#未执行5.创建standby控制文件:SQL>alter database create standby controlfile as 'd:standby.ctl'6. 创建密码密码文件存放于D:appAdministratorproduct11.1.0db_1database目录,文件名为PWDjrwx.ora(jrwx为实例名),如果目录下找不到，则手动创建，创建命令为：orapwd file=D:appAdminist

9、ratorproduct11.1.0db_1databasePWDjrwx.ora password=oracle entries=107. 修改主库的初始化参数SQL>create pfile='d:initora.ora' from spfile; #创建参数文件编辑以上创建的参数文件，添加以下内容*.db_unique_name ='primary' #tns中的名称*.archive_lag_target = 1830*.log_archive_config='DG_CONFIG=(primary,standby)' *.log_a

10、rchive_dest_1='location=d:appadministratororadatajrwx VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'*.log_archive_dest_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby' *.log_archive_dest_state_1= 'enable' #默认就是enable*.

11、log_archive_dest_state_2= 'enable'*.log_archive_format='%t_%s_%r.dbf'推荐精选/*以下部分为主机切换为备库使用*/*.fal_server='primary'*.fal_client='standby'*.standby_file_management='auto'/*如果主备库文件或路径不同*/*.db_file_name_convert='d:appadministratororadatajrwx','d:appadmi

12、nistratororadatajrwx'*.log_file_name_convert='d:appadministratororadatajrwx','d:appadministratororadatajrwx'建议参数文件使用spfile，这样可以在不停库使用命令修改参数。8.使用修改过的参数文件启动主库:startup pfile='d:initora.ora' nomount;create spfile from pfile='d:initora.ora' shutdown immediate;startup;9

13、.配置主库静态网络监听(备用库同理)修改listener.ora文件,内容如下（SID必须指定否则可能会影响TNS访问）:# File: d:appAdministratorproduct11.1.0db_1networkadminlistener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521) ) ) ) SID_LIS

14、T_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = d:appAdministratorproduct11.1.0db_1) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = jrwx) (ORACLE_HOME = d:appAdministratorproduct11.1.0db_1) (SID_NAME = jrwx) ) )推荐精选修改tnsnames.ora文件,内容如下:primary = (DESCRIPTION = (ADDRE

15、SS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = jrwx) ) ) standby = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db2)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = jrwx) ) )10.开启监听,用网络名连接测试监听是否正常。lsnrctl start;sqlplus usern

16、ame/pwdprimary as sysdbaselect * from user_segments;三、创建standby database1. 拷贝文件（先shutdown immediate）a.将d:appAdministrator目录下admin、cfgtollogs、flash_recovery_area和diag目录拷至备用库b.将d:appAdministratororadatajrwx目录拷至备用库的d盘下c.将d:appAdministratorproduct11.1.0db_1databasePWDjrwx.ora文件拷至备用库的d盘下d.将standby.ctl拷至备

17、用库的d:appAdministratororadatajrwx目录下,并分别复制为control01.ctl,control02.ctl,control03.ctl.e.将d:initora.ora文件拷至备用库的d盘下供以下编辑2. 配置standby database的listener.ora 和tnsnames.ora文件同主库重起监听并检验是否可以tnsping通3.在windows上注意注册oracle实例到服务中,具体命令如下: oradim -new -sid jrwx(实例名)4. 修改standby database初始化参数*.db_unique_name ='s

18、tandby' #tns中的名称*.archive_lag_target = 1800推荐精选*.log_archive_config='DG_CONFIG=(primary,standby)' *.log_archive_dest_1='location=d:appadministratororadatajrwx VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'*.log_archive_dest_2='SERVICE=standby LGWR ASYNC VALID_FOR=

19、(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary' *.log_archive_dest_state_1= 'enable' #默认就是enable*.log_archive_dest_state_2= 'enable'*.log_archive_format='%t_%s_%r.dbf'/*以下部分为主机切换为备库使用*/*.fal_server='standby'*.fal_client='primary'*.standby_file_manag

20、ement='auto'/*如果主备库文件或路径不同*/*.db_file_name_convert='d:appadministratororadatajrwx','d:appadministratororadatajrwx'*.log_file_name_convert='d:appadministratororadatajrwx','d:appadministratororadatajrwx'修改完之后用如下语句查看SQL> select * from V$DATAGUARD_CONFIG;DB_UNI

21、QUE_NAMEprimarystandby5.启动备用节点:regedit 加个ORACLE_HOME=jrwx，否则可能会出错startup nomount pfile='d:initora.ora'create spfile from pfile='d:initora.ora'shutdown immediate;startup nomount;6.加截数据库为备用库模式.alter database mount standby database;7.开始Redo应用主库SQL> alter system archive log current;备库

22、，11g可以只读打开库应用日志SQL> alter database open read only;SQL> alter database recover managed standby database disconnect from session;也可以使用alter database recover managed standby database using current logfile disconnect from session;应用日志。没有using current logfile是去apply standby archived log.推荐精选有using

23、current logfile是当日志传到standby redo log的时候就去应用，是real-time的，必须要有standby redo log.8.查看配置是否成功: a.查看切换状态:  select switchover_status from v$database;b.手动切换归档,查看归档文件是否自动同步到备用库.  切换归档:alter system switch logfile; #主库c.在主库和备库上查看归档日志是否同步:SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#

24、;如果查看从库可以看到归档日志,表示归档日志同步成功,如果APPLIED列的值为yes,表示重做应用成功.主库从库:四.备用服务器的管理模式与只读模式推荐精选1.启动到管理模式SQL>shutdown immediate;SQL>startup nomount;SQL>alter database mount standby database;SQL>alter database recover managed standby database disconnect from session;2.启动到只读方式SQL>shutdown immediate;SQL&

25、gt;startup nomount;SQL>alter database mount standby database;SQL>alter database open read only;（3）如果在管理恢复模式下到只读模式SQL> recover managed standby database cancel;SQL> alter database open read only;这个时候，可以给数据库增加临时数据文件（这个在热备份的时候是没有备份过来的）如alter tablespace temp add tempfile '/u02/oradata/tes

26、t/temp01.dbf' size 100M;5.从只读方式到管理恢复方式SQL> recover managed standby database disconnect from session;五.主备切换主库lsnrctl stopSQL> alter database commit to switchover to physical standby with session shutdown;SQL> shutdown immediate;SQL> startup mount;SQL> alter database recover managed

27、 standby database disconnect;lsnrctl start备库推荐精选SQL> alter database commit to switchover to primary;SQL> shutdown immediate;SQL> startup附：主库initora.orajrwx._db_cache_size= 838860800jrwx._java_pool_size= 33554432jrwx._large_pool_size=8388608jrwx._oracle_base='D:appAdministrator'#ORAC

28、LE_BASE set from environmentjrwx._pga_aggregate_target=0jrwx._sga_target=0jrwx._shared_io_pool_size=0jrwx._shared_pool_size=243269632jrwx._streams_pool_size=0*.audit_file_dest='D:appAdministratoradminjrwxadump'*.audit_trail='db'*.compatible='.0'*.control_files='D:

29、appAdministratororadatajrwxcontrol01.ctl','D:appAdministratororadatajrwxcontrol02.ctl','D:appAdministratororadatajrwxcontrol03.ctl'*.db_block_size=8192*.db_domain=''*.db_name='jrwx'*.db_recovery_file_dest='D:appAdministratorflash_recovery_area'*.db_recover

30、y_file_dest_size=2147483648*.diagnostic_dest='D:appAdministrator'*.dispatchers='(PROTOCOL=TCP) (SERVICE=jrwxXDB)'*.log_archive_format='ARC%S_%R.%T'*.memory_target= 2147483648*.nls_language='SIMPLIFIED CHINESE'*.nls_territory='CHINA'*.open_cursors=300*.processe

31、s=1500*.remote_login_passwordfile='EXCLUSIVE'*.undo_tablespace='UNDOTBS1'*.db_unique_name='primary'*.archive_lag_target=1800*.fal_client='standby'*.fal_server='primary'推荐精选*.log_archive_config='DG_CONFIG=(primary,standby)'*.log_archive_dest_1='LOCA

32、TION=d:appadministratororadatalog VALID_FOR=(all_logfiles,all_roles) db_unique_name=primary'*.log_archive_dest_2='service=standby arch async valid_for=(online_logfiles,primary_role) db_unique_name=standby'*.log_archive_dest_state_1='enable'*.log_archive_dest_state_2='enable&#

33、39;*.log_archive_format='%t_%s_%r.dbf'*.DB_FILE_NAME_CONVERT='d:appadministratororadatajrwx','d:appadministratororadatajrwx'*.LOG_FILE_NAME_CONVERT='d:appadministratororadatalog','d:appadministratororadatalog'*.standby_file_management='auto'备库initora.o

34、rajrwx._db_cache_size= 838860800jrwx._java_pool_size= 33554432jrwx._large_pool_size=8388608jrwx._oracle_base='D:appAdministrator'#ORACLE_BASE set from environmentjrwx._pga_aggregate_target=0jrwx._sga_target=0jrwx._shared_io_pool_size=0jrwx._shared_pool_size=243269632jrwx._streams_pool_size=0

35、*.audit_file_dest='D:appAdministratoradminjrwxadump'*.audit_trail='db'*.compatible='.0'*.control_files='D:appAdministratororadatajrwxcontrol01.ctl','D:appAdministratororadatajrwxcontrol02.ctl','D:appAdministratororadatajrwxcontrol03.ctl'*.db_block_size=8192*.db_domain=''*.db_name='jrwx'*.db_recovery_file_dest='D:appAdministratorflash_recovery_area'*.db_recovery_file_dest_size=2147483648*.diagnostic_dest='D:appAdministrator'*.dispatchers='(PROTOCOL=TCP) (