ORA-01552 非系統表空間不能使用系統回滾段處理

wuweilong發表於2012-07-10
                 ORA-01552: cannot use system rollback segment for...錯誤解決
    
故障現象:
     今天本要做資料庫的不完全恢復,但是在準備環境的時候需要建立幾張表做為恢復用的基表。在建立表的時候
就報瞭如下一段錯誤:
13:56:03 SQL> create table wwl001 (id number,name varchar(12));
create table wwl001 (id number,name varchar(12))
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'WWL';
!!!! 非系統表空間'WWL'不能使用系統回滾段
問題原因:
  我昨天做了由於磁碟損壞導致資料庫災難的恢復,可能是由於恢復的時候引數檔案修改了,所以才導致成回滾段
變成了手動管理模式,我們可以看看如下步驟:
詳情:
1、建立表,非系統表空間不能使用回滾段
13:56:03 SQL> create table wwl001 (id number,name varchar(12));
create table wwl001 (id number,name varchar(12))
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'WWL';
2、檢視回滾段管理方式,為手動管理
13:57:24 SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
3、檢視回滾段狀態
13:57:23 SQL> select segment_name, tablespace_name, status from dba_rollback_segs;
SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
SYSTEM                         SYSTEM                         ONLINE
_SYSSMU1$                      UNDOTBS1                       OFFLINE
_SYSSMU2$                      UNDOTBS1                       OFFLINE
_SYSSMU3$                      UNDOTBS1                       OFFLINE
_SYSSMU4$                      UNDOTBS1                       OFFLINE
_SYSSMU5$                      UNDOTBS1                       OFFLINE
_SYSSMU6$                      UNDOTBS1                       OFFLINE
_SYSSMU7$                      UNDOTBS1                       OFFLINE
_SYSSMU8$                      UNDOTBS1                       OFFLINE
_SYSSMU9$                      UNDOTBS1                       OFFLINE
_SYSSMU10$                     UNDOTBS1                       OFFLINE
11 rows selected.
4、針對現象修改undo_management引數為auto即可。
14:08:06 SQL> alter system set undo_management=auto scope=spfile;
System altered.
14:17:51 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
14:18:22 SQL> startup
ORACLE instance started.
Total System Global Area  100663296 bytes
Fixed Size                  1217884 bytes
Variable Size              88083108 bytes
Database Buffers            8388608 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
5、檢視回滾段狀態
14:18:55 SQL> select segment_name, tablespace_name, status from dba_rollback_segs;
SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
SYSTEM                         SYSTEM                         ONLINE
_SYSSMU1$                      UNDOTBS1                       ONLINE
_SYSSMU2$                      UNDOTBS1                       ONLINE
_SYSSMU3$                      UNDOTBS1                       ONLINE
_SYSSMU4$                      UNDOTBS1                       ONLINE
_SYSSMU5$                      UNDOTBS1                       ONLINE
_SYSSMU6$                      UNDOTBS1                       ONLINE
_SYSSMU7$                      UNDOTBS1                       ONLINE
_SYSSMU8$                      UNDOTBS1                       ONLINE
_SYSSMU9$                      UNDOTBS1                       ONLINE
_SYSSMU10$                     UNDOTBS1                       ONLINE
11 rows selected.
6、我們再執行剛才的建表語句,非常好,可以建立表了。
14:19:51 SQL> create table wwl001 (id number,name varchar(12));
Table created.
 
 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/20674423/viewspace-735073/,如需轉載,請註明出處,否則將追究法律責任。

相關文章