ORACLE datafile 이동 방법 2 가지

1. db 기동 중에 데이터 파일 단위로 이동

- 사용자용 데이터 파일 이동 할 때 유용

 

alter database datafile '/data3/MERGE_DB/SP_ETC/SP_ETC_DB_010.DBF' offline;

alter database datafile '/data3/MERGE_DB/SP_IDX/SP_IDX_IX_005.DBF' offline;

cp /data3/MERGE_DB/SP_ETC/SP_ETC_DB_010.DBF /data6/orcl/

cp /data3/MERGE_DB/SP_IDX/SP_IDX_IX_005.DBF /data6/orcl/


alter database rename file '/data3/MERGE_DB/SP_ETC/SP_ETC_DB_010.DBF' to '/data6/orclorcl/SP_ETC_DB_010.DBF';


alter database rename file '/data3/MERGE_DB/SP_IDX/SP_IDX_IX_005.DBF' to '/data6/orcl/SP_IDX_IX_005.DBF';

recover datafile '/data6/orcl/SP_ETC_DB_010.DBF';



recover datafile '/data6/orcl/SP_IDX_IX_005.DBF';

alter database datafile '/data6/orcl/SP_ETC_DB_010.DBF' online;

alter database datafile '/data6/orcl/SP_IDX_IX_005.DBF' online;


============================================================================================

2. DB 정지 후 마운트 상태에서 이동

 

- system 테이블스페이스등  기본 테이블 스페이스 이동 할 때 db down 꼭 필요

select tablespace_name,FILE_ID,file_name,bytes/1024/1024 from dba_data_files
where tablespace_name in ('SYSTEM','SYSAUX','UNDOTBS1','USERS') ORDER BY FILE_ID;

TABLESPACE_NAME             FILE_ID FILE_NAME                                          BYTES/1024/1024
------------------------ ---------- -------------------------------------------------- ---------------
SYSTEM                            1 /oradata/orcl/system01.dbf                                     2048
SYSAUX                            3 /oradata/orcl/sysaux01.dbf                                     2150
UNDOTBS1                          4 /oradata/orcl/undotbs01.dbf                                   10000
USERS                             7 /oradata/orcl/users01.dbf                                      2000

4 rows selected.


DB 기본 테이블스페이스 이동 

startup mount

!cp /oradata/orcl/system01.dbf   /orasys/orcl/system01.dbf   
!cp /oradata/orcl/sysaux01.dbf   /orasys/orcl/sysaux01.dbf   
!cp /oradata/orcl/undotbs01.dbf  /orasys/orcl/undotbs01.dbf  
!cp /oradata/orcl/users01.dbf    /orasys/orcl/users01.dbf    

alter database rename file '/oradata/orcl/system01.dbf'   to '/orasys/orcl/system01.dbf';  
alter database rename file '/oradata/orcl/sysaux01.dbf'   to '/orasys/orcl/sysaux01.dbf' ; 
alter database rename file '/oradata/orcl/undotbs01.dbf'  to '/orasys/orcl/undotbs01.dbf' ;
alter database rename file '/oradata/orcl/users01.dbf'    to '/orasys/orcl/users01.dbf' ; 

alter database open;