Redo logs

    You are here:  
  1. Home
  2. ORACLE DBA
  3. General
  4. File Management
  5. Redo logs
Hits: 3508
Rating:
( 1 Rating )

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

Size

SQL> SELECT SUM (BYTES*BLOCKSIZE/1024/1024/1024/1024) FSIZE_GB FROM V$LOG;
SQL> SELECT SUM (BYTES*BLOCKSIZE/1024/1024/1024) FSIZE_MB FROM V$LOG;

Multiplexing

SQL> HOST mkdir -p /u02/oracle/oradata/${ORACLE_SID}/redo
SQL> HOST mkdir -p /u03/oracle/oradata/${ORACLE_SID}/redo

SQL>
set linesize 250;
set pagesize 0;
col GROUP# format 99;
col MEMBER format a60;
col STATUS format a10;
col Gb format 9999;
select a.group#, member, a.status, bytes/1024/1024 as "Mb"
from v$log a, v$logfile b
where a.group# = b.group#
order by 1;

1 /u02/oracle/oradata/orcl/redo01.log INACTIVE     50
2 /u02/oracle/oradata/orcl/redo02.log INACTIVE     50
3 /u02/oracle/oradata/orcl/redo03.log CURRENT      50

RAC

SQL>
col thread# format 99;
select a.group#,thread#,member, a.status, bytes/1024/1024 as "Gb"
from v$log a, v$logfile b
where a.group# = b.group#
order by 1;

1     1 +DATAC1/cbs3stdb/onlinelog/group_t1_01a.log     INACTIVE 1024
1     1 +DATAC1/cbs3stdb/onlinelog/group_t1_01b.log     INACTIVE 1024
2     1 +DATAC1/cbs3stdb/onlinelog/group_t1_02a.log     CURRENT 1024
2     1 +DATAC1/cbs3stdb/onlinelog/group_t1_02b.log     CURRENT 1024
…

RAC

SQL> select group#,thread#,members,status from v$log;

  GROUP#    THREAD#    MEMBERS STATUS
---------- ---------- ---------- ----------------
         1          1          2 INACTIVE
         2          1          2 INACTIVE
         3          1          2 CURRENT
         4          1          2 INACTIVE
         5          1          2 INACTIVE
         6          2          2 INACTIVE
         7          2          2 INACTIVE
         8          2          2 CURRENT
         9          2          2 INACTIVE
        10          2          2 INACTIVE

We can delete only files of inactive group

* How to switch group we will see later

Let's delete files of INACTIVE group

1) We need to re-create group 1 and files

Delete files of groups 1

SQL> alter database drop logfile group 1;

Swithers for standby server, if you can't delate groups

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

SQL> HOST rm /u01/oracle/oradata/orcl/redo01.log

Add new group, set files and their size

SQL> alter database add logfile group 1 ('/u02/oracle/oradata/orcl/redo/redo01.log' , '/u03/oracle/oradata/orcl/redo/redo01.log') size 300M;

RAC with 3 nodes and ASM example

SQL> alter database add logfile thread 1 group 1 ('+DATA/orcl/onlinelog/group_t1_01a.log','+DATA/orcl/onlinelog/group_t1_01b.log') size 1024m;
SQL> alter database add logfile thread 1 group 2 ('+DATA/orcl/onlinelog/group_t1_02a.log','+DATA/orcl/onlinelog/group_t1_02b.log') size 1024m;

SQL> alter database add logfile thread 2 group 3 ('+DATA/orcl/onlinelog/group_t2_03a.log','+DATA/orcl/onlinelog/group_t2_03b.log') size 1024m;
SQL> alter database add logfile thread 2 group 4 ('+DATA/orcl/onlinelog/group_t2_04a.log','+DATA/orcl/onlinelog/group_t2_04b.log') size 1024m;

SQL> alter database add logfile thread 3 group 5 ('+DATA/orcl/onlinelog/group_t3_05a.log','+DATA/orcl/onlinelog/group_t3_05b.log') size 1024m;
SQL> alter database add logfile thread 3 group 6 ('+DATA/orcl/onlinelog/group_t3_06a.log','+DATA/orcl/onlinelog/group_t3_06b.log') size 1024m;

SQL> alter database add logfile thread 1 group 7 ('+DATA/orcl/onlinelog/group_t1_07a.log','+DATA/orcl/onlinelog/group_t1_07b.log') size 1024m;
SQL> alter database add logfile thread 2 group 8 ('+DATA/orcl/onlinelog/group_t2_08a.log','+DATA/orcl/onlinelog/group_t2_08b.log') size 1024m;
SQL> alter database add logfile thread 3 group 9 ('+DATA/orcl/onlinelog/group_t3_09a.log','+DATA/orcl/onlinelog/group_t3_09b.log') size 1024m;

group thread members status
     1 1   2 CURRENT
     2 1   2 INACTIVE
     3 2   2 INACTIVE
     4 2   2 ACTIVE
     5 3   2 INACTIVE
     6 3   2 ACTIVE
     7 1   2 CURRENT
     8 2   2 INACTIVE
     9 3   2 INACTIVE

RAC with 6 nodes and ASM example (Exadata)

SQL> alter database add logfile thread 1 group 1 ('+DATA/orcl/onlinelog/group_1.1541.810559891','+DATA/orcl/onlinelog/group_1.1542.810559893') size 1024m;
SQL> alter database add logfile thread 1 group 2 ('+DATA/orcl/onlinelog/group_2.1543.810559893','+DATA/orcl/onlinelog/group_2.1544.810559895') size 1024m;

SQL> alter database add logfile thread 2 group 3 ('+DATA/orcl/onlinelog/group_3.1568.810560001','+DATA/orcl/onlinelog/group_3.1567.810559999') size 1024m;
SQL> alter database add logfile thread 2 group 4 ('+DATA/orcl/onlinelog/group_4.1570.810560003','+DATA/orcl/onlinelog/group_4.1569.810560001') size 1024m;

SQL> alter database add logfile thread 3 group 5 ('+DATA/orcl/onlinelog/group_5.1564.810559997','+DATA/orcl/onlinelog/group_5.1563.810559995') size 1024m;
SQL> alter database add logfile thread 3 group 6 ('+DATA/orcl/onlinelog/group_6.1565.810559997','+DATA/orcl/onlinelog/group_6.1566.810559999') size 1024m;

SQL> alter database add logfile thread 4 group 7 ('+DATA/orcl/onlinelog/group_7.1559.810559993','+DATA/orcl/onlinelog/group_7.1560.810559993') size 1024m;
SQL> alter database add logfile thread 4 group 8 ('+DATA/orcl/onlinelog/group_8.1561.810559993','+DATA/orcl/onlinelog/group_8.1562.810559995') size 1024m;

SQL> alter database add logfile thread 5 group 9 ('+DATA/orcl/onlinelog/group_9.1555.810559989','+DATA/orcl/onlinelog/group_9.1556.810559989') size 1024m;
SQL> alter database add logfile thread 5 group 10 ('+DATA/orcl/onlinelog/group_10.1557.810559991','+DATA/orcl/onlinelog/group_10.1558.810559991') size 1024m;

SQL> alter database add logfile thread 6 group 11 ('+DATA/orcl/onlinelog/group_11.1552.810559985','+DATA/orcl/onlinelog/group_11.1551.810559985') size 1024m;
SQL> alter database add logfile thread 6 group 12 ('+DATA/orcl/onlinelog/group_12.1554.810559987','+DATA/orcl/onlinelog/group_12.1553.810559987') size 1024m;

SQL> alter database add logfile thread 1 group 13 ('+DATA/orcl/onlinelog/group_13.1281.810573215','+DATA/orcl/onlinelog/group_13.1503.810573217') size 1024m;
SQL> alter database add logfile thread 2 group 14 ('+DATA/orcl/onlinelog/group_14.1502.810573285','+DATA/orcl/onlinelog/group_14.1501.810573285') size 1024m;
SQL> alter database add logfile thread 3 group 15 ('+DATA/orcl/onlinelog/group_15.1500.810573295','+DATA/orcl/onlinelog/group_15.1499.810573297') size 1024m;
SQL> alter database add logfile thread 4 group 16 ('+DATA/orcl/onlinelog/group_16.1498.810573307','+DATA/orcl/onlinelog/group_16.1497.810573309') size 1024m;
SQL> alter database add logfile thread 5 group 17 ('+DATA/orcl/onlinelog/group_17.1496.810573321','+DATA/orcl/onlinelog/group_17.1495.810573323') size 1024m;
SQL> alter database add logfile thread 6 group 18 ('+DATA/orcl/onlinelog/group_18.1494.810573339','+DATA/orcl/onlinelog/group_18.1493.810573341') size 1024m;

group#     thread# members status
     1 1   2 CURRENT
     2 1   2 INACTIVE
     3 2   2 INACTIVE
     4 2   2 ACTIVE
     5 3   2 INACTIVE
     6 3   2 ACTIVE
     7 4   2 CURRENT
     8 4   2 INACTIVE
     9 5   2 INACTIVE
    10 5   2 CURRENT
    11 6   2 INACTIVE
    12 6   2 CURRENT
    13 1   2 ACTIVE
    14 2   2 CURRENT
    15 3   2 CURRENT
    16 4   2 ACTIVE
    17 5   2 INACTIVE
    18 6   2 INACTIVE

2) Then re-create group 2 and files

SQL> alter database drop logfile group 2;
SQL> HOST rm /u01/oracle/oradata/orcl/redo02.log

SQL> alter database add logfile group 2 ('/u02/oracle/oradata/orcl/redo/redo02.log' , '/u03/oracle/oradata/orcl/redo/redo02.log') size 300M;

3) Re-create Нужно пересоздать группу 3 и файлы данной группы

If group is active, we should switch it

* Use this commands to switch redo logfile group

SQL> alter system checkpoint;
SQL> alter system switch logfile; 

Delete group 3

SQL> alter database drop logfile group 3;
SQL> HOST rm /u01/oracle/oradata/orcl/redo03.log 

SQL> alter database add logfile group 3 ('/u02/oracle/oradata/orcl/redo/redo03.log' , '/u03/oracle/oradata/orcl/redo/redo03.log') size 300M; 

4) Check result 

SQL>
select a.group#, member, a.status, bytes/1024/1024 as "MB"
from v$log a, v$logfile b
where a.group# = b.group#
order by 1,2; 

1 /u02/oracle/oradata/orcl/redo/redo01.log INACTIVE 300
1 /u03/oracle/oradata/orcl/redo/redo01.log INACTIVE 300

2 /u02/oracle/oradata/orcl/redo/redo02.log CURRENT 300
2 /u03/oracle/oradata/orcl/redo/redo02.log CURRENT 300

3 /u02/oracle/oradata/orcl/redo/redo03.log UNUSED 300
3 /u03/oracle/oradata/orcl/redo/redo03.log UNUSED 300 

 

STANDBY LOGFILES, resize standby redolog files example

Show Info

SQL> 
set lines 155 pages 9999
col thread# for 9999990
col sequence# for 999999990
col grp for 990
col fnm for a50 head "File Name"
col "Fisrt SCN Number" for 999999999999990
break on thread
# skip 1
select a.thread#
,a.sequence#
,a.group# grp     
, a.bytes/1024/1024 Size_MB     
,a.status     
,a.archived     
,a.first_change# "First SCN Number"     
,to_char(FIRST_TIME,'DD-Mon-RR HH24:MI:SS') "First SCN Time"   
,to_char(LAST_TIME,'DD-Mon-RR HH24:MI:SS') "Last SCN Time"  from
 v$standby_log a  order by 1,2,3,4; 

1) On primary defer log shipping (dynamic change)

SQL> alter system set log_archive_dest_state_2 = defer scope = memory; 

2) On standby database cancel managed recovery

SQL> alter database recover managed standby database cancel;

3) Drop standby logs on standby database

SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 4;
SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 5;
SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 6;
SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 7; 

4) Recreate the new Standby logs, ASM

SQL> alter database add standby logfile THREAD 1 group 4 ('+DATA(ONLINELOG)','+FRA(ONLINELOG)') SIZE 1000M;
SQL> alter database add standby logfile THREAD 1 group 5 ('+DATA(ONLINELOG)','+FRA(ONLINELOG)') SIZE 1000M;
SQL> alter database add standby logfile THREAD 1 group 6 ('+DATA(ONLINELOG)','+FRA(ONLINELOG)') SIZE 1000M;
SQL> alter database add standby logfile THREAD 1 group 7 ('+DATA(ONLINELOG)','+FRA(ONLINELOG)') SIZE 1000M; 

5) Enable log shipping on the Primary database (memory - temporary)

SQL> alter system set log_archive_dest_state_2 = enable scope = memory;

6) Enable managed recovery on standby database

SQL> alter database recover managed standby database using current logfile disconnect;

7) Check the the standby logs are being used

SQL> 
set lines 155 pages 9999
col thread# for 9999990
col sequence# for 999999990
col grp for 990
col fnm for a50 head "File Name"
col "Fisrt SCN Number" for 999999999999990
break on thread
# skip 1
select a.thread#
,a.sequence#
,a.group# grp     
, a.bytes/1024/1024 Size_MB     
,a.status     
,a.archived     
,a.first_change# "First SCN Number"     
,to_char(FIRST_TIME,'DD-Mon-RR HH24:MI:SS') "First SCN Time"   
,to_char(LAST_TIME,'DD-Mon-RR HH24:MI:SS') "Last SCN Time"  from
 v$standby_log a  order by 1,2,3,4; 

Drop Logfile only example

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u02/oracle/oradata/orcl/redo/redo01.log'
- group 1 should not be current and not be an active
- group 1 should be multiplexed
- and mount DB, please

Newsletter

Enter your email to join our mailing list

Tag Cloud

Oracle DBA Exadata Linux

Quick Contact

Search