ITPub博客

首页 > 数据库 > Oracle > oracle 11g duplicate from active database 复制数据库(二)

oracle 11g duplicate from active database 复制数据库(二)

原创 Oracle 作者:eric0435 时间:2015-06-05 16:01:22 0 删除 编辑

下面介绍将数据库复制到远程主机相同目录结构的操作步骤
1.创建辅助实例的密码文件(这里辅助实例名为dup) ,在目的主机上为辅助实例创建密码文件可以有以下选项:
.手动创建密码文件,对于duplicate ... from active database有额外的要求。必须使用SYS用户ID并且密码必须与原数据库的密码相匹配。当想要使用单独的密码来创建密码文件时因此可以启动辅助实例并使用它来连接原数据库。

.在执行duplicate ... from active database命令时指定password file选项,在这种情况下,RMAN将原数据库的密码文件复制到目的主机上并且覆盖辅助实例已经存在的密码文件。如果原数据库密码文件有多个密码且你想让它们在副本数据库中使用时这种技术是非常有用的。

[oracle11@jingyong1 dbs]$ orapwd file=/u03/app/oracle/11.2.0/db/dbs/orapwdup password=system entries=10;

[oracle11@jingyong1 dbs]$ ls -lrt orapwdup
-rw-r----- 1 oracle11 oinstall 2560 Jun  5 09:40 orapwdup

2.创建辅助实例网络连接,修改监听文件,使用静态监听来监听辅助实例

[oracle11@jingyong1 admin]$ vi listener.ora


# listener.ora Network Configuration File: /u03/app/oracle/11.2.0/db/network/admin/listener.ora
# Generated by Oracle configuration tools.

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

SID_LIST_LISTENER =
  (SID_LIST =

    (SID_DESC =
     (SID_NAME = dup)
      (ORACLE_HOME =/u03/app/oracle/11.2.0/db)
    )
  )

[oracle11@jingyong1 admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 05-JUN-2015 11:25:35

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

Starting /u03/app/oracle/11.2.0/db/bin/tnslsnr: please wait...

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=jingyong1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                05-JUN-2015 11:25:35
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u03/app/oracle/11.2.0/db/network/admin/listener.ora
Listener Log File         /u03/app/oracle/diag/tnslsnr/jingyong1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jingyong1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "cs" has 1 instance(s).
  Instance "cs", status UNKNOWN, has 1 handler(s) for this service...
Service "dup" has 1 instance(s).
  Instance "dup", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

给辅助实例增加网络服务名

[oracle11@jingyong1 admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u03/app/oracle/11.2.0/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

dup =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dup)
      (UR = A)
    )
  )

3.创建辅助实例的参数文件,初始化参数文件的目录和内容依赖于给复制文件命名选择的策略。可以选择建议的技术来对原主机和目的主机使用相同的命名策略。因此对于Oracle RAC环境,对于原主机和目的主机可以使用相同的ORACLE_SID。在参数文件中将db_name设置为任意值,db_name是唯一必须设置的参数。创建参数文件有以下选项:
.为辅助实例来创建文本参数文件,初始化参数文件的存储目录是在主机上操作系统特定的缺省目录。例如,在Linux和UNIX中缺省参数文件名是ORACLE_HOME/dbs/initORACLE_SID.ora,在Windows平台上参数文件名是ORACLE_HOME\database\initORACLE_SID.ora

.在执行duplicate命令时指定spfile子句。duplicate ... spfile技术最简单,因为在执行复制时RMAN自动将原数据库的spfile文件复制到辅助实例或从备份中还原spfile文件。如果在辅助实例上存在spfile文件,那么RMAN就会覆盖它。

[oracle11@jingyong1 dbs]$ vi initdup.ora
db_name=dup
db_unique_name=dup
control_files= /u03/app/oracle/oradata/db/control01.ctl
remote_login_passwordfile=exclusive
compatible = 11.2.0.4.0
db_block_size=8192
sga_target=300M
sga_max_size=300M
pga_aggregate_target=32M

4.启动辅助实例,启动SQL*Plus并使用sysdba权限连接到辅助实例。将辅助实例启动到nomount状态(如果参数文件在缺省目录中startup命令不需要pfile参数)。注意:确保辅助实例使用文本参数文件来启动而不是SPFILE参数文件。不要创建控制文件或试图mount或open辅助实例。

[oracle11@jingyong1 dbs]$ export ORACLE_SID=dup
[oracle11@jingyong1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 5 09:58:16 2015

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  313860096 bytes
Fixed Size                  1364340 bytes
Variable Size             104861324 bytes
Database Buffers          201326592 bytes
Redo Buffers                6307840 bytes


 

5.在目标主机(运行被复制数据库的主机)配置辅助实例的网络服务名

[oracle11@oracle11g admin]$ vi tnsnames.ora
dup =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME =dup)
      (UR=A)
    )
  )

[oracle11@oracle11g admin]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 5 10:05:14 2015

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

SQL> conn sys/system@dup as sysdba
Connected.

6.加载或打开目标数据库,如果RMAN连接到原数据库作为目标数据库,那么原数据库为了执行复制必须设置为合适的状态。如果原数据库实例没有mount或open,那么将原数据库mount或open。如果执行active database复制,那么确保满足下面额外的条件:
-如果原数据库open,那么必须启用归档
-如果原数据库没有open,那么数据库不需要执行实例恢复
由于原数据库启用了归档所以可以将原数据库启动到open状态

[oracle11@oracle11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 5 08:35:45 2015

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  422670336 bytes
Fixed Size                  1365068 bytes
Variable Size             310381492 bytes
Database Buffers          104857600 bytes
Redo Buffers                6066176 bytes
Database mounted.
Database opened.

7.使用duplicate命令来执行数据库复制。因为我们这里复制后数据库名为dup与原数据库名不相同,所以不用指定spfile子句从原数据库复制SPFILE参数文件。因为复制后的副本数据库与原数据库的文件拥有相同文件名,所以在执行duplicate命令时要使用nofilenamecheck选项。如果目录不同,在pfile里加这个2个参数进行转换:db_file_name_convert,log_file_name_convert。如果指定password file选项那么RMAN将会从原数据库复制密码文件到目的主机上。RMAN会自动复制SPFILE参数文件到目的主机,使用SPFILE文件来启动辅助实例,复制所有需要的数据库文件和归档重做日志文件到目的主机,并恢复数据库。最终,RMAN将使用resetlogs选项来创建联机重做日志。

[oracle11@oracle11g ~]$ rman target sys/system@db catalog rman/rman@cs auxiliary sys/system@dup

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jun 5 10:54:38 2015

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

connected to target database: DB (DBID=1644809111)
connected to recovery catalog database
connected to auxiliary database: DUP (not mounted)

[oracle11@oracle11g ~]$ rman target sys/system@db catalog rman/rman@cs auxiliary sys/system@dup

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jun 5 11:09:58 2015

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

connected to target database: DB (DBID=1644809111)
connected to recovery catalog database
connected to auxiliary database: DUP (not mounted)

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

Starting Duplicate Db at 2015-06-05 11:10:13
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 Area     313860096 bytes

Fixed Size                     1364340 bytes
Variable Size                109055628 bytes
Database Buffers             197132288 bytes
Redo Buffers                   6307840 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''DB'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''DUP'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '/u03/app/oracle/oradata/db/control01.ctl';
   alter clone database mount;
}
executing Memory Script

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area     313860096 bytes

Fixed Size                     1364340 bytes
Variable Size                109055628 bytes
Database Buffers             197132288 bytes
Redo Buffers                   6307840 bytes

Starting backup at 2015-06-05 11:10:32
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u03/app/oracle/11.2.0/db/dbs/snapcf_db.f tag=TAG20150605T111033 RECID=4 STAMP=881579434
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 2015-06-05 11:10:36

database mounted

contents of Memory Script:
{
   set newname for datafile  1 to
 "/u03/app/oracle/oradata/db/system01.dbf";
   set newname for datafile  2 to
 "/u03/app/oracle/oradata/db/sysaux01.dbf";
   set newname for datafile  3 to
 "/u03/app/oracle/oradata/db/undotbs01.dbf";
   set newname for datafile  4 to
 "/u03/app/oracle/oradata/db/users01.dbf";
   set newname for datafile  5 to
 "/u03/app/oracle/oradata/db/test01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/u03/app/oracle/oradata/db/system01.dbf"   datafile
 2 auxiliary format
 "/u03/app/oracle/oradata/db/sysaux01.dbf"   datafile
 3 auxiliary format
 "/u03/app/oracle/oradata/db/undotbs01.dbf"   datafile
 4 auxiliary format
 "/u03/app/oracle/oradata/db/users01.dbf"   datafile
 5 auxiliary format
 "/u03/app/oracle/oradata/db/test01.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

executing command: SET NEWNAME

Starting backup at 2015-06-05 11:10:42
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u03/app/oracle/oradata/db/system01.dbf
output file name=/u03/app/oracle/oradata/db/system01.dbf tag=TAG20150605T111042
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:56
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u03/app/oracle/oradata/db/sysaux01.dbf
output file name=/u03/app/oracle/oradata/db/sysaux01.dbf tag=TAG20150605T111042
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u03/app/oracle/oradata/db/undotbs01.dbf
output file name=/u03/app/oracle/oradata/db/undotbs01.dbf tag=TAG20150605T111042
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u03/app/oracle/oradata/db/test01.dbf
output file name=/u03/app/oracle/oradata/db/test01.dbf tag=TAG20150605T111042
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u03/app/oracle/oradata/db/users01.dbf
output file name=/u03/app/oracle/oradata/db/users01.dbf tag=TAG20150605T111042
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2015-06-05 11:13:07

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/u03/archivelog/1_19_881232587.dbf" auxiliary format
 "/u03/app/oracle/11.2.0/db/dbs/arch1_19_881232587.dbf"   ;
   catalog clone archivelog  "/u03/app/oracle/11.2.0/db/dbs/arch1_19_881232587.dbf";
   switch clone datafile all;
}
executing Memory Script

Starting backup at 2015-06-05 11:13:11
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=19 RECID=38 STAMP=881579588
output file name=/u03/app/oracle/11.2.0/db/dbs/arch1_19_881232587.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 2015-06-05 11:13:12

cataloged archived log
archived log file name=/u03/app/oracle/11.2.0/db/dbs/arch1_19_881232587.dbf RECID=38 STAMP=881579595

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=881579595 file name=/u03/app/oracle/oradata/db/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=881579595 file name=/u03/app/oracle/oradata/db/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=881579595 file name=/u03/app/oracle/oradata/db/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=881579595 file name=/u03/app/oracle/oradata/db/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=8 STAMP=881579595 file name=/u03/app/oracle/oradata/db/test01.dbf

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

executing command: SET until clause

Starting recover at 2015-06-05 11:13:13
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 19 is already on disk as file /u03/app/oracle/11.2.0/db/dbs/arch1_19_881232587.dbf
archived log file name=/u03/app/oracle/11.2.0/db/dbs/arch1_19_881232587.dbf thread=1 sequence=19
media recovery complete, elapsed time: 00:00:00
Finished recover at 2015-06-05 11:13:17
Oracle instance started

Total System Global Area     313860096 bytes

Fixed Size                     1364340 bytes
Variable Size                109055628 bytes
Database Buffers             197132288 bytes
Redo Buffers                   6307840 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''DUP'' 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

sql statement: alter system set  db_name =  ''DUP'' 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     313860096 bytes

Fixed Size                     1364340 bytes
Variable Size                109055628 bytes
Database Buffers             197132288 bytes
Redo Buffers                   6307840 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUP" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 ( '/u03/app/oracle/oradata/db/redo01.log' ) SIZE 50 M  REUSE,
  GROUP   2 ( '/u03/app/oracle/oradata/db/redo02.log' ) SIZE 50 M  REUSE,
  GROUP   3 ( '/u03/app/oracle/oradata/db/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/u03/app/oracle/oradata/db/system01.dbf'
 CHARACTER SET ZHS16GBK


contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u03/app/oracle/oradata/db/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u03/app/oracle/oradata/db/sysaux01.dbf",
 "/u03/app/oracle/oradata/db/undotbs01.dbf",
 "/u03/app/oracle/oradata/db/users01.dbf",
 "/u03/app/oracle/oradata/db/test01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u03/app/oracle/oradata/db/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/db/sysaux01.dbf RECID=1 STAMP=881579617
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/db/undotbs01.dbf RECID=2 STAMP=881579617
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/db/users01.dbf RECID=3 STAMP=881579617
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/db/test01.dbf RECID=4 STAMP=881579617

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=881579617 file name=/u03/app/oracle/oradata/db/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=881579617 file name=/u03/app/oracle/oradata/db/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=881579617 file name=/u03/app/oracle/oradata/db/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=881579617 file name=/u03/app/oracle/oradata/db/test01.dbf

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

database opened
Finished Duplicate Db at 2015-06-05 11:13:55

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

请登录后发表评论 登录
全部评论
系统架构师,ACOUG MEMBER,Oracle ACE,Mail:yongjing.star@gmail.com Mobile:13875984558 QQ:409898894 Blog:www.jydba.net

注册时间:2011-10-12

  • 博文量
    527
  • 访问量
    6148368