Move datafile:From ASM to File System

linfeng_oracle發表於2013-08-20
ASM

Move datafile:From ASM to File System

 

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

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

[oracle] sqlplus '/as sysdba'

SQL>  shutdown immediate

SQL> startup mount;


2、確保檔案系統有足夠的空間

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 +DGDATA/dg1/datafile/users.260.815367243                                                500
         5 /home/oracle/test.dbf                                                                   500
這裡舉例移動undo表空間檔案

[oracle@dg1:/tmp/oracle/backup]#df -k
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                      28313732  15067440  11784968  57% /
/dev/sda1               101086     12332     83535  13% /boot
tmpfs                   517348    153436    363912  30% /dev/shm


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

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

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

RMAN> copy datafile 3 to '/tmp/oracle/backup/undo01.dbf';

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=+DGDATA/dg1/datafile/undotbs1.258.815367141
  output file name=/tmp/oracle/backup/undo01.dbf tag=TAG20130820T032739 RECID=3 STAMP=823922979
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:08
Finished backup at 20-AUG-13


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

[oracle] sqlplus '/as sysdba'

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

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 /tmp/oracle/backup/undo01.dbf
         4 +DGDATA/dg1/datafile/users.260.815367243
         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 /tmp/oracle/backup/undo01.dbf
USERS                                             4 +DGDATA/dg1/datafile/users.260.815367243
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 +DGDATA/dg1/datafile/users.260.815367243                                                500
         5 /home/oracle/test.dbf                                                                   500
這裡舉例移動users表空間檔案

[oracle@dg1:/tmp/oracle/backup]#df -k
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                      28313732  15067440  11784968  57% /
/dev/sda1               101086     12332     83535  13% /boot
tmpfs                   517348    153436    363912  30% /dev/shm


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

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

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

RMAN> copy datafile 4 to '/tmp/oracle/backup/users01.dbf';

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=24 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DGDATA/dg1/datafile/users.260.815367243
 output file name=/tmp/oracle/backup/users01.dbf tag=TAG20130820T034557 RECID=4 STAMP=823924020
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:13
Finished backup at 20-AUG-13

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

[oracle] sqlplus '/as sysdba'

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

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 /tmp/oracle/backup/undo01.dbf
         4 /tmp/oracle/backup/users01.dbf
         5 /home/oracle/test.dbf


6、online表空間

SQL> alter tablespace USERS online;

Tablespace altered.

 

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

相關文章