2017年5月6日土曜日

Testing Log Miner(English)

Oracle LogMiner can check substance of Oracle database redo log file using SQL.
Checking Oracle database redo log file is good for seeing history of transactions.

If you want to use LogMiner, you have to additional row in redo log to record in advance.
Recording in additional row called Supplimental Logging.

In default, supplimental logging is not valid. so I need supplimental logging valid to use LogMiner.

For now, I checked configureation of supplimental logging.

===
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME
--------
NO
===

NO means supplimental logging is invalid in this database.
So, I enable supplimental logging.
===
SQL> alter database add supplemental log data;

Database altered.

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME
--------
YES
===
Supplimental logging is now valid.

Next, let's get history of transactions.

1. Specify directory of LogMiner
I can specify directory. But this time, I specified when starting LogMiner, so I didn't anything.

2. Specify redo log file
I can specify redo log file that I want to get LogMiner data.
I specified online redo log file which was used right now.
===
SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/balloon/redo03.log
/u01/app/oracle/oradata/balloon/redo02.log
/u01/app/oracle/oradata/balloon/redo01.log

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE  MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
1    1       1   52428800   512 1 YES
INACTIVE       1127233 13-NOV-16      1147535 09-APR-17

2    1       2   52428800   512 1 NO
CURRENT       1147535 09-APR-17   2.8147E+14

3    1       0   52428800   512 1 YES
UNUSED     0    0

===
The redo02.log is used right now. So, I specified redo02.log's data.

===
SQL> execute dbms_logmnr.add_logfile(logfilename => '/u01/app/oracle/oradata/balloon/redo02.log', options=> dbms_logmnr.new);

PL/SQL procedure successfully completed.
===
It's finished to specify.

3. Starting LogMiner
I started LogMiner. This time, I used online catalog for specifing directory.
We can use online catalog to analyzing online redo log file and arhived redo log file.
===
SQL> execute dbms_logmnr.start_logmnr( options => dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.
===

*I executed some sql to recording redo log file.
I added new row and commit and I deleted row. After that, I checked difference transaction rollback or commit.
===
SQL> select * from qb.keiyaku;

NAME     KEIYAKUBI
-------------------- ---------
madoka     03-OCT-10
homura     01-AUG-09
mami     01-AUG-10
sayaka     15-OCT-10
kyoko     01-MAY-10

SQL>  insert into qb.keiyaku values('oriko',to_date('2011/03/20','yyyy/mm/dd'));

1 row created.

SQL> commit;

Commit complete.

SQL> select * from qb.keiyaku;

NAME     KEIYAKUBI
-------------------- ---------
madoka     03-OCT-10
homura     01-AUG-09
mami     01-AUG-10
sayaka     15-OCT-10
kyoko     01-MAY-10
oriko     20-MAR-11 *

6 rows selected.

SQL> delete from qb.keiyaku where name = 'oriko';

1 row deleted.

SQL> rollback;

Rollback complete.

SQL> select * from qb.keiyaku;

NAME     KEIYAKUBI
-------------------- ---------
madoka     03-OCT-10
homura     01-AUG-09
mami     01-AUG-10
sayaka     15-OCT-10
kyoko     01-MAY-10
oriko     20-MAR-11 *

6 rows selected.

SQL>
SQL> delete from qb.keiyaku where name = 'oriko';

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from qb.keiyaku;

NAME     KEIYAKUBI
-------------------- ---------
madoka     03-OCT-10
homura     01-AUG-09
mami     01-AUG-10
sayaka     15-OCT-10
kyoko     01-MAY-10

===

4.Checking recorded redo log.
For checking recorded redo log, We can use the view v$logmnr_contents.
===
set line 6000
select SCN,TIMESTAMP,TABLE_NAME,OPERATION,SQL_REDO from v$logmnr_contents where operation!='INTERNAL';


       SCN TIMESTAMP TABLE_NAME      OPERATION       SQL_REDO
...
   1152964 15-APR-17 KEIYAKU      INSERT       insert into "QB"."KEIYAKU"("NAME","KEIYAKUBI") values ('oriko',TO_DATE('20-MAR-11', 'DD-MON-RR'));
...
   1153175 15-APR-17 KEIYAKU      DELETE       delete from "QB"."KEIYAKU" where "NAME" = 'oriko' and "KEIYAKUBI" = TO_DATE('20-MAR-11', 'DD-MON-RR') and ROWID = 'AAASAfAAEAAAAEPAAB';
   1153180 15-APR-17 KEIYAKU      INSERT       insert into "QB"."KEIYAKU"("NAME","KEIYAKUBI") values ('oriko',TO_DATE('20-MAR-11', 'DD-MON-RR'));
   1153181 15-APR-17      ROLLBACK       rollback;
...
   1153193 15-APR-17 KEIYAKU      DELETE       delete from "QB"."KEIYAKU" where "NAME" = 'oriko' and "KEIYAKUBI" = TO_DATE('20-MAR-11', 'DD-MON-RR') and ROWID = 'AAASAfAAEAAAAEPAAB';
   1153194 15-APR-17      COMMIT       commit;

===


5.Stopping LogMiner
In the end,stopping LogMiner.
===
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR;

PL/SQL procedure successfully completed.
===
Log miner can be used in the above procedure.
I think that it can be used for confirmation when it is operation mistake.

However, You may warry about load of the system.
According to manual, when you use "minimal supplemental logging" ( I tried this time), the load does not become much heavier. Of course, you should verificate before introducing.
The other hand, when you use "recognition key / logging"(it can record up to the contents of the line) the load is heavier than minimal supplemental logging.

0 件のコメント:

コメントを投稿