ORA-39083 and ORA-02304 during impdp or imp

ORA-39083: Object type TYPE failed to create with error:ORA-02304: invalid object identifier literal

Failing sql is:CREATE TYPE "ACCTEST"."SPLIT_TBL1"   OID 'DD944536D2EB453B8A965D8502B03C14' AS TABLE OF CLOB

Cause : Oracle was trying to import an object with OID which already existed in database. OID should be unique in a database.

Solution : I added the clause transform=OID:n in my impdp command.

========================================================================

SQL>

SQL> host impdp ACCTEST/log directory=imports dumpfile=2029012015-CORPORATE.DMP

remap_schema=CORPORATE:ACCTEST logfile=ACCTEST.txt full=y;

Import: Release 11.2.0.3.0 - Production on Thu Mar 5 15:46:13 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit

Production

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

Master table "ACCTEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "ACCTEST"."SYS_IMPORT_FULL_01":  ACCTEST/******** directory=imports dum

pfile=2029012015-CORPORATE.DMP remap_schema=CORPORATE:ACCTEST logfile=ACCTEST.tx

t full=y

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"ACCTEST" already exists

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/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC

ORA-39083: Object type TYPE failed to create with error:

ORA-02304: invalid object identifier literal

Failing sql is:

CREATE TYPE "ACCTEST"."SPLIT_TBL"   OID 'ECD98FAC59A9469F8E458BDC2567F955' AS TA

BLE OF VARCHAR2 (32767)

Solution:

host impdp ACCT/log directory=imports dumpfile=2029012015-CORPORATE.DMP remap_schema=CORPORATE:ACCT logfile=ACCTEST.txt transform=OID:n full=y;

SQL> host impdp ACCT/log directory=imports dumpfile=2029012015-CORPORATE.DMP rem

ap_schema=CORPORATE:ACCT logfile=ACCTEST.txt transform=OID:n full=y;

Import: Release 11.2.0.3.0 - Production on Thu Mar 5 15:55:18 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit

Production

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

Master table "ACCT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "ACCT"."SYS_IMPORT_FULL_01":  ACCT/******** directory=imports dumpfile=

2029012015-CORPORATE.DMP remap_schema=CORPORATE:ACCT logfile=ACCTEST.txt transfo

rm=OID:n full=y

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"ACCT" already exists

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/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC

Processing object type SCHEMA_EXPORT/DB_LINK

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "ACCT"."MVW_COSTOFGOODS_V2"                 7.541 GB 51376158 rows

. . imported "ACCT"."STOCKVALUE"                         5.582 GB 35892288 rows

. . imported "ACCT"."MVW_STOCKVALUESUM"                  5.081 GB 31559509 rows

. . imported "ACCT"."MVW_COSTOFGOODS_V2_130614"          4.568 GB 31653770 rows

. . imported "ACCT"."SALESINVOICEDTL"                    4.388 GB 34245812 rows

. . imported "ACCT"."SALESINVOICEHDR"                    4.020 GB 14193074 rows

. . imported "ACCT"."TB_COSTOFGOODS2_2014"               2.337 GB 15613672 rows

. . imported "ACCT"."SALESINVOICEDTL0404"                2.163 GB 17440925 rows

. . imported "ACCT"."MVW_STOCKBATCHVALUE"                1.964 GB 20554589 rows

How to change DBNAME and DBID in Oracle database Using Normal & RMAN Method

(i)Changing the DBID in normal method:

    If u clone the database , the new DBID same as like the source DB. If  u want to change the DBID following below methods useful.

This is my new clone database DBID. Same as source DBID, now i want to change new DBID for clone database.

Step 1:

Step 3:

This script checks user for the new NAME and DBID.

(i)DBNAME change

var old_name varchar2(20)

var old_dbid number

var new_name varchar2(20)

var new_dbid number

exec select name, dbid -

       into :old_name,:old_dbid -

       from v$database

print old_name

accept new_name prompt "Enter the new Database Name:" 

Step 4:

(ii) DBID Change

    If u run the script , it will ask DBID enter the new DBID.

      accept new_dbid prompt "Enter the new Database ID:"

Step 5:

Make the changes of new DBNAME and new DBID.

exec :new_name:='&&new_name'

exec :new_dbid:=&&new_dbid

Step 6:

  The following below script run- new DBID changes in contolfile level all database files.

set serveroutput on

exec dbms_output.put_line('Convert '||:old_name||  -

     '('||to_char(:old_dbid)||') to '||:new_name|| -

     '('||to_char(:new_dbid)||')')

declare

  v_chgdbid   binary_integer;

  v_chgdbname binary_integer;

  v_skipped   binary_integer;

begin

  dbms_backup_restore.nidbegin(:new_name,

       :old_name,:new_dbid,:old_dbid,0,0,10);

  dbms_backup_restore.nidprocesscf(

       v_chgdbid,v_chgdbname);

  dbms_output.put_line('ControlFile: ');

  dbms_output.put_line('  => Change Name:'

       ||to_char(v_chgdbname));

  dbms_output.put_line('  => Change DBID:'

       ||to_char(v_chgdbid));

  for i in (select file#,name from v$datafile)

     loop

     dbms_backup_restore.nidprocessdf(i.file#,0,

       v_skipped,v_chgdbid,v_chgdbname);

     dbms_output.put_line('DataFile: '||i.name);

     dbms_output.put_line('  => Skipped:'

       ||to_char(v_skipped));

     dbms_output.put_line('  => Change Name:'

       ||to_char(v_chgdbname));

     dbms_output.put_line('  => Change DBID:'

       ||to_char(v_chgdbid));

     end loop;

  for i in (select file#,name from v$tempfile)

     loop

     dbms_backup_restore.nidprocessdf(i.file#,1,

       v_skipped,v_chgdbid,v_chgdbname);

     dbms_output.put_line('DataFile: '||i.name);

     dbms_output.put_line('  => Skipped:'

       ||to_char(v_skipped));

     dbms_output.put_line('  => Change Name:'

       ||to_char(v_chgdbname));

     dbms_output.put_line('  => Change DBID:'

       ||to_char(v_chgdbid));

     end loop;

  dbms_backup_restore.nidend;

end;

/

O/P:-

Step 7:

Change db_name and open the database.

Bef u can open the database, you have to change db_name parameter in the spfile. Everything is done open the database in open resetlogs mode.

shutdown immediate;

startup mount;

alter database open resetlogs;

startup force;

create spfile from pfile=’e:\initcorporate.ora’

startup force; 

========================================================================

(ii) Changing the DBID in RMAN method:

Step 1. Identify the DBID of the database

SQL> select dbid from v$database;

DBID

———-

1320285073

Step 2. Shutdown the database in normal mode

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Step 3.Start the database to mount phase

SQL> startup mount

ORACLE instance started.

Total System Global Area    250667625 bytes

Fixed Size                    2365080 bytes

Variable Size                73274908 bytes

Database Buffers             56721608 bytes

Redo Buffers                   5876423 bytes

Database mounted.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production

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

Step 4. Execute the nid command

[oracle@orcl ~]$ which nid

/u01/app/oracle/product/11.2.0/dbhome_1/bin/nid

[oracle@orcl ~]$ nid target=/

DBNEWID: Release 11.2.0.3.0 – Production on Mon Jan 23 11:45:22 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to database RC (DBID=1320285073)

Connected to server version 11.2.0

Control Files in database:

/u01/app/oracle/oradata/control01.ctl

/u01/app/oracle/oradata/control02.ctl

Change database ID of database RC? (Y/[N]) => Y

Proceeding with operation

Changing database ID from 1320285073 to 1323314655

Control File /u01/app/oracle/oradata/control01.ctl – modified

Control File /u01/app/oracle/oradata/control02.ctl – modified

Datafile /u01/app/oracle/oradata/system01.dbf– dbid changed

Datafile /u01/app/oracle/oradata/sysaux01.dbf – dbid changed

Datafile /u01/app/oracle/oradata/undotbs01.dbf– dbid changed

Datafile /u01/app/oracle/oradata/temp01.dbf – dbid changed

Instance shut down

Database ID for database RC changed to 1323314655

All previous backups and archived redo logs for this database are unusable.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database ID.

DBNEWID – Completed succesfully.

Step 5. Open the DB in mount stage

[oracle@orcl ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 23 12:10:45 2015

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

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area     250667625 bytes

Fixed Size                    2365080 bytes

Variable Size                73274908 bytes

Database Buffers             56721608 bytes

Redo Buffers                   5876423 bytes

Database mounted.

Step 6. Open the database with resetlog option

SQL> alter database open resetlogs ;

Database altered.

SQL>

Step 7. Identify the new changed DBID

SQL> select dbid from v$database;

DBID

———-

1323314655

=============================================================================================

Temp file wise usage:

Temp file wise usage:

set lines 152

col FILE_NAME for a30

col TABLESPACE_NAME for a10

col BYTES for a12

col MAXBYTES for a12

col AUT format a8

col STATUS for a10

select file_name,tablespace_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible,status from dba_temp_files;

Temp tablespace usage:

Temp tablespace usage:

set lines 152

col FreeSpaceGB format 999.999

col UsedSpaceGB format 999.999

col TotalSpaceGB format 999.999

col host_name format a30

col tablespace_name format a30

select tablespace_name,

(free_blocks*8)/1024/1024 FreeSpaceGB,

(used_blocks*8)/1024/1024 UsedSpaceGB, (total_blocks*8)/1024/1024 TotalSpaceGB,

i.instance_name,i.host_name

from gv$sort_segment ss,gv$instance i where ss.tablespace_name in (select tablespace_name from dba_tablespaces where contents='TEMPORARY') and

i.inst_id=ss.inst_id;

Tablespace Datafile wise free space:

Tablespace Datafile wise free space:

set lines 152

col TABLESPACE format a10

col FILE_NAME format a35

col BYTES format format a10

col MAXBYTES format a12

col AUT format a8

col STATUS format a12

select TABLESPACE_NAME,FILE_NAME,bytes/1024/1024,maxbytes/1024/1024,autoextensible,status from dba_data_files;

Particular Tablespace Usage Report:

Particular Tablespace Usage Report:

select tablespace_name, tbs_mb, free_mb,

round(free_mb/tbs_mb*100,2) pct_free, round(tbs_mb*15/100-free_mb,2) Est_mb from

( select a.tablespace_name ,sum(bytes)/1024/1024 tbs_mb,

nvl((select sum(bytes)/1024/1024 from dba_free_space where tablespace_name=a.tablespace_name),0) free_mb

from dba_data_files a group by tablespace_name)

where free_mb/tbs_mb*100 <=10 and tablespace_name ='&tablespace_name';

Total Tablespace space usage – Scripts

Total Tablespace space usage – Scripts

select tbs.tbs,total_mb tbs_mb, nvl(free_mb,0) free_mb, round(nvl(free_mb,0)/total_mb*100,2) pct_free,

total_mb*25/100 -nvl(free_mb,0) Est_mb    from

(select tablespace_name tbs,round(sum(bytes)/1024/1024,2) free_mb

from dba_free_space   group by tablespace_name) free_tbs,

(select tablespace_name tbs,round(sum(bytes)/1024/1024,2) total_mb

from dba_data_files   group by tablespace_name) tbs

where tbs.tbs=free_tbs.tbs(+) AND round(nvl(free_mb,0)/total_mb*100,2)<=10;