【原創】ASM下的資料檔案轉換為普通檔案

發現很多文章都是介紹如何將普通檔案裝換為ASM下的資料檔案的，介紹ASM下的檔案轉換為普通檔案的較少。下面的實驗就是介紹如何將ASM的檔案轉換為普通檔案的，主要使用了RMAN和ASMCMD工具。

注：此實驗的為SYSAUX表空間。如果將整個資料庫都從ASM中轉移出來，其他表空間的操作類似，不過線上日誌、Undo表空間、臨時表空間重建即可，不需要轉移出來。

具體實驗步驟
1.使用RMAN將需要轉換的檔案+TEST/test/datafile/sysaux.257.650496219備份為F:\sysaux01.dbf
C:\WINDOWS>rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jun 20 23:32:26 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: T (DBID=544433466)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    540      SYSTEM               ***     +TEST/test/datafile/system.258.650496175
2    140      UNDOTBS1             ***     +TEST/test/datafile/undotbs1.259.650496245
3    370      SYSAUX               ***     +TEST/test/datafile/sysaux.257.650496219
4    5        USERS                ***     +TEST/test/datafile/users.260.650496247
5    1        TEST_BIG             ***     +TEST/test/datafile/test_big.262.650496255
6    10       SYSAUX               ***     F:\ORACLE\PRODUCT\ORADATA\TEST\SYSAUX02.DBF
8    10       TEST                 ***     F:\ORACLE\PRODUCT\ORADATA\TEST\TEST01.DBF
9    10       TEST1                ***     F:\ORACLE\PRODUCT\ORADATA\TEST\TEST02.DBF
10   10       UNDO01               ***     F:\ORACLE\PRODUCT\ORADATA\TEST\UNDO01.DBF
11   10       UNDO02               ***     F:\ORACLE\PRODUCT\ORADATA\TEST\UNDO02.DBF
12   10       TEST03               ***     F:\ORACLE\PRODUCT\ORADATA\TEST\TEST03.DBF

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
2    30       TEMP                 30          +TEST/temp01.dbf

RMAN> copy datafile 3 to 'F:\sysaux01.dbf';

Starting backup at 20-JUN-08
configuration for DISK channel 2 is ignored
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=158 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=+TEST/test/datafile/sysaux.257.650496219
output filename=D:\1.DBF tag=TAG20080620T233341 recid=13 stamp=657934458
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
Finished backup at 20-JUN-08

2.由於操作的是SYSAUX表空間，恢復需要在mount下進行
RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     268435456 bytes

Fixed Size                     1248504 bytes
Variable Size                138412808 bytes
Database Buffers             121634816 bytes
Redo Buffers                   7139328 bytes

3.使用ASMCMD刪除ASM中已有的SYSAUX表空間的資料檔案
Microsoft Windows XP [版本 5.1.2600]
(C) 版權所有 1985-2001 Microsoft Corp.

C:\WINDOWS>set ORACLE_HOME=f:\oracle\product/10.2.0
C:\WINDOWS>set ORACLE_SID=+asm
C:\WINDOWS>asmcmd
ASMCMD> ls
TEST/
ASMCMD> cd test
ASMCMD> ls
TEST/
redo04.log
redo05.log
redo06.log
temp01.dbf
ASMCMD> cd test
ASMCMD> ls
BACKUPSET/
CONTROLFILE/
DATAFILE/
ONLINELOG/
TEMPFILE/
control01.ctl
control02.ctl
ASMCMD> cd datafile
ASMCMD> ls
SYSAUX.257.650496219
SYSTEM.258.650496175
TEST_BIG.262.650496255
UNDOTBS1.259.650496245
USERS.260.650496247
ASMCMD> rm SYSAUX.257.650496219
ASMCMD> ls
SYSTEM.258.650496175
TEST_BIG.262.650496255
UNDOTBS1.259.650496245
USERS.260.650496247
ASMCMD>

4.修改備份的資料檔名（這步也可以不需要，在備份的時候直接備份到需要的位置即可）
從F:\sysaux01.dbf移動至F:\ORACLE\PRODUCT\ORADATA\TEST\SYSAUX01.DB

5.修改資料庫的控制檔案，將原來SYSAUX的資料檔案由ASM下改名到備份的檔案
RMAN> run
2> {set newname for datafile '+TEST/test/datafile/sysaux.257.650496219' to 'F:\oracle\product\oradata\test\sysaux01.dbf';
3> switch datafile all;
4> }

由於移動了備份的檔案的位置（從F:\sysaux01.dbf移動至F:\ORACLE\PRODUCT\ORADATA\TEST\SYSAUX01.DB），而新的檔案沒有catalog，所以報錯了
executing command: SET NEWNAME

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of switch command at 06/20/2008 23:41:02
RMAN-20230: datafile copy not found in the recovery catalog
RMAN-06015: error while looking up datafile copy name: F:\ORACLE\PRODUCT\ORADATA\TEST\SYSAUX01.DBF

6.將移動後的備份資料檔案catalog
RMAN> catalog datafilecopy 'F:\ORACLE\PRODUCT\ORADATA\TEST\SYSAUX01.DBF';

cataloged datafile copy
datafile copy filename=F:\ORACLE\PRODUCT\ORADATA\TEST\SYSAUX01.DBF recid=14 stamp=657934913

7.重新修改資料檔案的位置
RMAN> run
2> {set newname for datafile '+TEST/test/datafile/sysaux.257.650496219' to 'F:\oracle\product\oradata\test\sysaux01.dbf';
3> switch datafile all;
4> }

executing command: SET NEWNAME

datafile 3 switched to datafile copy
input datafile copy recid=14 stamp=657934913 filename=F:\ORACLE\PRODUCT\ORADATA\TEST\SYSAUX01.DBF

8.恢復資料檔案
RMAN> recover datafile 3;

Starting recover at 20-JUN-08
configuration for DISK channel 2 is ignored
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=152 devtype=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 20-JUN-08

9.開啟資料庫，此時SYSAUX表空間的資料檔案已經由ASM下轉移為普通檔案
RMAN> sql 'alter database open';

sql statement: alter database open

RMAN>

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/498744/viewspace-364439/，如需轉載，請註明出處，否則將追究法律責任。