Step by step - How to resolve ORA-01555 from the EXPDP

Scenario - we have around 2 TB of database and daily export job is scheduled to run at 12 AM EST. But it is failing with the below error. There are multiple occurrence of this error. I am just putting out one to show the exact error.

ORA-31693: Table data object "RDR"."REGW_EXTRACT" 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 10 with nam"_SYSSMU10_3196945471$" too small 

So we need to heck the few things:

1.      Check the total duration of the export job.

2.      Find the undo retention of the database.

3.      Find the undo table space size

4.      Find the job running timing

Solution:

1.      Change the undo_retention to the total time the export is taking. But remember to add atlease 30 mins more. Suppose export is taking 6 hours then keep the undo_retention to atleast 6:30 hours. Mostly it will resolve the issue.

2.      Also make sure we have enough space on the undo table space.

3.      Also make sure to run the export in non-business hours.

WARNING: Subscription for node down event still pending

SYMPTOMS

We are receiving the following warning messages in the listener log file constantly:

'WARNING: Subscription for node down event still pending'

CHANGES

This may be a new installation or a recent upgrade to 11g or newer.

CAUSE

These messages are related to the Oracle TNS Listener's default subscription to the Oracle Notification Service (ONS). In a non-RAC environment it is recommended to disable this subscription.   This feature was introduced in Oracle 10g.

SOLUTION

Set the following parameter in the listener.ora:

SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name>=OFF

Where <listener_name> should be replaced with the actual listener name configured in the

LISTENER.ORA file.

SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name> parameter is to be placed by itself on an empty line.

It will be necessary to restart or reload the listener following the addition of this parameter.

This will prevent the messages from being written to the log file and may also prevent the TNS

Please Note: Setting SUBSCRIBE_FOR_NODE_DOWN_<listener_name> to OFF disables a necessary RAC functionality. The above workaround is recommended only for non-RAC environments.

The issue may be present in all 11g and newer installations.