說明：Oracle11.2.0.4 RAC/RedHat6.3_x64 à Oracle11.2.0.4/Oracle Linux 6.6

　　　主資料庫：racdb 備庫：phydb

1、網路配置：
主庫是透過SCAN IP進行網路訪問，顧可不做相關監聽配置，下面為備庫靜態監聽配置檔案：

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = phydb)

(ORACLE_HOME = /oracle/app/oracle/product/11.2.0)

(SID_NAME = phydb)

)

注意：為了確保能夠在Oracle Data Guard配置中的任意節點上，發生角色轉換或者故障轉移後恢復原來的狀態，必須為每個資料庫例項在各自的節點上建立一個靜態監聽器項。詳情可參考《【DATAGUARD】DG系列之DG代理（broker)的搭建和應用》，後邊文章會說明相關配置。

2、配置TNS，每個節點都需配置，本次測試主要針對RAC To ONE，暫不考慮RAC to RAC相關方面問題：

RACDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))

(SERVICE_NAME = racdb)

)

PHYDB =

(DESCRIPTION =

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

(SERVICE_NAME = phydb)

)

3、修改資料庫為歸檔模式，開啟強制日誌記錄

--歸檔不做相關說明，下面是開啟強制日誌記錄

SQL> alter database force logging;

4、備庫建立密碼檔案
#orapwd file=$ORACLE_HOME/dbs/orapwphydb password=oracle

5、複製引數檔案，修改，並啟動

create pfile=’/tmp/initphydb.ora’ from spfile;
scp /tmp/initphydb.ora oracle@oeloracle:$ORACLE_HOME/dbs
--將引數檔案修改為單例項，修改相關目錄

--啟動資料庫到nomount模式
export ORACLE_SID=phydb
create spfile from pfile;
startup nomount

6、修改初始化引數，修改完成後重啟各例項
主庫引數：

alter system set db_unique_name='racdb' scope=spfile sid='*';

alter system set standby_file_management=AUTO scope=both;

alter system set log_archive_config='DG_CONFIG=(racdb,phydb)' scope=both sid='*';

alter system set db_file_name_convert='/oracle/app/oracle/oradata/PHYDB','+DATA/racdb/datafile','/oracle/app/oracle/oradata/PHYDB','+DATA/racdb/tempfile' scope=spfile sid='*';

alter system set log_file_name_convert='/oracle/app/oracle/archivelog_phy','+DATA/racdb/archivelog' scope=spfile sid='*';

alter system set log_archive_dest_1='location=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=RACDB' scope=spfile sid='*';

alter system set log_archive_dest_2='SERVICE=phydb VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=phydb' scope=spfile sid='*';

alter system set log_archive_dest_state_2=enable scope=both sid='*';

alter system set fal_server='phydb' scope=spfile sid='*';

備庫引數：

alter system set db_unique_name='phydb' scope=spfile sid='*';

alter system set standby_file_management=AUTO scope=both;

alter system set log_archive_config='DG_CONFIG=(racdb,phydb)' scope=both sid='*';

alter system set db_file_name_convert='+DATA/racdb/datafile','/oracle/app/oracle/oradata/PHYDB','+DATA/racdb/tempfile','/oracle/app/oracle/oradata/PHYDB' scope=spfile sid='*';

alter system set log_file_name_convert='+DATA/racdb/archivelog','/oracle/app/oracle/archivelog_phy' scope=spfile sid='*';

alter system set log_archive_dest_1='location=/oracle/app/oracle/archivelog_phy VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=phydb' scope=spfile sid='*';

alter system set log_archive_dest_2='SERVICE=racdb VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdb' scope=spfile sid='*';

alter system set log_archive_dest_state_2=enable scope=both;

alter system set fal_server='racdb' scope=spfile sid='*';

7、建立備用重做日誌，在建立備用重做日誌時，必須使它們的大小與線上重做日誌相同，2為每個執行緒建立一個額外的備用重做日誌組。

alter database add standby logfile thread 1 group 7 '+DATA' size 200m;

alter database add standby logfile thread 2 group 8 '+DATA' size 200m;

alter database add standby logfile thread 1 group 9 '+DATA' size 200m;

alter database add standby logfile thread 2 group 10 '+DATA' size 200m;

alter database add standby logfile thread 1 group 11 '+DATA' size 200m;

alter database add standby logfile thread 2 group 12 '+DATA' size 200m;

8、使用duplicate屬性建立備用資料庫

[oracle@rac01 dbs]$ rman target sys/oracle@racdb auxiliary sys/oracle@phydb

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jul 2 09:19:55 2015

connected to target database: RACDB (DBID=857055310)

connected to auxiliary database: RACDB (not mounted)

RMAN> duplicate target database for standby from active database;

Starting Duplicate Db at 2015-07-02 09:19:59

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=192 device type=DISK

contents of Memory Script:

{

backup as copy reuse

targetfile '/oracle/app/oracle/product/11.2.0/dbs/orapwracdb1' auxiliary format

'/oracle/app/oracle/product/11.2.0/dbs/orapwphydb' ;

}

executing Memory Script

Starting backup at 2015-07-02 09:20:00

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=51 instance=racdb1 device type=DISK

Finished backup at 2015-07-02 09:20:01

contents of Memory Script:

{

backup as copy current controlfile for standby auxiliary format '/oracle/app/oracle/oradata/PHYDB/control01.dbf';

restore clone controlfile to '/oracle/app/oracle/oradata/PHYDB/control02.dbf' from

'/oracle/app/oracle/oradata/PHYDB/control01.dbf';

}

executing Memory Script

Starting backup at 2015-07-02 09:20:01

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying standby control file

output file name=/oracle/app/oracle/product/11.2.0/dbs/snapcf_racdb1.f tag=TAG20150702T092001 RECID=7 STAMP=883992006

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 2015-07-02 09:20:08

Starting restore at 2015-07-02 09:20:08

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 2015-07-02 09:20:09

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

"/oracle/app/oracle/oradata/PHYDB/temp.286.859438605";

switch clone tempfile all;

set newname for datafile 1 to

"/oracle/app/oracle/oradata/PHYDB/system.293.859438573";

set newname for datafile 2 to

"/oracle/app/oracle/oradata/PHYDB/sysaux.288.859438589";

set newname for datafile 3 to

"/oracle/app/oracle/oradata/PHYDB/undotbs1.287.859438601";

set newname for datafile 4 to

"/oracle/app/oracle/oradata/PHYDB/undotbs2.285.859438615";

set newname for datafile 5 to

"/oracle/app/oracle/oradata/PHYDB/users.278.859438619";

set newname for datafile 6 to

"/oracle/app/oracle/oradata/PHYDB/test1.261.859451689";

set newname for datafile 7 to

"/oracle/app/oracle/oradata/PHYDB/firsoul.259.882071697";

set newname for datafile 8 to

"/oracle/app/oracle/oradata/PHYDB/firsoul.263.882093749";

set newname for datafile 11 to

"/oracle/app/oracle/oradata/PHYDB/firsoul01.dbf";

backup as copy reuse

datafile 1 auxiliary format

"/oracle/app/oracle/oradata/PHYDB/system.293.859438573" datafile

2 auxiliary format

"/oracle/app/oracle/oradata/PHYDB/sysaux.288.859438589" datafile

3 auxiliary format

"/oracle/app/oracle/oradata/PHYDB/undotbs1.287.859438601" datafile

4 auxiliary format

"/oracle/app/oracle/oradata/PHYDB/undotbs2.285.859438615" datafile

5 auxiliary format

"/oracle/app/oracle/oradata/PHYDB/users.278.859438619" datafile

6 auxiliary format

"/oracle/app/oracle/oradata/PHYDB/test1.261.859451689" datafile

7 auxiliary format

"/oracle/app/oracle/oradata/PHYDB/firsoul.259.882071697" datafile

8 auxiliary format

"/oracle/app/oracle/oradata/PHYDB/firsoul.263.882093749" datafile

11 auxiliary format

"/oracle/app/oracle/oradata/PHYDB/firsoul01.dbf" ;

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /oracle/app/oracle/oradata/PHYDB/temp.286.859438605 in control file

executing command: SET NEWNAME

Starting backup at 2015-07-02 09:20:16

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=+DATA/racdb/datafile/undotbs1.287.859438601

output file name=/oracle/app/oracle/oradata/PHYDB/undotbs1.287.859438601 tag=TAG20150702T092016

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=+DATA/racdb/datafile/system.293.859438573

output file name=/oracle/app/oracle/oradata/PHYDB/system.293.859438573 tag=TAG20150702T092016

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=+DATA/racdb/datafile/sysaux.288.859438589

output file name=/oracle/app/oracle/oradata/PHYDB/sysaux.288.859438589 tag=TAG20150702T092016

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:36

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=+DATA/racdb/datafile/undotbs2.285.859438615

output file name=/oracle/app/oracle/oradata/PHYDB/undotbs2.285.859438615 tag=TAG20150702T092016

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting datafile copy

input datafile file number=00006 name=+DATA/racdb/datafile/test1.261.859451689

output file name=/oracle/app/oracle/oradata/PHYDB/test1.261.859451689 tag=TAG20150702T092016

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting datafile copy

input datafile file number=00008 name=+DATA/racdb/datafile/firsoul.263.882093749

output file name=/oracle/app/oracle/oradata/PHYDB/firsoul.263.882093749 tag=TAG20150702T092016

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting datafile copy

input datafile file number=00007 name=+DATA/racdb/datafile/firsoul.259.882071697

output file name=/oracle/app/oracle/oradata/PHYDB/firsoul.259.882071697 tag=TAG20150702T092016

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04

channel ORA_DISK_1: starting datafile copy

input datafile file number=00011 name=+DATA/racdb/datafile/firsoul01.dbf

output file name=/oracle/app/oracle/oradata/PHYDB/firsoul01.dbf tag=TAG20150702T092016

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=+DATA/racdb/datafile/users.278.859438619

output file name=/oracle/app/oracle/oradata/PHYDB/users.278.859438619 tag=TAG20150702T092016

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 2015-07-02 09:23:14

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=7 STAMP=883992258 file name=/oracle/app/oracle/oradata/PHYDB/system.293.859438573

datafile 2 switched to datafile copy

input datafile copy RECID=8 STAMP=883992258 file name=/oracle/app/oracle/oradata/PHYDB/sysaux.288.859438589

datafile 3 switched to datafile copy

input datafile copy RECID=9 STAMP=883992258 file name=/oracle/app/oracle/oradata/PHYDB/undotbs1.287.859438601

datafile 4 switched to datafile copy

input datafile copy RECID=10 STAMP=883992258 file name=/oracle/app/oracle/oradata/PHYDB/undotbs2.285.859438615

datafile 5 switched to datafile copy

input datafile copy RECID=11 STAMP=883992258 file name=/oracle/app/oracle/oradata/PHYDB/users.278.859438619

datafile 6 switched to datafile copy

input datafile copy RECID=12 STAMP=883992258 file name=/oracle/app/oracle/oradata/PHYDB/test1.261.859451689

datafile 7 switched to datafile copy

input datafile copy RECID=13 STAMP=883992258 file name=/oracle/app/oracle/oradata/PHYDB/firsoul.259.882071697

datafile 8 switched to datafile copy

input datafile copy RECID=14 STAMP=883992258 file name=/oracle/app/oracle/oradata/PHYDB/firsoul.263.882093749

datafile 11 switched to datafile copy

input datafile copy RECID=15 STAMP=883992258 file name=/oracle/app/oracle/oradata/PHYDB/firsoul01.dbf

Finished Duplicate Db at 2015-07-02 09:24:27

9、在執行復制過程中，容易出現以下錯誤，主要還是針對密碼檔案，為了避免出現錯誤，建議使用scp或者其他命令將主庫密碼檔案複製過去直接使用。

RMAN> duplicate target database for standby from active database;

Starting Duplicate Db at 2015-07-02 09:17:42

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=192 device type=DISK

contents of Memory Script:

{

backup as copy reuse

targetfile '/oracle/app/oracle/product/11.2.0/dbs/orapwracdb1' auxiliary format

'/oracle/app/oracle/product/11.2.0/dbs/orapwphydb' ;

}

executing Memory Script

Starting backup at 2015-07-02 09:17:44

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=55 instance=racdb2 device type=DISK

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 07/02/2015 09:17:45

RMAN-05501: aborting duplication of target database

RMAN-03015: error occurred in stored script Memory Script

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/02/2015 09:17:45

ORA-19505: failed to identify file "/oracle/app/oracle/product/11.2.0/dbs/orapwracdb1"

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

10、只讀方式開啟資料庫，這裡忽略了read only，Oracle透過讀取控制檔案來確定它是否是備用資料庫，這裡它將自動置於只讀模式。

SQL> alter database open;

11、啟用實時查詢，只讀方式開啟後，重啟管理恢復，至此，Oracle ADG搭建完成：

SQL>alter database recover managed standby database using current logfile disconnect;

12、透過建立表空間、表等操作，檢視adg實時性，測試均正常。

13、下面幾條adg管理命令

--把MRP作為前臺程式啟動，MRP程式也是啟動會話的一部分，如果退出會話，MRP程式也就退出了。
alter database recover managed standby database;

--把MRP作為後臺程式啟動
alter database recover managed standby database disconnect;

--讓日誌實時應用
alter database recover managed standby database using current logfile disconnect;

--推遲10分鐘啟動MRP

alter database recover managed standby database delay 120 disconnect;

--立即讓備庫與主庫同步

alter database recover managed standby database nodelay;

--停止MRP

alter database recover managed standby database cancel;

14、主庫和備庫之間角色切換

1、主庫切換為備庫

alter database commit to switchover to physical standby;

alter database commit to switchover to physical standby with session shutdown; shutdown immediate

startup nomount;

alter database mount standby database;

alter database recover managed standby database disconnect from session;

2、從庫切換為主庫

alter database commit to switchover to primary;

shutdown immediate;

startup

15、監視備用資料庫

--檢視dg整理情況

Select protection_mode,protection_level,database_role from v$database;

--檢視是否使用實時應用

select dest_id,recovery_mode from v$archive_dest_status;
--檢查資料同步情況

select name,value,time_computed from v$datagurad_stats;

OK

文盲筱燁

2015年7月3日星期五

【DATAGUARD】DG系列之RACtoONE物理備庫的搭建

相關文章