RMAN duplicate复制库
RMAN duplicate复制库
【官方文档】
Backup and Recovery User's Guide
25 Duplicating a Database
26 Duplicating a Database: Advanced Topics
1 概念
Source Database（或source host） 源数据库
Duplicate Database（或new host） 新复制的数据库
Catalog Database（或catalog host） 目录库
1.1 RMAN Duplicate用途
① 用于测试的Database
就是source host的一个copy，RMAN通过不完全恢复复制出来一个new host，如果sourc和new host都在一个平台上，文件路径命名不能相同，如果是两个平台（不支持异构），文件路径命名可同也可不同。无论如何，new host有自己唯一的DBID
②Standby用途Database
典型的就是Data Guard，Standby Database的主要功能是可以提供Failover，所以Primary database会将log输送到Standy Database，使其不断更新数据
1.2 RMAN duplicate模式
① Active Database Duplication
基于RMAN网络的，从源数据库通过网络（不使用RMAN备份）直接创建辅助库
② Backup-Based Duplication
基于RMAN备份的，利用RMAN备份创建辅助库
1.3 基本框架
2 复制新库步骤
1）将辅助库上安装数据库软件，不建库，建立Oracle Home
2）为辅助库建立口令文件，Active方式下与源数据库口令相同
3）确保到辅助实例的网络连通性（辅助库建立静态监听）
4）为辅助实例创建参数文件
5）以nomount方式启动辅助实例（启动静态监听）
6）生产数据库处于mount或open模式下
7）创建备份或将备份和归档日志文件复制到辅助库实例可以访问的某个位置，或者使用活动（active）数据库复制
8）如果有必要分配辅助通道
9）运行rman duplicate命令
10）使用resetlogs打开辅助数据库
duplicate是个不完全恢复，因为current log没有办法通过RMAN备份
3 注意点
1）duplicate目前还不支持跨平台的复制，如windows和linux就是两个平台（windows32位和64位算是一个平台）
2）非active方式，必须保证RMAN的备份能使new host访问到
3）Active模式要保证 source host和duplicate host两端有同样的sys口令文件
4）如果是复制test database，Oracle依靠不同的DBID区别source host和duplicate host如果是复制standby database， Oracle依靠db_unqiue_name区分primary host和standby host
5）基于backup的duplicate是由auxiliary channel工作，而基于active的duplicate是由target channel工作
6）初始化参数DB_FILE_NAME_CONVERT指定数据文件和临时文件的映射名称，初始化参数LOG_FILE_NAME_CONVERT指定联机重做日志文件的映射名称
7）也可以通过duplicate database中的spfile子句指定上面两个参数
8）duplicate是一个不完全恢复，因为rman不备份online redo logfile
4 【实战案例】RMAN duplicate搭建测试库
RMAN通过网络（active方式）在新节点上复制用于测试的数据库
新节点要求如下：
数据库名同source database ORCL
实例名同source database ORCL
数据库（控制文件，在线日志，数据文件)目录：同source database
审计、参数文件、口令文件命名遵循oracle规范
不同的DBID
DUPLICATE TARGET DATABASE TO dupdb
FROM ACTIVE DATABASE
PASSWOED FILE
SPFILE
NOFILENAMECHECK;
解析：
基于网络的复制数据库，此例是Active方式，source host和target host需要同样的一个PASSWORD FILE，SPFILE是复制过去的（没有任何参数修改）。两个节点有着相同的目录结构及文件命名。NOFILENAMECHECK的意思是不检查文件是否重名，因为source host和duplicate host不是同一个平台
4.1 名词定义
source database --->duplicate database //duplicate端只安装Oracle软件，不创建数据库
db_name---->orcl //source端的数据库名
instance_name---->orcl //source端的实例名
4.2 准备工作（source端/目标库）
在source端创建表插入数据，方便复制库之后查询验证
conn scott/tiger create table test(id number); insert into test values(100); insert into test values(101); insert into test values(102); commit; conn / as sysdba alter system switch logfile; select * from scott.test;
4.3 准备工作（duplicate端/测试库）
4.3.1 环境变量实例为orcl
echo $ORACLE_SID sqlplus / as sysdba
4.3.2 建立pfile哑参文件
cd $ORACLE_HOME/dbs vi dummy.ora
写入：
db_name='aaa'
4.3.3 从source库复制口令文件到duplicate库
4.3.3.1 编辑hosts解析文件
vi /etc/hosts
写入：
192.168.9.20 duplicate 192.168.9.10 source
同步到source库
cat /etc/hosts scp duplicate:/etc/hosts source:/etc
4.3.3.2 复制口令文件
cd /u01/app/oracle/product/19.3.0/dbhome_1/dbs/ scp source:/u01/app/oracle/product/19.3.0/dbhome_1/dbs/orapworcl ./
4.4 创建duplicate数据库需要的目录
4.4.1 审计目录
show parameter audit_file_dest
在duplicate端创建目录
mkdir -p /u01/app/oracle/admin/orcl/adump
4.4.2 快速恢复区目录
show parameter db_recovery_file_dest
在duplicate端创建目录
mkdir /u01/flash_recovery_area ls -ld /u01/flash_recovery_area/
4.4.3 归档目录
archive log list
在duplicate端创建目录
mkdir /u01/arch ls -ld /u01/arch
4.4.4 参数文件、密码文件
cd $ORACLE_HOME/dbs
ll
一个是静态参数文件（哑参）----- dummy.ora
一个source database的远程口令文件副本 --- orapworcl
4.4.5 创建数据库目录
mkdir /u01/app/oracle/oradata/ORCL ll /u01/app/oracle/oradata/
4.5 source端和duplicate端之间的oracle net
网络复制是通过source端和duplicate端之间的oracle net，必须保证两端的监听都能启动，duplicate端用的是静态监听，source端使用静态/动态都可
4.5.1 配置duplicate端的静态监听配置文件
lsnrctl stop cd $ORACLE_HOME/network/admin vi listener.ora
写入：
LISTENER= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=duplicate)(PORT=1521)) ) ) SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=orcl) (ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1) (SID_NAME=orcl) ) )
lsnrctl start
Oracle服务名、实例名一样
4.5.2 两端都需配置tnsnames.ora
source和duplicate两端的tnsnames.ora都必须配置，两端内容相同即可
cd $ORACLE_HOME/network/admin vi tnsnames.ora
写入：
orcl = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = source)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) aux = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = duplicate)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )
4.5.3 测试网络
tnsping aux
sqlplus sys/oracle@aux as sysdba
tnsping orcl
sqlplus sys/oracle@orcl as sysdba
4.6 测试库使用哑参文件到nomount状态
duplicate端使用dummy参数文件启动实例到nomount状态
sqlplus / as sysdba
startup nomount pfile=$ORACLE_HOME/dbs/dummy.ora
4.7 rman复制
测试库开启log session记录会话日志方便以后查看
在duplicate端使用rman同时连接source和duplicate（两端都使用远程连接方式）
rman target sys/oracle@orcl auxiliary sys/oracle@aux
开始RMAN通过网络复制数据库
duplicate target database to orcl from active database spfile nofilenamecheck;
……
……
……
复制的新库和source库的DBID是不同的，但它们有相同的db_name和数据库文件路径
oracle@duplicate ~ $ rman target sys/oracle@orcl auxiliary sys/oracle@aux Recovery Manager: Release 19.0.0.0.0 - Production on Mon Jul 12 02:14:45 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1597307870) connected to auxiliary database: AAA (not mounted) RMAN> duplicate target database to orcl from active database spfile nofilenamecheck; Starting Duplicate Db at 12-JUL-21 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=21 device type=DISK current log archived contents of Memory Script: { restore clone from service 'orcl' spfile to '/u01/app/oracle/product/19.3.0/dbhome_1/dbs/spfileorcl.ora'; sql clone "alter system set spfile= ''/u01/app/oracle/product/19.3.0/dbhome_1/dbs/spfileorcl.ora''"; } executing Memory Script Starting restore at 12-JUL-21 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service orcl channel ORA_AUX_DISK_1: restoring SPFILE output file name=/u01/app/oracle/product/19.3.0/dbhome_1/dbs/spfileorcl.ora channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02 Finished restore at 12-JUL-21 sql statement: alter system set spfile= ''/u01/app/oracle/product/19.3.0/dbhome_1/dbs/spfileorcl.ora'' contents of Memory Script: { sql clone "alter system set db_name = ''ORCL'' comment= ''duplicate'' scope=spfile"; shutdown clone immediate; startup clone nomount; } executing Memory Script sql statement: alter system set db_name = ''ORCL'' comment= ''duplicate'' scope=spfile Oracle instance shut down connected to auxiliary database (not started) Oracle instance started Total System Global Area 1660940992 bytes Fixed Size 8897216 bytes Variable Size 956301312 bytes Database Buffers 687865856 bytes Redo Buffers 7876608 bytes duplicating Online logs to Oracle Managed File (OMF) location contents of Memory Script: { sql clone "alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile"; sql clone "alter system set db_unique_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile"; shutdown clone immediate; startup clone force nomount restore clone from service 'orcl' primary controlfile; alter clone database mount; } executing Memory Script sql statement: alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile sql statement: alter system set db_unique_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile Oracle instance shut down Oracle instance started Total System Global Area 1660940992 bytes Fixed Size 8897216 bytes Variable Size 956301312 bytes Database Buffers 687865856 bytes Redo Buffers 7876608 bytes Starting restore at 12-JUL-21 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=255 device type=DISK channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service orcl channel ORA_AUX_DISK_1: restoring control file channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04 output file name=/u01/app/oracle/oradata/ORCL/control01.ctl output file name=/u01/app/oracle/oradata/ORCL/control02.ctl Finished restore at 12-JUL-21 database mounted RMAN-05538: warning: implicitly using DB_FILE_NAME_CONVERT RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/ORCL/system01.dbf conflicts with a file used by the target database RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/ORCL/sysaux01.dbf conflicts with a file used by the target database RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/ORCL/undotbs01.dbf conflicts with a file used by the target database RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/ORCL/test_tbs01.dbf conflicts with a file used by the target database RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/ORCL/users01.dbf conflicts with a file used by the target database RMAN-05158: WARNING: auxiliary (tempfile) file name /u01/app/oracle/oradata/ORCL/temp01.dbf conflicts with a file used by the target database contents of Memory Script: { set newname for datafile 1 to "/u01/app/oracle/oradata/ORCL/system01.dbf"; set newname for datafile 3 to "/u01/app/oracle/oradata/ORCL/sysaux01.dbf"; set newname for datafile 4 to "/u01/app/oracle/oradata/ORCL/undotbs01.dbf"; set newname for datafile 5 to "/u01/app/oracle/oradata/ORCL/test_tbs01.dbf"; set newname for datafile 7 to "/u01/app/oracle/oradata/ORCL/users01.dbf"; restore from nonsparse from service 'orcl' clone database ; sql 'alter system archive log current'; } executing Memory Script executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting restore at 12-JUL-21 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service orcl channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORCL/system01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:03:25 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service orcl channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORCL/sysaux01.dbf Message from syslogd@dba at Jul 12 02:27:56 ... kernel:NMI watchdog: BUG: soft lockup - CPU#1 stuck for 21s! [vmtoolsd:699] Message from syslogd@dba at Jul 12 02:27:56 ... kernel:NMI watchdog: BUG: soft lockup - CPU#0 stuck for 22s! [in:imjournal:1028] channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:11:39 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service orcl channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORCL/undotbs01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:08 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service orcl channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ORCL/test_tbs01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service orcl channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ORCL/users01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 12-JUL-21 sql statement: alter system archive log current current log archived contents of Memory Script: { restore clone force from service 'orcl' archivelog from scn 3044539; switch clone datafile all; } executing Memory Script Starting restore at 12-JUL-21 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: starting archived log restore to default destination channel ORA_AUX_DISK_1: using network backup set from service orcl channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=36 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_AUX_DISK_1: starting archived log restore to default destination channel ORA_AUX_DISK_1: using network backup set from service orcl channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=37 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 12-JUL-21 datafile 1 switched to datafile copy input datafile copy RECID=2 STAMP=1077676435 file name=/u01/app/oracle/oradata/ORCL/system01.dbf datafile 3 switched to datafile copy input datafile copy RECID=3 STAMP=1077676435 file name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf datafile 4 switched to datafile copy input datafile copy RECID=4 STAMP=1077676435 file name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf datafile 5 switched to datafile copy input datafile copy RECID=5 STAMP=1077676435 file name=/u01/app/oracle/oradata/ORCL/test_tbs01.dbf datafile 7 switched to datafile copy input datafile copy RECID=6 STAMP=1077676435 file name=/u01/app/oracle/oradata/ORCL/users01.dbf contents of Memory Script: { set until scn 3045285; recover clone database delete archivelog ; } executing Memory Script executing command: SET until clause Starting recover at 12-JUL-21 using channel ORA_AUX_DISK_1 starting media recovery archived log for thread 1 with sequence 36 is already on disk as file /u01/arch/arch_ORCL_1_36_1069527136.dbf archived log for thread 1 with sequence 37 is already on disk as file /u01/arch/arch_ORCL_1_37_1069527136.dbf archived log file name=/u01/arch/arch_ORCL_1_36_1069527136.dbf thread=1 sequence=36 archived log file name=/u01/arch/arch_ORCL_1_37_1069527136.dbf thread=1 sequence=37 media recovery complete, elapsed time: 00:00:04 Finished recover at 12-JUL-21 contents of Memory Script: { delete clone force archivelog all; } executing Memory Script released channel: ORA_AUX_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=274 device type=DISK deleted archived log archived log file name=/u01/arch/arch_ORCL_1_36_1069527136.dbf RECID=1 STAMP=1077676432 deleted archived log archived log file name=/u01/arch/arch_ORCL_1_37_1069527136.dbf RECID=2 STAMP=1077676433 Deleted 2 objects Oracle instance started Total System Global Area 1660940992 bytes Fixed Size 8897216 bytes Variable Size 956301312 bytes Database Buffers 687865856 bytes Redo Buffers 7876608 bytes contents of Memory Script: { sql clone "alter system set db_name = ''ORCL'' comment= ''Reset to original value by RMAN'' scope=spfile"; sql clone "alter system reset db_unique_name scope=spfile"; } executing Memory Script sql statement: alter system set db_name = ''ORCL'' comment= ''Reset to original value by RMAN'' scope=spfile sql statement: alter system reset db_unique_name scope=spfile Oracle instance started Total System Global Area 1660940992 bytes Fixed Size 8897216 bytes Variable Size 956301312 bytes Database Buffers 687865856 bytes Redo Buffers 7876608 bytes sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 SIZE 200 M , GROUP 2 SIZE 200 M , GROUP 3 SIZE 200 M DATAFILE '/u01/app/oracle/oradata/ORCL/system01.dbf' CHARACTER SET AL32UTF8 contents of Memory Script: { set newname for tempfile 1 to "/u01/app/oracle/oradata/ORCL/temp01.dbf"; switch clone tempfile all; catalog clone datafilecopy "/u01/app/oracle/oradata/ORCL/sysaux01.dbf", "/u01/app/oracle/oradata/ORCL/undotbs01.dbf", "/u01/app/oracle/oradata/ORCL/test_tbs01.dbf", "/u01/app/oracle/oradata/ORCL/users01.dbf"; switch clone datafile all; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to /u01/app/oracle/oradata/ORCL/temp01.dbf in control file cataloged datafile copy datafile copy file name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf RECID=1 STAMP=1077676480 cataloged datafile copy datafile copy file name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf RECID=2 STAMP=1077676480 cataloged datafile copy datafile copy file name=/u01/app/oracle/oradata/ORCL/test_tbs01.dbf RECID=3 STAMP=1077676480 cataloged datafile copy datafile copy file name=/u01/app/oracle/oradata/ORCL/users01.dbf RECID=4 STAMP=1077676480 datafile 3 switched to datafile copy input datafile copy RECID=1 STAMP=1077676480 file name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf datafile 4 switched to datafile copy input datafile copy RECID=2 STAMP=1077676480 file name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf datafile 5 switched to datafile copy input datafile copy RECID=3 STAMP=1077676480 file name=/u01/app/oracle/oradata/ORCL/test_tbs01.dbf datafile 7 switched to datafile copy input datafile copy RECID=4 STAMP=1077676480 file name=/u01/app/oracle/oradata/ORCL/users01.dbf contents of Memory Script: { Alter clone database open resetlogs; } executing Memory Script database opened Finished Duplicate Db at 12-JUL-21
4.8 查看验证
select instance_name,status from v$instance; select * from scott.test;
数据库复制完成！！
浙公网安备 33010602011771号