LogMiner to analyze online or archived redo logs

We had a situation - yes, other than having a situation, why should I dig stuffs in archive logs! Log mining is not my hobby :-)

I had to find what were going on in database with nightly cron scheduler for a particular day. I looked at archive logs to get all the answer of my questions - LogMiner made my life easy!


What is LogMiner?

Anyone can easily guess, it a tool or one of the capabilities provided by Oracle to look at online redo logs or old archive logs. These logs keep history of activities performed in database. We can find all the activities those happened to database either by the application users or by the system itself - amazing! By initiating LogMiner, we can simply query some database views through SQL interface to find desired information.

It can be used as a data audit tool or for any other sophisticated data analysis.

It's nice to go through the LogMiner benefits -

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/logminer.htm#i1005606


Prerequisites

Supplemental logging must be enabled prior to the redo/archive logs are being generated - this option will put additional information to those logs which will be analyzed by LogMiner later.

SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL>SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

How do I do that?

We need to do all these being a sys user. Otherwise, some special roles will be required explicitely - EXECUTE_CATALOG_ROLE and SELECT ANY TRANSACTION.

Step-1: Add logfile(s)

The procedure will add archive log file as the log mining candidate from where we might extract information.

SQL>EXECUTE DBMS_LOGMNR.ADD_LOGFILE('C:\oracle\product\10.2.0\flash_recovery_area\SHAWON_D\ARCHIVELOG\2009_03_15\O1_MF_1_10_4VRZ1DT4_.ARC', DBMS_LOGMNR.ADDFILE);

We could add as many as we want in the above way.

Step-2: Start LogMiner with data dictionary information

LogMiner requires data dictionary information to translate Object ID (kept in redo/archive logs) to Object Names when it returns data as a part of data analysis.

The dictionary options are -

1. Using the Online Catalog
2. Extracting a LogMiner Dictionary to the Redo Log Files
3. Extracting a LogMiner Dictionary to the Redo Log Files

I used the online catalog option as I could use the database during off peak hours for log analysis.

SQL>EXECUTE DBMS_LOGMNR.START_LOGMNR( -
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

Step-3: Query LogMiner view to retrieve desired information

The main source of data is V$LOGMNR_CONTENTS. Just describe the view and queried as I wanted.

Forexample, I wanted to know all the operations within a specified period of time by a user -

SELECT OPERATION, SQL_REDO, SQL_UNDO, TIMESTAMP
FROM V$LOGMNR_CONTENTS
WHERE USERNAME = 'TEST'
AND TIMESTAMP
BETWEEN TO_DATE('03-15-2009 09:40:00 am','mm-dd-yyyy hh:mi:ss am')
AND TO_DATE('03-15-2009 09:50:00 am','mm-dd-yyyy hh:mi:ss am')
ORDER BY TIMESTAMP;


Step-4: Close LogMiner

SQL>EXECUTE DBMS_LOGMNR.END_LOGMNR;

That's it! You may or may not close. Witout closing the previous one, we can start another LogMinger session.


I used this detailed Oracle doc during my activities -

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/logminer.htm

Oracle: SQL_TRACE &TKPROF Usage

I saw fringe benefits of writing blogs. In child days, teachers were saying that - one time writing is better than ten times reading! This is kind of valuable statements I always realize.

I did use SQL_TRACE and TKPROF earlier - before 3 years may be. But again last week, I had to look at Oracle docs to refresh my memory! This time what I did, I am trying to put it here so that next time, if needed, I won't have to spend much time on that :-)

What is SQL_TRACE?

Simply, it is a dynamic parameter could be set in session or system level to enable some detailed tracing of database activities.

What is TKPROF?

Ha! This was a common question I asked many times in interviews earlier days. It is a command line tool supplied by Oracle to format tracing output. That's it!

Why SQL_TRACE & TKPROF come altogether?

TKPROF has made our life easier to understand the contents of trace file. When we enable tracing - I mean when we set SQL_TRACE=TRUE, Oracle does good stuffs for us which is not formatted well to retrieve useful information from those files. TKPROF makes the output readable.

SQL Trace facility and TKPROF are two basic performance diagnostic tools used to analyze query and server performance. Now a days, I am using other tools (AWR, ADDM etc) more than this for performance analysis. Actually I used the SQL_TRACE this time to diagnose a BLOB problem - I was interested to see some information when BLOB got inserted into database using jdbc, anyways.


Considerations before using SQL_TRACE

We need to check the following 3 parameter before enabling sql tracing.

1. TIMED_STATISTICS - > This enables and disables the collection of timed statistics, such as CPU and elapsed times

2. MAX_DUMP_FILE_SIZE - > When the SQL Trace facility is enabled at the instance level, every call to the server produces a text line in a file in the operating system's file format. This controls the size of the file.

3. USER_DUMP_DEST - > Determines the destination of log files.

Come to the point - how to enable this?

--session level
ALTER SESSION SET SQL_TRACE = TRUE;

--system level
ALTER SYSTEM SET SQL_TRACE = TRUE;

How to use TKPROF?

In simple form, TKPROF takes source trace file and then the name of output file that will be generated by TKPROF.

# tkprof trace_file output=output_file_name

In windows, the command would be -

(running from command prompt...)
C:\Users\shawon>tkprof C:\oracle\product\10.2.0\admin\shawon_db\udump\shawon_ora_4288.trc output = C:\oracle\product\10.2.0\admin\shawon_db\udump\tmp.out


The detailed documents could be found from this site -

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/sqltrace.htm#8723

Date conversion according to timezone

We do often need to generate reports from database. Usually dates are stored in database formatted to a specific timezone - preferably in server timezone. When we retrieve those date related data, we have to format those according to users' timezone to put sense in date information.

We could easily do that using the date function NEW_TIME(). We have to need the source/base timezone and the desired timezone we want to convert.

For example, if I want to convert a date which is stored in 'US/Eastern' timezone to user's timezone 'US/Mountain' - I could do it easily in the following ways -

--To look up the timezone short names to be used in the function.
SELECT * FROM v$timezone_names;

Example - 1:

--US/Eastern to US/Mountain
SELECT TO_CHAR(REPORT_DATE, 'DD-MON-YY HH:MI:SS AM') ORIGINAL_EST, TO_CHAR(NEW_TIME(REPORT_DATE, 'EST','MST'), 'DD-MON-YY HH:MI:SS AM') "Eastern to Mountain"
FROM USER_REPORTS
WHERE rownum <4;

Example - 2:

--sysdate conversion, Central to Pacific
SELECT TO_CHAR(NEW_TIME(sysdate, 'CDT','PDT'), 'DD-MON-YY HH:MI:SS AM') "Central to Pacific"
FROM DUAL;

Third party replication product for Oracle database!

Before I forget, I want to write a few words on this. It's my feeling, one might say "gut feeling" about the technology or technological world. Sometimes, what it seems, may not be the case for a product. In my opinion, people actually buy "trust" with money! It is fair as long as that meets the demands.

For replication, the product we chose, we had been told that the product were being used by many fortune 500 companies and wall street shops were really happy with the product.

The reality is, we found a number of problems time to time, which we did never expect from such a "high profile" product. It seems that they never had an extensive user like us or they were lagging for testing resources, sounds funny though!

* During the transition phase, one special feature of the product swapped our data between two servers and the databases got out of sync. It was really really hard to understand which data was more correct. To correct the problem, I had to go through about each and every 7000 records - one by one :-(

* Initially, the LOB replications were problematic - after replication, it inserted invalid and empty spaces in between each and every characters on destination database. We could not believe this!

* Again, after 2 months, I discovered another replication problem with CLOB data type. At destination server, after replication, some character got truncated. This problem specially happens with Multibyte character set. We reported the problem but "they" did not understand what was happening. On this issue, I had to spend 3/4 days to analyze, reproduce and pin-point the problem and I described why and how this is happening with a big explanation like a detective - the company were not able to find the cause of the problem initially.

* After few months, again discovered problem with BLOB replication. The product generates some internal files after capturing changes. For some bug in their product, what I saw is, for 20 MB data size in BLOB field, the product were generating internal files of size more than 20 GB! So, one night we caught for out of disk space!

It seems that we were doing extensive testing for their product and coming up with bugs to fix.

Well, I am not going to tell you which third party replication product we are using. But what I want to point out is - one should test any third party product (my recommendation) if they think that they are doing something vary important for their customers. We can't always rely on what other says or how big the product profile is!

Oracle: Cursor sharing and database performance

Sometimes, we experience hard parsing and more CPU utilization issues in database. In an ideal world, literally there should be no hard parsing in a database. But in reality, it's not possible to completely overcome this. To parse a SQL, CPU cycles are needed. So, more hard parsing means more CPU utilization. Oracle parse a query before fetching the actual data from database.

Parsing are of two types -

Hard parsing: When bind variables are not used, oracle thinks every query is new to it.

Soft Parsing: When bind variables are used, still oracle parse (which is called soft parse) the query if it is in memory, it requires very less CPU.

The hard parsing should not be more than 1-2% for a well-tuned application.

In this situation, most of the time we need to deal with the settings of CURSOR_SHARING parameter - the values availabe to use for this parameter are EXACT, FORCE and SIMILAR.

I was reading some articles other than Oracle's own docs. Here I am presenting some basic concept of CURSOR_SHARING parameter settings in Oracle database.

There are three modes -

EXACT --> The default setting is EXACT. In this case, cursor for SQL will only be shared if bind variables are used in the query. If literals are used in the query, each time the the query will be parsed. It forces us to use bind variable in the application otherwise performance impact would be obvious.

FORCE --> If the setting is FORCE, literals in the query will be overwritten and cursor for the query will be shared. But in this case, the execution plan will be fixed and not be changed.

SIMILAR --> This feature provides the combined capabilities of EXACT and FORCE. In this case, literals will be replaced first and also there will be scope to change the plan for any new bind values - that means the plan will not be static.

The setting FORCE or SIMILAR depend on the necessity of the application. Tom nicely described in his article -

http://www.oracle.com/technology/oramag/oracle/06-jan/o16asktom.html

Bulk data manipulation and index rebulding

Sometimes we do need to manipulate large amount of data in database. This manipulation might include INSERT, UPDATE or DELETE operations. We need to analyze tables and indexes after such operation with large data. From my experience, I have seen, up-to-date statistics plays major role in SQL performance.

For example, we might be interested to analyze the table from where I just deleted 500K records and rebuild all it's corresponding indexes for current statistics.

The following scripts takes TABLE NAME as input and generates the necessary scripts for the said purposes.


INPUT:
LB_WORKLIST

OUTPUTS:
ANALYZE TABLE LB_WORKLIST COMPUTE STATISTICS;
ALTER INDEX LB_WORK_EXP_I REBUILD COMPUTE STATISTICS;
ALTER INDEX LB_WORKLIST_NOTIF REBUILD COMPUTE STATISTICS;
ALTER INDEX LB_WORK_FRM_LOGIN_I REBUILD COMPUTE STATISTICS;
ALTER INDEX LB_WORKLIST_LOGIN_TEST REBUILD COMPUTE STATISTICS;


/* scripts- table analyze and index rebuild */
SET SERVEROUT ON;

DECLARE
TYPE alalyze_cur IS REF CURSOR;
ind_cursor alalyze_cur;
TABLE_NAME VARCHAR2(30);
INDEX_NAME VARCHAR2(30);
stmt VARCHAR2(200);

BEGIN
TABLE_NAME:=UPPER('&TABLE_NAME');
stmt := 'SELECT index_name FROM USER_INDEXES WHERE INDEX_TYPE <> ''LOB'' AND TABLE_NAME = :j';
DBMS_OUTPUT.PUT_LINE('ANALYZE TABLE '||TABLE_NAME||' COMPUTE STATISTICS;');
OPEN ind_cursor FOR stmt USING table_name;

LOOP
FETCH ind_cursor INTO index_name;
EXIT WHEN ind_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('ALTER INDEX '||index_name||' REBUILD COMPUTE STATISTICS;');
END LOOP;
END;
/

Google Chrome: Open source web browser by Google

It's good to have another browser from a company like Google which always promises to deliver simple but effective applications. Google Chrome is an open source - and off course free web browser developed by Google.

Google is saying that it is multi-threaded web browser and most importantly the browser will never get crashed! At a glance, I like the cool features "Recently Closed Tabs" and things like "Search in History" - others feel similar to Firefox 3.0.1. Let's see more about how it is different from other browsers!

Google knows the trick of presenting complex information in a simpler way and make it understandable just like a story. Few words about the Chrome from Google can be found from the link below. It a pleasure to read the Chrome book. Here it goes -

http://books.google.com/books?id=8UsqHohwwVYC&printsec=frontcover#PPP1,M1

The Chrome can be downloaded from this link -

http://www.google.com/chrome/?hl=en?hl=en

The 911 service and GPS location tracking

Let me describe the background in brief -

We have web applications. The users can fill up forms from anywhere in the world. Some service needs to be physically present to some physical location. How could we know whether the forms have been filled up from a desired physical location or how could we verify if users logged in to the system from office or home?

The solution we thought - is GPS!

I did some studies about some similar services those use GPS information. The 911 emergency service is one of them.

The 911 service in cell phone is an interesting one. There are basically two kinds of solutions available for the cell phone operators - Network assisted location tracking and GPS assisted location tracking.

For the first case, some operator like T-Mobile uses Triangulation method to locate subject when it is seen by 3 transmitting tower. At any given time, 3 towers calculates receiving signal based on time differences and/or angle differences and locates on relative readings.

Other operators, like Sprint supports E911 based on GPS information. A 911 call triggers the phone chip - the GPS coordinates are transmitted along with baseband signal to 911 operator and the nearest Public Safety Answering Point. This feature is built in with all phone made after 2004-05 - I think.

There are other services provided by operators. For example, Sprint offers the TeleNav service. In that case, tracking by private parties require the person whose phone is being tracked to explicitly give permission and monthly charge. And usually client software has to be installed on the phone to provide these kind of services.

For us, the desktop based solution - where a GPS device will be attached to users' computer, a program will be running inside web browser, having Java Runtime Environment installed in the machine, the web browser will collect GPS coordinates from pc's port and send these data along with application data to the servers.

The demo version worked successfully!

Some of the interesting articles I liked can be found here -

http://electronics.howstuffworks.com/gadgets/travel/gps.htm