DBA hallucinations

Every once in a while something happens that makes me question not only my skills as a DBA, but my sanity as well. Today’s event was one of the stranger ones that happened to me.

A coworker asked me to export a schema and import it into the same database but under a different name. Very common request, not something that is very likely to go wrong.  I’m running the expdp command, finished without warnings. Time to run impdp command, with remap_schema to load the data into a new schema, again it finishes with no warnings. I log on to the database and change the password for the new user, again it finished successfully.

Great, I call the co-worker and I tell him his new schema is ready. Two minutes later he is back, and complains that he gets an error logging in. Puzzled, I try logging in as that user myself:

SQL> conn test_user_copy/secret
ORA-01017: invalid username/password; logon denied

Even more puzzled I check dba_users for the user I just created. It is not there.  Blinking, I check again. Still not there.  I know that I successfully ran “alter user… identified by….”  on that user not two minutes ago. It is a large user – dropping the schema would take significantly longer, even if someone was so inclined, which I doubt.

I recheck the import log, and it still seems that the new user was created and the data was imported successfully.  Resigned, I reran the import command. It succeeded, which means that the user really was no longer there. I altered the password again, verified that the user still exists and notified the coworker that he can try again. He confirmed that everything is fine, so I guess the user didn’t disappear this time.

Puzzled, I consulted a senior DBA with my problem. He suggested looking at the alert log, as exports and imports also write a rather cryptic line there. I checked.  The alert log clearly showed only one import – the second one.

The senior DBA kindly suggested that I was imagining things when I thought I was running the first import, and maybe I should go home early today and get some rest.

I really have no explanation for this event. I’m not even planning on contacting Oracle Support with this story, it is just too strange.


While searching for something vaguely NFS related in Google, I found out that someone in Netapp has a blog.  I was unable to find out what is he doing in Netapp besides blogging, but I found his posts interesting. I happen to love working with Netapp – both the appliance and the vendor, and the blog really echo the competent and friendly vibe I get when dealing with the company.


7 Comments on “DBA hallucinations”

  1. Gary says:

    Are there any other databases that you may have done the import into ?

  2. Sidhu says:

    I also suspect the same thing. If no traces are there, there is a possibility that you did it into some other database. eg in a session ORACLE_SID was set to something else before you ran the import.

    If none is the case then its really strange !


  3. Noons says:

    ‘sOK, relax: there is worse.

    Today I got a “datafile inconsistent, needs recovery” on a “restore database” rman command.

    The backup is taken with the database in mount state (not open) and the datafile in question was the undo tablespace one…

    not even gonna bother opening a call with Support either!

  4. prodlife says:

    Hi Gary and Sidhu,

    Yes, importing into the wrong DB was the most likely answer. I started looking for the schema in other DBs and although I didn’t find it yet, I’m pretty sure this is where it will end up.

    Few month back I created a bunch of objects in a schema, but when a co-worker looked for them, they were gone. I found them few days later in the SYSTEM schema. Oops.

  5. Hallucinations frequently happens to me too, but i don’t care. Only a little question: until now i’ve rarely used DataPump because i’ve to manage a lot of 9i databases, but i’m studying for OCA and just yesterday i’ve discovered that you can do the operation you did without exporting to file but just using network_link parameter during impdb. I’ve not yet tried, but it seems interesting.

  6. prodlife says:

    cristian cudizio,
    network_link works fine between databases of the same version. I rarely use this option, because in my experience export usually works while I often need to run import more than once to get it right (wrong remap parameters are a frequent mistake). So I’d rather save time by doing the export once and retrying the import from the dump file.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s