Move datafile:From File System to ASM

一、資料庫關閉(mount狀態).
適用於移動undo或system表空間檔案

1、關閉資料庫並啟動到mount狀態.

[oracle] sqlplus '/as sysdba'

SQL> shutdown immediate;

SQL> startup mount;

2、確保ASM有足夠的空間

SQL> col name for a80
SQL> select file#, name, bytes/1024/1024 Size_MB from v$datafile;

     FILE# NAME                                                                                SIZE_MB
---------- -------------------------------------------------------------------------------- ----------
         1 +DGDATA/dg1/datafile/system.256.815366947                                              2048
         2 +DGDATA/dg1/datafile/sysaux.257.815367079                                              1024
         3 /tmp/oracle/backup/undo01.dbf                                                          1024
         4 /tmp/oracle/backup/users01.dbf                                                          500
         5 /home/oracle/test.dbf                                                                   500

這裡舉例移動undo表空間檔案

[grid@dg1:/home/grid]#asmcmd
ASMCMD> lsdg
State    Type    Rebal Sector Block       AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N         512   4096 1048576      1568     1187                0            1187              0             N DGCRS/
MOUNTED EXTERN N         512   4096 1048576      8628     4989                0            4989              0             N DGDATA/

3、利用RMAN將資料檔案從asm磁碟拷貝到檔案系統

[oracle@dg1:/home/oracle]#rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Aug 20 03:26:58 2013

connected to target database: DG (DBID=1728697911, not open)

RMAN> copy datafile 3 to '+DGDATA';

Starting backup at 20-AUG-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=29 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/tmp/oracle/backup/undo01.dbf
output file name=+DGDATA/dg1/datafile/undotbs1.260.823927355 tag=TAG20130820T044233 RECID=6 STAMP=823927487
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:21
Finished backup at 20-AUG-13

4、修改控制檔案中新檔案的路徑

[oracle] sqlplus '/as sysdba'

SQL> alter database rename file '/tmp/oracle/backup/undo01.dbf' to '+DGDATA/dg1/datafile/undotbs1.260.823927355';

Database altered.

5、檢視新檔案路徑

SQL> col name for a80
SQL> set line 180
SQL> select file#, name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------------------------------------
         1 +DGDATA/dg1/datafile/system.256.815366947
         2 +DGDATA/dg1/datafile/sysaux.257.815367079
         3 +DGDATA/dg1/datafile/undotbs1.260.823927355
         4 /tmp/oracle/backup/users01.dbf
         5 /home/oracle/test.dbf

6、開啟資料庫
SQL> alter database open;

Database altered.

二、資料庫執行(只要將對應的表空間offline).

1、確定要移動的資料檔案，並offline

SQL> col tablespace_name for a40
SQL> col file_name for a80
SQL> select TABLESPACE_NAME,FILE_ID,FILE_NAME from dba_data_files;

TABLESPACE_NAME                             FILE_ID FILE_NAME
---------------------------------------- ---------- --------
SYSTEM                                            1 +DGDATA/dg1/datafile/system.256.815366947
SYSAUX                                            2 +DGDATA/dg1/datafile/sysaux.257.815367079
UNDOTBS1                                          3 +DGDATA/dg1/datafile/undotbs1.260.823927355
USERS                                             4 /tmp/oracle/backup/users01.dbf
TEST                                              5 /home/oracle/test.dbf

SQL> alter tablespace USERS offline;

Tablespace altered.

2、確保ASM有足夠的空間

SQL> col name for a80
SQL> select file#, name, bytes/1024/1024 Size_MB from v$datafile;

     FILE# NAME                                                                                SIZE_MB
---------- -------------------------------------------------------------------------------- ----------
         1 +DGDATA/dg1/datafile/system.256.815366947                                              2048
         2 +DGDATA/dg1/datafile/sysaux.257.815367079                                              1024
         3 +DGDATA/dg1/datafile/undotbs1.258.815367141                                            1024
         4 /tmp/oracle/backup/users01.dbf                                                          500
         5 /home/oracle/test.dbf                                                                   500

這裡舉例移動users表空間檔案

[grid@dg1:/home/grid]#asmcmd
ASMCMD> lsdg
State    Type    Rebal Sector Block       AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N         512   4096 1048576      1568     1187                0            1187              0             N DGCRS/
MOUNTED EXTERN N         512   4096 1048576      8628     4989                0            4989              0             N DGDATA/

3、利用RMAN將資料檔案從asm磁碟拷貝到檔案系統

[oracle@dg1:/home/oracle]#rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Aug 20 03:26:58 2013

connected to target database: DG (DBID=1728697911, not open)

RMAN> copy datafile 4 to '+DGDATA';

Starting backup at 20-AUG-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/tmp/oracle/backup/users01.dbf
output file name=+DGDATA/dg1/datafile/users.258.823927747 tag=TAG20130820T044907 RECID=7 STAMP=823927870
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:07
Finished backup at 20-AUG-13

4、修改控制檔案中新檔案的路徑

RMAN> switch datafile 4 to copy;

datafile 4 switched to datafile copy "+DGDATA/dg1/datafile/users.258.823927747"
或
[oracle] sqlplus '/as sysdba'

SQL> alter database rename file '/tmp/oracle/backup/users01.dbf' to '+DGDATA/dg1/datafile/users.258.823927747';

Database altered.

5、檢視新檔案路徑

SQL> select file#, name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------------------------------------
         1 +DGDATA/dg1/datafile/system.256.815366947
         2 +DGDATA/dg1/datafile/sysaux.257.815367079
         3 +DGDATA/dg1/datafile/undotbs1.260.823927355
         4 +DGDATA/dg1/datafile/users.258.823927747
         5 /home/oracle/test.dbf

6、online表空間

SQL> alter tablespace USERS online;

Tablespace altered.

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

Move datafile:From File System to ASM

相關文章