Posts mit dem Label cman werden angezeigt. Alle Posts anzeigen
Posts mit dem Label cman werden angezeigt. Alle Posts anzeigen

2023-06-29

SOURCE_ROUTE in easy connect plus syntax

I regularly use Oracle connection manager (CMAN) to simplify connectivity to the database in complex IT environments. Normally the network infrastructure between the CMAN host and the target service is transparent for the client. 

but in some cases, the client can (or need) to define not only the connection to the CMAN, but also the 2nd leg to the DB service. This is called SOURCE_ROUTE in Oracles network naming. 

A common (quite simple) connection description is this tnsnames.ora entry: 


ALIAS=
 (DESCRIPTION= 
   (SOURCE_ROUTE=yes) 
   (ADDRESS=(PROTOCOL=tcp)(HOST=CMAN_HOST)(PORT=1521))  
   (ADDRESS=(PROTOCOL=tcp)(HOST=DB_INSTANCE_HOST)(PORT=1521))  
   (CONNECT_DATA=(SERVICE_NAME=DB_Service))
 )

By the definition SOURCE_ROUTE=yes the first entry defines the direct connection to the CMAN_HOST. In this connection the 2nd connection is described implicit to go to DB_INSTANCE_HOST

 

But sometimes, a tnsnames.ora entry isn't useful and a Easy Connect syntax is preferred. The simplest syntax does not show any way for a multi step connection. 



host:port/SERVICE_NAME


But this very simple syntax


the full possible syntax is

[[protocol:]//]host1{,host2}[:port1]{,host2:port2}[/[service_name][:server_type][/instance_name]][?parameter_name1=value1{&parameter_name2= value2}]
In a more specific case it looks like

//CMAN_HOST,DB_INSTANCE_HOST:1521/DB_Service?SOURCE_ROUTE=YES
And it works quite fine. I can do a simple test with sqlplus:

sqlplus x/x@CMAN_HOST,DB_INSTANCE_HOST:1521/DB_Service?SOURCE_ROUTE=YES

SQL*Plus: Release 21.0.0.0.0 - Production on Mon Jun 26 10:54:02 2023
Version 21.3.0.0.0

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

ERROR:
ORA-01017: invalid username/password; logon denied
And ORA-01017 is exactly what I expect: I could reach the instance, but of course authentication for user x with password x failed.

Another feature worth to mention is tnsping. This only tries to connect to the given listener but never checks for the service available. In addition it also prints the connection it tested in tnsnames.ora format. A simple connect string looks like: 

tnsping CMAN_HOST:1521/DB_Service

TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 26-JUN-2023 10:53:44

Copyright (c) 1997, 2021, Oracle.  All rights reserved.

Used parameter files:
/tns/admin/sqlnet.ora

Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=DB_Service))(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.2)(PORT=1521)))
OK (10 msec)
But even with a source route defined, tnsping is quite honest what's (not) doing: 


tnsping CMAN_HOST:1521,DB_INSTANCE_HOST:1521/DB_Service?SOURCE_ROUTE=YES

TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 26-JUN-2023 10:53:51

Copyright (c) 1997, 2021, Oracle.  All rights reserved.

Used parameter files:
/tns/admin/sqlnet.ora

Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=DB_Service))(SOURCE_ROUTE=YES)(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.2)(PORT=1521)))
OK (10 msec)
It adds the SOURCE_ROUTE=YES entries, but does not add the 2nd host (or IP) it doesn't even try to reach the DB_INSTANCE_HOST

The usage of tnsping is limited in any cman environment. 

2023-03-16

OEM 13.5 - the missing CMAN metrics (confusion of languages)

 As my journey with Oracle Connection Manager (cman) and Enterprise Manger (OEM) continues, I tried to get used to the metrics Oracle provides for cman out of the box. Even I can not change the Metric Thresholds for cman (Note:2590064.1), at least I'd like to see any telemetry. 

But unfortunately OEM always shows zero. Also other Metrics like the general status shows a lot of empty fields, and even the "Serviced Databases" view is empty. Of course I opened a SR at My Oracle Support (MOS), but the effect was only some back & forth of emctl, cmctl commands and their logfiles. Some basic Tests I provided showed at least the problem might be somewhere on the agents side. 

CMAN shows some active connections:


cmctl show connections -c cman_xxx


CMCTL for Linux: Version 21.0.0.0.0 - Production on 03-MAR-2023 10:21:51

Copyright (c) 1996, 2021, Oracle. All rights reserved.

Current instance cman_xxx is already started
Connecting to (DESCRIPTION=(address=(protocol=tcp)(host=xxx)(port=1521)))
Number of connections: 24.
The command completed successfully.

and also the cmanload.pl script provided some values:


export CMAN_ORACLE_HOME=$ORACLE_HOME
export CMAN_NAME=cman_xxx
export CMAN_ORA_DIR=$TNS_ADMIN
export CMAN_MACHINE=xxx
export CMAN_PROTOCOL="TCP"
export CMAN_PORT="1521"
unset CMAN_PASSWORD

cd $AGENT_HOME/agent_13.5.0.0.0/plugins/oracle.sysman.db.agent.plugin_13.5.1.0.0/scripts
perl cmanload.pl
em_result=1270|0|24

but the agent is not capable of getting these informations properly: 


oracle@xxx:~ $ $AGENT_HOME/agent_inst/bin/emctl getmetric agent cman_xxx,oracle_cman,Load
Oracle Enterprise Manager Cloud Control 13c Release 5
Copyright (c) 1996, 2021 Oracle Corporation. All rights reserved.
estConnsTotal,refConnsTotal,activeConnsTotal,estConns,refConns
0,0,0,0,0

At least I'm quite confident the problem is somewhere on the agents side, not in OEM or its repository. 

Unfortunately the script cmanload.pl is poorly instrumented, so even setting 
emctl setproperty agent -name 'EMAGENT_PERL_TRACE_LEVEL' -value 'DEBUG', reloading the agent and check emagent_perl.trc did not show any useful insight. 

In my frustration, I suggested to edit cmanload.pl and add some debug code, and the reply was encouraging:

yes we should do that .do you think of anything ? 

Without discussing the whole cmanload.pl, I only show some relevant lines here and the debug line I've added: 


...
my $executable = $ENV{CMAN_ORACLE_HOME} . "/bin/cmctl";
...
  $command = "$executable show gateways -c $name";
...
  $result = executeCmd($command);
...
  my @info = split( /\n/, $result );
...
foreach $line (@info)
{
  EMAGENT_PERL_DEBUG("line: $line");
...
EMAGENT_PERL_DEBUG("em_result=$totalEstablished|$totalRefused|$totalActiveConnections");
...

With this extra line I get everything cmctl show gateways  provides in the tracefile:


cmanload.pl: 2023-03-14 14:23:48,865: DEBUG:  line:  CMCTL for Linux: Version 21.0.0.0.0 - Production on 14-MRZ-2023 14:23:48
cmanload.pl: 2023-03-14 14:23:48,866: DEBUG:  line:  
cmanload.pl: 2023-03-14 14:23:48,866: DEBUG:  line:  Copyright (c) 1996, 2021, Oracle.  All rights reserved.
cmanload.pl: 2023-03-14 14:23:48,866: DEBUG:  line:  
cmanload.pl: 2023-03-14 14:23:48,866: DEBUG:  line:  Aktuelle Instanz cman_xxx ist schon gestartet
cmanload.pl: 2023-03-14 14:23:48,866: DEBUG:  line:  Anmeldung bei (DESCRIPTION=(address=(protocol=tcp)(host=xxx)(port=1521)))
cmanload.pl: 2023-03-14 14:23:48,866: DEBUG:  line:  
cmanload.pl: 2023-03-14 14:23:48,866: DEBUG:  line:  Gateway-ID                     0
cmanload.pl: 2023-03-14 14:23:48,866: DEBUG:  line:  Gateway-Status                  READY
cmanload.pl: 2023-03-14 14:23:48,866: DEBUG:  line:  Anzahl von aktiven Verbindungen   0
cmanload.pl: 2023-03-14 14:23:48,866: DEBUG:  line:  Spitze von aktiven Verbindungen        5
cmanload.pl: 2023-03-14 14:23:48,866: DEBUG:  line:  Gesamte Verbindungen              17
cmanload.pl: 2023-03-14 14:23:48,866: DEBUG:  line:  Gesamte abgelehnte Verbindungen      0
cmanload.pl: 2023-03-14 14:23:48,866: DEBUG:  line:  Empfangene eingehende Byte              128214
cmanload.pl: 2023-03-14 14:23:48,866: DEBUG:  line:  Empfangene ausgehende Byte              337359
cmanload.pl: 2023-03-14 14:23:48,866: DEBUG:  line:  Gesendete eingehende Byte                  337359
cmanload.pl: 2023-03-14 14:23:48,866: DEBUG:  line:  Gesendete ausgehende Byte                 128214
cmanload.pl: 2023-03-14 14:23:48,867: DEBUG:  line:  
cmanload.pl: 2023-03-14 14:23:48,867: DEBUG:  line:  Gateway-ID                     1
cmanload.pl: 2023-03-14 14:23:48,867: DEBUG:  line:  Gateway-Status                  READY
cmanload.pl: 2023-03-14 14:23:48,867: DEBUG:  line:  Anzahl von aktiven Verbindungen   0
cmanload.pl: 2023-03-14 14:23:48,867: DEBUG:  line:  Spitze von aktiven Verbindungen        6
cmanload.pl: 2023-03-14 14:23:48,867: DEBUG:  line:  Gesamte Verbindungen              19
cmanload.pl: 2023-03-14 14:23:48,867: DEBUG:  line:  Gesamte abgelehnte Verbindungen      0
cmanload.pl: 2023-03-14 14:23:48,867: DEBUG:  line:  Empfangene eingehende Byte              260851
cmanload.pl: 2023-03-14 14:23:48,867: DEBUG:  line:  Empfangene ausgehende Byte              832901
cmanload.pl: 2023-03-14 14:23:48,867: DEBUG:  line:  Gesendete eingehende Byte                  832901
cmanload.pl: 2023-03-14 14:23:48,867: DEBUG:  line:  Gesendete ausgehende Byte                 260851
cmanload.pl: 2023-03-14 14:23:48,867: DEBUG:  line:  
cmanload.pl: 2023-03-14 14:23:48,867: DEBUG:  line:  
cmanload.pl: 2023-03-14 14:23:48,867: DEBUG:  line:  Übersicht:
cmanload.pl: 2023-03-14 14:23:48,867: DEBUG:  line:  
cmanload.pl: 2023-03-14 14:23:48,867: DEBUG:  line:  Aktive Verbindungen gesamt              0
cmanload.pl: 2023-03-14 14:23:48,867: DEBUG:  line:  Aktive Spitzenverbindungen gesamt          11
cmanload.pl: 2023-03-14 14:23:48,868: DEBUG:  line:  Verbindungen gesamt              36
cmanload.pl: 2023-03-14 14:23:48,868: DEBUG:  line:  Abgelehnte Verbindungen gesamt      0
cmanload.pl: 2023-03-14 14:23:48,868: DEBUG:  line:  Empfangene eingehende Byte gesamt        389065
cmanload.pl: 2023-03-14 14:23:48,868: DEBUG:  line:  Empfangene ausgehende Byte gesamt       1170260
cmanload.pl: 2023-03-14 14:23:48,868: DEBUG:  line:  Gesendete eingehende Byte gesamt            1170260
cmanload.pl: 2023-03-14 14:23:48,868: DEBUG:  line:  Gesendete ausgehende Byte gesamt           389065
cmanload.pl: 2023-03-14 14:23:48,868: DEBUG:  line:  Der Befehl wurde erfolgreich abgeschlossen.
cmanload.pl: 2023-03-14 14:23:48,868: DEBUG:  em_result=0|0|0

 Here the output of cmctl isn't as expected! 

Lines like Aktive Verbindungen gesamt, Verbindungen gesamt or Abgelehnte Verbindungen gesamt are probably not what cmanload.pl expects in it's pattern matching: 


...
  if($line =~ /^\s*Total Connections\s*[0-9]+/i )
...
  elsif($line =~ /^\s*Total Connections refused\s*[0-9]+/i )
...
  elsif($line =~ /^\s*Number of active connections\s*[0-9]+/i)
...


The explanation for these observations are quite simple: someone at this site decided to have all messages on servers in German and therefore NLS_LANG is set to GERMAN_GERMANY.WE8ISO8859P15. This is also true for the oem agent, and all the programs it calls - in this case cmanload.pl and cmctl

With this valuable insight, my workaround is simple. Replace the line 

	my @output = `$cmd 2>&1`;

by 

	my @output = `unset NLS_LANG; $cmd 2>&1`;

 or, as I'm lazy, do

sed -i 's/my @output = `$cmd 2>&1`;/my @output = `unset NLS_LANG; $cmd 2>\&1`;/' cmanload.pl

Now the tracefile show some understandable lines:


cmanload.pl: 2023-03-14 14:39:03,156: DEBUG:  line:  CMCTL for Linux: Version 21.0.0.0.0 - Production on 14-MAR-2023 14:39:03
cmanload.pl: 2023-03-14 14:39:03,156: DEBUG:  line:  
cmanload.pl: 2023-03-14 14:39:03,156: DEBUG:  line:  Copyright (c) 1996, 2021, Oracle.  All rights reserved.
cmanload.pl: 2023-03-14 14:39:03,156: DEBUG:  line:  
cmanload.pl: 2023-03-14 14:39:03,156: DEBUG:  line:  Current instance cman_xxx is already started
cmanload.pl: 2023-03-14 14:39:03,156: DEBUG:  line:  Connecting to (DESCRIPTION=(address=(protocol=tcp)(host=xxx)(port=1521)))
cmanload.pl: 2023-03-14 14:39:03,157: DEBUG:  line:  
cmanload.pl: 2023-03-14 14:39:03,157: DEBUG:  line:  Gateway ID                     0
cmanload.pl: 2023-03-14 14:39:03,157: DEBUG:  line:  Gateway state                  READY
cmanload.pl: 2023-03-14 14:39:03,157: DEBUG:  line:  Number of active connections   6
cmanload.pl: 2023-03-14 14:39:03,157: DEBUG:  line:  Peak active connections        6
cmanload.pl: 2023-03-14 14:39:03,157: DEBUG:  line:  Total connections              23
cmanload.pl: 2023-03-14 14:39:03,157: DEBUG:  line:  Total connections refused      0
cmanload.pl: 2023-03-14 14:39:03,157: DEBUG:  line:  Received IN bytes              148674
cmanload.pl: 2023-03-14 14:39:03,157: DEBUG:  line:  Received OUT bytes             372165
cmanload.pl: 2023-03-14 14:39:03,157: DEBUG:  line:  Sent IN bytes                  372165
cmanload.pl: 2023-03-14 14:39:03,157: DEBUG:  line:  Sent OUT bytes                 148674
cmanload.pl: 2023-03-14 14:39:03,157: DEBUG:  line:  
cmanload.pl: 2023-03-14 14:39:03,157: DEBUG:  line:  Gateway ID                     1
cmanload.pl: 2023-03-14 14:39:03,158: DEBUG:  line:  Gateway state                  READY
cmanload.pl: 2023-03-14 14:39:03,158: DEBUG:  line:  Number of active connections   6
cmanload.pl: 2023-03-14 14:39:03,158: DEBUG:  line:  Peak active connections        6
cmanload.pl: 2023-03-14 14:39:03,158: DEBUG:  line:  Total connections              25
cmanload.pl: 2023-03-14 14:39:03,158: DEBUG:  line:  Total connections refused      0
cmanload.pl: 2023-03-14 14:39:03,158: DEBUG:  line:  Received IN bytes              281311
cmanload.pl: 2023-03-14 14:39:03,158: DEBUG:  line:  Received OUT bytes             867707
cmanload.pl: 2023-03-14 14:39:03,158: DEBUG:  line:  Sent IN bytes                  867707
cmanload.pl: 2023-03-14 14:39:03,158: DEBUG:  line:  Sent OUT bytes                 281311
cmanload.pl: 2023-03-14 14:39:03,158: DEBUG:  line:  
cmanload.pl: 2023-03-14 14:39:03,158: DEBUG:  line:  
cmanload.pl: 2023-03-14 14:39:03,158: DEBUG:  line:  Summary:
cmanload.pl: 2023-03-14 14:39:03,158: DEBUG:  line:  
cmanload.pl: 2023-03-14 14:39:03,159: DEBUG:  line:  Total active cons              12
cmanload.pl: 2023-03-14 14:39:03,159: DEBUG:  line:  Total peak active con          12
cmanload.pl: 2023-03-14 14:39:03,159: DEBUG:  line:  Total Connections              48
cmanload.pl: 2023-03-14 14:39:03,159: DEBUG:  line:  Total connections Refused      0
cmanload.pl: 2023-03-14 14:39:03,159: DEBUG:  line:  Total Received IN bytes        429985
cmanload.pl: 2023-03-14 14:39:03,159: DEBUG:  line:  Total Received OUT bytes       1239872
cmanload.pl: 2023-03-14 14:39:03,159: DEBUG:  line:  Total Sent IN bytes            1239872
cmanload.pl: 2023-03-14 14:39:03,159: DEBUG:  line:  Total Sent OUT bytes           429985
cmanload.pl: 2023-03-14 14:39:03,159: DEBUG:  line:  The command completed successfully.
cmanload.pl: 2023-03-14 14:39:03,159: DEBUG:  em_result=96|0|12

Agent and OEM metric page are also happy now! 


Oracle accepted this unwanted behavior as Bug 35186390 : "Load" Metric it always show values Zero for CMAN target. Let's see how long it will take until an official Patch is available. Until then, at least I know how to help myself. 

2023-02-18

OEM 13.5 - change cman password

During the last week I learned how seldom Oracles Connection Manager (cman) is used. This also means it's integrated into the ecosystem with less motivation - at least in comparison to other products.  

One such a loose integration is the management of cman passwords in Oracle Enterprise Manager (OEM). 

In the web interface it's quite easy: The password is just a property like anything else. 

But it gets more complicated when trying to change the configuration with emcli. The "obvious" approach, use emcli set_credential fails: 


emcli set_credential \
-target_type="oracle_cman" \
-target_name="$cman" \
-credential_set="CmanCredsMonitoring" \
-monitoring

Internal Error: The EM CLI system has encountered an internal error. Details have been added to the OMS log files.

Well, there are other methods to modify properties - and it somehow seems OEM indicates cmans password more a property than a password. With this in mind, the password can be modified: 

emcli modify_target \
-type="oracle_cman" \
-name="$cman" \
-properties="Password:xxx"

Target "cman_········:oracle_cman" modified successfully

Unfortunately, this command can be used to set a password. but when trying to delete it (yes, this might happen sometimes) it fails:

emcli modify_target \
-type="oracle_cman" \
-name="$cman" \
-properties="Password:"

Syntax Error: Invalid value for parameter "properties": "Password:". Reason: "Password:" is not a name-value pair.


At the moment, I don't have a reasonable solution how to delete cmans password in OEM configuration. The only supported way is to do it in the web frontend - but this just doesn't scale and is not very cloud-ish (aka automated). 

I can do the totally unsupported method by updating the OEM repository table SYSMAN.EM_NC_CRED_COLUMNS  and set CREAD_ATTR_VALUE to NULL. My query to get it's content is 


select mt.target_name
     , mt.target_type
	 , mt.emd_url
	 , mt.target_guid
     , etc.set_name
     , encc.*
from sysman.MGMT$target mt
   , sysman.EM_TARGET_CREDS etc
   , sysman.em_nc_cred_columns encc
where 1=1
  and mt.target_guid = etc.target_guid
  and etc.cred_guid  = encc.cred_guid
  and mt.target_type = 'oracle_cman'
  and mt.target_name like '%&cman_name%'
order by 1 ;

you can guess the update statement.  OR, we hope Oracle will soon implement the Enhancement Request 35084478 : EMCLI delete password not working for cman target



2023-01-07

cman - the misleading error message

 In my last post I showed how a human error and a misleading log entry leaded to a waste of resources and time identifying (and fixing) the problem. 

But that's not all: Oracle connection manager (cman) is providing additional sources of joy and time wasting error analysis by leading in the wrong direction.

In this case, the cman (21.3) is working fine. 

The database instance has a REMOTE_LISTENER configuration pointing to the cman. 

The service is registered in the cman (show services) and there is a proper rule for the service in place (show rules):

Still at connecting with sqlplus, an unexpected error occurs: 


sqlplus -L x/x@<MY_ALIAS>

SQL*Plus: Release 19.0.0.0.0 - Production on Do Jan 6 21:40:04 2023
Version 19.14.0.0.0

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

ERROR:
TNS-12529: TNS:connect request rejected based on current filtering rules


But even after checking the services and rules again, there is no visible error. 
To cut things short (and avoid moaning about lost hours of investigation), the problem is located in some unexpected configuration: 

The database instance parameter LOCAL_LISTENER was set to 
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=REGISTER_listener)))

This made totally sense as long as no cman was in use: the instance registered to it's dedicated listener and then the services were available for clients to connect to. 

http://wikiality.wikia.com/wiki/File:Bridge-to-nowhere.jpg


But in my case, rdbms instance told the cman it can handle the service MY_SERVICE (which is used in MY_ALIAS) by redirecting all connections to ipc:REGISTER_listener. Of course this listener is not available at the cman host, and so the gateway process fails miserably. 

By understanding the problem, the solution is simple: changing the database instance parameter to 

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=REGISTER_listener))),instance_host:1521

did the trick - and sqlplus was happy (at least as happy as ORA-1017 can make)
Quite simple, but it didn't map the error message 
TNS-12529: TNS:connect request rejected based on current filtering rules
at least to my standards. 
I'd prefer a statement like 
gateway couldn't establish connection for service MY_SERVICE to (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=REGISTER_listener))) 
Maybe a future release can do that trick?

2023-01-06

cman - the misleading log entry

 In a recent Oracle connection Manager (cman) installation (Version 21.3) I tried to configure a minimal ruleset and test the cman instance, before I let database instances register their services. 

So a simple list of rules at that stage should allow administration of the cmon service (that's the cman itself) from the local machine, but reject all other attempts regardless their source or destination. 
Later on in between I plan to add all the dedicated services, their allowed source machines and so on. 


  (rule_list=
    (rule=(src=<CMAN_HOST>)(dst=127.0.0.1)(srv=cmon)(act=accept))
    (rule=(src=*)(dst=*)(srv=*)(act=deny))
  )  


But when I tried to startup the cman, It threw me a nasty TNS-04012: Unable to start Oracle Connection Manager instance:

CMCTL> administer cman
Current instance cman is not yet started
Connections refer to (DESCRIPTION=(address=(protocol=tcp)(host=<CMAN_HOST>)(port=1521))).
The command completed successfully.
CMCTL:cman> startup
TNS-04012: Unable to start Oracle Connection Manager instance.
CMCTL:cman>
A short glimpse into the logfile showed one matching line 

(LOG_RECORD=(TIMESTAMP=06-JAN-2023 18:40:01)(EVENT=CMAN.ORA contains no rule for local CMCTL connection)(Add (rule=(src=<CMAN_HOST>)(dst=127.0.0.1)(srv=cmon)(act=accept)) in rule_list)

 
This is suspicious, as exactly that line already exists in the rule list. Even when I add this rule several times, or with any other patterns like ::1 for localhost, it didn't help. 
Only changing the last line to act=accept let me start the cman - which is fine to make some progress, but not acceptable from a later required security perspective. 

I was so desperate fixing the rule for accepting the cmon service, I failed to solve this problem myself - so I asked for help: 

the solution is as simple as I was blind: deny is not a valid keyword for any rules action, the correct keywords are accept, reject or drop

So the simple solution is to change the configuration to

  (rule_list=
    (rule=(src=<CMAN_HOST>)(dst=127.0.0.1)(srv=cmon)(act=accept))
    (rule=(src=*)(dst=*)(srv=*)(act=drop))
  )  


And I could easily startup the listener.
The Problem obviously existed between my keyboard and chair. 
I just have wished the error-message would have been a little bit more helpful. 
Maybe in a future release the logfile could show 
rule (rule=(src=*)(dst=*)(srv=*)(act=deny)) can not be parsed at keyword  deny 
Let's hope for the best. 
 

2022-12-22

OEM 13.5 - add cman target with emcli

 Yesterday I had some fun adding a cman to Oracle Enterprise Manager. 

Using the web frontend is fine for a single target, but it doesn't scale well. But OEM also has a very fine command line utility: emcli

emcli has a high amount of parameters, in my case I'm interested in emcli add_target. This verb has basically 4 parameters: -name, -type -host and -propertiesname and host are kind of obvious, type I already identified as oracle_cman, but the properties are of some mystery. I did not find the list of properties somewhere in the documentation, and also google didn't help. 

So I mixed find and grep together in som random methods until I found the file 

 ./middleware/plugins/oracle.sysman.db.oms.plugin_13.5.1.0.0/metadata/targetType/oracle_cman.xml

Beside some other information (e.g. the name of the perlscripts which test the target) there is also a list of all properties:


  <InstanceProperties>
     <InstanceProperty NAME="Machine" CREDENTIAL="FALSE" OPTIONAL="FALSE">
       <Display>
         <Label NLSID="oracle_cman_Machine_iprop">Machine Name</Label>
       </Display>
     </InstanceProperty>
     <InstanceProperty NAME="Port" CREDENTIAL="FALSE" OPTIONAL="FALSE">
       <Display>
         <Label NLSID="oracle_cman_Port_iprop">Port Number</Label>
       </Display>
     </InstanceProperty>
     <InstanceProperty NAME="Protocol" CREDENTIAL="FALSE" OPTIONAL="FALSE">
       <Display>
         <Label NLSID="oracle_cman_Protocol_iprop">Connection Protocol</Label>
       </Display>
     </InstanceProperty>
     <InstanceProperty NAME="CManOraDir" CREDENTIAL="FALSE" OPTIONAL="FALSE">
       <Display>
         <Label NLSID="oracle_cman_CmanOraDir_iprop">cman.ora Directory</Label>
       </Display>
     </InstanceProperty>
     <InstanceProperty NAME="CManName" CREDENTIAL="FALSE" OPTIONAL="FALSE">
       <Display>
         <Label NLSID="oracle_cman_CmanName_iprop">Connection Manager Name</Label>
       </Display>
     </InstanceProperty>
     <InstanceProperty NAME="OracleHome" CREDENTIAL="FALSE" OPTIONAL="FALSE">
       <Display>
         <Label NLSID="oracle_cman_OracleHome_iprop">Oracle Home</Label>
       </Display>
     </InstanceProperty>
     <InstanceProperty NAME="Password" CREDENTIAL="TRUE" OPTIONAL="TRUE">
       <Display>
         <Label NLSID="oracle_cman_OracleHome_iprop">Connection Manager Password</Label>
       </Display>
     </InstanceProperty>


But all this effort would not have been necessary 😁  

At my tests, I hit some issues, and so identified

emcli add_target -name=cman_xxx -type=oracle_cman -host="xxx.yyy.zzz"

threw a proper error:

Error: Required properties are missing: Machine, Port, Protocol, CManOraDir, CManName, OracleHome


This syntax worked for me:


emcli add_target -name=cman_xxx -type=oracle_cman \
  -host="xxx.yyy.zzz" \
  -properties="Machine:xxx.yyy.zzz;Port:1521;Protocol:TCP;CManOraDir:<tns_admin>;CManName:cman_xxx;OracleHome:<oracle_home>;Password:🍻🍻🍻"

My next goal is to test if these values are stored correct.
For this I stumbled about the Note 

Emcli Command To Get Target Properties Information Like ORACLE HOME (Doc ID 2329892.1)


emcli list -resource="TargetProperties" -search="TARGET_NAME ='cman_xxx'" -column="PROPERTY_NAME,PROPERTY_VALUE" -script 


PROPERTY_NAME  PROPERTY_VALUE
CManNamecman_xxx
CManOraDir<TNS_ADMIN>
Machinexxx.yyy.zzz
OracleHome<ORACLE_HOME>
Port1521
ProtocolTCP
orcl_gtp_osLinux
orcl_gtp_platformx86_64

unfortunately, the Password isn't shown here and can not be checked by this method.

2022-12-21

OEM 13.5 - add cman target

Right now I have to add some connection manager instances to Oracle Enterprise manager (13.5.0.10). I did not find any automatic discovery, so I had to enter the required values manually. But not all of these values are quite clear (to me) and all the documentation I found was ... of little use. 

Luckily I stumbled across $ORACLE_HOME/plugins/oracle.sysman.db.agent.plugin_*/scripts/cmanresp.pl
This script seem to check if cman is available at all. To understand the meaning of these properties, the code is quite useful. Here some relevant parts: 

my $oracleHome = $ENV{CMAN_ORACLE_HOME};
my $executable = $ENV{CMAN_ORACLE_HOME} . "/bin/cmctl";
my $name = $ENV{CMAN_NAME};
my $cmanOraDir = $ENV{CMAN_ORA_DIR};
my $machine = $ENV{CMAN_MACHINE};
my $protocol = $ENV{CMAN_PROTOCOL};
my $port = $ENV{CMAN_PORT};
my $password = $ENV{CMAN_PASSWORD};

#set default protocol
if(!defined($protocol) or  $protocol eq "")
{
  $protocol = "TCP";
}

#Set environment variable ORACLE_HOME
$ENV{ORACLE_HOME} = $oracleHome;

my $address = "(ADDRESS=(PROTOCOL=$protocol)(HOST=$machine)(PORT=$port))";

my $responseTime;

#----------------------------------------------
#Execute tnsping and see if cman is reachable.
#-----------------------------------------------
#check tnsping availability
if ( !-x "$oracleHome/bin/tnsping" )
 {
  print "em_result=|0|tnsping not found in $oracleHome/bin\n";
  exit;
 }     
Connection Protocol, Machine Name and Port Number should match exactly the values as in cmans connection string - they are combined to (ADDRESS=(PROTOCOL=$protocol)(HOST=$machine)(PORT=$port)).
The default Protocol is TCP - as it's a required field, this should be entered there. 

Another question I raised (and could not answer before) was if or how the Password was used. In this script it's quite easy visible:

#Command to get the cman status
if($password eq "")
{
  $command = "$executable show status -c $name";
}
else
{
  $command = "$executable show status -c $name -p $password";
}

If no password is given, cmctl is used without the -p parameter. 

The properties Oracle Home (ORACLE_HOME) and cman.ora directory (TNS_ADMIN) are obvious, even for me. 


That's a short post, at least my documentation-to-self.

2019-01-03

connection manager - what happens at startup & shutdown

Oracle Connection Manager (cman) is a great tool to create a gateway between networks which can not be connected, or filter those who can access to a service.
Most of the time, it's a very robust implementation and doesn't need a lot of attention. But if it's required to dig into it, a basic knowledge about it's components can be useful.


In this post I'll show what happens in a simple startup (& shutdown) of a mostly "default" cman.
The test was done on Oracle Linux 7.5 with cman 12.2.0.1.181016.


tl:dr

In my setup, processes are called this order.
at startup, cmctl spawns several processes. these processes communicate via sockets and has some shared memory segments

The details: 

The results in this post show some lines of interest cenerated by
strace -f -o <some_dir&gt>/cman_$$.strace -y -s 128 cman
and the 3 commands
administer cman_berx
startup
shutdown

Before each quote of the tracefile I write the line number - this is not an exact offset for your tests, just shows the sequence and sometimes big gaps which I do not show. I will name PIDs and executes together for better readability.

1:
10115 execve("<ORACLE_HOME>/bin/cmctl", ["cmctl"], [/* 118 vars */]) = 0
process 10115 is now cmctl.

200:
10115 open("<ORACLE_HOME>/env.ora", O_RDONLY) = 3
... 
10115 read(3, "# Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved.\n# \n# NAME\n#   env.ora\n#\n# FUNCTION\n#   Oracle ENVironme"..., 4096) = 852
The undocumented file <ORACLE_HOME>/env.ora is checked for TNSNAMES_ORA parameter there - my file is default, so nothing is set there.

231:
10115 access("<TNS_ADMIN>/cman.ora", F_OK) = -1 ENOENT (No such file or directory)
10115 access("/etc/cman.ora", F_OK)     = -1 ENOENT (No such file or directory)
10115 access("<ORACLE_HOME>/network/admin/cman.ora", F_OK) = 0
10115 open("<ORACLE_HOME>/network/admin/cman.ora", O_RDONLY) = 3
... 
10115 read(3<<ORACLE_HOME>/network/admin/cman.ora>, "######################################################################\n#\n# Copyright (c) 2001,2002, Oracle Corporation. All righ"..., 4096) = 3996 
... 
10115 close(3<<ORACLE_HOME>/network/admin/cman.ora>) = 0 
10115 (cman) searches for cman.ora in <TNS_ADMIN> (nothing there) /etc (also nothing) and <ORACLE_HOME>/network/admin (there is an entry for cman_berx).

297:
10115 write(1</dev/pts/0>, "CMCTL> ", 7) = 7
10115 read(0</dev/pts/0>, "administer cman_berx\n", 1024) = 21
10115 (cmctl) writes a prompt (CMCTL>) and gets my command line command (administer cman_berx).

364:
10115 connect(4<socket:[1549779484]>, {sa_family=AF_INET, sin_port=htons(1522), sin_addr=inet_addr("x.y.z.78")}, 16) = -1 EINPROGRESS (Operation now in progress)
10115 (cmctl) tries to connect to the address:port provided in cman.ora - and can't connect. I assume that's the moment it realises it must start all related processes.


418:
10115 geteuid()                         = 5001
10115 clone(child_stack=0, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|SIGCHLD, child_tidptr=0x2b8713915150) = 10286
10115 rt_sigprocmask(SIG_BLOCK, [PIPE], NULL, 8) = 0
10286 set_robust_list(0x2b8713915160, 24 <unfinished ...>
10115 rt_sigaction(SIGPIPE, {0x2b8717669de0, ~[ILL TRAP ABRT BUS FPE SEGV USR2 TERM XCPU XFSZ SYS RTMIN RT_1], SA_RESTORER|SA_RESTART|SA_SIGINFO, 0x2b8718e9f680},  <unfinished ...>
10286 <... set_robust_list resumed> )   = 0
10115 <... rt_sigaction resumed> {SIG_DFL, [], 0}, 8) = 0
10115 rt_sigprocmask(SIG_UNBLOCK, [PIPE], NULL, 8) = 0
10286 clone( <unfinished ...>
10115 wait4(10286,  <unfinished ...>
10286 <... clone resumed> child_stack=0, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|SIGCHLD, child_tidptr=0x2b8713915150) = 10287
10287 set_robust_list(0x2b8713915160, 24) = 0
10287 close(6<pipe:[1549773687]>)       = 0
10287 close(7<pipe:[1549773688]> <unfinished ...>
10286 exit_group(0)                     = ?
10287 <... close resumed> )             = 0
10287 setsid( <unfinished ...>
10286 +++ exited with 0 +++
10115 (cmctl) creates (clone) a new process (10286) - this process immediately clones another one (10287) and then terminates itself (exit_group). By this there is no parent-child relationship between 10115 (cmctl) and 10287. Still they both use the same binary (cmctl) and filehandles etc. - so 10287 closes some filehandles it doesn't require.

449:
10287 execve("/appl/oracle/product/cman_12201/bin/cmadmin", ["/appl/oracle/product/cman_12201/bin/cmadmin", "cman_berx", "-inherit"], [/* 119 vars */]) = 0
process 10287 is now cmadmin.


2602:
10287 access("/var/tmp/.oracle", F_OK)  = 0
10287 chmod("/var/tmp/.oracle", 01777)  = -1 EPERM (Operation not permitted)
10287 socket(AF_LOCAL, SOCK_STREAM, 0)  = 6<socket:[1549770245]>
...
10287 access("/var/tmp/.oracle_500100", F_OK) = -1 ENOENT (No such file or directory)
10287 mkdir("/var/tmp/.oracle_500100", 0700) = 0
10287 chmod("/var/tmp/.oracle_500100", 0700) = 0
10287 access("/var/tmp/.oracle_500100/s#10287.1", F_OK) = -1 ENOENT (No such file or directory)
10287 bind(6<socket:[1549770245]>, {sa_family=AF_LOCAL, sun_path="/var/tmp/.oracle_500100/s#10287.1"}, 110) = 0
10287 chmod("/var/tmp/.oracle_500100/s#10287.1", 0777) = 0
10287 listen(6<socket:[1549770245]>, 100) = 0
10287 getsockname(6<socket:[1549770245]>, {sa_family=AF_LOCAL, sun_path="/var/tmp/.oracle_500100/s#10287.1"}, [36]) = 0
10287 chmod("/var/tmp/.oracle_500100/s#10287.1", 0777) = 0 
10287 (cmadmin) checks if /var/tmp/.oracle exists and if it can change it's permission. In my environment the directory exists (as Grid Infrastructure is installed) but it's permissions can't be changed. It belongs to root.  Then /var/tmp/.oracle_500100 is created and a socket is created there. For easier readability, the PID is part of the socket name.

2631:
10287 clone(child_stack=0x2b6fd20de570, flags=CLONE_VM|CLONE_FS|CLONE_FILES|CLONE_SIGHAND|CLONE_THREAD|CLONE_SYSVSEM|CLONE_SETTLS|CLONE_PARENT_SETTID|CLONE_CHILD_CLEARTID, parent_tidptr=0x2b6fd20e99d0, tls=0x2b6fd20e9700, child_tidptr=0x2b6fd20e99d0) = 10289
10287 (cmadmin) creates another process (10289).

2701:
10287 <... clone resumed> child_stack=0, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|SIGCHLD, child_tidptr=0x2b6fd1f80150) = 10290
10287 (cmadmin) creates another process (10290).

2719:
10290 <... clone resumed> child_stack=0, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|SIGCHLD, child_tidptr=0x2b6fd1f80150) = 10291
...
10290 exit_group(0)                     = ?
...
10290 +++ exited with 0 +++
10290 creates 10291 and terminates itself.

2764:
10291 execve("/appl/oracle/product/cman_12201/bin/tnslsnr", ["/appl/oracle/product/cman_12201/bin/tnslsnr", "ifile=/appl/oracle/product/cman_12201/network/admin/cman.ora", "cman_berx", "-inherit", "-mode", "proxy"], [/* 119 vars */]) = 0
10291 becomes tnslsnr


3613:
10291 bind(11<socket:[1549780776]>, {sa_family=AF_INET, sin_port=htons(1522), sin_addr=inet_addr("0.0.0.0")}, 16) = 0
10291 listen(11<socket:[1549780776]>, 128) = 0
10291 getsockname(11<socket:[1549780776]>, {sa_family=AF_INET, sin_port=htons(1522), sin_addr=inet_addr("0.0.0.0")}, [16]) = 0
10291 (tnslsnr) listens on 0.0.0.0:1522 - But in cman.ora a specific IP address is given.

3669:
10291 write(12<<DIAG_DEST>/netcman/<hostname>/cman_berx/trace/cman_berx.log>, "Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<server.domain>)(PORT=1522)))\n", 91) = 91
10291 (tnslsnr) writes something different to the logfile (?)

3674:
10291 access("/var/tmp/.oracle", F_OK)  = 0
10291 chmod("/var/tmp/.oracle", 01777)  = -1 EPERM (Operation not permitted)
10291 socket(AF_LOCAL, SOCK_STREAM, 0)  = 14<socket:[1549790968]>
10291 access("/var/tmp/.oracle/s#10291.1", F_OK) = -1 ENOENT (No such file or directory)
10291 bind(14<socket:[1549790968]>, {sa_family=AF_LOCAL, sun_path="/var/tmp/.oracle/s#10291.1"}, 110) = 0
10291 chmod("/var/tmp/.oracle/s#10291.1", 0777) = 0
10291 listen(14<socket:[1549790968]>, 1) = 0
10291 getsockname(14<socket:[1549790968]>, {sa_family=AF_LOCAL, sun_path="/var/tmp/.oracle/s#10291.1"}, [29]) = 0
10291 chmod("/var/tmp/.oracle/s#10291.1", 0777) = 0
10291 (tnslsnr) creates its own socket file for communication purpose.

4088:
10291 connect(16<socket:[1549778909]>, {sa_family=AF_LOCAL, sun_path="/var/tmp/.oracle/sprocr_local_conn_0_PROL"}, 110) = 0

4149:
10291 connect(15<socket:[1549778910]>, {sa_family=AF_LOCAL, sun_path="/var/tmp/.oracle/sCRSD_UI_SOCKET"}, 110) = 0
10291 (tnslsnr) checks some sockets which belong to Grid Infrastructure.

4616:
10291 clone(child_stack=0x2ac6b580b570, flags=CLONE_VM|CLONE_FS|CLONE_FILES|CLONE_SIGHAND|CLONE_THREAD|CLONE_SYSVSEM|CLONE_SETTLS|CLONE_PARENT_SETTID|CLONE_CHILD_CLEARTID, parent_tidptr=0x2ac6b58169d0, tls=0x2ac6b5816700, child_tidptr=0x2ac6b58169d0) = 10292
10291 (tnslsnr) creates PID 10292

4754:
10292 clone( <unfinished ...>
10291 <... close resumed> )             = 0
10292 <... clone resumed> child_stack=0x2ac6b584c570, flags=CLONE_VM|CLONE_FS|CLONE_FILES|CLONE_SIGHAND|CLONE_THREAD|CLONE_SYSVSEM|CLONE_SETTLS|CLONE_PARENT_SETTID|CLONE_CHILD_CLEARTID, parent_tidptr=0x2ac6b58579d0, tls=0x2ac6b5857700, child_tidptr=0x2ac6b58579d0) = 10293
10293 set_robust_list(0x2ac6b58579e0, 24 <unfinished ...>
10292 creates PID 10293

4790:
10293 exit(0)                           = ?
10292 <... poll resumed> )              = 1 ([{fd=15, revents=POLLIN}])
10293 +++ exited with 0 +++
10293 exits - it did not "clone" or "execve" anything

4841:
10287 shmget(0x59248000, 2462280, IPC_CREAT|IPC_EXCL|0600 <unfinished ...>
10287 (cmadmin) allocates a shared memory segment with key 0x59248000.

4937:
10287 <... clone resumed> child_stack=0, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|SIGCHLD, child_tidptr=0x2b6fd1f80150) = 10294
...
10294 <... clone resumed> child_stack=0, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|SIGCHLD, child_tidptr=0x2b6fd1f80150) = 10295
...
10294 exit_group(0)                     = ?
...
10294 +++ exited with 0 +++
10287 (cmadmin) creates PID 10294, this creates PID 10295 and terminates itself.


4967:
10295 execve("/appl/oracle/product/cman_12201/bin/cmgw", ["/appl/oracle/product/cman_12201/bin/cmgw", "cmgw0", "0", "16", "cman_berx", "SNLSM:59248000"], [/* 119 vars */]) = 0
10295 becomes cmgw0 - you can see the parameter SNLSM - it gives the shared memory key from 10287 (cmadmin).

5158:
10295 shmget(0x59248000, 1, 0)          = 2099576860
10295 (cmgw0) attaches to shared memory 10287 (cmadmin).


5566:
10287 <... clone resumed> child_stack=0, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|SIGCHLD, child_tidptr=0x2b6fd1f80150) = 10296
...
10296 <... clone resumed> child_stack=0, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|SIGCHLD, child_tidptr=0x2b6fd1f80150) = 10297
...
10296 exit_group(0)                     = ?
10287 (cmadmin) creates PID 10296 which creates 10297 and exits. - same as above

5625:
10297 execve("/appl/oracle/product/cman_12201/bin/cmgw", ["/appl/oracle/product/cman_12201/bin/cmgw", "cmgw1", "1", "16", "cman_berx", "SNLSM:59248000"], [/* 119 vars */] <unfinished ...>
10297 becomes cmgw1.

some (later) important sockets:
5680:
10295 connect(5<socket:[1549804554]>, {sa_family=AF_LOCAL, sun_path="/var/tmp/.oracle_500100/s#10287.1"}, 110 <unfinished ...>

6447:
10297 connect(5<socket:[1549798977]>, {sa_family=AF_LOCAL, sun_path="/var/tmp/.oracle_500100/s#10287.1"}, 110 <unfinished ...>

7448:
10295 bind(7<socket:[1549804555]>, {sa_family=AF_LOCAL, sun_path="/var/tmp/.oracle_500100/s#10295.1"}, 110 <unfinished ...>

7458:
10297 bind(7<socket:[1549798978]>, {sa_family=AF_LOCAL, sun_path="/var/tmp/.oracle_500100/s#10297.1"}, 110 <unfinished ...>

7896:
10115 connect(5<socket:[1549779550]>, {sa_family=AF_LOCAL, sun_path="/var/tmp/.oracle_500100/s#10287.1"}, 110 <unfinished ...> 

At this stage, cman is started, all processes are in memory and happy to server any requests.



The shutdown- command will be much faster:

8553:
10115 <... read resumed> "shutdown\n", 1024) = 9
10115 (cmctl) recieves "shutdown" from command line

8554
10115 write(5, "\0\0\0S\6$\0\0\0\0E\0\0\200\2\0\0\0\0\4\0\0\220_8\2\0\0\0\0\20\0#\0\0\000901A4C4F9FD8-3939-E034-0800208AB384\1\0v\0\0\0\3\0\0\0\0\0", 83) = 83
101115 (cmctl) informs 10287 (cmadmin)

8568:
10287 write(5&lt;socket:[1549778933]&gt;, "\0\0\0S\6 \0\0\0\0E\0\0\200\2\0\0\0\0\4\0\0p\344t\2\0\0\0\0\20\0#\0\0\00091986B2B10AC-3001-E034-0800208AB384\1\0l\0\0\0\1\0\0\0\0\0", 83) = 83
10287 times( &lt;unfinished ...&gt;
10295 &lt;... epoll_wait resumed&gt; [{EPOLLIN, {u32=13989488, u64=13989488}}], 1024, -1) = 1
10287 &lt;... times resumed&gt; NULL)         = 1269637186
10287 epoll_wait(7&lt;anon_inode:[eventpoll]&gt;,  &lt;unfinished ...&gt;
10295 read(5&lt;socket:[1549804554]&gt;, "\0\0\0S\6 \0\0\0\0E\0\0\200\2\0\0\0\0\4\0\0p\344t\2\0\0\0\0\20\0#\0\0\00091986B2B10AC-3001-E034-0800208AB384\1\0l\0\0\0\1\0\0\0\0\0", 8208) = 83
1
10287 (cmadmin) informs 10295 (cmgw0) (and later 10297 (cmgw1) by the same method) to shutdown

some cleanup takes place:

8585:
10295 unlink("/var/tmp/.oracle_500100/s#10295.1" <unfinished ...>

8590:
10295 rmdir("/var/tmp/.oracle_500100" <unfinished ...>

8621:
10297 unlink("/var/tmp/.oracle_500100/s#10297.1" <unfinished ...>




and all the processes terminate:

8869:
10289 exit(0)                           = ?
...
10289 +++ exited with 0 +++

8927:
10295 exit_group(0)                     = ?

8967:
10297 exit_group(0)                     = ?

9096:
10291 unlink("/var/tmp/.oracle/s#10291.1" <unfinished ...>


9293:
10291 exit_group(0)                     = ?

9305:
10287 exit_group(0)                     = ?

9321:
10115 exit_group(0)                     = ?

That's a simple startup and shutdown of connection manager - nothing fancy here, just a lot of processes and sockets which are created - and then deleted.

I hope you never need to read this article to it's end. But if you need to, it should provide some value for you!

2012-05-04

how to secure CMAN against CVE-2012-1675 - or an easier method than ASO

In the Oracle DBA World at the moment CVE-2012-1675 is a great issue. Oracle announced some methods how to secure existing systems. But these are sometimes not that easy, and there is no backport for older systems.
As I investigated the problem how to secure a connection manager I was hinted at Note:1455068.1.
The solution is somewhat easy: Only allow incoming connections to your systems. e.g.
    (rule=(src=*)(dst=10.220.8.114)(srv=*)(act=accept))

In a well designed environment where you can separate your DB Servers from others at low network layers, a set of CMAN might be enough to secure your DBs against CVE-2012-1675.
At least it might be a simple and fast solution to secure your systens from untrusted areas, until you know how to secure the DB servers themselves. Especially for legacy systems it might be the only solution to secure it.

2012-01-31

Setting Up Oracle Connection Manager (without SOURCE_ROUTE)


This post must be seen as a direct follow up to Arup Nandas Setting Up Oracle Connection Manager.
As there are many references to this post, please read it first. Problem and Solution are quite similar, only the architecture is a little bit different:

The Architecture

 The network diagram of the three machines is slightly different:


There is a new needed connection: from the instance on dbhost1 to the connection manager on cmhost1.

After changing the setup, you will need to rewrite the TNSNAMES.ORA in the following way:

TNS_CM = 
  (DESCRIPTION = 
    (ADDRESS = 
      (PROTOCOL = TCP)(HOST = cmhost1)(PORT = 1950)
    )
    (CONNECT_DATA = 
      (SERVICE_NAME=srv1)
    )
  )

You see, the (SOURCE_ROUTE = YES) disappeared as well as the ADDRESS of the listener on dbhost1.

How it Works


Note, all the special parameters and settings on the clients TNSNAMES.ORA disappeared. But the cman must know about the SERVICE_NAME it has to serve. As the cman can be seen as a special kind of listener, there is a common way a listener gets informed about a SERVICE_NAME: the Instance has to register the services to the listener. In general this is done by pmon at registering to logal_listener and remote_listener. In this case, remote_listener is the magic parameter.

Setting Up


You can follow step (1) to (9) as in Arups blog.
But before (10) an additional step is required:

(x) on the instance add the cman to remote_listener:

Alter System Set remote_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=cmhost1)(PORT=1950))))' scope=both;

If there is already an entry in remote_listener, e.g. in a RAC, you can separate the different connection strings by comma. An example can be

Alter System Set remote_listener='SCAN-IP:1521,(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=cmhost1)(PORT=1950))))' scope=both;

(For more details about SCAN I'd recommend this PDF)

CMCTL Primer

As we have now the services registered also on cman, we can see it there. The  SHOW command has a 2nd parameter services. Here an example

Services Summary...
Proxy service "cmgw" has 1 instance(s).
  Instance "cman", status READY, has 2 handler(s) for this service...
    Handler(s):
      "cmgw001" established:1 refused:0 current:0 max:256 state:ready
         <machine: 127.0.0.1, pid: 16786 >
         (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=44391))
      "cmgw000" established:1 refused:0 current:0 max:256 state:ready
         <machine: 127.0.0.1,pid: 16784>
         (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=44390))
Service "INSTANCE1" has 1 instance(s).
  Instance "INSTANCE1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=dbhost1)(PORT=1521))
Service "cmon" has 1 instance(s).
  Instance "cman", status READY, has 1 handler(s) for this service...
    Handler(s):
      "cmon" established:3 refused:0 current:1 max:4 state:ready
         <machine: 127.0.0.1, pid: 16759>
         (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=44374))
The command completed successfully.

Fine Tuning

I try to create a dedicated service for all (or a well known set of) connections via the connection manager. By doing so it's sometimes easier to separate or identify different kinds of sessions.

2011-09-07

when does PMON register to remote listeners

I had a complex problem today: I tried to setup a connection manager, but unlike Arup, I did not like to to use SOURCE_ROUTE. So I had to make the pmon register itself to the cman. As we have already an entry in spfile for remote_listener=REMOTE, I just enhanced this alias in tnsnames.ora by the additional line for the cmans HOST and PORT.
Unfortunately the services did not show up in the cmans show services. Not even an alter system register; did any good, still no service.
After checking with tcpdump (there where really no communication to the cman) and oradebug event 10246 I had still no clue how to find out why my pmon does not like to contact the cman. At a short ask for help on twitter, Martin Nash pointed me to the Note How to Trace Dynamic Registration from PMON ? [ID 787055.1]. There I found the event
alter system set events='immediate trace name listener_registration level 3';
With this, (beside a lot of other useful information) I found the pmon just not knowing about the new entries.
As a solution I had to tell it about the new entries in tnsnames.ora by
alter system set remote_listener=REMOTE;
This made pmon to re-read the tnsnames.ora and accept the new values. All my services shows up in cman now.
Yong Huang has some more Informations about the different trace levels here:
Actually, trc_level 4 (user) is enough to show info about load stats. Other levels are:
0,1: off
2,3: err
4,5: user
6-14:admin
15: dev
16-: support