 | Example 1: Find all modifications in the last archived redo log
Steps: (the various steps are part of a script file logmnr_sql.sh)
|
SQL> alter database add supplemental log data; SQL> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';(1) Get user credentials (UserA, UserB, System)
#!/bin/bash
# logmnr_sql.sh
LOGFILE=logminer.log
###############################################################
# Get users credentials
##############################################################
usersystem=system
echo "Enter password for system"
read -s passwds
usera=usera
echo "Enter password for usera"
read -s passwda
userb=userb
echo "Enter password for userb"
read -s passwdb
sid=${ORACLE_SID}
(cont..)
(2) UserA creates table UserA.EMP
(cont..)
###############################################################
# Log in as userA. Recreate EMP table
##############################################################
sqlplus -s /nolog << EndOfSQL >> ${LOGFILE}
connect ${usera}/${passwda}
whenever sqlerror exit sql.sqlcode;
prompt "Connected: UserA - recreating EMP.."
drop table emp;
create table emp as
select employee_id as empno,
first_name || ' ' || last_name as ename,
department_id as deptno,
salary as salary
from hr.employees;
grant select, update on emp to userb;
exit;
EndOfSQL
ERRORCODE=$?
#check the return code from SQL Plus
if [ $ERRORCODE != 0 ]
then
echo ********************************
echo "ERROR: SQL*Plus failed with error code: $ERRORCODE"
else
echo ********************************
echo "SQL*Plus completed successfully. Error code: $ERRORCODE"
fi
(cont..)
(3) UserA updates some rows on table UserA.EMP
(cont..)
###############################################################
# Log in as usera. update data on table EMP
##############################################################
sqlplus -s /nolog << EndOfSQL >> ${LOGFILE}
connect ${usera}/${passwda}
whenever sqlerror exit sql.sqlcode;
prompt "Connected: UserA - Updating rows..."
set serveroutput on
begin
update emp set salary = salary*1.05 where deptno = 50;
commit;
dbms_output.put_line('salaries on dept 50 updated..');
declare
cursor c_emp is
select empno, ename, deptno, salary from emp
where deptno = 30
for update;
begin
for c_reg in c_emp loop
update emp
set ename = initcap(c_reg.ename)
where current of c_emp;
update emp
set salary = c_reg.salary *1.10
where current of c_emp;
end loop;
commit;
dbms_output.put_line('names and salaries on dept 30 updated..');
end;
end;
/
exit;
EndOfSQL
ERRORCODE=$?
# Check the return code from SQL Plus
if [ $ERRORCODE != 0 ]
then
echo ********************************
echo "ERROR: SQL*Plus failed with error code: $ERRORCODE"
else
echo ********************************
echo "SQL*Plus completed successfully. Error code: $ERRORCODE"
fi
(cont..)
(4) UserB updates rows on table UserA.EMP
###############################################################
# Log in as userB. update data on table EMP
##############################################################
sqlplus -s /nolog << EndOfSQL >> ${LOGFILE}
connect ${userb}/${passwdb}
whenever sqlerror exit sql.sqlcode;
prompt "Connected: USERB"
set serveroutput on
begin
update usera.emp set salary = salary*1.05 where deptno = 60;
dbms_output.put_line('SQL%ROWCOUNT: ['|| SQL%ROWCOUNT || '] rows returned');
commit;
dbms_output.put_line('salaries on dept 60 updated..');
declare
cursor c_emp2 is
select empno, ename, deptno, salary from usera.emp
where deptno = 90
for update;
begin
for c_reg in c_emp2 loop
update usera.emp
set ename = initcap(c_reg.ename)
where current of c_emp2;
update usera.emp
set salary = c_reg.salary *1.10
where current of c_emp2;
end loop;
commit;
dbms_output.put_line('names and salaries on dept 30 updated..');
end;
end;
/
exit;
EndOfSQL
ERRORCODE=$?
# Check the return code from SQL Plus
if [ $ERRORCODE != 0 ]
then
echo ********************************
echo "ERROR: SQL*Plus failed with error code: $ERRORCODE"
else
echo ********************************
echo "SQL*Plus completed successfully. Error code: $ERRORCODE"
fi
(cont..)
(5) Log in as SYSTEM:
(a) archives current redo log;
(b) Add list of log files for mining;
(c) starts log miner;
(d) query log miner information; and
(e) Ends log miner section
###############################################################
# Log in as SYSTEM. Run Log Miner
##############################################################
echo "Configuring Log Miner section..."
sqlplus -s /nolog << EndOfSQL >> ${LOGFILE}
connect ${usersystem}/${passwds}
whenever sqlerror exit sql.sqlcode;
prompt "Connected: SYSTEM"
prompt " (a) Switching current log file.."
alter system archive log current;
prompt "Most recently archived log file is"
set linesize 150
select name, first_change# from v\$archived_log
where first_time = (select max(first_time)
from v\$archived_log);
prompt "(b) Configuring the list of redo log files to be mined.."
declare
v_logname varchar2(200);
begin
select name into v_logname
from v\$archived_log
where first_time = (select max(first_time)
from v\$archived_log);
dbms_logmnr.add_logfile(LOGFILENAME => v_logname, OPTIONS => dbms_logmnr.new);
end;
/
prompt "List of log files added to log miner: "
select to_char(low_time, 'dd-mon-yy hh24:mi:ss') lowtime,
to_char(high_time, 'dd-mon-yy hh24:mi:ss') hightime,
type, filesize/1024/1024 Mb, filename
from v\$logmnr_logs;
prompt "(c) Starting log miner using online catalog.."
execute dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog);
prompt "(d) Querying log miner results"
set linesize 150
set pagesize 80
column usr format a10
column osusr format a10
column machine_name format a10
column xid format a12
col operation format a15
col table_name format a10
col sql_redo format a30
col sql_undo format a30
select username as usr,
os_username as osusr,
(xidusn ||'.'|| xidslt ||'.'||xidsqn) as xid,
operation,
table_name,
sql_redo,
sql_undo
from v\$logmnr_contents
where username in ('USERA', 'USERB');
USR OSUSR XID OPERATION TABLE_NAME SQL_REDO SQL_UNDO
------ -------- ---------- --------------- ---------- ------------------------------ ------------------------------
USERA jdoe 10.12.988 START set transaction read write;
USERA jdoe 1.7.928 DDL EMP ALTER TABLE "USERA"."EMP" RENA
ME TO "BIN$sKOUXNE/r7TgQAB/AQE
yrA==$0" ;
...
USERA jdoe 1.7.928 DDL EMP drop table emp AS "BIN$sKOUXNE
/r7TgQAB/AQEyrA==$0" ;
...
USERA jdoe 4.32.983 DDL EMP create table emp as
select employee_id as empno,
first_name || ' ' ||
last_name as ename,
department_id as deptno,
salary as salary
from hr.employees;
..
USERA jdoe 4.23.984 START set transaction read write;
USERA jdoe 4.32.983 DIRECT INSERT EMP insert into "USERA"."EMP"("EMP delete from "USERA"."EMP" wher
NO","ENAME","DEPTNO","SALARY") e "EMPNO" = '198' and "ENAME"
values ('198','Donald OConnel = 'Donald OConnell' and "DEPTN
l','50','2600'); O" = '50' and "SALARY" = '2600
' and ROWID = 'AAASdlAAEAAAAMb
AAA';
...
USERA jdoe 4.32.983 DIRECT INSERT EMP insert into "USERA"."EMP"("EMP delete from "USERA"."EMP" wher
NO","ENAME","DEPTNO","SALARY") e "EMPNO" = '197' and "ENAME"
values ('197','Kevin Feeney', = 'Kevin Feeney' and "DEPTNO"
'50','3000'); = '50' and "SALARY" = '3000' a
nd ROWID = 'AAASdlAAEAAAAMbABq
';
...
USERA jdoe 10.31.988 DDL EMP grant select, update on emp to
userb;
...
USERA jdoe 10.31.988 UPDATE OBJ$ update "SYS"."OBJ$" set "OBJ#" update "SYS"."OBJ$" set "OBJ#"
= '75621', "DATAOBJ#" = '7562 = '75621', "DATAOBJ#" = '7562
1', "TYPE#" = '2', "CTIME" = T 1', "TYPE#" = '2', "CTIME" = T
O_DATE('22-NOV-10', 'DD-MON-RR O_DATE('22-NOV-10', 'DD-MON-RR
'), "MTIME" = TO_DATE('22-NOV- '), "MTIME" = TO_DATE('22-NOV-
10', 'DD-MON-RR'), "STIME" = T 10', 'DD-MON-RR'), "STIME" = T
O_DATE('22-NOV-10', 'DD-MON-RR O_DATE('22-NOV-10', 'DD-MON-RR
'), "STATUS" = '1', "FLAGS" = '), "STATUS" = '1', "FLAGS" =
'0', "OID$" = NULL, "SPARE1" = '0', "OID$" = NULL, "SPARE1" =
'6', "SPARE2" = '1' where "OB '6', "SPARE2" = '1' where "OB
J#" = '75621' and "DATAOBJ#" = J#" = '75621' and "DATAOBJ#" =
'75621' and "OWNER#" = '92' a '75621' and "OWNER#" = '92' a
nd "TYPE#" = '2' and "CTIME" = nd "TYPE#" = '2' and "CTIME" =
TO_DATE('22-NOV-10', 'DD-MON- TO_DATE('22-NOV-10', 'DD-MON-
RR') and "MTIME" = TO_DATE('22 RR') and "MTIME" = TO_DATE('22
-NOV-10', 'DD-MON-RR') and "ST -NOV-10', 'DD-MON-RR') and "ST
IME" = TO_DATE('22-NOV-10', 'D IME" = TO_DATE('22-NOV-10', 'D
D-MON-RR') and "STATUS" = '1' D-MON-RR') and "STATUS" = '1'
and "FLAGS" = '0' and "OID$" I and "FLAGS" = '0' and "OID$" I
S NULL and "SPARE1" = '6' and S NULL and "SPARE1" = '6' and
"SPARE2" = '1' and ROWID = 'AA "SPARE2" = '1' and ROWID = 'AA
AAASAABAAAU6JAAi'; AAASAABAAAU6JAAi';
..
USERA jdoe 10.31.988 COMMIT commit;
USERA jdoe 7.26.985 START set transaction read write;
...
USERA jdoe 3.23.1299 START set transaction read write;
USERA jdoe 3.23.1299 UPDATE EMP update "USERA"."EMP" set "SALA update "USERA"."EMP" set "SALA
RY" = '2730' where "EMPNO" = ' RY" = '2600' where "EMPNO" = '
198' and "ENAME" = 'Donald OCo 198' and "ENAME" = 'Donald OCo
nnell' and "DEPTNO" = '50' and nnell' and "DEPTNO" = '50' and
"SALARY" = '2600' and ROWID = "SALARY" = '2730' and ROWID =
'AAASdlAAEAAAAMbAAA'; 'AAASdlAAEAAAAMbAAA';
...
USERA jdoe 3.23.1299 UPDATE EMP update "USERA"."EMP" set "SALA update "USERA"."EMP" set "SALA
RY" = '2250' where "EMPNO" = ' RY" = '3000' where "EMPNO" = '
197' and "ENAME" = 'Kevin Feen 197' and "ENAME" = 'Kevin Feen
ey' and "DEPTNO" = '50' and "S ey' and "DEPTNO" = '50' and "S
ALARY" = '3000' and ROWID = 'A ALARY" = '2250' and ROWID = 'A
AASdlAAEAAAAMbABq'; AASdlAAEAAAAMbABq';
USERA jdoe 3.23.1299 COMMIT commit;
USERA jdoe 1.12.927 START set transaction read write;
USERA jdoe 1.12.92 SELECT_FOR_UPDATE EMP select * from "USERA"."EMP" wh
ere ROWID = 'AAASdlAAEAAAAMbAA
X' for update;
...
USERA jdoe 1.12.927 UPDATE EMP update "USERA"."EMP" set "ENAM update "USERA"."EMP" set "ENAM
E" = 'Den Raphaely' where "EMP E" = 'Den Raphaely' where "EMP
NO" = '114' and "ENAME" = 'Den NO" = '114' and "ENAME" = 'Den
Raphaely' and "DEPTNO" = '30' Raphaely' and "DEPTNO" = '30'
and "SALARY" = '11000' and RO and "SALARY" = '11000' and RO
WID = 'AAASdlAAEAAAAMbAAX'; WID = 'AAASdlAAEAAAAMbAAX';
...
USERA jdoe 1.12.927 UPDATE EMP update "USERA"."EMP" set "SALA update "USERA"."EMP" set "SALA
RY" = '2750' where "EMPNO" = ' RY" = '2500' where "EMPNO" = '
119' and "ENAME" = 'Karen Colm 119' and "ENAME" = 'Karen Colm
enares' and "DEPTNO" = '30' an enares' and "DEPTNO" = '30' an
d "SALARY" = '2500' and ROWID d "SALARY" = '2750' and ROWID
= 'AAASdlAAEAAAAMbAAc'; = 'AAASdlAAEAAAAMbAAc';
USERB jdoe 1.12.927 COMMIT commit;
...
USERB jdoe 8.21.1521 START set transaction read write;
USERB jdoe 6.16.1306 UPDATE EMP update "USERA"."EMP" set "SALA update "USERA"."EMP" set "SALA
RY" = '9450' where "EMPNO" = ' RY" = '9000' where "EMPNO" = '
103' and "ENAME" = 'Alexander 103' and "ENAME" = 'Alexander
Hunold' and "DEPTNO" = '60' an Hunold' and "DEPTNO" = '60' an
d "SALARY" = '9000' and ROWID d "SALARY" = '9450' and ROWID
= 'AAASdlAAEAAAAMbAAM'; = 'AAASdlAAEAAAAMbAAM';
...
USERB jdoe 6.16.1306 COMMIT commit;
USERB jdoe 4.5.983 START set transaction read write;
USERB jdoe 4.5.983 SELECT_FOR_UPDA EMP select * from "USERA"."EMP" wh
TE ere ROWID = 'AAASdlAAEAAAAMbAA
...
USERB jdoe 4.5.983 UPDATE EMP update "USERA"."EMP" set "ENAM update "USERA"."EMP" set "ENAM
E" = 'Steven King' where "EMPN E" = 'Steven King' where "EMPN
O" = '100' and "ENAME" = 'Stev O" = '100' and "ENAME" = 'Stev
en King' and "DEPTNO" = '90' a en King' and "DEPTNO" = '90' a
nd "SALARY" = '24000' and ROWI nd "SALARY" = '24000' and ROWI
D = 'AAASdlAAEAAAAMbAAJ'; D = 'AAASdlAAEAAAAMbAAJ';
...
USERB jdoe 4.5.983 COMMIT commit;
...
188 rows selected.
(e) End the Log Miner Section
prompt "(e) Ending log miner section.." execute dbms_logmnr.end_logmnr(); exit; EndOfSQL ERRORCODE=$? # Check the return code from SQL Plus if [ $ERRORCODE != 0 ] then echo ******************************** echo "ERROR: SQL*Plus failed with error code: $ERRORCODE" else echo ******************************** echo "SQL*Plus completed successfully. Error code: $ERRORCODE" fi
