Add new redo group and change size of redo

Add new redo group and change size of redo
https://docs.oracle.com/database/121/OSTMG/GUID-42519F90-AB27-46A8-9E59-18393C56C375.htm#OSTMG13960
1. query
SQL> Select GROUP#,THREAD#,SEQUENCE#,bytes/1024/1024,MEMBERS,STATUS from v$log;
GROUP# THREAD# SEQUENCE# BYTES/1024/1024 MEMBERS STATUS
———- ———- ———- ————— ———- —————-
1 1 13937 100 2 ACTIVE
2 1 13938 100 2 ACTIVE
3 1 13939 100 2 CURRENT
4 1 13936 100 2 INACTIVE

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_ CON_ID
———- ——- ——- ————————————————– — ———-
1 ONLINE +REDO/CDB01/ONLINELOG/group_1.257.1055595795 NO 0
1 ONLINE +DATA/CDB01/ONLINELOG/group_1.258.1055595797 NO 0
2 ONLINE +REDO/CDB01/ONLINELOG/group_2.258.1055595799 NO 0
2 ONLINE +DATA/CDB01/ONLINELOG/group_2.259.1055595799 NO 0
3 ONLINE +REDO/CDB01/ONLINELOG/group_3.259.1055595801 NO 0
3 ONLINE +DATA/CDB01/ONLINELOG/group_3.260.1055595801 NO 0
4 ONLINE +REDO/CDB01/ONLINELOG/group_4.260.1055595803 NO 0
4 ONLINE +DATA/CDB01/ONLINELOG/group_4.261.1055595803 NO 0
There are four redo log groups; make sure that group 1 is not listed as CURRENT
SQL> select group#,status from v$log;

GROUP# STATUS
———- —————-
1 CURRENT
2 ACTIVE
3 ACTIVE
4 ACTIVE
2. add one more redo group
alter database add logfile group 5 ( ‘+REDO/’, ‘+DATA/’) size 1000M;

3. Remove redo group
If it is listed as current, use the following command to set it to INACTIVE
alter system switch logfile;
Confirm group 1 is now INACTIVE
SQL> select group#,status from v$log;

GROUP# STATUS
———- —————-
1 INACTIVE
2 ACTIVE
3 ACTIVE
4 ACTIVE
5 CURRENT
SQL> alter database drop logfile group 1;
If the command fails, run the checkpoint:
alter system checkpoint global;
Now try the command to drop group 1 again, it should complete successfully.
Create a new group with with a larger size. Note that the path to your database location will be different, so edit this command appropriately. For example (remember to use the locations from step 3)
alter database add logfile group 1 ( ‘+REDO/’, ‘+DATA/’) size 1000M;

Make the new group 1 CURRENT
alter system switch logfile;
To confirm group 1 is CURRENT, run:
select group#, status from v$log;

4. repeat steps.
alter system switch logfile;
alter database add logfile group 2 ( ‘+REDO/’, ‘+DATA/’) size 1000M;
alter database drop logfile group 2;

alter database add logfile group 3 ( ‘+REDO/’, ‘+DATA/’) size 1000M;
alter database drop logfile group 3;

alter database add logfile group 4 ( ‘+REDO/’, ‘+DATA/’) size 1000M;
alter database drop logfile group 4;

SQL> show parameter log_buffer

NAME TYPE VALUE
———————————— ———– ——————————
log_buffer big integer 44720K
SQL> show parameter buffer

NAME TYPE VALUE
———————————— ———– ——————————
buffer_pool_keep string
buffer_pool_recycle string
db_block_buffers integer 0
log_buffer big integer 44720K

Leave a Reply

Your email address will not be published. Required fields are marked *