Search and capture multiple groups between two patterns

Question

I need to capture the content of my alert log for lines starting with ORA-01555 and their related SQL statements.

The file content is below:

Sat Oct 21 12:11:40 2017
Thread 1 advanced to log sequence 143 (LGWR switch)
  Current log# 5 seq# 143 mem# 0:
Sat Oct 21 12:12:22 2017
Thread 1 cannot allocate new log, sequence 144
Private strand flush not complete
  Current log# 5 seq# 143 mem# 0:
Sat Oct 21 12:12:22 2017
ORA-01555 caused by SQL statement below (SQL ID: 1sfgms7z66zrs, SCN: 0x0401.1e45bbc4):
Sat Oct 21 12:11:48 2017
CREATE INDEX AAD_SAA_ADB_RESULTS_AUD_GTI1 ON AAD_PS_SAA_ADB_RESULTS_AUD_GT (
 rpt_date
)
Sat Oct 21 12:20:56 2017
Thread 1 advanced to log sequence 153 (LGWR switch)
  Current log# 6 seq# 153 mem# 0: 
+REDO_VOL1/DSS1STG/ONLINELOG/group_6.262.957710915
  Current log# 6 seq# 153 mem# 1: 
+REDO_VOL1/DSS1STG/ONLINELOG/group_6.263.957710917
Sat Oct 21 12:21:11 2017
ORA-01555 caused by SQL statement below (SQL ID: 9ad5awvfvsfd9, Query 
Duration=6444 sec, SCN: 0x0401.1e53de65):
Sat Oct 21 12:21:11 2017
SELECT a.EMPLID,    a.TEST_ID,    a.TEST_COMPONENT,    a.SCORE
    FROM
    ODS_PSE.PS_STDNT_TEST_COMP a,
    DSS_RDS.IR_STU_TST_SCR_SAT_VLD_DT_ST b
    where
    a.TEST_COMPONENT in ('VE','MA')
    AND a.EMPLID = b.PRSN_UNIV_ID
    AND ROUND(a.SCORE) = a.SCORE
    AND a.TEST_ID = b.STU_TST_CD
    AND a.TEST_DT = b.TEST_DT
    AND a.LS_DATA_SOURCE = b.LS_DATA_SOURCE
UNION
    SELECT a.EMPLID,    a.TEST_ID,    a.TEST_COMPONENT,    a.SCORE
    FROM
    ODS_PSE.PS_STDNT_TEST_COMP a,
    DSS_RDS.IR_STU_TST_SCR_SAT_VLD_DT_ST b
    where
    a.TEST_COMPONENT in ('ERWS','MSS','MT','RT','WLT') 
    AND a.EMPLID = b.PRSN_UNIV_ID
    AND ( 
            (ROUND(a.SCORE) = a.SCORE and a.TEST_COMPONENT in 
('ERWS','MSS','RT','WLT'))
        or  (a.SCORE BETWEEN 10 AND 40  and a.TEST_COMPONENT in ('MT'))
        )
    AND a.TEST_ID = b.STU_TST_CD
    AND a.TEST_DT = b.TEST_DT
    AND a.LS_DATA_SOURCE = b.LS_DATA_SOURCE
Sat Oct 21 13:05:01 2017
Thread 1 advanced to log sequence 173 (LGWR switch)
  Current log# 5 seq# 173 mem# 0:     +REDO_VOL1/DSS1STG/ONLINELOG/group_5.257.957710909
  Current log# 5 seq# 173 mem# 1:     +REDO_VOL1/DSS1STG/ONLINELOG/group_5.256.957710911

The content I want to capture is:

ORA-01555 caused by SQL statement below (SQL ID: 1sfgms7z66zrs, SCN: 0x0401.1e45bbc4):
Sat Oct 21 12:11:48 2017
CREATE INDEX AAD_SAA_ADB_RESULTS_AUD_GTI1 ON AAD_PS_SAA_ADB_RESULTS_AUD_GT (
 rpt_date
)
ORA-01555 caused by SQL statement below (SQL ID: 9ad5awvfvsfd9, Query 
Duration=6444 sec, SCN: 0x0401.1e53de65):
Sat Oct 21 12:21:11 2017
SELECT a.EMPLID,    a.TEST_ID,    a.TEST_COMPONENT,    a.SCORE
    FROM
    ODS_PSE.PS_STDNT_TEST_COMP a,
    DSS_RDS.IR_STU_TST_SCR_SAT_VLD_DT_ST b
    where
    a.TEST_COMPONENT in ('VE','MA')
    AND a.EMPLID = b.PRSN_UNIV_ID
    AND ROUND(a.SCORE) = a.SCORE
    AND a.TEST_ID = b.STU_TST_CD
    AND a.TEST_DT = b.TEST_DT
    AND a.LS_DATA_SOURCE = b.LS_DATA_SOURCE
UNION
    SELECT a.EMPLID,    a.TEST_ID,    a.TEST_COMPONENT,    a.SCORE
    FROM
    ODS_PSE.PS_STDNT_TEST_COMP a,
    DSS_RDS.IR_STU_TST_SCR_SAT_VLD_DT_ST b
    where
    a.TEST_COMPONENT in ('ERWS','MSS','MT','RT','WLT') 
    AND a.EMPLID = b.PRSN_UNIV_ID
    AND ( 
            (ROUND(a.SCORE) = a.SCORE and a.TEST_COMPONENT in 
('ERWS','MSS','RT','WLT'))
        or  (a.SCORE BETWEEN 10 AND 40  and a.TEST_COMPONENT in ('MT'))
        )
    AND a.TEST_ID = b.STU_TST_CD
    AND a.TEST_DT = b.TEST_DT
    AND a.LS_DATA_SOURCE = b.LS_DATA_SOURCE

Here is the grep I'm using but as you can see in the result it isn't capturing the content for each ORA-01555 line and its entire SQL statement. Not sure why my negative lookahead isn't quite working:

grep -Pzo 'ORA-01555.*\n(^.*[0-9]{2}:[0-9]{2}:[0-9]{2}.*)\n.*(?!.*[0-9]{2}:[0-9]{2}.*\n).+' alert.log

ORA-01555 caused by SQL statement below (SQL ID: 1sfgms7z66zrs, SCN: 0x0401.1e45bbc4):
Sat Oct 21 12:11:48 2017
CREATE INDEX AAD_SAA_ADB_RESULTS_AUD_GTI1 ON AAD_PS_SAA_ADB_RESULTS_AUD_GT (
ORA-01555 caused by SQL statement below (SQL ID: 9ad5awvfvsfd9, Query Duration=6444 sec, SCN: 0x0401.1e53de65):
Sat Oct 21 12:21:11 2017
SELECT a.EMPLID,    a.TEST_ID,    a.TEST_COMPONENT,    a.SCORE

ctac_ · Accepted Answer · 2017-10-22T22:45:05.640

You can try this sed command.

sed -E '/ORA-01555/!d;:A;N;/\n[A-Za-z]{3} [A-Za-z]{3} [0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2} [0-9]{4}$/!bA;:B;N;/\n[A-Za-z]{3} [A-Za-z]{3} [0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2} [0-9]{4}$/!bB;s/(.*)\n.*/\1/' infile

If a line contain ORA-01555 --> keep in patern space.

Take the next line in patern space while it's not a date.

Keep the first date in the patern space.

Continue to take the next line while it's not a date.

Delete the last line in the patern space which is a date.

Print the patern space and return to the start to find a new line with ORA-01555.

score 0 · Answer 2 · answered Oct 22 '17 at 18:23

0

awk solution:

awk '/ORA-01555/{ f=1 }f && /^[A-Z][a-z]{2} .* [0-9]{4}$/{ date++; if(date>=2) f=date=0 }f' alert.log

answered Oct 22 '17 at 18:23

RomanPerekhrest

88,541
4
65
105

Hmm, this returns the entire file contents. Not what I'm looking for – Bill Oct 22 '17 at 20:13
@BillMyers, doubt about that. This screenshot https://ibb.co/i9HFy6 is your friend – RomanPerekhrest Oct 22 '17 at 20:16
No doubt that's what I'm looking for friend but I'm getting the entire file. What version of awk are you running? I'm on RHEL 6.9 and GNU Awk 3.1.7. – Bill Oct 22 '17 at 20:58

Search and capture multiple groups between two patterns

2 Answers2