Disclaimer: You must be licensed for Active Data Guard, an additional license on top of Enterprise Edition to be able to follow the steps in this guide without violating your license terms.

You may be tasked with tuning a query that only runs against your 19c Active Data Guard (ADG) standby database. If you try to do that with OEM 13c, you’ll get a message like below indicating that you need to have a database link to the primary. This makes sense since the tuning details will be persisted in the database itself, and that can’t work on a physical standby since it’s open READ ONLY WITH APPLY.

How do we do this? First, user SYS$UMF must own the DB link, but that Oracle provided user is locked by default. First, unlock it and give it a good password. You execute these commands on the primary database. SYS$UMF is a common user, so it suffices to run these commands from CDB$ROOT, you don’t need to run them in PDBs.

SYS@PRIMDB> select username, account_status from dba_users where username = 'SYS$UMF';
SYS@PRIMDB> alter user sys$umf identified by "<password>" account unlock;
SYS@PRIMDB> select username, account_status from dba_users where username = 'SYS$UMF';

Now that user SYS$UMF is unlocked and has a valid password, we need to create for them a database link that points to the primary CDB or instance. This link (which I call LINK_TO_PRIMARY below) will be replicated over to the standby database by redo apply. Execute these commands on the primary. You can specify a full TNS connect descriptor like below, or use a service name that’s available on the local host’s tnsnames.ora file.

SYS@PRIMDB> -- using full TNS descriptor
SYS@PRIMDB> create database link LINK_TO_PRIMARY
            connect to "SYS$UMF" identified by "<password>"
            using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=PRIMDBHOST)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PRIMDB)))';

SYS@PRIMDB> -- using existing TNS name
SYS@PRIMDB> create database link LINK_TO_PRIMARY
            connect to "SYS$UMF" identified by "<password>"
            using 'PRIMDB';

SYS@PRIMDB> -- verify DB link was created
SYS@PRIMDB> select db_link, host from cdb_db_links where db_link = 'LINK_TO_PRIMARY';

Try again to run the SQL Tuning Advisor, this time specifying your LINK_TO_PRIMARY database link. If everything was done correctly, it should now work.

Hope this helps,
Dustin