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

Question

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.

Answer 1

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

You can find the hostname of primary database server.

Answer 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.

Answer 3

The accepted answer assumes that the standby database is in mount state and is not open for readonly queries!

However, if Active dataguard (ADG) is enabled, then for the machine column, one would also see Application nodes, which renders the accepted answer invalid/incorrect.

I would suggest to use the below query, instead:

select db_unique_name from v$dataguard_config where dest_role = 'PRIMARY DATABASE';

Using this query, there's no chance incorrect machine name being returned.

Here's a useful Bash oneliner to retrieve the exact database host using the above query and tnsping (works for both single node and RAC primary DBs):

PRIMARY_DB_NAME=$(printf "conn / as sysdba\nset feed off head off verify off echo off pages 0\nselect db_unique_name from v\$dataguard_config where dest_role = 'PRIMARY DATABASE';\nexit\n" | sqlplus -s /nolog); tnsping ${PRIMARY_DB_NAME} | grep DESCRIPTION | tr '\)' '\n' | awk -F"=" '/HOST/ {print $2}' | head -1 | tr -d '[:space:]'

NOTE: The command head -1 is for RAC primary DB in cases where scan name isn't used and hence, the output will have multiple hostnames.