DATA GUARD配置过程
ORACLEG搭建DATAGUARD步骤
ORACLEG搭建DATAGUARD步骤搭建Oracle11g Data Guard需要以下步骤：1. 确保主/备数据库的版本和配置要求：首先，主数据库和备数据库的Oracle版本必须匹配，且需要安装和配置相同的操作系统。
同时，还需要确保主/备数据库的参数设置和硬件要求是一致的。
2. 创建主数据库：使用Oracle Database ConfigurationAssistant（DBCA）创建主数据库。
在创建数据库过程中，确保将主数据库配置为运行在归档日志模式下，以便能够启用数据保护模式并实现归档日志传送到备库。
3. 备份主数据库：在搭建Data Guard之前，需要对主数据库进行备份。
这样可以确保在搭建过程中出现任何问题时，能够快速还原主数据库。
4. 安装备数据库：在备数据库所在的服务器上安装Oracle软件，并确保配置与主数据库相同的操作系统和Oracle版本。
5. 配置备数据库参数：在备数据库上修改参数文件，以确保与主数据库保持一致。
将数据库配置为运行在归档日志模式下，并启用自动归档和数据保护模式。
还需要配置Log Archive Destination（LAD）和Fast Recovery Area（FRA）。
6. 启动备库模式：在备库上启动数据库，并将数据库模式设置为mount。
执行以下命令启动数据库：```startup mount;```7.创建备数据库控制文件：在备库上创建控制文件的物理副本，以便能够使用这些文件启动和恢复备库。
执行以下命令创建备数据库控制文件副本：```ALTER DATABASE CREATE STANDBY CONTROLFILE AS'path_to_control_file_copy';```8.将备库注册到主数据库：在主数据库上执行以下命令，将备库注册到主数据库：```ALTER SYSTEM SETLOG_ARCHIVE_CONFIG='DG_CONFIG=(primary_database,standby_datbase)';ALTER SYSTEM SETLOG_ARCHIVE_DEST_2='SERVICE=standby_database LGWR ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=standby_database';```9.复制主数据库备份到备数据库服务器上：将主数据库备份传输到备数据库所在的服务器。
Oracle_Data_Guard配置步骤
Oracle Data Guard配置步骤一：测试环境主数据库：windows English XP;版本ora9i（9.2.0.1.0）；IP地址192.168.1.38；主机名：CISCOSYS；实例SID：PRIMARY备用数据库：windows English XP;版本ora9i（9.2.0.1.0）；IP地址192.168.1.111；主机名：CISCOROUTER；实例SID：STANDBY二：运行Data Guard的条件1、在主数据库和备用数据库的所有机器上必须安装同一个版本的Oracle企业版。
：SELECT * FROM V$VERSION；2、主数据库必须运行在归档模式下，推荐主和备用数据库都是force Logging模式，这样方便双向自由切换：ALTER DATABASE FORCE LOGGING;3、主数据库和备用数据库的操作系统必须一样（允许版本不同），备用数据库可以使用与主数据库不同的目录结构，ORACLE推荐使用相同的目录结构。
4、主备用数据库硬件系统的体系结构必须相同，但允许主备数据库硬件的配置可以不同如：CPU数量、内存大小、存储配置等。
5、主备数据库可以是单实例的数据库，也可以是多实例的RAC数据库。
6、每个主备数据库必须有它自己的控制文件，ORACLE NET不同，必须分别修改。
三：运行Data Guard的具体步骤1、主数据库和备用数据库的Oracle安装的时候都选择相同的安装路径D:\oracle\ora92；并且先不要安装数据库，即在安装过程中“数据库配置”一项下面选择“只安装软件”；2、在主数据库上使用DBCA命令建一个数据库，SID设为PRIMARY；3、设置主数据库归档路径为D:\oracle\ora92\database\Archive：SQL>alter system setlog_archive_dest_1='LOCATION=d:\oracle\ora92\database\Archive MANDATORY' scope=both;4、将主数据库实例、数据库关闭，然后启动实例，但不打开数据库，只启动到数据库挂载模式: SQL>shutdown immediate; SQL>startup mount;5、将主数据库设置成归档状态和自动归档模式：SQL>alter database archivelog; SQL>alter system set log_archive_start=true scope=spfile;6、在主数据库上面创建备用数据库的控制文件standby.ctl：SQL>alter database create standby controlfile as 'd:\oracle\oradata\standby.ctl';7、在主数据库上面创建备用数据库的初始化参数文件standby.ora（将其拷贝回备用数据库后转换成备用数据库的spfile）：SQL>createpfile='e:\oracle\oradata\standby.ora' from spfile;8、查看主数据库的数据文件及其的位置：SQL>select name from v$datafile;记下查询结果中的数据文件的位置。
DataGuard安装配置
DataGuard安装配置1.安装Windows2003标准版DataGuard主数据库服务器：内存3GB，硬盘646GB，IP：172.17.34.7，机器名：gq-db-1，创建e:\ORACLE_DATA\Arch目录DataGuard备数据库服务器：内存3GB，硬盘646GB，IP：172.17.34.8，机器名：gq-db-2，创建e:\ORACLE_DATA\Arch目录注意：关闭防火墙，设置->控制面板->管理工具->本地安全策略->本地策略->用户权利指派->作为批处理作业登录->添加帐号2.在主备服务器安装Oracle10g软件注意：只安装软件，不安装数据库3.创建主备数据库全局数据库名：btvgqdb，SID：btvgqdb，快速恢复区大小：2048MB，启用归档：e:\ORACLE_DATA\Arch，数据库字符集AL32UTF8，国家字符集UTF8，配置监听和net服务名4.配置主备数据库设置数据库为force logging 模式alter database force logging;创建口令文件orapwd file=d:\oracle\product\10.2.0\db_1\database\PWDbtvgqdb.ora password=oracle entries=20创建备重做日志alter database add standby logfile group 5 (‘E:\ORACLE_DATA\Data\btvgqdb\sredo1_51.log’, ‘E:\ORACLE_DATA\Data\btvgqdb\sredo1_52.log’) size 250m;alter database add standby logfile group 6 (‘E:\ORACLE_DATA\Data\btvgqdb\sredo1_61.log’, ‘E:\ORACLE_DATA\Data\btvgqdb\sredo1_62.log’) size 250m;alter database add standby logfile group 7 (‘E:\ORACLE_DATA\Data\btvgqdb\sredo1_71.log’, ‘E:\ORACLE_DATA\Data\btvgqdb\sredo1_72.log’) size 250m;alter database add standby logfile group 8 (‘E:\ORACLE_DATA\Data\btvgqdb\sredo1_81.log’, ‘E:\ORACLE_DATA\Data\btvgqdb\sredo1_82.log’) size 250m;alter database add standby logfile group 9 (‘E:\ORACLE_DATA\Data\btvgqdb\sredo1_91.log’, ‘E:\ORACLE_DATA\Data\btvgqdb\sredo1_92.log’) size 250m;select * from v$logfile;select group#, thread#, sequence#, archived, status from v$standby_log;设置主数据库初始化参数使用spfile创建pfilecreate pfile from spfile;注意：Data Guard中所有数据库DB_NAME相同；log_archive_config的dg_config为db_unique_name；log_archive_dest_2的service为Oracle Net服务名主数据库初始化参数中增加：db_unique_name='btvgqdb'log_archive_config='dg_config=(btvgqdb,btvgqdbs)'log_archive_dest_1='LOCATION=e:\oracle_data\arch valid_for=(all_logfiles,all_roles)db_unique_name=btvgqdb'log_archive_dest_2='service=btvgqdbs lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name= btvgqdbs 'log_archive_dest_state_1='enable'log_archive_dest_state_2='defer'fal_server='btvgqdbs'fal_client='btvgqdb'standby_file_management=’auto’log_file_name_convert='e:\oracle_data\data\btvgqdb', 'e:\oracle_data\data\btvgqdb'在主数据库创建备数据库控制文件alter database create standby controlfile as ‘c:\standby.ctl’;将主数据物理文件拷贝到备数据库shutdown immediate修改备数据库初始化参数文件db_unique_name='btvgqdbs'log_archive_dest_1='LOCATION=e:\oracle_data\arch valid_for=(all_logfiles,all_roles) db_unique_name=btvgqdbs'log_archive_dest_2='service=btvgqdb lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=btvgqdb'log_archive_dest_state_2='enable'fal_server='btvgqdb'fal_client='btvgqdbs'配置listener.ora和tnsnames.oralistener.oraSID_LIST_LISTENER =(SID_LIST =(SID_DESC =(SID_NAME = PLSExtProc)(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)(PROGRAM = extproc))(SID_DESC =(GLOBAL_DBNAME = btvgqdb)(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)(SID_NAME = btvgqdb)))LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))))tnsnames.oraBTVGQDB =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.34.7)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = btvgqdb)))BTVGQDBS =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.34.8)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = btvgqdb)))EXTPROC_CONNECTION_DATA =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)))(CONNECT_DATA =(SID = PLSExtProc)(PRESENTATION = RO)))在主数据库创建spfile并启动主数据库create spfile from pfile='D:\oracle\product\10.2.0\db_1\database\initbtvgqdb.ora';startup在备数据库创建spfile并启动备数据库create spfile from pfile='D:\oracle\product\10.2.0\db_1\database\initbtvgqdbs.ora';startup mount将主数据库的重做日志传输给备用数据库alter system set log_archive_dest_state_2='enable' scope=both;在备数据库启动重做应用alter database recover managed standby database disconnect from session;在主数据库检查日志文件是否成功传输select status,error from v$archive_dest where dest_id=2;select sequence#, first_time, next_timefrom v$archived_log order by sequence#;select sequence#,applied from v$archived_logorder by sequence#;设置主库为最大可用模式shutdown immediatestartup mountalter database set standby to maximize availability;alter database open;select protection_mode, protection_level from v$database;5.测试建表create table test(col1 varchar2(20), col2 varchar2(60));insert into test values(1,1);insert into test values(2,2);停止应用日志alert database recover managed standby database cancel;alert database recover managed standby database finish;alert database commit to switchover to primary;shutdown immediate;startup;alter system switch logfile;。
dataguard配置文档_分析
Oracle Dataguard一、新建STANDBY数据库1、在开始运行-REGEDIT-打开注册表，定位至新建字符串值（大写）ORACLE_SID（数值数据为主库的名称）2、开始-运行-cmd输入oradim -new -sid (数据库名称)完成实例的建设。
二、配置监听与网络服务分别在主库和备用库上配置监听和网络服务名(可用oracle net configuration assistant配置工具)两个节点的配置相同一个网络服务名指向主库,另一个指向备用库1、监听程序配置（主库与STANDBY库配置一样）完成监听程序的配置2、配置本地NET服务名配置。
服务名输入为数据库名。
主机名输入为主库的IP地址网络服务名为主库名称。
继续配置STANDBY的NET服务名这里改为STANDBY的IP地址网络服务名改为（oraclestandby）这里改变名称时需要到主库更改选项。
完成网络服务名配置和监听配置。
以上步骤需主库与STANDBY库同时设置。
服务名都为实例名。
三、配置主库server1、ALTER DATABASE FORCE LOGGING;使其使用强制记录方式2、改初始化文件(加上以下一行)：检查是否为pfile或者spfile文件:select value from v$parameter where name = 'spfile'; *.log_archive_dest_2='SERVICE=ORCLSTANDBY'注：可用以下命令：alter system set log_archive_dest_2='service=STANDBY名' scope=spfile;alter system set log_archive_dest_2='service=STANDBY名' scope=both;(当初始化文件是spfile，用这条参数可以不用重启数据库)图中DEST_4因测试环境不同，做法按默认命令为准。
Dataguard配置手册
DATAGUARD配置手册一、DATAGUARD体系架构图二、配置步骤1.首先在节点1用dbca创建实例，节点2不安装实例，只装数据库软件即可，如果节点1是现成运行的数据库，则不需要新建;2.把主节点1的TNSNAMES.ORA配置成如下，并把该文件复制到备节点2的相同目录下：ORCL =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))ORCLBAK =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orclbak)))注:这里要注意红色部分，要根据自己的情况对IP和服务名进行调整；3.配置两个节点的监听：listener.ora设置如下(用现成的监听文件即可，无需修改)：主节点1设置：LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = shck70data279)(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))))注：这里将红色的主机名改为节点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并增加修改如下：SQL>create pfile from spfle;找到initorcl.ora文件：orcl.__db_cache_size=5251268608orcl.__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:\u01\admin\orcl\adump'*.audit_trail='db'* patible='11.2.0.0.0'*.control_files='E:\u01\oradata\orcl\control01.ctl','E:\u01\fast_recovery_area\orcl\control02.ctl'*.db_block_size=8192*.db_domain=''*.db_name='orcl'*.db_recovery_file_dest='E:\u01\fast_recovery_area'*.db_recovery_file_dest_size=52428800000*.diagnostic_dest='E:\u01'*.dispatchers='(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'--下面为增加的内容：DB_UNIQUE_NAME=orclLOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orclbak)'LOG_ARCHIVE_DEST_1= 'LOCATION=E:\u01\fast_recovery_area\orcl 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_ARCHIVE_DEST_STATE_1=ENABLELOG_ARCHIVE_DEST_STATE_2=ENABLELOG_ARCHIVE_FORMAT=%t_%s_%r.arcFAL_SERVER=orclbakFAL_CLIENT=orclSTANDBY_FILE_MANAGEMENT=AUTO7.在备端创建几个跟主端同样的目录：E:\u01\oradata\orcl,E:\u01\fast_recovery_area\orcl,E:\u01\admin\orcl\adump,E:\u01\admin\orcl\bdump,E:\u01\admin\orcl\pfile然后创建STANDBY控制文件并拷贝至备端的相同目录SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'e:\control01.ctl';然后把E:\control01.ctl复制到备端的E:\u01\oradata\orcl下8.根据6的参数文件内容，拷贝到到备端，并增加修改内容如下（记住pfile文件名要一样，也是叫initorcl.ora,路径是：E:\u01\product\11.2.0\dbhome_1\database\initorcl.ora）：备库的参数文件如下：orcl.__db_cache_size=5251268608orcl.__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:\u01\admin\orcl\adump'*.audit_trail='db'* patible='11.2.0.0.0'*.control_files='E:\u01\oradata\orcl\control01.ctl'--这里不同于主端，注意只指定一个控制文件即可*.db_block_size=8192*.db_domain=''*.db_name='orcl'*.db_recovery_file_dest='E:\u01\fast_recovery_area'*.db_recovery_file_dest_size=524288000000*.diagnostic_dest='E:\u01'*.dispatchers='(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'--调整以下内容：DB_UNIQUE_NAME=orclbakLOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orclbak)'LOG_ARCHIVE_DEST_1= 'LOCATION=E:\u01\fast_recovery_area\orcl 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=ENABLELOG_ARCHIVE_DEST_STATE_2=ENABLELOG_ARCHIVE_FORMAT=%t_%s_%r.arcFAL_SERVER=orclFAL_CLIENT=orclbakSTANDBY_FILE_MANAGEMENT=AUTO9.关闭主端数据库并把数据文件拷贝至备端的相同目录下：SQL>shutdown immediate然后把主端的E:\u01\oradata\orcl目录复制到备端同样的目录下,这里千万要注意，控制文件千万别复制过去，因为步骤7已将standby 控制文件复制过去；10.在主，备端同时建立standby重做日志：主端此时OPEN数据库，然后创建以下日志，SQL>statupSQL>ALTER DATABASE ADD STANDBY LOGFILE group 4('E:\u01\oradata\standby_log\slog1.rdo') SIZE 200M;SQL>ALTER DATABASE ADD STANDBY LOGFILE group 5('E:\u01\oradata\standby_log\slog2.rdo') SIZE 200M;SQL>ALTER DATABASE ADD STANDBY LOGFILE group 6('E:\u01\oradata\standby_log\slog3.rdo') SIZE 200M;SQL>ALTER DATABASE ADD STANDBY LOGFILE group 7('E:\u01\oradata\standby_log\slog4.rdo') SIZE 200M;备端先mount起来再创建：SQL>starup nomountSQL>alter database mount;SQL>ALTER DATABASE ADD STANDBY LOGFILE group 4('E:\u01\oradata\standby_log\slog1.rdo') SIZE 200M;SQL>ALTER DATABASE ADD STANDBY LOGFILE group 5('E:\u01\oradata\standby_log\slog2.rdo') SIZE 200M;SQL>ALTER DATABASE ADD STANDBY LOGFILE group 6('E:\u01\oradata\standby_log\slog3.rdo') SIZE 200M;SQL>ALTER DATABASE ADD STANDBY LOGFILE group 7('E:\u01\oradata\standby_log\slog4.rdo') SIZE 200M;11.备端进行同步操作SQL>shutdown immediate;SQL>startup nomount;SQL>alter database mount standby database;SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;此时先用步骤12，13查看DG是否同步正常，如正常（这里判断的方法是：用步骤13语句在主端看是否有LNS字样的同步日志出现，如出现ERROR则需要诊断不同步的原因，另外用步骤12在备端查看归档日志是否处于YES的应用状态），可进行下列操作切换到OPEN状态下同步：要切换到OPEN READ ONLY状态下的操作：SQL>alter database recover managed standby database cancel;SQL>alter database open read only;其实在open read only状态下也可以进行日志同步：ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;这样就可以既可以实时查数据，也可以同步数据--到此配置完毕，以下为内容为维护DATAGUARD使用：12.查看备库日志应用状态：SQL>SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME,applied FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;.13.查看主库日志归档情况：SQL>set lines 180SQL>col message format a70SQL>select * from V$DATAGUARD_STATUS;14.查看数据库主备状态：SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;15.查看备库standby_log状态：SQL>select thread#,sequence#,used,archived,status from v$standby_log;16.查看归档文件是否连续SQL> select thread#,low_sequence#,high_sequence# from v$archive_gap;。
逻辑Data Guard配置过程
逻辑DataGuard配置环境：我们需要物理Data Guard作为基础，物理Data Guard的配置过程已经演练过1、执行DBMS_LOGSTDBY.BUILD过程，生成数据字在执行该过程之前，要确保standby数据库停止了REDO应用STANDBY > alter database recover managed standby database cancel;PRIMARY > execute DBMS_LOGSTDBY.BUILDPL/SQL procedure successfully completed.2、将物理standby数据库转换为逻辑standby数据库STANDBY > select name from v$database;NAME---------ORCLSTANDBY > alter database recover to logical standby orcl_lg;Database altered.关闭数据库并重启至mount状态STANDBY > shutdown immediateORA-01507: database not mountedORACLE instance shut down.STANDBY > startup mountORACLE instance started.Total System Global Area 167772160 bytesFixed Size 1218316 bytesVariable Size 62916852 bytesDatabase Buffers 100663296 bytesRedo Buffers 2973696 bytesDatabase mounted.查看当前数据库DB_NAME和数据库角色都已经发生过改变STANDBY > select name,database_role from v$database;NAME DATABASE_ROLE--------- ----------------ORCL_LG LOGICAL STANDBY3、设置primary数据库端发来的归档文件接受路径为arch1STANDBY > alter system set log_archive_dest_1='location=/u01/arch1valid_for=(standby_logfiles, standby_role) db_unique_name=orcl2';System altered.设置本地生成的归档文件路径为archSTANDBY > alter system set log_archive_dest_3='location=/u01/arch valid_for=(online_logfiles, all_roles) db_unique_name=orcl2';System altered.由于逻辑standby与primary数据库事物不一致，第一次打开必须以resetlogs模式打开STANDBY > alter database open resetlogs;Database altered.开始REDO应用数据STANDBY > alter database start logical standby apply;Database altered.4、启动实时应用：（可选）添加几组Standby RedologsSTANDBY > alter database add standby logfile group 4'/u01/app/oracle/oradata/orcl_s/lg_stdredo01.log' size 50m;Database altered.STANDBY > alter database add standby logfile group 5'/u01/app/oracle/oradata/orcl_s/lg_stdredo02.log' size 50m;Database altered.STANDBY > alter database add standby logfile group 6'/u01/app/oracle/oradata/orcl_s/lg_stdredo03.log' size 50m;Database altered.关闭REDO,并再次打开，进入APPL Y IMMEDIATE命令来实现实时应用STANDBY > alter database stop logical standby apply;Database altered.STANDBY > alter database start logical standby apply immediate;Database altered.逻辑standby配置完成5、验证：primary数据库添加一条数据PRIMARY > select * from SCOTT.DG_TEST; ID----------DG_TEST_1DG_TEST_2DG_TEST_3PRIMARY > insert into SCOTT.DG_TEST2 values('DG_TEST_4')3 /1 row created.PRIMARY > select * from SCOTT.DG_TEST; ID----------DG_TEST_1DG_TEST_2DG_TEST_3DG_TEST_4提交，并执行一次归档PRIMARY > commit;Commit complete.PRIMARY > alter system switch logfile; System altered.standby数据库端直接查看数据STANDBY > select * from scott.DG_TEST;ID----------DG_TEST_1DG_TEST_2DG_TEST_3DG_TEST_4数据成功传输到逻辑standby端。
Windows环境下配置oracle 11gR2 Data Guard详细过程
如果不能连接，请检查防火墙。
3.10 关闭注数据库
3.11 在备用建立文件夹 A. 将 D:\app\Administrator 目录下的 admin、cfgtoollogs、
diag 目录及 E:\oracleDB\下的 flash 目录及密码文件拷贝到备用 库相同的路径下。
B. 将主库的 listener.ora 和 tnsnames.ora 拷贝到备库相同 路径下。
d:\app\Administrator\product\11.2.0\dbhome_1) (PROGRAM = extproc) (ENVS =
"EXTPROC_DLLS=ONLY:d:\app\Administrator\product\11.2.0\dbho
8
me_1\bin\oraclr11.dll") )
5
3.5 启动数据库 Alter database open; 重启数据库将修改的参数加载； Shutdown immediate Startup
3.6 创建 pfile 参数文件； Create pfile from spfile; 默认路径下
D:\app\Administrator\product\11.2.0\dbhome_1\database\INITo rcl.ORA 。
) (CONNECT_DATA =
(SERVICE_NAME = orcl) ) )
红色内容为需要修改成的。
3.9 测试监听
重启监听，用网络名连接测试监听是否正常 C:\>lsnrctl stop C:\>lsnrctl start
重启数据库： shutdown immediate ---> startup 测试监听 C:\>tnsping primary C:\>sqlplus sys/syspwd@primary as sysdba
搭建DataGuard环境操作流程
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =EECSNUM502.ecs.corp)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))RODUCT\10.2.0\ORADATA\EECS\REDO05.LOG'size 50m;
已更改資料庫.
SQL> alter database add standby logfile group 6
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EECS\REDO06.LOG'size 50m;
(GLOBAL_DBNAME = EECS) (ORACLE_HOME = C:\oracle\product\10.2.0\db_1) (SID_NAME = EECS) ) 5. 備庫 tnsnames.ora 添加如下信息： primary = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.241.10.18)(PORT = 1521)) (CONNECT_DATA =
9. 主庫重啟監聽器，關閉防火墻(包括殺毒軟體的防火墻); 二．備庫(standby)配置 1. 複製主庫準備的數據文件，日誌文件，密碼文件，(standby
logfile&controlfile),admin 目錄,archivelog 目錄,flash_recovery_area 目錄到備庫 的相應文件夾下，并將 standby controlfile 複製三份; 2. 修改備庫 pfile 文件: *.DB_UNIQUE_NAME='standby' *.FAL_CLIENT='standby' *.FAL_SERVER='primary' *.log_archive_config='DG_CONFIG=(primary,standby)'
最简单的11g Active DataGuard(ADG)搭建配置过程(项目步骤)
[-] 1.最简单的11g Active DataGuardADG搭建配置过程项目步骤1.一环境介绍2.二11g ADG部署1.pri端和sty端配置静态监听2.修改primary端初始化参数文件3.在primary端pfile参数文件和密码文件并且拷贝到standby段相应位置4.修改standby端的监听文件及初始化参数文件5.在primary端通过Rman Duplicate创建备库在db01上执行如下命令6.在primary和standby端添加standby日志7.在standby端开启实时日志应用3.三开始测试ADG1.执行日志切换测试在pri端切换归档在节点二上检查是否也发生了切换2.查看standby启动的DG进程3.查看数据库的保护模式4.查看DG的日志信息5.Open Read Only standby数据库并且开启实时日志应用6.解锁scott用户添加数据验证数据是否能同步4.四ADG三种模式切换及介绍1.ADG有三种PROTECTIONAVAILABILITYPERFORMANCE模式具体参考探索Oracle11gR2之DataGuard_03三种保护模式5.五切换测试1.ADG做switchover切换测试2.ADG做fail over切换测试最简单的11g Active DataGuard(ADG)搭建配置过程（项目步骤）一、环境介绍:我在db01和db02两台Linux虚拟机上首先分别安装了一套数据库软件，在db01主机上创建了名为woo的数据库；我们这次的实验是要搭建了一套Oracle11g Active DataGuard；目的是为了实现数据库同步的功能，并且了解Oracle11g DG的基本功能。
db01:192.168.1.50db02:192.168.1.51二、11g ADG部署：1、pri端和sty端配置静态监听[python]view plain copy print?1.[oracle@sty admin]$cat listener.ora2.#listener.ora Network Configuration File:/DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora3.#Generated by Oracle configuration tools.4.5.SID_LIST_LISTENER=6.(SID_LIST=7.(SID_DESC=8.(SID_NAME=PLSExtProc)9.(ORACLE_HOME=/DBSoft/oracle/product/11.2.4/dbhome_1)10.(PROGRAM=extproc)11.)12.(SID_DESC=13.(SID_NAME=Woo)14.(ORACLE_HOME=/DBSoft/oracle/product/11.2.4/dbhome_1)15.)16.)17.18.[oracle@sty admin]$cat tnsname.ora19.#tnsnames.ora Network Configuration File:/DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/tnsnames.ora20.#Generated by Oracle configuration tools.21.22.STY=23.(DESCRIPTION=24.(ADDRESS_LIST=25.(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.51)(PORT=1521))26.)27.(CONNECT_DATA=28.(SERVICE_NAME=woo)29.)30.)31.32.PRI=33.(DESCRIPTION=34.(ADDRESS_LIST=35.(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.50)(PORT=1521))36.)37.(CONNECT_DATA=38.(SERVICE_NAME=woo)39.)40.)2、修改primary端初始化参数文件[sql]view plain copy print?1.startup mount;2.alter database archivelog;3.alter database force logging;4.alter database open;5.alter system set log_archive_config='DG_CONFIG=(pri,sty)'scope=spfile;6.alter system set log_archive_dest_1='LOCATION=/DBBackup/Archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=pri'scope=spfile;7.alter system set log_archive_dest_2='SERVICE=sty LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=sty'scope=spfile;8.alter system set log_archive_dest_state_1=ENABLE;9.alter system set log_archive_dest_state_2=ENABLE;10.alter system set fal_server=sty scope=spfile;11.alter system set fal_client=pri scope=spfile;12.alter system set standby_file_management=AUTO scope=spfile;3、在primary端pfile参数文件和密码文件，并且拷贝到standby段相应位置[python]view plain copy print?1.SQL>create pfile from spfile;2.3.File created.4.5.[oracle@db01dbs]$scp initwoo.ora orapwwoo db02:/DBSoft/oracle/product/11.2.4/dbhome_1/dbs6.oracle@192.168.1.51's password:7.initwoo.ora100%1260 1.2KB/s00:008.orapwwoo100%1536 1.5KB/s00:009.10.[oracle@db01oracle]$scp-r admin/diag/fast_recovery_area/oradata/192.168.1.51:$ORACLE_BASE11.oracle@192.168.1.51's password:12.init.ora.512201522543100%1778 1.7KB/s00:0113.dp.log100%1160.1KB/s00:0014.........4、修改standby端的监听文件及初始化参数文件[sql]view plain copy print?1.--修改监听文件2.[oracle@db02~]$cd$ORACLE_HOME/network/admin3.[oracle@db02admin]$vi listener.ora4.#listener.ora Network Configuration File:/DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora5.#Generated by Oracle configuration tools.6.7.LISTENER=8.(DESCRIPTION_LIST=9.(DESCRIPTION=10.(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))11.(ADDRESS=(PROTOCOL=TCP)(HOST=db02)(PORT=1521))12.)13.)14.15.SID_LIST_LISTENER=16.(SID_LIST=17.(SID_DESC=18.(SID_NAME=PLSExtProc)19.(ORACLE_HOME=/DBSoft/oracle/product/11.2.4/dbhome_1)20.(PROGRAM=extproc)21.)22.23.(SID_DESC=24.(GLOBAL_DBNAME=woo)25.(ORACLE_HOME=/DBSoft/oracle/product/11.2.4/dbhome_1)26.(SID_NAME=woo)27.)28.29.)30.31.ADR_BASE_LISTENER=/DBSoft/oracle32.33.--启动监听34.[oracle@db02dbs]$lsnrctl start35.36.LSNRCTL for Linux:Version11.2.0.4.0-Production on17-JUN-201521:29:5737.38.Copyright(c)1991,2013,Oracle.All rights reserved.39.40.Starting/DBSoft/oracle/product/11.2.4/dbhome_1/bin/tnslsnr:please wait...41.42.TNSLSNR for Linux:Version11.2.0.4.0-Production43.System parameter file is/DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora44.Log messages written to/DBSoft/oracle/diag/tnslsnr/db02/listener/alert/log.xml45.Listening on:(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))46.Listening on:(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db02)(PORT=1521)))47.48.Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))49.STATUS of the LISTENER50.------------------------51.Alias LISTENER52.Version TNSLSNR for Linux:Version11.2.0.4.0-Production53.Start Date17-JUN-201521:29:5754.Uptime0days0hr.0min.1sec55.Trace Level off56.Security ON:Local OS Authentication57.SNMP OFF58.Listener Parameter File/DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora59.Listener Log File/DBSoft/oracle/diag/tnslsnr/db02/listener/alert/log.xml60.Listening Endpoints Summary...61.(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))62.(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db02)(PORT=1521)))63.Services Summary...64.Service"PLSExtProc"has1instance(s).65.Instance"PLSExtProc",status UNKNOWN,has1handler(s)for this service...66.Service"woo"has1instance(s).67.Instance"woo",status UNKNOWN,has1handler(s)for this service...68.The command completed successfully69.70.--查看监听状态71.[oracle@db02dbs]$lsnrctl status72.73.LSNRCTL for Linux:Version11.2.0.4.0-Production on17-JUN-201521:30:0274.75.Copyright(c)1991,2013,Oracle.All rights reserved.76.77.Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))78.STATUS of the LISTENER79.------------------------80.Alias LISTENER81.Version TNSLSNR for Linux:Version11.2.0.4.0-Production82.Start Date17-JUN-201521:29:5783.Uptime0days0hr.0min.4sec84.Trace Level off85.Security ON:Local OS Authentication86.SNMP OFF87.Listener Parameter File/DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora88.Listener Log File/DBSoft/oracle/diag/tnslsnr/db02/listener/alert/log.xml89.Listening Endpoints Summary...90.(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))91.(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db02)(PORT=1521)))92.Services Summary...93.Service"PLSExtProc"has1instance(s).94.Instance"PLSExtProc",status UNKNOWN,has1handler(s)for this service...95.Service"woo"has1instance(s).96.Instance"woo",status UNKNOWN,has1handler(s)for this service...97.The command completed successfully98.[oracle@db02dbs]$99.100.101.--修改参数文件102.[oracle@db02~]$sqlplus/as sysdba103.104.SQL*Plus:Release11.2.0.4.0Production on Wed Jun1721:35:542015105.106.Copyright(c)1982,2013,Oracle.All rights reserved.107.108.Connected to an idle instance.109.110.SQL>startup nomount;111.ORACLE instance started.112.113.Total System Global Area1188511744bytes114.Fixed Size1364228bytes115.Variable Size754978556bytes116.Database Buffers419430400bytes117.Redo Buffers12738560bytes118.119.SQL>create spfile from pfile='/DBSoft/oracle/product/11.2.4/dbhome_1/dbs/initwoo.ora';120.121.File created.122.123.SQL>124.SQL>shutdown abort;125.ORACLE instance shut down.126.SQL>startup nomount;127.ORACLE instance started.128.129.Total System Global Area1188511744bytes130.Fixed Size1364228bytes131.Variable Size754978556bytes132.Database Buffers419430400bytes133.Redo Buffers12738560bytes134.SQL>135.136.alter system set db_unique_name=sty scope=spfile;137.alter system set log_archive_config='DG_CONFIG=(pri,dg)'scope=spfile;138.alter system set log_archive_dest_1='LOCATION=/DBBackup/Archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=sty'scope=spfile; 139.alter system set log_archive_dest_2='SERVICE=pri LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=pri'scope=spfile; 140.alter system set fal_server=pri scope=spfile;141.alter system set fal_client=sty scope=spfile;142.143.SQL>shutdown abort;144.ORACLE instance shut down.145.SQL>startup nomount146.ORACLE instance started.147.148.Total System Global Area1188511744bytes149.Fixed Size1364228bytes150.Variable Size754978556bytes151.Database Buffers419430400bytes152.Redo Buffers12738560bytes153.SQL>154.SQL>5、在primary端通过Rman Duplicate创建备库,在db01上执行如下命令rman target sys/oracle@pri auxiliary sys/oracle@sty nocatalogduplicate target database for standby from active database nofilenamecheck; [python]view plain copy print?1.[oracle@db01~]$rman target sys/oracle@pri auxiliary sys/oracle@sty nocatalog2.3.Recovery Manager:Release11.2.0.4.0-Production on Wed Jun1722:33:5420154.5.Copyright(c)1982,2011,Oracle and/or its affiliates.All rights reserved.6.7.connected to target database:WOO(DBID=4221729487)ing target database control file instead of recovery catalog9.connected to auxiliary database:WOO(not mounted)10.11.RMAN>12.13.RMAN>duplicate target database for standby from active database nofilenamecheck;14.15.Starting Duplicate Db at17-JUN-1516.allocated channel:ORA_AUX_DISK_117.channel ORA_AUX_DISK_1:SID=19device type=DISK18.19.contents of Memory Script:20.{21.backup as copy reuse22.targetfile'/DBSoft/oracle/product/11.2.4/dbhome_1/dbs/orapwwoo'auxiliary format23.'/DBSoft/oracle/product/11.2.4/dbhome_1/dbs/orapwwoo';24.}25.executing Memory Script26.27.Starting backup at17-JUN-1528.allocated channel:ORA_DISK_129.channel ORA_DISK_1:SID=44device type=DISK30.Finished backup at17-JUN-1531.32.contents of Memory Script:33.{34.backup as copy current controlfile for standby auxiliary format'/DBSoft/oracle/oradata/woo/control01.ctl';35.restore clone controlfile to'/DBSoft/oracle/fast_recovery_area/woo/control02.ctl'from36.'/DBSoft/oracle/oradata/woo/control01.ctl';37.}38.executing Memory Script39.40.Starting backup at17-JUN-15ing channel ORA_DISK_142.channel ORA_DISK_1:starting datafile copy43.copying standby control file44.output file name=/DBSoft/oracle/product/11.2.4/dbhome_1/dbs/snapcf_woo.f tag=TAG20150617T223502RECID=1STAMP=88265730845.channel ORA_DISK_1:datafile copy complete,elapsed time:00:00:1546.Finished backup at17-JUN-1547.48.Starting restore at17-JUN-15ing channel ORA_AUX_DISK_150.51.channel ORA_AUX_DISK_1:copied control file copy52.Finished restore at17-JUN-1553.54.contents of Memory Script:55.{56.sql clone'alter database mount standby database';57.}58.executing Memory Script59.60.sql statement:alter database mount standby database61.62.contents of Memory Script:63.{64.set newname for tempfile1to65."/DBSoft/oracle/oradata/woo/temp01.dbf";66.switch clone tempfile all;67.set newname for datafile1to68."/DBSoft/oracle/oradata/woo/system01.dbf";69.set newname for datafile2to70."/DBSoft/oracle/oradata/woo/sysaux01.dbf";71.set newname for datafile3to72."/DBSoft/oracle/oradata/woo/undotbs01.dbf";73.set newname for datafile4to74."/DBSoft/oracle/oradata/woo/users01.dbf";75.backup as copy reuse76.datafile1auxiliary format77."/DBSoft/oracle/oradata/woo/system01.dbf"datafile78.2auxiliary format79."/DBSoft/oracle/oradata/woo/sysaux01.dbf"datafile80.3auxiliary format81."/DBSoft/oracle/oradata/woo/undotbs01.dbf"datafile82.4auxiliary format83."/DBSoft/oracle/oradata/woo/users01.dbf";84.sql'alter system archive log current';85.}86.executing Memory Script87.88.executing command:SET NEWNAME89.90.renamed tempfile1to/DBSoft/oracle/oradata/woo/temp01.dbf in control file91.92.executing command:SET NEWNAME93.94.executing command:SET NEWNAME95.96.executing command:SET NEWNAME97.98.executing command:SET NEWNAME99.100.Starting backup at17-JUN-15ing channel ORA_DISK_1102.channel ORA_DISK_1:starting datafile copy103.input datafile file number=00001name=/DBSoft/oracle/oradata/woo/system01.dbf 104.output file name=/DBSoft/oracle/oradata/woo/system01.dbf tag=TAG20150617T223532 105.channel ORA_DISK_1:datafile copy complete,elapsed time:00:05:58106.channel ORA_DISK_1:starting datafile copy107.input datafile file number=00002name=/DBSoft/oracle/oradata/woo/sysaux01.dbf108.output file name=/DBSoft/oracle/oradata/woo/sysaux01.dbf tag=TAG20150617T223532109.channel ORA_DISK_1:datafile copy complete,elapsed time:00:05:30110.channel ORA_DISK_1:starting datafile copy111.input datafile file number=00003name=/DBSoft/oracle/oradata/woo/undotbs01.dbf112.output file name=/DBSoft/oracle/oradata/woo/undotbs01.dbf tag=TAG20150617T223532113.channel ORA_DISK_1:datafile copy complete,elapsed time:00:00:25114.channel ORA_DISK_1:starting datafile copy115.input datafile file number=00004name=/DBSoft/oracle/oradata/woo/users01.dbf116.output file name=/DBSoft/oracle/oradata/woo/users01.dbf tag=TAG20150617T223532117.channel ORA_DISK_1:datafile copy complete,elapsed time:00:00:03118.Finished backup at17-JUN-15119.120.sql statement:alter system archive log current121.122.contents of Memory Script:123.{124.switch clone datafile all;125.}126.executing Memory Script127.128.datafile1switched to datafile copy129.input datafile copy RECID=1STAMP=882658052file name=/DBSoft/oracle/oradata/woo/system01.dbf 130.datafile2switched to datafile copy131.input datafile copy RECID=2STAMP=882658052file name=/DBSoft/oracle/oradata/woo/sysaux01.dbf 132.datafile3switched to datafile copy133.input datafile copy RECID=3STAMP=882658052file name=/DBSoft/oracle/oradata/woo/undotbs01.dbf 134.datafile4switched to datafile copy135.input datafile copy RECID=4STAMP=882658052file name=/DBSoft/oracle/oradata/woo/users01.dbf 136.Finished Duplicate Db at17-JUN-15#至此已经恢复完成6、在primary和standby端添加standby日志[sql]view plain copy print?1.SQL>alter database add standby logfile2.group4('/DBSoft/oracle/oradata/woo/styredo04.log')size50m,3.group5('/DBSoft/oracle/oradata/woo/styredo05.log')size50m,4.group6('/DBSoft/oracle/oradata/woo/styredo06.log')size50m,5.group7('/DBSoft/oracle/oradata/woo/styredo07.log')size50m;6.7.SQL>SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;8.9.GROUP#THREAD#SEQUENCE#ARC STATUS10.-------------------------------------------11.400YES UNASSIGNED12.500YES UNASSIGNED13.600YES UNASSIGNED14.700YES UNASSIGNED7、在standby端开启实时日志应用[sql]view plain copy print?1.SQL>recover managed standby database using current logfile disconnect from session;2.Media recovery complete.3.SQL>三、开始测试ADG8、执行日志切换测试（在pri端切换归档，在节点二上检查是否也发生了切换）[sql]view plain copy print?1.--primary执行日志切换2.3.SQL>archive log list;4.Database log mode Archive Mode5.Automatic archival Enabled6.Archive destination/DBBackup/Archive7.Oldest online log sequence218.Next log sequence to archive239.Current log sequence2310.SQL>alter system switch logfile;11.12.System altered.13.14.SQL>archive log list;15.Database log mode Archive Mode16.Automatic archival Enabled17.Archive destination/DBBackup/Archive18.Oldest online log sequence2219.Next log sequence to archive2320.Current log sequence2421.22.#standby查看日志的sequence号也跟着变了23.SQL>archive log list;24.Database log mode Archive Mode25.Automatic archival Enabled26.Archive destination/DBBackup/Archive27.Oldest online log sequence2228.Next log sequence to archive029.Current log sequence2330.SQL>archive log list;31.Database log mode Archive Mode32.Automatic archival Enabled33.Archive destination/DBBackup/Archive34.Oldest online log sequence2235.Next log sequence to archive036.Current log sequence2437.SQL>9、查看standby启动的DG进程[sql]view plain copy print?1.SQL>select process,client_process,sequence#,status from v$managed_standby;2.3.PROCESS CLIENT_P SEQUENCE#STATUS4.---------------------------------------5.ARCH ARCH23CLOSING6.ARCH ARCH0CONNECTED//归档进程7.ARCH ARCH21CLOSING8.ARCH ARCH0CONNECTED9.RFS ARCH0IDLE10.RFS UNKNOWN0IDLE11.RFS LGWR24IDLE//归档传输进程12.RFS UNKNOWN0IDLE13.MRP0N/A24APPLYING_LOG//日志应用进程14.15.9rows selected.10、查看数据库的保护模式：[sql]view plain copy print?1.#primary端查看，我们可以看到数据库的保护模式为最大性能2.SQL>select database_role,protection_mode,protection_level,open_mode from v$database;3.4.DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE5.----------------------------------------------------------------------------6.PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ WRITE7.8.#standby端查看，也是一样的。
19cR3之Data Guard搭建
19cR3之Data Guard搭建环境node1 192.168.8.128 Primary orcl_01node2 192.168.8.129 Standby orcl_02内容——Primary配置——Standby搭建（Duplicate方式）——Standby搭建（物理拷贝方式）——Standby搭建（DBCA命令方式）——善后及模式调整一．Primary配置搭建Data Guard，需要对Primary进行如下配置。
1.开启归档模式Data Guard环境中，必须开启归档模式，以保存完整日志，这是数据同步的基础。
[oracle@node1 ~]$ sqlplus / as sysdbaSQL> start mount; ——启动实例到mount状态SQL> alter database archivelog; ——开启归档模式SQL> alter database open; ——打开实例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 2Current log sequence 2可见，已成功开启归档模式，缺省路径为：$ORACLE_HOME/dbs/arch，后面会修改。
2.强制日志模式我们知道，Oracle可使用nologging子句避免写日志，以便提高效率；但Data Guard就是基于日志来实现主备数据同步，这显然是不允许的，因此需要修改为Forced Logging模式，以强制记录任何修改。
SQL> alter database force logging;SQL> select name,log_mode,force_logging from v$database;NAME LOG_MODE FORCE_LOGGING--------- ------------ ---------------------------------------ORCL ARCHIVELOG YES3.调整参数配置Data Guard中，起关键作用的就是远程归档参数，一套简单的Data Guard环境，只需设置一个参数即可成功搭建，但为了以后方便相互切换，最好完善相关配置。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
DATA GUARD（standy database）重要参数:远程归档参数是dg配置中的重中之重!其它都为辅助参数!*.LOG_ARCHIVE_DEST_2='SERVICE=standby lgwr SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=madrid'ARCH/LGWR默认使用ARCH模式传送，即归档日志级别传送redo，最大可用和最大保护模式需要LGWR模式传送，即语句级传送redo条目，需要建立备库的online redolog并且要使用日志的实时应用模式即LGWR SYNC AFFIMSYNC/ASYNC 网络I/O操作方式，SYNC是实时模式，ASYNC异步模式，ASYNC=0 ~ 102400 blocksAFFIRM/NOAFFIRM磁盘I/O模式(数据写模式)，AFFIRM同步并且发送成功写操作到主库；NOAFFIRM主库无需等待备库日志写成功与否AFFIRM和NOAFFIRM设置主库做数据写之前是否需要等待主、从redo条目写成功ALTERNATE和NOALTERNATE设置在归档目录1无法写入时，是否将归档写入到备用归档路径ARCH和LGWR设置是以归档日志文件模式还是以redo条目模式传输数据DELAY和NODELAY设置STANDBY数据库是否延迟应用归档日志（分钟为单位）；DEPENDENCY和NODEPENDENCY参数在归档文件可以直接被其他STANDBY数据库访问时，其他的STANDBY在归档文件可用时是否还需要传输归档；LOCATION和SERVICE设置本地归档或归档到STANDBY数据库；MANDATORY和OPTIONAL设置归档路径是强制的还是可选的；MAX_FAILURE和NOMAX_FAILURE参数设置归档失败后，连接重试的最大次数；NET_TIMEOUT和NONET_TIMEOUT设置LGWR进程写归档信息时等待的秒数；QUOTA_SIZE和NOQUOTA_SIZE设置归档目录可以使用的空间大小；QUOTA_USED和NOQUOTA_USED显示归档目录以及使用的空间大小；REGISTER和NOREGISTER设置是否在目标站点记录归档日志的位置；REOPEN和NOREOPEN设置在归档失败后是否进行重试；SYNC和ASYNC设置LGWR进程写IO操作是否同步进行；TEMPLATE和NOTEMPLATE设置归档路径和格式的模板信息。
使用物理standby时，主从数据库的db_name必须一致!必须的！*.db_name=orcl识别数据库的别名，要唯一，必须不一致！*.db_unique_name='aux1'启用dg配置:*.log_archive_config='dg_config=(主库唯一名,从库唯一名)'管理恢复模式下保存主库传输过来的归档日志:*.standby_archive_dest='/home/oracle/aux1_stdlog'如果主从数据库的数据文件或联机日志文件的存储位置不同，需要使用参数说明，但逻辑备用库时无效*.db_file_name_convert=('/u1/oracle/oradata/madrid/','/u1/oracle/oradata/stan dby/')*.log_file_name_convert=('/u1/oracle/oradata/madrid/','/u1/oracle/oradata/stan dby/')主库增加或减少数据文件的动作是否自动应用到从库*.standby_file_management='AUTO'当保护模式为“最大保护”时，至少需要几个备库处于正常状态log_archive_min_succeed_dest=1最高可用的物理standby主库日志传输模式级别要更细致:redo条目级别网络同步i/o同步LGWR SYNC AFFIRMlog_archive_dest_1='service=aux1 lgwr sync affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=从库唯一名'在最大可用的物理standby要拥有自己的联机日志:接收主库传送过来的redo条目alter database add standby logfile '' size 50m;alter database add standby logfile '' size 50m;alter database add standby logfile '' size 50m;alter database add standby logfile '' size 50m;从库要拥有归档路径:当数据库角色为standby，联机日志类型为standby时使用log_archive_dest_3指向的路径保存归档log_archive_dest_3='location=/home/oracle/aux1_stdlogvalid_for=(STANDBY_LOGFILES,STANDBY_ROLE) db_unique_name=aux1'将数据库转换为最高可用模式(主库)：suhtdown immediatestartup mountalter database set standby database to maximize availability;alter database open;查看数据库保护模式:select protection_mode,database_role,protection_level from v$database;开启物理standby的日志实时应用(从库):alter database recover managed standby database using current logfile disconnect from session;停止日志应用:alter database recover managed standby database cancel;最大保护的物理standby将数据库转换为最大保护模式（要在mount下转换）：以最高可用模式为基础进行转换,主库数据写要等待从库日志写成功alter database set standby database to maximize protection;查看数据库保护模式:select protection_mode,database_role,protection_level from v$database;物理的standy database：远程数据库必须mount,不能open，应用的是远程传输来的归档逻辑的standy database（现建立物理的，在物理的基础上建立逻辑）：必须是open，应用的是远程传输来的归档的挖掘出来的SQL。
1、日志的传输SQL> show parameter namedb_name string upldb_unique_name string upl-beijingglobal_names boolean FALSEinstance_name string upl stringlog_file_name_convert string2、日志的应用3、角色的切换DATA GUARD配置过程1、两台主机配置，数据库版本相同2、主的别名bj，从库删除数据库，删除归档，只保留产品3、主数据库切换日志，删除所有的归档，然后crosscheck copy;delete expired copy;listcopy;4、修改初始化参数，先写主，再写从。
建立密码文件5、建立standy日志组6、减肥数据库，一致性停库7、把数据文件传输到远程的相同目录8、建立从数据库的控制文件9、启动主数据库，启动从数据库到mount10、切换主数据库的日志，在从数据库中standy_archive_dest位置看到远程传来的文档11、应用归档12、主数据库的操作，切换13、Alter database open read only打开从数据库，验证操作并更新14、从数据库停止，重新处于standy模式下15、角色的切换Show parameter log_archiveAlter system switch logfile;Delete archivecrosscheck copy;delete expired copy;Alter system set db_unique_name=tj scope=spfile;Standby_archive_destSQL> select switchover_status from v$database;SWITCHOVER_STATUS--------------------NOT ALLOWED一、创建物理Standby数据库1、配置主库为建立备库作准备SQL> ALTER DATABASE FORCE LOGGING; #将日志设置为强制记录状态配置主库的监听：% lsnrctl stopCd /usr/local/oracle/10.2.0/db_1/network/admin/Vim tnsnames.ora添加：22 chicago =23 (DESCRIPTION =24 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.175)(PORT = 1521))25 (CONNECT_DATA =26 (SERVER = DEDICATED)27 (SERVICE_NAME = chicago)28 )% lsnrctl start创建密码文件（也可以将主库密码文件直接拷贝到备库）创建备份重做日志文件A standby redo log is required for the maximum protection and maximum availabilitymodes and the LGWR ASYNC transport mode is recommended for all databases。
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 52> ('/oracle/dbs/log1c.rdo','/oracle/dbs/log2c.rdo') SIZE 500M;SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 102> ('/oracle/dbs/log1c.rdo','/oracle/dbs/log2c.rdo') SIZE 500M;SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;参数文件的设置－－－－－－－－－－－－－－－－－－－－－－－－－－－－－Database DB_UNIQUE_NAME Oracle Net Service NamePrimary chicago chicagoPhysical standby boston boston－－－－－－－－－－－－－－－－－－－－－－－－－－－－－DB_NAME=chicagoDB_UNIQUE_NAME=chicagoLOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'CONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl' LOG_ARCHIVE_DEST_1='LOCATION=/arch1/chicago/VALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=chicago'LOG_ARCHIVE_DEST_2='SERVICE=boston LGWR ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=boston'LOG_ARCHIVE_DEST_STATE_1=ENABLELOG_ARCHIVE_DEST_STATE_2=ENABLEREMOTE_LOGIN_PASSWORDFILE=EXCLUSIVELOG_ARCHIVE_FORMAT=%t_%s_%r.arcLOG_ARCHIVE_MAX_PROCESSES=30以下参数用于主从角色切换：FAL_SERVER=bostonFAL_CLIENT=chicagoDB_FILE_NAME_CONVERT='boston','chicago'LOG_FILE_NAME_CONVERT='/arch1/boston/','/arch1/chicago/','/arch2/boston/','/arch2/chicago/' STANDBY_FILE_MANAGEMENT=AUTO将主库至于归档日志状态：SQL> SHUTDOWN IMMEDIATE;SQL> STARTUP MOUNT;SQL> ALTER DATABASE ARCHIVELOG;SQL> ALTER DATABASE OPEN;创建备库的控制文件：SQL> STARTUP MOUNT;SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/boston.ctl'; SQL> ALTER DATABASE OPEN;创建备库的参数文件：SQL> CREATE PFILE='/tmp/initboston.ora' FROM SPFILE;修改上面创建的参数文件如下（黑体为需要修改的参数）：DB_NAME=chicagoDB_UNIQUE_NAME=bostonLOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'CONTROL_FILES='/arch1/boston/control1.ctl', '/arch2/boston/control2.ctl'DB_FILE_NAME_CONVERT='chicago','boston'LOG_FILE_NAME_CONVERT='/arch1/chicago/','/arch1/boston/','/arch2/chicago/','/arch2/boston/' LOG_ARCHIVE_FORMAT=log%t_%s_%r.arcLOG_ARCHIVE_DEST_1='LOCATION=/arch1/boston/VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=boston'LOG_ARCHIVE_DEST_2='SERVICE=chicago LGWR ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=chicago'如下参数用于主从库之间的切换：LOG_ARCHIVE_DEST_STATE_1=ENABLELOG_ARCHIVE_DEST_STATE_2=ENABLEREMOTE_LOGIN_PASSWORDFILE=EXCLUSIVESTANDBY_FILE_MANAGEMENT=AUTOFAL_SERVER=chicagoFAL_CLIENT=boston2、将主库的数据文件，及以上建立好的控制文件、参数文件、和密码文件拷贝到从库备份主库:rman target /configure channel device type disk format '/home/oracle/rmanbk/%d_%I_%s_%p.bkp';backup as compressed backupset database include current controlfile for standby plus archivelog;将所有备份片和备份之后的归档日志传到从库:在主库所在节点使用rman的复制数据库命令，还原从库：rman target sys/oracle@orcl auxiliary sys/oracle@aux1RMAN> duplicate target database for standby;3、建立从数据库的运行环境WINNT> oradim -NEW -SID boston -INTPWD password -STARTMODE manual 配置从库的监听（注意SERVICE_NAME要与db_unique_name相同）：% lsnrctl stopCd /usr/local/oracle/10.2.0/db_1/network/admin/Vim tnsnames.ora添加：22 boston =23 (DESCRIPTION =24 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.175)(PORT = 1521))25 (CONNECT_DATA =26 (SERVER = DEDICATED)27 (SERVICE_NAME = boston)28 )% lsnrctl start创建二进制二进制文件SQL> CREATE SPFILE FROM PFILE='initboston.ora';启动备库到MOUNTSQL> STARTUP MOUNT;4、测试主从数据库可用性在主库中查看第二存档终点是否有异常:col dest_name for a30col error for a20select dest_name,status,error,target,process from v$archive_dest where substr(dest_name,-1) in (1,2);在主库切换日志，查看是否能传到从库:alter system switch logfile;从库进入管理恢复模式:ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;查看从库的alert文件，看主库传送过来的归档是否被应用:cd $ORACLE_BASE/admin/bdumptail -f alert_aux1.log取消管理恢复的方法：ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;当主库的归档在从库中应用之后可以打开从库进行数据验证:alter database open;5、主从之间的切换测试查看数据库角色和数据保护模式:select protection_mode,database_role,protection_level from v$database;主从切换测试:主变从:select switchover_status from v$database;SWITCHOVER_STATUS--------------------TO STANDBY --可以直接切换alter database commit to switchover to physical standby;SWITCHOVER_STATUS--------------------SESSIONS ACTIVE --不能直接切换执行下列命令alter database commit to switchover to physical standby with session shutdown;从变主:select switchover_status from v$database;SWITCHOVER_STATUS--------------------NOT ALLOWED --没有切换标记!不能切换TO PRIMARY --可以直接切换alter database commit to switchover to primary;SESSION ACTIVE --不能直接切换执行下列命令alter database commit to switchover to primary with session shutdown;从的SWITCHOVER状态：SQL> select switchover_status from v$database;SWITCHOVER_STATUS--------------------NOT ALLOWED主变从:SQL> select switchover_status from v$database;SWITCHOVER_STATUS--------------------TO STANDBY --可以直接切换SQL> alter database commit to switchover to physical standby;从的SWITCHOVER状态：SQL> select switchover_status from v$database;SWITCHOVER_STATUS--------------------SWITCHOVER PENDING主的SWITCHOVER状态：SQL> select switchover_status from v$database;SWITCHOVER_STATUS--------------------SESSIONS ACTIVE从到主切换：SQL> alter database commit to switchover to primary;alter database commit to switchover to primary*ERROR at line 1:ORA-16139: media recovery requiredSQL> alter database recover managed standby database disconnect from session; SQL> alter database commit to switchover to primary;SQL> select switchover_status from v$database;SWITCHOVER_STATUS--------------------TO STANDBY。