Home » RDBMS Server » Backup & Recovery » log miner
log miner [message #361467] Wed, 26 November 2008 09:35 Go to next message
allahmorad
Messages: 18
Registered: June 2006
Junior Member
Hi,
if i can not speak english ,sorry.
I run following commands
but when i run last select staement ,only return:

SQL_REDO -------------------------------------------
create table mytbl2 (c1 number,c2 varchar2(10)); 1 rows selected


but i want return information about insert and update.
whey dont show that information?
thanks
allahmorad




connect scott/tiger;

create table mytbl2 (c1 number,c2 varchar2(10));
insert into mytbl2 values (10,'dah');
insert into mytbl2 values(20,'bist');
insert into mytbl2 values(30,'si');
insert into mytbl2 values(40,'chel');
insert into mytbl2 values(50,'panjah');
commit;
select * from mytbl2;
update mytbl2 set c2='test' where c1=20;
commit;

connect / as sysdba

execute dbms_logmnr.add_logfile('D:\log\O1_MF_1_3_4LTN2K1G_.ARC', dbms_logmnr.new);
execute dbms_logmnr.add_logfile('D:\log\O1_MF_1_4_4LTOBJNJ_.ARC', dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile('D:\log\O1_MF_1_5_4LTOPO89_.ARC', dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile('D:\log\O1_MF_1_6_4LTOPQ4W_.ARC', dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile('D:\log\O1_MF_1_7_4LTOPXWB_.ARC', dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile('D:\log\O1_MF_1_8_4LTOPZ1Y_.ARC', dbms_logmnr.addfile);

select * from v$logmnr_logs;

EXECUTE DBMS_LOGMNR.start_logmnr ( options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG );

SELECT sql_redo FROM v$logmnr_contents where sql_redo like '%mytbl2%';
Re: log miner [message #361482 is a reply to message #361467] Wed, 26 November 2008 10:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The other statements are not in the archived logs, add the online ones.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel


Re: log miner [message #361857 is a reply to message #361482] Fri, 28 November 2008 04:59 Go to previous messageGo to next message
allahmorad
Messages: 18
Registered: June 2006
Junior Member
Hi Michel Cadot,
thank , for your help.
but after insert & update commands
i run following commanf for 3 or 4 time.
alter system switch logfile
an i have only 3 online redo logfile,
i think after run this commands.
online redo logfile save as archive logfile.
if that is right. why dont show any information
about insert and update?
best regards
allahmorad.
Re: log miner [message #361867 is a reply to message #361857] Fri, 28 November 2008 05:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Switch does not mean archive.
To archive use "alter system archive log current".

Regards
Michel
Re: log miner [message #361971 is a reply to message #361867] Fri, 28 November 2008 23:40 Go to previous messageGo to next message
allahmorad
Messages: 18
Registered: June 2006
Junior Member
Hi ,
I add The online redo log.
but no change in result.

SQL_REDO
-----------------------------------------
create table mytbl2 (c1 number,c2 varchar2(10));

I think my problem is in
DBMS_LOGMNR.start_logmnr parameters.
But I am not sure.
Re: log miner [message #361975 is a reply to message #361971] Sat, 29 November 2008 00:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I add The online redo log.

Should online redo logS. All groups.

Regards
Michel
Re: log miner [message #361978 is a reply to message #361975] Sat, 29 November 2008 00:47 Go to previous messageGo to next message
allahmorad
Messages: 18
Registered: June 2006
Junior Member
Hi,
add all groups REDO01.LOG ,REDO02.LOG,REDO03.LOG
and add a new row in result ,
but no abbout insert and update in mytbl2.
SQL_REDO
-----------------------------------------
create table mytbl2 (c1 number,c2 varchar2 10));
insert into "SYS"."WRH$_SQLTEXT"("SNAP_ID","DBID","SQL_ID","SQL_TEXT","COMMAND_TYPE","REF_COUNT") 
values ('54','2049544261','d7h6frrgg6c8y',
'SELECT  sql_redo FROM v$logmnr_contents where sql_redo like '%mytbl2%'','3','0');

Regards
Allahmorad

[Updated on: Sat, 29 November 2008 01:40] by Moderator

Report message to a moderator

Re: log miner [message #361980 is a reply to message #361978] Sat, 29 November 2008 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Keep your lines in 80 characters.

You didn't mention your Oracle version (4 decimals).

Regards
Michel
Re: log miner [message #361982 is a reply to message #361980] Sat, 29 November 2008 01:17 Go to previous messageGo to next message
allahmorad
Messages: 18
Registered: June 2006
Junior Member
Hi,
my oracle version is 10g Release 10.2.0.1.0

regards
allahmorad
Re: log miner [message #361986 is a reply to message #361982] Sat, 29 November 2008 01:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So this is the "correct" behaviour.
Oracle introduced in 10g "in memory undo" feature to optimize some operations and the redo records associated to this feature are currently not readable by log miner engine.

Regards
Michel
Re: log miner [message #361988 is a reply to message #361986] Sat, 29 November 2008 01:53 Go to previous message
allahmorad
Messages: 18
Registered: June 2006
Junior Member
thank you very much.

regards
allahmorad
Previous Topic: steps to make Backup by RMAN in shared memory
Next Topic: HOW TO CONNECT RMAN WITH PIPE ?
Goto Forum:
  


Current Time: Sat Dec 10 22:16:09 CST 2016

Total time taken to generate the page: 0.10677 seconds