iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Training

    Home      .Services     Login       Start Learning     Certification      .                 .Share your BELIEF(s)...

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       Email2aFriend    |

 

Question:

I have an Oracle test database which I found unable to open, because my undo log somehow got corrupted.

In my alert file I get the following error when I try to open the database:

Errors in file /u01/oracle/admin/TESTGEA/ udump/ORA26786760.TRC:
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u06/UNDOTBS01.DBF'
...
Error 376 happened during db open, shutting down database

The log file ORA26786760.TRC has very little information, it simply says:

KCRA: buffers claimed = 6/6, eliminated = 0
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u06/UNDOTBS01.DBF'

It looks like that my database has corrupted undo tablespace. When I use the dbv command (dbv file='/u06/undotbs01.dbf' blocksize=64384) I get an error message that my undo tablespace is corrupted.

 

Please advise what I need to do. I cannot open my database and I cannot do anything.

Thanks for your help.

 

More Resources by Google:

 

Answer:

Since your database has a corrupted undo tablespace, you cannot open the database. You should first offline the corrupted undo tablespace and then open the database to create a new undo tablespace.

 

To do that, do the following step.

Try to startup your database with mount open. This will give you an option to offline your tablespace.

SQL> startup mount;

 

Alter database to offline your undo tablespace datafile. Notice that you cannot drop your undo tablespace. If you try to drop the tablespace you get the following error message.

ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

SQL> alter database datafile '/u06/UNDOTBS01.DBF' offline drop;

Database altered.

 

Now, you should be able to open your database. You need to open your database to create your new undo tablespace.

SQL> alter database open;

Database altered.

 

Once you open your database, you are able to create a new undo tablespace.

SQL> create undo tablespace undotbs2 datafile '/u06/UNDOTBS02.DBF' SIZE 50M;

Tablespace created.

 

Make the new undo tablespace as a default undo tablespace for your database.

SQL> alter system set undo_tablespace = 'undotbs2' scope = both;

System altered.

 

Try to drop the old undo tablespace (undotbs1).

SQL> drop tablespace undotbs1;

Tablespace dropped.

 

If the drop was successful then create pfile from spfile. Always when you change or alter database make sure you a copy of pfile in the case if you need to open your database using pfile.

To create pfile.

SQL> CREATE PFILE FROM SPFILE;

In the case that you are not able to drop the undo tablespace, you can do the following.

1.       Leave the old undo tablespace.

2.       Shutdown the database and startup; then drop the undo tablespace.

3.       Create a new control file remove all references to the old undo tablespace and run the script.

 

How to do step 3:

Once your database is open, backup the controlfile to trace:

SQL> alter database backup controlfile to trace as '/u01/myscript.sql';    

Database altered.

 

Edit the script to remove all references to the old undo tablespace and the run the script.

 

Good Luck!

 

Google
 
Web web site