DG如何搭建

這篇文章主要為大家展示了“DG如何搭建”，內容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領大家一起研究并學習一下“DG如何搭建”這篇文章吧。

一、主庫

1歸檔模式

SQL> archive log list

Database log mode           Archive Mode

Automatic archival           Enabled

Archive destination           USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Next log sequence to archive   2

Current log sequence           2

2強制日志

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FOR

---

YES

3添加standby日志

standby logfile的數量和大小均要與redo logfile相同

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

   THREAD#     GROUP#     MEMBERS BYTES/1024/1024

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

     1        1           1          50

     1        2           1          50

     1        3           1          50

SQL> col MEMBER for a25

SQL> select * from v$logfile;

    GROUP# STATUS TYPE MEMBER             IS_

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

     3       ONLINE  /oradata/orcl/redo03.log  NO

     2       ONLINE  /oradata/orcl/redo02.log  NO

     1       ONLINE  /oradata/orcl/redo01.log  NO

從圖中可以看到我們主庫有三組大小為50M的redo logfile，故我們也需要創建同樣數量和大小的standby logfile：

SQL> alter database add standby logfile group 11 ('/oradata/orcl/stb01.log')size 50m;

Database altered.

SQL> alter database add standby logfile group 12('/oradata/orcl/stb02.log')size 50m;

Database altered.

SQL> alter database add standby logfile group 13('/oradata/orcl/stb03.log')size 50m;

Database altered.

SQL> select group#,THREAD#,SEQUENCE#,ARCHIVED,STATUS from v$standby_log;

    GROUP#    THREAD#  SEQUENCE# ARC STATUS

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

    11        0           0 YES UNASSIGNED

    12        0           0 YES UNASSIGNED

    13        0           0 YES UNASSIGNED

4設置數據庫口令文件的使用模式

查看remote_login_passwordfile的值是否EXCLUSIVE

SQL> show parameter remote_login_passwordfile

NAME                 TYPE     VALUE

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

remote_login_passwordfile         string     EXCLUSIVE

如果不是，執行以下命令進行設置，并且重啟數據庫，使其生效：

SQL>alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;

SQL>shutdown immediate;

SQL>startup;

5參數（文件）設置

SQL> show parameter db_unique_name;

DG的搭建需要修改許多數據庫的參數，并且部分參數主備庫之間有點區別，需要在配置過程細心一點。

NAME                 TYPE     VALUE

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

db_unique_name                 string     orcl

SQL> alter system set log_archive_config='dg_config=(orcl,orcls)' scope=spfile;

System altered.

--其中dg_config填寫的是主備庫的db_unique_name。

修改歸檔文件位置

SQL> show parameter db_recovery_file_dest

NAME                 TYPE     VALUE

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

db_recovery_file_dest             string     /u01/app/oracle/fast_recovery_

                         area

db_recovery_file_dest_size         big integer 4182M

設置本地歸檔位置，參數涉及切換

alter system set log_archive_dest_1='LOCATION=/oradata/arch/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=orcl' scope=spfile;

alter system set log_archive_dest_2='SERVICE=orcls ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcls' scope=spfile;

啟用設置的日志路徑

SQL>alter system set log_archive_dest_state_1=enable scope=spfile;

SQL>alter system set log_archive_dest_state_2=enable scope=spfile;

設置歸檔日志進程的最大數量（視實際情況調整）：

SQL>alter system set log_archive_max_processes=30 scope=both;

設置standby庫從哪個數據庫獲取歸檔日志（只對standby庫有效，在主庫上設置是為了在故障切換后，主庫可以成為備庫使用）：

SQL>alter system set fal_server=orcls scope=both;

設置文件管理模式，此項設置為自動，不然在主庫創建數據文件后，備庫不會自動創建：

SQL>alter system set standby_file_management=auto scope=spfile;

啟用OMF功能：

SQL> alter system set db_create_file_dest='/oradata/orcl' scope=spfile;

--如果主備庫文件的存放路徑不同，還需要設置以下兩個參數（需要重啟數據庫生效）：

SQL> alter system set db_file_name_convert='/data/oradata/orcls/datafile','/data/oradata/orcl/datafile','/data/oradata/orcls/tempfile','/data/oradata/orcl/tempfile' scope=spfile;

SQL> alter system set log_file_name_convert='/data/oradata/orcls/redo','/data/oradata/orcl/redo' scope=spfile;

這步路徑的先后順序在主備庫上的設置是不一樣的，大家要注意！

二、備庫參數設置

完成了以上步驟后，通過以下命令生成一個pfile文件給備庫使用：

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

File created.

打開生成的文件,修改部分參數，具體如下：

--修改后

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

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/oradata/orcls/control01.ctl','/u01/app/oracle/fast_recovery_area/orcls/control02.ctl'

*.db_block_size=8192

*.db_create_file_dest='/oradata/orcls'

*.db_domain=''

*.db_name='orcl'

*.db_unique_name='ocrls'

*.db_recovery_file_dest_size=4385144832

*.db_recovery_file_dest=''

*.diagnostic_dest='/u01/app/oracle'

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

*.log_archive_config='dg_config=(orcl,orcls)'

*.log_archive_dest=''

*.log_archive_dest_1='LOCATION=/oradata/arch/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=orcls'

*.log_archive_dest_2='SERVICE=orcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

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

*.memory_target=780140544

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

c)密碼文件配置

密碼文件是創建DG不可缺少的一部分，主庫的密碼文件一般在$ORACLE_HOME/dbs，命名格式是：orapw+db_unique_name

如果不存在此文件，我們可以通過以下命令生成一個：

#su - oracle

$cd $ORACLE_HOME/dbs

$orapwdfile=orapwocrl password=oracle

我們將密碼文件和剛才修改好的pfile一起拷貝到備庫的$ORACLE_HOME/dbs目錄下，并重命名密碼文件的名字：

備庫上修改密碼文件名和參數文件

5.listener.ora與tnsnames.ora配置

這兩個文件均在$ORACLE_HOME/network/admin目錄下，如果沒有，可以自行創建一下

a)備庫配置

listener.ora內容如下：

LISTENER=

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

      (ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1521))

    )

  )

SID_LIST_LISTENER=

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = orcls)

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

      (SID_NAME = orcls)

    )

   )

tnsnames.ora內容如下：

orcl =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME =orcl)

    )

  )

orcls =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME =orcls)

    )

  )

重啟一下監聽：

$lsnrctl stop

$lsnrctl start

b)主庫配置

listener.ora內容如下：

LISTENER=

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

      (ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1521))

    )

  )

SID_LIST_LISTENER=

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = ocrls)

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

      (SID_NAME = ocrls)

    )

   )

tnsnames.ora內容如下：

orcl =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME =orcl)

    )

  )

orcls =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME =orcls)

    )

  )

執行以下命令重啟監聽，使配置生效：

$lsnrctl stop

$lsnrctl start

做完以上配置后，在主備庫上執行以下命令，確保兩個主機之間網絡相通：

$tnsping orcls

$tnsping orcls

6.目錄創建

參數和網絡配置好后，我們需要為備庫dump文件創建相應的目錄（對照主庫$ORACLE_BASE/admin）：

[oracle@node2 ~]$ echo $ORACLE_BASE

/u01/app/oracle

[oracle@node2 ~]$ mkdir -p $ORACLE_BASE/admin/orcls/adump

[oracle@node2 ~]$ mkdir -p $ORACLE_BASE/admin/orcls/dpdump

為數據庫文件創建目錄（就是之前db_file_name_convert和log_file_name_convert的目錄）--/oradata

ocrls:/data/oradata/orls@standby>mkdir -p/data/oradata/ocrls/redo/

ocrls:/data/oradata/ocrls@standby>mkdir -p/data/oradata/ocrls/datafile/

ocrls:/data/oradata/ocrls@standby>mkdir -p /data/oradata/ocrls/control/

7.RMAN復制創建standby庫

準備工作都完成了，那我們可以開始standby庫的創建了。

注：以下操作在備庫完成

a)文件復制

先，我們使用之前修改的pfile把備庫啟動到nomount狀態，生成spfile：

$echo $ORACLE_SID （確認SID是否我們設置的）

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcls.ora';

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

SQL> create spfile from pfile;

File created.

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL>exit

從spfile啟動

SQL>STARTUP NOMOUNT

SQL> show parameter db_unique_name;

NAME                 TYPE     VALUE

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

db_unique_name                 string     orcls

SQL> show parameter name;

NAME                 TYPE     VALUE

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

cell_offloadgroup_name             string

db_file_name_convert             string

db_name                  string     orcl

db_unique_name                 string     orcls

global_names                 boolean     FALSE

instance_name                 string     orcls

lock_name_space              string

log_file_name_convert             string

processor_group_name             string

service_names                 string     orcls

SQL>

復制數據文件，在備庫上操作

[oracle@node2 dbs]$ rman target sys/oracle@orcl auxiliary sys/oracle@orcls

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jun 15 00:33:22 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1506854844)

connected to auxiliary database: ORCL (not mounted)

RMAN>

確認我們已經連接上主庫和備庫后，執行以下命令：

如果在RMAN恢復時不指定 nofilenamecheck 參數

則在數據文件相同文件名恢復時會出現RMAN-05501錯誤

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

命令執行完后，可以看到主庫在開始復制文件到備庫中

復制完成后，打開數據庫開啟實時同步：

SQL>ALTER DATABASE ARCHIVELOG;

SQL>ALTER DATABASE OPEN;

SQL>ARCHIVE LOG LIST

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

查看數據庫狀態

登陸到主庫

$sqlplus / as sysdba

SQL> select database_role from v$database;

DATABASE_ROLE

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

PRIMARY

登錄到備庫：

$sqlplus / as sysdba

SQL> select database_role from v$database;

DATABASE_ROLE

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

PHYSICAL STANDBY

檢查歸檔日志是否能正常傳輸（日志的序號必須是一樣的）：

主庫

SQL> select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;

 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED     ARC

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

     2 17-JUN-18 18-JUN-18 NO     YES

     3 18-JUN-18 18-JUN-18 NO     YES

     4 18-JUN-18 18-JUN-18 NO     YES

     5 18-JUN-18 18-JUN-18 NO     YES

     6 18-JUN-18 19-JUN-18 NO     YES

     7 19-JUN-18 19-JUN-18 NO     YES

     8 19-JUN-18 19-JUN-18 NO     YES

     9 19-JUN-18 19-JUN-18 NO     YES

    10 19-JUN-18 19-JUN-18 NO     YES

    11 19-JUN-18 19-JUN-18 NO     YES

    11 19-JUN-18 19-JUN-18 YES     YES

 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED     ARC

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

    12 19-JUN-18 19-JUN-18 NO     YES

    12 19-JUN-18 19-JUN-18 NO     YES

13 rows selected.

備庫

SQL> select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;

 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED     ARC

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

    11 19-JUN-18 19-JUN-18 YES     YES

    12 19-JUN-18 19-JUN-18 IN-MEMORY YES

b)切換日志測試

主庫

SQL> alter system switch logfile;

System altered.

SQL> select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;

 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED     ARC

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

     2 17-JUN-18 18-JUN-18 NO     YES

     3 18-JUN-18 18-JUN-18 NO     YES

     4 18-JUN-18 18-JUN-18 NO     YES

     5 18-JUN-18 18-JUN-18 NO     YES

     6 18-JUN-18 19-JUN-18 NO     YES

     7 19-JUN-18 19-JUN-18 NO     YES

     8 19-JUN-18 19-JUN-18 NO     YES

     9 19-JUN-18 19-JUN-18 NO     YES

    10 19-JUN-18 19-JUN-18 NO     YES

    11 19-JUN-18 19-JUN-18 NO     YES

    11 19-JUN-18 19-JUN-18 YES     YES

 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED     ARC

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

    12 19-JUN-18 19-JUN-18 NO     YES

    12 19-JUN-18 19-JUN-18 NO     YES

    13 19-JUN-18 19-JUN-18 NO     YES

    13 19-JUN-18 19-JUN-18 NO     YES

15 rows selected.

備庫

SQL> select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;

 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED     ARC

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

    11 19-JUN-18 19-JUN-18 YES     YES

    12 19-JUN-18 19-JUN-18 IN-MEMORY YES

SQL> select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;

 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED     ARC

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

    11 19-JUN-18 19-JUN-18 YES     YES

    12 19-JUN-18 19-JUN-18 YES     YES

    13 19-JUN-18 19-JUN-18 IN-MEMORY YES

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

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

MAX(SEQUENCE#)

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

        13

以上是“DG如何搭建”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！