How to move redo log files and undo tablespace file? [message #138656] |
Thu, 22 September 2005 19:40 |
mitra fatolahi
Messages: 38 Registered: October 2002
|
Member |
|
|
Hello,
We installed Oracle9i software and the database datafiles on two separate drives as recommended. However, after the install I noticed all the redo log files and the undo tablespace file are on the same drive as the software.
We are experiencing a poor performance and according to Oracle9i recommendation, is best to have the redo log files on its own drive.
First, I like to know why Oracle puts the redo log files on the same drive it installs the software when it is so important to have the redo log files on a separate drive?
Second, what is the safest way to move the redo log files and the undo tablespace file to different drives without corrupting the database. By the way, I do NOT have the database in Archive mode.
I appreciate if you be specific with the instruction.
Thank you so much,
Mitra
|
|
|
Re: How to move redo log files and undo tablespace file? [message #138680 is a reply to message #138656] |
Fri, 23 September 2005 00:40 |
tarundua
Messages: 1080 Registered: June 2005 Location: India
|
Senior Member |
|
|
--> why Oracle puts the redo log files on the same drive ?
: Everyone is not so lucky like you , sometimes one has to survive with a single drive. You can take the case of students who are installing for just learning purpose, they all cant afford multiple drives.
--> safest way to move the redo log files .
1. Shutdown the database.
2. Copy (not move) the redo log files from the old location to the new location using OS command.
3. Startup & mount the database but do not open.
SQL> Startup mount
4. Rename
SQL> Alter database rename file
'c:\source_loc\redo01.log' to 'd:\dest_loc\redo01.log';
5. Now open the database
SQL> Alter database open.
{ now you can delete the old log file from the source location}
--> Relocating datafiles ( you can use this method for any tablespace including undo}.
1. Shut down the database.
2. Move the file to new location.
3. Mount the database.
4. Rename
SQL> Alter database rename file
'c:\sour_loc\undo01.dbf' to
'd:\dest_loc\undo01.dbf' ;
5. Open the database.
Hope this might help you.
regards,
tarun
|
|
|
Re: How to move redo log files and undo tablespace file? [message #138714 is a reply to message #138680] |
Fri, 23 September 2005 03:27 |
alliejane
Messages: 59 Registered: July 2005 Location: Glasgow
|
Member |
|
|
Nothing wrong with your solution Tarun,
However I try to avoid shutting the database unless I absolutely have to.. therefore I would.
1. Create new redo logfile groups in the new location.
alter database add logfile group 3 'filename','filename' size 100m;
2. Alter system switch logfile till the the new one is current and the old one is no longer active.
3. Alter system drop logfile group 1;
4 Delete old OS files.
Undo Tablespace,
Again to avoid shutting down the database.
1. create new undo tablespace in required location.
2. Alter system set undo_tablespace = new_ts;
Once all of the existing transactions have completed (and the retention policy has expired)
3. Drop tablespace old_undo including contents and datafiles;
|
|
|
|