Bravery Family

Author: GoldKeeperSnr

  • Getting annoying error with phpMyAdmin

    I upgraded phpMyAdmin from 4.1.2 from 4.2.6. Having done this, I copied in the config.inc.php file and started everything up.

    Logged in and I get “You should upgrade to MySQL 5.5.0 or later.”.

    Well yes, I should. But I can’t (the mysql version is attached to the NAS – so i can’t really change that very easily). But I want to use phpMyAdmin. Checked on phpMyAdmin site, and it says “supported only on MySQL 5.5”.

    Anything after here is at your own risk. I have not comprehensively tested this, nor have I any intention of doing that.

    Right, so with that out of the way, After digging about for a little bit I found that the error is being generated by:

    libraries/common.inc.php

    If you open this up, there is a line:

            if (PMA_MYSQL_INT_VERSION < 50500) {

    Change it to say:

            if (PMA_MYSQL_INT_VERSION < 50100) {

    And now “hey presto!” it won’t complain anymore.

    Although we SHOULD all really upgrade to MySQL 5.5, or MariaDB, or move to PostgreSQL

    Again, if you do this and it breaks things its not my fault.
    I wanted to make it so i could see tables in MySQL again. I rarely user phpMyAdmin for much more than that anyway.

  • Configuring GIT

    First up, I found most of my information on this here:

    Seth Robertson – Git On The Web

    The one thing that I didn’t realise initially is that you can’t use the gitweb URL to do clones etc.
    I spent ages trying to do this, until I found Seth’s page. It explains things in a very structured manner that can be applied to most situations I suspect.

    The only other thing I think I should point out is related to rewrites. If you are using them in an Apache configuration section that is higher than site that everything will be accessed from you need to remember to set the following, otherwise they will be ignored:

            RewriteEngine On
        RewriteOptions Inherit

    So, in my case, I am accessing git via a VirtualHost that I have. The virtual host needed these lines adding to it otherwise the rewrite configuration in conf.d/gitweb didn’t get picked up.

    To enable LDAP, I also had to do this:

            sudo a2enmod authnz_ldap
        sudo a2enmod cgi
        sudo service apache restart

    In the end, to have a Git Repository​ authenticating with LDAP (with Group) authenticating, with GitWeb, some aliases, source IP restrictions and some rewrites to a gitweb file that looks like this:

    Alias /<gitweb alias> /usr/share/gitweb
Alias /<shortened gitweb alias> /usr/share/gitweb

RewriteEngine On
RewriteRule ^/<shortened gitweb alias>/([^/]+)$ /g/?p=$1 [R,NE]
RewriteRule ^/<shortened gitweb alias>//([^/]+)/([0-9a-f]+)$ /<shortened gitweb alias>/?p=$1/.git;a=commitdiff;h=$2 [R,NE]
RewriteRule ^/<shortened gitweb alias>/([^/]+)/([0-9a-f]+)$ /<shortened gitweb alias>/?p=$1;a=commitdiff;h=$2 [R,NE]

<Directory /usr/share/gitweb>
  Options FollowSymLinks +ExecCGI
  AllowOverride all
  AddHandler cgi-script .cgi
  Order deny,allow
  Deny from all
  Allow from <restricting IP addresses>
  SSLRequireSSL
  AuthType basic
  AuthName "Private git repository"
  AuthBasicProvider ldap
  AuthLDAPURL "ldap://<ldap server>:<port>/<LDAP User DN>?<LDAP User ID>?sub?(objectClass=*)"
  Require valid-user
  AuthLDAPGroupAttribute memberUid
  AUthLDAPGroupAttributeIsDn off
  Require ldap-group <LDAP Group DN>
</Directory>

ScriptAlias /<shortened git alias>/ /usr/lib/git-core/git-http-backend/
<Directory "/usr/lib/git-core/">
  Options +ExecCGI
  SetEnv GIT_PROJECT_ROOT <path to projects>
  SetEnv GIT_HTTP_EXPORT_ALL
  Order deny,allow
  Deny from all
  Allow from <restricting IP addresses>
  SSLRequireSSL
  AuthType basic
  AuthName "Private git repository"
  AuthBasicProvider ldap
  AuthLDAPURL "ldap://<ldap server>:<port>/<LDAP User DN>?<LDAP User ID>?sub?(objectClass=*)"
  Require valid-user
  AuthLDAPGroupAttribute memberUid
  AUthLDAPGroupAttributeIsDn off
  Require ldap-group <LDAP Group DN>​
</Directory>

    And we are done (well other than making the virtual host allow the rewrites).

    Just to prove it, here is a sample checkout:

    ~/temp$ git clone https://<server>/<GIT Alias>/test.git
Cloning into 'test'...
Username for 'https://<server>': <good user>
Password for 'https://<good user>@<server>': 
remote: Counting objects: 10, done.
remote: Compressing objects: 100% (6/6), done.
remote: Total 10 (delta 0), reused 4 (delta 0)
Unpacking objects: 100% (10/10), done.
~/temp$ rm -rf test
~/temp$ git clone https://<server>/<GIT Alias>/test.git
Cloning into 'test'...
Username for 'https://<server>': <bad user>
Password for 'https://<bad user>@<server>': 
fatal: Authentication failed

  • Updating Language of postings in Drupal.

    Everything said it was undefined language (very weird).

    Found this post so I ran this update:

    UPDATE node SET language = 'en' WHERE language = 'und';

    And it did exactly what it says on the tin (turned everything set to und to en).

    Perfect!

  • Linking Drupal to QNAP LDAP

    This was way more simple than I expected.

    For reference, I have a separate server running my Drupal Installation. The QNAP sits along side it (but not accessible to the web). 

    I basically just added the LDAP module to the Drupal installation. I also had to install the Entity module for LDAP to use. Then I enabled LDAP functionality bit by bit, testing as I went. The first module was LDAP Servers. 

    Enabling this resulted in a request to install the PHP LDAP module on the server. Once that was in place, the module would enable. I then set up links back to my LDAP server. The only additional thing I needed on the LDAP server, was a service account for Drupal to bind with (which I created).

    Setting up the user relationship was easy:
    I set the base DN to my domain name.
    Then I set AuthName to be UID AccountName is also set to be UID
    The Email attribute is mail
    I then set the “Expression for the user DN” to “uid=%username,ou=people,%basedn”

    Setting up the group configuration proved the biggest stumbling point. The important bit to get right it that it asks for the Group Object Class, and not the name thing that holds the group. I miss understood the question here initially, especially as my container is called group, and that was one of the examples. What I needed to put in here was posixGroup.

    Then I told it only that LDAP Group Entry Attribute was memberuid and the this field actually held the UID.

    Once this was done, I could turn it on and test it.

    Now I enabled the LDAP User and LDAP Authentication modules.

    In the LDAP User module, I just told it to associate accounts together if an LDAP one existed (second option).
    I didn’t want two way provisioning, so I have left that unset.

    In the Authentication module, I initially left it mixed mode until I was confident it worked, at which point I have moved it to the second authentication option. The rest of this section I have left default. 

    It was after setting this up that I did my first proper test of everything using another browser to prevent cookie clashes etc.

    Once i was 100% sure that I could login as administrator still, and also login as my proper user. Once I was happy with this, THEN I altered authentication to the second mode. 

    Now I moved on to LDAP Authorization. I enabled this module went to configure it, and found that without a module to use it with it didn’t do much. 

    So I enabled the LDAP Authorization – Drupal Roles module too. The setup of this module was also very simple. I simply told it to use my LDAP server, and then explained how to map my LDAP groups to the Drupal groups.

    The only real fly in the ointment was that I was initially not looking to the groups correctly, as I thought that it was looking for the container of the groups, and not the Object Class of the group.

    Once I resolved that all the test worked properly and I have a working system that automatically adds people to drupal if they exist in the LDAP, and also correctly sets up their groups for them.

    Very pleased.

  • 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.