Oracle 11g RMAN复制数据库的测试

myinfinity

2016-01-19

RMAN支持两种类型复制：活动数据库复制和基于备份的复制，主要用来建立测试库。分别进行测试
我们把要复制的数据库称为源数据库(SOURCE DB=orcl)，复制后的数据库成为目标数据库(TARGET DB=bdup)，不容易搞混。
一、基于备份的测试
测试环境：同一台主机，OS=RHEL 6.4; DB=Oracle11gR2
1.在目标数据库上创建密码文件
[oracle@myrac1 ~]$ cd /s01/app/oracle/product/11.2.0/dbhome_1/dbs/
[oracle@myrac1 dbs]$ pwd
/s01/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@myrac1 dbs]$ orapwd file='/s01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwhjjdb' password=oracle entries=10
2.在目标端创建相应的目录
[oracle@myrac1 admin]$ pwd
/s01/app/oracle/admin
[oracle@myrac1 admin]$ mkdir hjjdb
[oracle@myrac1 admin]$ cd hjjdb/
[oracle@myrac1 hjjdb]$ mkdir adump dpdump pfile scripts
3.在目标端创建初始化参数文件
[oracle@myrac1 dbs]$ vi inithjjdb.ora
hjjdb.__db_cache_size=146800640
hjjdb.__java_pool_size=4194304
hjjdb.__large_pool_size=4194304
hjjdb.__oracle_base='/s01/app/oracle'#ORACLE_BASE set from environment
hjjdb.__pga_aggregate_target=104857600
hjjdb.__sga_target=314572800
hjjdb.__shared_io_pool_size=0
hjjdb.__shared_pool_size=150994944
hjjdb.__streams_pool_size=0
*._in_memory_undo=FALSE
*.audit_file_dest='/s01/app/oracle/admin/hjjdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DATA_DG/hjjdb/control01.ctl','+DG_FRA/hjjdb/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA_DG'
*.db_domain=''
*.db_name='hjjdb'
*.db_recovery_file_dest='+DG_FRA'
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest='/s01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=hjjdbXDB)'
*.open_cursors=300
*.pga_aggregate_target=104857600
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=314572800
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert=('+DATA_DG/hjj/datafile','+DATA_DG/hjjdb/datafile')
*.log_file_name_convert=('+DG_FRA/hjj/onlinelog','+DG_FRA/hjjdb/onlinelog')
4.启动目标数据库到NOMOUNT状态
[oracle@myrac1 dbs]$ export ORACLE_SID=hjjdb
[oracle@myrac1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 5 00:13:04 2014

Connected to an idle instance.

SQL> startup pfile='/s01/app/oracle/product/11.2.0/dbhome_1/dbs/inithjjdb.ora' nomount
ORACLE instance started.

Total System Global Area 313860096 bytes
Fixed Size 1336232 bytes
Variable Size 159386712 bytes
Database Buffers 146800640 bytes
Redo Buffers 6336512 bytes
5.在目标端配置listener.ora和tnsnames.ora
[grid@myrac1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /g01/app/grid/product/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = myrac1)(PORT = 1521))
)
)

ADR_BASE_LISTENER = /g01/app/grid

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

这部分需要自己添加，listener.ora文件中没有的。
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
#BEQUEATH CONFIG
(GLOBAL_DBNAME=hjjdb)
(SID_NAME=hjjdb)
(ORACLE_HOME=/s01/app/oracle/product/11.2.0/dbhome_1)
#PRESPAWN CONFIG
(PRESPAWN_MAX=20)
(PRESPAWN_LIST=
(PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))
)
)
)
[oracle@myrac1 admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /s01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

HJJ =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myrac1.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hjj)
)
)

HJJDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myrac1.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hjjdb)
)
)
~
6.重启目标端监听
[grid@myrac1 admin]$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 05-MAR-2014 00:25:16

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
[grid@myrac1 admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 05-MAR-2014 00:25:21

Starting /g01/app/grid/product/11.2.0/grid/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /g01/app/grid/product/11.2.0/grid/network/admin/listener.ora
Log messages written to /g01/app/grid/diag/tnslsnr/myrac1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myrac1)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 05-MAR-2014 00:25:21
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /g01/app/grid/product/11.2.0/grid/network/admin/listener.ora
Listener Log File /g01/app/grid/diag/tnslsnr/myrac1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myrac1)(PORT=1521)))
Services Summary...
Service "myrac1.oracle.com" has 1 instance(s).
Instance "hjjdb", status UNKNOWN, has 3 handler(s) for this service...
The command completed successfully
7.在源数据库备份DB
[oracle@myrac1 admin]$ export ORACLE_SID=orcl
[oracle@myrac1 admin]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Mar 5 00:28:44 2014

connected to target database: HJJ (DBID=2845675742)

RMAN>backup database format='/tmp/full_%s_%T_%p.bak' plus archivelog delete input;
--验证数源据库是否存在有效备份
RMAN> list backup of database;

using target database control file instead of recovery catalog

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

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 624.26M DISK 00:01:12 04-MAR-14
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20140304T184244
Piece Name: /tmp/full_1_3_20140304_1.bak
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1957940 04-MAR-14 +DATA_DG/hjj/datafile/system.260.837919351
2 Full 1957940 04-MAR-14 +DATA_DG/hjj/datafile/sysaux.261.837919391
3 Full 1957940 04-MAR-14 +DATA_DG/hjj/datafile/undotbs1.262.837919417
4 Full 1957940 04-MAR-14 +DATA_DG/hjj/datafile/users.264.837919457
5 Full 1957940 04-MAR-14 +DATA_DG/hjj/datafile/tbs01.dbf
6 Full 1957940 04-MAR-14 +DATA_DG/hjj/datafile/rman_tbs0.dbf
8.RMAN连接源数据库和目标数据库进行复制
[oracle@myrac1 admin]$ rman target sys/oracle@hjj auxiliary sys/oracle@hjjdb

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Mar 5 00:32:14 2014

connected to target database: HJJ (DBID=2845675742)
connected to auxiliary database: HJJDB (not mounted)
RMAN> duplicate target database to hjjdb nofilenamecheck;

Starting Duplicate Db at 05-MAR-14
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=135 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 Area 313860096 bytes

Fixed Size 1336232 bytes
Variable Size 109055064 bytes
Database Buffers 197132288 bytes
Redo Buffers 6336512 bytes

contents of Memory Script:
{
sql clone "alter system set db_name =
''HJJ'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''HJJDB'' 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 = ''HJJ'' comment= ''Modified by RMAN duplicate'' scope=spfile

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

Oracle instance shut down

Oracle instance started

Total System Global Area 313860096 bytes

Fixed Size 1336232 bytes
Variable Size 109055064 bytes
Database Buffers 197132288 bytes
Redo Buffers 6336512 bytes

Starting restore at 05-MAR-14
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=12 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 +DG_FRA/hjj/autobackup/2014_03_05/s_841366547.267.841366555
channel ORA_AUX_DISK_1: piece handle=+DG_FRA/hjj/autobackup/2014_03_05/s_841366547.267.841366555 tag=TAG20140305T005547
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:05
output file name=+DATA_DG/hjjdb/control01.ctl
output file name=+DG_FRA/hjjdb/control02.ctl
Finished restore at 05-MAR-14

database mounted
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:
{
set until scn 1975002;
set newname for datafile 1 to
"+data_dg";
set newname for datafile 2 to
"+data_dg";
set newname for datafile 3 to
"+data_dg";
set newname for datafile 4 to
"+data_dg";
set newname for datafile 5 to
"+DATA_DG/hjjdb/datafile/tbs01.dbf";
set newname for datafile 6 to
"+DATA_DG/hjjdb/datafile/rman_tbs0.dbf";
restore
clone database
;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 05-MAR-14
using channel ORA_AUX_DISK_1

。。。。。。。。。。。。。。。省略了很多行。。。。。。。。。。。。。。。。
至此，基于备份的RMAN DUPLICATE测试完成。
9.遇到的问题以及解决
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/05/2014 00:51:41
RMAN-05501: aborting duplication of target database
RMAN-05001: auxiliary file name +DATA_DG/hjj/onlinelog/group_1.258 conflicts with a file used by the target database

如果在RMAN恢复时不指定 nofilenamecheck 参数，则在数据文件相同文件名恢复时会出现RMAN-05501错误，当源数据库和目标数据库的数据库文件目录是一样的时候，必须使用 nofilenamecheck参数告诉rman源数据库和目标数据库拥有一样的文件目录和文件名。

二、基于活动数据库的复制
测试环境：同一台主机，OS=WIN7 64BIT; DB=oracle11gR2
1.在目标数据库端创建密码文件
C:\>orapwd file='C:\app\Administrator\product\11.2.0\dbhome_1\database\PWDbdup.ora' password=oracle entries=10
2.在目标数据库端创建相应的目录
C:\>mkdir -p D:\oradata\bdup --用于存放datafile,logfile,controlfile
C:\>mkdir -p C:\app\Administrator\admin\bdup\adump
3.在目标数据库端创建参数文件
可以在源数据库端创建目标端的参数文件，例如
SQL>create pfile='C:\app\Administrator\product\11.2.0\dbhome_1\database\initbdup.ora' from spfile;
编辑initbdup.ora文件，内容如下：
bdup.__db_cache_size=285212672
bdup.__java_pool_size=16777216
bdup.__large_pool_size=16777216
bdup.__oracle_base='C:\app\Administrator'#ORACLE_BASE set from environment
bdup.__pga_aggregate_target=419430400
bdup.__sga_target=822083584
bdup.__shared_io_pool_size=0
bdup.__shared_pool_size=469762048
bdup.__streams_pool_size=16777216
*.audit_file_dest='C:\app\Administrator\admin\bdup\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_file_record_keep_time=7
*.control_files='D:\oradata\bdup\control01.ctl','D:\oradata\bdup\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='bdup'
*.db_recovery_file_dest='C:\app\Administrator\flash_recovery_area'
*.db_recovery_file_dest_size=4102029312
*.diagnostic_dest='C:\app\Administrator'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=bdupXDB)'
*.fast_start_mttr_target=90
*.job_queue_processes=1000
*.memory_target=1232076800
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=587202560
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert=('C:\app\Administrator\oradata\orcl','D:\oradata\bdup')
*.log_file_name_convert=('C:\app\Administrator\oradata\orcl','D:\oradata\bdup')
4.启动目标数据库到NOMONNT
C:\>set ORACLE_SID=bdup
SQL> startup pfile='C:\app\Administrator\product\11.2.0\dbhome_1\database\initbdup.ora' nomount
ORACLE instance started.

Total System Global Area 1235959808 bytes
Fixed Size 2175288 bytes
Variable Size 939527880 bytes
Database Buffers 285212672 bytes
Redo Buffers 9043968 bytes
5.配置目标端监听和服务
listener.ora文件内容如下：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
(SID_DESC =
(GLOBAL_DBNAME = orcl.oracle.com)
(ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = bdup)
(ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
(SID_NAME = bdup)
)
)
tnsnames.ora文件添加如下内容：
# tnsnames.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

BDUP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = kermart)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bdup)
)
)
6.重启目标端监听
C:\>lsnrctl reload
C:\>lsnrctl status
7.RMAN连接源数据库和目标数据库进行复制
C:\>rman target sys/oracle@orcl auxiliary sys/oracle@bdup
RMAN>duplicate database to BDUP FROM ACTIVE DATABASE NOFILENAMECHECK;

8.测试目标数据库
C:\>set ORACLE_SID=bdup
C:\>sqlplus / as sysdba
SQL> set linesize 1000
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
-------------------------------- -----------------------
bdup OPEN
SQL> select * from v$dbfile;
FILE# NAME
---------- ---------------------------------
1 D:\ORADATA\BDUP\SYSTEM01.DBF
2 D:\ORADATA\BDUP\SYSAUX01.DBF
3 D:\ORADATA\BDUP\UNDOTBS01.DBF
4 D:\ORADATA\BDUP\USERS01.DBF
5 D:\ORADATA\BDUP\EXAMPLE01.DBF
6 D:\ORADATA\BDUP\TBS01.DBF
7 D:\ORADATA\BDUP\UNDOTBS02.DBF
8 D:\ORADATA\BDUP\RMAN_TBS01.DBF

8 rows selected.
9.遇到的错误及解决
RMAN>duplicate database to BDUP FROM ACTIVE DATABASE SPFILE NOFILENAMECHECK;
RMAN-03002: failure of Duplicate Db command
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on d1 channel
ORA-17628: Oracle error 19505 returned by remote Oracle server
需要设置DB_FILE_NAME_CONVERT参数，这个参数我在参数文件中已经指定了，所以不需要加SPFILE选项；如果要在RMAN中指定，命令如下
RMAN>run {
duplicate database to BDUP FROM ACTIVE DATABASE SPFILE NOFILENAMECHECK;
spfile
set db_file_name_convert='C:\app\Administrator\oradata\orcl','D:\oradata\bdup'
set log_file_name_convert='C:\app\Administrator\oradata\orcl','D:\oradata\bdup'
set log_archive_dest_1=''
...........都可以设置........
set db_unique_name=bdup;
}

至此，RMAN两种类型的复制数据库方式测试完毕，也可以利用RMAN复制创建standby数据库。

总结：

基于备份的复制，RMAN必须执行基于时间点的恢复，即使没有明确的时间点，因为联机日志文件在源数据库没有备份，不能应用到目标数据库。目标数据库最远的恢复点是源数据库最近归档的日志文件。对于基于活动数据库的复制，需要做介质恢复，并且以resetlogs方式打开数据库。

在复制过程中，RMAN会自动做以下

1. 为辅助实例创建默认的spfile(复制不涉及standby database或者Server parameter files没有被复制或者辅助实例没有用spfile启动)
2. 从活动数据库的备份或拷贝中恢复，最新的控制文件满足UNTIL选项。
3. 挂载从活动数据库中备份或拷贝的控制文件
4. 用RMAN恢复备份中的数据文件到辅助实例，这步应用于基于备份的复制。
5. 恢复和拷贝复制数据文件，用增量备份和归档日志恢复到非当前的时间点。
6. 关闭重启实例到NOMOUNT状态
7. 创建新的控制文件，然后创建并存储新的DBID在数据库文件中。
8. 以resetlogs方式打开复制数据库，为新数据库创建联机日志。

--------------------------------------推荐阅读 --------------------------------------

--------------------------------------分割线 --------------------------------------

oracle 数据库 oracle数据库

安科网

Oracle 11g RMAN复制数据库的测试

myinfinity

myinfinity

相关推荐

甲骨文推出Oracle Exadata云服务X8M

限制ip访问Oracle数据库的方法步骤

关于使用PLSQL Developer时出现报错ora-12514的问题

oracle锁表该如何解决

Oracle表字段有Oracle关键字出现异常解决方案

oracle备份之备份测试脚本的方法（冷备、热备、rman)

Oracle和MySQL的数据导入为何差别这么大

Oracle数据库的实例/表空间/用户/表之间关系简单讲解

浅谈入门级oracle数据库数据导入导出步骤

oracle数据库的删除方法详解

Oracle通过LogMiner实现数据同步迁移

ORACLE数据库中Rownum用法详解

oracle数据库关于索引建立及使用的详细介绍

Oracle 11g 数据库的部署的图文教程

静默安装卸载 ORACLE

oracle数据库跟踪工具

踩坑了！踩坑了！NHibernate使用批量插入Oracle数据，Batch属性

oracle 11g修改归档日志目录及大小

Logstash Oracle同步设置

服务器断电导致的ORACLE异常 : ORA-00214 ORA-01033 ORA-01034 ORA-00172 ORA-27101

关于ORACLE索引的几种扫描方式

Oracle期末复习

Oracle 导入数据库dmp文件

oracle 授予权限和收回权限 grant和revoke 以及角色的创建删除

oracle执行计划打印

Oracle数据库视图创建以及优点分析

myinfinity