Moving Log Files in Oracle 9.2.0
Hi there
i have 5 log groups in oracle 9.2.0. (running on win2k) my 3 log groups are all created under D: drive as i used oui to install my db. i had 16 hard disks,and the senior dba had asked me to move the 2 log groups to other hard disk. the configuration for my log groups (each w 3 mbrs) are as follows
D:LOGGRP1
LOGDATA0101 100MB
LOGDATA0102 100MB
LOGDATA0103 100MB
D:D:LOGGRP2
LOGDATA0201 100MB
LOGDATA0202 100MB
LOGDATA0203 100MB
D:LOGGRP3
LOGDATA0301 100MB
LOGDATA0302 100MB
LOGDATA0303 100MB
G:LOGGRP4
LOGDATA0401 100MB
LOGDATA0402 100MB
LOGDATA0403 100MB
H:LOGGRP5
LOGDATA0501 100MB
LOGDATA0502 100MB
LOGDATA0503 100MB
I need to move log groups 2 & 3 to E: and F: respectively.
Q1: What are the necessary steps to move log groups & their data files + mbrs? (besides do full backup)
Q2: Do i have to change init.ora to indicate the new locations for my loggrp2 & loggrp3?
Q3: Any other considerations?
Any help is much appreciated
Software/Hardware used:
Software/Hardware used:
ASKED:
August 31, 2005 8:32 PM
UPDATED:
September 1, 2005 6:34 AM
Answer Wiki:
What you need to do is take the database down cleanly i.e. not shutdown abort.
Using filesystem commands move the log files to the correct location
Mount the database - startup mount
Then use - alter database rename file 'old name' to 'new name'
for each log file
then open the database - alter database open
Viola! Job done.
There are no init.ora changes to make as the log file location is held within the controlfile.
I can think if no other considerations except that I have never seen 5 log copies. You really are covered for 5 disk crashes at the smae time?
To see all answers submitted to the Answer Wiki: View Answer History.
You can drop the logfile groups and recreate them where you want them. Before you drop the logfiles you need to make sure that they are inactive and archived. If the files you want to drop is active then switch logfile until they are incative and archived. In this case you don’t need to restart the database.
The first consideration is to make sure that your E: and F: drives are separate from the D: drive. If not, you’re gaining nothing.
Also, multiple members to a log group may not improve performance. One strategy would be to “stripe” all your logs across the the three drives (D:, E:, F:) in a different series (e.g., Group1 D, E, F; Group 2 E, F, D) so that the I/O is distributed across all the drives).
There’s nothing special about “moving” the log groups. You can create and delete log groups while the database is up and active. You’re a hero for doing all this without taking the database offline.