Home » RDBMS Server » Backup & Recovery » Corrupt control file? (Oracle 9i, AIX)
Corrupt control file? [message #534011] Fri, 02 December 2011 02:55 Go to next message
Rodge
Messages: 6
Registered: December 2011
Junior Member
Hi all,

I've recently moved jobs and am doing a lot of the normal things getting familiar with my new company's databases.
I was checking through our production backup logs and came across something that looks a little funny. From the last two days backups, I see:

select completion_time, marked_corrupt, media_corrupt, logically_corrupt 
from v$backup_datafile
where controlfile_type = 'B'
order by completion_time desc


COMPLETION_TIME	        MARKED_CORRUPT MEDIA_CORRUPT LOGICALLY_CORRUPT
01/12/2011 11:27:25	2011	       1201	     2
01/12/2011 11:26:18	0	       0	     0
01/12/2011 11:25:24	2011	       1201	     1
01/12/2011 11:15:56	2011	       1201	     0
01/12/2011 01:40:10	0	       0	     0
30/11/2011 11:23:48	2011	       1130	     2
30/11/2011 11:22:44	0	       0	     0
30/11/2011 11:21:56	2011	       1130	     1
30/11/2011 11:13:00	2011	       1130	     0
30/11/2011 01:41:01	0	       0	     0


The 01:40 record will come from our nightly command:

BACKUP INCREMENTAL LEVEL=0 FILESPERSET=20 FORMAT 'full_%d_%t_%s_%p' 
DATABASE INCLUDE CURRENT CONTROLFILE


The 11AM records will come from the command:

BACKUP INCREMENTAL LEVEL=0 FORMAT 'ctl_%t_%s_%p' CURRENT CONTROLFILE;


(I know backups shouldn't still be running at 11am and we don't need level 0 backups every night, that's a seperate issue I'll be addressing! Smile )
I see there's been a similar entry here in v$backup_datafile for all backups that there's records for.
The database is up and running just fine.
There are no unusual errors being reported by the database in OEM or the alert log.
I tried looking at both our control files (yes, should have 3 - something else I've listed to change!) with dbverify which showed no issues - though I'm not sure if this is designed to work with control files.

I'm just wondering whether I need to be concerned here?
If so, how could I confirm whether it's an issue with a specific controlfile (I'm assuming it's not an issue with the backup to tape itself, as it's reporting the same corruption every day)?
I'd obviously like to diagnose this now while the db is up and running, rather than at 4am some morning during a crash and finding out my backup control files are no good!

Thanks in advance for any help or opinions!

[Updated on: Fri, 02 December 2011 03:07] by Moderator

Report message to a moderator

Re: Corrupt control file? [message #534014 is a reply to message #534011] Fri, 02 December 2011 03:10 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use dbverify on the file(s) the corruptions are marked.

Regards
Michel
Re: Corrupt control file? [message #534018 is a reply to message #534014] Fri, 02 December 2011 03:18 Go to previous messageGo to next message
Rodge
Messages: 6
Registered: December 2011
Junior Member
DBV against the control files on the database doesn't seem to show any issues:

bash-2.05a$ dbv file=control01.ctl blocksize=8192

DBVERIFY: Release 9.2.0.8.0 - Production on Fri Dec 2 09:15:18 2011

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

DBVERIFY - Verification starting : FILE = control01.ctl


DBVERIFY - Verification complete

Total Pages Examined         : 564
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 562
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 2
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 281470692519508 (65535.10776148)
bash-2.05a$


bash-2.05a$ dbv file=control02.ctl blocksize=8192

DBVERIFY: Release 9.2.0.8.0 - Production on Fri Dec 2 09:15:59 2011

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

DBVERIFY - Verification starting : FILE = control02.ctl


DBVERIFY - Verification complete

Total Pages Examined         : 564
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 559
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 5
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 281470692519516 (65535.10776156)
bash-2.05a$
Re: Corrupt control file? [message #534019 is a reply to message #534018] Fri, 02 December 2011 03:50 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
NOT on the control file, on the file that is reported as containing corruptions.

Regards
Michel
Re: Corrupt control file? [message #534021 is a reply to message #534019] Fri, 02 December 2011 04:15 Go to previous messageGo to next message
Rodge
Messages: 6
Registered: December 2011
Junior Member
Oh, the physically backed up file.

Yesterday's tape is offsite.
I've asked operations to leave the tape onsite when the backup finishes today, so I'll check that when the backup completes.
Re: Corrupt control file? [message #534022 is a reply to message #534021] Fri, 02 December 2011 04:20 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, the database file, the one you find in FILE# column of v$backup_datafile (and you didn't fetch).

Regards
Michel
Re: Corrupt control file? [message #534028 is a reply to message #534022] Fri, 02 December 2011 04:43 Go to previous messageGo to next message
Rodge
Messages: 6
Registered: December 2011
Junior Member
select file#, completion_time, marked_corrupt, media_corrupt, logically_corrupt 
from v$backup_datafile
where controlfile_type = 'B'
order by completion_time desc


FILE#	COMPLETION_TIME     MARKED_CORRUPT MEDIA_CORRUPT LOGICALLY_CORRUPT

0	01/12/2011 11:27:25 2011           1201          2
0	01/12/2011 11:26:18 0              0             0
0	01/12/2011 11:25:24 2011           1201          1
0	01/12/2011 11:15:56 2011           1201          0
0	01/12/2011 01:40:10 0              0             0
0	30/11/2011 11:23:48 2011           1130          2
0	30/11/2011 11:22:44 0              0             0
0	30/11/2011 11:21:56 2011           1130          1
0	30/11/2011 11:13:00 2011           1130          0
0	30/11/2011 01:41:01 0              0             0

Sorry for misunderstanding what you were asking for.
File# = 0 doesn't link to anything in DBA_DATA_FILES.
Since the file# was zero, I was searching originally where controlfile_type = 'B'.
This is what lead me to believe the issue was with one of my controlfiles, but seeing no issue there on the server, I haven't been sure what I need to check next
Re: Corrupt control file? [message #534029 is a reply to message #534028] Fri, 02 December 2011 04:47 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So FILE_TYPE is also useful...

Regards
Michel
Re: Corrupt control file? [message #534031 is a reply to message #534029] Fri, 02 December 2011 05:31 Go to previous messageGo to next message
Rodge
Messages: 6
Registered: December 2011
Junior Member
Including controlfile_type column, also showing a few previous files from backup. There is no other lines for the backup with any corruption marked, only these first lines.

select file#, completion_time, marked_corrupt, media_corrupt, logically_corrupt, controlfile_type
from v$backup_datafile
order by completion_time desc

FILE# COMPLETION_TIME     MARKED_CORRUPT MEDIA_CORRUPT LOGICALLY_CORRUPT CONTROLFILE_TYPE

0     01/12/2011 11:27:25 2011           1201          2                 B
0     01/12/2011 11:26:18 0              0             0                 B
0     01/12/2011 11:25:24 2011           1201          1                 B
0     01/12/2011 11:15:56 2011           1201          0                 B
171   01/12/2011 11:14:59 0              0             0 
16    01/12/2011 11:14:59 0              0             0 
43    01/12/2011 11:14:59 0              0             0 
71    01/12/2011 11:14:59 0              0             0 
Re: Corrupt control file? [message #534043 is a reply to message #534031] Fri, 02 December 2011 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your Oracle version (4 decimals)?

Regards
Michel
Re: Corrupt control file? [message #534072 is a reply to message #534043] Fri, 02 December 2011 08:38 Go to previous message
Rodge
Messages: 6
Registered: December 2011
Junior Member
select version from v$instance

9.2.0.8.0
Previous Topic: Recovery fails on archived log, which is in the backup set.
Next Topic: completed before clause
Goto Forum:
  


Current Time: Tue Jan 16 12:26:46 CST 2018

Total time taken to generate the page: 0.14623 seconds