如何移動asm磁碟組內的資料檔案到另外一個磁碟組
1) Identify the data file to be moved.
2) Identify the diskgroup on to which the file has to be moved.
3) Take the file offline.
4) Copy the file to new diskgroup using Either RMAN or DBMS_FILE_TRANSFER.
5) Rename the file to point to new location.
6) Recover the file.
7) Bring the file online.
8) Verify the new file locations.
9) Delete the file from its original location.
1) Identify the data file to be moved.
----------------------------------------
In database instance
SQL:ORCL> SELECT FILE_NAME FROM DBA_DATA_FILES:
+ASMDSK2/orcl/datafile/users.256.565313879 <======= Move this to ASDSK1.
+ASMDSK1/orcl/sysaux01.dbf
+ASMDSK1/orcl/undotbs01.dbf
+ASMDSK1/orcl/system01.dbf
2) Identify the diskgroup on to which the file has to be moved.
--------------------------------------------------------------
In ASM instance
SQL:ASM> SELECT GROUP_NUMBER, NAME FROM V$ASM_DISKGROUP;
GROUP_NUMBER NAME
------------ ---------
1 ASMDSK1
2 ASMDSK2
3) Take the file offline.
--------------------------
SQL:ORCL> ALTER DATABASE DATAFILE '+ASMDSK2/orcl/datafile/users.256.565313879' OFFLINE;
4)Now Copy the file from Source diskgroup ASMDSK1 to target Diskgroup ASMDSK2.
--------------------------------------------------------------------------------------------
Either
4. a) DBMS_FILE_TRANSFER package or
4. b) RMAN
can be used for this step.
( The step 5 to step 8 is based on the filenames from method b).
4.a).Using DBMS_FILE_TRANSFER package
SQL:ORCL>create or replace directory orcl1 as '+asmdsk1/orcl/datafile';
SQL:ASM> Alter disgroup asmdsk2 add directory '+asmdsk2/test';
SQL:ORCL> create or replace directory orcl2 as '+asmdsk2/test';
SQL:ORCL>
BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object => 'ORCL1',
source_file_name => 'users.259.565359071',
destination_directory_object => 'ORCL2',
destination_file_name => 'USERS01.DBF');
END; Database altered.
4 b).Using RMAN copy the file to new diskgroup.
$ rman target system@orcl10
target database Password:
connected to target database: ORCL (DBID=1089529226)
RMAN>
RMAN> COPY DATAFILE '+ASMDSK2/orcl/datafile/users.256.565313879' TO '+ASMDSK1';
Starting backup at 03-AUG-05
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+ASMDSK2/orcl/datafile/users.256.565313879
output filename=+ASMDSK1/orcl/datafile/users.259.565359071 tag=TAG20050803T12110
9 recid=2 stamp=565359071
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 03-AUG-05
5) Rename the file to point to new location.
-------------------------------------------
If you have used DBMS_FILE_TRANSFER (method 4 a)) use the following command to rename:
SQL:ORCL> ALTER DATABASE RENAME FILE '+ASMDSK2/orcl/datafile/users.256.565313879' TO
'+ASMDSK1/orcl/datafile/users.259.565359071'
Database altered.
If you have used RMAN (method 4 b) use the following option of RMAN
RMAN run {
set newname for datafile '+ASMDSK2/orcl/datafile/users.256.565313879'
to '+ASMDSK1/orcl/datafile/users.259.565359071' ;
switch datafile all;
}
6) Recover the file.
-------------------
SQL:ORCL> RECOVER DATAFILE '+ASMDSK1/orcl/datafile/users.259.565359071'
Media recovery complete.
7) Bring the file online.
-----------------------
SQL:ORCL>ALTER DATABASE DATAFILE '+ASMDSK1/orcl/datafile/users.259.565359071' ONLINE
Database altered.
8) Verify the new file location.
---------------------------------
SQL:ORCL> SELECT FILE_NAME FROM DBA_DATA_FILES;
FILE_NAME
-------------------------------------------------------------------------------
+ASMDSK1/orcl/datafile/users.259.565359071
+ASMDSK1/orcl/sysaux01.dbf
+ASMDSK1/orcl/undotbs01.dbf
+ASMDSK1/orcl/system01.dbf
9) Delete the file from its original location either per SQLPLUS or per ASMCMD:
e.g.: SQL:ASM> ALTER DISKGROUP ASMDSK2 DROP FILE users.256.565313879;
or: ASMCMD> rm -rf <filename>
Note:
Most Automatic Storage Management files do not need to be manually
deleted because, as Oracle managed files, they are removed
automatically when they are no longer needed.
However, if
you need to drop an Oracle Managed File (OMF) manually you should use
the fully qualified filename if you reference the file. Otherwise you
will get an error (e.g. ORA-15177).
e.g.: SQL:ASM> ALTER DISKGROUP ASMDSK2 DROP FILE '+ASMDSK2/orcl/datafile/users.256.565313879';
Note: The steps provided above assume that the database is open and in Archivelog mode.
--以上步驟是在資料庫開啟狀態並且是歸檔模式
如果要遷移system和sysaux表空間下的資料檔案到另外一個磁碟組,則需要啟動資料庫到mount狀態
以下是相關步驟:
1. Create a Copy of datafile in target Diskgroup:
RMAN> backup as copy tablespace system format '<New DG>';
RMAN> backup as copy tablespace sysaux format '<New DG>';
2. Then shutdown the database and restart to a mounted state
RMAN> shutdown immediate;
RMAN> startup mount;
3. switch the datafiles to the copy
RMAN> switch tablespace system to copy;
RMAN> switch tablespace sysaux to copy;
4. Recover the changes made to these tablespaces;
RMAN> recover database;
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/7199859/viewspace-2123720/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 移動資料檔案從檔案系統到ASM磁碟組中ASM
- 在ASM磁碟組之間移動檔案ASM
- 【資料遷移】RMAN遷移資料庫到ASM(一)建立ASM磁碟組資料庫ASM
- 使用rman在oracle ASM磁碟組之間移動資料檔案OracleASM
- 遷移ASM磁碟組ASM
- 用oracle amdu 抽取asm磁碟組的資料檔案OracleASM
- asm 磁碟組 增刪磁碟組ASM
- 在ASM磁碟組中刪除一個磁碟ASM
- ORACLE 資料庫 ASM磁碟組上新增控制檔案Oracle資料庫ASM
- oracle asm 儲存 a磁碟組中的資料檔案 遷移到b磁碟組實施步驟OracleASM
- Windows 下使用檔案模擬磁碟配置ASM磁碟組WindowsASM
- ASM下資料檔案遷移至不同磁碟組小記ASM
- ASM磁碟組限制ASM
- 資料檔案遷移至其他磁碟組
- ASM之磁碟組中目錄檔案的管理ASM
- 11G RAC 為 ASM 磁碟組增加一個磁碟。(AIX)ASMAI
- RAC資料庫新增ASM磁碟組(1)資料庫ASM
- 遷移ocr/votedisk/asm spfile所在磁碟組ASM
- 11.2刪除第一個ASM磁碟組ASM
- 【ASM】Oracle asm磁碟被格式化,如何掛載該磁碟組ASMOracle
- 零當機時間遷移 ASM 磁碟組到另一個 SAN/磁碟陣列/DAS 的準確步驟ASM陣列
- ASM磁碟組不能自動掛載ASM
- ASM磁碟組更換磁碟的操作方法ASM
- 實現資料庫由檔案系統遷移到 ASM 磁碟組中資料庫ASM
- 一次ASM新增新的磁碟組ASM
- 修改ASM磁碟組的屬性ASM
- ORACLE RAC重建ASM磁碟組OracleASM
- ASM磁碟組修改重建操作ASM
- ASM磁碟組空間不足ASM
- 恢復表空間到不同的ASM磁碟組ASM
- ASM 啟動時預設mount 的磁碟組ASM
- asm磁碟組建立錯誤,用中轉儲存,重建磁碟組ASM
- oracle 將表空間下的資料檔案從檔案系統遷移到ASM磁碟組OracleASM
- asm磁碟組依賴導致資料庫自啟動報錯ASM資料庫
- 新增磁碟多連路磁碟併為ASM磁碟組擴容ASM
- 移動資料檔案,平衡磁碟負載負載
- 【MOS】零當機遷移ASM磁碟組到另一個SAN/磁碟陣列/DAS的準確步驟 (文件 ID 1946664.1)ASM陣列
- ASM 磁碟組的建立及擴容ASM