题记： 可以使用rman 的duplicate命令直接把asm存储的数据库复制到文件管理的系统，本文就是基于这样的情况。。。。。
有关rman的另外2篇文章：
http://blog.itpub.net/26736162/viewspace-1223253/
http://blog.itpub.net/26736162/viewspace-1223247/
-
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种情况 ②基于活动数据库
-
本次实验简介
本次实验就是基于备份，但是不连接到目标数据库，也不连接到恢复目录。
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.
-
本次实验原理图
-
环境及搭建要求
环境：
-
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管理的，目标数据库是文件管理系统的
-
源数据库开启了数据库闪回，块改变跟踪，属于归档模式
-
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;
-
对辅助数据库的要求
辅助数据库应该提前安装好数据库，配置好环境变量。。。。。
辅助数据库的环境变量配置：
[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'
-
正式开始
-
前期准备
-
建表
-
-
--登录源数据库并创建一个新的表
[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>
-
数据库归档模式
让源数据库处于归档模式：
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>
-
备份数据库
--做个全备份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>
查看备份：
-
将备份内容拷贝到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 库查看，确保已经传递到：
-
创建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就没有任何关系了。。。。。。。。。
-
创建和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
仔细检查，不能马虎的。。。。。。。。。。。。。。。。
-
创建密码文件
[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]$
-
实施数据库的复制
--启动auxiliary instance到nomount状态
-
启动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]$
-
连接到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
-
可能的报错
-
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;）
-
备份位置
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目录下仍然有很多其它文件夹的：
-
验证
验证归档？验证之前的表？验证块改变跟踪功能？验证闪回？？？
[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>
搞定。。。。。。。。。。。。。。。。。
-
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>
告警日志：
