Vignesh Oracle DBA: Resize Logfile Group

View log file location:

SQL> select * from v$logfile;

---------- ------- -------

SQL> set linesize 1000;

SQL> set pagesize 1000;

GROUP# STATUS TYPE MEMBER IS_ CON_ID

1 ONLINE /u01/app/oracle/oradata/TESTDB/redo1.log NO 0

2 ONLINE /u01/app/oracle/oradata/TESTDB/redo2.log NO 0

3 ONLINE /u01/app/oracle/oradata/TESTDB/redo3.log NO 0

Adding new logfile group 4 and 2 members

SQL> alter database add logfile group 4 ('/u01/app/oracle/oradata/TESTDB/redo04a.log','/u01/app/oracle/oradata/TESTDB/redo04b.log') size 100m;

Database altered.

SQL> col member format a50;

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------

1 1 58 41943040 512 1 NO INACTIVE 2026743 26-JUL-21 2040694 26-JUL-21 0

2 1 59 41943040 512 1 NO INACTIVE 2040694 26-JUL-21 2053407 26-JUL-21 0

3 1 60 41943040 512 1 NO CURRENT 2053407 26-JUL-21 1.8447E+19 0

4 1 0 104857600 512 2 YES UNUSED 0 0 0

Drop logfile group 1:

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

Database altered.

Adding new logfile group 5

SQL> alter database add logfile group 5 ('/u01/app/oracle/oradata/TESTDB/redo05a.log','/u01/app/oracle/oradata/TESTDB/redo05b.log') size 50m;

Database altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------

2 1 59 41943040 512 1 NO INACTIVE 2040694 26-JUL-21 2053407 26-JUL-21 0

3 1 60 41943040 512 1 NO CURRENT 2053407 26-JUL-21 1.8447E+19 0

4 1 0 104857600 512 2 YES UNUSED 0 0 0

5 1 0 52428800 512 2 YES UNUSED 0 0 0

Drop logfile group 2

SQL> ALTER DATABASE DROP LOGFILE GROUP 2;

Database altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------

3 1 60 41943040 512 1 NO CURRENT 2053407 26-JUL-21 1.8447E+19 0

4 1 0 104857600 512 2 YES UNUSED 0 0 0

5 1 0 52428800 512 2 YES UNUSED 0 0 0

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------

3 1 60 41943040 512 1 NO ACTIVE 2053407 26-JUL-21 2062712 26-JUL-21 0

4 1 61 104857600 512 2 NO CURRENT 2062712 26-JUL-21 1.8447E+19 0

5 1 0 52428800 512 2 YES UNUSED 0 0 0

Adding log group 6:

SQL> alter database add logfile group 6 ('/u01/app/oracle/oradata/TESTDB/redo06a.log','/u01/app/oracle/oradata/TESTDB/redo06b.log') size 50m;

Database altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------

3 1 60 41943040 512 1 NO ACTIVE 2053407 26-JUL-21 2062712 26-JUL-21 0

4 1 61 104857600 512 2 NO CURRENT 2062712 26-JUL-21 1.8447E+19 0

5 1 0 52428800 512 2 YES UNUSED 0 0 0

6 1 0 52428800 512 2 YES UNUSED 0 0 0

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------

3 1 60 41943040 512 1 NO ACTIVE 2053407 26-JUL-21 2062712 26-JUL-21 0

4 1 61 104857600 512 2 NO ACTIVE 2062712 26-JUL-21 2062768 26-JUL-21 0

5 1 62 52428800 512 2 NO CURRENT 2062768 26-JUL-21 1.8447E+19 0

6 1 0 52428800 512 2 YES UNUSED 0 0 0

SQL> alter system drop logfileALTER DATABASE DROP LOGFILE GROUP 3;

alter system drop logfileALTER DATABASE DROP LOGFILE GROUP 3

ERROR at line 1:

ORA-02065: illegal option for ALTER SYSTEM

SQL> alter system switch logfile;

System altered.

SQL>

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------

3 1 60 41943040 512 1 NO ACTIVE 2053407 26-JUL-21 2062712 26-JUL-21 0

4 1 61 104857600 512 2 NO ACTIVE 2062712 26-JUL-21 2062768 26-JUL-21 0

5 1 62 52428800 512 2 NO ACTIVE 2062768 26-JUL-21 2062802 26-JUL-21 0

6 1 63 52428800 512 2 NO CURRENT 2062802 26-JUL-21 1.8447E+19 0

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------

3 1 64 41943040 512 1 NO ACTIVE 2062809 26-JUL-21 2062812 26-JUL-21 0

4 1 65 104857600 512 2 NO CURRENT 2062812 26-JUL-21 1.8447E+19 0

5 1 62 52428800 512 2 NO ACTIVE 2062768 26-JUL-21 2062802 26-JUL-21 0

6 1 63 52428800 512 2 NO ACTIVE 2062802 26-JUL-21 2062809 26-JUL-21 0

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------

3 1 64 41943040 512 1 NO ACTIVE 2062809 26-JUL-21 2062812 26-JUL-21 0

4 1 65 104857600 512 2 NO ACTIVE 2062812 26-JUL-21 2063502 26-JUL-21 0

5 1 66 52428800 512 2 NO CURRENT 2063502 26-JUL-21 1.8447E+19 0

6 1 63 52428800 512 2 NO INACTIVE 2062802 26-JUL-21 2062809 26-JUL-21 0

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------

3 1 64 41943040 512 1 NO ACTIVE 2062809 26-JUL-21 2062812 26-JUL-21 0

4 1 65 104857600 512 2 NO ACTIVE 2062812 26-JUL-21 2063502 26-JUL-21 0

5 1 66 52428800 512 2 NO ACTIVE 2063502 26-JUL-21 2063511 26-JUL-21 0

6 1 67 52428800 512 2 NO CURRENT 2063511 26-JUL-21 1.8447E+19 0

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------

3 1 68 41943040 512 1 NO CURRENT 2063522 26-JUL-21 1.8447E+19 0

4 1 65 104857600 512 2 NO ACTIVE 2062812 26-JUL-21 2063502 26-JUL-21 0

5 1 66 52428800 512 2 NO ACTIVE 2063502 26-JUL-21 2063511 26-JUL-21 0

6 1 67 52428800 512 2 NO ACTIVE 2063511 26-JUL-21 2063522 26-JUL-21 0

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------

3 1 68 41943040 512 1 NO INACTIVE 2063522 26-JUL-21 2063527 26-JUL-21 0

4 1 69 104857600 512 2 NO CURRENT 2063527 26-JUL-21 1.8447E+19 0

5 1 66 52428800 512 2 NO INACTIVE 2063502 26-JUL-21 2063511 26-JUL-21 0

6 1 67 52428800 512 2 NO INACTIVE 2063511 26-JUL-21 2063522 26-JUL-21 0

Drop logfile group 3

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

Database altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------

4 1 69 104857600 512 2 NO CURRENT 2063527 26-JUL-21 1.8447E+19 0

5 1 66 52428800 512 2 NO INACTIVE 2063502 26-JUL-21 2063511 26-JUL-21 0

6 1 67 52428800 512 2 NO INACTIVE 2063511 26-JUL-21 2063522 26-JUL-21 0

SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;

GROUP# BYTES MEMBERS STATUS

---------- ---------- ---------- ----------------

4 104857600 2 CURRENT

5 52428800 2 INACTIVE

6 52428800 2 INACTIVE

SQL>

Vignesh Oracle DBA

Tuesday, August 17, 2021

Resize Logfile Group

No comments:

Post a Comment

Oracle OS Management Hub in OCI – A Complete Overview

My Blog List

OCI

Performance Tuning

Oracle EBS Queries

Oracle DBA Scripts

Backup and Recovery

Oracle DBA Articles