Monday, December 24, 2012

Oracle 10g Express

Oracle Database Express Edition (Oracle Database XE) is an entry-level, small-footprint database which is free to develop, deploy, and distribute.

Install

Cent-OS
[bash]$ rpm -hiv oracle-xe-univ-10.2.0.1-1.0.i386.rpm
Ubuntu
[bash]$ sudo dpkg -i oracle-xe-universal_10.2.0.1-1.0_i386.deb

Configure

[bash]$ sudo /etc/init.d/oracle-xe configure

Specify the HTTP port that will be used for Oracle Application Express [8080]:8000
Specify a port that will be used for the database listener [1521]:
Specify a password to be used for database accounts.  Note that the same
password will be used for SYS and SYSTEM.  Oracle recommends the use of
different passwords for each database account.  This can be done after
initial configuration:
Confirm the password:
Do you want Oracle Database 10g Express Edition to be started on boot (y/n) [y]: y


/etc/default/oracle-xe

#This is a configuration file for automatic starting of the Oracle
#Database and listener at system startup.It is generated By running
#'/etc/init.d/oracle-xe configure'.Please use that method to modify this
#file

# ORACLE_DBENABLED:'true' means to load the Database at system boot.

ORACLE_DBENABLED=true

# LISTENER_PORT: Database listener

LISTENER_PORT=1521

# HTTP_PORT : HTTP port for Oracle Application Express

HTTP_PORT=8080

# Configuration : Check whether configure has been done or not

CONFIGURE_RUN=true 

Start/stop/check the status of oracle

Cent-OS
[bash]$ sudo /sbin/service oracle-xe start
[bash]$ sudo /sbin/service oracle-xe stop
[bash]$ sudo /sbin/service oracle-xe status

Ubuntu
[bash]$ sudo service oracle-xe start
[bash]$ sudo service oracle-xe stop
[bash]$ sudo service oracle-xe status
or
[bash]$ sudo /etc/init.d/oracle-xe start
[bash]$ sudo /etc/init.d/oracle-xe stop

Browse the Oracle service

http://localhost:8000/apex/

Uninstall

Stop server
sudo /etc/init.d/oracle-xe stop

Cent-OS
[bash]$ rpm -e oracle-xe-univ-10.2.0.1-1.0

Ubuntu
[bash]$ sudo dpkg -r oracle-xe-universal

Above doesn't remove everything

Use following
[bash]$ sudo apt-get remove --purge oracle-xe-universal

It removes following folders as well.
/usr/lib/oracle
/etc/init.d/oracle-xe
/etc/default/oracle-xe

Listeners

The Oracle Net foundation layer is responsible for establishing and maintaining the connection between the client application and database server, as well as exchanging messages between them. The Oracle Net foundation layer is able to perform these tasks because of a technology called Transparent Network Substrate (TNS). TNS provides a single, common interface for all industry-standard protocols. In other words, TNS enables peer-to-peer application connectivity, where two or more computers can communicate with each other directly, without the need for any intermediary devices.

On the client side, the Oracle Net foundation layer receives client application requests and resolves all generic computer-level connectivity issues, such as:
  • The location of the database server or destination
  • How many protocols are involved in the connection
  • How to handle interrupts between client and database server based on the capabilities of each

On the server side, the Oracle Net foundation layer performs the same tasks as it does on the client side and also works with the listener to receive incoming connection requests.

The database server receives an initial connection from a client application through the listener. The listener is an application positioned on top of the Oracle Net foundation layer

The listener brokers client requests, handing off the requests to the Oracle database server. Every time a client requests a network session with a database server, a listener receives the initial request.

Each listener is configured with one or more protocol addresses that specify its listening endpoints. Clients configured with one of these protocol addresses can send connection requests to the listener.

Once a client request has reached the listener, the listener selects an appropriate service handler to service the client's request and forwards the client's request to it. The listener determines if a database service and its service handlers are available through service registration. During service registration, the PMON process—an instance background process—provides the listener with information about the following:
  • Names of the database services provided by the database
  • Name of the instance associated with the services and its current and maximum load
  • Service handlers (dispatchers and dedicated servers) available for the instance, including their type, protocol addresses, and current and maximum load

Listener Parameters (listener.ora)

Listener configuration, stored in the listener.ora file, consists of the following elements:
  • Name of the listener
  • Protocol addresses that the listener is accepting connection requests on
  • Database services
  • Control parameters
ORACLE_HOME/network/admin/listener.ora

# listener.ora Network Configuration File:SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
      (ADDRESS = (PROTOCOL = TCP)(HOST = dev30)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)

Local Naming Parameters (tnsnames.ora)

This tnsnames.ora file is a configuration file that contains net service names mapped to connect descriptors for the local naming method, or net service names mapped to listener protocol addresses.

A net service name is an alias mapped to a database network address contained in a connect descriptor. A connect descriptor contains the location of the listener through a protocol address and the service name of the database to which to connect. Clients and database servers (that are clients of other database servers) use the net service name when making a connection with an application.

ORACLE_HOME/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File:XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dev30)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )


Troubleshooting


[bash] $ tnsping xe

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 24-DEC-2012 14:54:26

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = cnb18)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))
OK (0 msec)


java.sql.SQLException: Io exception: The Network Adapter could not establish the connection

I recived the above due to adding the wrong port when configuring. Eventhough it was reconfigured and everything looked fine in config files stil I got the following error.

ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

Uninstalling didn't solve the problem. Finally deleting /usr/lib/oracle/xe and reinstall worked!!

I recived the following error
[WLServer adminserver] ORA-12519, TNS:no appropriate service handler found


And increasing the number of processes by running the following query as the admin user solved it.

ALTER SYSTEM SET PROCESSES=200 SCOPE=SPFILE; ALTER SYSTEM SET;

No comments:

Post a Comment