Hello all,
I have to migrate our Oracle databases running on Solaris to Windows.
Versions
Solaris 10 to Windows 2012 R2
11.2.0.3 to 11.2.0.4 or 12.1.0.2
As the endian file format is different I understand the only options I have are EXPDP full or transporatable tablespace option.
I have performed the transportable tablespace operation before from Solaris (11.2.0.3) to Windows 2012 R2 (11.2.0.4) and this worked quite well. I created the schemas that owned the tablespaces first and then imported the tablespaces after converting them to the correct format with RMAN convert. This database had users created using the application, rather than directly in the database itself.
The difference with this current database is that there are around 200 user/schema objects that use the Users tablespace. Each user of the application is created directly on the database itself. I proceeded to run expdp with transport_tablespaces= tablespace1, tablesapce2, users
When I run rman convert and specify E:\Oracle\Oradata\<dbname> as the output directory, it fails because the USERS01.dbf datafile exists. I created the database again, but specified the name of the datafile as USERS101.dbf - a different name to the source datafile. I can convert the files OK now, but the import fails with a message saying:ORA-29349 Tablespace Users already exists. I'm not able to drop this tablespace as I get an ORA-12919 saying cannot drop default permanent tablespace.
I have run:
CONVERT DATAFILE 'G:\TTS\datafile0_1.dbf','G:\TTS\datafile0_2.dbf','.......','G:\TTS\users01.dbf' TO PLATFORM="Microsoft Windows x86 64-bit" FROM PLATFORM="Solaris[tm] OE (64-bit)" DB_FILE_NAME_CONVERT='G:\TTS','E:\Oracle\oradata\<dbanme>\' PARALLELISM=8;
Above works OK
I then ran:
impdp system/*********@<dbname> directory=data_pump_dir dumpfile=tts.dmp logfile=tts.log transport_datafiles='e:\Oracle\oradata\<dbname>\datafile0_1.dbf','e:\Oracle\oradata\<dbname>\datafile0_2.dbf','.........',','e:\Oracle\oradata\<dbname>\users01.dbf'
This failed with ORA-29349 Tablespace Users already exists
Is there any way around this? Should I just use export/import of the full database?
The datapump specific questions:
Transportable tablespaces: I read you can do same version. Does this mean 11.2.0.3 - 11.2.0.3 and 11.2.0.4, but not to 12.x.x.x?
When doing a full import, do you have to remap the datafiles? The Solaris path is something like u01/oradata/<dbname> and the Windows is E:\Oracle\Oradata\<dbname>
I have read the documentation, but it doesn't give you specific examples. I remember doing a schema level import, where I dropped the schemas before importing (the tablespaces existed). I have also done a schema import where I created the tablespaces first. It would be good to read working examples of the different options and what to create ahead of an import if possible.
Also I'd like to be clearer on pre and post checks to make sure I have a consistent copy.