move redo logs

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.
hussainkhan22Asked:
Who is Participating?
 
manzoor_dbaCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
slightwv (䄆 Netminder) Commented:
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
johnsoneSenior Oracle DBACommented:
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
 
hussainkhan22Author Commented:
what is the command to check current redo log size and how many members in each group.
0
 
Aaron ShiloChief Database ArchitectCommented:
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
 
manzoor_dbaCommented:


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
 
slightwv (䄆 Netminder) Commented:
>>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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.