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

墨墨导读：本文来自墨天轮用户投稿，介绍使用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/hosts127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4192.168.xx.175 dg1192.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 a50SQL> 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 toolLISTENER_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.orasingledb.__data_transfer_cache_size=0singledb.__db_cache_size=1191182336singledb.__inmemory_ext_roarea=0singledb.__inmemory_ext_rwarea=0singledb.__java_pool_size=16777216singledb.__large_pool_size=33554432singledb.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environmentsingledb.__pga_aggregate_target=83886080singledb.__sga_target=1593835520singledb.__shared_io_pool_size=0singledb.__shared_pool_size=335544320singledb.__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 logNAME TYPE VALUE------------------------------------ ----------- ------------------------------audit_syslog_level stringcommit_logging stringdb_create_online_log_dest_1 stringdb_create_online_log_dest_2 stringdb_create_online_log_dest_3 stringdb_create_online_log_dest_4 stringdb_create_online_log_dest_5 stringenable_ddl_logging boolean FALSElog_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_pdlog_archive_dest_10 stringlog_archive_dest_11 stringlog_archive_dest_12 stringlog_archive_dest_13 stringlog_archive_dest_14 stringlog_archive_dest_15 stringlog_archive_dest_16 string
NAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_dest_17 stringlog_archive_dest_18 stringlog_archive_dest_19 stringlog_archive_dest_2 string SERVICE=singledb_pd lgwr async affirm VALID_FOR=(ONLINE_LOGF ILES,PRIMARY_ROLE) DB_UNIQUE_N AME=singledb_stlog_archive_dest_20 string

192.168.xx.177配置

SQL> show parameter log_NAME TYPE VALUE------------------------------------ ----------- ------------------------------audit_syslog_level stringcommit_logging stringdb_create_online_log_dest_1 stringdb_create_online_log_dest_2 stringdb_create_online_log_dest_3 stringdb_create_online_log_dest_4 stringdb_create_online_log_dest_5 stringenable_ddl_logging boolean FALSElog_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_stlog_archive_dest_10 stringlog_archive_dest_11 stringlog_archive_dest_12 stringlog_archive_dest_13 stringlog_archive_dest_14 stringlog_archive_dest_15 stringlog_archive_dest_16 string
NAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_dest_17 stringlog_archive_dest_18 stringlog_archive_dest_19 stringlog_archive_dest_2 string SERVICE=singledb_st lgwr async affirm VALID_FOR=(ONLINE_LOGF ILES,PRIMARY_ROLE) DB_UNIQUE_N AME=singledb_pdlog_archive_dest_20 stringlog_archive_dest_21 stringlog_archive_dest_22 stringlog_archive_dest_23 string

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

mkdir /archivechown oracle:oinstall /archive/mkdir -p /oracle/app/oracle/admin/singledb/adumpmkdir -p /oracle/app/oracle/oradata/singledbmkdir -p /oracle/app/oracle/oradata/singledb_st

启动备库到nomount状态

[oracle@oracle ~]$ sqlplus / as sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 24 23:59:36 2017Copyright (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 bytesFixed Size 8798312 bytesVariable Size 322965400 bytesDatabase Buffers 503316480 bytesRedo Buffers 3780608 bytesSQL> show parameter db_unique_nameNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_unique_name string singledb_st

测试主备库之间的连通性

sqlplus sys/password@std as sysdbasqlplus sys/password@singledb_pd as sysdba
sqlplus sys/password@std as sysdbasqlplus 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 ApplySQL> alter database recover managed standby database cancel;(2)然后以只读方式打开数据库SQL> alter database open;(3)重新开始Redo ApplySQL> 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 nomountORACLE instance started.
Total System Global Area 893562880 bytesFixed Size 2218512 bytesVariable Size 587204080 bytesDatabase Buffers 297795584 bytesRedo Buffers 6344704 bytesSQL> 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。```

墨天轮原文链接：https://www.modb.pro/db/46457（复制到浏览器打开或者点击“阅读原文”立即查看）