11g dataguard使用总结(r5笔记第12天)

11g的dataguard相比于10g来说，最优越的特性应该算就是active dataguard了，这一点改进在很大意义上促使用户需要把数据库从10g升级到11g,读写分离在这个时候得到了升华，而且在后台会根据需要进行数据的同步，相比于使用10g，想读数据的时候把数据库启动到read only 阶段，但这个时候不接受日志同步数据，如果需要同步数据还需要把数据库再启动到mount阶段，感觉还是比较繁琐的。 11g的active dataurad功能很强大，同时搭建的时候使用rman 的duplicate选项在11g也得到了很大的改进，我们不需要专门去做一个备份，就可以直接在备库上通过duplicate把数据文件从主库传送到备库，同时做恢复，整个过程更加简洁。 当然了一般的系统是一主一备，关键的系统甚至要一主两备，如果系统多了，手工管理很容易搞糊涂，这个时候还是用加强版的dg broker来做吧，以前在10g的时候感觉还是手工管理，手工switchover，failover比较踏实，因为也在10g的过程中switchover,failover出现过一些问题，算是留下了一些阴影，到了11g的时候，工作中大量使用，就尝试着自己试了试，发现确实很好，而且搭建备库步骤比手工搭建要轻松的多。所以还是需要拥抱变化，勇敢接受新事物，一方面可以给自己疗伤，一方面恢复使用的信心，至少在灾难发生的时候回临危不乱。 我们来简单看一个例子，怎么结合dg broker来搭建dataguard,步骤相比手工要简化很多，而且可靠性要高很多，因为人为去做的检查点，在dg broker的检查中都会去做。 出了设置主库为归档模式，force logging之外。 我们所要做的就是配置网络监听。当然这个也不是难点，listener.ora tnsnames.ora主备是很相似的。 比如我们使用1523的端口在primary和standby之间互联 listener.ora 主库和备库的host部分不一样 LISTENER_1523 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.xxxxx.45)(PORT = 1523)) ) ) ) SID_LIST_LISTENER_1523 = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = test11g) (ORACLE_HOME = /DATA/app/oracle/product/11.2.0.4) (SID_NAME = test11g) ) ) tnsnames.ora 主库备库一致 TEST11G = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.xxxxx.45)(PORT = 1523)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test11g) ) ) STEST11G = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.xxxxxx.46)(PORT = 1523)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test11g) ) ) 然后就是使用tnsping开始检查，检查防火墙，等等 添加备库日志 主库查看日志情况，可以使用下面的方法检查一下。 SQL> select member,group#,status from v$logfile MEMBER GROUP# STATUS -------------------------------------------------- ---------- ------- /DATA/app/oracle/oradata/test11g/redo03.log 3 /DATA/app/oracle/oradata/test11g/redo02.log 2 /DATA/app/oracle/oradata/test11g/redo01.log 1 SQL> select group#,status,bytes,blocksize,status from v$log; GROUP# STATUS BYTES BLOCKSIZE STATUS ---------- ---------------- ---------- ---------- ---------------- 1 INACTIVE 52428800 512 INACTIVE 2 CURRENT 52428800 512 CURRENT 3 INACTIVE 52428800 512 INACTIVE 添加备库日志的语句类似： ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/DATA/app/oracle/oradata/test11g/redo04.log' SIZE 524288000; 修改下面的数据库参数， alter system set standby_file_management=auto scope=both; alter system set dg_broker_start=TRUE scope=both; alter system set local_listener=TEST11G scope=both; 检查dmon的情况 SQL> !ps -ef|grep dmon_test11g oracle 8129 1 0 11:25 ? 00:00:00 ora_dmon_test11g oracle 8138 8108 0 11:25 pts/0 00:00:00 /bin/bash -c ps -ef|grep dmon_test11g oracle 8140 8138 0 11:25 pts/0 00:00:00 grep dmon_test11g 查看dg broker的日志，开始的时候没有配置文件，会重新创建。 DMON: cannot open configuration file "/DATA/app/oracle/product/11.2.0.4/dbs/dr2test11g.dat" ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 DMON: cannot open configuration file "/DATA/app/oracle/product/11.2.0.4/dbs/dr2test11g.dat" ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Configuration does not exist, Data Guard broker ready 数据库alert日志： Mon Jul 27 11:25:15 2015 DMON started with pid=26, OS id=8129 Starting Data Guard Broker (DMON) Mon Jul 27 11:25:23 2015 接着拷贝密码文件，参数文件(spfile)到备库 在备库生成pfile,然后修改下面的参数 修改db_unique_name为stest11g 修改local_listener为stest11g 配置 db_file_name_convert log_file_name_convert db_file_name_convert='/DATA/app/oracle/oradata/test11g','/DATA/app/oracle/oradata/test11g','/DATA/app/oracle/fast_recovery_area/test11g','/DATA/app/oracle/fast_recovery_area/test11g' log_file_name_convert='/DATA/app/oracle/oradata/test11g','/DATA/app/oracle/oradata/test11g','/DATA/app/oracle/fast_recovery_area/test11g','/DATA/app/oracle/fast_recovery_area/test11g' 检查db_recovery_file_dest 和其它的文件路径是否有效(adump,oradata) fal_server fal_client 生成spfile 这个时候被库的工作就基本完成了，剩下的就是使用rman来恢复，dg broker来配置了。 数据复制恢复 启动备库到nomount阶段 SQL> create spfile from pfile; File created. SQL> startup nomount SQL> !ps -ef|grep dmon_test11g oracle 17773 1 0 11:44 ? 00:00:00 ora_dmon_test11g oracle 17779 17733 0 11:44 pts/0 00:00:00 /bin/bash -c ps -ef|grep dmon_test11g oracle 17781 17779 0 11:44 pts/0 00:00:00 grep dmon_test11g 检查参数是否生效 备库通过rman复制数据文件 $ rman target sys/xxxx@test11g auxiliary sys/xxxxx@stest11g nocatalog Recovery Manager: Release 11.2.0.4.0 - Production on Mon Jul 27 11:46:23 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: TEST11G (DBID=1038061657) using target database control file instead of recovery catalog connected to auxiliary database: TEST11G (not mounted) RMAN> duplicate target database for standby from active database nofilenamecheck; 配置完成，数据复制工作就告一段落。 开始配置dg broker 主库执行： $ dgmgrl / DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production Copyright (c) 2000, 2009, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected. DGMGRL> create configuration dg_test11g as > primary database is test11g > connect identifier is test11g; Configuration "dg_test11g" created with primary database "test11g" DGMGRL> add database stest11g as > connect identifier is stest11g > maintained as physical; Database "stest11g" added 这个时候配置还没有生效 DGMGRL> show configuration; Configuration - dg_test11g Protection Mode: MaxPerformance Databases: test11g - Primary database stest11g - Physical standby database Fast-Start Failover: DISABLED Configuration Status: DISABLED 启用配置 DGMGRL> enable configuration; 然后再次查看，如果配置没有问题，就会生效。 DGMGRL> show configuration; Configuration - dg_test11g Protection Mode: MaxPerformance Databases: test11g - Primary database stest11g - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS 接着进行再次验证 DGMGRL> enable database test11g; Enabled. DGMGRL> enable database stest11g; Enabled. DGMGRL> DGMGRL> show configuration; Configuration - dg_test11g Protection Mode: MaxPerformance Databases: test11g - Primary database stest11g - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS dataguard的搭建就完成了，可以在主库切换日志查看是否在备库数据库日志中会应用日志 Media Recovery Waiting for thread 1 sequence 12 (in transit) Recovery of Online Redo Log: Thread 1 Group 4 Seq 12 Reading mem 0 Mem# 0: /DATA/app/oracle/oradata/test11g/redo04.log 11g备库使用active dataguard特性。 SQL> alter database open; Database altered. SQL> recover managed standby database using current logfile disconnect from session; Media recovery complete. switchover的问题答疑 如果出现下面的错误，很可能当前操作系统用户下安装了多个数据库实例，取消默认的ORACLE_SID即可。 或者直接使用dgmgrl sys/oracle@test11g 这种方式连接 DGMGRL> switchover to 'stest11g'; Performing switchover NOW, please wait... Operation requires a connection to instance "test11g" on database "stest11g" Connecting to instance "test11g"... ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. connect to instance "test11g" of database "stest11g" 切换正常，但是需要手工启动备库，切换其实是正常的，需要手工启动一下主库 主要原因就是在listener.ora中需要配置一个xxxx_DGMGRL的global_name DGMGRL> switchover to stest11g Performing switchover NOW, please wait... Operation requires a connection to instance "test11g" on database "stest11g" Connecting to instance "test11g"... Connected. New primary database "stest11g" is opening... Operation requires startup of instance "test11g" on database "test11g" Starting instance "test11g"... Unable to connect to database ORA-12514: TNS:listener does not currently know of service requested in connect descriptor Failed. Warning: You are no longer connected to ORACLE. Please complete the following steps to finish switchover: start up instance "test11g" of database "test11g" 如果配置完全正确，正常切换的日志就会如下： DGMGRL> switchover to stest11g; Performing switchover NOW, please wait... Operation requires a connection to instance "test11g" on database "stest11g" Connecting to instance "test11g"... Connected. New primary database "stest11g" is opening... Operation requires startup of instance "test11g" on database "test11g" Starting instance "test11g"... ORACLE instance started. Database mounted. Database opened. Switchover succeeded, new primary is "stest11g" DGMGRL> 最后还是希望大家能够合理使用dataguard,一切顺利。