ITPub博客

首页 > 数据库 > Oracle > 采用DUPLICATE 把asm数据库复制到文件系统

采用DUPLICATE 把asm数据库复制到文件系统

原创 Oracle 作者:lhrbest 时间:2014-07-22 21:09:02 0 删除 编辑

 

题记: 可以使用rman 的duplicate命令直接把asm存储的数据库复制到文件管理的系统,本文就是基于这样的情况。。。。。

有关rman的另外2篇文章:

http://blog.itpub.net/26736162/viewspace-1223253/

http://blog.itpub.net/26736162/viewspace-1223247/

 

  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. 环境及搭建要求

环境:

  • source host :192.168.59.130 sid:orclasm database:11gR2 文件系统:ASM系统
  • Destination host :192.168.59.135 sid:orcl database:11gR2

 

 

注意事项说明:

  • 这里列出source database主要是为了备份
  • 源数据库和辅助数据库的几种不同名称:source/target database ---->>>>>----- (destination host)/duplicate database (auxiliary instance)
  • 源数据库是ASM管理的,目标数据库是文件管理系统的
  • 源数据库开启了数据库闪回,块改变跟踪,属于归档模式

 

 

  1. source database环境

C:\Users\Administrator>sqlplus sys/lhr@orclasm as sysdba

 

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 7月 22 11:34:45 2014

 

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

 

 

连接到:

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

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

 

SQL> select * from v$version where rownum=1;

 

BANNER

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

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

 

SQL> archive log list;

数据库日志模式 存档模式

自动存档 启用

存档终点 USE_DB_RECOVERY_FILE_DEST

最早的联机日志序列 290

下一个存档日志序列 292

当前日志序列 292

SQL>

 

 

select * from v$block_change_tracking;

 

 

  1. 对辅助数据库的要求

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

 

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

[oracle@testdb dbs]$ more ~/.bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

    . ~/.bashrc

fi

 

# User specific environment and startup programs

 

export PATH=$PATH:$HOME/bin

 

 

# for oracle user

unset USERNAME

 

export GLOBAL_DB_UNIQUE_NAME=orcl

export ORACLE_HOSTNAME=192.168.59.135

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.ZHS16GBK"

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'

 

 

  1. 正式开始

    1. 前期准备

      1. 建表

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

[oracle@rhel6_lhr dbs]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on 星期二 7月 22 11:44:15 2014

 

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

 

 

连接到:

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

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

 

SQL> set time on;

11:44:15 SQL> SET LONG 99999999;

11:44:15 SQL> set timing on;

11:44:15 SQL> set serveroutput on size 1000000;

11:44:15 SQL> set sqlblanklines on;

11:44:15 SQL> set linesize 800;

11:44:15 SQL> set pagesize 50000;

11:44:15 SQL> set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> "

11:44:15 SYS@orclasm>

create table lhr.test_duplicate(id number,text varchar2(20));

insert into lhr.test_duplicate values(1,'a');

insert into lhr.test_duplicate values(2,'b');

commit;

 

表已创建。

 

已用时间: 00: 00: 01.22

11:44:17 SYS@orclasm>

已创建 1 行。

 

已用时间: 00: 00: 00.25

11:44:17 SYS@orclasm>

已创建 1 行。

 

已用时间: 00: 00: 00.01

11:44:18 SYS@orclasm>

提交完成。

 

已用时间: 00: 00: 00.00

11:44:18 SYS@orclasm> select * from lhr.test_duplicate;

 

    ID TEXT

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

     1 a

     2 b

 

已用时间: 00: 00: 00.07

11:44:20 SYS@orclasm>

  1. 数据库归档模式

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

C:\Users\Administrator>sqlplus sys/lhr@orclasm as sysdba

 

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 7月 22 11:34:45 2014

 

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

 

 

连接到:

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

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

 

SQL> archive log list;

数据库日志模式 存档模式

自动存档 启用

存档终点 USE_DB_RECOVERY_FILE_DEST

最早的联机日志序列 290

下一个存档日志序列 292

当前日志序列 292

SQL>

 

  1. 备份数据库

 

--做个全备份source database并将备份传送到destination database

[oracle@rhel6_lhr dbs]$ rman target /

 

恢复管理器: Release 11.2.0.3.0 - Production on 星期二 7月 22 11:47:07 2014

 

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

 

已连接到目标数据库: ORCLASM (DBID=3424884828)

 

RMAN> backup as compressed backupset format '/home/oracle/oracle_bk/orclasm/full_%n_%T_%t_%s.bak' database plus archivelog delete input;

 

 

启动 backup 于 22-7月 -14

当前日志已存档

使用通道 ORA_DISK_1

使用通道 ORA_DISK_2

通道 ORA_DISK_1: 正在启动压缩的归档日志备份集

通道 ORA_DISK_1: 正在指定备份集内的归档日志

输入归档日志线程=1 序列=294 RECID=290 STAMP=853588692

通道 ORA_DISK_1: 正在启动段 1 于 22-7月 -14

通道 ORA_DISK_1: 已完成段 1 于 22-7月 -14

段句柄=/home/oracle/oracle_bk/orclasm/full_ORCLASMx_20140722_853588692_16.bak 标记=TAG20140722T115812 注释=NONE

通道 ORA_DISK_1: 备份集已完成, 经过时间:00:00:01

通道 ORA_DISK_1: 正在删除归档日志

归档日志文件名=+FRA/orclasm/archivelog/2014_07_22/thread_1_seq_294.262.853588693 RECID=290 STAMP=853588692

完成 backup 于 22-7月 -14

 

启动 backup 于 22-7月 -14

使用通道 ORA_DISK_1

使用通道 ORA_DISK_2

通道 ORA_DISK_1: 正在启动压缩的全部数据文件备份集

通道 ORA_DISK_1: 正在指定备份集内的数据文件

输入数据文件: 文件号=00001 名称=+DATA/orclasm/datafile/system.256.850260145

输入数据文件: 文件号=00002 名称=+DATA/orclasm/datafile/sysaux.257.850260145

输入数据文件: 文件号=00006 名称=+DATA/orclasm/datafile/undotbs2.267.851204361

输入数据文件: 文件号=00007 名称=+DATA/orclasm/datafile/tbs_rc.268.852116523

通道 ORA_DISK_1: 正在启动段 1 于 22-7月 -14

通道 ORA_DISK_2: 正在启动压缩的全部数据文件备份集

通道 ORA_DISK_2: 正在指定备份集内的数据文件

输入数据文件: 文件号=00004 名称=+DATA/orclasm/datafile/users.259.850260147

输入数据文件: 文件号=00005 名称=+DATA/orclasm/datafile/example.265.850260295

输入数据文件: 文件号=00003 名称=+DATA/orclasm/datafile/undotbs1.258.851526539

输入数据文件: 文件号=00008 名称=+DATA/orclasm/datafile/ts_lhr.269.852632495

通道 ORA_DISK_2: 正在启动段 1 于 22-7月 -14

通道 ORA_DISK_1: 已完成段 1 于 22-7月 -14

段句柄=/home/oracle/oracle_bk/orclasm/full_ORCLASMx_20140722_853588694_17.bak 标记=TAG20140722T115814 注释=NONE

通道 ORA_DISK_1: 备份集已完成, 经过时间:00:02:06

通道 ORA_DISK_2: 已完成段 1 于 22-7月 -14

段句柄=/home/oracle/oracle_bk/orclasm/full_ORCLASMx_20140722_853588694_18.bak 标记=TAG20140722T115814 注释=NONE

通道 ORA_DISK_2: 备份集已完成, 经过时间:00:02:06

完成 backup 于 22-7月 -14

 

启动 backup 于 22-7月 -14

当前日志已存档

使用通道 ORA_DISK_1

使用通道 ORA_DISK_2

通道 ORA_DISK_1: 正在启动压缩的归档日志备份集

通道 ORA_DISK_1: 正在指定备份集内的归档日志

输入归档日志线程=1 序列=295 RECID=291 STAMP=853588821

通道 ORA_DISK_1: 正在启动段 1 于 22-7月 -14

通道 ORA_DISK_1: 已完成段 1 于 22-7月 -14

段句柄=/home/oracle/oracle_bk/orclasm/full_ORCLASMx_20140722_853588823_19.bak 标记=TAG20140722T120022 注释=NONE

通道 ORA_DISK_1: 备份集已完成, 经过时间:00:00:01

通道 ORA_DISK_1: 正在删除归档日志

归档日志文件名=+FRA/orclasm/archivelog/2014_07_22/thread_1_seq_295.262.853588821 RECID=291 STAMP=853588821

完成 backup 于 22-7月 -14

 

启动 Control File Autobackup 于 22-7月 -14

段 handle=/home/oracle/oracle_bk/orclasm/control_c-3424884828-20140722-01.bak comment=NONE

完成 Control File Autobackup 于 22-7月 -14

 

RMAN> list backup;

 

 

备份集列表

===================

 

 

BS 关键字 大小 设备类型占用时间 完成时间

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

15 955.50K DISK 00:00:00 22-7月 -14

BP 关键字: 15 状态: AVAILABLE 已压缩: YES 标记: TAG20140722T115812

段名:/home/oracle/oracle_bk/orclasm/full_ORCLASMx_20140722_853588692_16.bak

 

备份集 15 中的已存档日志列表

线程序列 低 SCN 时间下限 下一个 SCN 下一次

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

1 294 7503944 22-7月 -14 7504825 22-7月 -14

 

BS 关键字 类型 LV 大小 设备类型 经过时间 完成时间

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

16 Full 128.61M DISK 00:02:03 22-7月 -14

BP 关键字: 16 状态: AVAILABLE 已压缩: YES 标记: TAG20140722T115814

段名:/home/oracle/oracle_bk/orclasm/full_ORCLASMx_20140722_853588694_18.bak

备份集 16 中的数据文件列表

文件 LV 类型 Ckp SCN Ckp 时间 名称

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

3 Full 7504837 22-7月 -14 +DATA/orclasm/datafile/undotbs1.258.851526539

4 Full 7504837 22-7月 -14 +DATA/orclasm/datafile/users.259.850260147

5 Full 7504837 22-7月 -14 +DATA/orclasm/datafile/example.265.850260295

8 Full 7504837 22-7月 -14 +DATA/orclasm/datafile/ts_lhr.269.852632495

 

BS 关键字 类型 LV 大小 设备类型 经过时间 完成时间

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

17 Full 328.95M DISK 00:02:04 22-7月 -14

BP 关键字: 17 状态: AVAILABLE 已压缩: YES 标记: TAG20140722T115814

段名:/home/oracle/oracle_bk/orclasm/full_ORCLASMx_20140722_853588694_17.bak

备份集 17 中的数据文件列表

文件 LV 类型 Ckp SCN Ckp 时间 名称

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

1 Full 7504835 22-7月 -14 +DATA/orclasm/datafile/system.256.850260145

2 Full 7504835 22-7月 -14 +DATA/orclasm/datafile/sysaux.257.850260145

6 Full 7504835 22-7月 -14 +DATA/orclasm/datafile/undotbs2.267.851204361

7 Full 7504835 22-7月 -14 +DATA/orclasm/datafile/tbs_rc.268.852116523

 

BS 关键字 大小 设备类型占用时间 完成时间

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

18 847.50K DISK 00:00:00 22-7月 -14

BP 关键字: 18 状态: AVAILABLE 已压缩: YES 标记: TAG20140722T120022

段名:/home/oracle/oracle_bk/orclasm/full_ORCLASMx_20140722_853588823_19.bak

 

备份集 18 中的已存档日志列表

线程序列 低 SCN 时间下限 下一个 SCN 下一次

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

1 295 7504825 22-7月 -14 7505140 22-7月 -14

 

BS 关键字 类型 LV 大小 设备类型 经过时间 完成时间

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

19 Full 9.55M DISK 00:00:04 22-7月 -14

BP 关键字: 19 状态: AVAILABLE 已压缩: NO 标记: TAG20140722T120024

段名:/home/oracle/oracle_bk/orclasm/control_c-3424884828-20140722-01.bak

包括的控制文件: Ckp SCN: 7505224 Ckp 时间: 22-7月 -14

 

RMAN>

 

查看备份:

 

 

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

[root@rhel6_lhr orclasm]# scp -r /home/oracle/oracle_bk/orclasm/* oracle@192.168.59.135:/home/oracle/ora_bk

oracle@192.168.59.135's password:

control_c-3424884828-20140722-01.bak 100% 9792KB 9.6MB/s 00:00

full_ORCLASMx_20140722_853588692_16.bak 100% 956KB 956.0KB/s 00:00

full_ORCLASMx_20140722_853588694_17.bak 100% 329MB 20.6MB/s 00:16

full_ORCLASMx_20140722_853588694_18.bak 100% 129MB 32.2MB/s 00:04

full_ORCLASMx_20140722_853588823_19.bak 100% 848KB 848.0KB/s 00:00

[root@rhel6_lhr orclasm]#

 

在destination 库查看,确保已经传递到:

  1. 创建pfile 参数文件

 

这个步骤也可以在destination host直接创建,指定一个参数db_name 就可以了。。。。不过这样存在一些其它的问题,不推荐。。。。

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

 

C:\Users\Administrator>sqlplus sys/lhr@orclasm as sysdba

 

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 7月 22 12:11:54 2014

 

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

 

 

连接到:

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

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

 

SQL> create pfile='/tmp/initocl.ora' from spfile;

 

文件已创建。

 

SQL>

 

 

这里用root用户传送权限不足,需要使用Oracle用户:

[root@rhel6_lhr orclasm]# scp /tmp/initocl.ora oracle@192.168.59.135:$ORACLE_HOME/dbs

oracle@192.168.59.135's password:

scp: /dbs: Permission denied

[root@rhel6_lhr orclasm]# su - oracle

[oracle@rhel6_lhr ~]$ scp /tmp/initocl.ora oracle@192.168.59.135:$ORACLE_HOME/dbs

oracle@192.168.59.135's password:

initocl.ora 100% 1161 1.1KB/s 00:00

[oracle@rhel6_lhr ~]$

 

确保传递到目的地:

[oracle@testdb dbs]$ cd $ORACLE_HOME/dbs

[oracle@testdb dbs]$ ll

total 4

-rw-r--r--. 1 oracle oinstall 1161 Jul 22 12:16 initocl.ora

[oracle@testdb dbs]$

 

至此与source database就没有任何关系了。。。。。。。。。

 

 

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

这一步至关重要,因为source database 为ASM存储的,而auxiliary instance为filesystem的,所以相关路径应该设置合理。

 

这里文件名写错了,修改一下,先看看传递过来的文件内容:

[oracle@testdb dbs]$ mv initocl.ora initorcl.ora

[oracle@testdb dbs]$ more initorcl.ora

orclasm.__db_cache_size=96468992

orclasm.__java_pool_size=4194304

orclasm.__large_pool_size=4194304

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

orclasm.__pga_aggregate_target=209715200

orclasm.__sga_target=381681664

orclasm.__shared_io_pool_size=0

orclasm.__shared_pool_size=260046848

orclasm.__streams_pool_size=4194304

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

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='+DATA/orclasm/controlfile/current.260.850260253','+FRA/orclasm/controlfile/current.256.850260253'

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_domain='lhr.com'

*.db_name='orclasm'

*.db_recovery_file_dest='+FRA'

*.db_recovery_file_dest_size=8589934592

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

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

*.job_queue_processes=1000

*.local_listener='LISTENER_ORCLASM'

*.log_archive_format='%t_%s_%r.dbf'

*.memory_target=588251136

*.O7_DICTIONARY_ACCESSIBILITY=TRUE

*.open_cursors=300

*.optimizer_capture_sql_plan_baselines=TRUE

*.processes=500

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=500

*.undo_retention=60

*.undo_tablespace='UNDOTBS1'

[oracle@testdb dbs]$

 

应该修改的几个参数我都用红色标注出来了,修改后的参数为:

[oracle@testdb dbs]$ more initorcl.ora

orcl.__db_cache_size=96468992

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

orcl.__sga_target=381681664

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=260046848

orcl.__streams_pool_size=4194304

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

*.audit_trail='db'

*.compatible='11.2.0.0.0'

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

*.db_block_size=8192

*.db_create_file_dest='/u01/app/oracle/oradata/'

*.db_domain='lhr.com'

*.db_name='orcl'

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

*.db_recovery_file_dest_size=8589934592

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

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

*.job_queue_processes=1000

*.log_archive_format='%t_%s_%r.dbf'

*.memory_target=588251136

*.O7_DICTIONARY_ACCESSIBILITY=TRUE

*.open_cursors=300

*.optimizer_capture_sql_plan_baselines=TRUE

*.processes=500

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=500

*.undo_retention=60

*.undo_tablespace='UNDOTBS1'

[oracle@testdb dbs]$

 

创建需要的路径:

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

[oracle@testdb dbs]$ mkdir -p /u01/app/oracle/flash_recovery_area

 

仔细检查,不能马虎的。。。。。。。。。。。。。。。。

 

  1. 创建密码文件

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

[oracle@testdb dbs]$ ll

total 8

-rw-r--r--. 1 oracle oinstall 1153 Jul 22 12:29 initocl.ora

-rw-r-----. 1 oracle oinstall 1536 Jul 22 12:31 orapworcl

[oracle@testdb dbs]$

 

  1. 实施数据库的复制

--启动auxiliary instancenomount状态

 

  1. 启动Auxiliary 到nomout 状态

[oracle@testdb dbs]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 22 12:42:59 2014

 

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

 

Connected to an idle instance.

SQL> create spfile from pfile;

 

File created.

 

SQL> startup nomount

ORACLE instance started.

 

Total System Global Area 588746752 bytes

Fixed Size         2230592 bytes

Variable Size         482346688 bytes

Database Buffers     96468992 bytes

Redo Buffers         7700480 bytes

SQL>

 

 

告警日志:

[oracle@testdb trace]$ more alert_orcl.log

Tue Jul 22 12:43:01 2014

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Picked latch-free SCN scheme 3

Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST

Autotune of undo retention is turned on.

IMODE=BR

ILAT =84

LICENSE_MAX_USERS = 0

SYS auditing is disabled

Starting up:

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

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

ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1

System name:    Linux

Node name:    testdb

Release:    2.6.32-431.el6.x86_64

Version:    #1 SMP Sun Nov 10 22:19:54 EST 2013

Machine:    x86_64

VM name:    VMWare Version: 6

Using parameter settings in server-side pfile /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora

System parameters with non-default values:

processes = 500

sessions = 772

memory_target = 564M

control_files = "/u01/app/oracle/oradata/control01.ctl"

control_files = "/u01/app/oracle/oradata/control02.ctl"

db_block_size = 8192

compatible = "11.2.0.0.0"

log_archive_format = "%t_%s_%r.dbf"

db_create_file_dest = "/u01/app/oracle/oradata/"

db_recovery_file_dest = "/u01/app/oracle/flash_recovery_area"

db_recovery_file_dest_size= 8G

undo_tablespace = "UNDOTBS1"

undo_retention = 60

O7_DICTIONARY_ACCESSIBILITY= TRUE

remote_login_passwordfile= "EXCLUSIVE"

db_domain = "lhr.com"

dispatchers = "(PROTOCOL=TCP) (SERVICE=orclXDB)"

job_queue_processes = 1000

audit_file_dest = "/u01/app/oracle/admin/orcl/adump"

audit_trail = "DB"

db_name = "orcl"

open_cursors = 300

optimizer_capture_sql_plan_baselines= TRUE

diagnostic_dest = "/u01/app/oracle"

Tue Jul 22 12:43:02 2014

PMON started with pid=2, OS id=7129

Tue Jul 22 12:43:02 2014

PSP0 started with pid=3, OS id=7131

Tue Jul 22 12:43:03 2014

VKTM started with pid=4, OS id=7133 at elevated priority

VKTM running at (1)millisec precision with DBRM quantum (100)ms

Tue Jul 22 12:43:03 2014

GEN0 started with pid=5, OS id=7137

Tue Jul 22 12:43:03 2014

DIAG started with pid=6, OS id=7139

Tue Jul 22 12:43:03 2014

DBRM started with pid=7, OS id=7141

Tue Jul 22 12:43:04 2014

DIA0 started with pid=8, OS id=7143

Tue Jul 22 12:43:04 2014

MMAN started with pid=9, OS id=7145

Tue Jul 22 12:43:04 2014

DBW0 started with pid=10, OS id=7147

Tue Jul 22 12:43:04 2014

LGWR started with pid=11, OS id=7149

Tue Jul 22 12:43:04 2014

CKPT started with pid=12, OS id=7151

Tue Jul 22 12:43:04 2014

SMON started with pid=13, OS id=7153

Tue Jul 22 12:43:04 2014

RECO started with pid=14, OS id=7155

Tue Jul 22 12:43:04 2014

MMON started with pid=15, OS id=7157

Tue Jul 22 12:43:04 2014

MMNL started with pid=16, OS id=7159

starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

starting up 1 shared server(s) ...

ORACLE_BASE from environment = /u01/app/oracle

[oracle@testdb trace]$

 

 

 

 

 

 

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

先准备run块,在source database上:

select 'set newname for datafile '||a.FILE#||' to "'||a.NAME||'";' from v$datafile a

union all

select 'set newname for tempfile '||a.FILE#||' to "'||a.NAME||'";' from v$tempfile a;

 

修改一下文件名,加入logfile,注意controlfile已经在pfile中指定了:

RUN{

set newname for datafile 1 to "/u01/app/oracle/oradata/system.dbf";

set newname for datafile 2 to "/u01/app/oracle/oradata/sysaux.dbf";

set newname for datafile 3 to "/u01/app/oracle/oradata/undotbs1.dbf";

set newname for datafile 4 to "/u01/app/oracle/oradata/users.dbf";

set newname for datafile 5 to "/u01/app/oracle/oradata/example.dbf";

set newname for datafile 6 to "/u01/app/oracle/oradata/undotbs2.dbf";

set newname for datafile 7 to "/u01/app/oracle/oradata/tbs_rc.dbf";

set newname for datafile 8 to "/u01/app/oracle/oradata/ts_lhr.dbf";

set newname for tempfile 1 to '/u01/app/oracle/oradata/TEMP01.DBF';

duplicate target database to orcl backup location '/home/oracle/ora_bk' nofilenamecheck

LOGFILE

'/u01/app/oracle/oradata/redo01.log' SIZE 20M,

'/u01/app/oracle/oradata/redo02.log' SIZE 20M,

'/u01/app/oracle/oradata/redo03.log' SIZE 20M;

}

 

在这里也可以多分配几个通道,加快恢复速度:

RUN{

ALLOCATE AUXILIARY CHANNEL aux1 DEVICE TYPE DISK;

ALLOCATE AUXILIARY CHANNEL aux2 DEVICE TYPE DISK;

set newname for datafile 1 to "/u01/app/oracle/oradata/system01.dbf";

set newname for datafile 2 to "/u01/app/oracle/oradata/sysaux01.dbf";

set newname for datafile 3 to "/u01/app/oracle/oradata/undotbs1.dbf";

set newname for datafile 4 to "/u01/app/oracle/oradata/users01.dbf";

set newname for datafile 5 to "/u01/app/oracle/oradata/example.dbf";

set newname for datafile 6 to "/u01/app/oracle/oradata/undotbs2.dbf";

set newname for datafile 7 to "/u01/app/oracle/oradata/tbs_rc.dbf";

set newname for datafile 8 to "/u01/app/oracle/oradata/ts_lhr.dbf";

set newname for tempfile 1 to '/u01/app/oracle/oradata/temp01.DBF';

duplicate target database to orcl backup location '/home/oracle/ora_bk' nofilenamecheck

LOGFILE

'/u01/app/oracle/oradata/redo01.log' SIZE 20M,

'/u01/app/oracle/oradata/redo02.log' SIZE 20M,

'/u01/app/oracle/oradata/redo03.log' SIZE 20M;

}

恢复的时候就是2个通道恢复:

 

 

开始:

[oracle@testdb trace]$ rman auxiliary /

 

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jul 22 12:45:25 2014

 

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

 

connected to auxiliary database: ORCL (not mounted)

 

RMAN>

 

RUN{

set newname for datafile 1 to "/u01/app/oracle/oradata/system.dbf";

set newname for datafile 2 to "/u01/app/oracle/oradata/sysaux.dbf";

set newname for datafile 3 to "/u01/app/oracle/oradata/undotbs1.dbf";

set newname for datafile 4 to "/u01/app/oracle/oradata/users.dbf";

set newname for datafile 5 to "/u01/app/oracle/oradata/example.dbf";

set newname for datafile 6 to "/u01/app/oracle/oradata/undotbs2.dbf";

set newname for datafile 7 to "/u01/app/oracle/oradata/tbs_rc.dbf";

set newname for datafile 8 to "/u01/app/oracle/oradata/ts_lhr.dbf";

set newname for tempfile 1 to '/u01/app/oracle/oradata/TEMP01.DBF';

duplicate target database to orcl backup location '/home/oracle/ora_bk' nofilenamecheck

LOGFILE

'/u01/app/oracle/oradata/redo01.log' SIZE 20M,

'/u01/app/oracle/oradata/redo02.log' SIZE 20M,

'/u01/app/oracle/oradata/redo03.log' SIZE 20M;

}

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

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 Duplicate Db at 22-JUL-14

 

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 588746752 bytes

 

Fixed Size 2230592 bytes

Variable Size 486540992 bytes

Database Buffers 92274688 bytes

Redo Buffers 7700480 bytes

 

contents of Memory Script:

{

sql clone "alter system set db_name =

''ORCLASM'' 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 '/home/oracle/ora_bk/control_c-3424884828-20140722-01.bak';

alter clone database mount;

}

executing Memory Script

 

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

 

Fixed Size 2230592 bytes

Variable Size 486540992 bytes

Database Buffers 92274688 bytes

Redo Buffers 7700480 bytes

 

Starting restore at 22-JUL-14

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=395 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/control01.ctl

output file name=/u01/app/oracle/oradata/control02.ctl

Finished restore at 22-JUL-14

 

database mounted

released channel: ORA_AUX_DISK_1

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=395 device type=DISK

allocated channel: ORA_AUX_DISK_2

channel ORA_AUX_DISK_2: SID=396 device type=DISK

 

contents of Memory Script:

{

set until scn 7505140;

sql clone 'alter database flashback off';

set newname for datafile 1 to

"/u01/app/oracle/oradata/system.dbf";

set newname for datafile 2 to

"/u01/app/oracle/oradata/sysaux.dbf";

set newname for datafile 3 to

"/u01/app/oracle/oradata/undotbs1.dbf";

set newname for datafile 4 to

"/u01/app/oracle/oradata/users.dbf";

set newname for datafile 5 to

"/u01/app/oracle/oradata/example.dbf";

set newname for datafile 6 to

"/u01/app/oracle/oradata/undotbs2.dbf";

set newname for datafile 7 to

"/u01/app/oracle/oradata/tbs_rc.dbf";

set newname for datafile 8 to

"/u01/app/oracle/oradata/ts_lhr.dbf";

restore

clone database

;

}

executing Memory Script

 

executing command: SET until clause

 

sql statement: alter database flashback off

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

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 22-JUL-14

using channel ORA_AUX_DISK_1

using channel ORA_AUX_DISK_2

 

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/system.dbf

channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/sysaux.dbf

channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/undotbs2.dbf

channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/tbs_rc.dbf

channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/ora_bk/full_ORCLASMx_20140722_853588694_17.bak

channel ORA_AUX_DISK_2: starting datafile backup set restore

channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_2: restoring datafile 00003 to /u01/app/oracle/oradata/undotbs1.dbf

channel ORA_AUX_DISK_2: restoring datafile 00004 to /u01/app/oracle/oradata/users.dbf

channel ORA_AUX_DISK_2: restoring datafile 00005 to /u01/app/oracle/oradata/example.dbf

channel ORA_AUX_DISK_2: restoring datafile 00008 to /u01/app/oracle/oradata/ts_lhr.dbf

channel ORA_AUX_DISK_2: reading from backup piece /home/oracle/ora_bk/full_ORCLASMx_20140722_853588694_18.bak

channel ORA_AUX_DISK_2: piece handle=/home/oracle/ora_bk/full_ORCLASMx_20140722_853588694_18.bak tag=TAG20140722T115814

channel ORA_AUX_DISK_2: restored backup piece 1

channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:01:25

channel ORA_AUX_DISK_1: piece handle=/home/oracle/ora_bk/full_ORCLASMx_20140722_853588694_17.bak tag=TAG20140722T115814

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:15

Finished restore at 22-JUL-14

 

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

 

datafile 1 switched to datafile copy

input datafile copy RECID=9 STAMP=853591730 file name=/u01/app/oracle/oradata/system.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=10 STAMP=853591730 file name=/u01/app/oracle/oradata/sysaux.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=11 STAMP=853591730 file name=/u01/app/oracle/oradata/undotbs1.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=12 STAMP=853591730 file name=/u01/app/oracle/oradata/users.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=13 STAMP=853591730 file name=/u01/app/oracle/oradata/example.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=14 STAMP=853591730 file name=/u01/app/oracle/oradata/undotbs2.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=15 STAMP=853591730 file name=/u01/app/oracle/oradata/tbs_rc.dbf

datafile 8 switched to datafile copy

input datafile copy RECID=16 STAMP=853591730 file name=/u01/app/oracle/oradata/ts_lhr.dbf

 

contents of Memory Script:

{

set until scn 7505140;

recover

clone database

delete archivelog

;

}

executing Memory Script

 

executing command: SET until clause

 

Starting recover at 22-JUL-14

using channel ORA_AUX_DISK_1

using channel ORA_AUX_DISK_2

 

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

channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/ora_bk/full_ORCLASMx_20140722_853588823_19.bak

channel ORA_AUX_DISK_1: piece handle=/home/oracle/ora_bk/full_ORCLASMx_20140722_853588823_19.bak tag=TAG20140722T120022

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_22/o1_mf_1_295_9wvv9nlv_.arc thread=1 sequence=295

channel clone_default: deleting archived log(s)

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_22/o1_mf_1_295_9wvv9nlv_.arc RECID=1 STAMP=853591732

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

Finished recover at 22-JUL-14

Oracle instance started

 

Total System Global Area 588746752 bytes

 

Fixed Size 2230592 bytes

Variable Size 486540992 bytes

Database Buffers 92274688 bytes

Redo Buffers 7700480 bytes

 

contents of Memory Script:

{

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

 

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 588746752 bytes

 

Fixed Size 2230592 bytes

Variable Size 486540992 bytes

Database Buffers 92274688 bytes

Redo Buffers 7700480 bytes

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

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/redo01.log' SIZE 20 M ,

GROUP 2 '/u01/app/oracle/oradata/redo02.log' SIZE 20 M ,

GROUP 3 '/u01/app/oracle/oradata/redo03.log' SIZE 20 M

DATAFILE

'/u01/app/oracle/oradata/system.dbf'

CHARACTER SET ZHS16GBK

 

 

contents of Memory Script:

{

set newname for tempfile 1 to

"/u01/app/oracle/oradata/TEMP01.DBF";

switch clone tempfile all;

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

"/u01/app/oracle/oradata/undotbs1.dbf",

"/u01/app/oracle/oradata/users.dbf",

"/u01/app/oracle/oradata/example.dbf",

"/u01/app/oracle/oradata/undotbs2.dbf",

"/u01/app/oracle/oradata/tbs_rc.dbf",

"/u01/app/oracle/oradata/ts_lhr.dbf";

switch clone datafile all;

}

executing Memory Script

 

executing command: SET NEWNAME

 

renamed tempfile 1 to /u01/app/oracle/oradata/TEMP01.DBF in control file

 

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/sysaux.dbf RECID=1 STAMP=853591752

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/undotbs1.dbf RECID=2 STAMP=853591752

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/users.dbf RECID=3 STAMP=853591752

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/example.dbf RECID=4 STAMP=853591752

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/undotbs2.dbf RECID=5 STAMP=853591752

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/tbs_rc.dbf RECID=6 STAMP=853591752

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/ts_lhr.dbf RECID=7 STAMP=853591752

 

datafile 2 switched to datafile copy

input datafile copy RECID=1 STAMP=853591752 file name=/u01/app/oracle/oradata/sysaux.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=853591752 file name=/u01/app/oracle/oradata/undotbs1.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=3 STAMP=853591752 file name=/u01/app/oracle/oradata/users.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=4 STAMP=853591752 file name=/u01/app/oracle/oradata/example.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=5 STAMP=853591752 file name=/u01/app/oracle/oradata/undotbs2.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=6 STAMP=853591752 file name=/u01/app/oracle/oradata/tbs_rc.dbf

datafile 8 switched to datafile copy

input datafile copy RECID=7 STAMP=853591752 file name=/u01/app/oracle/oradata/ts_lhr.dbf

Reenabling controlfile options for auxiliary database

Executing: alter database add supplemental log data

Executing: alter database add supplemental log data(PRIMARY KEY, UNIQUE) columns

Executing: alter database enable block change tracking

 

contents of Memory Script:

{

Alter clone database open resetlogs;

}

executing Memory Script

 

database opened

Executing: alter database flashback on

Finished Duplicate Db at 22-JUL-14

 

RMAN>

 

复制完成。。。。。。。。。。。。。

 

这里把复制过程中的告警日志贴出来:

Tue Jul 22 12:46:06 2014

Shutting down instance (immediate)

Shutting down instance: further logons disabled

Stopping background process MMNL

Stopping background process MMON

License high water mark = 3

All dispatchers and shared servers shutdown

alter database close

ORA-1507 signalled during: alter database close...

alter database dismount

ORA-1507 signalled during: alter database dismount...

ARCH: Archival disabled due to shutdown: 1089

Shutting down archive processes

Archiving is disabled

Tue Jul 22 12:46:09 2014

Stopping background process VKTM

ARCH: Archival disabled due to shutdown: 1089

Shutting down archive processes

Archiving is disabled

Tue Jul 22 12:46:11 2014

Instance shutdown complete

Tue Jul 22 12:46:11 2014

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

_ksb_restart_policy_times={0,60,120,240}

Picked latch-free SCN scheme 3

Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST

Autotune of undo retention is turned on.

IMODE=BR

ILAT =84

LICENSE_MAX_USERS = 0

SYS auditing is disabled

Tue Jul 22 12:46:12 2014

WARNING: The background_dump_dest init.ora parameter has been deprecated.

WARNING: Please remove the background_dump_dest parameter from the init.ora file.

WARNING: The diagnostic_dest init.ora parameter now determines the location of the diagnostic data

WARNING: The new location for the background logs and traces is /u01/app/oracle/diag/rdbms/orcl/orcl/trace

Tue Jul 22 12:46:12 2014

WARNING: The user_dump_dest init.ora parameter has been deprecated.

WARNING: Please remove the user_dump_dest parameter from the init.ora file.

WARNING: The diagnostic_dest init.ora parameter now determines the location of the diagnostic data

WARNING: The new location for the user logs and traces is /u01/app/oracle/diag/rdbms/orcl/orcl/trace

Starting up:

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

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

ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1

System name:    Linux

Node name:    testdb

Release:    2.6.32-431.el6.x86_64

Version:    #1 SMP Sun Nov 10 22:19:54 EST 2013

Machine:    x86_64

VM name:    VMWare Version: 6

Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora

System parameters with non-default values:

processes = 500

sessions = 772

_ksb_restart_policy_times= "0"

_ksb_restart_policy_times= "60"

_ksb_restart_policy_times= "120"

_ksb_restart_policy_times= "240"

memory_target = 564M

control_files = "/u01/app/oracle/oradata/control01.ctl"

control_files = "/u01/app/oracle/oradata/control02.ctl"

db_block_size = 8192

compatible = "11.2.0.0.0"

log_archive_format = "%t_%s_%r.dbf"

log_buffer = 7364608

db_create_file_dest = "/u01/app/oracle/oradata/"

db_recovery_file_dest = "/u01/app/oracle/flash_recovery_area"

db_recovery_file_dest_size= 8G

undo_tablespace = "UNDOTBS1"

undo_retention = 60

_compression_compatibility= "11.2.0.0.0"

O7_DICTIONARY_ACCESSIBILITY= TRUE

remote_login_passwordfile= "EXCLUSIVE"

db_domain = "lhr.com"

dispatchers = "(PROTOCOL=TCP) (SERVICE=orclXDB)"

plsql_warnings = "DISABLE:ALL"

job_queue_processes = 1000

result_cache_max_size = 1472K

background_dump_dest = "/u01/app/oracle/diag/rdbms/orcl/orcl/trace"

user_dump_dest = "/u01/app/oracle/diag/rdbms/orcl/orcl/trace"

core_dump_dest = "/u01/app/oracle/diag/rdbms/orcl/orcl/cdump"

audit_file_dest = "/u01/app/oracle/admin/orcl/adump"

audit_trail = "DB"

db_name = "orcl"

open_cursors = 300

optimizer_mode = "ALL_ROWS"

_optimizer_mode_force = TRUE

_always_anti_join = "CHOOSE"

_optimizer_null_aware_antijoin= TRUE

_partition_view_enabled = TRUE

_b_tree_bitmap_plans = TRUE

_cpu_to_io = 0

_optimizer_extended_cursor_sharing= "UDO"

_optimizer_extended_cursor_sharing_rel= "SIMPLE"

_optimizer_adaptive_cursor_sharing= TRUE

_optimizer_cost_model = "CHOOSE"

_optimizer_undo_cost_change= "11.2.0.3"

_optimizer_system_stats_usage= TRUE

_new_sort_cost_estimate = TRUE

_complex_view_merging = TRUE

_unnest_subquery = TRUE

_eliminate_common_subexpr= TRUE

_pred_move_around = TRUE

_convert_set_to_join = FALSE

_px_ual_serial_input = TRUE

_px_minus_intersect = TRUE

_remove_aggr_subquery = TRUE

_optimizer_push_down_distinct= 0

_optimizer_cost_based_transformation= "LINEAR"

_optimizer_squ_bottomup = TRUE

_push_join_predicate = TRUE

_push_join_union_view = TRUE

_push_join_union_view2 = TRUE

_fast_full_scan_enabled = TRUE

_optimizer_skip_scan_enabled= TRUE

_optimizer_join_sel_sanity_check= TRUE

_optim_enhance_nnull_detection= TRUE

_parallel_broadcast_enabled= TRUE

_always_semi_join = "CHOOSE"

_ordered_nested_loop = TRUE

_optimizer_max_permutations= 2000

_nlj_batching_enabled = 1

query_rewrite_enabled = "TRUE"

_mmv_query_rewrite_enabled= TRUE

_local_communication_costing_enabled= TRUE

_left_nested_loops_random= TRUE

_improved_row_length_enabled= TRUE

_index_join_enabled = TRUE

_enable_type_dep_selectivity= TRUE

_improved_outerjoin_card = TRUE

_use_column_stats_for_function= TRUE

_subquery_pruning_enabled= TRUE

_subquery_pruning_mv_enabled= FALSE

_or_expand_nvl_predicate = TRUE

_table_scan_cost_plus_one= TRUE

_cost_equality_semi_join = TRUE

_new_initial_join_orders = TRUE

_optim_peek_user_binds = TRUE

_minimal_stats_aggregation= TRUE

_gs_anti_semi_join_allowed= TRUE

_optim_new_default_join_sel= TRUE

optimizer_dynamic_sampling= 2

_pre_rewrite_push_pred = TRUE

_optimizer_new_join_card_computation= TRUE

_union_rewrite_for_gs = "YES_GSET_MVS"

_generalized_pruning_enabled= TRUE

_optim_adjust_for_part_skews= TRUE

_optimizer_compute_index_stats= TRUE

_optimizer_push_pred_cost_based= TRUE

_optimizer_extend_jppd_view_types= TRUE

_optimizer_filter_pred_pullup= TRUE

_optimizer_connect_by_cost_based= TRUE

_optimizer_connect_by_combine_sw= TRUE

_optimizer_connect_by_elim_dups= TRUE

_connect_by_use_union_all= "TRUE"

_right_outer_hash_enable = TRUE

_replace_virtual_columns = TRUE

skip_unusable_indexes = TRUE

_optimizer_correct_sq_selectivity= TRUE

_optimizer_dim_subq_join_sel= TRUE

_query_rewrite_setopgrw_enable= TRUE

_optimizer_join_order_control= 3

_bloom_filter_enabled = TRUE

_bloom_folding_enabled = TRUE

_optimizer_join_elimination_enabled= TRUE

_gby_hash_aggregation_enabled= TRUE

_globalindex_pnum_filter_enabled= TRUE

_sql_model_unfold_forloops= "RUN_TIME"

_optimizer_cost_hjsmj_multimatch= TRUE

_optimizer_transitivity_retain= TRUE

_px_pwg_enabled = TRUE

_optimizer_cbqt_no_size_restriction= TRUE

_optimizer_enhanced_filter_push= TRUE

_optimizer_rownum_pred_based_fkr= TRUE

_optimizer_better_inlist_costing= "ALL"

_optimizer_or_expansion = "DEPTH"

_optimizer_outer_to_anti_enabled= TRUE

_optimizer_order_by_elimination_enabled= TRUE

_optimizer_star_tran_in_with_clause= TRUE

_optimizer_sortmerge_join_inequality= TRUE

_selfjoin_mv_duplicates = TRUE

_dimension_skip_null = TRUE

_optimizer_complex_pred_selectivity= TRUE

_bloom_pruning_enabled = TRUE

_first_k_rows_dynamic_proration= TRUE

_optimizer_distinct_elimination= TRUE

_optimizer_multi_level_push_pred= TRUE

_optimizer_group_by_placement= TRUE

_optimizer_distinct_placement= TRUE

_optimizer_coalesce_subqueries= TRUE

_optimizer_enable_density_improvements= TRUE

_optimizer_rownum_bind_default= 10

_optimizer_improve_selectivity= TRUE

optimizer_capture_sql_plan_baselines= TRUE

_optimizer_native_full_outer_join= "FORCE"

_optimizer_enable_extended_stats= TRUE

_pivot_implementation_method= "CHOOSE"

_optimizer_interleave_jppd= TRUE

_optimizer_fkr_index_cost_bias= 10

_optimizer_extended_stats_usage_control= 192

_optimizer_fast_pred_transitivity= TRUE

_optimizer_fast_access_pred_analysis= TRUE

_optimizer_unnest_disjunctive_subq= TRUE

_optimizer_unnest_corr_set_subq= TRUE

_optimizer_distinct_agg_transform= TRUE

_aggregation_optimization_settings= 0

_optimizer_eliminate_filtering_join= TRUE

_optimizer_join_factorization= TRUE

_optimizer_use_cbqt_star_transformation= TRUE

_optimizer_table_expansion= TRUE

_and_pruning_enabled = TRUE

_optimizer_use_feedback = TRUE

_optimizer_try_st_before_jppd= TRUE

_px_partition_scan_enabled= TRUE

_optimizer_false_filter_pred_pullup= TRUE

_optimizer_enable_table_lookup_by_nl= TRUE

_optimizer_outer_join_to_inner= TRUE

_optimizer_full_outer_join_to_outer= TRUE

diagnostic_dest = "/u01/app/oracle"

Deprecated system parameters with specified values:

background_dump_dest

user_dump_dest

End of deprecated system parameter listing

Tue Jul 22 12:46:12 2014

PMON started with pid=2, OS id=7268

Tue Jul 22 12:46:12 2014

PSP0 started with pid=3, OS id=7270

Tue Jul 22 12:46:13 2014

VKTM started with pid=4, OS id=7272 at elevated priority

VKTM running at (1)millisec precision with DBRM quantum (100)ms

Tue Jul 22 12:46:13 2014

GEN0 started with pid=5, OS id=7276

Tue Jul 22 12:46:13 2014

DIAG started with pid=6, OS id=7278

Tue Jul 22 12:46:13 2014

DBRM started with pid=7, OS id=7280

Tue Jul 22 12:46:13 2014

DIA0 started with pid=8, OS id=7282

Tue Jul 22 12:46:13 2014

MMAN started with pid=9, OS id=7284

Tue Jul 22 12:46:13 2014

DBW0 started with pid=10, OS id=7286

Tue Jul 22 12:46:13 2014

LGWR started with pid=11, OS id=7288

Tue Jul 22 12:46:13 2014

CKPT started with pid=12, OS id=7290

Tue Jul 22 12:46:13 2014

SMON started with pid=13, OS id=7292

Tue Jul 22 12:46:13 2014

RECO started with pid=14, OS id=7294

Tue Jul 22 12:46:13 2014

MMON started with pid=15, OS id=7296

Tue Jul 22 12:46:13 2014

MMNL started with pid=16, OS id=7298

starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

starting up 1 shared server(s) ...

ORACLE_BASE from environment = /u01/app/oracle

Tue Jul 22 12:46:14 2014

ALTER SYSTEM SET db_name='ORCLASM' COMMENT='Modified by RMAN duplicate' SCOPE=SPFILE;

ALTER SYSTEM SET db_unique_name='ORCL' COMMENT='Modified by RMAN duplicate' SCOPE=SPFILE;

Shutting down instance (immediate)

Shutting down instance: further logons disabled

Stopping background process MMNL

Stopping background process MMON

License high water mark = 2

Waiting for dispatcher 'D000' to shutdown

All dispatchers and shared servers shutdown

alter database close

ORA-1507 signalled during: alter database close...

alter database dismount

ORA-1507 signalled during: alter database dismount...

ARCH: Archival disabled due to shutdown: 1089

Shutting down archive processes

Archiving is disabled

ARCH: Archival disabled due to shutdown: 1089

Shutting down archive processes

Archiving is disabled

Stopping background process VKTM

Tue Jul 22 12:46:23 2014

Instance shutdown complete

Tue Jul 22 12:46:23 2014

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

_ksb_restart_policy_times={0,60,120,240}

Picked latch-free SCN scheme 3

Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST

Autotune of undo retention is turned on.

IMODE=BR

ILAT =84

LICENSE_MAX_USERS = 0

SYS auditing is disabled

Tue Jul 22 12:46:24 2014

WARNING: The background_dump_dest init.ora parameter has been deprecated.

WARNING: Please remove the background_dump_dest parameter from the init.ora file.

WARNING: The diagnostic_dest init.ora parameter now determines the location of the diagnostic data

WARNING: The new location for the background logs and traces is /u01/app/oracle/diag/rdbms/orcl/orcl/trace

Tue Jul 22 12:46:24 2014

WARNING: The user_dump_dest init.ora parameter has been deprecated.

WARNING: Please remove the user_dump_dest parameter from the init.ora file.

WARNING: The diagnostic_dest init.ora parameter now determines the location of the diagnostic data

WARNING: The new location for the user logs and traces is /u01/app/oracle/diag/rdbms/orcl/orcl/trace

Starting up:

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

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

ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1

System name:    Linux

Node name:    testdb

Release:    2.6.32-431.el6.x86_64

Version:    #1 SMP Sun Nov 10 22:19:54 EST 2013

Machine:    x86_64

VM name:    VMWare Version: 6

Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora

System parameters with non-default values:

processes = 500

sessions = 772

_ksb_restart_policy_times= "0"

_ksb_restart_policy_times= "60"

_ksb_restart_policy_times= "120"

_ksb_restart_policy_times= "240"

memory_target = 564M

control_files = "/u01/app/oracle/oradata/control01.ctl"

control_files = "/u01/app/oracle/oradata/control02.ctl"

db_block_size = 8192

compatible = "11.2.0.0.0"

log_archive_format = "%t_%s_%r.dbf"

log_buffer = 7364608

db_create_file_dest = "/u01/app/oracle/oradata/"

db_recovery_file_dest = "/u01/app/oracle/flash_recovery_area"

db_recovery_file_dest_size= 8G

undo_tablespace = "UNDOTBS1"

undo_retention = 60

_compression_compatibility= "11.2.0.0.0"

O7_DICTIONARY_ACCESSIBILITY= TRUE

remote_login_passwordfile= "EXCLUSIVE"

db_domain = "lhr.com"

dispatchers = "(PROTOCOL=TCP) (SERVICE=orclXDB)"

plsql_warnings = "DISABLE:ALL"

job_queue_processes = 1000

result_cache_max_size = 1472K

background_dump_dest = "/u01/app/oracle/diag/rdbms/orcl/orcl/trace"

user_dump_dest = "/u01/app/oracle/diag/rdbms/orcl/orcl/trace"

core_dump_dest = "/u01/app/oracle/diag/rdbms/orcl/orcl/cdump"

audit_file_dest = "/u01/app/oracle/admin/orcl/adump"

audit_trail = "DB"

db_name = "ORCLASM"

db_unique_name = "ORCL"

open_cursors = 300

optimizer_mode = "ALL_ROWS"

_optimizer_mode_force = TRUE

_always_anti_join = "CHOOSE"

_optimizer_null_aware_antijoin= TRUE

_partition_view_enabled = TRUE

_b_tree_bitmap_plans = TRUE

_cpu_to_io = 0

_optimizer_extended_cursor_sharing= "UDO"

_optimizer_extended_cursor_sharing_rel= "SIMPLE"

_optimizer_adaptive_cursor_sharing= TRUE

_optimizer_cost_model = "CHOOSE"

_optimizer_undo_cost_change= "11.2.0.3"

_optimizer_system_stats_usage= TRUE

_new_sort_cost_estimate = TRUE

_complex_view_merging = TRUE

_unnest_subquery = TRUE

_eliminate_common_subexpr= TRUE

_pred_move_around = TRUE

_convert_set_to_join = FALSE

_px_ual_serial_input = TRUE

_px_minus_intersect = TRUE

_remove_aggr_subquery = TRUE

_optimizer_push_down_distinct= 0

_optimizer_cost_based_transformation= "LINEAR"

_optimizer_squ_bottomup = TRUE

_push_join_predicate = TRUE

_push_join_union_view = TRUE

_push_join_union_view2 = TRUE

_fast_full_scan_enabled = TRUE

_optimizer_skip_scan_enabled= TRUE

_optimizer_join_sel_sanity_check= TRUE

_optim_enhance_nnull_detection= TRUE

_parallel_broadcast_enabled= TRUE

_always_semi_join = "CHOOSE"

_ordered_nested_loop = TRUE

_optimizer_max_permutations= 2000

_nlj_batching_enabled = 1

query_rewrite_enabled = "TRUE"

_mmv_query_rewrite_enabled= TRUE

_local_communication_costing_enabled= TRUE

_left_nested_loops_random= TRUE

_improved_row_length_enabled= TRUE

_index_join_enabled = TRUE

_enable_type_dep_selectivity= TRUE

_improved_outerjoin_card = TRUE

_use_column_stats_for_function= TRUE

_subquery_pruning_enabled= TRUE

_subquery_pruning_mv_enabled= FALSE

_or_expand_nvl_predicate = TRUE

_table_scan_cost_plus_one= TRUE

_cost_equality_semi_join = TRUE

_new_initial_join_orders = TRUE

_optim_peek_user_binds = TRUE

_minimal_stats_aggregation= TRUE

_gs_anti_semi_join_allowed= TRUE

_optim_new_default_join_sel= TRUE

optimizer_dynamic_sampling= 2

_pre_rewrite_push_pred = TRUE

_optimizer_new_join_card_computation= TRUE

_union_rewrite_for_gs = "YES_GSET_MVS"

_generalized_pruning_enabled= TRUE

_optim_adjust_for_part_skews= TRUE

_optimizer_compute_index_stats= TRUE

_optimizer_push_pred_cost_based= TRUE

_optimizer_extend_jppd_view_types= TRUE

_optimizer_filter_pred_pullup= TRUE

_optimizer_connect_by_cost_based= TRUE

_optimizer_connect_by_combine_sw= TRUE

_optimizer_connect_by_elim_dups= TRUE

_connect_by_use_union_all= "TRUE"

_right_outer_hash_enable = TRUE

_replace_virtual_columns = TRUE

skip_unusable_indexes = TRUE

_optimizer_correct_sq_selectivity= TRUE

_optimizer_dim_subq_join_sel= TRUE

_query_rewrite_setopgrw_enable= TRUE

_optimizer_join_order_control= 3

_bloom_filter_enabled = TRUE

_bloom_folding_enabled = TRUE

_optimizer_join_elimination_enabled= TRUE

_gby_hash_aggregation_enabled= TRUE

_globalindex_pnum_filter_enabled= TRUE

_sql_model_unfold_forloops= "RUN_TIME"

_optimizer_cost_hjsmj_multimatch= TRUE

_optimizer_transitivity_retain= TRUE

_px_pwg_enabled = TRUE

_optimizer_cbqt_no_size_restriction= TRUE

_optimizer_enhanced_filter_push= TRUE

_optimizer_rownum_pred_based_fkr= TRUE

_optimizer_better_inlist_costing= "ALL"

_optimizer_or_expansion = "DEPTH"

_optimizer_outer_to_anti_enabled= TRUE

_optimizer_order_by_elimination_enabled= TRUE

_optimizer_star_tran_in_with_clause= TRUE

_optimizer_sortmerge_join_inequality= TRUE

_selfjoin_mv_duplicates = TRUE

_dimension_skip_null = TRUE

_optimizer_complex_pred_selectivity= TRUE

_bloom_pruning_enabled = TRUE

_first_k_rows_dynamic_proration= TRUE

_optimizer_distinct_elimination= TRUE

_optimizer_multi_level_push_pred= TRUE

_optimizer_group_by_placement= TRUE

_optimizer_distinct_placement= TRUE

_optimizer_coalesce_subqueries= TRUE

_optimizer_enable_density_improvements= TRUE

_optimizer_rownum_bind_default= 10

_optimizer_improve_selectivity= TRUE

optimizer_capture_sql_plan_baselines= TRUE

_optimizer_native_full_outer_join= "FORCE"

_optimizer_enable_extended_stats= TRUE

_pivot_implementation_method= "CHOOSE"

_optimizer_interleave_jppd= TRUE

_optimizer_fkr_index_cost_bias= 10

_optimizer_extended_stats_usage_control= 192

_optimizer_fast_pred_transitivity= TRUE

_optimizer_fast_access_pred_analysis= TRUE

_optimizer_unnest_disjunctive_subq= TRUE

_optimizer_unnest_corr_set_subq= TRUE

_optimizer_distinct_agg_transform= TRUE

_aggregation_optimization_settings= 0

_optimizer_eliminate_filtering_join= TRUE

_optimizer_join_factorization= TRUE

_optimizer_use_cbqt_star_transformation= TRUE

_optimizer_table_expansion= TRUE

_and_pruning_enabled = TRUE

_optimizer_use_feedback = TRUE

_optimizer_try_st_before_jppd= TRUE

_px_partition_scan_enabled= TRUE

_optimizer_false_filter_pred_pullup= TRUE

_optimizer_enable_table_lookup_by_nl= TRUE

_optimizer_outer_join_to_inner= TRUE

_optimizer_full_outer_join_to_outer= TRUE

diagnostic_dest = "/u01/app/oracle"

Deprecated system parameters with specified values:

background_dump_dest

user_dump_dest

End of deprecated system parameter listing

Tue Jul 22 12:46:24 2014

PMON started with pid=2, OS id=7398

Tue Jul 22 12:46:24 2014

PSP0 started with pid=3, OS id=7400

Tue Jul 22 12:46:25 2014

VKTM started with pid=4, OS id=7402 at elevated priority

VKTM running at (1)millisec precision with DBRM quantum (100)ms

Tue Jul 22 12:46:25 2014

GEN0 started with pid=5, OS id=7406

Tue Jul 22 12:46:25 2014

DIAG started with pid=6, OS id=7408

Tue Jul 22 12:46:25 2014

DBRM started with pid=7, OS id=7410

Tue Jul 22 12:46:25 2014

DIA0 started with pid=8, OS id=7412

Tue Jul 22 12:46:25 2014

MMAN started with pid=9, OS id=7414

Tue Jul 22 12:46:25 2014

DBW0 started with pid=10, OS id=7416

Tue Jul 22 12:46:25 2014

LGWR started with pid=11, OS id=7418

Tue Jul 22 12:46:25 2014

CKPT started with pid=12, OS id=7420

Tue Jul 22 12:46:25 2014

SMON started with pid=13, OS id=7422

Tue Jul 22 12:46:25 2014

RECO started with pid=14, OS id=7424

Tue Jul 22 12:46:25 2014

MMON started with pid=15, OS id=7426

Tue Jul 22 12:46:25 2014

starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

MMNL started with pid=16, OS id=7428

starting up 1 shared server(s) ...

ORACLE_BASE from environment = /u01/app/oracle

Tue Jul 22 12:46:27 2014

alter database mount

Set as converted control file due to db_unique_name mismatch

Changing di2dbun from orclasm to ORCL

Successful mount of redo thread 1, with mount id 3428226147

Allocated 3981120 bytes in shared pool for flashback generation buffer

Starting background process RVWR

Tue Jul 22 12:46:32 2014

RVWR started with pid=22, OS id=7460

Database mounted in Exclusive Mode

Lost write protection disabled

Create Relation IPS_PACKAGE_UNPACK_HISTORY

Completed: alter database mount

alter database flashback off

Stopping background process RVWR

Tue Jul 22 12:46:33 2014

Checker run found 9 new persistent data failures

Deleted Oracle managed file /u01/app/oracle/flash_recovery_area/ORCL/flashback/o1_mf_9wvv57sl_.flb

Flashback Database Disabled

Completed: alter database flashback off

Tue Jul 22 12:46:38 2014

Full restore complete of datafile 8 to datafile copy /u01/app/oracle/oradata/ts_lhr.dbf. Elapsed time: 0:00:01

checkpoint is 7504837

last deallocation scn is 4371981

Tue Jul 22 12:46:39 2014

Full restore complete of datafile 7 to datafile copy /u01/app/oracle/oradata/tbs_rc.dbf. Elapsed time: 0:00:03

checkpoint is 7504835

last deallocation scn is 995550

Full restore complete of datafile 6 to datafile copy /u01/app/oracle/oradata/undotbs2.dbf. Elapsed time: 0:00:04

checkpoint is 7504835

last deallocation scn is 2326625

Undo Optimization current scn is 7496957

Tue Jul 22 12:46:58 2014

Full restore complete of datafile 3 to datafile copy /u01/app/oracle/oradata/undotbs1.dbf. Elapsed time: 0:00:18

checkpoint is 7504837

last deallocation scn is 7422172

Undo Optimization current scn is 7496957

Tue Jul 22 12:47:13 2014

Full restore complete of datafile 5 to datafile copy /u01/app/oracle/oradata/example.dbf. Elapsed time: 0:00:35

checkpoint is 7504837

last deallocation scn is 4659253

Tue Jul 22 12:47:55 2014

Full restore complete of datafile 4 to datafile copy /u01/app/oracle/oradata/users.dbf. Elapsed time: 0:01:17

checkpoint is 7504837

last deallocation scn is 7108050

Tue Jul 22 12:48:39 2014

Full restore complete of datafile 2 to datafile copy /u01/app/oracle/oradata/sysaux.dbf. Elapsed time: 0:02:02

checkpoint is 7504835

last deallocation scn is 7252344

Full restore complete of datafile 1 to datafile copy /u01/app/oracle/oradata/system.dbf. Elapsed time: 0:02:05

checkpoint is 7504835

last deallocation scn is 4624043

Undo Optimization current scn is 7496957

Tue Jul 22 12:48:50 2014

Switch of datafile 1 complete to datafile copy

checkpoint is 7504835

Switch of datafile 2 complete to datafile copy

checkpoint is 7504835

Switch of datafile 3 complete to datafile copy

checkpoint is 7504837

Switch of datafile 4 complete to datafile copy

checkpoint is 7504837

Switch of datafile 5 complete to datafile copy

checkpoint is 7504837

Switch of datafile 6 complete to datafile copy

checkpoint is 7504835

Switch of datafile 7 complete to datafile copy

checkpoint is 7504835

Switch of datafile 8 complete to datafile copy

checkpoint is 7504837

alter database recover datafile list clear

Completed: alter database recover datafile list clear

alter database recover datafile list

1 , 2 , 3 , 4 , 5 , 6 , 7 , 8

Completed: alter database recover datafile list

1 , 2 , 3 , 4 , 5 , 6 , 7 , 8

alter database recover if needed

start until change 7505140 using backup controlfile

Media Recovery Start

started logmerger process

Parallel Media Recovery started with 2 slaves

ORA-279 signalled during: alter database recover if needed

start until change 7505140 using backup controlfile

...

alter database recover logfile '/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_22/o1_mf_1_295_9wvv9nlv_.arc'

Media Recovery Log /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_22/o1_mf_1_295_9wvv9nlv_.arc

Tue Jul 22 12:48:54 2014

Incomplete Recovery applied until change 7505140 time 07/22/2014 12:00:20

Media Recovery Complete (orcl)

Completed: alter database recover logfile '/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_07_22/o1_mf_1_295_9wvv9nlv_.arc'

Tue Jul 22 12:48:55 2014

License high water mark = 7

USER (ospid: 7476): terminating the instance

Instance terminated by USER, pid = 7476

Tue Jul 22 12:48:56 2014

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

_ksb_restart_policy_times={0,60,120,240}

Picked latch-free SCN scheme 3

Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST

Autotune of undo retention is turned on.

IMODE=BR

ILAT =84

LICENSE_MAX_USERS = 0

SYS auditing is disabled

Tue Jul 22 12:48:57 2014

WARNING: The background_dump_dest init.ora parameter has been deprecated.

WARNING: Please remove the background_dump_dest parameter from the init.ora file.

WARNING: The diagnostic_dest init.ora parameter now determines the location of the diagnostic data

WARNING: The new location for the background logs and traces is /u01/app/oracle/diag/rdbms/orcl/orcl/trace

Tue Jul 22 12:48:57 2014

WARNING: The user_dump_dest init.ora parameter has been deprecated.

WARNING: Please remove the user_dump_dest parameter from the init.ora file.

WARNING: The diagnostic_dest init.ora parameter now determines the location of the diagnostic data

WARNING: The new location for the user logs and traces is /u01/app/oracle/diag/rdbms/orcl/orcl/trace

Starting up:

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

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

ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1

System name:    Linux

Node name:    testdb

Release:    2.6.32-431.el6.x86_64

Version:    #1 SMP Sun Nov 10 22:19:54 EST 2013

Machine:    x86_64

VM name:    VMWare Version: 6

Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora

System parameters with non-default values:

processes = 500

sessions = 772

_ksb_restart_policy_times= "0"

_ksb_restart_policy_times= "60"

_ksb_restart_policy_times= "120"

_ksb_restart_policy_times= "240"

memory_target = 564M

control_files = "/u01/app/oracle/oradata/control01.ctl"

control_files = "/u01/app/oracle/oradata/control02.ctl"

db_block_size = 8192

compatible = "11.2.0.0.0"

log_archive_format = "%t_%s_%r.dbf"

log_buffer = 7364608

db_create_file_dest = "/u01/app/oracle/oradata/"

db_recovery_file_dest = "/u01/app/oracle/flash_recovery_area"

db_recovery_file_dest_size= 8G

undo_tablespace = "UNDOTBS1"

undo_retention = 60

_compression_compatibility= "11.2.0.0.0"

O7_DICTIONARY_ACCESSIBILITY= TRUE

remote_login_passwordfile= "EXCLUSIVE"

db_domain = "lhr.com"

dispatchers = "(PROTOCOL=TCP) (SERVICE=orclXDB)"

plsql_warnings = "DISABLE:ALL"

job_queue_processes = 1000

result_cache_max_size = 1472K

background_dump_dest = "/u01/app/oracle/diag/rdbms/orcl/orcl/trace"

user_dump_dest = "/u01/app/oracle/diag/rdbms/orcl/orcl/trace"

core_dump_dest = "/u01/app/oracle/diag/rdbms/orcl/orcl/cdump"

audit_file_dest = "/u01/app/oracle/admin/orcl/adump"

audit_trail = "DB"

db_name = "ORCLASM"

db_unique_name = "ORCL"

open_cursors = 300

optimizer_mode = "ALL_ROWS"

_optimizer_mode_force = TRUE

_always_anti_join = "CHOOSE"

_optimizer_null_aware_antijoin= TRUE

_partition_view_enabled = TRUE

_b_tree_bitmap_plans = TRUE

_cpu_to_io = 0

_optimizer_extended_cursor_sharing= "UDO"

_optimizer_extended_cursor_sharing_rel= "SIMPLE"

_optimizer_adaptive_cursor_sharing= TRUE

_optimizer_cost_model = "CHOOSE"

_optimizer_undo_cost_change= "11.2.0.3"

_optimizer_system_stats_usage= TRUE

_new_sort_cost_estimate = TRUE

_complex_view_merging = TRUE

_unnest_subquery = TRUE

_eliminate_common_subexpr= TRUE

_pred_move_around = TRUE

_convert_set_to_join = FALSE

_px_ual_serial_input = TRUE

_px_minus_intersect = TRUE

_remove_aggr_subquery = TRUE

_optimizer_push_down_distinct= 0

_optimizer_cost_based_transformation= "LINEAR"

_optimizer_squ_bottomup = TRUE

_push_join_predicate = TRUE

_push_join_union_view = TRUE

_push_join_union_view2 = TRUE

_fast_full_scan_enabled = TRUE

_optimizer_skip_scan_enabled= TRUE

_optimizer_join_sel_sanity_check= TRUE

_optim_enhance_nnull_detection= TRUE

_parallel_broadcast_enabled= TRUE

_always_semi_join = "CHOOSE"

_ordered_nested_loop = TRUE

_optimizer_max_permutations= 2000

_nlj_batching_enabled = 1

query_rewrite_enabled = "TRUE"

_mmv_query_rewrite_enabled= TRUE

_local_communication_costing_enabled= TRUE

_left_nested_loops_random= TRUE

_improved_row_length_enabled= TRUE

_index_join_enabled = TRUE

_enable_type_dep_selectivity= TRUE

_improved_outerjoin_card = TRUE

_use_column_stats_for_function= TRUE

_subquery_pruning_enabled= TRUE

_subquery_pruning_mv_enabled= FALSE

_or_expand_nvl_predicate = TRUE

_table_scan_cost_plus_one= TRUE

_cost_equality_semi_join = TRUE

_new_initial_join_orders = TRUE

_optim_peek_user_binds = TRUE

_minimal_stats_aggregation= TRUE

_gs_anti_semi_join_allowed= TRUE

_optim_new_default_join_sel= TRUE

optimizer_dynamic_sampling= 2

_pre_rewrite_push_pred = TRUE

_optimizer_new_join_card_computation= TRUE

_union_rewrite_for_gs = "YES_GSET_MVS"

_generalized_pruning_enabled= TRUE

_optim_adjust_for_part_skews= TRUE

_optimizer_compute_index_stats= TRUE

_optimizer_push_pred_cost_based= TRUE

_optimizer_extend_jppd_view_types= TRUE

_optimizer_filter_pred_pullup= TRUE

_optimizer_connect_by_cost_based= TRUE

_optimizer_connect_by_combine_sw= TRUE

_optimizer_connect_by_elim_dups= TRUE

_connect_by_use_union_all= "TRUE"

_right_outer_hash_enable = TRUE

_replace_virtual_columns = TRUE

skip_unusable_indexes = TRUE

_optimizer_correct_sq_selectivity= TRUE

_optimizer_dim_subq_join_sel= TRUE

_query_rewrite_setopgrw_enable= TRUE

_optimizer_join_order_control= 3

_bloom_filter_enabled = TRUE

_bloom_folding_enabled = TRUE

_optimizer_join_elimination_enabled= TRUE

_gby_hash_aggregation_enabled= TRUE

_globalindex_pnum_filter_enabled= TRUE

_sql_model_unfold_forloops= "RUN_TIME"

_optimizer_cost_hjsmj_multimatch= TRUE

_optimizer_transitivity_retain= TRUE

_px_pwg_enabled = TRUE

_optimizer_cbqt_no_size_restriction= TRUE

_optimizer_enhanced_filter_push= TRUE

_optimizer_rownum_pred_based_fkr= TRUE

_optimizer_better_inlist_costing= "ALL"

_optimizer_or_expansion = "DEPTH"

_optimizer_outer_to_anti_enabled= TRUE

_optimizer_order_by_elimination_enabled= TRUE

_optimizer_star_tran_in_with_clause= TRUE

_optimizer_sortmerge_join_inequality= TRUE

_selfjoin_mv_duplicates = TRUE

_dimension_skip_null = TRUE

_optimizer_complex_pred_selectivity= TRUE

_bloom_pruning_enabled = TRUE

_first_k_rows_dynamic_proration= TRUE

_optimizer_distinct_elimination= TRUE

_optimizer_multi_level_push_pred= TRUE

_optimizer_group_by_placement= TRUE

_optimizer_distinct_placement= TRUE

_optimizer_coalesce_subqueries= TRUE

_optimizer_enable_density_improvements= TRUE

_optimizer_rownum_bind_default= 10

_optimizer_improve_selectivity= TRUE

optimizer_capture_sql_plan_baselines= TRUE

_optimizer_native_full_outer_join= "FORCE"

_optimizer_enable_extended_stats= TRUE

_pivot_implementation_method= "CHOOSE"

_optimizer_interleave_jppd= TRUE

_optimizer_fkr_index_cost_bias= 10

_optimizer_extended_stats_usage_control= 192

_optimizer_fast_pred_transitivity= TRUE

_optimizer_fast_access_pred_analysis= TRUE

_optimizer_unnest_disjunctive_subq= TRUE

_optimizer_unnest_corr_set_subq= TRUE

_optimizer_distinct_agg_transform= TRUE

_aggregation_optimization_settings= 0

_optimizer_eliminate_filtering_join= TRUE

_optimizer_join_factorization= TRUE

_optimizer_use_cbqt_star_transformation= TRUE

_optimizer_table_expansion= TRUE

_and_pruning_enabled = TRUE

_optimizer_use_feedback = TRUE

_optimizer_try_st_before_jppd= TRUE

_px_partition_scan_enabled= TRUE

_optimizer_false_filter_pred_pullup= TRUE

_optimizer_enable_table_lookup_by_nl= TRUE

_optimizer_outer_join_to_inner= TRUE

_optimizer_full_outer_join_to_outer= TRUE

diagnostic_dest = "/u01/app/oracle"

Deprecated system parameters with specified values:

background_dump_dest

user_dump_dest

End of deprecated system parameter listing

Tue Jul 22 12:48:57 2014

PMON started with pid=2, OS id=7550

Tue Jul 22 12:48:57 2014

PSP0 started with pid=3, OS id=7552

Tue Jul 22 12:48:58 2014

VKTM started with pid=4, OS id=7554 at elevated priority

VKTM running at (1)millisec precision with DBRM quantum (100)ms

Tue Jul 22 12:48:59 2014

GEN0 started with pid=5, OS id=7558

Tue Jul 22 12:48:59 2014

DIAG started with pid=6, OS id=7560

Tue Jul 22 12:48:59 2014

DBRM started with pid=7, OS id=7562

Tue Jul 22 12:48:59 2014

DIA0 started with pid=8, OS id=7564

Tue Jul 22 12:48:59 2014

MMAN started with pid=9, OS id=7566

Tue Jul 22 12:48:59 2014

DBW0 started with pid=10, OS id=7568

Tue Jul 22 12:48:59 2014

LGWR started with pid=11, OS id=7570

Tue Jul 22 12:48:59 2014

CKPT started with pid=12, OS id=7572

Tue Jul 22 12:48:59 2014

SMON started with pid=13, OS id=7574

Tue Jul 22 12:48:59 2014

RECO started with pid=14, OS id=7576

Tue Jul 22 12:48:59 2014

MMON started with pid=15, OS id=7578

Tue Jul 22 12:48:59 2014

MMNL started with pid=16, OS id=7580

starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

starting up 1 shared server(s) ...

ORACLE_BASE from environment = /u01/app/oracle

Tue Jul 22 12:48:59 2014

ALTER SYSTEM SET db_name='ORCL' COMMENT='Reset to original value by RMAN' SCOPE=SPFILE;

ALTER SYSTEM RESET db_unique_name SCOPE=SPFILE;

Shutting down instance (immediate)

Shutting down instance: further logons disabled

Stopping background process MMNL

Stopping background process MMON

License high water mark = 2

Waiting for dispatcher 'D000' to shutdown

All dispatchers and shared servers shutdown

alter database close

ORA-1507 signalled during: alter database close...

alter database dismount

ORA-1507 signalled during: alter database dismount...

ARCH: Archival disabled due to shutdown: 1089

Shutting down archive processes

Archiving is disabled

Stopping background process VKTM

ARCH: Archival disabled due to shutdown: 1089

Shutting down archive processes

Archiving is disabled

Tue Jul 22 12:49:08 2014

Instance shutdown complete

Tue Jul 22 12:49:08 2014

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

_ksb_restart_policy_times={0,60,120,240}

Picked latch-free SCN scheme 3

Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST

Autotune of undo retention is turned on.

IMODE=BR

ILAT =84

LICENSE_MAX_USERS = 0

SYS auditing is disabled

Tue Jul 22 12:49:09 2014

WARNING: The background_dump_dest init.ora parameter has been deprecated.

WARNING: Please remove the background_dump_dest parameter from the init.ora file.

WARNING: The diagnostic_dest init.ora parameter now determines the location of the diagnostic data

WARNING: The new location for the background logs and traces is /u01/app/oracle/diag/rdbms/orcl/orcl/trace

Tue Jul 22 12:49:09 2014

WARNING: The user_dump_dest init.ora parameter has been deprecated.

WARNING: Please remove the user_dump_dest parameter from the init.ora file.

WARNING: The diagnostic_dest init.ora parameter now determines the location of the diagnostic data

WARNING: The new location for the user logs and traces is /u01/app/oracle/diag/rdbms/orcl/orcl/trace

Starting up:

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

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

ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1

System name:    Linux

Node name:    testdb

Release:    2.6.32-431.el6.x86_64

Version:    #1 SMP Sun Nov 10 22:19:54 EST 2013

Machine:    x86_64

VM name:    VMWare Version: 6

Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora

System parameters with non-default values:

processes = 500

sessions = 772

_ksb_restart_policy_times= "0"

_ksb_restart_policy_times= "60"

_ksb_restart_policy_times= "120"

_ksb_restart_policy_times= "240"

memory_target = 564M

control_files = "/u01/app/oracle/oradata/control01.ctl"

control_files = "/u01/app/oracle/oradata/control02.ctl"

db_block_size = 8192

compatible = "11.2.0.0.0"

log_archive_format = "%t_%s_%r.dbf"

log_buffer = 7364608

db_create_file_dest = "/u01/app/oracle/oradata/"

db_recovery_file_dest = "/u01/app/oracle/flash_recovery_area"

db_recovery_file_dest_size= 8G

undo_tablespace = "UNDOTBS1"

undo_retention = 60

_compression_compatibility= "11.2.0.0.0"

O7_DICTIONARY_ACCESSIBILITY= TRUE

remote_login_passwordfile= "EXCLUSIVE"

db_domain = "lhr.com"

dispatchers = "(PROTOCOL=TCP) (SERVICE=orclXDB)"

plsql_warnings = "DISABLE:ALL"

job_queue_processes = 1000

result_cache_max_size = 1472K

background_dump_dest = "/u01/app/oracle/diag/rdbms/orcl/orcl/trace"

user_dump_dest = "/u01/app/oracle/diag/rdbms/orcl/orcl/trace"

core_dump_dest = "/u01/app/oracle/diag/rdbms/orcl/orcl/cdump"

audit_file_dest = "/u01/app/oracle/admin/orcl/adump"

audit_trail = "DB"

db_name = "ORCL"

open_cursors = 300

optimizer_mode = "ALL_ROWS"

_optimizer_mode_force = TRUE

_always_anti_join = "CHOOSE"

_optimizer_null_aware_antijoin= TRUE

_partition_view_enabled = TRUE

_b_tree_bitmap_plans = TRUE

_cpu_to_io = 0

_optimizer_extended_cursor_sharing= "UDO"

_optimizer_extended_cursor_sharing_rel= "SIMPLE"

_optimizer_adaptive_cursor_sharing= TRUE

_optimizer_cost_model = "CHOOSE"

_optimizer_undo_cost_change= "11.2.0.3"

_optimizer_system_stats_usage= TRUE

_new_sort_cost_estimate = TRUE

_complex_view_merging = TRUE

_unnest_subquery = TRUE

_eliminate_common_subexpr= TRUE

_pred_move_around = TRUE

_convert_set_to_join = FALSE

_px_ual_serial_input = TRUE

_px_minus_intersect = TRUE

_remove_aggr_subquery = TRUE

_optimizer_push_down_distinct= 0

_optimizer_cost_based_transformation= "LINEAR"

_optimizer_squ_bottomup = TRUE

_push_join_predicate = TRUE

_push_join_union_view = TRUE

_push_join_union_view2 = TRUE

_fast_full_scan_enabled = TRUE

_optimizer_skip_scan_enabled= TRUE

_optimizer_join_sel_sanity_check= TRUE

_optim_enhance_nnull_detection= TRUE

_parallel_broadcast_enabled= TRUE

_always_semi_join = "CHOOSE"

_ordered_nested_loop = TRUE

_optimizer_max_permutations= 2000

_nlj_batching_enabled = 1

query_rewrite_enabled = "TRUE"

_mmv_query_rewrite_enabled= TRUE

_local_communication_costing_enabled= TRUE

_left_nested_loops_random= TRUE

_improved_row_length_enabled= TRUE

_index_join_enabled = TRUE

_enable_type_dep_selectivity= TRUE

_improved_outerjoin_card = TRUE

_use_column_stats_for_function= TRUE

_subquery_pruning_enabled= TRUE

_subquery_pruning_mv_enabled= FALSE

_or_expand_nvl_predicate = TRUE

_table_scan_cost_plus_one= TRUE

_cost_equality_semi_join = TRUE

_new_initial_join_orders = TRUE

_optim_peek_user_binds = TRUE

_minimal_stats_aggregation= TRUE

_gs_anti_semi_join_allowed= TRUE

_optim_new_default_join_sel= TRUE

optimizer_dynamic_sampling= 2

_pre_rewrite_push_pred = TRUE

_optimizer_new_join_card_computation= TRUE

_union_rewrite_for_gs = "YES_GSET_MVS"

_generalized_pruning_enabled= TRUE

_optim_adjust_for_part_skews= TRUE

_optimizer_compute_index_stats= TRUE

_optimizer_push_pred_cost_based= TRUE

_optimizer_extend_jppd_view_types= TRUE

_optimizer_filter_pred_pullup= TRUE

_optimizer_connect_by_cost_based= TRUE

_optimizer_connect_by_combine_sw= TRUE

_optimizer_connect_by_elim_dups= TRUE

_connect_by_use_union_all= "TRUE"

_right_outer_hash_enable = TRUE

_replace_virtual_columns = TRUE

skip_unusable_indexes = TRUE

_optimizer_correct_sq_selectivity= TRUE

_optimizer_dim_subq_join_sel= TRUE

_query_rewrite_setopgrw_enable= TRUE

_optimizer_join_order_control= 3

_bloom_filter_enabled = TRUE

_bloom_folding_enabled = TRUE

_optimizer_join_elimination_enabled= TRUE

_gby_hash_aggregation_enabled= TRUE

_globalindex_pnum_filter_enabled= TRUE

_sql_model_unfold_forloops= "RUN_TIME"

_optimizer_cost_hjsmj_multimatch= TRUE

_optimizer_transitivity_retain= TRUE

_px_pwg_enabled = TRUE

_optimizer_cbqt_no_size_restriction= TRUE

_optimizer_enhanced_filter_push= TRUE

_optimizer_rownum_pred_based_fkr= TRUE

_optimizer_better_inlist_costing= "ALL"

_optimizer_or_expansion = "DEPTH"

_optimizer_outer_to_anti_enabled= TRUE

_optimizer_order_by_elimination_enabled= TRUE

_optimizer_star_tran_in_with_clause= TRUE

_optimizer_sortmerge_join_inequality= TRUE

_selfjoin_mv_duplicates = TRUE

_dimension_skip_null = TRUE

_optimizer_complex_pred_selectivity= TRUE

_bloom_pruning_enabled = TRUE

_first_k_rows_dynamic_proration= TRUE

_optimizer_distinct_elimination= TRUE

_optimizer_multi_level_push_pred= TRUE

_optimizer_group_by_placement= TRUE

_optimizer_distinct_placement= TRUE

_optimizer_coalesce_subqueries= TRUE

_optimizer_enable_density_improvements= TRUE

_optimizer_rownum_bind_default= 10

_optimizer_improve_selectivity= TRUE

optimizer_capture_sql_plan_baselines= TRUE

_optimizer_native_full_outer_join= "FORCE"

_optimizer_enable_extended_stats= TRUE

_pivot_implementation_method= "CHOOSE"

_optimizer_interleave_jppd= TRUE

_optimizer_fkr_index_cost_bias= 10

_optimizer_extended_stats_usage_control= 192

_optimizer_fast_pred_transitivity= TRUE

_optimizer_fast_access_pred_analysis= TRUE

_optimizer_unnest_disjunctive_subq= TRUE

_optimizer_unnest_corr_set_subq= TRUE

_optimizer_distinct_agg_transform= TRUE

_aggregation_optimization_settings= 0

_optimizer_eliminate_filtering_join= TRUE

_optimizer_join_factorization= TRUE

_optimizer_use_cbqt_star_transformation= TRUE

_optimizer_table_expansion= TRUE

_and_pruning_enabled = TRUE

_optimizer_use_feedback = TRUE

_optimizer_try_st_before_jppd= TRUE

_px_partition_scan_enabled= TRUE

_optimizer_false_filter_pred_pullup= TRUE

_optimizer_enable_table_lookup_by_nl= TRUE

_optimizer_outer_join_to_inner= TRUE

_optimizer_full_outer_join_to_outer= TRUE

diagnostic_dest = "/u01/app/oracle"

Deprecated system parameters with specified values:

background_dump_dest

user_dump_dest

End of deprecated system parameter listing

Tue Jul 22 12:49:09 2014

PMON started with pid=2, OS id=7679

Tue Jul 22 12:49:09 2014

PSP0 started with pid=3, OS id=7681

Tue Jul 22 12:49:10 2014

VKTM started with pid=4, OS id=7683 at elevated priority

VKTM running at (1)millisec precision with DBRM quantum (100)ms

Tue Jul 22 12:49:10 2014

GEN0 started with pid=5, OS id=7687

Tue Jul 22 12:49:10 2014

DIAG started with pid=6, OS id=7689

Tue Jul 22 12:49:10 2014

DBRM started with pid=7, OS id=7691

Tue Jul 22 12:49:10 2014

DIA0 started with pid=8, OS id=7693

Tue Jul 22 12:49:10 2014

MMAN started with pid=9, OS id=7695

Tue Jul 22 12:49:10 2014

DBW0 started with pid=10, OS id=7697

Tue Jul 22 12:49:10 2014

LGWR started with pid=11, OS id=7699

Tue Jul 22 12:49:10 2014

CKPT started with pid=12, OS id=7701

Tue Jul 22 12:49:10 2014

SMON started with pid=13, OS id=7703

Tue Jul 22 12:49:10 2014

RECO started with pid=14, OS id=7705

Tue Jul 22 12:49:10 2014

MMON started with pid=15, OS id=7707

Tue Jul 22 12:49:10 2014

MMNL started with pid=16, OS id=7709

starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

starting up 1 shared server(s) ...

ORACLE_BASE from environment = /u01/app/oracle

Tue Jul 22 12:49:11 2014

CREATE CONTROLFILE REUSE SET DATABASE "ORCL" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/redo01.log' SIZE 20 M ,

GROUP 2 '/u01/app/oracle/oradata/redo02.log' SIZE 20 M ,

GROUP 3 '/u01/app/oracle/oradata/redo03.log' SIZE 20 M

DATAFILE

'/u01/app/oracle/oradata/system.dbf'

CHARACTER SET ZHS16GBK

WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command

Default Temporary Tablespace will be necessary for a locally managed database in future release

Successful mount of redo thread 1, with mount id 1381375303

Completed: CREATE CONTROLFILE REUSE SET DATABASE "ORCL" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/redo01.log' SIZE 20 M ,

GROUP 2 '/u01/app/oracle/oradata/redo02.log' SIZE 20 M ,

GROUP 3 '/u01/app/oracle/oradata/redo03.log' SIZE 20 M

DATAFILE

'/u01/app/oracle/oradata/system.dbf'

CHARACTER SET ZHS16GBK

Tue Jul 22 12:49:12 2014

Switch of datafile 2 complete to datafile copy

checkpoint is 7505140

Switch of datafile 3 complete to datafile copy

checkpoint is 7505140

Switch of datafile 4 complete to datafile copy

checkpoint is 7505140

Switch of datafile 5 complete to datafile copy

checkpoint is 7505140

Switch of datafile 6 complete to datafile copy

checkpoint is 7505140

Switch of datafile 7 complete to datafile copy

checkpoint is 7505140

Switch of datafile 8 complete to datafile copy

checkpoint is 7505140

alter database add supplemental log data

SUPLOG: Previous supplemental logging attributes at scn = 0

SUPLOG: minimal = OFF, primary key = OFF

SUPLOG: unique = OFF, foreign key = OFF, all column = OFF

SUPLOG: procedural replication = OFF

SUPLOG: New supplemental logging attributes at scn = 0

SUPLOG: minimal = ON, primary key = OFF

SUPLOG: unique = OFF, foreign key = OFF, all column = OFF

SUPLOG: procedural replication = OFF

Completed: alter database add supplemental log data

alter database add supplemental log data(PRIMARY KEY, UNIQUE) columns

SUPLOG: Previous supplemental logging attributes at scn = 0

SUPLOG: minimal = ON, primary key = OFF

SUPLOG: unique = OFF, foreign key = OFF, all column = OFF

SUPLOG: procedural replication = OFF

SUPLOG: New supplemental logging attributes at scn = 0

SUPLOG: minimal = ON, primary key = ON

SUPLOG: unique = ON, foreign key = OFF, all column = OFF

SUPLOG: procedural replication = OFF

Completed: alter database add supplemental log data(PRIMARY KEY, UNIQUE) columns

alter database enable block change tracking

Block change tracking file is current.

Completed: alter database enable block change tracking

alter database open resetlogs

RESETLOGS after incomplete recovery UNTIL CHANGE 7505140

 

  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@testdb ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 22 14:11:41 2014

 

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

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.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 1381372487

 

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 2

Current log sequence     2

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> select * from v$block_change_tracking;

 

STATUS

----------

FILENAME

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

BYTES

----------

ENABLED

/u01/app/oracle/oradata/ORCL/changetracking/o1_mf_9wvvb8k7_.chg

11599872

 

 

set linesize 800

set pagesize 9999

column name format a80

select name ,'data' file_types from v$datafile

union all

select member,'log' from v$logfile

union all

select name,'control' from v$controlfile

union all

7 select name,'temp' from v$tempfile;

 

NAME                                         FILE_TY

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

/u01/app/oracle/oradata/system.dbf                         data

/u01/app/oracle/oradata/sysaux.dbf                         data

/u01/app/oracle/oradata/undotbs1.dbf                         data

/u01/app/oracle/oradata/users.dbf                         data

/u01/app/oracle/oradata/example.dbf                         data

/u01/app/oracle/oradata/undotbs2.dbf                         data

/u01/app/oracle/oradata/tbs_rc.dbf                         data

/u01/app/oracle/oradata/ts_lhr.dbf                         data

/u01/app/oracle/oradata/redo03.log                         log

/u01/app/oracle/oradata/redo02.log                         log

/u01/app/oracle/oradata/redo01.log                         log

/u01/app/oracle/oradata/control01.ctl                         control

/u01/app/oracle/oradata/control02.ctl                         control

/u01/app/oracle/oradata/TEMP01.DBF                         temp

 

14 rows selected.

 

SQL>

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

  1. drop database

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

 

[oracle@testdb ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 22 14:15:58 2014

 

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

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.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 588746752 bytes

Fixed Size         2230592 bytes

Variable Size         482346688 bytes

Database Buffers     96468992 bytes

Redo Buffers         7700480 bytes

Database mounted.

SQL> exit

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

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

[oracle@testdb ~]$ rman target /

 

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jul 22 14:16:36 2014

 

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

 

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

 

RMAN> drop database;

 

database name is "ORCL" and DBID is 1381372487

 

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

database dropped

 

RMAN>

 

告警日志:

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

请登录后发表评论 登录
全部评论
QQ:646634621| 网名:小麦苗| 微信公众号:xiaomaimiaolhr| 11g OCM| QQ群:618766405 微信群:私聊| 《数据库笔试面试宝典》作者| OCP、OCM、高可用(RAC+DG+OGG)网络班开讲啦,有需要的小伙伴可以私聊我。

注册时间:2012-09-23

  • 博文量
    1367
  • 访问量
    8348665