Managing Redolog

Redo log groups and files

posted Mar 21, 2011, 1:04 PM by Sachchida Ojha   [ updated Mar 21, 2011, 1:21 PM ]

 select GROUP#,STATUS,MEMBER from V$LOGFILE order by GROUP#
SQL> /

    GROUP# STATUS  MEMBER
---------- ------- ----------------------------------------------------------------
         7         /mnt/u05/RPTDB/oradata/redo07.log
         7         /mnt/u06/RPTDB/oradata/redo07.log
         8         /mnt/u06/RPTDB/oradata/redo08.log
         8         /mnt/u05/RPTDB/oradata/redo08.log
         9         /mnt/u05/RPTDB/oradata/redo09.log
         9         /mnt/u06/RPTDB/oradata/redo09.log
        10         /mnt/u05/RPTDB/oradata/redo10.log
        10         /mnt/u06/RPTDB/oradata/redo10.log
        11         /mnt/u05/RPTDB/oradata/redo11.log
        11         /mnt/u06/RPTDB/oradata/redo11.log
        12         /mnt/u06/RPTDB/oradata/redo12.log
        12         /mnt/u05/RPTDB/oradata/redo12.log

12 rows selected.

SQL> ALTER DATABASE ADD LOGFILE ('/mnt/u05/RPTDB/oradata/log1a.log', '/mnt/u06/RPTDB/oradata/log1b.log') SIZE 512M;
ALTER DATABASE ADD LOGFILE ('/mnt/u05/RPTDB/oradata/log2a.log', '/mnt/u06/RPTDB/oradata/log2b.log') SIZE 512M;
ALTER DATABASE ADD LOGFILE ('/mnt/u05/RPTDB/oradata/log3a.log', '/mnt/u06/RPTDB/oradata/log3b.log') SIZE 512M;
ALTER DATABASE ADD LOGFILE ('/mnt/u05/RPTDB/oradata/log4a.log', '/mnt/u06/RPTDB/oradata/log4b.log') SIZE 512M;
ALTER DATABASE ADD LOGFILE ('/mnt/u05/RPTDB/oradata/log5a.log', '/mnt/u06/RPTDB/oradata/log5b.log') SIZE 512M;

Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL> select GROUP#,STATUS,MEMBER from V$LOGFILE order by GROUP#;

    GROUP# STATUS  MEMBER
---------- ------- ----------------------------------------------------------------
         1         /mnt/u06/RPTDB/oradata/log1b.log
         1         /mnt/u05/RPTDB/oradata/log1a.log
         2         /mnt/u06/RPTDB/oradata/log2b.log
         2         /mnt/u05/RPTDB/oradata/log2a.log
         3         /mnt/u06/RPTDB/oradata/log3b.log
         3         /mnt/u05/RPTDB/oradata/log3a.log
         4         /mnt/u05/RPTDB/oradata/log4a.log
         4         /mnt/u06/RPTDB/oradata/log4b.log
         5         /mnt/u05/RPTDB/oradata/log5a.log
         5         /mnt/u06/RPTDB/oradata/log5b.log
         7         /mnt/u05/RPTDB/oradata/redo07.log
         7         /mnt/u06/RPTDB/oradata/redo07.log
         8         /mnt/u06/RPTDB/oradata/redo08.log
         8         /mnt/u05/RPTDB/oradata/redo08.log
         9         /mnt/u06/RPTDB/oradata/redo09.log
         9         /mnt/u05/RPTDB/oradata/redo09.log
        10         /mnt/u06/RPTDB/oradata/redo10.log
        10         /mnt/u05/RPTDB/oradata/redo10.log
        11         /mnt/u05/RPTDB/oradata/redo11.log
        11         /mnt/u06/RPTDB/oradata/redo11.log
        12         /mnt/u06/RPTDB/oradata/redo12.log
        12         /mnt/u05/RPTDB/oradata/redo12.log

22 rows selected.

SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

    GROUP# ARC STATUS
---------- --- ----------------
         1 YES UNUSED
         2 YES UNUSED
         3 YES UNUSED
         4 YES UNUSED
         5 YES UNUSED
         7 NO  CURRENT
         8 YES UNUSED
         9 YES UNUSED
        10 YES UNUSED
        11 YES UNUSED
        12 YES UNUSED

11 rows selected.

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 2;

Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 4;

Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 5;

Database altered.

SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

    GROUP# ARC STATUS
---------- --- ----------------
         7 NO  CURRENT
         8 YES UNUSED
         9 YES UNUSED
        10 YES UNUSED
        11 YES UNUSED
        12 YES UNUSED

6 rows selected.

SQL>

SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

    GROUP# ARC STATUS
---------- --- ----------------
         1 YES UNUSED
         2 YES UNUSED
         3 YES UNUSED
         4 YES UNUSED
         5 YES UNUSED
         7 NO  CURRENT
         8 YES UNUSED

7 rows selected.

SQL>  ALTER DATABASE DROP LOGFILE GROUP 8;

Database altered.

SQL>  SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

    GROUP# ARC STATUS
---------- --- ----------------
         1 YES UNUSED
         2 YES UNUSED
         3 YES UNUSED
         4 YES UNUSED
         5 YES UNUSED
         7 NO  CURRENT

6 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

    GROUP# ARC STATUS
---------- --- ----------------
         1 YES ACTIVE
         2 YES ACTIVE
         3 YES ACTIVE
         4 NO  CURRENT
         5 YES UNUSED
         7 YES ACTIVE

6 rows selected.

SQL>  alter system switch logfile;

System altered.

SQL>  SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

    GROUP# ARC STATUS
---------- --- ----------------
         1 YES ACTIVE
         2 YES ACTIVE
         3 YES ACTIVE
         4 YES ACTIVE
         5 NO  CURRENT
         7 YES ACTIVE

6 rows selected.

SQL>  SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

    GROUP# ARC STATUS
---------- --- ----------------
         1 YES ACTIVE
         2 YES ACTIVE
         3 YES ACTIVE
         4 YES ACTIVE
         5 NO  CURRENT
         7 YES ACTIVE

6 rows selected.

SQL> /

    GROUP# ARC STATUS
---------- --- ----------------
         1 YES ACTIVE
         2 YES ACTIVE
         3 YES ACTIVE
         4 YES ACTIVE
         5 NO  CURRENT
         7 YES ACTIVE

6 rows selected.

SQL> /

SQL>  select BYTES/(1024*1024) from v$log;

BYTES/(1024*1024)
-----------------
              512
              512
              512
              512
              512

SQL>




1-1 of 1