1、DATAGUARD配置手册一、 DATAGUARD 体系架构图二、 配置步骤1. 首先在节点 1 用 dbca 创建实例，节点 2 不安装实例，只装数据库软件即可，如果节点 1 是现成运行的数据库，则不需要新建;2. 把主节点 1 的 TNSNAMES.ORA 配置成如下，并把该文件复制到备节点 2 的相同目录下：ORCL =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.14.13)(PORT = 1521)(CONNECT_DATA =(SERVICE_NAME = orcl)ORCLBAK =
2、(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.14.12)(PORT = 1521)(CONNECT_DATA =(SERVICE_NAME = orclbak)注:这里要注意红色部分，要根据自己的情况对 IP 和服务名进行调整；3. 配置两个节点的监听：listener.ora 设置如下(用现成的监听文件即可，无需修改)：主节点 1 设置：LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = s
3、hck70data279)(PORT = 1521)(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)红色部分位节点 1 的主机名，确认好配置后把这个监听文件拷贝到节点 2 相同的目录下，并调整：备节点 2 设置：LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = shck70data280)(PORT = 1521)(DESCRIPTION =(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)注：这里将红色
4、的主机名改为节点 2 的主机名4. 主节点设置强制写日志SQL select FORCE_LOGGING from v$database;NOSQL alter database force logging;SQL select FORCE_LOGGING from v$database;YES5. 把主节点的密码文件拷到备节点相同的目录下（名字要一样，别改名，本例是 pwdorcl.ora 文件）：6. 主节点创建 PFILE 并增加修改如下：SQLcreate pfile from spfle;找到 initorcl.ora 文件：orcl._db_cache_size=525126860
5、8orcl._java_pool_size=16777216orcl._large_pool_size=16777216orcl._oracle_base=E:u01#ORACLE_BASE set from environmentorcl._pga_aggregate_target=2147483648orcl._sga_target=6408896512orcl._shared_io_pool_size=0orcl._shared_pool_size=1056964608orcl._streams_pool_size=16777216*.audit_file_dest=E:u01admin
6、orcladump*.audit_trail=db*.compatible=11.2.0.0.0*.control_files=E:u01oradataorclcontrol01.ctl,E:u01fast_recovery_areaorclcontrol02.ctl*.db_block_size=8192*.db_domain=*.db_name=orcl*.db_recovery_file_dest=E:u01fast_recovery_area*.db_recovery_file_dest_size=52428800000*.diagnostic_dest=E:u01*.dispatch
7、ers=(PROTOCOL=TCP) (SERVICE=orclXDB)*.log_archive_format=ARC%S_%R.%T*.nls_language=SIMPLIFIED CHINESE*.nls_territory=CHINA*.open_cursors=300*.pga_aggregate_target=2131755008*.processes=1500*.remote_login_passwordfile=EXCLUSIVE*.sessions=1655*.sga_target=6396313600*.undo_tablespace=UNDOTBS1-下面为增加的内容：
8、DB_UNIQUE_NAME=orclLOG_ARCHIVE_CONFIG=DG_CONFIG=(orcl,orclbak) LOG_ARCHIVE_DEST_1= LOCATION=E:u01fast_recovery_areaorcl VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl LOG_ARCHIVE_DEST_2= SERVICE=orclbak LGWR ASYNC affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclbak LOG_ARC
9、HIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc FAL_SERVER=orclbakFAL_CLIENT=orclSTANDBY_FILE_MANAGEMENT=AUTO 7. 在备端创建几个跟主端同样的目录：E:u01oradataorcl, E:u01fast_recovery_areaorcl,E:u01adminorcladump,E:u01adminorclbdump,E:u01adminorclpfile然后创建 STANDBY 控制文件并拷贝至备端的相
10、同目录SQLALTER DATABASE CREATE STANDBY CONTROLFILE AS e:control01.ctl;然后把 E:control01.ctl 复制到备端的 E:u01oradataorcl 下8. 根据 6 的参数文件内容，拷贝到到备端，并增加修改内容如下（记住 pfile 文件名要一样，也是叫 initorcl.ora,路径是：E:u01product11.2.0dbhome_1databaseinitorcl.ora ）：备库的参数文件如下：orcl._db_cache_size=5251268608orcl._java_pool_size=16777216
11、orcl._large_pool_size=16777216orcl._oracle_base=E:u01#ORACLE_BASE set from environmentorcl._pga_aggregate_target=2147483648orcl._sga_target=6408896512orcl._shared_io_pool_size=0orcl._shared_pool_size=1056964608orcl._streams_pool_size=16777216*.audit_file_dest=E:u01adminorcladump*.audit_trail=db*.com
12、patible=11.2.0.0.0*.control_files=E:u01oradataorclcontrol01.ctl -这里不同于主端，注意只指定一个控制文件即可*.db_block_size=8192*.db_domain=*.db_name=orcl*.db_recovery_file_dest=E:u01fast_recovery_area*.db_recovery_file_dest_size=524288000000*.diagnostic_dest=E:u01*.dispatchers=(PROTOCOL=TCP) (SERVICE=orclXDB)*.log_archi
13、ve_format=ARC%S_%R.%T*.nls_language=SIMPLIFIED CHINESE*.nls_territory=CHINA*.open_cursors=300*.pga_aggregate_target=2131755008*.processes=1500*.remote_login_passwordfile=EXCLUSIVE*.sessions=1655*.sga_target=6396313600*.undo_tablespace=UNDOTBS1-调整以下内容：DB_UNIQUE_NAME=orclbakLOG_ARCHIVE_CONFIG=DG_CONFI
14、G=(orcl,orclbak) LOG_ARCHIVE_DEST_1= LOCATION=E:u01fast_recovery_areaorcl VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orclbak LOG_ARCHIVE_DEST_2= SERVICE=orcl LGWR ASYNC affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2
15、=ENABLE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc FAL_SERVER=orclFAL_CLIENT=orclbakSTANDBY_FILE_MANAGEMENT=AUTO 9. 关闭主端数据库并把数据文件拷贝至备端的相同目录下：SQLshutdown immediate然后把主端的 E:u01oradataorcl 目录复制到备端同样的目录下,这里千万要注意，控制文件千万别复制过去，因为步骤 7 已将 standby 控制文件复制过去；10. 在主，备端同时建立 standby 重做日志：主端此时 OPEN 数据库，然后创建以下日志，SQLstatupSQLAL
16、TER DATABASE ADD STANDBY LOGFILE group 4(E:u01oradatastandby_logslog1.rdo) SIZE 200M;SQLALTER DATABASE ADD STANDBY LOGFILE group 5(E:u01oradatastandby_logslog2.rdo) SIZE 200M;SQLALTER DATABASE ADD STANDBY LOGFILE group 6(E:u01oradatastandby_logslog3.rdo) SIZE 200M;SQLALTER DATABASE ADD STANDBY LOGFI
17、LE group 7(E:u01oradatastandby_logslog4.rdo) SIZE 200M;备端先 mount 起来再创建：SQLstarup nomount SQLalter database mount;SQLALTER DATABASE ADD STANDBY LOGFILE group 4(E:u01oradatastandby_logslog1.rdo) SIZE 200M;SQLALTER DATABASE ADD STANDBY LOGFILE group 5(E:u01oradatastandby_logslog2.rdo) SIZE 200M;SQLALTE
18、R DATABASE ADD STANDBY LOGFILE group 6(E:u01oradatastandby_logslog3.rdo) SIZE 200M;SQLALTER DATABASE ADD STANDBY LOGFILE group 7(E:u01oradatastandby_logslog4.rdo) SIZE 200M;11. 备端进行同步操作SQLshutdown immediate;SQLstartup nomount;SQLalter database mount standby database;SQLALTER DATABASE RECOVER MANAGED
19、 STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;此时先用步骤 12，13 查看 DG 是否同步正常，如正常（这里判断的方法是：用步骤 13 语句在主端看是否有 LNS 字样的同步日志出现，如出现 ERROR 则需要诊断不同步的原因，另外用步骤 12 在备端查看归档日志是否处于 YES 的应用状态） ，可进行下列操作切换到 OPEN 状态下同步：要切换到 OPEN READ ONLY 状态下的操作：SQLalter database recover managed standby database cancel;SQ
20、Lalter database open read only;其实在 open read only 状态下也可以进行日志同步：ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;这样就可以既可以实时查数据，也可以同步数据-到此配置完毕，以下为内容为维护 DATAGUARD 使用：12. 查看备库日志应用状态：SQLSELECT SEQUENCE#, FIRST_TIME, NEXT_TIME,applied FROM V$ARCHIVED_LOG ORDER
21、BY SEQUENCE#;.13. 查看主库日志归档情况：SQLset lines 180SQLcol message format a70SQLselect * from V$DATAGUARD_STATUS;14. 查看数据库主备状态：SQLSELECT SWITCHOVER_STATUS FROM V$DATABASE;15. 查看备库 standby_log 状态：SQLselect thread#,sequence#,used,archived,status from v$standby_log;16. 查看归档文件是否连续 SQL select thread#,low_sequence#,high_sequence# from v$archive_gap;
