Bravery Family

Category: Oracle

  • eBusiness Autoconfig Customisation

    Why customise?

    Autoconfig Customisation has the potential to make life much easier for an Oracle Applications DBA.

    In short, it enables the creation of controlled templates that can make all of the things that have to be done after running autoconfig go away.

    For example, generally after you run adautocfg.sh you then have to remember to comment out the mobile setting in the jserv files, otherwise the java server doesn’t start up. Wouldn’t it be nice to change the template so that it know that these should be commented out already? Well, with autoconfig customisation you can!

    There are a couple of different approaches to this, the simplest involves just changing the template. The second involves adding a customisation value to the context file and then modifying the template to use that value.

    Before we start

    Before anything else is done, we must validate that the current configuration is good.
    To do this run the following command (because of the profile we use, it is in our path):

    adchkcfg.sh contextfile=$CONTEXT_FILE appspass=

    The output of this is as follows:

    The log file for this session is located at: /home/aicprj04/ICPRJ04/appl/admin/I
    CPRJ04_ictap41/log/07211026/adconfig.log

    AutoConfig is running in test mode and building diffs...

    AutoConfig will consider the custom templates if present.
            Using APPL_TOP location     : /home/aicprj04/ICPRJ04/appl
            Classpath                   : /home/aicprj04/ICPRJ04/comn/java/jdk1.6.0_
    02/jre/lib/rt.jar:/home/aicprj04/ICPRJ04/comn/java/jdk1.6.0_02/lib/dt.jar:/home/
    aicprj04/ICPRJ04/comn/java/jdk1.6.0_02/lib/tools.jar:/home/aicprj04/ICPRJ04/comn
    /java/appsborg2.zip:/home/aicprj04/ICPRJ04/comn/java

            Using Context file          : /home/aicprj04/ICPRJ04/appl/admin/ICPRJ04_ictap41/out/07211026/ICPRJ04_ictap41.xml

    Context Value Management will now update the test Context file

            Updating test Context file...COMPLETED

            [ Test mode ]
            No uploading of Context File and its templates to database.

    Testing templates from all of the product tops...
            Testing AD_TOP........COMPLETED
            Testing FND_TOP.......COMPLETED
            ...
            Testing CSD_TOP.......COMPLETED
            Testing IGC_TOP.......COMPLETED

    Differences text report is located at: /home/aicprj04/ICPRJ04/appl/admin/ICPRJ04_ictap41/out/07211026/cfgcheck.txt
     
            Generating Profile Option differences report...COMPLETED
            Generating File System differences report......COMPLETED
    Differences html report is located at: /home/aicprj04/ICPRJ04/appl/admin/ICPRJ04_ictap41/out/07211026/cfgcheck.html

    Differences Zip report is located at: /home/aicprj04/ICPRJ04/appl/admin/ICPRJ04_ictap41/out/07211026/ADXcfgcheck.zip

    AutoConfig completed successfully.

    There are a couple of outputs, but I find the most useful output is the zip file. I generally copy this over and extract it out, then use a web browser to view the set of html pages. These explain what differences there are between what you have and what should be there. Ideally there should be no differences.

    ADX Config Report is here.

    The real benefit of this, is that you can look at the differences very easily and decide if they are important or not. Once the differences have been reviewed, and you are satisfied that the autoconfig baseline is correct, then we can move on to performing the customisation.

    Customise a template

    This is the simplest type of customisation as there is not additional variables.
    You might follow this approach if there was a need to add a comment permanently to a file (for example to add entries to url_fw.conf).

    To do this find the file that is needs to be changed and run the following:

    adtmpreport.sh contextfile=$CONTEXT_FILE target=

    The output will be like the following:

    #########################################################################
              Generating Report .....                                       
    #########################################################################
    For details check log file: /home/aicprj01/ICPRJ01/appl/admin/ICPRJ01_ictap37/log/07211256.log

    The important detail is in the log file:

    =================================================================
    Starting Utility to Report on Templates and their  Targets  at Mon Jul 21 12:58:36 BST 2008
    Using ATTemplateReport.java version 115.7
      

    [ INFO_REPORT ]

    [FND_TOP]
    TEMPLATE FILE   : /home/aicprj01/ICPRJ01/appl/fnd/11.5.0/admin/template/url_fw.conf
    TARGET FILE     : /home/aicprj01/ICPRJ01/comn/conf/ICPRJ01_ictap37/iAS/Apache/Apache/conf/url_fw.conf

    This indicates that if an addition needs to be made to the file urlfw.conf, then the template file is in $FND_TOP/admin/template and is called url_fw.conf

    Apparently, not all templates can be customised. If there is the word LOCK in the application driver file, then it is not customisable.
    So, in the above case, we should look for LOCK in $FND_TOP/admin/driver/fndtmpl.drv

    If the file is customisable, then the following steps need to be done:

    1. Move to the directory that contains the source template, and make a directory called custom.
      For example:
      cd $FND_TOP/admin/template
      mkdir custom
    2. Copy the template into here, and then make the change to it.
    3. Then you should verify the customisation using the command adchkcfg.sh command.
    4. Finally, run autoconfig to make the template properly.

    Adding a context variable

    Again, determine what the template that is going to be added as above. Then using Oracle Application Manager, add a new custom entry in the context file.

    Then create a custom template exactly as above. When making your changes, refer to the new context value. For example to allow for controlling of mobile entries using context values I added the following to jserv_ux_ias1022.conf:

    %c_DisableMobile%ApJServGroupMount /mobile              balance://OACoreGroup/mobile

    The %c_DisableMobile% is the custom value.

    Again, validate that the file is going to work right, and then run autoconfig to make the change.

    Final Note

    This is all based on Metalink Note 270519.1

  • Time zones, Oracle Agents, Java, and the paths to madness…..

    I was attempting to re-install the Grid Control agents on two RHEL 6 servers today today. They’d had been upgraded from RHEL 5 to 6 and I wanted to see what needed to be done to re-attach them to Grid Control if we didn’t take the existing installation. I was having some issues with it (the agent wouldn’t start up – complained about “OMS decided to shutdown the agent because of the following reason sent from OMS: AGENT_TZ_MISMATCH”) After a bit of digging I started to find various levels of weirdness occurring. 

    I started off looking in Grid Control.

    If you look at the hosts table of grid control:

    select Timezone_region, count(*) from sysman.mgmt_targets group by Timezone_region order by 2;

    You see we have a nice interesting selection of TimeZones in use:

    +00:00				2
Europe/Isle_of_Man		6
+01:00				8
Europe/Guernsey			26
Europe/Lonon			49
Europe/Jersey			59
GB				80
Europe/Belfast			85
GB-Eire				367 

    That’s a wee bit scary – why on earth do all the servers think they are scattered over most of England (including the Channel Islands). We are based in London. Now I have checked a bit and at the OS level we set the timezone using:

    /etc/sysconfig/clock

    To:

    ZONE="Europe/London"

    And date is always sensible too:

    Wed Feb 20 15:39:01 GMT 2013

    So there I think there is some weirdness at work when the agent tries to work out the time zone in use. You can see what the agent thinks the zone is using:

    cd [agent home]/bin
./emctl config agent getTZ

    Depending on the server you run it on you get different answers:

    Europe/Guernsey
GB
GB-Eire
GB

    Ignore the apparent consistency here, it isn’t true…. I found a chunk of Java code in a Oracle Support note (330737.1) that seemed show the same results (I expanded it slightly to check another set of values):

    import java.util.*;
public class TestTZ
{
  public static void main(String[] args)
  {
     System.out.println("Local Timezone:    "+(TimeZone.getDefault()).getDisplayName());
     System.out.println("Local ID:          "+(TimeZone.getDefault()).getID());
     TimeZone tz = Calendar.getInstance().getTimeZone();
     System.out.println("Calendar TimeZone: "+tz.getDisplayName());
     System.out.println("Calendar ID:       "+tz.getID());
  }
 }

    The output looks the same as what the agent reports. But I couldn’t find a pattern to what was goin on. In desperation I moved to looking at the actual timezone files … Its safest note to ask why.. lets just say I was desperate and looking for any possible reason for this now.  I used the md5sum of localtime:

    $ md5sum /etc/localtime 410c65079e6d14f4eedf50c19bd073f8  /etc/localtime

    And then found zone files that had the same md5sum (just don’t ask why I started to do this):

    $ find /usr/share/zoneinfo -type f | xargs md5sum | grep 410c65079e6d14f4eedf50c19bd073f8
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/GB
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/Europe/Belfast
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/Europe/Guernsey
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/Europe/London
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/Europe/Jersey
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/Europe/Isle_of_Man
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/GB-Eire
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/posix/GB
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/posix/Europe/Belfast
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/posix/Europe/Guernsey
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/posix/Europe/London
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/posix/Europe/Jersey
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/posix/Europe/Isle_of_Man
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/posix/GB-Eire

    Its worth pointing out here that they are actually all just links to the same file (the file system inodes match).  Now when I looked at these I (finally) started to see some consistency. The file at the top of this list matches the zone reported back. So on the first server I see:

    -bash-4.1$ find /usr/share/zoneinfo -type f | xargs md5sum | grep 410c65079e6d14f4eedf50c19bd073f8
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/posix/Europe/Guernsey
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/posix/Europe/London
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/posix/Europe/Isle_of_Man
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/posix/Europe/Jersey
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/posix/Europe/Belfast
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/posix/GB
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/posix/GB-Eire
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/Europe/London
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/Europe/Isle_of_Man
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/Europe/Jersey
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/Europe/Belfast
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/GB
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/GB-Eire
-bash-4.1$ ./emctl config agent getTZ
Oracle Enterprise Manager 12c Cloud Control 12.1.0.1.0 
Copyright (c) 1996, 2012 Oracle Corporation. 
All rights reserved.
Europe/Guernsey  

    On a second server, I see:

    -bash-4.1$ find /usr/share/zoneinfo -type f | xargs md5sum | grep 410c65079e6d14f4eedf50c19bd073f8
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/GB
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/Europe/Jersey
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/Europe/Belfast
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/Europe/London
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/Europe/Isle_of_Man
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/Europe/Guernsey
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/posix/GB
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/posix/Europe/Jersey
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/posix/Europe/Belfast
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/posix/Europe/London
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/posix/Europe/Isle_of_Man
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/posix/Europe/Guernsey
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/posix/GB-Eire
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/GB-Eire
-bash-4.1$ ./emctl config agent getTZ
Oracle Enterprise Manager 12c Cloud Control 12.1.0.1.0 
Copyright (c) 1996, 2012 Oracle Corporation. 
All rights reserved.
GB

    For the OS, much like a database, the order that files are returned here is indeterminant. To make it determinant, and order would have to be imposed. It hasn’t been, and it looks like the method being used by the agent (and Java) to determine the timezone is equally unordered. An OS colleague and I then tried an experiment. He removed the top file (Europe/Guernsey) from the first system:

    -bash-4.1$ find /usr/share/zoneinfo -type f | xargs md5sum | grep 410c65079e6d14f4eedf50c19bd073f8
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/posix/Europe/London
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/posix/Europe/Isle_of_Man
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/posix/Europe/Jersey
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/posix/Europe/Belfast
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/posix/GB
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/posix/GB-Eire
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/Europe/London
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/Europe/Isle_of_Man
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/Europe/Jersey
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/Europe/Belfast
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/GB
410c65079e6d14f4eedf50c19bd073f8  /usr/share/zoneinfo/GB-Eire

    Once he did this, the zone reported shifted to the next line:

    -bash-4.1$ ./emctl config agent getTZ
Oracle Enterprise Manager 12c Cloud Control 12.1.0.1.0 
Copyright (c) 1996, 2012 Oracle Corporation. 
All rights reserved.
Europe/London
-bash-4.1$ java TestTZ
Local Timezone:    		Greenwich Mean Time
Local ID:          			Europe/London
Calendar TimeZone: 	Greenwich Mean Time
Calendar ID:       		Europe/London

    So what does all of this tell me (and therefore you)? We have a bit of a problem (especially with java). If (for whatever the reason) code wants to look up what the timezone is, it just gets the top row of the unsorted list. Which is ridiculously stupid. The way to avoid this it to impose our own Timezone. If we set the TZ environment value:

    export TZ=Europe/Belfast  

    It will then be picked up by java in preference:

    -bash-4.1$ java -cp ~ TestTZ
Local Timezone:    Greenwich Mean Time
Local ID:          Europe/Belfast Calendar
TimeZone: Greenwich Mean Time
Calendar ID:       Europe/Belfast
-bash-4.1$ ./emctl config agent getTZ
Oracle Enterprise Manager 12c Cloud Control 12.1.0.1.0 
Copyright (c) 1996, 2012 Oracle Corporation. 
All rights reserved.
Europe/Belfast

    So we can impose consistency again (although don’t use Belfast, that was to prove a point). This means two things: Going forward, we will have to add the setting of the TZ environment variable at the server level. This value will be Europe/London (so its consistent with /etc/sysconfig/clock). This means things running on new servers will know they are in Europe/London and going forward things will be fine. We also need to set the TZ value in .profile and .bash_profile for any Oracle Agent user especially where Java is being used (so WebLogic). This will then bring consistency back to the agents (and any other java installs). That said, for agents you will need to have the following run on them as they are fixed:

    ./emctl stop agent
./emctl resetTZ agent

    This sets the values in the properties file correctly. After that has happens the agent setup also needs changing in the database:

    alter session set current_schema=[oms user];
exec mgmt_target.set_agent_tzrgn('[oms server]:[port]','Europe/London');
commit;

    And then the agent can be started:

    ./emctl start agent

    All of this will make agents consistent in the system again, and should prevent weird answers about timezones from java going forward (which hopefully should prevent java date/time zone weirdness in the future).  Its important to note this is a general Java issue – any java app could show similar symptoms. Now if anyone wants me I’ll be in a darkened room bashing my head against a wall and mumbling about Oracle and my hatred of Java.

  • Using Oracle InstantClient 11g with PHP onto RHEL 5 with SELinux

    This is an example of using the Oracle InstantClient 11g with PHP.

    Installation InstantClient for PHP

    This is based on work from the past – but I appeared to have done the following:

    1. Installed InstantClient and Pear to manage the php installation.
      • sudo yum install php-pear
      • sudo rpm -ivh oracle-instantclient11.1-basic-11.1.0.7.0-1.i386.rpm
      • sudo rpm -Uvh oracle-instantclient11.1-devel-11.1.0.7.0-1.i386.rpm
    2. Update the library cache to include oracle:
      • sudo vi /etc/ld.so.conf.d/oracle.conf 
      • This file just contains the line: 
        /usr/lib/oracle/11.1/client/lib
    3. Used pecl to install the OCI client into PHP:
      • sudo pecl install oci8
    4. Updated PHP to then make use of the installed module
      • sudo vi /etc/php.d/oci8.ini
      • This file just contained
        ; Enable oci8 extension module
        extension=oci8.so
    5. I then restarted the web server.
      • sudo /etc/init.d/httpd restart

    Testing

    You can then test this by creating a php file with the following content:

    <?php      
$conn = oci_connect('[user]', '[password]', ' [//]host_name[:port][/service_name][:server_type][/instance_name]');       
$query = 'select table_name from user_tables';       
$stid = oci_parse($conn, $query);       
oci_execute($stid, OCI_DEFAULT);       
while ($row = oci_fetch_array($stid, OCI_ASSOC)) {       
  foreach ($row as $item) {       
    echo $item." ";       
  }       
  echo "&lt;br&gt;\n";       
}       
oci_free_statement($stid);       
oci_close($conn);       
?>

    Then use a browser to get to this web page, and it should show you the content of user_tables.

    Troubleshooting

    Then, if SELinux its not turned off then the following is needed, (but we don’t want to do it this way as it’s painful and make SELinux a bit pointless):

    sudo chcon -t textrel_shlib_t '/usr/lib/oracle/11.1/client/lib/libnnz11.so'       
sudo chcon -t textrel_shlib_t '/usr/lib/oracle/11.1/client/lib/libclntsh.so.11.1'       
sudo chcon -t textrel_shlib_t '/usr/lib/php/modules/oci8.so'       
sudo execstack -c /usr/lib/oracle/11.1/client/lib/libclntsh.so       
sudo execstack -c /usr/lib/oracle/11.1/client/lib/libclntsh.so.11.1       
sudo execstack -c /usr/lib/oracle/11.1/client/lib/libnnz11.so       
sudo chcon -u system_u '/usr/lib/php/modules/oci8.so'       
sudo execstack -s /usr/sbin/httpd       
sudo /usr/sbin/setsebool -P httpd_can_network_connect 1

  • Oracle eBusiness Submit Job – PL/SQL

    Again, a simple piece of code:

    This also runs Active User concurrent program:

    declare
      v_request_id number := -1;
    begin
      --fnd_global.apps_initialize(user_id, resp_id, appl_resp_id)
      fnd_global.apps_initialize(0, 20420, 1); --So sysadmin using responsibility System Administrator in application System Administration
      --v_request_id : fnd_request.submit_request(application short name,concurrent program short name,argument1 =>,  argument2 =>))
      v_request_id := fnd_request.submit_request(application=>'FND',program=>'FNDSCURS');
      commit;
      if v_request_id > 0
      then
        dbms_output.put_line('Successfully submitted: '||v_request_id);
      else
        dbms_output.put_line('Not Submitted');
      end if;
    end;
    /

    You can work out the application ids etc using these pieces of SQL:

    select * from fnd_application_tl
    where application_id = 1;
    select * from fnd_user
    where user_name = 'SYSADMIN';
    select * from fnd_responsibility_tl
    where responsibility_name = 'System Administrator';

    Easiest place to check the program short name etc is in eBusiness Suite itself.

    Once you get this working, you can increase the complexity. For example I took the above code and added calls to get users id:

    declare
  procedure fSubmitJob ( vSourceName varchar2, vTargetName varchar2)
  is
    v_request_id number := -1;
    v_source_id  number :=null;
    v_target_id  number :=null;
  begin
    select user_id
    into  v_source_id 
    from fnd_user
    where user_name = vSourceName; 
    select user_id
    into  v_target_id
    from fnd_user
    where user_name = vTargetName; 
    if ( v_source_id is not null and v_target_id is not null )
    then
      v_request_id := fnd_request.submit_request(application=>'<our app>',program=>'<our prog>',argument1 => v_target_id,  argument2 =>v_source_id);
      commit;
      if v_request_id > 0
      then
        dbms_output.put_line('Successfully submitted'); -–Removed rest of message
      else
        dbms_output.put_line('Failed submit'); -–Removed rest of message
      end if;
    else
      dbms_output.put_line('Missing ID'); -–Removed rest of message
    end if;
  end fSubmitJob;
begin
  fnd_global.apps_initialize(0, 20420, 1);
  fSubmitJob('<user>','<another user>');
  fSubmitJob('<user>','<another user>');
end;
/

    In this it was easier to code a function to find user ids and run a job each time, than do the equivalent code in shell script.

  • Oracle eBusiness Submit Job – Command line

    The below code is the simplest way of doing a job submission for a concurrent program in eBusiness Suite:

    CONCSUB <db user>/<db password> <user> <role> <application group> WAIT=<Y|N> CONCURRENT <application> <application short name> PROGRAM_NAME="<long name>"

    So, as an example take the following command:

    CONCSUB apps/<password> SYSADMIN "System Administrator" SYSADMIN WAIT=N CONCURRENT FND FNDSCURS PROGRAM_NAME='"Active Users"'

    What this command means is:

    1. to run a job as the user “SYSADMIN”,
    2. using the “System Administrator” responsibility
    3. from the “SYSADMIN” application group,
    4. without waiting for it to complete.
    5. it should submit a concurrent program
    6. from the application “FND”
    7. which has the short name of “FNDSCURS”
    8. and the program name of “Active Users”

    This approach is going to be good for shell scripts that need to schedule things easily. However, if some more complex is going on (e.g. database information needs to be merged into the parameter) then it’s not going to help much. In this case, it is much easier to do that totally using PL/SQL.

  • User Certificates in Oracle Wallet

    Since I spent an age getting Oracle Wallet working with User Certificates, so I decided to blog about it, collecting all that I needed into one place.

    Why do you need user certificates in an Oracle Wallet?

    We have several Oracle ApEx applications at work. A new one that is being proposed will need to access a web service on a remote server. To control access to the server, the vendor has decided to use client certificates. So this means that I need to get a client certificate into a place were the Oracle database can make use of it when the PL/SQL ApEx will use tries to make connection to the remote server.

    Solution Components

    The first problem is that client certificates aren’t supported until Oracle 11g, so we will have to do an Oracle database upgrade. For what it’s worth, 11.1.0.6 will support client certificates without further patching. 11.1.0.7 does support certificates, but will also need an additional patch to re-enable it. Please note, the patch required isn’t released for Microsoft platform at time of writing so if you plan on doing this on a Window based database instance you are restricted to 11.1.0.6

    You will also need an Oracle iAS installation for one of the commands that are used below. Version doesn’t seem to matter. In this case I am using the Linux version of iAS and it’s command line. There will be a slightly different command needed if Windows is used.

    You will also need openssl installed, as we need to rip the supplied wallet apart.

    We requested the user certificate, and the vendor supplied it (as a .pfx wallet file) along with the chain of authentication. I then used the wallet to connect to the server we would use in the end, and downloaded the certificate chain for the server as well. N.B. There is generally a password associated with the wallet. We will need this if there is one.

    Creating the new Oracle Wallet.

    I binary transferred the wallet file and both required certificate chains to the ApEx server (meaning the chain for User Certificate, and chain for web site). Then login to the ApEx server.

    We need to extract the private key:

    openssl pkcs12 -in [pfx file name] -nocerts -out private.key
    Enter Import Password: [password for end user]
    MAC verified OK
    Enter PEM pass phrase: [new key password]
    Verifying - Enter PEM pass phrase: [new key password]

    And then extract the certificate:

    openssl pkcs12 -in [pfx file name] -clcerts -nokeys -out user_certificate.crt
    Enter Import Password: [password for end user]
    MAC verified OK

    Now set the Oracle home to be an iAS home and create the wallet using (need to make directory first):

    mkdir –p /etc/ORACLE/WALLETS/[user]
    $ORACLE_HOME/Apache/Apache/bin/ssl2ossl -cert user_certificate.crt -key private.key -chain [user certificate chain file] –wallet /etc/ORACLE/WALLETS/[user] -ssowallet yes
    Enter PEM pass phrase:[new key password]
    Enter wallet password:[new wallet password]
    Verifying password - Enter wallet password:[new wallet password]
    SUCCESS

    Keep a note of the new wallet password, you will need this now and forever more!
    Don’t delete the files created using openssl. We will need one again shortly.

    Open the new Oracle wallet using iAS Wallet Manager:

    $ORACLE_HOME/bin/owm

    Make sure it looks correct (mainly that the certificate is there and is ready, and that the user certificate chain is there).

    Then save it and exit. The wallet has been created, and is useable. However, we will probably use the 11g Wallet Manager associated with the database in the future and that means we need to do some extra work.

    Convert Oracle Wallet to an Oracle 11g Wallet

    Set Oracle home to DB home and start the database Wallet Manager:

    $ORACLE_HOME/bin/owm

    You will notice that thinks don’t look correct anymore. The steps to resolve this are:

    1. Re-import user certificate.
    2. Save.
    3. Remove user certificate
      Just the certificate, not the request for it.
    4. Save
      You should see that the ensure the blank trusted certificate entry has gone and the user certificate is back to requested.
    5. Re-import user certificate.

    The wallet will now look correct again (user certificate is ready, trusted certificates are the ones in the chain to the user certificate.

    Now import trust chain for server as normal (either one at a time, or as a single combined file).

    Finally, save wallet. It should now contain all the certificates that are needed.

    Testing the Oracle Wallet holding the User Certificate

    Please remember, depending on who the database is actually running as, you may need to change the files in the wallet directory to open to the group, rather than just the user (i.e. this is the case if you use a shared software installation).

    Test by running this as sys (to avoid ACL issues):

    select utl_http.request('[secure url]'
,null
,'file:[path to wallet directory]'
,'[password for wallet]') a
from dual
/

    If it works, you won’t get any errors and you’ll see the http code that is returned by the secure web page.

    Troubleshooting

    All I can really suggest for this is some things I have seen.

    1. Check the certificate chain for the server is really correct
    2. Check the permissions of the wallet files
    3. 11g Database ACLs (Oracle have embedded a firewall in the database… if you don’t open it for the right user, you’ll see nothing).
    4. Obviously, that you are using the correct password.

  • Configure BIEE BI Publisher Scheduler with MySQL

    I was keen to use the BI Publisher Scheduler and configure it to use a MySQL database. The instructions for this imply that you need to hunt around and find out how to register the JDBC library for MySQL. The reality with the Oracle Application Server or standalone OC4J is much simpler than that.

    1. Put the JDBC driver file in this directory: \j2ee\home\applib
    2. Restart the OC4J or Oracle iAS instance.
    3. When logged in now, you should be able to test a MySQL connection and find that it works properly (as show below):
      BIEE settings
    4. You can now create the schema, by clicking the install schema button, and finally click the apply button to make the scheduler start working.
    5. Restart the Oracle OC4J or iAS.

    This time messages will appear in the log that indicate that the Publisher Scheduler has started up:

     - Job execution threads will use class loader of thread: OC4J Launcher
 - Quartz Scheduler v.1.5.1 created.
 - Using thread monitor-based data access locking (synchronization).
 - Removed 0 Volatile Trigger(s).
 - Removed 0 Volatile Job(s).
 - JobStoreTX initialized.
 - Quartz scheduler 'BIPublisherScheduler' initialized from the specified file : 'C:\OracleBI\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\xdo\cache\xmlp42298771.tmp'
 - Quartz scheduler version: 1.5.1
 - Freed 0 triggers from 'acquired' / 'blocked' state.
 - Recovering 0 jobs that were in-progress at the time of the last shut-down.
 - Recovery complete.
 - Removed 0 'complete' triggers.
 - Removed 0 stale fired job entries.
 - Scheduler BIPublisherScheduler_$_NON_CLUSTERED started.

    And that’s it.

    BI Publisher’s Scheduler should now be configured against MySQL and will now work. This means that jobs should now be able to be created and will run.

  • Configure BIEE Scheduler with MySQL

    I am using a Windows PC to do this, so the instructions may not directly translate to UNIX/Linux.

    1. Create a database for BIEE to use.
      Create the objects required using this script (this is a tweaked version of the standard SAJOBS.DB2.sql script):
      CREATE TABLE S_NQ_JOB (
          JOB_ID decimal(10,0) NOT Null,
          NAME varchar (50)  ,
          DESC_TEXT varchar (255)  ,
          SCRIPT_TYPE varchar (20)  ,
          SCRIPT varchar (255)  ,
          MAX_RUNTIME_MS decimal(10,0)  ,
          USER_ID varchar (128)  ,
          NEXT_RUNTIME_TS timestamp,
          LAST_RUNTIME_TS timestamp,
          MAX_CNCURRENT_INST decimal(10,0)  ,
          BEGIN_YEAR decimal(10,0)  ,
          BEGIN_MONTH decimal(10,0)  ,
          BEGIN_DAY decimal(10,0)  ,
          END_YEAR decimal(10,0)  ,
          END_MONTH decimal(10,0)  ,
          END_DAY decimal(10,0)  ,
          START_HOUR decimal(10,0)  ,
          START_MINUTE decimal(10,0)  ,
          END_HOUR decimal(10,0)  ,
          END_MINUTE decimal(10,0)  ,
          INTERVAL_MINUTE decimal(10,0)  ,
          TRIGGER_TYPE decimal(10,0)  ,
          TRIGGER_DAY_INT decimal(10,0)  ,
          TRIGGER_WEEK_INT decimal(10,0)  ,
          TRIGGER_RANGE_DOW decimal(10,0)  ,
          TRIGGER_RANGE_DOM decimal(10,0)  ,
          TRIGGER_RANGE_MTH decimal(10,0)  ,
          TRIG_RANGE_DAY_OCC decimal(10,0)  ,
          DELETE_DONE_FLG decimal(10,0) NOT Null,
          DISABLE_FLG decimal(10,0) NOT Null,
          HAS_END_DT_FLG decimal(10,0) NOT Null,
          EXEC_WHEN_MISS_FLG decimal(10,0) NOT Null,
          DEL_SCPT_DONE_FLG decimal(10,0) NOT Null,
          PATH_IN_SCPT_FLG decimal(10,0) NOT Null,
          ISUSER_SCPT_FLG decimal(10,0) NOT Null,
          DELETE_FLG decimal(10,0) NOT Null,
          TZ_NAME varchar(100)
      );
      CREATE INDEX S_NQ_JOB_M1 ON S_NQ_JOB (NEXT_RUNTIME_TS) ;
      CREATE INDEX S_NQ_JOB_M2 ON S_NQ_JOB (USER_ID);
      CREATE UNIQUE INDEX S_NQ_JOB_P1 ON S_NQ_JOB (JOB_ID);CREATE TABLE S_NQ_JOB_PARAM (
          JOB_ID decimal(10,0) NOT NULL,
          RELATIVE_ORDER decimal(10,0) NOT NULL ,
          JOB_PARAM varchar (255),
          DELETE_FLG decimal(10,0) NOT NULL
      );
      CREATE TABLE S_NQ_INSTANCE (
          JOB_ID decimal(10,0) NOT NULL,
          INSTANCE_ID decimal(20,0) NOT NULL,
          STATUS decimal(10,0),
          BEGIN_TS timestamp,
          END_TS timestamp,
          EXIT_CODE decimal(10,0) ,
          DELETE_FLG decimal(10,0) NOT NULL,
          ERROR_MSG_FLG decimal(10,0) NOT NULL
      );
      CREATE UNIQUE INDEX S_NQ_INSTANCE_U1 ON S_NQ_INSTANCE (JOB_ID, INSTANCE_ID);
      CREATE INDEX S_NQ_INSTANCE_M1 ON S_NQ_INSTANCE (END_TS, STATUS, INSTANCE_ID ASC) ;
      CREATE INDEX S_NQ_INSTANCE_M2 ON S_NQ_INSTANCE (BEGIN_TS, STATUS, INSTANCE_ID ASC) ;
      CREATE INDEX S_NQ_INSTANCE_M3 ON S_NQ_INSTANCE (INSTANCE_ID ASC, DELETE_FLG ASC) ;
      CREATE INDEX S_NQ_INSTANCE_M4 ON S_NQ_INSTANCE (JOB_ID, INSTANCE_ID, STATUS, DELETE_FLG) ;
      CREATE INDEX S_NQ_INSTANCE_M5 ON S_NQ_INSTANCE (STATUS ASC, DELETE_FLG ASC) ;
      CREATE TABLE S_NQ_ERR_MSG (
          JOB_ID decimal(10,0) NOT NULL,
          INSTANCE_ID decimal(20,0) NOT NULL,
          RELATIVE_ORDER decimal(10,0) NOT NULL,
          ERROR_MSG_TEXT varchar (255),
          DELETE_FLG decimal(10,0) NOT NULL
      );
      CREATE UNIQUE INDEX S_NQ_ERR_MSG_U1 ON S_NQ_ERR_MSG (JOB_ID, INSTANCE_ID, RELATIVE_ORDER);
      CREATE INDEX S_NQ_ERR_MSG_F1 ON S_NQ_ERR_MSG (INSTANCE_ID ASC)  ;
    2. Create a user, and grant it access to the tables.
    3. Create an ODBC Connection to the MySQL Database, and check it works.
    4. Now start the scheduler configuration tool.
      Tell it that the connection type is ODBC and let it use the default Call Interface. Then enter details of the Data source created in step three, and include the user name and password.
      So, using the Windows OS, I have set it as follows:
      BIEE Scheduler Connection Settings
      Can also do this in the schconfig command line based tool, so I would expect this to work the same way under Linux.
    5. Then start the Scheduler service.
    6. On windows navigate to this directory:
      c:\OracleBIData\web\config
    7. Then run the following:
      \OracleBI\web\bin\cryptotools.exe credstore -add -infile credentialstore.xml
      and input the following bold values:
      >Credential Alias: admin
      >Username: Administrator
      >Password: *************
      >Do you want to encrypt the password? y/n (y): n
      >File "credentialstore.xml" exists. Do you want to overwrite it? y/n (y): y
    8. Now must edit instanceconfig.xml file, as modification by MBean didn’t work correctly.
      edit instanceconfig.xml
      Now, look for </ServerInstance> entry at the end of the file, and immediately before it, insert the following (path needs to be adapted appropriately for installation directory.
      <CredentialStore>
      <CredentialStorage type="file" path="c:/OracleBIData/web/config/credentialstore.xml"/>
      </CredentialStore>
      Save the file. Credentials have now been saved correctly and will be used by Presentation Server.
    9. Restart the presentation server service.
    10. You should now be able to save a iBot and not get a error reported by the application server.

  • Installing Oracle InstantClient on Linux

    This was very quick and easy to to actually!

    I transferred the 11i client (basic and the SQL*Plus bit) to my central server, and extracted out the software.
    Then I copied the executables to my bin directory, and the libraries to the lib directory.
    Finally, I moved glogin.sql to my sql directory.
    All I then had to do was amend the profile to set an LD_LIBRARY_PATH variable.

    The only thing left was to ensure SQLPATH was set, and then set TNS_ADMIN variable to where I am housing the tnsadmin.ora file.

    Hey presto! an 11i client install which can connect everywhere I tell it that it can get to.