Understanding RMAN-08137 Warning and log_archive_dest_state_2 Reset: Investigating an Unusual Scenario

    

SCOPE

The article covers possible solutions to resolve warnings that appear in RMAN backups due to incorrect database configurations. Continuing from my previous post on RMAN backup failure warnings, I am now addressing another situation.

Initially, the error seems concerning; however, there are scenarios where it appears to be a minor incorrect configuration, even though Data-Guard is working fine.

Following was the excerpt from logfile : 

channel ch2: backup set complete, elapsed time: 00:00:07

Finished Control File and SPFILE Autobackup at May 09 2024 07:40:09

RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process archived log file
name=+RECO/NKDBPRI1/ARCHIVELOG/2024_05_04/thread_1_seq_1552.445.1756246984 thread=1
sequence=1552

demystifying

Lets check the data-guard configuration and sync status..  

SQL> !dgmgrl/

DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed May 9 15:04:05 2024

Connected to "NKDBPRI1"
Connected as SYSDG.

DGMGRL> show configuration
	
	Configuration - dg_config
	Protection Mode: MaxPerformance
	
	Members:
	NKDBPRI1 - Primary database
	  NKDBPRI1A - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS (status updated 57 seconds ago)



DB_NAME 	INSTANCE_NAME 		DATABASE_ROLE 			DB_UP_TIME  			HOST_NAME   
--------	-------------		-------------			-------------------		-----------------------			
NKDBPRI1 	NKDBPRI1		PRIMARY				04-MAY-2024 09:15:24	        localhost.oradbahelp.com		

 

Hmm.. This situation seems unusual because the Data Guard is in sync and replication is functional, yet this error is appearing. It is noteworthy that streams replication is not configured.

Lets take a look at necessary parameters and start with log_destination. 

SQL> show parameter log_archive_dest_state_2

NAME						TYPE 		VALUE
log_archive_dest_state_2	string 		RESET

SQL> alter system set log_archive_dest_state_2 = enable;
System altered.
 
 
 
SQL> show parameter log_archive_dest_state_2

NAME						TYPE 		VALUE
log_archive_dest_state_2	string 		ENABLE

Here we go, 

Now lets validate the DG configuration again.. 

SQL>!dgmgrl /

DGMGRL> show configuration
	Configuration - dg_config
	Protection Mode: MaxPerformance
	
	Members:
	NKDBPRI1 - Primary database
		Error: ORA-16810: multiple errors or warnings detected for the member
		NKDBPRI1A-Physical standby database
	
	Fast-Start Failover: Disabled
Configuration Status:
ERROR (status updated 33 seconds ago)

Oh no.. I see errors in DG broker configuration now .. 😑 

🧐 What I noticed here is that the moment I enable the log_destination, the Data Guard (DG) broker is broken. However, when I execute 'enable configuration' in the DG broker, the value for log_archive_dest_state_2 resets on its own.

Lets take a thorough look at the broker configuration from here.. 

SQL>!dgmgrl /

DGMGRL> show database 'NKDBPRI1';
Database - NKDBPRI1

Enterprise Manager Name: NKDBPRI1_ORADBAHELP
Role:			 PRIMARY
Intended State:	         TRANSPORT-OFF
Instance(s):
	NKDBPRI1
	Error: ORA-16739: redo transport service for member "NKDBPRI1A" is running
	
Database Status:
ERROR
DGMGRL>

Viola, did you notice that ? 😘

The Transport is set to OFF. 

It sounds like there's an unexpected situation where the Transport is set to OFF, but the MRP is still running due to its initiation at the BCP site using SQL command line. This scenario suggests that the MRP process may not be directly impacted by the Transport status, possibly due to its independent initiation and execution.

Let's try enabling it..  

DGMGRL> edit database NKDBPRI1 set state='TRANSPORT-ON';
Succeeded.

DGMGRL> 
DGMGRL show database 'NKDBPRI1":
Database - NKDBPRI1
Enterprise Manager Name: NKDBPRI1_ORADBAHELP
Role:			 PRIMARY
Intended State:	         TRANSPORT-ON
Instance(s):
	NKDBPRI1

Database Status:
SUCCESS

DGMGRL> show configuration
	
	Configuration - dg_config
	Protection Mode: MaxPerformance
	
	Members:
	NKDBPRI1- Primary database
	  NKDBPRI1A - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS (status updated 57 seconds ago)

After correcting this configuration, we no longer see any of these warnings in Backup log.

Additionally, the value for Destination_2 remained ENABLED with a happy broker configuration. 😋

    

Conclusion

Despite the standby database being in sync, the database internal views do not recognize it as archive sequence being applied to the BCP site. As a result, RMAN continues to issue warnings because of the value of log_archive_dest_state_2 being 'reset'.

Thank you for reading!

Best regards,

Nikhil 😉