Resizing redo logs and standby Redologs in Dataguard

by

IN PRIMARY DATABASE :-

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
---------------------------------------------------
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
---------------------------
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 --