728x90
<Log Miner> - 11g
| [oracle@19c-:/u01/base/product/19c/rdbms/admin]$sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 17 03:01:43 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> @dbmslm.sql
Session altered.
Package created.
Grant succeeded.
Synonym created.
Session altered.
|
| SQL> select supplemental_log_data_min from v$database;
SUPPLEME
--------
NO
SQL> alter database add supplemental log data;
Database altered.
SQL> select supplemental_log_data_min from v$database;
SUPPLEME
--------
YES
|
| SQL> select * from test;
A B
---------- ----------
1 1
1 2
1 3
1 4
1 5
1 6
2 1
2 2
2 3
2 4
2 5
2 6
SQL> update test set A=100;
12 rows updated.
SQL> commit;
Commit complete.
SQL> select * from test;
A B
---------- ----------
100 1
100 2
100 3
100 4
100 5
100 6
100 1
100 2
100 3
100 4
100 5
100 6
12 rows selected.
|
| [root@11g ~]# mkdir /logmnr
[root@11g ~]# chown -R oracle:dba /logmnr/
SQL> conn / as sysdba
Connected.
SQL> alter system set utl_file_dir='/logmnr' scope=spfile;
System altered.
SQL> show parameter utl_file_dir
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
utl_file_dir string
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 2388422656 bytes
Fixed Size 2255552 bytes
Variable Size 603981120 bytes
Database Buffers 1761607680 bytes
Redo Buffers 20578304 bytes
Database mounted.
Database opened.
SQL> show parameter utl_file_dir
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
utl_file_dir string /logmnr
|
SQL> select a.sequence#, a.group#, a.status, a.members, b.status, b.type, b.member from v$log a , v$logfile b where a.group#=b.group# order by a.group# asc, a.sequence# asc
SEQUENCE# GROUP# STATUS MEMBERS STATUS TYPE MEMBER
---------- ---------- --------------- ---------- --------------- --------------------- ------------------------------
85 1 INACTIVE 1 ONLINE /oradata/ver11g/redo01.log
86 2 INACTIVE 1 ONLINE /oradata/ver11g/redo02.log
87 3 CURRENT 1 ONLINE /oradata/ver11g/redo03.log
SQL> exec dbms_logmnr_d.build ('dict','/logmnr');
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.add_logfile('/oradata/ver11g/redo01.log',1);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.add_logfile('/oradata/ver11g/redo02.log',3);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.add_logfile('/oradata/ver11g/redo03.log',3)
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/logmnr/dict');
PL/SQL procedure successfully completed.
SQL> select to_char(timestamp,'YYYY-MM-DD:HH24:MI:SS'), seg_owner, username, sql_redo, sql_undo from v$logmnr_contents where sql_redo like '%update%' and seg_owner='TEST'
TO_CHAR(TIMESTAMP,'YYYY-MM-DD:HH24:MI:SS SEG_O USERN SQL_REDO SQL_UNDO
---------------------------------------- ----- ----- ---------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------------------------------------
2020-11-18:21:55:49 TEST TEST update "TEST"."TEST" set "A" = '100' where "A" = '1' and ROWID = 'AAAUUPAAHAAAACHAAA'; update "TEST"."TEST" set "A" = '1' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAA';
2020-11-18:21:55:49 TEST TEST update "TEST"."TEST" set "A" = '100' where "A" = '1' and ROWID = 'AAAUUPAAHAAAACHAAB'; update "TEST"."TEST" set "A" = '1' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAB';
2020-11-18:21:55:49 TEST TEST update "TEST"."TEST" set "A" = '100' where "A" = '1' and ROWID = 'AAAUUPAAHAAAACHAAC'; update "TEST"."TEST" set "A" = '1' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAC';
2020-11-18:21:55:49 TEST TEST update "TEST"."TEST" set "A" = '100' where "A" = '1' and ROWID = 'AAAUUPAAHAAAACHAAD'; update "TEST"."TEST" set "A" = '1' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAD';
2020-11-18:21:55:49 TEST TEST update "TEST"."TEST" set "A" = '100' where "A" = '1' and ROWID = 'AAAUUPAAHAAAACHAAE'; update "TEST"."TEST" set "A" = '1' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAE';
2020-11-18:21:55:49 TEST TEST update "TEST"."TEST" set "A" = '100' where "A" = '1' and ROWID = 'AAAUUPAAHAAAACHAAF'; update "TEST"."TEST" set "A" = '1' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAF';
2020-11-18:21:55:49 TEST TEST update "TEST"."TEST" set "A" = '100' where "A" = '2' and ROWID = 'AAAUUPAAHAAAACHAAG'; update "TEST"."TEST" set "A" = '2' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAG';
2020-11-18:21:55:49 TEST TEST update "TEST"."TEST" set "A" = '100' where "A" = '2' and ROWID = 'AAAUUPAAHAAAACHAAH'; update "TEST"."TEST" set "A" = '2' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAH';
2020-11-18:21:55:49 TEST TEST update "TEST"."TEST" set "A" = '100' where "A" = '2' and ROWID = 'AAAUUPAAHAAAACHAAI'; update "TEST"."TEST" set "A" = '2' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAI';
2020-11-18:21:55:49 TEST TEST update "TEST"."TEST" set "A" = '100' where "A" = '2' and ROWID = 'AAAUUPAAHAAAACHAAJ'; update "TEST"."TEST" set "A" = '2' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAJ';
2020-11-18:21:55:49 TEST TEST update "TEST"."TEST" set "A" = '100' where "A" = '2' and ROWID = 'AAAUUPAAHAAAACHAAK'; update "TEST"."TEST" set "A" = '2' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAK';
TO_CHAR(TIMESTAMP,'YYYY-MM-DD:HH24:MI:SS SEG_O USERN SQL_REDO SQL_UNDO
---------------------------------------- ----- ----- ---------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------------------------------------
2020-11-18:21:55:49 TEST TEST update "TEST"."TEST" set "A" = '100' where "A" = '2' and ROWID = 'AAAUUPAAHAAAACHAAL'; update "TEST"."TEST" set "A" = '2' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAL';
12 rows selected.
SQL> update "TEST"."TEST" set "A" = '1' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAA';
update "TEST"."TEST" set "A" = '1' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAB';
update "TEST"."TEST" set "A" = '1' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAC';
update "TEST"."TEST" set "A" = '1' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAD';
update "TEST"."TEST" set "A" = '1' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAE';
update "TEST"."TEST" set "A" = '1' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAF';
update "TEST"."TEST" set "A" = '2' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAG';
update "TEST"."TEST" set "A" = '2' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAH';
update "TEST"."TEST" set "A" = '2' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAI';
update "TEST"."TEST" set "A" = '2' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAJ';
update "TEST"."TEST" set "A" = '2' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAK';
update "TEST"."TEST" set "A" = '2' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAL';
SQL> conn test/test
Connected.
SQL> select * from test;
A B
---------- ----------
1 1
1 2
1 3
1 4
1 5
1 6
2 1
2 2
2 3
2 4
2 5
A B
---------- ----------
2 6
12 rows selected.
SQL> commit;
Commit complete.
SQL> exec dbms_logmnr.end_logmnr();
728x90
'ORACLE' 카테고리의 다른 글
| [ORACLE] Window HA install (0) | 2022.06.27 |
|---|---|
| [ORACLE] Window RAC install (0) | 2022.06.27 |
| [ORACLE] local_listener (0) | 2022.06.27 |
| [ORACLE] Windows Client install (0) | 2022.06.27 |
| [ORACLE] Listener.ora Tnsnames.ora Sqlnet.ora (0) | 2022.06.27 |