Basically I am an oracle dba. We have a task to switch-over from primary db to standby db and we need tns entries (host-name) to be modified from sape-scan to sapi-scan in the tnsnames.ora file containing tns connection entries SAP_PROD, GOLD_PROD, and EVENTS_SAP.


      (ADDRESS = (PROTOCOL = TCP)(HOST = sape-scan.walmart.net)(PORT = 1521))

sape-scan should get changed to sapi-scan if the tns entry found as SAP_PROD

  • for people who aren't DBA's, is that format flexible? Will "SAP_PROD" always be 3 lines above any "sape-scan" text? Can "HOST" and "sape-scan..." be on separate lines? – Jeff Schaller Jul 13 '17 at 14:07
  • My Apologies, I dont see a formatting tool here using which I can make it look as it should be. Yes SAP_PROD will always be 3 lines above . It will work if we put it on seperate lines as long as the syntax is correct. But would be great if we keep it as it looks. – Mohammeddba Jul 13 '17 at 14:53
  • 1
    Look at the {} tool/button, it will format text as if you would find it in the config file. My question was more around "could the tnsnames file have linebreaks in it that we would need to allow for, in the substitution"? – Jeff Schaller Jul 13 '17 at 15:07
  • Thanks for your reply Jeff, There are no linebreaks in it. – Mohammeddba Jul 13 '17 at 19:45
  • Did any of the answers solve your problem? If so, please use the checkmark to tell the system that the question is Answered. Thank you! – Jeff Schaller Jul 30 '17 at 12:11

Another option, with sed:

sed -re '/^(SAP_PROD|GOLD_PROD|EVENTS_SAP) =/,/^$/s/HOST = sape-scan/HOST = sapi-scan/' tnsnames.ora
  • turn on extended regular expressions (-r) for the | regex alternation
  • take the next parameter (the quoted text) as an expression to evaluate (-e)
  • between lines bracketed by either "SAP_PROD", "GOLD_PROD", or "EVENTS_SAP" at the beginning of the line, followed by space and equals sign, until a blank line,
  • search and replace the text "HOST = sape-scan" with the text "HOST = sapi-scan"
  • in the file tnsnames.ora

Use the above version to non-destructively test your changes, perhaps with:

sed ... tnsnames.ora > tnsnames.new
diff tnsnames.ora tnsnames.new

... to see what changed. In many sed implementations, you can add a -i flag to then make the changes "in-place".

  • Nope, It did not work. Using the above command its just displaying the whole file as it was earlier without any changes just like ' cat tnsnames.ora ' file command. – Mohammeddba Jul 17 '17 at 11:51
  • Perhaps there are no blank lines in the input file so the match criteria isn't fulfilled? – Jeff H. Jun 18 at 21:17

Assuming an entry starts when there is text in the first column, you can change those three entries with

awk '/^[A-Z]/ { substitute = ($1 ~ /^((SAP|GOLD)_PROD|EVENTS_SAP)$/) }
    substitute { sub(/sape-scan/, "sapi-scan") }1' tnsnames.ora >tnsnames.new

Awk processes each line from the input line according to the script, i.e. the text between the single quotes is a small program which is applied to each line in your file.

In this script, the variable substitute gets set to a boolean indicating whether the header line matches the regular expression. If the boolean is true, we perform the substitution on each line, until we see another header line, and the boolean's value gets evaluated again and possibly updated. The lone 1 causes every line to be printed.

The output is placed in a new file so you can diff etc against the original. If you are satisfied with the result, you can replace the old file simply by mv:ing the new file to the old file's name.

  • If I guessed wrong about the input file format, maybe edit your question to describe the file in more detail. – tripleee Jul 13 '17 at 15:53
  • all - Thank you so much for all your inputs, really appreciate your efforts. It worked using the above command using awk. Thanks @triplee, Jeff , don_crissti , SuperKrish – Mohammeddba Jul 17 '17 at 12:03

Very simple brother, use sed like below

 sed -i -e 's/sape-scan/sapi-scan/g' $(grep -R 'SAP_PROD' ./ *ora|cut -d ':' -f1)

Thats it.:)

  • @don_crissti I update my answer, please check it – SuperKrish Jul 13 '17 at 14:32
  • Thanks for your update Krish, I tried your answer but it did not work. [oracle@testmachine1 admin]$ sed -i -e 's/sape-scan/sapi-scan/g' $(grep -R 'SAP_PROD' ./ *js|cut -d ':' -f1) grep: *js: No such file or directory [oracle@testmachine1 admin]$ [oracle@testmachine1 admin]$ ls -lrt tnsnames.ora -rw-r--r-- 1 oracle oinstall 189 Jul 13 20:20 tnsnames.ora [oracle@testmachine1 admin]$ – Mohammeddba Jul 13 '17 at 14:51
  • @don_crissti . That's absolutely correct . I have a file by name "tnsnames.ora" which contains hundreds of entries it should not modify "sape" to "sapi" in the entire file rather on match cases for "SAP_PROD,GOLD_PROD,EVENTS_SAP" . Thanks – Mohammeddba Jul 13 '17 at 14:59
  • @Mohammeddba , sorry i add please check now, I thought your file extension is filename.ora. If yes try this, else change your file extension with *ora – SuperKrish Jul 13 '17 at 15:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.