Wednesday, September 25, 2013

Oracle: Setup APEX on Linux


 Install Oracle HTTP Server


Download binary (Oracle Webtier Utilities) from http://www.oracle.com/technetwork/java/webtier/downloads/index2-303202.html

Create user/group
user name: ohs
group: ohs

log on as ohs

extract binary into /var/tmp/ohs

$ cd /var/tmp/ohs/Disk1
$ ./runInstaller


Follow the instructions on screen to create an instance called apex and finish installation, at the end you will see the url to connect to your OHS.

to uninstall it, please run below command in  <Web_Tier_ORACLE_HOME>/oui/bin

$ ./runInstaller -deinstall


Download and Install APEX

Download the binary from
http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html

extract it into /var/tmp/apex

Full development environment

Run apexins.sql passing the following four arguments in the order shown:

SQL> @apexins.sql tablespace_apex tablespace_files tablespace_temp images

Where:

    tablespace_apex is the name of the tablespace for the Oracle Application Express application user.

    tablespace_files is the name of the tablespace for the Oracle Application Express files user.

    tablespace_temp is the name of the temporary tablespace or tablespace group.

    images is the virtual directory for Oracle Application Express images. To support future Oracle Application Express upgrades, define the virtual image directory as /i/.
 

connect to your database using sqlplus as sysdba


$ cd /var/tmp/apex/apex
$ sqlplus / as sysdba
SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/


Change the Password for the ADMIN Account

Login as sysdba

SQL> @apxchpwd.sql

 Type in your password when asked.

Configure APEX_PUBLIC_USER Account

Run the following statement:

SQL> ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK

Changing the Password for the APEX_PUBLIC_USER Account

SQL> ALTER USER APEX_PUBLIC_USER IDENTIFIED BY new_password

About Password Expiration in Oracle Database 11g

 SQL> CREATE PROFILE UNLIMITED_PASSWORD_LIFETIME LIMIT
  SESSIONS_PER_USER DEFAULT
  CPU_PER_SESSION DEFAULT
  CPU_PER_CALL DEFAULT
  CONNECT_TIME DEFAULT
  IDLE_TIME DEFAULT
  LOGICAL_READS_PER_SESSION DEFAULT
  LOGICAL_READS_PER_CALL DEFAULT
  COMPOSITE_LIMIT DEFAULT
  PRIVATE_SGA DEFAULT
  FAILED_LOGIN_ATTEMPTS DEFAULT
  PASSWORD_LIFE_TIME UNLIMITED  
  PASSWORD_REUSE_TIME DEFAULT
  PASSWORD_REUSE_MAX DEFAULT
  PASSWORD_LOCK_TIME DEFAULT
  PASSWORD_GRACE_TIME DEFAULT
  PASSWORD_VERIFY_FUNCTION DEFAULT;

SQL> ALTER USER APEX_PUBLIC_USER
  PROFILE UNLIMITED_PASSWORD_LIFETIME
  ACCOUNT UNLOCK;


Granting Connect Privileges


The following example demonstrates how to grant connect privileges to any host for the APEX_040200 database user. This example assumes you connected to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role.

DECLARE
  ACL_PATH  VARCHAR2(4000);
BEGIN
  -- Look for the ACL currently assigned to '*' and give APEX_040200
  -- the "connect" privilege if APEX_040200 does not have the privilege yet.

  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
   WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

  IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040200',
     'connect') IS NULL THEN
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
     'APEX_040200', TRUE, 'connect');
  END IF;

EXCEPTION
  -- When no ACL has been assigned to '*'.
  WHEN NO_DATA_FOUND THEN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
    'ACL that lets power users to connect to everywhere',
    'APEX_040200', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

Enable

Enabling Oracle XML DB Protocol Server


EXEC DBMS_XDB.SETHTTPPORT(8080);

Load Images


if below script failed, user will see blank page when connect to workspace.

SQL> @apxldimg.sql

PL/SQL procedure successfully completed.

Enter value for 1: /var/tmp

PL/SQL procedure successfully completed.

. Loading images directory: /var/tmp/apex/images

Directory created.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Commit complete.


Directory dropped.

timing for: Load Images
Elapsed: 00:03:18.77

Friday, September 13, 2013

Build Ear File with ant for MongoDB, WebSphere Application Server

I have explained how to setup MongoDB development environment for WebSphere Application Server with Eclipse in post: http://feijiangnan.blogspot.ca/2013/09/setup-eclipse-for-mongodb-and-websphere.html, let's go further to compile the code using Apache Ant.

In this scenario, I used Linux to run the tasks.

1. Install Ant

You can find the binary and installation guide on http://ant.apache.org/

2. Create build.xml like below


<?xml version="1.0"?>
<project name="mongodbHelloWorld" default="buildEar" basedir=".">
    <description>
        First Mongodb Java Project
    </description>
    <!-- set global properties for this build -->
    <property name="distName" value="mongodbHelloWorld" />
    <property name="src" location="src" />
    <property name="build" location="build" />
    <property name="warDir" location="wars" />
    <property name="earDir" location="ears" />
    <property name="outputDir" location="output" />
    <property name="warFile" value="${distName}.war" />
    <property name="earFile" value="${distName}.ear" />
    <property name="wasJRELib" value="/opt/IBM/WebSphere/AppServer/java/jre/lib" />
    <property name="wasLib" value="/opt/IBM/WebSphere/AppServer/java/jre/lib" />
    <property name="wasJavaLib" value="/opt/IBM/WebSphere/AppServer/java/lib" />
    <property name="wasLibExt" value="/opt/IBM/WebSphere/AppServer/java/jre/lib/ext" />
    <property name="wasPlugins" value="/opt/IBM/WebSphere/AppServer/plugins" />
    <property name="wasEndorsed" value="/opt/IBM/WebSphere/AppServer/endorsed_apis" />
    <property name="wasJEE" value="/opt/IBM/WebSphere/AppServer/dev/JavaEE/6.0" />
    <property name="wasVM" value="/opt/IBM/WebSphere/AppServer/java/jre/lib/amd64/default/jclSC160" />

    <path id="project.class.path">
        <pathelement location="lib/mongo-java-driver-2.11.2.jar" />
        <fileset dir="${wasJEE}" includes="*.jar" />
        <fileset dir="${wasJRELib}" includes="*.jar" />
        <fileset dir="${wasLib}" includes="*.jar" />
        <fileset dir="${wasJavaLib}" includes="*.jar" />
        <fileset dir="${wasLibExt}" includes="*.jar" />
        <fileset dir="${wasEndorsed}" includes="*.jar" />
        <fileset dir="${wasPlugins}" includes="*.jar" />
    </path>


    <target name="init">
        <!-- Create the time stamp -->
        <tstamp />
        <!-- Create the build directory structure used by compile -->
        <mkdir dir="${build}" />
    </target>

    <target name="compile" depends="init" description="compile the source ">
        <!-- Compile the java code from ${src} into ${build} -->
        <javac srcdir="${src}" destdir="${build}" includeantruntime="false">
            <classpath refid="project.class.path" />
        </javac>
    </target>

    <target name="buildWar" depends="compile" description="generate the distribution">
        <!-- Create the distribution directory -->
        <mkdir dir="${warDir}/lib" />
        <war destfile="${warDir}/wars/${warFile}" needxmlfile='false'>

            <webinf dir="WebContent/WEB-INF">
                <include name="**/*.xml" />
            </webinf>

            <classes dir="WebContent/WEB-INF/classes" />
           
            <lib dir="lib">
                <include name="mongo-java-driver-2.11.2.jar" />
                <exclude name="jdbc1.jar" />
            </lib>
        </war>

    </target>

    <target name="clean" description="clean up">
        <!-- Delete the ${build} and ${warDir} directory trees -->
        <delete dir="${build}" />
        <delete dir="${warDir}" />
        <delete dir="${earDir}" />
    </target>

    <target name="buildEar" depends="clean, buildWar">
        <mkdir dir="${earDir}/META-INF" />

        <copy todir="${earDir}/" file="${warDir}/wars/${warFile}" />
        <copy tofile="${earDir}/META-INF/application.xml" file="application.xml" />
    <!--    <copy todir="${earDir}/META-INF">
            <fileset dir="localDirectory" includes="was.policy" />
        </copy>
        -->
        <jar jarfile="${outputDir}/${earFile}" basedir="${earDir}">
            <!-- Define the properties for the Manifest file. -->
            <manifest>
                <attribute name="Implementation-Vendor" value="Go Weekend" />
                <attribute name="Implementation-Title" value="Client Registration" />
                <attribute name="Implementation-Version" value="1.0" />
            </manifest>
        </jar>
    </target>
</project>

3. Create application.xml as below

<?xml version="1.0" encoding="UTF-8"?>
<application id="HellowMongodb" version="6" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/application_6.xsd">
        <application-name>HelloMongoDB</application-name>
        <display-name>Hello Mongodb</display-name>
        <icon>
                <small-icon>/META-INF/MongoDB_Small.jpeg</small-icon>
                <large-icon>/META-INF/MongoDB_Large.jpeg</large-icon>
        </icon>
        <module id="WebModule_HelloMongodb">
                <web>
                        <web-uri>mongodbHelloWorld.war</web-uri>
                        <context-root>HelloMongodb</context-root>
                </web>
        </module>
</application>

4. Create web.xml in WebContent/WEB-INF

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" version="3.0">
    <display-name>mongodbHelloWorld</display-name>
    <welcome-file-list>
        <welcome-file>index.html</welcome-file>
        <welcome-file>index.htm</welcome-file>
        <welcome-file>index.jsp</welcome-file>
        <welcome-file>default.html</welcome-file>
        <welcome-file>default.htm</welcome-file>
        <welcome-file>default.jsp</welcome-file>
    </welcome-file-list>
</web-app>

5. Run Ant to build ear file


 ant -f build.xml buildEar

6. Deploy the ear file

Deploy the ear file in your WebSphere Application Server, and connect to the application in your browser like:
http://<your hostname>:9060/HelloMongodb/HelloMongodb

Friday, September 6, 2013

WAS: move deployment manager and application server profiles between differrent platforms


Backup profiles

On source server
$ cd $DMGR_BIN
$ ./manageprofiles.sh -backupProfile -profileName <Dmgr profile name> -backupFile /var/tmp/Dmgr_Backup.zip
$ cd $NODE_BIN
$ ./manageprofiles.sh -backupProfile -profileName <Application Server profile name> -backupFile /tmp/Appsrv_Backup.zip

Restore profiles on destination

Transfer the backup files to destination server.

On destination server
$ cd $WAS_HOME/bin
restore deployment manager profile
$ ./manageprofiles.sh -restoreProfile -backupFile /var/tmp/Dmgr_Backup.zip
restore WAS node profile
$ ./manageprofiles.sh -restoreProfile -backupFile /var/tmp/Appsrv_Backup.zip

 

Customize configuration files

Deployment Manager


replace all old hostname with new hostname in deployment manager serverindex.html, i.e.
/opt/IBM/WebSphere/AppServer/profiles/<Dmgr profile name>/config/cells/<cell name>/nodes/<dmgr node name>/serverindex.xml
fix all os and architeture configuration in node-metadata.properties
/opt/IBM/WebSphere/AppServer/profiles/<Dmgr profile name>/config/cells/<cell name>/nodes/<dmgr node name>/node-metadata.properties

WAS Node Profile

replace all old hostname with new hostname in node agent serverindex.html, i.e.
/opt/IBM/WebSphere/AppServer/profiles/<Dmgr profile name>/config/cells/<cell name>/nodes/<app server node name>/serverindex.xml
fix all os and architeture configuration in node-metadata.properties
/opt/IBM/WebSphere/AppServer/profiles/<Dmgr profile name>/config/cells/<cell name>/nodes/<app server node name>/node-metadata.properties

Synchornize node with deployment manager

./syncNode.sh <dmgr server name> <dmgr soap port number>

Start up profiles

$ cd $DMGR_BIN
$ ./startManager
$ cd $NODE_BIN
$ ./startNode.sh
$ ./startServer.sh server1

Setup Eclipse for mongoDB and WebSphere Application Development Environment


Install JDK

Download jdk from http://www.oracle.com/technetwork/java/javasebusiness/downloads/java-archive-downloads-javase6-419409.html
# cd /opt
# <path to jdk binary>/jdk-6u38-linux-x64.bin
# cd /opt/jdk1.6.0_38

set environment variables for users accordingly
set JAVA_HOME=/opt/jdk1.6.0_38
and set PATH=$JAVA_HOME/bin:$PATH

Install mongoDB

Download mongoDB from http://www.mongodb.org/downloads
Extract the binaries in /opt/mongoDB
Verify Installation
# cd /opt/mongoDB/bin
Start Server
# ./mongod

Start client
# ./mongo

Install WebSphere Application Server

please refer to IBM WebSphere Application Server Installation

Install Eclipse

Download Eclipse from http://www.eclipse.org/downloads/
Eclipse Juno is used in this scenario.
extract the binary into /opt/tools
$ cd /opt/tools
$ tar -zxf eclipse-jee-juno-SR2-linux-gtk-x86_64.tar.gz
$ mv eclipse mongodbDev

Configuring Eclipse with WebSphere Application Server

Help -> Eclipse Marketplace -> Search
type in "Websphere application server" in Find field, and click Go
In the results panel, Install "IBM WebSphere Application Server V8.0 Developer Tools for Eclipse Juno"
Followin instructions in screen to finish the installation.
Restart Eclipse

Goto Windows -> Preferences -> Server -> Runtime Environments
Remove the existing one "Web Preview Server Runtime".
Add -> IBM -> WebSphere Application Server v8.0
Check "Create a new local server", click Next
Point "Installation Dictionary" to your WebSphere Application Server installation location, i.e. /opt/IBM/WebSphere/AppServer
Provide information accordingly in the following screens, and click Finish.
Goto Windows -> Preferences -> Server -> WebSphere Application Server to verify the settings.

Download MongoDB Java Driver from http://api.mongodb.org/java/
we use version of 2.11.2 in this scenario, and put it in your CLASSPATH.

Create First Application, mongodbHelloWorld

Goto File -> New -> Dynamic Web Project
Project Name: mongodbHelloWorld
Click Finish

In project of mongodbHelloWorld, goto File -> New -> Servlet
Java package: ca.goweekend.mongo
Class name: HelloMongodb
Click Finish
paste below code in HelloMongodb.java, and you will see many errors.

Now let's include Mongodb Java Driver into CLASSPATH to resove those errors.
right click on mongodbHelloWorld project, and choose properties -> Java Build Path -> Libraries -> Add External Jars, add the Java driver you downloaded before. Click OK, and the errors should be all gone.

Goto Windows -> Preferences -> Java -> Build Path -> Classpath Variables -> New
Name: Mongodb Java Driver
Path: /apps/mongodb/mongo-java-driver-2.11.2.jar

HelloMongodb.java

package ca.goweekend.mongo;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.ibm.ws.webcontainer.servlet.ServletConfig;
import com.mongodb.MongoClient;
import com.mongodb.DB;
import com.mongodb.DBCollection;
import com.mongodb.BasicDBObject;
import com.mongodb.DBCursor;

/**
 * Servlet implementation class HelloMongodb
 */
@WebServlet("/HelloMongodb")
public class HelloMongodb extends HttpServlet {
        private static final long serialVersionUID = 1L;

        /**
         * @see HttpServlet#HttpServlet()
         */
        public HelloMongodb() {
                super();
        }

        public void init(ServletConfig config) {
                System.out.println("Inititiating Database Connection.");
        }

        /**
         * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
         *      response)
         */
        protected void doGet(HttpServletRequest request,
                        HttpServletResponse response) throws ServletException, IOException {

                MongoClient mongleClient = new MongoClient("db.goweekend.ca",
                                27017);

                DB db = mongleClient.getDB("HelloMongodb");

                DBCollection coll = db.getCollection("client");

                try {
                        BasicDBObject doc = new BasicDBObject();

            String yourIPAddress = request.getRemoteAddr();
            String hostname = request.getRemoteHost();
            int portNumber = request.getRemotePort();
                        doc.put("hostname", hostname);
                        doc.put("ipaddress", yourIPAddress);
                        doc.put("portnumber", portNumber);

                        coll.insert(doc);

                } catch (Exception e) {
                        e.printStackTrace();
                }

                DBCursor cursor = coll.find();

                try {
                   while(cursor.hasNext()) {
                           response.getWriter().print(cursor.next());
                           response.getWriter().print("\n");
                   }
                } finally {
                   cursor.close();
                }
        }
}

right click on HellowMongodb.java in Enterprise Explorer Panel, and chose Run As -> Run on Server, and follow the instructions on screen, and you will see the result. Open the URL on different machine, you will see different machine entries added in MongoDB.

To use ant to build ear file, please refer to http://feijiangnan.blogspot.ca/2013/09/build-ear-file-with-ant-for-mongodb.html

Thursday, September 5, 2013

Oracle Database 11g: JDBC Connection Authentication Using Certificate

Please finish instructions 1-3.1 in Oracle 11g: SQLNet Authentication Using PKI

Create location to hold jar files needed, i.e. /home/oracle/SSL
$ mkdir -p /home/oracle/SSL
Copy the jar files needed to test the connection.
$ cd $ORACLE_HOME/jlib
$ cp ojpse.jar oraclepki.jar /home/oracle/SSL/
$ cp osdt_cert.jar osdt_core.jar /home/oracle/SSL/
$ cp $ORACLE_HOME/jdbc/lib/ojdbc6.jar /home/oracle/SSL/
Create DbSSLTester.java with below code:
    import java.security.Security;                                                                                                                                            

    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.Statement;


    public class DbSSLTester

    {
    public static void main(String[] args)
    throws Exception
    {
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    Security.insertProviderAt(new oracle.security.pki.OraclePKIProvider(), 3);
    String url = "jdbc:oracle:thin:@(DESCRIPTION = " +
    "(ADDRESS_LIST = " +
    "(ADDRESS = " +
    "(PROTOCOL = TCPS)" +
    "(HOST = suzhou.goweekend.ca)" +
    "(PORT = 2484)" +
    ")" +
    ") " +
    "(CONNECT_DATA = (SERVICE_NAME = GUSU))" +
    ")";

    java.util.Properties info = new java.util.Properties();
    info.setProperty("oracle.net.authentication_services", "(TCPS)");
    info.setProperty("javax.net.ssl.trustStore", "/app/oracle/product/11.2.0.3/owm/wallets/fei/cwallet.sso");
    info.setProperty("javax.net.ssl.trustStoreType", "SSO");
    info.setProperty("javax.net.ssl.keyStore", "/app/oracle/product/11.2.0.3/owm/wallets/fei/cwallet.sso");
    info.setProperty("javax.net.ssl.keyStoreType", "SSO");

    Connection conn = DriverManager.getConnection(url, info);
    Statement stmt = conn.createStatement();
    ResultSet rset = stmt.executeQuery("select user from dual");

    while (rset.next())
    System.out.println(rset.getString(1));

    rset.close();
    stmt.close();
    conn.close();
    }
    }
$ export CLASSPATH=$CLASSPSTH:/home/oracle/SSL/ojpse.jar:/home/oracle/SSL/oraclepki.jar:/home/oracle/SSL/osdt_cert.jar:/home/oracle/SSL/osdt_core.jar:/home/oracle/SSL/ojdbc6.jar
$ export PATH=/opt/jdk1.6.0_38/bin:$PATH
$ javac DbSSLTester.java
$java DbSSLTester
 FEI

Oracle Database 11g: SQLNet Authentication Using PKI


Secure connection between Oracle Server and Client

1. Create the server and client wallets

1.1 Server

  • Create wallet location
    $ mkdir /app/oracle/product/11.2.0.3/owm/wallets/server
  • Initiate wallet
    $ orapki wallet create -wallet /app/oracle/product/11.2.0.3/owm/wallets/server -auto_login -pwd think4me
  • Create self-signed certificate
    $ orapki wallet add -wallet /app/oracle/product/11.2.0.3/owm/wallets/server -dn 'CN=server' -keysize 2048 -self_signed -validity 3650 -pwd think4me
  • Export server root certificate
    $ orapki wallet export -wallet /app/oracle/product/11.2.0.3/owm/wallets/server -dn 'CN=server' -cert /app/oracle/product/11.2.0.3/owm/wallets/server/server_ca.cert

1.2 Client

  • Create wallet location
    $ mkdir /app/oracle/product/11.2.0.3/owm/wallets/fei
  • Initiate wallet
    $ orapki wallet create -wallet /app/oracle/product/11.2.0.3/owm/wallets/fei -auto_login -pwd think4me
  • Create self-signed certificate
    $ orapki wallet add -wallet /app/oracle/product/11.2.0.3/owm/wallets/fei -dn 'CN=fei' -keysize 2048 -self_signed -validity 3650 -pwd think4me
  • Export root certificate
    $ orapki wallet export -wallet /app/oracle/product/11.2.0.3/owm/wallets/fei -dn 'CN=fei' -cert /app/oracle/product/11.2.0.3/owm/wallets/fei/client_fei_ca.cert

2. Exchange the server and client root certificates

2.1 Import client root certificate into server wallet

$ orapki wallet add -wallet /app/oracle/product/11.2.0.3/owm/wallets/server -trusted_cert -cert /app/oracle/product/11.2.0.3/owm/wallets/fei/client_fei_ca.cert -pwd Welcome1

2.2 Import server root certificate into client wallet

$ orapki wallet add -wallet /app/oracle/product/11.2.0.3/owm/wallets/fei -cert /app/oracle/product/11.2.0.3/owm/wallets/server/server_ca.cert -pwd Welcome2

3. Configure TCPS on the server and client

3.1 Server

  • listener.ora
SID_LIST_LISTENER =
  (SID_LIST =    
    (SID_DESC =  
      (GLOBAL_DBNAME = weekend.db.goweekend.ca)
      (ORACLE_HOME = /app/oracle/product/11.2.0.3)
      (SID_NAME = weekend)                           
    )                                                
  )                                                  
SSL_CLIENT_AUTHENTICATION = TRUE
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /app/oracle/product/11.2.0.3/owm/wallets/server)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = db.goweekend.ca)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCPS)(HOST = db.goweekend.ca)(PORT = 2484))
    )
  )
  •  sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)
SSL_VERSION = 0
NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME)
SSL_CLIENT_AUTHENTICATION = TRUE
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /app/oracle/product/11.2.0.3/owm/wallets/server)
    )
  )

3.2 Client

  • sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES= (TCPS)
SSL_VERSION = 0
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SSL_SERVER_DN_MATCH = Yes
SSL_CLIENT_AUTHENTICATION = TRUE
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /opt/app/oracle/product/11.2.0.2/owm/wallets/fei)
    )
  )
  •  tnsnames.ora
WEEKEND =
  (DESCRIPTION =
   (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = tcps)(HOST = db.goweekend.ca)(PORT = 2484))
   )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = weekend)
    )
    (SECURITY =
      (SSL_SERVER_CERT_DN="CN=server"))
  )

4. Configure database and user

4.1 OS_AUTHENT_PREFIX and REMOTE_OS_AUTHENT

The database parameter OS_AUTHENT_PREFIX must be null and REMOTE_OS_AUTHENT must be FALSE.
SQL> show parameter remote_os_authent
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_os_authent                    boolean     FALSE
SQL> show parameter os_authent_prefix
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string      ops$
SQL>
if any of them doesn't meet the requirements, use below command to change it.
Login as sysdba,

    SQL> alter system set remote_os_authent=FALSE scope=spfile;
    and
    SQL> alter system set os_authent_prefix='' scope=spfile;
If any above changes made, please restart the instance.

4.2 Users

The user within the database has to be created specifying the distiguished name (DN) on their certificate.

SQL> create user user2 identified externally as 'CN=fei';
The user should have have create session granted so they are able to connect,
SQL> grant create session to fei;
If the user exists, please run below command to change it.
SQL> alter user identified externally as 'CN=fei';

5. Test Connection

On client, run below command:
$ sqlplus /nolog
SQL> connect /@weekend
Connected.
SQL>