Datapump import not working after flashback database

Flashbacked a RAC database to an old restore point. Post flashback datapump import (impdp) stopped working with following error

[test-srv-02] $ impdp  directory=EXT_DIR dumpfile=user2.dmp logfile=imp_user2.log

Import: Release 10.2.0.4.0 – 64bit Production on Friday, 15 April, 2011 8:25:26

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining Scoring Engine
and Real Application Testing options

UDI-00008: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 2772
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 3886
ORA-06512: at line 1

Few checks done

1. Checked if any of the components are invalid in the database

SQL> select comp_name, status, version from dba_registry;
COMP_NAME                                          STATUS      VERSION
-------------------------------------------------- ----------- ----------
Oracle Database Catalog Views                      VALID       10.2.0.4.0
Oracle Database Packages and Types                 VALID       10.2.0.4.0
Oracle Real Application Clusters                   VALID       10.2.0.4.0
Oracle Workspace Manager                           VALID       10.2.0.4.3
JServer JAVA Virtual Machine                       VALID       10.2.0.4.0
Oracle XDK                                         VALID       10.2.0.4.0
Oracle Database Java Packages                      VALID       10.2.0.4.0
Oracle Expression Filter                           VALID       10.2.0.4.0
Oracle Data Mining                                 VALID       10.2.0.4.0
Oracle XML Database                                VALID       10.2.0.4.0
Oracle Rules Manager                               VALID       10.2.0.4.0
Oracle Enterprise Manager                          VALID       10.2.0.4.0
12 rows selected.

 
All the components are valid.

2. Checked if any of the specific objects are invalid

SQL> @?/rdbms/admin/utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2011-04-15 08:39:13
DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.

TIMESTAMP
----------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2011-04-15 08:39:31

PL/SQL procedure successfully completed.
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
                320
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
                          0

PL/SQL procedure successfully completed.

SQL> select object_name, object_type from dba_objects where
status ='INVALID' and owner = 'SYS';
no rows selected

But even after that, impdp  was failing with same error.  Found the solution in Mrtalink note – ORA-31623 When Submitting a Datapump Job [ID 308388.1]

connect / as sysdba
startup restrict
alter system set cluster_database = false scope=spfile;
shutdown immediate
startup upgrade
alter system set cluster_database = true scope=spfile;
shutdown immediate
startup

After this I could use the impdp utility

[test-srv-02] $ impdp  directory=EXT_DIR dumpfile=user2.dmp
logfile=imp_user2.log
Import: Release 10.2.0.4.0 - 64bit Production on Friday,
15 April, 2011 8:51:34
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 -
64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
Scoring Engine
and Real Application Testing options
Master table "XYZ"."SYS_IMPORT_SCHEMA_05" successfully loaded/unloaded
Starting "XYZ"."SYS_IMPORT_SCHEMA_05":  /******** directory=EXT_DIR
dumpfile=user2.dmp logfile=imp_user2.log
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Advertisements
This entry was posted in Oracle Data Pump and tagged . Bookmark the permalink.

One Response to Datapump import not working after flashback database

  1. Pingback: Oracle RAC DBA « Center Point for Oracle DBA & Kuwait Info

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s