採用DUPLICATE 把asm資料庫複製到檔案系統
題記： 可以使用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>
告警日誌：
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-1224861/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 採用Duplicate不連線target資料庫的方式將檔案系統遷移到ASM資料庫ASM
- Rman duplicate資料庫複製(單系統)資料庫
- 從ASM磁碟中複製檔案到本地檔案系統ASM
- 用rman從檔案系統遷移資料庫到asm資料庫ASM
- 10G下從ASM複製檔案到檔案系統ASM
- 四、用rman從檔案系統遷移資料庫到asm資料庫ASM
- ASM與檔案系統之間copy資料檔案--檔案系統到ASMASM
- 使用RMAN遷移檔案系統資料庫到ASM資料庫ASM
- 使用RMAN在ASM和檔案系統之間複製資料ASM
- 三、rman 資料庫遷移--從檔案系統到裸裝置 用dd複製控制檔案資料庫
- 12c複製 RAC ASM中的密碼檔案到檔案系統ASM密碼
- Oracle 之 Duplicate 複製資料庫Oracle資料庫
- DUPLICATE遠端複製資料庫資料庫
- 把檔案系統的資料檔案遷移到ASM儲存ASM
- 移動資料檔案從ASM到檔案系統ASM
- 如何遷移ASM資料檔案到檔案系統ASM
- ASM FTP 功能複製 ASM資料庫ASMFTP資料庫
- duplicate複製資料庫（rac-rac）資料庫
- 【RMAN】使用duplicate本地複製資料庫資料庫
- duplicate rman複製資料庫技術資料庫
- 遷移資料庫檔案到ASM資料庫ASM
- 資料庫從檔案系統遷移到ASM資料庫ASM
- RMAN實戰系列之一：用duplicate複製資料庫資料庫
- 直接複製資料檔案實現linux平臺資料庫複製到windows平臺資料庫Linux資料庫Windows
- RMAN duplicate from active database 複製資料庫Database資料庫
- Duplicate 複製資料庫實驗過程資料庫
- 使用rman的命令duplicate複製資料庫資料庫
- 移動資料檔案從檔案系統到ASM磁碟組中ASM
- 將資料庫從ASM遷移到檔案系統資料庫ASM
- 將資料庫從檔案系統遷移到ASM資料庫ASM
- RMAN高階應用之Duplicate複製資料庫(3)複製流程資料庫
- Linux系統複製檔案/資料夾到遠端伺服器Linux伺服器
- asm拷貝檔案到檔案系統ASM
- duplicate複製資料庫（rac-單例項）資料庫單例
- 【RMAN】使用RMAN duplicate複製同機資料庫資料庫
- 【資料遷移】RMAN遷移資料庫到ASM(二)切換資料檔案到ASM資料庫ASM
- 把資料庫控制檔案備份到跟蹤檔案資料庫
- 使用RMAN高階應用之Duplicate複製資料庫資料庫