Managing Redo Logfiles in Oracle database 11gR2
Posted by Mir Sayeed Hassan on February 7th, 2018
Managing Redo Logfiles in Oracle database 11gR2
Brief about the Redo logfiles in an Oracle databases it should contain atleast 2 redo logfile group as mandatory & Oracle writes every statement expect the select statement, Example – update etc, The Oracle writes to the datafile in case if there is power failure or any system crush occur before the row is written to the disk, that’s why the oracle writes the statement into the redo logfile, More information refer the Oracle Guide
Below is the different test scenario in redo logfiles
Considering my test database:
sys@TESTDB> select INSTANCE_NAME,VERSION from V$instance; INSTANCE_NAME VERSION ---------------- ----------- testdb 11.2.0.4.0
Verify the current redo logfile present in the database.
sys@TESTDB> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC ---------- ---------- ---------- ---------- ---------- ---------- --- STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------------- ------------- --------- ------------ --------- 1 1 12448 104857600 512 1 NO INACTIVE 2.2859E+11 05-FEB-18 2.2859E+11 06-FEB-18 2 1 12451 104857600 512 1 NO CURRENT 2.2859E+11 07-FEB-18 2.8147E+14
sys@TESTDB> select member from V$logfile; MEMBER ------------------------------------------- /u01/app/oracle/oradata/prim/redo1.log /u01/app/oracle/oradata/prim/redo2.log
Add a New Redo Logfile Group
sys@TESTDB> alter database add logfile group 3 '/u01/app/oracle/oradata/prim/redo3.log' size 10m; Database altered.
Add members to an existing group
sys@TESTDB> alter database add logfile member '/u01/app/oracle/oradata/prim/redo3a.log' to group 3; Database altered.
Verify:
sys@TESTDB> select member from V$logfile; MEMBER -------------------------------------------------------------------------- /u01/app/oracle/oradata/prim/redo1.log /u01/app/oracle/oradata/prim/redo2.log /u01/app/oracle/oradata/prim/redo3.log ---------- Add a group 3 /u01/app/oracle/oradata/prim/redo3a.log ---------- Add a member in group 3
Dropping Members of a group
Note:
If you are trying to drop member from a log group, only if group has more than 1 member in it & its should not be a current group – In case if you like to drop a member from the current group, perform some log switch & you have to wait for some time where the log switch is inactive & other group become current.
Consider I need to drop the member from the group 3 as shown below:
sys@TESTDB> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC ---------- ---------- ---------- ---------- ---------- ---------- --- STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------------- ------------- --------- ------------ --------- 1 1 12453 104857600 512 1 NO INACTIVE 2.2859E+11 07-FEB-18 2.2859E+11 07-FEB-18 2 1 12454 104857600 512 1 NO ACTIVE 2.2859E+11 07-FEB-18 2.2859E+11 07-FEB-18 3 1 12455 10485760 512 2 NO -- It contain 2 member in it CURRENT 2.2859E+11 07-FEB-18 2.8147E+14
sys@TESTDB> alter database drop logfile member '/u01/app/oracle/oradata/prim/redo3a.log'; alter database drop logfile member '/u01/app/oracle/oradata/prim/redo3a.log' * ERROR at line 1: ORA-01609: log 3 is the current log for thread 1 - cannot drop members ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/prim/redo3a.log'
To drop this member, perform some log switch & wait for another member current
sys@TESTDB> alter system switch logfile; System altered. sys@TESTDB> / System altered.
sys@TESTDB> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC ---------- ---------- ---------- ---------- ---------- ---------- --- STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------------- ------------- --------- ------------ --------- 1 1 12456 104857600 512 1 NO ACTIVE 2.2859E+11 07-FEB-18 2.2859E+11 07-FEB-18 2 1 12457 104857600 512 1 NO CURRENT 2.2859E+11 07-FEB-18 2.8147E+14 3 1 12455 10485760 512 2 NO ACTIVE 2.2859E+11 07-FEB-18 2.2859E+11 07-FEB-18 – This status changes from current to active
Now try to drop the member:
sys@TESTDB> alter database drop logfile member '/u01/app/oracle/oradata/prim/redo3a.log'; Database altered.
Note:
After you drop logfiles the files are not deleted from the disk & the OS file remain to exist, manually delete the OS files from disk.
Dropping Logfile Group
Similarly, drop logfile group only if the database is having more than 2 groups & it should not the current group as (tested above scenario for dropping a member)
sys@TESTDB> alter database drop logfile group 3; Database altered.
Note:
After you drop logfiles the files are not deleted from the disk & the OS file remain to exist, manually delete the OS files from disk.
Resizing Logfiles
As per the Oracle standard, you cannot resize logfiles. In case if you want to resize a logfile, Need create a new logfile group with the new size & drop the existing logfile group.
Renaming or Relocating Logfiles
To Rename or Relocate Logfiles perform the below steps
Example:
Consider you want to move a logfile from ‘/u01/app/oracle/oradata/prim/redo2.log’ to /home/redolog_new/redo2.log’, then follow the below steps:
Shutdown database:
sys@TESTDB> shu immediate Database closed. Database dismounted. ORACLE instance shut down.
Move the logfile from existing location to new location using OS command
[oracle@ogg-test1 prim]$ mv /u01/app/oracle/oradata/prim/redo2.log /home/redolog_new/redo2.log
Start and mount the database
testdb> startup mount ORACLE instance started. Total System Global Area 2455228416 bytes Fixed Size 2255712 bytes Variable Size 771753120 bytes Database Buffers 1660944384 bytes Redo Buffers 20275200 bytes Database mounted.
Now change the location in controlfile
testdb> alter database rename file '/u01/app/oracle/oradata/prim/redo2.log' to '/home/redolog_new/redo2.log'; Database altered.
Open the database
testdb> alter database open; Database altered.
Fetch the member contain in database:
testdb> SELECT * FROM V$LOG; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC ---------- ---------- ---------- ---------- ---------- ---------- --- STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------------- ------------- --------- ------------ --------- 1 1 12465 104857600 512 1 NO INACTIVE 2.2859E+11 07-FEB-18 2.2859E+11 07-FEB-18 2 1 12466 104857600 512 1 NO CURRENT 2.2859E+11 07-FEB-18 2.8147E+14
Fetch the member contain in database:
testdb> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- /u01/app/oracle/oradata/prim/redo1.log /home/redolog_new/redo2.log ------------------ Hence we have to change the location of the redo logfile
Regarding the Clearing Redo Logfiles
When the redo log file might become corrupted while the database is open, & the stop database perform the activity because archiving cannot continue in the database, In such scenario ALTER DATABASE CLEAR LOGFILE statement can be used to reinitialize the file without shutting down/bounce the database.
The below statement clears the log files in redo log group number 2:
ALTER DATABASE CLEAR LOGFILE GROUP 2;
Different scenario to overcome where dropping redo logs are not possible:
– If there are only 2 log groups
– The corrupt redo log file belongs to the current group
In case if the corrupt redo log file has not been archived, use the UNARCHIVED as shown below.
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
The above statement clears the corrupted redo logs & avoids archiving them. Therefore the cleared redo logs are available for use even though they were not archived.
In case if you clear a log file that is needed for recovery of a backup in a database, then you can no longer recover from that backup. The database will write a message in the alert log describing the backups from which you cannot recover.