Moving datafiles about & relocating the Arch destination
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Moving datafiles about & relocating the Arch destination

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    Moving datafiles about & relocating the Arch destination

    I've been asked to clean up one of our databases.

    It archives the redo to one of the drives on the same server (Its Win NT4 and Ora8.1.5) - which I've never liked. I'm going to have it archive to a network location on a separate server.

    I also have to switch/move some of the datafiles around.

    So -

    Datafiles: I believe its as simple as
    (Make backup)
    (a) close down Db and restart restricted.
    (b) Make OS copy of file to new location.
    (c) Rename file-in-orig-location to file-in-new-location (ALTER DATABASE rename 'C:\direc\datafile1.dbf' to 'F:\direc\datafile1.dbf')
    (d) open db.
    (e) Delete old version using OS command.

    Redo logs :
    (Make backup)
    (a) Close down db.
    (b) Move existing Arch redo logs to new location.
    (c) Edit init.ora to write redo logs to new location
    (d) Start db.
    (e) Switch logfile several times to test change.

    Is it as simple as I believe? Any pitfalls to watch out for?
    Any misconceptions on my part?

  2. #2
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865

    Re: Moving datafiles about & relocating the Arch destination

    Originally posted by JMac
    (a) close down Db and restart restricted.
    (b) Make OS copy of file to new location.
    (c) Rename file-in-orig-location to file-in-new-location (ALTER DATABASE rename 'C:\direc\datafile1.dbf' to 'F:\direc\datafile1.dbf')
    (d) open db.
    (e) Delete old version using OS command.
    a) should be restart, mounting the db but leaving it closed.

    After (e) you have to backup the database as you have made a structural change.

    HTH.

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    In 8i if you've Enterprise Edition, you can use LOG_ARCHIVE_DEST_n to write to more than one place - I would do that in case the other server is down.

    Otherwise perhaps generate the Arch logs on the db server and copy them with OS commannds elsewhere . . . we discussed this some time ago . . .
    http://www.dbasupport.com/forums/sho...threadid=36357
    Last edited by DaPi; 07-10-2003 at 09:33 AM.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Re: Moving datafiles about & relocating the Arch destination

    Originally posted by JMac

    Redo logs :
    (Make backup)
    (a) Close down db.
    (b) Move existing Arch redo logs to new location.
    (c) Edit init.ora to write redo logs to new location
    (d) Start db.
    (e) Switch logfile several times to test change.
    Nah, too complicated...

    1. with the database up, stop archiving:
    alter system archive log stop;
    http://download-west.oracle.com/docs...19.htm#2053642

    2. Start archiving to a new location:
    alter system archive log start to '/u02/newdest/';
    http://download-west.oracle.com/docs...19.htm#2053642

    3. change your init.ora parameter to reflect the change

    If you are using RMAN, your method will cause RMAN to fail the next time you run it because he is looking for the archived redo logs in a certain directory and you have moved them.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Using Standard Edition.(!)

    In Windows explorer I mapped a network Drive on the Oracle server to a location on the network. So the J: drive (as far as the Oracle NT4 server was concerned) pointed to a suitable location on a remote machine. I can navigate to this machine and explore the folders with no problems.

    I edited the init.ora to point to this remote machine directory.

    So ...
    log_archive_dest=G:\oracle\arch
    became ...
    log_archive_dest=J:\oracle_remote\arch

    When I restart I get:

    ORA-16032: parameter LOG_ARCHIVE_DEST destination string cannot be translated.
    ORA-09291: sksachk: invalid device specified for archive destination.
    OSD-04018: Unable to access the specified directory or device.
    O/S-Error: Access is denied.

    Which leads me to believe that this is NT permissions preventing the DB from accessing the remote directory. Would this make sense?
    Our Network Admin set the remote location up and the NT user that I log on to the Oracle server as, has Admin privs and permissions.

    Any clues?

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    as the oracle owner, copy a file to j: to see if you get the same type of error. Most likely is a permissions issue, although I don't do Windoz. (You could always reboot...)
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    By default the Oracle NT services run as SYSTEM. SYSTEM can not use network drives . . . . . I think you will need to run the Services applet from Control Panel to configure the services' startup as some other user - watch out for the imapct of password changes ! ! ! !

    ( . . . or copy the archlogs with an OS copy . . . )

  8. #8
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Ok ... Jeff

    Unix 1
    Windows 0

    But this is the first time I've found Windows to be lacking!

    So where do I go from here...?

    Can anyone suggest a good strategy for OS copying the archived logs on Windoz????

    DaPi : services applet? You've lost me there. Any resources you can point me at?

  9. #9
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    DaPi - I see what you mean.
    But what account should I use to start the service? If I create one, what permissions will it need?

  10. #10
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    The link in my first post describes what I do to copy the logs.

    I think you would need to have a user with Local Admin rights plus rights on the share on the other server . . . (I confess I've never done this in production - just some experimentation in test with UTL_FILE which has the same problem).
    I'm not too happy about this solution . . . what happens if someone trys to hack this user's p/w and the account gets locked out - can you still restart the services?

    The 8i doc says not to use UNC file names (\\machine\share\etc) but does not say anything about mapped drives. I haven't looked on metalink.

Posting Permissions

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



Click Here to Expand Forum to Full Width