配置listener tcps加密 enable SSL encryption for Oracle SQL*Net

一配置客戶端和服務端的wallet

2端配置方法一致，相互添加證書

orapki wallet create -wallet “/u01/oracle/wallet” -pwd Wdkf984jkkgekj434FKFD -auto_login_local

orapki wallet add -wallet “/u01/oracle/wallet” -pwd Wdkf984jkkgekj434FKFD -dn “CN=`hostname`“ -keysize 1024 -self_signed -validity 3650

導出證書
orapki wallet export -wallet “/u01/oracle/wallet” -pwd Wdkf984jkkgekj434FKFD -dn “CN=`hostname`“ -cert /tmp/`hostname`-certificate.crt

導入證書
orapki wallet add -wallet “/u01/oracle/wallet” -pwd Wdkf984jkkgekj434FKFD -trusted_cert -cert /tmp/ROCKY-20160621P-certificate.crt

驗證
orapki wallet display -wallet “/u01/oracle/wallet” -pwd Wdkf984jkkgekj434FKFD

二監聽配置

2端sqlnet一致添加

WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /u01/app/oracle/wallet)
)
)
SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS,BEQ)
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)

服務端netca配置listener

客戶端添加tnsname

三驗證

tnsping ok

sqlplus 登錄ok

**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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1.1. OracleListener靜態服務配置與動態服務注冊　　1.1.1 什么是服務注冊
　　先溫習幾個基本概念：Oracle實例、Oracle數據庫、Oracle Server、Oracle服務：
　　Oracle實例=Oracle SGA內存+Oracle后臺進程
　　Oracle數據庫=核心文件（Data file,control file,logfile）
　　OracleServer=Oracle實例+Oracle數據庫
　　在HA環境中，ORACLE Server=1個ORACLE實例+1個Oracle數據庫，在兩臺主機的RAC環境中，ORACLEServer=2個ORACLE實例+1個Oracle數據庫。
　　Oracle服務：對外提供服務，1個Oracle數據庫可以有多個服務。如果需要執行連接時故障轉移或負載均衡，或者想要在RAC中配置在實例之間透明地分布連接，那么使用service_names參數將是必要的。為啟用這些功能，您只需要將每個實例的數據庫參數文件中的 service_names設置為同一個值，并在客戶端連接請求的service_name設置中引用該值。
　　服務注冊就是將數據庫作為一個服務注冊到監聽程序；無論何時啟動一個數據庫，默認地都有兩條信息注冊到監聽器中：數據庫服務器對應的實例和服務。
　　客戶端不需要知道數據庫名和實例名，只需要知道該數據庫對外提供的服務名就可以申請連接到數據庫。
　　在數據庫服務器啟動過程中，數據庫服務器會向監聽程序注冊相應的服務。
　　1.1.2 動態注冊
　　動態注冊是在instance啟動的時候PMON進程根據init.ora中的instance_name,service_names兩個參數將實例和服務動態注冊到listener中。
　　首先要在init.ora中指定instance_name,service_names兩個參數的值。在不指定這兩個參數時，系統會取默認值（實例值那么它將取init.ora文件中的db_name的值，服務名取db_name+db_domain）。
　　需要注意的是，動態注冊時，服務的注冊不僅包括service_names的注冊，如果service_names不包括缺省的 db_name+db_domain,也會將該值注冊進來。您可以在service_names參數中指定多個服務值，值之間用逗號格開，這對于共享服務器配置是很有用的。
　　由于PMON進程1分鐘執行一次，因此如果listener晚于Oracle Server啟動，那么最長需要等到1分鐘PMON會將這些服務注冊到listener進程中。
　　采取動態注冊方法時，不需要有listener.ora,該文件是指定靜態配置使用。如果有該文件，那么該文件中不能有關于該數據庫的靜態配置信息，只能有類似如下信息：
　　SID_LIST_LISTENER=
　　（SID_LIST =
　　（SID_DESC =
　　（SID_NAME =PLSExtProc）
　　（ORACLE_HOME =…）
　　（PROGRAM =extproc）
　　）
　　）

　　否則將被當做靜態注冊，動態注冊的特性失效（PMON自動注冊會失效，使用altersystem register也注冊不進去），但是如果如果使用了local_listener時，我的經驗是除了在tnsnames.ora中配置，還需要在 listener.ora中配置，此時可以動態注冊。

動態注冊默認只注冊到默認的監聽器上（名稱是LISTENER、端口是1521、協議是 TCP），因為pmon只會動態注冊port等于1521的監聽。如果需要向非默認監聽注冊，則需要配置local_listener參數，并將監聽的信息添加到tnsnames.ora文件中（或者配置local_listener參數的值為LOCAL_LISTENER=’（ADDRESS = （PROTOCOL = TCP）（HOST =xxx.xxx.xxx.xxx）（PORT = 1522）））。注意，是tnsnames.ora 文件，因為pmon在動態注冊監聽時要從tnsnames.ora中讀取相關信息。　　LISTENER =
　　（DESCRIPTION =
　　（ADDRESS = （PROTOCOL = TCP）（HOST = DaveDai）（PORT = 1522））
　　）
　　然后以sys用戶運行（這里也可以直接修改init文件）：
　　SQL> alter system set local_listener=listener scope=both;
　　SQL> alter system register;
　　或者：
　　SQL> alter system set LOCAL_LISTENER=’（ADDRESS = （PROTOCOL = TCP）（HOST = xxx.xxx.xxx.xxx）（PORT = 1522））’;
　　1.1.3 靜態注冊
　　靜態注冊就是實例啟動時讀取listener.ora文件的配置，將實例和服務注冊到監聽程序。
　　靜態注冊時，listener.ora中的GLOBAL_DBNAME向外提供服務名，listener.ora中的SID_NAME提供注冊的實例名。
　　采取靜態注冊方法時，listener.ora中的內容如下：
　　SID_LIST_LISTENER =
　　（SID_LIST =
　　（SID_DESC =
　　（SID_NAME = PLSExtProc）
　　（ORACLE_HOME = D:/oracle/product/10.2.0/db_1）
　　（PROGRAM = extproc）
　　）
　　（SID_DESC =
　　（GLOBAL_DBNAME =orcl）
　　（ORACLE_HOME = D:/oracle/product/10.2.0/db_1）
　　（SID_NAME =orcl）
　　）
　　（SID_DESC =
　　（GLOBAL_DBNAME =orcl1）
　　（ORACLE_HOME = D:/oracle/product/10.2.0/db_1）
　　（SID_NAME =orcl）
　　）
　　）
　　該文件表明數據庫是單實例的，實例名為orcl,向外提供了兩個服務：orcl和orcl1
　　靜態監聽：
　　oracle實例運行后，監聽程序啟動時，根據listener.ora的配置注冊相應的服務。
　　其中global_dbname對應的是oracle對外的服務名，即初始化參數里的service_names
　　而sid_name對應的是oralce實例的名稱，即初始化參數里的instance_name
　　1.1.4 查詢某服務是靜態注冊還是動態注冊
　　可以使用命令lsnrctl status來查看某服務是靜態注冊還是動態注冊。
　　實例狀態為UNKNOWN值時表明此服務是靜態注冊的設置。這時監聽器用來表明它不知道關于該實例的任何信息，只有當客戶發出連接請求時，它才檢查該實例是否存在。
　　動態注冊的數據庫通過狀態信息中的狀態READY或狀態BLOCKED（對于一個備用數據庫）來指明。不管關閉何時數據庫，動態注冊的數據庫都會動態地從監聽器注銷，而與之相關的信息將從狀態列表中消失。這樣，不管數據庫是在運行還是已經關閉，監聽器總是知道它的狀態。該信息將被用于連接請求的回退（fallback）和負載平衡