couldn't drop a rollback segment

[s***@gmail.com]

Hello,

We lost a datafile belonging to an undo tablespace. Since we are in the
process of setting up the test environment, we don't have any backup of
the undo datafile. So we created another undo tablespace and set
undo_tablespace instance parameter to point to the new one.

When we were trying to drop the old undo tablespace, we received the
following error:

SQL> alter database datafile '/oracle/oradata/test/undotbs02.dbf'
offline drop;

Database altered.

SQL> drop tablespace undotbs02;
drop tablespace undotbs02
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1$' found, terminate
dropping
tablespace


SQL> drop rollback segment _SYSSMU1$;
drop rollback segment _SYSSMU1$
*
ERROR at line 1:
ORA-00911: invalid character

SQL> drop rollback segment '_SYSSMU1$';
drop rollback segment '_SYSSMU1$'
*
ERROR at line 1:
ORA-02175: invalid rollback segment name

What should we do in order to drop undo tablespace UNDOTBS02? Since
this is a test box, we don't really care about data loss.

Thanks for your help.

[R. Schierbeek]

Post by s***@gmail.com
Hello,
We lost a datafile belonging to an undo tablespace. Since we are in the
process of setting up the test environment, we don't have any backup of
the undo datafile. So we created another undo tablespace and set
undo_tablespace instance parameter to point to the new one.
SQL> alter database datafile '/oracle/oradata/test/undotbs02.dbf' offline drop;
Database altered.
SQL> drop tablespace undotbs02;
drop tablespace undotbs02
*
ORA-01548: active rollback segment '_SYSSMU1$' found, terminate dropping
SQL> drop rollback segment _SYSSMU1$;
drop rollback segment _SYSSMU1$
*
ORA-00911: invalid character
SQL> drop rollback segment '_SYSSMU1$';
drop rollback segment '_SYSSMU1$'
*
ORA-02175: invalid rollback segment name
What should we do in order to drop undo tablespace UNDOTBS02? Since
this is a test box, we don't really care about data loss.

Did you bounce the database after creating another undo tablespace ?
Check where the segs are :
select tablespace_name,segment_name
from dba_segments where segment_type='ROLLBACK'

and go from there. good luck,
Roelof Schierbeek; DBA

[s***@gmail.com]

Yes we bounced the database after creating the new undo tablespace.

The rollback segs are from the old undo tablespace UNDOTBS02:

SQL> select segment_name, tablespace_name, status from
dba_rollback_segs;
Press RETURN to continue

SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------
----------------
SYSTEM SYSTEM ONLINE
_SYSSMU1$ UNDOTBS02 NEEDS
RECOVERY
_SYSSMU2$ UNDOTBS02 NEEDS
RECOVERY
_SYSSMU3$ UNDOTBS02 NEEDS
RECOVERY
_SYSSMU4$ UNDOTBS01 ONLINE
_SYSSMU5$ UNDOTBS01 ONLINE
_SYSSMU6$ UNDOTBS01 ONLINE
_SYSSMU7$ UNDOTBS01 ONLINE
_SYSSMU8$ UNDOTBS01 ONLINE
_SYSSMU9$ UNDOTBS01 ONLINE
_SYSSMU10$ UNDOTBS01 ONLINE
_SYSSMU11$ UNDOTBS02 NEEDS
RECOVERY
_SYSSMU12$ UNDOTBS02 NEEDS
RECOVERY
_SYSSMU13$ UNDOTBS02 NEEDS
RECOVERY
_SYSSMU14$ UNDOTBS02 NEEDS
RECOVERY
_SYSSMU15$ UNDOTBS02 NEEDS
RECOVERY
_SYSSMU16$ UNDOTBS02 NEEDS
RECOVERY
_SYSSMU17$ UNDOTBS02 NEEDS
RECOVERY
_SYSSMU18$ UNDOTBS02 NEEDS
RECOVERY
_SYSSMU19$ UNDOTBS02 NEEDS
RECOVERY
_SYSSMU20$ UNDOTBS02 OFFLINE
_SYSSMU21$ UNDOTBS01 ONLINE
_SYSSMU22$ UNDOTBS01 ONLINE
_SYSSMU23$ UNDOTBS01 ONLINE


The datafile for UNDOTBS02 no longer exists, and we don't have any
backup.
How can we force it to drop UNDOTBS02 without doing any recovery?


Thanks.

Post by R. Schierbeek

Post by s***@gmail.com
Hello,
We lost a datafile belonging to an undo tablespace. Since we are in the
process of setting up the test environment, we don't have any backup of
the undo datafile. So we created another undo tablespace and set
undo_tablespace instance parameter to point to the new one.
SQL> alter database datafile '/oracle/oradata/test/undotbs02.dbf' offline drop;
Database altered.
SQL> drop tablespace undotbs02;
drop tablespace undotbs02
*
ORA-01548: active rollback segment '_SYSSMU1$' found, terminate dropping
SQL> drop rollback segment _SYSSMU1$;
drop rollback segment _SYSSMU1$
*
ORA-00911: invalid character
SQL> drop rollback segment '_SYSSMU1$';
drop rollback segment '_SYSSMU1$'
*
ORA-02175: invalid rollback segment name
What should we do in order to drop undo tablespace UNDOTBS02? Since
this is a test box, we don't really care about data loss.Did you bounce the database after creating another undo tablespace ?

select tablespace_name,segment_name
from dba_segments where segment_type='ROLLBACK'
and go from there. good luck,
Roelof Schierbeek; DBA- Hide quoted text -- Show quoted text -

[EscVector]

Post by s***@gmail.com
Hello,
We lost a datafile belonging to an undo tablespace. Since we are in the
process of setting up the test environment, we don't have any backup of
the undo datafile. So we created another undo tablespace and set
undo_tablespace instance parameter to point to the new one.
When we were trying to drop the old undo tablespace, we received the
SQL> alter database datafile '/oracle/oradata/test/undotbs02.dbf'
offline drop;
Database altered.
SQL> drop tablespace undotbs02;
drop tablespace undotbs02
*
ORA-01548: active rollback segment '_SYSSMU1$' found, terminate
dropping
tablespace
SQL> drop rollback segment _SYSSMU1$;
drop rollback segment _SYSSMU1$
*
ORA-00911: invalid character
SQL> drop rollback segment '_SYSSMU1$';
drop rollback segment '_SYSSMU1$'
*
ORA-02175: invalid rollback segment name
What should we do in order to drop undo tablespace UNDOTBS02? Since
this is a test box, we don't really care about data loss.
Thanks for your help.

Is this production?

If all transactions were closed there is an underscore (undocumented)
parameter that support can provide to open the database and ignore bad
undo files/blocks.

The database will open and you can then drop the bad tablespace.

[EscVector]

Post by EscVector

Post by s***@gmail.com
Hello,
We lost a datafile belonging to an undo tablespace. Since we are in the
process of setting up the test environment, we don't have any backup of
the undo datafile. So we created another undo tablespace and set
undo_tablespace instance parameter to point to the new one.
When we were trying to drop the old undo tablespace, we received the
SQL> alter database datafile '/oracle/oradata/test/undotbs02.dbf'
offline drop;
Database altered.
SQL> drop tablespace undotbs02;
drop tablespace undotbs02
*
ORA-01548: active rollback segment '_SYSSMU1$' found, terminate
dropping
tablespace
SQL> drop rollback segment _SYSSMU1$;
drop rollback segment _SYSSMU1$
*
ORA-00911: invalid character
SQL> drop rollback segment '_SYSSMU1$';
drop rollback segment '_SYSSMU1$'
*
ORA-02175: invalid rollback segment name
What should we do in order to drop undo tablespace UNDOTBS02? Since
this is a test box, we don't really care about data loss.
Thanks for your help.

Is this production?
If all transactions were closed there is an underscore (undocumented)
parameter that support can provide to open the database and ignore bad
undo files/blocks.
The database will open and you can then drop the bad tablespace.

Here's the parameter, but don't use this w/o support.
There are quite a few steps to get there prior to starting the db with
this param.

_CORRUPTED_ROLLBACK_SEGMENTS=( _SYSSMU1$ , ,.... _SYSSMU10$ )

[f***@cox.net]

Post by s***@gmail.com
Hello,
We lost a datafile belonging to an undo tablespace. Since we are in the
process of setting up the test environment, we don't have any backup of
the undo datafile. So we created another undo tablespace and set
undo_tablespace instance parameter to point to the new one.
When we were trying to drop the old undo tablespace, we received the
SQL> alter database datafile '/oracle/oradata/test/undotbs02.dbf'
offline drop;
Database altered.
SQL> drop tablespace undotbs02;
drop tablespace undotbs02
*
ORA-01548: active rollback segment '_SYSSMU1$' found, terminate
dropping
tablespace
SQL> drop rollback segment _SYSSMU1$;
drop rollback segment _SYSSMU1$
*
ORA-00911: invalid character
SQL> drop rollback segment '_SYSSMU1$';
drop rollback segment '_SYSSMU1$'
*
ORA-02175: invalid rollback segment name
What should we do in order to drop undo tablespace UNDOTBS02? Since
this is a test box, we don't really care about data loss.
Thanks for your help.

Single quotes denote text, double quotes surround object names:

SQL> drop rollback segment "_SYSSMU1$";

The leading _ causes the original 'invalid character' error; the ""
surrounding the name allow it to be processed. Mixed case table names
and other anomalies are allowed using such constructs:

SQL> create table MyFunnyName(blah number);

Table created.

SQL> create table "MyFunnyName" (blah_again number);

Table created.

SQL> select table_name
2 from user_tables
3 where table_name like 'M%'
4 /

TABLE_NAME
------------------------------
MYFUNNYNAME
MyFunnyName

7 rows selected.

SQL> create table _mytest (testing varchar2(40));
create table _mytest (testing varchar2(40))
*
ERROR at line 1:
ORA-00911: invalid character


SQL> create table "_MYTEST" (testing varchar2(40));

Table created.

SQL>

Of course, to access these objects you also need to use "":

SQL> select * from _mytest;
select * from _mytest
*
ERROR at line 1:
ORA-00911: invalid character


SQL> select * from "_MYTEST";

no rows selected

SQL>

Single-quotes won't work:

SQL> select * from '_MYTEST';
select * from '_MYTEST'
*
ERROR at line 1:
ORA-00903: invalid table name


SQL>

The same error you received when you used single-quotes around your
segment name.



David Fitzjarrell