Remove Extra member from standby redo group

Tips , Reset below parameters before create standby redo group
db_create_online_log_dest_1=+DATA1 and db_create_online_log_dest_2=+DATA2

If you have set these parameters and you create standby redo log then it will be multiplexed in +DATA1 and +DATA2, Not recommended in Prod:)

@Primary
SQL> show parameter standby_file_management=MANUAL;
SQL> alter system set standby_file_management=MANUAL;

System altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE MEMBER ‘+ORADATA/BLRDB/ONLINELOG/group_5.319.922465363’;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE MEMBER ‘+ORADATA/BLRDB/ONLINELOG/group_6.310.922465367’;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE MEMBER ‘+ORADATA/BLRDB/ONLINELOG/group_7.311.922465371’;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE MEMBER ‘+ORADATA/BLRDB/ONLINELOG/group_8.299.922465375’;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE MEMBER ‘+ORADATA/BLRDB/ONLINELOG/group_9.872.922465379’;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE MEMBER ‘+ORADATA/BLRDB/ONLINELOG/group_10.874.922465383’;

Database altered.

SQL> select member from v$logfile;

MEMBER
——————————————————————————–
+ORADATA/BLRDB/ONLINELOG/group_2.305.922465353
+ORADATA/BLRDB/ONLINELOG/group_2.622.922465353
+ORADATA/BLRDB/ONLINELOG/group_1.307.922465349
+ORADATA/BLRDB/ONLINELOG/group_1.306.922465351
+ORADATA/BLRDB/ONLINELOG/group_3.620.922465355
+ORADATA/BLRDB/ONLINELOG/group_3.323.922465357
+ORADATA/BLRDB/ONLINELOG/group_4.322.922465359
+ORADATA/BLRDB/ONLINELOG/group_4.320.922465361
+ORADATA/BLRDB/ONLINELOG/group_5.313.922465365
+ORADATA/BLRDB/ONLINELOG/group_6.301.922465369
+ORADATA/BLRDB/ONLINELOG/group_7.302.922465373

MEMBER
——————————————————————————–
+ORADATA/BLRDB/ONLINELOG/group_8.300.922465377
+ORADATA/BLRDB/ONLINELOG/group_9.873.922465381
+ORADATA/BLRDB/ONLINELOG/group_10.875.922465385
+ORADATA/BLRDB/ONLINELOG/group_11.1351.927649007

15 rows selected.

SQL> show parameter standby_file_management=AUTO scope=both;
SQL> ALTER SYSTEM SET standby_file_management=AUTO scope=both;

System altered.

SQL> select member from v$logfile;

MEMBER
——————————————————————————–
+ORADATA/BLRDB/ONLINELOG/group_2.305.922465353
+ORADATA/BLRDB/ONLINELOG/group_2.622.922465353
+ORADATA/BLRDB/ONLINELOG/group_1.307.922465349
+ORADATA/BLRDB/ONLINELOG/group_1.306.922465351
+ORADATA/BLRDB/ONLINELOG/group_3.620.922465355
+ORADATA/BLRDB/ONLINELOG/group_3.323.922465357
+ORADATA/BLRDB/ONLINELOG/group_4.322.922465359
+ORADATA/BLRDB/ONLINELOG/group_4.320.922465361
+ORADATA/BLRDB/ONLINELOG/group_5.313.922465365
+ORADATA/BLRDB/ONLINELOG/group_6.301.922465369
+ORADATA/BLRDB/ONLINELOG/group_7.302.922465373

MEMBER
——————————————————————————–
+ORADATA/BLRDB/ONLINELOG/group_8.300.922465377
+ORADATA/BLRDB/ONLINELOG/group_9.873.922465381
+ORADATA/BLRDB/ONLINELOG/group_10.875.922465385
+ORADATA/BLRDB/ONLINELOG/group_11.1351.927649007

15 rows selected.

SQL>
SQL> alter system switch logfile;

System altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1

@DR

alter system set standby_file_management=MANUAL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SQL> ALTER DATABASE DROP STANDBY LOGFILE MEMBER ‘+ORADATA/BLRDB/ONLINELOG/group_5.319.922465363’;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE MEMBER ‘+ORADATA/BLRDB/ONLINELOG/group_6.310.922465367’;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE MEMBER ‘+ORADATA/BLRDB/ONLINELOG/group_7.311.922465371’;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE MEMBER ‘+ORADATA/BLRDB/ONLINELOG/group_8.299.922465375’;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE MEMBER ‘+ORADATA/BLRDB/ONLINELOG/group_9.872.922465379’;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE MEMBER ‘+ORADATA/BLRDB/ONLINELOG/group_10.874.922465383′;

Database altered.

Standby redo logs without multiplexting:-

SQL> SELECT MEMBER FROM V$LOGFILE WHERE TYPE=’STANDBY’;

MEMBER
——————————————————————————–
+ORADATA/BLRDB/ONLINELOG/group_5.313.922465365
+ORADATA/BLRDB/ONLINELOG/group_6.301.922465369
+ORADATA/BLRDB/ONLINELOG/group_7.302.922465373
+ORADATA/BLRDB/ONLINELOG/group_8.300.922465377
+ORADATA/BLRDB/ONLINELOG/group_9.873.922465381
+ORADATA/BLRDB/ONLINELOG/group_10.875.922465385

SQL> SELECT GROUP#,THREAD#,STATUS FROM V$STANDBY_LOG;

GROUP# THREAD# STATUS
———- ———- ———-
5 1 UNASSIGNED
6 1 UNASSIGNED
7 1 UNASSIGNED
8 2 UNASSIGNED
9 2 UNASSIGNED
10 2 UNASSIGNED

Advertisements