A technical troubleshooting blog about Oracle with other Databases & Cloud Technologies.

Resizing redo logs and standby Redologs in Dataguard

6 min read
tailor measuring shoulder of mannequin in light studio

Photo by Michael Burrows on Pexels.com

IN PRIMARY DATABASE :-

1) Check Instance details :-

select status,instance_name,database_role from v$database,v$instance;

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

2) Check redo log and standby redo log size in Primary and standby :-

Redo log :- use script referred at the end
select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;

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

3) Check if the parameter standby_file_management is set to AUTO or MANUAL on standby database. If it is not set to MANUAL, then set it.                                                                        

SQL> show parameter standby_file_management

           NAME				             TYPE	             VALUE
------------------------------------ ----------- ------------------------------
     standby_file_management 	        string	              AUTO
	 
	 
SQL> alter system set standby_file_management=manual;



4) On the primary database:-

Check the status of the Online Redo Logs and resize them by dropping the INACTIVE redo logs and re-creating them with the new size.

SQL> @redologsize.sql

GROUP# THREAD# MEMBER								      ARCHIVED	 STATUS     Size (MB)
------ ------- ---------------------------------------------------------------------- ---------- ---------- ---------
     1	     1 /u01/app/oracle/oradata/DG/redo01.log				      YES	 INACTIVE	  200
     2	     1 /u01/app/oracle/oradata/DG/redo02.log				      NO	 CURRENT	  200
     3	     1 /u01/app/oracle/oradata/DG/redo03.log				      YES	 INACTIVE	  200


SQL> alter database drop logfile group 1;

SQL> alter database add logfile group 1 '/u01/app/oracle/oradata/DG/redo01.log' size 250M reuse;

SQL> alter database drop logfile group 3;

SQL> alter database add logfile group 3 '/u01/app/oracle/oradata/DG/redo03.log' size 250M reuse;

5>Now that Online Redo Log Groups 1 and 2 are resized and the status of Group 2 is CURRENT, switch logfiles manually until Group 2 becomes INACTIVE :-

SQL> @redologsize.sql

GROUP# THREAD# MEMBER								      ARCHIVED	 STATUS     Size (MB)
------ ------- ---------------------------------------------------------------------- ---------- ---------- ---------
     1	     1 /u01/app/oracle/oradata/DG/redo01.log				      YES	 UNUSED 	  250
     2	     1 /u01/app/oracle/oradata/DG/redo02.log				      NO	 CURRENT	  200
     3	     1 /u01/app/oracle/oradata/DG/redo03.log				      YES	 UNUSED 	  250

SQL>  alter system switch logfile;

SQL> @redologsize.sql

GROUP# THREAD# MEMBER								      ARCHIVED	 STATUS     Size (MB)
------ ------- ---------------------------------------------------------------------- ---------- ---------- ---------
     1	     1 /u01/app/oracle/oradata/DG/redo01.log				      NO	 CURRENT	  250
     2	     1 /u01/app/oracle/oradata/DG/redo02.log				      YES	 INACTIVE	  200
     3	     1 /u01/app/oracle/oradata/DG/redo03.log				      YES	 INACTIVE	  250

Now drop group 2 :-

SQL> alter database drop logfile group 2;

SQL> alter database add logfile group 2 '/u01/app/oracle/oradata/DG/redo02.log' size 250M reuse;

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

6>Do log switch and make all usable

 alter system switch logfile;
 
 alter system checkpoint;                      <---------- TO REGISTER THE CHANGES IN CONTROL FILE
 
------------------------------------------------------------------------------------------------------------------------------------ 
 
7>Moving on to the Standby Redo Logs on the Primary Database:-
 
 
 SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;

GROUP# size in MB
------ ----------
     6	      201
\
\

SQL> select group#,status from v$standby_log;

GROUP# STATUS
------ ----------
     4 UNASSIGNED
     5 UNASSIGNED
\
\

SQL> select group#,member from v$logfile;

GROUP# MEMBER
------ ----------------------------------------------------------------------
     4 /u01/app/oracle/oradata/DG/stndby1.log
     5 /u01/app/oracle/oradata/DG/stndby2.log
     6 /u01/app/oracle/oradata/DG/stndby3.log
     7 /u01/app/oracle/oradata/DG/stndby4.log

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

8>Drop all standby logfile like below

SQL> alter database drop standby logfile group 4;  [ SAME FOR 5,6,& 7 GROUP STANDBY LOGFILES ]

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

9>Add standby redo file with same size.

# REPEAT THIS FOR THE REMAINING SRLS ON THE PRIMARY DATABASE AND WE CAN SEE BELOW THAT ALL THE SRL ON THE PRIMARY DATABASE HAVE BEEN RESIZED.

alter database add standby logfile group 4 '/u01/app/oracle/oradata/DG/stndby1.log' size 251M reuse;
alter database add standby logfile group 5 '/u01/app/oracle/oradata/DG/stndby2.log' size 251M reuse;
alter database add standby logfile group 6 '/u01/app/oracle/oradata/DG/stndby3.log' size 251M reuse;
alter database add standby logfile group 7 '/u01/app/oracle/oradata/DG/stndby4.log' size 251M reuse;



-------------------------------------
Moving on to the standby database:- 
-------------------------------------
1) SQL> select group#,status from v$log;

GROUP# STATUS
------ ----------
     1 UNUSED
     2 UNUSED
     3 UNUSED

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

2) SQL> select group#,status from v$log;

GROUP#          STATUS
---------- ----------------
1              CURRENT
3              CLEARING
2              CLEARING

# LETS TRY TO DROP ONLINE REDO LOG GROUP 2 AS GROUP 1 IS CURRENT STATUS AND IT CANNOT BE DROPPED.

SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
# ERROR at line 1:
# ORA-01156: recovery or flashback in progress may need access to files

# Here above, we faced ORA-01156 error, which is self-explanatory. The recovery (MRP process) on the standby database is active and it needs 
  to be cancelled before we drop any Redo log groups
  
------------------------------------------------------------------------------------------------------------------------------------

3) SQL>  alter database recover managed standby database cancel;

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

4>Since the status of Group 1 is still clearing, lets clear it manually before dropping the group.Repeat for all.

alter database clear logfile group 1;                                       <--------- TO CLEAR LOGFILE MANUALLY

# NOW, DROP LOGFILE, 

SQL> alter database drop logfile group 1;

alter database drop logfile group 1
# ERROR :
# ORA-01275: Operation DROP LOGFILE is not allowed if standby file management is automatic.


SQL>  alter system set standby_file_management=manual;

SQL>  alter database drop logfile group 1;

SQL> alter database add logfile group 1 '/u01/app/oracle/oradata/DG/redo01.log' size 250M reuse;

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

GROUP# STATUS
---------- ----------------
1 CURRENT
3 CLEARING
2 UNUSED

# CHECK THE SIZE OF REDOLOG FILE
SQL> @redologsize.sql 


---------------------------------------------------
5) Resizing Standby Redo Logs on standby database:
---------------------------------------------------

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;

GROUP#   size in MB
------   ----------
   4	    201
   5        201
   6	    201
   7	    201

SQL>  select group#,status from v$standby_log;

GROUP#      STATUS
-------- --------------
4           ACTIVE
5           UNASSIGNED
6           UNASSIGNED
7           UNASSIGNED

SQL> select group#,member from v$logfile order by group#;

GROUP#                MEMBER
------   ----------------------------------------------------------------------
   4      /u01/app/oracle/oradata/DG/stndby1.log
   5      /u01/app/oracle/oradata/DG/stndby2.log
   6      /u01/app/oracle/oradata/DG/stndby3.log
   7      /u01/app/oracle/oradata/DG/stndby4.log

6> SWITCH LOGS AND CLEAR THE SRL GROUP WHOSE STATUS IS ACTIVE AS DONE EARLIER, DROP THE GROUP AND RE-CREATE THEM WITH THE SIZE SAME AS THAT OF THE ONLINE REDO LOGS.

FOR THE SRL GROUPS WHOSE STATUS IS UNASSIGNED, JUST DROP THE GROUP AND RECREATE THEM WITH THE +1 SIZE AS THAT OF THE ONLINE REDO LOGS.

SQL> alter database clear logfile group 5;
SQL> alter database clear logfile group 6;
SQL> alter database clear logfile group 7;

SQL> alter database add standby logfile group 5 '/u01/app/oracle/oradata/DG/stndby2.log' size 251M reuse;
SQL> alter database add standby logfile group 6 '/u01/app/oracle/oradata/DG/stndby3.log' size 251M reuse;
SQL> alter database add standby logfile group 7 '/u01/app/oracle/oradata/DG/stndby4.log' size 251M reuse;

SQL> alter system switch logfile;

SQL> alter database clear logfile group 4;                   <------ CLEAR THE SRL WHICH IS ACTIVE

SQL> alter database drop standby logfile group 4;            <------ DROP THE SRL AFTER CLEARING

SQL> alter database add standby logfile group 4 '/u01/app/oracle/oradata/DG/stndby1.log' size 251M reuse;

7) VERIFY:-

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#;

GROUP# size in MB
------ ----------
     1	      250
     2	      250
     3	      250

SQL>  select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;

GROUP# size in MB
------ ----------
     6	      251
     4	      251
     5	      251
     7	      251

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

8) ONCE ALL THE STANDBY REDO LOGS AND ONLINE REDO LOGS HAVE BEEN RESIZE ON BOTH PRIMARY AND STANDBY DATABASE, SET THE STANDBY_FILE_MANAGEMENT TO "AUTO" ON THE 
   STANDBY DATABASE AND START THE RECOVERY (MRP) ON STANDBY DATABASE.

SQL> alter system set standby_file_management=auto;

SQL> alter database recover managed standby database disconnect from session using current logfile;        <---- MRP START 

SQL> select process,status,sequence# from v$managed_standby;                                               <---- CHECK MRP STATUS

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

9>>Verify sync status:-

Primary:-
SQL> archive log list
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     23
Next log sequence to archive   25
Current log sequence	       25

Standby:-

USe below script:- @standbysync.sql

Scripts used in above part:
---------------------------
1) @standbysync.sql :-
---------------------------

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;

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

-------------------------
2) @redologsize.sql :-
-------------------------

-- Script begins here --
--
-- Please note, this is a sample script
-- provided for educational purposes only
-- and is not certified by Oracle Support for any purpose.

spool log.lst
set echo off
set feedback on
set linesize 120
set pagesize 35
set trim on
set trims on
set lines 120
col group# format 999
col thread# format 999
col member format a70 wrap
col status format a10
col archived format a10
col fsize format 999 heading "Size (MB)"

select l.group#, l.thread#,
f.member,
l.archived,
l.status,
(bytes/1024/1024) fsize
from
v$log l, v$logfile f
where f.group# = l.group#
order by 1,2
/

spool off

-- End of script --