Full db import isn't?

[Thargy]

Hello,

I've just had cause to do a full database export and then an import into a different target database, for the first time.

During my struggle to get the beastie working, I googled for some error messages that popped up. According to the best advice I could find, a full import will only work if the source tablespaces and users have been pre-created on the target database, before the import is attempted. The poster also stated that the target tablespace data files have to have the same names and locations as the source files.

I've checked the oracle utilities documentation which appears to be silent on this subject. Can anyone confirm or deny the above suppositions/assertions. Can anyone point me at authoritative documentation about this?

Regards

Tharg

Grinding away at things Oracular

 

[KenCunningham]

Hi Tharg, not 100% on this, but I would have thought the data file names would have to be the same, as they are referenced in dba_data_files? Or perhaps they can be different if the system tablespace is pre-created as I assume it would be in a new database.

As for the user and tablespace creation, doesn't full=y imply that?

I'll be interested to follow this thread as it is something of a grey area for me.

 

[Dagon]

In theory, the FULL import will create the tablespaces for you and it contains the statements to do so. However, unless your target system matches your source system exactly (i.e. has the same file systems etc), then it is quite likely you will get errors. Most people prefer to pre-create tablespaces so that they can choose the most appropriate size and location, then do an import with "ignore=y".

 

[Thargy]

Gents,

thanks for the amazingly swift responses. I was seething yesterday, because

a) having taken the trouble to download, print out and read the Oracle documentation, it said nothing
b) The software doesn't do what it says on the tin.
c) File location independence is not provided.

W.R.T. point c), isn't one of the main reasons for an import and export to move stuff around? Relying on someone else having data files in precisely the same location as a source machine seems to be barking mad, and defeat the object of such software.

Of course, this all occurred whilst I was under pressure to deliver. My Murphy field is as intense as ever... sigh.

Regards

Tharg

Grinding away at things Oracular

 

[Dagon]

I think there are also some problems with rollback segments. It will create the rollback segments but doesn't bring them online. Without at least one non-system rollback segment, you can't import any non-system data, so the res of the import fails. Therefore, it's usually best to pre-create any objects like tablespaces and rollback segments.

 

[SantaMufasa]

Tharg,

These limitations/issues are precisely why, when I want to "transport" an entire database, that I do schema exports of all the schemae I want moved, instead of a full db export. The additional benefit of doing schema exports/imports is that the exports can occur in parallel and the imports can occur in parallel, thus (typically) reducing the overall time to completion of the db move.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Thargy]

Santa,

Am I therefore correct in thinking that if I export each and every schema in a database (with the exception of sys) and then import them into a blank database, that this will effectively have moved the entire database?

Regards

Tharg

Grinding away at things Oracular

 

[SantaMufasa]

Correctamundo !

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[LKBrwnDBA]

PS: You can import schema by schema from a FULL export:

imp / FROMUSER=XXX TOUSER=XXX...etc...


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Correct, LK, but that FULL export contains some stuff (thus "exp" cycles) that would never come into play if one's intent is to do schema imports. Also, (despite specifying parallelism in a FULL export) one loses the (albeit small) benefits of true schema-export parallelism and (again, small) benefits of file-management/compression granularity that accrues from schema-by-schema exports.

But again, as always, what you say is true.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Beilstwh]

All of the above being said, for simplicity, I would do a FULL export and then do a schema by schema import. You would only have one export file that you could use to import all the schemas that you want and all the schemas would be consistant in their data (assuming you did a constant export).

Bill
Oracle DBA/Developer
New York State, USA