ORA-01157 undo tablespace corrupted

ORA-01157 undo tablespace corrupted


In rare cases (usually DBA error) the Oracle UNDO tablespace can become corrupted.  This manifests with this error:

ORA-00376: file xx cannot be read at this time

 [ora920@tkyte-pc-isdn ora920]$ sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Sep 28 08:33:29 2002

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

idle> connect / as sysdba
Connected to an idle instance.
idle> startup
ORACLE instance started.

Total System Global Area  143725064 bytes
Fixed Size                   451080 bytes
Variable Size             109051904 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/usr/oracle/ora920/OraHome1/oradata/ora920/undotbs01.dbf'


idle> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS1

idle> alter system set undo_management = manual scope=spfile;

System altered.

Note: disabling the undo tablespace here, this'll let us go back to "rollback segments"
and we'll use the system RBS to get back on our feet


idle> shutdown
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

idle> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

[ora920@tkyte-pc-isdn ora920]$ !sql
sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Sep 28 08:35:34 2002

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

idle> connect / as sysdba;
Connected to an idle instance.
idle> startup
ORACLE instance started.

Total System Global Area  143725064 bytes
Fixed Size                   451080 bytes
Variable Size             109051904 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/usr/oracle/ora920/OraHome1/oradata/ora920/undotbs01.dbf'

Note: we still have to get rid of that thing..

idle> alter database datafile  '/usr/oracle/ora920/OraHome1/oradata/ora920/undotbs01.dbf'
  2  offline drop;

Database altered.

idle> alter database open;

Database altered.

idle> drop tablespace undotbs1;

Tablespace dropped.

idle> create UNDO tablespace undotbs1
  2  datafile '/usr/oracle/ora920/OraHome1/oradata/ora920/undotbs01.dbf' size 25m
  3  autoextend on next 1m maxsize 1024m;

Tablespace created.

idle> alter system set undo_management = auto scope=spfile;

System altered.

Now, we've put the undo tablespace "back" by creating a new one.  Just bounce...

idle> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
idle> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
[ora920@tkyte-pc-isdn ora920]$ !sql
sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Sep 28 08:40:51 2002

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

idle> connect / as sysdba;
Connected to an idle instance.
idle> startup
ORACLE instance started.

Total System Global Area  143725064 bytes
Fixed Size                   451080 bytes
Variable Size             109051904 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
idle> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS1
idle>


and we are right back where we started from


Undo regards,

Suresh R
oracle dba.

Rename oracle schema or Alter schema in oracle

Rename the schema name in oracle by using below one step;

Step 1;

  Make sure no connection using the username which is going to rename.

If its using ,bounce the DB and do the below steps.

Step 2 :

  

   In oracle one of the table is having all the user information , so we can update the username through that table avoiding alter command.

select * from sys.user$;

Now I am going to change the username from nath to nathan as like below;

UPDATE SYS.USER$ SET NAME='NATHAN' where name='NATH';

AND ALTER THE USER LIKE BELOW;

ALTER USER NATHAN IDENTIFIED BY NATHAN;

GRANT DBA TO NATHAN;

Good luck

Suresh R

oracle dba.