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/[email protected] auxiliary sys/[email protected]

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;