如何使用RMAN duplicate搭建12C的Data Guard环境？

数据和云

发布于 2021-03-12 13:10:14

1K0

发布于 2021-03-12 13:10:14

文章被收录于专栏：数据和云

墨墨导读：本文来自墨天轮用户投稿，介绍使用RMAN duplicate搭建12C的Data Guard环境的全过程。

1. 实验环境

2. 主端的数据库环境

[oracle@dg1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Dec 29 10:43:05 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show pdbs;
CON_ID CON_NAME                       OPEN MODE  RESTRICTED
     2 PDB$SEED                       READ ONLY  NO
     3 SINGLE                         READ WRITE NO
     5 GYLRS                          MOUNTED
     6 JY                             MOUNTED
     7 TA401                          MOUNTED

3. 添加hosts文件

主备端添加以下内容

[oracle@dg1 ~]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
192.168.xx.175 dg1
192.168.xx.177 dg2
[oracle@dg1 ~]$

4. 主端开启force logging

SQL> select name,log_mode,force_logging from gv$database;
NAME      LOG_MODE     FORCE_LOGGING
--------- ------------ ---------------------------------------
NEWCDB    ARCHIVELOG   NO

SQL> alter database force logging;
Database altered.

SQL> select name,log_mode,force_logging from gv$database;
NAME      LOG_MODE     FORCE_LOGGING
--------- ------------ ---------------------------------------
NEWCDB    ARCHIVELOG   YES

5. 主端提前添加standby redo备端会自动添加

SQL> col member format a50
SQL> select a.GROUP#,BYTES/1024/1024STATUS,TYPE,MEMBER from v$log a,v$logfile b where a.GROUP#=b.GROUP#;
    GROUP#     STATUS TYPE    MEMBER
---------- ---------- ------- -------------------------------------------------
         3        200 ONLINE  /oracle/app/oracle/oradata/singledb/redo03.log
         2        200 ONLINE  /oracle/app/oracle/oradata/singledb/redo02.log
         1        200 ONLINE  /oracle/app/oracle/oradata/singledb/redo01.log

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/oracle/app/oracle/oradata/singledb/redo04.log') size 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/oracle/app/oracle/oradata/singledb/redo05.log') size 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/oracle/app/oracle/oradata/singledb/redo06.log') size 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/oracle/app/oracle/oradata/singledb/redo07.log') size 200M;

6. 主端配置静态监听

配置listener.ora文件，添加singledb的静态监听条目

   SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = singledb)
      (ORACLE_HOME = /oracle/app/oracle/product/12.2.0.1/dbhome_1/)
      (SID_NAME = singledb)
    )
   )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.xx.175)(PORT = 1521))
  )

ADR_BASE_LISTENER = /oracle/app/oracle

配置tnsnames.ora文件，添加备端的监听连接串

# Generated by Oracle configuration tool
LISTENER_SINGLEDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.xx.175)(PORT = 1521))

SINGLEDB_PD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.xx.175)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = SINGLEDB)
    )
  )

SINGLEDB_ST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.xx.177)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = singledb)
    )
  )

7. 备端配置静态监听

配置listener.ora文件，添加singledb的静态监听条目

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /oracle/app/oracle/product/12.2.0.1/dbhome_1/)
      (SID_NAME = singledb)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.xx.177)(PORT = 1521))
  )

ADR_BASE_LISTENER = /oracle/app/oracle

配置tnsnames.ora文件，添加备端的监听连接串

LISTENER_SINGLEDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.xx.175)(PORT = 1521))

SINGLEDB_PD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.xx.175)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = SINGLEDB)
    )
  )

SINGLEDB_ST=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.xx.177)(PORT = 1521))
    (CONNECT_DATA =
      (sid = singledb)
    )
  )

8. Tnsping测试网络连通性

备端tnsping主端

[oracle@oracle ~]$ tnsping singledb_pd

主端tnsping备端

[oracle@12cr2 ~]$ tnsping ingledb_st

9. 主端创建pfile文件并将pfile和密码文件传输到备端

SQL> create pfile='/home/oracle/pfile.ora' from spfile;

scp /home/oracle/pfile.ora 192.168.xx.177:`pwd`
scp orapwnewcdb 192.168.xx.177:`pwd`

10.备端修改主端传输的pfile文件

添加db_unique_name，要不同于主库

[oracle@dg1 ~]$ cat pfile.ora
singledb.__data_transfer_cache_size=0
singledb.__db_cache_size=1191182336
singledb.__inmemory_ext_roarea=0
singledb.__inmemory_ext_rwarea=0
singledb.__java_pool_size=16777216
singledb.__large_pool_size=33554432
singledb.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
singledb.__pga_aggregate_target=83886080
singledb.__sga_target=1593835520
singledb.__shared_io_pool_size=0
singledb.__shared_pool_size=335544320
singledb.__streams_pool_size=0
*.audit_file_dest='/oracle/app/oracle/admin/singledb/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/oracle/app/oracle/oradata/singledb/control01.ctl','/oracle/app/oracle/fast_recovery_area/singledb/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/oracle/app/oracle/product/12.2.0.1/dbhome_1/dbs'
*.db_name='singledb'
*.db_recovery_file_dest='/oracle/app/oracle/fast_recovery_area/singledb'
*.db_recovery_file_dest_size=12780m
*.db_unique_name='singledb_pd'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=singledbXDB)'
*.enable_pluggable_database=true
*.memory_target=1600m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1585446912
*.undo_tablespace='UNDOTBS1'

添加内容：

--DB_FILE_NAME_CONVERT=主库数据文件地址，备库数据文件地址。用于主、备库数据文件的路径不一致的情况下，路径不一致时，进行路径转换。当数据库角色为备库时，将主库的路径转换为备库路径。
*.db_unique_name='singledb_st'
*.LOG_FILE_NAME_CONVERT=('/oracle/app/oracle/oradata/singledb','/oracle/app/oracle/oradata/singledb_st')

如下是搭建ADG的常用参数，含义如下：

DB_UNIQUE_NAME=数据库的唯一名称。

LOG_ARCHIVE_CONFIG='DG_CONFIG=(主库的db_unique_name,备库的db_unique_name)'

LOG_ARCHIVE_DEST_n=日志归档的地址，最少需要两个，LOG_ARCHIVE_DEST_1指向主库，LOG_ARCHIVE_DEST_2指向备库。

FAL_SERVER=指向主库的网络服务名

FAL_CLIENT=指向备库的网络服务名

DB_FILE_NAME_CONVERT=主库数据文件地址，备库数据文件地址。用于主、备库数据文件的路径不一致的情况下，路径不一致时，进行路径转换。当数据库角色为备库时，将主库的路径转换为备库路径。

LOG_FILE_NAME_CONVERT=主库联机日志文件地址，备库联机日志文件地址。用于主、备库联机日志文件的路径不一致的情况下，路径不一致时，进行路径转换。当数据库角色为备库时，将主库的路径转换为备库路径。

STANDBY_FILE_MANAGEMENT=AUTO，当主库添加或减少数据文件时会自动同步到备库而不需要手动干预。

修改主库参数

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(singledb_pd, singledb_st)' scope=both;

alter system set LOG_ARCHIVE_DEST_1='LOCATION= USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=singledb_pd' scope=both;

alter system set LOG_ARCHIVE_DEST_2='SERVICE=singledb_st lgwr async affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=singledb_st' scope=both;

alter system set FAL_SERVER=singledb_standby scope=both;

alter system set FAL_CLIENT=singledb_primary scope=both;

alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both;

修改备库参数

alter system set DB_UNIQUE_NAME='singledb_st' scope=both;

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(singledb_pd,singledb_st)' scope=both;

alter system set LOG_ARCHIVE_DEST_1='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=singledb_st' scope=both;

alter system set LOG_ARCHIVE_DEST_2='SERVICE=singledb_primary lgwr async affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=singledb_st scope=both;

alter system set FAL_SERVER=singledb_primary scope=both;

alter system set FAL_CLIENT=singledb_standby scope=both;

alter system set DB_FILE_NAME_CONVERT='+MKMDB_DATA/mkmdb/datafile/ ','+DATA/mkmdbstd/datafile/' scope=both;

alter system set LOG_FILE_NAME_CONVERT='+MKMDB_ARCH/MKMDB/ONLINELOG/','+DATA/MKMDBSTD/ONLINELOG/', '+MKMDB_DATA/MKMDB/ONLINELOG/', '+DATA/MKMDBSTD/ONLINELOG/' scope=both;

alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both;

192.168.xx.175配置

SQL> show parameter log
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_syslog_level                   string
commit_logging                       string
db_create_online_log_dest_1          string
db_create_online_log_dest_2          string
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string
enable_ddl_logging                   boolean     FALSE
log_archive_config                   string      DG_CONFIG=( singledb_pd, singl
                                                 edb_st)
log_archive_dest                     string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      LOCATION=USE_DB_RECOVERY_FILE_
                                                 DEST VALID_FOR=(ALL_LOGFILES,A
                                                 LL_ROLES) DB_UNIQUE_NAME=singl
                                                 edb_pd
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_17                  string
log_archive_dest_18                  string
log_archive_dest_19                  string
log_archive_dest_2                   string      SERVICE=singledb_pd lgwr async
                                                  affirm VALID_FOR=(ONLINE_LOGF
                                                 ILES,PRIMARY_ROLE) DB_UNIQUE_N
                                                 AME=singledb_st
log_archive_dest_20                  string

192.168.xx.177配置

SQL> show parameter log_
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_syslog_level                   string
commit_logging                       string
db_create_online_log_dest_1          string
db_create_online_log_dest_2          string
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string
enable_ddl_logging                   boolean     FALSE
log_archive_config                   string      DG_CONFIG=(singledb_pd,singled
                                                 b_st)
log_archive_dest                     string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      LOCATION=USE_DB_RECOVERY_FILE_
                                                 DEST VALID_FOR=(ALL_LOGFILES,A
                                                 LL_ROLES) DB_UNIQUE_NAME=singl
                                                 edb_st
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_17                  string
log_archive_dest_18                  string
log_archive_dest_19                  string
log_archive_dest_2                   string      SERVICE=singledb_st lgwr async
                                                  affirm VALID_FOR=(ONLINE_LOGF
                                                 ILES,PRIMARY_ROLE) DB_UNIQUE_N
                                                 AME=singledb_pd
log_archive_dest_20                  string
log_archive_dest_21                  string
log_archive_dest_22                  string
log_archive_dest_23                  string

备端创建参数文件所需目录

mkdir /archive
chown oracle:oinstall /archive/
mkdir -p /oracle/app/oracle/admin/singledb/adump
mkdir -p /oracle/app/oracle/oradata/singledb
mkdir -p /oracle/app/oracle/oradata/singledb_st

启动备库到nomount状态

[oracle@oracle ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 24 23:59:36 2017
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to an idle instance.


SQL> create spfile from pfile='/home/oracle/pfile.ora';
File created.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area  838860800 bytes
Fixed Size                  8798312 bytes
Variable Size             322965400 bytes
Database Buffers          503316480 bytes
Redo Buffers                3780608 bytes
SQL> show parameter db_unique_name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      singledb_st

测试主备库之间的连通性

sqlplus sys/password@std as sysdba
sqlplus sys/password@singledb_pd as sysdba

sqlplus sys/password@std as sysdba
sqlplus sys/password@singledb_pd as sysdba

备库执行RMAN duplicate

[oracle@oracle ~]$ rman target sys/password@singledb_pd auxiliary sys/password@std nocatalog

Recovery Manager: Release 12.2.0.1.0 - Production on Sun Jun 25 00:12:14 2017

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

connected to target database: NEWCDB (DBID=36700136)

using target database control file instead of recovery catalog

connected to auxiliary database: NEWCDB (not mounted)

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

11. 启用物理备用数据库(DG启动)

duplicate 完成之后，备库是mount的。
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED

-- 备库开启时实时日志应用
SQL> alter database recover managed standby database disconnect from session;

12. 查看主备库同步状态(DG启动)

SQL> alter system switch logfile;     //主库
SQL> select max(sequence#) from v$archived_log;  
SQL> select process, client_process, sequence#, status from v$managed_standby;

13. 备库开启flashback(DG启动)（利用Flashback将备库激活为读写模式）

SQL> select flashback_on from v$database;   --查看备库是否处于flashback状态
SQL> alter database recover managed standby database cancel; --停止日志应用
SQL> alter database flashback on; ---开启flashback状态（需要开启ADG模式）
SQL> alter database recover managed standby database disconnect from session; --开启redo应用

如果要开启Active Data Guard，按以下步骤操作即可：

(1)取消管理备用数据库Redo Apply
SQL> alter database recover managed standby database cancel;
(2)然后以只读方式打开数据库
SQL> alter database open;
(3)重新开始Redo Apply
SQL> alter database recover managed standby database disconnect from session;
(4)查看备库打开模式
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

ADG关闭和启动步骤：

参考文章：https://cloud.tencent.com/developer/article/1591253

关闭步骤

停止standby（备库）数据库redo日志的应用：

sql> alter database recover managed  standby database cancel ; 
停止主库数据库：sql> shutdown immediate;
停止备用数据库：sql> shutdown immediate;

启动步骤



注：一定要先启动备库

备库：
[oracle@PD orcl]$ lsnrctl start   主备监听需在启动数据库前启动

[oracle@PD orcl]$ sqlplus / as sysdba

SQL> startup nomount
ORACLE instance started.

Total System Global Area  893562880 bytes
Fixed Size        2218512 bytes
Variable Size      587204080 bytes
Database Buffers    297795584 bytes
Redo Buffers        6344704 bytes
SQL> startup nomount ;
ORA-01081: cannot start already-running ORACLE - shut it down first

SQL> alter database mount standby database; 
---您可以指定MOUNT STANDBY DATABASE来挂载物理备用数据库。关键字STANDBY DATABASE是可选的，因为Oracle数据库会自动确定要装载的数据库是主数据库还是备用数据库。一旦执行该语句，备用实例就可以从主实例接收重做数据。
Database altered.

启动备库数据库

SQL> alter database open read only;
Database altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;
---使用using current logfile参数，可以实时同步主库的更新。（在没有延时的情况下，实时传输redo日志。）
SQL> alter database recover managed standby database disconnect from session; --开启redo应用,

Database altered.

主库：
[oracle@PD orcl]$ lsnrctl start   --启动监听

[oracle@PD orcl]$ sqlplus / as sysdba

SQL> startup       --启动数据库服务


## DG三种模式

最大保护模式（Maximum protection） --性能不佳

```
alter database set standby database to maximize protection;

Primary Database上的每个事务的Redo日志必须在本地和Standby Database上都写入日志文件后才能提交，如果不能写入到Standby Database，Primary Database就会自动关闭（挂起）以防止数据丢失。
```

最大可用性（Maximum Availability）

```
Primary Database每个事务的Redo日志要写到本地和Standby Database中才能提交。

这个和最大保护模式不同的是，如果写入到Standby Database失败，Primary Database不会自动关闭。这时Primary Database会自动转换为Maximum Performance模式，等待问题解决并且Standby Database再次和Primary Database同步之后，Primary Database会自动的转换为Maximum Availability。

这种模式要求Standby Database必须配置Standby Redo log，而Primary Database必须配置为LGWR、SYNC、AFFIRM方式归档。
```

最大性能（Maximum Performance） 

```
这个模式是缺省模式，他更加侧重对Primary Database的可用性不造成任何影响。

Primary Database上的事务的Redo日志只要写到本地日志文件就可以提交，不必等待到Standby Database的传递完成。

Primary Database的Redo流可以异步的发送到Standby Database。

这种模式通过LGWR ASYNC或者ARCH实现，Standby Database也不要求使用Standby Redo Log。
```

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2021-03-10，如有侵权请联系 cloudcommunity@tencent.com 删除

网络安全

数据库

sql

本文分享自数据和云微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

网络安全

数据库

sql

登录后参与评论

0 条评论

热度