Below is High Level Steps:
On Primary :
1. check primary logfile and standby logfile group, thread and size
2. ADD and Drop primary logfile group
3. ADD and Drop standby primary logfile group
On standby :
cancel media recovery
set file_management to manual
1. check Standby logfile group, thread and size
2. ADD and Drop standby primary logfile group
set file_management auto
start media recovery
############## For Logfile on Primay
SQL> 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# ;
GROUP# THREAD# MEMBER ARCHIVED STATUS Size (MB)
------ ------- ---------------------------------------------------------------------- ---------- ---------- ---------
1 1 +RECO/WPDGBPR/ONLINELOG/group_1.257.915122141 YES ACTIVE 250
1 1 +DATA/WPDGBPR/ONLINELOG/group_1.258.915122141 YES ACTIVE 250
2 1 +DATA/WPDGBPR/ONLINELOG/group_2.259.915122309 YES ACTIVE 250
2 1 +RECO/WPDGBPR/ONLINELOG/group_2.258.915122309 YES ACTIVE 250
3 1 +DATA/WPDGBPR/ONLINELOG/group_3.266.915122023 NO CURRENT 250
3 1 +RECO/WPDGBPR/ONLINELOG/group_3.283.915122023 NO CURRENT 250
4 2 +DATA/WPDGBPR/ONLINELOG/group_4.267.915121991 NO CURRENT 250
4 2 +RECO/WPDGBPR/ONLINELOG/group_4.260.915121991 NO CURRENT 250
5 2 +DATA/WPDGBPR/ONLINELOG/group_5.269.915121623 YES ACTIVE 250
5 2 +RECO/WPDGBPR/ONLINELOG/group_5.288.915121623 YES ACTIVE 250
6 2 +DATA/WPDGBPR/ONLINELOG/group_6.270.915121641 YES ACTIVE 250
6 2 +RECO/WPDGBPR/ONLINELOG/group_6.287.915121641 YES ACTIVE 250
-- alter database add standby logfile THREAD &thread group &group ('+DATA','+RECO') size 500m;
alter system switch logfile;
alter system archivelog current;
alter database add logfile thread 1 group 15 ('+DATA','+RECO') size 500M;
alter database add logfile thread 1 group 16 ('+DATA','+RECO') size 500M;
alter database add logfile thread 1 group 17 ('+DATA','+RECO') size 500M;
alter database add logfile thread 2 group 18 ('+DATA','+RECO') size 500M;
alter database add logfile thread 2 group 19 ('+DATA','+RECO') size 500M;
alter database add logfile thread 2 group 20 ('+DATA','+RECO') size 500M;
alter database drop logfile group 3;
alter database drop logfile group 2;
alter database drop logfile group 1;
alter database drop logfile group 4;
alter database drop logfile group 5;
alter database drop logfile group 6;
SQL> 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# ;
GROUP# THREAD# MEMBER ARCHIVED STATUS Size (MB)
------ ------- ---------------------------------------------------------------------- ---------- ---------- ---------
15 1 +DATA/WPDGBPR/ONLINELOG/group_15.409.1070665839 NO CURRENT 500
15 1 +RECO/WPDGBPR/ONLINELOG/group_15.694.1070665841 NO CURRENT 500
16 1 +DATA/WPDGBPR/ONLINELOG/group_16.439.1070665993 YES INACTIVE 500
16 1 +RECO/WPDGBPR/ONLINELOG/group_16.7788.1070665993 YES INACTIVE 500
17 1 +DATA/WPDGBPR/ONLINELOG/group_17.408.1070666057 YES INACTIVE 500
17 1 +RECO/WPDGBPR/ONLINELOG/group_17.7901.1070666059 YES INACTIVE 500
18 2 +RECO/WPDGBPR/ONLINELOG/group_18.258.1070666787 NO CURRENT 500
18 2 +DATA/WPDGBPR/ONLINELOG/group_18.259.1070666787 NO CURRENT 500
19 2 +DATA/WPDGBPR/ONLINELOG/group_19.258.1070666811 YES INACTIVE 500
19 2 +RECO/WPDGBPR/ONLINELOG/group_19.257.1070666811 YES INACTIVE 500
20 2 +DATA/WPDGBPR/ONLINELOG/group_20.266.1070666831 YES INACTIVE 500
20 2 +RECO/WPDGBPR/ONLINELOG/group_20.283.1070666831 YES INACTIVE 500
############## For standby logfile on Primay :
SQL> SELECT thread#, group#, sequence#, bytes, archived, status FROM v$standby_log order by thread#, group#;
THREAD# GROUP# SEQUENCE# BYTES ARCHIVED STATUS
------- ------ ---------- ---------- ---------- ----------
1 7 0 262144000 YES UNASSIGNED
1 8 0 262144000 YES UNASSIGNED
1 9 0 262144000 YES UNASSIGNED
1 10 0 262144000 YES UNASSIGNED
2 11 0 262144000 YES UNASSIGNED
2 12 0 262144000 YES UNASSIGNED
2 13 0 262144000 YES UNASSIGNED
2 14 0 262144000 YES UNASSIGNED
alter database recover managed standby database cancel;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 7;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 8;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 9;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 10;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 11;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 12;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 13;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 14;
alter database add standby logfile THREAD 1 group 7 ('+DATA','+RECO') SIZE 500M;
alter database add standby logfile THREAD 1 group 8 ('+DATA','+RECO') SIZE 500M;
alter database add standby logfile THREAD 1 group 9 ('+DATA','+RECO') SIZE 500M;
alter database add standby logfile THREAD 1 group 10 ('+DATA','+RECO') SIZE 500M;
alter database add standby logfile THREAD 2 group 11 ('+DATA','+RECO') SIZE 500M;
alter database add standby logfile THREAD 2 group 12 ('+DATA','+RECO') SIZE 500M;
alter database add standby logfile THREAD 2 group 13 ('+DATA','+RECO') SIZE 500M;
alter database add standby logfile THREAD 2 group 14 ('+DATA','+RECO') SIZE 500M;
alter database recover managed standby database disconnect from session;
############## For logfile on Standby :
SQL> alter database recover managed standby database cancel;
Database altered.
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='*';
System altered.
alter database drop logfile group 3;
alter database drop logfile group 2;
alter database drop logfile group 1;
alter database drop logfile group 4;
alter database drop logfile group 5;
alter database drop logfile group 6;
alter database add logfile thread 1 group 15 ('+DATA','+RECO') size 500M;
alter database add logfile thread 1 group 16 ('+DATA','+RECO') size 500M;
alter database add logfile thread 1 group 17 ('+DATA','+RECO') size 500M;
alter database add logfile thread 2 group 18 ('+DATA','+RECO') size 500M;
alter database add logfile thread 2 group 19 ('+DATA','+RECO') size 500M;
alter database add logfile thread 2 group 20 ('+DATA','+RECO') size 500M;
SQL> alter system set standby_file_management=auto scope=both sid='*';
System altered.
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
SQL> alter database recover managed standby database disconnect from session ;
Database altered.
0 Comments