Saturday, 4 January 2014

Xcode Build for iOS - conditional copy of resource files based on Build Configuration

A brief: Use Run Script build phase in Xcode to selectively copy resource files, such as Settings.bundle, security certificates, etc., depending on the build configuration, e.g. Debug or Release.
How-To: Project Navigator: select a project, select a target, select Build Phases tab; menu: Editor > Add Build Phase > Add Run Script Build Phase.

In detail:
Couple days ago I needed to make a minor change to how we compile/build our iOS app. The app has a few settings but all of them for development only. While preparing a submission to Apple, we wanted to remove the app from the Settings view on iPad completely. The challenge was to keep the settings for developer builds. This proved to be a bit more difficult than expected. Hence, a post to document how it can be done as well as a few things learned about Build Project settings, Targets, Build Configurations, Schemes, logs etc. in Xcode (v5).  

For an iOS app to have an entry to the standard Settings view, the app needs to include a Settings.bundle file (which is actually a directory on the file system, by the way). When the file is added to a project (any file for that matter actually), Xcode allows to selectively include it into the project's targets. Normally, an Xcode project would have the main target (named after the app) and a test target. So, one way to conditionally include a file, Settings.bundle in my case, into the build is to duplicate the main target and use that duplicated target for developer builds only. For example, let's say our app name is iStockFutures and by default the main target is iStockFutures. We could've duplicated that target into iStockFutures-Dev and kept the Settings.bundle file as a member of the iStockFutures-Dev target only. A sample screenshot is shown below.



That would accomplish the task. But there is drawback - having multiple targets means that developers have to be mindful when adding new files (any new file) and better not forget to include it into both targets. When running the app in Xcode, the dev target then should be used but when committing code to a build server, it had better be tested on both targets. Needless to say, my development team was not thrilled on that prospect. 

Luckily, there is a more transparent way:

The build process in Xcode includes multiple phases. One of them is Copy Bundle Resources. The phase has a list of resource files to copy. When the Settings.bundle was added to the project, Xcode automatically included it into that list. Unfortunately, Xcode 5 does not allow to have multiple versions of the list based on Configuration, e.g. Release or Debug. Not sure why Apple didn't do it, after all, such capability exists and is widely used in Build Settings. Anyway, this can be easily achieved by using a Run Script phase. Run Script is a feature in Xcode that allows to execute a custom script while building a Product (i.e. an App). Multiple script languages are supported (see the link above) but since all what we need to do is to copy a file, ah, sorry, I meant a directory, we just going to use the standard /bin/sh.
To create a Run Script phase, select the project in the Project Navigator, then make sure that a target is selected (otherwise the menu will be greyed out/disabled).
Then use the Editor menu to add a Run Script phase: Editor > Add Build Phase > Add Run Script Build Phase





When it's added, expand the Run Script phase and add this script (modify it as needed, of course) that copies Settings.bundle if build is run in Debug configuration:

echo "Checking configuration to determine whether to copy Settings.bundle: CONFIGURATION=$CONFIGURATION"
if [ "$CONFIGURATION" == "Debug" ]; then
echo "Copying ${SRCROOT}/${PRODUCT_NAME}/Settings.bundle directory to ${BUILT_PRODUCTS_DIR}/${PRODUCT_NAME}.app"
cp -R ${SRCROOT}/${PRODUCT_NAME}/Settings.bundle ${BUILT_PRODUCTS_DIR}/${PRODUCT_NAME}.app/Settings.bundle
echo "Settings.bundle directory has been copied."
else
echo "Skipped copying Settings.bundle - not required for $CONFIGURATION configuration."
fi

Now, remove the Settings.bundle from the target, iStockFutures in the sample app.
(There are at least 2 ways to do that - either edit the list under Copy Bundle Resources or select Settings.bundle in Project Navigator and uncheck all targets in the File Inspector's Target Membership [View > Utilities > Show File Inspector]).
This is important:

  • Run Product > Clean before running Product > Build, otherwise the Settings.bundle copied before will still be packaged into the app.
  • Uninstall the app from the device and/or simulator - that will remove the app entry from the Settings app.

Also, if you played with the solution presented first, i.e. a dev target, remember to remove that dev target.

Let's build the product in Debug configuration first and look into Xcode build log files to verify the script is being run.

Hint: Where to find Xcode build logs: from the menu: View > Navigators > Show Log Navigator


Select the default group and the All Messages option: 



This is it for building in Debug configuration.

To fully complete the work we need to verify it builds correctly in Release configuration as well. It can be done by running a command-line build configured to Release (a default configuration setting on the Project) on the team's CI (Continuous Integration) server. But of course a better approach is to test it on a developer's Mac beforehand.
An Xcode's feature called Scheme comes handy here (more info). It allows to maintain multiple sets of targets each configured to a specific Build Configuration, i.e. Debug or Release.

A new scheme can be created via the Product > Scheme menu.

Select Product > Scheme > Edit Scheme…, then Duplicate one of the existing schemes (1) and change the Build Configuration to Release (2):

 


After creating and configuring a new scheme, make it active by selecting it in Product > Scheme > iStockFutures-Release menu.
Build the app by running Product > Clean and Product > Build. When completed, check the build log file.



That should be all.

Ok, that's not been very complicated, why it took more time than expected? That's because a bulk went into attempting to figure out and tinkering with Xcode environment variables. 

Here is a couple things that helped.

How to print all Xcode environment variables:
Open a Terminal window, change the directory to the project directory, i.e. the one that contains <project_name>.xcodeproj file and run this command:

xcodebuild -project iStockFutures.xcodeproj -target "iStockFutures" -showBuildSettings > iStockFutures-build-settings.txt

All Xcode settings will be saved in the specified file. Replace iStockFutures with your project, of course.

Official Xcode Build Settings Reference doc from Apple:


And a last tip - for successful builds the log file does not show too many details. However, add a faulty Run Script build phase, something like this for example:

cp ${SRCROOT}/file-that-doesnot-exist.txt ${BUILT_PRODUCTS_DIR}/file-that-doesnot-exist.txt

Build fails and the log file can be expanded to see quite a bit of details; it might be handy in understanding how the build works:



Conclusion

By the way, the Run Script approach can be used to copy not only Settings.bundle but other environment-specific files. For example, we also used the script to copy Development and UAT/Production security certificates that we use for 2-way SSL/TLS authentication.








Monday, 10 December 2012

Running statistics on Oracle tables from Java program.

Oracle database query optimizer relies heavily on statistics. These statistics are used by the query optimizer to choose the best execution plan for each SQL statement. Normally, having correct and up-to-date statistics falls under DBA responsibilities (Oracle recommends automatic statistics gathering). However, there are cases when it might be necessary to update statistics manually, and moreover, at runtime directly from a Java program. For example, when data had been reloaded into the tables and now need to be accessed from a Java batch program while database instance is not configured for automatic statistics update. Below is an example of Java code that implements a method to gather statistics for a particular table. The code is based on the JdbcDaoSupport class from Spring framework but it is a simple convenience and author's preference - there is nothing that would prevent from porting this code to the standard plain JDBC implementation.
/*
 * Copyright 2012 VT Enterprise Software Consulting Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package com.vtesc.examples.db;

import java.sql.Types;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

/**
 * Demonstrates invocation of dbms_stats.gather_table_stats procedure
 * to gather an Oracle table statistics.
 * @author Vitali Tchalov
 */
public class JavaOracleStats extends JdbcDaoSupport {
 /**
  * Updates statistics for table in schema using
  * Oracle dbms_stats.gather_table_stats procedure.
  * 
  * @param schema
  * @param table
  */
 @Transactional(readOnly = false, propagation = Propagation.REQUIRES_NEW)
 public void gatherTableStats(String schema, String table) {
  JdbcTemplate jdbcTemplate = new JdbcTemplate(getJdbcTemplate()
    .getDataSource());
  jdbcTemplate.setResultsMapCaseInsensitive(true);

  SimpleJdbcCall gatherStats = new SimpleJdbcCall(jdbcTemplate)
    .withProcedureName("dbms_stats.gather_table_stats")
    .withoutProcedureColumnMetaDataAccess()
    .useInParameterNames("ownname", "tabname")
    .declareParameters(new SqlParameter("ownname", Types.VARCHAR),
     new SqlParameter("tabname", Types.VARCHAR));
  SqlParameterSource in = new MapSqlParameterSource().addValue("ownname",
    schema).addValue("tabname", table);

  gatherStats.execute(in);
 }
}

This is a fully tested and usable code. But I plan to package this example along with a JUnit test, Spring context file and other artefacts and will publish when ready.

Friday, 14 September 2012

Frequently used SQL DDL statements

A collection of reference examples of SQL DDL (Data Definition Language) statements frequently used in application development to create and alter database tables, synonyms, constraints, indexes etc. 
Note: The statements have been tested on Oracle 11G database; most probably should work with other relational databases. Some of the datatypes used in the samples are Oracle-specific and would require some tweaking.

-- Create a table
CREATE TABLE TX_HISTORY (
  tx_id      NUMBER(10, 0) NOT NULL,
  stock_id   NUMBER(10, 0) NOT NULL,
  tx_date    DATE          NOT NULL,
  currency   VARCHAR2(5)   NOT NULL,
  price      NUMBER(9, 2)  NOT NULL,
  price_2    NUMBER
)
/

-- Create a table with a PK (Primary Key) on a single column
CREATE TABLE STOCK (
  stock_id      NUMBER(10, 0) NOT NULL,
  symbol        VARCHAR2(5)   NOT NULL,
  stk_exchange  VARCHAR2(5)   NOT NULL,
  currency      VARCHAR2(5)   NOT NULL,
--  
  CONSTRAINT stock_id_pk PRIMARY KEY (stock_id)
)
/

-- Add a PK (Primary Key) to already existing table
ALTER TABLE tx_history ADD CONSTRAINT tx_history_pk PRIMARY KEY (tx_id)
/

-- Create a table with a FK (Foreign Key) to another table
CREATE TABLE STOCK_HISTORY (
  rec_id        NUMBER(10, 0) NOT NULL,
  stock_id      NUMBER(10, 0) NOT NULL, -- FK to STOCK table
  trade_date    DATE          NOT NULL,
  open_price    NUMBER(9, 2)  NOT NULL,
  close_price   NUMBER(9, 2)  NOT NULL,
--  
  CONSTRAINT stock_history_stock_fk FOREIGN KEY (stock_id) REFERENCES stock (stock_id) ENABLE VALIDATE
)
/

-- Add a FK (Foreign Key) to already existing table to reference another table
ALTER TABLE tx_history ADD CONSTRAINT tx_history_stock_fk FOREIGN KEY (stock_id) REFERENCES stock (stock_id) ENABLE VALIDATE
/
-- Drop (delete) a table
DROP TABLE TX_HISTORY
/

-- Add a new column to an existing table
ALTER TABLE TX_HISTORY ADD QTY NUMBER
/

-- Drop a column
ALTER TABLE TX_HISTORY DROP COLUMN PRICE_2
/

Wednesday, 15 August 2012

NoClassDefFoundError and ClassNotFoundException errors when running standalone Java programs

This post is intended as a collection of various cases related to Java CLASSPATH issues that cause NoClassDefFoundError and ClassNotFoundException errors that are often mysterious and sometimes quite difficult to troubleshoot. 
The addressed cases go beyond trivial causes of missing jar files or mistyped package names.

Case I - A standalone Java program is packaged into a jar file. The JAR includes a seemingly correct manifest specifying the main class and the program classpath as in the sample below:

MANIFEST.MF file:


Manifest-Version: 1.0
Main-Class: com.vtesc.batchjobs.daily.txprocessor.Launcher
Class-Path: . ./txprocessor.jar ./lib/spring.jar ./lib/commons-logging.jar ./lib/ojdbc6.jar d:/common/lib/dbaccess.jar


The txprocessor.jar file is correctly built by an Ant build script and has a totally correct structure and contents:



Everything seems to be perfectly fine but when run, it fails with classloader errors (Java version "1.6.0_29"):


D:\batchjobs\txprocessor>java -jar txprocessor.jar
Exception in thread "main" java.lang.NoClassDefFoundError: com/vtesc/batchjobs/daily/txprocessor/Launcher
Caused by: java.lang.ClassNotFoundException: com.vtesc.batchjobs.daily.txprocessor.Launcher
        at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:247)
Could not find the main class: com.vtesc.batchjobs.daily.txprocessor.Launcher. Program will exit.

Cause:
The returned error displaying the "Could not find the main class" message can be actually quite misleading as it may make you to suspect some problem with the main class. However, in this case it has absolutely nothing to do with the main class. The cause of the error is actually in the Manifest file on the Class-Path: line:

d:/common/lib/dbaccess.jar


Hard-coding an absolute path to a library is a very bad practice in itself but specifying the path without indicating a protocol causes the total failure. By default, the Java platform uses the java.net.URLClassLoader class for loading program classes. As its name implies, the URLClassLoader uses another system class, java.net.URL for specifying the resource location. The URL class complies with a number of RFC documents and it requires the file:/ protocol to be specified for file system resources.

The problem can be corrected by prefixing the absolute path to the dbaccess.jar file with the file:/ protocol as shown below:


Manifest-Version: 1.0
Main-Class: com.vtesc.batchjobs.daily.txprocessor.Launcher
Class-Path: . ./txprocessor.jar ./lib/spring.jar ./lib/commons-logging.jar ./lib/ojdbc6.jar file:/d:/common/lib/dbaccess.jar


Yet, a better way would be not to use absolute paths. Case in point, using the Windows specific path here will confine this Java program to the Windows platform which goes against the Java platform-independence principle.
But we live in a world which is far from perfect and sometimes one finds himself forced to follow requirements even if they go against best practices and common sense.

Thursday, 9 August 2012

Samples of control files for Oracle SQL Loader

This is intended as a quick reference to provide a set of templates for creating SQL Loader control files for most frequently used scenarios.

Sample 1:
Loading data from a TAB-delimited file: FIELDS TERMINATED BY X'09'
The sample also demonstrates the following techniques:
  • skip lines: OPTIONS (SKIP=1)  instructs the SQL Loader to skip the first line (the number specifies the number of lines)
  • auto-generate values for a primary key column: SEQUENCE(MAX, 1)
  • load data by adding more rows without truncating the existing data: APPEND
  • load into DATE type columns: "TO_DATE(:TX_DATE,'MM/DD/YYYY')"

-- ============================================================
-- stocktx.ctl
-- SQLLDR Control File for data loading to STOCKTX table.
--
-- The input file has the TAB-separated format with a single line per table row.
--
-- To run (sample):
--   sqlldr batchjob/<pwd>@oradb control=stocktx.ctl errors=1000 log=logs/stocktx_load.log

OPTIONS (SKIP=1) load data
  infile 'data/stocktx_data.tab'
  badfile 'logs/stocktx_data.bad'
  discardfile 'logs/stocktx_data.discard'
  APPEND
  into table portfolio.stocktx
  FIELDS TERMINATED BY X'09'
  trailing nullcols
(
    TX_ID        SEQUENCE(MAX, 1),
    EXCHANGE,
    SYMBOL,
    QTY,
    PRICE,
    TX_DATE      "TO_DATE(:TX_DATE,'MM/DD/YYYY')"
)

Sample 2:
Loading data from a multicharacter-delimited file (^|^) file: FIELDS TERMINATED BY '^|^'
The sample also demonstrates the following techniques:
  • delete existing data prior to the load: TRUNCATE
  • specify the data file name on the command line: data=addr.dsv
  • specify log files pathnames via command line parameters (as opposite to hard-coding in the control file).
  • evaluate the source values and transform on a condition, e.g if the Postal Code is "N/A", replace it with NULL: ZIP_CODE "decode(:ZIP_CODE, 'N/A', '', :ZIP_CODE)"
  • apply the current time/date to a DATE column: SYSDATE 
  • CONSTANT - allows to set a column to a constant value; this value is hard-coded into the control script file, not from the input data file. The value is treated as a character string; SQLLDR will attempt to convert it to the column type if required.
-- ============================================================
-- addr.ctl
-- SQLLDR Control File for data loading to ADDR table.
--
-- The input file the '^|^' sequence of characters to separate values.
--
-- To run (sample):
-- sqlldr batchjob/@oradb control=addr.ctl errors=100 log=logs/addr_load.log bad=logs/addr.bad discard=logs/addr.discard data=addr.dsv
--
-- Note: the logs directory needs to be created prior to running the script.

OPTIONS (SKIP=0)

load data
  TRUNCATE
  into table ADDR
  FIELDS TERMINATED BY '^|^'
  trailing nullcols
(
    ADDR_ID SEQUENCE(MAX, 1),
    CITY,
    STREET_NUM,
    STREET_NAME,
    STREET_DIR,
    ZIP_CODE    "decode(:ZIP_CODE, 'N/A', '', :ZIP_CODE)",
    MODIFIED_ON SYSDATE,

    BATCH_ID    CONSTANT 1
)

This is a table structure for the sample above:
CREATE TABLE ADDR (
  addr_id      NUMBER(10)     NOT NULL,
  city         VARCHAR2(40)   NOT NULL,
  street_num   NUMBER         NOT NULL,
  street_name  VARCHAR2(40)   NOT NULL,
  street_dir   VARCHAR2(4)    NOT NULL,
  zip_code     VARCHAR2(7),
  modified_on  DATE           NOT NULL,
  batch_id     NUMBER,
--  
  CONSTRAINT addr_id_pk PRIMARY KEY (addr_id)
)
/
Sample 3:
SQLLDR control file to load data from a comma-separated file (,) where field values may be enclosed in double-quotes (").


Control file explanation:
  • FIELDS TERMINATED BY ',' - specifies that the fields are delimited by comma.
  • OPTIONALLY ENCLOSED BY '"' - specifies that field values may (or may not) be enclosed in double quotes, for example: "2269 Lake Shore Blvd. W., Toronto, ON"
    This is often required if the data can contain characters that are used for field separation, the comma character in this particular example.
  • SEQUENCE(COUNT, 1) - this is usually used to generate unique values for the table primary key. The COUNT parameter specifies that the 1st value to be the number of records in the table before the load plus 1.
  • --  INTO TABLE ADDR.STREET_VALUES_BAK - a commented out line; it is handy during the development phase when the control file can be re-used to load data into different tables.
  • "UPPER(:ALT_STREET_NAME)" - convert the values to the upper case.
  • CREATE_DATE "TO_DATE(TO_CHAR(SYSDATE, 'YYYYMMDD'), 'YYYYMMDD')" - this is useful to load only the date part of the current time into DATE type columns.
    SYSDATE returns the current time which is formatted into a string containing Year, Month, and Day which in turn converted back to DATE type.
  • BATCH_ID EXPRESSION "(SELECT MAX(BATCH_ID) FROM ADDR.BATCH_ID WHERE TABLE_NAME = 'STREET_ALTERNATIVE')" - useful to insert a constant value for each data load, for a example a Batch ID or Run ID.
    A separate table is required, of course, to maintain load data IDs. A new record can be inserted into this table from a shell script prior to invoking the SQLLDR script. See a DDL and INSERT statement below.
-- =============================================================
-- street_alternative.ctl
-- Loading alternative street names.
-- SQLLDR Control File for data loading into ADDR.STREETS_ALTERNATIVE table.
--
-- The input file is |-separated in double quotes.
-- sqlldr vit1@devdb1 control=street_alternative.ctl errors=1 log=logs/street_alternative.log bad=logs/street_alternative.bad discard=logs/street_alternative.discard data=street_alternative.txt
--

OPTIONS (SKIP=1)

LOAD DATA
  APPEND
  INTO TABLE ADDR.STREET_ALTERNATIVE
--  INTO TABLE ADDR.STREET_VALUES_BAK
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
  TRAILING NULLCOLS (
  REC_ID            SEQUENCE(COUNT, 1),
  STREET_ID,
  ALT_STREET_NAME   "UPPER(:ALT_STREET_NAME)",
  CREATE_DATE       "TO_DATE(TO_CHAR(SYSDATE, 'YYYYMMDD'), 'YYYYMMDD')",
  BATCH_ID          EXPRESSION "(SELECT MAX(BATCH_ID) FROM ADDR.BATCH_ID WHERE TABLE_NAME = 'STREET_ALTERNATIVE')"
)



DDL for the tables used in the sample #3 above:
CREATE TABLE ADDR.STREET_ALTERNATIVE (
  rec_id          NUMBER       NOT NULL,
  street_id       NUMBER       NOT NULL,
  alt_street_name VARCHAR2(40) NOT NULL,
  create_date     DATE         NOT NULL,
  batch_id        NUMBER       NOT NULL,
--  
  CONSTRAINT rec_id_pk PRIMARY KEY (rec_id)
)
/
CREATE TABLE ADDR.BATCH_ID (
  rec_id          NUMBER        NOT NULL,
  table_name      VARCHAR2(255) NOT NULL,
  batch_id        NUMBER        NOT NULL,
  create_date     DATE          NOT NULL,
 --  
  CONSTRAINT batch_id_pk PRIMARY KEY (rec_id)
)
/
-- A statement to insert a record into the BATCH_ID table for
-- the next value to be used in a data load:
insert into ADDR.BATCH_ID values (1, 'STREET_ALTERNATIVE', 
  (select nvl(max(batch_id), 0) from ADDR.BATCH_ID
    where table_name = 'STREET_ALTERNATIVE') + 1, sysdate)
/

Thursday, 5 July 2012

Oracle Database Management for Application Developers

A set of how-to's and tips for managing an Oracle 10g/11g database targeted for non-DBA folks such as Application Architects and Java Developers.


Problem: The Enterprise Management (EM) webapp console can no longer be started after a password change.

This tip is specific to running Oracle on Windows. Normally the installer will register a Windows service for the EM console. Assuming the default settings for the instance and host were kept, the service name is likely to be OracleDBConsoleorcl.
Since it is a full-blown web application running under OC4J application server, it's quite heavy on the hardware; so, as a post-installation step it is common to change the service default Startup Type from Automatic to Manual and launch it only when there is a need.
If all of a sudden the service begins to fail to start, chances are that the message and error code reported by the service will be very generic and most likely useless for troubleshooting.
So, the first thing to do is to locate the EM deployment directory which contains log files. Normally (the tip assumes an installation on a single host, not cluster), it will be under the <DBHOME> directory; for example, a typical location:
....\product\11.2.0\dbhome_1\localhost_orcl\sysman\log

One possible reason could be the expired password for the SYSMAN user. The SYSMAN is the default super user account created by the installer to set up and administer the Enterprise Manager. It is also the database account that owns the objects stored in the Oracle Management Repository.

Seeing the following exceptions in the emoms.log file is a good indication that something is wrong with the SYSMAN account:
Caused by: java.sql.SQLException: ORA-01017: invalid username/password; logon denied
    at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:73)
...
java.sql.SQLException: ORA-28000: the account is locked
    at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:73)
 

In one particular case (that inspired this post), it was a forced password change after logging in to EM. The passwords had expired for a number of system accounts, SYSMAN included, and I was forced to change them. The submitted request led the EM console app to crash and it was impossible to start it again. (In retrospect, a better course of action probably was to shutdown the EM and instead change the passwords from sqlplus but it's a topic for a different post).
This is what helped to resolve the situation:
  • Locate the emoms.properties file for the EM. It should be at a path similar to this: C:\app\<user_account>\product\11.2.0\dbhome_1\localhost_orcl\sysman\config
  • Make a backup copy of the file and then edit these 2 properties: 
    oracle.sysman.eml.mntr.emdRepPwd=<new_password>
    oracle.sysman.eml.mntr.emdRepPwdEncrypted=FALSE
  • Use sqlplus command line tool or any other client to connect with a system account (e.g. SYS) and run the following statement to change the password and unlock the SYSMAN account:
    alter user SYSMAN identified by <new_password> account unlock;
  • Shutdown the database server service (usually named OracleServiceORCL), the listener (OracleOraDb11g_home1TNSListener) and whatever other related Oracle services (OracleJobSchedulerORCL, OracleMTSRecoveryService, etc.) and for a good measure reboot the Windows. 
  • Start back the OracleServiceORCL and OracleOraDb11g_home1TNSListener services. After that, the EM service, OracleDBConsoleorcl, should start successfully.
    Keep in mind that the password used for the SYSMAN account is not necessarily the same used to login into EM; normally you login to EM with the SYS account as SYSDBA and have to use the password set for the SYS user. The SYSMAN account is a system account not to be used by human users.
Check the emoms.properties configuration file - the previously edited emdRepPwd property should be now overridden with an encrypted value for the password and the emdRepPwdEncrypted should be reset back to TRUE by the EM.
In case this tip has not helped to resolve the problem, check again the log files, particularly OracleDBConsoleorclsrvc.log, emdb.nohup and emoms.log, for clues.
Also, a message log at C:\app\<user_account>\diag\rdbms\orcl\orcl\alert\log.xml contains alerts that reference errors and detailed log files.
And finally, refer to Maintaining and Troubleshooting the Repository in Oracle Enterprise Manager Advanced Configuration 10g documentation for help.

Check out the following tip to disable the password expiry to prevent these kind of troubles in the future.

Tip: Change password expiration policy

For a locally installed developer instance of Oracle database, the security is often of no concern. On the contrary, it can be quite a hussle when the database is used only by a single developer or a small team.
It therefore makes sense to disable password expiry for system accounts to prevent troubles such as one described above. Normally, most of the system accounts are locked by default in simple installations such as a development instance, but SYS, SYSTEM, SYSMAN, MGMT_VIEW have to be unlocked and active  even in a minimal configuration.
In Oracle, the password expiry policy is defined on the profile level.
By default, the Oracle installer creates these system accounts under the DEFAULT profile. If these assumptions are applicable, then executing the statement below will disable password expiration:

ALTER PROFILE "DEFAULT" LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Of course, this will impact every user account (a.k.a. schema) that is based on the DEFAULT profile.

The same can be achieved via the EM - log in under SYS user as SYSDBA and navigate to the Sever > Profiles > DEFAULT page. Click Edit and then select the Password tab. In the Password section put UNLIMITED (or click on the lookup icon) into the "Expire in (days)" input field.

Saturday, 26 March 2011

Using Sun JSSE on IBM J9 JVM

A client asked to secure communications between quite old devices running IBM J9 JVM version 1.3. The geographically dispersed devices communicate with central servers via web services over HTTP protocol to sync data. What appeared to be a very simple task to switch to SSL protocol by replacing http with https in a config file, turned out to be quite a daunting exercise.
Though it is unlikely someone else encounters similar tasks, information below may also be helpful for learning intricacies around SSL certificates, keystores and even JSSE in general.

In order for an application running on a J2ME device under IBM J9 virtual machine to access a web service provided over secure HTTPS protocol, the procedure described in the document should be implemented.
Prior to JDK 1.4 version, the Java Secure Socket specification was not a part of standard JDK and therefore vendors were not required to implement it. It is quite likely that IBM did implement SSL in their J9 JVM version 1.3, however, it had not been possible to locate any usable user and developer documentation. On the contrary, Sun provided a well-documented reference implementation called JSSE. This document describes how to setup and configure the Sun JSSE library on a device running IBM J9 Java virtual machine.
The JSSE setup would be a straightforward exercise except that there is a problem with the keystore format. The JDK 1.3 specification, particularly java.securtiy.KeyStore class, does not specify persistence mechanism to store keys and certificates. Apparently, the JSSE library relies on a vendor implementation, in this case IBM, for providing persistence mechanism. Attempting to connect over HTTPS protocol using the standard cacerts keystore file results in the “java.net.SocketException: SSL implementation not available” exception. This is, however, not the root cause of the problem. As it turned out, the JSSE fails to initialize the truststore providing this error message:  
“default context init failed: java.security.PrivilegedActionException: java.io.IOException: Invalid keystore”

Running J9 with the –verbose flag reveals that JVM loads com/ibm/oti/security/provider/KeyStore class. In the contrast, the same test code, run under Sun JRE 1.3_15, loads the sun.security.provider.JavaKeyStore class from the rt.jar. Therefore, the conclusion is that IBM persistence implementation of keystore is not compatible with the Sun's. This document includes a description of how to convert the standard Sun cacerts file to the IBM J9 specific format that can be processed by the com.ibm.oti.security.provider.KeyStore class.
The keytool (included in both Sun and IBM JVM distributions) utility stores the keys and certificates in a so-called keystore. By default, both Sun and IBM implement the keystore as a file in JKS format. However, the IBM file format is not compatible with Sun JKS format. The following is the description on how to create a keystore file in IBM J9 format and install and configure Sun JSSE library to run in J9 VM. 

1. Export the root Certification Authority certificates from the standard cacerts file.
1.1. Open a console window and navigate to a working directory, e.g. c:\projects\certificates
1.2. Set path to Sun’s keytool with the command, for example in Windows:
set PATH=<JDK-installation>/jre/bin;%PATH%
1.3. Locate the cacerts file in jre\lib\security folder of a Sun JDK installation and copy it into the working directory.
1.4. List all available certificates with the command into a temporary file:
keytool -list -keystore cacerts > temp.txt
When prompted, enter the password ‘changeit’ (it is a default password that was set by Sun). Here is a sample what the file should contain:
Enter keystore password:changeit
Keystore type: jks
Keystore provider: SUN
Your keystore contains 33 entries:
verisignclass1g3ca, Thu Mar 25 14:27:59 EST 2004, trustedCertEntry, Certificate fingerprint (MD5): B1:47:BC:18:57:D1:18:A0:78:2D:EC:71:E8:2A:95:73
equifaxsecureebusinessca1, Fri Jul 18 14:43:22 EDT 2003, trustedCertEntry, Certificate fingerprint (MD5): 64:9C:EF:2E:44:FC:C6:8F:52:07:D0:51:73:8F:CB:3D
verisignclass2g2ca, Thu Mar 25 14:18:49 EST 2004, trustedCertEntry, Certificate fingerprint (MD5): 2D:BB:E5:25:D3:D1:65:82:3A:B7:0E:FA:E6:EB:E2:E1
verisignclass3g3ca, Thu Mar 25 14:31:09 EST 2004, trustedCertEntry,
...

1.5. Each certificate in cacerts file has to be exported into an individual certificate file. To do this, you need to run the following command for each alias (aliases are shown in bold in the sample above. If you want, you can edit the file to leave only aliases in it – this is all information that you need from this temporary file):
keytool -export -keystore cacerts -file <alias>.cer -alias <alias>
where <alias> is an alias in the file, e.g. verisignclass1g3ca. On each run you will have to enter the password – ‘changeit’.
2. Import the root certificates into IBM-specific file.
For this procedure you need an IBM keytool utility that generates keystore file in J9 1.3 specific format. One place where it can be found is a WebSphere Studio Device Developer package.
2.1. There are several keytool utilities in the package. To find the right one you can try this: search the WSDD installation directory for keytool.exe files. Open a console window and navigate to the directory that contains a keytool.exe. Because the file formats are incompatible, the IBM keytool is not able to understand the Sun format (and vice versa). To test it, try to list certificates from the Sun keystore file by running the command:
keytool –list –keystore c:\projectes\certificates\cacert

At the prompt, enter the password changeit
If it fails with a message similar to this: keytool error: java.io.IOException: Invalid keystore, this is the utility that you need.
In the default installation of a trial version of WSDD 5.6, the keytool utility is located in the following directory:
C:\Program Files\IBM\DeviceDeveloper5.6\wsdd5.0\ive-2.1\bin 

When running the IBM keytool utility, make sure that the environment does not have JAVA_HOME variable, otherwise the utility will not be able to locate the runtime library and will fail with a message similar to this:
Fatal error: Unable to find and initialize required class java/lang/Object
2.2. Import each certificate file that was created in the step 1 to an IBM-specific keystore by running the following command:

keytool -import -alias <alias> -file c:\projects\certificates\<alias>.cer -keystore c:\projects\certificates\cacerts.j9 

Enter the same password for each certificate.
3. HTTPS Support
The JSSE implementation contains a URL handler for the HTTPS protocol. In order to use this handler, the handler's implementation package name has to be added to the list of packages which are searched by the java URL class. This is configured via the "java.protocol.handler.pkgs" system property. See the java.net.URL class documentation for details.
System properties can be set via the command line or at runtime through the java.lang.System class.
For example, you can set this property on the command line via:

java -Djava.protocol.handler.pkgs=com.sun.net.ssl.internal.www.protocol

When accessing HTTPS servers through a web proxy, you must set the "https.proxyHost" and "https.proxyPort" system properties to the correct host name and port number of the web proxy. For example, to set this property on the command line to access HTTPS servers through the proxy host "webproxy" running at port 8080 you would use:
java -Dhttps.proxyHost=webproxy -Dhttps.proxyPort=8080
To configure the handler at runtime, the following code has to be added to the application start-up module:
System.setProperty("java.protocol.handler.pkgs", "com.sun.net.ssl.internal.www.protocol");
4. Configuring the TrustManager keystore location. 
The created in the step 2 cacerts.j9 file is a keystore file in an IBM J9 specific format. This file has to be available to the JVM running a program that uses SSL. The location of the file and the password for the keystore can be specified through appropriate system properties dynamically, e.g.
java -Djavax.net.ssl.trustStore=cacerts.j9 -Djavax.net.ssl.trustStorePassword=<password>


or they can be statically coded into a program like this:


System.setProperty("javax.net.ssl.trustStore", "cacerts.j9");
System.setProperty("javax.net.ssl.trustStorePassword", “<password>”);
5. Sun Java Secure Socket Extension package
Download Sun Java Secure Socket Extension package – JSSE from http://java.sun.com/products/jsse/index.jsp
The JSSE version 1.0.3_03 is the latest (and the last) implementation available for Java 1.2 and 1.3 (Update: it has reached EOL now). Install the package on a development computer. You can find 3 jar files in the JSSE lib directory: jcert.jar, jnet.jar, jsse.jar. Copy the files to a lib directory on a clock device and include them into the classpath.
6. Register the SunJSSE provider.
To dynamically register the provider, include the following line into the application startup code:
Security.addProvider(new com.sun.net.ssl.internal.ssl.Provider()); 

Detailed instructions can be found in INSTALL.txt file of the JSSE installation.

7. To test the installation 
Configure the device to access a server via HTTPS protocol. If the server uses a certificate signed by a Certificate Authority (CA), verify that the CA was added to the cacerts.j9 file (Step 2). If using a self-signed certificate, add the certificate to the cacerts.j9 following the procedure described in step 2. 
Copy the cacerts.j9 file to a device. Run the device application launching it from a starter class that adds the security provider (step 6). Verify log files contain no errors when accessing the web services over HTTPS.

Useful links that can be used for understanding the subject and troubleshooting: