ORA-39083 object type type failed to create with error impdp

Today I was trying to import a schema dump using datapump utility and I got below mentioned error during the import :-

Error Message:
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:

CREATE TYPE "AMIT"."TEST_TYPE"   OID '8E21EF4C1F6EF167E04011AC99643EB0' IS TABLE OF VARCHAR2(40);

Cause :- 

Object Identifier has to be unique in a database. Above statement uses OID of old schema to create object in new schema.

Solution :-

1. Rerun the failed sql like below 

CREATE TYPE "AMIT"."TEST_TYPE"  TABLE OF VARCHAR2(40);

2. Best Solution is to use transform=oid:n in impdp statement.

I hope this article helped you

Regards,
Amit Rath

ORA-39700: database must be opened with UPGRADE option

Today I faced below error while restoring my current database to a new server. PFB :-

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-39700: database must be opened with UPGRADE option

Process ID: 21426

Session ID: 133 Serial number: 23

Cause :-

My current database version is 11.2.0.3.0

In new server database version is 11.2.0.4.0

So when I restored 11.2.0.3 backup to 11.2.0.4 database , it got successfully retored . But when I was trying to open the database got above error.

Solution :-

As in new server database version is higher(11.2.0.4.0), so we have to upgrade our restored database(11.2.0.3.0) to same higher version. As new server Oracle Home is at higher version, so dictionary of restored database also has to be at the same level.

Follow the below steps to start your database successfully in above kind of situations :-

$ sqlplus

SQL*Plus: Release 11.2.0.4.0 Production on Sat Oct 18 03:36:30 2014

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

Enter user-name: /as sysdba

Connected to an idle instance.

SQL> startup upgrade pfile='/ora_backup/amit/restore_test/initAMIT.ora'

ORACLE instance started.

Total System Global Area 1060585472 bytes

Fixed Size                  2260000 bytes

Variable Size             633340896 bytes

Database Buffers          402653184 bytes

Redo Buffers               22331392 bytes

Database mounted.

Database opened.

SQL>

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

DOC>#######################################################################

DOC>#######################################################################

DOC>

DOC>   The first time this script is run, there should be no error messages

DOC>   generated; all normal upgrade error messages are suppressed.

DOC>

DOC>   If this script is being re-run after correcting some problem, then

DOC>   expect the following error which is not automatically suppressed:

DOC>

DOC>   ORA-00001: unique constraint (<constraint_name>) violated

DOC>              possibly in conjunction with

DOC>   ORA-06512: at "<procedure/function name>", line NN

DOC>

DOC>   These errors will automatically be suppressed by the Database Upgrade

DOC>   Assistant (DBUA) when it re-runs an upgrade.

DOC>

DOC>#######################################################################

DOC>#######################################################################

DOC>#

DOC>######################################################################

DOC>######################################################################

DOC>    The following statement will cause an "ORA-01722: invalid number"

DOC>    error if the user running this script is not SYS.  Disconnect

DOC>    and reconnect with AS SYSDBA.

DOC>######################################################################

DOC>######################################################################

DOC>#

no rows selected

DOC>######################################################################

DOC>######################################################################

DOC>    The following statement will cause an "ORA-01722: invalid number"

DOC>    error if the database server version is not correct for this script.

DOC>    Perform "ALTER SYSTEM CHECKPOINT" prior to "SHUTDOWN ABORT", and use

DOC>    a different script or a different server.

DOC>######################################################################

DOC>######################################################################

DOC>#

no rows selected

..

.

.

..

.

Oracle Database 11.2 Post-Upgrade Status Tool           10-18-2014 04:38:30

.

Component                               Current      Version     Elapsed Time

Name                                    Status       Number      HH:MM:SS

.

Oracle Server

.                                         VALID      11.2.0.4.0  00:44:37

Oracle Real Application Clusters

.                                       INVALID      11.2.0.4.0  00:00:04

Oracle Workspace Manager

.                                         VALID      11.2.0.4.0  00:00:50

Final Actions

.                                                                00:00:01

Total Upgrade Time: 00:45:35

PL/SQL procedure successfully completed.

SQL>

SQL> SET SERVEROUTPUT OFF

SQL> SET VERIFY ON

SQL> commit;

Commit complete.

SQL>

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL>

SQL> DOC

DOC>#######################################################################

DOC>#######################################################################

DOC>

DOC>   The above sql script is the final step of the upgrade. Please

DOC>   review any errors in the spool log file. If there are any errors in

DOC>   the spool file, consult the Oracle Database Upgrade Guide for

DOC>   troubleshooting recommendations.

DOC>

DOC>   Next restart for normal operation, and then run utlrp.sql to

DOC>   recompile any invalid application objects.

DOC>

DOC>   If the source database had an older time zone version prior to

DOC>   upgrade, then please run the DBMS_DST package.  DBMS_DST will upgrade

DOC>   TIMESTAMP WITH TIME ZONE data to use the latest time zone file shipped

DOC>   with Oracle.

DOC>

DOC>#######################################################################

DOC>#######################################################################

DOC>#

SQL>

SQL> Rem Set errorlogging off

SQL> SET ERRORLOGGING OFF;

SQL>

SQL> REM END OF CATUPGRD.SQL

SQL>

SQL> REM bug 12337546 - Exit current sqlplus session at end of catupgrd.sql.

SQL> REM                This forces user to start a new sqlplus session in order

SQL> REM                to connect to the upgraded db.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

  
This script will run for almost an hour. kindly wait for script to complete.

Once the script completed , database is down as you can see above. Kindly start the database with normal option.

SQL> startup pfile='/ora_backup/amit/restore_test/initAMIT.ora'

 ORACLE instance started.

Total System Global Area 1060585472 bytes

Fixed Size                  2260000 bytes

Variable Size             633340896 bytes

Database Buffers          402653184 bytes

Redo Buffers               22331392 bytes

Database mounted.

Database opened.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

--------- --------------------

AMIT     READ WRITE

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0      Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

Run the utlrp script to check details regarding invalid objects.

SQL> @?/rdbms/admin/utlrp

TIMESTAMP

--------------------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_BGN  2014-10-18 05:18:42

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  2014-10-18 05:23:46

DOC> The following query reports the number of objects that have compiled

DOC> with errors.

DOC>

DOC> 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

-------------------

                  0

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

Function created.

  

PL/SQL procedure successfully completed.

  

Function dropped.

  

PL/SQL procedure successfully completed.

Database opened successfully after upgrade.

I hope this article helped you.

Regards,

Amit Rath