Oracle ORA-01552 While Creating Undo Tablespace

Posted by PDSERVICE on Mar 23, 2016 In

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线： 13764045638 QQ号:47079569 邮箱：service@parnassusdata.com

oerr ora 1552
01552, 00000, "cannot use system rollback segment for non-system tablespace '%s'"
// *Cause: Tried to use the system rollback segment for operations involving
// non-system tablespace. If this is a clone database then this will
// happen when attempting any data modification outside of the system
// tablespace. Only the system rollback segment can be online in a
// clone database.
// *Action: Create one or more private/public segment(s), shutdown and then
// startup again. May need to modify the INIT.ORA parameter
// rollback_segments to acquire private rollback segment. If this is
// a clone database being used for tablspace point in time recovery
// then this operation is not allowed. If the non-system tablespace
// has AUTO segment space management, then create an undo tablespace.

症状

创建撤销表空间或回滚段时，收到ORA-01552。

create undo tablespace undotbs2 datafile ‘/dev/vx/rdsk/test/undotbs1’ size 3069m
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01552: cannot use system rollback segment for non-system tablespace ‘TESTING’
ORA-06512: at line 2

CREATE ROLLBACK SEGMENT R1 TABLESPACE SYSTEM
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01552: cannot use system rollback segment for non-system tablespace TESTING’
ORA-06512: at line 2

变化

撤销表空间被离线删除，假恢复后，数据库被打开。

原因

CDC (Change Data Capture) trigger 被启用

10046 trace 表明因为CDC trigger，创建撤销表空间失败。

解决方案

Disable the CDC trigger and then undo tablespace can be created successfully.
要禁用CDC trigger，遵循以下步骤。
在INIT.ORA中进行以下设置，然后重启数据库:

_system_trig_enabled=false

SQL> conn / as sysdba
SQL> ALTER TRIGGER sys.cdc_alter_ctable_before DISABLE;
SQL> ALTER TRIGGER sys.cdc_create_ctable_after DISABLE;
SQL> ALTER TRIGGER sys.cdc_create_ctable_before DISABLE;
SQL> ALTER TRIGGER sys.cdc_drop_ctable_before DISABLE;
一旦撤销表空间创建成功，CDC triggers可被启用，系统应通过_system_trig_enabled=true重启。

You are here

Oracle ORA-01552 While Creating Undo Tablespace