datapump: ORA-02304: invalid object identifier literal

During datapump import, there were errors with object ID (OID). This probably coming from another user who created the datapump export with transform=OID:y. In order to get the datapump import going, simply insert the clause of transform=OID:n since the default is transform=OID:y

Failing sql is:
CREATE TYPE "TRD"."SETOF_XORP_LOCKED_RESOURCES_SP"   OID 'B0DDDACF8F8G1137E0400D0AC4181435' AUTHID DEFINER AS OBJECT(moref VARCHAR2(254),vc_id RAW(16),cpu_required NUMBER(10,0),mem_required NUMBER(10,0))
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE TYPE "TRD"."SETOF_XKI_ID_MO_DEPLOY_COUNT"   OID 'B0DDDACF8F961137E9400D0AC4181435' AUTHID DEFINER AS OBJECT(vs_id RAW(16),root_dir_id NUMBER(10,0),ms_count NUMBER(10,0))
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE TYPE "TRD"."SETOF_MO_ID"   OID 'B0DDDACF8FA81137E0400D0AC4181435' AUTHID DEFINER AS OBJECT(ms_id RAW(16))
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE TYPE "TRD"."SETOFMSKOCKEDRXSOURCESSPSET"   OID 'B0DDDACF8F9C1137E0405D0AC4181435' AS TABLE OF setof_ms_locked_resources_sp
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE TYPE "TRD"."SETOFRPLTCKEDEESOURCESSPSET"   OID 'B0DDD6ACF8FA01137E000D0AC4181435' AS TABLE OF setof_rp_locked_resources_sp
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal


impdp TXD@txorcl DIRECTORY=tx_dump dumpfile=tx_export_6300414.dmp REMAP_SCHEMA=TXDU:TXD remap_tablespace=TXDU_DATA:TXD_DATA TRANSFORM=oid:n

Oracle Troubleshoot: Time Drift

The following can be caused by Snapshot, SDRS if host is slow enough and lacking of resources or if the VAPP gone into suspend mode in cloud.


TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    nt main err code: 505


Warning: VKTM detected a time drift.

Time drifts can result in an unexpected behavior such as time-outs. Please check trace file for more details.

IMP-00031 fromuser touser

import and export from original schema to another dedicated schema. Often, I have no idea what people gave me. I was asking for datapump and what I got was something else with no export log.

imp SRM1/passwordfile=/backup/srmadmin.exp full=n buffer=2000000 commit=y ignore=y log=/backup/srmadmin.log


Import: Release 11.2.0.1.0 - Production on Tue Nov 11 09:23:37 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
Warning: the objects were exported by SYSTEM, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
export client uses UTF8 character set (possible charset conversion)
export server uses UTF8 NCHAR character set (possible ncharset conversion)
IMP-00031: Must specify FULL=Y or provide FROMUSER/TOUSER or TABLES arguments
IMP-00000: Import terminated unsuccessfully

imp SRM1/password file=/backup/srmadmin.exp full=n buffer=2000000 commit=y ignore=y log=/backup/srmadmin.log fromuser=SRMADMIN touser=SRM1

imp instead of impdp was provided causing ORA-39000

In this case, I was told a "datapump" export file was provided. However, when I performed the datapump import, it failed with ORA-39000. From my best knowledge
only couple things could trigger this error. There are either corrupted datapump import file or user performed an import with "imp" instead of datapump. Datapump file
corruption usually resulted of zip being trimmed during ftp or uploading.


Importing with "impdp" will trigger the following error.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39143: dump file "/backup/RD_20140217-2.dmp" may be an original export dump file


imp RDU file=/home/oracle/downloads/CHGPR_20140217.dmp full=n buffer=2000000 commit=y ignore=y log=/home/oracle/downloads/imp_RDU_20140217.log

lsnrctl hung . Unable to start or check on status.

./pcmScheduler|10411|01/25/12 15:24:24|       Raw data: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

./pcmScheduler|10411|01/25/12 15:24:24|     Error With Sql Return

./pcmScheduler|10411|01/25/12 15:24:24|       Raw data:

./pcmScheduler|10411|01/25/12 15:24:24|     Error With Sql Return

./pcmScheduler|10411|01/25/12 15:24:24|       Raw data:

./pcmScheduler|10411|01/25/12 15:24:24|     Error With Sql Return

./pcmScheduler|10411|01/25/12 15:24:24|       Raw data:       FROM pcm_schedule

./pcmScheduler|10411|01/25/12 15:24:24|     Error With Sql Return

./pcmScheduler|10411|01/25/12 15:24:24|       Raw data:            *

./pcmScheduler|10411|01/25/12 15:24:24|     Error With Sql Return

./pcmScheduler|10411|01/25/12 15:24:24|       Raw data: ERROR at line 2:

./pcmScheduler|10411|01/25/12 15:24:24|     Error With Sql Return

./pcmScheduler|10411|01/25/12 15:24:24|       Raw data: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

./pcmScheduler|10411|01/25/12 15:24:24|     Error With Sql Return

./pcmScheduler|10411|01/25/12 15:24:24|       Raw data:

./pcmScheduler|10411|01/25/12 15:24:24|     Error With Sql Return

./pcmScheduler|10411|01/25/12 15:24:24|       Raw data:

./pcmScheduler|10411|01/25/12 15:24:25|     Error With Sql Return

./pcmScheduler|10411|01/25/12 15:24:25|       Raw data:       FROM pcm_schedule

acshc:ccuser[live]/u/ccuser 104> lsnrctl status

LSNRCTL for HPUX: Version 10.1.0.5.0 - Production on 25-JAN-2012 15:16:32

Copyright (c) 1991, 2004, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

...hang indefinitely right here ... It turns out, 2 listeners of the same name were running. Not sure how that's possible but it does. Killed both and restarted the lsnrctl .. everything worked fine.

Script to track Oracle DB locks.

-- db locks with session and serial

select b.inst_id, 'alter system kill session ''' || b.sid || ',' || b.serial# || ''';' 

from gv$locked_object a , gv$session b, dba_objects c where b.sid = a.session_id 

and a.object_id = c.object_id;

-- for node1 

select b.inst_id, 'alter system kill session ''' || b.sid || ',' || b.serial# || ''';' 

from gv$locked_object a , gv$session b, dba_objects c where b.sid = a.session_id 

and a.object_id = c.object_id and b.inst_id=1;

-- transaction 2PC problem

select 'execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(' || LOCAL_TRAN_ID || ')' from dba_2pc_pending;

-- getting sql from the db locks

select inst_id, sql_text from gv$sql where sql_id in (select sql_id from gv$locked_object a , gv$session b, 

dba_objects c where b.sid = a.session_id and a.object_id = c.object_id);

--- revised 

--- tracking sid, serial and OS pid.

select p.spid,  b.username, a.sql_text from gv$sql a, gv$session b, gv$process p
where b.sql_address = a.address AND b.paddr = p.addr
  and b.sql_hash_value= a.hash_value and b.sid = &sid and b.serial# = '&serial';

-- revised- use when necessar - results can be cluttered. 

-- getting sid and serial and sql. compare with db locks sql and analyze if killing is necessary.

set lines 190

select b.sid, b.serial#,d.sql_text

from gv$locked_object a , gv$session b, dba_objects c, 

gv$sql d where b.sid = a.session_id 

and a.object_id = c.object_id and d.sql_id=b.sql_id;

-- objects that locked up.

set lines 220

col object_name format a30
col username format a8
col machine format a9
col spid format a6

col instance format 999
col object_owner format a11
col locked_mode format a10
col logon_time format a25

SELECT b.inst_id as instance,

       b.session_id AS sid,

       s.serial#,
       NVL(b.oracle_username, '(oracle)') AS username,
       a.owner AS object_owner,
       a.object_name,
       Decode(b.locked_mode, 0, 'None',
                             1, 'Null (NULL)',
                             2, 'Row-S (SS)',
                             3, 'Row-X (SX)',
                             4, 'Share (S)',
                             5, 'S/Row-X (SSX)',
                             6, 'Exclusive (X)',
                             b.locked_mode) locked_mode,
       b.os_user_name, s.machine, s.program, p.spid, s.logon_time
FROM   dba_objects a,
       gv$locked_object b, gv$session s, gv$process  p
WHERE  a.object_id = b.object_id
and    s.sid (+)= b.session_id
and    s.inst_id (+) = b.inst_id
and    s.paddr = p.addr
and    s.inst_id = p.inst_id

ORDER BY 1, 2, 3, 4;

-- tracking lock with OS PID

set long 500000

set lines 250

SET LINESIZE 80 HEADING OFF FEEDBACK OFF
SELECT
 RPAD('USERNAME : ' || s.username, 80) ||
 RPAD('OSUSER   : ' || s.osuser, 80) ||
 RPAD('PROGRAM  : ' || s.program, 80) ||
 RPAD('SPID     : ' || p.spid, 80) ||
 RPAD('SID      : ' || s.sid, 80) ||
 RPAD('SERIAL#  : ' || s.serial#, 80) ||
 RPAD('MACHINE  : ' || s.machine, 80) ||
 RPAD('TERMINAL : ' || s.terminal, 80)||

 RPAD ('STATUS  : ' || S.STATUS, 80) ||
 RPAD('SQL TXT  : ' || q.sql_text, 3000)  

 FROM gv$session s,
      gv$process p,
      gv$sql q
 WHERE s.paddr = p.addr
 AND   p.spid  = '&PID_FROM_OS'
 AND   s.sql_address        = q.address
 AND   s.sql_hash_value = q.hash_value;

-- a user causing lock ups

set lines 220

col object_name format a30
col username format a8
col machine format a9
col spid format a6

col instance format 999
col object_owner format a11
col locked_mode format a10
col logon_time format a25

SELECT b.inst_id as instance,

       b.session_id AS sid,

       s.serial#,
       NVL(b.oracle_username, '(oracle)') AS username,
       a.owner AS object_owner,
       a.object_name,
       Decode(b.locked_mode, 0, 'None',
                             1, 'Null (NULL)',
                             2, 'Row-S (SS)',
                             3, 'Row-X (SX)',
                             4, 'Share (S)',
                             5, 'S/Row-X (SSX)',
                             6, 'Exclusive (X)',
                             b.locked_mode) locked_mode,
       b.os_user_name, s.machine, s.program, p.spid, s.logon_time
FROM   dba_objects a,
       gv$locked_object b, gv$session s, gv$process  p
WHERE  a.object_id = b.object_id
and    s.sid (+)= b.session_id
and    s.inst_id (+) = b.inst_id
and    s.paddr = p.addr
and    s.inst_id = p.inst_id

and machine name like '&machinenamehere'

and wait_class != 'Idle' ;

ORDER BY 1, 2, 3, 4;

Oracle: my RMAN recovery note on Oracle 10gR1

. oraenv

comm

sqlplus > alter system set cluster_database=false scope=spfile;

sqlplus > startup force nomount;

# to look at preview summary

RMAN>

run{

    set until time "to_date('16-OCT-2010 04:00:00','DD-MON-YYYY HH24:MI:SS')";

    restore database preview summary;

}

# to force restore

## longest part

RMAN>

run{

    set until time "to_date('16-OCT-2010 04:00:00','DD-MON-YYYY HH24:MI:SS')";

    restore force database;

}

## recover database

RMAN>

run{

    set until time "to_date('16-OCT-2010 04:00:00','DD-MON-YYYY HH24:MI:SS')";

    restore force database;

    recover database;

}

RMAN> alter database open resetlogs;

database opened