If your PowerCenter repository is on database host1.domain.com, please use the below format
Please confirm the SID by tnsping host1 from command prompt
Also, the configuration needs to be confirmed from the Oracle client side. I think the tnsping command shows the most useful information here. If tnsping is successful, it will show as fine. The tnsping shows where the connection information is picked up from, in this case /home/oracle/apps/oracle/product/18.104.22.168/client/network/admin/tnsnames.ora. You can use this same technique when it fails to determine which tnsnames.ora file is being used that would refer to the invalid Oracle server.
To clarify a few details before they get messed up badly:
TNSPING does not require a user name and a password. So using tnsping to test the IP connection to an Oracle database is a good idea.
Using SQL*PLUS requires a user name and a password (assuming you don't use Oracle wallets).
Using SQL*PLUS in a command task requires you (except with a wallet) to store user name and password in plain text somewhere (e.g. within the command itself). This probably will be a bad security breach. So that's an approach I usually don't recommend: everyone who has read access to the workflow can (ab-)use this user name and password for whatever purpose. Your security officers may want to kill you for that.
ping is a network command. It helps in (almost) no way when you are trying to identify connection problems to some Oracle database.
BTW why do you want to use a command task to check an Oracle connection?
You can perform the same action within a fairly simple PowerCenter mapping. You just need some dummy source delivering one record only; then use a SQL transformation to perform a "SELECT 2 AS two FROM DUAL" statement; the SQL transformation just needs one output port of type Integer named "two", and it will have a SQLError output port.
If the connection to the database works, then SQLError will be NULL. In any other case, SQLError will contain some error message. So you can easily distinguish between a successful and a failed attempt to connect using this SQLError output port.
And this approach has the additional advantage that neither any user name nor any password must be exposed in any way because these details are stored within the PowerCenter connection you use for the SQL transformation.
Does that help?
Please use the below SQLPLUS command to check the connectivity from your Linux box.
$ sqlplus <username>/<password>@connection_string