OracleApps Table Naming Conventions for _B, _TL and _VL

The most seen format of tables in Oracle are the general tables, views, synonyms etc . But we alos have others like _B,__TL and _VL which have their own specific operation and usage. Let’s see what they actually define within them and how they are different from general tables,views etc.

_B these are the BASE tables.

• They are very important and the data is stored in the table with all validations.
• It is supposed that these tables will always contain the perfect format data.
• If anything happens to the BASE table data, then it is a data corruption issue.

_TL are tables corresponding to another table with the same name minus the _TL. These tables provide multiple language support. For each item in the table without _TL there can be many rows in the _TL table, but all with different values in the LANGUAGE column.

_VL are views for multi language tables which combines the row of the base table with the corresponding row of the _TL table where the LANGUAGE = USERENV('LANG').

What does this mean, let get into details with an example.

Let’s say we have a record in BASE table with some name as TABLENAME_B. This can have multiple records in TABLENAME_TL table with LANGUAGE column different for each record.


Now how does TABLENAME_VL come into usage is when a user runs a select on this table only one record is displayed based on his language setting if at all that language exists in the TABLENAME_TL table.

If you want to see the records, you have to change the NLS_LANGUAGE parameter as per the language availability in _TL tables.

RHEL : Working with ODBC and ODBCINI

 Most of us are aware that ODBC drivers are the most used connectivity option on Linux when you want to connect to non-Oracle databases. As the Oracle databases are mostly connected by using the Oracle client installation or Default configuration available as part of the installation of the application, in any case, tnsnames.ORA will hold the database details as required.

Similarly, for ODBC we have two files odbcinst.ini and odbc.ini, each having its own purpose. For now I'm considering that we are on Red Hat Linux and the ODBC manager used is unixODBC 2.3.1

Find OS Version on RHEL using the below command

cat /etc/redhat-release



Find the default ODBC Manager version and where it is setup use below command

odbcinst –j



odbcinst.ini

This initialization file will have the details of the drivers that are available on the server, for example, the default ones are as below.

[PostgreSQL]

[MySQL]
You can get the drivers available in the file by using the below command.

odbcinst –q –d

Let’s say you want to configure Amazon RedShift drivers on the server, first step is to install the driver on the server after the installation add the below to the odbcinst.ini file

[RedShiftDB]

Description=Amazon Redshift ODBC Driver (64-bit)

Driver=/opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so


Once we add this and run the odbcinst –q –d command we will now see below drivers

[PostgreSQL]

[MySQL]

[RedShiftDB]

ODBC.ini

This is the file where we actually configure the DSN with server details of the database’s we intend to connect or access from the Linux server.

In our Amazon RedShift case add the below with your respective details.

[RedShiftDB]

Description=Amazon Redshift ODBC

Driver=/opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so

ServerName=abcdefgh.redshift.amazonaws.com

Port=5439

Database=redshiftdatabase1

locale=enUS

QueryTimeout=2000

ProxyHost=

ProxyPort=

We can verify the sources from the odbc.ini file using the below command

odbcinst –q –s

we can see the below output

[RedShiftDB]

Now it’s time we set these files as the system variables in bash_profile so that they work as expected,

OBDCINI – location of odbc.ini

AMAZONREDSHIFTODBC – location of file amazon.redshiftodbc.ini

LD_LIBRARY_PATH – library paths where your ODBC manager has installed, ex. /usr/lib64/


See the below examples

export ODBCSYSINI=/shared/odbc

export ODBCINI=/shared/odbc/odbc.ini

export AMAZONREDSHIFTODBCINI=/shared/odbc/amazon.redshiftodbc.ini

export LD_LIBRARY_PATH=/usr/lib64/:$LD_LIBRARY_PATH


Now lets test this using the isql command. This will help us to connect to anyof the DSN we added in odbc.ini, the command can be used as below


Isql –v RedShiftDB username password

OBIEE 12c : Changing the system component port numbers

One should be aware of the default ports used in OBIEE 12c, as soon as you install it.9514 for BI Server Component and 9507 for presentation server. This is because of the port range specified during the installation most of us will not have any problem with this. But sometimes when you have other instances with different versions of OBIEE or other applications installed in the same server as OBIEE 12c, there is a chance that we might have the port conflict.

My case, my other versions were all using 9031and 9033 for BI Server and Presentation Server respectively. So to get uniformity we were asked to change the OBIEE 12c ports as per the other OBIEE instances.

After a bit of digging on the Internet, I was able to find the process, which turns out to be pretty simple. Below are the steps

• Stop the component for which you want to change the port.

Stop.sh -I obis1

• Locate the file bienv-components.xml from below location 

Backup [DOMAIN_HOME]/config/fmwconfig/bienv/core/

• Change the port which you intended to my case it’s the BI Server component (obis1)

<component instanceId="obis1">
  <endpoint>
    <componentType>OBIS</componentType>
    <port>9031</port>
    <initialMachine>server.domain</initialMachine>
    <sslMode>shared</sslMode>
  </endpoint>
  <endpoint>
    <componentType>OBIS_MONITOR</componentType>
    <port>9591</port>
    <initialMachine>server.doimain/initialMachine>
    <sslMode>shared</sslMode>
   </endpoint>
</component>   

• Start the component using the below command

Start.sh –I obis1

• This doesn’t change the values in Enterprise manager yet, as to synchronize we will need a full restart of Admin server. Can perform the same using stop.sh and start.sh

Note: The port that you give in the file should be available for use at OS level else your service will fail.

OBIEE 12c : Starting and Stopping the services is too slow

We have recently installed a OBIEE 12c on RHEL 7 server. As part of initial test we have done multiple restarts of the server using the default scripts start.sh and stop.sh. The thing that I observed is its taking very long time in doing the start and stop operations. I did some investigation on this and to my surprise I found an interesting update in OBIEE 12c.

As we are already aware that we no longer have the OPMN to monitor the system components, I was under the impression that they are monitored by Admin server. To my surprise I learnt that Node Manager is the one looking after these BI System Components. From this it’s clear that we have the delay because of the Node Manager and to fix this we need to increase the JVM Heap size.

We will do this using MEM_ARGS parameter which is a variable used to override the standard memory arguments passed to java.

Let’s see how we can do that in 12c

• Locate the file “commBaseEnv.sh” in below location

[ORACLE_HOME]/oracle_common/common/bin

• Open the file and Make the change for below line. Below shows the default values. Set some higher values, be sure to check your free memory and do the changes accordingly.

.....

case $JAVA_VENDOR in

Oracle)

if [ "${VM_TYPE}" = "JRockit" ]; then

JAVA_VM=-jrockit

MEM_ARGS="-Xms128m -Xmx256m"

UTILS_MEM_ARGS="-Xms32m -Xmx1024m"

else

JAVA_VM=-server

MEM_ARGS="-Xms32m -Xmx200m" 

UTILS_MEM_ARGS="-Xms32m -Xmx1024m" 

fi

....

...

...

else

case $JAVA_VENDOR in

Oracle)

if [ "${VM_TYPE}" = "JRockit" ]; then

JAVA_VM=-jrockit

MEM_ARGS="-Xms128m -Xmx256m"

UTILS_MEM_ARGS="-Xms32m -Xmx1024m"

else

JAVA_VM=-server

MEM_ARGS="-Xms32m -Xmx200m" 

UTILS_MEM_ARGS="-Xms32m -Xmx1024m" 

fi

VERIFY_NONE="-Xverify:none"

• Restart the services using the start.sh and stop.sh as usual. This should resolve the slow start and stop issue.

OBIEE 12c : Change the default Locale from US to UK

All the Oracle Software’s comes with default as English-United States locale, this is something that gets some of us confused, because we don’t have any idea what a locale means. Frankly it doesn’t matter as it is mostly end user specific and Developers doesn’t bother about this very often. However end user happiness being the ultimate agenda of all the Software projects we are more inclined to enquire and acquire knowledge on this area.

Locale settings are something that are derived based on the region specific formats followed in the real time and are set to be followed by application, let’s take the OBIEE locales available in the localedefinitions.xml file which defines locale settings of different regions across the globe.

Some Example of Locale Settings

• Date Format 

                   UK - DD-MM-YYYY
                   US - MM-DD-YYYY

• Language

                    US - English (en)
                    France - French (fr)

Similar to the above we have date separator, Time Separator, Date Order, First day of week etc which may vary based on a country or region.

In OBIEE 12c, there are two files which hold information on these locale settings, localedefenitions.xml and localemapppings.xml located in below folder

[Oracle_Home]/bi/bifoundation/web/display

you can verify the default by logging on to the analytics and in my account you will find the below details by default.

If you want to change the default Locale to a different one to that of United States, please follow the below steps

• Open the localemappings.xml file from above Location
• Locate and change the below lines as shown to change it to English - United Kingdom

• Restart the complete OBIEE services

OBIEE 11g : Presentation Server state is NONE

Recently i had an issue starting the Presentation Services in OBIEE 11g. Where the status is NONE. I tried restart of all OPMN components as well was presentation service alone but all my efforts went  in vain. Then i started looking at the sawlog and found something very annoying.

[OBIPS] [INCIDENT_ERROR:1] [] [saw.webextensionbase.init] [ecid: ] [tid: ] Invalid item /system/mktgcache/sawguidstate.[[

File:catalogimpl.cpp

the error is now where related to what i do and moreover i haven't made any changes to the configuration or any other thing in OBIEE. Then i started looking close at the error which states that the initialization is not happening. Then i realized the last time the system has been turned off without shutting down the services which has left the saw server in non responsive state. the error says the file sawguidstate id invalid.

So lets delete this file and .atr related to this and start the presentation service.

Below is the location of files

[INSTANCE_HOME]\bifoundation/bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog\SampleAppLite\root\system\mktgcache

delete both sawguidstate  and sawguidstate.atr , if you don't want to delete them take a back up under this folder.

[INSTANCE_HOME]\bifoundation/bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog\SampleAppLite\root\system\mktgcache\{hostname}

Now start the Presentation server using the below statement

opmnctl startproc ias-component=coreapplication_obips1

Hope this resolves your error anything otherwise contact me.

Have a Good Day !!!!!

OBIEE 12c: Amazon RedShift ODBC configuration on Linux

Starting from OBIEE 12c Oracle has started supporting the Amazon Cloud database Redshift as a data source. This is very good news as there are lot of clients waiting for using the cloud data sources with OBIEE. Below are the high level steps on configuring the ODBC driver on Linux to use in connection pool.

 Below are the high level steps

• Installing Driver 
• Configuration of ODBC parameters
• Configure Connection Pool

Installing the Driver

Download and install Amazon Redshift ODBC driver to access Amazon Redshift data warehouse. Follow the instructions provided in Amazon Redshift documentation.

Note: By default, Amazon Redshift drivers are installed in the following directories
/opt/amazon/redshiftodbc/lib/32 (for a 32-bit driver)
/opt/amazon/redshiftodbc/lib/64 (for a 64-bit driver)

Configure connectivity to Amazon Redshift

The following files has to be edited to configure Oracle BI server connectivity to Amazon Redshift :
1. odbc.ini
2. odbcinst.ini
3. amazon.redshiftodbc.ini
4. Copy dbfeatures.ini

Note: The following edits to files assume that the Amazon Redshift drivers are installed under “/opt/amazon/redshiftodbc/lib/64”.


1. odbc.ini

Add the following to odbc.ini file

[ODBC Data Sources]

.

.

.

RedShift=installed

[RedShift]

Description=Amazon Redshift ODBC

Driver=/opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so(default location, once you upgrade or reinstall the driver, file name may change)

ServerName=

Port=

Database=

locale=en-US

QueryTimeout=2000

ProxyHost=

ProxyPort=

2. odbcinst.ini

Add the following to odbcinst.ini file

[ODBC Drivers]
.
.
.
RedShift=installed
[RedShift]
Description=Amazon Redshift ODBC Driver (64-bit)
Driver=/opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so

3. amazon.redshiftodbc.ini

Edit “/opt/amazon/redshiftodbc/lib/64/amazon.redshiftodbc.ini” and update the following.

[Driver]
.
.
DriverManagerEncoding=UTF-16

4. dbfeatures.ini

Copy the attached dbfeatures.ini file to BI server config directory, /config/fmwconfig/biconfig/OBIS

Setting Up Database Objects and Connection Pools


Install Amazon Redshift ODBC driver for windows. Create a system DSN.

Set the database type to “ODBC Basic” or “ODBC Advanced” in connection pool properties

Set connection pool call interface to “ODBC 3.5”