Ufff... RECYCLE BIN - Three Strange Problems

After long 8 hrs official hours in the office, I was just above to leave home as my week-end starts. I have told my family that I will be on time home and promised them for outing including a good dinner as well as shopping. Unfortunately, I could not make it on time home as I promised, it took more couple of hrs of work due to few silly problems and I thought of just sharing it.

One issue leads to another and that also kept me to resolve another (different) issue.

Problem 1: MMAN terminated the instance

While I was leaving home, my colleague came to me said he found something strange with export and import.

The issue was, he did a user level export (10gR2) and imported in another database also 10gR2. The problem was that, when he made count of tables and indexes on source and target databases to make sure, he found out that at target database around 70 tables are missing compares to source database. I saw few errors in the import log, but, they were java related issues.

As source and target databases on version 10g, I told him to try with EXPDP instead of traditional EXP/IMP. When he started the export data pump using EXPDP, surprisingly. MMAN terminated the instance. We tried to start the databases couple of times, but, instance was continuously crashing.

Searching in the METALINK doesn’t help either as it says it’s a bug and fixed in 10gR2, as we are already on 10gR2, thought something else would be wrong. I just suspected about the SGA parameters, specifically, SHARED_POOL_SIZE. The SHARED_POOL_SIZE parameter was set to 120M, increasing the parameter value from 120M to 250M has resolved the issue.

Problem 2: Difference between source and target – after export and import.

After solving the MMAN instance termination issue, EXPDP and IMPDP doesn’t resolves the problem as it was still 70 tables difference between source and target.

After spending one hour, we found out that those 70 tables were present in the RECYCLE BIN in the source database. This was mystery behind about missing tables in the target database.

Problem 3: Oracle software mount point ownership change.

The whole day we were busy with the new database creation, schema preparation and other database setups for one of the forthcoming IPO.

The UNIX admin gave us the new server with more CPUs and RAM where they simply detach the disk from the old server (where we initially installed and setup everything) to the new server so that we will not loose our work.

When tried to bring up the database on the new server, got permission issues as Oracle doesn’t able to neither read nor write bytes in the controlfile. While checking permissions, everything just looks okay also we were able to create files using TOUCH on the all the mount points.

After some investigations, we found out that the new server Oracle software was installed using Oracle user as the old machine user was Oracle10. Upon on changing the ownership to Oracle10 of Oracle Software filesystem, everything worked as usual.

Meanwhile, I have got around 10 calls from my kids as well my wife as well.

However, we didn’t miss the dinner and shopping.

Happy reading,

Jaffar