Completed the Configuring Linux for the Installation of Oracle Database 10g lesson

Completed the Installing the Oracle Database 10g on Linux lesson

Download the streams.zip into your working directory.

From a terminal window, execute the following command:

dbca

Click Next.

Make sure Create a Database is selected and click Next.

Select General Purpose and click Next.

Enter site1.us.oracle.com for the Global Database Name and site1 for the SID and click Next.

For Management Options, you want to select Use Database Control for Database Management and click Next.

Select Use Same Password for All Accounts and enter a password and confirm password and click Next.

Make sure File system is selected and click Next.

Click the Use Common Location for All Database Files and enter /u01/app/oracle/oradata in the Database Area field. Click Next.

Click Enable Archiving and click Next.

Select Sample Schemas and click Next.

Accept the defaults for Initialization Parameters and click Next.

Accept the defaults for Database Storage and click Next.

Make sure Create Database is checked and click Next.

At the Confirmation screen, click OK to start the database creation.

You will see the progress window appear.

You need to unlock the HR user. Click Password Management.

Scroll down to the HR user, click the Unlock Account? column and enter HR in both the new password and confirm password fields and click OK.

After the database has been created, click Exit.

You want to perform the same steps again for the site2.us.oracle.com database.

From a terminal window, execute the following command(s):

cd wkdir
sqlplus /nolog
@crtblsp

The crtblsp.sql script contains the following:

connect sys/<password>@SITE1 as sysdba
create tablespace obetbs datafile '<oradata_directory>/obetbs.dbf' size 20m;

From your SQL*Plus session, execute the following SQL command(s):

@crusers

The crusers.sql script contains the following:

REM
REM Create STRMADMIN and OBE users at SITE1.US.ORACLE.COM
REM

grant connect, resource, dba, select_catalog_role to strmadmin
   identified by <password>;
exec dbms_streams_auth.grant_admin_privilege('STRMADMIN',TRUE);

grant connect, resource to obe identified by <password>;
alter user OBE default tablespace OBETBS;

REM
REM Create STRMADMIN and OBE users at SITE2.US.ORACLE.COM
REM

connect sys/<password>@SITE2 as sysdba
grant connect, resource, dba, select_catalog_role to strmadmin 
   identified by <password>;
exec dbms_streams_auth.grant_admin_privilege('STRMADMIN',TRUE);

grant connect, resource to obe identified by <password>;

You need to first setup your queue. From your SQL*Plus session, execute the following SQL commands:

@site2_queue

The site2_queue.sql script contains the following:

connect STRMADMIN/<password>@SITE2
exec dbms_streams_adm.set_up_queue();

Now you need to create your database link to the site1 database. From your SQL*Plus session, execute the following SQL commands:

@site2_dblink

The site2_dblink.sql script contains the following:

connect STRMADMIN/<password>@SITE2
create database link site1 connect to strmadmin identified by <password> using 'site1';

The last step to setting up streams on the site2 database is to create the directories where the files will go. From your SQL*Plus session, execute the following SQL commands:

@site2_directory

The site2_directory.sql script contains the following:

connect STRMADMIN/<password>@SITE2

drop directory OBE_SITE1_FILES;
drop directory OBE_SITE2_FILES;

create directory OBE_SITE1_FILES as '<oradata_directory>/site1/';
create directory OBE_SITE2_FILES as '<oradata_directory>/site2/';

You need to first setup your queue. From your SQL*Plus session, execute the following SQL commands:

@site1_queue

The site1_queue.sql script contains the following:

connect STRMADMIN/<password>@SITE1
exec dbms_streams_adm.set_up_queue();

Now you need to create your database link to the SITE2 database. From your SQL*Plus session, execute the following SQL commands:

@site1_dblink

The site1_dblink.sql script contains the following:

connect STRMADMIN/<password>@SITE1
create database link site2 connect to strmadmin 
   identified by <password> using 'site2';

You also need to create the propagation on your SITE2 database. From your SQL*Plus session, execute the following SQL commands:

@site1_propagation

The site1_propagation.sql script contains the following:

connect STRMADMIN/<password>@SITE1
exec dbms_propagation_adm.create_propagation('STRMADMIN_SITE1_STRM',
   'STRMADMIN.STREAMS_QUEUE','STRMADMIN.STREAMS_QUEUE','SITE2',null);

Next you need to create the directories on the SITE1 database where the files will go. From your SQL*Plus session, execute the following SQL commands:

@site1_directory

The site1_directory.sql script contains the following:

connect STRMADMIN/<password>@SITE1
drop directory OBE_SITE1_FILES;
drop directory OBE_SITE2_FILES;
drop directory ORACLE_DBS_DIR ;

create directory OBE_SITE1_FILES as '<oradata_directory>/site1/';
create directory OBE_SITE2_FILES as '<oradata_directory>/site2/';
create directory ORACLE_DBS_DIR as '<oradata_directory>/';

The last step is to create a table in the tablespace and put some data in it for streams to transport. From your SQL*Plus session, execute the following SQL commands:

@loaddata

The loaddata.sql script contains the following:

connect sys/<password>@SITE1 as sysdba
grant all on hr.employees to obe;

connect obe/<password>@SITE1
create table employees as
  select * from hr.employees;

First, you want to view the contents of the database to see that there is data in the SITE1 database that is not in the SITE2 database. From your SQL*Plus session, execute the following script:

@chkdb

The chkdb.sql script contains the following:

connect obe/<password>@SITE1
select * from employees;

connect obe/<password>@SITE2
select * from employees;

Note that there is no employees table in the SITE2 database.

Now you want to run the streams process to transport the tablespace. From your SQL*Plus session, execute the following script:

connect strmadmin/<password>@SITE1
@maintain_ts

The maintain_ts.sql script contains the following:

BEGIN
   DBMS_STREAMS_ADM.MAINTAIN_SIMPLE_TABLESPACE(
        tablespace_name=>'OBETBS' ,
        source_directory_object=>'OBE_SITE1_FILES' ,
        destination_directory_object=>'OBE_SITE2_FILES' ,
        destination_database=>'SITE2.US.ORACLE.COM',
        setup_streams=>true,
        bi_directional=>true);
   END;
/

Once the script finishes you can rerun the script to check the database. From your SQL*Plus session, execute the following script:

@chkdb

The chkdb.sql script contains the following:

connect obe/<password>@SITE1
select * from employees;

connect obe/<password>@SITE2
select * from employees;

Note that you now have data in the SITE2 database.