採用Duplicate不連線target資料庫的方式將檔案系統遷移到ASM
Duplicating a Database Without a Target Connection to a Host with ASM，感覺這個標題還是用英文描述比較直接。
最近公司有一個新的專案，是上海、北京異地資料庫災備。
上海市為生產端，資料庫版本為Oracle11.2.0.4 RAC雙節點，採用叢集檔案系統儲存資料。並沒有採用我們熟悉的ASM。
北京市為災備端，資料庫版本也是Oracle11.2.0.4，單例項，採用檔案系統儲存資料。
當發生災難的時候，需要將災備端的單例項資料庫掛載到接管區的一套雙節點RAC環境上去。本次工作內容是將災備端的檔案系統遷移到ASM上，為下一次的單機掛載到RAC做好準備。
災備端的資料庫沒有多餘的儲存空間，無法搭建好一套ASM環境並採用RMAN的backup as copy tablespace xx format ‘+DATA’的方式。這次的實施計劃是在上海生產端用RMAN執行一次全備，儲存到磁碟上。在災備端刪除現有資料庫，建立ASM磁碟組，使用生產端的全備來複制資料庫。下面的實驗模擬了這次實施的步驟。
步驟如下：
1，執行生產端資料庫全備
2，將全備介質掛載到災備端
3，備份災備端pfile檔案，匯出控制檔案到trace檔案
4，刪除災備端資料庫
5，災備端建立ASM例項，並且建立磁碟組
6，建立災備端所需目錄
7，新增災備端靜態監聽
8，使用生產端全備duplicate複製資料庫
本次實驗都是在虛擬機器上演練
1，DBCA建立生產端資料庫PROD，為臨時的生產環境。
建立過程不演示，來檢視一下基本資訊
檢視目標資料庫資料檔案路徑
SYS@PROD >select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/oradata/PROD/system01.dbf
/u01/oradata/PROD/sysaux01.dbf
/u01/oradata/PROD/undotbs01.dbf
/u01/oradata/PROD/users01.dbf
檢視目標資料庫臨時檔案路徑
SYS@PROD >select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/oradata/PROD/temp01.dbf
檢視目標資料庫聯機重做日誌路徑
SYS@PROD >select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/oradata/PROD/redo03.log
/u01/oradata/PROD/redo02.log
/u01/oradata/PROD/redo01.log
2，使用生產端資料庫的spfile建立pfile到/home/oracle/pfile
SYS@PROD >create pfile='/home/oracle/pfile' from spfile;
修改新建立的目標資料庫pfile將控制檔案路徑修改為ASM磁碟組+DATA和+FRA上，修改快速恢復區路徑為+FRA
注意紅色字型部分為修改後的路徑，##註釋的是剛才建立時的路徑
PROD.__db_cache_size=222298112
PROD.__java_pool_size=4194304
PROD.__large_pool_size=8388608
PROD.__oracle_base='/u01'#ORACLE_BASE set from environment
PROD.__pga_aggregate_target=234881024
PROD.__sga_target=348127232
PROD.__shared_io_pool_size=0
PROD.__shared_pool_size=104857600
PROD.__streams_pool_size=0
*.audit_file_dest='/u01/admin/PROD/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
##*.control_files='/u01/oradata/PROD/control01.ctl','/u01/fast_recovery_area/PROD/control02.ctl'
*.control_files='+DATA/PROD/controlfile/control01.ctl','+FRA/PROD/controlfile/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='PROD'
##*.db_recovery_file_dest='/u01/fast_recovery_area'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRODXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=583008256
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
3，生產端資料庫執行一次全備，儲存路徑為/home/oracle
$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Oct 8 04:14:09 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=272224555)
run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
backup tag 'SH_DB' as compressed backupset format '/home/oracle/full_%U.bak' database
include current controlfile;
backup tag 'SH_ARCH' archivelog all format '/home/oracle/arch_%U.bak';
release channel c1;
release channel c2;
}
4，DBCA刪除資料庫
刪除資料庫後，現在的生產端系統就不存在了。該系統變成了已經刪除資料庫等待建立ASM的災備端環境了。
5，建立災備端auxiliary所需目錄
mkdir -p $ORACLE_BASE/admin/PROD/adump
6，建立auxiliary資料庫密碼檔案
$ orapwd file=$ORACLE_HOME/dbs/orapwPROD password=oracle entries=5
7，在grid使用者下為auxiliary資料庫新增靜態監聽連線，紅色字型部分為新增的內容
[grid@single ~]$ vi /grid/app/11.2.0/grid/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = single)(PORT = 1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=PROD)
(ORACLE_HOME=/u01/oracle)
(GLOBAL_DBNAME=PROD)))
ADR_BASE_LISTENER = /grid
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
8，auxiliary資料庫使用修改過的pfile啟動資料庫到nomount狀態，並且quit推出。
$ sqlplus / as sysdba
SYS@PROD >startup nomount pfile=/home/oracle/pfile;
SYS@PROD >exit
9，使用rman登入auxiliary資料庫
[oracle@single ~]$ rman auxiliary /
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Oct 8 06:26:36 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to auxiliary database: PROD (not mounted)
10，使用RMAN執行duplicate複製災備資料庫
在這一步需要從生產庫alter database backup controlfile to trace匯出指令碼，在使用select value from v$diag_info找到該trace的路徑。將生成控制檔案指令碼中的資料庫檔案路徑進行參考來寫入set newname for datafile n，因為生產端的資料檔案在叢集人檔案系統上，而災備端資料儲存在ASM上。所以需要set newname來修改路徑
run{
allocate auxiliary channel dup1 type disk;
set newname for datafile 1 to '+DATA';
set newname for datafile 2 to '+DATA';
set newname for datafile 3 to '+DATA';
set newname for datafile 4 to '+DATA';
set newname for tempfile 1 to '+DATA';
duplicate target database to PROD backup location '/home/oracle'
logfile
group 1('+DATA') size 50m reuse,
group 2('+DATA') size 50m reuse,
group 3('+DATA') size 50m reuse;
}
螢幕輸出內容較多，放在文章末尾供參考
11，驗證auxiliary資料庫的複製結果，對比之前的檔案系統目標庫
SYS@PROD >select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/prod/datafile/system.264.860394501
+DATA/prod/datafile/sysaux.280.860394525
+DATA/prod/datafile/undotbs1.279.860394525
+DATA/prod/datafile/users.281.860394501
SYS@PROD >select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/prod/tempfile/temp.259.860394555
SYS@PROD >select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DATA/prod/onlinelog/group_3.296.860394553
+DATA/prod/onlinelog/group_2.287.860394553
+DATA/prod/onlinelog/group_1.269.860394553
+FRA/prod/onlinelog/group_1.257.860394553
+FRA/prod/onlinelog/group_2.273.860394553
+FRA/prod/onlinelog/group_3.274.860394553
6 rows selected.
SYS@PROD >show parameter control_file;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DATA/prod/controlfile/control
01.ctl, +FRA/prod/controlfile/
control02.ctl
SYS@PROD >select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
PROD READ WRITE
SYS@PROD >select * from t1;
ID NAME
---------- ----------
1 007
12，auxiliary資料庫從記憶體中建立引數檔案到ASM磁碟組+DATA/PROD
SYS@PROD >create spfile='+DATA/PROD/spfilePROD.ora' from memory;
在ASM磁碟組中生成了手工建立的spfile檔案
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfilePROD.ora
13，建立auxiliary的pfile檔案，指定spfile所在路徑.紅色字型內容是需要新增的。
$ vi $ORACLE_HOME/dbs/initPROD.ora
SPFILE='+DATA/PROD/spfilePROD.ora’
$ cat $ORACLE_HOME/dbs/initPROD.ora
SPFILE='+DATA/PROD/spfilePROD.ora'
14，使用新建立的initPROD.ora來啟動auxiliary資料庫
SYS@PROD >shutdown immediate;
SYS@PROD >startup;
SYS@PROD >show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/prod/spfileprod.ora
本次試驗圓滿成功！
duplicate複製auxiliary資料庫的螢幕輸出如下：
RMAN> run{
2> allocate auxiliary channel dup1 type disk;
3> set newname for datafile 1 to '+DATA';
4> set newname for datafile 2 to '+DATA';
5> set newname for datafile 3 to '+DATA';
6> set newname for datafile 4 to '+DATA';
7> set newname for tempfile 1 to '+DATA';
8> duplicate target database to PROD backup location '/home/oracle'
9> logfile
10> group 1('+DATA') size 50m reuse,
11> group 2('+DATA') size 50m reuse,
12> group 3('+DATA') size 50m reuse;
13> }
allocated channel: dup1
channel dup1: SID=24 device type=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting Duplicate Db at 2014-10-08 06:28:01
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 580395008 bytes
Fixed Size 2255392 bytes
Variable Size 411043296 bytes
Database Buffers 163577856 bytes
Redo Buffers 3518464 bytes
allocated channel: dup1
channel dup1: SID=24 device type=DISK
contents of Memory Script:
{
sql clone "alter system set db_name =
''PROD'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''PROD'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from '/home/oracle/full_03pkgtf4_1_1.bak';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 580395008 bytes
Fixed Size 2255392 bytes
Variable Size 411043296 bytes
Database Buffers 163577856 bytes
Redo Buffers 3518464 bytes
allocated channel: dup1
channel dup1: SID=24 device type=DISK
Starting restore at 2014-10-08 06:28:12
channel dup1: restoring control file
channel dup1: restore complete, elapsed time: 00:00:03
output file name=+DATA/prod/controlfile/control01.ctl
output file name=+FRA/prod/controlfile/control02.ctl
Finished restore at 2014-10-08 06:28:15
database mounted
contents of Memory Script:
{
set until scn 963961;
set newname for datafile 1 to
"+DATA";
set newname for datafile 2 to
"+DATA";
set newname for datafile 3 to
"+DATA";
set newname for datafile 4 to
"+DATA";
restore
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 2014-10-08 06:28:19
channel dup1: starting datafile backup set restore
channel dup1: specifying datafile(s) to restore from backup set
channel dup1: restoring datafile 00001 to +DATA
channel dup1: restoring datafile 00004 to +DATA
channel dup1: reading from backup piece /home/oracle/full_01pkgtea_1_1.bak
channel dup1: piece handle=/home/oracle/full_01pkgtea_1_1.bak tag=SH_DB
channel dup1: restored backup piece 1
channel dup1: restore complete, elapsed time: 00:00:25
channel dup1: starting datafile backup set restore
channel dup1: specifying datafile(s) to restore from backup set
channel dup1: restoring datafile 00002 to +DATA
channel dup1: restoring datafile 00003 to +DATA
channel dup1: reading from backup piece /home/oracle/full_02pkgtea_1_1.bak
channel dup1: piece handle=/home/oracle/full_02pkgtea_1_1.bak tag=SH_DB
channel dup1: restored backup piece 1
channel dup1: restore complete, elapsed time: 00:00:15
Finished restore at 2014-10-08 06:29:00
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=860394540 file name=+DATA/prod/datafile/system.264.860394501
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=860394540 file name=+DATA/prod/datafile/sysaux.280.860394525
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=860394540 file name=+DATA/prod/datafile/undotbs1.279.860394525
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=860394540 file name=+DATA/prod/datafile/users.281.860394501
contents of Memory Script:
{
set until scn 963961;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 2014-10-08 06:29:00
starting media recovery
channel dup1: starting archived log restore to default destination
channel dup1: restoring archived log
archived log thread=1 sequence=4
channel dup1: reading from backup piece /home/oracle/arch_05pkgtf7_1_1.bak
channel dup1: piece handle=/home/oracle/arch_05pkgtf7_1_1.bak tag=SH_ARCH
channel dup1: restored backup piece 1
channel dup1: restore complete, elapsed time: 00:00:01
archived log file name=+FRA/prod/archivelog/2014_10_08/thread_1_seq_4.257.860394541 thread=1 sequence=4
channel clone_default: deleting archived log(s)
archived log file name=+FRA/prod/archivelog/2014_10_08/thread_1_seq_4.257.860394541 RECID=1 STAMP=860394540
media recovery complete, elapsed time: 00:00:00
Finished recover at 2014-10-08 06:29:01
Oracle instance started
Total System Global Area 580395008 bytes
Fixed Size 2255392 bytes
Variable Size 411043296 bytes
Database Buffers 163577856 bytes
Redo Buffers 3518464 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''PROD'' 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 = ''PROD'' 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 580395008 bytes
Fixed Size 2255392 bytes
Variable Size 411043296 bytes
Database Buffers 163577856 bytes
Redo Buffers 3518464 bytes
allocated channel: dup1
channel dup1: SID=24 device type=DISK
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "PROD" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '+DATA' ) SIZE 50 M REUSE,
GROUP 2 ( '+DATA' ) SIZE 50 M REUSE,
GROUP 3 ( '+DATA' ) SIZE 50 M REUSE
DATAFILE
'+DATA/prod/datafile/system.264.860394501'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
set newname for tempfile 1 to
"+DATA";
switch clone tempfile all;
catalog clone datafilecopy "+DATA/prod/datafile/sysaux.280.860394525",
"+DATA/prod/datafile/undotbs1.279.860394525",
"+DATA/prod/datafile/users.281.860394501";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +DATA in control file
cataloged datafile copy
datafile copy file name=+DATA/prod/datafile/sysaux.280.860394525 RECID=1 STAMP=860394553
cataloged datafile copy
datafile copy file name=+DATA/prod/datafile/undotbs1.279.860394525 RECID=2 STAMP=860394553
cataloged datafile copy
datafile copy file name=+DATA/prod/datafile/users.281.860394501 RECID=3 STAMP=860394553
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=860394553 file name=+DATA/prod/datafile/sysaux.280.860394525
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=860394553 file name=+DATA/prod/datafile/undotbs1.279.860394525
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=860394553 file name=+DATA/prod/datafile/users.281.860394501
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 2014-10-08 06:29:16
released channel: dup1
 |
最近公司有一個新的專案，是上海、北京異地資料庫災備。
上海市為生產端，資料庫版本為Oracle11.2.0.4 RAC雙節點，採用叢集檔案系統儲存資料。並沒有採用我們熟悉的ASM。
北京市為災備端，資料庫版本也是Oracle11.2.0.4，單例項，採用檔案系統儲存資料。
當發生災難的時候，需要將災備端的單例項資料庫掛載到接管區的一套雙節點RAC環境上去。本次工作內容是將災備端的檔案系統遷移到ASM上，為下一次的單機掛載到RAC做好準備。
災備端的資料庫沒有多餘的儲存空間，無法搭建好一套ASM環境並採用RMAN的backup as copy tablespace xx format ‘+DATA’的方式。這次的實施計劃是在上海生產端用RMAN執行一次全備，儲存到磁碟上。在災備端刪除現有資料庫，建立ASM磁碟組，使用生產端的全備來複制資料庫。下面的實驗模擬了這次實施的步驟。
步驟如下：
1，執行生產端資料庫全備
2，將全備介質掛載到災備端
3，備份災備端pfile檔案，匯出控制檔案到trace檔案
4，刪除災備端資料庫
5，災備端建立ASM例項，並且建立磁碟組
6，建立災備端所需目錄
7，新增災備端靜態監聽
8，使用生產端全備duplicate複製資料庫
本次實驗都是在虛擬機器上演練
1，DBCA建立生產端資料庫PROD，為臨時的生產環境。
建立過程不演示，來檢視一下基本資訊
檢視目標資料庫資料檔案路徑
SYS@PROD >select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/oradata/PROD/system01.dbf
/u01/oradata/PROD/sysaux01.dbf
/u01/oradata/PROD/undotbs01.dbf
/u01/oradata/PROD/users01.dbf
檢視目標資料庫臨時檔案路徑
SYS@PROD >select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/oradata/PROD/temp01.dbf
檢視目標資料庫聯機重做日誌路徑
SYS@PROD >select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/oradata/PROD/redo03.log
/u01/oradata/PROD/redo02.log
/u01/oradata/PROD/redo01.log
2，使用生產端資料庫的spfile建立pfile到/home/oracle/pfile
SYS@PROD >create pfile='/home/oracle/pfile' from spfile;
修改新建立的目標資料庫pfile將控制檔案路徑修改為ASM磁碟組+DATA和+FRA上，修改快速恢復區路徑為+FRA
注意紅色字型部分為修改後的路徑，##註釋的是剛才建立時的路徑
PROD.__db_cache_size=222298112
PROD.__java_pool_size=4194304
PROD.__large_pool_size=8388608
PROD.__oracle_base='/u01'#ORACLE_BASE set from environment
PROD.__pga_aggregate_target=234881024
PROD.__sga_target=348127232
PROD.__shared_io_pool_size=0
PROD.__shared_pool_size=104857600
PROD.__streams_pool_size=0
*.audit_file_dest='/u01/admin/PROD/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
##*.control_files='/u01/oradata/PROD/control01.ctl','/u01/fast_recovery_area/PROD/control02.ctl'
*.control_files='+DATA/PROD/controlfile/control01.ctl','+FRA/PROD/controlfile/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='PROD'
##*.db_recovery_file_dest='/u01/fast_recovery_area'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRODXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=583008256
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
3，生產端資料庫執行一次全備，儲存路徑為/home/oracle
$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Oct 8 04:14:09 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=272224555)
run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
backup tag 'SH_DB' as compressed backupset format '/home/oracle/full_%U.bak' database
include current controlfile;
backup tag 'SH_ARCH' archivelog all format '/home/oracle/arch_%U.bak';
release channel c1;
release channel c2;
}
4，DBCA刪除資料庫
刪除資料庫後，現在的生產端系統就不存在了。該系統變成了已經刪除資料庫等待建立ASM的災備端環境了。
5，建立災備端auxiliary所需目錄
mkdir -p $ORACLE_BASE/admin/PROD/adump
6，建立auxiliary資料庫密碼檔案
$ orapwd file=$ORACLE_HOME/dbs/orapwPROD password=oracle entries=5
7，在grid使用者下為auxiliary資料庫新增靜態監聽連線，紅色字型部分為新增的內容
[grid@single ~]$ vi /grid/app/11.2.0/grid/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = single)(PORT = 1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=PROD)
(ORACLE_HOME=/u01/oracle)
(GLOBAL_DBNAME=PROD)))
ADR_BASE_LISTENER = /grid
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
8，auxiliary資料庫使用修改過的pfile啟動資料庫到nomount狀態，並且quit推出。
$ sqlplus / as sysdba
SYS@PROD >startup nomount pfile=/home/oracle/pfile;
SYS@PROD >exit
9，使用rman登入auxiliary資料庫
[oracle@single ~]$ rman auxiliary /
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Oct 8 06:26:36 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to auxiliary database: PROD (not mounted)
10，使用RMAN執行duplicate複製災備資料庫
在這一步需要從生產庫alter database backup controlfile to trace匯出指令碼，在使用select value from v$diag_info找到該trace的路徑。將生成控制檔案指令碼中的資料庫檔案路徑進行參考來寫入set newname for datafile n，因為生產端的資料檔案在叢集人檔案系統上，而災備端資料儲存在ASM上。所以需要set newname來修改路徑
run{
allocate auxiliary channel dup1 type disk;
set newname for datafile 1 to '+DATA';
set newname for datafile 2 to '+DATA';
set newname for datafile 3 to '+DATA';
set newname for datafile 4 to '+DATA';
set newname for tempfile 1 to '+DATA';
duplicate target database to PROD backup location '/home/oracle'
logfile
group 1('+DATA') size 50m reuse,
group 2('+DATA') size 50m reuse,
group 3('+DATA') size 50m reuse;
}
螢幕輸出內容較多，放在文章末尾供參考
11，驗證auxiliary資料庫的複製結果，對比之前的檔案系統目標庫
SYS@PROD >select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/prod/datafile/system.264.860394501
+DATA/prod/datafile/sysaux.280.860394525
+DATA/prod/datafile/undotbs1.279.860394525
+DATA/prod/datafile/users.281.860394501
SYS@PROD >select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/prod/tempfile/temp.259.860394555
SYS@PROD >select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DATA/prod/onlinelog/group_3.296.860394553
+DATA/prod/onlinelog/group_2.287.860394553
+DATA/prod/onlinelog/group_1.269.860394553
+FRA/prod/onlinelog/group_1.257.860394553
+FRA/prod/onlinelog/group_2.273.860394553
+FRA/prod/onlinelog/group_3.274.860394553
6 rows selected.
SYS@PROD >show parameter control_file;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DATA/prod/controlfile/control
01.ctl, +FRA/prod/controlfile/
control02.ctl
SYS@PROD >select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
PROD READ WRITE
SYS@PROD >select * from t1;
ID NAME
---------- ----------
1 007
12，auxiliary資料庫從記憶體中建立引數檔案到ASM磁碟組+DATA/PROD
SYS@PROD >create spfile='+DATA/PROD/spfilePROD.ora' from memory;
在ASM磁碟組中生成了手工建立的spfile檔案
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfilePROD.ora
13，建立auxiliary的pfile檔案，指定spfile所在路徑.紅色字型內容是需要新增的。
$ vi $ORACLE_HOME/dbs/initPROD.ora
SPFILE='+DATA/PROD/spfilePROD.ora’
$ cat $ORACLE_HOME/dbs/initPROD.ora
SPFILE='+DATA/PROD/spfilePROD.ora'
14，使用新建立的initPROD.ora來啟動auxiliary資料庫
SYS@PROD >shutdown immediate;
SYS@PROD >startup;
SYS@PROD >show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/prod/spfileprod.ora
本次試驗圓滿成功！
duplicate複製auxiliary資料庫的螢幕輸出如下：
RMAN> run{
2> allocate auxiliary channel dup1 type disk;
3> set newname for datafile 1 to '+DATA';
4> set newname for datafile 2 to '+DATA';
5> set newname for datafile 3 to '+DATA';
6> set newname for datafile 4 to '+DATA';
7> set newname for tempfile 1 to '+DATA';
8> duplicate target database to PROD backup location '/home/oracle'
9> logfile
10> group 1('+DATA') size 50m reuse,
11> group 2('+DATA') size 50m reuse,
12> group 3('+DATA') size 50m reuse;
13> }
allocated channel: dup1
channel dup1: SID=24 device type=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting Duplicate Db at 2014-10-08 06:28:01
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 580395008 bytes
Fixed Size 2255392 bytes
Variable Size 411043296 bytes
Database Buffers 163577856 bytes
Redo Buffers 3518464 bytes
allocated channel: dup1
channel dup1: SID=24 device type=DISK
contents of Memory Script:
{
sql clone "alter system set db_name =
''PROD'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''PROD'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from '/home/oracle/full_03pkgtf4_1_1.bak';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 580395008 bytes
Fixed Size 2255392 bytes
Variable Size 411043296 bytes
Database Buffers 163577856 bytes
Redo Buffers 3518464 bytes
allocated channel: dup1
channel dup1: SID=24 device type=DISK
Starting restore at 2014-10-08 06:28:12
channel dup1: restoring control file
channel dup1: restore complete, elapsed time: 00:00:03
output file name=+DATA/prod/controlfile/control01.ctl
output file name=+FRA/prod/controlfile/control02.ctl
Finished restore at 2014-10-08 06:28:15
database mounted
contents of Memory Script:
{
set until scn 963961;
set newname for datafile 1 to
"+DATA";
set newname for datafile 2 to
"+DATA";
set newname for datafile 3 to
"+DATA";
set newname for datafile 4 to
"+DATA";
restore
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 2014-10-08 06:28:19
channel dup1: starting datafile backup set restore
channel dup1: specifying datafile(s) to restore from backup set
channel dup1: restoring datafile 00001 to +DATA
channel dup1: restoring datafile 00004 to +DATA
channel dup1: reading from backup piece /home/oracle/full_01pkgtea_1_1.bak
channel dup1: piece handle=/home/oracle/full_01pkgtea_1_1.bak tag=SH_DB
channel dup1: restored backup piece 1
channel dup1: restore complete, elapsed time: 00:00:25
channel dup1: starting datafile backup set restore
channel dup1: specifying datafile(s) to restore from backup set
channel dup1: restoring datafile 00002 to +DATA
channel dup1: restoring datafile 00003 to +DATA
channel dup1: reading from backup piece /home/oracle/full_02pkgtea_1_1.bak
channel dup1: piece handle=/home/oracle/full_02pkgtea_1_1.bak tag=SH_DB
channel dup1: restored backup piece 1
channel dup1: restore complete, elapsed time: 00:00:15
Finished restore at 2014-10-08 06:29:00
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=860394540 file name=+DATA/prod/datafile/system.264.860394501
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=860394540 file name=+DATA/prod/datafile/sysaux.280.860394525
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=860394540 file name=+DATA/prod/datafile/undotbs1.279.860394525
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=860394540 file name=+DATA/prod/datafile/users.281.860394501
contents of Memory Script:
{
set until scn 963961;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 2014-10-08 06:29:00
starting media recovery
channel dup1: starting archived log restore to default destination
channel dup1: restoring archived log
archived log thread=1 sequence=4
channel dup1: reading from backup piece /home/oracle/arch_05pkgtf7_1_1.bak
channel dup1: piece handle=/home/oracle/arch_05pkgtf7_1_1.bak tag=SH_ARCH
channel dup1: restored backup piece 1
channel dup1: restore complete, elapsed time: 00:00:01
archived log file name=+FRA/prod/archivelog/2014_10_08/thread_1_seq_4.257.860394541 thread=1 sequence=4
channel clone_default: deleting archived log(s)
archived log file name=+FRA/prod/archivelog/2014_10_08/thread_1_seq_4.257.860394541 RECID=1 STAMP=860394540
media recovery complete, elapsed time: 00:00:00
Finished recover at 2014-10-08 06:29:01
Oracle instance started
Total System Global Area 580395008 bytes
Fixed Size 2255392 bytes
Variable Size 411043296 bytes
Database Buffers 163577856 bytes
Redo Buffers 3518464 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''PROD'' 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 = ''PROD'' 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 580395008 bytes
Fixed Size 2255392 bytes
Variable Size 411043296 bytes
Database Buffers 163577856 bytes
Redo Buffers 3518464 bytes
allocated channel: dup1
channel dup1: SID=24 device type=DISK
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "PROD" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '+DATA' ) SIZE 50 M REUSE,
GROUP 2 ( '+DATA' ) SIZE 50 M REUSE,
GROUP 3 ( '+DATA' ) SIZE 50 M REUSE
DATAFILE
'+DATA/prod/datafile/system.264.860394501'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
set newname for tempfile 1 to
"+DATA";
switch clone tempfile all;
catalog clone datafilecopy "+DATA/prod/datafile/sysaux.280.860394525",
"+DATA/prod/datafile/undotbs1.279.860394525",
"+DATA/prod/datafile/users.281.860394501";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +DATA in control file
cataloged datafile copy
datafile copy file name=+DATA/prod/datafile/sysaux.280.860394525 RECID=1 STAMP=860394553
cataloged datafile copy
datafile copy file name=+DATA/prod/datafile/undotbs1.279.860394525 RECID=2 STAMP=860394553
cataloged datafile copy
datafile copy file name=+DATA/prod/datafile/users.281.860394501 RECID=3 STAMP=860394553
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=860394553 file name=+DATA/prod/datafile/sysaux.280.860394525
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=860394553 file name=+DATA/prod/datafile/undotbs1.279.860394525
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=860394553 file name=+DATA/prod/datafile/users.281.860394501
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 2014-10-08 06:29:16
released channel: dup1
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29047826/viewspace-1297065/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 將資料庫從ASM遷移到檔案系統資料庫ASM
- 將資料庫從檔案系統遷移到ASM資料庫ASM
- 利用RMAN將資料庫從檔案系統遷移到ASM資料庫ASM
- 資料庫從檔案系統遷移到ASM資料庫ASM
- 採用DUPLICATE 把asm資料庫複製到檔案系統ASM資料庫
- 使用RAM將檔案系統遷移到ASMASM
- 利用RMAN將資料庫從檔案系統遷移到ASM(單例項)資料庫ASM單例
- 將spfile從ASM裡遷移到檔案系統ASM
- 把檔案系統的資料檔案遷移到ASM儲存ASM
- 利用rman將本地資料檔案遷移到asmASM
- 實現資料庫由檔案系統遷移到 ASM 磁碟組中資料庫ASM
- oracle 將表空間下的資料檔案從檔案系統遷移到ASM磁碟組OracleASM
- 從檔案系統遷移到ASM上ASM
- oralce 從檔案系統遷移到ASMASM
- 資料庫遷移到ASM資料庫ASM
- 用rman從檔案系統遷移資料庫到asm資料庫ASM
- 將資料庫遷移到ASM的實驗記錄資料庫ASM
- 使用rman將資料庫遷移到ASM例項資料庫ASM
- 四、用rman從檔案系統遷移資料庫到asm資料庫ASM
- 使用rman copy將資料庫遷移到ASM例項資料庫ASM
- 使用RMAN遷移檔案系統資料庫到ASM資料庫ASM
- 將ORACLE資料庫資料檔案遷移到其他目錄(ZT)Oracle資料庫
- 如何遷移ASM資料檔案到檔案系統ASM
- 使用資料庫冷備份方式進行資料庫遷移,資料庫檔案遷移到不同的目錄資料庫
- 三種ASM下將資料檔案由dg遷移到dg的方法ASM
- Oracle 11g單例項ASM遷移到檔案系統Oracle單例ASM
- 遷移資料庫檔案到ASM資料庫ASM
- oracle將控制檔案從裸裝置遷移到檔案系統Oracle
- 將pentaho資料庫遷移到oracle資料庫資料庫Oracle
- 利用RMAN在檔案系統與ASM之間遷移資料庫ASM資料庫
- ASM檔案系統遷移ASM
- asm 檔案系統遷移ASM
- 【資料遷移】RMAN遷移資料庫到ASM(二)切換資料檔案到ASM資料庫ASM
- ASM與檔案系統之間copy資料檔案--檔案系統到ASMASM
- 線上過期資料遷移到離線資料庫資料庫
- 把64位的unix系統資料庫遷移到32位的windows系統資料庫中資料庫Windows
- 遷移到ASMASM
- 一、rman 資料庫遷移--從檔案系統到檔案系統用預設的備份路徑資料庫