Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

move redo logs

Posted on 2011-02-15
8
Medium Priority
?
582 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 78

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 78

Expert Comment

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

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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 5

Accepted Solution

by:
manzoor_dba earned 2000 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 78

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses
Course of the Month11 days, 12 hours left to enroll

916 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