Creating a Downstream Capture Process That Assigns Log Files Explicitly

BEGIN
  DBMS_CAPTURE_ADM.CREATE_CAPTURE(
    queue_name         => 'ORCL2_QUEUE',
    capture_name       => 'strm05_capture',
    rule_set_name      => NULL,
    start_scn          => NULL,
    source_database    => 'orcl1',
    use_database_link  => false,
    first_scn          => 543676, -- Use value from Step 2
    logfile_assignment => 'explicit');
END;

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
QUEUE_TABLE=>'STRMADMIN.ORCL1_QUEUE_TABLE',
QUEUE_NAME=>'STRMADMIN.ORCL1_QUEUE',
QUEUE_USER=>'strmadmin');
END;

begin
dbms_streams_adm.add_table_rules(
table_name => 'linyu.test',
streams_type => 'capture',
streams_name => 'strmadmin_orcl1capture',
queue_name => 'STRMADMIN.ORCL1_QUEUE',
include_dml => TRUE,
include_ddl => false,include_tagged_lcr => false,inclusion_rule => true);
end;

begin
dbms_streams_adm.add_table_propagation_rules(
table_name => 'linyu.test',
streams_name => 'strmorcl1_propagation',
source_queue_name => 'STRMADMIN.ORCL1_QUEUE',
destination_queue_name => 'STRMADMIN.ORCL2_QUEUE@orcl12',
include_dml => true,
include_ddl => false,
include_tagged_lcr => false,
source_database => 'orcl1',
inclusion_rule => true,queue_to_queue => true);
end;

begin
dbms_capture_adm.start_capture(
capture_name => 'strmadmin_orcl1capture');
end;

 

select * from AQ$OE_Q_TABLE_ANY;

select * from strmadmin.aq$_orcl1_queue_table_c

BEGIN
 DBMS_STREAMS_ADM.SET_UP_QUEUE(
   queue_user => 'STRMADMIN');
END;

SELECT * FROM DBA_CAPTURE;
SELECT * FROM

begin
dbms_streams_auth.grant_admin_privilege(
grantee => 'strmadmin',
grant_privileges => true);
end;

Creating a Downstream Capture Process That Assigns Log Files Explicitly

To create a capture process that performs downstream capture, you must use the CREATE_CAPTURE procedure. This section describes creating a downstream capture process that assigns redo log files explicitly. That is, you must use the DBMS_FILE_TRANSFER package, FTP, or some other method to transfer redo log files from the source database to the downstream database, and then you must register these redo log files with the downstream capture process manually.

In this example, assume the following:

• The source database is dbs1.net and the downstream database is dbs2.net.
• The capture process that will be created at dbs2.net uses the strm03_queue.
• The capture process will capture DML changes to the hr.departments table.
• The capture process does not use a database link to the source database for administrative actions.

Complete the following steps:

1. Connect to the source database dbs1.net as the hr user:

   CONNECT hr/hr@dbs1.net
   
   

2. Specify primary key supplemental logging for the hr.departments table:

   ALTER TABLE hr.departments ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
   
   

   Primary key supplemental logging is required for the hr.departments table because this example creates a capture processes that captures changes to this table.

3. Connect to the source database dbs1.net as the Streams administrator. For example, if the Streams administrator is strmadmin, then issue the following statement:

   CONNECT strmadmin/strmadminpw@dbs1.net
   
   

   If you do not use a database link from the downstream database to the source database, then a Streams administrator must exist at the source database.

4. If there is no capture process at dbs2.net that captures changes from dbs1.net, then perform. a build of the dbs1.net data dictionary in the redo log. This step is optional if a capture process at dbs2.net is already configured to capture changes from the dbs1.net source database.

   SET SERVEROUTPUT ON
   DECLARE
     scn  NUMBER;
   BEGIN
     DBMS_CAPTURE_ADM.BUILD(
       first_scn => scn);
     DBMS_OUTPUT.PUT_LINE('First SCN Value = ' || scn);
   END;
   /
   First SCN Value = 409391
   
   

   This procedure displays the valid first SCN value for the capture process that will be created at dbs2.net. Make a note of the SCN value returned because you will use it when you create the capture process at dbs2.net.

   If you run this procedure to build the data dictionary in the redo log, then when the capture process is first started at dbs2.net, it will create a LogMiner data dictionary using the data dictionary information in the redo log.

5. Prepare the hr.departments table for instantiation:

   BEGIN
     DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(
        table_name  =>  'hr.departments');
   END;
   /
   
   

6. Connect to the downstream database dbs2.net as the Streams administrator. For example, if the Streams administrator is strmadmin, then issue the following statement:

   CONNECT strmadmin/strmadminpw@dbs2.net
   
   

7. Run the CREATE_CAPTURE procedure to create the capture process and specify the value obtained in Step 2 for the first_scn parameter:

   BEGIN
     DBMS_CAPTURE_ADM.CREATE_CAPTURE(
       queue_name         => 'strm03_queue',
       capture_name       => 'strm05_capture',
       rule_set_name      => NULL,
       start_scn          => NULL,
       source_database    => 'dbs1.net',
       use_database_link  => false,
       first_scn          => 409391, -- Use value from Step 2
       logfile_assignment => 'explicit');
   END;
   /
   
   

   Running this procedure performs the following actions:

   • Creates a capture process named strm05_capture at the downstream database dbs2.net. A capture process with the same name must not exist.
   • Associates the capture process with an existing queue on dbs2.net named strm03_queue
   • Specifies that the source database of the changes that the capture process will capture is dbs1.net
   • Specifies that the first SCN for the capture process is 409391. This value was obtained in Step 2. The first SCN is the lowest SCN for which a capture process can capture changes. Because a first SCN is specified, the capture process creates a new LogMiner data dictionary when it is first started, regardless of whether there are existing LogMiner data dictionaries for the same source database.
   • Specifies new redo log files from dbs1.net must be assigned to the capture process explicitly. After a redo log file has been transferred to the computer running the downstream database, you assign the log file to the capture process explicitly using the following DDL statement:

     ALTER DATABASE REGISTER LOGICAL LOGFILE file_name FOR capture_process;
     
     

     Here, file_name is the name of the redo log file and capture_process is the name of the capture process that will use the redo log file at the downstream database. You must add redo log files manually if the logfile_assignment parameter is set to explicit.

   This step does not associate the capture process strm05_capture with any rule set. A rule set will be created and associated with the capture process in the next step.

   See Also: "Capture Process Creation" "SCN Values Relating to a Capture Process" Oracle Database SQL Reference for more information about the ALTER DATABASE statement Oracle Data Guard Concepts and Administration for more information registering redo log files

8. Create the positive rule set for the capture process and add a rule to it:

   BEGIN 
     DBMS_STREAMS_ADM.ADD_TABLE_RULES(
       table_name          =>  'hr.departments',
       streams_type        =>  'capture',
       streams_name        =>  'strm05_capture',
       queue_name          =>  'strm03_queue',
       include_dml         =>  true,
       include_ddl         =>  false,
       include_tagged_lcr  =>  false,
       source_database     =>  'dbs1.net',
       inclusion_rule      =>  true );
   END;
   /
   
   

   Running this procedure performs the following actions:

   • Creates a rule set at dbs2.net for capture process strm04_capture. The rule set has a system-generated name. The rule set is a positive rule set for the capture process because the inclusion_rule parameter is set to true.
   • Creates a rule that captures DML changes to the hr.departments table, and adds the rule to the rule set for the capture process. The rule has a system-generated name. The rule is added to the positive rule set for the capture process because the inclusion_rule parameter is set to true.
9. After the redo log file at the source database dbs1.net that contains the first SCN for the downstream capture process is archived, transfer the archived redo log file to the computer running the downstream database. The BUILD procedure in Step 4 determined the first SCN for the downstream capture process. If the redo log file is not yet archived, you can run the ALTER SYSTEM SWITCH LOGFILE statement on the database to archive it.

   You can run the following query at dbs1.net to identify the archived redo log file that contains the first SCN for the downstream capture process:

   COLUMN NAME HEADING 'Archived Redo Log|File Name' FORMAT A50
   COLUMN FIRST_CHANGE# HEADING 'First SCN' FORMAT 999999999
   
   SELECT NAME, FIRST_CHANGE# FROM V$ARCHIVED_LOG
     WHERE FIRST_CHANGE# IS NOT NULL AND DICTIONARY_BEGIN = 'YES';
   

   Transfer the archived redo log file with a FIRST_CHANGE# that matches the first SCN returned in Step 4 to the computer running the downstream capture process.

10. At the downstream database dbs2.net, connect as an administrative user and assign the transferred redo log file to the capture process. For example, if the redo log file is /oracle/logs_from_dbs1/1_10_486574859.dbf, then issue the following statement:

    ALTER DATABASE REGISTER LOGICAL LOGFILE 
       '/oracle/logs_from_dbs1/1_10_486574859.dbf' FOR 'strm05_capture';
    
    

Now you can configure propagation or apply, or both, of the LCRs captured by the strm05_capture capture process.

In this example, if you want to use an apply process to apply the LCRs at the downstream database dbs2.net, then set the instantiation SCN for the hr.departments table at dbs2.net. If this table does not exist at dbs2.net, then instantiate it at dbs2.net.

For example, if the hr.departments table exists at dbs2.net, then set the instantiation SCN for the hr.departments table at dbs2.net by running the following procedure at the source database dbs1.net:

CONNECT strmadmin/strmadminpw@dbs1.net

DECLARE
  iscn  NUMBER;         -- Variable to hold instantiation SCN value
BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@DBS2.NET(
    source_object_name    => 'hr.departments',
    source_database_name  => 'dbs1.net',
    instantiation_scn     => iscn);
END;
/

After the instantiation SCN has been set, you can configure an apply process to apply LCRs for the hr.departments table from the strm03_queue queue. Setting the instantiation SCN for an object at a database is required only if an apply process applies LCRs for the object.

Note: To set the instantiation SCN using the previous example requires a database link from the source database to the destination database. If you want the database objects to be synchronized at the source database and the destination database, then make sure the database objects are consistent when you set the instantiation SCN at the destination database. In the previous example, the hr.departments table should be consistent at the source and destination databases when the instantiation SCN is set.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7301064/viewspace-474768/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7301064/viewspace-474768/