Home » RDBMS Server » Server Utilities » LogMiner - no records (Windows XP SP2 Oracle 10g)
| LogMiner - no records [message #342731] |
Sun, 24 August 2008 14:05  |
Smash
Messages: 18
Registered: August 2008
|
Junior Member |
|
|
This is my first post so hello %)
I have small problem with LogMiner.
Oracle is running in ARCHIVELOG mode
LOG_MODE
------------
ARCHIVELOG
I've created a dictionary file
begin EXECUTE sys.dbms_logmnr_d.build(Dictionary_FileName=>'Dictionary.ora',Dictionary_Location=>'C:\LogMinerOutput');
end;
select name,value from v$parameter where name = 'utl_file_dir';
NAME VALUE
-----------------------------------------------------------------
utl_file_dir C:\LogMinerOutput
Add Redo log file:
begin sys.dbms_logmnr.add_logfile( LogFileName=>'E:\ORACLE\ORADATA\DB1\REDO01.LOG',Options=>sys.dbms_logmnr.NEW);
end;
and start logminer:
begin sys.dbms_logmnr.start_logmnr(DictFileName=>'C:\LogMinerOutput\Dictionary.ora');
end;
I create test table , make some inserts, delete , updates (in my schema and in scott as well).
When I try to look for logs
SELECT username, sql_redo, sql_undo
FROM v$logmnr_contents WHERE UPPER(username) = 'SYSTEM';
It gives:
Any ideas ?
[Updated on: Sun, 24 August 2008 14:36] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: LogMiner - no records [message #342737 is a reply to message #342731] |
Sun, 24 August 2008 15:36  |
Smash
Messages: 18
Registered: August 2008
|
Junior Member |
|
|
Sorry for chaos in previous post, but I've spend about 5h working on it and effects are poor so far.
I build dictionary on system user. Changes where made in system schema and scott (all the time from system account)
I followed Michel Cadot advice and create dictionary file after I make table and execute insert,delete statements.
SELECT sql_redo
FROM v$logmnr_contents
WHERE UPPER(username) = 'SYSTEM';
give some results but as you can see it's hard to get some information from this output:
set transaction read write;
update "SYS"."TSQ$" set "TS#" = '0', "GR
ANTOR#" = '1968', "BLOCKS" = '0', "MAXBL
OCKS" = '0', "PRIV1" = '0', "PRIV2" = '0
' where "TS#" = '0' and "GRANTOR#" = '19
76' and "BLOCKS" = '0' and "MAXBLOCKS" =
'0' and "PRIV1" = '0' and "PRIV2" = '0'
and ROWID = 'AAAAAKAABAAAABaAAB';
is there any option to make it more "human-readable"
|
|
|
|
|
|
|
|
| Re: LogMiner - no records [message #343246 is a reply to message #342731] |
Tue, 26 August 2008 14:37 |
Smash
Messages: 18
Registered: August 2008
|
Junior Member |
|
|
I found information that without a dictionary, LogMiner returns internal object IDs and presents data as binary data.
But in my case I've included dictionary file.
So any other ideas about this output ?
|
|
|
|
|
|
| Re: LogMiner - no records [message #343252 is a reply to message #342731] |
Tue, 26 August 2008 15:02 |
Smash
Messages: 18
Registered: August 2008
|
Junior Member |
|
|
ok so why in every tutorial the output is like that :
USR XID SQL_REDO SQL_UNDO
---- --------- ----------------------------------------------------
HR 1.11.1476 set transaction read write;
HR 1.11.1476 insert into "HR"."EMPLOYEES"( delete from "HR"."EMPLOYEES"
"EMPLOYEE_ID","FIRST_NAME", where "EMPLOYEE_ID" = '306'
"LAST_NAME","EMAIL", and "FIRST_NAME" = 'Mohammed'
"PHONE_NUMBER","HIRE_DATE", and "LAST_NAME" = 'Sami'
"JOB_ID","SALARY", and "EMAIL" = 'MDSAMI'
"COMMISSION_PCT","MANAGER_ID", and "PHONE_NUMBER" = '1234567890'
"DEPARTMENT_ID") values and "HIRE_DATE" = TO_DATE('10-JAN-2003
('306','Mohammed','Sami', 13:34:43', 'dd-mon-yyyy hh24:mi:ss')
'MDSAMI', '1234567890', and "JOB_ID" = 'HR_REP' and
TO_DATE('10-jan-2003 13:34:43', "SALARY" = '120000' and
'dd-mon-yyyy hh24:mi:ss'), "COMMISSION_PCT" = '.05' and
'HR_REP','120000', '.05', "DEPARTMENT_ID" = '10' and
'105','10'); ROWID = 'AAAHSkAABAAAY6rAAO';
What should i do to get similar ?
|
|
|
|
|
|
|
|
|
|
| Re: LogMiner - no records [message #343395 is a reply to message #342731] |
Wed, 27 August 2008 03:20 |
Smash
Messages: 18
Registered: August 2008
|
Junior Member |
|
|
Ok lets analyze this one :
update "SYS"."TSQ$" set "TS#" = '0', "GR
ANTOR#" = '1968', "BLOCKS" = '0', "MAXBL
OCKS" = '0', "PRIV1" = '0', "PRIV2" = '0
' where "TS#" = '0' and "GRANTOR#" = '19
76' and "BLOCKS" = '0' and "MAXBLOCKS" =
'0' and "PRIV1" = '0' and "PRIV2" = '0'
and ROWID = 'AAAAAKAABAAAABaAAB';
The only thing I can see is update instruction.
update SYS.TSQ$ set TS#='0 and so on
I don't know on which table I performed operation and what records where updated.
|
|
|
|
|
|
|
|
| Re: LogMiner - no records [message #345689 is a reply to message #342731] |
Thu, 04 September 2008 08:27 |
Smash
Messages: 18
Registered: August 2008
|
Junior Member |
|
|
| Quote: | Concentrate on user statements.
Make some modification with SCOTT for example and query Log Miner view where username='SCOTT' or the like.
|
Ok, so I've created using system account a user called 'smash'.
Give him the proper right to execute dbms_logmnr package, select on v$ views etc.
Then I log on as smash and do this:
create table test (x int);
insert into test values(1);
insert into test values(2);
insert into test values(3);
insert into test values(4);
insert into test values(5);
update test
set x=10 where x=1;
delete from test
where x=2;
begin
sys.dbms_logmnr_d.build(Dictionary_FileName=>'Dictionary.ora',Dictionary_Location=>'C:\LogMinerOutput');
end;
begin
sys.dbms_logmnr.add_logfile( LogFileName=>'E:\ORACLE\ORADATA\DB1\REDO01.LOG',Options=>sys.dbms_logmnr.NEW);
end;
begin
sys.dbms_logmnr.add_logfile( LogFileName=>'E:\ORACLE\ORADATA\DB1\REDO02.LOG',Options=>sys.dbms_logmnr.ADDFILE);
end;
begin
sys.dbms_logmnr.add_logfile( LogFileName=>'E:\ORACLE\ORADATA\DB1\REDO03.LOG',Options=>sys.dbms_logmnr.ADDFILE);
end;
begin
sys.dbms_logmnr.start_logmnr(DictFileName=>'C:\LogMinerOutput\Dictionary.ora');
end;
When I execute query
select timestamp,sql_undo,sql_redo from v$logmnr_contents where username='SMASH'
and trunc(timestamp) = to_char(sysdate,'DD-MON-YY')
I get output like this:
04-SEP-08 insert into "SYS"."OBJ$"("OBJ#","DATAOBJ
#","OWNER#","NAME","NAMESPACE","SUBNAME"
,"TYPE#","CTIME","MTIME","STIME","STATUS
","REMOTEOWNER","LINKNAME","FLAGS","OID$
","SPARE1","SPARE2","SPARE3","SPARE4","S
PARE5","SPARE6") values ('53128','53128'
,'64','BIN$P44wVP2qQVKG8rBw/ZWprQ==$0','
1',NULL,'2',TO_DATE('03-SEP-08', 'DD-MON
-RR'),TO_DATE('04-SEP-08', 'DD-MON-RR'),
TO_DATE('04-SEP-08', 'DD-MON-RR'),'1',NU
LL,NULL,'128',NULL,'6','2',NULL,NULL,NUL
L,NULL);
Are those still a SYS statements ?
If yes how to get USER statements ?
|
|
|
|
|
|
|
|
|
|
| Re: LogMiner - no records [message #345757 is a reply to message #342731] |
Thu, 04 September 2008 13:37 |
Smash
Messages: 18
Registered: August 2008
|
Junior Member |
|
|
Everything ?
insert into "SMASH"."BIN$l3j+nTkqSPqVcb5
j8GM+IA==$0"("X") values ('2');
and so on....
And in most tutorials they get something like this :
INSERT INTO
JHUNTER.DEPT(DEPTNO,DNAME,LOC)
VALUES (50,'IT','PITTSBURGH');
[Updated on: Thu, 04 September 2008 13:38] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: LogMiner - no records [message #345791 is a reply to message #342731] |
Thu, 04 September 2008 16:31 |
Smash
Messages: 18
Registered: August 2008
|
Junior Member |
|
|
Ok now output is more friendly but...
Could you tell me how to check current log file.
gives:
GROUP# STATUS TYPE MEMBER
3 ONLINE E:\ORACLE\ORADATA\DB1\REDO03.LOG
2 ONLINE E:\ORACLE\ORADATA\DB1\REDO02.LOG
1 ONLINE E:\ORACLE\ORADATA\DB1\REDO01.LOG
when I alter system result is the same (3 logs online)
Maybe should I use diffrent view ?
|
|
|
|
|
|
| Re: LogMiner - no records [message #345917 is a reply to message #342731] |
Fri, 05 September 2008 04:40 |
Smash
Messages: 18
Registered: August 2008
|
Junior Member |
|
|
Ok now I really don't know what is wrong
1.Create table ( log group 2 current)
2.Switch logfile ( log 3 is current now)
3.DML statements + commit
4.Switch logfile (log 1 is current)
5.Start log miner on the previous logfile
sys.dbms_logmnr_d.build(Dictionary_FileName=>'Dictionary.ora',Dictionary_Location=>'C:\LogMinerOutput');
sys.dbms_logmnr.add_logfile( LogFileName=>'E:\ORACLE\ORADATA\DB1\REDO03.LOG',Options=>sys.dbms_logmnr.NEW);
sys.dbms_logmnr.start_logmnr(DictFileName=>'C:\LogMinerOutput\Dictionary.ora');
6.Query:
select seg_owner,timestamp,sql_undo,sql_redo from v$logmnr_contents where username='SMASH'
and trunc(timestamp) = to_char(sysdate,'DD-MON-YY')
and seg_owner!='SYS';
no rows selected
|
|
|
|
|
|
|
|
| Re: LogMiner - no records [message #345943 is a reply to message #342731] |
Fri, 05 September 2008 06:34 |
Smash
Messages: 18
Registered: August 2008
|
Junior Member |
|
|
SQL> show user;
USER is "SMASH"
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE
SQL> create table test(x int);
Table created.
SQL> alter system switch logfile;
System altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 CURRENT
3 INACTIVE
SQL> insert into test values(1);
1 row created.
SQL> insert into test values(2);
1 row created.
SQL> delete from test where x=1;
1 row deleted.
SQL> update test set x=20 where x=2;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 ACTIVE
3 CURRENT
SQL> exec sys.dbms_logmnr_d.build(Dictionary_FileName=>'Dictionary.ora',Dictionary_Location=>'C:\Log
MinerOutput');
PL/SQL procedure successfully completed.
SQL> exec sys.dbms_logmnr.add_logfile( LogFileName=>'E:\ORACLE\ORADATA\DB1\REDO02.LOG',Options=>sys.
dbms_logmnr.NEW);
PL/SQL procedure successfully completed.
SQL> exec sys.dbms_logmnr.start_logmnr(DictFileName=>'C:\LogMinerOutput\Dictionary.ora');
PL/SQL procedure successfully completed.
SQL> col sql_undo format a40 wrap;
SQL> col sql_redo format a40;
SQL> select seg_owner,timestamp,sql_undo,sql_redo from v$logmnr_contents where username='SMASH'
2 and trunc(timestamp) = to_char(sysdate,'DD-MON-YY')
3 and seg_owner!='SYS';
no rows selected
SQL> select seg_owner,timestamp,sql_undo,sql_redo from v$logmnr_contents where
2 trunc(timestamp) = to_char(sysdate,'DD-MON-YY')
3 and seg_owner!='SYS' and seg_owner!='SYSMAN';
no rows selected
|
|
|
|
| Re: LogMiner - no records [message #345945 is a reply to message #345943] |
Fri, 05 September 2008 06:39 |
 |
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
As I said, your last query is wrong.
In addition, Oracle 10g introduced optimization in redo log generation and so you will not see small transactions (LogMiner is not able to analyze them). See my second statement.
SQL> show user
USER is "MICHEL"
SQL> alter session set "_in_memory_undo"=false;
Session altered.
SQL> create table test (col number);
Table created.
SQL> alter system switch logfile;
System altered.
SQL> @log
Thr Grp Mo Seq# Premier acces Arc Statut Groupe Etat Mb Membre
--- ---- ------ ------ ------------------- --- ---------------- ------- ------------------------------------
1 1 10 653 05/09/2008 13:29:07 NO ACTIVE ONLINE C:\ORACLE\ARCHIVES\MIKA\RL_G1_2.RDO
ONLINE C:\ORACLE\BASES\MIKA\RL_G1_1.RDO
2 10 654 05/09/2008 13:29:14 NO ACTIVE ONLINE C:\ORACLE\ARCHIVES\MIKA\RL_G2_2.RDO
ONLINE C:\ORACLE\BASES\MIKA\RL_G2_1.RDO
3 10 655 05/09/2008 13:32:29 NO ACTIVE ONLINE C:\ORACLE\ARCHIVES\MIKA\RL_G3_2.RDO
ONLINE C:\ORACLE\BASES\MIKA\RL_G3_1.RDO
4 10 656 05/09/2008 13:32:52 NO CURRENT ONLINE C:\ORACLE\ARCHIVES\MIKA\RL_G4_2.RDO
ONLINE C:\ORACLE\BASES\MIKA\RL_G4_1.RDO
SQL> insert into test values(0);
1 row created.
SQL> update test set col=col+1;
1 row updated.
SQL> delete test;
1 row deleted.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> exec sys.dbms_logmnr_d.build(Dictionary_FileName=>'Dictionary.ora',Dictionary_Location=>'C:\Oracle\admin\Log')
LogMnr Dictionary Procedure started
LogMnr Dictionary File Opened
Procedure executed successfully - LogMnr Dictionary Created
PL/SQL procedure successfully completed.
SQL> exec sys.dbms_logmnr.add_logfile( LogFileName=>'C:\ORACLE\BASES\MIKA\RL_G4_1.RDO',Options=>sys.dbms_logmnr.NEW);
PL/SQL procedure successfully completed.
SQL> exec sys.dbms_logmnr.start_logmnr(DictFileName=>'C:\Oracle\admin\Log\Dictionary.ora');
PL/SQL procedure successfully completed.
SQL> set recsep wrap
SQL> set recsepchar '-'
SQL> set recsep wrap
SQL> select seg_owner,timestamp,sql_undo,sql_redo from v$logmnr_contents where seg_owner='MICHEL';
SEG_OWNER TIMESTAMP
-------------------------------- -------------------
SQL_UNDO
----------------------------------------------------------------------------------------------------
SQL_REDO
----------------------------------------------------------------------------------------------------
MICHEL 05/09/2008 13:32:56
delete from "MICHEL"."TEST" where "COL" = '0' and ROWID = 'AAANLWAAEAAAARkAAA';
insert into "MICHEL"."TEST"("COL") values ('0');
----------------------------------------------------------------------------------------------------
MICHEL 05/09/2008 13:32:56
update "MICHEL"."TEST" set "COL" = '0' where "COL" = '1' and ROWID = 'AAANLWAAEAAAARkAAA';
update "MICHEL"."TEST" set "COL" = '1' where "COL" = '0' and ROWID = 'AAANLWAAEAAAARkAAA';
----------------------------------------------------------------------------------------------------
MICHEL 05/09/2008 13:32:56
insert into "MICHEL"."TEST"("COL") values ('1');
delete from "MICHEL"."TEST" where "COL" = '1' and ROWID = 'AAANLWAAEAAAARkAAA';
----------------------------------------------------------------------------------------------------
3 rows selected.
Regards
Michel
|
|
|
|
| Re: LogMiner - no records [message #346014 is a reply to message #342731] |
Fri, 05 September 2008 10:32 |
Smash
Messages: 18
Registered: August 2008
|
Junior Member |
|
|
Ok it seems to work.
| Quote: | Oracle 10g introduced optimization in redo log generation and so you will not see small transactions (LogMiner is not able to analyze them)
|
So how big should the transaction be (100 record, 10000...) ?
[Updated on: Fri, 05 September 2008 11:12] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: LogMiner - no records [message #346513 is a reply to message #342731] |
Mon, 08 September 2008 14:42 |
Smash
Messages: 18
Registered: August 2008
|
Junior Member |
|
|
If someone is interested I've made some simple tests.
On my database Oracle 10 g (no patches), I put simple script
create table test (x int);
declare
i int;
begin
i:=0;
for i in 1..50 loop
insert into test values(i);
end loop;
update test set x=x+1;
delete test ;
commit;
end;
It seems that minimal of variable 'i' is approximately 50 - that make 150 operations (insert,update,delete).
Interesting is that log miner return only 27 rows.
select seg_owner,timestamp,sql_undo,sql_redo from v$logmnr_contents where seg_owner='SMASH';
|
|
|
|
| Re: LogMiner - no records [message #346527 is a reply to message #346513] |
Mon, 08 September 2008 15:10 |
|
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Thanks for the feedback, I think it may depend on operation (row) size.
| Quote: | Interesting is that log miner return only 27 rows
|
This is because other operations are flushed in vector array that Log Miner is unable to read and so skips, instead of being recorded in stand alone change vector.
Regards
Michel
[Updated on: Mon, 08 September 2008 15:12] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Tue Dec 24 11:10:40 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
