{"id":1056,"date":"2016-01-01T13:45:32","date_gmt":"2016-01-01T13:45:32","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=1056"},"modified":"2016-01-01T13:45:32","modified_gmt":"2016-01-01T13:45:32","slug":"oracle-log-miner","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=1056","title":{"rendered":"ORACLE Log Miner"},"content":{"rendered":"

 # Oracle Log Miner(8i \ub4f1\uc7a5)<\/SPAN><\/P>
\n

-Log Miner \uc720\ud2f8\ub9ac\ud2f0\ub294 Redo Log file\uc5d0 \uc788\ub294 \ub0b4\uc6a9\uc744 \ucd94\ucd9c\ud574\uc11c SQL \ubb38\uc7a5\uc73c\ub85c \ubcc0\ud658\ud574\uc90d\ub2c8\ub2e4.<\/SPAN><\/DIV>
\n
-\ub370\uc774\ud130\ubca0\uc774\uc2a4\uc5d0\uc11c \ubcc0\uacbd\ub41c \ub0b4\uc5ed\uc744 \ud2b8\ub79c\uc7ad\uc158, \uc0ac\uc6a9\uc790, \ud14c\uc774\ube14, \ubc1c\uc0dd\uc2dc\uac04\ub300 \ubcc4\ub85c \ucd94\uc801\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/SPAN><\/DIV>
\n

<\/SPAN><\/DIV>
\n
\uc544\ub798\uc640 \uac19\uc740 \uc81c\uc57d\uc0ac\ud56d\uc774 \uc788\uc2b5\ub2c8\ub2e4.<\/SPAN><\/DIV>
\n
1. log miner\ub294 8i \uc774\ud6c4 \ubc84\uc804\uc5d0\uc11c\ub9cc \uc791\ub3d9\ud569\ub2c8\ub2e4.<\/SPAN><\/DIV>
\n
2. \ubd84\uc11d \ub300\uc0c1 \uc778\uc2a4\ud134\uc2a4\uc640 \uac19\uc740 \ud50c\ub7ab\ud3fc\uc5d0\uc11c \ub3d9\uc791\ud558\ub294 \uac19\uc740 \uce90\ub9ad\ud130\uc14b\uc744 \uc0ac\uc6a9\ud558\ub294 db\uc5d0\uc11c \uc0dd\uc131\ub41c redo log file\ub9cc \ubd84\uc11d\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/SPAN><\/DIV>
\n
3. \ubd84\uc11d \ub300\uc0c1 \uc778\uc2a4\ud134\uc2a4\uc640 Redo log\uac00 \uc0dd\uc131\ub41c \uc778\uc2a4\ud134\uc2a4\ub294 DB_BLOCK_SIZE\uac00 \ub3d9\uc77c\ud574\uc57c \ud569\ub2c8\ub2e4. \ub9cc\uc57d \ub2e4\ub97c \uacbd\uc6b0 corruption\uc774 \ubc1c\uc0dd\ud588\ub2e4\ub294 \ub0b4\uc6a9\uc774 \uae30\ub85d\ub429\ub2c8\ub2e4.<\/SPAN><\/DIV>
\n

<\/SPAN><\/DIV>
\n
# Log miner \uc124\uc815\ud574\uc11c Redo Log \ubd84\uc11d <\/SPAN><\/DIV>
\n
– log miner \uc124\uc815\ud558\uae30<\/SPAN><\/DIV>
\n
**Supplemental log \ud65c\uc131\ud654\ud558\uae30(9i\ubd80\ud130 default\uac00 disable\uc784, \ud544\ud788 enable \ud574\uc904 \uac83)<\/SPAN><\/DIV>
\n
– instance vector\uc5d0 \ud64d\uae38\ub3d9\uc774\ub77c\ub294 \uc774\ub984\uc774 \uc5ec\ub7ff \uc788\ub294\ub370 \uc5ec\uae30\uc5d0 update\ub97c \ud64d\uae38\ub3d9->\uc77c\uc9c0\ub9e4\ub85c \ud574\uc900\ub2e4\uba74 \ubaa8\ub4e0 \ub370\uc774\ud130\uac00 \uc5c5\ub370\uc774\ud2b8 \ub420 \uac83\uc774\ub2e4.<\/SPAN><\/DIV>
\n
 \uc774\ub97c \ubc29\uc9c0\ud558\uae30 \uc704\ud574 DATA+PK \ud558\uc5ec \uad6c\ubd84\uc744 \uc9c0\uc5b4 \uc904\uc218 \uc788\uac8c\ub054 \uc124\uc815 \ud558\ub294 \uac83\uc785\ub2c8\ub2e4<\/SPAN><\/DIV>
\n

<\/SPAN><\/DIV>
\n
SQL> alter database add supplemental log data;<\/p>\n

Database altered.<\/p>\n

SQL> select supplemental_log_data_min from v$database;<\/p>\n

SUPPLEME
——–
YES <\/SPAN><\/DIV>
\n


<\/SPAN><\/DIV>
\n
1. parameter \ud30c\uc77c\uc5d0 dictionary file\uc774 \uc0dd\uc131\ub420 \uacbd\ub85c \uc124\uc815\uc744 \ucd94\uac00\ud569\ub2c8\ub2e4.<\/SPAN><\/DIV>
\n
*.utl_file_dir=”\/data\/logmnr” <– redo log\ub97c \ubd84\uc11d\ud560 \ub54c \uc0ac\uc6a9\ud558\ub294 target database\uc758 \uc815\ubcf4\ub97c \uc800\uc7a5\ud558\uace0 \uc788\ub294 \ud30c\uc77c\uc785\ub2c8\ub2e4. \uc0dd\uc131\ud6c4\uc5d0 OS\ud3b8\uc9d1\uae30\ub85c \uc5f4\uc5b4\uc11c \uc0b4\ud3b4\ubcf4\uba74 \ub0b4\uc6a9\uc744 \ud655\uc778\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/SPAN><\/DIV>
\n

<\/SPAN><\/DIV>
\n
2. DB \uc7ac\uc2dc\uc791<\/SPAN><\/DIV>
\n

<\/SPAN><\/DIV>
\n
3.\ub515\uc154\ub108\ub9ac \ud30c\uc77c \uc0dd\uc131<\/SPAN><\/DIV>
\n
SQL>exec dbms_logmnr_d.build (‘dict’, ‘\/data\/logmnr’); <—(\ud30c\uc77c\uc774\ub984,\uc800\uc7a5\ud560 \uacbd\ub85c)<\/SPAN><\/DIV>
\n

<\/SPAN><\/DIV>
\n
4. \ubd84\uc11d\ud558\uae30\ub97c \uc6d0\ud558\ub294 log\ud30c\uc77c\uc744 \ub4f1\ub85d\ud569\ub2c8\ub2e4.<\/SPAN><\/DIV>
\n

<\/SPAN><\/DIV>
\n
SQL> @log<\/p>\n

GROUP# MEMBER                                MB SEQ# STATUS     ARC
—— ——————————————— —- —- ——– —–
     1 \/data\/imsi\/redo01.log                      50    1 INACTIVE YES
     2 \/data\/imsi\/redo02.log                      50    2 CURRENT     NO
     3 \/data\/imsi\/redo03.log                      50    0 UNUSED     YES
<\/SPAN><\/DIV>
\n


<\/SPAN><\/DIV>
\n
SQL>exec dbms_logmnr.add_logfile(‘\/data\/imsi\/redo01.log’,1);<\/SPAN><\/DIV>
\n

<\/SPAN><\/DIV>
\n
\uc22b\uc790 1\uc740 \uc0c8\ud30c\uc77c \ub4f1\ub85d, 2\ub294 \uae30\uc874\ud30c\uc77c \uc0ad\uc81c, 3\uc740 \ub2e4\ub978 \ud30c\uc77c \ucd94\uac00\ub97c \uc758\ubbf8\ud569\ub2c8\ub2e4.<\/SPAN><\/DIV>
\n

<\/SPAN><\/DIV>
\n
5. \ub4f1\ub85d\ub41c log \ud30c\uc77c \ubd84\uc11d\ud569\ub2c8\ub2e4.<\/SPAN><\/DIV>
\n
exec dbms_logmnr.start_logmnr(dictfilename=>’\/home\/oracle\/logminer\/dict.dat’);<\/SPAN><\/DIV>
\n

<\/SPAN><\/DIV>
\n
6. \ucc3e\uace0\uc790 \ud558\ub294 \ub0b4\uc6a9\uc744 \uc870\ud68c\ud569\ub2c8\ub2e4<\/SPAN><\/DIV>
\n

<\/SPAN><\/DIV>
\n
set line 200
col timestamp for a20
col seg_owner for a10
col username for a10
col sql_redo for a50
select timestamp,seg_owner,username,sql_redo from v$logmnr_contents where sql_redo like ‘%drop%’ and seg_owner= ‘SCOTT’;<\/SPAN><\/DIV>
\n

<\/SPAN><\/DIV>
\n
\uad00\ub828 \ud14c\uc774\ube14 : v$logmnr_logs, v$logmnr_contents<\/SPAN><\/DIV><\/SPAN><\/p>\n","protected":false},"excerpt":{"rendered":"

 # Oracle Log Miner(8i \ub4f1\uc7a5) -Log Miner \uc720\ud2f8\ub9ac\ud2f0\ub294 Redo Log file\uc5d0 \uc788\ub294 \ub0b4\uc6a9\uc744 \ucd94\ucd9c\ud574\uc11c SQL \ubb38\uc7a5\uc73c\ub85c \ubcc0\ud658\ud574\uc90d\ub2c8\ub2e4. -\ub370\uc774\ud130\ubca0\uc774\uc2a4\uc5d0\uc11c \ubcc0\uacbd\ub41c \ub0b4\uc5ed\uc744 \ud2b8\ub79c\uc7ad\uc158, \uc0ac\uc6a9\uc790, \ud14c\uc774\ube14, \ubc1c\uc0dd\uc2dc\uac04\ub300 \ubcc4\ub85c \ucd94\uc801\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4. \uc544\ub798\uc640 \uac19\uc740 \uc81c\uc57d\uc0ac\ud56d\uc774 \uc788\uc2b5\ub2c8\ub2e4. 1. log miner\ub294 8i \uc774\ud6c4 \ubc84\uc804\uc5d0\uc11c\ub9cc \uc791\ub3d9\ud569\ub2c8\ub2e4. 2. \ubd84\uc11d \ub300\uc0c1 \uc778\uc2a4\ud134\uc2a4\uc640 \uac19\uc740 \ud50c\ub7ab\ud3fc\uc5d0\uc11c \ub3d9\uc791\ud558\ub294 \uac19\uc740 \uce90\ub9ad\ud130\uc14b\uc744 \uc0ac\uc6a9\ud558\ub294 db\uc5d0\uc11c \uc0dd\uc131\ub41c redo log file\ub9cc \ubd84\uc11d\ud560 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[9],"tags":[],"jetpack_featured_media_url":"","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1056"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1056"}],"version-history":[{"count":0,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1056\/revisions"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1056"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1056"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1056"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}