ORA-31693 ORA-02354: error in exporting/importing data ORA-01555: snapshot too old

Error:

ORA-31693: Table data object "MIS"."MASTER_MISDB" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 4 with name "_SYSSMU4_4250244621$" too small


[oracle@said]$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 26 10:15:18 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show parameter undo

NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled      boolean FALSE
undo_management      string AUTO
undo_retention      integer 7200
undo_tablespace      string UNDOTBS1
SQL>

SQL> select max(maxquerylen) from v$undostat;

MAX(MAXQUERYLEN)
----------------
  204662
 
Solution:
 
1. Modify the current UNDO_RETENTION for the database:
 

SQL> ALTER SYSTEM SET UNDO_RETENTION = 205000 scope=both sid='*';

SQL> show parameter undo

NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled      boolean FALSE
undo_management      string AUTO
undo_retention      integer 205000
undo_tablespace      string UNDOTBS1

2. Perform the export again.

Step by Step Enable Archive Log in Oracle 12c

SQL> archive log list
Database log mode        No Archive Mode
Automatic archival        Disabled
Archive destination        USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     244249
Current log sequence

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> show parameter db_name

NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name      string miscdb
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1.0100E+10 bytes
Fixed Size     3721224 bytes
Variable Size 1778386936 bytes
Database Buffers 8287944704 bytes
Redo Buffers    29831168 bytes
Database mounted.
SQL>


Space Assign for Fast Recovery Area:

SQL> alter system set db_recovery_file_dest_size = 200G;

System altered.

Choose the destination:

SQL> alter system set db_recovery_file_dest='/u02/app/oracle/FRA/miscdb';

System altered.

SQL> ALTER DATABASE ARCHIVELOG;

Database altered.

SQL>

SQL> alter database open;

Database altered.

SQL> show parameter recovery

NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest      string /u02/app/oracle/FRA/miscdb
db_recovery_file_dest_size      big integer 200G
recovery_parallelism      integer 0
SQL>

ORA-00600: internal error code, arguments: [4193], [31580], [31585], [], [], [], [], [], [], [], [], []

Problem: The following error coming from alert log

Errors in file /d01/app/oracle/diag/rdbms/bacdbpr/bacdb/trace/bacdb_m001_26439.trc  (incident=1129768) (PDBNAME=CDB$ROOT):
ORA-00600: internal error code, arguments: [4193], [31580], [31585], [], [], [], [], [], [], [], [], []
Incident details in: /d01/app/oracle/diag/rdbms/bacdbpr/bacdb/incident/incdir_1129768/bacdb_m001_26439_i1129768.trc

Solution:

idle@SYS> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE

idle@SYS>


1. Create pfile from spfile to edit
SQL> Create pfile='/export/home/oracle/pfile081018.ora' from spfile;

2. Shutdown the instance

3. set the following parameters in the pfile /tmp/initsid.ora
    undo_management = manual
    event = '10513 trace name context forever, level 2'

4. SQL>>startup restrict pfile='/export/home/oracle/pfile081018.ora'

startup restrict pfile='/export/home/oracle/pfile081018.ora'

5. SQL>select tablespace_name, status, segment_name from dba_rollback_segs where status != 'OFFLINE';

idle@SYS> select tablespace_name, status, segment_name from dba_rollback_segs where status != 'OFFLINE';

TABLESPACE_NAME                STATUS           SEGMENT_NAME
------------------------------ ---------------- ------------------------------
SYSTEM                         ONLINE           SYSTEM
UNDOTBS1                       ONLINE           _SYSSMU33_748006928$
UNDOTBS1                       ONLINE           _SYSSMU34_2282707298$
UNDOTBS1                       ONLINE           _SYSSMU35_3873732304$
UNDOTBS1                       ONLINE           _SYSSMU36_2146687221$
UNDOTBS1                       ONLINE           _SYSSMU37_2465056056$
UNDOTBS1                       ONLINE           _SYSSMU38_941211847$
UNDOTBS1                       ONLINE           _SYSSMU39_113148174$
UNDOTBS1                       ONLINE           _SYSSMU40_2428414494$
UNDOTBS1                       ONLINE           _SYSSMU41_3210694757$
UNDOTBS1                       ONLINE           _SYSSMU42_1725270345$

.
..
.
.
.
.


This is critical - we are looking for all undo segments to be offline - System will always be online.

If any are 'PARTLY AVAILABLE' or 'NEEDS RECOVERY' - Please open an issue with Oracle Support or update the current SR.  There are many options from this moment and Oracle Support Analyst can offer different solutions for the bad undo segments.

If all offline then continue to the next step

6. Create new undo tablespace - example
SQL>create undo tablespace datafile size 2000M;

CREATE UNDO TABLESPACE UNDO2 DATAFILE   '+DATA/bacdb/undo2tbs01_dbf' SIZE 10240M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;

7. Drop old undo tablespace
SQL>drop tablespace including contents and datafiles;

drop tablespace UNDOTBS1 including contents and datafiles;


8. SQL>shutdown immediate;

9 SQL>startup nomount;  --> Using your Original spfile

10. Modify the spfile with the new undo tablespace name

SQL> Alter system set undo_tablespace = '' scope=spfile;

Alter system set undo_tablespace = 'UNDO2' scope=spfile;

11. SQL>shutdown immediate;

12. SQL>startup;  --> Using spfile