RedHat搭建物理Data Guard

我的搭建环境：两台机器均为：Red Hat Enterprise Linux Server release 5.4数据库版本为：Oracle10g10.2.0primary机上装oracle软件并创建数据库orclstandby机上只装oralce软件，无需装数据库基本配置：源数据库：IP：10.37.1.1数据库SID：orcl_pdb_unique_name：orcl1standby数据库：IP：10.37.1.2数据库SID：orcl_sdb_unique_name：orcl2配置步骤：1、配置primary数据库归档，并设置本地归档路径SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startup mountORACLE instance started.Total System Global Area 167772160 bytesFixed Size 1218316 bytesVariable Size 83888372 bytesDatabase Buffers 79691776 bytesRedo Buffers 2973696 bytesDatabase mounted.SQL> alter database archivelog;Database altered.SQL> alter database open;Database altered.SQL> alter system set log_archive_dest_1="location=/u01/arch";System altered.SQL> archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination /u01/archOldest online log sequence 2Next log sequence to archive 4Current log sequence 42、将primary数据库置于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、创建并修改primary数据库的初始化参数文件SQL> create pfile="/u01/p_pfile.ora" from spfile;File created.[oracle@localhost ~]$ vi /u01/p_pfile.ora修改如下内容：*.db_unique_name=orcl1*.log_archive_config="dg_config=（orcl1,orcl2）"*.log_archive_dest_2="service=orcl_s.2_tns arch valid_for=（online_logfiles,primary_role）db_unique_name=orcl2"*.log_archive_dest_state_2=defer*.fal_server=orcl_s.2_tns*.fal_client=orcl_p.1_tns*.db_file_name_convert="/u01/app/oracle/oradata/orcl", "/u01/app/oracle/oradata/orcl_s"*.log_file_name_convert="/u01/app/oracle/oradata/orcl", "/u01/app/oracle/oradata/orcl_s"*.standby_file_management=auto关闭数据库利用修改后的pfile创建spfileSQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> ！export ORACLE_SID=orcl_pSQL> create spfile from pfile="/u01/p_pfile.ora";File created.SQL> startupORACLE instance started.Total System Global Area 167772160 bytesFixed Size 1218316 bytesVariable Size 62916852 bytesDatabase Buffers 100663296 bytesRedo Buffers 2973696 bytesDatabase mounted.Database opened.4、创建备份：创建standby的控制文件：SQL> alter database create standby controlfile as "/u01/orcl2control01.ctl";Database altered.创建所有的数据文件备份（此处仅以users表空间下的数据文件为例，其他数据文件均要备份）SQL> alter tablespace users begin backup;Tablespace altered.SQL> ！cp /u01/app/oracle/oradata/orcl/users01.dbf /u01SQL> alter tablespace users end backup;Tablespace altered.5、配置primary监听和tns服务[oracle@localhost ~]$ cd $ORACLE_HOME/network/admin[oracle@localhost admin]$ vi listener.ora配置内容如下：SID_LIST_LISTENER = （SID_LIST = （SID_DESC = （SID_NAME = orcl_p）（ORACLE_HOME = /u01/app/oracle/10.2.0/db_1）（GLOBAL_DBNAME = orcl_p）））LISTENER = （DESCRIPTION_LIST = （DESCRIPTION = （ADDRESS = （PROTOCOL = TCP）（HOST = 10.37.1.1）（PORT = 1521））））配置tns[oracle@localhost admin]$ vi tnsnames.ora配置内容如下：orcl_p.1_tns = （DESCRIPTION = （ADDRESS_LIST = （ADDRESS = （PROTOCOL = TCP）（HOST = 10.37.1.1）（PORT = 1521）））（CONNECT_DATA = （SID = orcl_p）（SERVER = DEDICATED）））orcl_s.2_tns = （DESCRIPTION = （ADDRESS_LIST = （ADDRESS = （PROTOCOL = TCP）（HOST = 10.37.1.2）（PORT = 1521）））（CONNECT_DATA = （SID = orcl_s）（SERVER = DEDICATED）））此时重启监听后，测试源数据库tns配置[oracle@localhost admin]$ lsnrctl stopLSNRCTL for Linux: Version 10.2.0.1.0 - Production on 26-FEB-2014 19:11:44Copyright （c） 1991, 2005, Oracle. All rights reserved.Connecting to （DESCRIPTION=（ADDRESS=（PROTOCOL=TCP）（HOST=10.37.1.1）（PORT=1521）））The command completed successfully[oracle@localhost admin]$ lsnrctl startLSNRCTL for Linux: Version 10.2.0.1.0 - Production on 26-FEB-2014 19:11:53Copyright （c） 1991, 2005, Oracle. All rights reserved.Starting /u01/app/oracle/10.2.0/db_1/bin/tnslsnr: please wait...TNSLSNR for Linux: Version 10.2.0.1.0 - ProductionSystem parameter file is /u01/app/oracle/10.2.0/db_1/network/admin/listener.oraLog messages written to /u01/app/oracle/10.2.0/db_1/network/log/listener.logListening on: （DESCRIPTION=（ADDRESS=（PROTOCOL=tcp）（HOST=10.37.1.1）（PORT=1521）））Connecting to （DESCRIPTION=（ADDRESS=（PROTOCOL=TCP）（HOST=10.37.1.1）（PORT=1521）））STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version 10.2.0.1.0 - ProductionStart Date 26-FEB-2014 19:11:53Uptime 0 days 0 hr. 0 min. 0 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /u01/app/oracle/10.2.0/db_1/network/admin/listener.oraListener Log File /u01/app/oracle/10.2.0/db_1/network/log/listener.logListening Endpoints Summary... （DESCRIPTION=（ADDRESS=（PROTOCOL=tcp）（HOST=10.37.1.1）（PORT=1521）））Services Summary...Service "orcl_p" has 1 instance（s）. Instance "orcl_P", status UNKNOWN, has 1 handler（s） for this service...The command completed successfully[oracle@localhost admin]$ tnsping orcl_p.1_tnsTNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 26-FEB-2014 19:12:38Copyright （c） 1997, 2005, Oracle. All rights reserved.Used parameter files:/u01/app/oracle/10.2.0/db_1/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting to contact （DESCRIPTION = （ADDRESS_LIST = （ADDRESS = （PROTOCOL = TCP）（HOST = 10.37.1.1）（PORT = 1521）））（CONNECT_DATA = （SID = orcl_p）（SERVER = DEDICATED）））OK （0 msec）6、standby数据库创建目录结构，并将源数据库的参数文件、备份的控制文件、创建的口令文件copy到相应的位置7、[oracle@localhost ~]$ mkdir -p /u01/arch2[oracle@localhost u01]$ mkdir -p /u01/app/oracle/flash_recovery_area[oracle@localhost ~]$ mkdir -p /u01/app/oracle/oradata/orcl_s[oracle@localhost ~]$ mkdir -p /u01/app/oracle/admin/orcl_s/{a,b,c,u}dump[oracle@localhost ~]$ scp 10.37.1.1:/u01/*.dbf /u01/app/oracle/oradata/orcl_s/oracle@10.37.1.1"s password: example01.dbf 100% 100MB 20.0MB/s 00:05 sysaux01.dbf 100% 240MB 16.0MB/s 00:15 system01.dbf 100% 480MB 14.6MB/s 00:33 undotbs01.dbf 100% 30MB 15.0MB/s 00:02 users01.dbf 100% 5128KB 5.0MB/s 00:01 [oracle@localhost ~]$ scp 10.37.1.1:/u01/*.ctl /u01/app/oracle/oradata/orcl_s/oracle@10.37.1.1"s password: orcl2control01.ctl 100% 6896KB 6.7MB/s 00:00 [oracle@localhost ~]$ scp 10.37.1.1:/u01/p_pfile.ora /u01s_pfile.oraoracle@10.37.1.1"s password: p_pfile.ora 100% 1508 1.5KB/s 00:00 [oracle@localhost ~]$ scp 10.37.1.1:/u01/app/oracle/10.2.0/db_1/dbs/orapworcl_p /u01/app/oracle/10.2.0/db_1/dbs/orapworcl_soracle@10.37.1.1"s password: orapworcl_p 100% 5120 5.0KB/s 00:007、??改standby数据库的sid为orcl_s并配置copy来的参数文件[root@localhost ~]# vi /u01/s_pfile.ora需要修改的内容如下：（没必要照搬，可根据自己的实际情况自行修改，注意红色部分是重点修改的地方）*.audit_file_dest="/u01/app/oracle/admin/orcl_s/adump"*.background_dump_dest="/u01/app/oracle/admin/orcl_s/bdump"*.control_files="/u01/app/oracle/oradata/orcl_s/orcl2control01.ctl","/u01/app/oracle/oradata/orcl_s/orcl2control02.ctl","/u01/app/oracle/oradata/orcl_s/orcl2control03.ctl"*.core_dump_dest="/u01/app/oracle/admin/orcl_s/cdump"*.user_dump_dest="/u01/app/oracle/admin/orcl_s/udump"*.db_unique_name=orcl2*.log_archive_dest_1="location=/u01/arch2"*.log_archive_dest_2="service=orcl_p.1_tns arch valid_for=（online_logfiles, primary_role） db_unique_name=orcl1"*.log_archive_dest_state_2=enable*.fal_server=orcl_p.1_tns*.fal_client=orcl_s.2_tns8、配置standby数据库的监听和服务名[oracle@localhost admin]$ cd $ORACLE_HOME/network/admin[oracle@localhost admin]$ vi listener.ora # listener.ora Network Configuration File: /u01/app/oracle/10.2.0/db_1/network/admin/listener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER = （SID_LIST = （SID_DESC = （SID_NAME = orcl_s）（ORACLE_HOME = /u01/app/oracle/10.2.0/db_1）（GLOBAL_DBNAME = orcl_s）））LISTENER = （DESCRIPTION_LIST = （DESCRIPTION = （ADDRESS = （PROTOCOL = TCP）（HOST = 10.37.1.2）（PORT = 1521））））[oracle@localhost admin]$ vi tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/10.2.0/db_1/network/admin/tnsnames.ora# Generated by Oracle configuration tools.orcl_s.2_tns = （DESCRIPTION = （ADDRESS_LIST = （ADDRESS = （PROTOCOL = TCP）（HOST = 10.37.1.2）（PORT = 1521）））（CONNECT_DATA = （SID = orcl_s）（SERVER = DEDICATED）））orcl_p.1_tns = （DESCRIPTION = （ADDRESS_LIST = （ADDRESS = （PROTOCOL = TCP）（HOST = 10.37.1.1）（PORT = 1521）））（CONNECT_DATA = （SID = orcl_p）（SERVER = DEDICATED）"tnsnames.ora" 36L, 764C 配置完成后重启监听服务[oracle@localhost admin]$ lsnrctl stop[oracle@localhost admin]$ lsnrctl start至此监听和服务配置完成，在primary和standby端用tnsping命令应该能ping都通两个服务，能远程登入两数据库视为配置成功SQL> conn sys/oracle@orcl_p.1_tns as sysdbaConnected.SQL> conn sys/oracle@orcl_s.2_tns as sysdbaConnected to an idle instance.9、配置stanby数据库并启动到mount状态，并接受归档文件任意终端连接到standby数据库[oracle@localhost ~]$ sqlplus sys/oracle@orcl_s.2_tns as sysdba利用s_pfile.ora常见standby的spfileSQL> create spfile from pfile="/u01/s_pfile.ora";File created.SQL> startup mountORACLE instance started.Total System Global Area 167772160 bytesFixed Size 1218316 bytesVariable Size 62916852 bytesDatabase Buffers 100663296 bytesRedo Buffers 2973696 bytesDatabase mounted.连接到primary数据库并设置远程归档路径开启SQL> conn sys/oracle@orcl_p.1_tns as sysdbaConnected.SQL> alter system set log_archive_dest_state_2=enable;System altered.查看归档接受情况SQL> select max（sequence#） from v$archived_log;MAX（SEQUENCE#）-------------- 4SQL> conn sys/oracle@orcl_s.2_tns as sysdbaConnected.SQL> select max（sequence#） from v$archived_log;MAX（SEQUENCE#）-------------- 4查看standby的归档路径下是否有源数据库传来的归档日志SQL> ！ls /u01/arch21_4_840520047.dbf10、primary数据插入，测试standby数据库能否正常接受primary端创建表并插入数据SQL> conn scott/tigerConnected.SQL> create table DG_TEST（ID VARCHAR2（10））;Table created.SQL> insert into DG_TEST 2 values （"DG_TEST"） 3 /1 row created.SQL> /1 row created.SQL> /1 row created.SQL> /1 row created.SQL> select * from DG_TEST;ID----------DG_TESTDG_TESTDG_TESTDG_TESTSQL> commit;Commit complete.切换归档日志，使当前日志归档SQL> conn / as sysdbaConnected.SQL> alter system switch logfile;System altered.standby启动redo应用（这也正是物理standby的工作方法，等于是从获取的归档中执行恢复操作，来保持与源数据库一致）SQL> alter database recover managed standby database disconnect from session;Database altered.打开数据库前必须停止redo应用SQL> alter database recover managed standby database cancel;Database altered.打开standby数据，查看是否有源数据库新插入的数据SQL> select * from scott.DG_TEST;ID----------DG_TESTDG_TESTDG_TESTDG_TEST数据成功传入standby数据库，物理的DG搭建成功！--------------------------------------分割线 --------------------------------------Oracle Data Guard 重要配置参数 http://www.linuxidc.com/Linux/2013-08/88784.htm基于同一主机配置 Oracle 11g Data Guard http://www.linuxidc.com/Linux/2013-08/88848.htm探索Oracle之11g DataGuard http://www.linuxidc.com/Linux/2013-08/88692.htmOracle Data Guard （RAC+DG）归档删除策略及脚本 http://www.linuxidc.com/Linux/2013-07/87782.htmOracle Data Guard 的角色转换 http://www.linuxidc.com/Linux/2013-06/86190.htmOracle Data Guard的日志FAL gap问题 http://www.linuxidc.com/Linux/2013-04/82561.htmOracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby 处理方法 http://www.linuxidc.com/Linux/2013-03/82009.htm--------------------------------------分割线 --------------------------------------更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址