asm和file system之間資料檔案的轉換

<b>how to move a datafile from a file system</b>

to asm<b></b>

moving a datafile from the file system can be achived in two ways.

i. while the database is shutdown (in mount stage).

ii. while the database is running (with the selected tablespace offline).

(資料檔案實作檔案系統到asm的轉換遷移，一是在資料庫mount狀态，二是在open狀态，但資料檔案所屬表空間需要offline狀态)

-----------------------------------------------------------------------------------------------

<b>i. while the database is shutdown (in mount stage).</b>

（1、mount狀态）

moving oracle datafile while the database is in mount stage is performed in the

following way:

1. shutdown and mount the database.<b></b>

[oracle@linux] sqlplus '/as sysdba'

sql&gt; shutdown immediate;

sql&gt; startup mount;

2. ensure you have enough space in the asm diskgroup to copy the datafile.

first identify the size of the datafile you wish to move.

在asm上要有足夠的空間

sql&gt; select file#, name, (bytes/1048576)

file_size_mb from v$datafile;

file# name file_size_mb

----- ---------------------------- --------------

...

4 /oradata/prod/users01.dbf 2500

* in this example we will be moving users01.dbf

[oracle@linux] export oracle_sid=+asm

sql&gt; select name, state, total_mb, free_mb from v$asm_diskgroup;

name state total_mb free_mb

------------------------------ ----------- ---------- ----------

dgroup1 mounted 100 3

dgroup2 mounted 4882 4830

3. connect to rman and copy the datafile from the filesystem to the select asm

diskgroup.

用rman copy 資料檔案

[oracle@linux] rman target=/

rman&gt; copy datafile 4 to '+dgroup2'

starting backup at 2006/09/05 12:14:23

using target database controlfile instead of recovery catalog

allocated channel: ora_disk_1

channel ora_disk_1: sid=31 devtype=disk

channel ora_disk_1: starting datafile copy

input datafile fno=00004 name=/oradata/prod/users01.dbf

output filename=+dgroup2/prod/datafile/users01.258.600351265

tag=tag20060905t121424 recid=10 stamp=600351264

channel ora_disk_1: datafile copy complete, elapsed time: 00:05:01

finished backup at 2006/09/05 12:19:24

4. update the controlfile with the new location of the datafile.

更新控制檔案中的資料檔案新的檔案位置

[oracle@linux] rman target /

rman&gt; switch datafile 4 to copy;

datafile 4 switched to datafile copy

"+dgroup2/prod/datafile/users01.258.600351265".

5. the file is now if the new location.

在資料庫中檢視，檔案位置已經更新

sql&gt; select name from v$datafile;

name

--------------------------------------------------------------------------------

..

+dgroup2/prod/datafile/users01.258.600351265

6. the database may now be opened.

open 資料庫。

<b>while the database is running (with the select tablespace offline).</b>

在open狀态下轉換

in order to move a datafile on a running active database the tablespace where

the datafile resides must be placed offline.

1. identify the tablespace which contains the datafile and offline the

tablespace.

確定表空間offline

sql&gt; select tablespace_name, file_name

from dba_data_files where file_id=4;

tablespace_name file_name

------------------ ------------------------------

users /oradata/rman/users01.dbf

sql&gt; alter tablespace users offline;

* * * * * continue with steps 2 - 5 above. *

* * * *

以上步驟2-5相同

6. after you have successfully completed the

above steps (2 -5) place the

tablespace online;

sql&gt; alter tablespace users online;