Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Transportable tablespace with missing datafile

Re: Transportable tablespace with missing datafile

From: Ken Payton <ken.payton_at_choicepointprg.net>
Date: Tue, 01 Mar 2005 14:04:53 -0500
Message-Id: <1109703893.6790.265.camel@kpaytonlap.na.choicepoint.net>


Scary thought.

I have editing my dump files in the past for other reasons, such as renaming a tablespace, but intellectual curiosity forced me to test this one and it actually worked.

Open your dump file in some binary file editor. I use "vi -b" on Redhat or Suse linux systems, I have not found this option on the version of vi installed on our Unix(Solaris) systems. Do not use vi alone, it will translate some binary characters into ASCII and upon saving will hose your dmp file.

Remove one from the 4th parameter of this line, this is the number of datafiles in this TEST tablespace. In this case stating there are only 3 files in the tablespace, not my original 4.

EXECTRP
sys.dbms_plugts.beginImpTablespace('TEST',57,'SYS',3,0,16384,1,12501998862,1,2147483645,64,64,64,0,1,0,64,2064877929,1,2,12501808311,NULL,0,0,NULL,NULL);

Remove the line corresponding to the missing datafile. 3rd parameter appears to be file_id. You might be able to detect this by which file imp is currently telling you is missing.

EXECTRP
sys.dbms_plugts.checkDatafile(NULL,2064877929,96,640,57,96,0,0,12501808335,12501998862,1,402653186,NULL,NULL,NULL);

import your tablespace excluding the missing file from the datafiles parameter.

I tested this on 8.1.7 and it worked. I was also able to read data until I hit the missing file. For my test I created a tablespace with 4 10MB datafiles. I then created a table that was 34MB in size ensuring I had data in each file. I dropped the tablespace and reimported the tablespace using the above instructions. The tablespace imported without problem and allowed select's against the table, although a select count caused a ORA-0600.

If you're looking for a solution to get the data back from the missing file you might try restoring the file from the backup, modifying the CheckDatafile's SCN to match that of the SCN in the file header. This would take a little more testing/digging but I would be willing to bet $5 it would work. Of course if the data is not worth $5 I wouldn't waste the effort.

Kenny

On Tue, 2005-03-01 at 13:14, scott.hutchinson_at_interact-analysis.com wrote:
> All,
>
> Does anyone know any tricks for forcing a transportable tablespace to "import"
> when a datafile is missing? I don't mind loosing the data within this
> datafile, but there are many other valuable tables in other datafiles within
> this tablespace.
>
> Due to an operational error, 1 file (out of 91) from a 2tb tablespace has been
> deleted after the metadata was exported. We have a cold backup of the
> datafiles from this tablespace only (not full DB) but the tablespace was NOT
> readonly at the point of the backup, and the database was restarted for the
> metadata export. This means we can not simply restore the missing file.
>
> The error we get on import is ORA-19722, and the oracle version is 9.2.0.5.
>
> Thanks,
> Scott.
>
> ::This message sent using the free Web Mail service from http://TheName.co.uk
> --
> http://www.freelists.org/webpage/oracle-l

-- 
Kenny Payton
Software Architect
Public Records Group, Boca Raton
Choicepoint, Inc.
ken.payton_at_choicepointprg.net
o: (770)752-4054
c: (561)926-4119
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Mar 01 2005 - 14:08:06 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US