Pages

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.

1 comment:

  1. The schema_name used in creation of DB link...should be given with these previs.

    ReplyDelete