Search This Blog

Wednesday, March 28, 2012

Oracle GoldenGate DDL Replication.

This post is built upon the previous post on Oracle GoldenGate DML replication.

Points to consider before implementing DDl replication.


1) Recyclebin feature should be turned off(Oracle 10g onwards).
Read more >>

Monday, March 26, 2012

Explain Plan Using Statement ID.

SQL>explain plan set statement_id = 'TEST' FOR select * from scott.EMP;

Explained.


SQL>@explain TEST





PLAN                      OBJECT_NAME    OBJECT_TYPE  BYTES    COST     PARTITION_START      PARTITION_STOP
---------------------     ------------   ------------ -------- -------- -------------------- --------------
Select Statement                                      1218        3
  1.1 Table Access (Full) EMP            TABLE        1218        3




Read more >>

Gathering Oracle Database Statistics Using DBMS_STATS.

Gathering Table Statistics.
Check the table for when it was last analyzed.

SQL> select last_analyzed, sample_size from dba_tables where owner='SCOTT' and
     table_name='EMP';

LAST_ANAL SAMPLE_SIZE
--------- -----------
19-MAR-12          14

Read more >>

Friday, March 16, 2012

Oracle GoldenGate Basic DML Replication.

Schema to be replicated "SCOTT".

SOURCE DATABASE.

OS - Red Hat Enterprise Linux Server release 5.4 

Hostname - canada

IP Address - 192.168.1.100

Database - Oracle 11g Release 2.

SID -  orcl

GoldenGate Version - 11.1.1.1.2

Sample "/etc/hosts" file.

# Do not remove the following line, or various programs
# that require network functionality will fail.

127.0.0.1                localhost.localdomain localhost

192.168.1.100   canada  canada
192.168.1.200   london  london

Read more >>

Oracle GoldenGate Basic Architecture.

Source Database - GoldenGate extracts the information from the source database. The source database may be from any of the following vendors. - Oracle Database - TimesTen - MySQL - IBM DB2 - Microsoft SQL Server - Teradata - Sybase - Enscribe - SQL/MX
Read more >>

Monday, March 12, 2012

Tools & commands for analyzing server performance.

To check for disk space.

[root@london ~]# df -h

Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2              26G   16G  8.3G  66% /
/dev/sda1              99M   12M   83M  12% /boot
tmpfs                 742M     0  742M   0% /dev/shm

Read more >>

Thursday, March 8, 2012

Oracle RAC Instance Recovery.

1. All nodes available.

2. One or more RAC instances fail.

3. Node failure is detected by any one of the remaining instances.

4. Global Resource Directory(GRD) is reconfigured and distributed among 
   surviving nodes.
Read more >>

How to kill sessions in oracle database.


Execute the following script to list all sessions running in your database.
SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45
Read more >>

Oracle Database 11g Release 2 (11.2) Installation On RedHat Enterprise Linux 5.

This post assumes that you have already installed RHEL5.(Cento OS 5 or Oracle Linux 5 can also be
used for this purpose.)

This installation is done with 2 Gb of swap space.

SELINUX and Firewall should be disabled on the server.

Server IP Address : 192.168.1.100
 
Read more >>

Friday, March 2, 2012

Split Brain Syndrome and I/O Fencing (RAC).

Split brain syndrome occurs when the instances in a RAC fails to connect or ping
to each other via the private interconnect, Although the servers are physically up
and running and the database instances on these servers is also running.

The individual nodes are running fine and can accept user connections and work
independently.
Read more >>

Oracle Clusterware Components.

Oracle clusterware is a software which facilitates multiple nodes to communicate
with each other and make them behave as a single Server.

Oracle clusterware is run by Cluster Ready Services(CRS) which in turn is dependent
on two components.

a) Oracle Cluster Registry(OCR)
b) Voting Disk
Read more >>

RAC Background Processes.

Lock Monitor(LMON) Process.

LMON is responsible for monitoring all instances in a cluster
for the detection of failed instances.

Once a failed Instance is detected it facilitates in the 
recovery of global locks held by that instance.

It is also responsible for reconfiguration of locks and other
resources when instances leave or are added to the cluster.
This dynamic reconfiguration is done in realtime.

Read more >>

Cache Fusion Scenarios (RAC).

Block Resource Modes.


Resource Mode  Identifier  Description
Null           N           Nodes which are holding the blocks
                           at this level do not have any access
                           rights to the block.

Shared         S           This level indicates that the block
                           at this level is in shared mode or
                           read mode, Multiple instances have
                           read access to this block but cannot
                           modify it.

Exclusive      X           In this level the block is held in Exclusive
                           mode and other instances cannot write to it.

Read more >>