ORA-01555: snapshot too old during datapump export

Datapump Export can fail with the following error when the parameter FLASHBACK_TIME is used

ORA-31693: Table data object "SCOTT"."EMPLOYEES" 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 271 with name "_SYSSMU271$" too small ORA-00604: error occurred at recursive SQL level 3 ORA-08180: no snapshot found based on specified time ORA-06512: at "SYS.TIMESTAMP_TO_SCN", line 1^@ ORA-00604: error occurred at recursive SQL level 3 ORA-08180: no snapshot found based on specified ti

The reason for this is the database parameter UNDO_RETENTION is set to low compared to the time set with FLASHBACK_TIME.

Example:

- export starts at 10:00 pm and ends at 00:00 am. Flashback time start at 10:00 pm of the same days when export starts.

Expdp may need 2 hours to complete and undo_retention is set to 2040 that means 34 minutes. So the information in undo is recycled.

Solution
Set UNDO_RETENTION to a value sufficient to get all info required by the flashback time.
Also consider increasing undo tablespace size.

Trigger on database to capture ORA errors

You might want to track ORA errors on your database so that whenever an oracle error comes,you may want the error code and error description to be logged into some table. This will be required when you see your application just exists saying some error but you did not see any ORA error in application log file or console.

In this cases, what you can do is write a trigger on the database server to log the error code, error statement and error description to a table when ORA error comes.

To implement this, first create a table which will hold the ORA error,statement and error description. This can be created in SYS schema.

CREATE TABLE stats$error_log (
        err_dt          TIMESTAMP,
        db_user         VARCHAR2(30),
        msg_stack       VARCHAR2(2000),
        sqltxt          VARCHAR2(1000))
tablespace users;

Now, create a trigger on the database server error.

CREATE OR REPLACE TRIGGER log_server_errors
  AFTER SERVERERROR
  ON DATABASE
DECLARE
          v_sqltext VARCHAR2(1000);
          nl_sqltext ora_name_list_t;
  BEGIN
          -- Capture entire error text
          FOR i IN 1 .. ora_sql_txt(nl_sqltext) LOOP
            v_sqltext := v_sqltext || nl_sqltext(i);
          END LOOP;
          INSERT INTO STATS$ERROR_LOG
          (err_dt, db_user, msg_stack, sqltxt)
          VALUES
          (systimestamp,
           sys.login_user,
           dbms_utility.format_error_stack, v_sqltext);
  END log_server_errors;
/

For further reference you can find.
Simple version:
http://www.adp-gmbh.ch/ora/sql/trigger/servererror.html

Slightly more fancy version:
http://blog.sydoracle.com/2011/02/using-oracles-servererror-to-catch.html

How to pin nodes in RAC

The following steps can be used to pin nodes in RAC.
Pinning a node means that the association of a node name with a node number is fixed. If a node is not pinned,node number may change if the lease expires while it is down. The lease of a pinned node never expires.

To find whether a node in a cluster is pinned,use the olsnodes command.

[grid@raclinux1 stage10g]$ olsnodes -t
raclinux1 Unpinned
raclinux2 Unpinned
 
Use the pin command to pin the nodes 

[root@raclinux1 bin]# ./crsctl pin css -n raclinux1
CRS-4664: Node raclinux1 successfully pinned.
[root@raclinux1 bin]#
 

[root@raclinux2 bin]# ./crsctl pin css -n raclinux2
CRS-4664: Node raclinux2 successfully pinned.
[root@raclinux2 bin]#
 
[grid@raclinux1 stage10g]$ olsnodes -t
raclinux1 Pinned 
raclinux2 Pinned