我的搭建環境：

兩臺機器均為：Red Hat Enterprise Linux Server release 5.4

資料庫版本為：Oracle10g10.2.0

primary機上裝oracle軟體並建立資料庫orcl

standby機上只裝oralce軟體，無需裝資料庫

基本配置：

源資料庫：

IP：10.37.1.1

資料庫SID：orcl_p

db_unique_name：orcl1

standby資料庫：

IP：10.37.1.2

資料庫SID：orcl_s

db_unique_name：orcl2

配置步驟：

1、配置primary資料庫歸檔，並設定本地歸檔路徑

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1218316 bytes

Variable Size 83888372 bytes

Database Buffers 79691776 bytes

Redo Buffers 2973696 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter system set log_archive_dest_1='location=/u01/arch';

System altered.

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/arch

Oldest online log sequence 2

Next log sequence to archive 4

Current log sequence 4

2、將primary資料庫置於force logging模式

SQL> select force_logging from v$database;

FOR

---

NO

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FOR

---

YES

3、建立並修改primary資料庫的初始化引數檔案

SQL> create pfile='/u01/p_pfile.ora' from spfile;

File created.

[oracle@localhost ~]$ vi /u01/p_pfile.ora

修改如下內容：

*.db_unique_name=orcl1

*.log_archive_config='dg_config=(orcl1,orcl2)'

*.log_archive_dest_2='service=orcl_s.2_tns arch valid_for=(online_logfiles,primary_role)db_unique_name=orcl2'

*.log_archive_dest_state_2=defer

*.fal_server=orcl_s.2_tns

*.fal_client=orcl_p.1_tns

*.db_file_name_convert='/u01/app/oracle/oradata/orcl', '/u01/app/oracle/oradata/orcl_s'

*.log_file_name_convert='/u01/app/oracle/oradata/orcl', '/u01/app/oracle/oradata/orcl_s'

*.standby_file_management=auto

關閉資料庫利用修改後的pfile建立spfile

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !export ORACLE_SID=orcl_p

SQL> create spfile from pfile='/u01/p_pfile.ora';

File created.

SQL> startup

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1218316 bytes

Variable Size 62916852 bytes

Database Buffers 100663296 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

4、建立備份：

建立standby的控制檔案：

SQL> alter database create standby controlfile as '/u01/orcl2control01.ctl';

Database altered.

建立所有的資料檔案備份（此處僅以users表空間下的資料檔案為例，其他資料檔案均要備份）

SQL> alter tablespace users begin backup;

Tablespace altered.

SQL> !cp /u01/app/oracle/oradata/orcl/users01.dbf /u01

SQL> alter tablespace users end backup;

Tablespace altered.

5、配置primary監聽和tns服務

[oracle@localhost ~]$ cd $ORACLE_HOME/network/admin

[oracle@localhost admin]$ vi listener.ora

配置內容如下：

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = orcl_p)

(ORACLE_HOME = /u01/app/oracle/10.2.0/db_1)

(GLOBAL_DBNAME = orcl_p)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

配置tns

[oracle@localhost admin]$ vi tnsnames.ora

配置內容如下：

orcl_p.1_tns =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SID = orcl_p)

(SERVER = DEDICATED)

)

orcl_s.2_tns =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SID = orcl_s)

(SERVER = DEDICATED)

)

此時重啟監聽後，測試源資料庫tns配置

[oracle@localhost admin]$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 26-FEB-2014 19:11:44

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.37.1.1)(PORT=1521)))

The command completed successfully

[oracle@localhost admin]$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 26-FEB-2014 19:11:53

Starting /u01/app/oracle/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production

System parameter file is /u01/app/oracle/10.2.0/db_1/network/admin/listener.ora

Log messages written to /u01/app/oracle/10.2.0/db_1/network/log/listener.log

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.37.1.1)(PORT=1521)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production

Start Date 26-FEB-2014 19:11:53

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/10.2.0/db_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/10.2.0/db_1/network/log/listener.log

Listening Endpoints Summary...

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

Services Summary...

Service "orcl_p" has 1 instance(s).

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

The command completed successfully

[oracle@localhost admin]$ tnsping orcl_p.1_tns

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 26-FEB-2014 19:12:38

Used parameter files:

/u01/app/oracle/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.37.1.1)(PORT = 1521))) (CONNECT_DATA = (SID = orcl_p) (SERVER = DEDICATED)))

OK (0 msec)

6、standby資料庫建立目錄結構，並將源資料庫的引數檔案、備份的控制檔案、建立的口令檔案copy到相應的位置

7、

[oracle@localhost ~]$ mkdir -p /u01/arch2

[oracle@localhost u01]$ mkdir -p /u01/app/oracle/flash_recovery_area

[oracle@localhost ~]$ mkdir -p /u01/app/oracle/oradata/orcl_s

[oracle@localhost ~]$ mkdir -p /u01/app/oracle/admin/orcl_s/{a,b,c,u}dump

[oracle@localhost ~]$ scp 10.37.1.1:/u01/*.dbf /u01/app/oracle/oradata/orcl_s/

oracle@10.37.1.1's password:

example01.dbf 100% 100MB 20.0MB/s 00:05

sysaux01.dbf 100% 240MB 16.0MB/s 00:15

system01.dbf 100% 480MB 14.6MB/s 00:33

undotbs01.dbf 100% 30MB 15.0MB/s 00:02

users01.dbf 100% 5128KB 5.0MB/s 00:01

[oracle@localhost ~]$ scp 10.37.1.1:/u01/*.ctl /u01/app/oracle/oradata/orcl_s/

oracle@10.37.1.1's password:

orcl2control01.ctl 100% 6896KB 6.7MB/s 00:00

[oracle@localhost ~]$ scp 10.37.1.1:/u01/p_pfile.ora /u01s_pfile.ora

oracle@10.37.1.1's password:

p_pfile.ora 100% 1508 1.5KB/s 00:00

[oracle@localhost ~]$ scp 10.37.1.1:/u01/app/oracle/10.2.0/db_1/dbs/orapworcl_p /u01/app/oracle/10.2.0/db_1/dbs/orapworcl_s

oracle@10.37.1.1's password:

orapworcl_p 100% 5120 5.0KB/s 00:00

7、修改standby資料庫的sid為orcl_s並配置copy來的引數檔案

[root@localhost ~]# vi /u01/s_pfile.ora

需要修改的內容如下：（沒必要照搬，可根據自己的實際情況自行修改，注意紅色部分是重點修改的地方）

*.audit_file_dest='/u01/app/oracle/admin/orcl_s/adump'

*.background_dump_dest='/u01/app/oracle/admin/orcl_s/bdump'

*.control_files='/u01/app/oracle/oradata/orcl_s/orcl2control01.ctl','/u01/app/oracle/oradata/orcl_s/orcl2control02.ctl','/u01/app/oracle/oradata/orcl_s/orcl2control03.ctl'

*.core_dump_dest='/u01/app/oracle/admin/orcl_s/cdump'

*.user_dump_dest='/u01/app/oracle/admin/orcl_s/udump'

*.db_unique_name=orcl2

*.log_archive_dest_1='location=/u01/arch2'

*.log_archive_dest_2='service=orcl_p.1_tns arch valid_for=(online_logfiles, primary_ro

le) db_unique_name=orcl1'

*.log_archive_dest_state_2=enable

*.fal_server=orcl_p.1_tns

*.fal_client=orcl_s.2_tns

8、配置standby資料庫的監聽和服務名

[oracle@localhost admin]$ cd $ORACLE_HOME/network/admin

[oracle@localhost admin]$ vi listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/10.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = orcl_s)

(ORACLE_HOME = /u01/app/oracle/10.2.0/db_1)

(GLOBAL_DBNAME = orcl_s)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

[oracle@localhost admin]$ vi tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/10.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

orcl_s.2_tns =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SID = orcl_s)

(SERVER = DEDICATED)

)

orcl_p.1_tns =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SID = orcl_p)

(SERVER = DEDICATED)

"tnsnames.ora" 36L, 764C

配置完成後重啟監聽服務

[oracle@localhost admin]$ lsnrctl stop

[oracle@localhost admin]$ lsnrctl start

至此監聽和服務配置完成，在primary和standby端用tnsping命令應該能ping都通兩個服務，能遠端登入兩資料庫視為配置成功

SQL> conn sys/oracle@orcl_p.1_tns as sysdba

Connected.

SQL> conn sys/oracle@orcl_s.2_tns as sysdba

Connected to an idle instance.

9、配置stanby資料庫並啟動到mount狀態，並接受歸檔檔案

任意終端連線到standby資料庫

[oracle@localhost ~]$ sqlplus sys/oracle@orcl_s.2_tns as sysdba

利用s_pfile.ora常見standby的spfile

SQL> create spfile from pfile='/u01/s_pfile.ora';

File created.

SQL> startup mount

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1218316 bytes

Variable Size 62916852 bytes

Database Buffers 100663296 bytes

Redo Buffers 2973696 bytes

Database mounted.

連線到primary資料庫並設定遠端歸檔路徑開啟

SQL> conn sys/oracle@orcl_p.1_tns as sysdba

Connected.

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

檢視歸檔接受情況

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

4

SQL> conn sys/oracle@orcl_s.2_tns as sysdba

Connected.

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

4

檢視standby的歸檔路徑下是否有源資料庫傳來的歸檔日誌

SQL> !ls /u01/arch2

1_4_840520047.dbf

10、primary資料插入，測試standby資料庫能否正常接受

primary端建立表並插入資料

SQL> conn scott/tiger

Connected.

SQL> create table DG_TEST(ID VARCHAR2(10));

Table created.

SQL> insert into DG_TEST

2 values ('DG_TEST')

3 /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> select * from DG_TEST;

ID

----------

DG_TEST

SQL> commit;

Commit complete.

切換歸檔日誌，使當前日誌歸檔

SQL> conn / as sysdba

Connected.

SQL> alter system switch logfile;

System altered.

standby啟動redo應用（這也正是物理standby的工作方法，等於是從獲取的歸檔中執行恢復操作，來保持與源資料庫一致）

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

Database altered.

開啟資料庫前必須停止redo應用

SQL> alter database recover managed standby database cancel;

Database altered.

開啟standby資料，檢視是否有源資料庫新插入的資料

SQL> select * from scott.DG_TEST;

ID

----------

DG_TEST

資料成功傳入standby資料庫，物理的DG搭建成功！

RedHat搭建物理Data Guard

相關文章