Saturday, February 9, 2013

0 Oracle Stream (1): Set up the environment

Environment:

Source:

  • ORACLE_SID: oradb1
  • HOST: panda1.localdomain

 

Target:

  • ORACLE_SID: oradb2
  • HOST: panda2.localdomain

 

Parameter Prerequisite

  • GLOBAL_NAMES: set to True . Default is false
  • COMPATIBLE: 11.2.0.0.0
  • JOB_QUEUE_PROCESSES: Stream needs to set this large than 2
  • STREAMS_POOL_SIZE: Can be manage by ASMM or manually set . 
  • AQ_TM_PROCESSES: This parameter is for setting up the advance queue size.
show parameter GLOBAL_NAMES
show parameter COMPATIBLE
show parameter JOB_QUEUE_PROCESSES
show parameter STREAMS_POOL_SIZE
show parameter AQ_TM_PROCESSES

image

alter system set GLOBAL_NAMES=True scope=both;
alter system set AQ_TM_PROCESSES=4 scope=both;
show parameter GLOBAL_NAMES
show parameter AQ_TM_PROCESSES
image

Archive log mode

Please refer here for more detail.

archive log list
show parameter log_archive_start
show parameter db_recovery_file_dest
show parameter log_archive_dest_1

image

alter system set log_archive_start=true scope=spfile;
alter system set log_archive_dest_1='LOCATION=db_recovery_file_dest';

image

shutdown immediate
startup mount
alter database archivelog;
alter database open;
archive log list

image

Setup Stream table space and user

create tablespace streams_tbs datafile size 1024M;
create user strmadmin identified by XXXXX
default tablespace streams_tbs quota unlimited on streams_tbs;
grant dba to strmadmin;

image

TNSNAME.ora and confirm the connectivity

We need to make sure we can connect to both Oracle database nodes .

tnsname.ora on panda1.localdomain

ORADB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = panda1.localdomain)(PORT = 7001))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oradb1.panda1.localdomain)
    )
  )
 
 
ORADB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = panda2.localdomain)(PORT = 7001))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (ORACLE_SID = oradb2)
    )
  )
 
LISTENER_ORACLEDB1 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = panda1.localdomain)(PORT = 7001))

 

Confirm the connectivity via sqlplus

image

tnsname.ora on panda2.localdomain

ORADB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = panda2.localdomain)(PORT = 7001))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oradb2.panda2.localdomain)
    )
  )
 
ORADB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = panda1.localdomain)(PORT = 7001))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (ORACLE_SID = oradb1)
    )
  )
 
 
LISTENER_ORACLEDB2 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = panda2.localdomain)(PORT = 7001))

 

Create the database link

Create the database link on both host and test the connectivity .

 

create database link ORADB2.panda2.localdomain connect to strmadmin
identified by XXXXX using 'ORADB2';
 
select count(*) from HR.employees@ORADB2.panda2.localdomain;

 

image

create database link ORADB1.panda1.localdomain connect to strmadmin
identified by XXXXX using 'ORADB1';
select count(*) from HR.employees@ORADB1.panda1.localdomain;

image

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates