Search This Blog

Sunday, July 31, 2011

How to recover corrupted UNDO tablespace

Dear All,
Yesterday our development system went down because of CORRUPTED UNDO TABLESPACE.

the following error reported in the alert.log file

Sun Jul 31 09:27:35 2011
Flush retried for xcb 0x333b8e78, pmd 0x32898888
Doing block recovery for file 2 block 330
Block recovery from logseq 1462, block 70 to scn 42078052
Sun Jul 31 09:27:36 2011
Recovery of Online Redo Log: Thread 1 Group 3 Seq 1462 Reading mem 0
  Mem# 0 errs 0: C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG
Block recovery completed at rba 1462.408.16, scn 0.42078054
Sun Jul 31 09:27:38 2011
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j006_4020.trc:
ORA-00600: internal error code, arguments: [4194], [18], [24], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [18], [24], [], [], [], [], []

Sun Jul 31 09:27:41 2011
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j006_4020.trc:
ORA-00600: internal error code, arguments: [4194], [18], [24], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [18], [24], [], [], [], [], []

Sun Jul 31 09:27:45 2011
DEBUG: Replaying xcb 0x333ad298, pmd 0x33bc9e54 for failed op 8
Doing block recovery for file 2 block 227
Sun Jul 31 09:27:45 2011
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j006_4020.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [4194], [18], [24], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [18], [24], [], [], [], [], []

No block recovery was needed
Sun Jul 31 09:27:49 2011
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_pmon_948.trc:
ORA-00600: internal error code, arguments: [4194], [13], [7], [], [], [], [], []

Sun Jul 31 09:27:53 2011
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_pmon_948.trc:
ORA-00600: internal error code, arguments: [4194], [13], [7], [], [], [], [], []

PMON: terminating instance due to error 472
Sun Jul 31 09:27:53 2011
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j009_1608.trc:
ORA-00472: PMON  process terminated with error

Kindly use the following solution if you encountered with the exact above situation.

Step 1
--------

SQL> Startup nomount ; --> using spfile

SQL> Create pfile='/tmp/corrupt.ora' from spfile ;

SQL> Shutdown immediate;

Step 2
-------

Modify the corrupt.ora and set Undo_managment=Manual

SQL> Startup mount pfile='/tmp/corrupt.ora'

SQL> Show parameter undo

it should show manual

SQL> Alter database open ;

If it comes up

SQL> Create rollback segment r01 ;

SQL> Alter rollback segment r01 online ;

Create a new undo tablespace

SQL> Create undo tablespace undotbs_new datafile '<>' size <> M ;

Drop the Old undo tablespace

SQL> Drop tablespace including contents and datafiles



Step 3
-------

SQL> Shutdown immediate;

SQL> Startup nomount ; ---> Using spfile

SQL>Alter system set undo_tablespace= scope=spfile;

SQL> Shutdown immediate ;

SQL> Startup

Check if error is reported

Live Example:

C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jul 31 10:52:25 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             100666244 bytes
Database Buffers          503316480 bytes
Redo Buffers                7135232 bytes
SQL> create pfile = 'c:\corrupt.ora' from spfile;

File created.

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount pfile='c:\corrupt.ora';
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             100666244 bytes
Database Buffers          503316480 bytes
Redo Buffers                7135232 bytes
Database mounted.
SQL> alter database open;

Database altered.

SQL> create rollback segment r01;

Rollback segment created.

SQL> alter rollback segment r01 online;

Rollback segment altered.

SQL> create undo tablespace undotbs01 datafile 'C:\oracle\product\10.2.0\oradata
\ORCL\undotbs001.dbf' size 100m;

Tablespace created.

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             100666244 bytes
Database Buffers          503316480 bytes
Redo Buffers                7135232 bytes


SQL> alter system set undo_tablespace=undotbs01 scope=spfile;

System altered.

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             100666244 bytes
Database Buffers          503316480 bytes
Redo Buffers                7135232 bytes
Database mounted.
Database opened.
SQL>

ANY QUERIES ASKTAJ AT ASKDBATAJ

4 comments:

Arya said...

if i am using pfile in my database not created spfile before the corruption then how to recover undo TS.

Anonymous said...

Verу good informаtion. Lucκy me I rаn acгosѕ your site by chance (stumbleuрon).
I've book marked it for later!

Visit my webpage - chattroulette

Anonymous said...

Hi Guys, We've been in the Xrumer VPS Business Now For Years!
We like this forum so much that we've decided to make this thread and give you this promo code [b]BHWPROMO[/b]

If your worried about any of the Google Changes, Don't be. We've tested all of our seo tools and the results are better.
IF you build your links slowly and make them look natural than you will alwasy get better rankings.

Ask our support on chat box about applying the promo code if you cannot find the box on our site to enter in!

Packages are as follows:

[b]Package #1[/b]
Scrapebox
Dedicated Proxies
SEO PowerSuite
The Best Spinner
Backlink Kracken
Get Article Pro
Yellow Pages Scraper
Tweet Adder
Open Office

[b]Package #2[/b]
SEnuke Xcr
SErobot
Scrapebox
Dedicated Proxies
SEO PowerSuite
The Best Spinner
Backlink Kracken
Get Article Pro
Yellow Pages Scraper
Tweet Adder
Open Office

[b]Package #3[/b]
SEnuke Xcr
GSA Search Engine Ranker
GSA Indexer
Scrapebox
SErobot
Dedicated Proxies
SEO PowerSuite
The Best Spinner
Get Article Pro
Yellow Pages Scraper
Tweet Adder
Open Office

[b]Package #4[/b]
Xrumer 7 Elite
Hreffer
SEnuke Xcr
GSA Search Engine Ranker
GSA Indexer
SErobot
Dedicated Proxies
SEO PowerSuite
The Best Spinner
Get Article Pro
Yellow Pages Scraper
Tweet Adder
Open Office

[url=http://xrumervps.ca]Best SEO Tools VPS[/url]

Anonymous said...

helped thanks