RAC，DATAGUARD，STREAM被認為是Oracle的高可用的三架馬車.三架馬車各有所側重:

(1)RAC主要解決了單節點故障，實現負載均衡，也實現了高效能。常用於24x7 的商業應用。

(2)DATAGUARD用冗餘的方式實現高可用的，解決容災。

(3)STREAM更加豐富，主要是應用層面的資料共享。

-------&gt>本篇小編主要模擬在Vmware Server2.0下搭建基於oracle10gR2_32的dataguard(主庫基於ASM儲存，從庫基於普通檔案系統儲存)

======================================================================================================
ORACLE 10G 搭建DATAGUARD步驟
======================================================================================================

1 安裝環境(VMServer2.0)

在主機1上安裝資料庫軟體，並建監聽和例項，在主機2上安裝資料庫軟體，並建監聽，但不建例項。

	主庫	dg備庫
作業系統	Redhat5 32位	Redhat5 32位
主機名	Rac01	Asm.dg
IP	192.168.63.100	192.168.63.132
資料庫軟體版本	oracle 10.2.0.1.0	oracle 10.2.0.1.0
ORACLE_BASE	/opt/oracle10g	/home/app/oracle/
ORACLE_HOME	$ORACLE_BASE/product/10.2.0/db_1	$ORACLE_BASE/product/10.2.0/db_1
ORACLE_SID	primary	standby
閃回區	2G	2G
歸檔	開啟	開啟

2 主資料庫配置

2.1 設定資料庫強制歸檔

sqlplus / as sysdba

SQL> ALTER DATABASE FORCE LOGGING;

SQL> select force_logging from v$database;

FOR

---

YES

2.2 新增STANDBY日誌檔案

SQL>alter database add standby logfile group 4 ('+RAC_DISK/primary/onlinelog/standby41.log','+RAC_DISK/primary/onlinelog/standby42.log') size 50m;

SQL>alter database add standby logfile group 5 ('+RAC_DISK/primary/onlinelog/standby51.log','+RAC_DISK/primary/onlinelog/standby52.log') size 50m;

SQL>alter database add standby logfile group 6 ('+RAC_DISK/primary/onlinelog/standby61.log','+RAC_DISK/primary/onlinelog/standby62.log') size 50m;

SQL>alter database add standby logfile group 7 ('+RAC_DISK/primary/onlinelog/standby71.log','+RAC_DISK/primary/onlinelog/standby72.log') size 50m;

SQL> select * from v$logfile order by 1;

2.3 修改引數檔案

2.3.1 生成pfile

SQL>create pfile from spfile;

SQL>shutdown immediate;

2.3.2 修改pfile

vi $ORACLE_HOME/dbs/initprimary.ora

在最後新增如下內容：

primary.__db_cache_size=268435456

primary.__java_pool_size=4194304

primary.__large_pool_size=4194304

primary.__shared_pool_size=121634816

primary.__streams_pool_size=0

*.audit_file_dest='/opt/oracle10g/admin/primary/adump'

*.audit_trail='db'

*.compatible='10.2.0.1.0'

*.db_block_size=8192

*.db_domain=''

*.db_name='primary'

*.db_recovery_file_dest='+RAC_DISK'

*.db_recovery_file_dest_size=2097152

*.diagnostic_dest='+RAC_DISK'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=primaryXDB)'

*.log_archive_dest_1='location=+RAC_DISK/primary'

*.log_archive_format='%t_%s_%r.dbf'

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

*.db_unique_name=primary

*.fal_server='standby'

*.fal_client='primary'

*.standby_file_management=auto

*.db_file_name_convert='/home/app/oracle/oradata/standby/','+RAC_DISK/primary/datafile/'

*.log_file_name_convert='/home/app/oracle/oradata/standby/','+RAC_DISK/primary/onlinelog/'

*.log_archive_config='dg_config=(primary,standby)'

*.log_archive_dest_2='service=standby LGWR SYNC AFFIRM valid_for=(online_logfiles,primary_role)'

2.3.3 生成spfile

SQL> create spfile from pfile;

File created.

2.4 修改監聽配置檔案

2.5 修改TNS配置檔案

2.6 重啟監聽服務

lsnrctl stop

lsnrctl start

2.7 配置最大可用模式

SQL> startup

ORACLE instance started

SQL> alter database set standby database to maximize availability;

Database altered.

SQL> exit

2.8 備份資料庫

----------&gt>rman備份:

RMAN> RUN {

allocate channel c1 type disk;

allocate channel c2 type disk;

sql 'alter system archive log current';

backup current controlfile for standby format='/home/oracle/ctl_%U';

BACKUP FORMAT '/home/oracle/db_%U_%T' skip inaccessible filesperset 5 DATABASE ;

sql 'alter system archive log current';

BACKUP FORMAT '/home/oracle/arch_%U_%T' skip inaccessible filesperset 5 ARCHIVELOG ALL DELETE INPUT;

release channel c2;

release channel c1;

}

----------&gt>透過scp命令命令將備份檔案傳輸到備庫相同目錄下:

3 備資料庫配置

3.1 建立相應的檔案目錄

包括dump檔案目錄，審計區,日誌區,閃回區，資料檔案目錄，可以透過 show parameter dest命令檢視(這裡都是透過oracle使用者建立)

mkdir -p /home/app/oracle/admin/standby/adump;

mkdir -p /home/app/oracle/admin/standby/udump;

mkdir -p /home/app/oracle/admin/standby/bdump;

mkdir -p /home/app/oracle/diag/rdbms/standby/standby/trace

mkdir -p /home/app/oracle/flash_recovery_area

mkdir -p /home/app/oracle/oradata/standby

3.2 修改監聽配置檔案

修改ip地址

3.3 修改TNS配置檔案

修改後的tnsnames.ora檔案內容:

3.4 重啟監聽服務

lsnrctl stop

lsnrctl start

3.5 主備庫互相tnsping

---------&gt>從庫tnsping主庫

-----------------&gt>主- ---------&gt>庫tnsping從庫

3.6 修改引數檔案

[oracle@dg2 admin]$ sqlplus / as sysdba

SQL> shutdown immediate;

vi $ORACLE_HOME/dbs/initstandby.ora

其中標紅的部分都是需要調整和備庫對應的:

SQL> create spfile from pfile;

3.7 啟動從庫到nomount,然後主庫遠端做RMAN恢復:

SQL> startup nomount pfile=/home/app/oracle/product/10.2.0/db_1/dbs/initstandby.ora;

--&gt>主庫遠端連線從庫做recovery:

[oracle@rac1 ~]$ rman target / auxiliary sys/sys@standby

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jul 17 08:58:36 2014

connected to target database: PRIMARY (DBID=1690390844)

connected to auxiliary database: PRIMARY (not mounted)

RMAN> run

2> {

3> allocate channel c2 device type disk format '/home/oracle/ctl_%U' connect sys/sys@primary;

4> allocate channel c1 device type disk format '/home/oracle/db_%U_%T' connect sys/sys@primary;

5> allocate channel c3 device type disk format '/home/oracle/arch_%U_%T' connect sys/sys@primary;

6> allocate auxiliary channel ac2 device type disk format '/home/oracle/ctl_%U';

7> allocate auxiliary channel ac1 device type disk format '/home/oracle/db_%U_%T';

8> allocate auxiliary channel ac3 device type disk format '/home/oracle/arch_%U_%T';

9> duplicate target database for standby nofilenamecheck dorecover;

10> }

using target database control file instead of recovery catalog

allocated channel: c2

channel c2: sid=156 devtype=DISK

allocated channel: c1

channel c1: sid=128 devtype=DISK

allocated channel: c3

channel c3: sid=130 devtype=DISK

allocated channel: ac2

channel ac2: sid=36 devtype=DISK

allocated channel: ac1

channel ac1: sid=35 devtype=DISK

allocated channel: ac3

channel ac3: sid=34 devtype=DISK

Starting Duplicate Db at 19-JUL-14

contents of Memory Script:

{

set until scn 581814;

restore clone standby controlfile;

sql clone 'alter database mount standby database';

}

executing Memory Script

executing command: SET until clause

Starting restore at 19-JUL-14

channel ac2: starting datafile backupset restore

channel ac2: restoring control file

channel ac2: reading from backup piece /home/oracle/ctl_0epdpi2j_1_1

channel ac2: restored backup piece 1

piece handle=/home/oracle/ctl_0epdpi2j_1_1 tag=TAG20140719T120642

channel ac2: restore complete, elapsed time: 00:00:02

output filename=/home/app/oracle/oradata/standby/control01.ctl

Finished restore at 19-JUL-14

sql statement: alter database mount standby database

contents of Memory Script:

{

set until scn 581814;

set newname for tempfile 1 to

"+RAC_DISK/primary/tempfile/temp.282.852908177";

switch clone tempfile all;

set newname for datafile 1 to

"/home/app/oracle/oradata/standby/system.270.852908093";

set newname for datafile 2 to

"/home/app/oracle/oradata/standby/undotbs1.272.852908095";

set newname for datafile 3 to

"/home/app/oracle/oradata/standby/sysaux.271.852908095";

set newname for datafile 4 to

"/home/app/oracle/oradata/standby/users.273.852908097";

restore

check readonly

clone database

;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed temporary file 1 to +RAC_DISK/primary/tempfile/temp.282.852908177 in control file

executing command: SET NEWNAME

Starting restore at 19-JUL-14

channel ac2: starting datafile backupset restore

channel ac2: specifying datafile(s) to restore from backup set

restoring datafile 00002 to /home/app/oracle/oradata/standby/undotbs1.272.852908095

restoring datafile 00003 to /home/app/oracle/oradata/standby/sysaux.271.852908095

channel ac2: reading from backup piece /home/oracle/db_0gpdpi2l_1_1_20140719

channel ac1: starting datafile backupset restore

channel ac1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /home/app/oracle/oradata/standby/system.270.852908093

restoring datafile 00004 to /home/app/oracle/oradata/standby/users.273.852908097

channel ac1: reading from backup piece /home/oracle/db_0fpdpi2l_1_1_20140719

channel ac2: restored backup piece 1

piece handle=/home/oracle/db_0gpdpi2l_1_1_20140719 tag=TAG20140719T120645

channel ac2: restore complete, elapsed time: 00:00:28

channel ac1: restored backup piece 1

piece handle=/home/oracle/db_0fpdpi2l_1_1_20140719 tag=TAG20140719T120645

channel ac1: restore complete, elapsed time: 00:00:28

Finished restore at 19-JUL-14

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy recid=5 stamp=853338716 filename=/home/app/oracle/oradata/standby/system.270.852908093

datafile 2 switched to datafile copy

input datafile copy recid=6 stamp=853338716 filename=/home/app/oracle/oradata/standby/undotbs1.272.852908095

datafile 3 switched to datafile copy

input datafile copy recid=7 stamp=853338716 filename=/home/app/oracle/oradata/standby/sysaux.271.852908095

datafile 4 switched to datafile copy

input datafile copy recid=8 stamp=853338716 filename=/home/app/oracle/oradata/standby/users.273.852908097

contents of Memory Script:

{

set until scn 581814;

recover

standby

clone database

delete archivelog

;

}

executing Memory Script

executing command: SET until clause

Starting recover at 19-JUL-14

starting media recovery

channel ac2: starting archive log restore to default destination

channel ac2: restoring archive log

archive log thread=1 sequence=18

channel ac2: restoring archive log

archive log thread=1 sequence=19

channel ac2: reading from backup piece /home/oracle/arch_0lpdpi42_1_1_20140719

channel ac2: restored backup piece 1

piece handle=/home/oracle/arch_0lpdpi42_1_1_20140719 tag=TAG20140719T120705

channel ac2: restore complete, elapsed time: 00:00:02

archive log filename=/home/app/oracle/oradata/arch_dest/1_18_852908159.arc thread=1 sequence=18

channel clone_default: deleting archive log(s)

archive log filename=/home/app/oracle/oradata/arch_dest/1_18_852908159.arc recid=1 stamp=853338718

archive log filename=/home/app/oracle/oradata/arch_dest/1_19_852908159.arc thread=1 sequence=19

channel clone_default: deleting archive log(s)

archive log filename=/home/app/oracle/oradata/arch_dest/1_19_852908159.arc recid=2 stamp=853338718

media recovery complete, elapsed time: 00:00:03

Finished recover at 19-JUL-14

Finished Duplicate Db at 19-JUL-14

released channel: c2

released channel: c1

released channel: c3

released channel: ac2

released channel: ac1

released channel: ac3

RMAN>

從日誌可以看到，oracle先根據引數檔案把控制檔案恢復到合適位置，然後再根據db_file_name_cover把資料檔案恢復到合適位置

3.8 恢復備庫並建立standby logfile
---&gt>主庫當前序列號

---&gt>恢復備庫:

3.9 主庫切換日誌，觀察從庫日誌:

SQL> alter system switch logfile;

System altered.

3.10 主庫進行資料修改,備庫以只讀方式查詢測試:

--&gt>主庫做修改

--&gt>從庫以只讀方式讀取主庫資料:

3.11 把主庫切換到備庫

----主庫

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

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

primary OPEN

SQL> alter database commit to switchover to physical standby;

Database altered.

SQL> alter database commit to switchover to physical standby with session shutdown;

alter database commit to switchover to physical standby with session shutdown

*

ERROR at line 1:

ORA-01507: database not mounted

SQL> alter database commit to switchover to physical standby with session shutdown;

alter database commit to switchover to physical standby with session shutdown

*

ERROR at line 1:

ORA-01507: database not mounted

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

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

primary STARTED

SQL> alter database mount;

alter database mount

*

ERROR at line 1:

ORA-01100: database already mounted

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 402653184 bytes

Fixed Size 1219664 bytes

Variable Size 125830064 bytes

Database Buffers 272629760 bytes

Redo Buffers 2973696 bytes

SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination +RAC_DISK/primary/

Oldest online log sequence 30

Next log sequence to archive 0

Current log sequence 31

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, Real Application Clusters, OLAP and Data Mining options

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jul 19 13:05:01 2014

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

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

primary MOUNTED

--備庫

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

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

standby OPEN

SQL> alter database commit to switchover to primary;

Database altered.

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 839282688 bytes

Fixed Size 2217992 bytes

Variable Size 557844472 bytes

Database Buffers 276824064 bytes

Redo Buffers 2396160 bytes

Database mounted.

Database opened.

SQL> show user;

USER is "SYS"

SQL>create table lyl_dept as select * from scott.dept;

SQL> select * from lyl_dept;

DEPTNO DNAME LOC

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

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> delete from lyl_dept where deptno=10;

1 row deleted.

SQL> commit;

Commit complete.

-----順利完成切換，並可在備庫進行正常更新操作.

3.12 為備庫建立tempfile

10G下面不需要給備庫建立臨時檔案。因為當它在備用狀態時候，用不到臨時檔案，當切換的時候，它會自動建立臨時檔案.如果是生產庫，個人感覺還是在備庫建立好臨時檔案.

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

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

standby MOUNTED

SQL> select name from v$tempfile;

NAME

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

+RAC_DISK

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE OPEN READ ONLY;

Database altered.

SQL> ALTER TABLESPACE temp ADD TEMPFILE '/home/app/oracle/oradata/standby/temp.dbf' SIZE 10M REUSE;

Tablespace altered.

SQL> select name from v$tempfile;

NAME

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

+RAC_DISK

/home/app/oracle/oradata/standby/temp.dbf

SQL>

3.13 遇到的問題及解決辦法

其實搭建dg不是很難，搭建過程中主要是由於沒注意細節，導致在一些環節浪費了不少時間，主要有:

(1)主庫遠端連線備庫不能,報錯資訊如下:

[oracle@rac1 ~]$ rman target / auxiliary sys/sys@standby

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jul 16 15:09:50 2014

connected to target database: PRIMARY (DBID=1690390844)

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

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

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

RMAN-00554: initialization of internal recovery manager package failed

RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections

--&gt>主要問題是此時備庫只是處於nomount狀態，註冊監聽需要是靜態的才可以.

(2)tempfile恢復報錯，大概如下示:

sql statement: alter database mount standby database

released channel: c2

released channel: c1

released channel: ac2

released channel: ac1

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

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

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

RMAN-03002: failure of Duplicate Db command at 07/17/2014 06:32:00

RMAN-05501: aborting duplication of target database

RMAN-05517: temporary file +RAC_DISK/primary/tempfile/temp.282.852908177 conflicts with file used by target database

--&gt>解決方法：

在主庫使用RMAN的Duplicate建立dg，如果資料檔案與重做日誌檔案目錄相同，要新增 nofilenamecheck ，否則不需要新增

(3)備庫記憶體不足報錯

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

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

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

RMAN-03002: failure of Duplicate Db command at 07/19/2014 12:22:47

RMAN-03015: error occurred in stored script Memory Script

RMAN-06136: ORACLE error from auxiliary database: ORA-04031: unable to allocate 396 bytes of shared memory ("shared pool","select inst_id,alrid,alstm,a...","Typecheck","seg:kggfaAllocSeg")

--&gt>解決：增大sga。這種問題其實不應該有，細節問題.

(4)恢復中途中錯,大概資訊如下:

--&gt>分析解決：透過檢視後臺資料，發現日誌能歸檔成功，也姝應用日誌，alert中報說與從庫失去連線，導致恢復進行不下去。後來發現在主庫恢復過程中從庫開啟的連線還沒有exit。Shutdown從庫，清理完恢復過去的資料文件和控制檔案，然後重新在主庫做遠端恢復就沒問題了.
4.參考資料:
--&gt>關於主從庫都是普通檔案系統且未用rman做備庫恢復請參見：
http://blog.itpub.net/29119536/viewspace-1166531/
--&gt>其他網友分享的搭建dg好貼子請參見:
http://blog.itpub.net/29119536/viewspace-1137574/
http://blog.itpub.net/29119536/viewspace-1223557/

RMAN配置DATAGUARD完整案例(主庫基於ASM儲存)

相關文章