Introduction to Oracle Real Application Clusters

What is Oracle Real Application Clusters or RAC?

Oracle RAC is a cluster database with a shared cache architecture that overcomes the limitations of traditional shared-nothing and shared-disk approaches to provide highly scalable and available database solutions for business applications.

http://www.oracle.com/technology/products/database/clustering/index.html

Evolution of RAC?

The evolution of RAC can be tracked back from Oracle 7, where it was called Oracle Parallel Server (OPS). But it was in Oracle 9i that RAC was introduced. It was not merely a name change from Oracle 7 to Oracle 9i. Oracle has changed almost everything. RAC supports even direct sharing of Oracle database blocks between two or more database instances. This sharing is the major change that can be tracked from OPS. In OPS blocks where not directly shared leaving one instance notifies another instance of a required database block and wait till the other instance hand overs the same after it has been written its content to the disk. This wait is totally absent in the new RAC design.

What is OPS and its architecture?

Oracle has had the functionality to handle parallel servers since Version 7 with the help of OPS. OPS is a technique whereby multiple Oracle database instances share a single set of database files. In the architecture of OPS only a single instance was allowed to modify a block at any given point of time. An instance in order to write to a block uses a technique called block pinging.
Each OPS instance creates locks in their own distributed lock area to manage consistency between multiple instances, which are called Parallel Cache Management (PCM) locks.

The responsibility of a PCM lock is to ensure that the instance reading a block gets a consistent image of the data in the block. An instance must acquire a PCM lock on a block before reading/modifying data in the block. When a block is requesting for a block and that block is currently being locked by another instance, then the holding instance must write the block back to disk before the requesting instance can read or edit the block. This way consistency is maintained.

Thus OPS would allow multiple Oracle instances to use same database, which will allow scalability to expand by simple addition of a server. Performance issues can be solved easily with this kind of architecture. But the performance of OPS was not up to the mark. It even required application level changes, functional data partitioning to prove it works.

What does RAC offer other than OPS?

With the advent of high speed interconnectivity Oracle now uses memory to memory transfer of data blocks at high speed (thanks to technology) which completely rubs out the use of disk as a transfer mechanism.

The major benefits of RAC are many, like Scalability, High availability, Transparency. Scalability is achieved through introduction of multiple RAC nodes to increase performance. High availability is meant by the process of one node talking over the load of other when it fails. By Transparency, Oracle server provides full transparency to underlying mechanisms from applications.

PL SQL ERROR 201: SRW.MESSAGE must be declared

If you are unable to compile your reports or plls in Oracle 10g Version, due to the error like

PL SQL ERROR 201: SRW.___ must be declared, then check the existence of the following file and its privilege:

rwrun.jar in /Oracle10gAS/reports/jlib folder.

Where Oracle10gAS is the folder where 10g Application server is installed.

You will normally get this error only in UNIX/Linux flavors, because this is related to privilege rights. If you are getting this error in Windows also, then it is quietly possible that the file is missing.

The file must have the privilege 755 or -rwx-r-x-r-x.

Query to get record count of all tables in a schema

Use this query to get the record count of all tables in a schema.

select
table_name,
to_number(
extractvalue(
xmltype(
dbms_xmlgen.getxml('select count(*) c from '||table_name)
),'/ROWSET/ROW/C')) count
from user_tables order by 1

The output is like

table_name count
----------- --------
DEPT 4
EMP 14


Courtesy: http://laurentschneider.com/wordpress/2007/04/
how-do-i-store-the-counts-of-all-tables.html

How to escape character in LIKE clause

To escape certain characters such as _ which serve as wild card characters in a SQL Query, there is an extension to the LIKE clause ESCAPE.

The syntax is:

LIKE string ESCAPE character

Why use escape

We will take on an example to make clear its usage. We have a table namely TAB_100 which has only column FIELD_100, which is VARCHAR2(20). The example data in the table is as below.

Code:

FIELD_100 ------------ sample_data sample data not applicable not_applicable

Now if we want to select the rows which have the underscores. What command will you normally issue. It would be naturally the first time like below:

Code:

select FIELD_100 from TABLE_100 where FIELD_100 like '%_%';

But you will be surprised to see the results:

Code:

FIELD_100 ------------ sample_data sample data not applicable not_applicable

Why did this happen? For experienced Oracle campaigners it is well known that _ is a wild card character. That is _ stands for any character. So your query yielded all the rows, which you did not want.

Now we modify the query to get the desired output as below:

Code:

select FIELD_100 from TABLE_100 where FIELD_100 like '%#_%' escape '#';

Now the output is as below:

Code:

FIELD_100 ------------ sample_data not_applicable

This is what you exactly wanted. There is no restriction to only use # character. You can use any character, including _ character.

How to create multi-row output for a Comma seperated value - Addendum

Use this query to convert any length string in comma separated format to rows:

SELECT
SUBSTR('AA,BBB,C,D,E,F,G,H',
INSTR(CHR(44)||'AA,BBB,C,D,E,F,G,H'||
CHR(44),',',1,LEVEL),
INSTR('AA,BBB,C,D,E,F,G,H'||CHR(44),',',1,LEVEL)-
INSTR(CHR(44)||'AA,BBB,C,D,E,F,G,H'||
CHR(44),',',1,LEVEL))
FROM DUAL
CONNECT BY LEVEL <= LENGTH('AA,BBB,C,D,E,F,G,H')-
LENGTH(REPLACE('AA,BBB,C,D,E,F,G,H',','))+1

How to create multi-row output for a Comma seperated value

Say for example we have a string 'A,B,C,D,E,F'. We would like to have it printed in separate lines say

A
B
C
D
E
F

Like so.

Now we can use a simple SQL statement to convert the same.

SELECT SUBSTR('A,B,C,D,E,F', INSTR('A,B,C,D,E,F',',',1, LEVEL)-1,1) FROM DUAL CONNECT BY LEVEL <= LENGTH( REPLACE('A,B,C,D,E,F',','))

To elaborate the technique, we have used the following logic:

1. Used CONNECT BY statement to find out how many rows are necessary
2. INSTR to find out the place of each row
3. SUBSTR to cut the string between the comma

Well what was the assumptions before using this query. The string between comma are having length of one. Any number of strings can be given in this fashion.