Corrupted Undo Tablespace.

[gssilva]

Good afternoon to all,

I have the following doubt. I have an Oracle database installed on a Sun server, I had to take the bench in an archive way and obviously Oracle did not allow it to happen because the UNDO tablespace was corrupted. So, I would like to know with you what options I have to recover the UNDO tablespace and thus be able to take the bank in an archive way?

Note: The ancient archive were erased, so there is no recovery possibility.
Environment information:
* Oracle version: 10g
* Operating system: Sun

[diegolenhardt]

What is the situation of the bank now?

Open or Closed?


The bid of in Archive or not, is changing a parameter, but first Passo is to make the bank entering Open,

When do you get the startup error returns?

[gssilva]

The bank is open at the moment, but I can lower the bank and climb smoothly, but when I low and Starto as Mount to run Alter Database Noarchivel, it does not allow because the UNDO tablespace is corrupted.

follows msg:

Select all

SQL> alter database noarchivelog 
  2  ; 
alter database noarchivelog 
* 
ERROR at line 1: 
ORA-01143: cannot disable media recovery - file 3 needs media recovery 
ORA-01110: data file 3: '/u2/data/undo01.dbf'

and I can not recover by archive.
What are the alternatives I have to normalize the tablespace.?

[diegolenhardt]

Dude, create a new tablespace to undo, define it by default,

in the init has a parameter or via alter system,

after this places the UNDO tablespace Old as offline.;

When you give Alter Undoantiga offline tablespace;

After this I believe that it will be able to, but to make the alter Database NoarchIlog has to be with the closed bank, ie



Maybe for this or need to create another tablespace, but I recommend getting rid of this before more problems, it would be creating a new and inactivating the old,

from there makes a full backup Bank also expensive,

I would make an export of this bank to ensure any possible problem ...

[diegopedrao]

You lost a Undo Datafile that was probably with the Undo_Management = Auto.
You will have to switch to manual and then open the bank with the altered pfile so you can mount the bank and create a new UNDO tablespace and change the old off off and then recreate the spfile pfile to make the changes permanent.

ATT,

Diego Monteiro

[gssilva]

Personal, how do I create a new UNDO tablespace and inactivate the old one?

I would like to make it clear that where I am doing this is a test server so it has no real impact, and I am using it only for own knowledge.

Hugs!

[gssilva]

Guys, I already managed to do the procedure to create a new Undo tablespace (thanks to the great Rodrigo blog http://rodrigo-oracle.blogspot.com/2010 ... as-em.html and changed in the init to when the bank starts, already start with the new tablespace.

99]] Everything worked right as shows the following parameter

Select all

SQL> show parameters undo_tablespace 
 
NAME                                 TYPE        VALUE 
------------------------------------ ----------- ------------------------------ 
undo_tablespace                      string      UNDO02 
SQL>

only I do not know why cargo d'AGua I still can not put in the mode narchivelog because the same error appears.

Bank in Mount mode.

Select all

SQL> alter database noarchivelog; 
alter database noarchivelog 
* 
ERROR at line 1: 
ORA-01143: cannot disable media recovery - file 3 needs media recovery 
ORA-01110: data file 3: '/u2/data/undo01.dbf'

Note: I could not disable the UNDO01 tablespace because it does not exist as below.

Select all

SQL> alter tablespace undo01 offline; 
alter tablespace undo01 offline 
* 
ERROR at line 1: 
ORA-00959: tablespace 'UNDO01' does not exist

Select all

SQL> alter tablespace undo01 offline; 
alter tablespace undo01 offline 
* 
ERROR at line 1: 
ORA-00959: tablespace 'UNDO01' does not exist

What should I do now? Do you have any idea? I imagine I have to disable this Datafile somewhere so that the bank fills only the new tablespace of Undo (UNDO02) But I have no idea.

[diegolenhardt]

Should this Datafile belong to tablespace UNDO2 or not?

Turn the SELECT below and discover,

Select all

 
select * 
 from Dba_Data_Files 
 where tablespace_name like '%UNDO%' 

knowing which is the tablespace of this Datafile, you put it offline , but see before if it is not the main undo,

show parameters UNDO_TABLESPACE

[gssilva]

Good afternoon sirs.

I discovered the UNDO01 tablespace through the view dba_data_files and the same is called testundo, but when I try to make the tablespace offline, it returns the following error.

Select all

SQL> alter tablespace testeundo offline 
  2  ; 
alter tablespace testeundo offline 
* 
ERROR at line 1: 
ORA-01191: file 3 is already offline - cannot do a normal offline 
ORA-01110: data file 3: '/u2/data/undo01.dbf'

You could tell me what this error means?

In the Undo_TablesPace show parameters is the new (UNCO02) that is.

[diegopedrao]

Do the following:

Select all

startup mount; 
 
alter database datafile '/u2/data/undo01.dbf'  offline drop;

Att,

Diego Monteiro

[diegolenhardt]

Could drop directly, because the file is already offline, see:

File 3 is Already offline

So received error, knife the command of the Diego stone that will finally drop your Datafile asking for recover ...

falow