出现ORA-01552: cannot use system rollback segment

最新推荐文章于 2024-02-28 17:45:58 发布

转载最新推荐文章于 2024-02-28 17:45:58 发布 · 242 阅读

文章标签：

本文介绍了Oracle数据库中遇到的无法使用系统回滚段的问题及其解决过程。通过调整回滚段配置并重启系统，成功解决了ORA-01552错误。此外，还涉及了控制文件记录保留时间参数的展示。

问题：
1 SQL> insert into test values(3);
insert into test values(3)
            *
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

2 看alert_ORCL.log日志：
Thu May 8 19:16:04 2008
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Thu May 8 19:16:05 2008
Successfully onlined Undo Tablespace 1.
Thu May 8 19:16:05 2008
SMON: enabling tx recovery
Thu May 8 19:16:05 2008
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
         This condition can occur when a backup controlfile has
         been restored. It may be necessary to add files to these
         tablespaces. That can be done using the SQL statement:

         ALTER TABLESPACE ADD TEMPFILE

         Alternatively, if these temporary tablespaces are no longer
         needed, then they can be dropped.
           Empty temporary tablespace: TEMP
*********************************************************************
Database Characterset is WE8ISO8859P1
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=21, OS id=8211
Thu May 8 19:16:11 2008
Completed: ALTER DATABASE OPEN

3 查回滚段表
SQL> select segment_name,status from dba_rollback_segs;

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

11 rows selected.

4 SQL> select us#,name,status$ from undo$;

       US# NAME                              STATUS$
---------- ------------------------------ ----------
         0 SYSTEM                                  3
         1 _SYSSMU1$                               5
         2 _SYSSMU2$                               5
         3 _SYSSMU3$                               5
         4 _SYSSMU4$                               5
         5 _SYSSMU5$                               5
         6 _SYSSMU6$                               5
         7 _SYSSMU7$                               5
         8 _SYSSMU8$                               5
         9 _SYSSMU9$                               5
        10 _SYSSMU10$                              5

       US# NAME                              STATUS$
---------- ------------------------------ ----------
        11 _SYSSMU11$                              1
        12 _SYSSMU12$                              1
        13 _SYSSMU13$                              1
        14 _SYSSMU14$                              1
        15 _SYSSMU15$                              1
        16 _SYSSMU16$                              1
        17 _SYSSMU17$                              1
        18 _SYSSMU18$                              1
        19 _SYSSMU19$                              1
        20 _SYSSMU20$                              1

21 rows selected.

状态为5的有10行，对应上面的dba_rollback_segs表
但是这里状态是需要恢复

查看数据库启动是用spfile还是pfile文件
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
如果为空则是从pfile启动，有值就是从spfile启动的！

6   编辑pfile文件：/u01/app/oracle/product/10.2.0/db_1/dbs/initORCL.ora

将*.undo_tablespace='UNDOTBS1'改成*.undo_tablespace='UNDOTBS2'

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 268435456 bytes
Fixed Size                  1218868 bytes
Variable Size              88082124 bytes
Database Buffers          171966464 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2

7   然后把整个系统重启一下就没出现问题了。