Oracle Stream Test ____oracle

Primary database
ip:192.168.2.100
Db:rdbms 11.2.0.4
Oracle_sid:test
Global_name:test


From the database
ip:192.168.2.14
Db:rdbms 11.2.0.4
Oracle_sid:orcl

Global_name:orcl

1 for test, ORCL Library to open archive
2 Configure the TEST,ORCL library related parameters

Alter system set aq_tm_processes=2 Scope=both;  
Alter system set Global_names=true Scope=both; 
Alter system set JOB_QUEUE_PROCESSES=10 Scope=both;  
Alter system set PARALLEL_MAX_SERVERS=20 Scope=both;   
Alter system set UNDO_RETENTION=3600 Scope=both;  
Alter system set nls_date_format= ' Yyyy-mm-dd HH24:MI:SS ' scope=spfile;   
Alter system set STREAMS_POOL_SIZE=25M Scope=spfile;  
Alter system set utl_file_dir= ' * ' Scope=spfile;   
Alter system set OPEN_LINKS=4 Scope=spfile;  

3 Configuring Streams users for the TEST,ORCL library

Create tablespace streams_tbs datafile '/u01/app/oracle/oradata/test/streams_tbs01.dbf '   
size 100m autoextend on   
next 1m maxsize unlimited;  

Create tablespace streams_tbs datafile '/u01/app/oracle/oradata/orcl/streams_tbs01.dbf '   
size 100m autoextend on   
next 1m maxsize unlimited; 

Create user stradmin identified by Oracle default Tablespace Streams_tbs;
Grant Connect, resource, DBA, Aq_administrator_role to stradmin;  

Begin  
    Dbms_streams_auth.grant_admin_privilege (  
        grantee => ' stradmin ',  
        grant_privileges => True  
    );  

4 Configuration Monitor

--listnern.ora Sid_list_listener = (Sid_list = (Sid_desc = (global_dbname = Test) (Oracle_home =/u01/a  Pp/oracle/product/11.2.0/dbhome_1) (sid_name = test)) LISTENER = (DESCRIPTION = (address = (PROTOCOL
    = TCP) (HOST = 192.168.2.100) (PORT = 1521)) Adr_base_listener =/u01/app/oracle Sid_list_listener = (Sid_list =  (Sid_desc = (Global_dbname = ORCL) (Oracle_home =/u01/app/oracle/product/11.2.0/dbhome_1) (Sid_name = ORCL)) LISTENER = (Description_list = (DESCRIPTION = (address = (PROTOCOL = IPC) (KEY = EXTPROC1521) ) (DESCRIPTION = (address = (PROTOCOL = TCP) (HOST = test02) (PORT = 1521))) Adr_base_listener =/ U01/app/oracle--tnsnames.ora ORCL = (DESCRIPTION = (address = (PROTOCOL = TCP) (HOST = test02) (PORT = 1521)) (C  Onnect_data = (SERVER = dedicated) (service_name = ORCL)) TEST = (DESCRIPTION = (address_list = (address = (PROTocol = TCP) (HOST = 192.168.2.100) (PORT = 1521)) (Connect_data = (SERVER = dedicated) (service_name
   = test))

5 Enable supplemental Log on the main library

ALTER DATABASE force logging;
ALTER DATABASE add supplemental log data;

SELECT supplemental_log_data_min,  
    supplemental_log_data_pk,   
    supplemental_log_data_ui,  
    Supplemental_ Log_data_all from   
v$database;

6 Creating Dblink

--The main library
Conn stradmin/oracle 
CREATE DATABASE link ORCL connect to stradmin identified by Oracle using ' ORCL ';
--From the library
conn stradmin/oracle 
CREATE DATABASE link Test connect to stradmin identified by Oracle using ' test ';

7 Creating a queue stream

--Main Library
Conn stradmin/oracle
exec dbms_streams_adm.set_up_queue (queue_table => ' test_queue_table ', queue_ Name => ' Test_queue '
--
stradmin/oracle exec dbms_streams_adm.set_up_queue from library conn
(queue_table = > ' orcl_queue_table ', queue_name => ' Orcl_queue ')  

8 Creating sample users for testing

--In the main library operation, create a tablespace created
tablespace bb datafile '/u01/app/oracle/oradata/test/bb.dbf '   
size 100m autoextend on   
next 1m maxsize unlimited;
--Create users in the main library bb create user
BB identified by Oracle default tablespace BB;
Grant Connect,resource to BB;

--Create a table space from the library create  
tablespace bb datafile '/u01/app/oracle/oradata/orcl/bb.dbf '   
size 100m autoextend   
on Next 1m maxsize unlimited;
--Create user on from the library creation user 
BB identified by Oracle default tablespace BB;
Grant Connect,resource to BB;

9 Creating a capture process on the main library

Conn stradmin/oracle 
begin  
    Dbms_streams_adm.add_schema_rules (  
        schema_name => ' BB ',  
        streams_ Type => ' Capture ',  
        streams_name => ' capture_test ',  
        queue_name => ' Stradmin.test_queue ',  
        include _dml => True,  
        include_ddl => true,   
        INCLUDE_TAGGED_LCR => false,  
        source_database => null,  
        Inclusion_rule => true  
    );  
End  

10 Instantiating a replicated database

Export the main library BB data exp userid=bb/oracle@test file= '/home/oracle/bb1.dmp ' object_consistent=y rows=y [oracle@test admin]$ exp Use  Rid=bb/oracle@test file= '/home/oracle/bb1.dmp ' object_consistent=y rows=y export:release 11.2.0.4.0-production on Thu  Feb 1 21:42:43 2018 Copyright (c) 1982, Oracle and/or its affiliates.


All rights reserved. Connected to:oracle Database 11g Enterprise Edition release 11.2.0.4.0-64bit Production with the partitioning, OLAP, Da Ta Mining and real application testing options Export do in Us7ascii character set and Al16utf16 NCHAR character set Ser Ver uses Al32utf8 character set (Possible charset). Exporting Pre-schema procedural objects and actions. Exporting foreign function library names for user BB. Exporting public type synonyms. Exporting private type synonyms. Exporting object type definitions for the user BB about to export BB ' s objects .... Exporting database links. Exporting sequence numbers. Exporting cluster definitions.About to export BB via conventional Path .... Exporting synonyms. Exporting views. Exporting stored procedures. Exporting operators. Exporting referential integrity constraints. exporting triggers. Exporting Indextypes. Exporting bitmap, functional and extensible indexes. Exporting posttables actions. Exporting materialized views. Exporting snapshot logs. Exporting job queues. Exporting refresh groups and children. Exporting dimensions. Exporting Post-Schema procedural objects and actions.



Exporting statistics Export terminated successfully without warnings. Import imp userid=bb/oracle@orcl file= '/home/oracle/bb1.dmp ' ignore=y commit=y log= '/home/oracle/bb.log ' Streams_ Instantiation=y fromuser=bb touser=bb [oracle@test02 ~]$ imp userid=bb/oracle@orcl file= '/home/oracle/bb1.dmp ' ignore= Y commit=y log= '/home/oracle/bb.log ' streams_instantiation=y fromuser=bb touser=bb import:release 11.2.0.4.0-producti On Thu Feb 1 21:44:04 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates.


All rights reserved. Connected to:oracle Database 11g Enterprise Edition release 11.2.0.4.0-64bit Production with the partitioning, OLAP, Da Ta Mining and real application testing options Export file created through export:v11.02.00 via conventional path import done In Us7ascii character set and Al16utf16 NCHAR character Set import server uses we8mswin1252 character set (Possible Charse T conversion) Import terminated successfully without warnings.

11 Create propagate process in main library

Conn stradmin/oracle 
begin   
    Dbms_streams_adm.add_schema_propagation_rules (   
        schema_name => ' BB ',   
        streams_name => ' Test_to_orcl ',   
        source_queue_name => ' stradmin.test_queue ',   
        destination_queue_ Name => ' Stradmin.orcl_queue@orcl ',   
        include_dml => true,   
        include_ddl => true,   
        include_tagged_ LCR => false,   
        source_database => ' test ',   
        inclusion_rule => true  
    );   
End;  

--Modifying propagation hibernation time is 5, which means that the LCR (logical change Recoder) is propagated in real time.
begin  
    Dbms_aqadm.alter_propagation_schedule (  
        queue_name => ' test_queue ',  
        destination = > ' ORCL ',  
        destination_queue => ' orcl_queue ',  
        latency => 5  
    );  
End  

12 creating the application process, from the library

Conn stradmin/oracle

 begin   
    Dbms_streams_adm.add_schema_rules (   
        schema_name => ' BB ',   
        streams_ Type => ' Apply ',   
        streams_name => ' Apply_orcl ', queue_name =>   
        ' stradmin.orcl_queue ',   
        include_dml => true,   
        include_ddl => true,   
        INCLUDE_TAGGED_LCR => false,   
        source_database => ' test ',   
        Inclusion_rule => true  
    );   
End   

13 Start Stream

Conn stradmin/oracle
exec dbms_apply_adm.start_apply (apply_name => ' APPLY_ORCL ')- 

 
 -Main Library, start capture process
Conn stradmin/oracle
 exec dbms_capture_adm.start_capture (capture_name => ' capture_test ');

14 Test
--In the main library

Conn BB/ORACLE@ORCL 

CREATE TABLE T1 (ID number, name VARCHAR2 ());  
INSERT into T1 values (1, ' AAA '); 
INSERT into T1 values (2, ' BBB ');  
INSERT into T1 values (3, ' CCC ');  
Commit

--On the library

Conn Bb/oracle 
desc T1

15 Stop Stream

--
Conn stradmin/oracle
exec dbms_capture_adm.stop_capture (capture_name => ' capture_test ') on the main library 
--from the library
Conn stradmin/oracle 
exec dbms_apply_adm.stop_apply (apply_name => ' APPLY_ORCL ')

16 Clear All Configurations

--The main library
Conn stradmin/oracle
exec dbms_streams_adm.remove_streams_configuration (); 
--From the library
conn stradmin/oracle
exec dbms_streams_adm.remove_streams_configuration ();

End 2018-02-01 has been tested