Oracle 19c – find primary DB information from a mounted standby

All we need is an easy explanation of the problem, so here it is.

I have a (probably simple) query about how to find an Oracle 19c primary database’s host by querying a mounted standby using sqlplus. I can get the primary name as follows:

select PRIMARY_DB_UNIQUE_NAME from v$database;

But how can I find the primary’s host in a similar way?

Many thanks.

As mentioned below in the discussion with sarat the following cmd based on his suggestion works:

select distinct(machine) from v$session where username='PUBLIC' and osuser='oracle' and machine <> (select PRIMARY_DB_UNIQUE_NAME from v$database);

but I need to add a leading header to the output in order to grab the result from the spool file afterwards.

select 'title'||distinct(machine) from v$session where username='PUBLIC' and osuser='oracle' and machine <> (select PRIMARY_DB_UNIQUE_NAME from v$database)

This fails though? Any way around that? Thanks.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

select distinct machine from v$session where username=’PUBLIC’ and osuser=’oracle’;

You can find the hostname of primary database server.

Method 2

If your tns name resolving is setup correctly you can use dbms_tns.resolve_tnsname to get the description of the tns alias for the PRIMARY_DB_UNIQUE_NAME.

See https://docs.oracle.com/en/database/oracle/oracle-database/18/arpls/DBMS_TNS.html#GUID-C9A939E2-8710-43BF-952C-B49D155C8163 for the usage details.

You might want to use regex to grab the host part from the address list.

Thinking about it …. This won’t work using sql in the mounted database but using tnsping it should work.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply