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如何搭建”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注行业资讯频道！