client_info null in log miner session_info field

From: Sreejith S Nair <Sreejith.Sreekantan_at_ibsplc.com>
Date: Tue, 2 Nov 2010 16:39:35 +0530
Message-ID: <OF47A6820C.23025708-ON652577CF.003D1D20-652577CF.003D4D81_at_ibsplc.com>

Hi friends,

I am facing a strange issue. I am trying to get the user information from client_info in logminer after i use log miner utility. Please see the tests below.

Database pre checks

H:\>sqlplus
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Nov 2 16:27:16 2010 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Enter user-name: sys/ctsmanager_at_CQR1 as sysdba Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> SELECT name, supplemental_log_data_min FROM v$database;

NAME SUPPLEME

---

CQR1 YES SQL> select group#,status from v$log;

GROUP# STATUS

---

1 CURRENT
2 INACTIVE
3 INACTIVE SQL> select member from v$logfile where GROUP#=1;

MEMBER
/applns/oracle/product/10.2.0/db_1/oradata/CQR1/redo01.log

Making the data change

Now, I took a client session and made one data change

H:\>sqlplus cts_qrc01/cts_qrc01_at_CQR1
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Nov 2 16:26:06 2010 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> exec dbms_application_info.set_client_info('Updated By User 1');

PL/SQL procedure successfully completed.

SQL> update persons set FST_NM='Neerus' where pers_id=161128;

1 row updated.

SQL> commit;

Commit complete.

Using the Log Miner

I expect the 'Updated By User 1' text to come against my redo record in the online redo log, when I use logminer on it.

H:\>sqlplus cts_qrc01/cts_qrc01_at_CQR1
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Nov 2 16:30:48 2010 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> EXECUTE sys.DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/applns/oracle/product/10.2.0/db_1/oradata/CQR1/redo01.log',OPTI ONS => sys.DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

SQL> EXECUTE sys.DBMS_LOGMNR.START_LOGMNR( OPTIONS => sys.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG+sys.DBMS_LOGMNR.COMMITTED_D ATA_ONLY); PL/SQL procedure successfully completed.

SQL> SELECT session_info,table_name
2 FROM v$logmnr_contents
3 WHERE table_name = 'PERSONS'
4 AND operation 'DDL'
5 AND session_info IS NOT NULL;

SESSION_INFO
TABLE_NAME
login_username=CTS_QRC01 client_info= OS_username=a-2541 Machine_name=TRV_IBS_IN
DIA\N7727
PERSONS
SQL> SELECT session_info,table_name,sql_redo 2 FROM v$logmnr_contents
3 WHERE table_name = 'PERSONS'
4 AND operation 'DDL'
5 AND session_info IS NOT NULL;

SESSION_INFO
TABLE_NAME
SQL_REDO
login_username=CTS_QRC01 client_info= OS_username=a-2541 Machine_name=TRV_IBS_IN
DIA\N7727
PERSONS
update "CTS_QRC01"."PERSONS" set "FST_NM" = 'Neerus', "SEARCH_NM" = 'Neeru', "PS
WD_TXT" = 'xy', "TMSTMP" = TO_DATE('02-NOV-10', 'DD-MON-RR') where
"FST_NM" = 'N

eeruu' and "SEARCH_NM" = 'Neeru' and "PSWD_TXT" = 'xy' and "TMSTMP" = TO_DATE('0
2-NOV-10', 'DD-MON-RR') and ROWID = 'AAAM8bAAGAABciNAAA';

SESSION_INFO
TABLE_NAME
SQL_REDO
SQL> EXECUTE sys.DBMS_LOGMNR.END_LOGMNR();

PL/SQL procedure successfully completed.

SQL> select banner from v$version;

BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production

SQL> The client_info is still 'null'. Please guide me with this.

Am I missing something here. OS is Linux X86 , 64 Bit

Any help is appreciated.
Thanks
SSN. Thank You,

Kind Regards,
Sreejith Nair

DISCLAIMER:
"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Nov 02 2010 - 06:09:35 CDT