wiki:OracleAccess

Accessing KLPEMS

You might be surprised to learn that KLPEMS (the unified database) is an Oracle database. A replicated DB has been created on the production server (hosted by Nettigritty). This DB is dropped every day at 0500 hrs and re-created from the nightly backup of the real production database.

This database can be accessed using Oracle's TNS. The Oracle 11g client has been installed on this machine and you can can use your favourite programming language (which supports Oracle, of course) for your DP needs.

Oracle installation

You can install either the Oracle Instant Client or the Oracle Standard Client. This machine uses the instant client. There is an OTN account registered.

Since this machine is x86_64, it makes sense to install a 64 bit version of the client software, but Oracle makes it a little interesting. Get the rpms from here. Make sure you get the basic client and not the basiclite package if you want to install DBD::Oracle. There are no debs so get the rpms. The packages needed are

  1. oracle-instantclient11.1-basic
  2. oracle-instantclient11.1-devel
  3. oracle-instantclient11.1-sqlplus

The winkle now is that the packages are oracle-instantclient11.1-basic while DBD::Oracle depends on oracle-instantclient-basic. Convert them to debs using alien. Use -g and changes the name of the package.

 $ mkdir t
 $ for i in *.rpm; do cp $i t; cd t; fakeroot alien -g $i; cd -; done
 $ find t -name control | xargs perl -pi.orig -e 's/client11.1-/client-/ if (/^Package:/);'
 $ cd t
 $ for i in oracle-instantclient11.1-basic-11.1.0.7.0 \
            oracle-instantclient11.1-devel-11.1.0.7.0 \
            oracle-instantclient11.1-sqlplus-11.1.0.7.0; \
   do cd $i; fakeroot dpkg-buildpackage; cd -; done
 $ sudo dpkg -i *.deb

Also install libaio1. You will also need to setup the Oracle environment for your user

 ORACLE_HOME=/usr/lib/oracle/11.1
 LD_LIBRARY_PATH=${ORACLE_HOME}/client64/lib
 TNS_ADMIN=~/etc/oracle
 export ORACLE_HOME LD_LIBRARY_PATH TNS_ADMIN

As an aside, Oracle does provide debs, but only for i386. The repository for the debian packages is

deb http://oss.oracle.com/debian unstable main non-free

DBD::Oracle installation

This is the Perl DBD backend for Oracle. The Debian package depends on a virtual oracle-instantclient-basic package. Also, the prebuilt binaries on lenny depend on Oracle 10g rather than 11g. Install DBI as

$ sudo aptitude install libdbi-perl

And install DBD::Oracle manually. You'll have to set the $ORACLE_HOME variable when generating the Makefile.

$ ORACLE_HOME=/usr/lib/oracle/11.1/client64 perl Makefile.PL
$ make
$ make test # Will fail a few tests
$ sudo make install

cx_Oracle installation

This is the Python DB API specification compliant backend for Oracle. Download the source tarball and build as

$ sudo ORACLE_HOME=/usr/lib/oracle/11.1/client64 LD_LIBRARY_PATH=${ORACLE_HOME}/lib python setup.py build install

There are some compilation warnings which appear to be ignorable.

Installation on Windows

From http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winsoft.html get

  • instantclient-basiclite-win32-11.1.0.7.0.zip and
  • instantclient-sqlplus-win32-11.1.0.7.0.zip

Unzip both files (make sure they are exactly the same version) into C:\Program Files\Oracle. Place the tnsnames.ora file in a suitable place and add the location of sqlplus to %PATH% and the location of tnsnames.ora in %TNS_ADMIN%.

If you would like to use TOAD to access the DB, make sure you download version 10.0 of the client software. TOAD does not work with version 11.1.

Oracle on dev.klp.org.in

As on December 2009, the KLP data does not fit in Oracle XE. Oracle 10g SE has been installed on dev.klp.org.in and will be used going forward.

Installation

As mentioned earlier, the process of installation doesn't work so well on pure 64-bit platforms. While RHEL and Solaris have biarch working pretty ok, Debian has loftier goals (multiarch). The niggles are with OUI and not with running the DB (which works just fine on pure x86_64 platforms).

Before starting the installation,

  • check that kernel parameters are as per spec
  • Create an oracle login and an oradba group
  • Add yourself to the oradba group if you plan on using SQL*Plus locally

Oracle attempts to use HUGETLB by default. If you see a problem creating the SGA, then this is probably the cause. It's easier to allow it than disable it. Add the following to /etc/sysctl.d/hugetlbfs.conf:

vm.nr_hugepages = 64
vm.hugetlb_shm_group = 1003

The other sysctl parameters required for Oracle are:

kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=262144
net.core.wmem_default=262144
net.core.rmem_max=262144
net.core.wmem_max=262144

To install, use a response file and use the installer in silent mode. The response file is attached below. In the response file, you can ask the installer to create a starter DB for you but OUI leaves some things undone so the listener and the starter database you asked to be done will not be done. Invoke OUI as:

# sudo -u oracle ./runInstaller -silent -ignoreSysPrereqs -responsefile /tmp/oracle.rsp

If you mess up and want to start again, you can just delete the directory you installed to and then remove the obsolete entry from oraInventory/ContentsXML/inventory.xml.

Once the installer is done and reports success, configure the listener. In $ORACLE_HOME/network/admin/listner.ora

LISTENER =
 (ADDRESS_LIST=
        (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
        (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

SID_LIST_LISTENER=
  (SID_LIST=
        (SID_DESC=
         (GLOBAL_DBNAME=oracle.klp.org.in)
         (SID_NAME=klpdb)                       
         (ORACLE_HOME=/srv/oracle)
       )
      )

Check with lsnrctrl. Now, create a initSID.ora in $ORACLE_HOME/dbs/. To begin with, ensure that you have

undo_management = AUTO

in it. Then connect to the instance as SYSDBA and startup nomount. If you get errors, check the permissions on $ORACLE_HOME/bin/oracle, it should be 6751. Then check the shared_pool_size in initSID.ora, it should be a few hundred MB at least. Apart from that, use Google and the log files. Once, the instance is up, create a database with

create database klp
logfile group 1 ('/srv/oracle/klp_oradata/redo/redo1_00.log') size 100M,
        group 2 ('/srv/oracle/klp_oradata/redo/redo2_00.log') size 100M,
        group 3 ('/srv/oracle/klp_oradata/redo/redo3_00.log') size 100M
character set AL32UTF8
national character set utf8
datafile '/srv/oracle/klp_oradata/klp_00.dbf' size 10G autoextend on next 512M maxsize unlimited extent management local
sysaux datafile '/srv/oracle/klp_oradata/sysaux.dbf' size 500M autoextend on next 10M maxsize unlimited
undo tablespace undotbs1 datafile '/srv/oracle/klp_oradata/undotbs_00.dbf' size 8G
default temporary tablespace temp tempfile '/srv/oracle/klp_oradata/temp_00.dbf' size 16G
user sys identified by <password>
user system identified by <password>;

Then run the catalog creation scripts.

@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql

shutdown and then startup. Now you should have a instance up and a database mounted. Now run sqlplus/admin/pupbld.sql as system to enable user profiles.

The default temp tablespace is about 16G. To add more:

alter tablespace temp add tempfile '/srv/oracle/klp_oradata/temp_01.dbf' size 14G;

Check to see if everything got added all right:

select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

Create a user and grant required privileges.

create user klp identified by <password> default tablespace klp temporary tablespace temp;

grant create session, create table, create view, create procedure, create sequence, create type to klp;

grant unlimited tablespace to klp;

Now import the dump.

$ imp klp/q1w2e3r4 file=<dumpfile> full=yes touser=klp feedback=10000 log=imp-$(date +%Y%m%d).log

To start oracle 1) Start listener as su - oracle | sudo -i -u oracle

$  lsnrctl start (stop to stop)

2) Start the oracle service as a sudo user - exit oracle and run as any beta sudo user

$  sudo /etc/init.d/oracle start (stop to stop - run this first while stopping Oracle)

Freeing up Temp Space:

ALTER DATABASE TEMPFILE '/srv/oracle/klp_oradata/temp_01.dbf' DROP INCLUDING DATAFILES;
ALTER DATABASE TEMPFILE '/srv/oracle/klp_oradata/temp_00.dbf' DROP INCLUDING DATAFILES; 

alter tablespace temp add tempfile '/srv/oracle/klp_oradata/temp_00.dbf' size 16G;
alter tablespace temp add tempfile '/srv/oracle/klp_oradata/temp_01.dbf' size 14G;
Last modified 7 years ago Last modified on 07/11/11 13:53:29