Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2007
    Posts
    4

    Unanswered: Moving oracle data

    I am not a DBA, I'm a server admin, so I'll try to explain this the best way I can.

    We have an old oracle 8i database on a windows 2000 Server that contains links to a bunch of images. As far as I know there are three pieces: The oracle installation, the table structure, and the images. The current layout is as follows:

    Server1, LogDrive 1: Oracle installation & Services
    Server1, LogDrive 2: Table Structure
    Server2, LogDrive 1: Image Files (being referenced by tables via UNC path)

    The Logical Drive that the Table Structure is on is failing. We want to move that stuff to Server2, LogDrive1.

    The problem: I tried moving the table structure to the other server, and mapping a network drive using the same letter that the current table structure uses. Since oracle runs as a service, and not as the currently logged on user, it can't see those network drives.

    The question: How do I get oracle, running as a windows service, to access its database that is on another server via mapped drives - is this possible?

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    why not move the whole database onto another server?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Nov 2007
    Posts
    4
    How easy is that to do for someone who's never done anything in oracle?

  4. #4
    Join Date
    Nov 2007
    Location
    Switzerland
    Posts
    27
    It ain't easy for sure. If I would be in your shoes I would

    - cleanly shutdown the database
    - copy the data from LogDrive2 to a safe place
    - rebuild / repair LogDrive2
    - move back the data
    - start the database again

    No need to be a DBA (beside from having the appropriate privilege to restart the Oracle instance)

    Peter

  5. #5
    Join Date
    Nov 2007
    Posts
    4
    Well - those drives are on OLD hardware that we're going to be surplusing, so fixing it isn't really an option.

    Can Oracle reference the table structure via a UNC path? where do we change that, or is it too much for a non-dba like myself. I do have full rights to start/stop the oracle instance.

  6. #6
    Join Date
    Nov 2007
    Location
    Switzerland
    Posts
    27
    Well, I am a Unix guy but basically if you stop the Oracle service, remap the LogDrive2 from server 2 Oracle will recognize the data. Did you shut down the database prior evacuating the data ?

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    i don't think any database system can reference a data file via a UNC path. I am not sure how Oracle on *NIX would react to a data file on an NFS file system, either. I doubt it would be pretty, though. Database systems need a high degree of certainty that entries written to a disk are written when the databases system thinks it is written. Any network lag, outright outage, or even a few lost packets could lead to a corrupt database.

    For the particular problem here, start practicing creating a new Oracle database on a new machine. There should be utilities to help with this, even in Oracle 8i.

    Once this database is created, look on the old machine for a file called initSID.ora, where SID is the name of your database. A search for init*.ora should uncover it. You may need to copy some lines for memory parameters from this file to the initSID.ora file for your new database. As always, keep the old initSID.ora file from your new database around, in case some of the parameters you copy over do not "play well" on the new system.

    Once you have the memory parameters settled (and the database has been restarted to have them take effect), then you need to worry about users and permissions. Hopefully you have all the passwords somewhere. If not, the old hardware may be around for quite a while. These usernames and passwords may have to be recovered from the application's config files. Once these users are all settled in their new home (and tested via SQLPLUS), then we can start to worry about the data.

    There is a commandline utility called exp that will export tables and their data from an Oracle database. Another utility called imp will import the data exported by imp. To get the usage type "exp help=y" at any command prompt (provided you have PATH variables set up right).

    Once you have that, you can export the structure and data to a file, transport that file to the new hardware, import, and start to point clients to it. Since you are a bit new to Oracle, leave yourself a LOT of room for testing. A lot of Oracle DBAs will encounter problems doing this, and this posting is by no means a complete listing of the challenges you will face. Good luck.

  8. #8
    Join Date
    Nov 2007
    Location
    Switzerland
    Posts
    27

    DBA needed ....

    NFS on Unix is supported by Oracle as long as you do the mount with the appropriate parameters. Guess it's got to be kinda the same for UNC on Windows. Sure create a new database on Server 2 and then export / import data would do the trick but you need to have some Oracle skills to do that. Obviously this is not the case here and thats why I pointed out to repair the local disk in Server 1 would be the best solution here. If the database has to be moved to another server a DBA needs to drop in.

    Would love to do that !
    Cheers: Peter

  9. #9
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    export the schema(s)/owners ? and imp them on the new box, cross platform should not be an issue.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •