Shellscript: Which database is this service_name from?

Hey!
Strange question, right? You should know you applications and databases and how everything works, right?

Happen that some days ago a client requested me to identify from which database is a service_name. Easy? Not if the server have 46 database instances running…

Ok, now how can I can this information as quick as possible?

I know that the command below give me all distinct databases in CRS:

/u01/app/12.1.0.2/grid/bin/crsctl stat res -t -w "TYPE = ora.database.type"

Now, lets make some sed magic:

[root@nprd01dbadm01 ~]# /u01/app/12.1.0.2/grid/bin/crsctl stat res -t -w "TYPE = ora.database.type" |grep ora. |sed 's/ora./srvctl status service -d /' |sed 's/.db/ |grep \$1/' | head -1
srvctl status service -d dba |grep $1

Arrá!
Now let’s script this. To make it easier I created a script in root (to run every time you add or remove a database form CRs) and a script into oracle, that solve the problem it self. Take a look:

[root@nprd01dbadm01 ~]# cat /root/locate_service.sh
/u01/app/12.1.0.2/grid/bin/crsctl stat res -t -w "TYPE = ora.database.type" |grep ora. |sed 's/ora./srvctl status service -d /' |sed 's/.db/ |grep \$1/' > /home/oracle/locate_service.sh
[root@nprd01dbadm01 ~]# sh /root/locate_service.sh

Now let’s check the generated script:

[root@nprd01dbadm01 ~]# cat /home/oracle/locate_service.sh
srvctl status service -d dba |grep $1
srvctl status service -d dbb |grep $1
srvctl status service -d dbc |grep $1
srvctl status service -d dbd |grep $1
srvctl status service -d dbe |grep $1
srvctl status service -d dbf |grep $1
srvctl status service -d dbg |grep $1
srvctl status service -d dbh |grep $1
srvctl status service -d dbi |grep $1
srvctl status service -d dbj |grep $1
srvctl status service -d dbk |grep $1
srvctl status service -d dbl |grep $1
srvctl status service -d dbm |grep $1
srvctl status service -d dbn |grep $1
srvctl status service -d dbo |grep $1
srvctl status service -d dbp |grep $1
srvctl status service -d dbq |grep $1
srvctl status service -d dbr |grep $1
srvctl status service -d dbs |grep $1
srvctl status service -d dbt |grep $1
srvctl status service -d dbu |grep $1
srvctl status service -d dbv |grep $1
srvctl status service -d dbx |grep $1
srvctl status service -d dby |grep $1
srvctl status service -d dbz |grep $1
srvctl status service -d dbaa |grep $1
srvctl status service -d dbab |grep $1
srvctl status service -d dbac |grep $1
srvctl status service -d dbad |grep $1
srvctl status service -d dbae |grep $1
srvctl status service -d dbaf |grep $1
srvctl status service -d dbag |grep $1
srvctl status service -d dbah |grep $1
srvctl status service -d dbai |grep $1
srvctl status service -d dbaj |grep $1
srvctl status service -d dbak |grep $1
srvctl status service -d dbal |grep $1
srvctl status service -d dbam |grep $1
srvctl status service -d dban |grep $1
srvctl status service -d dbao |grep $1
srvctl status service -d dbap |grep $1
srvctl status service -d dbaq |grep $1
srvctl status service -d dbar |grep $1
srvctl status service -d dbas |grep $1
srvctl status service -d dbat |grep $1
srvctl status service -d dbau |grep $1

Cool, now let’s go to oracle user and simulate execution:

[root@nprd01dbadm01 ~]# su - oracle
[oracle@nprodserver]$ sh locate_service.sh app_xyz
Service app_xyz is running on instance(s) dbt1,dbt2

Cool!
Now I know service app_xyz is running in instances 1 and 2 from dbt database. 🙂

Hope it be useful to you too..

Enjoy!