The contents are of my own testing and deployments. Not guaranteed that these might work in your environment. Please test before usage.
REDO LOG FILES:
- Redo log files are operating system files used by Oracle to maintain logs of all transactions performed against the database.
- The primary purpose of these log files is to allow Oracle to recover changes made to the database in the case of a failure.
- An Oracle database must have at least two redo log files, and most databases have more than two. These files are written by the LGWR process in a circular fashion; when the last log file is filled, the first log file is reused.
Example:
If a database has three redo log files, blocks will be written to file1 until it is filled; then that file is closed, and LGWR begins writing to file2 (a log switch).
When file2 is filled, LGWR switches to file3.
When file3 is filled, file1 is reused, and soon.
Important Notes:
Unused - It newly
added redo log group, Until not used.
Current - LGWR
enabled, the redo entries transfer from RLBC to this group
Active - ARWR
enabled. The redo entries transfer from this group to Archiver
Inactive - Its completed the Archive process, Ready to
reuse.
Clearing - its recreated
after clearing the group(alter database
clear logfile group1)
Clearing current – it being
clearing
Member status
Blank – file is in use(now in use)
State – contents as incomplete
Delete – no longer use,drop (os level)
Invalid – unable
to access(created new member)
Views: logfile(group):
SQL> select
* from v$log;
SQL>
alter database add logfile group 4
('/home/oracle/dev/oradata/log/log4a.log') size 50m;
SQL>
alter system switch logfile;
SQL>
alter database drop logfile group4a;
SQL>
alter database clear logfile group 1;
SQL>
alter database clear unarchived logfile group 3;
SQL> select
group#, status from v$log;
SQL>
alter database rename file '/home/oracle/dev/oradata/log/log01.log' to
'/home/oracle/dev/oradata/log/log1a.log';
Logfile(member):
SQL>
select GROUP#,MEMBER,STATUS from v$logfile;
SQL> alter
database add logfile member '/home/oracle/dev/oradata/log/log4b.log' to group 4
SQL>
alter database drop logfile member '/home/oracle/dev/oradata/log/log4b.log';
SQL>
select member from v$logfile where group# = 3;
MEMBER
----------------------
/home/oracle/dev/oradata/log/log03.log
Views:
1)V$logfile
2)V$log
3)V$loghistory
Restriction
to drop groups:
1)An instance
required at least 2 group of online redologfile.
2)An active
or current group cannot be dropped.
3)When an
online redo lofgile group is dropped, the os file not dropped.
Restriction
to drop Members:
1)Can’t drop
the last valid member of the group.
2)Can’t drop
the current log file member.
3)Can’t drop
the unarchived group member.
4)The OMF
features group only allowed to drop a member physically.
SQL> desc v$log;
Name Null?
Type
-----------------------------------------
-------- ----------------------------
GROUP# NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
BYTES NUMBER
BLOCKSIZE NUMBER
MEMBERS NUMBER
ARCHIVED VARCHAR2(3)
STATUS VARCHAR2(16)
FIRST_CHANGE#
NUMBER
FIRST_TIME DATE
NEXT_CHANGE# NUMBER
NEXT_TIME DATE
CON_ID NUMBER
SQL> desc v$logfile;
Name Null?
Type
-----------------------------------------
-------- ----------------------------
GROUP# NUMBER
STATUS VARCHAR2(7)
TYPE VARCHAR2(7)
MEMBER VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
CON_ID NUMBER
SQL> select GROUP#,MEMBERS,STATUS
from v$log;
---------- ----------
----------------
1 1 CURRENT
2 1 INACTIVE
3 1 INACTIVE
4 1 INACTIVE
---------- ----------
----------------
1 1 ACTIVE
2 1 INACTIVE
3 1 INACTIVE
4 1 CURRENT
---------- ----------
----------------
1 1 INACTIVE
2 1 INACTIVE
3 1 CURRENT
4 1 ACTIVE
SQL> alter system switch logfile;
GROUP# MEMBERS STATUS
---------- ----------
----------------
1 1 INACTIVE
2 1 CURRENT
3 1 ACTIVE
4 1 ACTIVE
SQL> alter database drop logfile member
'/home/oracle/dev/oradata/log/log4c.log'
3 Comments
Super👏👍
ReplyDelete👌
ReplyDelete𝑵𝒊𝒄𝒆
ReplyDelete