Moving Data Between Oracle Instances with Data Pump

Last weekend I took our annual trip up north to open my grandpa’s cabin up in Oneida County.  There are various tasks to get it ready for use in the summer, such as raking leaves, cleaning the gutters, and putting in the pier.  When I arrived I found most of the work had already been done by a group of people that had come up to do some deep cleaning a few weeks earlier.  This gave me extra time to fish!  I ended up bring my five year old son with me this time, so much of my fishing time was spent with him.  He’s a pretty good fisherman, but I still spent most of my time helping him rather than fishing myself.  It was a great weekend and he caught a ton of fish, including his first northern pike.  He was incredibly excited and I was relieved I got it into the net before it bit off the end of the line.  Taking your children fishing when the bite is really on is one of life’s greatest thrills.

Sons first northern

I didn’t more than a couple hours of muskie fishing in and I didn’t catch any.  I did have one follow from a small fish, probably around 25″, and I didn’t get a great look so it may have even been an aggressive northern pike.  However, I did see a different muskie that took my breath away.  It wasn’t following my lure because it came from a different direction than I had been casting.  It came in slowly and lazily and then gulped some air before slowly swimming off.  I’ve read that muskies sometimes will gulp air to aid in digestion, so I assume it had just finished a large meal.  Since it was moving so slowly and came completely out of the water, I got a really good look at it.  It had a large gash on its back right above the dorsal fin that must have been a result of the recent spawn.  But its most distinct feature was the size.  It was unquestionably the largest muskie I have ever seen.  Not having seen many fish that big, its hard to give a really good estimate, but it must have been a mid 50″ fish.  I wouldn’t be surprised in the least if it was a full 60″.  You don’t get to see many fish that size, and I felt very blessed to get such a great look at it.

Data pump is an effective way to move data in and out of an Oracle database instance.  It can easily be used to move data from one instance to another by first exporting the data from instance A and then importing it into instance B.  It can also be an effective means of taking a snapshot of the database for simple backup purposes.  I was recently tasked with recovering data in a particular table in an Oracle database and was able to get that data by loading a nightly data pump export.  In my case, the export had been created with the old deprecated way of importing and exporting data, which uses the imp and exp commands instead of impdp and expdp.  The command used to export the data was:

20160607 data pump export

You can see the command run is exp.  The next section, f21/$my_f21pwd@my_sid is the login to the database.  The file=$exportfile is a variable that contains the file name which contains the day of the week.  This was defined earlier in the shell script.  The script was set up to do a nightly export and replace the current export for that day of the week, so you always have a rolling seven days of export “backups”.  The buffer specifies the size, in bytes, of the buffer used to fetch rows.  Bigger buffer settings will perform better.  Full = y indicates that the entire database will be exported.  Consistent = y makes sure the database is in a consistent state.  This ensures relationships between tables will be consistent; it makes the data usable.  Lastly, the logfile parameter is specifying a file to put the log of what was done.  It is helpful to have a log for each import and export so you can see what was run and view any errors or notices that may arise.

Next came time to import a specific table from this export file.  I needed a table called FMSH_IMAGE, and I wanted to put it into my own user’s database.  From there I could grab only the specific column I wanted using an update statement to the table in the real database.  I ran this:

20160607 data pump import

You can see the command run is imp.  You can’t use the new data pump impdp with a file exported from the old exp.  The rest is pretty self explanatory.  I include a username and password to access the database.  I specify the file to use, this one from the most recent Thursday.  I specify a log file to put any messages into.  The last three parameters are a little more interesting.  Fromuser tells the program the database that was exported.  Touser tells the program what database I want the import to go into.  These are important because I didn’t want to just override the FMSH_IMAGE table in the source database – this would get rid of any newly imported data.  I only needed to update a single column with the data from the previous day.  Lastly, I was able to specify that I only wanted to import the single table.

Using this example, you can see Oracle’s import and export programs are very useful for getting data into and out of the database.  Be sure to check out the Oracle documentation on the newer impdp and expdp programs, as these have different parameters and improved functionality.

Oracle data pump import

Oracle data pump export