Wednesday, October 5, 2011

ORA-01555: snapshot too old during datapump export

0 comments
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.

Tuesday, October 4, 2011

Trigger on database to capture ORA errors

1 comments


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

 

ORA-BLOG. Copyright 2008 All Rights Reserved Revolution Two Church theme by Brian Gardner Converted into Blogger Template by Bloganol dot com