ORA-01552 While Creating Undo Tablespace

刘泽宇

现象：
While creating undo tablespace or Rollback segment ORA-01552 is received.

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
Changes
Undo tablespace was offline dropped and the database was open after a fake recovery.

原因：
CDC (Change Data Capture) trigger is enabled

10046 trace will show that the create undo tablespace is failing due to CDC trigger.

处理方法：
Disable the CDC trigger and then undo tablespace can be created successfully.

To disable the CDC trigger follow the below steps.
Set the following in the INIT.ORA then restart the database:

_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;