Redo Log File:

Stores all the entries of DDL DML statements run by users.

This members are separate files which are connected logically to form this groups. These groups, they form the redolog file. Log Writter when start writing (writes every 3 seconds) it will start writing in group 1. The thing is that it will write the same content in both member 1 and member 2. When the size of all the members are full in group 1, the log writter will switch to group 2. This is called Log switch. Now if group 2 become full another log switch will occur and the log writter will swithc to group 3.

After group 3 will become full the log writter will switch to group 1 again and over write the previous logs. Now whats the point of this if the previous logs gets deleted. So to make this work it happens like this:

There are 3 states of a group: Inactive: When nothing is happening here. Current: It is when a group is currently written by log writter. Active: When a log switch occurs the next group will become current. Then the previous group will become active. It is then all the contents of the members will be archived that is stored in another place.

Now remember. All the member files of each group are physically located in different places. It can be located on a separate server also. This way the storage of the logs are secured.

Archive Log:

When a group becomes active after a log switch the logs in these will be archived (copied to somewhere else). When group 1 becomes full a log switch will occur and group 1 will become archived. Then same process will follow in a cycle. And every time a log switch occurs a sequence number is generated. When the database starts the sequence number starts with sequence number 1, one log switch sequence number 2, then 3, 4 this will continue.

Instance Recovery:

Now what is the point of keeping these logs archived. Now lets think that if the database shuts down due to a server failure before lets say that dirty blocks were not written by the database writer after committing. This is where SMON background process comes in. System monitor checks the control file and finds the SCN number during commits and checks whether the data blocks connected to the dml statements of those SCN numbers are updated according to those statements. It they are not updated then the system monitor performs those statements by itself and updates the data file upto the last commit. This is calles Instance recovery.

Also if a transaction is not completed (if there is no commit or rollback from that user) the SMON will do a rollback by itself during instance recovery.

When the database is restarted the SMOn process will read all the redolog file and will check whether the values showing in the redolog file are showing in the data file or not. If the SCN number is found in redolog file and the control file but those values is not in the data files then the SMON will do those changes itself in the required data blocks of the data files. This is called Role forwarding.

Also what is the current state of the database? If something happen to the data due to any server failure, running those queries of the user from start to finish will give the current state of the database.

Now we can also do a log switch manually.

Alter system switch logfile;

And also doing this manually is not good all the time.

To see the files in this group.

**select a.group#, a.members, a.sequence#, a.bytes/1024/1024 "Size in MB", b.member, a.status from v$log a inner join v$logfile b on a.group# = b.group#; **

Now if our database is not in archive log mode then every logs will not be there. Try this command to check.

To disable or enable. the archive log mode then start the database in mount state.

Only in mount state can it be disabled and enable

alter database noarchivelog;

alter database archivelog;

Create a member for a log file:

Its better to create multiple member files of group in different server or even in different geographical location. So even if a server crashes the logs will still be safe.

Create a log file member:

Create a location.

Alter database add logfile member 'path to the location/name.log' to group 1;

Now lets say the size of the group size is 50 MB. And the group has 5 members. So what will be the size of each members?

Ans. 50 MB each.

A group is not a physical file. The members are physical files. Just they are group together logically and all the member files of that group will have the same size which we will assign to the group.

Add a new group:

Alter database add logfile group 4 'path to the first member/name.log' size 20M;

Lets add a new member to it.

Now when we create another group. When the transactions are many in number (in banks- in a second thousand, lakhs of entries are comming), log switch happen in group, then the system should have time to archive the data. So we create multiple groups then so that other groups gets time to archive the data.

The server capacity, memory all of these factors are taken into consideration before creating a group. Because creating more members requires more resource.