Today's lesson is one in being careful using tab completion

Today I was exporting a complete schema from one production oracle database (11.2) and importing the same data into a test Oracle database (10.2), also changing the schema it was going to.  Not rocket science, a few corners with versions that you forget about and swear about, but nothing too hard:

This was the export:
expdp SYSTEM \
dumpfile=xxx_10_2_export.dmp \
full=n \
schemas=xxxxxxxx \
directory=DMP \
logfile=xxx_10_2_export.log \
version=10.2
I transferred the dump file using rsync, and this is the import that I used:
impdp SYSTEM \
dumpfile=xxx_10_2_export.dmp \
schemas=xxxxxxxx \
remap_schema=xxxxxxxx:yyyyyyyy \
directory=IMPDIR \
logfile=xxx_10_2_export.dmp \
version=10.2
Look at what I got back...
Connected to: Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31619: invalid dump file "/home/oracle/imp/xxx_10_2_export.dmp"
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Linux-x86_64 Error: 9: Bad file descriptor
Additional information: 4
Additional information: 1
Erk!  I checked the MD5sum of the file, it was different to the one on the prod box.

I transferred the file over to the test box again, checked the MD5 of the file and it was the same.  Phew.  Just a transfer error.  Run the import again with a couple of taps (yay for command history!), but the same thing happened.

I tried all sorts ; reading the docs, different switches, transferred the file again and again only to find it failed every time.  Google really wasn't helping me today, lots of hits about space and corrupt files and failing disks, none of which I thought was really the answer.

It was only when I did the following process that I twigged what was going on:

  1. Transfer the file from prod
  2. Check the MD5.  It's the same as on the prod box.
  3. Attempt the import again
  4. Check the MD5 again.  It's now different to the one on prod box?

How can the MD5 be different after attempting an import?  It's a read-only operation on the file it's reading from.  The only writes are to... oh.... the log file.

Have a careful look at the logfile= line of my impdp:
logfile=xxx_10_2_export.dmp
I was appending the logs from the operation to the dump file.  This obviously won't work.

 The take-away?  Don't get complacent when using tab-completion or copy & paste. Check your filenames.

Comments

Post a Comment

Popular posts from this blog

Data Guard with Transparent Application Failover (TAF)

One of the problems with Data Guard is that even after switching/failing over to the standby database, all clients need to be reconfigured to communicate with the new primary. The simplest way to achieve this (if using a centralised tnsnames.ora ), is to modify the file and  add the line " (FAILOVER=YES) ",  and either multiple ( DESCRIPTION=...) or (ADDRESS=...)  lines, one for each of the standby systems, in the order that the client should attempt connections . This tells the client to try each address in turn until one succeeds. Doing so provides basic failover functionality within Oracle Net, and will probably function correctly most of the time.  Though be aware - because both primary and standby can share the same instance name (e.g. the same SID, therefore the same connection string), there is potential for this simple scheme to go wrong. An example in an active data guard environment (where the standby is open read only): a temporary network issue causes all c
Read more

RMAN-05531 During RMAN Duplicate from Active Data Guard Standby

[oracle@target dbs]$ rman auxiliary sys@dup target sys@source [...] RMAN> duplicate target database to DUP from active database db_file_name_convert '/u2/data/PROD','/u2/data/DUP' LOG_FILE_NAME_CONVERT '/u2/data/PROD/onlinelog','/u2/data/DUP' nofilenamecheck; Starting Duplicate Db at 25-FEB-15 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=171 device type=DISK RMAN-00571: =================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ======= RMAN-00571: =================================================== RMAN-03002: failure of Duplicate Db command at 02/25/2015 16:04:44 RMAN-05501: aborting duplication of target database RMAN-05531: a mounted database cannot be duplicated while datafiles are fuzzy Using Oracle 11.2.0.3 (anything below 11.2.0.3.6), this is the error if an attempt is made to duplicate from an activ
Read more

Data pump - "ORA-39786: Number of columns does not match between export and import databases"

Today I was asked if it was possible to import data for a specific table from a data pump export file, which was taken from when a table had a different structure to what it has now. e.g. when the export was taken, there were 10 columns.  Now there are 11. I thought that was simple, and I was sure I'd heard of this being done before.  Simply import using the "CONTENT=DATA_ONLY" parameter.  Right? Apparently not: Centos-/home/oracle/demo> impdp DEMO_USER/yyyyyyy directory=INBOUND dumpfile=demo_data.dmp logfile=impdpdemo.log content=data_only tables=xxxxxxxx Import: Release 10.2.0.4.0 - 64bit Production on Tuesday, 08 July, 2014 14:32:20 Copyright (c) 2003, 2007, Oracle.  All rights reserved. Connected to: Oracle Database 10g Release 10.2.0.4.0 - 64bit Production Master table "DEMO_USER"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "DEMO_USER"."SYS_IMPORT_TABLE_01":  xxxxxxxx/******** directory=INBOUND dumpfile
Read more