Multiplexing redo log files.

[oracle@DB ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Sun Oct 30 17:59:02 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, Oracle Label Security, OLAP,
Data Mining, Oracle Database Vault and Real Application Testing options

SQL> SELECT GROUP#,TYPE, MEMBER FROM GV$LOGFILE;

    GROUP# TYPE
---------- -------
MEMBER
--------------------------------------------------------------------------------
         3 ONLINE
+DG_REDO/ERPDB/ONLINELOG/group_3.263.911340093

         2 ONLINE
+DG_REDO/ERPDB/ONLINELOG/group_2.262.911340093

         1 ONLINE
+DG_REDO/ERPDB/ONLINELOG/group_1.261.911340093


SQL> SELECT GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024 SIZE_IN_MB,ARCHIVED,STATUS FROM GV$LOG;

    GROUP#    THREAD#  SEQUENCE# SIZE_IN_MB ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
         1          1      39979        100 NO  ACTIVE
         2          1      39980        100 NO  ACTIVE
         3          1      39981        100 NO  CURRENT

SQL> ALTER DATABASE ADD LOGFILE THREAD 1
GROUP 5 ('+DG_DATA/ERPDB/ONLINELOG/redo-05-01.log','+DG_REDO/ERPDB/ONLINELOG/redo-05-02.log') SIZE 1G,
GROUP 6 ('+DG_DATA/ERPDB/ONLINELOG/redo-06-01.log','+DG_REDO/ERPDB/ONLINELOG/redo-06-02.log') SIZE 1G;
  2    3

Database altered.

SQL> SQL>
SQL>
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024 SIZE_IN_MB,ARCHIVED,STATUS FROM GV$LOG;

    GROUP#    THREAD#  SEQUENCE# SIZE_IN_MB ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
         1          1      39979        100 NO  ACTIVE
         2          1      39980        100 NO  ACTIVE
         3          1      39981        100 NO  CURRENT
         5          1          0       1024 YES UNUSED
         6          1          0       1024 YES UNUSED

SQL> ALTER SYSTEM CHECKPOINT GLOBAL;

System altered.

SQL> ALTER SYSTEM SWITCH LOGFILE ;

System altered.

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024 SIZE_IN_MB,ARCHIVED,STATUS FROM GV$LOG;

    GROUP#    THREAD#  SEQUENCE# SIZE_IN_MB ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
         1          1      39979        100 NO  INACTIVE
         2          1      39980        100 NO  INACTIVE
         3          1      39981        100 NO  INACTIVE
         5          1      39982       1024 NO  ACTIVE
         6          1      39983       1024 NO  CURRENT

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 2;

Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE THREAD 1
GROUP 1 ('+DG_DATA/ERPDB/ONLINELOG/redo-01-01.log','+DG_REDO/ERPDB/ONLINELOG/redo-01-02.log') SIZE 1G,
GROUP 2 ('+DG_DATA/ERPDB/ONLINELOG/redo-02-01.log','+DG_REDO/ERPDB/ONLINELOG/redo-02-02.log') SIZE 1G;
  2    3

Database altered.

SQL> SQL>
SQL> ALTER DATABASE ADD LOGFILE THREAD 1
GROUP 3 ('+DG_DATA/ERPDB/ONLINELOG/redo-03-01.log','+DG_REDO/ERPDB/ONLINELOG/redo-03-02.log') SIZE 1G,
GROUP 4 ('+DG_DATA/ERPDB/ONLINELOG/redo-04-01.log','+DG_REDO/ERPDB/ONLINELOG/redo-04-02.log') SIZE 1G;  2    3

Database altered.

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024 SIZE_IN_MB,ARCHIVED,STATUS FROM GV$LOG;

    GROUP#    THREAD#  SEQUENCE# SIZE_IN_MB ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
         1          1          0       1024 YES UNUSED
         2          1          0       1024 YES UNUSED
         3          1          0       1024 YES UNUSED
         4          1          0       1024 YES UNUSED
         5          1      39982       1024 NO  ACTIVE
         6          1      39983       1024 NO  CURRENT

6 rows selected.

SQL>

Resize the Redo log file.

We can not resize the redo log file. We must drop and recreate redo log file. When we want to drop redo log file it must be in INACTIVE, if redo log file status are not INACTIVE we have to change the status after dropped from database it will update in control file and we need to remove operating system file manually.

Forcing Checkpoint : Forcing a checkpoint are ensure that all changed in the database buffer cache are written in datafile by using ALTER SYSTEM CHECKPOINT GLOBAL;

[oracle@OEL64BN1 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Mon Aug 3 16:08:18 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
SQL> conn /as sysdbda
Connected.
SQL> SELECT GROUP#,SEQUENCE#,BYTES,ARCHIVED,STATUS FROM V$LOG;
    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
 1  49752428800 YES INACTIVE
 2  49652428800 YES INACTIVE
 3  49852428800 NO  CURRENT
SQL>  alter system checkpoint global ;
System altered.
SQL> select sum(bytes)/1024/1024 file_size, group# group_number from v$log group by GROUP#;
 FILE_SIZE GROUP_NUMBER
---------- ------------
50      1
50      2
50      3
SQL> col member format a35
col member format a65
set lines 130
set pages 100
select group#,type, member from v$logfile;SQL> SQL> SQL> SQL>
    GROUP# TYPE    MEMBER
---------- ------- -----------------------------------------------------------------
 3 ONLINE  /u01/app/oracle/oradata/orcln1/redo03.log
 2 ONLINE  /u01/app/oracle/oradata/orcln1/redo02.log
 1 ONLINE  /u01/app/oracle/oradata/orcln1/redo01.log
3 rows selected.
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
Database altered.
SQL> !
[oracle@OEL64BN1 ~]$ rm /u01/app/oracle/oradata/orcln1/redo01.log
[oracle@OEL64BN1 ~]$ exit
exit
SQL> ALTER DATABASE ADD LOGFILE GROUP 1 '/u01/app/oracle/oradata/orcln1/redo01.log' SIZE 1G;
Database altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
Database altered.
SQL> exit    !
[oracle@OEL64BN1 ~]$ exitrm /u01/app/oracle/oradata/orcln1/redo01.log.log2.log
[oracle@OEL64BN1 ~]$ exit
exit
SQL> SELECT GROUP#,SEQUENCE#,BYTES,ARCHIVED,STATUS FROM V$LOG;
    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
 1    0 1073741824 YES UNUSED
 3  49852428800 NO  CURRENT
SQL> ALTER DATABASE ADD LOGFILE GROUP 2 '/u01/app/oracle/oradata/orcln1/redo02.log' SIZE 1G;
Database altered.
SQL> SELECT GROUP#,SEQUENCE#,BYTES,ARCHIVED,STATUS FROM V$LOG;
    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
 1    0 1073741824 YES UNUSED
 2    0 1073741824 YES UNUSED
 3  49852428800 NO  CURRENT
SQL> ALTER SYSTEM SWITCH LOGFILE ;
System altered.
SQL> SELECT GROUP#,SEQUENCE#,BYTES,ARCHIVED,STATUS FROM V$LOG;
    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
 1  499 1073741824 NO  CURRENT
 2    0 1073741824 YES UNUSED
 3  498 52428800 YES ACTIVE
SQL> ALTER SYSTEM SWITCH LOGFILE ;
System altered.
SQL> alter system checkpoint global;
System altered.
SQL> SELECT GROUP#,SEQUENCE#,BYTES,ARCHIVED,STATUS FROM V$LOG;
    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
 1  499 1073741824 YES INACTIVE
 2  500 1073741824 NO  CURRENT
 3  49852428800 YES INACTIVE
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
Database altered.
SQL> !
[oracle@OEL64BN1 ~]$ rm /u01/app/oracle/oradata/orcln1/redo02.log.log3.log
[oracle@OEL64BN1 ~]$ exit
exit
SQL> ALTER DATABASE ADD LOGFILE GROUP 3 '/u01/app/oracle/oradata/orcln1/redo03.log' SIZE 1G;
Database altered.
SQL>  SELECT GROUP#,SEQUENCE#,BYTES,ARCHIVED,STATUS FROM V$LOG;
    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
 1  499 1073741824 YES INACTIVE
 2  500 1073741824 NO  CURRENT
 3    0 1073741824 YES UNUSED
SQL> ALTER SYSTEM SWITCH LOGFILE ;
System altered.
SQL> alter system checkpoint global;
System altered.
SQL> SELECT GROUP#,SEQUENCE#,BYTES,ARCHIVED,STATUS FROM V$LOG;
    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
 1  499 1073741824 YES INACTIVE
 2  500 1073741824 YES INACTIVE
 3  501 1073741824 NO  CURRENT
SQL> select sum(bytes)/1024/1024 file_size, group# group_number from v$log group by GROUP#;
 FILE_SIZE GROUP_NUMBER
---------- ------------
      1024      1
      1024      2
      1024      3

ORA-19502: write error on file "/oradata/cbsdb/REDO/redo01a.log", block number 1351681 (block size=512)

Cause : In my case there are no available space in file system. After increase disk space file was created. 


SQL> ALTER DATABASE ADD LOGFILE GROUP 1
('/oradata/cbsdb/REDO/redo01a.log',
'/u01/app/oracle/fast_recovery_area/redo01b.log'
)
size 1024M;  2    3    4    5
ALTER DATABASE ADD LOGFILE GROUP 1
*
ERROR at line 1:
ORA-19502: write error on file "/oradata/cbsdb/REDO/redo01a.log", block number 1351681 (block size=512)
ORA-27072: File I/O error
Additional information: 4
Additional information: 1351681
Additional information: 122368

[oracle@DB REDO]$ exit
exit
SQL> ALTER DATABASE ADD LOGFILE GROUP 1
('/oradata/spftldb/REDO/redo01a.log',
'/u01/app/oracle/fast_recovery_area/redo01b.log'
)
size 1024M;  2    3    4    5
Database altered.
SQL>