OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.10.0.0
방법 : 오라클 19c 온라인 데이터파일 MOVE 테스트(online datafile move)
운영 환경에서 datafile이 저장된 디렉토리의 용량이 모자라거나
asm 디스크 그룹의 USABLE_FILE_MB 이 음수(-)가 되었을때 datafile 을 다른 디스크그룹으로 옮겨줘야함.
12c 이전까지는 tablespace를 offline 하고 했어야했지만
12c 부터는 온라인으로 데이터파일을 move 할수 있음
datafile move 시나리오
테스트 목록
1. MOVE 시 어떤 LOCK TYPE이 발생하는지 여부 테스트
1_2. MOVE 시 alert log 확인
2. MOVE + DDL 시 LOCK 발생 여부 테스트
3. MOVE + 데이터 insert 시 I/O 확인 테스트
4. MOVE 시 ash로 이벤트 확인
이 테스트에선 세션을 총 5개 이용함
1번 째 세션 : 194번 세션(move datafile 실행용 세션)
2번 째 세션 : 관측용 세션
3번 째 세션 : 75번 세션(ddl lock 테스트용 세션)
4번 째 세션 : I/O 테스트용 세션1
5번 째 세션 : I/O 테스트용 세션2
1. MOVE 시 어떤 LOCK TYPE이 발생하는지 여부 테스트
1번 째 세션 접속
1번 째 세션에서 테스트용 테이블스페이스 생성
1번 째 세션에서 테스트용 테이블스페이스 생성
|
1
2
3
|
SQL> create tablespace MOVETS datafile '/app2/oracle/app/oracle/movets.dbf' size 5g autoextend off;
Tablespace created.
|
데이터파일 상태 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL>
set lines 200 pages 1000
col tablespace_name for a15
col file_name for a50
col status for a15
col online_status for a15
select tablespace_name, file_id, file_name, status, online_status
from dba_data_files
order by file_id;
TABLESPACE_NAME FILE_ID FILE_NAME STATUS ONLINE_STATUS
--------------- ---------- -------------------------------------------------- --------------- ---------------
SYSTEM 1 /app/oracle/oradata/ORCL19/system01.dbf AVAILABLE SYSTEM
SYSAUX 2 /app2/oracle/app/oracle/sysaux01.dbf AVAILABLE ONLINE
UNDOTBS1 3 /app/oracle/oradata/ORCL19/undotbs01.dbf AVAILABLE ONLINE
USERS 4 /app/oracle/oradata/ORCL19/users01.dbf AVAILABLE ONLINE
MOVETS 5 /app2/oracle/app/oracle/movets.dbf AVAILABLE ONLINE
|
현재 세션 sid 확인
|
1
2
3
4
5
|
SQL> select sid from v$mystat where rownum<=1;
SID
----------
194
|
관측용 세션으로 접속하여 현재 lock 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1
and sid = 194
--and TYPE in ('TX','TM')
order by sid
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
194 AE 134 0 4 0 0 0 134 0
|
LMODE(lock 모드)가 4(share(S))인 lock만 나옴
194번 세션에서 datafile online move 명령 실행
|
1
2
3
4
|
SQL> alter database move datafile '/app2/oracle/app/oracle/movets.dbf' to '/app/oracle/oradata/ORCL19/movets.dbf';
(참조용 반대로 다시 옮기는 구문)
SQL> alter database move datafile '/app/oracle/oradata/ORCL19/movets.dbf' to '/app2/oracle/app/oracle/movets.dbf';
|
관측용 세션에서 move시 lock 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1
and sid = 194
--and TYPE in ('TX','TM')
order by sid
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
194 AE 134 0 4 0 0 0 134 0
194 MV 0 5 6 0 0 0 0 5
194 MV 1 5 4 0 0 0 1 5
194 IS 0 0 4 0 0 0 0 0
194 MR 5 0 4 0 0 0 5 0
|
기존에 안보이던 4개 lock이 추가로 보임
이중 하나 MV에는 LMODE(lock 모드)가 6(exclusive(X))인 lock이 걸려있음
해당 lock 설명 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
col type for a4
col name for a30
col description for a80
select type, name, description
from v$lock_type
where type in ('MV', 'IS','MR');
TYPE NAME DESCRIPTION
---- ------------------------------ --------------------------------------------------------------------------------
IS Instance State Enqueue used to synchronize instance state changes
MV Online Datafile Move Synchronizes online datafile move operation or cleanup
MR Media Recovery Lock used to coordinate media recovery with other uses of datafiles
|
MV는 online datafile move 작업을 할때 동기화 및 클린업을 시켜주는 lock type이라고 설명하고 있음
관측용 세션에서 move 시 진행상황 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
col username for a10
col opname for a33
col target for a10
col %DONE for a10
select * from (
select b.username, b.sid, b.opname, b.target,
round(b.SOFAR*100/b.TOTALWORK,0) || '%' as "%DONE", b.time_remaining,
to_char(b.start_time,'YYYY/MM/DD HH24:MI:SS') start_time
from v$session_longops b
where 1=1
and b.opname = 'Online data file move'
order by last_update_time desc
) where rownum<=1;
USERNAME SID OPNAME TARGET %DONE TIME_REMAINING START_TIME
---------- ------- --------------------------------- ---------- ---------- -------------- -------------------
SYS 194 Online data file move 5 94% 2 2023/01/23 04:38:28
|
move 시 alert log 확인
|
1
2
3
4
5
6
7
8
9
|
$ vi alert.log
2023-01-23T04:38:29.436822+09:00
alter database move datafile '/app2/oracle/app/oracle/movets.dbf' to '/app/oracle/oradata/ORCL19/movets.dbf'
2023-01-23T04:38:29.437627+09:00
Moving datafile /app2/oracle/app/oracle/movets.dbf (5) to /app/oracle/oradata/ORCL19/movets.dbf
2023-01-23T04:38:47.300699+09:00
Move operation committed for file /app/oracle/oradata/ORCL19/movets.dbf
2023-01-23T04:38:49.474937+09:00
Completed: alter database move datafile '/app2/oracle/app/oracle/movets.dbf' to '/app/oracle/oradata/ORCL19/movets.dbf'
|
관측용 세션에서 move 시 데이터파일 상태 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL>
set lines 200 pages 1000
col tablespace_name for a15
col file_name for a50
col status for a15
col online_status for a15
select tablespace_name, file_id, file_name, status, online_status
from dba_data_files
order by file_id;
TABLESPACE_NAME FILE_ID FILE_NAME STATUS ONLINE_STATUS
--------------- ---------- -------------------------------------------------- --------------- ---------------
SYSTEM 1 /app/oracle/oradata/ORCL19/system01.dbf AVAILABLE SYSTEM
SYSAUX 2 /app2/oracle/app/oracle/sysaux01.dbf AVAILABLE ONLINE
UNDOTBS1 3 /app/oracle/oradata/ORCL19/undotbs01.dbf AVAILABLE ONLINE
USERS 4 /app/oracle/oradata/ORCL19/users01.dbf AVAILABLE ONLINE
MOVETS 5 /app/oracle/oradata/ORCL19/movets.dbf AVAILABLE ONLINE
|
status 및 online status는 변함이 없고
alert log에 Completed 메세지가 나오기 전까지 이전 file_name 위치이다가
Completed: 메세지가 나오고 난뒤 조회해보면 변경된 위치로 나옴
데이터 파일 번호는 변경되지 않음
2. MOVE + DDL 시 LOCK 발생 여부 테스트
194번 세션에서 datafile online move 명령 실행
|
1
2
3
4
|
SQL> alter database move datafile '/app/oracle/oradata/ORCL19/movets.dbf' to '/app2/oracle/app/oracle/movets.dbf';
(참조용 반대로 다시 옮기는 구문)
SQL> alter database move datafile '/app2/oracle/app/oracle/movets.dbf' to '/app/oracle/oradata/ORCL19/movets.dbf';
|
새 세션 열어서 sid 확인
|
1
2
3
4
5
|
SQL> select sid from v$mystat where rownum<=1;
SID
----------
75
|
75번 세션에서 ddl 실행 테스트1 : resize datafile
|
1
2
3
4
5
|
SQL> alter database datafile 5 resize 3g;
alter database datafile 5 resize 3g
*
ERROR at line 1:
ORA-63000: operation disallowed: data file /app/oracle/oradata/ORCL19/movets.dbf is being moved
|
에러가 바로 발생함
75번 세션에서 ddl 실행 테스트2 : drop tablespace
|
1
2
|
SQL> drop tablespace movets including contents and datafiles;
(lock 걸린듯이 멈춰있음)
|
관측용 세션에서 move+drop tablespace 시 lock 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1
and (sid = 194 or sid = 75)
--and TYPE in ('TX','TM')
order by sid
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
194 AE 134 0 4 0 0 0 134 0
194 MV 0 5 6 0 0 0 0 5
194 MV 1 5 4 0 1 0 1 5
194 MR 5 0 4 0 0 0 5 0
194 IS 0 0 4 0 0 0 0 0
75 TT 12 0 6 0 0 0 12 0
75 AE 134 0 4 0 0 0 134 0
75 TX 7012371 371 6 0 0 107 19 371
75 MV 1 5 0 6 0 0 1 5
75 IS 0 0 4 0 0 0 0 0
75 TM 16 0 3 0 0 0 16 0
75 TT 12 8 6 0 0 0 12 8
12 rows selected.
|
75번 세션에서 move 만 할때는 안보이던 6개 lock이 추가로 보임(move 세션 : 194, drop 세션 : 75)
TM, TX lock도 관찰됨.
이중 하나 TX에는 LMODE(lock 모드)가 6(exclusive(X))인 lock이 걸려있음
해당 lock 설명 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL>
col type for a4
col name for a30
col description for a80
select type, name, description
from v$lock_type
where type in ('MV', 'TT','TX','TM','IS');
TYPE NAME DESCRIPTION
---- ------------------------------ --------------------------------------------------------------------------------
IS Instance State Enqueue used to synchronize instance state changes
MV Online Datafile Move Synchronizes online datafile move operation or cleanup
TM DML Synchronizes accesses to an object
TX Transaction Lock held by a transaction to allow other transactions to wait for it
TT Tablespace Serializes DDL operations on tablespaces
|
194번 세션에서 move 명령이 끝난 이후 75번 세션의 drop 명령도 resize 를 할때처럼 에러가 나면서 실패함
|
1
2
3
4
5
|
SQL> drop tablespace movets including contents and datafiles;
drop tablespace movets including contents and datafiles
*
ERROR at line 1:
ORA-63000: operation disallowed: data file /app/oracle/oradata/ORCL19/movets.dbf is being moved
|
3. MOVE + 데이터 insert 시 I/O 확인 테스트
4번 째 세션 접속
4번 째 세션에서 샘플 테이블 및 샘플 프로시저 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
create table TEST (num ,txt) tablespace movets as select rownum,to_char( date'-4712-01-01'+rownum-1,'Jsp')
from (select * from dual connect by level <=1000),(select * from dual connect by level <=1000) order by 2;
create table TEST2 tablespace movets as select * from TEST;
create or replace procedure TESTACTIVITY as
begin
commit;
execute immediate 'truncate table TEST2 reuse storage';
for i in 1..1 loop
lock table TEST in exclusive mode;
insert /*+ APPEND */ into TEST2 select -num,txt from TEST;
commit;
delete from TEST2;
commit;
end loop;
end;
/
|
케이스1. move 안 할때 insert 시 physical write 확인
4번 째 세션에서 insert 프로시저 실행
|
1
2
|
SQL> exec TESTACTIVITY;
PL/SQL procedure successfully completed.
|
4번 째 세션에서 I/O 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL>
set lines 200 pages 1000
col name for a40
select b.name,a.value
from v$mystat a, v$statname b
where a.statistic# = b.statistic#
and value>0
and name like '%physical%bytes%'
order by 1;
NAME VALUE
---------------------------------------- ----------
cell physical IO interconnect bytes 222208000
physical read bytes 149553152
physical read total bytes 149553152
physical write bytes 72654848
physical write total bytes 72654848
|
72654848 정도 write함
케이스2. move 할때 insert 시 physical write 확인
5번 째 세션 접속
5번 째 세션에서 move 명령 백그라운드로 실행
|
1
2
3
4
5
6
7
8
|
SQL>
variable job number
exec dbms_job.submit(:job,q'[ begin execute immediate 'set role all'; execute immediate q'(alter database move datafile '/app2/oracle/app/oracle/movets.dbf' to '/app/oracle/oradata/ORCL19/movets.dbf' )'; end; ]'); commit; dbms_lock.sleep(3);
(참고용> 다시 원래대로 돌리는 백그라운드 실행 프로시저)
SQL>
variable job number
exec dbms_job.submit(:job,q'[ begin execute immediate 'set role all'; execute immediate q'(alter database move datafile '/app/oracle/oradata/ORCL19/movets.dbf' to '/app2/oracle/app/oracle/movets.dbf' )'; end; ]'); commit; dbms_lock.sleep(3);
|
5번 째 세션에서 insert 프로시저 실행
|
1
2
|
SQL> exec TESTACTIVITY;
PL/SQL procedure successfully completed.
|
5번 째 세션에서 다시 I/O 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL>
set lines 200 pages 1000
col name for a40
select b.name,a.value
from v$mystat a, v$statname b
where a.statistic# = b.statistic#
and value>0
and name like '%physical%bytes%'
order by 1;
NAME VALUE
---------------------------------------- ----------
cell physical IO interconnect bytes 295542784
physical read bytes 150118400
physical read total bytes 150233088
physical write bytes 72654848
physical write total bytes 145309696
|
결과에서 physical write total bytes 값이 move 명령을 수행하지 않을때에 비해 2배나 큼
4. MOVE 시 ash로 이벤트 확인
5번 째 세션에서 동시에 ash로 발생중인 event 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
select * from (
select
WAIT_CLASS ,
EVENT,
count(sample_time) as EST_SECS_IN_WAIT
from v$active_session_history
where 1=1
-- and sample_time between sysdate - interval '1' hour and sysdate
-- and sample_time between sysdate - interval '5' minute and sysdate
and sample_time between sysdate - interval '1' minute and sysdate
-- and sample_time between sysdate - interval '30' second and sysdate
group by WAIT_CLASS,EVENT
order by count(sample_time) desc
)
where rownum <= 5;
no rows selected
|
dml이 적어서 그런지 이벤트는 별도로 발생하지 않음
이렇게 온라인으로 데이터파일을 move 할수 있기때문에 업무에 유용하게 사용할수 있을것 같음
트랜젝션이 많을때는 해보지 시도해보지 않았기 때문에 충분한 검증 및 모니터링을 병행하여 작업이 필요함
참조 : https://oracle-base.com/articles/12c/online-move-datafile-12cr1
https://www.dbi-services.com/blog/12c-online-datafile-move-and-ongoing-changes/
2763939.1, 1566797.1
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c ORA-04031 에러를 발생시키는 방법 (0) | 2023.01.25 |
|---|---|
| 오라클 19c 컨트롤파일 maxdatafiles와 db_files 파라미터 (0) | 2023.01.15 |
| 오라클 19c 테이블 move 동일 테이블스페이스 테스트 (0) | 2022.12.21 |
| 오라클 11gR2 ddl wait (ddl_lock_timeout 파라미터) (0) | 2022.08.22 |
| 오라클 19c 파티션 테이블 통계정보 이관, 복제 (0) | 2022.07.17 |