資料庫從檔案系統轉移至ASM實驗記錄
實驗環境：WinXP SP2
資料庫版本：10.2.0.1
準備遷移的資料庫例項名：TEST
ASM例項名：+ASM
ASM磁碟組：+TEST
建立ASM例項和磁碟組的步驟這裡不再重複，請參考：http://woodnan.itpub.net/post/37055/458037
注：由於itpub的blog會遮蔽反斜槓，以下反斜槓都用斜槓代替了
[@more@]Microsoft Windows XP [版本 5.1.2600]
(C) 版權所有 1985-2001 Microsoft Corp.
C:/WINDOWS>set ORACLE_SID=test
C:/WINDOWS>sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 27 20:56:54 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
1、修改引數檔案中的control_files引數，指向ASM
TEST>show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string F:/ORACLE/PRODUCT/ORADATA/TEST/CONTROL01.CTL, F:/ORACLE/PRODUCT/ORADATA/TEST/CONTROL02.CTL, F:/ORACLE/PRODUCT/ORADATA/TEST/CONTROL03.CTL
TEST>alter system set control_files ='+test/test/control01.ctl' scope=spfile;
System altered.
修改完成後關閉資料庫
TEST>shut immediate
2、使用RMAN將controlfile遷移至ASM
Microsoft Windows XP [版本 5.1.2600]
(C) 版權所有 1985-2001 Microsoft Corp.
C:/WINDOWS>rman
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Mar 27 20:58:22 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount
Oracle instance started
Total System Global Area 209715200 bytes
Fixed Size 1248116 bytes
Variable Size 71304332 bytes
Database Buffers 130023424 bytes
Redo Buffers 7139328 bytes
RMAN> restore controlfile from 'F:/ORACLE/PRODUCT/ORADATA/TEST/CONTROL01.CTL';
Starting restore at 27-MAR-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/27/2008 21:02:46
ORA-19504: failed to create file "+TEST/test/control01.ctl"
ORA-17502: ksfdcre:3 Failed to create file +TEST/test/control01.ctl
ORA-15001: diskgroup "TEST" does not exist or is not mounted
ORA-15077: could not locate ASM instance serving a required diskgroup
ORA-19600: input file is control file (F:/ORACLE/PRODUCT/ORADATA/TEST/CONTROL01.CTL)
ORA-19601: output file is control file (+TEST/test/control01.ctl)
出現了錯誤：提示磁碟組TEST不存在，連入ASM例項看看為什麼
Microsoft Windows XP [版本 5.1.2600]
(C) 版權所有 1985-2001 Microsoft Corp.
C:/WINDOWS>set ORACLE_SID=+asm
C:/WINDOWS>sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 27 21:04:33 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
原來是上次做試驗，把磁碟組刪除後沒有重建-.-!
+ASM>select * from v$asm_diskgroup;
no rows selected
重新建立磁碟組
+ASM>create diskgroup test normal redundancy
2 failgroup controller1 disk 'H:/asmDISKS/_FILE_DISK1','H:/asmDISKS/_FILE_DISK2'
3 failgroup controller2 disk 'H:/asmDISKS/_FILE_DISK3','H:/asmDISKS/_FILE_DISK4';
Diskgroup created.
回到RMAN中，再次執行成功
RMAN> restore controlfile from 'F:/ORACLE/PRODUCT/ORADATA/TEST/CONTROL01.CTL';
Starting restore at 27-MAR-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=+TEST/test/control01.ctl
Finished restore at 27-MAR-08
這時候control file已經遷移成功，啟動至mount
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
3、備份全部資料檔案到ASM
RMAN> backup as copy database format '+test';
Starting backup at 27-MAR-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=152 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=F:/ORACLE/PRODUCT/ORADATA/TEST/SYSTEM01.DBF
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/27/2008 21:07:21
ORA-19504: failed to create file "+TEST"
ORA-17502: ksfdcre:4 Failed to create file +TEST
ORA-15041: diskgroup space exhausted
出現錯誤：提示磁碟組的空間不足，磁碟組用的是4個200m的模擬磁碟，又使用了normal redundancy冗餘模式，造成了空間不足，（嘗試將冗餘模式改為extenal redundancy也不夠），再新增4塊200m的模擬磁碟
+ASM>alter diskgroup test add disk 'H:/asmDISKS/_FILE_DISK5','H:/asmDISKS/_FILE_DISK6','H:/asmDISKS/_FILE_DISK7','H:/asmDISKS/_FILE_DISK8';
Diskgroup altered.
可以看到新增磁碟後，ASM例項自動進行了rebalance
+ASM>select * from v$asm_operation;
GROUP_NUMBER OPERATION STATE POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES
------------ ---------- -------- ---------- ---------- ---------- ---------- ---------- -----------
1 REBAL RUN 1 1 1 226 0 0
再次備份整個資料庫到ASM，成功完成
RMAN> backup as copy database format '+test';
Starting backup at 27-MAR-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=F:/ORACLE/PRODUCT/ORADATA/TEST/SYSTEM01.DBF
output filename=+TEST/test/datafile/system.258.650496175 tag=TAG20080327T212252 recid=3 stamp=650496208
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=F:/ORACLE/PRODUCT/ORADATA/TEST/SYSAUX01.DBF
output filename=+TEST/test/datafile/sysaux.257.650496219 tag=TAG20080327T212252 recid=4 stamp=650496238
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=F:/ORACLE/PRODUCT/ORADATA/TEST/UNDOTBS01.DBF
output filename=+TEST/test/datafile/undotbs1.259.650496245 tag=TAG20080327T212252 recid=5 stamp=650496246
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=F:/ORACLE/PRODUCT/ORADATA/TEST/USERS01.DBF
output filename=+TEST/test/datafile/users.260.650496247 tag=TAG20080327T212252 recid=6 stamp=650496248
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+TEST/test/controlfile/backup.261.650496251 tag=TAG20080327T212252 recid=7 stamp=650496252
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=F:/ORACLE/PRODUCT/ORADATA/TEST/TEST_BIG.DBF
output filename=+TEST/test/datafile/test_big.262.650496255 tag=TAG20080327T212252 recid=8 stamp=650496254
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 27-MAR-08
channel ORA_DISK_1: finished piece 1 at 27-MAR-08
piece handle=+TEST/test/backupset/2008_03_27/nnsnf0_tag20080327t212252_0.263.650496257 tag=TAG20080327T212252 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 27-MAR-08
4、使用10g的新特性，切換資料庫到剛才備份到ASM的備份上，至此datafile和controlfile的遷移已經完成了
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+TEST/test/datafile/system.258.650496175"
datafile 2 switched to datafile copy "+TEST/test/datafile/undotbs1.259.650496245"
datafile 3 switched to datafile copy "+TEST/test/datafile/sysaux.257.650496219"
datafile 4 switched to datafile copy "+TEST/test/datafile/users.260.650496247"
datafile 5 switched to datafile copy "+TEST/test/datafile/test_big.262.650496255"
RMAN>
5、Redo log還在檔案系統上，也需要進行遷移
TEST>alter database open;
Database altered.
在ASM中建立3組新的redo log
TEST>alter database add logfile group 4 '+test/redo04.log' size 10m;
Database altered.
TEST>alter database add logfile group 5 '+test/redo05.log' size 10m;
Database altered.
TEST>alter database add logfile group 6 '+test/redo06.log' size 10m;
Database altered.
檢視6組redolog的狀態，可以看到當前使用的是第3組
TEST>select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 CURRENT
4 UNUSED
5 UNUSED
6 UNUSED
因為要刪除1-3組redolog，先進行redo log的切換
TEST>alter system switch logfile;
System altered.
TEST>/
System altered.
TEST>/
System altered.
可以看到當前使用的是第6組
TEST>select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 INACTIVE
3 INACTIVE
4 ACTIVE
5 ACTIVE
6 CURRENT
但是第1組redo log的狀態仍然為ACTIVE，手工執行一次checkpoing
TEST>alter system checkpoint;
1-5組redo log的狀態都為INACTIVE了
TEST>select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 INACTIVE
4 INACTIVE
5 INACTIVE
6 CURRENT
刪除1-3組redo log
TEST>alter database drop logfile group 1;
Database altered.
TEST>alter database drop logfile group 2;
Database altered.
TEST>alter database drop logfile group 3;
Database altered.
6、目前臨時表空間也還在檔案系統上，也需要進行遷移
檢視目前資料庫中tempfile的位置
TEST>select file_name,tablespace_name from dba_temp_files;
FILE_NAME TABLESPACE_NAME
--------------------------------------------- -----------------------
F:/ORACLE/PRODUCT/ORADATA/TEST/TEMP01.DBF TEMP
在temp表空間中新新增一個在ASM中的tempfile
TEST>alter tablespace temp add tempfile '+test/temp01.dbf' size 30m;
Tablespace altered.
刪除原來在檔案系統中的tempfile
TEST>alter tablespace temp drop tempfile 'F:/ORACLE/PRODUCT/ORADATA/TEST/TEMP01.DBF';
Tablespace altered.
7、目前系統只有一個controlfile，為了保證系統的安全性，再新增一個controlfile
orcl>shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
idle>startup mount
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 1248116 bytes
Variable Size 71304332 bytes
Database Buffers 130023424 bytes
Redo Buffers 7139328 bytes
Database mounted.
將controlfile備份到ASM中（和目前的controlfile是一樣的，可以直接使用）
TEST>alter database backup controlfile to '+test';
Database altered.
修改引數檔案中的control_files引數
TEST>show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +TEST/test/control01.ctl
TEST>alter system set control_files='+TEST/test/control01.ctl','+TEST/test/control02.ctl' scope=spfile;
System altered.
使用asmcmd給備份至ASM的controlfile起個別名，以方便使用
C:/WINDOWS>set ORACLE_HOME=F:/oracle/product/10.2.0
C:/WINDOWS>set ORACLE_SID=+asm
C:/WINDOWS>asmcmd
ASMCMD> ls
BACKUPSET/
CONTROLFILE/
DATAFILE/
ONLINELOG/
TEMPFILE/
control01.ctl
ASMCMD> cd controlfile
ASMCMD> ls -l
Type Redund Striped Time Sys Name
CONTROLFILE UNPROT FINE MAR 27 22:00:00 Y Backup.268.650498443
CONTROLFILE UNPROT FINE MAR 27 22:00:00 Y backup.256.650495709
ASMCMD> mkalias +TEST/TEST/CONTROLFILE/Backup.268.650498443 control02.ctl
ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y BACKUPSET/
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y TEMPFILE/
N control01.ctl => +TEST/TEST/CONTROLFILE/backup.256.650495709
N control02.ctl => +TEST/TEST/CONTROLFILE/Backup.268.650498443
ASMCMD>
8、重啟資料，整個資料庫已成功遷移至ASM
TEST>startup force
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 1248116 bytes
Variable Size 71304332 bytes
Database Buffers 130023424 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/175187/viewspace-1001641/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 資料庫從檔案系統轉移至ASM實驗記錄[zt]資料庫ASM
- 【原創】資料庫從檔案系統轉移至ASM實驗記錄資料庫ASM
- 資料庫從檔案系統遷移到ASM資料庫ASM
- 將資料庫從ASM遷移到檔案系統資料庫ASM
- 將資料庫從檔案系統遷移到ASM資料庫ASM
- 移動資料檔案從ASM到檔案系統ASM
- 用rman從檔案系統遷移資料庫到asm資料庫ASM
- 利用RMAN將資料庫從檔案系統遷移到ASM資料庫ASM
- 將資料庫遷移到ASM的實驗記錄資料庫ASM
- ASM下資料檔案遷移至不同磁碟組小記ASM
- 四、用rman從檔案系統遷移資料庫到asm資料庫ASM
- oracle之 RAC本地資料檔案遷移至ASMOracleASM
- ASM與檔案系統之間copy資料檔案--檔案系統到ASMASM
- 移動資料檔案從檔案系統到ASM磁碟組中ASM
- 利用RMAN將資料庫從檔案系統遷移到ASM(單例項)資料庫ASM單例
- 實現資料庫由檔案系統遷移到 ASM 磁碟組中資料庫ASM
- 使用RMAN遷移檔案系統資料庫到ASM資料庫ASM
- 【RAC】RAC本地資料檔案遷移至ASM的方法(3)ASM
- 【RAC】RAC本地資料檔案遷移至ASM的方法(2)ASM
- 【RAC】RAC本地資料檔案遷移至ASM的方法(1)ASM
- 如何遷移ASM資料檔案到檔案系統ASM
- 從檔案系統遷移到ASM上ASM
- oralce 從檔案系統遷移到ASMASM
- 採用DUPLICATE 把asm資料庫複製到檔案系統ASM資料庫
- 調整資料庫的資料檔案記錄資料庫
- Oracle檔案改名實驗記錄Oracle
- 使用shell指令碼及asm cp或RMAN copy批量將資料檔案從ASM拷貝到檔案系統指令碼ASM
- 把檔案系統的資料檔案遷移到ASM儲存ASM
- 利用RMAN在檔案系統與ASM之間遷移資料庫ASM資料庫
- 資料庫系統檔案啟動資料庫資料庫
- oracle 將表空間下的資料檔案從檔案系統遷移到ASM磁碟組OracleASM
- 從ASM磁碟中複製檔案到本地檔案系統ASM
- 遷移資料庫檔案到ASM資料庫ASM
- 將spfile從ASM裡遷移到檔案系統ASM
- 【ASM】ASM資料檔案和OS檔案（FILESYSTEM）轉移方法總結ASM
- 10G下從ASM複製檔案到檔案系統ASM
- oracle實驗記錄 (恢復,備份-含壞塊資料檔案)Oracle
- oracle實驗記錄（恢復-丟失未備份資料檔案）Oracle