Manually creating database in 11g

As you know Database creation is most important and primary work for DBA.
now 11g also introduce and in 11g it is take few mintues to do :) with very easy steps.

http://dbataj. blogspot. com/2008/ 05/manually- db-creation- with-11g. html

Performance Optimization by avoiding Table Locks

Performance Optimization by avoiding Table Locks

Locking is a very important part of any database system. Database table locks are coordinated through global inter-instance communication for RAC. It is the fact that properly designed applications need not to lock entire tables and therefore table locks can be disabled to improve locking efficiency with minimal adverse side effects.

Refer: http://www.articles.freemegazone.com/oracle-table-locks.php

Database Security

Database Security: Step by step guideline

As a database administrator one of my prime responsibilities is to secure my organization's database from unauthorized access. Developing a database is not difficult at all but developing a secure database is really a complicated task.

Refer: http://www.articles.freemegazone.com/databaseSecurity_guidelines.php

Tuning Database Materialized Views

Tuning Database Materialized Views
A materialized view is a piece of replicated data that must be kept synchronized with the master tables. Materialized Views are a wonderful tool for reducing repetitive I/O. They prevent unnecessary repeating large-table full-table scans, as summaries are computed, over and over.
Tuning materialized views is very challenging. In this article I will give some useful tips for tuning the materialized views.
Refer: http://www.articles.freemegazone.com/oracle-materialized-views.php

Hot Database Jobs

Leading companies all over the world are urgently looking for bright professionals in Engineering and IT fields. Visit Your Correspondingcountry page to see full job descriptions.

Australia Jobs http://www.exposedjobs.com/ausjobs.php

Untied states Jobs:- http://www.exposedjobs.com/usajobs.php

India Jobs:- http://www.exposedjobs.com/indiajobs.php

United Kingdom Jobs:- http://www.exposedjobs.com/ukjobs.php

Pakistan Jobs:- http://www.exposedjobs.com/pakjobs.php

Middle East Jobs:- http://www.exposedjobs.com/mejobs.php

Best Setting for Oracle Multiblock Read Count

Refer: http://www.articles .freemegazone. com/multiblock- read-count- MBRC.php

Performance Impact of Low Cardinality Leading Columns in an Index

Refer: http://www.articles .freemegazone. com/low-cardinal ity-leading- columns-in- index.php

Oracle Index Rebuild Locking Issues

Oracle Index Rebuild Locking Issues
Refer: http://www.articles .freemegazone. com/oracle- index-rebuild- locking.php

The Power of 11g Snapshot Standby Database for Oracle 10g

Refer: http://www.articles.freemegazone.com/11g-snapshot-standby-db-10g.php

ORA-28547: connection to server failed, probable Oracle Net admin error

Cause:
--------
A failure occurred during initialization of a network connection from a client process to the Oracle server: The connection was completed but a disconnect occurred while trying to perform protocol-specific initialization, usually due to use of different network protocols by opposite sides of the connection. This usually is caused by incorrect Oracle Net administrative setup for database links or external procedure calls. The most frequent specific causes are: -- The connection uses a connect string which refers to a Heterogeneous Services agent instead of an Oracle server. -- The connection uses a connect string which includes an (HS=) specification.

Action:
--------
Check Oracle Net administration in the following ways: -- When using TNSNAMES.ORA or an Oracle Names server, make sure that the client connection to the ORACLE server uses the correct service name or SID. -- Check LISTENER.ORA on the connection end point's host machine to assure that this service name or SID refers to the correct server. -- Confirm in TNSNAMES.ORA or the equivalent service definition that the connect string does NOT contain (HS=).

Partitioning in Oracle 11g

Partitioning is one of the most sought after options for data warehousing. Almost all Oracle data warehouses use partitioning to improve the performance of queries and also to ease the day-to-day maintenance complexities. Starting with 11G, more partitioning options have been provided and these should reduce the burden of the DBA to a great extent.

Refer: http://orafaq.com/node/1912

Table Fragmentation

When rows are not stored contiguously, or if rows are split onto more than one block, performance decreases because these rows require additional block accesses.

Note that table fragmentation is different from file fragmentation. When a lot of DML operations are applied on a table, the table will become fragmented because DML does not release free space from the table below the HWM.

HWM is an indicator of USED BLOCKS in the database. Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted. Since Oracle knows that blocks beyond the high water mark don't have data, it only reads blocks up to the high water mark when doing a full table scan.

Refer: http://orafaq.com/node/1936

Complete ETL Process - step by step using the Oracle Warehouse Builder

Refer: http://www.hotcoding.com/dbs/oracle/35440.html

Oracle Warehouse Builder

Refer documentation: http://www.oracle.com/technology/documentation/warehouse.html

There is a table which has 2 date columns as follows:-

date1 date2
05-10-07 15-10-07
10-10-07 20-10-07

The output should be as follows:-

date1 date2 count (no.of days between the date range)
01-10-07 03-10-07 3
05-10-07 20-10-07 16

Query
-------

drop table t purge;
create table t (date1 date, date2 date);
truncate table t;insert into t select dt-dbms_random.value(1,9) date1, dt date2from (select sysdate-dbms_random.value(1+5*level,3+5*level) dt from dual connect by level <= 10);

SQL> with
step1 as (
select date1, date2, case when nvl(lag(date2) over (order by date1, date2), date1-1) < date1 then row_number() over (order by date1, date2) end flag from t ),
step2 as (
select date1, date2, max(flag) over (order by date1, date2) grp from step1)
select min(date1) date1, max(date2) date2, round(max(date2)-min(date1)) daycount from step2 group by grp order by 1;

Oracle RAC Listener

Oracle listener listens for users connecting to the database. It is a small yet important part of Oracle RAC configuration. Most database administrators set listeners by default which may result in wrong configuration.
In this article I will give some efficient tips that will help you ensure optimal and secure configuration for your Oracle listener.

Refer: http://www.articles.freemegazone.com/oracle-rac-listener-dba-tips.php

Improved Statistics Gathering in Oracle 11g

Oracle uses dbms_stats to manage optimizer statistics. The dbms_stats estimates statistics in a better way especially for large partitioned tables and thus results in faster SQL execution plans. dbms_stats package provides several pl/sql procedures to gather statistics for a table, schema, or a database.

Refer: http://www.articles.freemegazone.com/improved-statistics-gathering-oracle-11g.php

Recovering Controlfile without Resetlogs

Oracle always requires you to recover and then open the database with resetlogs. However you do not always need to do a resetlogs when you open the database. If you recreate the controlfile rather than restoring a backup then Oracle allows you to open the database normally. Normally all information in controlfile like RMAN configuration and records, incarnation table etc will be lost so it is preferred to keep the old file.

Refer: http://www.articles.freemegazone.com/recovering-controlfile-without-resetlogs.php

How to trim the spaces between the words in oracle

Ex : the word is 'HAI HELLO'
The o/p should be 'HAI HELLO'

SQL> SELECT REGEXP_REPLACE('hai hello', '[[:blank:]]+', ' ') FROM dual;

REGEXP_RE
--------------
hai hello

ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old

If the ORA-01555 error occured along with the ora-22924 error, then the issue is not related with the undo segment but with the LOB segment which is being overwritten.

22924, 00000, "snapshot too old"
Cause: The version of the LOB value needed for the consistent read was already overwritten by another writer.

Action: Use a larger version pool.

To fix:
-------
ALTER TABLE tablename MODIFY LOB (lobname) (PCTVERSION 100);
If the pctversion is set 100, then lob images will not get overwritten.

Looks like this happens with many concurrent updates. Snapshots and long running queries need to use an old version of a LOB - that version gets overwritten as PCTVERSION is too small.

SQL Tuning - Oracle Cost Based Optimizer (CBO)

Oracle's cost-based SQL optimizer (CBO) is an extremely sophisticated component of Oracle that governs the execution for every Oracle query. The CBO has evolved into one of the world's most sophisticated software components, and it has the challenging job of evaluating any SQL statement and generating the "best" execution plan for the statement.

Refer: http://www.dba-oracle.com/art_otn_cbo.htm

Using Statspack to solve Performance Problems Reactively

Statspack is a performance diagnosis tool, available with Oracle8i Release 8.1.6. Statspack can
be considered BSTAT/ESTAT's successor, incorporating many new features, such as:
• Storing performance data in permanent Oracle tables which can be used for analysis at any
time in the future
• Capturing high-resource SQL statements
• An easy to use report, with many useful ratios pre-computed
• The ability to be run on all nodes of an OPS system

Refer: http://www.oracle.com/technology/deploy/performance/pdf/statspack_tuning_otn_new.pdf

RMAN Backup & Recovery

Oracle Recovery Manager (RMAN) utility is used for backing-up, restoring and recovering Oracle Databases. In this article I will discuss the frequently used RMAN commands that will make your life easy as a database administrator.

Refer: http://www.articles.freemegazone.com/rman-recovery.php

Easy, Quick and Safe Oracle patching Secrets

http://www.articles.freemegazone.com/oracle-patching-secrets.php

Tips for Database hang

http://www.articles.freemegazone.com/hang-databases-tips.php

A Guideline for Oracle Instantiation with RMAN!

http://www.articles .freemegazone. com/oracle- instantiation- RMAN.php

Step-By-Step Installation of RAC with RAW Datafiles on Windows 2000

http://www.eygle.com/ha/step-by-step.install.rac.with.raw.on.windows2000.htm

Oracle® Database Recovery Manager Quick Start Guide

http://download-east.oracle.com/docs/cd/B14117_01/server.101/b10769/toc.htm

Resposibilities of Oracle DBA

http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96521/dba.htm

Faster incremental backup

Visit:- http://www.database-articles.jobzmela.com/oracle-faster-incremental-backups.php

Faster Database Cloning ..!!!

Article URL:http://www.articles.freemegazone.com/oracle-database-cloning.phpPublication Code: 9295XPublisher: Articles.FreeMegaZo neArticle
Title: Step by Step Guide for Super fast Database Cloning!!

Evaluating Oracle index access methods

Oracle9i offers a variety of indexing methods including b-tree, bitmapped, and function-based indexes. Regardless of the index structure, an Oracle index can be thought of as a pair bond of a symbolic key, paired with a ROWID.
The goal of Oracle index access is to gather the ROWIDs required to quickly retrieve the desired rows from the table. Within Oracle, we see the following types of index access.
Index Range Scan
The index range scan is one of the most common access methods. During an index range scan, Oracle accesses adjacent index entries and then uses the ROWID values in the index to retrieve the table rows.
An example of an index range scan would be the following query.
select employee_namefrom employeewhere home_city = ‘Rocky Ford’;
In practice, many Oracle SQL tuning professionals will resequence the table rows into the same physical order as the primary index. This technique can reduce disk I/O on index range scans by several orders of magnitude. For details, see "Turning the Tables on Disk I/O" in the January/February 2000 issue of Oracle Magazine online.
Fast Full-index Scan
Index full scans are sometimes called fast full-index scans, which were introduced in Oracle 7.3. There are some SQL queries that can be resolved by reading the index without touching the table data. For example, the following query does not need to access the table rows, and the index alone can satisfy the query.
select distinct color, count(*)from automobilesgroup by color;
Oracle enhanced the fast full-index scan to make it behave similar to a full-table scan. Just as Oracle has implemented the initialization parameter db_file_multiblock_read_count for full-table scans, Oracle allows this parameter to take effect when retrieving rows for a fast full-index scan. Since the whole index is accessed, Oracle allows multi-block reads.
There is a huge benefit to not reading the table rows, but there are some requirements for Oracle to invoke the fast full-index scan.
All of the columns required must be specified in the index. That is, all columns in the select and where clauses must exist in the index.
The query returns more than 10 percent of the rows within the index. This 10 percent figure depends on the degree of multi-block reads and the degree of parallelism.
You are counting the number of rows in a table that meet a specific criterion. The fast full-index scan is almost always used for count(*) operations.
You can also force a fast full-index scan by specifying the index_ffs hint, and this is commonly combined with the parallel_index hint to improve performance. For example, the following query forces the use of a fast full-index scan with parallelism:
select distinct /*+ index_ffs(c,pk_auto) parallel_index(automobile, pk_auto) color, count(*)from automobilesgroup by color;
It is not always intuitive whether a fast full-index scan is the fastest way to service a query, because of all of the variables involved. Hence, most expert SQL tuners will time any query that meets the fast full-index scan criteria and see if the response time improves.
If you like Oracle tuning, you might enjoy my latest book “Creating a Self-tuning Oracle Database” by Rampant TechPress. It’s only $9.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:
Not all Indexes are used in Range Scans
When deciding to segregate indexes into larger blocksizes it is important to understand that those indexes that are subject to frequent index range scans XE "index range scans" and fast-full scans will benefit the most from a larger blocksize.

When Oracle joins two tables together with a nested loop, only one of the indexes may be accessed as a range. The optimizer always performs an index range scan on one index, gathers the rowid values, and does fetch by rowid on the matching rows in the other table. For example:

select
customer_name,
order_date
from
customer
orders
where
customer.cust_key = orders.cust_key;

The Oracle documentation notes “In a nested loop join, for every row in the outer row set, the inner row set is accessed to find all the matching rows to join. Therefore, in a nested loop join, the inner row set is accessed as many times as the number of rows in the outer row set.”

Oracle will only scan one index, build a set of keys, and then probe the rows from the other table (Figure 13.1).
So, if this nested loop never uses the customer index, why is it there? The answer is, for index unique scans. In an index unique scan, a single row is accessed within the index, as seen in this query:

select
customer_last_name,
customer_address
from
customer
where
cust_key = 123;

In sum, the DBA must find out “how” their indexes are being used by the SQL. Remember, an index that never experiences range scans would not benefit from a larger blocksize. The question becomes one of finding those indexes that experience lots of range scans, and AWR can help.

It is possible to identify those indexes with the most index range scans XE "index range scans" with the following simple AWR script.