Test Streams data transmission in Oracle 11g Streams

Description:
The source database is the Oracle11gR2 database with two clusters of 64-bit HP-UNIX,
The target database is a 64-bit Linux Oracle11gR2 database,
Transfers the data of a user in the source database to the target database.

1. Both the source and target databases are in the archive mode.

Source:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination + ARCHDG
Oldest online log sequence 60
Next log sequence to archive 61
Current log sequence 61

Objectives:
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 54
Current log sequence 56

SQL> startup mount
ORACLE instance started.

Total System Global Area 6747725824 bytes
Fixed Size 2213976 bytes
Variable Size 4697622440 bytes
Database Buffers 2013265920 bytes
Redo Buffers 34623488 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

2. set parameters for the source and target Databases
Source:
SQL> show parameter global_names

NAME TYPE VALUE
-----------------------------------------------------------------------------
Global_names boolean FALSE
SQL>
SQL> alter system set global_names = true scope = both;

System altered.

SQL> show parameter aq_tm_processes

NAME TYPE VALUE
-----------------------------------------------------------------------------
Aq_tm_processes integer 0

SQL> alter system set aq_tm_processes = 2 scope = both;

System altered.

Objectives:
SQL> show parameter global_names

NAME TYPE VALUE
-----------------------------------------------------------------------------
Global_names boolean FALSE
SQL>
SQL> alter system set global_names = true scope = both;

System altered.

SQL> show parameter aq_tm_processes

NAME TYPE VALUE
-----------------------------------------------------------------------------
Aq_tm_processes integer 0

SQL> alter system set aq_tm_processes = 2 scope = both;

System altered.

3. Enable append logs in the source database
Enable secondary log
SQL> alter database add supplemental log data;

Database altered.

4. Create tablespaces, users, and authorizations in the source and target Databases
Source:
View the data file location:
Select file_name from dba_data_files
Create a tablespace:
Create tablespace streams_space
Datafile '+ DATADG/cboms/datafile/streams_space.dbf'
Size 5 M autoextend on
Create a user:
Create user strmadmin
Identified by strmadmin
Default tablespace streams_space
Authorize the dba role:
Grant dba to strmadmin
Grant Stream Management permissions:
Exec dbms_streams_auth.grant_admin_privilege ('strmadmin') ---- use sys or system to execute

Objectives:
View the data file location:
Select file_name from dba_data_files
Create a tablespace:
Create tablespace streams_space
Datafile '/u01/app/oracle/oradata/bhoms/streams_space.dbf'
Size 5 M autoextend on
Create a user:
Create user strmadmin
Identified by strmadmin
Default tablespace streams_space
Authorize the dba role:
Grant dba to strmadmin
Grant Stream Management permissions:
Exec dbms_streams_auth.grant_admin_privilege ('strmadmin') ---- use sys or system to execute

5. Configure tnsnames. ora for the source and target Databases
Source database Node 1:
CBDBS01-> cd $ ORACLE_HOME
CBDBS01-> pwd
/Oracle/db/product/11.2.0/db_1
CBDBS01-> cd network/admin
CBDBS01-> vi tnsnames. ora
Add at the end of the file:

BHOMS_192.168.2.2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.2.2) (PORT = 1521 ))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bhoms)
)
)

Test connection to the target database
CBDBS01-> SQL plus testUser/testPassword@BHOMS_192.168.2.2

Source database Node 2:
Source database Node 1:
CBDBS02-> cd $ ORACLE_HOME
CBDBS02-> pwd
/Oracle/db/product/11.2.0/db_1
CBDBS02-> cd network/admin
CBDBS02-> vi tnsnames. ora
Add at the end of the file:

BHOMS_192.168.2.2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.2.2) (PORT = 1521 ))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bhoms)
)
)

Test connection to the target database
CBDBS02> sqlplustestUser/testPassword@BHOMS_192.168.2.2

Target database:
[Www.bkjia.com @ bkjia ~] $ Cd $ ORACLE_HOME
[Www.bkjia.com @ bkjia dbhome_1] $ cd network
[Www.bkjia.com @ bkjia network] $ cd admin
[Www.bkjia.com @ bkjia admin] $ pwd
/U01/app/oracle/product/11.2.0/dbhome_1/network/admin

There is no tnsnames. ora file in this directory. Create a New tnsnames. ora file and add the following content:
[Www.bkjia.com @ bkjia admin] $ vi tnsnames. ora

CBOMS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.1) (PORT = 1568 ))
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.2) (PORT = 1568 ))
(LOAD_BALANCE = yes)
(FAILOVER = ON)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cboms)
(FAILOVER_MODE =
(TYPE = Select)
(METHOD = BASIC)
)
)
)

Test whether to connect to the source database.
[Www.bkjia.com @ bkjia admin] $ sqlplus testUser/testPassword @ CBOMS

6. db_link created from the source and target databases to the target database
Source:
SQL> conn strmadmin/strmadmin

SQL> create database link BHOMS connect to strmadmin identified by strmadmin using 'bhoms _ 192.168.2.2 ';

Test:
Select * fromglobal_name @ BHOMS

Objectives:
SQL> conn strmadmin/strmadmin

SQL> create database link CBOMS connect to strmadmin identified by strmadmin using 'cboms ';

Test:
Select * fromglobal_name @ CBOMS

1 2 Next Page