Every DB instance must require
minimum two online redolog files.Every online redolog file must be attatched to
a group. Oracle requires minimum 2 groups with each of one redolog file.These
log files also called members.
Finally
Oracle recommends minimum 2 groups with each of 1 member with equal size.
Multiplexing means maintaining
multiple members. Redo log files have at least two groups and each group should
have at least one member as redo log file. In multiplexing we will maintain two
members in each group.
To
avoid human errors and physical corruption we maintaining multiple members in
each group
Status of Online Redolog file:
There are 3 status of online
redolog files are available in any db instance.
1.
Current 2.Active 3.Inactive
1. Current:
Log
writer always connected to the current logfile to write the contents of log
buffer information.
2. Active:
Checkpoint
compares the database between relogfile and datafiles and makes sure all
commited trancsation are available in datafiles.
Arc
background processes always connect to the active logfile to copy this active
logfile information in to the archive destination.
3. Inactive:
Log file
which was ready to overwrite is called inactive logfile.
What are the advantages of checkpoint background process:
- Checkpoint
numbers are generated automatically when ever log switch occurs.
- These checkpoint
numbers are stored into the control files.
- Checkpoint
numbers are useful for faster recovery of the DB.
How to switch log writer from one redo log file to another redolog
file manually?
sys>Alter system switch logfile;
this
command switch the log writer from One logfile to another logfile.
SQL>SELECT GROUP#, STATUS FROM
V$LOG;
GROUP# STATUS
1 ACTIVE
2 CURRENT
3 INACTIVE
4 INACTIVE
è We
cannot resize the redo log file directly.So we can only drop and recreate the
redo log file. We can drop the redo log file only when the status of the redo
log file is inactive.
è We
need to remember that the redo log file has three status current, active and
inactive.
è And
when we are dropping a redo log file which is not in INACTIVE state then it
will throw an error.
è In
oracle we should have two redo log groups and each having at least one redo log
file as member. So to drop a redo log file we need to create a redo log file
with a bigger size first then we can drop.
è When
dropping the redo log file, the basic thumb rule is we need to
o
Have the redolog file in inactive state.
o
Should have have at least 3 redolog files or
3 redolog groups having one member each then we can drop the redolog file or
group.
How to resize online redolog files?
ADD REDOLOGFILES or CREATE NEW REDOLOGFILE GROUP’s WITH BIGGER
SIZE THEN DROP THE OLD ONE’S
Demos on Managing online Redolog files:
How to add additional online redolog file to the existing group1:
sys>alter database add logfile
member ‘/u01/PROD/redolog1b.log’ to group 1;
sys>select * from v$logfile;
sys>alter database add logfile
member ‘/u01 /PROD/redolog2b.log’ to group 2;
sys>select * from v$logfile;
How to identify log status (current, inactive, active, and
unused):
sys>select * from v$log;
How to create new group to the existing database :
sys>alter database add logfile
group 3
‘/u01/PROD/redolog3a.log’ size 10m;
All members in the group must have same size
sys>alter database add logfile
member ‘/u01/PROD/redolog3b.log’ to group 3;
sys>select * from v$logfile;
How to remove a logmember from existing group 1;
sys>alter database drop
logfile member ‘/u01/PROD/redolog1b.log’;
Note :- All groups and members
can be dropped except current log group
and members.
sys>select * from v$logfile;
To remove another member from group 2 :
sys>alter database drop
logfile member ‘/u01/PROD/redolog2b.log’;
sys>select * from v$log;
sys>alter system switch
logfile;
every group should contain
atleast one logfile.
How to drop a group 3 including all members :
sys>select * from v$logfile;
sys>select * from v$log;
sys>alter system switch
logfile;
sys>alter database drop
logfile group 3;
No comments:
Post a Comment