Urgent- Error in PROD--ORA-01552: cannot use system rollback segment for non-system tablespace 'WQT'

I get this error when I login into my application which accesses Oracle database. I am new to Oracle. please let me know how to fix this error.
ORA-01552: cannot use system rollback segment for non-system tablespace 'WQT'  
 
Please help...

Thanks...
D
durga_manthaAsked:
Who is Participating?
 
actonwangConnect With a Mentor Commented:
you might follow the following step:

1. Create the undo tablespace , if it does not exists.
SQL> CREATE UNDO TABLESPACE UNDOTBS1
DATAFILE '<location>' SIZE 100M;

2. SQL> alter system set undo_management=auto scope=spfile;

3. SQL> alter system set undo_tablespace=UNDOTBS1 scope=spfile;

4. Shutdown and re-start
0
 
Mark GeerlingsDatabase AdministratorCommented:
By default when an Oracle database is created, only a "SYSTEM" rollback segment is created.  Thtis cannot be used though for transactions invlolving tables in other tablespaces.

This was true in Oracle8 and earlier, and in Oracle9 only if you are not using the default "undo" tablespace in Oracle9.  Please tell us which version of Oracle you have.

If you have Oracle8 or earlier, of if you have Oracle9 or later and for some reason you are not using the "undo" tablespace, you will need to create public rollbacksegments in a tablespace other than the SYSTEM tablespace.  I always created a tablespace just for rollback segments, because you don't normally want them in the same tablespace with your tables and indexes.
0
 
durga_manthaAuthor Commented:
I have Oracle 9 and later. I was looking at another thread and found this solution but is still doen't seem to work. Also what is the simplest way to restart a database without shutting down the server? Can I start and stop a service?

--- Create an Undo tablespace as sys user.

CREATE UNDO TABLESPACE undotbs_01
DATAFILE 'location_of_datafile/undo01.dbf' SIZE 10M
REUSE AUTOEXTEND ON;

-- Change the init.ora parameters
UNDO_MANAGEMENT = AUTO
UNDO_TABLESPACE = undotbs_01

-- Restart the database
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
durga_manthaAuthor Commented:
Also,
I am issuing the following statement to create Public rollback statement

CREATE PUBLIC ROLLBACK SEGMENT wqt_01 TABLESPACE wqt

and I get the following error.

"Illegal Rollback operation in Automatic Undo Mode"

I switched the undo_managnerment in the init.ora to MANUAL and restarted but I am still unable to create the rollback statement.

Please continue to help. It is an emergency production problem for me.
Thanks...
0
 
johnsoneSenior Oracle DBACommented:
With undo_management=auto, there is no need to create a rollback segment.  Oracle handles that internally.

With undo_mangement=manual, after you create the rollback segment, you have to alter it to online.  You should also include the names of the rollback segments in the rollback_segments parameter in init.ora, this will bring them online when the database comes up.

You can shut down and start up oracle from SQL*Plus.  Connect as sysdba, and you shoud be able to issue "shutdown immediate", which will shut the database down, then issue "startup", which will bring it back up.  There shouldn't be a need to restart the server.
0
 
actonwangCommented:
You'll need to create a second rollback segment in the tablespace SYSTEM..
After that you can create other tablespaces, create rollback segments in those tablespaces and put the extra rollback segment in the tablespace SYSTEM offline.

acton
0
 
durga_manthaAuthor Commented:
I don't really want to create a rollback segment. But since this morning I have been getting this error when I do some operations on my database
ORA-01552: cannot use system rollback segment for non-system tablespace 'WQT'

Without creating rollback segments, how do I fix this problem. Please help.
0
 
actonwangCommented:
>>Without creating rollback segments, how do I fix this problem
     No. You must use the approach as I told to fix this problem. You can not alter the data if you don't have non-system rollback segment.
0
 
durga_manthaAuthor Commented:
When I try to create a second rollback statement in the system table space
CREATE ROLLBACK SEGMENT system1 TABLESPACE SYSTEM

I get an error "Illegal Rollback operation in Automatic Undo Mode"

I went to my init.ora file and changed this section as follows and restarted the server. but I still get this error

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=MANUAL
 
0
 
actonwangCommented:
issue:

show parameters undo

what do you see?
0
 
Mark GeerlingsDatabase AdministratorCommented:
With Oracle9 you should *NOT* be using "undo_management=MANUAL".  You should have these parameters:
UNDO_MANAGEMENT = AUTO
UNDO_TABLESPACE = undotbs_01

Actually the name of the UNDO_TABLESPACE ("undotbs_01") in this example can be any name you like, as long as it is not the name of another Oracle tablespace.

I have never switched an Oracle9 database from manual rollback segments to automatic (or undo) segments, so I'n not sure if you simply change the init parameters, then stop and restart the database, or if you have to manually create the undo tablespace either before or after that shutdwon.
0
 
actonwangCommented:
you might need to restart your server using:

startup pfile='<your init.ora location';
0
 
actonwangCommented:
you need to run:

show parameters undo

to verify your undo parameters.

you can use the approach I mentioned (if you choose manual) or simply use auto undo management as you saw in other forum. But you have to make sure they take effect after you restart the server.
0
 
actonwangCommented:
also you could use this to see rollback segments available in your db:

select segment_name,status,tablespace_name from dba_rollback_segs
/
0
 
actonwangCommented:
if you still want to stick to manual undo, follow the above

1. SQL> alter system set undo_management=manual scope=spfile;

2. shutdown and restart

3. following what I said above...


use "show parameters ..." to see current settings, you might not need to issue some "alter system .." s.
0
 
durga_manthaAuthor Commented:
I was trying to issue show parameters undo from sql plus.
When I login into the SQL plus using sysdba I get login denied. When I login using TOAD uisng the same username and password it works. How do I connect to SQL PLus using sysdba.

username: sysdba
password:
host string

Do I have to use any other systax to connect as sysdba?
0
 
actonwangCommented:
command line:

sqlplus " sys/pass@hoststring as sysdba"
0
 
Mark GeerlingsDatabase AdministratorCommented:
I don't like what Oracle did in Oracle9 (and it is the same in Oracle10) for logging in via SQL*Plus as sysdba.  It is awkward!

I usually log in as a normal user first, then at the SQL> prompt type:
connect sys@[host_string] as sysdba
Press [Enter], that will prompt you for the password


0
 
durga_manthaAuthor Commented:
ok here are my results.
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS
SQL>
0
 
actonwangCommented:
your problem is resolved?

you can run this and you should see some segments already been there:

select segment_name,status,tablespace_name from dba_rollback_segs
/
0
 
actonwangCommented:
also make sure you have unto tablespace named 'UNDOTBS' using:

select tablespace,contents,status
from dba_tablespaces
/
0
 
durga_manthaAuthor Commented:
Not yet solved. But my undo_tablespace UNDOTBS says NEEDS RECOVERY. Is that where the problem is?


SQL> select segment_name,status,tablespace_name from dba_rollback_segs

SEGMENT_NAME                   STATUS           TABLESPACE_NAME
------------------------------ ---------------- ----------------------
SYSTEM                         ONLINE           SYSTEM
_SYSSMU1$                      NEEDS RECOVERY   UNDOTBS
_SYSSMU2$                      NEEDS RECOVERY   UNDOTBS
_SYSSMU3$                      NEEDS RECOVERY   UNDOTBS
_SYSSMU4$                      NEEDS RECOVERY   UNDOTBS
_SYSSMU5$                      NEEDS RECOVERY   UNDOTBS
_SYSSMU6$                      NEEDS RECOVERY   UNDOTBS
_SYSSMU7$                      NEEDS RECOVERY   UNDOTBS
_SYSSMU8$                      NEEDS RECOVERY   UNDOTBS
_SYSSMU9$                      NEEDS RECOVERY   UNDOTBS
_SYSSMU10$                     NEEDS RECOVERY   UNDOTBS

SEGMENT_NAME                   STATUS           TABLESPACE_NAME
------------------------------ ---------------- ----------------------
_SYSSMU11$                     OFFLINE          UNDOTBS_01
_SYSSMU12$                     OFFLINE          UNDOTBS_01
_SYSSMU13$                     OFFLINE          UNDOTBS_01
_SYSSMU14$                     OFFLINE          UNDOTBS_01
_SYSSMU15$                     OFFLINE          UNDOTBS_01
_SYSSMU16$                     OFFLINE          UNDOTBS_01
_SYSSMU17$                     OFFLINE          UNDOTBS_01
_SYSSMU18$                     OFFLINE          UNDOTBS_01
_SYSSMU19$                     OFFLINE          UNDOTBS_01
_SYSSMU20$                     OFFLINE          UNDOTBS_01

21 rows selected.
0
 
durga_manthaAuthor Commented:
Also I got this error  ORA-01552: cannot use system rollback segment for non-system tablespace 'WQT'
only this morning and never before. Is it because of backups or something.
0
 
actonwangCommented:
did you run :

recover database

after you restore your database (assuming you are in archivelog mode).
0
 
durga_manthaAuthor Commented:
Once I issued this
alter system set undo_tablespace=UNDOTBS_01;

it started working.
0
 
actonwangCommented:
you can also refer to this to fix it:

http://www.fors.com/orasupp/rdbms/dba/28812_1.HTM

An easy way to do it is as the following:

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

Shutdown and re-start

It should bring up UNDOTBS_01 as your undo tablespace.
0
 
durga_manthaAuthor Commented:
But what happens to the old undo table space which says NEEDS RECOVERY?
What does it mean? Is it a recovery I have to do?
0
 
actonwangCommented:
NEEDS RECOVERY

Contains data from uncommitted transactions that cannot be rolled back (because the data files involved are inaccessible) or is corrupted.
0
 
actonwangCommented:
Did you restore your database lately? which mode are you in?

if you are running in noarchive mode and try to "recover database", Oracle tries to rollback and fails because it can not corresponding data in the backup datafiles, it could happen.

Acton
0
 
actonwangCommented:
One thing I was wrong:

SQL> alter system set undo_tablespace=UNDOTBS_01 scope=both;

This will take effect immediately. don't need to restart the db server.

Sorry.

0
 
durga_manthaAuthor Commented:
How do I change the mode to ARCHIVE mode?
0
 
actonwangCommented:
>> How do I change the mode to ARCHIVE mode?
      It might not be due to archive mode or not. I am not sure.

      make sure all your tablespaces and datafiles are online by using query I showed above.

     
0
 
actonwangCommented:
in order to make it as archivelog mode, you need to:

1.shutdown db
2.start up and mount the db
3. issue:
        alter database archivelog
4. make sure your parameter:
    log_archive_start = auto
    log_archive_dest = <your location>
5. then issue:
        alter database open
0
 
actonwangCommented:
0
 
actonwangCommented:
you need to think it through about archivelog mode and your backup/recovery strategies before you decide to do it.
0
 
durga_manthaAuthor Commented:
Hi actonwang,
Not sure if this is because of the stuff I did before, but at some parts of the application, I get the following error.

ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: 'D:\ORA10G_INF\ORADATA\IASDB\UNDOTBS01.DBF'

Do you know what it is?
Thanks...
0
 
actonwangCommented:
is this file online?
0
 
durga_manthaAuthor Commented:
How do I know that? Is there a command I can issue and tell?
0
 
actonwangCommented:
use this to see status:

select file#,status,name from v$datafile where name like '%UNDOTBS01.DBF';
0
 
actonwangCommented:
sometimes it is also due to hardware failure or permission on the file.
0
 
actonwangCommented:
if it is offline, use:

ALTER DATABASE
   DATAFILE 'D:\ORA10G_INF\ORADATA\IASDB\UNDOTBS01.DBF' ONLINE;


to bring it online.
0
 
durga_manthaAuthor Commented:
This is the result.

SQL> select file#,status,name from v$datafile where name like '%UNDOTBS01.DBF';

     FILE# STATUS
---------- -------
NAME
--------------------------------------------------------------------------------
         2 RECOVER
D:\ORA10G_INF\ORADATA\IASDB\UNDOTBS01.DBF
0
 
actonwangCommented:
>>D:\ORA10G_INF\ORADATA\IASDB\UNDOTBS01.DBF
??

is this your NEWLY created undo tablespace datafile or already existed?
0
 
durga_manthaAuthor Commented:
Already existing old one. Can I make it to point to a newly created one?
0
 
actonwangCommented:
You already changed your undo tablespace.

restart your applicatoin and retry.
0
 
durga_manthaAuthor Commented:
I think this is what I did, I created the new table space but put the same name for the datafile. Do you think that might have caused it? If so shall I drop the undo tablespace and recreate it this time with a different datafile?
0
 
Mark GeerlingsDatabase AdministratorCommented:
You definitely do *NOT* want two tablespaces to share the same datafile!  (I think Oracle will not allow you do that.)  Yes, try dropping the undo tablespace and re-creating it making sure that both the tablespace name and the data file name are unique in your database.  You may need to first create a new undo tablespace, and do an "alter system..." to set that as the one to use before you can drop the one you have now.
0
 
actonwangCommented:
>>I think this is what I did, I created the new table space but put the same name for the datafile. Do you think that might have caused it?

no ....

defintely not the same name...
0
 
actonwangCommented:
after you go through http://www.fors.com/orasupp/rdbms/dba/28812_1.HTM and can not resolve that "recover" issue. this  article is copied from metalink. You can opt to call oracle support for that.

You can create a new undo tablespace with new datafile as undo tablespace though I would recommend you try to resolve the "needs recovery" issue according to the article.
0
 
durga_manthaAuthor Commented:
I tried creating a new undo table space

CREATE UNDO TABLESPACE UNDOTBS_02
DATAFILE 'D:\ORA10G_INF\ORADATA\IASDB\UNDOTBS_02.dbf' SIZE 10M
REUSE AUTOEXTEND ON;

and I got the followig error.

ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: 'D:\ORA10G_INF\ORADATA\IASDB\UNDOTBS01.DBF'

0
 
durga_manthaAuthor Commented:
Thanks for the help, I would open another thread if need to if I cannot resolve it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.