Implementation to Resize Online Redo Logs and Add Standby Redo Logs in RAC Environment (Primary & Standby Databases)
This guide explains how to resize the online redo log file groups and add standby redo log groups in a 4-node Oracle RAC environment for both primary and standby databases. It also includes key steps and SQL commands for the task.
Note: First add online redo log group with recommended size and add standby redo group (n+1) on standby then proceed on Primary Database
Pre-Requisite
---------------
1 Sufficient free space is available on ASM(Both DC And DR)
2 change parameter - standby_file_management to manual (Both DC And DR)
3 disable DC-DR replication(Defer)
Implementation on DR(Standby Database)
-------------------------------------------
1 cancel recovery on standby database
2 add redo log group with recommended size on standby database
3 Verify newly created online redo log file
4 add new standby redo log group on standby database (N+1)
5 verify standby redo log file
6 drop old redo log group from standby database
7 Verify online redo log file after drop
Implementation on Primary database (DC)
----------------------------------------
1 add online redo log group with recommended size on primary database
2 Verify newly created online redo log file
3 add standby redo log with recommended size primary database (N+1)
4 Verify newly created standby redo log file
5 drop old redo log group from primary database
6 verify online redo log file after drop
Post Implementation
----------------
1 enable DC-DR replication(enable)
2 change parameter - standby_file_management to auto (Both DC And DR)
3 Monitor Primary and Standby syncIn Detail with example
Pre requisite (Primary and Standby Database)
------------------------------------------------------------
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.
it will run on both Primary and Standby database
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
SQL> alter system set standby_file_management=manual scope=both sid='*';
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL
The command will run on primary database only
2) disable DC-DR replication(Defer)
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER SCOPE=BOTH sid='*'; -DC
Now we will run below command on standby(DR):-
Start of online and standby redo adding and dropping Standby Database
Addition of online redo file with recommended size
select THREAD#,MAX(SEQUENCE#) from v$loghist group by THREAD#;
select name,open_mode,log_mode from v$database;
column member format a50 wrap heading 'Member'
column group# format 99990 heading 'Group#'
column status format a10 wrap heading 'Status'
SELECT a.group#, a.member, b.bytes/1024/1024 FROM v$logfile a, v$log b WHERE a.group# = b.group#;
select group#,members,status,bytes/1024/1024 as mb from v$log;
select group#,member from v$logfile;
alter database recover managed standby database cancel;
alter database add logfile thread 1 group 101 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 1 group 102 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 1 group 103 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 1 group 104 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 1 group 105 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 1 group 106 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 1 group 107 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 1 group 108 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 1 group 109 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 1 group 110 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 1 group 111 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 1 group 112 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
column member format a50 wrap heading 'Member'
column group# format 99990 heading 'Group#'
column status format a10 wrap heading 'Status'
SELECT a.group#, a.member, b.bytes/1024/1024 FROM v$logfile a, v$log b WHERE a.group# = b.group#;
select group#,members,status,bytes/1024/1024 as mb from v$log;
select group#,member from v$logfile;
alter database add logfile thread 2 group 113 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 2 group 114 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 2 group 115 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 2 group 116 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 2 group 117 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 2 group 118 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 2 group 119 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 2 group 120 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 2 group 121 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 2 group 122 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 2 group 123 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 2 group 124 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
column member format a50 wrap heading 'Member'
column group# format 99990 heading 'Group#'
column status format a10 wrap heading 'Status'
SELECT a.group#, a.member, b.bytes/1024/1024 FROM v$logfile a, v$log b WHERE a.group# = b.group#;
select group#,members,status,bytes/1024/1024 as mb from v$log;
select group#,member from v$logfile;
alter database add logfile thread 3 group 125 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 3 group 126 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 3 group 127 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 3 group 128 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 3 group 129 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 3 group 130 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 3 group 131 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 3 group 132 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 3 group 133 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 3 group 134 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 3 group 135 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 3 group 136 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
column member format a50 wrap heading 'Member'
column group# format 99990 heading 'Group#'
column status format a10 wrap heading 'Status'
SELECT a.group#, a.member, b.bytes/1024/1024 FROM v$logfile a, v$log b WHERE a.group# = b.group#;
select group#,members,status,bytes/1024/1024 as mb from v$log;
select group#,member from v$logfile;
alter database add logfile thread 4 group 137 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 4 group 138 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 4 group 139 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 4 group 140 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 4 group 141 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 4 group 142 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 4 group 143 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 4 group 144 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 4 group 145 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 4 group 146 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 4 group 147 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 4 group 148 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
column member format a50 wrap heading 'Member'
column group# format 99990 heading 'Group#'
column status format a10 wrap heading 'Status'
SELECT a.group#, a.member, b.bytes/1024/1024 FROM v$logfile a, v$log b WHERE a.group# = b.group#;
select group#,members,status,bytes/1024/1024 as mb from v$log;
select group#,member from v$logfile;
Drop the log groups on standby database :
-------------------------------------------------------
column member format a50 wrap heading 'Member'
column group# format 99990 heading 'Group#'
column status format a10 wrap heading 'Status'
SELECT a.group#, a.member, b.bytes/1024/1024 FROM v$logfile a, v$log b WHERE a.group# = b.group#;
select group#,members,status,bytes/1024/1024 as mb from v$log;
select group#,member from v$logfile;
Note : Since the status of Group is still clearing, lets clear it manually before dropping the group.
eg
alter database clear logfile group 1;
select lg.group#,lg.bytes/1024/1024 mb, lg.status, lg.archived,lf.member from v$logfile lf, v$log lg where lg.group# = lf.group# order by 1, 2;
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database drop logfile group 4;
alter database drop logfile group 5;
alter database drop logfile group 6;
alter database drop logfile group 7;
alter database drop logfile group 8;
alter database drop logfile group 9;
alter database drop logfile group 10;
select lg.group#,lg.bytes/1024/1024 mb, lg.status, lg.archived,lf.member from v$logfile lf, v$log lg where lg.group# = lf.group# order by 1, 2;
alter database drop logfile group 11;
alter database drop logfile group 12;
alter database drop logfile group 13;
alter database drop logfile group 14;
alter database drop logfile group 15;
alter database drop logfile group 16;
alter database drop logfile group 17;
alter database drop logfile group 18;
alter database drop logfile group 19;
alter database drop logfile group 20;
select lg.group#,lg.bytes/1024/1024 mb, lg.status, lg.archived,lf.member from v$logfile lf, v$log lg where lg.group# = lf.group# order by 1, 2;
alter database drop logfile group 21;
alter database drop logfile group 22;
alter database drop logfile group 23;
alter database drop logfile group 24;
alter database drop logfile group 25;
alter database drop logfile group 26;
alter database drop logfile group 27;
alter database drop logfile group 28;
alter database drop logfile group 29;
alter database drop logfile group 30;
alter database drop logfile group 31;
select lg.group#,lg.bytes/1024/1024 mb, lg.status, lg.archived,lf.member from v$logfile lf, v$log lg where lg.group# = lf.group# order by 1, 2;
alter database drop logfile group 32;
alter database drop logfile group 33;
alter database drop logfile group 34;
alter database drop logfile group 35;
alter database drop logfile group 36;
alter database drop logfile group 37;
alter database drop logfile group 38;
alter database drop logfile group 39;
alter database drop logfile group 40;
alter database drop logfile group 41;
alter database drop logfile group 42;
alter database drop logfile group 43;
select lg.group#,lg.bytes/1024/1024 mb, lg.status, lg.archived,lf.member from v$logfile lf, v$log lg where lg.group# = lf.group# order by 1, 2;
alter database drop logfile group 44;
alter database drop logfile group 45;
alter database drop logfile group 46;
alter database drop logfile group 47;
alter database drop logfile group 48;
alter database drop logfile group 49;
alter database drop logfile group 50;
alter database drop logfile group 51;
alter database drop logfile group 52;
alter database drop logfile group 53;
alter database drop logfile group 54;
alter database drop logfile group 55;
alter database drop logfile group 56;
alter database drop logfile group 57;
select lg.group#,lg.bytes/1024/1024 mb, lg.status, lg.archived,lf.member from v$logfile lf, v$log lg where lg.group# = lf.group# order by 1, 2;
alter database drop logfile group 58;
alter database drop logfile group 59;
alter database drop logfile group 60;
alter database drop logfile group 61;
alter database drop logfile group 62;
alter database drop logfile group 63;
alter database drop logfile group 64;
Post Implementation on primary
----------------------------------------------
To verify size for newly created log file
column member format a50 wrap heading 'Member'
column group# format 99990 heading 'Group#'
column status format a10 wrap heading 'Status'
SELECT a.group#,b.thread#, a.member, b.bytes/1024/1024 FROM v$logfile a, v$log b WHERE a.group# = b.group#;
add standby redo log file on (n+1)13 Standby redo Database(DR) in each thread
select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;
alter database add standby logfile thread 1 group 201 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 1 group 202 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 1 group 203 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 1 group 204 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 1 group 205 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 1 group 206 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 1 group 207 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 1 group 208 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 1 group 209 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 1 group 210 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 1 group 211 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 1 group 212 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 1 group 213 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;
alter database add standby logfile thread 2 group 214 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 2 group 215 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 2 group 216 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 2 group 217 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 2 group 218 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 2 group 219 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 2 group 220 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 2 group 221 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 2 group 222 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 2 group 223 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 2 group 224 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 2 group 225 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 2 group 226 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;
alter database add standby logfile thread 3 group 227 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 3 group 228 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 3 group 229 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 3 group 230 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 3 group 231 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 3 group 232 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 3 group 233 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 3 group 234 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 3 group 235 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 3 group 236 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 3 group 237 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 3 group 238 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 3 group 239 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;
alter database add standby logfile thread 4 group 240 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 4 group 241 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 4 group 242 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 4 group 243 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 4 group 244 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 4 group 245 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 4 group 246 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 4 group 247 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 4 group 248 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 4 group 249 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 4 group 250 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 4 group 251 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 4 group 252 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;
---------end of online and standby redo addition Standby Database -----------------Now Start on Primary Database - Adding log files
Add on 12 online redo with recommend size and 13 (n+1)standby redo on Primary Database (DC)
Change Implementation Steps:
-----------------------------
select name,open_mode,log_mode from v$database;
column member format a50 wrap heading 'Member'
column group# format 99990 heading 'Group#'
column status format a10 wrap heading 'Status'
SELECT a.group#, a.member, b.bytes/1024/1024 FROM v$logfile a, v$log b WHERE a.group# = b.group#;
select group#,members,status,bytes/1024/1024 as mb from v$log;
select group#,member from v$logfile;
alter database add logfile thread 1 group 101 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 1 group 102 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 1 group 103 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 1 group 104 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 1 group 105 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 1 group 106 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 1 group 107 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 1 group 108 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 1 group 109 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 1 group 110 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 1 group 111 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 1 group 112 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
column member format a50 wrap heading 'Member'
column group# format 99990 heading 'Group#'
column status format a10 wrap heading 'Status'
SELECT a.group#, a.member, b.bytes/1024/1024 FROM v$logfile a, v$log b WHERE a.group# = b.group#;
select group#,members,status,bytes/1024/1024 as mb from v$log;
select group#,member from v$logfile;
alter database add logfile thread 2 group 113 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 2 group 114 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 2 group 115 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 2 group 116 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 2 group 117 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 2 group 118 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 2 group 119 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 2 group 120 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 2 group 121 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 2 group 122 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 2 group 123 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 2 group 124 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
column member format a50 wrap heading 'Member'
column group# format 99990 heading 'Group#'
column status format a10 wrap heading 'Status'
SELECT a.group#, a.member, b.bytes/1024/1024 FROM v$logfile a, v$log b WHERE a.group# = b.group#;
select group#,members,status,bytes/1024/1024 as mb from v$log;
select group#,member from v$logfile;
alter database add logfile thread 3 group 125 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 3 group 126 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 3 group 127 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 3 group 128 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 3 group 129 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 3 group 130 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 3 group 131 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 3 group 132 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 3 group 133 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 3 group 134 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 3 group 135 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 3 group 136 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
column member format a50 wrap heading 'Member'
column group# format 99990 heading 'Group#'
column status format a10 wrap heading 'Status'
SELECT a.group#, a.member, b.bytes/1024/1024 FROM v$logfile a, v$log b WHERE a.group# = b.group#;
select group#,members,status,bytes/1024/1024 as mb from v$log;
select group#,member from v$logfile;
alter database add logfile thread 4 group 137 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 4 group 138 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 4 group 139 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 4 group 140 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 4 group 141 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 4 group 142 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 4 group 143 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 4 group 144 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 4 group 145 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 4 group 146 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 4 group 147 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add logfile thread 4 group 148 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
column member format a50 wrap heading 'Member'
column group# format 99990 heading 'Group#'
column status format a10 wrap heading 'Status'
SELECT a.group#, a.member, b.bytes/1024/1024 FROM v$logfile a, v$log b WHERE a.group# = b.group#;
select group#,members,status,bytes/1024/1024 as mb from v$log;
select group#,member from v$logfile;
Primary - Drop the log groups :
-------------------------------------
ALTER SYSTEM ARCHIVE LOG CURRENT;
Alter system switch logfile;
alter system checkpoint;
check inactive group and drop it if it is current ,make then inactive after switch logfile.
select lg.group#,lg.bytes/1024/1024 mb, lg.status, lg.archived,lf.member from v$logfile lf, v$log lg where lg.group# = lf.group# order by 1, 2;
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database drop logfile group 4;
alter database drop logfile group 5;
alter database drop logfile group 6;
alter database drop logfile group 7;
alter database drop logfile group 8;
alter database drop logfile group 9;
alter database drop logfile group 10;
select lg.group#,lg.bytes/1024/1024 mb, lg.status, lg.archived,lf.member from v$logfile lf, v$log lg where lg.group# = lf.group# order by 1, 2;
alter database drop logfile group 11;
alter database drop logfile group 12;
alter database drop logfile group 13;
alter database drop logfile group 14;
alter database drop logfile group 15;
alter database drop logfile group 16;
alter database drop logfile group 17;
alter database drop logfile group 18;
alter database drop logfile group 19;
alter database drop logfile group 20;
select lg.group#,lg.bytes/1024/1024 mb, lg.status, lg.archived,lf.member from v$logfile lf, v$log lg where lg.group# = lf.group# order by 1, 2;
alter database drop logfile group 21;
alter database drop logfile group 22;
alter database drop logfile group 23;
alter database drop logfile group 24;
alter database drop logfile group 25;
alter database drop logfile group 26;
alter database drop logfile group 27;
alter database drop logfile group 28;
alter database drop logfile group 29;
alter database drop logfile group 30;
alter database drop logfile group 31;
select lg.group#,lg.bytes/1024/1024 mb, lg.status, lg.archived,lf.member from v$logfile lf, v$log lg where lg.group# = lf.group# order by 1, 2;
alter database drop logfile group 32;
alter database drop logfile group 33;
alter database drop logfile group 34;
alter database drop logfile group 35;
alter database drop logfile group 36;
alter database drop logfile group 37;
alter database drop logfile group 38;
alter database drop logfile group 39;
alter database drop logfile group 40;
alter database drop logfile group 41;
alter database drop logfile group 42;
alter database drop logfile group 43;
select lg.group#,lg.bytes/1024/1024 mb, lg.status, lg.archived,lf.member from v$logfile lf, v$log lg where lg.group# = lf.group# order by 1, 2;
alter database drop logfile group 44;
alter database drop logfile group 45;
alter database drop logfile group 46;
alter database drop logfile group 47;
alter database drop logfile group 48;
alter database drop logfile group 49;
alter database drop logfile group 50;
alter database drop logfile group 51;
alter database drop logfile group 52;
alter database drop logfile group 53;
alter database drop logfile group 54;
alter database drop logfile group 55;
alter database drop logfile group 56;
alter database drop logfile group 57;
select lg.group#,lg.bytes/1024/1024 mb, lg.status, lg.archived,lf.member from v$logfile lf, v$log lg where lg.group# = lf.group# order by 1, 2;
alter database drop logfile group 58;
alter database drop logfile group 59;
alter database drop logfile group 60;
alter database drop logfile group 61;
alter database drop logfile group 62;
alter database drop logfile group 63;
alter database drop logfile group 64;
Primary:- To verify size for newly created log file
column member format a50 wrap heading 'Member'
column group# format 99990 heading 'Group#'
column status format a10 wrap heading 'Status'
SQL> SELECT a.group#,b.thread#, a.member, b.bytes/1024/1024 FROM v$logfile a, v$log b WHERE a.group# = b.group#;
Add standby redo log file (12+1=13) on each thread on Primary Database (DC)
----------------------------------------------------------------------
select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;
alter database add standby logfile thread 1 group 201 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 1 group 202 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 1 group 203 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 1 group 204 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 1 group 205 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 1 group 206 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 1 group 207 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 1 group 208 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 1 group 209 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 1 group 210 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 1 group 211 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 1 group 212 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 1 group 213 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;
alter database add standby logfile thread 2 group 214 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 2 group 215 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 2 group 216 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 2 group 217 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 2 group 218 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 2 group 219 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 2 group 220 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 2 group 221 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 2 group 222 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 2 group 223 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 2 group 224 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 2 group 225 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 2 group 226 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;
alter database add standby logfile thread 3 group 227 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 3 group 228 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 3 group 229 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 3 group 230 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 3 group 231 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 3 group 232 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 3 group 233 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 3 group 234 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 3 group 235 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 3 group 236 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 3 group 237 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 3 group 238 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 3 group 239 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;
alter database add standby logfile thread 4 group 240 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 4 group 241 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 4 group 242 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 4 group 243 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 4 group 244 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 4 group 245 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 4 group 246 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 4 group 247 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 4 group 248 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 4 group 249 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 4 group 250 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 4 group 251 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
alter database add standby logfile thread 4 group 252 ('+ORCL_REDO','+ORCL_FRA') size 4096m;
select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;
---------------END on DC =====================================
Post Implementation after completion on both Primary and Standby database
-----------------------------------------------------------------------------
1 enable DC-DR replication(enable)
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=enable SCOPE=BOTH sid='*'; (DC)
alter database recover managed standby database disconnect from session (DR)
2 change parameter -standby_file_management to auto (Both DC And DR)
alter system set standby_file_management=auto scope=both sid='*';(DC And DR)
3 Monitor Primary and Standby sync
select process,status,sequence# from v$managed_standby;
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.
useful command
------------------------
select 'alter database add standby logfile '''||
regexp_substr(MEMBER,'/.+/')||'stdby_'||
regexp_replace(member,regexp_substr(MEMBER,'/.+/'),'')||
''' size '||bytes||';' "Create Standby redo"
from v$logfile lf , v$log l
where l.group# = lf.group#
union all
select 'alter database add standby logfile '''||
regexp_substr(MEMBER,'/.+/')||'stdby_redo0'||(select max(group#)+1 from v$log)||'.rdo'||
''' size '||bytes||';' "Create Standby redo"
from v$logfile lf , v$log l
where l.group# = lf.group#
and rownum <=2
/
No comments:
Post a Comment