0

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.

1
  • I've just dropped the distinct function and simply used grep to get the first element of the list of hosts. So it's all good.
    – Franco
    Aug 19 at 13:33
1

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

You can find the hostname of primary database server.

4
  • The following command words: select distinct(machine) from v$session where username='PUBLIC' and osuser='oracle' and machine <> (select PRIMARY_DB_UNIQUE_NAME from v$database); But could there be issues if there are multiple standbys and the primary is a RAC?
    – Franco
    Aug 19 at 9:59
  • There wouldn't be any issue - You are firing this SQL from the standby database - so this query will show the hostname of the primary alone.
    – sarat
    Aug 19 at 10:37
  • It will give both primary hosts if it's a RAC DB. Which is fine ... I just need one of them. Would like to add a prefix to the result though ... which doesn't appear to work when using the distinct function. Any ideas? (I've updated the Q above).
    – Franco
    Aug 19 at 10:59
  • 1
    I used in this way... PRIMARY_HO=$(sqlplus -s "/ as sysdba" <<-EOF set pages 0 feed off select distinct machine from v\$session where username='PUBLIC' and osuser='oracle' and rownum<2 order by machine;
    – sarat
    Aug 20 at 8:39
1

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.

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.