My Oracle Support Banner

SQL Apply fails With LOGSTDBY Status: ORA-01552: Cannot Use System Rollback Segment For Non-sys (Doc ID 2256030.1)

Last updated on APRIL 03, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.5 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

Goal

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample schema(s), Public Documentation delivered with an Oracle database product or other training material. Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

For the purposes of this document, the following are fictitious names to be used as examples to describe the procedure:

Archive location: /u02/ARCH_PRIMARY/
Tablespace name:  UNDOTBS1

*********************

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 /<path>/<sid>_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 /<path>/<sid>_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

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.