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>;
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.