0

One can easily check via SELECT database_role FROM v$database; whether your Oracle database is a primary or standby database. Is it possible to make the same determination without database access, but only having (Unix) host access?

Oracle DBAs are very used to doing things like ps -ef | grep pmon or ps -ef | grep tnslsnr to quickly check if things are running, and these commands require no DB access. They can even be run from an unprivileged (non-root) Unix user who doesn't own Oracle. So thinking along those lines, I have 90% of it solved with things like ps -eo args | grep mrp0, but that falls short. It only works if the standby is mounted and doing managed recovery. It doesn't work if (i) the standby is simply mounted without starting MRP, or (ii) if the standby has been opened read-only.

So to recap, here are the parameters:

  1. Goal is to determine whether a running DB instance is a Primary or Standby.
  2. No access to the DB (e.g.: via SQL*Plus, JDBC, etc), but you have server access.
  3. Therefore can only use Unix tools (ps, lsof, /proc, awk, ...)
  4. Nice if can be done from unprivileged (non-root) Unix user.
  5. Triple nice if can be done from any unprivileged Unix user (i.e. not the Oracle owner).

Note, I'm running Oracle 11gR2 & 12c on AIX, but I'll gladly take any *nix solution.

Note2, I don't care about the quasi-solution where as root you simply su to the Oracle owner and sqlplus / as sysdba. I want to do this without a database connection. No V$ views allowed, no X$ tables allowed.

2

You can try using:

ps -ef | grep -v grep | grep ora_pr

Or if data guard is in use:

ps -ef | grep -v grep | grep ora_rsm

  • 1
    First, thanks for the suggestions. ora_pr didn't seem to help me. Same issue as ora_mrp0: if your standby is open read-only, then managed recovery isn't running, so no ora_mrp and no ora_pr. I still want to know that the DB is a Standby even if it's open read-only. Your 2nd suggestion ora_rsm is more promising, however depends on DG Broker, which I'm not yet using. Question for you then is: does ora_rsm run on both Primary and Standby? If so, it indeed indicates that DG is configured, but it still wouldn't identify which one is the Primary or Standby. Is that correct? – Joshua Huber Dec 4 '14 at 20:00
  • You can also goto $ORACLE_BASE/diag/rdbms/<dbsid>/<dbsid>/trace and grep the alert to see if it is in recovery. You just need to have read access to the file. "egrep -B1 "Media Recovery Waiting" alert_<dbsid>.log | tail -2" – Gandolf989 Dec 4 '14 at 20:25
  • Cool. Another good idea. If I've switched Primary/Standby back and forth a few times, both their alert logs will show "Media Recovery Waiting" in them, but at least the dates (provided by your egrep -B) will help. Here might be a problem case: Suppose San Jose is Primary and Boston is Standby and in MRP. Suppose San Jose has never yet been a Standby. Then I switchover and mount San Jose as standby but don't start MRP, instead opening it read-only. San Jose's alert trace won't show "Media Recovery Waiting", but it's in fact a standby role. Boston's alert log still has the old "Media..." lines. – Joshua Huber Dec 4 '14 at 20:46
  • You can at least get the last date for that message. If you use data guard with the data guard manager there will be more stats that you can use to see using either "SHOW CONFIGURATION VERBOSE;" or "SHOW DATABASE VERBOSE <dbsid>;". Ultimately if you want to monitor or manage an Oracle database you need to do it as the OS user at at least with the DBA group. You can create a script that runs as Oracle and provides just the information that you want, then setup a sudo so that a non-privileged user can only run that script. And that script won't change the database, just verify that it is OK. – Gandolf989 Dec 4 '14 at 20:57
  • I think the upshot is that I really just need to migrate to using DG Broker. I like the sudo w/ script idea, too. You've give me a lot of good ideas here. If I could give you 3 upvotes for these ideas, I would. – Joshua Huber Dec 4 '14 at 21:01
2

Ok, so I had some time to waste, and this is just for the "fun" or "interesting" factor. It's nowhere near that I would use in a real scenario, I have played with it in my lab environment on x86-64 Linux platform, with a few 10g, 11g and 12c databases. At least you can do this even if the database is shut down.

When you do a controlfile dump with:

alter session set events 'immediate trace name controlf level 3';

There is a section, that looks like this (its a standby database):

***************************************************************************
DATABASE ENTRY
***************************************************************************
 (size = 316, compat size = 316, section max = 1, section in-use = 1,
  last-recid= 0, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 1, numrecs = 1)
 11/08/2014 19:59:46
 DB Name "DQX"
 Database flags = 0x004054c7 0x00001200

Here at database flags, we have 0x004054c7. If the flag 0x0000400 is set, then the controlfile is a standy controlfile, so its a standby database. This is explained in the good old Oracle DSI Course 403e (Recovery Architechture Components), File Dump Analysis chapter, Control File Header section.

Another source, thats easier to obtain (search for "kcc3.h"): https://alovesly.files.wordpress.com/2013/03/oracle-recovery-internal2.pdf

I searched for the above values in hex dumps of controlfiles, and found that this particular information can be found at position 44030 (278576 decimal) in all the versions and databases I have tried (10.2.0.5, 11.2.0.4, 12.1.0.2). So we can get this information by dumping the controlfile as:

$ xxd -s 278576 -l 4 /oracle/base/oradata/DQX/control01.ctl
0044030: c754 4000                                .T@.

Byte order is reversed, swapping the bytes and the octets: 0x004054c7.

Doing this on a non-standby database, with minimal supplemental logging (0x4000000) and force logging (0x1000000) enabled (for GoldenGate replication):

$ xxd -s 278576 -l 4 /oracle/base/oradata/GOLD/control01.ctl
0044030: 0140 4050                                .@@P

Flags: 0x50404001

Relevant part from the trace:

***************************************************************************
DATABASE ENTRY
***************************************************************************
 (size = 316, compat size = 316, section max = 1, section in-use = 1,
  last-recid= 0, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 1, numrecs = 1)
 12/01/2014 23:24:49
 DB Name "GOLD"
 Database flags = 0x50404001 0x00001200
  • Brilliant. Totally appreciate your diving in deep into the bits on this one. I had never thought to reverse engineer the controlfile. I'm going to see if I can get this working on on AIX (shouldn't be too different from Linux w.r.t controlfile structure). – Joshua Huber Dec 4 '14 at 23:06
0

It COULD be simple, but then it's down to luck, more than to anything else. The way of working is very simple : get all processes listed of the main database, ignore the ones from the standby. Then, do the same with the standby database : collect all process info.

What is important here, is that you capture this while all is normal.

Then you compare both lists, in detail. Chances you find some process description active on one, but not on the other, is pretty high.

There you go, differing both databases on OS level.

It must be said, although you technically may be able to differ (as explained above), it's never a good sign if questions like that are asked. Either no permissions are granted, or knowledge is limited. Both are not good. The good way of differing both databases, is querying some V$ view. What I'm describing above, is an alternative method, but you should not rely too much on it. Why not ? Because when alternative situations emerge, the procedure may not work anymore.

I'm thinking of; databases being started up and such, but you described already that things may not work if different database modes are used.

  • Thanks for your answer. We pretty much had the same idea to start. I did a ps to gather all the processes for Oracle db on the primary and standby sites, sorted them, then cross checked for differences. I believe the only diff I found was that the standby may have ora_mrp0 or ora_pr(nn) processes, but only when doing managed recov., so no dice when standby is in a read-only reporting mode. The primary sometimes has an ora_cjq0, but it comes and goes when the DBMS Scheduler wakes/sleeps, so was also not reliable. The ora_rsm process is probably key, but I'm not using DG broker (yet). – Joshua Huber Dec 7 '14 at 3:30
  • A trick may also be to run both databases as different OS users (whatever the machine they are one). Then, it's made extremely easy to differ between those databases. Most people run all database on the same server (or any server) as the same user "oracle", but you should know you can run any database, as any user. That is, if you are not sharing the Oracle Home. It is possible to switch a complete database (including dedidated Oracle Home) to another OS user, but it's easier to install it right away as a specific user. If that is an option. – tvCa Dec 7 '14 at 10:39
  • So supposing I ran the primary and standby as different OS users (on different hosts, of course). Suppose Boston is primary and running as "oracle", and New York is standby and running as a different OS user, say "orastandby". Fine and nice until I do a switchover and make New York the primary. It would be a lot of work to change the OS user if all I want to do is switchover to standby. Without DG Broker, I do switchovers with about 4 commands. With DG Broker, this could be as little as 1 command. How would using different OS users help with this? – Joshua Huber Dec 8 '14 at 15:53
  • 2nd scenario, if I'm working as a consultant and go to a client's site, I might want to run some quick inventory on their Unix boxes to find out which databases they have and whether the instances are primary and standby. Making changes to the way the client's database are installed (such as the OS user), might be off limits, or would at least seem odd to the client. The client DBAs would probably not want to give me SYSDBA access. But the client would probably not push back much if I asked for an unprivileged OS user account on the server. – Joshua Huber Dec 8 '14 at 15:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.