SQL Apply fails With LOGSTDBY Status: ORA-01552: Cannot Use System Rollback Segment For Non-sys

Last updated on MAY 16, 2017

Applies to:

Goal

SQL apply process fail with error ORA-01552



LOGMINER: End mining logfile: /u02/ARCH_PRIMARY/1_92_940861240.dbf
LOGSTDBY Analyzer process P003 started with pid=22 OS id=4038
LOGSTDBY Apply process P008 started with pid=27 OS id=4048
LOGSTDBY Apply process P005 started with pid=24 OS id=4042
LOGSTDBY Apply process P006 started with pid=25 OS id=4044
LOGSTDBY Apply process P004 started with pid=23 OS id=4040
LOGSTDBY Apply process P007 started with pid=26 OS id=4046
Tue Apr 11 12:44:17 CEST 2017
krvxerpt: Errors detected in process 20, role builder.
Tue Apr 11 12:44:17 CEST 2017
krvxmrs: Leaving by exception: 604
Tue Apr 11 12:44:17 CEST 2017
Errors in file /u02/bdump/orcl10stb_p001_4034.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'
ORA-06512: at line 1
LOGSTDBY status: ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'
ORA-06512: at line 1
Tue Apr 11 12:44:17 CEST 2017
LogMiner process death detected
logminer process death detected, exiting logical standby
LOGSTDBY Analyzer process P003 pid=22 OS id=4038 stopped
Tue Apr 11 12:44:18 CEST 2017
Errors in file /u02/bdump/orcl10stb_lsp0_4063.trc:
ORA-12801: error signaled in parallel query server P001
ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'
ORA-06512: at line 1
========================

On standby
#######################################################
SQL> select segment_name, status,tablespace_name from dba_rollback_segs;

SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
SYSTEM ONLINE SYSTEM
_SYSSMU10$ OFFLINE UNDOTBS1
_SYSSMU9$ OFFLINE UNDOTBS1
_SYSSMU8$ OFFLINE UNDOTBS1
_SYSSMU7$ OFFLINE UNDOTBS1
_SYSSMU6$ OFFLINE UNDOTBS1
_SYSSMU5$ OFFLINE UNDOTBS1
_SYSSMU4$ OFFLINE UNDOTBS1
_SYSSMU3$ OFFLINE UNDOTBS1
_SYSSMU2$ OFFLINE UNDOTBS1
_SYSSMU1$ OFFLINE UNDOTBS1

11 rows selected.

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string

SQL> select TABLESPACE_NAME,CONTENTS from dba_tablespaces where TABLESPACE_NAME <> 'SYSTEM';

TABLESPACE_NAME CONTENTS
------------------------------ ---------
UNDOTBS1             UNDO
SYSAUX                 PERMANENT
TEMP                     TEMPORARY
USERS                   PERMANENT

Solution