Příklad je zahrnutý v ebooku. Zaregistrujte se k odběru.
Potřebujete pomoci s administrací databáze nebo se jen poradit? Kontaktujte mě přímo.
Příklad nechtěné DML operace.
SQL> insert into zam values (3,'pepik',null); 1 row created. SQL> insert into zam values (4,'Katka',null); 1 row created. SQL> commit;
Dohledání konkrétního archívního souboru.
SQL> select GROUP#, SEQUENCE#, ARCHIVED, FIRST_CHANGE# from V$LOG;
GROUP# SEQUENCE# ARC FIRST_CHANGE# ---------- ---------- --- ------------- 1 45 YES 2231745 2 46 YES 2239126 3 48 NO 2266580 4 47 YES 2265070
SQL> select timestamp_to_scn(to_timestamp('14/03/2014 11:10:00','DD/MM/YYYY HH24:MI:SS')) as scn from dual;
SCN ---------- 2244703 SQL> select scn_to_timestamp('2265070') from dual;
SCN_TO_TIMESTAMP('2265070') --------------------------------------------------------------------------- 14-MAR-14 11.21.57.000000000 AM
SQL> select NAME, SEQUENCE#, FIRST_TIME, FIRST_CHANGE# from V$ARCHIVED_LOG where FIRST_CHANGE# < 2244703;
NAME -------------------------------------------------------------------------------- SEQUENCE# FIRST_TIM FIRST_CHANGE# ---------- --------- ------------- /u02/fra/ORCL12C/archivelog/2014_03_19/o1_mf_1_46_9lm3rxd6_.arc 46 14-MAR-14 2239126
Vytvoření konfiguračního souboru v adresáři SQL>
show parameter utl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string /home/oracle
SQL> BEGIN
DBMS_LOGMNR_D.build (
dictionary_filename => 'Logminer_dict.ora',
dictionary_location => '/home/oracle');
END;
/
PL/SQL procedure successfully completed.
[oel64:oracle:orcl12c]$ pwd
/home/oracle
[oel64:oracle:orcl12c]$ ls -ltr
-rw-r--r--. 1 oracle oinstall 44611669 Mar 19 14:18 Logminer_dict.ora
Přídání archívních redo log souborů
SQL> BEGIN
2 DBMS_LOGMNR.add_logfile (
3 options => DBMS_LOGMNR.new,
4 logfilename => '/u02/fra/ORCL12C/archivelog/2014_03_19/o1_mf_1_46_9lm3rxd6_.arc');
5
6 DBMS_LOGMNR.add_logfile (
7 options => DBMS_LOGMNR.addfile,
8 logfilename => '/u02/fra/ORCL12C/archivelog/2014_03_19/o1_mf_1_47_9lm46ksp_.arc');
9 END;
10 /
PL/SQL procedure successfully completed.
Zpracování souborů
SQL> BEGIN
DBMS_LOGMNR.start_logmnr (
dictfilename => '/home/oracle/Logminer_dict.ora');
END;
/
PL/SQL procedure successfully completed.
Dohledání konkrétního DML příkazu
SQL> SELECT timestamp,USERNAME,OPERATION ,STATUS, sql_redo
FROM v$logmnr_contents
WHERE SEG_NAME = 'ZAM'; 2 3
TIMESTAMP USERNAME OPERATION
--------- ------------------------------ --------------------------------
STATUS
----------
SQL_REDO
--------------------------------------------------------------------------------
19-MAR-14 UNKNOWN INSERT
3
insert into "TOM"."ZAM"("ID","JMENO","HIST_DATE") values ('3','pepik',NULL);
19-MAR-14 UNKNOWN INSERT
3
insert into "TOM"."ZAM"("ID","JMENO","HIST_DATE") values ('4','Katka',NULL);
Ukončení Logmineru
SQL> BEGIN
DBMS_LOGMNR.end_logmnr;
END;
/
PL/SQL procedure successfully completed.