Solved

move redo logs

Posted on 2011-02-15
8
552 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Linux users are sometimes dumbfounded by the severe lack of documentation on a topic. Sometimes, the documentation is copious, but other times, you end up with some obscure "it varies depending on your distribution" over and over when searching for …
FreeBSD on EC2 FreeBSD (https://www.freebsd.org) is a robust Unix-like operating system that has been around for many years. FreeBSD is available on Amazon EC2 through Amazon Machine Images (AMIs) provided by FreeBSD developer and security office…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now