資料檔案遷移

壹頁書發表於2014-04-15
資料檔案遷移有兩種方法：
1  使表空間到離線狀態，然後重新命名資料檔案，然後再恢復表空間狀態
2  關閉資料庫，然後起到mount狀態，重新命名資料檔案，然後把庫開到open狀態

第一種演示如下

點選(此處)摺疊或開啟

  1. SQL> select file_id , file_name , tablespace_name , bytes/1024/1024 from dba_data_files;

  3.    FILE_ID FILE_NAME TABLESPACE BYTES/1024/1024
  4. ---------- -------------------------------------------------- ---------- ---------------
  5.          1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf SYSTEM 325
  6.          2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf UNDOTBS 200
  7.          3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf SYSAUX 325
  8.          4 /u01/app/oracle/oradata/PROD/disk3/system02.dbf UNDOTBS2 10
  9.          5 /u01/app/oracle/oradata/PROD/disk4/undotbs02.dbf UNDOTBS3 10
  10.          6 /u01/app/oracle/oradata/PROD/disk3/user01.dbf USERS 15
  11.          7 /u01/app/oracle/oradata/PROD/disk4/user02.dbf USERS 10
  12.          8 /u01/app/oracle/oradata/PROD/disk4/lxtbs01.dbf LXTBS 10
  13.          9 /u01/app/oracle/oradata/PROD/disk3/lxtbs02.dbf LXTBS 10

  15. 9 rows selected.

  17. SQL> alter tablespace lxtbs offline;

  19. Tablespace altered.
  20. SQL> alter tablespace lxtbs rename datafile \'/u01/app/oracle/oradata/PROD/disk4/lxtbs01.dbf\' to \'/u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf\';

  22. Tablespace altered.

  24. SQL> alter tablespace lxtbs online;

  26. Tablespace altered.

  28. SQL> select file_id , file_name , tablespace_name , bytes/1024/1024 from dba_data_files;

  30.    FILE_ID FILE_NAME TABLESPACE BYTES/1024/1024
  31. ---------- -------------------------------------------------- ---------- ---------------
  32.          1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf SYSTEM 325
  33.          2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf UNDOTBS 200
  34.          3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf SYSAUX 325
  35.          4 /u01/app/oracle/oradata/PROD/disk3/system02.dbf UNDOTBS2 10
  36.          5 /u01/app/oracle/oradata/PROD/disk4/undotbs02.dbf UNDOTBS3 10
  37.          6 /u01/app/oracle/oradata/PROD/disk3/user01.dbf USERS 15
  38.          7 /u01/app/oracle/oradata/PROD/disk4/user02.dbf USERS 10
  39.          8 /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf LXTBS 10
  40.          9 /u01/app/oracle/oradata/PROD/disk3/lxtbs02.dbf LXTBS 10

  42. 9 rows selected.
第二種
alter database rename file '/u01/app/oracle/oradata/PROD/disk4/lxtbs01.dbf' to
'/u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf';

alter database rename file '/u01/app/oracle/oradata/PROD/disk4/lxtbs01.dbf' to
'/u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf';建議使用第一種辦法，方便，且不需要關庫，省掉了好多書面報告

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

相關文章