Oracle BI Discoverer session limits

Every now and then, Oracle Discoverer (10.1.2) throws a tantrum saying that the “Hard limit on the number of sessions” has been reached. It gets particularly annoying when this happens during the middle of the week and we are left with no choice but to schedule a downtime and restart the services.

What causes this error?

The number of sessions is specified in the opmn.xml file and it also there that the parameter can be edited to a desired value. Thought this by no means is a solution, it greatly reduces the frequency of occurrence of this error.

Recently, I’ve tried to monitor the number of sessions, which could help me predict when the limit might be reached and therefore schedule a restart during an off-peak time beforehand.

There are three numbers that I’m checking

1. On the Linux command line,

$ ps -ef | grep dis51ws | wc -l

2. On the command line again,

$ netstat -apn | grep unix | grep dis51ws | wc -l

3. The number of sessions as shown in the Oracle Enterprise Manager Application Server console. 

Each of these produces a different value, however all of these values show an increase / decrease when a new session is opened / closed. Which of these values would be more likely to hit the limit specified in the xml file remains to be seen.

Once verified, a script can be created and scheduled to restart the services automatically.

Posted in Uncategorized | Tagged , , , , | Leave a comment

Creating Oracle – SQL Server database link

The idea is to create a Link between a ‘source’ database and a ‘target’ database so that, the client is able to query from the target, without having to configure the additional network connectivity. This is especially useful when there are many ‘targets’, plus many clients involved which would be a hassle to for example, edit the tnsnames for each client with individual entries for each target.

Instead, The tnsnames for all targets can be added in the source database and then a link may be created between the source and each of the targets. The client can then run a query normally, and by simply appending an extra word (dblink name) is able to query the target for which that link had been created!

Linking from an Oracle db to a non oracle db essentially involves using Heterogenous services, which is included with the database installation. For 10g and below, the program is ‘hsodbc’, and for 11g it’s ‘dg4odbc’. It can be found at $ORACLE_HOME/dg4odbc.

Firstly, the ODBC driver for that target db needs to installed on the source machine.

In this case, the source Oracle db ois on a unix machine. The host OS of the target dbs is … irrelevant.

1. Data Direct is the source from which the ODBC drivers can be obtained. Note that the trial is free but can only be used for 15 days. Also, registration is required before getting access to the download link :-)

2. Create a new directory on the ‘source’ machine (ODBCHOME for example). This would be the primary location of the software files, including odbc.ini

3. Download the driver package to the source system. The package will usually be in a compressed .gz form. Uncompress it and run unixmi.ksh

4. There are a number of prompts, most of which can be defaulted. At the software installation directory however, specify the path for the ODBCHOME directory.

5. When prompted for a Key or a Serial Number, enter ‘EVAL’ for the trial version.

6. Once the installation is successful, navigate to the ODBCHOME directory and open the file ‘odbc.ini’

7. This file is where the connection information is configured for each target. For starters, there’s two headers – [odbc data sources] and [Name of the data source]. There’s one example data source under the first header, and the list of parameters under the second.

8. To add a new data source, enter a name and specify the driver. For example,

[ODBC data sources]

dblink_orcl2mssql = Datadirect 7.1 SQL Server legacy wire protocol

9. Note the second header i.e. parameters for the data source. A separate header is defined for each target, with the header name being the data source name as defined earlier.

[ dblink_orcl2mssql]

Driver=

Description=

10. Edit the parameters for the target databases, specifically the database name, ip address, port, username and password.

11. Once the target data source(s) are configured, save and exit

That was only the tip of the iceberg. For the next part, the listener.ora and tnsnames.ora will be edited to include these newly created data sources. To do so, dg4odbc needs to be invoked.

12. Edit listener.ora to include the data source(s) as below:

(SID_DESC =

(SID_NAME = dblink_orcl2mssql)

(ORACLE_HOME = ...)

(ENVS = ....)

(PROGRAM = dg4odbc)

)

13. Reload the listener for the changes to take effect.

$ lsnrctl reload LISTENER_NAME

14. Likewise, edit tnsnames.ora to include the data sources

DBLINK01 =

(DESCRIPTION =

(ADDRESS  = (PROTOCOL = TCP) (HOST = ...) (PORT = ...))

(CONNECT_DATA = (SID = dblink_orcl2mssql))

(HS=OK)

)

Note the (HS=OK), which is required to enable heterogeneous services.

15. Once the tnsnames file is saved, verify the connectivity using TNSPING

$ tnsping DBLINK01

Next, creating the parameter file init<SID>.ora for each data source / dblink

16. In the $ORACLE_HOME/hs/admin directory, a few sample init files are included, such as initDG4ODBC.ora. For each data source, an init<SID>.ora file needs to be created and configured.

17. In this case, create a new file init<SID>.ora or copy an existing init.ora file and rename it.

18. Edit the parameters, specifically the first one i.e. HS_FDS_CONNECT_INFO. This parameter must be assigned the Data source name as the value.

19. Optionally, add any environment variables in the same init file.

20. Save and exit.

Finally, creating the database link itself.

21. Logon to SQLPlus with the sysdba privilege and execute the command to create the link

SQL> CREATE PUBLIC DATABASE LINK dblink_orcl2mssql CONNECT TO '<target_db_username>' IDENTIFIED BY '<target_db_password>' using 'DBLINK01';

Database link created

22. Run a query to test the link

SQL> SELECT COUNT(*) FROM SC01.EMP@dblink_orcl2mssql;

COUNT(*)

-------------------

86

Presto! Works like a charm.

Posted in Uncategorized | Tagged | Leave a comment

Oracle Enterprise Performance System 11 on Solaris – Installation and Configuration

INSTALLATION

1. Download the Installation packages from the Oracle Software Cloud and unzip into a single location.

2. Run installTool.sh

3. Specify the installation directories and proceed to select components to install. Foundation services is mandatory for any of the other components as a single, shared infrastructure.

More on Foundation Services here.

4. Confirm the selected components and proceed with the rest of the installation

CONFIGURATION

While installation in this case is only so much as extracting the components from the packages, configuration is the part where the individual components are made ready to use.

1. Navigate to your MIDDLEWARE_HOME/user_projects/epmsystem1/bin folder

2. Run configTool.sh

3. Configure selected components as required, notably

- Configure the email server and specify the host and an administrator email address

- Specify username/password to connect to a database

- Deploy all applications to a single managed server

- By default, the URL for the Browser Inferface would be - https://<hostname>:19000/interop

- Configure weblogic to manage application deployment. Port 9000 by default

Note: The Foundation Services must always be configured first, followed by the other components.

4. Verify the changes and ‘Finish’ the installation to make them permanent.

POST CONFIGURATION

1. Navigate to MIDDLEWARE_HOME/user_projects/epmsystem1/bin

2. Run start.sh to start all the services

3. Verify using ps -ef | grep oracle

4. Run stop.sh to stop the services

5. To start weblogic, navigate to MIDDLEWARE_HOME/user_projects/../domain

6. Run startweblogic.sh

7. Once the ‘Running’ status is displayed, the weblogic interface can be accessed from the browser using the URL specified at configuration.

Posted in Enterprise Performance Management System, Oracle | Tagged , , , | Leave a comment

Oracle Enterprise Performance System 11 on Solaris – Preparing the System

Detailed information can be obtained from the official Oracle EPM documentation or from the media pack.

Image

Installing the latest EPM 11.1.2.2 from Oracle is no biggie, be it Windows or a Unix based OS. The complexity lies in understanding the numerous components that are packaged together to form this massive piece of Enterprise software. I’ve only ever worked with the Reporting and Analysis component which barely counts as ‘scratching the surface’.

I used a machine created on a Solaris SPARC T4 with the following specifications:

System Memory: 32G
Hard Disk Space: 120G
OS: Solaris 10 64-bit

VNCServer needs to be configured so as to be able to display the GUI of the Installer. To do that,

$ /usr/bin/vncserver

New '<host-name>:1 (oracle) desktop is <host-name>:1

Starting applications specified in /home/oracle/.vnc/xstartup
Log file is /home/oracle/.vnc/<host-name>:1.log

Note: VNCServer may prompt to set the password during the first run. To change the password,

$ /usr/bin/vncpasswd

Open VNC Viewer and connect to <host-name>:1, which looks something like this:

Image

Test if the DISPLAY is set

$ xclock

If not, then as root user, disable access control.

$ xhosts +

JAVA DESKTOP

It is possible to make the screen look more visually appealing by enabling the standard desktop view. To do that,

$ export PATH=$PATH;/usr/X11/bin

$ vi /usr/bin/vncserver

As according to this method, modify the $defaultXStartup line to look like:

$defaultXStartup
= ("#!/bin/sh\\n\\n".
"gnome-session &\\n");

Restart the VNCServer and reconnect to <host-name>:1

Image

Now that the interface is more attractive, Installation would not be such a pain in the arse.

Posted in Enterprise Performance Management System, Oracle | Tagged , , , | Leave a comment

Virtualization with Oracle VM Manager

An excellent description of virtualization and the Oracle VM Manager is presented in the Oracle VM Manager User’s Guide:

Virtualization is the ability to run multiple virtual machines on a single piece of hardware. The hardware runs software that enables you to install multiple operating systems capable of running simultaneously and independently, in their own secure environment, with minimal impact on performance. Each virtual machine has its own virtual CPU, network interfaces, storage, and operating system.

One of the many perks of virtualization is the amount of resources it saves for an enterprise – power, cooling costs, physical space and maintenance staff. A single server supporting Ten Virtual Machines is often easier to maintain and costs less resources than Ten different Real Machines.

SYSTEM SETUP

Setting up the VM Manager is simple enough. Insert/mount the CD media to a server and run the installer.

Image

Setting up the VM Server(s): On each machine, insert/mount the Oracle Virtual Server CD media and proceed with the installation.

Image

Note: The IP address and the host-name can be set manually for ease of access.

POST INSTALLATION

Access OVM Browser Interface with the URL https://<host-name&gt;:7001/ovm/console

Image

  • Create a server pool
  • Discover existing servers using their IP addresses and assign them to the pool
  • Create a shared Repository
  • Present the repository to the servers
  • Update servers using Serevr Update Management (YUM)
  • Add a select number of VNICS (Virtual Network Interface Cards) by specifying an IP range. Each one is identified by an IP address, which can later be assigned to virtual machines.
  • Create a bond port
  • Import existing VM templates*
  • Create Virtual Machine(s)
  • (Live) Migrate the VM to any other server if needed
  • Access the VM console and configure system and network settings

* An issue may be encountered at this stage, possibly due to a faulty template. To address issues with VM templates, locate the file titled ‘vm.cfg’ specific to the template on the NFS Share and modify the parameters as needed.

RELATED CONTENT

Posted in Oracle, Virtualization, VM | Tagged , , | Leave a comment

Oracle Database Installation – Key points

A Summary of key points to be noted in subsequent installations, following a successful installation:

Installation Part 1
Installation Part 2
Installation Part 3

  • For Database Installation on a Virtual Machine Disk, It is recommended to set the Virtual Disk type as dynamic for optimum disk usage.
  • Oracle recommends a minimum of 1G system memory. However, atleast 1.5G is necessary to have enough resource for both the Operating System processes as well as the Database processes.
  • A database installed on a system with insufficient memory or improperly specified SGA/PGA may cause errors at Startup such as ORA-27102: Out of Memory.
  • The system hostname by default is ‘localhost.localdomain’. This can be changed before installation, or by editing /etc/sysconfig. It is recommended to do this before the Database Installation so as to avoid complications.
  • fdisk can be used to partition disks, but not existing partitions or volumes. It fails with the following error message:

Re-reading the partition table failed with error 22:    invalid argument

  • In order to create a partition on an existing partition or volume, use pvcreate / lvcreate.
  • +DATA diskgroup should have a minimum of 4G space available, or the database cannot be created.
  • To check the status of various components from the ASM instance:

$ crs_stat -t

Note: The installation had been done for a single node cluster.

  • Additional unix packages can be downloaded from the internet and copied across. Unfortunately, it is not possible to update the OS as it is a host-only network.
  • To connect from a remote client, ensure that

- Firewall is Disabled on the server
- SELinux set to permissive
- Listener is running

$ lsnrctl status <listener>

  • For a remote client, the tnsnames.ora file must have an entry corresponding to the SSID, identical to that in the server
  • To check the connectivity from the client:

$ tnsping orcl

Posted in Database, Oracle | Tagged , , , , , | 2 Comments

Oracle Database Installation (Part 3 of 3) – Like a BOSS

Continuing from Oracle Database Installation (Part 2 of 3)

The Database Software and the ASM instance have been setup. The final stage post installation is to create and configure a database.

CREATE A DATABASE

Set the environment variables before invoking the database configuration assistant

$ . oraenv
ORACLE_SID = [oracle] ? orcl
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/11.2.0/dbhome_1

The Oracle base for /u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle

$ dbca

This starts up the database configuration assistant. With the exception of a few changes, rest all have been left as default:

1. Operations > Create a Database
2. Database Templates > General Purpose or Transaction Processing
3. Database Identification > SID set to orcl
4. Database Credentials > Use same password for all accounts
5. Storage options > Specify ASM and select the +DATA diskgroup.
6. Recovery Configuration > Specify Flash Recovery Area and select FRA
7. Database Content > Selected sample schemas
8. Memory tabbed > Character set changed to Unicode AL32UTF8
9. Creation options > Save Database Template and Scripts
10. Finish

START THE DATABASE

To start up the database,

$ sqlplus / as sysdba

SQL> startup nomount

Alternatively, login to enterprise manager as SYS and click the startup button. To check if enterprise manager is running,

$ emctl status dbconsole

followed by,

$ emctl <start | stop> dbconsole

A simple way to check if the database instance is running:

$ ps -ef | grep smon

Subsequent Database management tasks can be accomplished either on the Enterprise Manager console or in SQLPLUS.

Posted in Database, Oracle | Tagged , , , | Leave a comment