Manage the online redo log file on Oracle Standalone and RAC database

Manage the Redo log on Oracle Standalone and RAC Database

Redo log file size is increased online without shutdown the oracle database. you will take care while adding data files for location. check redo files is added at same location.

Standalone: Steps to Increase the redo log file size on oracle database

1. Check the size and location in redo log files.

select l.bytes/1024/1024 "Meg",g.member from v$log l,v$logfile g where l.group# = g.group#;

2. Add the new log file for increased the size to 250 MB.

alter database add logfile group 4 'F:\oracle\Redo\Redo04.log' size 250M;
alter database add logfile group 5 'F:\oracle\Redo\Redo05.log' size 250M;
alter database add logfile group 6 'F:\oracle\Redo\Redo06.log' size 250M;

--Use the following syntax for increase the members if you want to add two files in a group

ALTER DATABASE ADD LOGFILE ('F:\oracle\redo\redo04A.log','F:\oracle\redo\redo04B.log') SIZE 250M;
ALTER DATABASE ADD LOGFILE ('F:\oracle\redo\redo05A.log','F:\oracle\redo\redo05B.log') SIZE 250M;
ALTER DATABASE ADD LOGFILE ('F:\oracle\redo\redo06A.log','F:\oracle\redo\redo06B.log') SIZE 250M;

3. Check the status of all redo log files.

select group#,status from v$log;

GROUP# STATUS
------ ------------
1      ACTIVE
2      INACTIVE
3      ACTIVE
4      CURRENT
5      UNUSED
6      UNUSED


4. Check the size of redo log files added.

select l.bytes/1024/1024 "Meg",g.member from v$log l,v$logfile g where l.group# = g.group#;

5. Now drop the old redo log file.

Note : For drop the old log file status must be inactive.
SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;

6. To switch logfile run the following command.

alter system switch logfile;
alter system checkpoint;

7. Check the Redo log files is working.

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;
GROUP# ARC STATUS
------- --- --------
4 NO  ACTIVE
5 NO  CURRENT
6 NO  ACTIVE

Manage Redo log file in RAC Environment

Check the redo log file with thread. Thread represent the instance no present in the RAC database.Suppose 3 Node RAC has 3 threads number.

Resize the redo log file in RAC Environment as follows:

1. Check the size of redo log and how many redo log file present in each instance.
You got information of each instance from thread column. Each thread(instance) has 2 redo log group means two files.

select GROUP#,THREAD#,MEMBERS,STATUS,BYTES from v$log;

GROUP# THREAD# MEMBERS STATUS   BYTES
------ ------- ------- -------- ----------
     1       1       1 INACTIVE 52428800
     2       1       1 CURRENT	52428800
     3       2       1 CURRENT  52428800
     4       2       1 INACTIVE 52428800	  


2. Check the thread information if you needed which thread belong to which instance.

select thread#,status,enabled,instance from v$thread;

THREAD# STATUS ENABLED  INSTANCE
------- ------ -------- --------
      1 OPEN   PUBLIC   ORCL_1
      2 OPEN   PUBLIC   ORCL_2


3. Retrieve all the information of member and location.

select group#, member from v$logfile;

Group# Member
------ ------
1      +DATA/orcl/onlinelog/group_1.391.787059447
2      +DATA/orcl/onlinelog/group_2.390.787059453
3      +DATA/orcl/onlinelog/group_3.258.787054781
4      +DATA/orcl/onlinelog/group_4.256.787054787


4. You want to increase the size of redo log files.

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 '+DATA' SIZE 500m;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 6 '+DATA' SIZE 500m;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 7 '+DATA' SIZE 500m;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 8 '+DATA' SIZE 500m;
OR
alter database add logfile instance 'ORCL_1' group 5 '+DATA' size 500m;
alter database add logfile instance 'ORCL_1' group 6 '+DATA' size 500m;
alter database add logfile instance 'ORCL_2' group 5 '+DATA' size 500m;
alter database add logfile instance 'ORCL_2' group 6 '+DATA' size 500m;

5. Check the status and size increased to 500MB.

select GROUP#,THREAD#,MEMBERS,STATUS,BYTES from v$log;

GROUP# THREAD# MEMBERS STATUS   BYTES
------ ------- ------- -------- ----------
     1       1       1 INACTIVE 524288000
     2       1       1 CURRENT	524288000
     3       2       1 CURRENT  524288000
     4       2       1 INACTIVE 524288000	  
     5       1       1 UNUSED   524288000
     6       1       1 UNUSED   524288000
     7       2       1 UNUSED   524288000
     8       2       1 UNUSED   524288000


6. Now drop the inactive redo log old group.

alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database drop logfile group 4;

7. If is is not inactive state then you use the following command to convert them into inactive state.

Alter system switch logfile;
alter system checkpoint;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.