Querying all client databases from cloud control host for custom output

Below is a quick way to access all client databases to get the output of a specific query. This can be really helpful during audits where particular parameter or configuration is checked throughout all the databases.

In the example cloud control host (Running RHEL 7.5) is used to check whether flashback is enabled on every client database.

First generate TNS names of client databases based on tnsnames.ora,

 cat tnsnames.ora | grep -iv "(" | grep -i "=" | cut -d = -f 1 > db_list.out  

Then run the simple bash script below,

 #!/bin/bash  
 export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1  
 export PATH=/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/u01/app/oracle/product/12.1.0.2/db_1/bin:/bin  
 LOC=/home/oracle/audit_outs  
 cat $LOC/db_list.txt | while read line  
 do  
 export ORACLE_SID=$line  
 sqlplus -S -M "HTML ON" dbsnmp/"<password>"@"$line"<<EOF  
 set lines 1000  
 spool $LOC/$line.out  
 select name,flashback_on from v\$database;  
 exit;  
 EOF  
 done