RedHat搭建物理Data Guard

RedHat搭建物理Data Guard

我的搭建環境：

兩台機器均為：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

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

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

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

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

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

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/

[email protected]'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/

[email protected]'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

[email protected]'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

[email protected]'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

DG_TEST

DG_TEST

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

DG_TEST

DG_TEST

DG_TEST

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

--------------------------------------分割線 --------------------------------------

Oracle Data Guard 重要配置參數

基於同一主機配置 Oracle 11g Data Guard

探索Oracle之11g DataGuard

Oracle Data Guard （RAC+DG) 歸檔刪除策略及指令碼

Oracle Data Guard 的角色轉換

Oracle Data Guard的日誌FAL gap問題

Oracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby 處理方法

--------------------------------------分割線 --------------------------------------