Oracle data file format
Henry Lau
I am using windows 2000 and oracle 8.1.6 Is any one know why my database
files format is ORA ? what is the different between ORA and DBF??
Is it possible to restore the DBF to Unix Oracle database??
Henry
RSH
solution, from this message. Could you elaborate a little bit?
Thanks.
RSH.
"Henry Lau" <hen...@asper.com.hk> wrote in message
news:adpchk$7rk$1...@hfc.pacific.net.hk...
Niall Litchfield
use the GUI) for data files is .ORA, in others it s .DBF So the answer to
the second question is "there is no difference between datafiles on a
windows platform even if the extension is different" . You however cannot
transfer datafiles from one OS to another because then they really are
different, the only way that I would consider for transferring data from
Windows to Unix would be export/import.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
"RSH" <RSH_O...@worldnet.att.net> wrote in message
news:uVWL8.25031$LC3.1...@bgtnsc04-news.ops.worldnet.att.net...
Mark Malakanov
CMD>ren filename.ora filename.dbf
and
SQL>alter database rename file 'FILENAME.ORA' to 'FILENAME.DBF';
You can choose any extention you like!
There is so many unixes. Probably it is possible to put window's
datafiles files into INTEL Linux, and may be it will even work,
but nobody can guarantee that, and Oracle Corp will not support.
use exp and imp to move databases across platforms.
Henry Lau
I am running windows 2000 now. Actually, i execute the installation script
and provided by other. Then it will install and configure the database for
me. It works normally. i can do use the client and connect to that database.
The database files format is *.ora (data.ora, index.ora) not *.dbf.
(noramlly data.dbf, index,dbf) First, I want to ask what is the different in
data format between *.ora and *.dbf?
Henry
"RSH" <RSH_O...@worldnet.att.net> wrote in message
news:uVWL8.25031$LC3.1...@bgtnsc04-news.ops.worldnet.att.net...
Dusan Bolek
> You can easily reformat them by issuing command:
>
> CMD>ren filename.ora filename.dbf
> and
> SQL>alter database rename file 'FILENAME.ORA' to 'FILENAME.DBF';
>
> You can choose any extention you like!
>
> There is so many unixes. Probably it is possible to put window's
> datafiles files into INTEL Linux, and may be it will even work,
No, you definitely can't transfer files between platforms, even if
they're using the same CPUs. Internal structure of datafiles is
different on different platforms, you will not be able to mount
database.
Seems to me like Mr. Lau is the victim of Gates' syndrom, which
symptoms is fifed idea that extension of file has something common
with contents. :-)
I think that he should use as extension .HLD - Henry Laus' datafiles.
--
_________________________________________
Dusan Bolek, Ing.
Oracle team leader
Note: pages...@usa.net has been cancelled due to changes (maybe we
can call it an overture to bankruptcy) on that server. I'm still using
this email to prevent SPAM. Maybe one day I will change it and have a
proper mail even for news, but right now I can be reached by this
email.
Norman Dunbar
long though :o(
Just to add on the 'can I move a Windows datafile to Unix' question. I
did actually do something similar to that at one point. I FTP'd the
datafiles for an HP database to a Digital Alpha server, did all the bits
about renaming and creating controlfiles etc, and then tried 'startup'.
It got so far and failed with an error along the lines of 'not a valid
datafile'.
Being a very junior dba, I logged a TAR and got a swift reply which said
'Oracle doesn't support transfer of datafile between operating systems
because some have numbers stored as 'little endian' and others are 'big
endian' this causes instant corruption were it to be allowed. The only
cross platform method of getting a database is to EXPort and IMPort.'
They didn't laugh at me either :o)
HTH
Regards
Norman.
-------------------------------------
Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman...@LFS.co.uk
Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------
Jon Waterhouse
can tell the starter database is set up with .dbf extensions. When you add
any data files to table spaces the default extension is .ora.
"Henry Lau" <hen...@asper.com.hk> wrote in message
news:adpl3o$f5k$1...@hfc.pacific.net.hk...
Yong Huang
> datafiles files into INTEL Linux, and may be it will even work,
> but nobody can guarantee that, and Oracle Corp will not support.
I documented the success of reusing datafiles in WindowsNT and
Windows2000 at http://www.stormloader.com/yonghuang/computer/OracleIdiosyncrasies.html,
Item#9. You may say they're not really different OSes. Well, Microsoft
think they are. I have a feeling that the file system and platform
byte ordering matter more than the OS label.
But more interesting is the test of reusing the same datafile between
WindowsWhatever and Intel Linux (has to be Intel Linux, not Sparc e.g.
due to byte ordering). Can anyone do this test? If that succeeds which
I doubt, then we can try Windows and True64.
In any case, all these are unsupported. Somebody I know had a plan to
write a program to directly modify datafiles to support cross-platform
datafile move. Not sure about his progress. That's even more
un-supported!
Yong Huang
Jim Kennedy
Jim
"Yong Huang" <yon...@yahoo.com> wrote in message
news:b3cb12d6.02060...@posting.google.com...
Sybrand Bakker
<kennedy...@attbi.com> wrote:
>Bad idea. Just use export and import.
>Jim
>"Yong Huang" <yon...@yahoo.com> wrote in message
>news:b3cb12d6.02060...@posting.google.com...
>> But more interesting is the test of reusing the same datafile between
>> WindowsWhatever and Intel Linux (has to be Intel Linux, not Sparc e.g.
>> due to byte ordering). Can anyone do this test? If that succeeds which
>> I doubt, then we can try Windows and True64.
>>
>> In any case, all these are unsupported. Somebody I know had a plan to
>> write a program to directly modify datafiles to support cross-platform
>> datafile move. Not sure about his progress. That's even more
>> un-supported!
>>
>> Yong Huang
>
Not only that: unprofessional advice. Will Mr. Yong Huang assume
responsibility if someone following this advice screws up or does he
have a very good lawyer?
There is also no difference between 'unsupported' and 'even more
unsupported'. If I would know the e-mail address of the Oracle Legal
Affairs department I would forward this post so they can sue him.
Regards
Sybrand Bakker, Senior Oracle DBA
To reply remove -verwijderdit from my e-mail address
Pete Sharman
>
>Bad idea. Just use export and import.
Not just a bad idea. It simply won't work.
HTH. Additions and corrections welcome.
Pete
SELECT standard_disclaimer, witty_remark FROM company_requirements;
Rauf Sarwar
What I don't understand is why people like to experiment with their
databases when Oracle has provided tools like exp/imp to move
databases between platforms and versions. Shortest distance between
WinNT/2000 <--> Unix <--> Linux etc is via exp/imp. Any other route
will be at one's own risk.
Sean M
>
> What I don't understand is why people like to experiment with their
> databases when Oracle has provided tools like exp/imp to move
> databases between platforms and versions. Shortest distance between
> WinNT/2000 <--> Unix <--> Linux etc is via exp/imp. Any other route
> will be at one's own risk.
Maybe because exp/imp worked fine back when databases were 2 gigs, but
fall incredibly short in a world where terabyte databases are
commonplace? When it comes time to migrate your mission-critical 2 TB
database from HP/UX to Solaris, it'd be really nice if Oracle could
provide a datafile conversion utility. Oh, and so long as I'm dreaming,
I'd like to add the ability to convert archives on the fly so as to
create a standby database on a different platform to reduce the downtime
during a platform migration even further. :)
Regards,
Sean
Jeremiah Wilton
> What I don't understand is why people like to experiment with their
> databases when Oracle has provided tools like exp/imp to move
> databases between platforms and versions. Shortest distance between
> WinNT/2000 <--> Unix <--> Linux etc is via exp/imp. Any other route
> will be at one's own risk.
I think people experiment because they are curious and intelligent. I
think the goal of doing experiments like this is to increase knowledge
and unrestanding of computing architectures and data storage
internals. Every top-level Oracle person I know got where they are by
experimenting with unsupported procedures and poking their nose where
Oracle never intended.
This kind of violent response to simple experimentation just tells me
that there are a lot of uninnovative and self-satidfied DBAs still out
there.
Export is dreadfully slow compared to file copy. In any environment
with scale and availability commensurate with real commerce, the
downtime and unreliability associated with moving a database via
export/import is unacceptable. That is why our friend whose nickname
begins with "x$" wanted to build a cross-platform datafile muncher.
--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton
Jim Kennedy
"Jeremiah Wilton" <jwi...@speakeasy.net> wrote in message
news:Pine.LNX.4.44.020608...@grace.speakeasy.net...
> On 8 Jun 2002, Rauf Sarwar wrote:
>
> > What I don't understand is why people like to experiment with their
> > databases when Oracle has provided tools like exp/imp to move
> > databases between platforms and versions. Shortest distance between
> > WinNT/2000 <--> Unix <--> Linux etc is via exp/imp. Any other route
> > will be at one's own risk.
>
> I think people experiment because they are curious and intelligent. I
> think the goal of doing experiments like this is to increase knowledge
> and unrestanding of computing architectures and data storage
> internals. Every top-level Oracle person I know got where they are by
> experimenting with unsupported procedures and poking their nose where
> Oracle never intended.
>
> This kind of violent response to simple experimentation just tells me
> that there are a lot of uninnovative and self-satidfied DBAs still out
> there.
>
technical reasons. We are highly innovative, but knowledgable enough that
we try NOT to do things that are going to break. Also one wants to be very
careful with a production system - there is a database with valuable data
and we take the responsiblity seriosly and do not have so cavalier attitude
about it. On real systems we actually care about the data and are not so
quick to just try it; it might work.
Any tool that would allow you to copy and then "fix up" so it would work on
a cross platform environment would end up being no faster than export. Why?
The data blocks would each and everyone need to be read in one format and
written in another. It would have to take care of the situation where the
resulting write made the data larger than the block and create a chained
row. (hence creating a performance problem ) The problem goes beyond bin
and little endian systems, different systems have different byte alignment
and so forth.
There are other ways to do it other than export (eg sqlplus copy) or a link
and pl/sql code or java code. While it would be difficult to prove, export,
import is probably fastest on cross platform massive data migration.
If I had to migrate a large system with minimal down time I would do it
another way.
Jim
koert54
"Jeremiah Wilton" <jwi...@speakeasy.net> wrote in message
news:Pine.LNX.4.44.020608...@grace.speakeasy.net...
>
> > What I don't understand is why people like to experiment with their
> > databases when Oracle has provided tools like exp/imp to move
> > databases between platforms and versions. Shortest distance between
> > WinNT/2000 <--> Unix <--> Linux etc is via exp/imp. Any other route
> > will be at one's own risk.
>
> I think people experiment because they are curious and intelligent. I
> think the goal of doing experiments like this is to increase knowledge
> and unrestanding of computing architectures and data storage
> internals. Every top-level Oracle person I know got where they are by
> experimenting with unsupported procedures and poking their nose where
> Oracle never intended.
>
Agreed
> This kind of violent response to simple experimentation just tells me
> that there are a lot of uninnovative and self-satidfied DBAs still out
> there.
>
Agreed !
> Export is dreadfully slow compared to file copy. In any environment
> with scale and availability commensurate with real commerce, the
> downtime and unreliability associated with moving a database via
> export/import is unacceptable. That is why our friend whose nickname
> begins with "x$" wanted to build a cross-platform datafile muncher.
>
export/import in parallel is fast enough - http://pepi.sourceforge.net &
http://sourceforge.net/projects/pepi
Yong Huang
> > and unrestanding of computing architectures and data storage
>
>
> > This kind of violent response to simple experimentation just tells me
> > that there are a lot of uninnovative and self-satidfied DBAs still out
> > there.
> >
>
> Agreed !
[many lines snipped]
Just realized how far this thread has gone over the weekend. And I
realize that I didn't say clearly enough my message is not advice,
much less [non-]professional advice. It's just an experiment to
satisfy my curiosity, because sometimes I have spare time around
midnight. Thanks to Jememiah and koert54 for understanding.
To make this thread continue to be of technical rather than political
nature, I'd really want to know if someone has a clue why Oracle
doesn't allow us to move datafiles between Windows2000 and WindowsNT.
It's proved to be working fine, although not by rigorous testing. If
Oracle approves it, it may potentially save a lot of customers' time.
Yong Huang
Nuno Souto
(and I quote):
> To make this thread continue to be of technical rather than political
> nature, I'd really want to know if someone has a clue why Oracle
> doesn't allow us to move datafiles between Windows2000 and WindowsNT.
> It's proved to be working fine, although not by rigorous testing. If
> Oracle approves it, it may potentially save a lot of customers' time.
NTFS itself has changed between NT and W2K. There are some very
fundamental differences on how it allocates and references files. This
was done mostly for the benefit of sql server and to properly support
quotas, but of course it also affects Oracle.
So, I'd be vewy reluctant in moving files between the two. One never
knows with M$...
--
Cheers
Nuno Souto
nso...@optushome.com.au.nospam
Jeremiah Wilton
> Wrong. We actually have some idea of how the thing works and
> understand the technical reasons. We are highly innovative, but
> knowledgable enough that we try NOT to do things that are going to
> break.
That's fine. That's different than scaremongering.
> Also one wants to be very careful with a production system - there
> is a database with valuable data and we take the responsiblity
> seriosly and do not have so cavalier attitude about it. On real
> systems we actually care about the data and are not so quick to just
> try it; it might work.
Uh, yeah. Don't experiment on your production database !! ??
> Any tool that would allow you to copy and then "fix up" so it would
> work on a cross platform environment would end up being no faster
> than export. Why? The data blocks would each and everyone need to
> be read in one format and written in another. It would have to take
> care of the situation where the resulting write made the data larger
> than the block and create a chained row. (hence creating a
> performance problem ) The problem goes beyond bin and little endian
> systems, different systems have different byte alignment and so
> forth.
I think the most convincing argument here is the chained row problem.
I think you could write a program to do the rest faster than
export/import would work. Just think of the number of operations,
system calls, etc. hypothetically required for the two methods.
I bet despite these challenges we are going to see cross-platform
transportable tablespaces from Oracle in some upcoming version.
> There are other ways to do it other than export (eg sqlplus copy) or
> a link and pl/sql code or java code. While it would be difficult to
> prove, export, import is probably fastest on cross platform massive
> data migration.
There's no way to use import without logging, which costs a lot of
time for most I/O subsystems.
CTAS or insert as select across a DB link does a round trip per row!
So, I've done a bunch of these on terabyte and larger systems, and
there are a variety of approaches that work in a supported manner. I
would say that the fastest was is parallel unload/load using an OCI
program with array fetch and read ahead piped across a hagh-capacity
network in large chunks to sqlload in nologging format.
Then there are really sneaky methods like having an application
temporarily use both systems, and lazily migrate records as needed and
in off-hours idle time.
Anyway, good posting Jim, and an interesting conversation.
Niall Litchfield
news:3d04078c$0$28008$afc3...@news.optusnet.com.au...
IIRC NTFS has been known to change with service packs! That's why its known
as Never The .. Same.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
Rauf Sarwar
I second Jim's view point and would like to add this to it.
I am a cautious person and do not like to muck around with my
Production databases when the expected uptime is required to be at
99.9%. I like to do things in Production whatever is thoroughly tested
and proven and comes from the horses mouth...in this case Oracle. This
is what my company pays me for to keep the system up and running at
99.9% of the time. Now this does not mean that new technology should
not be tested. I do it all the time BUT I always advise to do it in
test environment. If one has to do it in Production...better take a
backup or it will bite you in the rear end.
When an advise is given in a forum like this where a question is posed
by a junior DBA or Software Developer...then it is incumbent upon the
person giving advise is to use best practices.
If someone out there can develop a tool to replace exp/imp and is
thoroughly tested and proven, then more power to them and I will
consider using it, until then, I have no choice but to stick with what
works best i.e. exp/imp.