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';