1. What is Oracle Streams?
The Oracle streams product from Oracle Corporation encourages users of Oracle databases to propagate information within and between databases.
It provides tools to capture, process (stage), and manage database events through Advanced Queuing queues.
2. What Is Streams Pool In Oracle 10g?
Streams pool is a part of System Global Area (SGA) from which memory for Streams is allocated if it is configured. It can be configured by specifying initialization parameter, STREAMS_POOL_SIZE.
If the size of the Streams pool is greater than zero, then any SGA memory used by Stream is allocated from the Streams pool.
If the size of the Stream pool is equal to zero, then the memory used by Streams is allocated from the shared pool that may use up to 10% of the shared pool.
3. What Is Database Change Notification?
Database Change Notification is a service that is used to notify the registered clients whenever a change is committed.
4. What is the difference between Oracle Streams and Change Data Capture?
Oracle CDC is all about capturing changes in DB tables and the changes are stored in special Oracle tables. There are two modes of CDC operation: asynchronous (based on Java) or synchronous. Oracle Streams sits on top of Oracle CDC and it’s a full transport mechanism for data synchronization between 2 servers. It’s based on Oracle Advanced Queues technology and it’s designed for high performance and reliability.
Both Oracle CDC and Streams are generally used for data synchronization between Oracle DB servers. With Oracle CDC, you don’t have to use Oracle Streams for, e.g. you could write your own data export routines which create flat files for the purpose of synchronization between 2 DB servers, whereas with Streams you must have a network link between the 2 servers.
Database Change Notification is something else again, it’s not used for server-to-server sync but instead more for server notification of result set changes to clients, mostly in the context of data caches on the client side.
5. What is Stream Replication?
Oracle streams extracts changes from transaction (redo) logs to propagate them to the destination database. It relies on methodology of redo log files for the database changes. The db transactions are recorded in the redo logs files, which are extracted by the log transport service. It can act as a DR solution. Since it is a pure data solution, data can be maintained as either a complete replica of the source database or as a subset of the data. It can be sent to multiple destinations. With the help of an application handler, you can have a variety of data transformations.
6. What is LCR?
A message with a specific format that describes a database change. All the captured changes are converted into events called LCR.
7. What is the Capture Process?
The capture process is an oracle background process that scans the database redo log files to extract the DML and DDL changes that were done on the database objects. It usually runs on the source database system where the actual transactional activity occurs. It is an optional background process whose process name is cnn, in which nnn is a capture process number. The infrastructure of LogMiner is used for this process.
8. What is the Apply Process?
The apply process is an optional oracle background process that dequeues LCRs and user messages from a specific queue. After that it either applies each one directly or passes it to a user-defined procedure called a handler. The process name is Anna, in which nnn is an Apply process number.
9. What is SYS.AnyData?
Events of different data types can be captured into a SYS.AnyData queue. This is defined at the database level. Majority of the payload type can be wrapped in a SYS.AnyData queue. Users and applications may enqueue events into a SYS.AnyData.
10. Why is additional supplemental logging needed?
When a particular column is updated at the source database table for a set of rows, the values in the column or columns are logged by default. When these values need to be applied on the destination side, where does oracle apply them and how does oracle identify which rows it needs updating? Supplemental logging provides the answers to these questions.
11. What is direct apply?
Directly apply means that the apply process is going to apply the LCR without having to run a user procedure. It is the simplest form of applying to the destination.
12. What is downstream capture?
This is a feature that you can use to shift the capture process from the primary database to another server that is dedicated for just that purpose. If you use this, there is no additional resource that is utilized in the source database. All of the capture and enqueue activities are done on the downstream database. The use of this allows you more flexibility and improves scalability.
13. What are the uses of streams?
Message Queuing
Data Replication
Event Management and Notification
Data Warehouse Loading
Data Protection
Database Availability During Upgrade and Maintenance Operations
14.What is changing data capture?
CDC is a technique to capture changes in database tables. These changes are stored in special tables. CDC operates in two modes: asynchronous, which is based on Java, and synchronous, which is used on database triggers. Performance overhead is higher in case of synchronous CDC.
15.What is the capture process?
Capture process extracts database changes from redo log files at source. It is a background process.
16. How can you set a DML handler?
DML handler can be set by using the following statement:
Dbms_apply_adm.set_dml_handler (object_name, object_type, operation_name, error_handler, user_procedure, apply_db_link)
17. Why is additional supplemental logging needed?
Default logging process records the information about DML operations.
Supplemental logging is required to identify the rows to be updated on each destination.
18. How can you find the setup of lob_assembly?
You find the setup of lob_assembly by using the DBA_APPLY_DML_HANDLER view.
19. What is Database Change Notification?
Database Change Notification is a service that is used to notify the registered clients whenever a change is committed.
20. List some important Streams views?
Following are some important Streams views:
DBA_STREAMS_TABLE_RULES
DBA_APPLY_CONFLICT_COLUMNS
GV$STREAMS_CAPTURE
