Monday, December 15, 2014

Sample code for encryption-only SSL using JDBC/thin driver with Oracle database

Working Code & Compilation

import java.sql.*;
import java.util.Properties;
import oracle.jdbc.pool.OracleDataSource;
import oracle.security.pki.OraclePKIProvider;
import java.security.Security;

public class SSLTestV2 {
    public static void main(String[] args) throws SQLException {
        Connection conn = getConnection();
        conn.close();
    }

    public static Connection getConnection() throws SQLException {
        OracleDataSource ods = new OracleDataSource();
        ods.setURL("jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=solaris)(PORT=1531))(CONNECT_DATA=(SERVICE_NAME=orcl1)))");
        Properties props = new Properties();
        props.setProperty("user", "donghua");
        props.setProperty("password", "Passw0rd");
        Security.insertProviderAt(new OraclePKIProvider(), 3);

        props.setProperty("oracle.net.wallet_location","(SOURCE=(METHOD=file)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/wallet)))");
        ods.setConnectionProperties(props);


        Connection conn = ods.getConnection();
        DatabaseMetaData dbmd = conn.getMetaData();
        System.out.println(dbmd.getDatabaseProductVersion());
        System.out.println("JDBC driver: " + dbmd.getDriverVersion());
        System.out.println("JDBC URL: " + dbmd.getURL());
        conn.setAutoCommit(false);
        return conn;
    }
}

oracle@solaris112:~$ /usr/java/bin/javac -cp /u01/app/oracle/product/12.1.0/dbhome_1/jdbc/lib/ojdbc7.jar:/u01/app/oracle/product/12.1.0/dbhome_1/jlib/oraclepki.jar SSLTestV2.java
oracle@solaris112:~$ /usr/java/bin/java -cp .:/u01/app/oracle/product/12.1.0/dbhome_1/jdbc/lib/ojdbc7.jar:/u01/app/oracle/product/12.1.0/dbhome_1/jlib/oraclepki.jar SSLTestV2
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
JDBC driver: 12.1.0.2.0
JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=solaris)(PORT=1531))(CONNECT_DATA=(SERVICE_NAME=orcl1)))

oracle@solaris112:~$

Not working code with possible errors:


root@solaris112:~# /usr/java/bin/java -version
java version "1.7.0_60"
Java(TM) SE Runtime Environment (build 1.7.0_60-b19)
Java HotSpot(TM) Client VM (build 24.60-b09, mixed mode)

oracle@solaris112:~$ $ORACLE_HOME/jdk/bin/java -version
java version "1.6.0_75"
Java(TM) SE Runtime Environment (build 1.6.0_75-b13)
Java HotSpot(TM) Client VM (build 20.75-b01, mixed mode)
oracle@solaris112:~$

import java.sql.*;
import java.util.Properties;
import oracle.jdbc.pool.OracleDataSource;

public class SSLTestV1 {
    public static void main(String[] args) throws SQLException {
        Connection conn = getConnection();
        conn.close();
    }

    public static Connection getConnection() throws SQLException {
        OracleDataSource ods = new OracleDataSource();
        ods.setURL("jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=solaris)(PORT=1531))(CONNECT_DATA=(SERVICE_NAME=orcl1)))");
        Properties props = new Properties();
        props.setProperty("user", "donghua");
        props.setProperty("password", "Passw0rd");
        props.setProperty("oracle.net.ssl_cipher_suites","(SSL_DH_anon_WITH_3DES_EDE_CBC_SHA, SSL_DH_anon_WITH_RC4_128_MD5,SSL_DH_anon_WITH_DES_CBC_SHA)");
        ods.setConnectionProperties(props);

        Connection conn = ods.getConnection();
        DatabaseMetaData dbmd = conn.getMetaData();
        System.out.println(dbmd.getDatabaseProductVersion());
        System.out.println("JDBC driver: " + dbmd.getDriverVersion());
        System.out.println("JDBC URL: " + dbmd.getURL());
        conn.setAutoCommit(false);
        return conn;
    }
}

oracle@solaris112:~$ $ORACLE_HOME/jdk/bin/javac -cp /u01/app/oracle/product/12.1.0/dbhome_1/jdbc/lib/ojdbc7.jar SSLTest.java
warning: oracle/jdbc/pool/OracleDataSource.class(oracle/jdbc/pool:OracleDataSource.class): major version 51 is newer than 50, the highest major version supported by this compiler.
It is recommended that the compiler be upgraded.
1 warning

oracle@solaris112:~$ $ORACLE_HOME/jdk/bin/java -cp .:/u01/app/oracle/product/12.1.0/dbhome_1/jdbc/lib/ojdbc7.jar SSLTest
Exception in thread "main" java.lang.UnsupportedClassVersionError: oracle/jdbc/pool/OracleDataSource : Unsupported major.minor version 51.0
        at java.lang.ClassLoader.defineClass1(Native Method)
        at java.lang.ClassLoader.defineClassCond(ClassLoader.java:637)
        at java.lang.ClassLoader.defineClass(ClassLoader.java:621)
        at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:141)
        at java.net.URLClassLoader.defineClass(URLClassLoader.java:283)
        at java.net.URLClassLoader.access$000(URLClassLoader.java:58)
        at java.net.URLClassLoader$1.run(URLClassLoader.java:197)
        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)
        at SSLTest.getConnection(SSLTest.java:12)
        at SSLTest.main(SSLTest.java:7)

oracle@solaris112:~$ java -cp .:/u01/app/oracle/product/12.1.0/dbhome_1/jdbc/lib/ojdbc7.jar SSLTest
Exception in thread "main" java.sql.SQLRecoverableException: IO Error: Received fatal alert: handshake_failure
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:752)
        at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:666)
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:566)
        at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:317)
        at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:241)
        at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:184)
        at SSLTest.getConnection(SSLTest.java:20)
        at SSLTest.main(SSLTest.java:7)
Caused by: javax.net.ssl.SSLHandshakeException: Received fatal alert: handshake_failure
        at sun.security.ssl.Alerts.getSSLException(Alerts.java:192)
        at sun.security.ssl.Alerts.getSSLException(Alerts.java:154)
        at sun.security.ssl.SSLSocketImpl.recvAlert(SSLSocketImpl.java:1959)
        at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:1077)
        at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1312)
        at sun.security.ssl.SSLSocketImpl.writeRecord(SSLSocketImpl.java:702)
        at sun.security.ssl.AppOutputStream.write(AppOutputStream.java:122)
        at oracle.net.ns.Packet.send(Packet.java:419)
        at oracle.net.ns.ConnectPacket.send(ConnectPacket.java:241)
        at oracle.net.ns.NSProtocolStream.negotiateConnection(NSProtocolStream.java:157)
        at oracle.net.ns.NSProtocol.connect(NSProtocol.java:264)
        at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1452)
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:496)
        ... 8 more

oracle@solaris112:~$ java -cp .:/u01/app/oracle/product/12.1.0/dbhome_1/jdbc/lib/ojdbc7.jar SSLTest
Exception in thread "main" java.sql.SQLRecoverableException: IO Error: Remote host closed connection during handshake
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:752)
        at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:666)
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:566)
        at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:317)
        at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:241)
        at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:184)
        at SSLTest.getConnection(SSLTest.java:20)
        at SSLTest.main(SSLTest.java:7)
Caused by: javax.net.ssl.SSLHandshakeException: Remote host closed connection during handshake
        at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:946)
        at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1312)
        at sun.security.ssl.SSLSocketImpl.writeRecord(SSLSocketImpl.java:702)
        at sun.security.ssl.AppOutputStream.write(AppOutputStream.java:122)
        at oracle.net.ns.Packet.send(Packet.java:419)
        at oracle.net.ns.ConnectPacket.send(ConnectPacket.java:241)
        at oracle.net.ns.NSProtocolStream.negotiateConnection(NSProtocolStream.java:157)
        at oracle.net.ns.NSProtocol.connect(NSProtocol.java:264)
        at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1452)
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:496)
        ... 8 more
Caused by: java.io.EOFException: SSL peer shut down incorrectly
        at sun.security.ssl.InputRecord.read(InputRecord.java:482)
        at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:927)
        ... 17 more
       

Database link with SSL SQL*Net encryption

SQL> connect donghua/password
Connected.
SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl2

SQL> create database link orcl1 connect to donghua identified by password using 'orcl1_tcps';

Database link created.

SQL> select instance_name from v$instance@orcl1;

INSTANCE_NAME
----------------
orcl1

From listener.log:

2014-12-15 19:31:46.280000 +08:00
15-DEC-2014 19:31:46 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl1)(CID=(PROGRAM=oracle)(HOST=solaris112)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcps)(HOST=192.168.6.101)(PORT=63856)) * establish * orcl1 * 0

How to enable SSL Authentication for Oracle Database Client

To setup SSL, refer to steps 1-7 in document “How to enable SSL encryption for Oracle SQL*Net”

http://www.dbaglobe.com/2014/12/how-to-enable-ssl-encryption-for-oracle.html

1) Configure the listeners.ora (server) & tnsnames.ora (server & client)

 

SSL_CLIENT_AUTHENTICATION = TRUE

 

SSL_CLIENT_AUTHENTICATION = TRUE

 

2) Configuring the database

 

The database parameter OS_AUTHENT_PREFIX must be null and REMOTE_OS_AUTHENT must be FALSE.

SQL> select value from v$parameter where name='remote_os_authent';

 

VALUE

--------------------------------------------------------------------------------

FALSE

SQL> select value from v$parameter where name='os_authent_prefix';

 

VALUE

--------------------------------------------------------------------------------

ops$

 

SQL> alter system set os_authent_prefix='' scope=spfile;

 

System altered.

 

SQL> startup force

 

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

SQL> create user solaris112_user identified externally as 'CN=solaris112.dbaglobe.com';

 

User created.

 

SQL>  grant create session to solaris112_user;

 

Grant succeeded.

 

SQL>  grant select on v$session_connect_info to solaris112_user;

 grant select on v$session_connect_info to solaris112_user

                 *

ERROR at line 1:

ORA-02030: can only select from fixed tables/views

 

 

SQL> grant select on v_$session_connect_info to solaris112_user;

 

Grant succeeded.

 

3) Connect as the TCPS authenticated user

oracle@solaris112:~$ sqlplus /@orcl1_tcps

 

SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 15 09:12:31 2014

 

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

 

SQL> show user

USER is "SOLARIS112_USER"

 

SQL> select sys_context('userenv','network_protocol') from dual;

 

SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')

--------------------------------------------------------------------------------

tcps

 

SQL> select userenv('sid') from dual;

 

USERENV('SID')

--------------

            36

 

SQL> set pages 999

 

SQL> select * from v$session_connect_info where sid=36;

 

       SID    SERIAL# AUTHENTICATION_TYPE        OSUSER

---------- ---------- -------------------------- ------------------------------

NETWORK_SERVICE_BANNER

--------------------------------------------------------------------------------

CLIENT_CHARSET                           CLIENT_CONNEC

---------------------------------------- -------------

CLIENT_OCI_LIBRARY          CLIENT_VERSION

--------------------------- ----------------------------------------

CLIENT_DRIVER                  CLIENT_LOBATTR          CLIENT_REGID     CON_ID

------------------------------ ----------------------- ------------ ----------

        36      63563 GLOBAL                     CN=solaris112.dbaglobe.com

 

US7ASCII                                 Homogeneous

Home-based                  12.1.0.2.0

SQL*PLUS                       Client Temp Lob Rfc On             0          0

 

        36      63563 GLOBAL                     CN=solaris112.dbaglobe.com

Authentication service for Solaris: Version 12.1.0.2.0 - Production

US7ASCII                                 Homogeneous

Home-based                  12.1.0.2.0

SQL*PLUS                       Client Temp Lob Rfc On             0          0

 

        36      63563 GLOBAL                     CN=solaris112.dbaglobe.com

Encryption service for Solaris: Version 12.1.0.2.0 - Production

US7ASCII                                 Homogeneous

Home-based                  12.1.0.2.0

SQL*PLUS                       Client Temp Lob Rfc On             0          0

 

        36      63563 GLOBAL                     CN=solaris112.dbaglobe.com

Crypto-checksumming service for Solaris: Version 12.1.0.2.0 - Production

US7ASCII                                 Homogeneous

Home-based                  12.1.0.2.0

SQL*PLUS                       Client Temp Lob Rfc On             0          0

How to enable SSL encryption for Oracle SQL*Net (Without SSH Authentication)

1) Create a directory to store all our wallets:

 

oracle@solaris:~$ mkdir /u01/app/oracle/wallet

 

oracle@solaris112:~$ mkdir /u01/app/oracle/wallet

2) Create a wallet for the primary database. Create an empty wallet with auto login enabled:

 

oracle@solaris:~$ cd /u01/app/oracle/wallet

oracle@solaris:/u01/app/oracle/wallet$ orapki wallet create -wallet /u01/app/oracle/wallet -auto_login -pwd welcome1

Oracle PKI Tool : Version 12.1.0.2

Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

 

oracle@solaris:/u01/app/oracle/wallet$ ls -l /u01/app/oracle/wallet

total 6

-rw-------   1 oracle   oinstall     120 Dec 14 23:29 cwallet.sso

-rw-rw-rw-   1 oracle   oinstall       0 Dec 14 23:29 cwallet.sso.lck

-rw-------   1 oracle   oinstall      75 Dec 14 23:29 ewallet.p12

-rw-rw-rw-   1 oracle   oinstall       0 Dec 14 23:29 ewallet.p12.lck

 

oracle@solaris112:/u01/app/oracle/wallet$ orapki wallet create -wallet /u01/app/oracle/wallet -auto_login -pwd welcome2

Oracle PKI Tool : Version 12.1.0.2

Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

 

oracle@solaris112:/u01/app/oracle/wallet$ ls -l /u01/app/oracle/wallet

total 4

-rw-------   1 oracle   oinstall     120 Dec 15 07:30 cwallet.sso

-rw-rw-rw-   1 oracle   oinstall       0 Dec 15 07:30 cwallet.sso.lck

-rw-------   1 oracle   oinstall      75 Dec 15 07:30 ewallet.p12

-rw-rw-rw-   1 oracle   oinstall       0 Dec 15 07:30 ewallet.p12.lck

 

3) Add a self-signed certificate in the wallet (a new pair of private/public keys is created):

 

oracle@solaris:/u01/app/oracle/wallet$ orapki wallet add -wallet /u01/app/oracle/wallet -dn "CN=solaris.dbaglobe.com" -keysize 1024 -self_signed -validity 365 -pwd welcome1

Oracle PKI Tool : Version 12.1.0.2

Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

 

oracle@solaris112:/u01/app/oracle/wallet$ orapki wallet add -wallet /u01/app/oracle/wallet -dn "CN=solaris112.dbaglobe.com" -keysize 1024 -self_signed -validity 365 -pwd welcome2

Oracle PKI Tool : Version 12.1.0.2

Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

 

If you display the server’s wallet you will see the following requested certificate:

oracle@solaris:/u01/app/oracle/wallet$ orapki wallet display -wallet /u01/app/oracle/wallet                                                            Oracle PKI Tool : Version 12.1.0.2

Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

 

Requested Certificates:

User Certificates:

Subject:        CN=solaris.dbaglobe.com

Trusted Certificates:

Subject:        CN=solaris.dbaglobe.com

 

oracle@solaris112:~$  orapki wallet display -wallet /u01/app/oracle/wallet                                                                             Oracle PKI Tool : Version 12.1.0.2

Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

 

Requested Certificates:

User Certificates:

Subject:        CN=solaris112.dbaglobe.com

Trusted Certificates:

Subject:        CN=solaris112.dbaglobe.com

4) Export the certificate:

oracle@solaris:/u01/app/oracle/wallet$ orapki wallet export -wallet /u01/app/oracle/wallet -dn "CN=solaris.dbaglobe.com" -cert /u01/app/oracle/wallet/solaris.dbaglobe.com.txt

Oracle PKI Tool : Version 12.1.0.2

Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

 

oracle@solaris112:~$ orapki wallet export -wallet /u01/app/oracle/wallet -dn "CN=solaris112.dbaglobe.com" -cert /u01/app/oracle/wallet/solaris112.dbaglobe.com.txt

Oracle PKI Tool : Version 12.1.0.2

Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

5) Exchange the server and client their public keys:

oracle@solaris:/u01/app/oracle/wallet$ scp solaris112:/u01/app/oracle/wallet/solaris112.dbaglobe.com.txt .

oracle@solaris:/u01/app/oracle/wallet$ scp /u01/app/oracle/wallet/solaris.dbaglobe.com.txt  solaris112:/u01/app/oracle/wallet/

 

oracle@solaris:/u01/app/oracle/wallet$ orapki wallet add -wallet /u01/app/oracle/wallet -trusted_cert -cert /u01/app/oracle/wallet/solaris112.dbaglobe.com.txt -pwd welcome1

Oracle PKI Tool : Version 12.1.0.2

Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

 

oracle@solaris112:~$ orapki wallet add -wallet /u01/app/oracle/wallet -trusted_cert -cert /u01/app/oracle/wallet/solaris.dbaglobe.com.txt -pwd welcome2

Oracle PKI Tool : Version 12.1.0.2

Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

 

oracle@solaris:/u01/app/oracle/wallet$ orapki wallet display -wallet /u01/app/oracle/wallet                                                            Oracle PKI Tool : Version 12.1.0.2

Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

 

Requested Certificates:

User Certificates:

Subject:        CN=solaris.dbaglobe.com

Trusted Certificates:

Subject:        CN=solaris112.dbaglobe.com

Subject:        CN=solaris.dbaglobe.com

oracle@solaris112:~$ orapki wallet display -wallet /u01/app/oracle/wallet

Oracle PKI Tool : Version 12.1.0.2

Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

 

Requested Certificates:

User Certificates:

Subject:        CN=solaris112.dbaglobe.com

Trusted Certificates:

Subject:        CN=solaris.dbaglobe.com

Subject:        CN=solaris112.dbaglobe.com

6) Configure the listeners to work with TCPS

 

oracle@solaris:/u01/app/oracle/wallet$ lsnrctl stop

 

Edit server side configuration use netmgr. Sample configuration as below:

 

oracle@solaris:/u01/app/oracle/wallet$ cat /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora

SSL_CLIENT_AUTHENTICATION = FALSE

WALLET_LOCATION =

  (SOURCE =

    (METHOD = FILE)

    (METHOD_DATA =

      (DIRECTORY = /u01/app/oracle/wallet)

    )

  )

 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = solaris)(PORT = 1521))

    )

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCPS)(HOST = solaris)(PORT = 1531))

    )

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

 

ADR_BASE_LISTENER = /u01/app/oracle

 

oracle@solaris:/u01/app/oracle/wallet$ cat /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora

 

 

SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)

 

SSL_VERSION = 0

 

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

 

SSL_CLIENT_AUTHENTICATION = FALSE

 

WALLET_LOCATION =

  (SOURCE =

    (METHOD = FILE)

    (METHOD_DATA =

      (DIRECTORY = /u01/app/oracle/wallet)

    )

  )

 

ADR_BASE = /u01/app/oracle

 

oracle@solaris:/u01/app/oracle/wallet$ lsnrctl start

 

7) Configure the sqlnet.ora & tnsnames.ora on the client

 

oracle@solaris112:~$ cat /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora

 

#Parameter for TDE

ENCRYPTION_WALLET_LOCATION =

  (SOURCE =

    (METHOD = FILE)

    (METHOD_DATA =

      (DIRECTORY = /u01/app/oracle/wallet)

    )

  )

 

SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)

 

SSL_VERSION = 0

 

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

 

SSL_CLIENT_AUTHENTICATION = FALSE

 

WALLET_LOCATION =

  (SOURCE =

    (METHOD = FILE)

    (METHOD_DATA =

      (DIRECTORY = /u01/app/oracle/wallet)

    )

  )

 

ADR_BASE = /u01/app/oracle

 

oracle@solaris112:~$ echo "

> ORCL1_TCPS =

>   (DESCRIPTION =

>     (ADDRESS = (PROTOCOL = TCP)(HOST = solaris)(PORT = 1521))

>     (CONNECT_DATA =

>       (SERVER = DEDICATED)

>       (SERVICE_NAME = orcl1)

>     )

>   )

>

> " >> /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora

 

8) Check the connectivity

 

oracle@solaris112:/u01/app/oracle/product/12.1.0/dbhome_1/network/admin$ tnsping ORCL1_TCPS

 

TNS Ping Utility for Solaris: Version 12.1.0.2.0 - Production on 15-DEC-2014 08:39:03

 

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

 

Used parameter files:

/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora

 

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = solaris)(PORT = 1531)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl1)))

OK (20 msec)

oracle@solaris112:~$ sqlplus system/password@orcl1_tcps

 

SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 15 08:39:57 2014

 

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

Last Successful login time: Mon Dec 15 2014 00:39:21 +08:00

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

 

SQL>  select sys_context('userenv','network_protocol') from dual;

 

SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')

--------------------------------------------------------------------------------

Tcps

SQL> select userenv('sid') from dual;

 

USERENV('SID')

--------------

            50

SQL> select * from v$session_connect_info where sid=50;

 

       SID    SERIAL# AUTHENTICATION_TYPE        OSUSER

---------- ---------- -------------------------- ------------------------------

NETWORK_SERVICE_BANNER

--------------------------------------------------------------------------------

CLIENT_CHARSET                           CLIENT_CONNEC

---------------------------------------- -------------

CLIENT_OCI_LIBRARY          CLIENT_VERSION

--------------------------- ----------------------------------------

CLIENT_DRIVER                  CLIENT_LOBATTR          CLIENT_REGID     CON_ID

------------------------------ ----------------------- ------------ ----------

        50      14986 DATABASE                   oracle

 

US7ASCII                                 Homogeneous

Home-based                  12.1.0.2.0

SQL*PLUS                       Client Temp Lob Rfc On             0          0

 

        50      14986 DATABASE                   oracle

Authentication service for Solaris: Version 12.1.0.2.0 - Production

US7ASCII                                 Homogeneous

Home-based                  12.1.0.2.0

SQL*PLUS                       Client Temp Lob Rfc On             0          0

 

        50      14986 DATABASE                   oracle

Encryption service for Solaris: Version 12.1.0.2.0 - Production

US7ASCII                                 Homogeneous

Home-based                  12.1.0.2.0

SQL*PLUS                       Client Temp Lob Rfc On             0          0

 

        50      14986 DATABASE                   oracle

Crypto-checksumming service for Solaris: Version 12.1.0.2.0 - Production

US7ASCII                                 Homogeneous

Home-based                  12.1.0.2.0

SQL*PLUS                       Client Temp Lob Rfc On             0          0

 

 

 

Possible errors 1: (To resolve it, include  –pwd parameter)

oracle@solaris:/u01/app/oracle/wallet$ orapki wallet add -wallet /u01/app/oracle/wallet -trusted_cert -cert /u01/app/oracle/wallet/solaris112.dbaglobe.com.txt

Oracle PKI Tool : Version 12.1.0.2

Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

 

Could not install trusted cert at/u01/app/oracle/wallet/solaris112.dbaglobe.com.txt

PKI-02008: Unable to modify a read-only Auto-login wallet.

 

Possible errors 2: (To resolve it, use different port number for TCP and TCPS)

 

oracle@solaris:/u01/app/oracle/wallet$ lsnrctl start

 

LSNRCTL for Solaris: Version 12.1.0.2.0 - Production on 15-DEC-2014 00:02:21

 

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

 

Starting /u01/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...

 

TNSLSNR for Solaris: Version 12.1.0.2.0 - Production

System parameter file is /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora

Log messages written to /u01/app/oracle/diag/tnslsnr/solaris/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=solaris)(PORT=1521)))

Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=solaris)(PORT=1521)))

TNS-12542: TNS:address already in use

 TNS-12560: TNS:protocol adapter error

  TNS-00512: Address already in use

   Solaris Error: 125: Address already in use

 

Listener failed to start. See the error message(s) above...

 

Possible errors 3: (To resolve it, use proper wallet location)

 

oracle@solaris112:~$ sqlplus system/p_ssw0rd@orcl1_tcps

 

SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 15 08:14:21 2014

 

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

ERROR:

ORA-28759: failure to open file

 

oracle@solaris112:~$ tnsping orcl1_tcps

 

TNS Ping Utility for Solaris: Version 12.1.0.2.0 - Production on 15-DEC-2014 08:14:50

 

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

 

Used parameter files:

/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora

 

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = solaris)(PORT = 1531)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl1)))

TNS-12560: TNS:protocol adapter error