Solved

move redo logs

Posted on 2011-02-15
8
563 Views
Last Modified: 2012-05-11
Customer requested me to move redo logs to seperate mount point and remove one member per groups. I really appreciate if you could provide me all steps which I need to follow. Thanks in advance.
0
Comment
Question by:hussainkhan22
8 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34898672
Have you Googled this topic?

http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_35.shtml#Moving Online Redo Log Files

If you cannot shut the database down, just create new redo log groups in the correct locations and remove the old ones.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34898680
0
 
LVL 34

Expert Comment

by:johnsone
ID: 34899308
My preference is to create them in the new location and then drop the old ones.  This allows you to keep the database up and users can be using the system.

Be aware that the setting of MAXLOGFILES when the database was created affects the number of groups that can exist.  You may need to drop one of the old ones before you can start creating the new ones.

Also, before you can drop the last old one, you need to do an

ALTER DATABASE SWITCH LOGFILE;

Then wait for the log to be archived.  Once that is complete, you should be able to drop it.
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 5

Accepted Solution

by:
manzoor_dba earned 500 total points
ID: 34903429
Hi,

Some more points to add...

You cannot drop an redo log file which is in 'ACTIVE' or 'CURRENT' status. You can drop it when it becomes INACTIVE status.

Hope the below example will help you.

3 Logfile Group each with 2 member  are currently placed at /u01/prod/logs and i want to move the
redos to the  /u02/prod/logs mount point.

Check the V$log for status of log groups.

SQL> select group#, status from V$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 ACTIVE
         3 CURRENT

--Now the Group 1 shows the status as INACTIVE , so i can drop that log file.

SQL> alter database drop logfile group 1;

--The above command will drop the group 1, and now create a group 1 in the new mount point.

SQL> alter database add logfile group 1 ('/u02/prod/logs/redo01.rdo') size 20m;

--You need to give the size as per your requirement.

--Now check the V$log;

SQL> select group#, status from V$log;

    GROUP# STATUS
---------- ----------------
         1 UNUSED
         2 ACTIVE
         3 CURRENT

SQL> alter system switch logfile;

--Now do a log swith , and once the group 2 becomes inactive drop it and create a new group 2 in the new mount point and then do a log switch again and follow the same procedure to drop and create group 3 in new mount point.

Thanks...



0
 

Author Comment

by:hussainkhan22
ID: 34906170
what is the command to check current redo log size and how many members in each group.
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34906301
hi

select * from v$log -- will retrive all the logical information regarding number of members , status etc
select * from v$logfile -- will retrive phisycal info like filename location etc..
0
 
LVL 5

Expert Comment

by:manzoor_dba
ID: 34913059


Hi,

As ashilo said you need to query the V$log to filnd the size of redo log files and to filnd the members you need to query the V$logfile, you can join these two tables to get the required details using the column group#.

SQL> select a.group#, b.member , a.bytes "Bytes" from V$log a, V$logfile b where a.group# = b.group#;


Thanks...

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34915736
>>what is the command to check current redo log size and how many members in each group.

Please keep in mind this is a different question than the one initially asked.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Every server (virtual or physical) needs a console: and the console can be provided through hardware directly connected, software for remote connections, local connections, through a KVM, etc. This document explains the different types of consol…
Fine Tune your automatic Updates for Ubuntu / Debian
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question