Author Posts

October 19, 2014 at 3:37 pm

Hi

I would like to read two log files and compare the numbers.
If the difference is more than 5, email should be sent.
I want to grep the latest string start with "Media Recovery" and capture the log number.
Here it is 6465.
I would like to compare it with another logfile.

Sample Log file output

*************************************************************************************
Media Recovery Log C:\ARCHIVE\ARCH6463_1_850416493.LOG
Mon Oct 20 10:10:05 2014
Media Recovery Log C:\ARCHIVE\ARCH6464_1_850416493.LOG
Errors with log C:\ARCHIVE\ARCH6464_1_850416493.LOG
ORA-279 signalled during: ALTER DATABASE RECOVER automatic from 'C:\archive' database until cancel using backup controlfile ...
Mon Oct 20 10:10:05 2014
ALTER DATABASE RECOVER CANCEL
Mon Oct 20 10:10:06 2014
Media Recovery Canceled
Completed: ALTER DATABASE RECOVER CANCEL
Mon Oct 20 11:10:00 2014
ALTER DATABASE RECOVER automatic from 'C:\archive' database until cancel using backup controlfile
Media Recovery Start
Mon Oct 20 11:10:01 2014
Media Recovery Log C:\ARCHIVE\ARCH6464_1_850416493.LOG
Mon Oct 20 11:10:17 2014
Media Recovery Log C:\ARCHIVE\ARCH6465_1_850416493.LOG
Errors with log C:\ARCHIVE\ARCH6465_1_850416493.LOG
ORA-279 signalled during: ALTER DATABASE RECOVER automatic from 'C:\archive' database until cancel using backup controlfile ...
Mon Oct 20 11:10:17 2014
ALTER DATABASE RECOVER CANCEL
Mon Oct 20 11:10:17 2014
Media Recovery Canceled
Completed: ALTER DATABASE RECOVER CANCEL

*************************************************************************************

In unix, I can achive this something like following:

x1= grep 'Media Recovery Log ' alert1.log | tail -1 | awk '{print $4}'| cut -d'_' -f2 | cut -d'.' -f1

echo $x1
6465

x2 grep 'Media Recovery Log ' alert2.log | tail -1 | awk '{print $4}'| cut -d'_' -f2 | cut -d'.' -f1
echo $x2
6462

if x1-x2 >5

.....

Not sure how to do same in powershell

October 19, 2014 at 4:42 pm

You can do something like this:

$match = Select-String -Pattern 'Media Recovery Log \D+(\d+)' -Path alert1.log |
         Select-Object -Last 1

$x1 = [int]$match.Matches[0].Groups[1].Value

$match = Select-String -Pattern 'Media Recovery Log \D+(\d+)' -Path alert2.log |
         Select-Object -Last 1

$x2 = [int]$match.Matches[0].Groups[1].Value

if ($x1-$x2 -gt 5)
{
    # . . .
}

This gets a little bit into regular expressions, but cuts down on the work that you have to do to extract the number. No need to use awk | cut | cut, since the regex pattern lets you pull out exactly the part you are looking for.

October 20, 2014 at 1:14 pm

Hi Dave,

Many thanks for your prompt response, it worked for the second log !!

Actually, first log is different than second one.
Below is the logfile.
I want to capture the latest number start with "log sequence" string.
Here it is 6599.

then I will compare it with other log (media recovery) that you have already provided solution.

Any idea how to do that ?

*******************************************************************************************************************************
Thread 1 cannot allocate new log, sequence 6596
Private strand flush not complete
Current log# 5 seq# 6595 mem# 0: C:\ORACLE\ORADATA\THRPROD\REDO05A.LOG
Current log# 5 seq# 6595 mem# 1: D:\ORACLE\ORADATA\THRPROD\REDO05B.LOG
Tue Oct 21 08:23:29 2014
Thread 1 advanced to log sequence 6596 (LGWR switch)
Current log# 1 seq# 6596 mem# 0: C:\ORACLE\ORADATA\THRPROD\REDO01A.LOG
Current log# 1 seq# 6596 mem# 1: D:\ORACLE\ORADATA\THRPROD\REDO01B.LOG
Tue Oct 21 08:27:15 2014
ALTER SYSTEM ARCHIVE LOG
Tue Oct 21 08:27:15 2014
Thread 1 cannot allocate new log, sequence 6597
Checkpoint not complete
Current log# 1 seq# 6596 mem# 0: C:\ORACLE\ORADATA\THRPROD\REDO01A.LOG
Current log# 1 seq# 6596 mem# 1: D:\ORACLE\ORADATA\THRPROD\REDO01B.LOG
Tue Oct 21 08:27:18 2014
Thread 1 advanced to log sequence 6597 (LGWR switch)
Current log# 2 seq# 6597 mem# 0: C:\ORACLE\ORADATA\THRPROD\REDO02A.LOG
Current log# 2 seq# 6597 mem# 1: D:\ORACLE\ORADATA\THRPROD\REDO02B.LOG
Tue Oct 21 08:27:20 2014
ALTER SYSTEM ARCHIVE LOG
Tue Oct 21 08:27:20 2014
Thread 1 cannot allocate new log, sequence 6598
Private strand flush not complete
Current log# 2 seq# 6597 mem# 0: C:\ORACLE\ORADATA\THRPROD\REDO02A.LOG
Current log# 2 seq# 6597 mem# 1: D:\ORACLE\ORADATA\THRPROD\REDO02B.LOG
Tue Oct 21 08:27:21 2014
Thread 1 advanced to log sequence 6598 (LGWR switch)
Current log# 3 seq# 6598 mem# 0: C:\ORACLE\ORADATA\THRPROD\REDO03A.LOG
Current log# 3 seq# 6598 mem# 1: D:\ORACLE\ORADATA\THRPROD\REDO03B.LOG
Tue Oct 21 09:10:50 2014
Thread 1 advanced to log sequence 6599 (LGWR switch)
Current log# 4 seq# 6599 mem# 0: C:\ORACLE\ORADATA\THRPROD\REDO04A.LOG
Current log# 4 seq# 6599 mem# 1: D:\ORACLE\ORADATA\THRPROD\REDO04B.LOG

October 20, 2014 at 5:34 pm

Same idea, slightly different regular expression for the first file. Try this:

$match = Select-String -Pattern 'log sequence (\d+)' -Path alert1.log |
         Select-Object -Last 1
 
$x1 = [int]$match.Matches[0].Groups[1].Value
 
$match = Select-String -Pattern 'Media Recovery Log \D+(\d+)' -Path alert2.log |
         Select-Object -Last 1
 
$x2 = [int]$match.Matches[0].Groups[1].Value
 
if ($x1-$x2 -gt 5)
{
    # . . .
}

October 21, 2014 at 4:20 pm

Thanks heaps Dave.