Categories

JAVA DATEBASE
Technology Network Community
Oracle Database
Fusion Middleware
Development Tools
Java
Desktop
Server & Storage Systems
Enterprise Management
Berkeley DB Family
Cloud Computing
Big Data
Business Intelligence
Architecture
Migration and Modernization
E-Business Suite
Siebel
PeopleSoft Enterprise
JD Edwards World
Industries
JD Edwards EnterpriseOne
User Productivity Kit Pro (UPK) and Tutor
Governance, Risk & Compliance (GRC)
Master Data Management (MDM)
Oracle CRM On Demand
On Demand: SaaS and Managed Applications
AutoVue Enterprise Visualization
Primavera
ATG
Agile PLM
Endeca Experience Management
Fusion Applications
Archived Forums

 



Tags

General Questions


ORA-01578: ORACLE data block corrupted on tables in sysaux tablespace


Dear Experts,   From the alert log file we noticed data block corruptions on one of our datafiles. After further investigation, we realized that the corruptions were on 3 of the AWR related tables in the SYSAUX tablespace:   1. WRH$_LIBRARYCACHE  2. WRH$_TEMPSTATXS  3. WRI$_ALERT_OUTSTANDING   The bad news is that we may not have a valid rman backup to do the recovery due to  the retention policy - RECOVERY WINDOW OF 2 DAYS. Since this is a development database with limited monitoring, we did not discover the corruption until 6 days later. The issue happened about 6 days ago (about Christmas time).   So, what are our recovery options? Can someone advice? We are thinking about drop and recreate the 3 affected v$WR* tables, but not quite sure about the impact to the system if we drop and recreate the 3 objects. Did someone experience this type of recovery. If you did, what are your approaches?   We are running oracle 10.2.0.3 version.  I greatly appreciate your input and suggestion. Thanks!!!

i am not sure if its a good idea to drop and recreate those tables as those are some system tables which is mainted by oracle itself....my suggestion as this is dev evn...open up a priority3 or 4 SR with oracle, as these are not just regular tables that can be droped or re-creating....It might cause more trouble than there is right now....

The retention policy doesn't preclude recovering from old backups. It just says, "as a MINIMUM, I must be able to perform an incomplete recovery to some point in the past two days". But so long as you have backups from before Christmas and have all the archives generated since then, you will be able to perform a recovery. If you have been zealously issuing 'delete obsolete' commands, then fair enough: those old backups and archives may no longer physically exist. But short of that, just because a retention policy makes RMAN believe old backups are obsolete and unneeded, it doesn't actually delete them and can make perfectly good use of them whenever needed.  Yours is a development database and there's therefore perhaps less need to be strict about this than I would be with a production database -but you simply shouldn't go around dropping and re-creating tables owned by SYS, no matter what the circumstances. These WRH$ tables are all to do with the Automatic Workload Repository, which is (assuming you're licensed for it) all about how a database performs and is tuned. Muck about with those in a production database, and Lord knows the consequences. I wouldn't do it.   If I had a corrupt development database and no usable backups (which, as I say, isn't necessarily true for you even with your retention policy), I'd consider re-creating it from scratch (perhaps as a clone from some other dev or prod database) before tinkering with system tables I knew nothing about...

as long as you have a backup of ur database before christmas, you can use the " MAXDAYS " cmd to get ur backup working so long as you have not used delete obsolote....had a same sistuation....where i had a backup and trying to restore it ...kept saying no valid backup...after going thru some stuff...found the MAXDAYS cmd to use my backup...here is an example ...  $ rman target / Recovery Manager: Release 10.2.0.2.0 - Production on Sun Apr 6 09:05:44 2008 Copyright (c) 1982, 2005, Oracle.  All rights reserved. connected to target database (not started)   RMAN> SET DBID=1528894801 executing command: SET DBID  RMAN> startup force nomount; startup failed: ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/u01/app/oracle/product/10.2.0/db_1/dbs/initsameera.ora' starting Oracle instance without parameter file for retrival of spfile Oracle instance started Total System Global Area     159383552 bytes Fixed Size                     1259672 bytes Variable Size                 58722152 bytes Database Buffers              92274688 bytes Redo Buffers                   7127040 bytes  RMAN> set controlfile autobackup format for device type disk to '/u99/backup/sameera/control_spfile_%F';                          executing command: SET CONTROLFILE AUTOBACKUP FORMAT using target database control file instead of recovery catalog   RMAN> run 2> { 3> allocate channel p1 type disk; 4>  restore spfile to pfile '/u01/app/oracle/product/10.2.0/db_1/dbs/initsameera.ora' from autobackup; 5> shutdown abort; 6> } allocated channel: p1 channel p1: sid=36 devtype=DISK Starting restore at 06-APR-08 channel p1: looking for autobackup on day: 20080406 channel p1: looking for autobackup on day: 20080405 channel p1: looking for autobackup on day: 20080404 channel p1: looking for autobackup on day: 20080403 channel p1: looking for autobackup on day: 20080402 channel p1: looking for autobackup on day: 20080401 channel p1: looking for autobackup on day: 20080331 channel p1: no autobackup in 7 days found released channel: p1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 04/06/2008 09:09:09 RMAN-06172: no autobackup found or specified handle is not a valid copy or piece  Solution:  RMAN> shutdown abort;  RMAN> EXIT;  $ ps -ef |grep pmon oracle    2891  2856  0 09:05 pts/1    00:00:00 grep pmon oracle    7448     1  0 Apr05 ?        00:00:00 ora_pmon_primary  $export ORACLE_SID=sameera  $ rman target / Recovery Manager: Release 10.2.0.2.0 - Production on Sun Apr 6 09:05:44 2008 Copyright (c) 1982, 2005, Oracle.  All rights reserved. connected to target database (not started)   RMAN> SET DBID=1528894801 executing command: SET DBID   RMAN> startup force nomount; startup failed: ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/u01/app/oracle/product/10.2.0/db_1/db s/initsameera.ora' starting Oracle instance without parameter file for retrival of spfile Oracle instance started Total System Global Area     159383552 bytes Fixed Size                     1259672 bytes Variable Size                 58722152 bytes Database Buffers              92274688 bytes Redo Buffers                   7127040 bytes   RMAN> set controlfile autobackup format for device type disk to '/u99/backup/sameera/control_spfile_%F';                          executing command: SET CONTROLFILE AUTOBACKUP FORMAT using target database control file instead of recovery catalog   RMAN> run 2> { 3> allocate channel p1 type disk; 4> restore spfile to pfile '/u01/app/oracle/product/10.2.0/db_1/dbs/initsameera.ora' from autobackup maxdays 15; 5> shutdown abort; 6> }  released channel: ORA_DISK_1 allocated channel: p1 channel p1: sid=36 devtype=DISK Starting restore at 06-APR-08 channel p1: looking for autobackup on day: 20080406 channel p1: looking for autobackup on day: 20080405 channel p1: looking for autobackup on day: 20080404 channel p1: looking for autobackup on day: 20080403 channel p1: looking for autobackup on day: 20080402 channel p1: looking for autobackup on day: 20080401 channel p1: looking for autobackup on day: 20080331 channel p1: looking for autobackup on day: 20080330 channel p1: looking for autobackup on day: 20080329 channel p1: looking for autobackup on day: 20080328 channel p1: looking for autobackup on day: 20080327 channel p1: looking for autobackup on day: 20080326 channel p1: looking for autobackup on day: 20080325 channel p1: looking for autobackup on day: 20080324 channel p1: looking for autobackup on day: 20080323 channel p1: autobackup found: /u99/backup/sameera/control_spfile_c-1528894801-20080323-00 channel p1: SPFILE restore from autobackup complete Finished restore at 06-APR-08 Oracle instance shut down  Check to make sure if initsameera.ora exists in $ORACLE_HOME/dbs location.  $ cd $ORACLE_HOME/dbs $ ls -ltr total 7052  -rw-r-----  1 oracle oinstall    2560 Apr  5 13:21 spfileprimary.ora -rw-r-----  1 oracle oinstall 7061504 Apr  5 13:23 snapcf_primary.f -rw-rw----  1 oracle oinstall    1544 Apr  5 18:42 hc_sameera.dat -rw-r--r--  1 oracle oinstall    1087 Apr  6 09:12 initsameera.ora $ pwd /u01/app/oracle/product/10.2.0/db_1/dbs $

It seems a bit of a long way to achieve this, to be honest. And anyway I dislike recovery steps which require me to restore things which aren't broken. In this case, the spfile is not lost or damaged, so restoring it from an autobackup (assuming one exists!) is not particularly desirable, it seems to me.    If you have backups from before Christmas as well as after, you can force RMAN to restore from the older backups (which it wouldn't normally do) simply by specifying the FROM TAG=xxxx syntax. For example, restore datafile 2 from tag='PREXMAS';

Thanks everyone for your reply. I appreciate your thoughts.  Here is our rman backup script:  connect target; RUN { crosscheck backup; DELETE NOPROMPT OBSOLETE; backup database plus archivelog; backup current controlfile; }  As you can see we used "DELETE NOPROMPT OBSOLETE".  Attached please see the "list backup summary":  RMAN> list backup summary;  using target database control file instead of recovery catalog  List of Backups =============== Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag  -- -- - --------------- ------- --- 2411    B  F  A DISK        28-DEC-08       1       1       YES        TAG20081228T230120 2412    B  F  A DISK        28-DEC-08       1       1       YES        TAG20081228T230120 2413    B  A  A DISK        28-DEC-08       1       1       YES        TAG20081228T230613 2414    B  A  A DISK        28-DEC-08       1       1       YES        TAG20081228T230613 2416    B  F  A DISK        28-DEC-08       1       1       NO         TAG20081228T230617 2417    B  A  A DISK        29-DEC-08       1       1       YES        TAG20081229T230034 2418    B  A  A DISK        29-DEC-08       1       1       YES        TAG20081229T230034 2419    B  A  A DISK        29-DEC-08       1       1       YES        TAG20081229T230034 2420    B  F  A DISK        29-DEC-08       1       1       YES        TAG20081229T230146 2421    B  F  A DISK        29-DEC-08       1       1       YES        TAG20081229T230146 2422    B  A  A DISK        29-DEC-08       1       1       YES        TAG20081229T230709 2423    B  A  A DISK        29-DEC-08       1       1       YES        TAG20081229T230709 2424    B  F  A DISK        29-DEC-08       1       1       YES        TAG20081229T230711 2425    B  F  A DISK        29-DEC-08       1       1       NO         TAG20081229T230713 2426    B  A  A DISK        30-DEC-08       1       1       YES        TAG20081230T230019 2427    B  A  A DISK        30-DEC-08       1       1       YES        TAG20081230T230019 2428    B  A  A DISK        30-DEC-08       1       1       YES        TAG20081230T230019 2429    B  F  A DISK        30-DEC-08       1       1       YES        TAG20081230T230136 2430    B  F  A DISK        30-DEC-08       1       1       YES        TAG20081230T230136 2431    B  A  A DISK        30-DEC-08       1       1       YES        TAG20081230T230558 2432    B  A  A DISK        30-DEC-08       1       1       YES        TAG20081230T230558 2433    B  F  A DISK        30-DEC-08       1       1       YES        TAG20081230T230600 2434    B  F  A DISK        30-DEC-08       1       1       NO         TAG20081230T230602 2435    B  A  A DISK        31-DEC-08       1       1       YES        TAG20081231T230023 2436    B  A  A DISK        31-DEC-08       1       1       YES        TAG20081231T230023 2437    B  A  A DISK        31-DEC-08       1       1       YES        TAG20081231T230023 2438    B  F  A DISK        31-DEC-08       1       1       YES        TAG20081231T230143 2439    B  F  A DISK        31-DEC-08       1       1       YES        TAG20081231T230143 2440    B  A  A DISK        31-DEC-08       1       1       YES        TAG20081231T230605 2441    B  A  A DISK        31-DEC-08       1       1       YES        TAG20081231T230605 2442    B  F  A DISK        31-DEC-08       1       1       YES        TAG20081231T230606 2443    B  F  A DISK        31-DEC-08       1       1       NO         TAG20081231T230608  I do not see the pre-Christams backups listed here. Any ideas?  Thanks!!!

You backup to disk?  Do you not copy these files to tape?  If you can obtain copies of the pre-Christmas backups from tape, you can simply re-catalogue them ("catalog backupset..."), which will make RMAN aware of them again.  Failing that, you're saying these pre-Christmas backups simply don't exist anymore, anywhere?   Fair enough if so: you're back to what I talked about first time... re-create this database (export your user data which seems to be OK, create a new database, import everything... for example). If it was me, I'd probably be checking hardware, too, because corruptions don't just happen without there being an underlying cause. I'd personally be thinking about switching to newer disk drives, for example. I'd certainly be looking to switch on and monitor things like SMART drive checking.  Oh: and I'd be editing my backup script so that deleting obsolete files was something I did maybe weekly or fortnightly, +manually+!

Thank you, HJR, for your advice!   For lower systems, backups go to disk only. I guess we are out of luck in getting a valid backup for this recovery. 3 options we may have:   1. Clone the database from a  production.  2. Repair the corrupted data block. Has anyone used the Oracle DBMS_REPAIR packages? If you do, do you mind sharing your experience?  3. Leave the corruption in its place but monitoring it. Anyone has experience leaving the corruption in its place and the corruptions are related to AWR tables?   One interesting observation to share: Our RMAN backups of this database has been running with no errors despite the corrupted blocks.  My guess is that the corruption could be logical not physical. Otherwise, RMAN backups could have picked it up. It looks that RMAN does not automatically detect logical corruption? Can someone share his/her thoughts on this?  Thanks again for your input!!!


Related Links

how to know Dedicated or Shared ...
re:Oracle/Linux help!!
initialization parameters in 9i
5th greatest date
multiple Oracle Homes
ORA-01000: maximum open cursors exceeded
Is it possible to create temp tablespace as in-memory datafile on UNIX ?
compare privileges
Index and foreign key
ora-01017 logon denied - help
open file descriptor limit
URGENT - import 7er-export into 9ir2??
Oracle9i Database Release 2 for Sun SPARC Solaris (9.2.0.3)
SQL PLus login without entering Host String
Cannot run Oracle9i Enterprise Manager Console
8i database new install