You can use export - import upgrade method as well. (not data pump export but the tradition export - import) other than that you are correct.
What I would consider is to upgrade 10gR2 in-place and data pump export-import would be available from here which could be faster as you have 2T data.
Message was edited by: Zoltan Kecskemethy added one more option (in place upgrade + data pump)
Thank you for reply,
I see the article but I can't upgrade source database to 10g version, I need to upgrade and migrate it directly.
In this scenario I have multiple limitation :
TTS not supported for 126.96.36.199 - I need to upgrate to 10g
DP not supported
Little and big_endian
Datastorage from File System to ASM
In particular I need to migrate only 3 schema for 2Tb of Data
IMHO export-import could be the best option. IF you can do a FULL export and import that could be fast and painless.
But I see you have 3 schema only so you can do them one-by-one.
TEST it if you hit bugs you have a backup plan using db links and insert select.
How about to duplicate the source with a simple cold backup and upgrade that one to 10g? So you would have the original source un-touched but you can use newer technologies for the migration.
Message was edited by: Zoltan Kecskemethy added duplication idea
The problem now it's downtime... 2 Tb over the net (database source it's 500Km far from target database) and I have very little time window.
Insert as Select It's more slower than export/import?
Do you have an insert as select best practice guide?
I guess you have support. Please get oracle support advice as well.
Here you have two possibilities as I see:
- The new MOS community forum
- and the good old SR
also dont forget docs like: Oracle 11gR2 Upgrade Companion Doc ID 785351.1
Message was edited by: Zoltan Kecskemethy added doc link
But if you have short time window.
Create a cold backup of your source - try to minimize the change or track changes there after this backup - get your cold backup into a disk and take it to the new location - upgrade - roll in the tracked changes to the upgraded DB...
I have about 48h...
So another option can be GoldenGate?
if you think so.
Ok I think using exp and imp...
The best way it's using 11R2 Binary for exp and 11R2 Binary for imp? It's correct export schema metadata, import metatada, disabling constraint, export schema data , import data and finally enabling constraint?
What is the best way for export e import on this scenario?
Sounds like a good plan. But you may not need to separate the structure and data import.
You would need to pre-create tablespaces before import.
But you may able to import in structure and data in one as far as I know import should handle constraints, triggers etc.
For 9i you may need to use its own export.11g import should able to read it.
I wan to disable constraint for avoid constrain error during import
1) export metadata for the schema
2) import metadata in EXADATA
3) disable Constraint in EXADATA for this schema
4) export data
5) import data using direct path
6) enable constraint
Using 11g software isn't good?
The rule here is always use the lowest version of the export aka in your case you need to use the source 9.2 export to create the dump.
To import in you can use 11g import.
For full details see MOS docs: Compatibility Matrix for Export And Import Between Different Oracle Versions Doc ID 132904.1