DBMS_LOGSTDBY INSTANTIATE_TABLE

Sometimes i have to recreate a table on the oracle standby database to keep standby consistent with the primary. It can be performed by DBMS_LOGSTDBY.INSTANTIATE_TABLE procedure. This procedure uses three parameters. These parameters are “Schema Name”, “Table Name”, “DB Link”.

I created a dblink that connected primary database with the user.

create database link stdb_to_prm connect to <schema> identified by <password> using '<TNS NAME>';

Then instantiated the table. (Remember STANDBY SQL APPLY is stopped.)

EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE (SCHEMA_NAME => <schema>, TABLE_NAME => <table name>, DBLINK => 'stdb_to_prm');

When i first made this process, i got following error:

ORA-16279: Supplied dblink must have CONNECT, RESOURCE, and SELECT_CATALOG_ROLE roles

ORA-06512: at “SYS.DBMS_INTERNAL_LOGSTDBY”, line 5359

ORA-00942: table or view does not exist.

As mentioned in the error code, i gave the appropriate privileges to the user on the primary database.

grant CONNECT, RESOURCE, SELECT_CATALOG_ROLE to <user>;

But it still gave the same error. I checked the privileges that i gave the user.

select GRANTED_ROLE from dba_role_privs where GRANTEE=<user>;

SELECT_CATALOG_ROLE

RESOURCE

CONNECT

Why was it giving same error? While i was struggling, i tried to drop dblink and recreate it. That’s it. Problem solved. It is understood that in the process of instantiation of a table, reqiured roles must be given before creating the dblink.