[Oracle] DROP TABLE PURGE 시점 확인하기

 

* 1줄요약 : audit_file_dest 파라미터 확인 후, 해당 위치의 dump 파일을 통해 확인

 

1. 완전한 삭제 purge

DROP TABLE PURGE 실수는 결코 마주하고 싶지 않은 실수이다.

 

휴지통(recyclebin)으로 Table을 이동시키는 DROP TABLE 명령어와 달리, 

PURGE 옵션은 Table을 완전 삭제하게 된다. 

 

메뉴얼에도 분명히 명시되어 있다.

You cannot roll back a DROP TABLE statement with the PURGE clause, nor can you recover the table if you have dropped it with the PURGE clause.

 

* DROP TABLE 메뉴얼 :

https://docs.oracle.com/database/121/SQLRF/statements_9003.htm#SQLRF01806

 

purge 옵션은 위험하지만, 테이블스페이스의 즉각 확보 등의 이유로 DBA는 작업 중 purge 옵션을 선호한다.

 

purge를 이용해 Table을 drop 했을 경우, DBA_OBJECTS, DBA_TABLES 와 같은 dictionary에서 데이터를 찾을 수 없으므로, 

DBMS에 쿼리로 Drop시점을 찾기 어렵다.

 

 

2. audit 파일을 통한 확인

이때, audit 파일을 통해 drop 시점을 확인할 수 있다.

 

audit은 '감사'라는 의미로 사용되며, DBMS에서 SQL 수행 log를 audit log 로 남길 수 있다.

 

하지만, 일반적인 B2C 서비스는 온라인 트랜잭션양이 많아 DBMS에서 SQL을 log level로 쌓기보단,

Chakra Max, Hiware와 같은 상용 솔루션으로  시스템과 DBMS의 접근을 제어한다.

 

하지만, 일반적인 운영모드의 DB에서 DDL은 audit 파일에 log가 남는다.

 

audit 관련한 전반적인 parameter를 확인하고, 'audit_file_dest' 에 해당하는 위치에 떨어진 dump 파일을 통해 확인할 수 있다.

-- audit 관련 parameter 조회
SELECT *
FROM gv$PARAMETER
WHERE NAME LIKE '%audit%'
ORDER BY 1,3,5;

 

아래와 같은 기록을 통해 DDL action 시점을 알 수 있다.

-- 덤프 파일 기록 예시

Thu Mar 23 19:21:22 2023 +09:00
LENGTH :'210'
ACTION :[56]'drop table schema.tablename purge'
DATABASE USER:[1]'.'
PRIVILEGE:[6]'SYSDBA'

 

 

3. 복구 절차

서버 환경에 따라 복구 절차는 다양하겠지만,

일반적으로 1) 스냅샷 복구 후 2) 아카이브 로그 복구 순서로 진행한다.

 

1) 스냅샷 복구 : AWS RDS 등 CSP 업체는 DB의 주기적 스냅샷 복원을 지원하며, 

                        대량 데이터의 온프레미스 환경 서버도 BCV 백업 등을 통한 스냅샷 백업을 지원한다.

 

2) 아카이브 로그 복구 : audit 로그를 통해 확인한 직전의 시점으로 복구하고,

 

3) DB LINK를 통해 운영 DB로 데이터를 이관한다.