rman迁移数据库，如何使用RMAN duplicate搭建12C的Data Guard环境？

2023-09-22 阅读 19 评论 0

摘要：墨墨导读：本文来自墨天轮用户投稿，介绍使用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) 1

墨墨导读：本文来自墨天轮用户投稿，介绍使用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 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.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（复制到浏览器打开或者点击“阅读原文”立即查看）

推荐阅读：267页！2020年度数据库技术年刊

推荐下载：2020数据技术嘉年华PPT下载

2020数据技术嘉年华近50个PPT下载、视频回放已上传墨天轮平台，可在“数据和云”公众号回复关键词“2020DTC”获得！

视频号，新的分享时代，关注我们，看看有什么新发现？