ORACLE11GR2 RAC DATABASE+STANDLONE STANDBY配置摘要
一、目標
為叢集資料庫配置DATAGUARD，同時實現SWITCH OVER，同時DATAGUARD端並沒有使用ASM，其配置方法預計和單庫區別不大，主要在於RMAN恢復RAC資料庫到單庫，同時DATAGUARD互聯準備使用SCAN IP而非VIP。
整個安裝過程注意資料檔案目錄的改變，為了避免不必要的麻煩，DATAGUARD端我們使用OMF，官方文件如下：
If the primary database is configured to use OMF, then Oracle recommends that the standby database be configured to use OMF, too.
To do this, set the DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n initialization parameters to appropriate values. Maintenance
and future role transitions are simplified if the same disk group names are used for both the primary and standby databases.
If OMF parameters are set on the standby, then new files on that standby are always created as OMF, regardless of how they were
created on the primary. Therefore, if both the DB_FILE_NAME_CONVERT and DB_CREATE_FILE_DEST parameters are set on the standby,
the DB_CREATE_FILE_DEST parameter takes precedence.
注意到一旦設定了OMF我們的DB_FILE_NAME_CONVERT和log_file_name_convert引數是不需要設定的。因為OMF會優先於這兩個引數。

二、環境描述

基礎環境（叢集端）
伺服器型號 Vmware Esx 4 虛擬機器
作業系統 Oracle Linux Server release 6.4
資料庫版本 Oracle database 11.2.0.3
Grid Infrastructure 版本 Grid Infrastructure 11.2.0.3
PUBLIC IP 192.168.1.141 192.168.1.142
VIP 192.168.1.143 192.168.1.144
PRI IP（雙心跳） 10.10.10.3 10.10.10.4 10.10.11.3 10.10.11.4
SCAN IP 192.168.1.145
DATABASE NAME ORA11G

基礎環境（STANDBY 端）
伺服器型號 Vmware Esx 4 虛擬機器
作業系統 Red Hat Enterprise Linux Server release 6.0 (Santiago)
資料庫版本 Oracle database 11.2.0.3
IP 192.168.1.170
DATABASE NAME ORA11G
三、配置
1、同單庫一樣可以首先改寫唯一不能靜態修改的引數
alter system set db_unique_name='ora11grac' scope=spfile sid='*';

2、啟動FORCE LOGGING
ALTER DATABASE FORCE LOGGING;
3、重啟RAC資料庫,讓修改的UNIQUE引數生效，其他的引數就可以動態修改了
srvctl stop database –d ora11g –o immediate
srvctl start database –d ora11g
4、注意修改UNIQUE NAME後SERVICE 會響應的修改為UNIQUE的名字如下:
Services Summary...
Service "ora11gXDB" has 2 instance(s).
Instance "ora11g1", status READY, has 1 handler(s) for this service...
Instance "ora11g2", status READY, has 1 handler(s) for this service...
Service "ora11grac" has 2 instance(s).
Instance "ora11g1", status READY, has 2 handler(s) for this service...
Instance "ora11g2", status READY, has 2 handler(s) for this service...

我們加入一個SERVICE
alter system set service_names=ora11grac,ora11g scope=both sid='*';
然後SCAN_LISTENER SERVICE如下：
Services Summary...
Service "ora11g" has 2 instance(s).
Instance "ora11g1", status READY, has 2 handler(s) for this service...
Instance "ora11g2", status READY, has 2 handler(s) for this service...
Service "ora11gXDB" has 2 instance(s).
Instance "ora11g1", status READY, has 1 handler(s) for this service...
Instance "ora11g2", status READY, has 1 handler(s) for this service...
Service "ora11grac" has 2 instance(s).
Instance "ora11g1", status READY, has 2 handler(s) for this service...
Instance "ora11g2", status READY, has 2 handler(s) for this service...
The command completed successfully

5、我們可以提前配置好TNSNAMES.ORA，RAC 使用SCAN IP進行連線
RAC雙節點和DATAUGARD節點同時加入如下：
ora11grac =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.145)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora11grac)
)
)
ora11gdg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.170)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora11gdg)
)
)

接下來我們為DATAGUARD端準備環境：

6、首先建立RAC的pfile檔案，預設的pfile是指向ASM spfile的一個指標如下：
[oracle@rac2 dbs]$ more initora11g2.ora
SPFILE='+DATA/ora11g/spfileora11g.ora'
所以我們不要用
Create pfile from spfile 而是要注意制定以下路徑
create pfile='/home/oracle/pfile.ora' from spfile;

得到RAC資料庫的配置檔案
*.audit_file_dest='/oracle/app/oracle/admin/ora11g/adump' 更改為
*.audit_file_dest='/home/oradba/ora11g/admin/ora11g/adump'
*.audit_trail='db'
*.cluster_database=true --去掉
*. service_names=ora11grac,ora11g更改為
*. service_names=ora11gdg,ora11g
*.compatible='11.2.0.0.0'
*.control_files='+DATA/ora11g/controlfile/current.262.858666455','+ARCH/ora11g/controlfile/current.314.858666455' 更改為
*.control_files='/bak/ora11g/data/current01.dbf','/bak/ora11g/data/current02.dbf'
*.db_block_size=8192
*.db_create_file_dest='+DATA' --使用OMF可以進行修改
*.db_create_file_dest='/bak/ora11g/data'
*.db_create_online_log_dest_1='+DATA' --使用OMF可以進行修改
*.db_create_online_log_dest_1='/bak/ora11g/data'
*.db_create_online_log_dest_2='+ARCH' --使用OMF可以進行修改
*.db_create_online_log_dest_2='/bak/ora11g/data'
*.db_domain=''
*.db_name='ora11g'
*.db_unique_name='ora11grac' 更改為
*.db_unique_name='ora11gdg'
*.diagnostic_dest='/oracle/app/oracle' 更改為
*.diagnostic_dest='/home/oradba/ora11g'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora11gXDB)'
ora11g1.instance_number=1 --去掉
ora11g2.instance_number=2 --去掉
*.log_archive_dest_1='LOCATION=+ARCH'更改為
*.log_archive_dest_1='LOCATION=/bak/ora11g/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ora11gdg'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=162529280
*.processes=150
*.remote_listener='racscan:1521' --去掉
*.remote_login_passwordfile='exclusive'
*.sessions=170
*.sga_target=488636416
ora11g2.thread=2 --去掉
ora11g1.thread=1 --去掉
ora11g1.undo_tablespace='UNDOTBS1'更改為
*.undo_tablespace='UNDOTBS1'
ora11g2.undo_tablespace='UNDOTBS2' --去掉

最後修改的引數檔案如下：

--原始引數
*.audit_file_dest='/home/oradba/ora11g/admin/ora11g/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.service_names=ora11gdg,ora11g
*.control_files='/bak/ora11g/data/current01.dbf','/bak/ora11g/data/current02.dbf'
*.db_block_size=8192
*.db_domain=''
*.db_name='ora11g'
*.db_unique_name='ora11gdg'
*.diagnostic_dest='/home/oradba/ora11g'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora11gXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=162529280
*.processes=150
*.remote_login_passwordfile='exclusive'
*.sessions=170
*.sga_target=488636416
*.undo_tablespace='UNDOTBS1'
*.db_create_file_dest='/bak/ora11g/data'
*.db_create_online_log_dest_1='/bak/ora11g/data'
*.db_create_online_log_dest_2='/bak/ora11g/data'

--加入DATAGUARD相關的引數如下：
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(ora11gdg,ora11grac)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/bak/ora11g/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ora11gdg'
*.LOG_ARCHIVE_DEST_2='SERVICE=ora11grac LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ora11grac'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
*.LOG_ARCHIVE_MAX_PROCESSES=2
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.FAL_SERVER=ora11grac
*.FAL_CLIENT=ora11gdg

7、主庫進行RMAN備份，然後備份STANDBY CONTROLFILE，隨後傳輸密碼檔案，備份檔案，STANDBY控制檔案到備庫
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/boston.ctl';
8、接下來啟動到MOUNT階段，同時準備進行恢復資料檔案，預計使用了OMF,RMAN會使用新的OMF位置索引不用SET NEWNAME也不用SWITCH DATABASE了，因為OMF會自動更新控制檔案資訊

RMAN> restore database
2> ;

Starting restore at 14-MAR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/ora11g/datafile/system.265.858666317
channel ORA_DISK_1: restoring datafile 00002 to +DATA/ora11g/datafile/sysaux.258.858666317
channel ORA_DISK_1: restoring datafile 00003 to +DATA/ora11g/datafile/undotbs1.259.858666317
channel ORA_DISK_1: restoring datafile 00004 to +DATA/ora11g/datafile/users.261.858666319
channel ORA_DISK_1: restoring datafile 00005 to +DATA/ora11g/datafile/undotbs2.272.858666695
channel ORA_DISK_1: restoring datafile 00006 to +DATA/ora11grac/datafile/testpp.276.858711957
channel ORA_DISK_1: reading from backup piece /home/oracle/bak_1_1_01pitrl1_1_1.bak
channel ORA_DISK_1: piece handle=/home/oracle/bak_1_1_01pitrl1_1_1.bak tag=TAG20140919T193600
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:16
Finished restore at 14-MAR-14

可以看到已經恢復成功，並且使用的目錄就是我們設定的OMF的資料目錄
[root@dg1 datafile]# ls -lrt
total 1409460
-rw-r----- 1 oradba dba 5251072 Mar 14 04:24 o1_mf_users_9l450d89_.dbf
-rw-r----- 1 oradba dba 10493952 Mar 14 04:24 o1_mf_testpp_9l450d6c_.dbf
-rw-r----- 1 oradba dba 26222592 Mar 14 04:24 o1_mf_undotbs2_9l450d5j_.dbf
-rw-r----- 1 oradba dba 78651392 Mar 14 04:24 o1_mf_undotbs1_9l450d48_.dbf
-rw-r----- 1 oradba dba 576724992 Mar 14 04:25 o1_mf_sysaux_9l450d33_.dbf
-rw-r----- 1 oradba dba 744497152 Mar 14 04:25 o1_mf_system_9l450d2b_.dbf
[root@dg1 datafile]# pwd
/bak/ora11g/data/ORA11GDG/datafile
檢視控制檔案DATAFILE資訊：

/bak/ora11g/data/ORA11GDG/datafile/o1_mf_system_9l450d2b_.dbf
/bak/ora11g/data/ORA11GDG/datafile/o1_mf_sysaux_9l450d33_.dbf
/bak/ora11g/data/ORA11GDG/datafile/o1_mf_undotbs1_9l450d48_.dbf
/bak/ora11g/data/ORA11GDG/datafile/o1_mf_users_9l450d89_.dbf
/bak/ora11g/data/ORA11GDG/datafile/o1_mf_undotbs2_9l450d5j_.dbf
/bak/ora11g/data/ORA11GDG/datafile/o1_mf_testpp_9l450d6c_.dbf

如果本步報錯
ORA-19504: failed to create file "+DATA/racdb/datafile/data01.dbf"
ORA-17502: ksfdcre:3 Failed to create file +DATA/racdb/datafile/data01.dbf
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-15077: could not locate ASM instance serving a required diskgroup
ORA-29701: unable to connect to Cluster Synchronization Servic

可以執行如下：
You are restoring or duplicating the target database to a new host using RMAN
The datafiles are not OMF files and you want to make them OMF. Using 'set newname for
datafile to NEW' will generate a new OMF filename for the restored datafile.
This will avoid the manual entry or vi/notepad editing of similar output.
Using this output the datafiles will be restored to the DB_CREATE_FILE_DEST.
If this parameter is not set you must add the correct path as in '/path/NEW'
will direct the files to the new location and give an OMF filename.

run {
set newname for datafile 1 to NEW;
set newname for datafile 2 to NEW;
set newname for datafile 3 to NEW;
set newname for datafile 4 to NEW;
set newname for datafile 5 to NEW;
set newname for datafile 6 to NEW;
set newname for datafile 7 to NEW;
restore database ;
switch datafile all;
}

9、在備庫增加STANDBY LOGFILE，數量為RAC總LOGFILE GROUP+1

alter database add standby logfile group 7 size 50m;
alter database add standby logfile group 8 size 50m;
alter database add standby logfile group 9 size 50m;
alter database add standby logfile group 10 size 50m;
alter database add standby logfile group 11 size 50m;
alter database add standby logfile group 12 size 50m;
alter database add standby logfile group 13 size 50m;

以下完成RAC主庫端的設定：

10、引數修改

DB_UNIQUE_NAME=ora11grac --已經更改
LOG_ARCHIVE_CONFIG='DG_CONFIG=(ora11gdg,ora11grac)' --需要更改
LOG_ARCHIVE_DEST_1='LOCATION=+ARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ora11grac' -需要更改
LOG_ARCHIVE_DEST_2='SERVICE=ora11gdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ora11gdg' -需要更改
LOG_ARCHIVE_DEST_STATE_1=ENABLE -可以更改
LOG_ARCHIVE_DEST_STATE_2=ENABLE -可以更改
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE --已經更改
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc --不能更改
LOG_ARCHIVE_MAX_PROCESSES=2 --可以更改
STANDBY_FILE_MANAGEMENT='AUTO' --必須修改
FAL_SERVER=ora11gdg --需要更改
FAL_CLIENT=ora11grac --需要更改

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(ora11gdg,ora11grac)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=+ARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ora11grac' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=ora11gdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ora11gdg' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both sid='*';
alter system set LOG_ARCHIVE_MAX_PROCESSES=2 scope=both sid='*';
alter system set FAL_SERVER=ora11gdg scope=both sid='*';
alter system set FAL_CLIENT=ora11grac scope=both sid='*';
alter system set STANDBY_FILE_MANAGEMENT='AUTO' scope=both sid='*';

10、主庫也建立的standby logfile

alter database add standby logfile thread 1 group 7 size 50m;
alter database add standby logfile thread 1 group 8 size 50m;
alter database add standby logfile thread 1 group 9 size 50m;
alter database add standby logfile thread 1 group 10 size 50m;

alter database add standby logfile thread 2 group 11 size 50m;
alter database add standby logfile thread 2 group 12 size 50m;
alter database add standby logfile thread 2 group 13 size 50m;
alter database add standby logfile thread 2 group 14 size 50m;

11、備庫啟動日誌應用

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

12、啟動ACTIVE STANDBY

取消重做應用：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
開啟資料庫以用於只讀訪問：
SQL> ALTER DATABASE OPEN;
如果重新
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
恢復到正常模式

13、檢查備份資料庫狀態

SQL> select * from v$dataguard_stats;

NAME VALUE UNIT TIME_COMPUTED DATUM_TIME
-------------------------------- ---------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------
transport lag +00 00:00:00 day(2) to second(0) interval 03/14/2014 05:50:54 03/14/2014 05:50:53
apply lag +00 00:00:00 day(2) to second(0) interval 03/14/2014 05:50:54 03/14/2014 05:50:53
apply finish time +00 00:00:00.000 day(2) to second(3) interval 03/14/2014 05:50:54
estimated startup time 48 second 03/14/2014 05:50:54

SQL> select * from v$managed_standby;

PROCESS PID STATUS CLIENT_PROCESS CLIENT_PID CLIENT_DBID GROUP# RESETLOG_ID THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS KNOWN_AGENTS ACTIVE_AGENTS
--------- ---------- ------------ -------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------- ---------- ---------- ---------- ---------- ---------- ------------ -------------
ARCH 3367 CLOSING ARCH 3367 4254866583 8 858666457 2 7 1 391 0 0 0
ARCH 3369 CONNECTED ARCH 3369 4254866583 N/A 0 0 0 0 0 0 0 0
RFS 3421 IDLE UNKNOWN 3516 4254866583 N/A 0 0 0 0 0 0 0 0
RFS 3417 IDLE LGWR 11479 4254866583 2 858666457 1 13 6473 1 0 0 0
RFS 3419 IDLE LGWR 7991 4254866583 3 858666457 2 8 6198 1 0 0 0
RFS 3423 IDLE UNKNOWN 3924 4254866583 N/A 0 0 0 0 0 0 0 0
MRP0 3598 APPLYING_LOG N/A N/A N/A N/A 858666457 1 13 6473 102400 0 0 0

14、進行SWITCH 測試

首先關閉一個RAC例項
[oracle@rac1 dbs]$ srvctl stop instance -d ora11g -i ora11g2
檢視叢集資料庫狀態
ora.ora11g.db
1 ONLINE ONLINE rac1 Open
2 OFFLINE OFFLINE Instance Shutdown
在剩餘節點執行
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO STANDBY
主庫進行切換：
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY (WITH SESSION SHUTDOWN);（有一點耗時,要關閉資料庫到MOUNT階段）
關閉主庫RAC剩餘的節點
srvctl stop database -d ora11g
然後啟動資料庫到MOUNT
srvctl start database -d ora11g -o mount

在備庫執行
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
結果應該是
SWITCHOVER_STATUS
--------------------
TO PRIMARY
然後
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY (WITH SESSION SHUTDOWN);
最後關閉資料庫啟動到open
shutdown immediate
startup

最後可以開啟備用RAC資料庫，讓RAC資料庫成為ACTIVE STANDBY
2個例項同時執行
alter database open;

最後2個例項同事啟動MRP程式
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

15、最後關注一下切換後RAC資料庫中關於STANDBY的程式
檢視節點1

PROCESS PID STATUS CLIENT_P CLIENT_PID
--------- ---------- ------------ -------- -------------------------------------
ARCH 16226 CLOSING ARCH 16226
ARCH 16228 CLOSING ARCH 16228
RFS 16307 IDLE LGWR 3729
RFS 16312 IDLE UNKNOWN 3727
MRP0 16563 APPLYING_LOG N/A N/A
節點1的所有程式和單庫沒有兩樣

檢視節點2
PROCESS PID STATUS CLIENT_P CLIENT_PID
--------- ---------- ------------ -------- -------------------------------------
ARCH 12240 CONNECTED ARCH 12240
ARCH 12242 CONNECTED ARCH 12242
雖然執行了開啟MRP程式語句節點的例項並沒有MRP程式，所以可以看到程式切換後DATAGUARD的恢復程式和傳輸程式實際是在THREAD 1程式的

總結：
1、RAC的DATAGUARD 如果使用OMF可以大大簡化，進行RMAN恢復的時候OMF會讓恢復自動恢復到正確的目錄，進行備庫OPEN的時候也會自動建立正確的LOGFILE 。其實不管資料庫級是否使用OMF，ASM實際都會使用OMF進行檔案管理
2、DATAGUARD的恢復程式和傳輸程式實際是在THREAD 1程式的
3、一旦設定了OMF我們的DB_FILE_NAME_CONVERT和log_file_name_convert引數是不需要設定的。因為OMF會優先於這兩個引數。

ORACLE11GR2 RAC DATABASE+STANDLONE DATAGUARD配置摘要

相關文章