DR DATABASE STRUCK OR STOPED APPLYING ARCHIVELOG ORA-16086

provided steps outline the process of managing standby redo logs (SRLs) for a Data Guard setup, focusing on configuring and troubleshooting issues during the addition and removal of standby log groups. Here’s a summary of what you performed and some recommendations:

Steps and Observations:

1. Primary DB Configuration:
   • Deferred log shipping to standby by setting log_archive_dest_state_2 to 'DEFER'.
   • alter system set log_archive_dest_state_2=’DEFER’ scope=both;
2. Standby DB Preparation:
   • Cancelled the managed recovery on the standby database to modify log files.
   • alter database recover managed standby database cancel;
   • col member for a60
   • select group#,type,member from v$logfile order by 1;
3. Dropped Existing Standby Log Groups:
   • Verified existing standby log groups with v$logfile and v$standby_log.
   • select group#,thread#,bytes,blocksize, status from v$standby_log;
   • 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;
   • alter database drop logfile group  21;
   • Dropped log groups 16 through 21.
4. Added New Standby Log Groups:
   • Attempted to add standby log groups using both +DATABASE and +FRA as ASM disk group destinations.
   • alter database add standby logfile group 16(‘+DATABASE’,’+FRA’) size 512M;
   • alter database add standby logfile group 17(‘+DATABASE’,’+FRA’) size 512M;
   • alter database add standby logfile group 18(‘+DATABASE’,’+FRA’) size 512M;
   • alter database add standby logfile group 19 (‘+DATABASE’,’+FRA’) size 512M;
   • alter database add standby logfile group 20 (‘+DATABASE’,’+FRA’) size 512M;
   • alter database add standby logfile group 21 (‘+DATABASE’,’+FRA’) size 512M;
5. Error During Addition:
   • Encountered ORA-00301, ORA-17502, and ORA-15005 when adding a log file group to the ASM disk group +FRA/CAODR/ONLINELOG.
   • Reason: Alias CAODR/ONLINELOG/group_5 was already used, leading to conflicts.
   • alter database add standby logfile group 5 (‘+FRA/CAODR/ONLINELOG/group_5’) size 512M
   • *
   • ERROR at line 1:
   • ORA-00301: error in adding log file ‘+FRA/CAODR/ONLINELOG/group_5’ – file cannot be created
   • ORA-17502: ksfdcre:4 Failed to create file +FRA/CAODR/ONLINELOG/group_5
   • ORA-15005: name “CAODR/ONLINELOG/group_5” is already used by an existing alias
6. ASM Commands:
   • Used asmcmd to navigate and remove conflicting files from the +FRA disk group.
   • SET the env variable and do the following
   • asmcmd
   • ls
   • cdfra/caodr/onlinelog
   • ls
   • rm group_16*
   • rm group_17*
   • rm group_18*
   • rm group_19*
   • rm group_20*
   • rm group_21*
7. Retried Standby Log Addition:
   • Successfully added standby log groups 16 through 21 after resolving conflicts.
   • alter database add standby logfile group 16 (‘+DATABASE’,’+FRA’) size 512M;
   • alter database add standby logfile group 17 (‘+DATABASE’,’+FRA’) size 512M;
   • alter database add standby logfile group 18 (‘+DATABASE’,’+FRA’) size 512M;
   • alter database add standby logfile group 19 (‘+DATABASE’,’+FRA’) size 512M;
   • alter database add standby logfile group 20 (‘+DATABASE’,’+FRA’) size 512M;
   • alter database add standby logfile group 21 (‘+DATABASE’,’+FRA’) size 512M;
8. Restarted Managed Recovery:
   • Re-enabled managed recovery on the standby database.
   • Re-enabled log shipping on the primary database by setting log_archive_dest_state_2 to 'ENABLE'.
   • col member for a60
   • select group#,type,member from v$logfile order by 1;
   • alter database recover managed standby database disconnect from session;
   • PRIMARY DB
   • alter system set log_archive_dest_state_2=’ENABLE’ scope=both;

---

Recommendations:

1. Verify Disk Group Capacity:
   • Before adding standby log groups, ensure the ASM disk groups (+DATABASE, +FRA) have sufficient space.
2. Avoid Alias Conflicts:
   • Use unique names for log groups or aliases in ASM. Conflicts like ORA-15005 can be avoided by verifying existing files:sqlCopyEditselect name from v$asm_alias where name like '%ONLINELOG%';
3. Ensure Symmetry:
   • The number and size of standby log groups should match the primary log groups for efficient redo log synchronization.
4. Log File Removal:
   • Use asmcmd rm carefully to delete obsolete log files from ASM disk groups. Verify their usage with:bashCopyEditasmcmd ls -l <directory_path>
5. Post-Changes Validation:
   • Confirm the configuration using:sqlCopyEditselect group#, thread#, bytes/1024/1024 as size_mb, status from v$standby_log;
6. Monitoring:
   • Monitor the Data Guard environment for errors:sqlCopyEditselect severity, error_code, message from v$dataguard_status order by timestamp desc;

---

Let me know if you need further assistance or troubleshooting!