in this , We ware going to resize online redo log file/group from 2gb to 4 GB and adding
in standby redo log (n+1) to improve performance of database on both primary and standby database
current setup = 4 node RAC primary database and 4 node RAC Standby database
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 availble on ASM(Both DC And DR)
2 change parameter -standby_file_management to manual (Both DC And DR)
3 disable DC-DR replication(Defer)
Implementatio 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
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 sync
in 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)
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 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 1 group 102 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 1 group 103 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 1 group 104 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 1 group 105 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 1 group 106 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 1 group 107 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 1 group 108 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 1 group 109 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 1 group 110 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 1 group 111 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 1 group 112 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') 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 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 2 group 114 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 2 group 115 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 2 group 116 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 2 group 117 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 2 group 118 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 2 group 119 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 2 group 120 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 2 group 121 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 2 group 122 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 2 group 123 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 2 group 124 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') 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 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 3 group 126 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 3 group 127 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 3 group 128 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 3 group 129 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 3 group 130 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 3 group 131 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 3 group 132 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 3 group 133 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 3 group 134 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 3 group 135 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 3 group 136 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') 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 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 4 group 138 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 4 group 139 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 4 group 140 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 4 group 141 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 4 group 142 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 4 group 143 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 4 group 144 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 4 group 145 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 4 group 146 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 4 group 147 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 4 group 148 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') 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 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 1 group 202 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 1 group 203 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 1 group 204 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 1 group 205 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 1 group 206 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 1 group 207 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 1 group 208 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 1 group 209 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 1 group 210 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 1 group 211 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 1 group 212 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 1 group 213 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') 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 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 2 group 215 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 2 group 216 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 2 group 217 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 2 group 218 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 2 group 219 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 2 group 220 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 2 group 221 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 2 group 222 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 2 group 223 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 2 group 224 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 2 group 225 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 2 group 226 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') 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 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 3 group 228 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 3 group 229 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 3 group 230 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 3 group 231 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 3 group 232 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 3 group 233 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 3 group 234 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 3 group 235 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 3 group 236 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 3 group 237 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 3 group 238 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 3 group 239 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') 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 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 4 group 241 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 4 group 242 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 4 group 243 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 4 group 244 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 4 group 245 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 4 group 246 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 4 group 247 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 4 group 248 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 4 group 249 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 4 group 250 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 4 group 251 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 4 group 252 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') 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
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 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 1 group 102 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 1 group 103 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 1 group 104 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 1 group 105 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 1 group 106 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 1 group 107 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 1 group 108 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 1 group 109 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 1 group 110 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 1 group 111 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 1 group 112 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') 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 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 2 group 114 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 2 group 115 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 2 group 116 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 2 group 117 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 2 group 118 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 2 group 119 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 2 group 120 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 2 group 121 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 2 group 122 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 2 group 123 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 2 group 124 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') 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 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 3 group 126 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 3 group 127 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 3 group 128 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 3 group 129 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 3 group 130 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 3 group 131 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 3 group 132 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 3 group 133 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 3 group 134 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 3 group 135 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 3 group 136 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') 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 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 4 group 138 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 4 group 139 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 4 group 140 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 4 group 141 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 4 group 142 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 4 group 143 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 4 group 144 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 4 group 145 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 4 group 146 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 4 group 147 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add logfile thread 4 group 148 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') 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 :
--------------------
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;
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 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 1 group 202 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 1 group 203 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 1 group 204 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 1 group 205 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 1 group 206 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 1 group 207 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 1 group 208 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 1 group 209 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 1 group 210 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 1 group 211 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 1 group 212 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 1 group 213 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') 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 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 2 group 215 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 2 group 216 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 2 group 217 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 2 group 218 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 2 group 219 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 2 group 220 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 2 group 221 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 2 group 222 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 2 group 223 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 2 group 224 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 2 group 225 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 2 group 226 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') 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 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 3 group 228 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 3 group 229 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 3 group 230 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 3 group 231 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 3 group 232 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 3 group 233 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 3 group 234 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 3 group 235 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 3 group 236 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 3 group 237 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 3 group 238 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 3 group 239 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') 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 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 4 group 241 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 4 group 242 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 4 group 243 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 4 group 244 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 4 group 245 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 4 group 246 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 4 group 247 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 4 group 248 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 4 group 249 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 4 group 250 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 4 group 251 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') size 4096m;
alter database add standby logfile thread 4 group 252 ('+EASYREDO_DGNEW','+EASYFRA_DGNEW') 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