Situation
------------------
There is some confusion, but I'll try to explain...
It's necessary to import tablespace, where dumpfile and datafile of tablespace is not from one set.
(original datafile, which prepared to import, was lost and database, from which might to run export again, also was lost (control files, redo files and datafiles were rewritten), but accidentally datafile, which previously imported from export dump, not rewritten). There are no any backups or copyes of interested datafile. There were no structural and changes in the schema in exported tablespace or in tablespace.
Solution
-----------------
So, we have database which know nothing about dafafile from dump, dumpfile from exp set and dafafile from previous state database (which contains different from dumpfile scn values).
Import uses procedures from package sys.dbms_plugts (from dumpfile)
-- import begins
EXECTRP sys.dbms_plugts.beginImport ('10.2.0.5.0',152,'2000',13,'Linux x86 64-bit',2304718,2942799951,1,0,0,0);
EXECTRP sys.dbms_plugts.checkCompType('COMPATSG','10.2.0.5.0');
-- check that user exists
EXECTRP sys.dbms_plugts.checkUser('XXX');
-- plugging tablespace and datafile
EXECTRP sys.dbms_plugts.beginImpTablespace('XXXX',37,'SYS',1,0,16384,1,1236469596676,1,2147483645,4,64,4,0,1,0,4,2517399282,1,33,8657726773,NULL,287,3813661688,NULL,NULL);
EXECTRP sys.dbms_plugts.checkDatafile(NULL,2517399282,22,6400,37,27,4194302,6400,1236469277014,1236469596676,1236469119757,113246210,NULL,NULL,NULL);
.................
-- import ends
EXECTRP sys.dbms_plugts.endImport;
In order to perform import its necessary to correct scn values in dumpfile, anyway error will occur
ORA-19721: Cannot find datafile with absolute file number 22 in tablespace XXXX
Need to correct :
- beginImpTablespace (clean_scn,1236469596676) ;
- checkDatafile(creation_scn,1236469277014;checkpoint_scn(coincide with beginImpTablespace.clean_scn,1236469596676);reset_scn,1236469119757)
To figure out scn value, use bbed:
1.
BBED> set filename 'FILE'
2.
BBED> map
File: FILE
Block: 1 Dba:0x00000000
------------------------------------------------------------
Data File Header
struct kcvfh, 676 bytes @0
ub4 tailchk @16380
3.
BBED> p kcvfh
struct kcvfh, 676 bytes @0
....................................
struct kcvfhcrs, 8 bytes @100
ub4 kscnbas @100 0xe38a4aac
ub2 kscnwrp @104 0x011f
....................................
struct kcvfhrls, 8 bytes @116
ub4 kscnbas @116 0xe387d20b
ub2 kscnwrp @120 0x011f
....................................
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0xe3d23120
ub2 kscnwrp @488 0x011f
....................................
Here:
kcvfhcrs - creation_scn
kcvfhrls - reset_scn
kcvfhckp - checkpoing_scn
scn evaluated as kscnwrp+kscnbas and after that transform to decimal number :
creation_scn : 011fe38a4aac ---> 1236473105068
reset_scn : 011fe387d20b ---> 1236472943115
checkpoint_scn : 011fe3d23120 ---> 1236477817120
4.
Edit dumpfile with new scn values
EXECTRP sys.dbms_plugts.beginImpTablespace('XXXX',37,'SYS',1,0,16384,1,1236477817120,1,2147483645,4,64,4,0,1,0,4,2517399282,1,33,8657726773,NULL,287,3813661688,NULL,NULL);
EXECTRP sys.dbms_plugts.checkDatafile(NULL,2517399282,22,6400,37,27,4194302,6400,1236473105068,1236477817120,1236472943115,113246210,NULL,NULL,NULL);
5.
Run imp
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in CL8MSWIN1251 character set and AL16UTF16 NCHAR character set
import server uses RU8PC866 character set (possible charset conversion)
export client uses RU8PC866 character set (possible charset conversion)
. importing XXX's objects into XXX
. . importing table ...
.......................
Import terminated successfully without warnings.
6.
change tablespace status
SQL> alter tablespace xxxx read write ;
Tablespace altered.
7.
That's all
In other situations, it may be necessary change DBID, number of blocks and other parameters.
