Export and Importing data using Oracle 11

Background

In the era of oracle 9-10, I usually does export and imports data using exp and imp utility. Using oracle 11g, things aren't as smooth as it used to be.

Problem A - Exp doesn't export empty tables

Exp no longer exports empty tables. The cause are, exp utility is processing data segments, creating export dump. The Oracle 11g has a new feature, called 'Deferred segment creation'. It means that tables with no data will occupy no segments. And these tables will be missing from the exp result. The symptom of this problem is simple, we have missing tables in the destination system whose row count in the source system are zero.

Solution A-1 - Allocate segment for empty tables

Alter table allocate extent;

This is not so ideal, we must execute the SQL above for each empty table before doing exp command.

Solution A-2 - Disable deferred segment creation

Alter system set DEFERRED_SEGMENT_CREATION=FALSE;

This need to be done before we even create our empty tables.

Solution A-3 - Don't use exp/imp, use expdp/impdp instead

If we have access to the source database server and destination database server's respective filesystems, administrator-like OS level access, Oracle Data Pump (expdp/impdp) is a good solution. Impdp and Expdp are faster than imp and exp. But both are not without problems as we shall see.

Problem B - Expdp in oracle 11 are buggy

Some of the installed Oracle 11 still have buggy expdp routines. The symptoms are ORA-14102 error when we're doing impdp. It seems that a bug in the expdp causes the import SQL contains the clause 'NOLOGGING NOCOMPRESS LOGGING'. Such clause cannot be correct. Makes me wonder what all oracle QA guys are doing. 

Solution B-1 Install Oracle Patch 8795792

You need to log on to Oracle Metalink and download patch 8795792. After shutting down your oracle database, install the patch, start the database, and execute the post-install steps. I forgot to do the post install steps (missed it in the readme file) and takes us 3x export-import cycles to realize that the patch were not effective (yet). But of course you need an active Oracle support contract. IMHO, if you don't have Oracle support contract, you would be better running MariaDB or MongoDB or other open source database.

Problem C - Error importing partitioned data 

One of the destination database is an Oracle Express 11g, which doesn't have Enterprise features. But the source database were Oracle 11g enterprise. When tables using partitioning were imported, we have ORA-00439 feature not enabled : Partitioning error. Fortunately Oracle 11g's impdp have a merge partition option.

Solution C-1 Use merge partition option when importing

Just insert partition_options=merge to impdp's command line. Beware that the partitions would be gone from table's DDL in the destination system.

impdp system TABLES=sh.sales PARTITION_OPTIONS=merge 
DIRECTORY=dpump_dir1 DUMPFILE=sales.dmp



Comments

Post a Comment

Popular posts from this blog

Long running process in Linux using PHP

Background To do stuff, I usually create web-based applications written in PHP. Sometimes we need to run something that takes a long time, far longer than the 10 second psychological limit for web pages. A bit of googling in stack overflow found us this  http://stackoverflow.com/questions/2212635/best-way-to-manage-long-running-php-script , but I will tell the similar story with a different solution. One of the long running tasks that need to be run is a Pentaho data integration transformation. Difficulties in long running PHP scripts I encountered some problems when trying to make PHP do long running tasks : PHP script timeout. This could be solved by running set_time_limit(0); before the long running tasks. Memory leaks. The framework I normally use have a bit of memory issues, this can be solved either by patching the framework (ok, it is a bit difficult to do, but I did something similar in the past ) or splitting the data to process into several batches. And if you
Read more

Reverse Engineering Reptile Kernel module to Extract Authentication code

Image
 This time I will show how to (partially) reverse engineer a linux kernel module. The linux kernel module is larger than usual kernel module because it is actually a Reptile-based rootkit dropped by some hacker.  Part 1. Get basic module information First we gather basic module info by using modinfo : [root@lb lkrg-0.9.1]# modinfo falc0n filename:       /lib/modules/3.10.0-1127.10.1.el7.x86_64/kernel/sdc/falc0n.ko intree:         Y license:         GPL retpoline:       Y rhelversion:     7.8 srcversion:     81F508029A53F7490CCDB44 depends:         vermagic:       3.10.0-1127.10.1.el7.x86_64 SMP mod_unload modversions   We could also refer to the kernel module file not yet installed in the OS : [root@lb lkrg-0.9.1]# file falc0n.ko falc0n.ko: ELF 64-bit LSB relocatable, x86-64, version 1 (SYSV), BuildID[sha1]=1bde6abf4732eb620983032a47fbcf07689ece11, not stripped [root@lb lkrg-0.9.1]# modinfo falc0n.ko filename:       /root/lkrg-0.9.1/falc0n.ko intree:         Y license:         GPL retp
Read more

SAP System Copy Lessons Learned

Image
Background Earlier this year I was part of a team that does System Copy for a 20 terabyte plus SAP ERP RM-CA System. And just now I am involved in doing two system copy in just over one week, for much lesser amount of data. I think I would note some lessons learned from the experience in this blog. For the record, we are migrating from HP/UX and AIX to Linux x86 platform. Things that go wrong First, following the System Copy guide carefully is quite a lot of work - mainly because some important stuff are hidden in references in the guide. And reading a SAP note that are referenced in another SAP note, that are referenced in Installation Guide.. is a bit too much. Let me describe what thing goes wrong. VM Time drift The Oracle RAC Cluster have time drift problem, killing one instance when the other is shutting down. The cure for our VMWare-based Linux database server is hidden in SAP Note 989963 "Linux VMWARE Timing", which is basically add a tinker panic 0 in the
Read more