oracle_一次移動資料庫dbf檔案的操作

標籤：

oracle資料庫的dbf路徑下面磁碟不足，需要把原始路徑下面的dbf檔案移動到另外一個磁碟路徑下， 具體的操作有四步。

1.把整個資料表空間offline.

2.copy原始路徑下的dbf檔案到新的路徑下面。

3.改變dbf檔案的路徑。

4.把資料表空間online。

 

查詢資料表空間和移動dbf檔案的sql如下：

select ts.rn,
ts.TABLESPACE_NAME,
df.BYTES/1024/1024/1024,
to_number(regexp_substr(ts.TABLESPACE_NAME,‘[0-9]{6}‘))date_tb,
df.FILE_NAME,
replace(df.FILE_NAME,‘/oradata/db/mos/‘,‘‘)os_filename,
‘alter tablespace ‘||ts.TABLESPACE_NAME||‘ offline;‘ SQL_offline_ts,
‘alter tablespace ‘||ts.TABLESPACE_NAME||‘ online;‘ SQL_online_ts,
‘cp ‘||df.FILE_NAME||‘ /oradata/db3/p/.‘ OS_copy,
‘alter tablespace ‘||ts.TABLESPACE_NAME||‘ rename datafile ‘‘‘||df.FILE_NAME||‘‘‘ to ‘‘/oradata/db3/pm/‘||replace(df.FILE_NAME,‘/oradata/db/mos/‘,‘‘)||‘‘‘;‘ SQL_rename_dbf

from (select rownum rn,
ts.TABLESPACE_NAME from
user_tablespaces ts
where to_number(regexp_substr(ts.TABLESPACE_NAME,‘[0-9]{6}‘)) >= 160000
and to_number(regexp_substr(ts.TABLESPACE_NAME,‘[0-9]{6}‘)) <= 160500
)ts
,dba_data_files df

where
ts.TABLESPACE_NAME = df.TABLESPACE_NAME
and lower(df.FILE_NAME) like ‘%/oradata/db/mos/p_db_dat%‘;

 

 

 

一次具體的操作過程如下：（一共移動了三個資料表空間的資料檔案，中間的複製操作在linux視窗進行，不在本次操作記錄裡。）

先用sys使用者登入資料庫。

 

SQL> alter tablespace PM4H_DB_DAT_W_150803 offline;

Tablespace altered.

SQL> alter tablespace PM4H_DB_DAT_W_150803 rename datafile ‘/oradata/db/pm/pm4h/mos5200/PM4H_DB_DAT_W_150803_MV0_0000.dbf‘ to ‘/oradata/db3/pm/PM4H_DB_DAT_W_150803_MV0_0000.dbf‘;

Tablespace altered.

SQL> alter tablespace PM4H_DB_DAT_W_150803 rename datafile ‘/oradata/db/pm/pm4h/mos5200/PM4H_DB_DAT_W_150803_MV0_0001.dbf‘ to ‘/oradata/db3/pm/PM4H_DB_DAT_W_150803_MV0_0001.dbf‘; 

Tablespace altered.

SQL>

SQL>

SQL> alter tablespace PM4H_DB_DAT_W_150803 rename datafile ‘/oradata/db/pm/pm4h/mos5200/PM4H_DB_DAT_W_150803_MV0_0002.dbf‘ to ‘/oradata/db3/pm/PM4H_DB_DAT_W_150803_MV0_0002.dbf‘;

Tablespace altered.

SQL> alter tablespace PM4H_DB_DAT_W_150803 online;

 

Tablespace altered.

SQL> alter tablespace PM4H_DB_DAT_W_150810 offline;

 

Tablespace altered.

 

SQL> alter tablespace PM4H_DB_DAT_W_150810 rename datafile ‘/oradata/db/pm/pm4h/mos5200/PM4H_DB_DAT_W_150810_MV0_0000.dbf‘ to ‘/oradata/db3/pm/PM4H_DB_DAT_W_150810_MV0_0000.dbf‘;

 

Tablespace altered.

 

SQL> alter tablespace PM4H_DB_DAT_W_150810 rename datafile ‘/oradata/db/pm/pm4h/mos5200/PM4H_DB_DAT_W_150810_MV0_0001.dbf‘ to ‘/oradata/db3/pm/PM4H_DB_DAT_W_150810_MV0_0001.dbf‘;

 

Tablespace altered.

 

SQL>

SQL> alter tablespace PM4H_DB_DAT_W_150810 rename datafile ‘/oradata/db/pm/pm4h/mos5200/PM4H_DB_DAT_W_150810_MV0_0002.dbf‘ to ‘/oradata/db3/pm/PM4H_DB_DAT_W_150810_MV0_0002.dbf‘;

 

Tablespace altered.

 

SQL> alter tablespace PM4H_DB_DAT_W_150810 online;

Tablespace altered.

 

SQL> alter tablespace PM4H_DB_DAT_W_150817 offline;

 

Tablespace altered.

 

SQL> alter tablespace PM4H_DB_DAT_W_150817 rename datafile ‘/oradata/db/pm/pm4h/mos5200/PM4H_DB_DAT_W_150817_MV0_0000.dbf‘ to ‘/oradata/db3/pm/PM4H_DB_DAT_W_150817_MV0_0000.dbf‘;

 

Tablespace altered.

 

SQL> SQL> SQL> alter tablespace PM4H_DB_DAT_W_150817 rename datafile ‘/oradata/db/pm/pm4h/mos5200/PM4H_DB_DAT_W_150817_MV0_0001.dbf‘ to ‘/oradata/db3/pm/PM4H_DB_DAT_W_150817_MV0_0001.dbf‘;

 

Tablespace altered.

 

SQL> alter tablespace PM4H_DB_DAT_W_150817 rename datafile ‘/oradata/db/pm/pm4h/mos5200/PM4H_DB_DAT_W_150817_MV0_0002.dbf‘ to ‘/oradata/db3/pm/PM4H_DB_DAT_W_150817_MV0_0002.dbf‘;

 

Tablespace altered.

 

SQL> alter tablespace PM4H_DB_DAT_W_150817 online;

 

Tablespace altered.

 

SQL> exit

 

oracle_一次移動資料庫dbf檔案的操作