Gateway connectivity (hetrogeneous ) from oracle to non-oracle databases .

Here we are connecting oracle to netizza same way we can connect  oracle and mysql

Step1: Ensure Unix ODBC Driver Manager is version 2.2.11 or higher.

[oracle@myhost-x1 ~]$

odbcinst --version

unixODBC 2.2.14

to check how to install unixODBC  on linux you can check here 

http://adminoracle10g.blogspot.in/2013/03/how-to-install-unixodbc-on-linux-system.html

Step2: Define or append below environment variables in user’s .bash_profile : highlighted in  yellow

[oracle@myhost-x1 ~]$ cat .bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

        . ~/.bashrc

fi

################ START: Entries at the time of ODBC installation ###############################

# User specific environment and startup programs

export TMP=/d2/db1/oracle/temp

export TEMPDIR=/d2/db1/oracle/temp

export ORACLE_BASE=/d2/db1/oracle/apps

export ORACLE_HOME=$ORACLE_BASE/product/db_1/11.2.0

export ORACLE_SID=DB1

export LD_LIBRARY_PATH=$ORACLE_HOME/lib32:$ORACLE_HOME/lib:/usr/local/nz/lib:/usr/lib64

export LD_LIBRARY_PATH_32=$ORACLE_HOME/lib32

PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:/bin:/usr/lib64/:$ORACLE_HOME/OPatch:/usr/local/nz/bin

export NZ_ODBC_INI_PATH=/etc

export ODBCSYSINI=/etc

export ODBCINI=/etc/odbc.ini

export PATH

################  END: Entries at the time of ODBC installation ###############################

Step3:

• Go to the path $ NZ_ODBC_INI_PATH set above in .bash_profile (i.e cd $ NZ_ODBC_INI_PATH).
• Login through root .( sudo su -) and Create odbc.ini using vi editor vi /etc/odbc.ini for server to be connected, in which DSN is defined like below. Please point to the right library file for ODBC.

[oracle@myhost-x1 ~]$ cat /etc/odbc.ini

[ODBC Data Sources]

NZSQL = NetezzaSQL

[NZSQL]

Driver                = /usr/local/nz/lib64/libnzodbc.so

Description           = NetezzaSQL ODBC

Servername            = 10.10.30.101

Port                  = 5480

Database              = xyz

Username              = abc

Password              = mypassword

ReadOnly              = false

ShowSystemTables      = false

LegacySQLTables       = false

LoginTimeout          = 0

QueryTimeout          = 0

DateFormat            = 1

NumericAsChar         = false

SQLBitOneZero         = false

StripCRLF             = false

securityLevel         = preferredUnSecured

caCertFile            =

[ODBC]

IANAAppCodePage=4

InstallDir=/opt/odbc32v51

Trace=0

TraceDll=/opt/odbc32v51/lib/odbctrac.so

TraceFile=odbctrace.out

UseCursorLib=0

[oracle@myhost-x1 ~]$

Test note 1: Now open another PuTTY session and check you can connect to non- oracle database using ODBC driver

[oracle@myhost-x1 ~]$

 isql -v NZSQL

+---------------------------------------+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

+---------------------------------------+

SQL>

Till this point the ODBC setup is complete, you can go ahead at oracle end.

Test note 2:

[oracle@myhost-n1 gateway]$ echo $ORACLE_HOME

/d1/db1/oracle/apps/product/11.1.0/gateway

[oracle@myhost-n1 gateway]$ pwd

/d1/db1/oracle/apps/product/11.1.0/gateway

[oracle@myhost-n1 gateway]$ ./bin/dg4odbc

Oracle Corporation --- THURSDAY  APR 15 2010 06:37:17.144

Heterogeneous Agent Release 11.1.0.7.0 - 64bit Production  Built with

   Oracle Database Gateway for ODBC

Step 4: Prepare the 11g gateway heterogeneous instance. In our case we have kept instance name to DG4ODBC.

[oracle@myhost-n1 admin]$ pwd

/d1/db1/oracle/apps/product/11.1.0/gateway/hs/admin

[oracle@myhost-n1 admin]$ cat initDG4ODBC.ora

# This is a sample agent init file that contains the HS parameters that are

# needed for the Database Gateway for ODBC

HS_LANGUAGE=american_america.we8iso8859P1

#HS_FDS_TRACE_LEVEL = debug

HS_FDS_CONNECT_INFO = NZSQL

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

#

# ODBC specific environment variables

#

set ODBCINI = /home/oracle/nz/odbc.ini

#HS_FDS_CHARACTER_SEMANTICS=TRUE

#

# Environment variables required for the non-Oracle system

#

#set <envvar>=<value>

HS_FDS_SUPPORT_STATISTICS=FALSE

Step 5: Create  gateway listener as below. Take care in specifying ENVS attribute.

trace_level_LISTENER_DB1=0

SID_LIST_LISTENER_DB1 =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /d1/db1/oracle/apps/product/db_1/11.2.0)

      (PROGRAM = extproc)

    )

    (SID_DESC =

      (SID_NAME = DB1)

      (ORACLE_HOME = /d1/db1/oracle/apps/product/db_1/11.2.0)

    )

   (SID_DESC =

      (SID_NAME = DG4ODBC)

      (ORACLE_HOME =/d1/db1/oracle/apps/product/11.1.0/gateway)

      (ENVS = LD_LIBRARY_PATH=/usr/lib64/:/home/oracle/nz/lib:/d1/db1/oracle/apps/product/11.1.0/gateway/lib)

      (PROGRAM = dg4odbc)

    )

  )

LISTENER_DB1 =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP)(HOST = aaaa.com)(PORT = 1522))

      )

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

      )

    )

  )

Start this listener and proceed for remaining steps to be performed at 10g server only.

export ORACLE_HOME=:/d1/db1/oracle/apps/product/11.1.0/gateway

$ORACLE_HOME/bin/lsnrctl start LISTENER_DB1

Step 6: Create TNS entry for the 11g gateway instance( DG4ODBC in our case)

Note:      (HS=OK) should be after connect_data is closed

DG4ODBC =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = aaaa.com)(PORT = 1522))

    )

    (CONNECT_DATA =

      (SID = dg4odbc)

   )

      (HS=OK)

  )

Test note 3: now tnsping the gateway  listener from database  home to check request is reaching to gateway  listener from db  home. If ok then go ahead.

Step 7:  connect to  database user, from where connectivity to non-oracle database is desired and create db link as below:

Create database link NZ connect to abc  identified by “mypassword” using ‘DG4ODBC’;

Step 8: Check connectivity oracle to natizza

 select * from test@NZ                                                                                                     .                                                                                             

You should be able to see data fetched from non-oracle database

If have issue, diagnosis  logs can be seen from 11g home location: $ORACLE_HOME/hs/admin/log

You need to uncomment #HS_FDS_TRACE_LEVEL = debug  and HS_FDS_TRACE_LEVEL = 4;  restart 11g listener and query again.

General issues:

- Driver manager older version is some times. Installing newer versions help.

- Some problems need compilation of Driver manager source code with some options like taking flag for 64bit data etc.

- Some problems require fix at Non Oracle database software end.

- Some problems required change the ODBC driver software.

- Some problems are fixed by changing the HS parameter in the initInstance.ora file of gateway.

 Real issue example 

http://adminoracle10g.blogspot.in/2013/05/im004unixodbcdriver-managerdrivers.html

RECRATE CORRUPTED ORACLE INVENTORY

First let us  understand what is orainventoy where it exists and what it contains

oraInventory is repository (directory) which store oracle software products & their oracle_homes location on a machine.it's XML file .

There are basically two kind of Inventory Global Inventory (also called as Central Inventory) and Local Inventory also called as Oracle Home Inventory.

Global Inventory holds information about Oracle Products on a Machine. These products can be various oracle components like database, oracle application server, collaboration suite, soa suite, forms & reports or discoverer server . This global Inventory location will be determined by file oraInst.loc in /etc (on Linux) or /var/opt/oracle (solaris). If you want to see list of oracle products on machine check for file inventory.xml under ContentsXML in oraInventory (Please note if you have multiple global Inventory on machine check all oraInventory directories)

Local Inventory inside each Oracle Home is called as local Inventory or oracle_home Inventory. This Inventory holds information to that oracle_home only.

To determine where oraInventory is located

/var/opt/oracle/oraInst.loc or /etc/oraInst.loc depending upon the Platform.

# cat /etc/oraInst.loc
inventory_loc=/home/oracle/apps/oraInventory
inst_group=oinstall

you can see the oracle_home information in inventory.xml file

#cd /home/oracle/apps/oraInventory/ContentsXML

#cat inventory.xml
<?xml version="1.0" standalone="yes" ?>
<!-- Copyright (c) 1999, 2011, Oracle. All rights reserved. -->
<!-- Do not modify the contents of this file by hand. -->
<INVENTORY>
<VERSION_INFO>
   <SAVED_WITH>11.2.0.1.0</SAVED_WITH>
   <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME="OraDbhome" LOC="/home/oracle/apps/product/db1/11.2.0.3.0" TYPE="O" IDX="1"/>
</HOME_LIST>
<COMPOSITEHOME_LIST>
</COMPOSITEHOME_LIST>
</INVENTORY>

how to re-create the inventory

./runInstaller -silent -attachHome -invPtrLoc ./oraInst.loc
oracle_home="<oracle_home_location>"

SHMALL , SHMMAX , SEMMNI, SEMMNS, SEMMNS

Shared memory and semaphores are two important resources for an Oracle instance on Unix. An instance cannot start if it is unable to allocate what it needs.

Shared memory is exactly that - a memory region that can shared between different processes. Oracle uses shared memory for implementing the SGA, which needs to be visible to all database sessions.

Semaphores can be thought of as flags (hence their name, semaphores). They are either on or off. A process can turn on the flag or turn it off. If the flag is already on, processes who try to turn on the flag will sleep until the flag is off. Upon awakening, the process will reattempt to turn the flag on, possibly suceeding or possibly sleeping again. Such behaviour allows semaphores to be used in implementing a post-wait driver - a system where processes can wait for events (i.e. wait on turning on a semphore) and post events (i.e. turning of a semaphore). This mechanism is used by Oracle to maintain concurrency control over the SGA, since it is writeable by all processes attached.

ALLOCATION IN SIMPLE TERMS -

Shared memory required by the Oracle Instance : On instance startup, the first things that the instance does is: -Read the "init.ora" -Start the background processes -Allocate the shared memory and semphores required The size of the SGA will be calculated from various "init.ora" parameters. This will be the amount of shared memory required. The SGA is broken into 4 sections - the fixed portion, which is constant in size, the variable portion, which varies in size depending on "init.ora" parameters, the redo block buffer, which has its size controlled by log_buffers, and the db block buffer, which has its size controlled by db_block_buffers. The size of the SGA is the sum of the sizes of the 4 portions. There is unfortunately no simple ormula for determining the size of the variable portion.

Generally, the shared pool dominates all other parts of the variable portion, so as a rule of thumb, one can estimate the size as the value of shared_pool_size.

The number of semphores required is much simpler to determine.

Oracle will need exactly as many semaphores as the value of the processes "init.ora" parameter.

SHARED MEMORY ALLOCATION

1. One-segment

2. Contigous multi-segment

3. Non-contigous multi-segment

When attempting to allocate and attach shared memory for the SGA, it will attempt each one, in the above order, until one succeeds or raises an ORA error. On other, non-fatal, errors, Oracle simply cleans up and tries again using the next memory model. The entire SGA must fit into shared memory, so the total amount of shared memory allocated under any model will be equal of the size of the SGA(SGASIZE).

1. One-segment:- The one-segment model is the simplest and first model tried. In this model, the SGA resides in only one shared memory segment. Oracle attempts to allocate and attach one shared memory segement of size equal to total size of the SGA. However, if the SGASIZE is larger than the configured SHMMAX, this will obviously fail. In this case, the SGA will need to be placed in multiple shared memory segments, and Oracle proceeds to the next memory model for the SGA.

With multiple segments there are two possibilities. The segments can be attached contiguously, so that it appears to be one large shared memory segment, or non-contiguously, with gaps between the segments.

2. Contigous multi-segment - In the contiguous segment model, Oracle simply divides the SGA into SGASIZE/SHMMAX (rounded down) segments of size SHMMAX plus another segment of size SGASIZE modulo SHMMAX

3. Non- contigous multi-segment : Once the number of segments and their sizes is determined, Oracle then allocates and attaches the segments one at a time; first the fixed and variable portion segment(s), then the redo block buffer segment(s), then the db block buffer segment(s). They will be attached non-contiguously,

max Sga that can be created by the one segment model is SHMMAX*SHMSEG

You can display the current kernel parameters by doing a "sysdef -i"

SEMAPHORE ALLOCATION

Oracle just needs to allocate a number of semaphores equal to the processes parameter in "init.ora".

SEMMSL= # of semaphores in a semaphore set

SEMMNI= the maximum # of semaphores sets in the system

SEMMNS= the number of semaphores in the system.

SEMOPM= max number of operations per semop call = 100

SEMVMX = semaphore max value = 32767

When an Oracle instance is started, all required semaphores will be allocated. Semaphores are allocated in sets.

Since each oracle process* requires a semaphore, the number that is allocated is equal to the value of the init.ora parameter PROCESSES. The total # of semaphores required is the sum of all your instance's PROCESSES.

You can allocate all of your semaphores in one or more semaphore sets. If SEMMSL=PROCESSES, then only one semaphore set is required.

The maximum # of semaphores that can be allocated will be the lesser of (SEMMSL*SEMMNI) or SEMMNS.

If SEMMSL is not equal to PROCESSES, be sure that the total # of semaphores required (sum of PROCESSES) does not exceed the maximum (SEMMSL*SEMMNI, SEMMNS).

SHMMAX and SHMALL are two key shared memory parameters that directly impact’s the way by which Oracle creates an SGA. Shared memory is nothing but part of Unix IPC System (Inter Process Communication) maintained by kernel where multiple processes share a single chunk of memory to communicate with each other.

While trying to create an SGA during a database startup, Oracle chooses from one of the 3 memory management models a) one-segment or b) contiguous-multi segment or c) non-contiguous multi segment. Adoption of any of these models is dependent on the size of SGA and values defined for the shared memory parameters in the linux kernel, most importantly SHMMAX.

What’s the optimal value for SHMALL?

As SHMALL is the total size of Shard Memory Segments System wide, it should always be less than the Physical Memory on the System and should also be less than sum of SGA’s of all the oracle databases on the server. Once this value (sum of SGA’s) hit the limit, i.e. the value of shmall, then any attempt to start a new database (or even an existing database with a resized SGA) will result in an “out of memory” error (below). This is because there won’t be any more shared memory segments that Linux can allocate for SGA.

ORA-27102: out of memory

Linux-x86_64 Error: 28: No space left on device.

So above can happen for two reasons. Either the value of shmall is not set to an optimal value or you have reached the threshold on this server.

Setting the value for SHMALL to optimal is straight forward. All you want to know is how much “Physical Memory” (excluding Cache/Swap) you have on the system and how much of it should be set aside for Linux Kernel and to be dedicated to Oracle Databases.

Convert this 5GB to bytes and divide by page size. Remember SHMALL should be set in “pages” not “bytes”.

So here goes the calculation.

Determine Page Size first, can be done in two ways. In my case it’s 4096 and that’s the recommended and default in most cases which you can keep the same.

# getconf PAGE_SIZE

4096

or

silicon:~ # cat /proc/sys/kernel/shmmni

4096

Convert 5GB into bytes and divide by page size, I used the linux calc to do the math.

# echo "( 5 * 1024 * 1024 * 1024 ) / 4096 " | bc -l1310720.00000000000000000000

Reset shmall and load it dynamically into kernel

# echo "1310720" > /proc/sys/kernel/shmall

# sysctl –p Verify if the value has been taken into effect.

# sysctl -a | grep shmallKernel.shmall = 1310720

Another way to look this up is

# ipcs -lm

------ Shared Memory Limits --------max number of segments = 4096 /* SHMMNI */max seg size (kbytes) = 524288 /* SHMMAX */max total shared memory (kbytes) = 5242880 /* SHMALL */min seg size (bytes) = 1

To keep the value effective after every reboot, add the following line to /etc/sysctl.conf

echo “kernel.shmall = 1310720” >> /etc/sysctl.conf

Also verify if sysctl.conf is enabled or will be read during boot.

silicon:~ # chkconfig boot.sysctlboot.sysctl on

If returns “off”, means it’s disabled. Turn it on by running

silicon:~ # chkconfig boot.sysctl onboot.sysctl on

What’s the optimal value for SHMMAX?

Oracle makes use of one of the 3 memory management models to create the SGA during database startup and it does this in following sequence. First Oracle attempts to use the one-segment model and if this fails, it proceeds with the next one which's the contiguous multi-segment model and if that fails too, it goes with the last option which is the non-contiguous multi-segment model.

So during startup it looks for shmmax parameter and compares it with the initialization parameter *.sga_target. If shmmax > *.sga_target, then oracle goes with one-segment model approach where the entire SGA is created within a single shared memory segment.

But the above attempt (one-segment) fails if SGA size otherwise *.sga_target > shmmax, then Oracle proceeds with the 2nd option – contiguous multi-segment model. Contiguous allocations, as the name indicates are a set of shared memory segments which are contiguous within the memory and if it can find such a set of segments then entire SGA is created to fit in within this set.

But if cannot find a set of contiguous allocations then last of the 3 option’s is chosen – non-contiguous multi-segment allocation and in this Oracle has to grab the free memory segments fragmented between used spaces.

So let’s say if you know the max size of SGA of any database on the server stays below 1GB, you can set shmmax to 1 GB. But say if you have SGA sizes for different databases spread between 512MB to 2GB, then set shmmax to 2Gigs and so on.

Like SHMALL, SHMMAX can be defined by one of these methods..

Dynamically reset and reload it to the kernel..

# echo "536870912" > /proc/sys/kernel/shmmaxsilicon:~ # sysctl –p -- Dynamically reload the parameters.Or use sysctl to reload and reset ..

# sysctl -w kernel.shmmax=536870912

To permanently set so it’s effective in reboots…

# echo "kernel.shmmax=536870912" >> /etc/systctl.conf

Install doc for 11g recommends the value of shmmax to be set to "4GB – 1byte" or half the size of physical memory whichever is lower. I believe “4GB – 1byte” is related to the limitation on the 32 bit (x86) systems where the virtual address space for a user process can only be little less than 4GB. As there’s no such limitation for 64bit (x86_64) bit systems, you can define SGA’s larger than 4 Gig’s. But idea here is to let Oracle use the efficient one-segment model and for this shmmax should stay higher than SGA size of any individual database on the system.