【RMAN】使用RMAN duplicate复制同机数据库
数据库 复制 Duplicate RMAN 使用
2023-09-14 08:57:30 时间
（1）构建辅助数据库目录结构
（2）根据源库的initorcl.ora 创建辅助数据库初始化参数文件inityqldb.ora
（3）利用源库的密码文件创建辅助实例口令文件直接拷贝
（4）配置监听listener.ora 和 tnsnames.ora文件
（5）RMAN 备份源数据库
（6）启动辅助库至nomount 状态
（7）使用RMAN duplicate功能复制并创建目标数据库
（8）创建目标库的spfile
1）构建辅助数据库目录结构
oracle@yangDB1:/home/oracle cd /opt/oracle/
oracle@yangDB1:/opt/oracle mkdir yqldb_arch --创建归档地址文件目录
oracle@yangDB1:/opt/oracle ls
10.2.0 admin extapi flash_recovery_area oradata oraInventory pri_arch std_arch yqldb_arch
oracle@yangDB1:/opt/oracle cd oradata/
oracle@yangDB1:/opt/oracle/oradata mkdir yqldb
oracle@yangDB1:/opt/oracle/oradata ls
orcl yqldb
oracle@yangDB1:/opt/oracle/admin ls
oracle@yangDB1:/opt/oracle/admin mkdir yqldb
oracle@yangDB1:/opt/oracle/admin ls
orcl yqldb
oracle@yangDB1:/opt/oracle/admin cd orcl
oracle@yangDB1:/opt/oracle/admin/orcl ls
adump bdump cdump dpdump pfile udump
oracle@yangDB1:/opt/oracle/admin/orcl cd ../yqldb
oracle@yangDB1:/opt/oracle/admin/yqldb mkdir adump bdump cdump dpdump pfile udump
oracle@yangDB1:/opt/oracle/admin/yqldb ls
adump bdump cdump dpdump pfile udump
oracle@yangDB1:/opt/oracle/admin/yqldb cd $ORACLE_HOME/dbs
2）根据源库的initorcl.ora 创建辅助数据库初始化参数文件inityqldb.ora
oracle@yangDB1:/opt/oracle/10.2.0/orcl/dbs cp init.ora inityqldb.ora
oracle@yangDB1:/opt/oracle/10.2.0/orcl/dbs vim inityqldb.ora
orcl.__db_cache_size=889192448
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__shared_pool_size=285212672
orcl.__streams_pool_size=0
*.audit_file_dest=/opt/oracle/admin/yqldb/adump
*.background_dump_dest=/opt/oracle/admin/yqldb/bdump
*.compatible=10.2.0.1.0
*.control_files=/opt/oracle/oradata/yqldb/control01.ctl,/opt/oracle/oradata/yqldb/control02.ctl,/opt/oracle/ora
data/yqldb/control03.ctl
*.core_dump_dest=/opt/oracle/admin/yqldb/cdump
*.db_block_size=8192
*.db_domain=
*.db_file_multiblock_read_count=16
*.db_name=yqldb
*.db_recovery_file_dest=/opt/oracle/flash_recovery_area
*.db_recovery_file_dest_size=2147483648
*.dispatchers=(PROTOCOL=TCP) (SERVICE=orclXDB)
*.job_queue_processes=10
*.log_archive_dest_1=location=/opt/oracle/yqldb_arch
*.open_cursors=300
*.pga_aggregate_target=402653184
*.processes=150
*.remote_login_passwordfile=EXCLUSIVE
*.sga_target=1210056704
*.undo_management=AUTO
*.undo_tablespace=UNDOTBS1
*.user_dump_dest=/opt/oracle/admin/yqldb/udump
*.db_file_name_convert = (/opt/oracle/oradata/orcl,/opt/oracle/oradata/yqldb)
*.log_file_name_convert = (/opt/oracle/oradata/orcl,/opt/oracle/oradata/yqldb)
3）利用源库的密码文件创建辅助实例口令文件直接拷贝,也可以手工创建密码文件
oracle@yangDB1:/opt/oracle/10.2.0/orcl/dbs cp orapworcl orapwyqldb
###orapwd file=$ORACLE_HOME/dbs/orapwORACLE_SID password=XXXX
4）修改listener.ora 中的内容添加蓝色内容，建议做lsnrctl status 测试！
oracle@yangDB1:/opt/oracle/10.2.0/orcl/network/admin vim listener.ora
# listener.ora Network Configuration File: /opt/oracle/10.2.0/orcl/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/10.2.0/orcl)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_NAME = yqldb)
(ORACLE_HOME = /opt/oracle/10.2.0/orcl)
(SID_NAME = yqldb)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1 )(PORT = 1521))
)
5）备份源数据库。
RUN {
allocate channel c1 type disk;
allocate channel c2 type disk;
BACKUP FORMAT /home/oracle/backup/rman、orcl_%U_%T skip inaccessible filesperset 5 DATABASE TAG orcl_hot_db_bk;
sql alter system archive log current;
BACKUP FORMAT /home/oracle/backup/rman、arch_%U_%T skip inaccessible filesperset 5 ARCHIVELOG ALL DELETE INPUT;
backup current controlfile tag=bak_ctlfile format=/home/oracle/backup/rman/ctl_file_%U_%T;
backup spfile tag=spfile format=/home/oracle/backup/rman/ORCL_spfile_%U_%T;
release channel c2;
release channel c1;
6）启动辅助库至nomount状态
oracle@yangDB1:/opt/oracle/10.2.0/orcl/dbs export ORACLE_SID=yqldb
oracle@yangDB1:/opt/oracle/10.2.0/orcl/dbs sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 17 18:28:17 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL conn /as sysdba
Connected.
SQL startup nomount pfile=/opt/oracle/10.2.0/orcl/dbs/inityqldb.ora
ORACLE instance started.
Total System Global Area 1224736768 bytes
Fixed Size 2020384 bytes
Variable Size 318770144 bytes
Database Buffers 889192448 bytes
Redo Buffers 14753792 bytes
SQL exit --一定要退出，否则后面rman duplicate时会卡住。
7）使用RMAN duplicate功能复制并创建目标数据库
oracle@yangDB1:/home/oracle export ORACLE_SID=orcl
oracle@yangDB1:/home/oracle rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Aug 17 18:29:40 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1286259285)
RMAN connect auxiliary sys/yang@yqldb
connected to auxiliary database: YQLDB (not mounted)
RMAN duplicate target database to yqldb;
Starting Duplicate Db at 17-AUG-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=155 devtype=DISK
contents of Memory Script.:
set until scn 1271492;
set newname for datafile 1 to
"/opt/oracle/oradata/yqldb/system01.dbf";
set newname for datafile 2 to
"/opt/oracle/oradata/yqldb/undotbs01.dbf";
set newname for datafile 3 to
"/opt/oracle/oradata/yqldb/sysaux01.dbf";
set newname for datafile 4 to
"/opt/oracle/oradata/yqldb/users01.dbf";
set newname for datafile 5 to
"/opt/oracle/oradata/yqldb/example01.dbf";
restore
check readonly
clone database;
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 17-AUG-11
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /opt/oracle/oradata/yqldb/undotbs01.dbf
restoring datafile 00004 to /opt/oracle/oradata/yqldb/users01.dbf
restoring datafile 00005 to /opt/oracle/oradata/yqldb/example01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup/rman、orcl_0fmk82fk_1_1_20110817
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /opt/oracle/oradata/yqldb/system01.dbf
restoring datafile 00003 to /opt/oracle/oradata/yqldb/sysaux01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup/rman、orcl_0emk82fk_1_1_20110817
failover to previous backup
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /opt/oracle/oradata/yqldb/system01.dbf
restoring datafile 00002 to /opt/oracle/oradata/yqldb/undotbs01.dbf
restoring datafile 00003 to /opt/oracle/oradata/yqldb/sysaux01.dbf
restoring datafile 00004 to /opt/oracle/oradata/yqldb/users01.dbf
restoring datafile 00005 to /opt/oracle/oradata/yqldb/example01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup/rman/orcl_0cmjnr7e_1_1_%S.bak
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/rman/orcl_0cmjnr7e_1_1_%S.bak tag=TAG20110811T140638
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 17-AUG-11
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "YQLDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( /opt/oracle/oradata/yqldb/redo01.log ) SIZE 50 M REUSE,
GROUP 2 ( /opt/oracle/oradata/yqldb/redo02.log ) SIZE 50 M REUSE,
GROUP 3 ( /opt/oracle/oradata/yqldb/redo03.log ) SIZE 50 M REUSE
DATAFILE
/opt/oracle/oradata/yqldb/system01.dbf
CHARACTER SET ZHS16GBK
contents of Memory Script.:
switch clone datafile all;
executing Memory Script
released channel: ORA_AUX_DISK_1
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=759436245 filename=/opt/oracle/oradata/yqldb/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=759436245 filename=/opt/oracle/oradata/yqldb/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=759436245 filename=/opt/oracle/oradata/yqldb/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=759436245 filename=/opt/oracle/oradata/yqldb/example01.dbf
contents of Memory Script.:
set until scn 1271492;
recover
clone database
delete archivelog;
executing Memory Script
executing command: SET until clause
Starting recover at 17-AUG-11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=155 devtype=DISK
starting media recovery
channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=23
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=24
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=25
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=26
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup/rman、arch_0jmk82gu_1_1_20110817
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/rman、arch_0jmk82gu_1_1_20110817 tag=TAG20110817T174917
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=18
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=19
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=20
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=21
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=22
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup/rman、arch_0imk82gu_1_1_20110817
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/rman、arch_0imk82gu_1_1_20110817 tag=TAG20110817T174917
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
archive log filename=/opt/oracle/yqldb_arch/1_18_758478551.dbf thread=1 sequence=18
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/yqldb_arch/1_18_758478551.dbf recid=5 stamp=759436252
archive log filename=/opt/oracle/yqldb_arch/1_19_758478551.dbf thread=1 sequence=19
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/yqldb_arch/1_19_758478551.dbf recid=6 stamp=759436257
archive log filename=/opt/oracle/yqldb_arch/1_20_758478551.dbf thread=1 sequence=20
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/yqldb_arch/1_20_758478551.dbf recid=7 stamp=759436257
archive log filename=/opt/oracle/yqldb_arch/1_21_758478551.dbf thread=1 sequence=21
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/yqldb_arch/1_21_758478551.dbf recid=8 stamp=759436257
archive log filename=/opt/oracle/yqldb_arch/1_22_758478551.dbf thread=1 sequence=22
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/yqldb_arch/1_22_758478551.dbf recid=9 stamp=759436257
archive log filename=/opt/oracle/yqldb_arch/1_23_758478551.dbf thread=1 sequence=23
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/yqldb_arch/1_23_758478551.dbf recid=2 stamp=759436249
archive log filename=/opt/oracle/yqldb_arch/1_24_758478551.dbf thread=1 sequence=24
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/yqldb_arch/1_24_758478551.dbf recid=3 stamp=759436249
archive log filename=/opt/oracle/yqldb_arch/1_25_758478551.dbf thread=1 sequence=25
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/yqldb_arch/1_25_758478551.dbf recid=4 stamp=759436249
archive log filename=/opt/oracle/yqldb_arch/1_26_758478551.dbf thread=1 sequence=26
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/yqldb_arch/1_26_758478551.dbf recid=1 stamp=759436249
channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=32
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup/rman、arch_0lmk82he_1_1_20110817
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/rman、arch_0lmk82he_1_1_20110817 tag=TAG20110817T174917
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=27
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=28
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=29
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=30
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=31
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup/rman、arch_0kmk82he_1_1_20110817
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/rman、arch_0kmk82he_1_1_20110817 tag=TAG20110817T174917
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
archive log filename=/opt/oracle/yqldb_arch/1_27_758478551.dbf thread=1 sequence=27
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/yqldb_arch/1_27_758478551.dbf recid=15 stamp=759436273
archive log filename=/opt/oracle/yqldb_arch/1_28_758478551.dbf thread=1 sequence=28
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/yqldb_arch/1_28_758478551.dbf recid=13 stamp=759436273
archive log filename=/opt/oracle/yqldb_arch/1_29_758478551.dbf thread=1 sequence=29
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/yqldb_arch/1_29_758478551.dbf recid=14 stamp=759436273
archive log filename=/opt/oracle/yqldb_arch/1_30_758478551.dbf thread=1 sequence=30
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/yqldb_arch/1_30_758478551.dbf recid=12 stamp=759436272
archive log filename=/opt/oracle/yqldb_arch/1_31_758478551.dbf thread=1 sequence=31
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/yqldb_arch/1_31_758478551.dbf recid=11 stamp=759436269
archive log filename=/opt/oracle/yqldb_arch/1_32_758478551.dbf thread=1 sequence=32
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/yqldb_arch/1_32_758478551.dbf recid=10 stamp=759436268
media recovery complete, elapsed time: 00:00:11
Finished recover at 17-AUG-11
contents of Memory Script.:
shutdown clone;
startup clone nomount ;
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1224736768 bytes
Fixed Size 2020384 bytes
Variable Size 318770144 bytes
Database Buffers 889192448 bytes
Redo Buffers 14753792 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "YQLDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( /opt/oracle/oradata/yqldb/redo01.log ) SIZE 50 M REUSE,
GROUP 2 ( /opt/oracle/oradata/yqldb/redo02.log ) SIZE 50 M REUSE,
GROUP 3 ( /opt/oracle/oradata/yqldb/redo03.log ) SIZE 50 M REUSE
DATAFILE
/opt/oracle/oradata/yqldb/system01.dbf
CHARACTER SET ZHS16GBK
contents of Memory Script.:
set newname for tempfile 1 to
"/opt/oracle/oradata/yqldb/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/opt/oracle/oradata/yqldb/undotbs01.dbf";
catalog clone datafilecopy "/opt/oracle/oradata/yqldb/sysaux01.dbf";
catalog clone datafilecopy "/opt/oracle/oradata/yqldb/users01.dbf";
catalog clone datafilecopy "/opt/oracle/oradata/yqldb/example01.dbf";
switch clone datafile all;
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 1 to /opt/oracle/oradata/yqldb/temp01.dbf in control file
cataloged datafile copy
datafile copy filename=/opt/oracle/oradata/yqldb/undotbs01.dbf recid=1 stamp=759436323
cataloged datafile copy
datafile copy filename=/opt/oracle/oradata/yqldb/sysaux01.dbf recid=2 stamp=759436323
cataloged datafile copy
datafile copy filename=/opt/oracle/oradata/yqldb/users01.dbf recid=3 stamp=759436323
cataloged datafile copy
datafile copy filename=/opt/oracle/oradata/yqldb/example01.dbf recid=4 stamp=759436323
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=759436323 filename=/opt/oracle/oradata/yqldb/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=759436323 filename=/opt/oracle/oradata/yqldb/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=759436323 filename=/opt/oracle/oradata/yqldb/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=759436323 filename=/opt/oracle/oradata/yqldb/example01.dbf
contents of Memory Script.:
Alter clone database open resetlogs;
executing Memory Script
database opened
Finished Duplicate Db at 17-AUG-11
8）--创建新的spfile文件,去掉如下两个参数
#*.db_file_name_convert = (/opt/oracle/oradata/orcl,/opt/oracle/oradata/yqldb)
#*.log_file_name_convert = (/opt/oracle/oradata/orcl,/opt/oracle/oradata/yqldb)
数据库已经开启，进行数据验证。
oracle@yangDB1:/opt/oracle/10.2.0/orcl/dbs export ORACLE_SID=yqldb
oracle@yangDB1:/opt/oracle/10.2.0/orcl/dbs sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 17 18:33:23 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL conn sys/yang@yqldb as sysdba
Connected.
SQL create spfile from pfile=/opt/oracle/10.2.0/orcl/dbs/inityqldb.ora;
File created.
SQL col tname for a15
SQL col tabletype for a10
SQL select * from tab;
TNAME TABTYPE CLUSTERID
--------------- --------------------- ----------
YANGB TABLE
YANGTAB TABLE
YANGOBJ TABLE
YANGUSER TABLE
OBJECTS TABLE
A TABLE
6 rows selected.
SQL select instance_name from v$instance;
INSTANCE_NAME
--------------
yqldb
SQL
相关文章
- 复制数据库数据
- 学习mysql数据库主从同步复制原理
- 修改mysql/MariaDB数据库的端口号+远程
- Android通讯录数据库操作
- 数据库内核月报 － 2015 / 10-PgSQL · 特性分析 · PG主备流复制机制
- SQLServer · 最佳实践 · 数据库实现大容量插入的几种方式
- mysql 数据库表迁移复制
- activemq持久化配置，设置为主从模式（带复制的主从模式，应用mysql数据库）
- 数据库设计学习④：需求分析举例
- MySQL数据库普通索引
- 使用SQLServer复制数据库
- 104.第十九章 MySQL数据库 -- MySQL主从复制、 级联复制和双主复制（十四）
- PostgreSQL的学习心得和知识总结（六十二）|语法级自上而下完美实现MySQL数据库的 建表时COMMENT列和表 的实现方案(原生版)
- postgresql数据库|数据库实操----表复制详解
- 数据库复制：Spectral Core Full Convert Ultimate 2022
- 图解MongoDB数据库学习路线指南