Solved

move redo logs

Posted on 2011-02-15
8
570 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 77

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 77

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 77

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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Fine Tune your automatic Updates for Ubuntu / Debian
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

739 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