logmnr\u5e38\u89c1\u4f7f\u7528\u65b9\u6cd5\uff0c\u4ee5\u524d\u7684\u4e00\u4e2a\u6d4b\u8bd5\u4f8b\u5b50\u3002
\n--\u6570\u636e\u5e93\u7248\u672c<\/p>\n
\r\nSQL> select * from gv$version;\r\n\r\n INST_ID BANNER\r\n---------- ----------------------------------------------------------------\r\n 1 Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod\r\n 1 PL\/SQL Release 10.2.0.5.0 - Production\r\n 1 CORE 10.2.0.5.0 Production\r\n 1 TNS for Linux: Version 10.2.0.5.0 - Production\r\n 1 NLSRTL Version 10.2.0.5.0 - Production\r\n 2 Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod\r\n 2 PL\/SQL Release 10.2.0.5.0 - Production\r\n 2 CORE 10.2.0.5.0 Production\r\n 2 TNS for Linux: Version 10.2.0.5.0 - Production\r\n 2 NLSRTL Version 10.2.0.5.0 - Production\r\n\r\n10 rows selected.\r\n<\/pre>\n--\u51c6\u5907\u914d\u7f6e\uff1a
\n--\u6253\u5f00\u6570\u636e\u5e93\u8865\u5145\u65e5\u5fd7\u529f\u80fd<\/p>\n\r\nSQL> select LOG_MODE from v$database;\r\n\r\nLOG_MODE\r\n------------\r\nARCHIVELOG\r\n\r\nSQL> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI from v$database;\r\n\r\nSUPPLEME SUP SUP\r\n-------- --- ---\r\nNO NO NO\r\n\r\nSQL> alter database add supplemental log data; <<\u5982\u679c\u4e0d\u5f00\u542fsupplemental log\uff0c\u5219\u5728\u67e5\u8be2\u65e5\u5fd7\u5206\u6790\u8868\u7684\u65f6\u5019\uff0cusername\u5217\u5c06\u4e3a\u7a7a\uff1b\u4ee5\u53ca\u53d1\u751f\u884c\u8fc1\u79fb\u7684redo\u65e0\u6cd5\u8ffd\u8e2a\u3002\r\n\r\nDatabase altered.\r\n\r\nSQL> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI from v$database;\r\n\r\nSUPPLEME SUP SUP\r\n-------- --- ---\r\nYES NO NO\r\n<\/pre>\n--\u8bbe\u7f6e\u4f1a\u8bdd\u65f6\u95f4\u683c\u5f0f<\/p>\n
\r\nSQL> alter session set nls_date_format ='yyyy\/mm\/dd hh24:mi:ss';\r\n\r\nSession altered.\r\n\r\nSQL> select sysdate from dual;\r\n\r\nSYSDATE\r\n-------------------\r\n2012\/09\/18 10:35:13\r\n<\/pre>\n--\u521b\u5efa\u6d4b\u8bd5\u6570\u636e <\/p>\n
\r\nSQL> create tablespace test datafile '+DATA\/racdb\/test01.dbf' size 2m;\r\n\r\nTablespace created.\r\n\r\nSQL> create user test identified by test default tablespace test quota unlimited on test;\r\n\r\nUser created.\r\n\r\nSQL> grant resource,connect to test;\r\n\r\nGrant succeeded.\r\n\r\nSQL> conn test\/test;\r\nConnected.\r\nSQL> create table test(id number,name varchar2(10));\r\n\r\nTable created.\r\n\r\nSQL> select a.group#,thread#,member,b.status,archived from v$logfile a,v$log b where a.group#=b.group# order by 1;\r\n\r\n GROUP# THREAD# MEMBER STATUS ARC\r\n---------- ---------- ------------------------- ---------------- ---\r\n 1 1 +DATA\/racdb\/redo01.log CURRENT NO\r\n 2 1 +DATA\/racdb\/redo02.log INACTIVE YES\r\n 3 2 +DATA\/racdb\/redo03.log INACTIVE YES\r\n 4 2 +DATA\/racdb\/redo04.log CURRENT NO\r\n\r\nSQL> conn test\/test\r\nConnected.\r\nSQL> insert into test values(1,'yallonking');\r\n\r\n1 row created.\r\n\r\nSQL> conn \/as sysdba\r\nConnected.\r\nSQL> create user yallonking identified by yallonking default tablespace test quota unlimited on test;\r\n\r\nUser created.\r\n\r\nSQL> grant connect,resource to yallonking;\r\n\r\nGrant succeeded.\r\n\r\nSQL> conn yallonking\/yallonking\r\nConnected.\r\nSQL> create table yallonking (id number,name varchar2(10));\r\n\r\nTable created.\r\n\r\nSQL> insert into yallonking values (2,'test');\r\n\r\n1 row created.\r\n\r\nSQL> commit;\r\n\r\nCommit complete.\r\n\r\nSQL> conn test\/test\r\nConnected.\r\nSQL> alter table test modify name varchar2(20);\r\n\r\nTable altered.\r\n\r\nSQL> update test set id=3 where id=1;\r\n\r\n1 row updated.\r\n\r\nSQL> delete from test where id=3;\r\n\r\n1 row deleted.\r\n\r\nSQL> commit;\r\n\r\nCommit complete.\r\n\r\nSQL> insert into test values (4,'test')\r\n 2 ;\r\n\r\n1 row created.\r\n\r\nSQL> rollback;\r\n\r\nRollback complete.\r\n\r\nSQL> conn \/as sysdba\r\nConnected.\r\n\r\nSQL> alter system switch logfile;\r\n\r\nSystem altered.\r\n\r\nSQL> alter system checkpoint;\r\n\r\nSystem altered.\r\n\r\nSQL> select a.group#,thread#,member,b.status,archived from v$logfile a,v$log b where a.group#=b.group# order by 1;\r\n\r\n GROUP# THREAD# MEMBER STATUS ARC\r\n---------- ---------- ------------------------- ---------------- ---\r\n 1 1 +DATA\/racdb\/redo01.log INACTIVE YES\r\n 2 1 +DATA\/racdb\/redo02.log CURRENT NO\r\n 3 2 +DATA\/racdb\/redo03.log INACTIVE YES\r\n 4 2 +DATA\/racdb\/redo04.log CURRENT NO\r\n<\/pre>\n\u793a\u4f8b\u4e00\uff1a\u67e5\u770b\u6700\u540e\u4e00\u4e2a\u5f52\u6863\u65e5\u5fd7\u4e2d\u6240\u6709\u7684\u64cd\u4f5c\u4fe1\u606f<\/strong>
\n\u6ce8\uff1a\u6b64logmnr\u67e5\u8be2\u89c6\u56fe\u4e2d\u542b\u6709\u672a\u63d0\u4ea4\u4e8b\u52a1\uff0c\u4e14\u8f93\u51fa\u6309\u65f6\u95f4\u987a\u5e8f\u8f93\u51fa<\/strong>
\n--\u786e\u5b9a\u5f52\u6863\u65e5\u5fd7<\/p>\n\r\nSQL> select name from v$archived_log where first_time=(select max(first_time) from v$archived_log);\r\n\r\nNAME\r\n--------------------------------------------------------------------------------\r\n+DATA\/racdb\/archivelog\/2012_09_18\/thread_1_seq_5.278.794314659\r\n\r\n<\/pre>\n--\u5c06\u65e5\u5fd7\u6dfb\u52a0\u5230logmnr\u4e2d<\/p>\n
\r\nSQL> execute dbms_logmnr.add_logfile(logfilename=>'+DATA\/racdb\/archivelog\/2012_09_18\/thread_1_seq_5.278.794314659',options=>dbms_logmnr.new);\r\n\r\nPL\/SQL procedure successfully completed.\r\n<\/pre>\n--\u4f7f\u7528logmnr\u8fdb\u884c\u5206\u6790<\/p>\n
\r\nSQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog + dbms_logmnr.print_pretty_sql);\r\n\r\nPL\/SQL procedure successfully completed.\r\n<\/pre>\n--\u67e5\u8be2logmnr\u89c6\u56fe<\/p>\n
\r\nselect username,(xidusn||'.'||xidslt||'.'||xidsqn)as xid,sql_redo,sql_undo from v$logmnr_contents where username in ('TEST','YALLONKING');\r\n\r\n<\/pre>\n--\u7ed3\u675flogmnr<\/p>\n
\r\nSQL> execute dbms_logmnr.end_logmnr();\r\n\r\nPL\/SQL procedure successfully completed.\r\n<\/pre>\n\u793a\u4f8b\u4e8c\uff1a\u67e5\u770b\u6700\u540e\u4e00\u4e2a\u5f52\u6863\u65e5\u5fd7\u4e2d\u6240\u6709\u7684\u64cd\u4f5c\u4fe1\u606f<\/strong>
\n\u6ce8\uff1a\u6b64logmnr\u67e5\u8be2\u89c6\u56fe\u4e2d\u53ea\u542b\u6709\u5df2\u63d0\u4ea4\u4e8b\u52a1\uff0c\u4e14\u8f93\u51fa\u6309\u65f6\u95f4\u987a\u5e8f\u6309\u7528\u6237\u5206\u7ec4\u8f93\u51fa<\/strong>
\n--\u786e\u5b9a\u5f52\u6863\u65e5\u5fd7<\/p>\n\r\nSQL> select name from v$archived_log where first_time=(select max(first_time) from v$archived_log);\r\n\r\nNAME\r\n--------------------------------------------------------------------------------\r\n+DATA\/racdb\/archivelog\/2012_09_18\/thread_1_seq_5.278.794314659\r\n<\/pre>\n--\u5c06\u65e5\u5fd7\u6dfb\u52a0\u5230logmnr\u4e2d<\/p>\n
\r\nSQL> execute dbms_logmnr.add_logfile(logfilename=>'+DATA\/racdb\/archivelog\/2012_09_18\/thread_1_seq_5.278.794314659',options=>dbms_logmnr.new);\r\n\r\nPL\/SQL procedure successfully completed.\r\n<\/pre>\n--\u4f7f\u7528logmnr\u8fdb\u884c\u5206\u6790<\/p>\n
\r\nSQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog + dbms_logmnr.committed_data_only + dbms_logmnr.print_pretty_sql);\r\n\r\nPL\/SQL procedure successfully completed.\r\n<\/pre>\n--\u67e5\u8be2logmnr\u89c6\u56fe<\/p>\n
\r\nselect username,(xidusn||'.'||xidslt||'.'||xidsqn)as xid,sql_redo,sql_undo from v$logmnr_contents where username in ('TEST','YALLONKING');\r\n\r\n<\/pre>\n--\u7ed3\u675flogmnr<\/p>\n
\r\nSQL> execute dbms_logmnr.end_logmnr();\r\n\r\nPL\/SQL procedure successfully completed.\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"logmnr\u5e38\u89c1\u4f7f\u7528\u65b9\u6cd5\uff0c\u4ee5\u524d\u7684\u4e00\u4e2a\u6d4b\u8bd5\u4f8b\u5b50\u3002 –\u6570\u636e\u5e93\u7248\u672c SQL> select * from g […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[9],"tags":[216],"_links":{"self":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/934"}],"collection":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=934"}],"version-history":[{"count":5,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/934\/revisions"}],"predecessor-version":[{"id":939,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/934\/revisions\/939"}],"wp:attachment":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=934"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=934"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=934"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}