如何搭建Oracle DataGuard

這篇文章主要講解了“如何搭建Oracle DataGuard”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“如何搭建Oracle DataGuard”吧！

一、準備工作

1.規劃

2.關閉防火墻

service iptables stop

chkconfig iptables off

3.禁用selinux防火墻

vi /etc/selinux/config

selinux=disabled

二、開啟歸檔模式（主備庫）

1、創建歸檔目錄

[oracle@primary ~]# mkdir -p /u01/app/oracle/archivelog

2、開啟歸檔模式

（1）數據庫到mount狀態開啟歸檔模式

[root@primary ~]# su - oracle

[oracle@primary ~]$ sqlplus / as sysdba

SQL> startup mount;

ORACLE instance started.

Total System Global Area  776646656 bytes

Fixed Size                  2257272 bytes

Variable Size             507514504 bytes

Database Buffers          264241152 bytes

Redo Buffers                2633728 bytes

Database mounted.

（2）設置主庫歸檔目錄

SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/archivelog';

（3）開啟歸檔模式

SQL> alter database archivelog;

Database altered.

（4）查看歸檔設置

SQL> archive log list;

Database log mode        Archive Mode

Automatic archival           Enabled

Archive destination          /u01/app/oracle/archivelog

Oldest online log sequence     2

Next log sequence to archive   4

Current log sequence         4

（5） 開啟數據庫

SQL> alter database open;

Database altered.

三、強制主庫為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

四、HOST文件配置（主備庫）

Root用戶（主備庫相同）

[root@primary ~]# vi /etc/hosts

127.0.0.1   localhost localhost.localdomain

#Primary database IP

192.168.131.100 primary

#Standby database IP

192.168.131.101 standby

五、配置lintener & tnsnames（主備庫）

Oracle用戶

1、主備庫監聽配置

（1）主庫監聽配置 

[oracle@primary ~]$ vi $ORACLE_HOME/network/admin/listener.ora

（添加以下內容）

SID_LIST_LISTENER =

   (SID_LIST =

        (SID_DESC =

          (GLOBAL_DBNAME = orcl1)

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

          (SID_NAME = orcl1)

        )

)

（2）備庫監聽配置

 [oracle@primary ~]$ vi $ORACLE_HOME/network/admin/listener.ora

（添加以下內容）

SID_LIST_LISTENER =

   (SID_LIST =

        (SID_DESC =

          (GLOBAL_DBNAME = orcl1)

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

          (SID_NAME = orcl1)

        )

)

2、修改tnsnames.ora文件（主備庫）

（1）主庫修改

[oracle@primary ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora

db01 =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl1)     ##此處service_name應和上面GLOBAL_NAME值相等

    )

  )

db02 =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl1)

    )

  )

（2）備庫修改

[oracle@primary ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora

db01 =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl1)

    )

  )

db02 =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl1)

(UR=A)

    )

  )

（3）主備庫檢測(主備庫)

[oracle@primary admin]$ tnsping db01

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 11-JUL-2018 08:48:56

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl1)))

OK (10 msec)

[oracle@primary admin]$ tnsping db02

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 11-JUL-2018 08:49:01

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl1) (UR=A)))

OK (10 msec)

六、修改參數文件（主備庫）

1、主庫修改參數

（1）生成參數文件

SQL> create pfile from spfile;

File created.

（2）修改參數文件

[oracle@primary ~]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs

[oracle@primary dbs]$ vi initorcl1.ora

添加以下內容：

db_unique_name=db01

log_archive_config='dg_config=(db01,db02)'

log_archive_dest_1='location=/u01/app/oracle/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=db01'

log_archive_dest_2='service=db02 lgwr async valid_for=(online_logfiles,primary_roles) db_unique_name=db02'

log_archive_dest_state_1=enable

log_archive_dest_state_2=enable

db_file_name_convert='/u01/app/oracle/oradata/orcl2','/u01/app/oracle/oradata/orcl1'

log_file_name_convert='/u01/app/oracle/oradata/orcl2','/u01/app/oracle/oradata/orcl1'

fal_server=db02

fal_client=db01

standby_file_management=auto    

（3）生成spfile（shutdown狀態）

create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl1.ora';

（4）開啟數據庫

startup(使新參數生效)

2、備庫修改參數 

（1）生成參數文件

SQL> create pfile from spfile;

File created.

（2）修改參數文件

[oracle@primary ~]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs

[oracle@primary dbs]$ vi initorcl1.ora

添加以下內容：

db_unique_name=db02

log_archive_config='dg_config=(db01,db02)'

log_archive_dest_1='location=/u01/app/oracle/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=db02'

log_archive_dest_2='service=db01 lgwr async valid_for=(online_logfiles,primary_roles) db_unique_name=db01'

log_archive_dest_state_1=enable

log_archive_dest_state_2=enable

db_file_name_convert='/u01/app/oracle/oradata/orcl1','/u01/app/oracle/oradata/orcl2'

log_file_name_convert='/u01/app/oracle/oradata/orcl1','/u01/app/oracle/oradata/orcl2'

fal_server=db01

fal_client=db02

standby_file_management=auto   // 如果要修改備庫日志文件的大小，需要將此處           auto修改成manual

****************

（3）生成spfile(shutdown狀態)

create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl1.ora';

（4）開啟數據庫

startup(使新參數生效)

七、復制數據庫

1、備庫開啟到nomount模式

SQL> shutdown immediate;

SQL> startup nomount;

2、RMAN進行復制（主庫） 

[oracle@primary ~]$ rman target sys/oracle@db01 auxiliary sys/oracle@db02

 RMAN>duplicate target database for standby nofilenamecheck from active database;

此時，已經完成了DataGuard搭建部分！

八、查詢主備庫角色 

1、主庫查詢

SQL> col db_unique_name for a15

SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;

DB_UNIQUE_NAME  OPEN_MODE      DATABASE_ROLE       SWITCHOVER_STATUS

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

db01                READ WRITE          PRIMARY             SESSIONS ACTIVE

2、備庫查詢

SQL> col db_unique_name for a15

SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;

DB_UNIQUE_NAME   OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS

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

db02            READ ONLY WITH APPLY     PHYSICAL STANDBY   NOT ALLOWED

3.參數設置檢查

SQL> col name for a25

SQL> col value for a30

SQL> select a.name, a.value from v$parameter a where a.name like '%file_name_convert' or a.name like '%fal%' or a.name like 'standby_file%';

NAME                      VALUE

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

db_file_name_convert      /u01/app/oracle/oradata/orcl2,

                            /u01/app/oracle/oradata/orcl1

log_file_name_convert     /u01/app/oracle/oradata/orcl2,

                            /u01/app/oracle/oradata/orcl1

fal_client                   db01

fal_server                  db02

standby_file_management   auto

九、備庫添加standby 日志組（備庫）

1、查看數據庫日志組

查看數據庫的日志組個數與大小，因為我們創建 standby 日志組的個數是原日志

組個數+1 再與 thread 的積((1)*3)，size 不能小于原日志文件的大小。

SQL> select group#,thread#,bytes/1024/1024 M,status from v$log;

   GROUP#    THREAD#          M STATUS

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

         1          1         50 UNUSED

         2          1         50 CLEARING

         3          1         50 CURRENT

SQL> select member from v$logfile;

MEMBER

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

/u01/app/oracle/oradata/orcl2/redo03.log

/u01/app/oracle/oradata/orcl2/redo02.log

/u01/app/oracle/oradata/orcl2/redo01.log 

2、新建備庫日志組

SQL> alter database add standby logfile thread 1 group 4

  2  ('/u01/app/oracle/oradata/orcl2/redo04.log') size 50M;

Database altered.

SQL> alter database add standby logfile thread 1 group 5

  2  ('/u01/app/oracle/oradata/orcl2/redo05.log') size 50M;

Database altered.

SQL> alter database add standby logfile thread 1 group 6

  2  ('/u01/app/oracle/oradata/orcl2/redo06.log') size 50M;

Database altered.

SQL> alter database add standby logfile thread 1 group 7

  2  ('/u01/app/oracle/oradata/orcl2/redo07.log') size 50M;

Database altered.  

3、查看日志組狀態

 SQL> select group#,status,type,member from v$logfile;

    GROUP# STATUS  TYPE    MEMBER

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

         3         ONLINE  /u01/app/oracle/oradata/orcl2/redo03.log

         2         ONLINE  /u01/app/oracle/oradata/orcl2/redo02.log

         1         ONLINE  /u01/app/oracle/oradata/orcl2/redo01.log

         4         STANDBY /u01/app/oracle/oradata/orcl2/redo04.log

         5         STANDBY /u01/app/oracle/oradata/orcl2/redo05.log

         6         STANDBY /u01/app/oracle/oradata/orcl2/redo06.log

         7         STANDBY /u01/app/oracle/oradata/orcl2/redo07.log

十、備庫應用日志

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

十一、驗證數據庫操作

1、在主庫創建用戶

SQL> create user test identified by test;

User created.

SQL> alter system switch logfile;

System altered.

2、在備庫查看 

SQL> select username from dba_users where username='TEST';

USERNAME

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

TEST

感謝各位的閱讀，以上就是“如何搭建Oracle DataGuard”的內容了，經過本文的學習后，相信大家對如何搭建Oracle DataGuard這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！