I have recently setup a 2 node Rac on oracle linux 5.4 with oracel 11gR2, the installation went smoothly and all the cluster resources are up and running however the data is not syncing across the nodes, when I create a table it shows up on the other node but when I insert rows into the tables they don't show up on the other node and when I restart the cluster the inserted rows are completely gone even from the node where I inserted them.
Can you please help me identify the problem, how can I fix it ?
Thank you very much for your help in advance.
what are you using for SHARED storage? What you describe is impossible with correctly installed RAC environment. Are you sure you are connecting to the correct database?
SHARED storage means that ALL nodes can read and write to the same disk device paths concurrently.
Sounds like you are not committing transactions. Do you understand how transaction isolation and MVCC work in Oracle?
RAC is a single database. Not a database per server. So it is not possible to insert and commit rows via one server, and not see that in the same database, via another server.
Thanks for your replies.
You're right I was not committing the inserts and that was the problem. I am new to Oracle and didn't know that you are required to commit inserts in Oracle even when you are not issuing a begin command.
Thanks for your help.
Oracle is different in this respect than SQL-Server and other databases. There is no lock escalation in Oracle. This concept does not exist in Oracle - as a billion row locks have the same overheads as a single row lock. None.
Read consistency also differs. Dirty reads do not exist.
A reader of data can never block a writer of data. And vice versa - a writer of data can never block a reader of data. Oracle implements a concept known as multi-version concurrency control. Different versions of data can exist simultaneously.
For example, you open a SQL cursor "+select * from some_table+". There are a 1000 rows. You have fetched a 100 rows. I delete the entire table and commit. I've created a new version of the truth. Your process however still will fetch 900 rows as a 1000 rows where the truth at the time you have created that read consistent SQL cursor.
There are fundamental concepts that differ between Oracle and most other databases. Concepts that makes Oracle the most advanced database product on this planet, and the reason for its success in the market place.
Not understanding these concepts will be a fundamental mistake on your part.
Oracle RAC adds a bunch of new concepts and features too. Do not underestimate its complexity - and ability to out perform and out scale any other similar RDBMS product in the world.