Today i ran into a good example for really really bad error messages: Imagine a tablespace with 720 datafiles (approx 22 TB in total size) which should be transported via transportable tablespace. For this you created a plain, new 11.2 database and you are trying to attach the transportable tablespace:
[oracle@ora1 ~]$ impdp system/manager parfile=import.par Import: Release 11.2.0.3.0 - Production on Wed Sep 26 19:55:19 2012 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 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** parfile=import.par Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK ORA-39123: Data Pump transportable tablespace job aborted ORA-01240: too many data files to add in one command Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 19:55:22
“Too many datafiles”… well 720 are really a lot but within the limits oracle imposes. On MOS there is only one matching note:
“The problem here is that during TTS plugin phase we generate a single redo record containing the name of all the datafiles in the operation. If there are too many files in the transportable set then the redo record that needs to be generated goes past the MAX size redo record, resulting in the error ORA-1240. The limiting factor is the size of the NAMES of the files, not the physical size of the files themselves.”
I can tell you shortening the path names did not help at all. But while developing a testcase i found the error. It is the value of DB_FILES which is by default set to 200… by adding 720 more datafiles you will go way beyond that limit.
So let´s see what happens if we increse the parameter before the import:
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 SQL> show parameter db_files NAME TYPE VALUE -------------------------- db_files integer 200 SQL> alter system set db_files=2000 scope=spfile; System altered. SQL> startup force ORACLE instance started. Total System Global Area 801701888 bytes Fixed Size 2232640 bytes Variable Size 230690496 bytes Database Buffers 566231040 bytes Redo Buffers 2547712 bytes Database mounted. Database opened. SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ora1 ~]$ impdp system/manager parfile=import.par Import: Release 11.2.0.3.0 - Production on Wed Sep 26 19:56:19 2012 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 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** parfile=import.par Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" completed with 1 error(s) at 19:56:27
Problem solved :-)