How do I get one name for the primary and standby database, such that I may connect to the primary site but in case of a disaster, the same name can redirect connections to the DR automatically.
Do we have such as an option in Autonomous Data Guard on Autonomous Database?
On Autonomous Database on Shared Infrastructure, once a user enables cross-region Autonomous Data Guard (ADG), the wallet file's tnsnames.ora is updated to contain the hostnames of both the Primary database and the Standby database.
So after enabling ADG and downloading a new database wallet, using the one single wallet, a user should be able to connect to the Primary database as well as the remote Standby database, after a switchover/failover.
Read more about this behaviour in the Oracle documentation on Autonomous Data Guard.
Ref - I am a product manager on the Oracle Autonomous Database :)