Improve standby detection

[bartowl]

Hello, I looked for a way to better deal with oracle startup/shutdown and SystemD. I am heavily using ansible-oracle and the systemd scripts there are a catastrophe. Having multiple databases, just some of them being in dbvisit/dataguard gets a quite challenge. Not to mention, that shutting down one database might pull the rest down if systemd decides the main pid stopped working and calls the kill command for the entire cgroup.

From first look I see, that this project is a great way to deal with this kind of issues, but I have one problem with it. You relay on the oratab for detection whether the database is a PRIMARY or PHYSICAL STANDBY. At the same time you write, that any changes in oratab will not be reflected. How do you deal then with a switchover? I mean - would it not be better, to get rid of the S flag (in ansible-oracle they use M for that, to start in MOUNT, which is in my opinion also not the good idea) and move the role detection to the startup procedure itself? I mean, instead of calling "STARTUP" or "STARTUP MOUNT" based on the oratab, one could do: STARTUP MOUNT; select database_role from v$database; if the database_role is PRIMARY, then and only then execute ALTER DATABASE OPEN READ WRITE

What do you think, would this be better? It can be even done with a simple PLSQL code. I can try to provide some. Then, you would not need the S flag anymore, just Y or N. One might consider eventually an extra flag when Active DataGuard is used (with database in OPEN READ ONLY + Apply) which requires additional license but this is not the main issue here.

Best Regards, BartOwl

[rippiedoos]

This is exactly why I posted this script on github, thank you!

We deal with switchovers by running ADG or not doing a lot of them. If you kill the main python-processes, you only kill the python-proces and not the database. The Oracle-databases aren't stopped or killed by systemd if the python-proces is killed. I then start the service again to reflect the changes in oratab.

I wanted to implement a reload-command to reflect changes in oratab, but I haven't gotten around to figure out how to restart the sub-proces in my main loop with the new oratab-entries or signal the sub-proces to parse the oratab again. That is the main route I wanted to take with this program.

Better detection of primary/physical standby code is always a good thing. Please supply the SQL-commands to do this and I will try to implement them. The suggestion is good, by doing the OPEN-mode based on roles and by doing and extra flag, like O or S extra, on can open the database accordingly. Can this be done with arguments in the PL/SQL-code, by supplying the extra flag to your PL/SQL-code?

I must say I'm not a fan of the explicit statement ALTER DATABASE OPEN READ WRITE. If you are running dataguard, and starting the database in mount-mode, the DG-broker will do the rest. If the intended state was open read only, DG will open the database in read-only-mode. And opening a physical standby database by doing a ALTER DATABASE OPEN will open it read-only, because of the physical standby role, at least in 12.2 IIRC. So running a ADG-database with a physical standby database that's open, will only open it READ-ONLY if started by ALTER DATABASE OPEN.

On the other hand, can't do dbvisit the starting/opening the databases in the correct mode? If yes, you could just use this to start the databases in mount-status and let DBVisit do the rest. I haven't used dbvisit before so I don't know how that works.

[rippiedoos]

I started working on reloading the oratab in a branch but it's a WIP: #2 .

[bartowl]

Hi, After you wrote it I reconsidered my statement. First I thought, that you have to specify :Y on thr primary node and :S on the standby which would be aproblem without oratab reloading and after a switchover. But you're right, in DataGuard (regardless if this is regular one or active) it is fully sufficient to start oracle up to MOUNT more, and then the broker will take over and open the right one instance. After a bit of thinking I must admit, that my initial idea with opening READ WRITE after checking the database_role from controlfile may be even dangerous and should not be used. Let's consider a scenario, that due to power outage primary side got down, and a failover has been made. Then, after power returns the old primary side would still have a primary controlfile and my approach would open it read write, which would end up in split-brain having both instances open. It should be avoided at any cost. There is indeed a problem with dbvisit managed databases, as dbvisit does not have broker like feature and it will not be aufficient to start the database to mount mode. But on the other hand, dbvisit has its own command to start the database using dbvisit binary, and it knows the database role and chooses itself to start the database in mount or open. At the moment I would consider dbvisit being out of scope here, as to integrate its support might be hard if not at lease challenging. Considering that this daemon would relocate all running oracle processes to its own CGroup would however mean that also dbvisit instances after start could potentially be relocated to it. I think that at the moment a feasible solution would be - keep dbvisit managed instances with :N in oratab and let other scripts handle them. Having :N in oratab would make this scripts to ighore them for both startup and schutdown. I initiated a small workgroup in my company to discuss the best solution for starting all kind of non-grid oracle databases over SystemD and I must say that at the moment your scripts are on very top of the ideas as they cover not only single databases but also data guard. It ight be very cool to add als dbvisit support and have all oracle databases re-assigning to same cgroup, but if we will decide this way to be the best we will extend your scripts and publish a PR here. Finally we would like ot integrate the best solution into oravirt/ansible-oracle. Currently it does not support installing a dbvisit cluster, dataguard is at the very draft wairing for final version and a PR into main stream. This way it would be a complete solution.

So regarding this PR I guess it can be closed, as single-db and data guard are apparently fully supported. Should I have a solution for dbvisit which also integrates here, I will send it via a PR. If we decide that due to differences it will be better to handle dbvisit databases completely differently, then I will write you as well, but without a PR.

Best Regards

[andreasdijkman]

I'll close this issue then, as you suggested. I've implemented the reloading of the service to reflect changes of the oratab but in your case, that may not be necessary.