Monday, August 31, 2009

LogMiner Viewer

Using LogMiner Viewer to Perform a Logical Recovery:
In this module, you will learn how to use LogMiner to analyze your redo log files so that you can logically recover your database.

Thursday, August 27, 2009



LogMiner is an Oracle utility which uses a dictionary meta data (online , from a flat file, or from redologs ) to scan the redo/archive logs and generates a set of SQL statements which would have the same effect on the database as applying the corresponding redo record.

Logminer can be used to mine the logs of the current database or a remote database.

The following steps are needed to analyze logs of a remote database.

1) Build the Dictionary file of the production database and scp to the mining/test server.
2) Get the list of archive logs which you want to mine from production server and scp to the mining/test server.
3) Add Redo Log Files for mining
4) Start LogMiner
6) End the LogMiner Session

Follow the below steps:
1: Log in as sys user
2: execute ('PROD_dict.ora', '/oracle/admin/PROD/utldir');
3: Determine the archive log sequences that are needed by querying v$archived_log on the remote database. (Look at first_time, next_time values).
Ex: /u006/archive/PROD/PROD_1_4829_651194874.arc
4: send the above 2 files mentioned in step 2 and 3 to Mining/Test Server using scp/ftp/sftp etc.

Mining/Test Server:
1: Log in as sys user
2: exec sys.dbms_logmnr.add_logfile('/export/home/oracle/logminer/archive/PROD_1_4829_651194874.arc');
3: exec sys.Dbms_Logmnr.start_logmnr(dictfilename => '/export/home/oracle/logminer/dict/PROD_dict.ora');
4: Query the V$LOGMNR_CONTENTS to see the mined rows
5: exec sys.DBMS_LOGMNR.end_logmnr;

Note: Do not execute step 5 until you have finished quering V$LOGMNR_CONTENTS.

Wednesday, August 26, 2009


When Row cache contention occurs, if the enqueue cannot be gotten within a certain time period, a trace file will be generated in the location with some trace details.
The trace file tends to contain the words:
>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! <<>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! << address="700000036f27628" cid="0(dc_tablespaces)
hash=a6840aa5 typ=9 transaction=0 flags=00008000

The trace will often contain a systemstate dump, although most useful information is in the header section. Typically a session holding the row cache resource will either be on cpu or blocked by another session. If it is on cpu then errorstacks are likely to be required to diagnose, unless tuning can be done to reduce the enqueue hold time. Remember that on a multi node system (RAC) the holder may be on another node and so multiple systemstates from each node will be required. For each enqueue type, there are a limited number of operations that require each enqueue.

Probably the most likely is allocation of new extents. If extent sizes are set low then the application may constantly be requesting new extents and causing contention. Do you have objects with small extent sizes that are rapidly growing? (You may be able to spot these by looking for objects with large numbers of extents). Check the trace for insert/update activity, check the objects inserted into for number of extents.

Check for appropriate caching of sequences for the application requirements.

Deadlock and resulting "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!" can occur if a session issues a GRANT to a user, and that user is in the process of logging on to the database.

Look for any object compilation activity which might require an exclusive lock and thus block online activity.

This is likely to be down to segment allocation. Identify what the session holding the enqueue is doing and use errorstacks to diagnose.

The details are also available at:
Doc ID:

Solution to ROW/LIBRARY Cache locks:
Set the below event in database level and restart the database. This will resolve the issue.
event='32333 trace name context forever, level 10'

Query Rewrite Feature in Materialized Views

Query Rewrite Feature in Materialized Views:
Setup the Environment:
This includes creating a MV_CAPABILITIES_TABLE and a customised function to test this feature on Materialized views.

( statement_id varchar(30) ,
mvowner varchar(30) ,
mvname varchar(30) ,
capability_name varchar(30) ,
possible character(1) ,
related_text varchar(2000) ,
related_num number ,
msgno integer ,
msgtxt varchar(2000) ,
seq number) ;

The above table can be created by executing the below script:

You can create a customised function to generate query rewrite functionalities reports on materialized views.
Refer the below URL to create a function called my_mv_capabilities.

Create/Test the Query Rewrite Feature of Materialized Views:
Create a table called dept:
SQL> create table dept (id number, name varchar2(20));
SQL> insert into dept values (1, 'Finance');
SQL> insert into dept values (2, 'Admin');
SQL> insert into dept values (3, 'HR');
SQL> commit;

Create a test mview:
SQL> Create materialized view test DISABLE QUERY REWRITE as select id,name from dept;

Generate Report:
SQl> set long 5000
SQL> select my_mv_capabilities( 'TEST', 'REWRITE' ) as mv_report from dual ;

Not Capable of:

query rewrite is disabled on the materialized view

query rewrite is disabled on the materialized view

query rewrite is disabled on the materialized view

The above report clarifies that the MV has no query rewrite feature available.

Enable Query Rewrite:
SQL>alter materialized view test enable query rewrite;

Generate Report:
SQl> set long 5000
SQL> select my_mv_capabilities( 'TEST', 'REWRITE' ) as mv_report from dual ;

Capable of:




The above report clarifies that the MV has no query rewrite feature enabled.

Effect of query rewrite on PLAN:
A very good analysis is available below:

How Query Rewrite can be enabled from database level:
Following 2 initialization parameters can enable this feature in database:
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced

Tuesday, August 25, 2009

Database Features: 8i vs 9i vs 10G vs 10GR2

Database Features: 8i vs 9i vs 10G vs 10GR2:
Here is an article which will give you an idea about the major differences in features for various versions of Oracle databases:
Oracle 8i Features:
Auditing - Basic setup instructions to allow the auditing of user actions.

Cost Based Optimizer (CBO) And Database Statistics - Lists the available mechanisms for gathering database statistics that are used by the cost based optimizer.

Dropping Columns - Hide unused columns or completely remove them.

Index Organized Tables - Save space and improve the performance of tables which are predominantly access by their primary keys.

Locally Managed Tablespaces - Reduce contention on the system tables by delegating extent management to individual tablespaces.

LogMiner - Analyze archived redo logs to identify specific specific statements and undo them.

Partitioned Tables And Indexes - Reduce tables and indexes to a more managable size and simultaneously improve performance.

Recovery Manager (RMAN) - Explanation of RMANs basic backup, recovery and reporting functionality.

Refreshing Stale Statistics - Keep your optimizer statistics up to date without increasing the load on the server.

Replication (Simple) - Using materialized views (snapshots) to replicate data on a remote database.

Replication (Advanced) - Using Oracle Advanced Replication for two-way data replication.

Statspack - Analyze the performance of your system using the replacement for the UTLBSTAT/UTLESTAT scripts.

Standby Database - Keep an up-to-date copy of the production database ready for failover in the event of a disaster.

Temporary Tables - Most applications need temporary tables to store information during complex data processing. See how Oracle can manage your temporary tables and keep your data secure.
Oracle9i New Features:
Automatic Segment Free Space Management - Reduce segment header contention and wasted space within blocks by switching from using FreeLists to Automatic Segment Free Space Management.

Automatic Undo Management - Replace you existing rollback segments with self-tuning undo segments.

Bitmap Join Indexes - Increase the performance and reduce the size of your data warehouse.

Data Guard - Oracle9i Data Guard is the new name for Oracle8i Standby Database incorporating a large number of new features.

Flashback Query - Get a consistent view of your data at a previous point in time.

Memory Management In Oracle9i - See how easy memory management has become in Oracle9i.

Multiple Block Sizes - Improve efficiency by having multiple block sizes that relate more closely to the operations that are being performed.

Oracle Managed Files - Let Oracle manage operating system level file placement, naming and cleanup.

Real Application Clusters - A brief introduction to the Oracle9i replacement for Oracle Parallel Server.

Recovery Enhancements In Oracle9i - Reduce unplanned downtime by using the new crash, instance and media recovery features of Oracle9i.

Recovery Manager (RMAN) Enhancements In Oracle9i - Use the latest RMAN features which make backup and recovery quicker and more reliable.

DBNEWID Utility - Change the internal DBID and the database name using this new utility without rebuilding your controlfile.

DBMS_XPLAN - Easily format the output of an explain plan with this replacement for the utlxpls.sql script.

Export BLOB Contents Using UTL_FILE - Use the new UTL_FILE functionality to write binary data to files.

STATISTICS_LEVEL - Let Oracle9i Release 2 control the collection of statistics and advisories with a single parameter.
Oracle 10G New Features:
Flashback Versions Query-

Tablespace Management-


Oracle Data Pump-
create directory dpdata1 as '/u02/dpdata1';
grant read, write on directory dpdata1 to ananda;

* Data Pump Export
expdp ananda/abc123 tables=CASES directory=DPDATA1 dumpfile=expCASES.dmp job_name=CASES_EXPORT
* With Parallel Operation
dumpfile=expCASES_%U.dmp parallel=4
* Data Pump Import
impdp ananda/abc123 directory=dpdata1 dumpfile=expCASES.dmp job_name=cases_import

Flashback Table-
drop table recycletest;
show recyclebin

Managing the Recycle Bin-

Other Uses of Flashback Tables-

Automatic Workload Repository-

Automatic Storage Management-

Incremental Backups Revisited by block change tracking enabled:
alter database enable block change tracking using file '/rman_bkups/change.log';

Flash Recovery Area-
alter system set db_recovery_file_dest = '/ora_flash_area';
alter system set db_recovery_file_dest_size = 2g;
alter system set db_flashback_retention_target = 1440;
alter database flashback on;

Incremental Merge-
backup incremental level_1 for recover of copy with tag level_0 database;

* Compressed Files
backup as compressed backupset incremental level 1 database;
* Recovery Preview
restore database preview;
* Resetlogs and Recovery

RMAN can now readily use all backups, before and after a resetlogs operation, to recover the Oracle database. There is no need to shut down the database to make a backup. This new capability means that the database can be re-opened immediately for the user community after a resetlogs operation.

Enterprise Manager 10g-
Ultra Search HTTP port number = 5620
iSQL*Plus HTTP port number = 5560
Enterprise Manager Console HTTP Port (starz10) = 5500
Enterprise Manager Agent Port (starz10) = 1830

Automatic Segment Management-
High Water Mark to Low Water Mark:
alter table bookings enable row movement;
alter table bookings shrink space compact;
alter table bookings shrink space cascade;

Transportable Tablespaces-

Automatic Shared Memory Management-

ADDM and SQL Tuning Advisor-

10G R2 new features
Manageability Features:
• ASM Command Line Tool
• Drop Empty Datafiles
• Direct SGA Access for Hung/Slow Systems
• Faster Startup
• Manage Multiple Objects in Oracle Enterprise Manager
• Automatic Segment Advisor

Backup and Availability Features
• Oracle Secure Backup
• Dynamic RMAN Views for Past and Current Jobs
• Dynamic Channel Allocation for Oracle RAC Clusters
• Tempfiles Recovery via RMAN
• Flashback Database/Query Through RESETLOGS
• Flashback Database Restore Points
• Flash Recovery Area View

Monday, August 24, 2009

Pre-Requirements for Grid Control

Pre-Requirements for Grid Control:
This chapter provides information about the preinstallation requirements you must meet before installing Enterprise Manager Grid Control (Grid Control).

High Availability Grid Control Architecture

High Availability Grid Control Architecture:

Oracle Enterprise Manager 10g Grid Control is based on a flexible architecture, which allows you to deploy the Grid Control components in the most efficient and practical manner for your organization. This chapter describes some common configurations that demonstrate how you can deploy the Grid Control architecture in various computing environments.

This chapter presents the common configurations in a logical progression, starting with the simplest configuration and ending with a complex configuration that involves the deployment of high availability components, such as server load balancers, Oracle Real Application Clusters, and Oracle Data Guard.

Follow the below link for more information on various recommended architecture of Oracle Grid control. Based on your business need the setup can be built.