Skip to main content

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.

Comments

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

    ReplyDelete

Post a Comment

Popular posts from this blog

Sending Jboss Server Logs to Logstash Using Filebeat with Multiline Support

In addition to sending system logs to logstash, it is possible to add a prospector section to the filebeat.yml for jboss server logs. Sometimes jboss server.log has single events made up from several lines of messages. In such cases Filebeat should be configured for a multiline prospector.
Filebeat takes lines do not start with a date pattern (look at pattern in the multiline section "^[[:digit:]]{4}-[[:digit:]]{2}-[[:digit:]]{2}" and negate section is set to true) and combines them with the previous line that starts with a date pattern.

server.log file excerpt where DatePattern: yyyy-MM-dd-HH and ConversionPattern: %d %-5p [%c] %m%n
Logstash filter: