Oracle 11gR2 Dataguard配置

首先关于异构平台搭建DATAGUARD的相关文档参见：http://blog.youkuaiyun.com/tianlesoftware/article/details/7241488 

Win7 64位与Win7 32位 Oracle 11gR2

Primary主要参数：

操作系统：window7 64bit

数据库名db_name：orcl1234

实例名oracle_sid：orcl1234

db_unique_name：orcl1234pre

IP：192.168.100.126  port：1526

Net Service Name：orcl1234pre_service_name

Database Home：D:\app\Administrator\product\11.2.0\dbhome_1

standby主要参数：

操作系统：window7 32bit

数据库名db_name：orcl1234（数据库名称一定相同）

实例名oracle_sid：orcl1234dg

db_unique_name：orcl1234dg

IP：192.168.100.128  port：1526

Net Service Name：orcl1234dg_service_name

Database Home：D:\app\Administrator\product\11.2.0\dbhome_1

首先确认Primary数据库处于归档模式：

SQL>ARCHIVE LOG LIST;可以查看数据库是否为归档模式

如果不是将数据库启动至mount模式，执行：

SQL>ALTER DATABASE ARCHIVELOG;

然后将主库设置为Force Logging模式：

查询和更改语句分别为：

SQL>SELECT FORCE_LOGGING FROM V$DATABASE;

SQL>ALTER DATABASE FORCE LOGGING;

接着配置Primary数据库的初始化参数：

首先将主库参数文件Spfile转换成pfile：

SQL>CREATE PFILE='D:\backup\pfile_orcl1234.ora' from spfile;

然后修改其参数，修改后的文件为：

orcl1234.__db_cache_size=176160768
orcl1234.__java_pool_size=12582912
orcl1234.__large_pool_size=12582912
orcl1234.__oracle_base='D:\app\Administrator'#ORACLE_BASE set from environment
orcl1234.__pga_aggregate_target=285212672
orcl1234.__sga_target=536870912
orcl1234.__shared_io_pool_size=0
orcl1234.__shared_pool_size=322961408
orcl1234.__streams_pool_size=0
*.audit_file_dest='D:\app\Administrator\admin\orcl1234\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='D:\APP\ADMINISTRATOR\ORADATA\ORCL1234\CONTROL01.CTL'#Restore Controlfile
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl1234'

*.db_unique_name='orcl1234pre'
*.log_archive_config='dg_config=(orcl1234pre,orcl1234dg)'
*.log_archive_dest_2='service=orcl1234dg_service_name arch valid_for=(online_logfiles,primary_role) db_unique_name=orcl1234dg'
*.log_archive_dest_state_2=defer

*.fal_server=orcl1234dg_service_name
*.fal_client=orcl1234pre_service_name
*.db_file_name_convert='D:\APP\ADMINISTRATOR\ORADATA\ORCL1234','D:\APP\ADMINISTRATOR\ORADATA\ORCL1234dg'
*.log_file_name_convert='D:\APP\ADMINISTRATOR\ORADATA\ORCL1234','D:\APP\ADMINISTRATOR\ORADATA\ORCL1234dg'
*.standby_file_management=auto

*.db_recovery_file_dest='D:\app\Administrator\flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='D:\app\Administrator'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcl1234XDB)'
*.local_listener='LISTENER_ORCL1234'
*.memory_target=822083584
*.open_cursors=300
*.processes=150
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

其中主要参数解释如下，感谢http://blog.youkuaiyun.com/sunjiapeng/article/details/9115531整理：

参数	主库角色？	备库角色？	建议和注释
ARCHIVE_LOG_TARGET	是	仅物理	经过指定的秒数后强制进行日志切换。
COMPATIBLE	是	物理和逻辑	Data Guard的要求最低值9.2.0.1.0。设置为10.2.0.0的最低使用Oracle数据库10g的新功能。指定主数据库和备库相同的值，如果你希望做一个切换。如果值不同，重做传输服务可能无法从主库的重做数据传输到备库。
CONTROL_FILE_RECORD_KEEP_TIME	是	物理和逻辑	可选，使用此参数指定的天数(从0到365)，以避免覆盖一个可重用的控制文件中记录(如归档重做日志文件包含所需的信息)。
CONTROL_FILES	是	物理和逻辑	必需，指定一个或多个控制文件的路径名和文件名 ​​。控制文件在数据库中必须已经存在。Oracle建议使用2个控制文件。如果另一个当前控制文件的副本是可用的，那么一个实例可以很容易地复制好的控制文件到坏控制文件的位置后重新启动。
DB_FILE_NAME_CONVERT	否	仅物理	如果备库作为主库，或者位于备库上的数据文件所在的目录是从主库不同，在同一系统上。此参数必须指定配对的字符串。第一个字符串是要寻找主库文件名中的字符序列。如果该字符序列匹配，它将被替换为第二个字符串构造备库文件名 ​​。您可以指定多个文件名。
DB_UNIQUE_NAME	是	物理和逻辑	推荐，但需要您指定LOG_ARCHIVE_CONFIG的参数。这个数据库指定一个唯一的名称。这个名字不会改变，即使主数据库和备用数据库互换角色。DB_UNIQUE_NAME参数默认值为DB_NAME参数的值。
FAL_CLIENT	是	仅物理	如果FAL_SERVER参数被指定则必须。指定Oracle Net服务名使用FAL服务器(通常指主库)参考FAL客户端(备库)。
FAL_SERVER	否	仅物理	如果FAL_CLIENT参数被指定则必须。为数据库指定一个或多个Oracle Net服务名，从这个备库可以获取丢失的归档重做日志文件(请求)。
INSTANCE_NAME	是	逻辑和物理	可选，如果这个参数被定义和主备库属于同一台主机上，为备库指定一个不同的名称区别于你的主库。
LOG_ARCHIVE_CONFIG	是	逻辑和物理	推荐，通过DG_CONFIG的属性为主库和每个备库来确定Data Guard配置中的DB_UNIQUE_NAME。此参数的默认值使主库的重做数据传送到远程目的地和使备库接收重做数据。当RAC环境下主库运行在最大保护模式或最大可用性模式时，备库中DG_CONFIG属性必须被设置为动态添加。
LOG_ARCHIVE_DEST_n	是	逻辑和物理	必需，定义多达十个(N = 1，2，3，... 10)目的地，每一个目的地都必须指定地点或服务的属性。每一个LOG_ARCHIVE_DEST_n参数对应每个LOG_ARCHIVE_DEST_STATE_n参数。
LOG_ARCHIVE_DEST_STATE_n	是	逻辑和物理	必需，指定LOG_ARCHIVE_DEST_STATE_n参数来启用或禁用重做传输服务来传送重做数据到指定的目的地(或替代)。为每个LOG_ARCHIVE_DEST_n参数定义LOG_ARCHIVE_DEST_STATE_n参数。
LOG_ARCHIVE_FORMAT	是	逻辑和物理	如果你指定了STANDBY_ARCHIVE_DEST参数则必须。在备库上这些参数都串连在一起以产生完全合格的归档重做日志文件名​​。
LOG_ARCHIVE_LOCAL_FIRST	是	否	可选。指定控制归档进程(ARCn)传输；一种是在联机重做日志文件成功地归档到至少一个本地目标之后(TRUE),一种是在相同的时间联机重做日志文件被归档到本地目的地(FALSE)。
LOG_ARCHIVE_MAX_PROCESSES	是	逻辑和物理	可选，指定一个你希望Oracle调用的最大的归档进程(ARCn)数(从1到30)。默认值是4。
LOG_ARCHIVE_MIN_SUCCEED_DEST	是	没有	可选,定义的最小数量(从1到10)的目的地,在主库上的日志写进程可以重用联机重做日志文件之前必须成功地接收重做数据。
LOG_ARCHIVE_TRACE	是	逻辑和物理	可选，设置这个参数来追踪重做数据传输到备用站点。有效的整数值(0，1，2，4，8，16，32，64，128，256，512，1024，2048，4096)
LOG_FILE_NAME_CONVERT	否	逻辑和物理	必需，当主备库所处操作系统相同或主备站点上的日志文件位于不同的目录结构时，此参数将在备库上对主库重做日志文件路径名转换为对应的备库路径名。
PARALLEL_MAX_SERVERS	是	仅逻辑	必需，指定逻辑备库的并行服务最大数量值。为了达到最佳效果，此参数不能小于5，PARALLEL_MAX_SERVERS最低限度设为9。
REMOTE_LOGIN_PASSWORDFILE	是	逻辑和物理	必需，在主库和每个备库上都需指定。
SHARED_POOL_SIZE	是	逻辑和物理	可选，使用指定系统全局区(SGA)阶段信息读取联机重做日志文件。越多的SGA可用，越多的信息可以实施。
SORT_AREA_SIZE	是	逻辑和物理	可选，在大型排序中增加SORT_AREA_SIZE的值(默认大小为65536字节)来提高效率。
STANDBY_ARCHIVE_DEST	否	逻辑和物理	可选，指定备库从主库接收归档重做日志文件的位置。初始化参数STANDBY_ARCHIVE_DEST会覆盖参数LOG_ARCHIVE_DEST_n指定的目录位置。STANDBY_ARCHIVE_DEST和LOG_ARCHIVE_FORMAT连接起来以产生完整的日志文件名。
STANDBY_FILE_MANAGEMENT	是	仅物理	将STANDBY_FILE_MANAGEMENT参数设置为AUTO，以便主库添加或删除数据文件时备库做出相应的变化，无需人工干预。如果主库和备库的目录结构不同，你必须设置初始化参数DB_FILE_NAME_CONVERT将主库上一组或多组数据文件的文件名转换至​​(物理)备库。
USER_DUMP_DEST	是	逻辑和物理	如果LOG_ARCHIVE_TRACE参数被指定则必须。USER_DUMP_DEST指定写入调试跟踪文件的路径名。

修改好pfile后再重建spfile：

在重建前需要：

SQL>SHUTDOWN IMMEDIATE;

然后：

SQL>CREATE SPFILE FROM PFILE='D:\BACKUP\PFILE_ORCL1234';

如果上面参数有任何语法错误就会立即出现各种错误，例如我遇到的：

最终查明好像是下面的参数valid for = 之间多了一个空格！oracle里面好像对空格非常敏感，例如set oracle_sid=XXX中等号左右是不允许有空格的，否则实例名称设置失败，关键还不提示警告。

*.log_archive_dest_2='service=orcl1234dg_service_name arch valid_for=(online_logfiles,primary_role) db_unique_name=orcl1234dg'

下面这个错误更奇葩，是因为里面有参数少了一个 ' 符号，也就是单引号！

如果想少一点错误，使用alter system 改变系统参数也是可以的。

接着再重新启动数据库：

SQL>STARTUP

然后在创建standby数据库的控制文件：

SQL>ALTER DATABASE CREATE STANDBY CONTROFILES AS 'D:\BACKUP\ORCL1234DG01.CTL';

接着备份Primary数据库文件至standby数据库，包括：数据文件、控制文件、客户端初始化文件至对应路径，所谓对应路径就是与Primary数据库中对应路径，例如Primary database_home对应Standby数据库 database_home路径，因为不同数据库之间有版本或者装载路径不同。

因为我初学oracle，所以备份方法是最原始的备份方法，即直接复制，当然还可以使用duplicate rman等方式进行备份恢复。为了不影响Primary数据库的使用，我们使用热备份法，即在数据库open状态复制。

首先查看数据库数据文件,显示路径位置：

SQL>SELECT FILE_NAME,TABLESPACE_NAME FROM DBA_DATA_FILES;

然后使用命令设置为备份状态：

SQL>ALTER TABLESPACE XXX BEGIN BACKUP;

使用命令进行备份：

SQL>HOST COPY XXXX 

最后设置成备份完成状态：

SQL>ALTER TABLESPACE XXX END BACKUP;

接下来配置监听文件和net service name 

listener.ora文件，我最终状态为：

# listener.ora Network Configuration File: D:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL1234)
      (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
      (SID_NAME = ORCL1234)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = Oracle8)
      (SID_NAME = ORCL)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.126)(PORT = 1526))
    )
  )

ADR_BASE_LISTENER = D:\app\Administrator

注意重新配置监听文件后需要重启才有效：

SQL>LSNRCTL STOP;

SQL>LSNRCTL START;

然后配置tnsname.ora:

# tnsnames.ora Network Configuration File: D:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORCL1234PRE_SERVICE_NAME =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.126)(PORT = 1526))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl1234)
    )
  )

LISTENER_ORCL1234 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1526))


ORCL1234 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl1234)
    )
  )

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL1234DG_SERVICE_NAME =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.128)(PORT = 1528))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl1234dg)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

创建数据库秘钥文件,如果没有的话：
ADMINISTRATOR>ORAPWD FILE=D:\app\Administrator\product\11.2.0\dbhome_1\database\PWDORCL1234.ORA password=XXX entries=30

再复制到standby数据库相应目录下。

Primary数据库已经基本配置完毕，接着需要Standby数据库的配置。

首先创建oracleservice

ADMINISTRATOR>oradim -new -sid orcl1234dg

这里注意，window下自己手动创建实例服务默认是手动的，也就是说在开机后需要自己启动服务OracleServiceOrcl1234dg，然后再打开控制台，使用sqlplus，先打开控制台也会出现问题。

接着创建日志相关目录，这个具体我也不清楚哪些是必须的，所以这部分先放着。

配置Standby的监听文件

# listener.ora Network Configuration File: D:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
   (SID_DESC =
      (GLOBAL_DBNAME = ORCL1234DG)
      (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
      (SID_NAME = ORCL1234DG)
    )
  )
   
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.128)(PORT = 1528))
    )
  )

ADR_BASE_LISTENER = D:\app\Administrator

net service name

# tnsnames.ora Network Configuration File: D:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

LISTENER_ORCL1234DG =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1528))

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCL1234PRE_SERVICE_NAME =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.126)(PORT = 1526))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl1234)
    )
  )

ORCL1234DG_SERVICE_NAME =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.128)(PORT = 1528))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl1234dg)
    )
  )

重启监听文件，命令在上面写过。

这时为了验证监听配置正确性，可以使用命令分别在Primary与standby测试

ADMINISTRATOR>TNSPING orcl1234dg_service_name;
ADMINISTRATOR>TNSPING orcl1234pre_service_name;

修改Standby数据库初始化文件，只要在Primary数据库初始化文件的基础上修改就可以了，修改后如下：

orcl1234dg.__db_cache_size=176160768
orcl1234dg.__java_pool_size=12582912
orcl1234dg.__large_pool_size=12582912
orcl1234dg.__oracle_base='D:\app\Administrator'#ORACLE_BASE set from environment
orcl1234dg.__pga_aggregate_target=285212672
orcl1234dg.__sga_target=536870912
orcl1234dg.__shared_io_pool_size=0
orcl1234dg.__shared_pool_size=322961408
orcl1234dg.__streams_pool_size=0
*.audit_file_dest='D:\app\Administrator\admin\orcl1234dg\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='D:\app\Administrator\oradata\orcl1234dg\ORCL1234DG01.CTL','D:\app\Administrator\oradata\orcl1234dg\ORCL1234DG02.CTL','D:\app\Administrator\oradata\orcl1234dg\ORCL1234DG03.CTL'
*.core_dump_dest='D:\app\Administrator\admin\orcl1234dg\cdump'

*.db_block_size=8192
*.db_domain=''
*.db_name='orcl1234'

*.db_unique_name='orcl1234dg'
*.log_archive_config='dg_config=(orcl1234pre,orcl1234dg)'
*.log_archive_dest_1='Location=D:\app\Administrator\oradata\orcl1234dg\archive'
*.log_archive_dest_2='service=orcl1234pre_service_name arch valid_for=(online_logfiles,primary_role) DB_unique_name=orcl1234pre'
*.log_archive_dest_state_2=enable

*.fal_server=orcl1234pre_service_name
*.fal_client=orcl1234dg_service_name
*.db_file_name_convert='D:\APP\ADMINISTRATOR\ORADATA\ORCL1234','D:\APP\ADMINISTRATOR\ORADATA\ORCL1234dg','D:\APP\ADMINISTRATOR\ORADATA\ORCL1234dg','D:\APP\ADMINISTRATOR\ORADATA\ORCL1234'
*.log_file_name_convert='D:\APP\ADMINISTRATOR\ORADATA\ORCL1234','D:\APP\ADMINISTRATOR\ORADATA\ORCL1234dg','D:\APP\ADMINISTRATOR\ORADATA\ORCL1234dg','D:\APP\ADMINISTRATOR\ORADATA\ORCL1234'
*.standby_file_management=auto

*.db_recovery_file_dest='D:\app\Administrator\flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='D:\app\Administrator'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcl1234XDB)'
*.local_listener='LISTENER_ORCL1234DG'
*.memory_target=822083584
*.open_cursors=300
*.processes=150
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

然后以sys身份连接到standby数据库：

SQL>CONN sys/pwd@orcl1234dg_service_name as sysdba;

SQL>create spfile from pfile='D:\app\Administrator\oradata\orcl1234dg\pfile_orcl1234.ora';

然后启动到mount状态：

SQL>startup mount;

设置Primary开始接受归档文件,向standy传送redo数据：

SQL>alter system set log_archive_dest_state_2=enable;
使用命令查看归档日志是否发送到standby数据库：

SQL>select max(sequence#) from v$archived_log;

SQL>select sequence#, name from v$archived_log order by sequence# desc;

接下来启动redo传输，启动后数据库计入mount状态：

SQL>alter database recover managed standby database disconnect from session;

配置完成

下面是遇到的错误：

没有在tnsname.ora中配置orcl1234dg_service_name网络名

监听程序中有语法错误

由于上面监听程序没启动，所以关闭会出现错误

 

详细参见：http://www.cnblogs.com/zrcoffee/archive/2012/11/21/2780468.html

 

spfile中的local_listener参数需要与tnsname.ora中的本机net_service_name对应

 

pfile_orcl1234中没有加入后缀.ora

没有启动OrcleServiceOrcl1234dg服务！

还有一个很诡异的问题，错误代码为：ORA-01102 ？？？exclusive？？？？？

具体可以参见http://blog.itpub.net/22664653/viewspace-692638/，具体原因主要有：

1 在ORACLE_HOME/dbs/存在 "sgadef<sid>.dbf" 文件或者lk<db_name> 文件。这两个文件是用来用于锁内存的。
2 oracle的 pmon, smon, lgwr and dbwr等进程未正常关闭。
3 数据库关闭后，共享内存或者信号量依然被占用。

这个问题出现时间：

1.我已经配置完所有命令，但是archive_log里没有反映

2.我发现standby数据库pfile文件log_archive_dest_2参数错误

3.我重新配置spfile后重新startup amount启动数据库出现上述问题

分析：

根据原因第三条可能是dataguard已经使Primary数据库和standby数据库进行同步原因，占用进程

解决方法：关闭主机和备用机网络、阻断通信，关闭主数据库后，两机就没有通信，然后再重新启用oracleserviceorcl234dg服务，重新startup mount备用数据库，成功挂载数据库。

Centos 6.5 Oracle 11gR2之间DataGuard配置：

Primary主要参数：

操作系统：CentOS 6.5 Final

数据库名db_name：orcl1234

实例名oracle_sid：orcl1234

db_unique_name：orcl1234_pre

IP：192.168.100.110  port：10010

Net Service Name：orcl1234_pre

Database Home：/u01/app/oracle/product/11.2.0/dhhome_1

standby主要参数：

操作系统：CentOS 6.5 Final

数据库名db_name：orcl1234（数据库名称一定相同）

实例名oracle_sid：orcl1234

db_unique_name：orcl1234_dg

IP：192.168.100.112  port：10012

Net Service Name：orcl1234_dg

Database Home：/u01/app/oracle/product/11.2.0/dhhome_1

Primary配置：

1.数据库设置为归档模式

2.数据库设置为force logging模式

3. 添加redo log file

添加一个新的Standby Redologs组（注意组号不要与当前存在的Online Redologs组重复），并为该组指定一个成员：

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/app/oracle/oradata/orcl1234/redo04.log') size 50M; 

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/app/oracle/oradata/orcl1234/redo05.log') size 50M; 

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/app/oracle/oradata/orcl1234/redo06.log') size 50M; 

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/app/oracle/oradata/orcl1234/redo07.log') size 50M;

4.创建pfile文件

5.修改Primary初始化参数,如下：

orcl1234.__db_cache_size=293601280
orcl1234.__java_pool_size=4194304
orcl1234.__large_pool_size=4194304
orcl1234.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl1234.__pga_aggregate_target=314572800
orcl1234.__sga_target=465567744
orcl1234.__shared_io_pool_size=0
orcl1234.__shared_pool_size=155189248
orcl1234.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl1234/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/orcl1234/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl1234/control02.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl1234'

*.db_unique_name='orcl1234_pre'
*.log_archive_config='dg_config=(orcl1234_pre,orcl1234_dg)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl1234_pre'
*.log_archive_dest_2='service=orcl1234_dg arch valid_for=(online_logfiles,primary_role) db_unique_name=orcl1234_dg';
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.log_archive_dest_state_2=ENABLE


*.fal_server=orcl1234_dg
*.fal_client=orcl1234_pre
*.standby_file_management=auto



*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcl1234XDB)'
*.local_listener='LISTENER_ORCL1234'
*.memory_target=779091968
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

6.重新创建spfile文件

7.按照新的spfile重启数据库

8.创建standby控制文件，可以按照*control_files参数中控制文件个数，决定复制个数

9.如果主数据库没有密码文件，则创建密码文件：

[oracle@primary～]$ orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs    /orapworcl1234 password=sys entries=30;

10.备份primary初始化文件pfile,日志文件(online与redo),数据文件,上面创建的控制文件(多份),密码文件。
11.配置监听文件listener.ora：

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (PROGRAM = extproc)
    )

    (SID_DESC =
      (GLOBAL_DBNAME = orcl1234)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = orcl1234)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.110)(PORT = 10010))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

12.配置网络服务名文件tnsname.ora：

 tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PRIMARY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.110)(PORT = 10010))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = primary)
    )
  )
LISTENER_OCRL1234 =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.110)(PORT = 10010))
  )

ORCL1234 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.110)(PORT = 10010))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl1234)
    )
  )


ORCL1234_PRE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.110)(PORT = 10010))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl1234)
    )
  )

ORCL1234_DG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.112)(PORT = 10012))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl1234)
    )
  )

standby：

1.在standby数据库中创建文件夹：

$ORACLE_BASE/ORADATA/ORCL1234

$ORACLE_BASE/admin/orcl1234/adump

$ORACLE_BASE/flash_recovery_area/orcl1234

(在初始化文件参数中出现的路径，如果standby中没有，最好都创建一下)

2.复制primary备份文件至standby相应路径：

[oracle@localhost ～]$cd $ORACLE_BASE/oradata/orcl1234

[oracle@localhost orcl1234]$ scp *.dbf 192.168.100.112://u01/app/oracle/oradata/orcl1234

[oracle@localhost orcl1234]$ scp *.ctl 192.168.100.112://u01/app/oracle/oradata/orcl1234

[oracle@localhost orcl1234]$ scp *.ctl 192.168.100.112://u01/app/oracle/flash_recovery_area/orcl1234

[oracle@localhost orcl1234]$ scp *.log 192.168.100.112://u01/app/oracle/oradata/orcl1234

[oracle@localhost orcl1234]$ scp pfile_orcl1234.ora 192.168.100.112://u01/app/oracle/product/11.2.0/dbhome_1/dbs

[oracle@localhost orcl1234]$ scp orapworcl1234 192.168.100.112://u01/app/oracle/product/11.2.0/dbhome_1/dbs

3.设置standby数据库初始化参数pfile

rcl1234.__java_pool_size=4194304
orcl1234.__large_pool_size=4194304
orcl1234.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl1234.__pga_aggregate_target=314572800
orcl1234.__sga_target=465567744
orcl1234.__shared_io_pool_size=0
orcl1234.__shared_pool_size=155189248
orcl1234.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl1234/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/orcl1234/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl1234/control02.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl1234'

*.db_unique_name='orcl1234_dg'
*.log_archive_config='dg_config=(orcl1234_pre,orcl1234_dg)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl1234_dg'
*.log_archive_dest_2='service=orcl1234_pre arch valid_for=(online_logfiles,primary_role) db_unique_name=orcl1234_pre';
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.log_archive_dest_state_2=ENABLE

*.fal_server=orcl1234_pre
*.fal_client=orcl1234_dg
*.standby_file_management=auto

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcl1234XDB)'
*.local_listener='LISTENER_ORCL1234'
*.memory_target=779091968
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

4.设置监听listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc) 
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (PROGRAM = extproc)
     )
  (SID_DESC =
     (GLOBAL_DBNAME = orcl1234) 
     (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
     (SID_NAME = orcl1234)  
    )
  )

    
LISTENER =
  (DESCRIPTION_LIST = 
    (DESCRIPTION =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.112)(PORT = 10012))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

5.设置网络服务名tnsname.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

STANDBY1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.112)(PORT = 10012))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby1)
    )
  )

ORCL1234_DG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.112)(PORT = 10012))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl1234)
    )
  )
ORCL1234_PRE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.110)(PORT = 10010))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl1234)
    )
  )

6.准备工作已完成，启动DataGuard:

注意Data Guard 启动顺序：

启动顺序：先standby ,后primary;

关闭顺序：先primary 后standby;

standby：

[oracle@standby1 ~]lsnrctl start;

[oracle@standby1 ~]sqlplus /nolog;

SQL>connsys/sys@orcl1234_dg as sysdba;

SQL>create spfile from pfile='pfile_orcl1234.ora';

SQL>startup mount;

primary:

[oracle@peimary ~]lsnrctl start;

SQL>startup;

注意：CentOS中不需要用oradim命令创建实例，直接根据网络服务名链接数据库就可以。

附录：

总结：配置中所有问题基本上都是由于初始化参数配置错误，所以一定要认真配置参数。

如果在主库执行 alter database clear unarchived logfile或alter database open resetlogs ,则dataguard要重建。

遇到问题：

SQL>startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

则表示obsolete or deprecated parameter(s)  初始化spfile有参数已过时或不推荐使用参数，具体需要查看alert_sid.log

参考：http://blog.youkuaiyun.com/tianlesoftware/article/details/5547565