DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Tuesday, May 14, 2013

Performance issue after 11.2.0.3 Database Upgradation


After the Upgradation from 11.1.0.7 to 11.2.0.3 we started facing performance issue in execution of the custom concurrent program. The program which was running within 5 minutes was not completing even after 24 hours.

We enabled the ORADEBUG on the custom concurrent program and found the below wait Events continuously appearing in the trace file.

*** 2013-05-10 19:33:50.434

WAIT #4786366736: nam='db file sequential read' ela= 123 file#=37 block#=483362 blocks=1 obj#=78688 tim=2862165438070

WAIT #4786366736: nam='db file sequential read' ela= 13734 file#=23 block#=530786 blocks=1 obj#=294940 tim=2862165452342

WAIT #4786366736: nam='db file sequential read' ela= 6370 file#=23 block#=530811 blocks=1 obj#=295006 tim=2862165460960

WAIT #4786366736: nam='db file sequential read' ela= 3017 file#=28 block#=348952 blocks=1 obj#=296770 tim=2862165595961

WAIT #4786366736: nam='db file sequential read' ela= 217 file#=23 block#=531149 blocks=1 obj#=295006 tim=2862165869723

WAIT #4786366736: nam='db file sequential read' ela= 91 file#=37 block#=483363 blocks=1 obj#=78688 tim=2862166021779

We identified a particular insert query which was running for a long time.

We also observed that Execution plan has been changed for the insert query from 11.1.0.7 to 11.2.0.3 after the db upgrade to 11.2.0.3

We performed the below action plan to revert back the performance before the db upgrade to 11.2.0.3.

We have set the parameter "optimizer_features_enable = 11.1.0.7" in session level for the particular package which has been utilized in the custom program, in the beginning of the package. Again at the end of the package we reset the optimizer back to 11.2.0.3 by setting "optimizer_features_enable = 11.2.0.3"

The above change considers the execution plan back to 11.1.0.7 as it was before the db upgrade.

After that custom program started to complete normally within 5 minutes, like how it worked before db upgradation.

Thursday, May 9, 2013

OBIEE 11g Analytics : Error while logging into application portal "Authentication failed: invalid user/password"

OBIEE 11g Analytics : Error while logging into application portal "Authentication failed: invalid user/password"

When we try to login to OBIEE 11g Analytics after installation, We were not able to login to portal and got the error saying "Authentication failed: invalid user/password"

The issue was caused because OBIEE Installed on Virtual Box and Loopback adapter was not installed , which is the pre-requirement for Installing OBIEE 11g in virtual box.

Solution:

To resolve this error , We Installed Loopback adapter in the windows server.

1. Open the Windows Control Panel.
     Windows 2003: Select Start > Control Panel > Add Hardware.
     Windows XP: Select Start > Control Panel, then double-click Add Hardware.


2. In the "Welcome" window, click Next.

3. In the "Is the hardware connected?" window, select Yes, I have already connected the hardware,
    then click Next.


4. In the "The following hardware is already installed on your computer" window, in the list of
    installed hardware, select Add a new hardware device, then click Next.


5. In the "The wizard can help you install other hardware" window, select Install the hardware
    that I manually select from a list, then click Next.


6. In the "From the list of hardware types, select the type of hardware you are installing" window,
    select Network adapters, then click Next.


7. In the "Select Network Adapter" window, make the following selections:
    o   Manufacturer: Microsoft
    o   Network Adapter: Microsoft Loopback Adapter


8. Click Next.

9. In the "The wizard is ready to install your hardware" window, click Next.

10. In the "Completing the Add Hardware Wizard" window, click Finish.

11. If you are using Windows 2003, restart your computer.

12. Right-click My Network Places on the desktop and choose Properties.
      This displays the Network Connections Control Panel.


13. Right-click the connection that was just created.
      This is usually named "Local Area Connection 2". Choose Properties.


14. On the "General" tab, select Internet Protocol (TCP/IP), then click Properties.

15. In the "Properties" dialog box, click Use the following IP address and do the following:
a. IP Address: Enter a non-routable IP for the loopback adapter. Oracle recommends the
    following non-routable addresses:
      192.168.x.x (x is any value between 1 and 255)
      10.10.10.10
b. Subnet mask: Enter 255.255.255.0.
c. Record the values you entered, which you will need later in this procedure.
d. Leave all other fields empty.
e. Click OK.


16. In the "Local Area Connection 2 Properties" dialog, click OK.

17. Close Network Connections.

18. Restart the computer.
 

ORA-12537 While Connecting To Database Via Listener

 
ORA-12537 While Connecting To Database Via Listener
 
Connection to the database server fails with ORA-12537
Local connections working fine but the connection via listener fails with ORA-12537
 
[oracle@myhost]$ sqlplus myuser/mypass@mytns
SQL*Plus: Release 11.1.0.7.0 - Production on Fri Apr 30 21:32:18 2010
Copyright (c) 1982, 2008, Oracle. All rights reserved.
ERROR:
ORA-12537: TNS:connection closed
 
 
The listener log for the failing connection shows following error message -
 
TNS-12518: TNS:listener could not hand off client connection
  TNS-12546: TNS:permission denied
   TNS-12560: TNS:protocol adapter error
    TNS-00516: Permission denied
      Linux Error: 13: Permission denied

Permissions on oracle binary ( $ORACLE_HOME/bin/oracle ) would not have set correctly.
 
The OS level error message in the listener log "Linux Error: 13: Permission denied" indicates that the listener was not able to hand off the connection to oracle processes or not able to spawn a new dedicated user process because of lack of permission at the OS level. 
 
To resolve the error -
 
1. Check and correct the permission on /var/tmp/.oracle directory. Permissions on this directory should be 777 with sticky bit set.
 
# chmod 01777 /var/tmp/.oracle 
 
2. Check and correct permissions on $ORACLE_HOME/bin/oracle binary and make sure that it is set to 6751.
 
  $ chmod 6751 $ORACLE_HOME/bin/oracle
 
Reference Metalink Note :1050756.6

OBIEE 11g Client Admin Tool Connection To Datasource In Offline Mode Fails With Error "The Connection Has Failed"

OBIEE 11g Client Admin Tool Connection To Datasource In Offline Mode Fails With Error "The Connection Has Failed"

Created a new repository (RPD) using the Oracle Business Intelligence Enterprise Edition (OBIEE) Admin Tool.  When trying to import data in offline mode, the following error occurs with both ODBC and OCI connection:

Error
The Connection has failed.

You may also notice all or some of the following errors in NQSAdminTool.log:
[2011-02-07T11:26:19.000+00:00] [OracleBIServerComponent] [nQSError: 17003] Oracle gateway error: OCIEnvNlsCreate or OCIEnvInit failed to initialize environment. Please check your Oracle Client installation and make sure the correct version of OCI libraries are in the library path.
...
[2011-02-08T15:42:58.000+00:00] [nQSError: 17014] Could not connect to Oracle database. [[
[nQSError: 17001] Oracle Error code: 12154, message: ORA-12154: TNS:could not resolve the connect identifier specified
at OCI call OCIServerAttach
...
]]
[2011-02-08T15:46:08.000+00:00] [nQSError: 17014] Could not connect to Oracle database. [[
[nQSError: 17001] Oracle Error code: 12705, message: ORA-12705: Cannot access NLS data files or invalid environment specified at OCI call OCISessionBegin
...

 The issue is not reproducible while in Online mode.

This issue would be due to Multiple Oracle Homes exist which can cause confusion to the OBIEE Admin Tool for where to reference OCI libraries, NLS and the tnsnames.ora entries.
Additional configuration steps are required to ensure the desired files are used.

To prevent this error, you need to complete the following configuration tasks:

NOTE: In all cases, the OBIEE Admin Tool should be started by using the Start -> Programs -> Oracle Business Intelligence -> Administration Tool menu item and not by double-clicking the RPD file itself.  Starting the Admin Tool via the menu item is required to initialize required environment variables.
Setting the TNSNAMES.ORA entry to use the same name as the Oracle Database SID is recommended. The TNS_ADMIN environment variable needs to point to the physical location of the TNSNAMES.ORA file in the local environment (whether this be the OBIEE server machine or Admin Tool machine).

 1. For the Admin Tool installed with the complete OBIEE server installation:
 1.1. Edit registry under HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ to make sure NLS_LANG key is set to a valid characterset for the Oracle Client used as the DSN.
 1.2. Please edit the file %MiddleWare_Home%\instances\instance1\bifoundation\OracleBIApplication\coreapplication\setup\bi-init.cmd to set %ORACLE_HOME%\bin as first entry in the PATH.
 e.g.


set PATH=%OBIEE_HOME%\bin;%OBIEE_HOME%\bifoundation\server\bin;%OBIEE_HOME%\bifoundation\web\bin;C:\Oracle\BIEE_11g\jre\bin;%windir%;%windir%\system32;%PATH%
 1.3. Please edit the file %MiddleWare_Home%\instances\instance1\bifoundation\OracleBIApplication\coreapplication\setup\user.cmd to set TNS_ADMIN to your Oracle client tnsnames.ora home directory path (in case exists) or to your OBIEE tnsadmin %OBIEE_Client_Home%\Oracle_BI1\network\admin.
 e.g.


set TNS_ADMIN=C:\oracle\product\11.2.0\client_1\network\admin
~ OR ~

set TNS_ADMIN=C:\Oracle\OracleBIEE11g\Oracle_BI1\network\admin

 2. For the Admin Tool installed with the new standalone installer:
 2.1. Edit registry under HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ to make sure NLS_LANG key is set to a valid characterset for the Oracle Client used as the DSN.
 2.2. Please edit the file %OBIEE_Client_Home%\\oraclebi\orahome\bifoundation\server\bin\bi_init.bat to set %ORACLE_HOME%\bin as first entry in the PATH.
 2.3. Please add TNS_ADMIN entry to the bi_init.bat file to point to your Oracle client tnsnames.ora home directory path.
e.g.


set TNS_ADMIN=C:\oracle\product\11.2.0\client_1\network\admin


NOTE:  While connecting from your standalone BI Admin Tool client in online mode, the Admin Tool will use the TNSNAMES.ORA file on the BI server.  Ensure that your connection pool has the correct entries for your Oracle DB and that the TNSNAMES.ORA file on the BI server has the corresponding TNS entry.
You may instead use the short format for database connection string in the Data Source Name field e.g. dbservername:1521/orcl

Reference Metalink Note : 1296370.1

Saturday, May 4, 2013

ORA-27211: Failed to load Media Management Library


Issue :


Rman backup script fails with the below error. But strangely if we run the backup manually it went fine.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on c1 channel at 05/03/2013 15:05:35
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information: 2




Solution:


Set LD_LIBRARY_PATH in the backup script before starting RMAN block and make sure you have $ORACLE_HOME/lib before /usr/lib.