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

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

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

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

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

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

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

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

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

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

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

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

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

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方式打开复制数据库,为新数据库创建联机日志。 

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

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

相关推荐