FAQ
Hi all,
I just got a corrupt block again, had fixed a block corruption in a same index file two weeks ago. Don't know why this happened twice in such a sort period.

I tried rman validate and dbverify to detect the corruption, but rman validate did not report any data corruption while dbverify did.

Could anyone shed any light on this?

Thanks,
Lu


The following is the output from rman validate and dbverify.

Using backup validate:

RMAN> backup check logical validate datafile 'F:\ORACLE\ERPPROD\DB\APPS_ST\DATA\A_TXN_IND01.DBF';
Starting backup at 28-DEC-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fnochannel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 28-DEC-11

Using dbverify:

dbv FILE=F:\ORACLE\ERPPROD\DB\APPS_ST\DATA\A_TXN_IND01.DBF FEEDBACK0
Corrupt block relative dba: 0x06426321 (file 25, block 156449)
Bad check value found during dbv:
.......
DBVERIFY - Verification complete
Total Pages Examined : 393216
Total Pages Processed (Data) : 2782
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 93966
Total Pages Failing (Index): 0
Total Pages Processed (Other): 19301
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 277166
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 1326171528 (0.1326171528)

Looks rman validate did not ddetect any data corruption while dbverify did.

Search Discussions

  • Howard Latham at Dec 28, 2011 at 7:53 pm
    Check and replace hardware

    Sent from my Windows Phone
    From: Jiang, Lu
    Sent: 28/12/2011 19:46
    To: oracle-l@freelists.org
    Subject: Block corruption again
    Hi all,
    I just got a corrupt block again, had fixed a block corruption in a
    same index file two weeks ago. Don't know why this happened twice in
    such a sort period.

    I tried rman validate and dbverify to detect the corruption, but rman
    validate did not report any data corruption while dbverify did.

    Could anyone shed any light on this?

    Thanks,
    Lu


    The following is the output from rman validate and dbverify.

    Using backup validate:

    RMAN> backup check logical validate datafile
    'F:\ORACLE\ERPPROD\DB\APPS_ST\DATA\A_TXN_IND01.DBF';
    Starting backup at 28-DEC-11
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting full datafile backupset
    channel ORA_DISK_1: specifying datafile(s) in backupset
    input datafile fnochannel ORA_DISK_1: backup set complete, elapsed
    time: 00:00:25
    Finished backup at 28-DEC-11

    Using dbverify:

    dbv FILE=F:\ORACLE\ERPPROD\DB\APPS_ST\DATA\A_TXN_IND01.DBF FEEDBACK0
    Corrupt block relative dba: 0x06426321 (file 25, block 156449)
    Bad check value found during dbv:
    .......
    DBVERIFY - Verification complete
    Total Pages Examined : 393216
    Total Pages Processed (Data) : 2782
    Total Pages Failing (Data) : 0
    Total Pages Processed (Index): 93966
    Total Pages Failing (Index): 0
    Total Pages Processed (Other): 19301
    Total Pages Processed (Seg) : 0
    Total Pages Failing (Seg) : 0
    Total Pages Empty : 277166
    Total Pages Marked Corrupt : 1
    Total Pages Influx : 0
    Highest block SCN : 1326171528 (0.1326171528)

    Looks rman validate did not ddetect any data corruption while dbverify did.
  • Timo Raitalaakso at Dec 28, 2011 at 10:38 pm
    There are operation system drivers in between Oracle and hardware. Check
    and replace also those if needed.

    --
    Timo Raitalaakso
    http://rafudb.blogspot.com

    28.12.2011 21:52, Howard Latham wrote:
    Check and replace hardware

    From: Jiang, Lu
    Sent: 28/12/2011 19:46
    Subject: Block corruption again
    Hi all,
    I just got a corrupt block again, had fixed a block corruption in a
    same index file two weeks ago. Don't know why this happened twice in
    such a sort period.

    Could anyone shed any light on this?

    Thanks,
    Lu
    --
    http://www.freelists.org/webpage/oracle-l
  • Taylor, Chris David at Dec 29, 2011 at 1:32 pm
    Seems to me if this was a hardware problem, and all the datafiles are on the same LUN, then block corruptions would be occurring in more than 1 file.

    Being that this is an index datafile, I would assume that it only contains index segments so it might be worthwhile tracking down which index contains the corrupted block.

    What version of Oracle are you running, and is there anything special about this database (Dataguard configuration etc etc)?



    Chris Taylor
    Sr. Oracle DBA
    Ingram Barge Company
    Nashville, TN 37205

    "Quality is never an accident; it is always the result of intelligent effort."
    -- John Ruskin (English Writer 1819-1900)

    CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Howard Latham
    Sent: Wednesday, December 28, 2011 1:52 PM
    To: Jiang, Lu; oracle-l@freelists.org
    Subject: RE: Block corruption again

    Check and replace hardware

    Sent from my Windows Phone
    From: Jiang, Lu
    Sent: 28/12/2011 19:46
    To: oracle-l@freelists.org
    Subject: Block corruption again
    Hi all,
    I just got a corrupt block again, had fixed a block corruption in a same index file two weeks ago. Don't know why this happened twice in such a sort period.

    I tried rman validate and dbverify to detect the corruption, but rman validate did not report any data corruption while dbverify did.

    Could anyone shed any light on this?

    Thanks,
    Lu


    The following is the output from rman validate and dbverify.

    Using backup validate:

    RMAN> backup check logical validate datafile
    'F:\ORACLE\ERPPROD\DB\APPS_ST\DATA\A_TXN_IND01.DBF';
    Starting backup at 28-DEC-11
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fnochannel ORA_DISK_1: backup set complete, elapsed
    time: 00:00:25
    Finished backup at 28-DEC-11

    Using dbverify:

    dbv FILE=F:\ORACLE\ERPPROD\DB\APPS_ST\DATA\A_TXN_IND01.DBF FEEDBACK0 Corrupt block relative dba: 0x06426321 (file 25, block 156449) Bad check value found during dbv:
    .......
    DBVERIFY - Verification complete
    Total Pages Examined : 393216
    Total Pages Processed (Data) : 2782
    Total Pages Failing (Data) : 0
    Total Pages Processed (Index): 93966
    Total Pages Failing (Index): 0
    Total Pages Processed (Other): 19301
    Total Pages Processed (Seg) : 0
    Total Pages Failing (Seg) : 0
    Total Pages Empty : 277166
    Total Pages Marked Corrupt : 1
    Total Pages Influx : 0
    Highest block SCN : 1326171528 (0.1326171528)

    Looks rman validate did not ddetect any data corruption while dbverify did.






    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l




    --
    http://www.freelists.org/webpage/oracle-l
  • CRISLER, JON A at Dec 29, 2011 at 7:58 pm
    On what Chris is saying- there is a known problem on 11gR1 where objects can get corrupted if you are in a Data Guard environment, and do a switchover and then switchback. Generally it seems to affect indexes, and it will also cause ORA-01555 errors. Rebuilding the index solves the problem until you put in a patch.
    You should also look into putting in the parameters to enforce a more strict level of block checking.

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Taylor, Chris David
    Sent: Thursday, December 29, 2011 8:31 AM
    To: 'howard.latham@gmail.com'; 'Jiang, Lu'; 'oracle-l@freelists.org'
    Subject: RE: Block corruption again

    Seems to me if this was a hardware problem, and all the datafiles are on the same LUN, then block corruptions would be occurring in more than 1 file.

    Being that this is an index datafile, I would assume that it only contains index segments so it might be worthwhile tracking down which index contains the corrupted block.

    What version of Oracle are you running, and is there anything special about this database (Dataguard configuration etc etc)?



    Chris Taylor
    Sr. Oracle DBA
    Ingram Barge Company
    Nashville, TN 37205

    "Quality is never an accident; it is always the result of intelligent effort."
    -- John Ruskin (English Writer 1819-1900)

    CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Howard Latham
    Sent: Wednesday, December 28, 2011 1:52 PM
    To: Jiang, Lu; oracle-l@freelists.org
    Subject: RE: Block corruption again

    Check and replace hardware

    Sent from my Windows Phone
    From: Jiang, Lu
    Sent: 28/12/2011 19:46
    To: oracle-l@freelists.org
    Subject: Block corruption again
    Hi all,
    I just got a corrupt block again, had fixed a block corruption in a same index file two weeks ago. Don't know why this happened twice in such a sort period.

    I tried rman validate and dbverify to detect the corruption, but rman validate did not report any data corruption while dbverify did.

    Could anyone shed any light on this?

    Thanks,
    Lu


    The following is the output from rman validate and dbverify.

    Using backup validate:

    RMAN> backup check logical validate datafile
    'F:\ORACLE\ERPPROD\DB\APPS_ST\DATA\A_TXN_IND01.DBF';
    Starting backup at 28-DEC-11
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fnochannel ORA_DISK_1: backup set complete, elapsed
    time: 00:00:25
    Finished backup at 28-DEC-11

    Using dbverify:

    dbv FILE=F:\ORACLE\ERPPROD\DB\APPS_ST\DATA\A_TXN_IND01.DBF FEEDBACK0 Corrupt block relative dba: 0x06426321 (file 25, block 156449) Bad check value found during dbv:
    .......
    DBVERIFY - Verification complete
    Total Pages Examined : 393216
    Total Pages Processed (Data) : 2782
    Total Pages Failing (Data) : 0
    Total Pages Processed (Index): 93966
    Total Pages Failing (Index): 0
    Total Pages Processed (Other): 19301
    Total Pages Processed (Seg) : 0
    Total Pages Failing (Seg) : 0
    Total Pages Empty : 277166
    Total Pages Marked Corrupt : 1
    Total Pages Influx : 0
    Highest block SCN : 1326171528 (0.1326171528)

    Looks rman validate did not ddetect any data corruption while dbverify did.






    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l




    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
  • Jiang, Lu at Dec 29, 2011 at 9:10 pm
    Thanks all for your replies.
    I had fixed this block corruption last night. Asked sysadmin to make sure no OS level activities against the data files except image backup. Now anti-Virus has been changed to not to scan on data files. I will keep an eye on this and wish the issue won't happen again. Really hope it is not a hardware issue.
    Oracle version is 10gR2 with Data Guard, there is no failover/switchover performed recently. The issue seems occurred after rebooting the database machine.


    -----Original Message-----
    From: CRISLER, JON A
    Sent: Thursday, December 29, 2011 2:58 PM
    To: ChrisDavid.Taylor@ingrambarge.com; 'howard.latham@gmail.com'; 'Jiang, Lu'; 'oracle-l@freelists.org'
    Subject: RE: Block corruption again

    On what Chris is saying- there is a known problem on 11gR1 where objects can get corrupted if you are in a Data Guard environment, and do a switchover and then switchback. Generally it seems to affect indexes, and it will also cause ORA-01555 errors. Rebuilding the index solves the problem until you put in a patch.
    You should also look into putting in the parameters to enforce a more strict level of block checking.

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Taylor, Chris David
    Sent: Thursday, December 29, 2011 8:31 AM
    To: 'howard.latham@gmail.com'; 'Jiang, Lu'; 'oracle-l@freelists.org'
    Subject: RE: Block corruption again

    Seems to me if this was a hardware problem, and all the datafiles are on the same LUN, then block corruptions would be occurring in more than 1 file.

    Being that this is an index datafile, I would assume that it only contains index segments so it might be worthwhile tracking down which index contains the corrupted block.

    What version of Oracle are you running, and is there anything special about this database (Dataguard configuration etc etc)?



    Chris Taylor
    Sr. Oracle DBA
    Ingram Barge Company
    Nashville, TN 37205

    "Quality is never an accident; it is always the result of intelligent effort."
    -- John Ruskin (English Writer 1819-1900)

    CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Howard Latham
    Sent: Wednesday, December 28, 2011 1:52 PM
    To: Jiang, Lu; oracle-l@freelists.org
    Subject: RE: Block corruption again

    Check and replace hardware

    Sent from my Windows Phone
    From: Jiang, Lu
    Sent: 28/12/2011 19:46
    To: oracle-l@freelists.org
    Subject: Block corruption again
    Hi all,
    I just got a corrupt block again, had fixed a block corruption in a same index file two weeks ago. Don't know why this happened twice in such a sort period.

    I tried rman validate and dbverify to detect the corruption, but rman validate did not report any data corruption while dbverify did.

    Could anyone shed any light on this?

    Thanks,
    Lu


    The following is the output from rman validate and dbverify.

    Using backup validate:

    RMAN> backup check logical validate datafile
    'F:\ORACLE\ERPPROD\DB\APPS_ST\DATA\A_TXN_IND01.DBF';
    Starting backup at 28-DEC-11
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fnochannel ORA_DISK_1: backup set complete, elapsed
    time: 00:00:25
    Finished backup at 28-DEC-11

    Using dbverify:

    dbv FILE=F:\ORACLE\ERPPROD\DB\APPS_ST\DATA\A_TXN_IND01.DBF FEEDBACK0 Corrupt block relative dba: 0x06426321 (file 25, block 156449) Bad check value found during dbv:
    .......
    DBVERIFY - Verification complete
    Total Pages Examined : 393216
    Total Pages Processed (Data) : 2782
    Total Pages Failing (Data) : 0
    Total Pages Processed (Index): 93966
    Total Pages Failing (Index): 0
    Total Pages Processed (Other): 19301
    Total Pages Processed (Seg) : 0
    Total Pages Failing (Seg) : 0
    Total Pages Empty : 277166
    Total Pages Marked Corrupt : 1
    Total Pages Influx : 0
    Highest block SCN : 1326171528 (0.1326171528)

    Looks rman validate did not ddetect any data corruption while dbverify did.






    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l




    --
    http://www.freelists.org/webpage/oracle-l



    --
    http://www.freelists.org/webpage/oracle-l
  • CRISLER, JON A at Dec 30, 2011 at 6:33 am
    Generally you want any anti-virus package to not perform any "on-demand" scanning of Oracle disk objects. Besides being a frequent performance bottleneck, it can cause errors as you have encountered. They can also cause some very interesting issues with Oracle RAC.

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Jiang, Lu
    Sent: Thursday, December 29, 2011 4:09 PM
    To: 'oracle-l@freelists.org'
    Subject: RE: Block corruption again

    Thanks all for your replies.
    I had fixed this block corruption last night. Asked sysadmin to make sure no OS level activities against the data files except image backup. Now anti-Virus has been changed to not to scan on data files. I will keep an eye on this and wish the issue won't happen again. Really hope it is not a hardware issue.
    Oracle version is 10gR2 with Data Guard, there is no failover/switchover performed recently. The issue seems occurred after rebooting the database machine.


    -----Original Message-----
    From: CRISLER, JON A
    Sent: Thursday, December 29, 2011 2:58 PM
    To: ChrisDavid.Taylor@ingrambarge.com; 'howard.latham@gmail.com'; 'Jiang, Lu'; 'oracle-l@freelists.org'
    Subject: RE: Block corruption again

    On what Chris is saying- there is a known problem on 11gR1 where objects can get corrupted if you are in a Data Guard environment, and do a switchover and then switchback. Generally it seems to affect indexes, and it will also cause ORA-01555 errors. Rebuilding the index solves the problem until you put in a patch.
    You should also look into putting in the parameters to enforce a more strict level of block checking.

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Taylor, Chris David
    Sent: Thursday, December 29, 2011 8:31 AM
    To: 'howard.latham@gmail.com'; 'Jiang, Lu'; 'oracle-l@freelists.org'
    Subject: RE: Block corruption again

    Seems to me if this was a hardware problem, and all the datafiles are on the same LUN, then block corruptions would be occurring in more than 1 file.

    Being that this is an index datafile, I would assume that it only contains index segments so it might be worthwhile tracking down which index contains the corrupted block.

    What version of Oracle are you running, and is there anything special about this database (Dataguard configuration etc etc)?



    Chris Taylor
    Sr. Oracle DBA
    Ingram Barge Company
    Nashville, TN 37205

    "Quality is never an accident; it is always the result of intelligent effort."
    -- John Ruskin (English Writer 1819-1900)

    CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Howard Latham
    Sent: Wednesday, December 28, 2011 1:52 PM
    To: Jiang, Lu; oracle-l@freelists.org
    Subject: RE: Block corruption again

    Check and replace hardware

    Sent from my Windows Phone
    From: Jiang, Lu
    Sent: 28/12/2011 19:46
    To: oracle-l@freelists.org
    Subject: Block corruption again
    Hi all,
    I just got a corrupt block again, had fixed a block corruption in a same index file two weeks ago. Don't know why this happened twice in such a sort period.

    I tried rman validate and dbverify to detect the corruption, but rman validate did not report any data corruption while dbverify did.

    Could anyone shed any light on this?

    Thanks,
    Lu


    The following is the output from rman validate and dbverify.

    Using backup validate:

    RMAN> backup check logical validate datafile
    'F:\ORACLE\ERPPROD\DB\APPS_ST\DATA\A_TXN_IND01.DBF';
    Starting backup at 28-DEC-11
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fnochannel ORA_DISK_1: backup set complete, elapsed
    time: 00:00:25
    Finished backup at 28-DEC-11

    Using dbverify:

    dbv FILE=F:\ORACLE\ERPPROD\DB\APPS_ST\DATA\A_TXN_IND01.DBF FEEDBACK0 Corrupt block relative dba: 0x06426321 (file 25, block 156449) Bad check value found during dbv:
    .......
    DBVERIFY - Verification complete
    Total Pages Examined : 393216
    Total Pages Processed (Data) : 2782
    Total Pages Failing (Data) : 0
    Total Pages Processed (Index): 93966
    Total Pages Failing (Index): 0
    Total Pages Processed (Other): 19301
    Total Pages Processed (Seg) : 0
    Total Pages Failing (Seg) : 0
    Total Pages Empty : 277166
    Total Pages Marked Corrupt : 1
    Total Pages Influx : 0
    Highest block SCN : 1326171528 (0.1326171528)

    Looks rman validate did not ddetect any data corruption while dbverify did.






    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l




    --
    http://www.freelists.org/webpage/oracle-l



    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
  • Jiang, Lu at Jan 3, 2012 at 4:04 pm
    Thanks Jon. Actually I had already given sysadmin a list of Oracle files which should be excluded from anti-virus scan.


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of CRISLER, JON A
    Sent: Friday, December 30, 2011 1:32 AM
    To: Lu.Jiang@umassmed.edu; 'oracle-l@freelists.org'
    Subject: RE: Block corruption again

    Generally you want any anti-virus package to not perform any "on-demand" scanning of Oracle disk objects. Besides being a frequent performance bottleneck, it can cause errors as you have encountered. They can also cause some very interesting issues with Oracle RAC.

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Jiang, Lu
    Sent: Thursday, December 29, 2011 4:09 PM
    To: 'oracle-l@freelists.org'
    Subject: RE: Block corruption again

    Thanks all for your replies.
    I had fixed this block corruption last night. Asked sysadmin to make sure no OS level activities against the data files except image backup. Now anti-Virus has been changed to not to scan on data files. I will keep an eye on this and wish the issue won't happen again. Really hope it is not a hardware issue.
    Oracle version is 10gR2 with Data Guard, there is no failover/switchover performed recently. The issue seems occurred after rebooting the database machine.


    -----Original Message-----
    From: CRISLER, JON A
    Sent: Thursday, December 29, 2011 2:58 PM
    To: ChrisDavid.Taylor@ingrambarge.com; 'howard.latham@gmail.com'; 'Jiang, Lu'; 'oracle-l@freelists.org'
    Subject: RE: Block corruption again

    On what Chris is saying- there is a known problem on 11gR1 where objects can get corrupted if you are in a Data Guard environment, and do a switchover and then switchback. Generally it seems to affect indexes, and it will also cause ORA-01555 errors. Rebuilding the index solves the problem until you put in a patch.
    You should also look into putting in the parameters to enforce a more strict level of block checking.

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Taylor, Chris David
    Sent: Thursday, December 29, 2011 8:31 AM
    To: 'howard.latham@gmail.com'; 'Jiang, Lu'; 'oracle-l@freelists.org'
    Subject: RE: Block corruption again

    Seems to me if this was a hardware problem, and all the datafiles are on the same LUN, then block corruptions would be occurring in more than 1 file.

    Being that this is an index datafile, I would assume that it only contains index segments so it might be worthwhile tracking down which index contains the corrupted block.

    What version of Oracle are you running, and is there anything special about this database (Dataguard configuration etc etc)?



    Chris Taylor
    Sr. Oracle DBA
    Ingram Barge Company
    Nashville, TN 37205

    "Quality is never an accident; it is always the result of intelligent effort."
    -- John Ruskin (English Writer 1819-1900)

    CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Howard Latham
    Sent: Wednesday, December 28, 2011 1:52 PM
    To: Jiang, Lu; oracle-l@freelists.org
    Subject: RE: Block corruption again

    Check and replace hardware

    Sent from my Windows Phone
    From: Jiang, Lu
    Sent: 28/12/2011 19:46
    To: oracle-l@freelists.org
    Subject: Block corruption again
    Hi all,
    I just got a corrupt block again, had fixed a block corruption in a same index file two weeks ago. Don't know why this happened twice in such a sort period.

    I tried rman validate and dbverify to detect the corruption, but rman validate did not report any data corruption while dbverify did.

    Could anyone shed any light on this?

    Thanks,
    Lu


    The following is the output from rman validate and dbverify.

    Using backup validate:

    RMAN> backup check logical validate datafile
    'F:\ORACLE\ERPPROD\DB\APPS_ST\DATA\A_TXN_IND01.DBF';
    Starting backup at 28-DEC-11
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fnochannel ORA_DISK_1: backup set complete, elapsed
    time: 00:00:25
    Finished backup at 28-DEC-11

    Using dbverify:

    dbv FILE=F:\ORACLE\ERPPROD\DB\APPS_ST\DATA\A_TXN_IND01.DBF FEEDBACK0 Corrupt block relative dba: 0x06426321 (file 25, block 156449) Bad check value found during dbv:
    .......
    DBVERIFY - Verification complete
    Total Pages Examined : 393216
    Total Pages Processed (Data) : 2782
    Total Pages Failing (Data) : 0
    Total Pages Processed (Index): 93966
    Total Pages Failing (Index): 0
    Total Pages Processed (Other): 19301
    Total Pages Processed (Seg) : 0
    Total Pages Failing (Seg) : 0
    Total Pages Empty : 277166
    Total Pages Marked Corrupt : 1
    Total Pages Influx : 0
    Highest block SCN : 1326171528 (0.1326171528)

    Looks rman validate did not ddetect any data corruption while dbverify did.






    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l




    --
    http://www.freelists.org/webpage/oracle-l



    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l



    --
    http://www.freelists.org/webpage/oracle-l
  • Jiang, Lu at Jan 3, 2012 at 9:04 pm
    Got this error again. Submitted a SR, hope can get an answer from Oracle.


    -----Original Message-----
    From: Jiang, Lu
    Sent: Tuesday, January 03, 2012 11:03 AM
    To: 'JC1706@att.com'; 'oracle-l@freelists.org'
    Subject: RE: Block corruption again

    Thanks Jon. Actually I had already given sysadmin a list of Oracle files which should be excluded from anti-virus scan.


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of CRISLER, JON A
    Sent: Friday, December 30, 2011 1:32 AM
    To: Lu.Jiang@umassmed.edu; 'oracle-l@freelists.org'
    Subject: RE: Block corruption again

    Generally you want any anti-virus package to not perform any "on-demand" scanning of Oracle disk objects. Besides being a frequent performance bottleneck, it can cause errors as you have encountered. They can also cause some very interesting issues with Oracle RAC.

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Jiang, Lu
    Sent: Thursday, December 29, 2011 4:09 PM
    To: 'oracle-l@freelists.org'
    Subject: RE: Block corruption again

    Thanks all for your replies.
    I had fixed this block corruption last night. Asked sysadmin to make sure no OS level activities against the data files except image backup. Now anti-Virus has been changed to not to scan on data files. I will keep an eye on this and wish the issue won't happen again. Really hope it is not a hardware issue.
    Oracle version is 10gR2 with Data Guard, there is no failover/switchover performed recently. The issue seems occurred after rebooting the database machine.


    -----Original Message-----
    From: CRISLER, JON A
    Sent: Thursday, December 29, 2011 2:58 PM
    To: ChrisDavid.Taylor@ingrambarge.com; 'howard.latham@gmail.com'; 'Jiang, Lu'; 'oracle-l@freelists.org'
    Subject: RE: Block corruption again

    On what Chris is saying- there is a known problem on 11gR1 where objects can get corrupted if you are in a Data Guard environment, and do a switchover and then switchback. Generally it seems to affect indexes, and it will also cause ORA-01555 errors. Rebuilding the index solves the problem until you put in a patch.
    You should also look into putting in the parameters to enforce a more strict level of block checking.

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Taylor, Chris David
    Sent: Thursday, December 29, 2011 8:31 AM
    To: 'howard.latham@gmail.com'; 'Jiang, Lu'; 'oracle-l@freelists.org'
    Subject: RE: Block corruption again

    Seems to me if this was a hardware problem, and all the datafiles are on the same LUN, then block corruptions would be occurring in more than 1 file.

    Being that this is an index datafile, I would assume that it only contains index segments so it might be worthwhile tracking down which index contains the corrupted block.

    What version of Oracle are you running, and is there anything special about this database (Dataguard configuration etc etc)?



    Chris Taylor
    Sr. Oracle DBA
    Ingram Barge Company
    Nashville, TN 37205

    "Quality is never an accident; it is always the result of intelligent effort."
    -- John Ruskin (English Writer 1819-1900)

    CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Howard Latham
    Sent: Wednesday, December 28, 2011 1:52 PM
    To: Jiang, Lu; oracle-l@freelists.org
    Subject: RE: Block corruption again

    Check and replace hardware

    Sent from my Windows Phone
    From: Jiang, Lu
    Sent: 28/12/2011 19:46
    To: oracle-l@freelists.org
    Subject: Block corruption again
    Hi all,
    I just got a corrupt block again, had fixed a block corruption in a same index file two weeks ago. Don't know why this happened twice in such a sort period.

    I tried rman validate and dbverify to detect the corruption, but rman validate did not report any data corruption while dbverify did.

    Could anyone shed any light on this?

    Thanks,
    Lu


    The following is the output from rman validate and dbverify.

    Using backup validate:

    RMAN> backup check logical validate datafile
    'F:\ORACLE\ERPPROD\DB\APPS_ST\DATA\A_TXN_IND01.DBF';
    Starting backup at 28-DEC-11
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fnochannel ORA_DISK_1: backup set complete, elapsed
    time: 00:00:25
    Finished backup at 28-DEC-11

    Using dbverify:

    dbv FILE=F:\ORACLE\ERPPROD\DB\APPS_ST\DATA\A_TXN_IND01.DBF FEEDBACK0 Corrupt block relative dba: 0x06426321 (file 25, block 156449) Bad check value found during dbv:
    .......
    DBVERIFY - Verification complete
    Total Pages Examined : 393216
    Total Pages Processed (Data) : 2782
    Total Pages Failing (Data) : 0
    Total Pages Processed (Index): 93966
    Total Pages Failing (Index): 0
    Total Pages Processed (Other): 19301
    Total Pages Processed (Seg) : 0
    Total Pages Failing (Seg) : 0
    Total Pages Empty : 277166
    Total Pages Marked Corrupt : 1
    Total Pages Influx : 0
    Highest block SCN : 1326171528 (0.1326171528)

    Looks rman validate did not ddetect any data corruption while dbverify did.






    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l




    --
    http://www.freelists.org/webpage/oracle-l



    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l



    --
    http://www.freelists.org/webpage/oracle-l
  • Wolfson Larry - lwolfs at Jan 3, 2012 at 11:56 pm
    Happy New Year!

    Oracle 11.2.0.2 on Linux RH
    Anyone seen this before and knows what is causing it?


    *** 2012-01-03 20:13:03.102
    dbkripxicx_cb: Error encountered: rc=0
    dbkripxicx_cb: Error encountered: rc=0
    dbkripxicx_cb: Error encountered: rc=0

    I just got these messages in 4 different trc files.
    Didn't see anything on ML.

    TIA
    Larry

    ***************************************************************************
    The information contained in this communication is confidential, is
    intended only for the use of the recipient named above, and may be legally
    privileged.

    If the reader of this message is not the intended recipient, you are
    hereby notified that any dissemination, distribution or copying of this
    communication is strictly prohibited.

    If you have received this communication in error, please resend this
    communication to the sender and delete the original message or any copy
    of it from your computer system.

    Thank You.
    ****************************************************************************
  • CRISLER, JON A at Jan 4, 2012 at 5:56 pm
    By any chance would this be an SAP install? The reason I ask is that I have encountered numerous cases where a fresh install of SAP and Oracle 10g or Oracle 11g encounters corrupted db blocks- we fix them only for them to pop up someplace else. This happens at the tail end of the install !! The basic cause is that there is something goofy in the specific build that SAP ships with its product: I can take the same basic version (say 11.1.0.7), install it on the same server, and never have a problem. Its almost like the seed database provided with SAP has built in corrupted blocks. And we got nowhere with SAP support- they just regurgitate technotes about looking for bad hardware etc.

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Jiang, Lu
    Sent: Tuesday, January 03, 2012 4:03 PM
    To: 'oracle-l@freelists.org'
    Subject: RE: Block corruption again

    Got this error again. Submitted a SR, hope can get an answer from Oracle.


    -----Original Message-----
    From: Jiang, Lu
    Sent: Tuesday, January 03, 2012 11:03 AM
    To: 'JC1706@att.com'; 'oracle-l@freelists.org'
    Subject: RE: Block corruption again

    Thanks Jon. Actually I had already given sysadmin a list of Oracle files which should be excluded from anti-virus scan.


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of CRISLER, JON A
    Sent: Friday, December 30, 2011 1:32 AM
    To: Lu.Jiang@umassmed.edu; 'oracle-l@freelists.org'
    Subject: RE: Block corruption again

    Generally you want any anti-virus package to not perform any "on-demand" scanning of Oracle disk objects. Besides being a frequent performance bottleneck, it can cause errors as you have encountered. They can also cause some very interesting issues with Oracle RAC.

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Jiang, Lu
    Sent: Thursday, December 29, 2011 4:09 PM
    To: 'oracle-l@freelists.org'
    Subject: RE: Block corruption again

    Thanks all for your replies.
    I had fixed this block corruption last night. Asked sysadmin to make sure no OS level activities against the data files except image backup. Now anti-Virus has been changed to not to scan on data files. I will keep an eye on this and wish the issue won't happen again. Really hope it is not a hardware issue.
    Oracle version is 10gR2 with Data Guard, there is no failover/switchover performed recently. The issue seems occurred after rebooting the database machine.


    -----Original Message-----
    From: CRISLER, JON A
    Sent: Thursday, December 29, 2011 2:58 PM
    To: ChrisDavid.Taylor@ingrambarge.com; 'howard.latham@gmail.com'; 'Jiang, Lu'; 'oracle-l@freelists.org'
    Subject: RE: Block corruption again

    On what Chris is saying- there is a known problem on 11gR1 where objects can get corrupted if you are in a Data Guard environment, and do a switchover and then switchback. Generally it seems to affect indexes, and it will also cause ORA-01555 errors. Rebuilding the index solves the problem until you put in a patch.
    You should also look into putting in the parameters to enforce a more strict level of block checking.

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Taylor, Chris David
    Sent: Thursday, December 29, 2011 8:31 AM
    To: 'howard.latham@gmail.com'; 'Jiang, Lu'; 'oracle-l@freelists.org'
    Subject: RE: Block corruption again

    Seems to me if this was a hardware problem, and all the datafiles are on the same LUN, then block corruptions would be occurring in more than 1 file.

    Being that this is an index datafile, I would assume that it only contains index segments so it might be worthwhile tracking down which index contains the corrupted block.

    What version of Oracle are you running, and is there anything special about this database (Dataguard configuration etc etc)?



    Chris Taylor
    Sr. Oracle DBA
    Ingram Barge Company
    Nashville, TN 37205

    "Quality is never an accident; it is always the result of intelligent effort."
    -- John Ruskin (English Writer 1819-1900)

    CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Howard Latham
    Sent: Wednesday, December 28, 2011 1:52 PM
    To: Jiang, Lu; oracle-l@freelists.org
    Subject: RE: Block corruption again

    Check and replace hardware

    Sent from my Windows Phone
    From: Jiang, Lu
    Sent: 28/12/2011 19:46
    To: oracle-l@freelists.org
    Subject: Block corruption again
    Hi all,
    I just got a corrupt block again, had fixed a block corruption in a same index file two weeks ago. Don't know why this happened twice in such a sort period.

    I tried rman validate and dbverify to detect the corruption, but rman validate did not report any data corruption while dbverify did.

    Could anyone shed any light on this?

    Thanks,
    Lu


    The following is the output from rman validate and dbverify.

    Using backup validate:

    RMAN> backup check logical validate datafile
    'F:\ORACLE\ERPPROD\DB\APPS_ST\DATA\A_TXN_IND01.DBF';
    Starting backup at 28-DEC-11
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fnochannel ORA_DISK_1: backup set complete, elapsed
    time: 00:00:25
    Finished backup at 28-DEC-11

    Using dbverify:

    dbv FILE=F:\ORACLE\ERPPROD\DB\APPS_ST\DATA\A_TXN_IND01.DBF FEEDBACK0 Corrupt block relative dba: 0x06426321 (file 25, block 156449) Bad check value found during dbv:
    .......
    DBVERIFY - Verification complete
    Total Pages Examined : 393216
    Total Pages Processed (Data) : 2782
    Total Pages Failing (Data) : 0
    Total Pages Processed (Index): 93966
    Total Pages Failing (Index): 0
    Total Pages Processed (Other): 19301
    Total Pages Processed (Seg) : 0
    Total Pages Failing (Seg) : 0
    Total Pages Empty : 277166
    Total Pages Marked Corrupt : 1
    Total Pages Influx : 0
    Highest block SCN : 1326171528 (0.1326171528)

    Looks rman validate did not ddetect any data corruption while dbverify did.






    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l




    --
    http://www.freelists.org/webpage/oracle-l



    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l



    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
  • Jiang, Lu at Jan 4, 2012 at 7:18 pm
    Thanks Jon for the info, this is good to know.
    It is an Oracle E-Business Suite database on Windows. According to sysadmins, there had been no hardware issue found or reported before or around the time block corruption was occurred. The good news is that they got a failed drive alert after a routine reboot last night. It is one of the drives host the data file which had corrupt blocks. Hope it would be the end of the story after the drive is replaced.
    Is there any good hardware diagnosis tool that you could recommend? It seems OEM does not help much on this for Windows environment.


    -----Original Message-----
    From: CRISLER, JON A
    Sent: Wednesday, January 04, 2012 12:55 PM
    To: Lu.Jiang@umassmed.edu; 'oracle-l@freelists.org'
    Subject: RE: Block corruption again

    By any chance would this be an SAP install? The reason I ask is that I have encountered numerous cases where a fresh install of SAP and Oracle 10g or Oracle 11g encounters corrupted db blocks- we fix them only for them to pop up someplace else. This happens at the tail end of the install !! The basic cause is that there is something goofy in the specific build that SAP ships with its product: I can take the same basic version (say 11.1.0.7), install it on the same server, and never have a problem. Its almost like the seed database provided with SAP has built in corrupted blocks. And we got nowhere with SAP support- they just regurgitate technotes about looking for bad hardware etc.

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Jiang, Lu
    Sent: Tuesday, January 03, 2012 4:03 PM
    To: 'oracle-l@freelists.org'
    Subject: RE: Block corruption again

    Got this error again. Submitted a SR, hope can get an answer from Oracle.


    -----Original Message-----
    From: Jiang, Lu
    Sent: Tuesday, January 03, 2012 11:03 AM
    To: 'JC1706@att.com'; 'oracle-l@freelists.org'
    Subject: RE: Block corruption again

    Thanks Jon. Actually I had already given sysadmin a list of Oracle files which should be excluded from anti-virus scan.


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of CRISLER, JON A
    Sent: Friday, December 30, 2011 1:32 AM
    To: Lu.Jiang@umassmed.edu; 'oracle-l@freelists.org'
    Subject: RE: Block corruption again

    Generally you want any anti-virus package to not perform any "on-demand" scanning of Oracle disk objects. Besides being a frequent performance bottleneck, it can cause errors as you have encountered. They can also cause some very interesting issues with Oracle RAC.

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Jiang, Lu
    Sent: Thursday, December 29, 2011 4:09 PM
    To: 'oracle-l@freelists.org'
    Subject: RE: Block corruption again

    Thanks all for your replies.
    I had fixed this block corruption last night. Asked sysadmin to make sure no OS level activities against the data files except image backup. Now anti-Virus has been changed to not to scan on data files. I will keep an eye on this and wish the issue won't happen again. Really hope it is not a hardware issue.
    Oracle version is 10gR2 with Data Guard, there is no failover/switchover performed recently. The issue seems occurred after rebooting the database machine.


    -----Original Message-----
    From: CRISLER, JON A
    Sent: Thursday, December 29, 2011 2:58 PM
    To: ChrisDavid.Taylor@ingrambarge.com; 'howard.latham@gmail.com'; 'Jiang, Lu'; 'oracle-l@freelists.org'
    Subject: RE: Block corruption again

    On what Chris is saying- there is a known problem on 11gR1 where objects can get corrupted if you are in a Data Guard environment, and do a switchover and then switchback. Generally it seems to affect indexes, and it will also cause ORA-01555 errors. Rebuilding the index solves the problem until you put in a patch.
    You should also look into putting in the parameters to enforce a more strict level of block checking.

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Taylor, Chris David
    Sent: Thursday, December 29, 2011 8:31 AM
    To: 'howard.latham@gmail.com'; 'Jiang, Lu'; 'oracle-l@freelists.org'
    Subject: RE: Block corruption again

    Seems to me if this was a hardware problem, and all the datafiles are on the same LUN, then block corruptions would be occurring in more than 1 file.

    Being that this is an index datafile, I would assume that it only contains index segments so it might be worthwhile tracking down which index contains the corrupted block.

    What version of Oracle are you running, and is there anything special about this database (Dataguard configuration etc etc)?



    Chris Taylor
    Sr. Oracle DBA
    Ingram Barge Company
    Nashville, TN 37205

    "Quality is never an accident; it is always the result of intelligent effort."
    -- John Ruskin (English Writer 1819-1900)

    CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Howard Latham
    Sent: Wednesday, December 28, 2011 1:52 PM
    To: Jiang, Lu; oracle-l@freelists.org
    Subject: RE: Block corruption again

    Check and replace hardware

    Sent from my Windows Phone
    From: Jiang, Lu
    Sent: 28/12/2011 19:46
    To: oracle-l@freelists.org
    Subject: Block corruption again
    Hi all,
    I just got a corrupt block again, had fixed a block corruption in a same index file two weeks ago. Don't know why this happened twice in such a sort period.

    I tried rman validate and dbverify to detect the corruption, but rman validate did not report any data corruption while dbverify did.

    Could anyone shed any light on this?

    Thanks,
    Lu


    The following is the output from rman validate and dbverify.

    Using backup validate:

    RMAN> backup check logical validate datafile
    'F:\ORACLE\ERPPROD\DB\APPS_ST\DATA\A_TXN_IND01.DBF';
    Starting backup at 28-DEC-11
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fnochannel ORA_DISK_1: backup set complete, elapsed
    time: 00:00:25
    Finished backup at 28-DEC-11

    Using dbverify:

    dbv FILE=F:\ORACLE\ERPPROD\DB\APPS_ST\DATA\A_TXN_IND01.DBF FEEDBACK0 Corrupt block relative dba: 0x06426321 (file 25, block 156449) Bad check value found during dbv:
    .......
    DBVERIFY - Verification complete
    Total Pages Examined : 393216
    Total Pages Processed (Data) : 2782
    Total Pages Failing (Data) : 0
    Total Pages Processed (Index): 93966
    Total Pages Failing (Index): 0
    Total Pages Processed (Other): 19301
    Total Pages Processed (Seg) : 0
    Total Pages Failing (Seg) : 0
    Total Pages Empty : 277166
    Total Pages Marked Corrupt : 1
    Total Pages Influx : 0
    Highest block SCN : 1326171528 (0.1326171528)

    Looks rman validate did not ddetect any data corruption while dbverify did.






    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l




    --
    http://www.freelists.org/webpage/oracle-l



    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l



    --
    http://www.freelists.org/webpage/oracle-l



    --
    http://www.freelists.org/webpage/oracle-l
  • Wolfson Larry - lwolfs at Jan 5, 2012 at 6:21 am
    Happy New Year!
    I've got a lot of 11.2.0.2 databases and was wondering what controls how many days are retained in the internal alert log
    X$DBGALERTEXT

    Servers may be
    AIX, DELL, HP, SOLARIS

    When I run the code below

    COL COUNT(*) FOR 99,999,999 HEA 'R O W S'
    COL DDAY FOR A10 HEA 'D A Y S'
    SELECT DISTINCT TO_CHAR (ORIGINATING_TIMESTAMP,'YYYY-MM-DD') DDAY
    ,COUNT(*)
    FROM X$DBGALERTEXT
    GROUP BY TO_CHAR (ORIGINATING_TIMESTAMP,'YYYY-MM-DD')
    ORDER BY TO_CHAR (ORIGINATING_TIMESTAMP,'YYYY-MM-DD')
    ;

    Some DBs have millions of rows back to when they were created
    Some have a few days back.
    Others only return the current day

    Now one isn't showing anything for 2012 although I see the data in the trace directory alert log
    So that may be a bug.
    I have a another DB on the same server, Dell Red Hat Enterprise Linux Server release 5.5 (Tikanga),
    which does show both 2011 and 2012.

    And another DB on another DELL server with same OS
    D A Y S R O W S
    ---------- -----------
    2011-09-22 488
    2011-09-23 18
    2011-09-24 9
    2011-09-25 12
    2011-09-26 548
    2011-09-27 12
    2011-09-28 9
    2011-09-29 9
    2011-09-30 2,590
    2011-10-01 16
    2011-10-02 9
    2011-10-03 5,060
    2011-10-04 18,743,441
    2011-10-05 1,032
    2011-10-06 2,538
    2011-10-07 2,588
    Skipped a lot
    2011-12-22 1,395
    2011-12-23 13
    2011-12-26 968
    2011-12-27 186
    2011-12-28 32
    2011-12-29 9
    2011-12-30 9
    2012-01-02 2,936 Works fine
    2012-01-03 5,465
    2012-01-04 9,130
    2012-01-05 3
    102 rows selected.

    While we're usually only interested in the last couple of days, it would be nice to go back as far as we'd want to.

    TIA
    Larry

    ***************************************************************************
    The information contained in this communication is confidential, is
    intended only for the use of the recipient named above, and may be legally
    privileged.

    If the reader of this message is not the intended recipient, you are
    hereby notified that any dissemination, distribution or copying of this
    communication is strictly prohibited.

    If you have received this communication in error, please resend this
    communication to the sender and delete the original message or any copy
    of it from your computer system.

    Thank You.
    ****************************************************************************
  • Subodh Deshpande at Jan 5, 2012 at 10:20 am
    Hello,
    happy new year to you !

    yes, i am not able to understand the purpose of the query itself..alert log
    can have alot of alert messages some times with reason sometimes without
    reason (without reason eg fire any successful ddl ..)

    I think the following link can be of more use related to the view you have
    said..

    http://www.dba-oracle.com/t_11g_dbgalertext_alert_log_sql.htm

    Thanks and take care..subodh

    On 5 January 2012 11:50, Wolfson Larry - lwolfs wrote:

    Happy New Year!
    I've got a lot of 11.2.0.2 databases and was wondering what controls how
    many days are retained in the internal alert log
    X$DBGALERTEXT

    Servers may be
    AIX, DELL, HP, SOLARIS

    When I run the code below

    COL COUNT(*) FOR 99,999,999 HEA 'R O W S'
    COL DDAY FOR A10 HEA 'D A Y S'
    SELECT DISTINCT TO_CHAR (ORIGINATING_TIMESTAMP,'YYYY-MM-DD') DDAY
    ,COUNT(*)
    FROM X$DBGALERTEXT
    GROUP BY TO_CHAR (ORIGINATING_TIMESTAMP,'YYYY-MM-DD')
    ORDER BY TO_CHAR (ORIGINATING_TIMESTAMP,'YYYY-MM-DD')
    ;

    Some DBs have millions of rows back to when they were created
    Some have a few days back.
    Others only return the current day

    Now one isn't showing anything for 2012 although I see the data in the
    trace directory alert log
    So that may be a bug.
    I have a another DB on the same server, Dell Red Hat Enterprise Linux
    Server release 5.5 (Tikanga),
    which does show both 2011 and 2012.

    And another DB on another DELL server with same OS
    D A Y S R O W S
    ---------- -----------
    2011-09-22 488
    2011-09-23 18
    2011-09-24 9
    2011-09-25 12
    2011-09-26 548
    2011-09-27 12
    2011-09-28 9
    2011-09-29 9
    2011-09-30 2,590
    2011-10-01 16
    2011-10-02 9
    2011-10-03 5,060
    2011-10-04 18,743,441
    2011-10-05 1,032
    2011-10-06 2,538
    2011-10-07 2,588
    Skipped a lot
    2011-12-22 1,395
    2011-12-23 13
    2011-12-26 968
    2011-12-27 186
    2011-12-28 32
    2011-12-29 9
    2011-12-30 9
    2012-01-02 2,936 Works fine
    2012-01-03 5,465
    2012-01-04 9,130
    2012-01-05 3
    102 rows selected.

    While we're usually only interested in the last couple of days, it would
    be nice to go back as far as we'd want to.

    TIA
    Larry

    ***************************************************************************
    The information contained in this communication is confidential, is
    intended only for the use of the recipient named above, and may be legally
    privileged.

    If the reader of this message is not the intended recipient, you are
    hereby notified that any dissemination, distribution or copying of this
    communication is strictly prohibited.

    If you have received this communication in error, please resend this
    communication to the sender and delete the original message or any copy
    of it from your computer system.

    Thank You.

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

    --
    http://www.freelists.org/webpage/oracle-l


    --
    =============================================
    Love me or Hate me both are in my Favour.
    Love me, I am in your Heart. Hate me, I am in your Mind.
    =============================================


    --
    http://www.freelists.org/webpage/oracle-l
  • Niall Litchfield at Jan 5, 2012 at 12:08 pm
    Larry
    An excellent question. The table appears to essentially be some sort of
    external table built on the log.xml file in the
    DIAG_DEST\<db_name>\<instance_name>\alert directory (i.e not on the trace
    file version). As such the contents *ought* to be controlled by the purge
    policies for the ADR_HOME and or any manual purging you have in place.
    However in my quick tests in 11.2.0.3 on windows this doesn't seem to be
    the case.

    I'd be really grateful as well if there was a clear definitive reference
    for what is subject to the short purge policy and what to the long purge
    policy - incidents are defined in the docs but the other diag_dest contents
    don't seem to be there.

    Niall
    On Thu, Jan 5, 2012 at 6:20 AM, Wolfson Larry - lwolfs wrote:

    Happy New Year!
    I've got a lot of 11.2.0.2 databases and was wondering what controls how
    many days are retained in the internal alert log
    X$DBGALERTEXT

    Servers may be
    AIX, DELL, HP, SOLARIS

    When I run the code below

    COL COUNT(*) FOR 99,999,999 HEA 'R O W S'
    COL DDAY FOR A10 HEA 'D A Y S'
    SELECT DISTINCT TO_CHAR (ORIGINATING_TIMESTAMP,'YYYY-MM-DD') DDAY
    ,COUNT(*)
    FROM X$DBGALERTEXT
    GROUP BY TO_CHAR (ORIGINATING_TIMESTAMP,'YYYY-MM-DD')
    ORDER BY TO_CHAR (ORIGINATING_TIMESTAMP,'YYYY-MM-DD')
    ;

    Some DBs have millions of rows back to when they were created
    Some have a few days back.
    Others only return the current day

    Now one isn't showing anything for 2012 although I see the data in the
    trace directory alert log
    So that may be a bug.
    I have a another DB on the same server, Dell Red Hat Enterprise Linux
    Server release 5.5 (Tikanga),
    which does show both 2011 and 2012.

    And another DB on another DELL server with same OS
    D A Y S R O W S
    ---------- -----------
    2011-09-22 488
    2011-09-23 18
    2011-09-24 9
    2011-09-25 12
    2011-09-26 548
    2011-09-27 12
    2011-09-28 9
    2011-09-29 9
    2011-09-30 2,590
    2011-10-01 16
    2011-10-02 9
    2011-10-03 5,060
    2011-10-04 18,743,441
    2011-10-05 1,032
    2011-10-06 2,538
    2011-10-07 2,588
    Skipped a lot
    2011-12-22 1,395
    2011-12-23 13
    2011-12-26 968
    2011-12-27 186
    2011-12-28 32
    2011-12-29 9
    2011-12-30 9
    2012-01-02 2,936 Works fine
    2012-01-03 5,465
    2012-01-04 9,130
    2012-01-05 3
    102 rows selected.

    While we're usually only interested in the last couple of days, it would
    be nice to go back as far as we'd want to.

    TIA
    Larry

    ***************************************************************************
    The information contained in this communication is confidential, is
    intended only for the use of the recipient named above, and may be legally
    privileged.

    If the reader of this message is not the intended recipient, you are
    hereby notified that any dissemination, distribution or copying of this
    communication is strictly prohibited.

    If you have received this communication in error, please resend this
    communication to the sender and delete the original message or any copy
    of it from your computer system.

    Thank You.

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

    --
    http://www.freelists.org/webpage/oracle-l


    --
    Niall Litchfield
    Oracle DBA
    http://www.orawin.info


    --
    http://www.freelists.org/webpage/oracle-l
  • Andy Klock at Jan 5, 2012 at 3:10 pm
    That's exactly what it is. If you remove those "log(_nn).xml" files with
    adrci purge or the old fashioned way (rm), querying X$DBGALERTEXT will
    only return rows for what is available. The XML version of the alert logs
    are automatically purged based on the LONG_POLICY (which is 365 days by
    default). The current purge policy settings for long and short term can be
    seen (and set) with adrci (show control, set control).
    As for, "> Skipped a lot" , Oracle automatically rotates the alert logs
    when they reach 10MB. I've never found a way to set this to something other
    than 10MB. I've done some testing back in the day and noticed that I could
    create gaps in the log sequence and this does confuse ADRCI and
    X$DBGALERTEXT. If you look in the
    DIAG_DEST\<db_name>\<instance_name>\alert directory look to see that you
    don't have any gaps in the log sequence.

    For determining what is short and what is long it is described here:

    *Which Files Are Part Of SHORTP_POLICY And LONGP_POLICY In ADR? [ID
    975448.1]*

    Andy
    On Thu, Jan 5, 2012 at 7:07 AM, Niall Litchfield wrote:

    Larry
    An excellent question. The table appears to essentially be some sort of
    external table built on the log.xml file in the
    DIAG_DEST\<db_name>\<instance_name>\alert directory (i.e not on the trace
    file version). As such the contents *ought* to be controlled by the purge
    policies for the ADR_HOME and or any manual purging you have in place.
    However in my quick tests in 11.2.0.3 on windows this doesn't seem to be
    the case.

    I'd be really grateful as well if there was a clear definitive reference
    for what is subject to the short purge policy and what to the long purge
    policy - incidents are defined in the docs but the other diag_dest contents
    don't seem to be there.

    Niall

    --
    http://www.freelists.org/webpage/oracle-l
  • Niall Litchfield at Jan 5, 2012 at 3:50 pm
    Thanks Andy - I'm sure that doc didn't exist when I was first looking at
    ADR in the 11.1 early days, and why the policy names have that extra P is
    beyond me..
    On Thu, Jan 5, 2012 at 3:08 PM, Andy Klock wrote:

    For determining what is short and what is long it is described here:

    *Which Files Are Part Of SHORTP_POLICY And LONGP_POLICY In ADR? [ID
    975448.1]*
    Niall Litchfield
    Oracle DBA
    http://www.orawin.info
  • Wolfson Larry - lwolfs at Jan 5, 2012 at 4:05 pm
    Thanks Andy, Niall.
    Sorry If I wasn't clear. On the Skipped a lot I just didn't send every day.
    On the bottom you saw 102 rows selected.

    But I do remember about the 10MB rotation you mentioned.

    I'll check the note and Controls.
    From: andyklock@gmail.com On Behalf Of Andy Klock
    Sent: Thursday, January 05, 2012 9:09 AM
    To: niall.litchfield@gmail.com
    Cc: Wolfson Larry - lwolfs; oracle-l@freelists.org
    Subject: Re: X$DBGALERTEXT

    That's exactly what it is. If you remove those "log(_nn).xml" files with adrci purge or the old fashioned way (rm), querying X$DBGALERTEXT will only return rows for what is available. The XML version of the alert logs are automatically purged based on the LONG_POLICY (which is 365 days by default). The current purge policy settings for long and short term can be seen (and set) with adrci (show control, set control).

    As for, "> Skipped a lot" , Oracle automatically rotates the alert logs when they reach 10MB. I've never found a way to set this to something other than 10MB. I've done some testing back in the day and noticed that I could create gaps in the log sequence and this does confuse ADRCI and X$DBGALERTEXT. If you look in the DIAG_DEST\<db_name>\<instance_name>\alert directory look to see that you don't have any gaps in the log sequence.

    For determining what is short and what is long it is described here:

    Which Files Are Part Of SHORTP_POLICY And LONGP_POLICY In ADR? [ID 975448.1]

    Andy
    On Thu, Jan 5, 2012 at 7:07 AM, Niall Litchfield wrote:
    Larry
    An excellent question. The table appears to essentially be some sort of
    external table built on the log.xml file in the
    DIAG_DEST\<db_name>\<instance_name>\alert directory (i.e not on the trace
    file version). As such the contents *ought* to be controlled by the purge
    policies for the ADR_HOME and or any manual purging you have in place.
    However in my quick tests in 11.2.0.3 on windows this doesn't seem to be
    the case.

    I'd be really grateful as well if there was a clear definitive reference
    for what is subject to the short purge policy and what to the long purge
    policy - incidents are defined in the docs but the other diag_dest contents
    don't seem to be there.

    Niall
    ***************************************************************************
    The information contained in this communication is confidential, is
    intended only for the use of the recipient named above, and may be legally
    privileged.

    If the reader of this message is not the intended recipient, you are
    hereby notified that any dissemination, distribution or copying of this
    communication is strictly prohibited.

    If you have received this communication in error, please resend this
    communication to the sender and delete the original message or any copy
    of it from your computer system.

    Thank You.
    ****************************************************************************
  • Andy Klock at Jan 5, 2012 at 4:10 pm
    On Thu, Jan 5, 2012 at 10:49 AM, Niall Litchfield wrote:
    Thanks Andy - I'm sure that doc didn't exist when I was first looking at
    ADR in the 11.1 early days, and why the policy names have that extra P is
    beyond me..
    I think you are right. I did a presentation about the XML version of the
    alert log (and this X table) and remember having to give a best guess at
    what I thought was "LONGP" and "SHORTP". During the talk I let the "P" be
    silent, otherwise I sounded like Porky Pig.

    On Thu, Jan 5, 2012 at 11:04 AM, Wolfson Larry - lwolfs wrote:

    Thanks Andy, Niall.



    Sorry If I wasn�t clear. On the Skipped a lot I just didn�t send every day.

    On the bottom you saw 102 rows selected.

    Ah, I missed that. Still, you mentioned that you have DBs that are missing
    data when querying X$DBGALERTEXT. That is still a puzzle to me.
  • Don Seiler at Jan 17, 2012 at 3:28 pm
    I believe the P stands for PURGE. ie long purge policy & short purge policy.
    Don.
    On Thu, Jan 5, 2012 at 10:09 AM, Andy Klock wrote:

    On Thu, Jan 5, 2012 at 10:49 AM, Niall Litchfield <
    niall.litchfield@gmail.com> wrote:
    Thanks Andy - I'm sure that doc didn't exist when I was first looking at
    ADR in the 11.1 early days, and why the policy names have that extra P is
    beyond me..
    I think you are right. I did a presentation about the XML version of the
    alert log (and this X table) and remember having to give a best guess at
    what I thought was "LONGP" and "SHORTP". During the talk I let the "P" be
    silent, otherwise I sounded like Porky Pig.

    On Thu, Jan 5, 2012 at 11:04 AM, Wolfson Larry - lwolfs <
    lawrence.wolfson@acxiom.com> wrote:

    Thanks Andy, Niall.



    Sorry If I wasn�t clear. On the Skipped a lot I just didn�t send every
    day.

    On the bottom you saw 102 rows selected.

    Ah, I missed that. Still, you mentioned that you have DBs that are missing
    data when querying X$DBGALERTEXT. That is still a puzzle to me.

    --
    http://www.freelists.org/webpage/oracle-l


    --
    Don Seiler
    http://www.seiler.us

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedDec 28, '11 at 7:45p
activeJan 17, '12 at 3:28p
posts20
users10
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase