12c 聯機狀態移動資料檔案

snowdba發表於2015-03-05
oracle12在表空間管理中推出了一個新特性，線上移動資料檔案ALTER DATABASE MOVE DATAFILE. 省去了過去offline  mv  online等工作，可以一氣呵成。

本次實驗環境為pdb資料庫中的線上移動資料檔案

1. 登入cdb資料庫
[root@snow ~]# su - oracle
[oracle@snow ~]$ sqlplus / as sysdba

SYS@cdb > show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO

2. 切換到pdb資料庫
SYS@cdb > alter session set container=pdb1;

3. 檢視pdb1資料庫的資料檔案路徑
SYS@cdb > select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb/pdb1/example01.dbf
/u01/app/oracle/oradata/cdb/pdb1/SAMPLE_SCHEMA_users01.dbf
/u01/app/oracle/oradata/cdb/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/cdb/pdb1/baymax_comp01.dbf
/u01/app/oracle/oradata/cdb/pdb1/system01.dbf
/u01/app/oracle/oradata/cdb/pdb1/baymax_comp02.dbf

4. 移動資料檔案到新路徑/home/oracle
SYS@cdb > alter database move datafile '/u01/app/oracle/oradata/cdb/pdb1/baymax_comp02.dbf' to '/home/oracle/baymax_comp02.dbf';

SYS@cdb > select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb/pdb1/system01.dbf
/u01/app/oracle/oradata/cdb/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/cdb/pdb1/SAMPLE_SCHEMA_users01.dbf
/u01/app/oracle/oradata/cdb/pdb1/example01.dbf
/home/oracle/baymax_comp02.dbf
/u01/app/oracle/oradata/cdb/pdb1/baymax_comp01.dbf

5.  確認一下資料檔案的檔案號file_id
SYS@cdb > col file_name for a60
SYS@cdb > select file_id,file_name from dba_data_files;

FILE_ID FILE_NAME
---------- ------------------------------------------------------------
11 /u01/app/oracle/oradata/cdb/pdb1/example01.dbf
10 /u01/app/oracle/oradata/cdb/pdb1/SAMPLE_SCHEMA_users01.dbf
9 /u01/app/oracle/oradata/cdb/pdb1/sysaux01.dbf
12 /u01/app/oracle/oradata/cdb/pdb1/baymax_comp01.dbf
8 /u01/app/oracle/oradata/cdb/pdb1/system01.dbf
13 /home/oracle/baymax_comp02.dbf

6.  使用keep關鍵字將/home/oracle/baymax_comp02.dbf移回原來的路徑，並且在/home/oracle路徑下保留一份。
SYS@cdb > alter database move datafile 13 to '/u01/app/oracle/oradata/cdb/pdb1/baymax_comp02.dbf' keep;

檔案已經被移動回原來的位置
SYS@cdb > select file_id,file_name from dba_data_files;

FILE_ID FILE_NAME
---------- ------------------------------------------------------------
8 /u01/app/oracle/oradata/cdb/pdb1/system01.dbf
9 /u01/app/oracle/oradata/cdb/pdb1/sysaux01.dbf
10 /u01/app/oracle/oradata/cdb/pdb1/SAMPLE_SCHEMA_users01.dbf
11 /u01/app/oracle/oradata/cdb/pdb1/example01.dbf
13 /u01/app/oracle/oradata/cdb/pdb1/baymax_comp02.dbf
12 /u01/app/oracle/oradata/cdb/pdb1/baymax_comp01.dbf

6 rows selected.

/home/oracle路徑下還保留一份資料檔案
SYS@cdb > !ls -l /home/oracle/bay*
-rw-r----- 1 oracle oinstall 15736832 Mar 4 14:29 /home/oracle/baymax_comp02.dbf

對比一下新特性之前和現在的變化
如果用傳統方式來做需要如下步驟
1.
alter tablespace baymax_comp offline;

2.
mv '/u01/app/oracle/oradata/cdb/pdb1/baymax_comp02.dbf' '/home/oracle/baymax_comp02.dbf'

3.
alter tablespace baymax_comp rename datafile '/u01/app/oracle/oradata/cdb/pdb1/baymax_comp02.dbf'
to '/home/oracle/baymax_comp02.dbf'

4.
alter tablespace baymax_comp online;

新特性只需一步
alter database move datafile '/u01/app/oracle/oradata/cdb/pdb1/baymax_comp02.dbf'
to '/home/oracle/baymax_comp02.dbf'


全文完

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

相關文章