不停机进行数据库克隆的实验

最新推荐文章于 2025-05-22 23:49:05 发布

cuidai9315

最新推荐文章于 2025-05-22 23:49:05 发布

阅读量357

点赞数

本文详细介绍了如何使用Oracle RMAN在本地服务器上实现数据库的同机复制，包括创建辅助实例、配置初始化参数文件、完整备份、执行复制命令及验证结果等关键步骤。通过此过程，可以在不中断生产环境的情况下搭建测试环境，实现少量数据的不同步。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

目标：主库不停机，搭建出一个测试环境，允许少量的数据不同步，不用逻辑导入导出，使用rman实现

同机复制数据库-131服务器上将PROD库通过rman-duplicate target database to PRODDUP1的方式进行复制

131服务器是建在VMware上的Linux虚拟机

服务器192.168.182.131

OEL5.8_32

Oracle 11.2.0.1.0 32bit
[oracle@oelr5u8-1 admin]$ uname -a
Linux oelr5u8-1.localdomain 2.6.32-300.10.1.el5uek #1 SMP Wed Feb 22 17:22:40 EST 2012 i686 i686 i386 GNU/Linux

SYS@PROD>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

过程中会经过几种测试：
1. 尝试不作任何配置，直接执行duplicate target database to PRODDUP1;
RMAN> duplicate target database to PRODDUP1;

Starting Duplicate Db at 05-APR-14
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/05/2014 11:10:29
RMAN-06174: not connected to auxiliary database
--DUPLICATE命令作为RMAN中的一个命令，要创建Duplicate数据库，首先就得保证RMAN客户端能够正常连接到目标数据库（PROD）和启动到NOMOUNT状态的辅助实例（即上面报错的“auxiliary database”）

2. 尝试完整的实战-参考三思的教程，感谢！
本地创建Duplicate数据库
复杂度最高，由于本地创建Duplicate数据库，新创建的文件路径不能与目标数据库相同，本地创建需要注意辅助实例初始化参数中DB_NAME与目标数据库不能相同。
核心要求：
保证辅助实例的初始化参数中文件路径的正确有效
执行DUPLICATE命令时，注意与辅助实例初始化参数设置的配合

2.1) 创建辅助实例 auxiliary instance
2.1.1) 创建辅助实例的密钥文件
orapwd file=orapwPRODDUP1 password=oracle
2.1.2) 指定环境变量ORACLE_SID--auxiliary instance
export ORACLE_SID=PRODDUP1
2.1.3) 配置初始化参数文件
由于11g与10g管理目录的区别，在11g库中查询：
audit_file_dest                      string      /u01/app/oracle/admin/PROD/adump
background_dump_dest                 string      /u01/app/oracle/diag/rdbms/prod/PROD/trace
core_dump_dest                       string      /u01/app/oracle/diag/rdbms/prod/PROD/cdump
user_dump_dest                       string      /u01/app/oracle/diag/rdbms/prod/PROD/trace
并且参数文件initPROD.ora中只指定了audit_file_dest，我们这里只手工创建路径adump，其他路径看ORACLE是否能够自动创建
cd $ORACLE_BASE/admin
mkdir -p ./PRODDUP1/adump

下面创建其他数据库文件（控制文件、日志文件、数据文件）的路径：
mkdir -p /u01/app/oracle/oradata/PRODDUP1/disk1
mkdir -p /u01/app/oracle/oradata/PRODDUP1/disk2
mkdir -p /u01/app/oracle/oradata/PRODDUP1/disk3
mkdir -p /u01/app/oracle/oradata/PRODDUP1/disk4
mkdir -p /u01/app/oracle/oradata/PRODDUP1/disk5

下面修改参数文件内容，先进行复制
cd $ORACLE_HOME/dbs
cp initPROD.ora initPRODDUP1.ora
vi initPRODDUP1.ora
PROD.__db_cache_size=230686720
PROD.__java_pool_size=4194304
PROD.__large_pool_size=4194304
PROD.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
PROD.__pga_aggregate_target=314572800
PROD.__sga_target=419430400
PROD.__shared_io_pool_size=0
PROD.__shared_pool_size=163577856
PROD.__streams_pool_size=8388608
*.audit_file_dest='/u01/app/oracle/admin/PROD/adump'
*.audit_trail='db'
*.compatible='11.2.0'
*.control_files='/u01/app/oracle/oradata/PROD/disk1/control01.ctl','/u01/app/oracle/oradata/PROD/disk2/control02.ctl','/u01/app/oracle/oradata/PROD/disk3/control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='PROD'
*.db_recovery_file_dest='/home/oracle/flash'
*.db_recovery_file_dest_size=2G
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
*.local_listener='listener2'
*.log_archive_dest_1='location=/home/oracle/arch'
*.memory_target=700M
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=400M
*.undo_management='auto'
*.undo_tablespace='UNDOTBS1'

改为：
PRODDUP1.__db_cache_size=230686720
PRODDUP1.__java_pool_size=4194304
PRODDUP1.__large_pool_size=4194304
PRODDUP1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
PRODDUP1.__pga_aggregate_target=314572800
PRODDUP1.__sga_target=419430400
PRODDUP1.__shared_io_pool_size=0
PRODDUP1.__shared_pool_size=163577856
PRODDUP1.__streams_pool_size=8388608
*.audit_file_dest='/u01/app/oracle/admin/PRODDUP1/adump'
*.audit_trail='db'
*.compatible='11.2.0'
*.control_files='/u01/app/oracle/oradata/PRODDUP1/disk1/control01.ctl','/u01/app/oracle/oradata/PRODDUP1/disk2/control02.ctl','/u01/app/oracle/oradata/PRODDUP1/disk3/control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='PRODDUP1'
*.db_recovery_file_dest='/home/oracle/flash'
*.db_recovery_file_dest_size=2G
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
*.local_listener='listener2'
*.log_archive_dest_1='location=/home/oracle/arch'
*.memory_target=700M
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=400M
*.undo_management='auto'
*.undo_tablespace='UNDOTBS1'
## add for duplicate
DB_FILE_NAME_CONVERT='PROD','PRODDUP1'
LOG_FILE_NAME_CONVERT='PROD','PRODDUP1'

2.1.4) 启动辅助实例
startup nomount

2.1.5) 配置监听和网络服务名
cd $ORACLE_HOME/network/admin
vi listener.ora
vi tnsnames.ora

2.2) 启动目标数据库到MOUNT或OPEN模式
startup

2.3) 验证备份
rman target /
connected to target database: PROD (DBID=251814023)
list backup of database;
using target database control file instead of recovery catalog
specification does not match any backup in the repository
2.4) 新作备份--曾经尝试用压缩备份compressed backupset，但是后面做duplicate的时候报错无法找到备份的数据文件，因此改用不压缩的备份集方式
rman target /
RMAN> backup as backupset full database plus archivelog;

RMAN> list backupset of database;

List of Backup Sets
===================

BS Key Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    680.90M    DISK        00:00:51     05-APR-14
        BP Key: 1   Status: AVAILABLE Compressed: NO Tag: TAG20140405T120005
        Piece Name: /home/oracle/flash/PROD/backupset/2014_04_05/o1_mf_nnndf_TAG20140405T120005_9myzy6yw_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN    Ckp Time Name
---- -- ---- ---------- --------- ----
1       Full 698762     05-APR-14 /u01/app/oracle/oradata/PROD/disk1/system01.dbf
2       Full 698762     05-APR-14 /u01/app/oracle/oradata/PROD/disk1/sysaux01.dbf
3       Full 698762     05-APR-14 /u01/app/oracle/oradata/PROD/disk1/undotbs01.dbf
4       Full 698762     05-APR-14 /u01/app/oracle/oradata/PROD/disk1/tuning_tbs01.dbf
5       Full 698762     05-APR-14 /u01/app/oracle/oradata/PROD/disk1/DATA01.dbf
6       Full 698762     05-APR-14 /u01/app/oracle/oradata/PROD/disk1/test_tbs01.dbf

BS Key Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Full    681.62M    DISK        00:00:47     05-APR-14
        BP Key: 4   Status: AVAILABLE Compressed: NO Tag: TAG20140405T133552
        Piece Name: /home/oracle/flash/PROD/backupset/2014_04_05/o1_mf_nnndf_TAG20140405T133552_9mz5kr7j_.bkp
List of Datafiles in backup set 4
File LV Type Ckp SCN    Ckp Time Name
---- -- ---- ---------- --------- ----
1       Full 701641     05-APR-14 /u01/app/oracle/oradata/PROD/disk1/system01.dbf
2       Full 701641     05-APR-14 /u01/app/oracle/oradata/PROD/disk1/sysaux01.dbf
3       Full 701641     05-APR-14 /u01/app/oracle/oradata/PROD/disk1/undotbs01.dbf
4       Full 701641     05-APR-14 /u01/app/oracle/oradata/PROD/disk1/tuning_tbs01.dbf
5       Full 701641     05-APR-14 /u01/app/oracle/oradata/PROD/disk1/DATA01.dbf
6       Full 701641     05-APR-14 /u01/app/oracle/oradata/PROD/disk1/test_tbs01.dbf

2.5) 执行复制
2.5.1) 连接目标库和辅助实例
rman target sys/oracle@prod_131 auxiliary sys/oracle@proddup1_131

[oracle@oelr5u8-1 admin]$ rman target sys/oracle@prod_131 auxiliary sys/oracle@proddup1_131

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Apr 5 13:13:13 2014

connected to target database: PROD (DBID=251814023)
connected to auxiliary database: PRODDUP1 (not mounted)

2.5.2) 执行DUPLICATE命令
duplicate target database to PRODDUP1;
Starting Duplicate Db at 05-APR-14
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set db_name =
''PROD'' comment=
''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set db_unique_name =
''PRODDUP1'' comment=
''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set db_name = ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''PRODDUP1'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 732352512 bytes

Fixed Size                     1339036 bytes
Variable Size                494928228 bytes
Database Buffers             230686720 bytes
Redo Buffers                   5398528 bytes

Starting restore at 05-APR-14
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=133 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/flash/PROD/backupset/2014_04_05/o1_mf_ncsnf_TAG20140405T133552_9mz5mkrc_.bkp
channel ORA_AUX_DISK_1: piece handle=/home/oracle/flash/PROD/backupset/2014_04_05/o1_mf_ncsnf_TAG20140405T133552_9mz5mkrc_.bkp tag=TAG20140405T133552
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/PRODDUP1/disk1/control01.ctl
output file name=/u01/app/oracle/oradata/PRODDUP1/disk2/control02.ctl
output file name=/u01/app/oracle/oradata/PRODDUP1/disk3/control03.ctl
Finished restore at 05-APR-14

database mounted

contents of Memory Script:
{
   set until scn 701666;
   set newname for datafile 1 to
"/u01/app/oracle/oradata/PRODDUP1/disk1/system01.dbf";
   set newname for datafile 2 to
"/u01/app/oracle/oradata/PRODDUP1/disk1/sysaux01.dbf";
   set newname for datafile 3 to
"/u01/app/oracle/oradata/PRODDUP1/disk1/undotbs01.dbf";
   set newname for datafile 4 to
"/u01/app/oracle/oradata/PRODDUP1/disk1/tuning_tbs01.dbf";
   set newname for datafile 5 to
"/u01/app/oracle/oradata/PRODDUP1/disk1/DATA01.dbf";
   set newname for datafile 6 to
"/u01/app/oracle/oradata/PRODDUP1/disk1/test_tbs01.dbf";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 05-APR-14
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
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/PRODDUP1/disk1/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/PRODDUP1/disk1/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/PRODDUP1/disk1/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PRODDUP1/disk1/tuning_tbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/PRODDUP1/disk1/DATA01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/PRODDUP1/disk1/test_tbs01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/flash/PROD/backupset/2014_04_05/o1_mf_nnndf_TAG20140405T133552_9mz5kr7j_.bkp
channel ORA_AUX_DISK_1: piece handle=/home/oracle/flash/PROD/backupset/2014_04_05/o1_mf_nnndf_TAG20140405T133552_9mz5kr7j_.bkp tag=TAG20140405T133552
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:37
Finished restore at 05-APR-14

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=844091028 file name=/u01/app/oracle/oradata/PRODDUP1/disk1/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=844091028 file name=/u01/app/oracle/oradata/PRODDUP1/disk1/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=844091028 file name=/u01/app/oracle/oradata/PRODDUP1/disk1/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=844091028 file name=/u01/app/oracle/oradata/PRODDUP1/disk1/tuning_tbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=844091028 file name=/u01/app/oracle/oradata/PRODDUP1/disk1/DATA01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=12 STAMP=844091028 file name=/u01/app/oracle/oradata/PRODDUP1/disk1/test_tbs01.dbf

contents of Memory Script:
{
   set until scn 701666;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 05-APR-14
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 23 is already on disk as file /home/oracle/arch/1_23_839975687.dbf
archived log file name=/home/oracle/arch/1_23_839975687.dbf thread=1 sequence=23
media recovery complete, elapsed time: 00:00:01
Finished recover at 05-APR-14

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set db_name =
''PRODDUP1'' comment=
''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset db_unique_name scope=spfile";
   shutdown clone immediate;
   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 732352512 bytes

Fixed Size                     1339036 bytes
Variable Size                494928228 bytes
Database Buffers             230686720 bytes
Redo Buffers                   5398528 bytes

sql statement: alter system set db_name = ''PRODDUP1'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 732352512 bytes

Fixed Size                     1339036 bytes
Variable Size                494928228 bytes
Database Buffers             230686720 bytes
Redo Buffers                   5398528 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "PRODDUP1" RESETLOGS ARCHIVELOG
MAXLOGFILES     30
MAXLOGMEMBERS      5
MAXDATAFILES      100
MAXINSTANCES     2
MAXLOGHISTORY      292
LOGFILE
GROUP 1 ( '/u01/app/oracle/oradata/PRODDUP1/disk1/redo101.log', '/u01/app/oracle/oradata/PRODDUP1/disk2/redo102.log', '/u01/app/oracle/oradata/PRODDUP1/disk3/redo103.log' ) SIZE 100 M REUSE,
GROUP 2 ( '/u01/app/oracle/oradata/PRODDUP1/disk1/redo201.log', '/u01/app/oracle/oradata/PRODDUP1/disk2/redo202.log', '/u01/app/oracle/oradata/PRODDUP1/disk3/redo203.log' ) SIZE 100 M REUSE,
GROUP 3 ( '/u01/app/oracle/oradata/PRODDUP1/disk1/redo301.log', '/u01/app/oracle/oradata/PRODDUP1/disk2/redo302.log', '/u01/app/oracle/oradata/PRODDUP1/disk3/redo303.log' ) SIZE 100 M REUSE
DATAFILE
'/u01/app/oracle/oradata/PRODDUP1/disk1/system01.dbf'
CHARACTER SET AL32UTF8

contents of Memory Script:
{
   set newname for tempfile 1 to
"/u01/app/oracle/oradata/PRODDUP1/disk1/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy "/u01/app/oracle/oradata/PRODDUP1/disk1/sysaux01.dbf",
"/u01/app/oracle/oradata/PRODDUP1/disk1/undotbs01.dbf",
"/u01/app/oracle/oradata/PRODDUP1/disk1/tuning_tbs01.dbf",
"/u01/app/oracle/oradata/PRODDUP1/disk1/DATA01.dbf",
"/u01/app/oracle/oradata/PRODDUP1/disk1/test_tbs01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/PRODDUP1/disk1/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/PRODDUP1/disk1/sysaux01.dbf RECID=1 STAMP=844091049
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/PRODDUP1/disk1/undotbs01.dbf RECID=2 STAMP=844091049
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/PRODDUP1/disk1/tuning_tbs01.dbf RECID=3 STAMP=844091049
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/PRODDUP1/disk1/DATA01.dbf RECID=4 STAMP=844091049
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/PRODDUP1/disk1/test_tbs01.dbf RECID=5 STAMP=844091049

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=844091049 file name=/u01/app/oracle/oradata/PRODDUP1/disk1/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=844091049 file name=/u01/app/oracle/oradata/PRODDUP1/disk1/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=844091049 file name=/u01/app/oracle/oradata/PRODDUP1/disk1/tuning_tbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=844091049 file name=/u01/app/oracle/oradata/PRODDUP1/disk1/DATA01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=844091049 file name=/u01/app/oracle/oradata/PRODDUP1/disk1/test_tbs01.dbf

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 05-APR-14

2.6) 验证结果
[oracle@oelr5u8-1 arch]$ sqlplus sys/oracle@proddup1_131 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Apr 5 13:47:34 2014

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning and Oracle Label Security options

SYS@proddup1_131>select name from v$tablespace;

NAME
------------------------------
SYSTEM
TEMPTS1
SYSAUX
UNDOTBS1
TUNING_TBS
DATA
TEST_TBS

7 rows selected.

SYS@proddup1_131>select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PRODDUP1/disk1/system01.dbf
/u01/app/oracle/oradata/PRODDUP1/disk1/sysaux01.dbf
/u01/app/oracle/oradata/PRODDUP1/disk1/undotbs01.dbf
/u01/app/oracle/oradata/PRODDUP1/disk1/tuning_tbs01.dbf
/u01/app/oracle/oradata/PRODDUP1/disk1/DATA01.dbf
/u01/app/oracle/oradata/PRODDUP1/disk1/test_tbs01.dbf

6 rows selected.

2.7) 同步duplicate数据库
将Duplicate数据库，即上面的PRODDUP1，启动到NOMOUNT状态下，再通过rman连接目标和辅助实例，重新执行duplicate命令即可。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26521853/viewspace-1137878/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26521853/viewspace-1137878/