//
you're reading...
Oracle Streams

Configure Oracle Streams BI-Directional (11gR2)

Configure Oracle Streams BI-Directional (11gR2)

Configure the Oracle database listeners and tnsnames.ora for both main database(source database) and target database

Some database instance parameters need to be checked on main database(source database)

[oracle@TESTDB01 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 1 16:36:21 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> show parameter global_names;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_names                         boolean     TRUE

SQL> show parameter db_domain;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_domain                            string      newzonetech.com

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
SM1

Some database instance parameters need to be checked on target database

[oracle@TESTDB02 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 1 16:36:21 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> show parameter global_names;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_names                         boolean     TRUE

SQL> show parameter db_domain;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_domain                            string      newzonetech.com

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
SM2

In main database server(source database server) – testdb01.newzonetech.com, modify the $ORACLE_HOME/network/admin/listener.ora

LISTENER = (  
    DESCRIPTION_LIST = (  
        DESCRIPTION =   
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.30)(PORT = 1521))  
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))  
    )  
)  
  
SID_LIST_LISTENER = (  
    SID_LIST = (  
        SID_DESC =  
            (GLOBAL_DBNAME = sm1)  
            (ORACLE_HOME = /ora01/app/oracle/product/11.2.0.4/dbhome_1)  
            (SID_NAME=sm1)  
    )  
)  
ADR_BASE_LISTENER = /ora01/app/oracle

In main database server(source database server) – testdb01.newzonetech.com, modify the $ORACLE_HOME/network/admin/tnsnames.ora

  
sm1.testdb01.newzonetech.com = (  
    DESCRIPTION = (  
        ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.30)(PORT = 1521)  
    )  
    (CONNECT_DATA =  
        (SERVER = DEDICATED)  
        (SERVICE_NAME = sm1.newzonetech.com)  
    )  
)  
  
sm2.testdb02.newzonetech.com = (  
    DESCRIPTION = (  
        ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.31)(PORT = 1521)  
    )  
    (CONNECT_DATA =  
        (SERVER = DEDICATED)  
        (SERVICE_NAME = sm2.newzonetech.com)  
    )  
)  

In target database server – testdb02.newzonetech.com, modify the $ORACLE_HOME/network/admin/listener.ora

LISTENER = (  
    DESCRIPTION_LIST = (  
        DESCRIPTION =   
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.31)(PORT = 1521))  
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))  
    )  
)  
  
SID_LIST_LISTENER = (  
    SID_LIST = (  
        SID_DESC =  
            (GLOBAL_DBNAME = sm2)  
            (ORACLE_HOME = /ora01/app/oracle/product/11.2.0.4/dbhome_1)  
            (SID_NAME=sm2)  
    )  
)  
ADR_BASE_LISTENER = /ora01/app/oracle  

In target database server – testdb02.newzonetech.com, modify the $ORACLE_HOME/network/admin/tnsnames.ora

sm1.testdb01.newzonetech.com = (  
    DESCRIPTION = (  
        ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.30)(PORT = 1521)  
    )  
    (CONNECT_DATA =  
        (SERVER = DEDICATED)  
        (SERVICE_NAME = sm1.newzonetech.com)  
    )  
)  
  
sm2.testdb02.newzonetech.com = (  
    DESCRIPTION = (  
        ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.31)(PORT = 1521)  
    )  
    (CONNECT_DATA =  
        (SERVER = DEDICATED)  
        (SERVICE_NAME = sm2.newzonetech.com)  
    )  
)

Create user for Oracle Steams

Create the streams administrator in main database(source database)


[oracle@testdb01 dbs]$ sqlplus sys/oracle123@sm1.testdb01.newzonetech.com as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 1 15:31:16 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> CREATE TABLESPACE streams_tbs DATAFILE '+TESTDBDATA' SIZE 1024M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

SQL>
CREATE USER strmadmin IDENTIFIED BY strmadmin
DEFAULT TABLESPACE streams_tbs
QUOTA UNLIMITED ON streams_tbs;
SQL> GRANT DBA TO strmadmin;
SQL>
BEGIN
	DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
	grantee => 'strmadmin',
	grant_privileges => true);
END;
/

Create the streams administrator in target database


[oracle@testdb02 dbs]$ sqlplus sys/oracle123@sm2.testdb02.newzonetech.com as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 1 15:31:16 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> CREATE TABLESPACE streams_tbs DATAFILE '+TESTDBDATA' SIZE 1024M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

SQL>
CREATE USER strmadmin IDENTIFIED BY strmadmin
DEFAULT TABLESPACE streams_tbs
QUOTA UNLIMITED ON streams_tbs;
SQL> GRANT DBA TO strmadmin;
SQL>
BEGIN
	DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
	grantee => 'strmadmin',
	grant_privileges => true);
END;
/

Create the user schema – davie in both main(source) and target databases

[oracle@testdb01 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 1 14:07:39 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL>
CREATE USER davie IDENTIFIED BY davie
DEFAULT TABLESPACE USERS
QUOTA UNLIMITED ON USERS;
[oracle@testdb02 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 1 14:07:39 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL>
CREATE USER davie IDENTIFIED BY davie
DEFAULT TABLESPACE USERS
QUOTA UNLIMITED ON USERS;

Check whether the correct privilage has been granted for streams through dba_streams_administrator

SQL> SELECT * FROM dba_streams_administrator; 

USERNAME                       LOC ACC
------------------------------ --- ---
STRMADMIN                      YES YES

Setup instance initalization parameters for Oracle streams

_job_queue_interval = 1, _job_queue_interval scan parameters job queue, the default is 5, that is scanned once 5s What are the considerations: setting a smaller _job_queue_interval conducive to propagation job.

SQL> alter system set "_job_queue_interval" = 1 scope = spfile;
SQL> alter system set compatible="11.2.0.0" scope=spfile;
SQL> alter system set global_names=true scope=spfile;
SQL> alter system set job_queue_processes=15;
SQL> alter system set PARALLEL_MAX_SERVERS=40;

STREAMS_POOL_SIZE specify the size oracle stream pool. Streams pool contains buffered queue messages. In addition, the stream pool will be used for parallel capture and apply internal communications.

Analyzing the proposed reference information V$STREAMS_POOL_ADVICE view of the optimal size to avoid overflow spill.

The parameters can be dynamically modified. If this parameter is zero instances streams related processes and jobs will not run. Streams pool size is influenced by the following factors:

  • The parallelism of the capture process, each additional capture process is necessary to increase the size of 10MB to stream pool;
    Also when the capture PARALLELISM parameter is greater than 1, it is necessary to increase the size of 10Mb*parallelism stream pool;
    For example, if the degree of parallelism for a capture process parallelism is set to 3, then the need to increase 30Mb of Streams pool.
  • Parallelism apply process for each additional process is necessary to increases 1MB for the streams pool;
    Also when the degree of parallelism apply process is greater than 1, in order to increase 1Mb * parallelism streams pool size;
    For example, an apply process parallelism is set to 5, you need to increase the pool of streams 5Mb.
  • Logical Change Records (LCRS) is stored in the cache buffered queues in the queue;
    Appropriate to increase the Streams pool size to accommodate the amount of data on the source and target libraries for data replication;
    Oracle recommends a minimum set of low load on the database Streams pool is 256Mb, 500Mb and is set in a high degree of active OLTP environments;
    Recommendations given by V $ STREAMS_POOL_ADVISE view to further adjust the size of the Streams Pool
    To a reasonable value to avoid excessive queue buffer overflow to disk.
select * from v$streams_pool_advice;
alter system set streams_pool_size=256M;

Restart both main and target database instances to apply all parameters.

Create supplemental log for user schema

Creating supplemental log for davie schema, we can use stored procedures prepare_schema_instantiation of the dbms_capture_adm to create a package supplemental logging for the specified mode:

NAME
  prepare_schema_instantiation()
FUNCTION
  prepare a schema for instantiation
PARAMETERS
  schema_name            - (IN)  the name of the schema to prepare
  supplemental_logging   - (IN)  supplemental logging level
                                 ('NONE', 'KEYS', or 'ALL')
								 
NOTES
  KEYS means PRIMARY KEY, UNIQUE INDEX, and FOREIGN KEY levels combined.
----------------------------------------------------------------------------*/
  PROCEDURE prepare_schema_instantiation(
   schema_name            IN VARCHAR2,
   supplemental_logging   IN VARCHAR2 DEFAULT 'KEYS');

The default option is supplemental logging Key, is the PRIMARY KEY, UNIQUE INDEX, and FOREIGN KEY and other key creation of additional log IMPLICIT.

SELECT SCHEMA_NAME,
       SUPPLEMENTAL_LOG_DATA_PK log_pk, 
       SUPPLEMENTAL_LOG_DATA_FK log_fk,
       SUPPLEMENTAL_LOG_DATA_UI log_ui,
       SUPPLEMENTAL_LOG_DATA_ALL log_all
  FROM DBA_CAPTURE_PREPARED_SCHEMAS;

SCHEMA_NAME   LOG_PK   LOG_FK   LOG_UI   LOG_ALL
------------- -------- -------- -------- --------
SCOTT         IMPLICIT IMPLICIT IMPLICIT NO

Or

SQL> ALTER DATABASE FORCE LOGGING;   
Database altered.  
  
SQL> alter database add supplemental log data;   
Database altered.  
  
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN,  
    SUPPLEMENTAL_LOG_DATA_PK,   
    SUPPLEMENTAL_LOG_DATA_UI,  
    SUPPLEMENTAL_LOG_DATA_ALL   
FROM V$DATABASE;  
  
SUPPLEME SUP SUP SUP  
-------- --- --- ---  
YES      NO  NO  NO  

Create database links

Create the database link for target database in the main database(source database).

SQL> conn strmadmin/strmadmin;
Connected.

SQL> create database link sm2.testdb01.newzonetech.com connect to strmadmin identified by strmadmin using 'sm2.testdb01.newzonetech.com';
Database link created.

sm2.testdb01.newzonetech.com is the global database name, sm2 is the database name, testdb01.newzonetech.com is the domain name.

Create the database link for main database(source database) in the target database.

SQL> conn strmadmin/strmadmin;
Connected.

SQL> create database link sm1.testdb02.newzonetech.com connect to strmadmin identified by strmadmin using 'sm1.testdb02.newzonetech.com';
Database link created.

Create queue for capture and apply in the main database(source database)

[oracle@TESTDB01 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 1 14:07:39 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> SELECT name from v$database;

NAME
---------
SM1

SQL> connect strmadmin/strmadmin
Connected.

SQL>
begin
	dbms_streams_adm.set_up_queue(
	queue_table => 'apply_srctab',
	queue_name => 'apply_src',
	queue_user => 'strmadmin');
end;
/
PL/SQL procedure successfully completed.

SQL>
begin
	dbms_streams_adm.set_up_queue(
	queue_table => 'capture_srctab',
	queue_name => 'capture_src',
	queue_user => 'strmadmin');
end;
/ 
PL/SQL procedure successfully completed.

Create queue for capture and apply in the target database

[oracle@TESTDB02 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 1 14:07:39 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> SELECT name from v$database;

NAME
---------
SM2

SQL> connect strmadmin/strmadmin
Connected.

begin
	dbms_streams_adm.set_up_queue(
	queue_table => 'apply_desttab',
	queue_name => 'apply_dest',
	queue_user => 'strmadmin');
end;
/

PL/SQL procedure successfully completed.

begin
	dbms_streams_adm.set_up_queue(
	queue_table => 'capture_desttab',
	queue_name => 'capture_dest',
	queue_user => 'strmadmin');
end;
/ 

PL/SQL procedure successfully completed.

Create capture process for schema davie in the main database(source database) ‘sm1’

SQL> conn strmadmin/strmadmin@sm1
Connected.

SQL>
begin
	dbms_streams_adm.add_schema_rules (
	schema_name => 'davie',
	streams_type => 'capture',
	streams_name => 'captures_src',
	queue_name => 'capture_src',
	include_dml => true,
	include_ddl => true,
	inclusion_rule => true);
end;
/ 
PL/SQL procedure successfully completed.

Create apply process for schema davie in the main database(source database) ‘sm1’

SQL> conn strmadmin/strmadmin@sm1
Connected.

SQL>
begin
	dbms_streams_adm.add_schema_rules (
	schema_name => 'davie',
	streams_type => 'apply',
	streams_name => 'applys_src',
	queue_name => 'apply_src',
	include_dml => true,
	include_ddl => true,
	source_database => 'clinicb.rh3.oracle.com');
end;
/
PL/SQL procedure successfully completed.

If you want reslove the conflict issue in main database(source database), the conflict handlers must be set.

SQL> conn strmadmin/strmadmin@sm1.testdb01.newzonetech.com
Connected.

SQL>
begin
	dbms_streams_adm.add_schema_propagation_rules (
	schema_name => 'davie',
	streams_name => 'prop_src_to_dest',
	source_queue_name => 'capture_src',
	destination_queue_name => 'apply_dest@sm2.testdb02.newzonetech.com',
	include_dml => true,
	include_ddl => true,
	source_database => 'sm1.testdb01.newzonetech.com');
end;
/ 
PL/SQL procedure successfully completed.

Configure the capture process in the target database – ‘sm2’

SQL> conn strmadmin/strmadmin@sm2.testdb02.newzonetech.com
Connected.

begin
	dbms_streams_adm.add_schema_rules (
	schema_name => 'davie',
	streams_type => 'capture',
	streams_name => 'captures_dest',
	queue_name => 'capture_dest',
	include_dml => true,
	include_ddl => true);
end;
/ 
PL/SQL procedure successfully completed.

In main database(source database) – ‘sm1’, configure SCN(schema instantiation SCN) as SCN of the target database – ‘sm2’

SQL> conn strmadmin/strmadmin@sm1.testdb01.newzonetech.com
Connected.

SQL>
declare
	v_scn number;
begin
	v_scn := dbms_flashback.get_system_change_number();
	dbms_apply_adm.set_schema_instantiation_scn@sm1.testdb01.newzonetech.com(
	source_schema_name => 'davie',
	source_database_name => 'sm2.testdb02.newzonetech.com',
	instantiation_scn => v_scn,
	recursive => true);
end;
/ 

Configure the apply process in the target database – ‘sm2’

SQL> conn strmadmin/strmadmin@sm2.testdb02.newzonetech.com
Connected.

SQL>
begin
	dbms_streams_adm.add_schema_rules (
	schema_name => 'scott',
	streams_type => 'apply',
	streams_name => 'applys_dest',
	queue_name => 'apply_dest',
	include_dml => true,
	include_ddl => true,
	source_database => 'sm1.testdb01.newzonetech.com');
end;
/ 
PL/SQL procedure successfully completed.

Configure the propagation process in the target database

SQL> conn strmadmin/strmadmin@sm2.testdb02.newzonetech.com
Connected.

begin
	dbms_streams_adm.add_schema_propagation_rules (
	schema_name => 'scott',
	streams_name => 'prop_dest_to_src',
	source_queue_name => 'capture_dest',
	destination_queue_name => 'apply_src@sm1.testdb01.newzonetech.com',
	include_dml => true,
	include_ddl => true,
	source_database => 'sm2.testdb02.newzonetech.com');
end;
/ 
PL/SQL procedure successfully completed.

Initialize the object mode davie on the target database, there are several ways to initialize. If the objects do not exist, you can use the exp/imp or datapump tools to complete instantiation, if the objects are already exist, the stored procedure directly dbms_apply_adm.set_schema_instantiation_scn.
Our environment scott model already exists in the target library:

SQL> conn strmadmin/strmadmin@sm1.testdb01.newzonetech.com
Connected.

SQL>
declare
	v_scn number;
begin
	v_scn := dbms_flashback.get_system_change_number();
	dbms_apply_adm.set_schema_instantiation_scn@sm2.testdb02.newzontech.com(
	source_schema_name => 'davie',
	source_database_name => 'sm1.testdb01.newzonetech.com',
	instantiation_scn => v_scn,
	recursive => true);
end;
/ 
PL/SQL procedure successfully completed.

If you want reslove the conflict issue in target database, the conflict handlers must be set.

Start the capture and apply processes in target database

SQL> conn strmadmin/strmadmin@sm2.testdb02.newzonetech.com
Connected.

SQL>
begin
	dbms_apply_adm.set_parameter (
	apply_name => 'applys_dest',
	parameter => 'disable_on_error',
	value => 'N');
end;
/
PL/SQL procedure successfully completed.

SQL> exec dbms_apply_adm.start_apply (apply_name=> 'applys_dest'); 
PL/SQL procedure successfully completed.

Start the capture process in the target database

SQL>exec dbms_capture_adm.start_capture (capture_name=>'captures_dest'); 
PL/SQL procedure successfully completed.

Start the capture and apply processes in the main database(source database) – ‘sm1’

SQL> conn strmadmin/strmadmin@sm1.testdb01.newzonetech.com
Connected.

SQL>
begin
dbms_apply_adm.set_parameter (
apply_name => 'applys_src',
parameter => 'disable_on_error',
value => 'N');
end;
/
PL/SQL procedure successfully completed.

SQL> exec dbms_apply_adm.start_apply (apply_name=> 'applys_src');

PL/SQL procedure successfully completed.

-- Start the capture process
SQL> exec dbms_capture_adm.start_capture (capture_name=>'captures_src');

PL/SQL procedure successfully completed.
Advertisements

About daviewning

I am an Oracle DBA

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: