Resizing redo logs and standby Redologs in Dataguard
6 min readIN 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 --
