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

0    275    1

Tags:

👉 本文共约42197个字,系统预计阅读时间或需159分钟。

题记: 可以使用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:

img

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

  1. 本次实验简介

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

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

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

  1. 本次实验原理图

img

  1. 环境及搭建要求

环境:

  • 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>

img

select * from v$block_change_tracking;

img

  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/$ORACLESID/trace/alert$ORACLE_SID.log'

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

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

  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>

img

  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/oraclebk/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>

查看备份:

img

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

img

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

img

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

img

确保传递到目的地:

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

img

至此与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_archiveformat='%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_archiveformat='%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]$

img

  1. 实施数据库的复制

--启动auxiliary instance到nomount状态

  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>

img

告警日志:

[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_archiveformat = "%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]$

img

  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;

img

修改一下文件名,加入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个通道恢复:

img

开始:

[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_2959wvv9nlv.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_2959wvv9nlv.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_archiveformat = "%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

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

标签:

Avatar photo

小麦苗

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

您可能还喜欢...

发表回复