Approximate Count Distinct–Oracle Database 12c

 

Not every query requires a completely accurate result, for example “How many distinct individuals visited our website last week?”.

New SQL function for approximate results for COUNT DISTINCT aggregates –APPROX_COUNT_DISTINCT()

• Approximate results can be significantly faster and use less resources than exact calculations
• –5x to 50x ++ times faster (depending upon number of distinct values and complexity of SQL)
  –Accuracy > 97% (with 95% confidence)

Example:

SQL> select count(distinct prod_id) from sh.sales;

COUNT(DISTINCTPROD_ID)
----------------------
                    72

SQL> select approx_count_distinct(prod_id) from sh.sales;

APPROX_COUNT_DISTINCT(PROD_ID)
------------------------------
                            72

SQL>  select count(distinct cust_id) from sh.sales;

COUNT(DISTINCTCUST_ID)
----------------------
                  7059

SQL> select approx_count_distinct(cust_id) from sh.sales;

APPROX_COUNT_DISTINCT(CUST_ID)
------------------------------
                          7014

Cannot drop suspect mirror database? Here is explanation and workaround

·         Mirror database is suspect (hardware failure causing corruption), SQL Server can’t apply the redo logs on the mirror

·         With the intent to recover DB and reestablish mirroring, ALTER DATABASE … SET PARTNER OFF is issued on the principal

o   DB Mirroring breaks DBM session from the principal point of view, and then sends the message to the mirror

o   Mirror tries to break the DBM session; however, SQL Server attempts to apply all the remaining log in the redo queue before it breaks the DBM session. This is by design. The goal is to apply all the logs it has received because the user may be attempting to break the mirror so as to read the data on the mirror database. It makes sense to apply as much redo log records as possible

·         DBM session is not broken from the mirror point of view

·         All subsequent actions (drop database, restore database) are blocked, because SQL Server still views this as a mirror database

Workaround:

Shutdown the instance and remove the mdf, ndf and ldf for the suspect database, and start the instance. You should be able to restore the instance, and recreate the mirror.

SQL Text Expansion in Oracle 12c

This procedure will be handy if you are asked to tune a query, which looks like short, but actual complexity hidden by the views. You can use this procedure to expand the view definition into the query.

https://docs.oracle.com/database/121/ARPLS/d_util.htm#ARPLS73973

SQL> variable x clob
SQL> begin
  2    dbms_utility.expand_sql_text(
  3      input_sql_text => 'select * from hr.emp_details_view where employee_id=107',
  4      output_sql_text => :x
  5    );
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> print x

X
--------------------------------------------------------------------------------
SELECT "A1"."EMPLOYEE_ID" "EMPLOYEE_ID","A1"."JOB_ID" "JOB_ID","A1"."MANAGER_ID"

SQL> set long 2000
SQL> set longc 2000
SQL> set pages 999
SQL> print x

X
--------------------------------------------------------------------------------
SELECT "A1"."EMPLOYEE_ID" "EMPLOYEE_ID","A1"."JOB_ID" "JOB_ID","A1"."MANAGER_ID"
"MANAGER_ID","A1"."DEPARTMENT_ID" "DEPARTMENT_ID","A1"."LOCATION_ID" "LOCATION_
ID","A1"."COUNTRY_ID" "COUNTRY_ID","A1"."FIRST_NAME" "FIRST_NAME","A1"."LAST_NAM
E" "LAST_NAME","A1"."SALARY" "SALARY","A1"."COMMISSION_PCT" "COMMISSION_PCT","A1
"."DEPARTMENT_NAME" "DEPARTMENT_NAME","A1"."JOB_TITLE" "JOB_TITLE","A1"."CITY" "
CITY","A1"."STATE_PROVINCE" "STATE_PROVINCE","A1"."COUNTRY_NAME" "COUNTRY_NAME",
"A1"."REGION_NAME" "REGION_NAME" FROM  (SELECT "A7"."EMPLOYEE_ID" "EMPLOYEE_ID",
"A7"."JOB_ID" "JOB_ID","A7"."MANAGER_ID" "MANAGER_ID","A7"."DEPARTMENT_ID" "DEPA
RTMENT_ID","A6"."LOCATION_ID" "LOCATION_ID","A4"."COUNTRY_ID" "COUNTRY_ID","A7".
"FIRST_NAME" "FIRST_NAME","A7"."LAST_NAME" "LAST_NAME","A7"."SALARY" "SALARY","A
7"."COMMISSION_PCT" "COMMISSION_PCT","A6"."DEPARTMENT_NAME" "DEPARTMENT_NAME","A
5"."JOB_TITLE" "JOB_TITLE","A4"."CITY" "CITY","A4"."STATE_PROVINCE" "STATE_PROVI
NCE","A3"."COUNTRY_NAME" "COUNTRY_NAME","A2"."REGION_NAME" "REGION_NAME" FROM HR
."EMPLOYEES" "A7",HR."DEPARTMENTS" "A6",HR."JOBS" "A5",HR."LOCATIONS" "A4",HR."C
OUNTRIES" "A3",HR."REGIONS" "A2" WHERE "A7"."DEPARTMENT_ID"="A6"."DEPARTMENT_ID"
AND "A6"."LOCATION_ID"="A4"."LOCATION_ID" AND "A4"."COUNTRY_ID"="A3"."COUNTRY_I
D" AND "A3"."REGION_ID"="A2"."REGION_ID" AND "A5"."JOB_ID"="A7"."JOB_ID") "A1" W
HERE "A1"."EMPLOYEE_ID"=107

SQL> set lin 20000

SQL> print x

X
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT "A1"."EMPLOYEE_ID" "EMPLOYEE_ID","A1"."JOB_ID" "JOB_ID","A1"."MANAGER_ID" "MANAGER_ID","A1"."DEPARTMENT_ID" "DEPARTMENT_ID","A1"."LOCATION_ID" "LOCATION_ID","A1"."COUNTRY_ID" "COUNTRY_ID","A1"."FIRST_NAME" "FIRST_NAME","A1"."LAST_NAME" "LAST_NAME","A1"."SALARY" "SALARY","A1"."COMMISSION_PCT" "COMMISSION_PCT","A1"."DEPARTMENT_NAME" "DEPARTMENT_NAME","A1"."JOB_TITLE" "JOB_TITLE","A1"."CITY" "CITY","A1"."STATE_PROVINCE" "STATE_PROVINCE","A1"."COUNTRY_NAME" "COUNTRY_NAME","A1"."REGION_NAME" "REGION_NAME" FROM  (SELECT "A7"."EMPLOYEE_ID" "EMPLOYEE_ID","A7"."JOB_ID" "JOB_ID","A7"."MANAGER_ID" "MANAGER_ID","A7"."DEPARTMENT_ID" "DEPARTMENT_ID","A6"."LOCATION_ID" "LOCATION_ID","A4"."COUNTRY_ID" "COUNTRY_ID","A7"."FIRST_NAME" "FIRST_NAME","A7"."LAST_NAME" "LAST_NAME","A7"."SALARY" "SALARY","A7"."COMMISSION_PCT" "COMMISSION_PCT","A6"."DEPARTMENT_NAME" "DEPARTMENT_NAME","A5"."JOB_TITLE" "JOB_TITLE","A4"."CITY" "CITY","A4"."STATE_PROVINCE" "STATE_PROVINCE","A3"."COUNTRY_NAME" "COUNTRY_NAME","A2"."REGION_NAME" "REGION_NAME" FROM HR."EMPLOYEES" "A7",HR."DEPARTMENTS" "A6",HR."JOBS" "A5",HR."LOCATIONS" "A4",HR."COUNTRIES" "A3",HR."REGIONS" "A2" WHERE "A7"."DEPARTMENT_ID"="A6"."DEPARTMENT_ID" AND "A6"."LOCATION_ID"="A4"."LOCATION_ID" AND "A4"."COUNTRY_ID"="A3"."COUNTRY_ID" AND "A3"."REGION_ID"="A2"."REGION_ID" AND "A5"."JOB_ID"="A7"."JOB_ID") "A1" WHERE "A1"."EMPLOYEE_ID"=107

Use SQL Developer to format the SQL Statement:

How to fix SQL Developer Unsupported JDK Version. (You are attempting to run with java …”

Change the Value for “SetJavaHome” in %APPDATA%\sqldeveloper\<version>\product.conf.

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