Saturday, May 8, 2021

ORA-12754 When Setting ADG_ACCOUNT_INFO_TRACKING to Global

ADG_ACCOUNT_INFO_TRACKING was first introduced in 18.1 enchance the security posture in a data guard configuration. Setting this parameter to global result in login failures accross the data guard configuraiton being counted towards FAILED_LOGIN_ATTEMPTS value set in the user account profile. Setting it to local result in the default behavior which is only login failures in the primary is counted towards the FAILED_LOGIN_ATTEMPTS values.
In a recent deployment which was on AWS EC2 VMs (this point is key to this post and it is not using AWS RDS) following error was observed when testing the ADG_ACCOUNT_INFO_TRACKING works as expected, by using a wrong password and trying to login from a standby instance and to see if it locks after FAILED_LOGIN_ATTEMPTS number of login failures.
sqlplus locktest/locktest321@testro

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 26 13:59:45 2021
Version 19.10.0.0.0

Copyright (c) 1982, 2020, Oracle. All rights reserved.

ERROR:
ORA-12754: Feature 'ADG Statement Redirection' is disabled due to missing capability 'Runtime Environment'.
ORA-01017: invalid username/password; logon denied 
Error is strange as ADG statement redirection wasn't being used. This feature was introduced in 19.1 and controlled by ADG_REDIRECT_DML. Secondly the due to the error ADG_ACCOUNT_INFO_TRACKING wasn't working as expected and one could have unlimited number of login failures.
The two parameters in concern had the following values.
adg_account_info_tracking string GLOBAL
adg_redirect_dml boolean FALSE 
Setting adg_redirect_dml to true didn't make any difference either.
Further investigations showed that dml redirecation is not avaiable on "Authorized Cloud Environments". This is mentioned in the license documentation. But nowhere on it says anything about ADG_ACCOUNT_INFO_TRACKING.



Priror to testing on AWS EC2 a similar setup was created and operational in Azure (using Azure VM). If ADG_ACCOUNT_INFO_TRACKING is also not supported in authorized cloud environment similar to dml redirection then it should fail on Azure VM setup as well.
It seems that ADG_ACCOUNT_INFO_TRACKING is using DML redirection behind the scene to update the failed login atttempt count in the primary. However, use of this is flagged as "missing capability" only when databases are craeted on AWS EC2 VMs and not on Azure VM even though license doc says dml redirection is not supported on authorized cloud environments. It seems issue is localized only to AWS EC2.
SR raised and Oracle has created internal bug 32838564 - SETTING ADG_ACCOUNT_INFO_TRACKING=GLOBAL RESULTS IN ORA-12754 IN AWS DG ENVIRONMENT and investigating.

Update 07 July 2022
Oracle confirmed that ADG_ACCOUNT_INFO_TRACKING is using dml redirection behind the scene. Since dml redirection is not allowed on cloud environment the ADG_ACCOUNT_INFO_TRACKING also fails. Bug 32838564 was closed as not a bug based on above explanation.