Moving datafiles about & relocating the Arch destination

[JMac]

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?

[ggnanaraj]

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.

[DaPi]

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

[marist89]

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.

[JMac]

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?

[marist89]

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...)

[DaPi]

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 . . . )

[JMac]

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?

[JMac]

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?

[DaPi]

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.