ODI: Purging OLD Sessions

One common administrative task that I find myself doing when I realize that my ODI logs are growing fairly large is purging old sessions from the log. The steps are fairly straightforward as follows:

 

  1. Login to your ODI Studio client
  2. To to the Operator View
  3. On the top right corner of your navigation pane, expand the menu and select purge log…

    image

  4. On the Purge Log screen you can select which old sessions to remove by date, agent, context, status, user and session name

    image

  5. Once you have set parameters as desired click on OK and the ODI session logs will be purged accordingly

 

Related:

How To: Manage your Oracle patch deployment life cycle using Oracle Support Patch Plans

Introduction

 

As part of my writing I often try to document and share best practices I develop on my day to day work, this one relates to formalizing the patch deployment process for your oracle environments. This approach is developed for organizations that have formal release cycles and have established procedures to take patches through test life cycles that; at a minimum, begin in a develop environment, followed by integration testing in a QA and culminate when patches are promoted to production.

I will try to keep this post brief so, at a high level, I have found that the best way to manage patches is to use the Oracle support portal patch & upgrades functionality to create a patch plan for each environment in the life cycle for either each major release or at least each quarter. This process is always initiated by the need to apply a patch so whenever no patches are necessary during a release or quarter no patch plans are created.

The two main benefits of this approach is (1) that it brings transparency into which patches have been approved for each environment, (2) it is a straight forward process that does not carry a lot of overhead. The way patches make it to a patch plan is when a project manager requests a patch to be applied or promoted to each environment in your life cycle, this in turn is monitored using standard project management mechanisms such as issue, task and test management.

 

Implementation

Creating your first patch plan is very simple, just take your first requested patch through the process outlined below.

 

  1. Login to http://support.oracle.com
  2. Click on the Patches & Updates tab
  3. Locate the appropriate version of your patch by specifying a patch number and operating system on the patch search interface

    Locate the appropriate version of your patch by specifying a patch number and operating system on the patch search interface

  4. Locate your patch on the search results screen and click on Add to Plan > Add to new …

    Locate your patch on the search results screen and click on Add to Plan > Add to new ...

  5. Locate the a valid target application server or host name using the search box
  6. Provide a patch plan name using your company’s naming standard and click create plan

    An example naming convention I have used in the past, this particular one allows system administrators to sort by date and to manage patch plans by product:

    – – – approved patches

    Provide a patch plan name using your company's naming standard and click create plan

  7. To add any additional requested patches to your plan go back to Patches & Updatesand select your plan from the Plans list and click on the Add Patch… button.

Having this patching plan makes it easy to manage patch deployment through your environments. As for the actual deployment of each patch, I am a command line geek and like the ability to make sure that each individual patch deployment works correctly by running OPatch for each individual package.

If you find this post useful please or Share our site!

Reference:

As part of my writing I often try to document and share best practices I develop on my day to day work, this one relates to formalizing the patch deployment process for your oracle environments …

Did you run out of time in Oracle Business Intelligence Applications (OBIA)?

There’s nothing worse than this right? I’m with you man (sister)!

All right in all due seriousness this can be an awkward situation where you come in Monday morning and your business stakeholders look deeply angry since none of their reports look right and they need to finish the close of the month/year/quarter.

Anyways, in both OBIA 7.9.x and 11g this is most likely due to the fact that the variables that control the generation of calendar tables are set to a date now in the past.

 

OBIA 11.1.1.7 Solution

  1. Log in to OBIA Configuration Manager (BIACM)
  2. Go to Manage Data Load Parameters
  3. Look for Configure Time Dimension > Gregorian Date Range End
  4. Change the END_DATE parameter to a date far far in the future
  5. Put on a contrite face and let your users know this will be fixed next time your load runs

OBIA 7.9.x Solution

The same general steps would need to be applied on your DAC client to fix this issue in versions of OBIA that use Informatica Power Center

  1. Open your DAC console client
  2. Navitate to Design > Tasks
  3. Look up the SIL_DayDimension task
  4. Look up the Parameters tab on the bottom panel
  5. Change the $$END_DATE parameter to a future date
  6. Same deal, contrite face, deep breath and break in the news that this won’t be fixed until tomorrow morning

 

Related:

Error when importing work repository in ODI Studio (java.lang.OutOfMemoryError: Java heap space)

INTRO

 

I am having an issue with the work repository in one of my environments this week to the point where I had to rebuild it. After dropping and recreating the schema I am running on a java heap space error.

clip_image002

 

SOLUTION

 

In my case the issue went away with the following steps:

  1. Unpack the repository content ZIP file I was importing into an uncompressed folder
  2. Up the MaxPermSize parameter on my ODI\client\odi\bin\odi.conf filefrom 512M to 1024M
    image

 

FULL ERROR MESSAGE

 

clip_image002

java.lang.OutOfMemoryError: Java heap space

                at java.lang.Class.getDeclaredMethods0(Native Method)

                at java.lang.Class.privateGetDeclaredMethods(Class.java:2427)

                at java.lang.Class.getDeclaredMethod(Class.java:1935)

                at com.sunopsis.tools.core.SnpsTools.getMethodFromHierarchy(SnpsTools.java:370)

                at com.sunopsis.tools.core.SnpsTools.getMethodFromHierarchy(SnpsTools.java:392)

                at com.sunopsis.tools.xml.SnpsXmlObjectParser.processValue(SnpsXmlObjectParser.java:611)

                at com.sunopsis.tools.xml.SnpsXmlObjectParser.endElement(SnpsXmlObjectParser.java:270)

                at oracle.xml.parser.v2.NonValidatingParser.parseElement(NonValidatingParser.java:1588)

                at oracle.xml.parser.v2.NonValidatingParser.parseRootElement(NonValidatingParser.java:442)

                at oracle.xml.parser.v2.NonValidatingParser.parseDocument(NonValidatingParser.java:388)

                at oracle.xml.parser.v2.XMLParser.parse(XMLParser.java:232)

                at com.sunopsis.tools.xml.SnpsXmlObjectParser.parseXmlFile(SnpsXmlObjectParser.java:390)

                at com.sunopsis.tools.xml.SnpsXmlObjectParser.parseXmlFile(SnpsXmlObjectParser.java:337)

                at com.sunopsis.tools.xml.SnpsXmlObjectParser.parseXmlFile(SnpsXmlObjectParser.java:347)

                at com.sunopsis.dwg.DwgObject.doImport(DwgObject.java:6747)

                at com.sunopsis.dwg.DwgObject.doImport(DwgObject.java:6620)

                at com.sunopsis.dwg.DwgObject.doImport(DwgObject.java:6578)

                at com.sunopsis.repository.manager.RepositoryManager.importObjectsUsingDoImport(RepositoryManager.java:5918)

                at com.sunopsis.repository.manager.RepositoryManager.treatObjectListGeneral(RepositoryManager.java:3985)

                at com.sunopsis.repository.manager.RepositoryManager.workRepositoryImport(RepositoryManager.java:4506)

                at com.sunopsis.repository.manager.RepositoryManager.access$7(RepositoryManager.java:4395)

                at com.sunopsis.repository.manager.RepositoryManager$2.doAction(RepositoryManager.java:4369)

                at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)

                at oracle.odi.core.persistence.dwgobject.TransactionalDwgObjectTemplate.execute(TransactionalDwgObjectTemplate.java:64)

                at com.sunopsis.repository.manager.RepositoryManager.internalWorkRepositoryImportWithCommit(RepositoryManager.java:4357)

                at com.sunopsis.repository.manager.RepositoryManager.workRepositoryImport(RepositoryManager.java:4661)

                at com.sunopsis.repository.manager.RepositoryManager.workRepositoryImportFromZipFile(RepositoryManager.java:4814)

                at com.sunopsis.repository.manager.RepositoryManager.workRepositoryImportFromZipFileWithCommit(RepositoryManager.java:4884)

                at com.sunopsis.repository.manager.RepositoryManager.workRepositoryImportFromZipFileWithCommit(RepositoryManager.java:4939)

                at com.sunopsis.graphical.dialog.SnpsDialogImportWork$1.run(SnpsDialogImportWork.java:155)

                at oracle.ide.dialogs.ProgressBar.run(ProgressBar.java:655)

                at java.lang.Thread.run(Thread.java:662)

 

RELATED

 

Other related issues I found when researching the solution are:

 

 

 

 

Sqoop: Create-hive-table tool

Apache Sqoop Logo

Tool: CREATE-HIVE-TABLE

–table <source_table> : The name of the table on the originating database

— hive-table <target table> :  The name of the table to be created written to into

 

–enclosed-by

–escaped-by

–fields-terminated-by

–lines-terminated-by

–mysql-delimiters : ( , ) for fields, ( \n ) for lines, enclosed-by ( ‘ ), escaped-by ( \ )

 

Notes:

* The tool will fail if the target table exists

Configuring OBIEE to run as a Windows Service

Introduction

One key step to configuring an enterprise deployment of Oracle Business Intelligence is to be setup your services to run in the background as Windows services and start automatically with your server.  By default the installer will create windows services for Oracle Process Manager (OPMN) and the Weblogic Node Manager, this leaves us with the need to configure services for the Weblogic AdminServer and the BI Managed Server.

Pre-requisites

  • Verify boot.properties files exist for both Weblogic Servers

    AdminServer:
    %mw_home%\user_projects\domains\bifoundation_domain\servers\AdminServer\security\boot.properties

    BI Managed Server:
    %mw_home%\user_projects\domains\bifoundation_domain\servers\bi_server1\security\boot.properties

  • Define the MW_HOME Variable
  • Edit %MW_HOME%\wlserver_10.3\server\bin\installSvc.cmd to redirect standard output to a file and to set the service name prefix to “Oracle_”

    Log Syntax:
    -log:”%MW_HOME%\user_projects\domains\bifoundation_domain\servers\%SERVER_NAME%-stdout.txt”

    Example Customization:

    set MW_HOME=%WL_HOME%\..\

    rem *** Install the service”
    %WL_HOME%\server\bin\beasvc” -install -svcname:”Oracle_%DOMAIN_NAME%_%SERVER_NAME%” -javahome:”%JAVA_HOME%” -execdir:”%USERDOMAIN_HOME%” -maxconnectretries:”%MAX_CONNECT_RETRIES%” -host:”%HOST%” -port:”%PORT%” -extrapath:”%EXTRAPATH%” -password:”%WLS_PW%” -cmdline:%CMDLINE% -log:”%MW_HOME%\user_projects\domains\bifoundation_domain\servers\%SERVER_NAME%-stdout.txt”

    Note: Make sure you replace beasvc and the subsequent space with Oracle_ on the section for -svcname

  • Change the environment script

    %MW_HOME%\user_projects\domains\bifoundation_domain\bin\setOBIDomainEnv.cmd

  • Edit %MW_HOME%\wlserver_10.3\server\bin\installSvc.cmd to ensure the correct Java memory arguments are utilized by your windows service

    Old Code:
    call “%WL_HOME%\common\bin\commEnv.cmd”
    New Code:
    call “%WL_HOME%\..\user_projects\domains\bifoundation_domain\bin\setOBIDomainEnv.cmd”

  • Edit %MW_HOME%\wlserver_10.3\server\bin\installSvc.cmd to implement a workaround for the Windows limitation of the maximum length of the command line being 2KB
    • Locate the two instances where the script sets the value of the CMDLINE variable
    • Add the code below before each instance, this code will output the current value of CLASSPATH to a text file

      REM –
      REM output the class path to text file and change reference to file on CMDLINE variable
      REM this is a workaround to a limit on windows command line to 2KB
      echo %CLASSPATH% > %WL_HOME%\server\bin\classpath.txt

    • Replace the class path variable reference \”%CLASSPATH%\” with@%WL_HOME%\server\bin\classpath.txt as depicted in the example below

      set CMDLINE=”%JAVA_VM% %MEM_ARGS% %JAVA_OPTIONS% -classpath @%WL_HOME%\server\bin\classpath.txt -Dweblogic.Name=%SERVER_NAME% -Dweblogic.management.username=%WLS_USER% -Dweblogic.ProductionModeEnabled=%PRODUCTION_MODE% -Djava.security.policy=\”%WL_HOME%\server\lib\weblogic.policy\” weblogic.Server”

  • Read the Microsoft Support article on specifying the startup order of Windows Services
  • Using regedit, add the one group for each of the OBIEE processes to be started at the end of the list entry at:

    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\ServiceGroupOrder

    Example groups:

    OBI Node Manager
    OBI AdminServer
    OBI Managed Server
    OBI OPMN

    This will sequence the startup of your services based on group

  • Note down the names of the OPMN and Node Manager Services from the registry

    Registry Location:

    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\

    Sample Service Names:

    OracleProcessManager_instance1
    Oracle WebLogic NodeManager (d_obi_mw_wlserver_10.3)

  • For each of the two services above add a string value (right click the registry folder and follow New > String Value) named Group and provide the corresponding group value for each service (ie. HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Oracle WebLogic NodeManager (d_obi_mw_wlserver_10.3)\Group=OBI Node Manager).

    This will work along with the ServiceGroupOrder configuration to ensure the startup order of your services

Implementation


AdminServer Service

  • Create a new script named %MW_HOME%\wlserver_10.3\server\bin\installAdminServer_svc.cmd and using the code below:

    SETLOCAL
    @echo off
    set MW_HOME=d:\obi_mw
    set DOMAIN_NAME=bifoundation_domain
    set USERDOMAIN_HOME=%MW_HOME%\user_projects\domains\%DOMAIN_NAME%
    set SERVER_NAME=AdminServer
    set PRODUCTION_MODE=true
    call “%MW_HOME%\wlserver_10.3\server\bin\installSvc.cmd”
    ENDLOCAL

  • Run the installAdminServer_svc.cmd script
  • Using regedit, verify that a service named Oracle_bifoundation_domain_AdminServer now exists under the following location

    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\

  • Right click your service folder and follow the context menus New > String Value to add a new entry in your service folder, call your new string value Group and double click on it to add OBI AdminServer as a value. This will work in tandem with the ServiceGroupOrder configuration to ensure the startup order of your services.
  • Right click your service folder and follow the context menus New > Multi-String Value to add a new entry in your service folder, call your new string value DependOnService and double click on it to add the node manager service as a dependency, the node manager service must match the service listing you noted down as part of the pre-requisite preparation steps (ie. Oracle WebLogic NodeManager (d_obi_mw_wlserver_10.3)). Using this dependency value will cause Windows to verify that dependent services have been started before attempting to start this service.

BI Managed Server Service

  • Create a new script named %MW_HOME%\wlserver_10.3\server\bin\installbi_server1_svc.cmd and using the code below:

    SETLOCAL
    @echo off
    set MW_HOME=d:\obi_mw
    set DOMAIN_NAME=bifoundation_domain
    set USERDOMAIN_HOME=%MW_HOME%\user_projects\domains\%DOMAIN_NAME%
    set SERVER_NAME=bi_server1
    set PRODUCTION_MODE=true
    set ADMIN_URL=http://localhost:7001
    call “%MW_HOME%\wlserver_10.3\server\bin\installSvc.cmd”
    ENDLOCAL

  • Run the installbi_server1_svc.cmd script
  • Using regedit, verify that a service named Oracle_bifoundation_domain_bi_server1 now exists under the following location

    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\

  • Right click your service folder and follow the context menus New > String Value to add a new entry in your service folder, call your new string value Group and double click on it to add OBI Managed Server as a value. This will work in tandem with the ServiceGroupOrder configuration to ensure the startup order of your services.
  • Right click your service folder and follow the context menus New > Multi-String Value to add a new entry in your service folder, call your new string value DependOnService and double click on it to add the node manager service and Weblogic AdminServer services as a dependencies (ie.Oracle_bifoundation_domain_AdminServer ). Using this dependency value will cause Windows to verify that dependent services have been started before attempting to start this service.
  • Right click the service folder for OPMN and follow the context menus New > Multi-String Value to add a new entry in your service folder, call your new string value DependOnService and double click on it to add the node manager service and Weblogic AdminServer  and your new BI Managed Server services as a dependencies (ie. Oracle_bifoundation_domain_bi_server1). Using this dependency value will cause Windows to verify that dependent services have been started before attempting to start this service.
  • On the Administration Tools > Services application verify that all of the following services are configured to start automatically and, optionally, configure what actions are taking on failure starting each service.

    Oracle WebLogic NodeManager
    Oracle_bifoundation_domain_AdminServer
    Oracle_bifoundation_domain_bi_server1
    Oracle Process Manager (instance 1)

Setup Validation

  • Restart your windows server and monitor the order in which services are started
  • If you see issues with OPMN starting at the same time as your WebLogic servers you might need to try setting the AdminServer, BI Managed Server and OPMN  services to start manually and using the code below to create batch command file that is executed by a scheduled task each time the computer starts:

    net start Oracle_bifoundation_domain_AdminServer
    timeout 300
    net start Oracle_bifoundation_domain_bi_server1
    timeout 300
    net start OracleProcessManager_instance1

    This script would use the timeout DOS command to institute a five minute wait between each of the OBI services being started.

One key step to configuring an enterprise deployment of Oracle Business Intelligence is to be setup your services to run in the background as Windows services and start automatically with your server.  By default the installer will create windows services for Oracle Process Manager (OPMN) and the Weblogic Node Manager, this leaves us with the need to configure services for the Weblogic AdminServer and the BI Managed Server.

Error: Unable to access Oracle Data Integrator repository. You will not be able generate or execute load plans.

Oracle Data Integrator (ODI) Logo

Upon logging to the Oracle Business Intelligence Application’s Configuration Manager system I was greeted with an error message stating the following: “Unable to access Oracle Data Integrator repository. You will not be able generate or execute load plans.”

 

Upon some triage I was able to determine a couple of possible root causes to my issue.

 

  1. Not using the BIAdmin user created during the install process, or, the BIAdmin user account was not created.
  2. Current session was initiated with a set of credentials that haven’t been granted the BIA_ADMINISTRATOR_DUTY  role on weblogic security
  3. Additional roles are missing or not assigned to the credentials initiating the current session

 

After reviewing all possible options and confirming I am using a valid account with the proper roles and permissions I asked my system administrator to restart the server, this fixed my issue. I should have started there but at least I came out of the experience with a better understanding of the roles that control security in my installation of OBIA 11.1.1.7.0.

 

Last resort, if all else fails:

* Make sure after you have regenerated and moved the security files that the file odi.conf is updated to refer to jps-config-jse.xml.

How to define an index on a source or target table in ODI

Oracle Data Integrator (ODI) Logo

 

 

 

 

 

 

 

 

 

 

The topic of indexes in Oracle Data Integrator (ODI) is an easy one once you learn your way around in ODI studio. Indexes are defined under Design > Models > [your model] > [Table] > [Constraints]. They are independent of the column definitions for the most part although they do reference valid column definitions.

 

  • The first step to create metadata for an index is to add a new constraint and name it:image

  • On the Columns tab define the list of columns to be indexedimage

  • Ensure that the index is active and marked for creation on the database on the Control tabimage

  • Review all additional options on the Flexfields tab, for some of the options such as Index Type you do need to manually type your selection.
    image

 

 

After you have defined an index on metadata you need to run a new load for the index to be created on the database.

 

My Tools:

Oracle Business Intelligence Applications (OBIA) 11.1.1.7.0

Oracle Data Integrator 11g (ODI Studio, ODI Server, no clustering)

 

Customizing your DOS prompt

Being back on a windows shop I find the DOS prompt sort of gets in my way, I have customized it so that it will display my current path and then let met type on a new line with this command:

PROMPT Ignacio’s rocking at $P$G$_

The different formatting options for the command are:

$A & (Ampersand)
$B | (pipe)
$C ( (Left parenthesis)
$D Current date
$E Escape code (ASCII code 27)
$F ) (Right parenthesis)
$G > (greater-than sign)
$H Backspace (erases previous character)
$L < (less-than sign)
$N Current drive
$P Current drive and path
$Q = (equal sign)
$S (space)
$T Current time
$V Windows XP version number
$_ Carriage return and linefeed
$$ $ (dollar sign)

To make your changes permanent go to My Computer > Properties > Advanced > Environment Variables and create a new variable called PROMPT.

If you find this post useful please or Share our site!

Being back on a windows shop I find the DOS prompt sort of gets in my way, I have customized it so that it will display my current path and then let met type on a new line with this command…

How to Make sure an index is defined as unique in odi

Oracle Data Integrator

Indexes are defined as constraints on the Model view in ODI Studio. To make a field unique follow one of the two alternatives listed below.

 

PRIMARY KEY

 

Define the Index as a Primary Key constraint object on the Models area within the design view

Description view on primary key index definition in ODI

 

Add the unique columns on the Columns tab

image

 

 

In the Control tab make sure the constraint is active and marked to be defined in the database for both flow and static control checkboxes

 

 

 

image

 

Review that the correct settings are configured in the Flexfields tab

Flexfields view on Primary Key Index in ODI

 

ALTERNATE KEY

 

Define the constraint object for your table as an alternate key in the ODI models area

Defining a unique index in Oracle ODI

 

Add the unique columns on the Columns tab

image

 

In the Control tab make sure the constraint is active and marked to be defined in the database for both flow and static control checkboxes

 

image

Review that the correct settings are configured in the Flexfields tab

Flexfields view on unique index in ODI