Odd DBA request to suppress connects after successful package start before their clearing

[Ralph Grothe]

Phew, I hope the subject line didn't get too long.

I'm currently rewriting package control scripts for a DB and application server packages to meet the admittetly very bizzare (and probably not SG-conforming) demands of the DBAs.
One of their constraints was that whenever a package got started (be it manual start or switchover or cmcld initiated failover) they first wish to look at their apps. (probably if anything is running as they'd expect), and that they want to reject client connects until the stuff got their official clearance.
I can see you grin, as this seems not what HA was meant to be (ok, as long as they don't mind being alerted in the middle of the night for their clearance ;-)

When I look at the template that the "cmmakepkg -s" command created I can see a func add_ip_addresses() that invokes in a loop over all defined IPs "cmmodnet -a -i".

I now wonder if I could plug in an "ifconfig lanN:N down" in the customer_defined_run_commands(), chern out an SMS to the DBAs to chase them out of their beds for their clearance, and create maybe a lockfile that needs to be deleted by them to let the script finally issue an "ifconfig lanN:N up".

Unfortunately I do lack a test cluster for playing, and only have productive systems at my disposal.
I created a fiddle package and added it to a running one, but since this package's IP is also bound like the other packages' IP to the same NIC, I really do hesitate to test the ifconfig down.
I fear this could affect the other real packages.

Anyone, a better idea?

[Eric Antunes]

Hi Ralph,

I don't understand anything about service guard (What is Service Guard anyway?) but there is an instruction that DBA's can execute to disable new connects do Oracle (apps and db):

ALTER SYSTEM RESTRICTED SESSION;

[Ralph Grothe]

Eric,

since I don't understand much about Oracle this is really some good hint from you.
It would be much cleaner from my point of view if I submitted such an Oracle SQL statement to the central DB instance from my package control script.
But I still suspect that I must treat the other packages (i.e. the application servers), those who do the forwarding of clients' requests to the DB, separately.
I know that this work really should be done by the application servers' developers.
Anyway, I will ask the DBAs what they think about your suggestion.

[Marcel Boogert_1]

Hi Ralph,

You can also block the Oracle port number after a switch on MCSG cluster. Default oracle port number is 1521. You can do this in combination with your lock file. In this way you block all clients on your server, before really connecting to the oracle database.

Regards, MB.

[RAC_1]

I am not sure if this what you are looking for. You dont want connections to the database untill applicationn is up. From your posting I understood that there are two packages - applications and database.

How about putting customer definded commands in database package, so that the database does not start untill application package is up and running smooth. Like as follows.

check "application package is up or not"
if not
sleep for a while
wake up
check again
if yes
start database package.

You can also control how long this should wait before giving up.

Anil

[Ralph Grothe]

Marcel,

how would I block the listener ports?
Does it mean I would have to install any packet filtering software (e.g. like Linux's netfilter)?
If so I guess this would require building and booting a new kernel, which I wouldn't want to do.
Apart, we do have dedicated firewalls, and thus our servers aren't expected to run their own (which would even multiply the usual mess with the firewall admins ;-).
Since the application servers (as well as the Oracle listeners) are standalone servers I guess it wouldn't help much to fumble with /var/adm/inetd.sec either.
But maybe I could customize some other tcp wrapper?

RAC,

actually there are four packages.
1 is the DB server, the remaining 3 are application servers, whose functionality I don't know and can only treat as black boxes.
(I'm not very inclinded to do packet sniffing or stack trace analysis just to obtain the missing documentation ;-)
Yes, I already supplied my own function that keeps the app. server packages idling until the DB package is up (see my attachment).
Pretty early in customer_defined_run_commands() I source my shell function lib, where this wait_for_pkg() is taken from.

[Eric Antunes]

Ralph,

With this command, nobody connects to the database excepts users with RESTRICTED_SESSION system privilege wich is normally just for DBA's. The exact command is:

ALTER SYSTEM ENABLE RESTRICTED SESSION;

After doing what you needed:

ALTER SYSTEM DISABLE RESTRICTED SESSION;

Regards,

Eric

[Marcel Boogert_1]

Ralph,

Check this link, it is a DLKM.

http://www.software.hp.com/portal/swdepot/displayProductInfo.do?productNumber=B9901AA

Regards, MB.

[Sridhar Bhaskarla]

Ralph,

Since you are going to introduce some manual procedure here, I would suggest that you don't bring up the listener through your serviceguard. Let it be part of the manual process your DBAs would like to follow. Or you can put a step in the serviceguard script to create a lock file before it brings up the listener. Make listener one of the processes monitored by Serviceguard and it should not start the listener as long as the lock file is there. Once the DBAs verify everything, then they can remove the lock file. Serviceguard should bring up the listener through the monitor process as it doesn't see the lock file anymore.

However, if your application is comprised of database and application, then I would put the check in application itself. Application should be able to determine if database is alright otherwise, it itself should go down.

-Sri

[Fred Ruffet]

Bringing down the listener or stopping its port may not be sufficent in case local programs access the DB (IPC conncections).

Setting the DB in restricted session mode may not be sufficent if some users have been granted the DBA role.

Solution may be for DBAs to put a trigger on connection that verifies a value in a table they set up. This could offer them to disconnect people when they don"t want them to connect. The value should be set to a blocking value at DB statup and modified only when DBA wants...

Regards,

Fred

[Ralph Grothe]

Eric,

thanks for the corrected SQL statement.

Marcel,

thanks for the doc link for IP filter.
Albeit, for my tiny port blocking functionality installing and configuring the IP filter DLKM seems to me like "shooting with canons at sparrows" (as a German proverb goes, I can't recall the English counterpart, feel free to tell me...)

Shridhar,

I will be deliberating over introducing an SG service for the listener.
The DBAs will provide me with a SQL statement to query for sure that the DB has reached a state of full servicability.

Fred,

thanks for pointing me at setting a DB trigger.
As I know too little about Oracle triggers I will convey your proposal to the DBAs.

All,

I now tripped over another issue.
The constraints demand that any application server must not be running on the same node as the DB server (for performance reasons).
I can easily script a little check to care for this situation.
However, while the package manager is starting an application server will and en route the scripting yields that this node is already occupied by the DB, can I than issue a swichover in the same script (i.e. cmhaltpkg, cmrunpkg)?

[Sridhar Bhaskarla]

Ralph,

Yes you can do it. You can run cmhaltpkg and cmrunpkg commands as long as you are operating them on packages *other than* the current package. My cluster has exactly the same configuration. The packages will run on two nodes seperately unless if one node is physically down.

-Sri

[John Palmer]

Hi Ralph,

English equivalent to 'shooting sparrows with cannons' would be 'using a sledgehammer to crack a nut'.

I think that your latest problem could be solved by effectively 'failing' the package start script if you've determined that it shouldn't run because the database is already there.

This complicates matters though... if your database fails over, should you be kicking off any application server that's already there or indeed restarting all application servers if the database has been restarted?

Also, it's normally acceptable to run apps and databases together in the situation when a node has failed unless you've got spare servers that is in which case you can designate specific servers for specific purposes in the package configuration files.

Regards,
John

[Ralph Grothe]

John,

thanks for the English sledghammer allusion.
I'm alway keen on learning more of these things.
I also like this picture, especially when the nut is a peanut.
Apropos, peanuts in German nowadays is a "quantitee negligeable", something very inferior as far as money matters.
Some years ago the former president of Deutsche Bank (the biggest, and wealthiest private bank in Germany) enriched our language with the coining "that's peanuts" while he was referring to a several million Mark loan that his house lost because they were foobar-ed by the notorious swindler Dr. Schneider who knew how to behave pretentious enough in those circles. You can imagine the furor his remarks generated in the public.

Yes, I will have to script so that all 3 appl. servers are downed when the DB fails.

I wrote the test cludge I attached to hopefully down the package if it attempts to start on the wrong node.

[Sridhar Bhaskarla]

Hi again Ralph,

The way I take care of dependencies is to make one package as intelligent and the others dummy. For ex., if the application cannot run without the database, then the database package should take care of starting/stopping the application package. This approach has been working for me fine. I will also have more control over the package starts by using lock files that are helpful during any maintenance I do.

-Sri