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 laterOracle 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 |