In my case, self-signed certification is used.
High level steps are;
1. Relink SE Oracle binary in order to support TCPS. Skip this step for EE
2. Create wallet to store self-signed cert
3. Create self-signed cert
4. Verify TCPS connection over SQL*NET
5. Configure http2 for APEX Embedded web server
Below are details.
Step 1 - Enable SE TCPS
Follow Oracle doc - How To Enable TCPS Support For Oracle Standard Edition (Doc ID 1457854.1)
-- current lib file
/software/oracle/product/12.2.0/lib> ls -l libntcps*
-rw-r--r-- 1 oracle oracle 356034 Dec 15 2016 libntcps12.a
--installer jar files
:/software/oracle/admin/INSTALLER/database/stage/Components/oracle.network.rsf/12.2.0.1.0/1/DataFiles> ls -l
total 4540
-rwxr-xr-x 1 oracle oracle 722400 Jan 26 2017 filegroup1.jar
-rwxr-xr-x 1 oracle oracle 3708288 Jan 26 2017 filegroup2.jar
-rwxr-xr-x 1 oracle oracle 212871 Jan 26 2017 filegroup3.jar
> pwd
/software/oracle/admin/INSTALLER/database/stage/Components/oracle.network.rsf/12.2.0.1.0/1/DataFiles
> $ORACLE_HOME/jdk/bin/jar tvf filegroup2.jar |grep libntcps
356034 Thu Dec 15 07:31:24 SGT 2016 lib/libntcps12_ee.a.dbl
> $ORACLE_HOME/jdk/bin/jar xvf ~/filegroup2.jar lib/libntcps12_ee.a.dbl
inflated: lib/libntcps12_ee.a.dbl
> ls -la libntcps*
-rw-r--r-- 1 oracle oracle 356034 Dec 15 2016 libntcps12.a
-rw-r--r-- 1 oracle oracle 356034 Jan 24 14:37 libntcps12_ee.a.dbl
> cp -p libntcps12.a libntcps12.a.orig
-- relink
> sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Wed Jan 24 14:39:17 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
> ps -f |grep tns
oracle 20494 20272 0 14:39 pts/0 00:00:00 grep --color=auto tns
> ps -fu oracle
UID PID PPID C STIME TTY TIME CMD
oracle 14048 14046 0 11:20 ? 00:00:01 sshd: oracle@pts/1
oracle 14049 14048 0 11:20 pts/1 00:00:00 -ksh
oracle 19604 1 0 14:04 ? 00:00:00 /software/oracle/product/12.2.0/bin/tnslsnr LISTENER_ORCL -inherit
oracle 20271 20269 0 14:28 ? 00:00:00 sshd: oracle@pts/0
oracle 20272 20271 0 14:28 pts/0 00:00:00 -ksh
oracle 20500 20272 0 14:40 pts/0 00:00:00 ps -fu oracle
> lsnrctl stop LISTENER_ORCL
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 24-JAN-2018 14:40:10
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1588)))
The command completed successfully
> relink all
writing relink log to: /software/oracle/product/12.2.0/install/relink.log
> ls -la libntcps*
-rw-r--r-- 1 oracle oracle 356034 Dec 15 2016 libntcps12.a
-rw-r--r-- 1 oracle oracle 356034 Dec 15 2016 libntcps12.a.orig
-rw-r--r-- 1 oracle oracle 356034 Jan 24 14:37 libntcps12_ee.a.dbl
> ls -l /software/oracle/product/12.2.0/install/relink.log
-rw-r--r-- 1 oracle oracle 169637 Jan 24 14:41 /software/oracle/product/12.2.0/install/relink.log
#highlighted are updated files
ls -lrt
...
-rw-r--r-- 1 oracle oracle 22215298 Jan 24 12:01 libcommon12.a
-rw-r--r-- 1 oracle oracle 36700774 Jan 24 12:01 libgeneric12.a
-rwxr-xr-x 1 oracle oracle 2032800 Jan 24 12:01 libasmclntsh12.so
-rw-r--r-- 1 oracle oracle 356034 Jan 24 14:37 libntcps12_ee.a.dbl
-rw-r--r-- 1 oracle oracle 14683 Jan 24 14:40 libskgxn2.so
-rw------- 1 oracle oracle 137 Jan 24 14:40 ldflagsO
-rw-r--r-- 1 oracle oracle 137 Jan 24 14:40 ldflags
-rw-r--r-- 1 oracle oracle 2872 Jan 24 14:40 ntcontab.o
-rw-r--r-- 1 oracle oracle 1968 Jan 24 14:40 nnfgt.o
-rw-r--r-- 1 oracle oracle 9619442 Jan 24 14:40 libn12.a
-rwxr-xr-x 1 oracle oracle 8033736 Jan 24 14:40 libclntshcore.so.12.1
-rw-r--r-- 1 oracle oracle 1687689 Jan 24 14:40 clntshcore.map
lrwxrwxrwx 1 oracle oracle 21 Jan 24 14:40 libclntshcore.so -> libclntshcore.so.12.1
-rwxr-xr-x 1 oracle oracle 71653096 Jan 24 14:40 libclntsh.so.12.1
-rw-r--r-- 1 oracle oracle 5080271 Jan 24 14:40 clntsh.map
lrwxrwxrwx 1 oracle oracle 17 Jan 24 14:40 libclntsh.so -> libclntsh.so.12.1
lrwxrwxrwx 1 oracle oracle 12 Jan 24 14:40 libclntsh.so.11.1 -> libclntsh.so
lrwxrwxrwx 1 oracle oracle 12 Jan 24 14:40 libclntsh.so.10.1 -> libclntsh.so
-rwxr-xr-x 1 oracle oracle 2222528 Jan 24 14:40 libocci.so.12.1
lrwxrwxrwx 1 oracle oracle 15 Jan 24 14:40 libocci.so -> libocci.so.12.1
-rwxr-xr-x 1 oracle oracle 2553264 Jan 24 14:40 libagtsh.so.1.0
lrwxrwxrwx 1 oracle oracle 15 Jan 24 14:40 libagtsh.so -> libagtsh.so.1.0
-rwxr-xr-x 1 oracle oracle 194936 Jan 24 14:40 libsrvmm2.so
-rw-r--r-- 1 oracle oracle 1192447 Jan 24 14:40 libskgxp12.so
Step 2 - create wallet
Use oracle wallet manager - owm to create an empty wallet.
Step 3 - create self-signed cert
ORCL:localhost:/software/oracle/admin/ORCL/wallet> orapki wallet add -wallet ./ -pwd Oracle123$ \
> -dn "CN=`hostname`, OU=DBA, O=mm, L=SG, ST=SG, C=SG" \
> -keysize 1024 -self_signed -validity 3650
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
Operation is successfully completed.
ORCL:localhost:/software/oracle/admin/ORCL/wallet> orapki wallet display -wallet ./ -pwd Oracle123$
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
Requested Certificates:
Subject: C=SG,ST=SG,L=SG,O=mm,OU=dba,CN=dbmate-cert
User Certificates:
Subject: CN=localhost,OU=DBA,O=mm,L=SG,ST=SG,C=SG
Trusted Certificates:
Subject: CN=localhost,OU=DBA,O=mm,L=SG,ST=SG,C=SG
Step 4 - verify TCPS
-- config sqlnet
ORCL:localhost:/software/oracle/product/12.2.0/network/admin> cat sqlnet.ora
# sqlnet.ora Network Configuration File: /software/oracle/product/12.2.0/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SSL_CLIENT_AUTHENTICATION = FALSE
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /software/oracle/admin/ORCL/wallet )
)
)
ORCL:localhost:/software/oracle/product/12.2.0/network/admin> cat listener.ora
# listener.ora Network Configuration File: /software/oracle/product/12.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER_ORCL =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1588))
(ADDRESS = (PROTOCOL = TCPS)(HOST = localhost)(PORT = 1589))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SSL_CLIENT_AUTHENTICATION = FALSE
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /software/oracle/admin/ORCL/wallet )
)
)
--tnsnames.ora
ORCL_SSL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCPS)(HOST = localhost)(PORT = 1589))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)
ORCL:localhost:/software/oracle/product/12.2.0/network/admin> sqlplus perfstat@ORCL_SSL
SQL*Plus: Release 12.2.0.1.0 Production on Fri Jan 26 11:00:54 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Enter password:
Last Successful login time: Fri Jan 26 2018 11:00:14 +08:00
Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
SQL> select sys_context('userenv','network_protocol') from dual;
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')
--------------------------------------------------------------------------------
tcps
SQL> exit
Disconnected from Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
ORCL:localhost:/software/oracle/product/12.2.0/network/admin> tnsping ORCL_SSL
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 26-JAN-2018 11:01:17
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
/software/oracle/product/12.2.0/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = localhost)(PORT = 1589)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL)))
OK (10 msec)
Step 5. Configure http2 for APEX Embedded web server
Set http2-port and http2-protocol in the XDB configuration:
See Note 942945.1
5.1 Set dispatcher for TCPS in database parameter file
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)','(PROTOCOL=TCPS) (SERVICE=ORCLXDB)'
5.2 set apex /Manage Instance/Requires HTTPS = always.
My database is 12.2c , the APEX http port was 8888, I am using same port for https , as no more HTTP will be supported.
SQL> select dbms_xdb_config.gethttpport() from dual;
DBMS_XDB_CONFIG.GETHTTPPORT()
-----------------------------
8888
SQL> exec dbms_xdb_config.sethttpport(8080);
PL/SQL procedure successfully completed.
SQL> select dbms_xdb_config.gethttpport() from dual;
DBMS_XDB_CONFIG.GETHTTPPORT()
-----------------------------
8080
SQL> call DBMS_XDB_CONFIG.SETLISTENERENDPOINT(2, null, 8888,2);
Call completed.
SQL> select dbms_xdb_config.gethttpport() from dual;
DBMS_XDB_CONFIG.GETHTTPPORT()
-----------------------------
8080
select extractValue(value(x),'/httpconfig/http2-protocol', 'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"') "Protocol"
2 , extractValue(value(x),'/httpconfig/http2-port', 'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"') "Port#"
3 from table(xmlsequence(extract(xdburitype('/xdbconfig.xml').getXML(),'/xdbconfig/sysconfig/protocolconfig/httpconfig'))) x
4 /
Protocol
--------------------------------------------------------------------------------
Port#
--------------------------------------------------------------------------------
tcps
8888
Cheers!
Popular Posts
-
configured "CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK; " when RMAN> delete noprompt archivelog u...
-
I have one PT env using storage sync for database sync. Thought need to re-register DB after DB refresh, so unregister and register again....
-
Encountered this problem during import data from 9i to 10g database. Table structure was already laid out. . . importing table &...
-
[symptoms] orarac2poc:ORCL2:/home/oracle> . ./oms_agent_env orarac2poc:ORCL2:/home/oracle> $AGENT_HOME/bin/emctl start agent Oracl...
-
I hit the error when using RMAN duplicate command to create a physical standby database, with purpose of using same directory structure in...
-
As I have CPU issue with physical standby database in previous post, I have to remove it before I have the solution. In 12c Enterprise Man... -
RMAN> show all; RMAN configuration parameters for database with db_unique_name ORCL are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # de...
-
after rounds of recovery testing, I believer there are tons of obsolete files in FRA. There are some stubborn datafile copy can't be de...
-
SQL> select * from dba_data_files where tablespace_name='TESTBS'; FILE_NAME ...
-
Oracle collects Active Session History (ASH) statistics (mostly wait statistics for different events) for all active sessions every second f...