Solved

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

Posted on 2006-06-09
51
1,040 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:durga_mantha
  • 28
  • 18
  • 4
  • +1
51 Comments
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 16871484
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
 

Author Comment

by:durga_mantha
ID: 16871632
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
 

Author Comment

by:durga_mantha
ID: 16871960
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
 
LVL 34

Expert Comment

by:johnsone
ID: 16872114
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16872131
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
 

Author Comment

by:durga_mantha
ID: 16872169
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16872196
>>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
 

Author Comment

by:durga_mantha
ID: 16872260
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16872312
issue:

show parameters undo

what do you see?
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 16872317
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16872327
you might need to restart your server using:

startup pfile='<your init.ora location';
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16872357
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16872444
also you could use this to see rollback segments available in your db:

select segment_name,status,tablespace_name from dba_rollback_segs
/
0
 
LVL 19

Accepted Solution

by:
actonwang earned 500 total points
ID: 16872477
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16872489
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
 

Author Comment

by:durga_mantha
ID: 16872537
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16872600
command line:

sqlplus " sys/pass@hoststring as sysdba"
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 16872602
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
 

Author Comment

by:durga_mantha
ID: 16872682
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16872722
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16872753
also make sure you have unto tablespace named 'UNDOTBS' using:

select tablespace,contents,status
from dba_tablespaces
/
0
 

Author Comment

by:durga_mantha
ID: 16872768
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
 

Author Comment

by:durga_mantha
ID: 16872797
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16872817
did you run :

recover database

after you restore your database (assuming you are in archivelog mode).
0
 

Author Comment

by:durga_mantha
ID: 16872830
Once I issued this
alter system set undo_tablespace=UNDOTBS_01;

it started working.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 19

Expert Comment

by:actonwang
ID: 16872839
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
 

Author Comment

by:durga_mantha
ID: 16872864
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16872932
NEEDS RECOVERY

Contains data from uncommitted transactions that cannot be rolled back (because the data files involved are inaccessible) or is corrupted.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16872976
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16873036
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
 

Author Comment

by:durga_mantha
ID: 16873063
How do I change the mode to ARCHIVE mode?
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16873087
>> 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
 
LVL 19

Expert Comment

by:actonwang
ID: 16873116
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16873122
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16873131
you need to think it through about archivelog mode and your backup/recovery strategies before you decide to do it.
0
 

Author Comment

by:durga_mantha
ID: 16873254
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16873295
is this file online?
0
 

Author Comment

by:durga_mantha
ID: 16873305
How do I know that? Is there a command I can issue and tell?
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16873317
use this to see status:

select file#,status,name from v$datafile where name like '%UNDOTBS01.DBF';
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16873330
sometimes it is also due to hardware failure or permission on the file.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16873358
if it is offline, use:

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


to bring it online.
0
 

Author Comment

by:durga_mantha
ID: 16873369
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16873459
>>D:\ORA10G_INF\ORADATA\IASDB\UNDOTBS01.DBF
??

is this your NEWLY created undo tablespace datafile or already existed?
0
 

Author Comment

by:durga_mantha
ID: 16873491
Already existing old one. Can I make it to point to a newly created one?
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16873556
You already changed your undo tablespace.

restart your applicatoin and retry.
0
 

Author Comment

by:durga_mantha
ID: 16873656
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
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 16873691
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16873693
>>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
 
LVL 19

Expert Comment

by:actonwang
ID: 16873719
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
 

Author Comment

by:durga_mantha
ID: 16873741
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
 

Author Comment

by:durga_mantha
ID: 16873890
Thanks for the help, I would open another thread if need to if I cannot resolve it.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now