"Convert" connect identifier to hostname

It's always easier to remember database connect identifiers than the database hostnames, especially when you have to manage lots of databases. You could run `tnsping connid' to find the host given the connect identifier connid. But if the database is RAC, tnsping returns SCAN, not the actual hostname(s). You can manually run sqlplus with this identifier to connect to the database and query (g)v$instance. The following Windows cmd script, called conn2host.cmd, automates this process for you.

C:\Temp>type conn2host.cmd
@echo off
FIND "/*%none% lbl" <%0 |sqlplus -s dbsnmp/xxxxxx@%1
GOTO end
: ----------- embedded SQL-------------------------
/* lbl */ set pages 0 head off feedb off
/* lbl */ select host_name from gv$instance order by 1;
: ----------- end of embedded SQL-------------------------
:end
C:\Temp>conn2host.cmd connid

mydbservera
mydbserverb

Please change dbsnmp/xxxxxx to whatever username and password you have in the database and make sure the user can query v$instance. This single file mimics a UNIX here-document. You can of course split it into two files, and the main script calls the .sql file with @ symbol.

If you're on Linux/UNIX instead of Windows, conn2host.sh will be even easier:

$ cat conn2host.sh
#!/bin/bash
sqlplus -s dbsnmp/xxxxxx@$1 <<EOF
set pages 0 head off feedb off
select host_name from gv\$instance order by 1;
exit
EOF
$ ./conn2host.sh connid
mydbservera
mydbserverb