AWK: Get/Extract String Matching Regexp

Example of how to use AWK in order to get/extract/return parts of lines/strings, matching a "dynamic" regular expression (specified through a shell environment variable).

Source file used in this example

$ cat example.txt

Attr[27716, Field[4937190,usage=51]]

Attr[27716, Field[4937191,usage=10]]

Attr[27716, Field[4937192,usage=321]]

Two usages

# Defining the regular expression pattern as environment variable

$ MY_PATTERN="([0-9]+),used"

# Using the predefined awk variables RSTART and RLENGTH

# Returns the whole string matching the regexp

$ awk -v PATTERN="${MY_PATTERN}" 'match($0, PATTERN) { print substr( $0, RSTART, RLENGTH) }' example.txt

4937190,usage

4937191,usage

4937192,usage

# Using the capture group & array functionality in order to get only the regexp part in parenthesis

$ awk -v PATTERN="${MY_PATTERN}" 'match($0, PATTERN, array) { print array[1] }' example.txt

4937190

4937191

4937192

Filtering and Splitting Lines With AWK & "while read"

In this example lines contained in a ksh/bash String are:

• filtered (by awk)
• duplicate consecutive lines are removed (by awk)
• then split only using the shell internal "while read" feature (without needing to use "cut, awk"  or other external "binaries")

Example: if ${RESULT_STR} (see below) contains:

*********************** Backup Status of DB_29384_SITE1 ************************

Backup Type   Status                   Start Time       Error Message

------------- ------------------------ ---------------- ---------------------------

ARCHIVELOG    COMPLETED                2018.02.07 16:54 

ARCHIVELOG    COMPLETED WITH WARNINGS  2018.02.07 17:00 RMAN-08137: WARNING: ...

ARCHIVELOG    COMPLETED                2018.02.07 20:55 

ARCHIVELOG    COMPLETED WITH WARNINGS  2018.02.07 21:06 RMAN-08137: WARNING: ...

DB INCR       COMPLETED                2018.02.08 02:00  ARCHIVELOG    COMPLETED                2018.02.08 03:04 

DB INCR       COMPLETED                2018.02.08 03:06 

ARCHIVELOG    COMPLETED WITH WARNINGS  2018.02.08 03:53 RMAN-08137: WARNING: ...

ARCHIVELOG    COMPLETED                2018.02.08 04:54 

AWK: Filtering & Removing Duplicate Lines

The result of

print - "${RESULT_STR}" | 

  awk '/ RMAN-| ORA-/{ 

    MSG=substr($0, 86)

    if(MSG!=PREV){ 

      print MSG

      PREV=MSG 

    }

  }' 

is:

("/ RMAN-| ORA-/" filters the "interesting" lines – containing error/warning messages. "substr($0, 86)" returns the end of the string starting with column 57. "MSG and PREV" are used in order to remove duplicate consecutive lines)

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process 

While Read: Splitting The Input

"errNr" gets the characters up to the first space, "errTxt" receives all other characters until the end of line

print - "${RESULT_STR}" | 

  awk '/ RMAN-| ORA-/{

    MSG=substr($0, 57)

    if(MSG!=PREV){ 

      print MSG

       PREV=MSG

   }

  }' |

  while read errNr errTxt ; do

    print - "errNr: ${errNr} / errTxt: ${errTxt} "

  done

Output

errNr: RMAN-08137: / errTxt: WARNING: archived log not deleted, needed for standby or upstream capture process 

Specifying a "while read" Delimiter

The delimiter can be changed from space to colon by using "IFS=':'"

IFS=':'

print - "${RESULT_STR}" |

  awk '/ RMAN-| ORA-/{MSG=substr($0, 57); if(MSG!=PREV){ print MSG; PREV=MSG }}' |

  while read errNr errTxt ; do

    print - "errNr: ${errNr} / errTxt: ${errTxt} "

  done

Output (without ':' after RMAN-08137)

errNr: RMAN-08137 / errTxt: WARNING: archived log not deleted, needed for standby or upstream capture process

[]

AWK Usage Example: Generating Archivelog Register Statements

Here examples of generating Oracle DB statements in order to register archived log files stored in ASM into the DB.

In order to keep the implementation simple, only one archived log directory (variable P) is processed by each call.

Meaning of the awk Variables below:

• A <- used in order print the character '
• p <- holds the archived log directory path specified by the environment variable P
• $8 <- Holds the strings of the 8th column. That means, contains the names of the archive log files

OS>

P="U02/<DbUniqueName>/ARCHIVELOG/2018_01_19/"

echo "ls -l $P" |

  asmcmd |

  awk -v A="'" -v p="$P" '/^ARCH/{ print "ALTER DATABASE REGISTER LOGFILE "A p $8 A";" }' |

  more

 

Output

. . .

ALTER DATABASE REGISTER LOGFILE 'U02/<DbUniqueName>/ARCHIVELOG/2018_01_19/thread_1_seq_12145.643.965816317';

ALTER DATABASE REGISTER LOGFILE 'U02/<DbUniqueName>/ARCHIVELOG/2018_01_19/thread_1_seq_12146.644.965816317';

ALTER DATABASE REGISTER LOGFILE 'U02/<DbUniqueName>/ARCHIVELOG/2018_01_19/thread_1_seq_12147.266.965815979';

 

Instead of the statement more as last command in the pipe, the output could by written into a file.

Another example of generating statements for archivelogs with sequence numbers >= than a given number (here 12090)  (SeqNo="12090"):

SeqNo="12094"

P="U02/<DbUniqueName>/ARCHIVELOG/2018_01_19/"

echo "ls -l $P" |

  asmcmd |

  head |

  awk -v A="'" -v p="$P" -v n="$SeqNo" '/^ARCH/{

    if(n!="") { 

      match($0, /.+_([0-9]+)\./, sn)

    }

    if(n=="" || sn[1]>=n) {

      print "ALTER DATABASE REGISTER LOGFILE "A p $8 A";" 

    }

  }' |

  more

Output

ALTER DATABASE REGISTER LOGFILE 'U02/<DbUniqueName>/ARCHIVELOG/2018_01_19/thread_1_seq_12094.415.965815957';

ALTER DATABASE REGISTER LOGFILE 'U02/<DbUniqueName>/ARCHIVELOG/2018_01_19/thread_1_seq_12095.432.965815957';

ALTER DATABASE REGISTER LOGFILE 'U02/<DbUniqueName>/ARCHIVELOG/2018_01_19/thread_1_seq_12096.428.965815957';

If an "empty" SeqNo is given, this parameter will be ignored

SeqNo=""

P="U02/<DbUniqueName>/ARCHIVELOG/2018_01_19/"

echo "ls -l $P" |

  asmcmd |

  head |

  awk -v A="'" -v p="$P" -v n="$SeqNo" '/^ARCH/{

    if(n!="") { 

      match($0, /.+_([0-9]+)\./, sn)

    }

    if(n=="" || sn[1]>=n) {

      print "ALTER DATABASE REGISTER LOGFILE "A p $8 A";" 

    }

  }' |

  more

Output: all archivelogs are now listed

ALTER DATABASE REGISTER LOGFILE 'U02/<DbUniqueName>/ARCHIVELOG/2018_01_19/thread_1_seq_1.444.965820029';

ALTER DATABASE REGISTER LOGFILE 'U02/<DbUniqueName>/ARCHIVELOG/2018_01_19/thread_1_seq_10.440.965828087';

ALTER DATABASE REGISTER LOGFILE 'U02/<DbUniqueName>/ARCHIVELOG/2018_01_19/thread_1_seq_12090.404.965815957';

ALTER DATABASE REGISTER LOGFILE 'U02/<DbUniqueName>/ARCHIVELOG/2018_01_19/thread_1_seq_12091.399.965815959';

ALTER DATABASE REGISTER LOGFILE 'U02/<DbUniqueName>/ARCHIVELOG/2018_01_19/thread_1_seq_12092.398.965815959';

ALTER DATABASE REGISTER LOGFILE 'U02/<DbUniqueName>/ARCHIVELOG/2018_01_19/thread_1_seq_12093.397.965815959';

ALTER DATABASE REGISTER LOGFILE 'U02/<DbUniqueName>/ARCHIVELOG/2018_01_19/thread_1_seq_12094.415.965815957';

ALTER DATABASE REGISTER LOGFILE 'U02/<DbUniqueName>/ARCHIVELOG/2018_01_19/thread_1_seq_12095.432.965815957';

ALTER DATABASE REGISTER LOGFILE 'U02/<DbUniqueName>/ARCHIVELOG/2018_01_19/thread_1_seq_12096.428.965815957';