Hello honored professionals,
My problem is that I have inherited som old servers with databases running different older versions of Oracle that we need to keep around for archive lookup purposes - and that I wish to consolidate on a few virtual servers. Most of the old databases have been a breeze to move using regular datapump export/import or exp/imp, but I have run into 2 databases that have a SYSAUX datafile in RECOVER mode and that I can't seem to get either to work on. (Naturally I tried running recover datafile, but it seems that finding the required backups is not as easy as all that.... long story)
The first of the 2 I managed to import to the new server using IMPDP over a database link (even though the import was incomplete and I had to use the SQL Plus copy command to move some tables containing LONG columns, all my tests of the important schema indicate that the relevant data was moved and all constraints are still satisfied - and the application seems to be running and finding the data it should which satisfies the requirements we have for this DB)
On the second database I have bigger problems though - running expdp on the old server doesn't work - and impdp over dblink doesn't work either, so I thought I might be able to use the Database Copy tool in SQL Developer (4.0.3.16) to copy the most important Schema, but the Copy-log gives me some errormessages regarding table creation - and after the copy has finished I see that a few tables are missing... (I think maybe the reason the copy failed is that the tables contain LONG columns??) I didn't do a complete check of the log, but tried running a Database Diff to compare the 2 schemas - (using a sys as sysdba connection to both databases) and I get an unable to compare object error (maybe because the SYSAUX tablespace on source is messed up?)
Running expdp on local server:
full=y
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=EXPDP_PROBLEMDB_20150227.dmp
LOGFILE=EXPDP_PROBLEMDB_20150227.log
flashback_time=systimestamp
C:\Users\marius.wilberg>expdp system/password parfile='E:\datapump\expdp_problemdb.par'
Export: Release 11.2.0.1.0 - Production on Fr Feb 27 12:14:54 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
UDE-00018: Datapumpeklienten er ikke kompatibel med databaseversjonen 11.2.0.1.0
Running impdp over dblink:
impdp_PROBLEMDB.par:
full=Y
NETWORK_LINK=problemdb_link
directory=DATA_PUMP_DIR
logfile=impdp_problemdb_20150227.log
table_exists_action=APPEND
flashback_time=systimestamp
C:\Windows\system32>impdp system/password parfile='E:\oradmp\impdp_PROBLEMDB.par'
Import: Release 11.2.0.4.0 - Production on Fri Feb 27 12:07:59 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
ORA-39006: internal error
ORA-39065: unexpected master process exception in DISPATCH
ORA-04052: error occurred when looking up remote object PUBLIC.DBMS_DATAPUMP@PROBLEMDB_LINK
ORA-00604: error occurred at recursive SQL level 3
ORA-04045: errors during recompilation/revalidation of SYS.DBMS_DATAPUMP
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'D:\ORADATA\PROBLEMDB\SYSAUX01.DBF'
ORA-02063: preceding 3 lines from PROBLEMDB_LINK
ORA-39097: Data Pump job encountered unexpected error -4052
Any hints on getting a more or less consistent copy of the source database on the new server?
Cheers,
Marius