Wednesday, July 2, 2014

How to Administer, Register and Use the Listeners Using the TCPS Protocol

1) Create an Oracle wallet: "ewallet.p12".


[oracle@vmxdb01 admin]$ mkdir $ORACLE_HOME/network/admin/cost
[oracle@vmxdb01 admin]$ orapki wallet create -wallet $ORACLE_HOME/network/admin/cost -pwd PassWd
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

2) The new wallet will contain a number of trusted certificates that are installed by default, remove all of these well known trusted certificates from the wallet.

[oracle@vmxdb01 admin]$ orapki wallet remove -trusted_cert_all -wallet $ORACLE_HOME/network/admin/cost -pwd PassWd
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

3) Create a self-signed certificate that will be stored in the wallet.


[oracle@vmxdb01 admin]$ orapki wallet add -wallet $ORACLE_HOME/network/admin/cost -self_signed -dn "cn=secure_register" -keysize 1024 -validity 3650 -pwd PassWd
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

4) View the wallet contents to confirm that it has a single trusted and user certificate.


[oracle@vmxdb01 admin]$ orapki wallet display -wallet $ORACLE_HOME/network/admin/cost -pwd PassWd -summary
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Subject:        CN=secure_register
Trusted Certificates:
Subject:        CN=secure_register

5) Make copies of the finished wallet (ewallet.p12) and place on each node in the same directory location;

$ORACLE_HOME/network/admin/cost.

6) Using orapki create a unique obfuscated auto-login file "cwallet.sso" (each node in RAC) .

Both PMON and the listener will use the cwallet.sso to establish a secure SSL connection with each other. Once COST parameters restricting registration to TCPS are enabled only instances that have access to the wallet and obfuscated wallet (cwallet.sso) will be able to register with the SCAN listeners.

[oracle@vmxdb01 admin]$ orapki wallet create -wallet $ORACLE_HOME/network/admin/cost -pwd PassWd -auto_login
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

[oracle@vmxdb01 admin]$ ls -l $ORACLE_HOME/network/admin/cost
total 8
-rw-------. 1 oracle oinstall 2493 Jul  2 22:56 cwallet.sso
-rw-------. 1 oracle oinstall 2416 Jul  2 22:53 ewallet.p12

7) Modify the permissions of the obfuscated wallet (cwallet.sso) to allow usage by authorized group members. (Optional)
8) Update the oracle home (including grid home) listener.ora & sqlnet.ora so that it references the wallet location and add the COST parameters that will restrict registration. Do this for each node.

Add the wallet location to sqlnet.ora & listener.ora.

WALLET_LOCATION =
  (SOURCE =
   (METHOD = FILE)
    (METHOD_DATA =
     (DIRECTORY =  /u01/app/11.2.0/grid/network/admin/cost)
    )
  )
[oracle@vmxdb01 ~]$ cp /u01/app/11.2.0/grid/network/admin/sqlnet.ora /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/

9) Update listener.ora to include TCPS end point

[oracle@vmxdb01 admin]$ cat listener.ora |grep -v '^#'|grep -v '^$'
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCPS)(HOST = localhost)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )
ADR_BASE_LISTENER = /u01/app/oracle
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
SECURE_PROTOCOL_LISTENER=(tcps,ipc)
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF
WALLET_LOCATION =
  (SOURCE =
   (METHOD = FILE)
    (METHOD_DATA =
     (DIRECTORY =  /u01/app/11.2.0/grid/network/admin/cost)
    )
  )

10) Update local_listener (or remote listener in RAC cases)

SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS = (PROTOCOL = TCPS)(HOST = localhost)(PORT = 1522)))';

11) (Optional) Update the tcps end point in the tnsnames.ora


[oracle@vmxdb01 admin]$ echo "
> ORCL_TCPS =
>   (DESCRIPTION =
>     (ADDRESS = (PROTOCOL = TCPS)(HOST = vmxdb01.dbaglobe.com)(PORT = 1522))
>     (CONNECT_DATA =
>       (SERVER = DEDICATED)
>       (SERVICE_NAME = orcl)
>     )
>   )
> " >> tnsnames.ora

[oracle@vmxdb01 admin]$ sqlplus system/PassWd@ORCL_TCPS

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 2 23:37:52 2014

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> select SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') from dual;

SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')
--------------------------------------------------------------------------------
tcps

SQL> exit