最簡單的11g Active DataGuard(ADG)搭建配置過程（項目步驟）

最簡單的11g Active DataGuard(ADG)搭建配置過程（項目步驟）

一、環境介紹:
    我在db01和db02兩台Linux虛擬機器上首先分別安裝了一套資料庫軟體，在db01主機上建立了名為woo的資料庫；我們這次的實驗是要搭建了一套Oracle 11g Active DataGuard；目的是為了實現資料庫同步的功能，並且瞭解Oracle 11g DG的準系統。

db01:192.168.1.50
db02:192.168.1.51

Oracle 11g Active DataGuard初探 

Oracle Data Guard 重要配置參數

基於同一主機配置 Oracle 11g Data Guard

探索Oracle之11g DataGuard

Oracle Data Guard （RAC+DG) 歸檔刪除策略及指令碼

Oracle Data Guard 的角色轉換

Oracle Data Guard的日誌FAL gap問題

Oracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby 處理方法

二、11g ADG部署：

1、pri端和sty端配置靜態監聽

[oracle@sty admin]$ cat listener.ora
 
# listener.ora Network Configuration File: /DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /DBSoft/oracle/product/11.2.4/dbhome_1)

    (PROGRAM = extproc)

    )

      (SID_DESC =

    (SID_NAME = Woo )

    (ORACLE_HOME = /DBSoft/oracle/product/11.2.4/dbhome_1)

    )

  )

 

[oracle@sty admin]$cat tnsname.ora

# tnsnames.ora Network Configuration File: /DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

STY =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME = woo)

    )

  )

PRI =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME = woo)

    )

  )

2、修改primary端初始化參數檔案

startup mount;
 
alter database archivelog;

alter database force logging;

alter database open;

alter system set log_archive_config = 'DG_CONFIG=(pri,sty)' scope=spfile;

alter system set log_archive_dest_1 = 'LOCATION=/DBBackup/Archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pri' scope=spfile;

alter system set log_archive_dest_2 = 'SERVICE=sty LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sty' scope=spfile;

alter system set log_archive_dest_state_1 = ENABLE;

alter system set log_archive_dest_state_2 = ENABLE;

alter system set fal_server=sty scope=spfile;

alter system set fal_client=pri scope=spfile;

alter system set standby_file_management=AUTO scope=spfile;

3、在primary端pfile參數檔案和密碼檔案，並且拷貝到standby段相應位置

SQL> create pfile from spfile;
 

File created.

[oracle@db01 dbs]$ scp initwoo.ora orapwwoo db02:/DBSoft/oracle/product/11.2.4/dbhome_1/dbs

oracle@192.168.1.51's password:
 initwoo.ora                                                                                    100% 1260    1.2KB/s  00:00   
 orapwwoo                                                                                      100% 1536    1.5KB/s  00:00

 [oracle@db01 oracle]$ scp -r admin/ diag/ fast_recovery_area/ oradata/ 192.168.1.51:$ORACLE_BASE
 oracle@192.168.1.51's password:
 init.ora.512201522543                                                                          100% 1778    1.7KB/s  00:01   
 dp.log                                                                                        100%  116    0.1KB/s  00:00   
 ........

   

4、修改standby端的監聽檔案及初始化參數檔案

--修改監聽檔案
 
[oracle@db02 ~]$ cd $ORACLE_HOME/network/admin

[oracle@db02 admin]$ vi listener.ora

# listener.ora Network Configuration File: /DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

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

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /DBSoft/oracle/product/11.2.4/dbhome_1)

      (PROGRAM = extproc)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = woo)

      (ORACLE_HOME = /DBSoft/oracle/product/11.2.4/dbhome_1)

      (SID_NAME = woo)

    )

  )

ADR_BASE_LISTENER = /DBSoft/oracle

--啟動監聽

[oracle@db02 dbs]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-JUN-2015 21:29:57

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Starting /DBSoft/oracle/product/11.2.4/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production

System parameter file is /DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora

Log messages written to /DBSoft/oracle/diag/tnslsnr/db02/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db02)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date 17-JUN-2015 21:29:57

Uptime 0 days 0 hr. 0 min. 1 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora

Listener Log File /DBSoft/oracle/diag/tnslsnr/db02/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db02)(PORT=1521)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "woo" has 1 instance(s).

  Instance "woo", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

--查看監聽狀態

[oracle@db02 dbs]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-JUN-2015 21:30:02

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date 17-JUN-2015 21:29:57

Uptime 0 days 0 hr. 0 min. 4 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora

Listener Log File /DBSoft/oracle/diag/tnslsnr/db02/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db02)(PORT=1521)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "woo" has 1 instance(s).

  Instance "woo", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

[oracle@db02 dbs]$

--修改參數檔案

[oracle@db02 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 17 21:35:54 2015

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 1188511744 bytes

Fixed Size 1364228 bytes

Variable Size 754978556 bytes

Database Buffers 419430400 bytes

Redo Buffers 12738560 bytes

SQL> create spfile from pfile='/DBSoft/oracle/product/11.2.4/dbhome_1/dbs/initwoo.ora';

File created.

SQL>

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 1188511744 bytes

Fixed Size 1364228 bytes

Variable Size 754978556 bytes

Database Buffers 419430400 bytes

Redo Buffers 12738560 bytes

SQL>

alter system set db_unique_name=sty scope=spfile;

alter system set log_archive_config='DG_CONFIG=(pri,dg)' scope=spfile;

alter system set log_archive_dest_1 ='LOCATION=/DBBackup/Archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sty' scope=spfile;

alter system set log_archive_dest_2 ='SERVICE=pri LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pri' scope=spfile;

alter system set fal_server=pri scope=spfile;

alter system set fal_client=sty scope=spfile;

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1188511744 bytes

Fixed Size 1364228 bytes

Variable Size 754978556 bytes

Database Buffers 419430400 bytes

Redo Buffers 12738560 bytes

SQL>

SQL>

 5、在primary端通過Rman Duplicate建立備庫,在db01上執行如下命令

rman target sys/oracle@pri auxiliary sys/oracle@sty nocatalog
 duplicate target database for standby from active database nofilenamecheck;

 [oracle@db01 ~]$ rman target sys/oracle@pri auxiliary sys/oracle@sty nocatalog

 Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jun 17 22:33:54 2015

 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 connected to target database: WOO (DBID=4221729487)
 using target database control file instead of recovery catalog
 connected to auxiliary database: WOO (not mounted)

 RMAN>

 RMAN> duplicate target database for standby from active database nofilenamecheck;

 Starting Duplicate Db at 17-JUN-15
 allocated channel: ORA_AUX_DISK_1
 channel ORA_AUX_DISK_1: SID=19 device type=DISK

 contents of Memory Script:
 {
    backup as copy reuse
    targetfile  '/DBSoft/oracle/product/11.2.4/dbhome_1/dbs/orapwwoo' auxiliary format
  '/DBSoft/oracle/product/11.2.4/dbhome_1/dbs/orapwwoo'  ;
 }
 executing Memory Script

 Starting backup at 17-JUN-15
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=44 device type=DISK
 Finished backup at 17-JUN-15

 contents of Memory Script:
 {
    backup as copy current controlfile for standby auxiliary format  '/DBSoft/oracle/oradata/woo/control01.ctl';
    restore clone controlfile to  '/DBSoft/oracle/fast_recovery_area/woo/control02.ctl' from
  '/DBSoft/oracle/oradata/woo/control01.ctl';
 }
 executing Memory Script

 Starting backup at 17-JUN-15
 using channel ORA_DISK_1
 channel ORA_DISK_1: starting datafile copy
 copying standby control file
 output file name=/DBSoft/oracle/product/11.2.4/dbhome_1/dbs/snapcf_woo.f tag=TAG20150617T223502 RECID=1 STAMP=882657308
 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
 Finished backup at 17-JUN-15

 Starting restore at 17-JUN-15
 using channel ORA_AUX_DISK_1

 channel ORA_AUX_DISK_1: copied control file copy
 Finished restore at 17-JUN-15

 contents of Memory Script:
 {
    sql clone 'alter database mount standby database';
 }
 executing Memory Script

 sql statement: alter database mount standby database

 contents of Memory Script:
 {
    set newname for tempfile  1 to
  "/DBSoft/oracle/oradata/woo/temp01.dbf";
    switch clone tempfile all;
    set newname for datafile  1 to
  "/DBSoft/oracle/oradata/woo/system01.dbf";
    set newname for datafile  2 to
  "/DBSoft/oracle/oradata/woo/sysaux01.dbf";
    set newname for datafile  3 to
  "/DBSoft/oracle/oradata/woo/undotbs01.dbf";
    set newname for datafile  4 to
  "/DBSoft/oracle/oradata/woo/users01.dbf";
    backup as copy reuse
    datafile  1 auxiliary format
  "/DBSoft/oracle/oradata/woo/system01.dbf"  datafile
  2 auxiliary format
  "/DBSoft/oracle/oradata/woo/sysaux01.dbf"  datafile
  3 auxiliary format
  "/DBSoft/oracle/oradata/woo/undotbs01.dbf"  datafile
  4 auxiliary format
  "/DBSoft/oracle/oradata/woo/users01.dbf"  ;
    sql 'alter system archive log current';
 }
 executing Memory Script

 executing command: SET NEWNAME

 renamed tempfile 1 to /DBSoft/oracle/oradata/woo/temp01.dbf in control file

 executing command: SET NEWNAME

 executing command: SET NEWNAME

 executing command: SET NEWNAME

 executing command: SET NEWNAME

 Starting backup at 17-JUN-15
 using channel ORA_DISK_1
 channel ORA_DISK_1: starting datafile copy
 input datafile file number=00001 name=/DBSoft/oracle/oradata/woo/system01.dbf
 output file name=/DBSoft/oracle/oradata/woo/system01.dbf tag=TAG20150617T223532
 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:05:58
 channel ORA_DISK_1: starting datafile copy
 input datafile file number=00002 name=/DBSoft/oracle/oradata/woo/sysaux01.dbf
 output file name=/DBSoft/oracle/oradata/woo/sysaux01.dbf tag=TAG20150617T223532
 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:05:30
 channel ORA_DISK_1: starting datafile copy
 input datafile file number=00003 name=/DBSoft/oracle/oradata/woo/undotbs01.dbf
 output file name=/DBSoft/oracle/oradata/woo/undotbs01.dbf tag=TAG20150617T223532
 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
 channel ORA_DISK_1: starting datafile copy
 input datafile file number=00004 name=/DBSoft/oracle/oradata/woo/users01.dbf
 output file name=/DBSoft/oracle/oradata/woo/users01.dbf tag=TAG20150617T223532
 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
 Finished backup at 17-JUN-15

 sql statement: alter system archive log current

 contents of Memory Script:
 {
    switch clone datafile all;
 }
 executing Memory Script

 datafile 1 switched to datafile copy
 input datafile copy RECID=1 STAMP=882658052 file name=/DBSoft/oracle/oradata/woo/system01.dbf
 datafile 2 switched to datafile copy
 input datafile copy RECID=2 STAMP=882658052 file name=/DBSoft/oracle/oradata/woo/sysaux01.dbf
 datafile 3 switched to datafile copy
 input datafile copy RECID=3 STAMP=882658052 file name=/DBSoft/oracle/oradata/woo/undotbs01.dbf
 datafile 4 switched to datafile copy
 input datafile copy RECID=4 STAMP=882658052 file name=/DBSoft/oracle/oradata/woo/users01.dbf
 Finished Duplicate Db at 17-JUN-15

#至此已經恢複完成

6、在primary 和standby端添加standby日誌

SQL> alter database add standby logfile
 
group 4 ('/DBSoft/oracle/oradata/woo/styredo04.log') size 50m,

group 5 ('/DBSoft/oracle/oradata/woo/styredo05.log') size 50m,

group 6 ('/DBSoft/oracle/oradata/woo/styredo06.log') size 50m,

group 7 ('/DBSoft/oracle/oradata/woo/styredo07.log') size 50m;

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

    GROUP# THREAD# SEQUENCE# ARC STATUS

---------- ---------- ---------- --- ----------

        4 0 0 YES UNASSIGNED

        5 0 0 YES UNASSIGNED

        6 0 0 YES UNASSIGNED

        7 0 0 YES UNASSIGNED

7、在standby端開啟即時日誌應用

SQL> recover managed standby database using current logfile disconnect from session;
 
Media recovery complete.

SQL>

1 2 3 下一頁