Oracle RMAN BACKup Failed : Please help

Printable View

08-18-2003, 02:40 PM
rmadduluri

Oracle RMAN BACKup Failed : Please help

Hi ,

I Scheduled a backup job from OEM's RMAN console and after backing up some tablespaces it broke out with error " Exceeded limit of 0 corrupt blocks". Could anyone of you direct me on this or give me solution. Please see the ERROR OUTPUT file for the error.

Thanks

MAN>
connected to target database: IMSPROD (DBID=2329681464)
using target database controlfile instead of recovery catalog

RMAN>
RMAN> run {
2> allocate channel Channel1 type disk format 'D:\backup\imsbak_%s_%t';
3> backup
4> ( database include current controlfile );
5>
6> }
allocated channel: Channel1
channel Channel1: sid=9 devtype=DISK

Starting backup at AUG 18 2003 15:15:47
channel Channel1: starting full datafile backupset
channel Channel1: specifying datafile(s) in backupset
input datafile fno=00095 name=D:\LTPP_DATABASE\IMSPROD\TABLESPACES\MON_CLUS TER_HUGE_TABLE_TS01.DBF
input datafile fno=00093 name=D:\LTPP_DATABASE\IMSPROD\TABLESPACES\MON_HUGE _TABLE_TS01.DBF
input datafile fno=00089 name=D:\LTPP_DATABASE\IMSPROD\TABLESPACES\SMP_HUGE _INDEX_TS01.DBF
input datafile fno=00080 name=D:\LTPP_DATABASE\IMSPROD\TABLESPACES\MON_LARG E_TABLE_TS01.DBF
input datafile fno=00074 name=D:\LTPP_DATABASE\IMSPROD\TABLESPACES\MON_LARG E_INDEX_TS01.DBF
input datafile fno=00073 name=D:\LTPP_DATABASE\IMSPROD\TABLESPACES\AWS_LARG E_INDEX_TS01.DBF
input datafile fno=00081 name=D:\LTPP_DATABASE\IMSPROD\TABLESPACES\SMP_LARG E_TABLE_TS01.DBF
input datafile fno=00088 name=D:\LTPP_DATABASE\IMSPROD\TABLESPACES\SMP_XLAR GE_TABLE_TS01.DBF
input datafile fno=00086 name=D:\LTPP_DATABASE\IMSPROD\TABLESPACES\TRF_XLAR GE_INDEX_TS01.DBF
input datafile fno=00106 name=D:\LTPP_DATABASE\IMSPROD\TABLESPACES\TST_PG_B INDER_INDEX_TS01.DBF
input datafile fno=00056 name=D:\LTPP_DATABASE\IMSPROD\TABLESPACES\DLR_MEDI UM_TABLE_TS01.DBF
input datafile fno=00066 name=D:\LTPP_DATABASE\IMSPROD\TABLESPACES\SPS4_MED IUM_TABLE_TS01.DBF
input datafile fno=00048 name=D:\LTPP_DATABASE\IMSPROD\TABLESPACES\PK_MEDIU M_INDEX_TS01.DBF
input datafile fno=00063 name=D:\LTPP_DATABASE\IMSPROD\TABLESPACES\SPS1_MED IUM_TABLE_TS01.DBF
input datafile fno=00102 name=D:\LTPP_DATABASE\IMSPROD\TABLESPACES\REC_COUN TS.ORA
input datafile fno=00055 name=D:\LTPP_DATABASE\IMSPROD\TABLESPACES\CLM_MEDI UM_TABLE_OFFLINE_TS01.DBF
input datafile fno=00058 name=D:\LTPP_DATABASE\IMSPROD\TABLESPACES\MNT_MEDI UM_TABLE_TS01.DBF
input datafile fno=00065 name=D:\LTPP_DATABASE\IMSPROD\TABLESPACES\SPS3_MED IUM_TABLE_TS01.DBF
input datafile fno=00011 name=D:\LTPP_DATABASE\IMSPROD\TABLESPACES\SMP_SMAL L_INDEX_TS01.DBF
input datafile fno=00043 name=D:\LTPP_DATABASE\IMSPROD\TABLESPACES\IMS_MEDI UM_INDEX_TS01.DBF
input datafile fno=00109 name=D:\LTPP_DATABASE\IMSPROD\TABLESPACES\QC.ORA
input datafile fno=00009 name=D:\LTPP_DATABASE\IMSPROD\TABLESPACES\MON_SMAL L_INDEX_TS01.DBF
input datafile fno=00029 name=D:\LTPP_DATABASE\IMSPROD\TABLESPACES\MON_SMAL L_TABLE_TS01.DBF
input datafile fno=00062 name=D:\LTPP_DATABASE\IMSPROD\TABLESPACES\SPS_MEDI UM_TABLE_TS01.DBF
input datafile fno=00003 name=D:\LTPP_DATABASE\IMSPROD\TABLESPACES\IMS_SMAL L_INDEX_TS01.DBF
input datafile fno=00007 name=D:\LTPP_DATABASE\IMSPROD\TABLESPACES\INV_SMAL L_INDEX_TS01.DBF
input datafile fno=00022 name=D:\LTPP_DATABASE\IMSPROD\TABLESPACES\TRF_SMAL L_INDEX_TS01.DBF
input datafile fno=00035 name=D:\LTPP_DATABASE\IMSPROD\TABLESPACES\SPS4_SMA LL_TABLE_TS01.DBF
input datafile fno=00039 name=D:\LTPP_DATABASE\IMSPROD\TABLESPACES\SPS8_SMA LL_TABLE_TS01.DBF
input datafile fno=00027 name=D:\LTPP_DATABASE\IMSPROD\TABLESPACES\INV_SMAL L_TABLE_TS01.DBF
channel Channel1: starting piece 1 at AUG 18 2003 15:15:52
released channel: Channel1
RMAN-00571: ================================================== =========
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ================================================== =========
RMAN-03009: failure of backup command on Channel1 channel at 08/18/2003 15:17:17
ORA-19566: exceeded limit of 0 corrupt blocks for file D:\LTPP_DATABASE\IMSPROD\TABLESPACES\SPS5_MEDIUM_T ABLE_TS01.DBF

RMAN>
RMAN> **end-of-file**

RMAN>

Recovery Manager complete.

child process exited abnormally
08-18-2003, 03:13 PM
skhanal

Sounds like your data file

D:\LTPP_DATABASE\IMSPROD\TABLESPACES\SPS5_MEDIUM_T ABLE_TS01.DBF

is corrupt. Can you access tables in this data file?. Run select * on all tables in this tablespace to see which one breaks.

You may have to recover this tablespace.
08-18-2003, 03:53 PM
rmadduluri

Hi,

Thank you, I did run the select * on all the tables in that tablespace and they all can be queried. So I do not know what else to do . Do you want me to still recover the tablespace ?

Rukmini
08-20-2003, 12:59 PM
rmadduluri

hi skhanal,

As posted earlier I queried the tables and also ran DBMS_REPAIR.CHECK_OBJECT and DBMS_REPAIR.SKIP_CORRUPT_BLOCKS . DBMS_REPAIR.CHECK_OBJECT did not report any corrupt blocks . So I'm wondering what is wrong with that tablespace.

Rukmini
08-20-2003, 03:29 PM
skhanal

Can you export all the tables in that tablespace using EXP utility.

Most likely an index may be corrupt. Rebuilding indexes may help.
08-20-2003, 03:33 PM
rmadduluri

Yes I did export all the tables using export utility and I had no problems. I looked at the alert log and tracked the table by using block number and file number. Now I know which table is corrupt. I'm planning export all the tables , drop the tablespace , recreate it and then import them back. does this plan sound good or do you have any suggestions
08-20-2003, 03:41 PM
skhanal

I would first try rebuilding indexes for that table, because when you export or do select * indexes may not have to be read and corrupt block may belong to an index.

Be sure to backup the whole database before you drop the tablespace. Also have the script to recreate all referential constraints from tables in other tablespace.

Recompile all objects after you import to make sure nothing is INVALID.
08-20-2003, 03:52 PM
rmadduluri

Thanks, I will do that tomorrow as we are almost closing for the day(EST) and I have had enough for today. I will post on the results
08-22-2003, 09:54 AM
rmadduluri

hi skhanal

I fixed the corrupt blocks problem by creating another tablespace moving all the tables and than droooing the old ones. I didn't try rebuilding the indexes because our indexes are in seperate tablespace.

Now I have some corrupt blocks in a index tablespace which was reported by dbv. Will rebuilding the indexes in that tablespace fix the blocks or should I drop and create them ?

One more question, in addressing the same problem with another index tablespace which had only few indexes , I dropped all the indexes and recreated them. In this process where I had an index for primary key,I created the index on sys schema and the primary key on user schema . How can I bring this index back to user schema. When I try to drop the PK constraint it complains that index does not exist. If I go to SYS and try to drop the index it complains that the an index related to PK cannot be dropped. In short the pk constraint and related index are owned by two different users.

Thanks for all your help....
08-25-2003, 09:28 AM
skhanal

It's better to drop and recreate indexes if you can.

How did you create PK and indexes separately?. Did you create the unique index first then used USING clause to assign that index for PK?

Did you use ALTER TABLE command to drop the primary key?. Try login an account with DBA privilege and drop and recreate the PKs.
08-25-2003, 09:44 AM
rmadduluri

I dropped the indexes and recrested them

For that one pk for which I created unique index first and then pk by using index clause, I dropped the table recreated table and index and then copied the data.

Yes I did use the alter table to drop pk but it did not let me. All through this Process your suggestions were really help ful. I'm now successfully able to backup the database. I also ran dbv on all the dbf files to check and have made sure everything was corrupt free.

Thanks again