| 일 | 월 | 화 | 수 | 목 | 금 | 토 |
|---|---|---|---|---|---|---|
| 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 |
Tags
- oracle
- SQL Server
- MSSQL
- oracle 파티션 datapump
- partition_options=merge
- create role
- oracle install
- oracleasm
- ora-28002: the password will expire within 7 days
- oracle SCN
- SSMS
- Oracle RAC
- oracle account_status expired
- oracle awr
- supplemental log 활성화
- ora-39083 ora-00439
- ora-00439: feature not enabled: partitioning
- oracle datapump
- oracle tde
- mssql database 삭제
- oracle supplemental
- oracle role 삭제
- oracle 테스트 데이터
- Oracle Database
- 오라클
- oracle system lock
- oracle pdb
- sql user 생성
- oracle dba_profile password_life_time
- ORA-00020
Archives
- Today
- Total
신문지한장
[B&R] oracle LOGMINER (11g 이하) 본문
💡 redo와 archive를 가지고 분석하여 트랜잭션 or 사용자 or 시간대별로 database에 가해진 변경작업에 대해 추적할 수 있다.
logminer로 복구 가능한 것
1. Database에 작성된 변경 사항들의 기록
2. 유형 (INSERT, UPDATE, DELETE,COMMIT/ROLLBACK,DDL또는 INDEX작업)
3. 그와 같이 변경이 발생하는 SCN(System Change Number)
4. 변경을 포함하는 트랜잭션 식별
5. 특정 트랜잭션이 커밋되는 SCN
6. 변경된 객체의 테이블 및 스키마 명칭
7. DML 또는 DDL 문을 발생시킨 사용자 정보
OS : CentOS7
DB : Oracle Database 11.2.0.4
- 사전 준비
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 유저로 설치
SQL> @?/rdms/badmidbmslm.sqln/
- supplemental logging 기능 상태 확인
ORACLE 9i R2 버전부터 supplemental logging 기능의 기본값 : disable 이유 : 사용하게 되면 redo log의 양이 커지기 때문에 성능저하 우려 → 실제 크게 차이 안남 = 사용하는게 유리하다고함(테스트해봐야함) 주의 : 활성화 시키고 난 후부터 생성된 redo log 만 분석이 되고 이 기능을 화성화 시키기 이전에 생성된 redo log는 분석 제한됨
SQL> select supplemental_log_data_min from v$database;
SUPPLEMENTAL_LOG
----------------
NO
- supplemental logging 기능 활성화
SQL> alter database add supplemental log data; (add>>drop)
Database altered.
- supplemental logging 기능 상태 재확인
SQL> select supplemental_log_data_min from v$database;
SUPPLEMENTAL_LOG
----------------
YES
장애 상황
- scott 계정에서 emp 테이블 empno=1000
- scott 계정에서 test2 테이블 삭제
SQL> create table emp2 as select * from emp;
- 해결 방법 (1)
SQL> @log //테이블이 생성되고 데이터가 입력된 후 삭제되었다는 내용이 1번 그룹에 들어간 것을 알 수 있음
GROUP# MEMBER MB SEQ# STATUS ARC
------ ------------------------------------------------------------ ---- ---- -------- -----
1 /oracle/app/oracle/oradata/test11/redo01.log 50 7 CURRENT NO
2 /oracle/app/oracle/oradata/test11/redo02.log 50 5 INACTIVE YES
3 /oracle/app/oracle/oradata/test11/redo03.log 50 6 INACTIVE YES
[test11]dbtest:/oracle/app/oracle/product/11.2.0/truin> :mkdir -p logmnr
[test11]dbtest:/oracle/app/oracle/product/11.2.0/dbs> :vi inittest11.ora
*.open_cursors=300
*.pga_aggregate_target=485m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1452m
*.undo_tablespace='UNDOTBS1'
utl_file_dir=/oracle/app/oracle/product/11.2.0/truin/logmnr11 --파라미터 파일 추가
SQL> @log //테이블이 생성되고 데이터가 입력된 후 삭제되었다는 내용이 1번 그룹에 들어간 것을 알 수 있음
GROUP# MEMBER MB SEQ# STATUS ARC
------ ------------------------------------------------------------ ---- ---- -------- -----
1 /oracle/app/oracle/oradata/test11/redo01.log 50 7 CURRENT NO
2 /oracle/app/oracle/oradata/test11/redo02.log 50 5 INACTIVE YES
3 /oracle/app/oracle/oradata/test11/redo03.log 50 6 INACTIVE YES
[test11]dbtest:/oracle/app/oracle/product/11.2.0/truin> :mkdir -p logmnr
[test11]dbtest:/oracle/app/oracle/product/11.2.0/dbs> :vi inittest11.ora
*.open_cursors=300
*.pga_aggregate_target=485m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1452m
*.undo_tablespace='UNDOTBS1'
utl_file_dir=/oracle/app/oracle/product/11.2.0/truin/logmnr11 //파라미터 파일 추가
db 재기동
SQL> shutdown immediate
SQL> startup pfile=$ORACLE_HOME/dbs/inittest11.ora
utl_file_dir 파라미터 확인
SQL> show parameter utl_file_dir
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string /oracle/app/oracle/product/11.
리두로그확인
SQL> @log
GROUP# MEMBER MB SEQ# STATUS ARC
------ ------------------------------------------------------------ ---- ---- -------- -----
1 /oracle/app/oracle/oradata/test11/redo01.log 50 7 CURRENT NO
2 /oracle/app/oracle/oradata/test11/redo02.log 50 5 INACTIVE YES
3 /oracle/app/oracle/oradata/test11/redo03.log 50 6 INACTIVE YES
SQL> exec dbms_logmnr_d.build('logmnrdict0701.ora','/oracle/app/oracle/product/11.2.0/truin/logmnr');
PL/SQL procedure successfully completed.
[test11]dbtest:/oracle/app/oracle/product/11.2.0/truin> :ls -al *
logmnr:
합계 36124
drwxr-xr-x 2 oracle dba 28 6월 30 16:46 .
drwxr-xr-x 3 oracle dba 35 6월 30 16:10 ..
-rw-r--r-- 1 oracle dba 36987507 6월 30 16:46 logmnrdict.ora
----분석 할 로그 파일을 등록
SQL> exec dbms_logmnr.add_logfile('/oracle/app/oracle/oradata/test11/redo01.log',1);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.add_logfile('/oracle/app/oracle/oradata/test11/redo02.log',3);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.add_logfile('/oracle/app/oracle/oradata/test11/redo03.log',3);
PL/SQL procedure successfully completed.
숫자 의미 : 1: 신규등록, 2: 파일 삭제, 3: 추가등록
(아카이브 로그파일을 넣어도됨)
----등록한 log 파일을 분석
SQL>
SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/oracle/app/oracle/product/11.2.0/truin/logmnr/logmnrdict0701.ora');
PL/SQL procedure successfully completed.
※ 분석 범위 설정 옵션
startscn (기본값 : 0)
endscn (기본값 : 0)
starttime (기본값 : 01-JAN-1988)
endtime (기본값 : 01-JAN-2988)
dictfilename (기본값 : null)
Options (기본값 : 0)
SQL> select to_char(timestamp,'YYYY-MM-DD:HH24:MI:SS'), seg_owner, username, sql_redo, sql_undo from v$logmnr_contents where seg_owner='SCOTT';
SQL> select to_char(timestamp,'YYYY-MM-DD:HH24:MI:SS'), seg_owner, username, sql_redo, sql_undo from v$logmnr_contents where sql_redo like '%create%' and seg_owner='SCOTT';
TO_CHAR(TIMESTAMP,' SEG_OWNER USERNAME SQL_REDO SQL_U
------------------- ---------- ---------- ---------------------------------------- -----
2022-06-28:15:35:30 SCOTT SYS create table scott.test1 (no number);
2022-06-28:15:44:49 SCOTT SYS create table scott.test2 (no number);
SQL> select to_char(timestamp,'YYYY-MM-DD:HH24:MI:SS'), seg_owner, username, sql_redo, sql_undo from v$logmnr_contents where seg_owner='SCOTT';
//실수로 update 한 구문(SQL_REDO)과 되돌릴수 있는 구문(SQL_UNDO)이 나옴
TO_CHAR(TIMESTAMP,' SEG_OWNER USERNAME SQL_REDO SQL_UNDO
------------------- ---------- ---------- ---------------------------------------- ------------------------------------------------------------
2022-06-28:15:18:04 SCOTT SYS alter user scott account unlock;
2022-06-28:15:22:58 SCOTT SYS alter user scott account unlock;
2022-06-28:15:23:04 SCOTT SYS alter user scott identified by VALUES '
25AB078993D8AC24' ;
2022-06-28:15:35:30 SCOTT SYS create table scott.test1 (no number);
2022-06-28:15:44:49 SCOTT SYS create table scott.test2 (no number);
2022-06-28:15:45:08 SCOTT SYS insert into "SCOTT"."TEST2"("NO") values delete from "SCOTT"."TEST2" where "NO" = '2' and ROWID = 'AA
('2'); ASYVAAEAAAAIXAAA';
2022-06-28:15:45:30 SCOTT SYS drop table scott.test1 purge;
7 rows selected.
//테이블 변경 후 commit
SQL> commit;
Commit complete.
로그마이너 중지
SQL> exec dbms_logmnr.end_logmnr();
//다른 방법(dictionary 참조)
SQL> @log
GROUP# MEMBER MB SEQ# STATUS ARC
------ ------------------------------------------------------------ ---- ---- -------- -----
1 /oracle/app/oracle/oradata/test11/redo01.log 50 7 CURRENT NO
2 /oracle/app/oracle/oradata/test11/redo02.log 50 5 INACTIVE YES
3 /oracle/app/oracle/oradata/test11/redo03.log 50 6 INACTIVE YES
분석 할 로그 파일을 등록(update 실행 시간을 모른다면 모든 redo, 아카이브 확인)
SQL>
exec dbms_logmnr.add_logfile('/oracle/app/oracle/oradata/test11/redo01.log ',1);
exec dbms_logmnr.add_logfile('/oracle/app/oracle/oradata/test11/redo02.log ',3);
exec dbms_logmnr.add_logfile('/oracle/app/oracle/oradata/test11/redo03.log ',3);
숫자 의미 : 1: 신규등록, 2: 파일 삭제, 3: 추가등록
(아카이브 로그파일을 넣어도됨)
SQL> exec dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
현재 운영중인 데이터베이스의 dictionary를 참조하게 할 수 있음
하지만 redo log file이 생성될 시점과 현재의 dictionary는 서로 다를 수 있다는 점을 고려해야함
또한 이 모드로는 'DDL tracking'을 사용할 수 없음
이 모드를 사용할 때는 DBMS_LOGMNR_D.BUILD의 과정이 필요없음
조회(한번에 여러개 파일을 확인하면 속도가 느림)
SQL> select to_char(timestamp,'YYYY-MM-DD:HH24:MI:SS'), seg_owner, username, sql_redo, sql_undo from v$logmnr
TO_CHAR(TIMESTAMP,' SEG_OWNER USERNAME SQL_REDO SQL_UNDO
------------------- ---------- ---------- ---------------------------------------- --------------------------
2022-06-28:15:18:04 SCOTT SYS alter user scott account unlock;
2022-06-28:15:22:58 SCOTT SYS alter user scott account unlock;
2022-06-28:15:23:04 SCOTT SYS alter user scott identified by VALUES '
25AB078993D8AC24' ;
2022-06-28:15:35:30 SCOTT SYS create table scott.test1 (no number);
2022-06-28:15:44:49 SCOTT SYS create table scott.test2 (no number);
2022-06-28:15:45:08 SCOTT SYS insert into "SCOTT"."TEST2"("NO") values delete from "SCOTT"."TEST2
('2'); ASYVAAEAAAAIXAAA';
2022-06-28:15:45:30 SCOTT SYS drop table scott.test1 purge;
TO_CHAR(TIMESTAMP,' SEG_OWNER USERNAME SQL_REDO SQL_UNDO
------------------- ---------- ---------- ---------------------------------------- --------------------------
2022-06-28:16:54:19 SCOTT SYS insert into "SCOTT"."TEST2"("NO") values delete from "SCOTT"."TEST2
('3'); ASYVAAEAAAAIXAAB';
2022-06-28:16:54:25 SCOTT SYS insert into "SCOTT"."TEST2"("NO") values delete from "SCOTT"."TEST2
('55'); AASYVAAEAAAAIXAAC';
2022-06-28:16:54:31 SCOTT SYS insert into "SCOTT"."TEST2"("NO") values delete from "SCOTT"."TEST2
('35'); AASYVAAEAAAAIXAAD';
2022-06-28:16:54:34 SCOTT SYS insert into "SCOTT"."TEST2"("NO") values delete from "SCOTT"."TEST2
('334'); AAASYVAAEAAAAIXAAE';
TO_CHAR(TIMESTAMP,' SEG_OWNER USERNAME SQL_REDO SQL_UNDO
------------------- ---------- ---------- ---------------------------------------- --------------------------
11 rows selected.
실수로 update 한 구문(SQL_REDO)과 되돌릴수 있는 구문(SQL_UNDO)이 나옴
바로 안나온다면 분석할 로그파일을 바꿔가며 확인
재변경하고 싶은 UNDO 사용
SQL> delete from "SCOTT"."TEST2" where "NO" = '35' and ROWID = 'AAASYVAAEAAAAIXAAD';
제대로 적용된 후 커밋
SQL> commit;
로그마이너 중지
SQL> exec dbms_logmnr.end_logmnr();
=========================================================================================================================
1. 로그마이너 확인 명령어
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'), seg_owner, username, sql_redo, sql_undo
from v$logmnr_contents
where sql_redo like '%update%'
and seg_owner='SCOTT';
2. 에러 : ORA-01336: specified dictionary file cannot be opened
로그마이너 패키지 실행 중 에러 발생
SQL> exec dbms_logmnr_d.build ('dict','/home/oracle/logmnr');
BEGIN dbms_logmnr_d.build ('dict','/home/oracle/logmnr'); END;
*
ERROR at line 1:
ORA-01336: specified dictionary file cannot be opened
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 6110
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 6200
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 12
ORA-06512: at line 1
>>해당 경로 확인
$ ls -al /home/oracle/logmnr
ls: cannot access /home/oracle/logmnr: No such file or directory
$ cd /home/oracle/logmnr
-bash: cd: /home/oracle/logmnr: No such file or directory
>>경로 생성
mkdir -p /home/oracle/logmnr
>>재실행
SQL> exec dbms_logmnr_d.build ('dict','/home/oracle/logmnr');
PL/SQL procedure successfully completed.
>> 해당 경로가 없어서 발생한 오류
에러 : ORA-01308: dictionary directory is not set
SQL> exec dbms_logmnr_d.build ('dict','/home/oracle/logmnr');
BEGIN dbms_logmnr_d.build ('dict','/home/oracle/logmnr'); END;
*
ERROR at line 1:
ORA-01308: dictionary directory is not set
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 7750
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 7406
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 7305
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 7698
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 7764
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 7912
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 12
ORA-06512: at line 1
SQL> show parameter utl_file_dir
해당 경로에 디렉토리 존재하지만 오류 발생
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string /home/oracle/logmnr
//create로 directory 재생성
SQL> create directory dict as '/home/oracle/logmnr';
Directory created.
//새로운 방법으로 DBMS_LOGMNR_D.build 사용
SQL>
BEGIN
SYS.DBMS_LOGMNR_D.build (
dictionary_filename => 'logmnrdict.ora',
dictionary_location => 'DICT'); <-디렉토리 이름 대문자로 입력
END;
/
PL/SQL procedure successfully completed.
원인 : Oracle 12R2부터는 UTL_FILE_DIR 파리미터가 사용되지 않음
UTL_FILE_DIR 초기화 매개 변수는 디렉토리 위치를 지정할때 사용되는 파라미터이지만
Oracle 12R2부터는 UTL_FILE_DIR 파리미터가 사용되지 않음
이버전과의 호환성을 위해 계속 지원되지만 대신 디렉토리를 사용할 것을 권장함
=======================================================================================
SQL> exec dbms_logmnr_d.build('logmnrdict.ora','/oracle/app/oracle/product/11.2.0/truin/logmnr');
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr_d.build('logmnrdict0630.ora','/oracle/app/oracle/product/11.2.0/truin/logmnr');
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.add_logfile('/oracle/app/oracle/oradata/test11/redo01.log',1);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.add_logfile('/oracle/app/oracle/oradata/test11/redo02.log',3);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.add_logfile('/oracle/app/oracle/oradata/test11/redo03.log',3);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/oracle/app/oracle/product/11.2.0/truin/logmnr/logmnrdict.ora');
PL/SQL procedure successfully completed.
'Oracle > Backup&Recovery' 카테고리의 다른 글
| [B&R] [HotBackup] 복구 (0) | 2024.08.07 |
|---|---|
| [B&R] [Clone DB] Drop table 복구 (0) | 2024.05.10 |
| [B&R] oracle Migration [ 11g to 19c (PDB) ] (0) | 2024.05.10 |
| [B&R] oracle RMAN (0) | 2024.05.10 |
| [B&R] oracle CSScan (0) | 2024.05.10 |