Online Move Datafile (12.1 버전 부터 ~)

12c 이전버전까지는 Datafile move작업하기 위해서는 offline -> OS 상에서 CP 혹은 MV -> alter tablespace rename ->online의 순서로 작업을 했어야 했습니다. 필연적으로 offline으로 인한downtime을 가질 수 밖에 없었습니다.

하지만 12c에서부터는 online으로 작업할 수 있는 기능이 생겼습니다.

 

구문

ALTER DATABASE MOVE DATAFILE ( 'filename' | 'ASM_filename' | file_number ) [ TO ( 'filename' | 'ASM_filename' ) ] [ REUSE ] [ KEEP ]

REUSE 키워드는 새 파일이 이미 존재하더라도 생성되어야 함을 의미하고, KEEP은 데이터 파일의 원본 사본이 유지되어야 함을 의미합니다.

OMF파일 형식인 경우 KEEP옵션을 사용할 수 없습니다. 대상 파일이 OMF파일 인 경우 TO 절을 생략할 수 있고, DB_CREATE_FILE_DEST위치에 OMF 이름으로 파일이 생성됩니다.

 

V$DATAFILE 뷰와 DBA_DATA_FILES뷰를 통해 FILE의 ID를 확인할 수 잇습니다.

SYS@ysbae> SELECT file#, name FROM v$datafile WHERE con_id = 0 ORDER BY file#;

     FILE# NAME
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         1 /app/oracle/oradata/ysbae/system01.dbf
         2 /app/oracle/oradata/ysbae/test01.dvf
         3 /app/oracle/oradata/ysbae/sysaux01.dbf
         4 /app/oracle/oradata/ysbae/undotbs01.dbf
         5 /app/oracle/oradata/ysbae/example01.dbf
         7 /app/oracle/oradata/ysbae/users01.dbf
         8 /app/oracle/oradata/ysbae/SQL_TEST_TS01.dbf

7 rows selected.


SYS@ysbae> SELECT file_id, file_name FROM dba_data_files ORDER BY file_id;

   FILE_ID FILE_NAME
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         1 /app/oracle/oradata/ysbae/system01.dbf
         2 /app/oracle/oradata/ysbae/test01.dvf
         3 /app/oracle/oradata/ysbae/sysaux01.dbf
         4 /app/oracle/oradata/ysbae/undotbs01.dbf
         5 /app/oracle/oradata/ysbae/example01.dbf
         7 /app/oracle/oradata/ysbae/users01.dbf
         8 /app/oracle/oradata/ysbae/SQL_TEST_TS01.dbf

7 rows selected.

테스트1

system01.dbf를 online으로 옮겨보겠습니다.

SYS@ysbae> alter database move datafile '/app/oracle/oradata/ysbae/system01.dbf' to '/app/oracle/oradata/ysbae2/system01.dbf';

Database altered.

Elapsed: 00:00:38.29
SYS@ysbae> select file_id,file_name from dba_data_files where file_id=1;

   FILE_ID FILE_NAME
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         1 /app/oracle/oradata/ysbae2/system01.dbf

1 row selected.

Elapsed: 00:00:00.02
SYS@ysbae> !ls /app/oracle/oradata/ysbae/system01.dbf
ls: cannot access /app/oracle/oradata/ysbae/system01.dbf: No such file or directory

SYS@ysbae> !ls /app/oracle/oradata/ysbae2/system01.dbf
/app/oracle/oradata/ysbae2/system01.dbf

 

테스트2

system01.dbf를 keep옵션을 사용하여 옮겨보겠습니다. KEEP 옵션 사용시 AS-IS와 TO-BE에 둘다 system01.dbf가 존재하는 것을 확인할 수 있습니다.

SYS@ysbae> alter database move datafile '/app/oracle/oradata/ysbae2/system01.dbf' to '/app/oracle/oradata/ysbae/system01.dbf' KEEP;

Database altered.

Elapsed: 00:00:28.49
SYS@ysbae> SELECT file_id, file_name FROM dba_data_files WHERE file_id = 1;

   FILE_ID FILE_NAME
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         1 /app/oracle/oradata/ysbae/system01.dbf

1 row selected.

Elapsed: 00:00:00.02
SYS@ysbae>  !ls /app/oracle/oradata/ysbae/system01.dbf
/app/oracle/oradata/ysbae/system01.dbf

SYS@ysbae>  !ls /app/oracle/oradata/ysbae2/system01.dbf
/app/oracle/oradata/ysbae2/system01.dbf

 

테스트3

OMF파일형식을 사용한다고 가정하고 SYSTEM01.dbf를 옮겨보겠습니다.

SYS@ysbae> show parameter db_create

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      (null)
db_create_online_log_dest_1          string      (null)
db_create_online_log_dest_2          string      (null)
db_create_online_log_dest_3          string      (null)
db_create_online_log_dest_4          string      (null)
db_create_online_log_dest_5          string      (null)
SYS@ysbae> alter system set db_create_file_dest='/app/oracle/oradata/ysbae';

System altered.

Elapsed: 00:00:00.03
SYS@ysbae>
SYS@ysbae>
SYS@ysbae> alter database move datafile '/app/oracle/oradata/ysbae/system01.dbf';

Database altered.

Elapsed: 00:00:55.24
SYS@ysbae> SELECT file_id, file_name FROM dba_data_files WHERE file_id = 1;

   FILE_ID FILE_NAME
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         1 /app/oracle/oradata/ysbae/YSBAE/datafile/o1_mf_system_hqs0ffbo_.dbf

1 row selected.

 

테스트4

OMF파일형식에 KEEP 옵션을 사용하여 옮겨보겠습니다. KEEP 옵션은 무시되고 MOVE되는 것을 확인할 수 잇습니다.

SYS@ysbae> alter database move datafile 1 to '/app/oracle/oradata/ysbae/system01.dbf' KEEP;

Database altered.

Elapsed: 00:00:34.01
SYS@ysbae> SELECT file_id, file_name FROM dba_data_files WHERE file_id = 1;

   FILE_ID FILE_NAME
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         1 /app/oracle/oradata/ysbae/system01.dbf

1 row selected.

Elapsed: 00:00:00.00
SYS@ysbae> !ls /app/oracle/oradata/ysbae/system01.dbf
/app/oracle/oradata/ysbae/system01.dbf

SYS@ysbae> !ls /app/oracle/oradata/ysbae/YSBAE/datafile/o1_mf_system_hqs0ffbo_.dbf
ls: cannot access /app/oracle/oradata/ysbae/YSBAE/datafile/o1_mf_system_hqs0ffbo_.dbf: No such file or directory

 

테스트5

TEMP FILE은 MOVE 명령어가 적용되지 않습니다. ORA-01516에러를 발생시키며 되지 않습니다.

SYS@ysbae> SELECT file_id, file_name FROM dba_temp_files;

   FILE_ID FILE_NAME
---------- ---------------------------------------------------------------------------------------------------                        --------------------------------------------------------------------------------------------------------------                        --------------------------------------------------------------------------------------------------------------                        --------------------------------------------------------------------------------------------------------------                        ------------------------------------------------------------------------------------
         1 /app/oracle/oradata/ysbae/temp01.dbf
         2 /app/oracle/oradata/ysbae/temp02.dbf
         3 /app/oracle/oradata/ysbae/temp_t1.dbf

3 rows selected.

Elapsed: 00:00:00.19

SYS@ysbae> alter database move datafile '/app/oracle/oradata/ysbae/temp01.dbf' to '/app/oracle/oradata/ysbae2/                        temp01.dbf';
alter database move datafile '/app/oracle/oradata/ysbae/temp01.dbf' to '/app/oracle/oradata/ysbae2/temp01.dbf'
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "/app/oracle/oradata/ysbae/temp01.dbf" in the cu                        rrent container

 

참고 : oracle-base.com/articles/12c/online-move-datafile-12cr1