Re: imp/exp - what database?

Peter wrote:
> Hi All -
>
> Please excuse what is, no doubt, a stupid question - I'm having to act
> as DBA for the time being...
>
> I need to exp a database from one server and imp it on another. The
> originating server hase 2 Oracle instances running on it, each hosting
> one database. When I run exp, I am required to enter a username and
> password (+ other info) but at no point is the instance or database
> required - nor are they commandline options. The same is the case for
> imp.
>
> The exp completes successfully, but what database have I exported?
> Both instances have the same username and password.
>
> Thanks
> Peter

The first item you need to address is which instance is set via the ORACLE_SID environment variable. For UNIX/Linux one would:

$ echo $ORACLE_SID

and the value would be returned. As you have two instances on one server (a bad idaa anyway, but this is not your problem to address) each instance MUST have its own SID. For Windows one would, at a command prompt, type:

c:\oracle > env

and the entire Windows environment would be displayed. One would scroll back through the list to find ORACLE_SID and its associated value (and, as the values are displayed in alphabetic order this should not prove difficult).

As you've only provided a username and password the ONLY database you could export would be the instance referenced by the ORACLE_SID environment variable. Each instance MUST have a unique ORACLE_SID; two instances on the same server with the same ORACLE_SID value cannot exist. This should also be O/S-user dependent, as certain O/S users should be assigned to use one instance or the other by default. In UNIX/Linux such settings are in files such as .profile, .bash_profile or .cshrc, depending upon which shell is assigned to the user account.

Before you attempt another export learn WHICH instance to which you connect by default, using simply a username and a password. You've been provided sufficient information in this post to accomplish that task. Once you know which instance to which you're connecting, ensure with your supervisor it is the correct instance to export. If he/she doesn't know (which is a frightening thought, but can happen) your only recourse is to export BOTH instances and hope for the best. I would name my export file after the ORACLE_SID used to create it. If you're using UNIX/Linux a script similar to this may be of help:

#!/bin/sh

# Request SID of database to export

echo "Enter Oracle SID: \n"
read sid

SAVE_SID=$ORACLE_SID # preserve existing setting ORACLE_SID=$sid; export ORACLE_SID # new setting

echo "Exporting database $sid"

$ORACLE_HOME/bin/exp file=$sid.dmp compress=n buffer=81920000 statistics=none

ORACLE_SID=$SAVE_SID; export ORACLE_SID # restore original setting

This is only an example, as your exp parameters may be different (I would STRONGLY suggest keeping the compress=n setting as it will preserve the existing table storage parameters, and the statistics=none parameter, as it will eliminate any messages regarding questionable statistics).

Of course, all of this is predicated upon you knowing which ORACLE_SID you need to access. If you're still confused, contact me offline.

David Fitzjarrell Received on Mon Jun 13 2005 - 11:30:45 CDT