AI 助理
备案控制台
开发者社区 数据库 文章 正文

Duplicating a Database Without Recovery Catalog or Target Connection

2014-07-20 890
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云原生数据仓库AnalyticDB MySQL版，基础版 8ACU 100GB 1个月
简介： Duplicating a Database Without Recovery Catalog or Target Connection duplicate体系结构 搞点英语出来吧，英语不好的哥们就好好翻译翻译吧，这段英语是必须看懂的。

  1. Duplicating a Database Without Recovery Catalog or Target Connection

    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:

 

 

 

 

简单点就是 ①基于备份 ，而基于备份又分为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. 本次实验原理图

  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/$ORACLE_SID/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>

 

  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>

  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_TAG20140720T210027_9wqhcdsw_.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_5_9vj2hrk1_.arc RECID=1 STAMP=852157306

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_20/o1_mf_1_33_9wqhcc06_.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_TAG20140720T210155_9wqhg4nj_.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_TAG20140720T210155_9wqhjkgq_.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_TAG20140720T210315_9wqhjmb6_.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_34_9wqhjlyf_.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_TAG20140720T210027_9wqhcdsw_.bkp 100% 1055MB 10.6MB/s 01:40

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

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

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

[oracle@rhel6 orcl]$

 

 

  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]$

 

确保传递到目的地：

 

 

 

 

 

 

  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_archive_format='%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]$

 

 

  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

Variable Size         159387560 bytes

Database Buffers     50331648 bytes

Redo Buffers         5226496 bytes

SQL>

 

 

 

 

 

 

  1. 连接到auxiliary instance并复制数据库

 

 

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

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

 

[oracle@rhel6 dbs]$ rman auxiliary /

 

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jul 20 22:11:21 2014

 

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

 

connected to auxiliary database: ORCL (not mounted)

 

RMAN> duplicate target database to orcl backup location '/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_20' nofilenamecheck;

 

Starting Duplicate Db at 20-JUL-14

 

contents of Memory Script:

{

sql clone "alter system set db_name =

''ORCL'' comment=

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

sql clone "alter system set db_unique_name =

''ORCL'' comment=

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

shutdown clone immediate;

startup clone force nomount

restore clone primary controlfile from '/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_20/o1_mf_ncsnf_TAG20140720T210155_9wqhjkgq_.bkp';

alter clone database mount;

}

executing Memory Script

 

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

 

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

 

Oracle instance shut down

 

Oracle instance started

 

Total System Global Area 313159680 bytes

 

Fixed Size 2212936 bytes

Variable Size 260049848 bytes

Database Buffers 46137344 bytes

Redo Buffers 4759552 bytes

 

Starting restore at 20-JUL-14

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=18 device type=DISK

 

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/orcl/control01.ctl

output file name=/u01/app/oracle/flash_recovery_area/orcl/control02.ctl

Finished restore at 20-JUL-14

 

database mounted

released channel: ORA_AUX_DISK_1

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=18 device type=DISK

Using previous duplicated file /u01/app/oracle/oradata/orcl/system01.dbf for datafile 1 with checkpoint SCN of 1403286

Using previous duplicated file /u01/app/oracle/oradata/orcl/sysaux01.dbf for datafile 2 with checkpoint SCN of 1403286

Using previous duplicated file /u01/app/oracle/oradata/orcl/undotbs01.dbf for datafile 3 with checkpoint SCN of 1403286

Using previous duplicated file /u01/app/oracle/oradata/orcl/users01.dbf for datafile 4 with checkpoint SCN of 1403286

Using previous duplicated file /u01/app/oracle/oradata/orcl/example01.dbf for datafile 5 with checkpoint SCN of 1403286

 

contents of Memory Script:

{

catalog clone datafilecopy "/u01/app/oracle/oradata/orcl/system01.dbf",

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

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

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

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

switch clone datafile 1 to datafilecopy

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

switch clone datafile 2 to datafilecopy

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

switch clone datafile 3 to datafilecopy

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

switch clone datafile 4 to datafilecopy

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

switch clone datafile 5 to datafilecopy

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

}

executing Memory Script

 

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/orcl/system01.dbf RECID=1 STAMP=853452698

cataloged datafile copy

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

cataloged datafile copy

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

cataloged datafile copy

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

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/orcl/example01.dbf RECID=5 STAMP=853452698

 

datafile 1 switched to datafile copy

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

 

datafile 2 switched to datafile copy

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

 

datafile 3 switched to datafile copy

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

 

datafile 4 switched to datafile copy

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

 

datafile 5 switched to datafile copy

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

 

contents of Memory Script:

{

set until scn 1403324;

recover

clone database

delete archivelog

;

}

executing Memory Script

 

executing command: SET until clause

 

Starting recover at 20-JUL-14

using channel ORA_AUX_DISK_1

 

starting media recovery

 

channel ORA_AUX_DISK_1: starting archived log restore to default destination

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=34

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_20/o1_mf_annnn_TAG20140720T210315_9wqhjmb6_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_20/o1_mf_annnn_TAG20140720T210315_9wqhjmb6_.bkp tag=TAG20140720T210315

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_20/o1_mf_1_34_9wqmjvkt_.arc thread=1 sequence=34

channel clone_default: deleting archived log(s)

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_20/o1_mf_1_34_9wqmjvkt_.arc RECID=1 STAMP=853452699

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

Finished recover at 20-JUL-14

 

contents of Memory Script:

{

shutdown clone immediate;

startup clone nomount;

sql clone "alter system set db_name =

''ORCL'' 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

 

database dismounted

Oracle instance shut down

 

connected to auxiliary database (not started)

Oracle instance started

 

Total System Global Area 313159680 bytes

 

Fixed Size 2212936 bytes

Variable Size 260049848 bytes

Database Buffers 46137344 bytes

Redo Buffers 4759552 bytes

 

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

 

Fixed Size 2212936 bytes

Variable Size 260049848 bytes

Database Buffers 46137344 bytes

Redo Buffers 4759552 bytes

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

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 SIZE 50 M ,

GROUP 2 SIZE 50 M ,

GROUP 3 SIZE 50 M

DATAFILE

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

CHARACTER SET ZHS16GBK

 

 

contents of Memory Script:

{

set newname for tempfile 1 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",

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

switch clone datafile all;

switch clone datafile 2 to datafilecopy

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

switch clone datafile 3 to datafilecopy

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

switch clone datafile 4 to datafilecopy

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

switch clone datafile 5 to datafilecopy

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

}

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=853452713

cataloged datafile copy

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

cataloged datafile copy

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

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/orcl/example01.dbf RECID=4 STAMP=853452713

 

 

datafile 2 switched to datafile copy

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

 

datafile 3 switched to datafile copy

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

 

datafile 4 switched to datafile copy

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

 

datafile 5 switched to datafile copy

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

 

contents of Memory Script:

{

Alter clone database open resetlogs;

}

executing Memory Script

 

database opened

Finished Duplicate Db at 20-JUL-14

 

RMAN>

 

  1. 可能的报错

    1. fra满了

channel ORA_AUX_DISK_1: starting archived log restore to default destination

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=34

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_20/o1_mf_annnn_TAG20140720T210315_9wqhjmb6_.bkp

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 07/20/2014 22:03:42

RMAN-03015: error occurred in stored script Memory Script

ORA-19870: error while restoring backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_20/o1_mf_annnn_TAG20140720T210315_9wqhjmb6_.bkp

ORA-19809: limit exceeded for recovery files

ORA-19804: cannot reclaim 1294336 bytes disk space from 4070572032 limit

 

RMAN>

 

 

解决办法：由于fra是直接从目标数据库copy过来的，可能db_recovery_file_dest_size    设置过小，所以启动auxiliary库到nomount状态，然后把该参数改大一点即可（alter system set db_recovery_file_dest_size=8G;）

 

  1. 备份位置

 

RMAN> duplicate target database to orcl spfile backup location '/u01/app/oracle/fast_recovery_area/ORCL' nofilenamecheck;

 

Starting Duplicate Db at 20-JUL-14

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 07/20/2014 21:52:23

RMAN-05569: SPFILE backup not found in /u01/app/oracle/fast_recovery_area/ORCL

 

解决办法：将命令中的backup location修改为备份的直接位置：

duplicate target database to orcl backup location '/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_20' nofilenamecheck;

大家可以看到ORCL目录下仍然有很多其它文件夹的：

 

 

 

  1. 验证

[oracle@rhel6 2014_07_20]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jul 20 22:13:49 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> select open_mode,name,dbid from v$database;

 

OPEN_MODE     NAME     DBID

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

READ WRITE     ORCL 1381233448

 

SQL> archive log list;

Database log mode     Archive Mode

Automatic archival     Enabled

Archive destination     USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 1

Next log sequence to archive 1

Current log sequence     1

SQL>

 

SQL> select * from lhr.test_duplicate;

 

    ID TEXT

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

     1 a

     2 b

 

SQL> show parameter spfile

 

NAME                 TYPE     VALUE

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

spfile                 string     /u01/app/oracle/product/11.2.0

                         /dbhome_1/dbs/spfileorcl.ora

SQL>

 

搞定。。。。。。。。。。。。。。。。。

  1. drop database

删掉数据库准备做其它实验：

 

[oracle@rhel6 2014_07_20]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jul 20 22:17:16 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> shutdown abort

ORACLE instance shut down.

SQL> startup mount restrict;

ORACLE instance started.

 

Total System Global Area 217157632 bytes

Fixed Size         2211928 bytes

Variable Size         159387560 bytes

Database Buffers     50331648 bytes

Redo Buffers         5226496 bytes

Database mounted.

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]$ rman target /

 

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

 

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

 

connected to target database: ORCL (DBID=1381233448, not open)

 

RMAN> drop database;

 

database name is "ORCL" and DBID is 1381233448

 

Do you really want to drop the database (enter YES or NO)? yes

database dropped

 

RMAN>

文章标签：
云原生数据仓库AnalyticDB MySQL版
数据库备份
关系型数据库
Oracle
数据库
SQL
OLAP
Perl
Linux
网络协议
关键词：
Database connection
Database recovery
相关实践学习
AnalyticDB MySQL海量数据秒级分析体验
快速上手AnalyticDB MySQL，玩转SQL开发等功能！本教程介绍如何在AnalyticDB MySQL中，一键加载内置数据集，并基于自动生成的查询脚本，运行复杂查询语句，秒级生成查询结果。
阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
云原生数据仓库AnalyticDB MySQL版是一种支持高并发低延时查询的新一代云原生数据仓库，高度兼容MySQL协议以及SQL:92、SQL:99、SQL:2003标准，可以对海量数据进行即时的多维分析透视和业务探索，快速构建企业云上数据仓库。 了解产品 https://www.aliyun.com/product/ApsaraDB/ads
小麦苗
目录
相关文章
程序员对白
|
SQL Oracle 关系型数据库
Oracle中Error while performing database login with the XXXdriver; Listener refused the connection wit...
Oracle中Error while performing database login with the XXXdriver; Listener refused the connection wit...
程序员对白
260 0
1507405490236623
|
关系型数据库 数据库 MySQL
关于网站出现 Error establishing a database connection 的解决办法
最近网站经常出现这个Error establishing a database connection ，真的是醉了，网上的解决办法就是重启数据库，或者开启configuration.php中的 true 【 define('WP_DEBUG', false);】，然后看到全部的提示，就是这个烦人的Error establishing a database connection，真的是醉了，下面讲讲我的解决办法。
1507405490236623
27262 0
liuzhijian
|
Java 数据库连接 数据库
### Error querying database. Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is jav
数据库连接超时，是数据库连接时的相关配置写错，例如：数据库密码，驱动等问题
liuzhijian
3352 0
阿福Chris
|
关系型数据库 数据库
psql: FATAL: the database system is in recovery mode - 问题定位方法与解决
阿福Chris
7682 0
李金泽
|
Oracle 关系型数据库 数据库
Oracle中Error while performing database login with the XXXdriver; Listener refused the connection with the following error; ORA-12505,TNS:listener does
一次连接数据库怎么也连接不上，查了多方面资料，终于找到答案，总结 首先应该保证数据库的服务启动 在myeclipse的数据库视图中点 右键->new 弹出database driver的窗口， Driver template选择oracle（thin driver）， Driver name 输入...
李金泽
1133 0
李金泽
|
人工智能 Oracle 关系型数据库
Oracle中Error while performing database login with the XXXdriver; Listener refused the connection with the following error; ORA-12505,TNS:listener does
欢迎关注大数据和人工智能技术文章发布的微信公众号：清研学堂，在这里你可以学到夜白（作者笔名）精心整理的笔记，让我们每天进步一点点，让优秀成为一种习惯！ 一次连接数据库怎么也连接不上，查了多方面资料，终于找到答案，总结 首先应该保证数据库的服务启动 在myeclipse的数据库视图中点 右键->ne...
李金泽
1773 0
芷沁
|
SQL 关系型数据库 对象存储
Database Recovery in GitLab – Implementing Database Disaster Tolerance & High Availability
How can we restore accidentally deleted database in GitLab? How can we implement database backup, recovery, disaster tolerance, and high availability?
芷沁
2178 0
技术小胖子
|
SQL 数据库 数据安全/隐私保护
BUG记录之 Database Connection Can’t Be Open!
技术小胖子
1179 0
liuzhijian
|
Java 数据库连接 数据库
### Error querying database. Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is jav
数据库连接超时，是数据库连接时的相关配置写错，例如：数据库密码，驱动等问题
liuzhijian
2113 0
史迪奇2号
|
数据库
DB_RUNRECOVERY: Fatal error, run database recovery StackTrace问题的解决办法
史迪奇2号
1064 0