Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to use oracle log mining to achieve rollback", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "how to use oracle log mining to achieve rollback" bar!

Archery's support for mysql is perfect, and although it also supports oracle, it is a great pity that oracle only supports query and execution, but not backup and auditing.

Now, we urgently need a backup function of oracle, so I think of the function that comes with oracle-log mining, which can back up sql statements.

The following is my demonstration of log mining.

0. Start the minimum log, this step is to make the log more detailed and complete (must)

Alter database add supplemental log data

1. Login user

SQL > conn czx/xxx;Connected.

2. Create a test table

SQL > create table t (id number, name varchar2 (10)); Table created.

3. Insert some test data

SQL > insert into t values, 1 row created.SQL > insert into t values, 1 row created.SQL > insert into t values, 3 insert into t values, 1 row created.SQL > commit;Commit complete.SQL > select * from t ID NAME--1 a 2 b 3 c 4 d 5 e

4. View the sid and serial# of the current session

SQL > select s.sidjudics.serial# from v$session s where s.sid = (select sid from v$mystat where rownum = 1); SID SERIAL#-- 36 3755

5. Check the current database time for later starttime

SQL > alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss';Session altered.SQL > select sysdate from dual;SYSDATE-2020-03-15 19:02:53

6. Execute some sql statements to simulate the live sql

SQL > delete from t where id

Update t set name = 'dddd' where id=4;1 row updated.SQL > insert into t values (6,' f'); 1 row created.SQL > insert into t values (7,'g'); 1 row created.SQL > commit;Commit complete.SQL > select * from t NAME- ID NAME--3 c 4 dddd 5 e 6 f 7 g

7. Check the current database time again for later endtime

SQL > select sysdate from dual;SYSDATE-2020-03-15 19:08:16

8. Through the time range, the redo log is automatically loaded into the v$logmnr_contents view by online dictionary

Note: starttime and endtime are the two current database times obtained above.

SQL > begindbms_logmnr.start_logmnr (starttime= > to_date), endtime= > to_date (SQL > begindbms_logmnr.start_logmnr), options= > dbms_logmnr.dict_from_online_catalog + dbms_logmnr.continuous_mine), end;/PL/SQL procedure successfully completed.

9. If you look at the sql_redo and sql_undo contents of the v$logmnr_contents view, you will get the original statement and the corresponding rollback statement that executed the sql.

Sql > SELECT sql_redo, sql_undo, SESSION#, serial#, username, OS_USERNAME, MACHINE_NAME, SESSION_INFO, operation XidFROM v$logmnr_contents WHERE SESSION# = (SELECT s.sid FROM v$session s WHERE s.sid = (SELECT sid FROM v$mystat WHERE ROWNUM = 1)) AND serial# = (SELECT serial# FROM v$session s WHERE s.sid = (SELECT sid FROM v$mystat WHERE ROWNUM = 1)) SESSION# SERIAL# USERNAME OPERATION XID SQL_REDO SQL_UNDO -36 3755 CZX START 0A000900C8060000 set transaction read write 36 3755 CZX DELETE 0A000900C8060000 delete from "CZX". "T" where "ID" ='1' and "NAME" ='a 'and ROWID =' AAAVSdAAEAA insert into "CZX". "T" ("ID", "NAME") values 36 3755 CZX DELETE 0A000900C8060000 delete from "CZX". "T" where "ID" ='2' and "NAME" ='b' and ROWID = 'AAAVSdAAEAA insert into "CZX". "T" ("ID", "NAME") values 36 3755 CZX UPDATE 0A000900C8060000 update "CZX". "T" set "NAME" = 'dddd' where "ID" =' 4' and "NAME" ='d' and ROWID update "CZX". "T" set "NAME" ='d' where "ID" ='4' and "NAME" = 'dddd' and ROWID 36 3755 CZX "INSERT 0A000900C8060000 insert into" CZX "T" ("ID" "NAME") values ('6 years, 5 minutes) Delete from "CZX". "T" where "ID" ='6' and "NAME" ='f 'and ROWID =' AAAVSdAAEAA 36 3755 CZX INSERT 0A000900C8060000 insert into "CZX". "T" ("ID", "NAME") values ('7 minutes. Delete from "CZX". "T" where "ID" ='7' and "NAME" ='G' and ROWID = 'AAAVSdAAEAA 36 3755 CZX COMMIT 0A000900C8060000 commit; 7 rows selected

If you know vue.js and django, you can make the above function in the form of web interface.

10. Remember to turn off the log mining function when you finally run out of it

SQL > begindbms_logmnr.end_logmnr;end;/PL/SQL procedure successfully completed. Thank you for your reading, the above is the content of "how to use oracle log mining to achieve rollback". After the study of this article, I believe you have a deeper understanding of how to use oracle log mining to achieve rollback, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.