User Tools

Site Tools


replicator_agent_database_faq

Database FAQ

1.1 Why am I getting the following error "ORA-04031: unable to allocate 592 bytes (<-byte size may vary) of shared memory ("shared pool","unknown object","sga heap(1,1)","KGLHD")" on my Agent when I'm replicating to an Oracle Database?

The reason the user is receiving this error is due to Oracle's parameters for SHARED_POOL_SIZE. Please note that when SGA_TARGET is set and the parameter is not specified, then the default is “0” (internally determined by the Oracle Database), but if the parameter is specified, then the user-specified value indicates a minimum value for the memory pool.

In the case where a value was set for SGA_TARGET, that would be the value the user would need to update rather than the SHARED_POOL_SIZE since by setting SGA_TARGET, the user is using automatic SGA management. Hence, there is no need to manually set the value of SHARED_POOL_SIZE because Oracle will internally transfer memory between the SGA components.

In the case where the user is more concerned with setting a larger value for SGA_TARGET, the user can also make a larger value for SHARED_POOL_SIZE but the value must be smaller than SGA_TARGET to avoid encountering the following issue.

SGA_TARGET = 1GB
SHARED_POOL_SIZE = no value

The user will encounter an issue when the value of SHARED_POOL_SIZE exceeds the value of SGA_TARGET

We highly recommended setting the SGA_TARGET value at a minimum of 5GB, therefore, if the SHARED_POOL_SIZE value is at 1GB, the SGA_TARGET will still have at least 4GB for allocation of other memory components that are concurrently stored in SGA_TARGET.

Please restart the Oracle Database after making the described value changes

Please refer to the SHARED_POOL_SIZE for more details. Please also see SGA_TARGET for additional information.

1.2 How does the SQL agent commit to the database?

The database agent leverages the default connection commit strategy of the JDBC driver for which Oracle is auto commit. The agent does not explicitly decide when to perform a commit, the JDBC driver makes this decision.

The agent retrieves a message from the message store in the order they were published, performs the required processing such as decryption, validation etc., possibly determines the type of SQL operation required (such as update or insert), and then issues the request to the database. The agent then determines the response and does any further processing required. Once completed it will fetch the next message from the message store in the queue.

This is what typically happens. You can configure either multiple tasks to run against a single queue or you can configure multiple instances of a single task to run against a single queue. This is done primarily when throughput of the agent is an issue. Both of these configurations introduce more than a single consumer of the queue and so the order in which the database transaction occurs could be different than the order of the messages within the message store due to scheduling of the task or thread.

1.3 Handling Schema Changes

The database agent handles schema changes in your instance as follows:

  • Columns that are added to a ServiceNow table will be automatically added to the table in the database.
  • When a column's max size is increased, the agent will automatically increase the column's size to the maximum size for that database. In the case of MySQL, the column will automatically transition to a CLOB data type.
  • If a column is changed from a different data type to another data type, the data in this column will be skipped (the record itself will insert/update all other columns).
replicator_agent_database_faq.txt · Last modified: 2018/08/06 22:52 by paul