Why On Earth This Happens?

A developer asked me to create a database link from one database to other. Database link is used to connect two databases in Oracle, so that you can share data. The user in one database (i.e prod) was ss1 and its password was ss1. The user in the other database (i.e. ias) was ss2 and its password was ss2. The prod is our production database and ias is the repository for application server. First I made an entry in tns file (a network configuration file for the connectivity to Oracle databases), and connected to ias db and the ss1 got connected to ss2. But when I created database link, things just got ugly and no connection was made.

I spared no option as far as I knew. I changed the net service name, I changed the host name, I changed the domain name, every thing. But Na Jee. After smashing my head every where, then I logged onto my local db and then created a db link and it worked!!!!!

It was a problem with tns file. The prod db was using the tns file of server and I was making changes to local tns. The funny thing is that SQL*Plus was working ok with local tns, but when it came to db link , it was using severs' tns.

So my dears , moral of the story is that always keep the servers configuration files like tnsnames and sqlnetora in mind, whenever connecting objects of database server to some other database servers or for any servers for that matter.

Oracle Server Architecture in Simple Words

In order to efficiently and intelligently manage an Oracle database, you have to have a sound knowledge of its underlying architectural details. This knowledge pays off because it is directly linked with DBA's job. The real job of DBA starts after the installation of database, when he embarks on to setting-up a database and then maintaining it. Naturally, the configuration and maintenance of database cannot be done by a DBA unless he doesn't know about the ins and outs of its architecture. The following section provides a short overview of Oracle architecture.

The Oracle Server can be divided into a physical and logical portion. The physical portion is Oracle database, which resides on disks and logical portion is Instance, which resides in memory. Instance is basically a mean to access the database.

Oracle Instance: In order to access the database Oracle Server initiates some background processes and allocates some shared memory. These background processes and shared memory structures made up of Oracle Instance. The properties of instance (i.e. the properties of background processes and memory structures) are present in the parameter file in the form of parameters having some values. Oracle Server needs these parameters to create an instance while in startup stage.

Shared Memory Structures: The shared memory structure of instance is called as Shared Global Area (SGA). Basically, it contains the most frequently data and code, along with some control information. SGA divides its work into subcomponents.

Database Buffer Cache: It is commonly called as 'Buffer Cache'. Buffer Cache stores the most recent and frequent data. Any data read from the database, first comes to buffer cache and from it, multiple user can access it.

Redo Log Buffer: It stores the changes, which are made to the data and is only used for recovery purposes.

Shared Pool: It is that part of SGA, which stores most frequent and recent SQL, PL/SQL code along with data dictionary information. The code part (SQL, PL/SQL) is stored library cache of shared pool and data about schema objects is stored in data dictionary cache of shared pool.

Large Pool: It is an optional area and used to facilitate large jobs.

Java Pool: It is also an optional area used to facilitate Java code.

Stream Pool: It is also an optional area, used in case of Oracle Streams.

Background Processes: The Background processes are used to facilitate the working of instance with respect to database. Background processes are responsible for the efficient interaction of memory structures of instance and database. Some background processes are always there and some are added up as you configure more features of database. Some common background processes are:

Database Writer: It takes data from buffer cache and writes it to data files.

Log Writer: It takes information from redo log buffer and writes it to online redo logs on disk.

Checkpoint: It ensures that at continuous intervals, all modified data from buffer cache is written to data files.

System Monitor: It performs crash recovery and some space management.

Process Monitor: It performs process recovery, in case of failure of user process.

Archiver: It makes offline copies of online redo logs and helps to recover from media failure.

Private Memory Area: Besides of SGA, there is a private memory structure called Program Global Area (PGA). It contains session information for a single server process. A server process is a foreground or shadow process which acts on behalf of one or more users. If a database is in dedicated mode, then for each user there would be a separate server process, and if a database is in shared mode, then there would be some server process which would be servicing all user processes between them.

Some Features of Oracle I Like More

Oracle stands out from the crowd of numerous other databases due to its sheer array of outstanding features, some of them are discussed below:

Concurrency:
Oracle allows simultaneous access to same data by multiple users without any locking issues. It is done through Oracle powerful transaction mechanism.

Read Consistency:
It means that while accessing the data, the reader of same data never block other reader and writer. The writers don't block the readers of same data. Only writer of same data block the other writers.

Portability:
Oracle has got the ability to operate on all major platforms. It can be transferred from one platform to other with little or no effort at all.

Self Managing Database:
To ease the burden of DBA and make him more productive, Oracle 10g has automated many things like space and memory management, undo management and backup/recovery etc.

High Availability:
Being an enterprise level database, Oracle understands the need of enterprise across the world regarding 24/07/365 availability. It tackles this issue with Real Application Cluster (RAC), RMAN, Data Guard, Oracle Flash Back, Redo Mechanism etc.

Unicode Support:
The rapidly shrinking world has introduced many new challenges. One of them is the support of multi-lingual support. In order to represent the multiple language, a global character set has been developed, which is Unicode. The latest release of Unicode is its version 4, in which it can support 1,226 new characters. Oracle's Unicode character sets, AL32UTF8 and AL16UTF16, have been updated to support Unicode 4.0 in Oracle Database 10g Release 2. The Globalization Development Kit (GDK) is also included in Oracle 10g with enhanced features.


Miscellaneous Features:
Moreover, Oracle throws its shadows on various areas. Its scope ranges from Data mining to Data Warehousing, from OLAP to BI, from Multimedia to Spatial, from mid-tier software to front end developer suites and much more.

First Confusion of a Beginner DBA

The first confusion a beginner DBA encounter is what exactly Oracle is? Is it the name of Company or is it the name of product? Is it the name of database or is it the name of database management system? In order to nullify these confusions, lets start with some definitions:

Database:
A database is an informational unit. It is used to store data for future use. Database enables to store and retrieve data in an efficient and flexible way.

Database management system:
It is software by which a database is used. DBMS enables the storage, manipulation and retrieval of data from the database.

Oracle Server:
Oracle Server is the flagship product of Oracle Corporation. Oracle Server is the DBMS for Oracle Database. The word Oracle is used to describe both Oracle Server and Oracle database. It depends on the context in which the term Oracle is being used.

ORA-00600 and ORA-07445

ORA-00600 and ORA-07445 errors are the most esoteric erros in Oracle.

There is a tool called "ORA-600/ORA-7445 Troubleshooter" available at Metalink. It asks for the first argument of ORA-600 error with an optional database version number.

For example, to see the description of the error:

ORA-00600: internal error code, arguments: [723], [25908], [25908], [memory leak]

You enter 723 in the "ORA-600 First Argument" field. The first argument is a pointer to where in the code the error was caught and thus is the key information in identifying the problem.

You can also embed (copy/paste) the "Call Stack Trace" there, and, when you click on the "Search Call Stack" button an advanced MetaLink search is performed to find documents containing relevant sections from the call stack.

Call Stack extracts from the following files are supported:

Generic foreground and background server trace files located in background_dump_dest and user_dump_dest
OpenVMS NETV2 and BEQ log files located in ORA_ROOT:[NETWORK.LOG]
WINDOWS CORE.LOG files
GDB (debugger) backtrace call stacks (best endeavors)

Oracle's Venerable SQL operators

We are gonna discuss the UNION,UNION ALL, INTERSECT and MINUS operators in SQL of Oracle.

To exemplify these operators, create two tables, 'A' and 'B',

create table A (C1 NUMBER(12),C2 VARCHAR2(50),C3 NUMBER(12));

INSERT INTO A VALUES (1,'AAA',100);
INSERT INTO A VALUES (1,'AAB',100);
INSERT INTO A VALUES (1,'AAC',100);


create table B (C1 NUMBER(12),C2 VARCHAR2(50),C3 NUMBER(12));

INSERT INTO B VALUES (1,'AAE',100);
INSERT INTO B VALUES (1,'AAB',100);
INSERT INTO B VALUES (1,'AAD',100);


Union:

If we apply 'UNION' operator on two tables, then it returns all rows from both tables but show the duplicate records only once.

SQL> SELECT * FROM A
2 UNION
3 SELECT * FROM B;

C1 C2 C3
---------- ---- ----------
1 AAA 100
1 AAB 100
1 AAC 100
1 AAD 100
1 AAE 100

5 rows selected.

Union All:

If we apply 'UNION ALL' operator on two tables, then it returns all rows from both tables including the duplicate rows.

SQL> SELECT * FROM A
2 UNION ALL
3 SELECT * FROM B;

C1 C2 C3
---------- ---- ----------
1 AAB 100
1 AAA 100
1 AAC 100
1 AAE 100
1 AAB 100
1 AAD 100

6 rows selected.


Intersect:

If we apply 'INTERSECT' operator on two tables, then it returns only the
same rows in both tables.


SQL> SELECT * FROM A
2 INTERSECT
3 SELECT * FROM B;

C1 C2 C3
---------- ---- ----------
1 AAB 100


1 row selected.

Minus:

If we apply 'MINUS' operator on two tables, then it returns rows which are present in first table but not in the second table.

SQL> SELECT * FROM A
2 MINUS
3 SELECT * FROM B;

C1 C2 C3
---------- ---- ----------
1 AAA 100
1 AAC 100

Merging With Oracle

A common need arises when you have to perform a combination of insert and update operation on a single table with respect to another table. Oracle provides a 'Merge' statement for this purpose. Its a DML operation.

The following example best descirbes the concept. We have two tables emp and bonuses. We want to perform a merge operation on bonuses and emp table. We want that if we match emp and bonuses table, then if a record matches in both then the record in bonuses table gets updated and if there is no match then the record from emp table gets inserted into bonus table.

SQL> create table emp (empid number, name varchar2(30));

Table created.

SQL> insert into emp values (1,'Fahd');

1 row created.

SQL> insert into emp values (2,'DAN');

1 row created.

SQL> insert into emp values (3,'TOM');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM EMP;

EMPID NAME
---------- ------------------------------
1 Fahd
2 DAN
3 TOM

SQL> CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);

Table created.

SQL> INSERT INTO BONUSES(employee_id) (select empid from emp);

3 rows created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM bonuses;

EMPLOYEE_ID BONUS
----------- ----------
1 100
2 100
3 100

SQL> insert into emp values (4,'TOW');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM EMP;

EMPID NAME
---------- ------------------------------
1 Fahd
2 DAN
3 TOM
4 TOW

SQL> MERGE INTO BONUSES B
2 USING
3 (SELECT EMPID FROM EMP) E
4 ON (B.EMPLOYEE_ID=E.EMPID)
5 WHEN MATCHED THEN
6 UPDATE SET B.BONUS=B.BONUS+100
7 WHEN NOT MATCHED THEN
8 INSERT (B.EMPLOYEE_ID,B.BONUS) VALUES (E.EMPID,200);

4 rows merged.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM BONUSES ;

EMPLOYEE_ID BONUS
----------- ----------
1 200
2 200
3 200
4 200

Lets dissect the above merge statement line by line,

1 MERGE INTO BONUSES B

This statement tells which table is being merged, in this case its Bonuses table with alias B.

2 USING
3 (SELECT EMPID FROM EMP) E

The 'Using' statement tells us about the table from which the comparison will be made. in this case its EMP table, and we have selected empid from this table.

4 ON (B.EMPLOYEE_ID=E.EMPID)

The 'ON' statement is the filter which decides whether insert or update will be performed. In the 'ON' statement we are comparing table-to-be-merged (i.e. Bonuses) and the table from which the comparison is being made.

5 WHEN MATCHED THEN
6 UPDATE SET B.BONUS=B.BONUS+100

If the condition is true in the 'On' statement on line 4, then the update on table Bonuses will be made.

7 WHEN NOT MATCHED THEN
8 INSERT (B.EMPLOYEE_ID,B.BONUS) VALUES (E.EMPID,200);

If the condition is false in the 'On statement on line 4, then the record from emp table will be inserted in bonuses table.

Passionate Virtues By Kathy Sierra

If you are passionate about some cause, you may want to re-invigorate your passion from the virtues described by Kathy Sierra in her blog at the following link:

http://headrush.typepad.com/creating_passionate_users/2007/03/seven_blog_virt.html

Where SQL Sings

Myths are often associated with the problems, which are hard to understand and even harder to solve. One of the most esoteric problems in the realm of performance tuning of Oracle is SQL tuning. After much ado, everyone seems to agree upon the fact that 90% tuning problems can be rectified by tuning the SQL. Most also tend to agree that tuning SQL shouldnt be an afterthought. SQL Tuning begins with the first select keyword to the last semicolon. But for some weird mythical reasons most of us seem unable to do it right the first time. Hence we have to resort to reactive SQL Tuning.

In a given ailing application, its rather easy to identify the badly-performing SQL, its also easy to get execution plan of the culprit SQL. Execution plan is the way which a query follows to get data. The choice of execution plan is directly proportional to the performance of query. The more optimal the plan, the more the query performs well. For any given query there could be literally billions of different execution plan, the real caveat is the finding of best optimal plan. To find best optimal plan from billions of plans of the same query is real nasty.

But there is a place in the virtual world, called as 'www.singingsql.com' and a book in the physical world called as 'SQL Tuning' which doesn't think its nasty to find the best optimal execution plan. Dan Tow is the man who is behind these two oracles for SQL tuning. He has remained with Oracle corporation for a considerable time dealing with issues related to SQL tuning, and nowadays provides consultancy in the same area. He also publishes an occasional newsletter describing gems of his research. In his outstanding book 'SQL Tuning' published by O'reilly, he tells very lucidly how to find the best optimal in a very logical way. His method comprises of delineating a query in tree-like graphical diagram, which step-by-step takes us to the best plan possible for that query, without falling in any painful rigmarole. Though I use his method of SQL tuning in an Oracle environment, his method is database-independent. You can use it for any SQL written for any database. His book gives the examples relating to Oracle, SQLSERVER and DB2.

Dan's Diagrams has made my SQLs' sing more than often. I fell in love with it, when it reduces my montly pay process from 4 hours to 12 minutes. Unbelievable, isnt it. But thats how it works. For me its easy, sensible, logical and most importantly workable.

TNS-12571:PACKET WRITER FAILURE

This is one of those errors which are really pain in the tooth. I encountered it yesterday and it ate up lots of my hours. To add insult to injury, the damned error appeared on the PC of department head. So I coudnt evade or postponed it.After trying virtually everything in my repertoire regarding Oracle net configuration debugging, I re-installed the software, but the error persisted. Then I started to look outside Oracle. I again pondered upon the wording of error and it was implying something wrong with network packet transportation. Just, beleive me, just through pure intuition, I thought perhaps the packets were blocking due to some checks and barriers. The most obvious barriers to the machines are antivirus programs. Zone Alarm antivirus was there. I uninstalled it and guess what? Yes it worked this time.

History of Oracle

History is the basis on which civilizations evolve. It tells us that from where we have come and to where we are going. It's the yard-stick through which the progress of an era is measured. Oracle has also got a rich historical background which helps in understanding the evolution of Oracle in particular and databases in general.

According to the Greek mythology, Appollo, the god of fine arts and reflections, chose city of Delphi on Mount Parnassus, for his prime place of worship. To help his followers in the matters ranging from public policies to personal affairs, he selected an older woman Pythia. Appollo spoke through Pythia in the form of meaningless cries, which were interpreted by the priests. She was called as Oracle. Oracle was the ultimate source of wisdom.According to the American legend, Oracle was the name of project sponsored by Central Intelligence Agency (CIA). The project vanished but Lary Ellison and Bob Miner materialized it in the form of Relational Software In-corporation (RSI) in 1977.

The RSI Company started its work on the basis of research done by Codd in early 70's. The Codd's work stressed on the use of relational model for databases instead of network or hierarchical model. RSI went to build its flagship product Oracle according to this relational model. Oracle version 1, written in Assembly language and running in PDP-11, was never marketed.

In 1980, Oracle version 2 was released and with it RSI became the first ever company to release a commercial relational database using SQL. 1982 version 3 was released, written in C and the major feature was transaction processing .In 1983, RSI changed its name to Oracle Corporation. In 1984, Version 4 was released; its main feature was read consistency. In 1986, Version 5 was released; its major feature was a true client/server database. In 1988 Version 6 was released and its prime features included PL/SQL language. In1989, Version 6 was commercialized; whose main feature was Oracle Parallel Server.

In 1992, Oracle released its version 7 for UNIX. In 1994, there came out the version 7.1. In 1997, Oracle 8 was released; its prime features included more scalability and object relational features. In 1999, Oracle 8i came out equipped with internet technologies. In 2000, Oracle 8i Release 2 with Oracle 9i Application Server (AS) was released. In 2001, Oracle 9i R1 wade made public.

Its major features include Real Application Cluster (RAC) and analytical functions. In 2002, Oracle 9i R2 got released. In 2004, Oracle 10g was released, the first ever database to support grid infrastructure. In 2005, Oracle 10g R2 was commercialized for major platforms. Now the 11g version of Oracle is in offing.

Blog of Oracle Server

Oracle is certainly an ancient thing. Oracle is blogging even before Mark Rittman thought about it. Alert Log file is the blog of Oracle. Oracle jots down here its musings,rants,experiences and complaints tirelessly.The location of this blog of Oracle is told to Oracle with the help of BACKGROUND_DUMP_DEST parameter in the init.ora file, which is the parameter file for the instance.

In its blog, Oracle marks its postings with a timestamp. Its postings are precise, un-baised, un-political,brief and un-passionate. You can't shut it up, neither you could control it. It will write what it feels. In its blog, Oracle writes about dreaded Ora-600 errors, about rare yet redoubtable Ora-1578 and DBA favourite Ora-60 errors. The Ora-60 errors are related to dead locks which is more than often a fault of developers, what better a DBA could ask for at the start of the day then to have Ora-60 errors in Oracle's blog.moreover, Oracle blogs about the sheer diligence of DBA when he atlast issues some administrative statements like create, alter and drop.

Oracle also blogs about when its blog went down (i.e. shutdown) and when it came up (i.e. startup). Oracle didnt get tired by posting about when it did the log switch and hence saved the world by checkpointing everything.Whenever the work horses of Oracle (i.e. server processes) gets tired and misbehave, Oracle blogs about this rarity leisurly. If any smart ass has given a go at materialized view and fumbled with its auto refresh, Oracle doesnt spare him in its blog.

Oracle also consider it important enough to blog the occurrance of non-default values of parameters at startup.This blog is read by countless numbers of Database Administrators (DBAs) around the globe. It can make or break their day. If the current postings of Oracle are happy enties then the DBA is happy and the database users are happy and if there are any black spots like Ora-00600, then the developer should beware of Evil DBA from Hell.