Oracle 11gR2 Active Data Guard 配置 (windows 环境）

Oracle 11gR2 Active Data Guard 配置

一、配置环境

基础准备

 Sqlplus链接数据库格式

系统管理员登录 sqlplus sys/password@tnsname as sysdba

数据库用户登录sqlplus user/password@tnsname

                                

*tnsname 可以用ip/sid替换

主库环境：

OS:  Windows server 2008 r2

IP: 172.16.40.33

database:  oracle 11.2.0.3

数据库名:  orcl

从库环境：

  OS:  Windows server 2008 r2

IP: 172.16.40.9

database:  oracle 11.2.0.3

数据库名:  orcl

 

二、监听配置

listener.ora文件修改

#D:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora

 

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 = ORCL)

       (ORACLE_HOME =D:\app\Administrator\product\11.2.0\dbhome_1)

       (SID_NAME = ORCL)

    )

  )

tnsnames.ora文件修改

#D:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora

 

ORCL01 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST =  172.16.40.33)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ORCL)

    )

  )

ORCL02 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.40.9)(PORT= 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ORCL)

    )

  )

 

红色为添加部分，主从库均要添加或修改

 

本文主从库默认路径和数据库sid一致，如不一致请做出对应修改

 

*配置后请用lsnrctl stop/restart重启监听服务或在服务列表中重启监听服务

 

三、主库配置

1.将主数据库改为强制产生日志模式

select force_logging from v$database;

alter database force logging;

2. 修改主库的初始化参数

            修改数据库唯一标识名

alter system set db_unique_name ='orcl01'scope=spfile;

 

设置归档日志配置DG_CONFIG=(主库,从库)

alter system set log_archive_config='DG_CONFIG=(orcl01,orcl02)' scope=spfile;

 

设置归档日志存放路径location= 物理路径

alter system setlog_archive_dest_1='location=E:\oracleDB\flash\orcl\ARCHIVELOGVALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl01'scope=spfile;

 

 

设置归档日志传输方式

alter system set log_archive_dest_2='SERVICE=orcl02LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl02' scope=spfile;

 

设置log_archive_dest _1 /2状态  默认为 enable

alter system set log_archive_dest_state_1= enable scope=spfile;

alter system set log_archive_dest_state_2= enable scope=spfile;

 

设置归档日志名称格式

ALTER SYSTEM SET log_archive_format='ARC_ERP5_%s_%r_%t.log'SCOPE=SPFILE;

 

设置归档数据库文件路径db_file_name_convert=”主库路径”,”从库路径”

alter system setdb_file_name_convert='D:\app\Administrator\oradata\ORCL','D:\app\Administrator\oradata\ORCL'scope=spfile;

 

设置归档数据库日志文件路径log_file_name_convert=”主库路径”,”从库路径”

, ”主库闪回路径”,”从库闪回路径”

alter system set log_file_name_convert=

'D:\app\Administrator\oradata\ORCL','D:\app\Administrator\oradata\ORCL',

'D:\app\Administrator\flash_recovery_area\ORCL\ONLINELOG',

'D:\app\Administrator\flash_recovery_area\ORCL\ONLINELOG' scope=spfile;

 

查看归档日志列表

archive log list;

关闭数据库实例

shutdown immediate;

装载数据库

startup mount;

设置数据库为归档数据库

alter database archivelog ;

打开数据库

alter database open;

 

 

四、从库配置

1.将从数据库改为强制产生日志模式

select force_logging from v$database;

alter database force logging;

 

2. 修改从库的初始化参数

修改数据库唯一标识名

alter system setdb_unique_name ='orcl02' scope=spfile;

 

设置归档日志配置DG_CONFIG=(主库,从库)

alter system setlog_archive_config='DG_CONFIG=(orcl01,orcl02)' scope=spfile;

 

设置归档日志存放路径location= 物理路径

alter system setlog_archive_dest_1='location=E:\oracleDB\flash\orcl\ARCHIVELOGVALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl02'scope=spfile;

 

设置归档日志传输方式

alter system setlog_archive_dest_2='SERVICE=orcl01 LGWR ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl01' scope=spfile;

 

设置log_archive_dest _1 /2状态  默认为 enable

alter system set log_archive_dest_state_1=enable scope=spfile;

alter system setlog_archive_dest_state_2= enable scope=spfile;

 

设置归档日志名称格式

ALTER SYSTEM SETlog_archive_format='ARC_ERP5_%s_%r_%t.log' SCOPE=SPFILE;

 

设置归档客服端fal_client/服务端fal_server

alter system setfal_client='orcl02' scope=spfile;

alter system setfal_server='orcl01' scope=spfile;

 

 

设置归档数据库文件路径db_file_name_convert=”从库路径”, ”主库路径”

alter system setdb_file_name_convert='D:\app\Administrator\oradata\ORCL','D:\app\Administrator\oradata\ORCL'scope=spfile;

 

设置归档数据库日志文件路径log_file_name_convert=”从库路径”, ”主库路径”,”从库闪回路径”, ”主库闪回路径”

alter system set log_file_name_convert=

'D:\app\Administrator\oradata\ORCL','D:\app\Administrator\oradata\ORCL',

'D:\app\Administrator\flash_recovery_area\ORCL\ONLINELOG',

'D:\app\Administrator\flash_recovery_area\ORCL\ONLINELOG' scope=spfile;

 

查看归档日志列表

archive log list;

关闭数据库实例

shutdown immediate;

装载数据库

startup mount;

设置数据库为归档数据库

alter database archivelog ;

打开数据库

alter database open;

 

五、还原与同步

1、准备工作

主库：完全启动

shutdown immediate;

startup;

 

从库：启动到未装载

shutdown immediate;

startup nomount;

 

2、开始还原

     Dos命令：

rman targetsys/wy@orcl01 auxiliary sys/wy@orcl02

duplicate targetdatabase for standby from active database;

duplicate targetdatabase for standby from active database dorecover nofilenamecheck;

3、开启同步归档

主库：alter system archive log current;

从库：

ALTERDATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;

ALTERDATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;

ALTERDATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;

  alter database open read only;

alter database recover managed standbydatabase using current logfile disconnect from session;