Oracle Streams DBA Interview Questions

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 (based on DB triggers, more performance overhead). Oracle Streams sits on top of Oracle CDC and it's a full transport mechanism (over e.g. HTTP) 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 synch but instead more for server notification of resultset changes to clients, mostly in the context of data caches on the client side. What is Stream Replication? Oracle streams extracts changes from transaction (redo) logs to propagate them to the destination database.  It relies on methodlogy of redo log files for the database changes.  The db transactions are recorded in the redo logs files, which are exracted 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 apply handler, you can have a variety of data transformations.  What is Stream pool in oracle 10g? In a single database, you can specify that Streams memory be allocated from a new pool in the SGA called the Streams pool. To configure the Streams pool, specify the size of the pool in bytes using the STREAMS_POOL_SIZE initialization parameter. If the size of the Streams pool is greater than zero, then any SGA memory used by Streams is allocated from the Streams pool. If the size of the Streams pool is zero, then the memory used by Streams is allocated from the shared pool and may use up to 10% of the shared pool.  What is LCR? A message with a specific format that describes a database change.  All the captured changes are converted into events called LCR.  What is 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 acutal transactional activity occurs.  It is an optional background process whose process name is cnnn, in which nnn is a capture process number.  The infrastructure of LogMiner is used for this process. What is 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 Annn, in which nnn is an Apply process number. 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. 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 to which rows it needs updating?  Supplemental logging provides the answers to these questions. 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. What is down stream 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 resources that is utilized in the source database.  All of the capture and enqueue activities are done on the down stream database.  The use of this allows you more flexibility and improves scalability. You want to relocate the STREAMS component from the SYSAUX tablespace to the SYS_COM tablespace. The SYS_COM tablespace is a bigfile tablespace. Which statement is true? A. You cannot relocate the STREAMS component. B. None of the occupants of the SYSAUX tablespace can be relocated. C. You cannot relocate the STREAMS component to a bigfile tablespace. D. The STREAMS component under the SYSAUX tablespace can be relocated only to the SYSTEM  tablespace. Answer: A. You cannot relocate the STREAMS component.