Home » RDBMS Server » Backup & Recovery » How to move redo log files and undo tablespace file?
How to move redo log files and undo tablespace file? [message #138656] Thu, 22 September 2005 19:40 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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;



Re: How to move redo log files and undo tablespace file? [message #138715 is a reply to message #138714] Fri, 23 September 2005 03:32 Go to previous message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member


I agree with u allejane. Razz

regards,
tarun
Previous Topic: using logminer....!
Next Topic: Oracle Backup problem
Goto Forum:
  


Current Time: Fri Mar 29 06:35:40 CDT 2024