本文章參考於：http://www.cnblogs.com/qipilang/archive/2010/03/18/1689022.html 和ORACLE10g 官方文件
本次實驗演示的是建立一個physical standby database，實驗環境如下：
虛擬機器版本：vmware9.0 作業系統版本：CentOS release 6.4
主資料庫版本：Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
備用資料庫配置：只安裝了資料庫軟體
1.確保primary database處於歸檔模式
修改資料庫歸檔路徑，並設定歸檔模式，如下：
SQL> alter system set log_archive_dest_1='location=/u01/app/my_arch';

System altered.
SQL> shutdown immediate
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/my_arch
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2
2.設定Primary Database為Forced Logging模式：

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> select FORCE_LOGGING from v$database;

FOR
---
YES
批註：資料庫指定為FORCE LOGGING後，資料庫將會記錄除臨時表空間或臨時回滾段外的所有操作，而忽略類似NOLOGGING之類的指定引數。如果在執行FORCE LOGGING時有NOLOGGING之類的語句在執行，那麼FORCE LOGGING會等待，直到這類語句全部執行。
FORCE LOGGING是作為固定引數儲存在控制檔案中，因為其不受重啟之類的操作的影響(只執行一次即可)，如果想取消，可以透過ALTER DATABASE NO FORCE LOGGING語句關閉強制記錄。
3.建立金鑰檔案
同一個Data Guard配置中所有資料庫必須都擁有獨立的金鑰檔案，並且必須儲存在同一個Data Guard配置中，所有資料庫伺服器的SYS使用者擁有相同的密碼，以保證REDO資料的順利傳輸，因為REDO傳輸服務是透過認證的網路會話來傳輸REDO資料，而會話使用包含在金鑰檔案中的SYS使用者密碼來認證。
[oracle@dayong ~]$ orapwd
Usage: orapwd file= password= entries= force=

where
    file - name of password file (mand),
    password - password for SYS (mand),
    entries - maximum number of distinct DBA and     force - whether to overwrite existing file (opt),
OPERs (opt),
There are no spaces around the equal-to (=) character.
[oracle@dayong ~]$ orapwd file=/u01/app/oracle/dbs/orapworcl password=oracle
4.建立standby logfile group
檢視主庫日誌組情況，如下：
SQL> select a.GROUP#,a.MEMBERS,b.MEMBER,a.BYTES/1024/1024 MB
2 from v$log a,v$logfile b where a.group#=b.group# order by 1;

    GROUP#    MEMBERS MEMBER                                                     MB
---------- ---------- -------------------------------------------------- ----------
         1          1 /u01/app/oradata/myorcl/redo01.log                         50
         2          1 /u01/app/oradata/myorcl/redo02.log                         50
         3          1 /u01/app/oradata/myorcl/redo03.log                         50

新增新的Standby Redologs組，同時為該組指定一個成員，如下：
[oracle@dayong oradata]$ mkdir orcl --首先建立日誌檔案存放目錄
[oracle@dayong oradata]$ pwd
/u01/app/oradata
SQL> alter database add standby logfile group 4 ('/u01/app/oradata/orcl/standbyrd01.log') size 50m;

SQL> alter database add standby logfile group 5 ('/u01/app/oradata/orcl/standbyrd02.log') size 50m;

SQL> alter database add standby logfile group 6 ('/u01/app/oradata/orcl/standbyrd03.log') size 50m;

透過動態效能檢視v$logfile檢視當前資料庫建立的Standby Redologs，如下：
SQL> select group#,status,type,member from v$logfile;

    GROUP# STATUS TYPE    MEMBER
---------- ------- ------- --------------------------------------------------
         3         ONLINE /u01/app/oradata/myorcl/redo03.log
         2         ONLINE /u01/app/oradata/myorcl/redo02.log
         1         ONLINE /u01/app/oradata/myorcl/redo01.log
         4         STANDBY /u01/app/oradata/orcl/standbyrd01.log
         5         STANDBY /u01/app/oradata/orcl/standbyrd02.log
         6         STANDBY /u01/app/oradata/orcl/standbyrd03.log

6 rows selected.

5.配置primary database的初始化引數

Database	DB_UNIQUE_NAME	Oracle Net Service Name
Primary	myorcl	myorcl
Physical Standby	orcl	orcl

新建立的資料庫是以伺服器引數檔案的形式啟動的，伺服器參事檔案是以二進位制方式存在的，不能手工編輯；我們建立客戶端的初始化引數檔案pfile並修改，然後在透過spfile建立pfile，操作如下：

SQL> create pfile='/u01/app/oracle/dbs/orcl_pfile.ora' from spfile; --透過spfile建立pfile

File created.

修改客戶端初始化引數檔案vi /u01/app/oracle/dbs/orcl_pfile.ora
新增的內容如下：
27 *.DB_UNIQUE_NAME=myorcl
28 *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(myorcl,orcl)'
29 *.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/my_arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=myorcl'
30 *.LOG_ARCHIVE_DEST_2='SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES ,PRIMARY_ROLE)DB_UNIQUE_NAME=orcl'
31 *.LOG_ARCHIVE_DEST_STATE_1=ENABLE
32 *.LOG_ARCHIVE_DEST_STATE_2=ENABLE
33 *.FAL_SERVER=orcl
34 *.FAL_CLIENT=myorcl
35 *.DB_FILE_NAME_CONVERT='orcl','myorcl'
36 *.STANDBY_FILE_MANAGEMENT=AUTO

1)以修改過的引數檔案啟動資料庫並生成伺服器引數檔案，再讓資料庫以伺服器引數檔案的方式啟動資料庫：
SQL> shutdown immediate
SQL> startup pfile='/u01/app/oracle/dbs/orcl_pfile.ora' nomount;
ORACLE instance started.

Total System Global Area 281018368 bytes
Fixed Size                  2020160 bytes
Variable Size             117443776 bytes
Database Buffers          155189248 bytes
Redo Buffers                6365184 bytes

2)透過pfile建立備庫的spfile
SQL> create spfile from pfile='/u01/app/oracle/dbs/orcl_pfile.ora';

File created.

3)重啟資料庫
SQL> shutdown immediate
SQL> startup
ORACLE instance started.

Total System Global Area 281018368 bytes
Fixed Size                  2020160 bytes
Variable Size             117443776 bytes
Database Buffers          155189248 bytes
Redo Buffers                6365184 bytes
Database mounted.
Database opened.

6.在主庫中建立備庫的控制檔案
SQL> alter database create standby controlfile as '/u01/app/oradata/orcl/standbycontrol01.ctl';

SQL> alter database create standby controlfile as '/u01/app/oradata/orcl/standbycontrol02.ctl';

SQL> alter database create standby controlfile as '/u01/app/oradata/orcl/standbycontrol03.ctl';

7.複製相關檔案到備庫
需要複製的檔案包括：所有的資料檔案、重做日誌檔案、金鑰檔案，以及剛剛建立的Standby Database的控制檔案和客戶端初始化引數檔案(用來建立Standby Database存檔伺服器端引數檔案)。
1)複製金鑰檔案
[oracle@dayong dbs]$ scp orapworcl /
The authenticity of host '10.41.6.119 (10.41.6.119)' can't be established.
RSA key fingerprint is 83:3e:27:18:f1:ce:eb:81:dd:7b:0f:8d:5d:4f:48:3d.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.41.6.119' (RSA) to the list of known hosts.
password:
orapworcl                                                                                  100% 1536     1.5KB/s   00:00
2)複製控制檔案
[oracle@dayong oradata]$ cd orcl/
[oracle@dayong orcl]$ ls
standbycontrol01.ctl standbycontrol02.ctl standbycontrol03.ctl standbyrd01.log standbyrd02.log standbyrd03.log
[oracle@dayong orcl]$ scp standbycontrol*
password:
standbycontrol01.ctl                                                                       100% 6896KB   6.7MB/s   00:00
standbycontrol02.ctl                                                                       100% 6896KB   6.7MB/s   00:00
standbycontrol03.ctl                                                                       100% 6896KB   6.7MB/s   00:00
3)複製引數檔案
[oracle@dayong dbs]$ scp orcl_pfile.ora
password:
orcl_pfile.ora                                                                             100% 1014     1.0KB/s   00:00
4)複製資料檔案
首先查詢當前資料庫的表空間和資料檔案
SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files;

FILE_NAME                                     TABLESPACE_NAME
--------------------------------------------- ------------------------------
/u01/app/oradata/myorcl/users01.dbf           USERS
/u01/app/oradata/myorcl/sysaux01.dbf          SYSAUX
/u01/app/oradata/myorcl/undotbs01.dbf         UNDOTBS1
/u01/app/oradata/myorcl/system01.dbf          SYSTEM
/u01/app/oradata/myorcl/example01.dbf         EXAMPLE

SQL> alter tablespace USERS begin backup; --告知資料庫對錶空間進行備份

SQL> alter tablespace SYSAUX begin backup;

SQL> alter tablespace UNDOTBS1 begin backup;

SQL> alter tablespace SYSTEM begin backup;

SQL> alter tablespace EXAMPLE begin backup;
複製表空間到備庫，如下：
[oracle@dayong myorcl]$ ls
control01.ctl control03.ctl redo01.log redo03.log    system01.dbf undotbs01.dbf
control02.ctl example01.dbf redo02.log sysaux01.dbf temp01.dbf    users01.dbf
[oracle@dayong myorcl]$ scp *.dbf
password:
example01.dbf                                                                              100% 100MB   9.1MB/s   00:11
sysaux01.dbf                                                                               100% 240MB   8.6MB/s   00:28
system01.dbf                                                                               100% 480MB   8.3MB/s   00:58
temp01.dbf                                                                                 100%   20MB 10.0MB/s   00:02
undotbs01.dbf                                                                              100%   30MB   7.5MB/s   00:04
users01.dbf                                                                                100% 5128KB   1.7MB/s   00:03
SQL> alter tablespace USERS end backup;      --告知資料庫結束表空間備份

SQL> alter tablespace SYSAUX end backup;

SQL> alter tablespace UNDOTBS1 end backup;

SQL> alter tablespace SYSTEM end backup;

SQL> alter tablespace EXAMPLE end backup;

8.配置監聽和網路服務名
配置監聽和TNS，以方便主、備節點互通有無，並且必須是靜態監聽。

因為如果配置的是動態監聽，只要有例項沒有啟動到open狀態，那就無法透過網路將服務註冊到資料庫。

1)對primary database的監聽配置如下：
下面顯示的是配置靜態監聽以及TNS的內容：
[oracle@dayong admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle)
      (PROGRAM = extproc)
    )
     (SID_DESC =
      (GLOBAL_DBNAME=myorcl)
      (ORACLE_HOME = /u01/app/oracle)
      (SID_NAME =myorcl)
     )
)

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dayong)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
)

重啟監聽生效，如下：
[oracle@dayong admin]$ lsnrctl stop
[oracle@dayong admin]$ lsnrctl start
2)對primary database的net service name進行配置，如下：
[oracle@dayong admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

MYORCL =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.41.6.118)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = myorcl)
    )
)
ORCL =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.41.6.119)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
)

9.物理standby database的配置
1)配置監聽，如下：
[oracle@xiaoru admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME= orcl)
      (ORACLE_HOME= /u01/app/oracle)
      (SID_NAME= orcl)
    )
)

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xiaoru)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
)

重啟監聽：
[oracle@xiaoru admin]$ lsnrctl stop
[oracle@xiaoru admin]$ lsnrctl start
2)配置Net Service Name
[oracle@dayong admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

MYORCL =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.41.6.118)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = myorcl)
    )
)
ORCL =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.41.6.119)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
)
3)在兩臺伺服器之間使用tnsping命令互相測試網路服務伺服器是否可用，如下：

[oracle@dayong ~]$ tnsping orcl --從主庫測試能否與備庫的監聽相通 (注意需要關閉iptables)

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 26-MAR-2014 23:49:38

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.41.6.119)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (10 msec) --顯示可以監聽能夠tnsping通
[oracle@xiaoru admin]$ tnsping myorcl --從備庫測試能否與主庫的監聽相通
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 27-MAR-2014 16:31:13

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.41.6.118)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = myorcl)))
OK (0 msec) --顯示可以監聽能夠tnsping通
4)建立日誌檔案相關目錄。包括Oracle內部程式的輸出資訊和使用者程式等儲存記錄：
[oracle@xiaoru admin]$ mkdir orcl
[oracle@xiaoru admin]$ cd orcl/
[oracle@xiaoru orcl]$ mkdir adump bdump cdump dpdump udump
[oracle@xiaoru orcl]$ ls
adump bdump cdump dpdump udump
[oracle@xiaoru orcl]$ pwd
/u01/app/admin/orcl
5)修改客戶端的初始化引數檔案
找到剛剛從Primary Database複製過來的客戶端初始化引數檔案/u01/app/oracle/dbs/orcl_pfile.ora 修改如下：
[oracle@xiaoru pfile]$ cat orcl_pfile.ora
myorcl.__db_cache_size=155189248
myorcl.__java_pool_size=4194304
myorcl.__large_pool_size=4194304
myorcl.__shared_pool_size=109051904
myorcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/admin/orcl/adump'
*.background_dump_dest='/u01/app/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oradata/orcl/standbycontrol01.ctl','/u01/app/oradata/orcl/standbycontrol02.ctl','/u01/app/oradata/orcl/standbycontrol03.ctl'
*.core_dump_dest='/u01/app/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='myorcl' ---此處要注意了，一定要是主資料庫的DB_NAME，要不然啟動資料庫到mount狀態時會報錯
*.db_recovery_file_dest='/u01/app/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=myorclXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/u01/app/my_arch'
*.open_cursors=300
*.pga_aggregate_target=93323264
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=281018368
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/admin/orcl/udump'
*.DB_UNIQUE_NAME=orcl
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,myorcl)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/my_arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.LOG_ARCHIVE_DEST_2='SERVICE=myorcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=myorcl'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER=myorcl
*.FAL_CLIENT=orcl
6)修改並儲存後，以sysdba身份連線到Standby資料庫，透過該pfile建立spfile：
SQL> create spfile from pfile='/u01/app/oracle/dbs/orcl_pfile.ora';

File created.

7)啟動物理standby database到mount狀態：
SQL> startup mount
ORACLE instance started.

Total System Global Area 281018368 bytes
Fixed Size                  2020160 bytes
Variable Size             117443776 bytes
Database Buffers          155189248 bytes
Redo Buffers                6365184 bytes
Database mounted.

8)啟動standby database到redo apply
即使Standby Database當前處於OPEN狀態，也不需要重啟資料庫，直接執行REDO應用語句即可，ORACLE會自動將資料庫切換至MOUNT狀態，如下：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; --若要暫停應用，如是操作

批註：此時只是暫停REDO應用，並不是停止物理Standby Database，Standby 仍會保持接收，只不過不會再應用接收到的歸檔，直到再次啟動REDO應用為止。
10 驗證
1)在主庫手動切換日誌
SQL> ALTER SYSTEM SWITCH LOGFILE;
2)備庫查詢檢視 V$ARCHIVED_LOG
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
3 27-MAR-14 27-MAR-14
3)在主庫再次切換日誌
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
4)備庫查詢檢視
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
3 27-MAR-14 27-MAR-14
4 27-MAR-14 27-MAR-14
11.簡單測試
1)主庫操作如下：
SQL> conn scott/tiger;
SQL> insert into dept values (50,'huyong','xiaoru');
SQL> commit;
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
50 huyong xiaoru
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> conn / as sysdba
SQL> alter system switch logfile;
2)將 Standby db 啟動到read only 模式下，觀察剛才插入的記錄是否已經被應用
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open;
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
orcl OPEN
SQL> select open_mode from v$database;
OPEN_MODE
----------
READ ONLY
SQL> select * from scott.dept;
DEPTNO DNAME LOC
---------- -------------- -------------
50 huyong xiaoru
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
備庫上檢視日誌：
SQL> SELECT NAME, CREATOR, SEQUENCE#, APPLIED, COMPLETION_TIME FROM V$ARCHIVED_LOG;
NAME CREATOR SEQUENCE# APP COMPLETIO
--------------------------------------------- ------- ---------- --- ---------
/u01/app/my_arch/1_3_843246228.dbf ARCH 3 YES 26-MAR-14
/u01/app/my_arch/1_4_843246228.dbf LGWR 4 YES 26-MAR-14
/u01/app/my_arch/1_5_843246228.dbf LGWR 5 YES 26-MAR-14

ORACLE10g DataGuard 配置Physical Standby Database

相關文章