내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.9.0.0
방법 : 오라클 DDL(drop 등)도 로그마이너로 복구가 될까?
DML 문(update, delete 문 등) 를 잘못 입력했을 때 로그마이너 v$logmnr_contents 의 sql_undo 컬럼으로 복구가 가능함
하지만 DDL 문을 잘못 입력한 경우 복구가 가능 한지 여부를 테스트하고자 함
사전 설정
로그마이너 설정
로그마이너가 있는지 확인
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 | SQL> desc dbms_logmnr PROCEDURE ADD_LOGFILE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- LOGFILENAME VARCHAR2 IN OPTIONS BINARY_INTEGER IN DEFAULT FUNCTION COLUMN_PRESENT RETURNS BINARY_INTEGER Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SQL_REDO_UNDO NUMBER IN DEFAULT COLUMN_NAME VARCHAR2 IN DEFAULT PROCEDURE END_LOGMNR FUNCTION MINE_VALUE RETURNS VARCHAR2 Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SQL_REDO_UNDO NUMBER IN DEFAULT COLUMN_NAME VARCHAR2 IN DEFAULT PROCEDURE REMOVE_LOGFILE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- LOGFILENAME VARCHAR2 IN PROCEDURE START_LOGMNR Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- STARTSCN NUMBER IN DEFAULT ENDSCN NUMBER IN DEFAULT STARTTIME DATE IN DEFAULT ENDTIME DATE IN DEFAULT DICTFILENAME VARCHAR2 IN DEFAULT OPTIONS BINARY_INTEGER IN DEFAULT |
default로 설치가 되어 있지 않다면 sysdba 유저로 설치
1 | SQL> @?/rdbms/admin/dbmslm.sql |
supplemental logging 기능 상태 확인
1 2 3 4 5 | SQL> select supplemental_log_data_min from v$database; SUPPLEMENTAL_LOG ---------------- NO |
supplemental logging 기능 활성화
1 2 3 | SQL> alter database add supplemental log data; Database altered. |
supplemental logging 기능 상태 재확인
1 2 3 4 5 | SQL> select supplemental_log_data_min from v$database; SUPPLEMENTAL_LOG ---------------- YES |
시나리오
샘플 유저 생성 및 권한 부여
1 2 3 4 5 6 7 | SQL> create user new identified by new account unlock default tablespace users quota unlimited on users; User created. SQL> grant resource, connect, dba to new; Grant succeeded. |
로그마이너 사용시 찾는 시간이 오래 걸리기때문에
특정 로그파일에서만 작업을 하고 그 로그파일만 로그마이너를 이용하여 읽을 예정
리두로그 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SQL> set lines 200 set pages 1000 col member for a60 select l.group#, member, archived, l.status, (bytes/1024/1024) MB from v$log l, v$logfile f where f.group# = l.group# order by 1 / GROUP# MEMBER ARC STATUS MB ---------- ------------------------------------------------------------ --- ---------------- ---------- 1 /app/oracle/oradata/ORCL19/redo01.log YES INACTIVE 1024 2 /app/oracle/oradata/ORCL19/redo02.log YES INACTIVE 1024 3 /app/oracle/oradata/ORCL19/redo03.log NO CURRENT 1024 |
현재 3번 redo 가 current 상태임
모두 아카이브로 내려씀
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | SQL> SQL> alter system checkpoint; System altered. SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. SQL> / System altered. |
다시 리두로그 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SQL> set pages 1000 col member for a60 select l.group#, member, archived, l.status, (bytes/1024/1024) MB from v$log l, v$logfile f where f.group# = l.group# order by 1 / GROUP# MEMBER ARC STATUS MB ---------- ------------------------------------------------------------ --- ---------------- ---------- 1 /app/oracle/oradata/ORCL19/redo01.log YES INACTIVE 1024 2 /app/oracle/oradata/ORCL19/redo02.log NO CURRENT 1024 3 /app/oracle/oradata/ORCL19/redo03.log YES INACTIVE 1024 |
2번 redo가 current 가 됨
샘플 테이블 생성
1 2 3 4 5 6 7 8 9 | SQL> create table ddl_table as select * from dba_objects; Table created. SQL> select count(*) from ddl_table; COUNT(*) ---------- 23866 |
샘플 테이블 업데이트 및 삭제
1 2 3 4 5 6 7 8 9 10 11 | SQL> update ddl_table set object_id = 0; 23866 rows updated. SQL> commit; Commit complete. SQL> drop table ddl_table; Table dropped. |
현재 리두로그 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SQL> set pages 1000 col member for a60 select l.group#, member, archived, l.status, (bytes/1024/1024) MB from v$log l, v$logfile f where f.group# = l.group# order by 1 / GROUP# MEMBER ARC STATUS MB ---------- ------------------------------------------------------------ --- ---------------- ---------- 1 /app/oracle/oradata/ORCL19/redo01.log YES INACTIVE 1024 2 /app/oracle/oradata/ORCL19/redo02.log NO CURRENT 1024 3 /app/oracle/oradata/ORCL19/redo03.log YES INACTIVE 1024 |
계속 2번 redo 가 current임
로그마이너 사용
디렉토리 생성
1 2 3 4 5 | SQL> !mkdir -p /home/oracle/logmnr SQL> create directory dict as '/home/oracle/logmnr'; Directory created. |
딕셔너리 파일 생성
1 2 3 4 5 6 7 8 9 | SQL> BEGIN SYS.DBMS_LOGMNR_D.build ( dictionary_filename => 'logmnrdict.ora', dictionary_location => 'DICT'); --디렉토리 이름 대문자로 입력 END; / PL/SQL procedure successfully completed. |
파일 생성 확인
1 2 3 4 5 | SQL> !ls -al /home/oracle/logmnr total 24068 drwxr-xr-x 2 oracle dba 46 Jan 31 21:48 . drwx------. 3 oracle dba 73 Jan 31 21:29 .. -rw-r--r-- 1 oracle dba 24639488 Jan 31 22:03 logmnrdict.ora |
분석 할 로그 파일을 등록(현재 current인 redo 2번)
1 2 3 | SQL> exec dbms_logmnr.add_logfile('/app/oracle/oradata/ORCL19/redo02.log',1); PL/SQL procedure successfully completed. |
등록한 log 파일을 분석
1 2 3 | SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr/logmnrdict.ora'); PL/SQL procedure successfully completed. |
로그마이너 update 문 조회
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 32 33 34 35 36 | SQL> set line 200 col timestamp for a20 col seg_owner for a10 col username for a10 col sql_redo for a120 col sql_undo for a120 select to_char(timestamp,'YYYY-MM-DD:HH24:MI:SS') time, seg_owner, username, sql_redo, sql_undo from v$logmnr_contents where sql_redo like '%update%' and seg_owner='NEW'; TIME SEG_OWNER USERNAME SQL_REDO ------------------- ---------- ---------- ------------------------------------------------------------------------------------------------------------------------ SQL_UNDO ------------------------------------------------------------------------------------------------------------------------ 2021-01-31:21:56:55 NEW NEW update "NEW"."BIN$ujLHb7HffCLgU/AKqMBQ1w==$0" set "COL 4" = HEXTORAW('80') where "COL 4" = HEXTORAW('c113') and ROWID = 'AAANJiAAEAAAFqDAAA'; update "NEW"."BIN$ujLHb7HffCLgU/AKqMBQ1w==$0" set "COL 4" = HEXTORAW('c113') where "COL 4" = HEXTORAW('80') and ROWID = 'AAANJiAAEAAAFqDAAA'; 2021-01-31:21:56:55 NEW NEW update "NEW"."BIN$ujLHb7HffCLgU/AKqMBQ1w==$0" set "COL 4" = HEXTORAW('80') where "COL 4" = HEXTORAW('c121') and ROWID = 'AAANJiAAEAAAFqDAAB'; update "NEW"."BIN$ujLHb7HffCLgU/AKqMBQ1w==$0" set "COL 4" = HEXTORAW('c121') where "COL 4" = HEXTORAW('80') and ROWID = 'AAANJiAAEAAAFqDAAB'; . . |
현재 테이블을 drop 한 상태로 recyclebin에 있는 이름(BIN$~)로 표시됨
내가 입력한 update 구문인 sql_redo 와 그 update 구문을 복구할 구문(sql_undo) 두가지가 모두 나옴
로그마이너 drop 문 조회
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SQL> set line 200 col timestamp for a20 col seg_owner for a10 col username for a10 col sql_redo for a120 col sql_undo for a120 select to_char(timestamp,'YYYY-MM-DD:HH24:MI:SS') time, seg_owner, username, sql_redo, sql_undo from v$logmnr_contents where sql_redo like '%drop%' and seg_owner='NEW'; TIME SEG_OWNER USERNAME SQL_REDO ------------------- ---------- ---------- ------------------------------------------------------------------------------------------------------------------------ SQL_UNDO ------------------------------------------------------------------------------------------------------------------------ 2021-01-31:21:57:25 NEW NEW drop table ddl_table AS "BIN$ujLHb7HffCLgU/AKqMBQ1w==$0" ; |
drop 문의 경우 시간과 sql_redo는 나오지만 sql_undo(복구구문)은 나오지 않음
drop 구문은 로그마이너로 복구 불가능
recyclebin 에서 복구
1 2 3 | SQL> flashback table ddl_table to before drop; Flashback complete. |
테이블 확인
1 2 3 4 5 | SQL> select count(*) from ddl_table; COUNT(*) ---------- 23866 |
결론 : ddl drop 구문은 로그마이너로 복구가 불가능함
하지만 flashback 기능 이용하거나 해당 테이블 생성 이전 시점으로 가서 복구하는
Point In Time Recovery (PITR)을 통해 복구는 가능함
'ORACLE > Backup&Recover' 카테고리의 다른 글
| 오라클 11g R2 rman 백업 후 until scn 을 이용한 복구 방법 (2) | 2021.04.23 |
|---|---|
| 오라클 11g R2 엔진 백업 복구 테스트(windows) (0) | 2021.02.06 |
| 오라클 11g R2 datapump db full 백업 (2) | 2021.01.27 |
| 오라클 19c rman incarnation 설명 및 복구 시나리오 (0) | 2021.01.21 |
| 오라클 19c rman catalog db 이용 백업 복구 (0) | 2021.01.21 |