This discussion is archived
10 Replies Latest reply: Dec 10, 2012 10:12 AM by Mark Malakanov (user11181920) RSS

Import into oracle from existing datafile

825199 Newbie
Currently Being Moderated
Hi,
I have an existing Oracle 10gr2 datafile ( and redolog , control file .... anything from Oracle 10gr2 folder in files, but since the OS was reinstalled, oracle is dead), and I want to get some of the data from it into Oracle 11g. So is there any method so I can do this?

Many Thanks
Nguyen.
  • 1. Re: Import into oracle from existing datafile
    sb92075 Guru
    Currently Being Moderated
    user1152556 wrote:
    Hi,
    I have an existing Oracle 10gr2 datafile ( and redolog , control file .... anything from Oracle 10gr2 folder in files, but since the OS was reinstalled, oracle is dead), and I want to get some of the data from it into Oracle 11g. So is there any method so I can do this?

    Many Thanks
    Nguyen.
    Only if you are willing to pay Oracle Much Money to apply their Data Unloader against the datafiles.

    Otherwise the standard response is to restore from the most recent backup.
  • 2. Re: Import into oracle from existing datafile
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    You don't have any backup RMAN_backup ?
  • 3. Re: Import into oracle from existing datafile
    Aman.... Oracle ACE
    Currently Being Moderated
    Though the methods suggested by Sb and Osama are correct but it would be far easier to get teh data if you would tell us that the dump of the files that you have , is a consistent one or inconsistent? If it is consistent you can just use that to create a new db from these files and get the data.

    Aman....
  • 4. Re: Import into oracle from existing datafile
    825199 Newbie
    Currently Being Moderated
    No I haven't any backup , that's the problem. All the files is consistent, how can I create new db from these files? Is it possible with Oracle 11g, or I have to do that with 10gr2?
  • 5. Re: Import into oracle from existing datafile
    Aman.... Oracle ACE
    Currently Being Moderated
    user1152556 wrote:
    No I haven't any backup , that's the problem. All the files is consistent, how can I create new db from these files? Is it possible with Oracle 11g, or I have to do that with 10gr2?
    If you know and are sure that the files are consistent, you can just create a pfile from any other database, edit it to give proper locations of the files , change the SID with your db and then try to start it. If the files are consistent, it would start up. There is nothing like that it's a feature of 11g .

    Aman....
  • 6. Re: Import into oracle from existing datafile
    EdStevens Guru
    Currently Being Moderated
    user1152556 wrote:
    Hi,
    I have an existing Oracle 10gr2 datafile ( and redolog , control file .... anything from Oracle 10gr2 folder in files, but since the OS was reinstalled, oracle is dead), and I want to get some of the data from it into Oracle 11g. So is there any method so I can do this?

    Many Thanks
    Nguyen.
    You say the OS was re-installed. Did you also reinstall Oracle?
    You say Oracle 10gr2. That is just a marketing term. DBAs deal in actual release numbers for a reason. A release number would look like 10.2.0.1.0.

    IF those files were saved after a clean shutdown of the database ... you may have hope. But first you will need to bring them up under the same version as they were originally running ... which looks like some form of 10.2.
  • 7. Re: Import into oracle from existing datafile
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    You can try what Aman has advised. But add into PFILE
    COMPATIBLE=10.2.0.2
  • 8. Re: Import into oracle from existing datafile
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    I think it would likely complain that the files are 'old' and you would need to start in upgrade mode - but that may be possible - it could be you just have to run the 11g upgrade against the database. The data may be accesible enough though for you to get what you need without doing that. If you already have a backup of the files just give it a go - if it doesn;t work you can always restore and look into getting 10.2 reinstalled.

    Cheers,
    Harry
  • 9. Re: Import into oracle from existing datafile
    EdStevens Guru
    Currently Being Moderated
    user1152556 wrote:
    Hi,
    I have an existing Oracle 10gr2 datafile ( and redolog , control file .... anything from Oracle 10gr2 folder in files, but since the OS was reinstalled, oracle is dead), and I want to get some of the data from it into Oracle 11g. So is there any method so I can do this?

    Many Thanks
    Nguyen.
    Before using these files for ANYTHING - make sure you make yourself an additional copy of them you can get back to.
  • 10. Re: Import into oracle from existing datafile
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    >
    I think it would likely complain that the files are 'old' and you would need to start in upgrade mode - but that may be possible - it could be you just have to run the 11g upgrade against the database. The data may be accesible enough though for you to get what you need without doing that.
    >
    I believe so.
    look into getting 10.2 reinstalled.
    to OP. You can install 10.2 without uninstalling 11g.
    You can have both instances running. However in Windows the last installed Oracle home will be added to PATH first. So I'd recommend you to stay in proper %ORACLE_HOME%\BIN when you launch sqlplus exp or other utils.

    Take export dump with exp 10g. Import it with imp 11g.

    Edited by: user11181920 on Dec 10, 2012 1:11 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points