Export Import Issues

IMP-00019: row rejected due to ORACLE error 1

IMP-00003: ORACLE error 1 encountered

ORA-00001: unique constraint (APPLSYS.FND_CONC_PROG_ONSITE_INFO_U1) violated

ORA-06512: at "APPS.FND_PROG_ONSITE_INSERT", line 4

ORA-04088: error during execution of trigger 'APPS.FND_PROG_ONSITE_INSERT'

Solution:

We have dropped the index FND_CONC_PROG_ONSITE_INFO_U1 and import the table FND_CONCURRENT_PROGRAMS and finally create the index.

SQL> Drop index FND_CONC_PROG_ONSITE_INFO_U1;

Index dropped.

dmas1> imp applsys/apps file=/temppi/refresh/mad/MAD4tables2011-01-21.dmp fromuser=APPLSYS touser=APPLSYS TABLES=FND_CONCURRENT_PROGRAMS  commit=y ignore=Y log=/temppi/refresh/mad/MAD4tables2011-01-21.dmp.dmas.import.log4_ssi_1

Import: Release 10.2.0.3.0 - Production on Fri Jan 21 17:51:43 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
export client uses UTF8 character set (possible charset conversion)
. importing APPLSYS's objects into APPLSYS
. . importing table      "FND_CONCURRENT_PROGRAMS"       9841 rows imported
Import terminated successfully without warnings.

SQL> select count(*) from APPLSYS.FND_CONCURRENT_PROGRAMS;

  COUNT(*)
----------
      9841

SQL> CREATE INDEX "APPLSYS"."FND_CONC_PROG_ONSITE_INFO_U1" ON "APPLSYS"."FND_CONC_PROG_ONSITE_INFO" ("PROGRAM_APPLICATION_ID", "CONCURRENT_PROGRAM_ID") PCTFREE 0 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT) TABLESPACE  "APPS_TS_TX_IDX" ;

Index created.

Issues: 

IMP-00058: ORACLE error 1552 encountered
ORA-01552: cannot use system rollback segment for non-system tablespace 'APPS_TS_SEED'
Import terminated successfully with warnings.

We tried to execute the import command with below command and got the error.

dmas1> imp applsys/apps file=/temppi/refresh/mad/MAD4tables2011-01-21.dmp fromuser=APPLSYS touser=APPLSYS TABLES=FND_USER  commit=y ignore=Y CONSTRAINTS=N indexes=N
log=/temppi/refresh/mad/MAD4tables2011-01-21.dmp.dmas.import.log4_ssi

Import: Release 10.2.0.3.0 - Production on Fri Jan 21 14:20:22 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
export client uses UTF8 character set (possible charset conversion)
. importing APPLSYS's objects into APPLSYS
. . importing table                     "FND_USER"
IMP-00058: ORACLE error 1552 encountered
ORA-01552: cannot use system rollback segment for non-system tablespace 'APPS_TS_SEED'
Import terminated successfully with warnings.

Then accordign to DOC ID:OERR: ORA 1552 cannot use system rollback segment for non-system tablespace (Doc ID 18951.1), we have created a new rollback segment and made it online and tried the import again.

SQL> create rollback segment ssi_test TABLESPACE APPS_UNDOTS1;

Rollback segment created.

SQL> select SEGMENT_NAME,OWNER,STATUS,TABLESPACE_NAME from DBA_ROLLBACK_SEGS where SEGMENT_NAME='SSI_TEST';

SEGMENT_NAME                   OWNER  STATUS
------------------------------ ------ ----------------
TABLESPACE_NAME
------------------------------
SSI_TEST                       SYS    OFFLINE
APPS_UNDOTS1

SQL> alter rollback segment ssi_test online;

Rollback segment altered.

SQL> select SEGMENT_NAME,OWNER,STATUS,TABLESPACE_NAME from DBA_ROLLBACK_SEGS where SEGMENT_NAME='SSI_TEST';

SEGMENT_NAME                   OWNER  STATUS
------------------------------ ------ ----------------
TABLESPACE_NAME
------------------------------
SSI_TEST                       SYS    ONLINE
APPS_UNDOTS1

We got unique constraint (APPLSYS.FND_USER_U1) violated error while again import.

IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (APPLSYS.FND_USER_U1) violated

Also we tried with option   CONSTRAINTS=N indexes=N  , but same error reoccured.

SO we plan to truncate the FND_USER table after taking the backup(fnd_user_bkp_210111) and tried the import , it was successfull.

SQL> SQL> create table fnd_user_bkp_210111 as select * from fnd_user;

Table created.

SQL> select count(*) from fnd_user;

  COUNT(*)
----------
       452

SQL> select count(*) from fnd_user_bkp_210111;

  COUNT(*)
----------
       452

SQL> truncate table fnd_user;

Table truncated.

dmas1> imp applsys/apps file=/temppi/refresh/mad/MAD4tables2011-01-21.dmp fromuser=APPLSYS touser=APPLSYS TABLES=FND_USER  commit=y ignore=Y

log=/temppi/refresh/mad/MAD4tables2011-01-21.dmp.dmas.import.log4_ssi

Import: Release 10.2.0.3.0 - Production on Fri Jan 21 15:32:15 2011

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
export client uses UTF8 character set (possible charset conversion)
. importing APPLSYS's objects into APPLSYS
. . importing table                     "FND_USER"        452 rows imported
Import terminated successfully without warnings.

SQL> select count(*) from fnd_user;

  COUNT(*)
----------
       452