mysql2ora datacopy,Oracle 11gR2 使用 RMAN duplicate from active database 复制数据库

最新推荐文章于 2023-01-30 15:51:23 发布
最新推荐文章于 2023-01-30 15:51:23 发布
阅读量127 收藏
点赞数
文章标签： mysql2ora datacopy

我们看一个完整的复制示例：

Target DB：

IP: 192.168.2.42

SID：orcl

Auxiliary：

IP：192.168.2.43

SID:orcl

我这里复制的目录相同。 如果不同的话需要在pfile里面加入db_file_name_convert和log_file_name_convert.

1.在Auxiliary创建pfile参数文件：

[Oracle@qs-dmm-rh2 dbs]$ pwd

/u01/app/oracle/product/11.2.0/dbhome_1/dbs

[oracle@qs-dmm-rh2 dbs]$ cat initorcl.ora

DB_NAME=orcl

只有一个参数：DB_NAME

2.在Auxiliary库上创建口令文件

[oracle@qs-dmm-rh2 admin]$ orapwd file=?/dbs/orapworcl password=oracle

3.在Auxiliary库创建相关的目录结构：

[oracle@qs-dmm-rh2 trace]$ mkdir -p /u01/app/oracle/oradata/orcl

不然在duplicate时会报如下错误：

ORA-19505: failed to identify file "/u01/app/oracle/oradata/orcl/users01.dbf"

ORA-27040: file create error, unable to create file

4.启动Auxiliary到nomout状态：

[oracle@qs-dmm-rh2 admin]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 8 00:28:48 2011

Copyright (c) 1982, 2009, Oracle.All rights reserved.

SQL> conn / as sysdba;

Connected to an idle instance.

SQL> startup nomount pfile=?/dbs/initorcl.ora

ORACLE instance started.

Total System Global Area146472960 bytes

Fixed Size1335080 bytes

Variable Size92274904 bytes

Database Buffers50331648 bytes

Redo Buffers2531328 bytes

SQL>

5.在Target和Auxiliary都配置Oracle Net(Listener.ora and tnsnames.ora)：

[oracle@www.linuxidc.com admin]$cat listener.ora

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost6.localdomain6)(PORT = 1521))

)

)

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

(SID_NAME = orcl)

)

)

[oracle@www.linuxidc.com admin]$ cat tnsnames.ora

ORCL_ST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.43)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

)

ORCL_PD =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.42)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

)

6.开始RMAN duplicate from active database：

注意：如果target和Auxiliary库的目录结构相同，记得加上nofilenamecheck参数，不然会报如下错误：

RMAN-05001: auxiliary file name /u01/app/oracle/oradata/orcl/users01.dbf conflicts with a file used by the target database

如果目录不同，在pfile里加如下2个参数进行转换：

db_file_name_convert

log_file_name_convert.

[oracle@qs-dmm-rh2 dbs]$ rman target sys/oracle@orcl_pd auxiliary sys/oracle@orcl_st

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Mar 8 01:01:42 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.All rights reserved.

connected to target database: ORCL (DBID=1272955137)

connected to auxiliary database: ORCL (not mounted)

RMAN> duplicate target database to orcl from active database nofilenamecheck;

Starting Duplicate Db at 08-MAR-11

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=20 device type=DISK

contents of Memory Script:

{

sql clone "create spfile from memory";

}

executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:

{

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area146472960 bytes

Fixed Size1335080 bytes

Variable Size92274904 bytes

Database Buffers50331648 bytes

Redo Buffers2531328 bytes

contents of Memory Script:

{

sql clone "alter system setdb_name =

''ORCL'' comment=

''Modified by RMAN duplicate'' scope=spfile";

sql clone "alter system setdb_unique_name =

''ORCL'' comment=

''Modified by RMAN duplicate'' scope=spfile";

shutdown clone immediate;

startup clone force nomount

backup as copy current controlfile auxiliary format'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/cntrlorcl.dbf';

alter clone database mount;

}

executing Memory Script

sql statement: alter system setdb_name =''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system setdb_unique_name =''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area146472960 bytes

Fixed Size1335080 bytes

Variable Size92274904 bytes

Database Buffers50331648 bytes

Redo Buffers2531328 bytes

Starting backup at 08-MAR-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=43 device type=DISK

channel ORA_DISK_1: starting datafile copy

copying current control file

output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f tag=TAG20110308T010214 RECID=3 STAMP=745203735

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 08-MAR-11

database mounted

contents of Memory Script:

{

set newname for datafile1 to

"/u01/app/oracle/oradata/orcl/system01.dbf";

set newname for datafile2 to

"/u01/app/oracle/oradata/orcl/sysaux01.dbf";

set newname for datafile3 to

"/u01/app/oracle/oradata/orcl/undotbs01.dbf";

set newname for datafile4 to

"/u01/app/oracle/oradata/orcl/users01.dbf";

backup as copy reuse

datafile1 auxiliary format

"/u01/app/oracle/oradata/orcl/system01.dbf"datafile

2 auxiliary format

"/u01/app/oracle/oradata/orcl/sysaux01.dbf"datafile

3 auxiliary format

"/u01/app/oracle/oradata/orcl/undotbs01.dbf"datafile

4 auxiliary format

"/u01/app/oracle/oradata/orcl/users01.dbf";

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

Starting backup at 08-MAR-11

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

--在这里开始copy数据文件，比较慢。

input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf

output file name=/u01/app/oracle/oradata/orcl/system01.dbf tag=TAG20110308T010221

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf

output file name=/u01/app/oracle/oradata/orcl/sysaux01.dbf tag=TAG20110308T010221

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf

output file name=/u01/app/oracle/oradata/orcl/undotbs01.dbf tag=TAG20110308T010221

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf

output file name=/u01/app/oracle/oradata/orcl/users01.dbf tag=TAG20110308T010221

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 08-MAR-11

sql statement: alter system archive log current

contents of Memory Script:

{

backup as copy reuse

archivelog like"/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_03_08/o1_mf_1_7_6qb3zyoo_.arc" auxiliary format

"/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_7_745174404.dbf";

catalog clone archivelog"/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_7_745174404.dbf";

switch clone datafile all;

}

executing Memory Script

Starting backup at 08-MAR-11

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log copy

input archived log thread=1 sequence=7 RECID=3 STAMP=745203841

output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_7_745174404.dbf RECID=0 STAMP=0

channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:07

Finished backup at 08-MAR-11

cataloged archived log

archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_7_745174404.dbf RECID=3 STAMP=745203848

datafile 1 switched to datafile copy

input datafile copy RECID=3 STAMP=745203849 file name=/u01/app/oracle/oradata/orcl/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=4 STAMP=745203849 file name=/u01/app/oracle/oradata/orcl/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=5 STAMP=745203849 file name=/u01/app/oracle/oradata/orcl/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=6 STAMP=745203849 file name=/u01/app/oracle/oradata/orcl/users01.dbf

contents of Memory Script:

{

set until scn844147;

recover

clone database

delete archivelog

;

}

executing Memory Script

executing command: SET until clause

Starting recover at 08-MAR-11

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=18 device type=DISK

starting media recovery

archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_7_745174404.dbf

archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_7_745174404.dbf thread=1 sequence=7

media recovery complete, elapsed time: 00:00:01

Finished recover at 08-MAR-11

contents of Memory Script:

{

shutdown clone immediate;

startup clone nomount;

sql clone "alter system setdb_name =

''ORCL'' comment=

''Reset to original value by RMAN'' scope=spfile";

sql clone "alter system resetdb_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 Area146472960 bytes

Fixed Size1335080 bytes

Variable Size92274904 bytes

Database Buffers50331648 bytes

Redo Buffers2531328 bytes

sql statement: alter system setdb_name =''ORCL'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system resetdb_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area146472960 bytes

Fixed Size1335080 bytes

Variable Size92274904 bytes

Database Buffers50331648 bytes

Redo Buffers2531328 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL" RESETLOGS ARCHIVELOG

MAXLOGFILES16

MAXLOGMEMBERS3

MAXDATAFILES100

MAXINSTANCES8

MAXLOGHISTORY292

LOGFILE

GROUP1 ( '/u01/app/oracle/oradata/orcl/redo01.log' ) SIZE 50 MREUSE,

GROUP2 ( '/u01/app/oracle/oradata/orcl/redo02.log' ) SIZE 50 MREUSE,

GROUP3 ( '/u01/app/oracle/oradata/orcl/redo03.log' ) SIZE 50 MREUSE

DATAFILE

'/u01/app/oracle/oradata/orcl/system01.dbf'

CHARACTER SET ZHS16GBK

contents of Memory Script:

{

set newname for tempfile1 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/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=745203867

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/orcl/undotbs01.dbf RECID=2 STAMP=745203867

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/orcl/users01.dbf RECID=3 STAMP=745203867

datafile 2 switched to datafile copy

input datafile copy RECID=1 STAMP=745203867 file name=/u01/app/oracle/oradata/orcl/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=745203867 file name=/u01/app/oracle/oradata/orcl/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=3 STAMP=745203867 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 08-MAR-11

RMAN>

复制结束。 查询：

Target库：

SQL> select name,dbid from v$database;

NAMEDBID

--------- ----------

ORCL1272955137

Auxiliary库：

SQL> select name,dbid from v$database;

NAMEDBID

--------- ----------

ORCL12729846020b1331709591d260c1c78e86d0c51c18.png

weixin_39939918
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Oracle11gR2——RMAN复制数据库的实施
wang_san_shi的专栏
01-12 595
1、测试方法 target database通过rman备份到NFS中，备份信息注册到catalog database，复制过程仅连接auxiliary database（不需要target database以及catalog database），通过备份文件路径，实现复制。 名词解释：auxiliary database（辅助数据库）：将备份复制到的数据库；target databa
DG搭建常见问题处理
Root__Liu的博客
08-13 1078
在搭建DG的过程中，会遇见好多问题，现在整理出部分产常见的错误及解决方法。 1、ORA-09925报错 报错1: connected to auxiliary database (not started) released channel: prmy1 released channel: prmy2 released channel: prmy3 released channel: pr...
duplicate database的时候，rman连接 auxiliary database的后状态不正确
weixin_34242331的博客
06-25 130
duplicate database的时候，rman连接 auxiliary database的后状态不正确 auxiliary database 已经被startup nomount，但是rman连接后显示状态是not started $ export ORACLE_SID=dupdb $ sqlplus '/as sysdba' SQL> startup nomount ...
RMAN-04006: error from auxiliary database
高云平的博客
04-10 2503
在搭建DG的过程中，之前都是使用备份集恢复数据库的。此次是使用在线恢复，使用duplicate恢复全库，发现报错如下 [oracle@stephenDG admin]$ rman target sys/oracle@stephen auxiliary sys/oracle@stephenDG Recovery Manager: Release 11.2.0.1.0 - Production on...
Duplicating a Database Without Recovery Catalog or Target Connection
小麦苗DBA宝典
08-03 440
Duplicating a Database Without Recovery Catalog or Target Connection Duplicating a Database Without Recovery Catalog or Target Connection duplicate体系结构 搞点英语出来吧，英语不好的哥们就好好翻译翻译吧，这段英语是必须看...
rman异地恢复oracle数据库
weixin_42136914的博客
01-27 919
异机恢复oracle备份数据库
Oracle 11gR2 使用 RMAN duplicate from active database 复制数据库
02-28
Oracle 11gR2 使用 RMAN duplicate from active database 复制数据库 Oracle 11gR2 中使用 RMAN duplicate from active database 复制数据库是一种高效的数据库复制方法。这种方法可以直接从活动数据库复制，省去...
Oracle 11gr2连Oracle 19c 报ORA-28040 ORA-01017解决方法.pdf
07-16
Oracle 11gr2连Oracle 19c 报ORA-28040 ORA-01017解决方法
Oracle Database 11gR2性能调整与优化
04-09
Oracle Database 11gR2性能调整与优化是数据库管理员和开发人员必须掌握的关键技能，以确保系统的高效运行和最佳响应时间。Oracle 11gR2版本引入了许多新的特性和改进，旨在提升数据库的性能和可管理性。以下是这个...
win64-11gR2-client.zip
05-29
标题“win64-11gR2-client.zip”指的是Oracle数据库的一个客户端版本，适用于64位Windows操作系统。11g R2是Oracle数据库的第十二个主要版本，发布于2009年，它提供了许多增强的功能和性能优化。这个压缩包很可能是...
linux_11gR2_database_2of2.zip
05-06
【标题】"Linux_11gR2_database_2of2.zip" 提供的是Linux操作系统上的Oracle 11g Release 2（11gR2）数据库的安装或配置资源的第二部分。Oracle 11gR2是Oracle公司推出的数据库管理系统的一个重要版本，它在11g的...
数据库迁移（分享十一续集）
weixin_34306446的博客
11-27 103
上集主要讲解了oracle自身导入导出功能，本集讲解rman这个工具。 Oracle 11gR2 使用RMAN Duplicate复制数据库、通过备份恢复方式来迁移数据库。 Active database duplicate 功能强大，不需要先把目标数据库进行rman备份，只要目标数据库处于归档模式下即可直接通过网络对数据库进行copy，且cop...
RMAN duplicate from active database
dhhpkf9734的博客
11-05 173
在Oracle 11G有二种方法实现duplicate： 1、Active database duplication 2、Backup-based duplication Active database duplication 通过网络，直接复制目标库到辅助库，然后创建辅助库。这种方法不需要先用RMAN 备份数据库，然后将备份文件发送到auxiliary端。这种方式解决了备份空间占...
rman本机复制数据库
weixin_42136914的博客
01-30 309
rman本机复制数据库
RMAN duplicate from active database 复制数据库
喝醉酒的小白
01-14 2084
Active database duplication 通过网络，直接copy target 库到auxiliary 库，然后创建复制库。 这种方法就不需要先用RMAN 备份数据库，然后将备份文件发送到auxiliary端。 这个功能的作用是非常大的。 尤其是对T级别的库。 因为对这样的库进行备份，然后将备份集发送到备库，在进行duplicate 的代价是非常大的。 一备份要占用时间， 二要占用备份空间， 三在网络传送的时候，还需要占用带宽和时间。 所以Active database duplic
oracle 11g rman catalog,Oracle 11g RMAN复制数据库的测试
weixin_34829543的博客
04-07 302
RMAN支持两种类型复制：活动数据库复制和基于备份的复制，主要用来建立测试库。分别进行测试我们把要复制的数据库称为源数据库(SOURCE DB=orcl)，复制后的数据库成为目标数据库(TARGET DB=bdup)，不容易搞混。一、基于备份的测试测试环境：同一台主机，OS=RHEL 6.4; DB=Oracle11gR21.在目标数据库上创建密码文件[oracle@myrac1 ~]$ cd /...
Duplicate From Active Database
weixin_33895695的博客
09-18 78
RMAN 'Duplicate From Active Database' Feature in 11G (文档 ID 452868.1)转到底部APPLIES TO:Oracle Database - Enterprise Edition - Version 11.1.0.6 and laterInformation in this document applies to ...
oracle数据副本,使用duplicate命令创建数据库副本
weixin_29434351的博客
04-11 823
使用duplicate命令创建数据库副本环境介绍操作系统：Centos 5.5数据库版本：Oracle Database 11g Enterprise Edition Release 11.2.0.1.0已开启归档模式实验一：在异机中使用duplicate命令创建数据库副本1.在目的服务器上创建辅助实例orapwd file=/u01/app/oracle/dbs/orapwocpdup pass...
RMAN复制数据库
csqm87956的博客
06-05 168
Oracle9i初始化参数中文说明一、手工创建辅助实例1。创建密码文件 orapwd file=F:oracleproduct10.1.0em_1databasePWDaux1.ora password=liang2. 创建参数...
RMAN高级应用之Duplicate复制数据库.docx
最新发布
10-25
"RMAN高级应用之Duplicate复制数据库" Oracle数据库的Recovery Manager (RMAN) 是一个强大的工具，用于数据库备份、恢复和维护。在RMAN中，`DUPLICATE`命令是一个非常实用的功能，它允许用户在保留原始数据库的同时...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值