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 cases where scan name isn't used and hence, the output will have multiple hostnames.

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.