合 Duplicating a Database Without Recovery Catalog or Target Connection

 

0    273    1

Tags：

👉 本文共约14890个字，系统预计阅读时间或需56分钟。

    1. duplicate体系结构

搞点英语出来吧，英语不好的哥们就好好翻译翻译吧，这段英语是必须看懂的。。。。

You can duplicate a source database to a destination database, which can be on the same or different computers. The database instance associated with the duplicate database is called the auxiliary instance. All duplication techniques require a connection to the auxiliary instance. The diagram shows you the following techniques for database duplication:

  • From an active database, connected to the target and auxiliary instances
  • From backup, connected to the target and auxiliary instances
  • From backup, connected to the auxiliary instance, not connected to the target, but with recovery catalog connection
  • From backup, connected to the auxiliary instance, not connected to the target and the recovery catalog

Choosing a technique to duplicate your database—always with connection to the auxiliary instance:

img

简单点就是 ①基于备份 ，而基于备份又分为3种情况 ②基于活动数据库

  1. 本次实验简介

本次实验就是基于备份，但是不连接到目标数据库，也不连接到恢复目录。

When you duplicate a database without a target database connection and without a recovery catalog, RMAN uses a BACKUP LOCATION where all necessary backups and copies reside.

The diagram illustrates backup-based duplication without connections to the target or to the recovery catalog database instance. A disk backup location containing all the backups or copies for duplication must be available to the destination host.

  1. 本次实验原理图

img

  1. 环境及搭建要求

环境：

  • Destination host ：192.168.59.133 sid：orcl database：11gR2

注意事项说明：

  • 这里的duplicate database 和 source database 的目录结构完全一样
  • 源数据库和辅助数据库的几种不同名称：source/target database ---->>>>>----- (destination host)/duplicate database (auxiliary instance)

source database环境：

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit 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. 对辅助数据库的要求

辅助数据库应该提前安装好数据库，配置好环境变量。。。。。

辅助数据库的环境变量配置：

[root@rhel6 ~]# su - oracle

[oracle@rhel6 ~]$ more .bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

fi

# User specific environment and startup programs

export PATH=$PATH:/sbin/:$HOME/bin

# for oracle user

unset USERNAME

export GLOBAL_DB_UNIQUE_NAME=orcl

export ORACLE_HOSTNAME=192.168.59.133

export ORACLE_UNQNAME=orcl

export EDITOR=vi

export ORACLE_SID=orcl

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib

export PATH=$ORACLE_HOME/bin:$PATH

umask 022

#export NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"

export SQLPATH=$ORACLE_HOME/sqlplus/admin

alias sqlplus='rlwrap sqlplus'

alias rman='rlwrap rman'

alias asmcmd='rlwrap asmcmd'

alias alert_log='tail -200f $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLESID/trace/alert$ORACLE_SID.log'

alias alert_xml='tail -200f $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/alert/log.xml'

alias alert_listener='tail -200f $ORACLE_BASE/diag/tnslsnr/rhel6/listener/trace/listener.log'

[oracle@rhel6 ~]$

  1. 正式开始

    1. 前期准备

      1. 建表

--登录源数据库并创建一个新的表

[oracle@rhel6 ~]$ sqlplus lhr/lhr

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jul 20 20:54:04 2014

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table test_duplicate(id number,text varchar2(20));

Table created.

SQL> insert into test_duplicate values(1,'a');

1 row created.

SQL> insert into test_duplicate values(2,'b');

1 row created.

SQL> commit;

Commit complete.

SQL>

img

  1. 数据库归档模式

让源数据库处于归档模式：

[oracle@rhel6 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jul 20 20:57:18 2014

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 31

Next log sequence to archive 33

Current log sequence 33

SQL>

img

  1. 备份数据库

--备份source database并将备份传送到destination database

[oracle@rhel6 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jul 20 20:59:18 2014

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

connected to target database: ORCL (DBID=1379935487)

RMAN> backup database include current controlfile plus archivelog delete input;

Starting backup at 20-JUL-14

current log archived

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=40 device type=DISK

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=5 RECID=1 STAMP=852157306

input archived log thread=1 sequence=6 RECID=2 STAMP=852374702

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

input archived log thread=1 sequence=8 RECID=4 STAMP=852384132

input archived log thread=1 sequence=9 RECID=5 STAMP=852384155

input archived log thread=1 sequence=10 RECID=6 STAMP=852384188

input archived log thread=1 sequence=11 RECID=7 STAMP=852384214

input archived log thread=1 sequence=12 RECID=8 STAMP=852384270

input archived log thread=1 sequence=13 RECID=9 STAMP=852384372

input archived log thread=1 sequence=14 RECID=10 STAMP=852384413

input archived log thread=1 sequence=15 RECID=11 STAMP=852384454

input archived log thread=1 sequence=16 RECID=12 STAMP=852384496

input archived log thread=1 sequence=17 RECID=13 STAMP=852384541

input archived log thread=1 sequence=18 RECID=14 STAMP=852384629

input archived log thread=1 sequence=19 RECID=15 STAMP=852384675

input archived log thread=1 sequence=20 RECID=16 STAMP=852384764

input archived log thread=1 sequence=21 RECID=17 STAMP=852385529

input archived log thread=1 sequence=22 RECID=18 STAMP=852391136

input archived log thread=1 sequence=23 RECID=19 STAMP=852398713

input archived log thread=1 sequence=24 RECID=20 STAMP=852398768

input archived log thread=1 sequence=25 RECID=21 STAMP=852398817

input archived log thread=1 sequence=26 RECID=22 STAMP=852398883

input archived log thread=1 sequence=27 RECID=23 STAMP=852398933

input archived log thread=1 sequence=28 RECID=24 STAMP=852426016

input archived log thread=1 sequence=29 RECID=25 STAMP=852464305

input archived log thread=1 sequence=30 RECID=26 STAMP=853429674

input archived log thread=1 sequence=31 RECID=27 STAMP=853431462

input archived log thread=1 sequence=32 RECID=28 STAMP=853437035

input archived log thread=1 sequence=33 RECID=29 STAMP=853448427

channel ORA_DISK_1: starting piece 1 at 20-JUL-14

channel ORA_DISK_1: finished piece 1 at 20-JUL-14

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_20/o1_mf_annnn_TAG20140720T2100279wqhcdsw.bkp tag=TAG20140720T210027 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:26

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_05/o1_mf_1_59vj2hrk1.arc RECID=1 STAMP=852157306

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_08/o1_mf_1_69vppscdl.arc RECID=2 STAMP=852374702

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_08/o1_mf_1_79vpzyom3.arc RECID=3 STAMP=852384088

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_08/o1_mf_1_89vq0006p.arc RECID=4 STAMP=852384132

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_08/o1_mf_1_99vq00or2.arc RECID=5 STAMP=852384155

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_08/o1_mf_1_109vq01noz.arc RECID=6 STAMP=852384188

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_08/o1_mf_1_119vq02jpc.arc RECID=7 STAMP=852384214

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_08/o1_mf_1_129vq049l9.arc RECID=8 STAMP=852384270

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_08/o1_mf_1_139vq07lm2.arc RECID=9 STAMP=852384372

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_08/o1_mf_1_149vq08t5m.arc RECID=10 STAMP=852384413

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_08/o1_mf_1_159vq0b484.arc RECID=11 STAMP=852384454

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_08/o1_mf_1_169vq0cgcr.arc RECID=12 STAMP=852384496

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_08/o1_mf_1_179vq0dvgv.arc RECID=13 STAMP=852384541

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_08/o1_mf_1_189vq0hlld.arc RECID=14 STAMP=852384629

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_08/o1_mf_1_199vq0k2n4.arc RECID=15 STAMP=852384675

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_08/o1_mf_1_209vq0mssr.arc RECID=16 STAMP=852384764

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_08/o1_mf_1_219vq1cn5m.arc RECID=17 STAMP=852385529

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_08/o1_mf_1_229vq6v0g7.arc RECID=18 STAMP=852391136

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_08/o1_mf_1_239vqg7jyw.arc RECID=19 STAMP=852398713

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_08/o1_mf_1_249vqg9c92.arc RECID=20 STAMP=852398768

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_08/o1_mf_1_259vqgbt43.arc RECID=21 STAMP=852398817

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_08/o1_mf_1_269vqgdv00.arc RECID=22 STAMP=852398883

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_08/o1_mf_1_279vqggh4k.arc RECID=23 STAMP=852398933

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_09/o1_mf_1_289vr8wxfx.arc RECID=24 STAMP=852426016

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_09/o1_mf_1_299vsg9jqw.arc RECID=25 STAMP=852464305

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_20/o1_mf_1_309wpx0zbh.arc RECID=26 STAMP=853429674

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_20/o1_mf_1_319wpys3xn.arc RECID=27 STAMP=853431462

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_20/o1_mf_1_329wq47c1r.arc RECID=28 STAMP=853437035

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_20/o1_mf_1_339wqhcc06.arc RECID=29 STAMP=853448427

Finished backup at 20-JUL-14

Starting backup at 20-JUL-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

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

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

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

input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf

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

channel ORA_DISK_1: starting piece 1 at 20-JUL-14

channel ORA_DISK_1: finished piece 1 at 20-JUL-14

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_20/o1_mf_nnndf_TAG20140720T2101559wqhg4nj.bkp tag=TAG20140720T210155 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 20-JUL-14

channel ORA_DISK_1: finished piece 1 at 20-JUL-14

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_20/o1_mf_ncsnf_TAG20140720T2101559wqhjkgq.bkp tag=TAG20140720T210155 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 20-JUL-14

Starting backup at 20-JUL-14

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=34 RECID=30 STAMP=853448595

channel ORA_DISK_1: starting piece 1 at 20-JUL-14

channel ORA_DISK_1: finished piece 1 at 20-JUL-14

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_20/o1_mf_annnn_TAG20140720T2103159wqhjmb6.bkp tag=TAG20140720T210315 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_20/o1_mf_1_349wqhjlyf.arc RECID=30 STAMP=853448595

Finished backup at 20-JUL-14

RMAN>

这里记录备份位置(Backup location)，默认在闪回恢复区，后边恢复的时候需要使用：

/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_20/

  1. 将备份内容拷贝到destination host

[oracle@rhel6 orcl]$ scp -r /u01/app/oracle/flash_recovery_area/ oracle@192.168.59.133:/u01/app/oracle/

oracle@192.168.59.133's password:

o1_mf_annnn_TAG20140720T2100279wqhcdsw.bkp 100% 1055MB 10.6MB/s 01:40

o1_mf_ncsnf_TAG20140720T2101559wqhjkgq.bkp 100% 9632KB 3.1MB/s 00:03

o1_mf_nnndf_TAG20140720T2101559wqhg4nj.bkp 100% 1018MB 10.7MB/s 01:35

o1_mf_annnn_TAG20140720T2103159wqhjmb6.bkp 100% 1266KB 1.2MB/s 00:00

[oracle@rhel6 orcl]$

img

  1. 创建pfile 参数文件

--生成文本初始化参数文件并传送到destination host

[oracle@rhel6 orcl]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jul 20 21:32:25 2014

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create pfile from spfile;

File created.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@rhel6 orcl]$ cd $ORACLE_HOME/dbs

[oracle@rhel6 dbs]$ ll

total 9592

-rw-rw----. 1 oracle oinstall 1544 Jul 20 15:47 hc_orcl.dat

-rw-rw----. 1 oracle oinstall 1544 Jul 9 11:39 hc_rman.dat

-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora

-rw-r--r--. 1 oracle oinstall 1015 Jul 20 21:32 initorcl.ora

-rw-r-----. 1 oracle oinstall 24 Jul 5 21:39 lkORCL

-rw-r-----. 1 oracle oinstall 24 Jul 5 21:56 lkRMAN

-rw-r-----. 1 oracle oinstall 1536 Jul 10 16:33 orapworcl

-rw-r-----. 1 oracle oinstall 1536 Jul 9 16:32 orapwrman

-rw-r-----. 1 oracle oinstall 9781248 Jul 20 21:03 snapcf_orcl.f

-rw-r-----. 1 oracle oinstall 3584 Jul 20 15:58 spfileorcl.ora

-rw-r-----. 1 oracle oinstall 2560 Jul 9 16:37 spfilerman.ora

[oracle@rhel6 dbs]$ scp $ORACLE_HOME/dbs/initorcl.ora oracle@192.168.59.133:$ORACLE_HOME/dbs/

oracle@192.168.59.133's password:

initorcl.ora 100% 1015 1.0KB/s 00:00

[oracle@rhel6 dbs]$

确保传递到目的地：

img

  1. 创建和source database的数据文件相关的目录结构

根据传递过来的参数文件来创建目录：

[oracle@rhel6 dbs]$ more initorcl.ora

orcl.__db_cache_size=50331648

orcl.__java_pool_size=4194304

orcl.__large_pool_size=4194304

orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

orcl.__pga_aggregate_target=130023424

orcl.__sga_target=184549376

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=117440512

orcl.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl'

*.db_block_size=8192

*.db_domain='lhr.com'

*.db_name='orcl'

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=4070572032

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.job_queue_processes=1000

*.local_listener='LSNR_ORCL'

*.log_archiveformat='%t%s_%r.dbf'

*.memory_target=314572800

*.open_cursors=300

*.processes=50

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=60

*.undo_tablespace='UNDOTBS1'

[oracle@rhel6 dbs]$ mkdir -p /u01/app/oracle/oradata/orcl

[oracle@rhel6 dbs]$ mkdir -p /u01/app/oracle/admin/orcl/adump

[oracle@rhel6 dbs]$

  1. 创建密码文件

[oracle@rhel6 dbs]$ orapwd file=?/dbs/orapworcl password=lhr

[oracle@rhel6 dbs]$ ll -h

total 12K

-rw-r--r--. 1 oracle oinstall 2.8K May 15 2009 init.ora

-rw-r--r--. 1 oracle oinstall 13 Jul 20 16:03 initorcl.ora

-rw-r-----. 1 oracle oinstall 1.5K Jul 20 16:09 orapworcl

[oracle@rhel6 dbs]$

img

  1. 实施数据库的复制

--启动auxiliary instance到nomount状态

  1. 启动Auxiliary 到nomout 状态

[oracle@rhel6 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jul 20 16:48:04 2014

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

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 217157632 bytes

Fixed Size 2211928 bytes

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
AiDBA后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！
验证码：
获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

标签：

Avatar photo

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复