Statistics are very important to generate efficient execution plans and thus directly related to database performance. We have nightly maintenance window, which runs for an hour, each alternate day.
As we are growing very fast, the 1 hour window sometimes can't cover all the tables and indexes.
Here is how we could find the stale/obsolete statistics - data for those objects have been modified more than 10%.
How to find?
SQL> SET SERVEROUT ON
SQL> DECLARE
ObjList DBMS_STATS.ObjectTab;
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'PROD', objlist=>ObjList, options=>'LIST STALE');
FOR i IN ObjList.FIRST..ObjList.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(ObjList(i).ObjName || ' ' || ObjList(i).ObjType || ' ' || ObjList(i).partname);
END LOOP;
END;
/
In my case, it were showing a couple of tables -
CRITICAL_INCIDENT - TABLE
EXTRA_TEMPLATE - TABLE
MODULE_ASSIGNMENT - TABLE
How to fix?
Simple - by collecting statistics for those objects -
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'PROD', tabname=>'EXTRA_TEMPLATE', estimate_percent=>50, cascade=>true);
More:
* The important view from where we could find the stale information and number of modifications done on a table is USER_TAB_MODIFICATIONS
* Following a data-modification, there may be a few minutes delay while Oracle propagates the information to this view. Use the DBMS_STATS. FLUSH_DATABASE_MONITORING_INFO procedure to immediately reflect the outstanding monitored information kept in the memory.
SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
Jul 31, 2009
Jul 28, 2009
Database Link and Public Database Link
Database link is a useful thing that I use time to time mostly to compare small tables among databases. We have both way (master-master configuration) replication in place, so sometimes, I need to check few things after small release to make sure some tables are in sync.
I thought, it is worth writing few words about this.
What is database link?
Officially, a database link is a schema object in one database that enables access to other objects on another database. I would like to say - it is kind of 'a connection cable' that establish connections between databases.
Side rule: If the value of the GLOBAL_NAMES initialization parameter is TRUE, then the database link must have the same name as the database to which it connects.
In SQL statements in source database, we can refer to a table or view on the other destination database by appending @dblink_name (at sign then db link name) to the table or view name.
How to create database link?
For example, we want to access a destination database which is known as DX01.
CREATE DATABASE LINK DX01.DOMAIN.NET CONNECT TO prod IDENTIFIED BY xyz USING 'DX01';
Here,
* DX01.DOMAIN.NET is the db link name
* prod is the destination schema and xyz is the pass to access that. So we would give access through 'prod' schema on destination server.
* DX01 is the service name for the destination database, which should be found in tnsnames.ora file on the source database.
Now, I can compare tables and also issue other commands to access tables on DX01 remote database using the database link.
SQL> SELECT COUNT(*) FROM LOGIN@DX01.DOMAIN.NET;
Also, to compare tables if those are in sync or not -
SQL> SELECT * FROM LOGIN
MINUS
SELECT * FROM LOGIN@DX01.DOMAIN.NET;
Who owns the database link?
By default, with the create syntax shown above, the database user who is creating database link will own this link - no other user will be able to use this.
To get information of the existing database links in a database, need to query the following view -
SQL> SELECT * FROM DBA_DB_LINKS;
Public database link
There may be cases where I want to create only one database link and would like to use the link for all the schema in source database. Public database link is the answer. The PUBLIC key word is specified to create a public database link available to all users.
CREATE PUBLIC DATABASE LINK DX01.DOMAIN.NET USING 'DX01';
Now, for example, HR schema can use this link, PROD schema can use this link and others too in source database.
Side note: To create a public database link, we will need the CREATE PUBLIC DATABASE LINK system privilege.
I thought, it is worth writing few words about this.
What is database link?
Officially, a database link is a schema object in one database that enables access to other objects on another database. I would like to say - it is kind of 'a connection cable' that establish connections between databases.
Side rule: If the value of the GLOBAL_NAMES initialization parameter is TRUE, then the database link must have the same name as the database to which it connects.
In SQL statements in source database, we can refer to a table or view on the other destination database by appending @dblink_name (at sign then db link name) to the table or view name.
How to create database link?
For example, we want to access a destination database which is known as DX01.
CREATE DATABASE LINK DX01.DOMAIN.NET CONNECT TO prod IDENTIFIED BY xyz USING 'DX01';
Here,
* DX01.DOMAIN.NET is the db link name
* prod is the destination schema and xyz is the pass to access that. So we would give access through 'prod' schema on destination server.
* DX01 is the service name for the destination database, which should be found in tnsnames.ora file on the source database.
Now, I can compare tables and also issue other commands to access tables on DX01 remote database using the database link.
SQL> SELECT COUNT(*) FROM LOGIN@DX01.DOMAIN.NET;
Also, to compare tables if those are in sync or not -
SQL> SELECT * FROM LOGIN
MINUS
SELECT * FROM LOGIN@DX01.DOMAIN.NET;
Who owns the database link?
By default, with the create syntax shown above, the database user who is creating database link will own this link - no other user will be able to use this.
To get information of the existing database links in a database, need to query the following view -
SQL> SELECT * FROM DBA_DB_LINKS;
Public database link
There may be cases where I want to create only one database link and would like to use the link for all the schema in source database. Public database link is the answer. The PUBLIC key word is specified to create a public database link available to all users.
CREATE PUBLIC DATABASE LINK DX01.DOMAIN.NET USING 'DX01';
Now, for example, HR schema can use this link, PROD schema can use this link and others too in source database.
Side note: To create a public database link, we will need the CREATE PUBLIC DATABASE LINK system privilege.
Jul 19, 2009
jdbc driver bug in 10g for batch updates
Recently we have discovered that, our batch updates not working for larger batch size. Then came to know that jdbc does not support batch size more than 65535 in 10g for the following versions-
The operations simply ignores rows after 65535. There was no errors for this types of serious problem!
The problem has been fixed in the versions -
10.2.0.2 Patch 3 on Windows Platforms 10.2.0.3 (Server Patch Set)
The operations simply ignores rows after 65535. There was no errors for this types of serious problem!
The problem has been fixed in the versions -
Apr 10, 2009
Reclaiming unused LOB space
There may be situation where big chunks of LOB data got cleaned up/deleted.
Few days ago, we had an issue with the LOB replication with our replication software. Then we tested DMLs time to time on the table containing LOB. Although, finally we deleted all the test data, it resulted occupying 39 GB space on the tablespace where we have the LOB data.
col sname format a30
col tab format a30
col mb format 99,999,999
set pagesize 100
SQL> select us.segment_name sname, ul.table_name tab, sum(bytes)/1024/1024 mb
from user_segments us, user_lobs ul
where us.tablespace_name='DATA03'
and us.segment_name = ul.segment_name
group by us.segment_name, ul.table_name
order by 3 desc;
SNAME TAB MB
-------------------------- -------------------------- -------------------
SYS_LOB0000052400C00003$$ TEST_DATA 39,670
SYS_LOB0000052362C00005$ USER_FILE 25,923
SYS_LOB0000052365C00003$$ USER_IMAGE 9,313
I knew, I had only 1 MB of data on my TEST_DATA table at the moment.
SQL> SELECT SUM(DBMS_LOB.getlength(RESULT_DATA))/1024/1024 MB
FROM TEST_DATA;
MB
----------
1
Approach -1:
Then the following command had been used to shrink the unused space and reset the HWM. This feature is available from Oracle 10g.
SQL> ALTER TABLE TEST_DATA MODIFY LOB (RESULT_DATA) (SHRINK SPACE);
--- This may take few hours depending on the amount of unused space.
SQL> select us.segment_name sname, ul.table_name tab, sum(bytes)/1024/1024 mb
from user_segments us, user_lobs ul
where us.tablespace_name='DATA03'
and us.segment_name = ul.segment_name
group by us.segment_name, ul.table_name
order by 3 desc;
SNAME TAB MB
-------------------------- -------------------------- -------------------
SYS_LOB0000052362C00005$ USER_FILE 25,923
SYS_LOB0000052365C00003$ USER_IMAGE 9,313
SYS_LOB0000052400C00003$$ TEST_DATA 1
I have reclaimed 39669 MB of space!
To shrink other non-lob tables -
ALTER TABLE test SHRINK SPACE;
Approach - 2:
Another approach to reclaim unused space might be moving the object to another tablespace. This is how the HWM could be reset.
SQL> alter table table_name move lob(lob_column) store as (tablespace tablespace_name);
We have to rebuild all the indexes associated to the table as those will be unusable after moving.
One thing, shrinking is allowed only for those segments which use Automatic Segment Space Management.
Few days ago, we had an issue with the LOB replication with our replication software. Then we tested DMLs time to time on the table containing LOB. Although, finally we deleted all the test data, it resulted occupying 39 GB space on the tablespace where we have the LOB data.
col sname format a30
col tab format a30
col mb format 99,999,999
set pagesize 100
SQL> select us.segment_name sname, ul.table_name tab, sum(bytes)/1024/1024 mb
from user_segments us, user_lobs ul
where us.tablespace_name='DATA03'
and us.segment_name = ul.segment_name
group by us.segment_name, ul.table_name
order by 3 desc;
SNAME TAB MB
-------------------------- -------------------------- -------------------
SYS_LOB0000052400C00003$$ TEST_DATA 39,670
SYS_LOB0000052362C00005$ USER_FILE 25,923
SYS_LOB0000052365C00003$$ USER_IMAGE 9,313
I knew, I had only 1 MB of data on my TEST_DATA table at the moment.
SQL> SELECT SUM(DBMS_LOB.getlength(RESULT_DATA))/1024/1024 MB
FROM TEST_DATA;
MB
----------
1
Approach -1:
Then the following command had been used to shrink the unused space and reset the HWM. This feature is available from Oracle 10g.
SQL> ALTER TABLE TEST_DATA MODIFY LOB (RESULT_DATA) (SHRINK SPACE);
--- This may take few hours depending on the amount of unused space.
SQL> select us.segment_name sname, ul.table_name tab, sum(bytes)/1024/1024 mb
from user_segments us, user_lobs ul
where us.tablespace_name='DATA03'
and us.segment_name = ul.segment_name
group by us.segment_name, ul.table_name
order by 3 desc;
SNAME TAB MB
-------------------------- -------------------------- -------------------
SYS_LOB0000052362C00005$ USER_FILE 25,923
SYS_LOB0000052365C00003$ USER_IMAGE 9,313
SYS_LOB0000052400C00003$$ TEST_DATA 1
I have reclaimed 39669 MB of space!
To shrink other non-lob tables -
ALTER TABLE test SHRINK SPACE;
Approach - 2:
Another approach to reclaim unused space might be moving the object to another tablespace. This is how the HWM could be reset.
SQL> alter table table_name move lob(lob_column) store as (tablespace tablespace_name);
We have to rebuild all the indexes associated to the table as those will be unusable after moving.
One thing, shrinking is allowed only for those segments which use Automatic Segment Space Management.
Mar 29, 2009
Oracle Technology Solutions - go beyond the database server!
Oracle changed it's identity!
Years ago, there was a perception - the words "Oracle" and "Oracle Database" were being used interchangeably. This is not the case anymore - we should remember that :-)
Today, I attended a whole day seminar organized for the Oracle partners in Bangladesh. There were three guys from Oracle Corporation conducted those five back to back sessions.
The sessions were basically based on the following five main product lines -
1. Oracle Technology Infrastructure
2. Oracle Fusion Middleware
3. Enterprise Security
4. Business Process Management and
5. Oracle Enterprise Management Solutions
Mr. Jonathan Tan, Senior Solutions Manager, Oracle Fusion Middleware, conducted the session on middleware architectures and various product lines Oracle provides. He also covered the Business Process Management products fits to Enterprise a.
Mr. Mazhar Ali, Sotutions Manager, SAGE West, covered a number of topics on Oracle Technology Infrastructures, Enterprise Security and Oracle Enterprise Management Solutions. The sessions were very informative and gave a very good sense about the variety of product lines for Enterprise solutions.
I found a number of interesting things to look at further -
* Information Lifecycle Management (ILM)
* Information Rights Management (IRM)
* Application Diagnostics for Java (AD4J)
* Application Testing Suite (ATS)
* SQL Performance Analyzer (SPA)
* Oracle Real Application Testing
* Oracle Coherence
And a lot more! So, Oracle is not just the database provider - rather it got all the products from the bottom to top on the stack of Enterprise product lines.
More information is available on Oracle biz site -
http://www.oracle.com/
Years ago, there was a perception - the words "Oracle" and "Oracle Database" were being used interchangeably. This is not the case anymore - we should remember that :-)
Today, I attended a whole day seminar organized for the Oracle partners in Bangladesh. There were three guys from Oracle Corporation conducted those five back to back sessions.
The sessions were basically based on the following five main product lines -
1. Oracle Technology Infrastructure
2. Oracle Fusion Middleware
3. Enterprise Security
4. Business Process Management and
5. Oracle Enterprise Management Solutions
Mr. Jonathan Tan, Senior Solutions Manager, Oracle Fusion Middleware, conducted the session on middleware architectures and various product lines Oracle provides. He also covered the Business Process Management products fits to Enterprise a.
Mr. Mazhar Ali, Sotutions Manager, SAGE West, covered a number of topics on Oracle Technology Infrastructures, Enterprise Security and Oracle Enterprise Management Solutions. The sessions were very informative and gave a very good sense about the variety of product lines for Enterprise solutions.
I found a number of interesting things to look at further -
* Information Lifecycle Management (ILM)
* Information Rights Management (IRM)
* Application Diagnostics for Java (AD4J)
* Application Testing Suite (ATS)
* SQL Performance Analyzer (SPA)
* Oracle Real Application Testing
* Oracle Coherence
And a lot more! So, Oracle is not just the database provider - rather it got all the products from the bottom to top on the stack of Enterprise product lines.
More information is available on Oracle biz site -
http://www.oracle.com/
Mar 21, 2009
Oracle User Profile and Resource Management
Sometimes we don't notice that there is something called 'User Profile' used to manage user resources and other important things. I would like to say in simple words - 'User profile' is some system parameters specific to a user.
The good use of profile could be password management for the user.
What is the profile assigned to a user?
When we create a database user, the default profile is assigned automatically.
SQL> SELECT x.username, x.profile
2 FROM dba_users x
3 WHERE username='SCOTT'
4 ORDER BY x.username;
USERNAME PROFILE
------------- -----------------------------------------------
SCOTT DEFAULT
For example, how many time(s) a database user/schema can attempt with incorrect login/password before the account got locked.
What are there in default profile?
SQL> SELECT p.resource_name, p.limit
2 FROM dba_users u, dba_profiles p
3 WHERE u.profile=p.profile
4 and u.username = 'SCOTT'
5 ORDER BY p.resource_name;
RESOURCE_NAME LIMIT
--------------------- ---------------------------
COMPOSITE_LIMIT UNLIMITED
CONNECT_TIME UNLIMITED
CPU_PER_CALL UNLIMITED
CPU_PER_SESSION UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
IDLE_TIME UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PRIVATE_SGA UNLIMITED
SESSIONS_PER_USER UNLIMITED
How to create a profile?
SQL> CREATE PROFILE TEMP_10_DAYS_USER LIMIT PASSWORD_LIFE_TIME 10 SESSIONS_PER_USER 5;
If this new profile is assigned to 'SCOTT' user, the account will be valid for 10 days and the user could open 5 concurrent sessions.
SQL> ALTER USER SCOTT PROFILE TEMP_10_DAYS_USER;
The detailed Oracle document could be found here -
How to change a profile?
SQL> ALTER PROFILE TEMP_10_DAYS_USER LIMIT PASSWORD_LIFE_TIME 10 SESSIONS_PER_USER 10;
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6010.htm
The good use of profile could be password management for the user.
What is the profile assigned to a user?
When we create a database user, the default profile is assigned automatically.
SQL> SELECT x.username, x.profile
2 FROM dba_users x
3 WHERE username='SCOTT'
4 ORDER BY x.username;
USERNAME PROFILE
------------- -----------------------------------------------
SCOTT DEFAULT
For example, how many time(s) a database user/schema can attempt with incorrect login/password before the account got locked.
What are there in default profile?
SQL> SELECT p.resource_name, p.limit
2 FROM dba_users u, dba_profiles p
3 WHERE u.profile=p.profile
4 and u.username = 'SCOTT'
5 ORDER BY p.resource_name;
RESOURCE_NAME LIMIT
--------------------- ---------------------------
COMPOSITE_LIMIT UNLIMITED
CONNECT_TIME UNLIMITED
CPU_PER_CALL UNLIMITED
CPU_PER_SESSION UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
IDLE_TIME UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PRIVATE_SGA UNLIMITED
SESSIONS_PER_USER UNLIMITED
How to create a profile?
SQL> CREATE PROFILE TEMP_10_DAYS_USER LIMIT PASSWORD_LIFE_TIME 10 SESSIONS_PER_USER 5;
If this new profile is assigned to 'SCOTT' user, the account will be valid for 10 days and the user could open 5 concurrent sessions.
SQL> ALTER USER SCOTT PROFILE TEMP_10_DAYS_USER;
The detailed Oracle document could be found here -
How to change a profile?
SQL> ALTER PROFILE TEMP_10_DAYS_USER LIMIT PASSWORD_LIFE_TIME 10 SESSIONS_PER_USER 10;
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6010.htm
Mar 17, 2009
Oracle Exadata Servers - For high performance database computing..
I had an opportunity last week to meet and discuss with one of the Oracle gurus and also top level Oracle professional Mr. Christopher G. Chelliah, Senior Director & Chief Architect, Asia Pacific Strategic Customers. He was visiting Dhaka for a business trip, I think.
Mr. Christopher gave a nice overview of Exadata - the high performance storage servers. He described the architectural design and some fringe benefits of the new product. I went there on behalf of another company (which is a sister concern of my present company) - we work in a national project. We were evaluating the high performance server for the probable National ID Databases.
Exadata is a complete hardware and software solution that Oracle targets for high performance data warehouses. This is a solution for both server and storage.
There are basically two components of Exadata.
1. The Server
2. The Storage
The server is clustered RAC, configured with multiple nodes, 8 nodes for us I think. The storage consists of 16 cells primarily, all these build the storage system which is basically another 16 database servers altogether!
The cool feature is, the database intelligence is distributed in server side and also in storage. Most of the data filtering are done on storage and the actual results are sent to server. It offloads the unnecessarily data transfer overheads of the network and also less processing for the actual server.
Some fringe benefits/features I marked are -
* Query predicate offloading feature
* Smart scan processing on storage server
* Reduced load on database servers - processings are distributed to storage side
* Optimized I/O and disk functionality
* Reliable hardware
* RAC with 8 nodes cluster for high performance and reliability
* High speed up and down link - 1 Gbps I think
* ASM for transparent storage management
And a lot more!
The technical stuffs could be found in Oracle's business site -
http://www.oracle.com/database/exadata.html
Mr. Christopher gave a nice overview of Exadata - the high performance storage servers. He described the architectural design and some fringe benefits of the new product. I went there on behalf of another company (which is a sister concern of my present company) - we work in a national project. We were evaluating the high performance server for the probable National ID Databases.
Exadata is a complete hardware and software solution that Oracle targets for high performance data warehouses. This is a solution for both server and storage.
There are basically two components of Exadata.
1. The Server
2. The Storage
The server is clustered RAC, configured with multiple nodes, 8 nodes for us I think. The storage consists of 16 cells primarily, all these build the storage system which is basically another 16 database servers altogether!
The cool feature is, the database intelligence is distributed in server side and also in storage. Most of the data filtering are done on storage and the actual results are sent to server. It offloads the unnecessarily data transfer overheads of the network and also less processing for the actual server.
Some fringe benefits/features I marked are -
* Query predicate offloading feature
* Smart scan processing on storage server
* Reduced load on database servers - processings are distributed to storage side
* Optimized I/O and disk functionality
* Reliable hardware
* RAC with 8 nodes cluster for high performance and reliability
* High speed up and down link - 1 Gbps I think
* ASM for transparent storage management
And a lot more!
The technical stuffs could be found in Oracle's business site -
http://www.oracle.com/database/exadata.html
Mar 16, 2009
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
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
Subscribe to:
Posts (Atom)