오라클 begin backup 중인 tablespace 확인(hot backup)

OS환경 : Oracle Linux6.8(64bit)

DB 환경 : Oracle Database 11.2.0.4

쿼리 : dba_data_files와 v$backup 뷰로 확인 가능

select a.tablespace_name, b.*
from dba_data_files a, v$backup b
where a.file_id = b.file#;
 
TABLESPACE_NAME             FILE# STATUS        CHANGE# TIME
------------------------------ ---------- ------------------ ---------- ---------
SYSTEM                    1 ACTIVE        4497379 17-DEC-18
SYSAUX                    2 ACTIVE        4497385 17-DEC-18
UNDOTBS1                3 ACTIVE        4497392 17-DEC-18
UNDOTBS2                4 ACTIVE        4497398 17-DEC-18
USERS                    5 ACTIVE        4497405 17-DEC-18

STATUS 가 ACTIVE 상태이면 

alter tablespace "TS명" begin backup; 을 입력한 상태라고 볼수 있음.

테스트 시나리오

begin backup 전 STATUS 확인

1 2 3 4 5 6 7 8 9 10 11

select a.tablespace_name, b.* from dba_data_files a, v$backup b where a.file_id = b.file#;   TABLESPACE_NAME             FILE# STATUS        CHANGE# TIME ------------------------------ ---------- ------------------ ---------- --------- SYSTEM                    1 NOT ACTIVE              0 (null) SYSAUX                    2 NOT ACTIVE              0 (null) UNDOTBS1                3 NOT ACTIVE              0 (null) UNDOTBS2                4 NOT ACTIVE              0 (null) USERS                    5 NOT ACTIVE              0 (null)

모든 tablespace begin backup 구문 입력

select 'alter tablespace '||tablespace_name||' begin backup;' from dba_data_files;
 
'ALTERTABLESPACE'||TABLESPACE_NAME||'BEGINBACKUP;'
-------------------------------------------------------------
alter tablespace SYSTEM begin backup;
alter tablespace SYSAUX begin backup;
alter tablespace UNDOTBS1 begin backup;
alter tablespace UNDOTBS2 begin backup;
alter tablespace USERS begin backup;
복사 후 붙여넣기
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.

begin backup 후 STATUS 확인

select a.tablespace_name, b.*
from dba_data_files a, v$backup b
where a.file_id = b.file#;
 
TABLESPACE_NAME             FILE# STATUS        CHANGE# TIME
------------------------------ ---------- ------------------ ---------- ---------
SYSTEM                    1 ACTIVE        4497379 17-DEC-18
SYSAUX                    2 ACTIVE        4497385 17-DEC-18
UNDOTBS1                3 ACTIVE        4497392 17-DEC-18
UNDOTBS2                4 ACTIVE        4497398 17-DEC-18
USERS                    5 ACTIVE        4497405 17-DEC-18

end backup 후 STATUS 확인

select a.tablespace_name, b.*
from dba_data_files a, v$backup b
where a.file_id = b.file#;
 
TABLESPACE_NAME             FILE# STATUS        CHANGE# TIME
------------------------------ ---------- ------------------ ---------- ---------
SYSTEM                    1 NOT ACTIVE        4497756 17-DEC-18
SYSAUX                    2 NOT ACTIVE        4497762 17-DEC-18
UNDOTBS1                3 NOT ACTIVE        4497768 17-DEC-18
UNDOTBS2                4 NOT ACTIVE        4497774 17-DEC-18
USERS                    5 NOT ACTIVE        4497781 17-DEC-18

참조 :