Monthly Archives: October 2014

Multitenant PDB connects with SID descriptor

Leave a reply

When moving to the Oracle 12c multitenant architecture one implication is that you have to switch to using service names in your connection strings for PDBs. Even SYSDBA connects to PDBs must use SERVICE_NAME.
Well, not quite necessarily. I guess customers adopting this new technology have not all managed to update their scripts. Or, software vendors and in-house development have hard-coded connection strings that cannot be changed quickly.
So, Oracle hastily added a new listener parameter USE_SID_AS_SERVICE_listener. This will tell the listener to treat the SID in the connect descriptor as SERVICE_NAME.
As long as the SID in the client connect descriptor matches the service name registered on the listener the connection is established.

$ cat /u01/app/oracle/network/admin/listener.ora 
LISTENER =
	(ADDRESS_LIST =
		(ADDRESS = 
			(PROTOCOL = TCP)
			(Host = oel6ora12cdb1.localdomain)
			(Port = 1521)
		)
	)

USE_SID_AS_SERVICE_LISTENER = ON

I have two PDBs which are registered on the listener as follows.

$ lsnrctl status
...
Services Summary...
Service "CDB1" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "testdb1" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "testdb2" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...

My client tnsnames.ora holds entries for both PDBs, one with SERVICE_NAME (TESTDB1) and one with SID (TESTDB2).

$ cat /u01/app/oracle/network/admin/tnsnames.ora 
TESTDB1 =
  (DESCRIPTION =
	(ADDRESS = (PROTOCOL = TCP)(HOST = oel6ora12cdb1.localdomain)(PORT = 1521))
	(CONNECT_DATA =
	  (SERVER = DEDICATED)
	  (SERVICE_NAME = TESTDB1)
	)
  )

TESTDB2 =
  (DESCRIPTION =
	(ADDRESS = (PROTOCOL = TCP)(HOST = oel6ora12cdb1.localdomain)(PORT = 1521))
	(CONNECT_DATA =
	  (SERVER = DEDICATED)
	  (SID = TESTDB2)
	)
  )

Next, I connect to both PDBs using above TNS entries.

$ sqlplus /nolog

SQL> connect system/manager@PDB1
Connected.

SQL> connect system/manager@PDB2
Connected.

Well, that’s all fine.
But, what’s going to happen if we set the DB_DOMAIN, then the db domain is appended to the service name. Ultimately, the SID parameter you have in the client connect descriptor does not match the service name anymore.

Since you most likely use above described feature because you cannot change the client connect descriptor this solution does not work anymore.
Back to square one.

Just for fun let’s try it anyway…but of course, for it to work I have to adjust the SID in the TNS entry for TESTDB2.

SQL> show parameter db_domain

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_domain			     string	 localdomain

On the listener there are the two services from my pluggable databases, TESTDB1 and TESTDB2.

$ lsnrctl status
...
Services Summary...
Service "CDB1.localdomain" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "testdb1.localdomain" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "testdb2.localdomain" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
The command completed successfully

Now, to match the service name I change the TNS entry for TESTDB2 to following:

TESTDB2.localdomain =
  (DESCRIPTION =
	(ADDRESS = (PROTOCOL = TCP)(HOST = oel6ora12cdb1.localdomain)(PORT = 1521))
	(CONNECT_DATA =
	  (SERVER = DEDICATED)
	  (SID = TESTDB2.localdomain)
	)
  )

At first sight it’s a bit strange to see the db domain in the SID parameter as this was never supposed to work this way. But at least we can connect again.

$ sqlplus system/manager@TESTDB2.localdomain
Connected.

I thought about creating a service using DBMS_SERVICE.CREATE_SERVICE but Oracle will always append the db domain to the service on the listener. And since a service in the format “PDB_NAME.DB_DOMAIN” already exists you will not be able to create it (ORA-44303: service name exists).

I file this under half-baked solution…(Not that I think this feature should by used anyway. Go get your scripts and applications fixed!)

Scripting mkstore

8 Replies

I had the pleasure to load a wallet (secure external password store) with around 180 credentials. Faced with a repeating task, I always try to automate things as much as possible or at least to the level I deem sensible. In this case my urge not to waste an afternoon typing in the same stuff over and over was overwhelming.
Unfortunately, the “mkstore” utility does not have a command switch to provide the wallet password. No problem, on Linux/Unix I would have just piped in the wallet password:

echo MyWallet-Password | mkstore -wrl /u01/app/oracle/etc/wallet/auth -createCredential DEV1.localdomain SYSTEM manager

Working on Windows using batch scripts piping does not work with “mkstore”. Here’s what I came up with:

echo | set /p="MyWallet-Password" > C:\Temp\walletpwd.txt

mkstore -wrl D:\app\oracle\etc\wallet\auth -createCredential DEV1.localdomain SYSTEM manager < C:\Temp\walletpwd.txt
...
...
...

Since we were generating new passwords the service account on all databases I went over the Enterprise Manager repository to generate the “mkstore” calls:

select
     'mkstore -wrl "D:\app\oracle\etc\wallet\auth" -createCredential '
  || tgt.target_name
  || ' srvacc "'
  || gen_pwd
  || '" < C:\Temp\walletpwd.txt'
from sysman.mgmt_targets tgt
  join sysman.mgmt_target_properties tgsid on (tgsid.target_guid = tgt.target_guid)
where tgt.target_type = 'oracle_database'
and tgsid.property_name = 'SID'
order by tgsid.property_value
;

Now I could just copy & paste the query output into a command line window. DONE.

Please remember to delete the temporary file containing the wallet password:

del /F /S /Q C:\Temp\walletpwd.txt

Although it saved me a lot of typing I’m not quite happy with the solution. The reason is I do temporarily write the wallet password to a file in plain text. I do not like that at all. And in some environments this might be considered a security breach, even if the file is only there for a couple of minutes.

If you come up with a solution that works without storing the wallet password in a file to redirect it to STDIN I’ll be happy to know about.

Don’t ditch AUDIT_TRAIL prematurely

1 Reply

When migrating to unified auditing in Oracle 12c I faced a strange behaviour in the recording of LOGON / LOGOFF actions.

As the Oracle 12c documentation points out, the AUDIT_* parameters have no effect once you enable unified auditing. Guess what I did in the good spirit of cleaning up obsolete settings:
I reset those parameters in the spfile after enabling unified auditing.

Enable unified auditing by relinking the oracle binary.

$ cd ${ORACLE_HOME}/rdbms/lib
$ make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=${ORACLE_HOME}

SQL> show parameter audit_trail
NAME          TYPE        VALUE
------------- ----------- ----------
audit_trail   string      NONE

I disabled all the Oracle built-in audit policies and created my own to record LOGON / LOGOFF actions.

SQL> create audit policy audpol_connection
     actions logon, logoff;

Audit policy created.

SQL> audit policy audpol_connection;

Audit succeeded.

SQL> select * from audit_unified_enabled_policies;

USER_NAME       POLICY_NAME          ENABL SUC FAI
--------------- -------------------- ----- --- ---
ALL USERS       AUDPOL_CONNECTION    BY    YES YES

SQL> select * from audit_unified_policies
     where policy_name = 'AUDPOL_CONNECTION'
     and audit_option like '%LOGO%';

POLICY_NAME          AUDIT CONDI AUDIT_OPTION    AUDIT_OPTION_TY OBJEC OBJEC OBJEC COMMO
-------------------- ----- ----- --------------- --------------- ----- ----- ----- -----
AUDPOL_CONNECTION    NONE  NONE  LOGOFF          STANDARD ACTION NONE  NONE  NONE
AUDPOL_CONNECTION    NONE  NONE  LOGON           STANDARD ACTION NONE  NONE  NONE

This shows, that my audit policy is enabled and should record LOGON and LOGOFF actions.

I also want the audit trail to be written immediately so it becomes visible in UNIFIED_AUDIT_TRAIL view without waiting for the flush to happen (or explicitly flushing it every time).

SQL> exec dbms_audit_mgmt.set_audit_trail_property(
         dbms_audit_mgmt.audit_trail_unified
       , dbms_audit_mgmt.audit_trail_write_mode
       , dbms_audit_mgmt.audit_trail_immediate_write
     )

PL/SQL procedure successfully completed.

For the sake of clean output let’s purge the existing audit trail.

SQL> exec dbms_audit_mgmt.clean_audit_trail(
         dbms_audit_mgmt.audit_trail_unified
       , false
     )

PL/SQL procedure successfully completed.

So far so good. Now begins the actual test case by connecting to the database from a different terminal.

$ sqlplus system/manager@dev1

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 21 17:39:02 2014
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Last Successful login time: Tue Oct 21 2014 17:38:49 +02:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options

Note that I have not logged out yet…

Checking in my original session we cannot find any LOGON event recorded. I most definitively expected to see a LOGON event.

SQL> select audit_type, sessionid, dbusername, event_timestamp, action_name, return_code
     from unified_audit_trail
     where action_name like '%LOG%'
     order by event_timestamp;

no rows selected

After disconnecting from the other session the LOGOFF event becomes visible.

SQL> select audit_type, sessionid, dbusername, event_timestamp, action_name, return_code
     from unified_audit_trail
     where action_name like '%LOG%'
     order by event_timestamp;

AUDIT_TYPE SESSIONID    DBUSERNAME    EVENT_TIMESTAMP                 ACTION_NAME     RETURN_CODE
---------- ------------ ------------- ------------------------------- --------------- -----------
Standard   2253237941   SYSTEM        21-OCT-14 05.13.38.712564 PM    LOGOFF          0

I have run multiple different test with different clients, connection methods, etc. all ending with the same result: Oracle just wont record LOGON actions! We only get to see the LOGOFF event.

Against all odds and feeling a little desperate I eventually decided to set “AUDIT_TRAIL = DB” and give it a go.

SQL> show parameter audit_trail

NAME          TYPE        VALUE
------------- ----------- ----------
audit_trail   string      DB

I purged the audit trail and ran the same test as before again.

Connect from a differen terminal to the database.

$ sqlplus system/manager@dev1

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 21 17:39:02 2014
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Last Successful login time: Tue Oct 21 2014 17:38:49 +02:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options

Check the unified audit trail – and there it is.

SQL> select audit_type, sessionid, dbusername, event_timestamp, action_name, return_code
     from unified_audit_trail
     where action_name like '%LOG%'
     order by event_timestamp;

AUDIT_TYPE SESSIONID    DBUSERNAME    EVENT_TIMESTAMP                 ACTION_NAME     RETURN_CODE
---------- ------------ ------------- ------------------------------- --------------- -----------
Standard   1706589952   SYSTEM        21-OCT-14 07.17.46.063184 PM    LOGON           0

After disconnecting from the other session I can again see the LOGOFF action.

SQL> select audit_type, sessionid, dbusername, event_timestamp, action_name, return_code
     from unified_audit_trail
     where action_name like '%LOG%'
     order by event_timestamp;

AUDIT_TYPE SESSIONID    DBUSERNAME    EVENT_TIMESTAMP                 ACTION_NAME     RETURN_CODE
---------- ------------ ------------- ------------------------------- --------------- -----------
Standard   1706589952   SYSTEM        21-OCT-14 07.17.46.063184 PM    LOGON           0
Standard   1706589952   SYSTEM        21-OCT-14 07.18.02.661384 PM    LOGOFF          0

In my opinion this does not work as designed as I believe Oracle actually wanted the AUDIT_* parameters to have no effect on unified auditing. I haven’t tested exhaustively but I imagine there might be other problems with unified auditing when AUDIT_TRAIL is set to NONE. So, test carefully whether all your auditing is working as expected.

Foot note: All test have been run on Oracle 12.1.0.2