----------------------------------------------------
prompt
prompt 0)Setup the environment
prompt =======================
prompt    You must setup SQL*Plus to connect as SYSDBA
prompt    on the primary database. Setup the network
prompt    aliases and the listener to be able to connect
prompt    to all the databases from that SQL*Plus
prompt    session.
prompt
prompt    Also mind:
prompt      * The script set the source database in 
prompt        ARCHIVELOG mode and change the location
prompt        to /u01/app/oracle/oradata/(DBNAME)/archivelogs
prompt      * You need the 2 databases to have a tablespace 
prompt        USERS and a temporary tablespace TEMP
prompt      * A user DEMO will be created and dropped 
prompt        (cascade) make sure there is no problem with
prompt        that.
prompt
----------------------------------------------------

show parameter db_name

accept sourcegdb default 'BLACK' -
prompt "Enter the Source Database Global Name                [BLACK]: "

accept destgdb   default 'WHITE' -
prompt "Enter the Destination Database Global Name           [WHITE]: "

accept source_user default 'sys'  -
prompt "Enter the Destination SYSDBA user                      [sys]: "

accept source_pwd  default 'change_on_install'  -
prompt "Enter the Destination SYSDBA password    [change_on_install]: "

accept dest_user default 'sys'  -
prompt "Enter the Destination SYSDBA user                      [sys]: "

accept dest_pwd  default 'change_on_install'  -
prompt "Enter the Destination SYSDBA password    [change_on_install]: "

----------------------------------------------------
prompt
prompt 1)Build The Sample Schema and Table
prompt ===================================
----------------------------------------------------

connect &&source_user/&&source_pwd@&&sourcegdb as sysdba

create user demo
      identified by demo
      default tablespace users
      temporary tablespace temp;

grant connect,resource to demo;

connect &&dest_user/&&dest_pwd@&&destgdb as sysdba

create user demo
      identified by demo
      default tablespace users
      temporary tablespace temp;

grant connect,resource to demo;

prompt Type any key to continue...
pause

----------------------------------------------------
prompt
prompt 2) Configure Database Logging and the Multi-Version Data Dictionary
prompt  ==================================================================
----------------------------------------------------

connect / as sysdba

!mkdir -p /u01/app/oracle/oradata/&&sourcegdb/archivelogs

alter system set log_archive_dest_1='location=/u01/app/oracle/oradata/&&sourcegdb/archivelogs';

shutdown immediate;

startup mount

alter database archivelog;

alter database open;

alter database add supplemental log
     data (primary key, unique index) columns;

select SUPPLEMENTAL_LOG_DATA_MIN,
       SUPPLEMENTAL_LOG_DATA_PK,
       SUPPLEMENTAL_LOG_DATA_UI
  from gv$database;

var first_scn number;

set serveroutput on

DECLARE
   scn NUMBER;
BEGIN
   DBMS_CAPTURE_ADM.BUILD(
          first_scn => scn);
   DBMS_OUTPUT.PUT_LINE('First SCN Value = ' || scn);
   :first_scn := scn;
END;
/

exec dbms_capture_adm.prepare_schema_instantiation(-
    schema_name=>'demo');

col first_scn new_value first_scn
select :first_scn first_scn from dual;

prompt Type any key to continue...
pause

----------------------------------------------------
prompt 
prompt  3) Create the Streams Administrators
prompt  ====================================
----------------------------------------------------

connect &&source_user/&&source_pwd@&&sourcegdb as sysdba

CREATE TABLESPACE streams_tbs
      DATAFILE '/u01/app/oracle/oradata/&&sourcegdb/streams_tbs.dbf' size 25M
      AUTOEXTEND ON MAXSIZE 256M;

CREATE USER strmadmin IDENTIFIED BY strmadmin
      DEFAULT TABLESPACE streams_tbs
      QUOTA UNLIMITED ON streams_tbs;

grant dba to strmadmin;

BEGIN
   DBMS_STREAMS_ADM.SET_UP_QUEUE(
        queue_table => 'strmadmin.streams_queue_table',
        queue_name  => 'strmadmin.streams_queue');
END;
/

connect &&dest_user/&&dest_pwd@&&destgdb as sysdba

CREATE TABLESPACE streams_tbs
      DATAFILE '/u01/app/oracle/oradata/&&destgdb/streams_tbs.dbf' size 25M
      AUTOEXTEND ON MAXSIZE 256M;

CREATE USER strmadmin IDENTIFIED BY strmadmin
      DEFAULT TABLESPACE streams_tbs
      QUOTA UNLIMITED ON streams_tbs;

grant dba to strmadmin;

BEGIN
   DBMS_STREAMS_ADM.SET_UP_QUEUE(
        queue_table => 'strmadmin.streams_queue_table',
        queue_name  => 'strmadmin.streams_queue');
END;
/

prompt Type any key to continue...
pause

----------------------------------------------------
prompt
prompt 4) Create a Propagation
prompt =======================
----------------------------------------------------

connect strmadmin/strmadmin@&&sourcegdb

create database link &&destgdb
 connect to strmadmin
 identified by strmadmin
 using '&&destgdb';

select * from dual@&&destgdb;

BEGIN
  DBMS_PROPAGATION_ADM.CREATE_PROPAGATION(
    propagation_name   => 'streams_propagation',
    source_queue       => 'strmadmin.streams_queue',
    destination_queue  => 'strmadmin.streams_queue',
    destination_dblink => '&&destgdb',
    rule_set_name      => null,
    queue_to_queue     => TRUE);
END;
/

set lines 120
col q format a23
col destination format a33
col latency format 999
col message_delivery_mode format a15

select schema||'.'||qname q, 
       destination,
       latency,
       message_delivery_mode
  from dba_queue_schedules;

prompt Type any key to continue...
pause

----------------------------------------------------
prompt
prompt 5) Create a Capture
prompt ===================
----------------------------------------------------

connect strmadmin/strmadmin@&&sourcegdb

var first_scn number;
exec :first_scn:=&&first_scn

BEGIN
   DBMS_CAPTURE_ADM.CREATE_CAPTURE(
        queue_name         => 'strmadmin.streams_queue',
        capture_name       => 'streams_capture',
        rule_set_name      => NULL,
        source_database    => NULL,
        use_database_link  => false,
        first_scn          => :first_scn,
        logfile_assignment => 'implicit');
END;
/

col capture_name format a15
col queue_name format a13
col first_scn format 999999999999
col start_scn format 999999999999
col rule_set_name format a11

select capture_name,
       queue_name,
       first_scn,
       start_scn,
       rule_set_name
  from dba_capture;

BEGIN
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
        schema_name        => 'demo',
        streams_type       => 'capture',
        streams_name       => 'streams_capture',
        queue_name         => 'strmadmin.streams_queue',
        include_dml        => true,
        include_ddl        => true,
        include_tagged_lcr => false,
        source_database    => null,
        inclusion_rule     => true);
END;
/

select streams_name, 
       streams_type,
       schema_name,
       rule_type,
       rule_owner,
       rule_name
  from dba_streams_schema_rules;

prompt Type any key to continue...
pause

----------------------------------------------------
prompt
prompt 6) Create a Apply
prompt ===================
----------------------------------------------------

connect strmadmin/strmadmin@&&destgdb

BEGIN
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
       schema_name         => 'demo',
       streams_type       => 'apply',
       streams_name       => 'streams_apply',
       queue_name         => 'strmadmin.streams_queue',
       include_dml        => true,
       include_ddl        => true,
       include_tagged_lcr => false,
       source_database    => '&&sourcegdb',
       inclusion_rule     => true);
END;
/


col apply_name format a13
col queue_name format a13
col rule_set_name format a11

select apply_name,
       queue_name,
       rule_set_name,
       status,
       message_delivery_mode
  from dba_apply;

set lines 120
col rule_owner format a10
col streams_name format a16
col streams_type format a9
col schema_name format a15
col rule_type format a8
col rule_name format a15

select rule_owner,
       STREAMS_NAME,
       STREAMS_TYPE,
       SCHEMA_NAME,
       RULE_TYPE,
       RULE_NAME
  from DBA_STREAMS_SCHEMA_RULES;

prompt Type any key to continue...
pause

----------------------------------------------------
prompt
prompt 7) Create a Create the Instantiation SCN
prompt ========================================
----------------------------------------------------

connect &&source_user/&&source_pwd@&&sourcegdb as sysdba

col apply_scn format 999999999999 new_value instantiation_scn

select dbms_flashback.get_system_change_number apply_scn
 from dual;

connect &&dest_user/&&dest_pwd@&&destgdb as sysdba

begin
  dbms_apply_adm.set_schema_instantiation_scn(
          source_schema_name   => 'demo',
          source_database_name => '&&sourcegdb',
          instantiation_scn    => &&instantiation_scn,
          recursive   => false);
end;
/

col SOURCE_DATABASE format a6
col OBJECT format a10
col INSTANTIATION_SCN format 999999999999

select source_database,
       source_schema,
       instantiation_scn
  from dba_apply_instantiated_schemas;

prompt Type any key to continue...
pause

----------------------------------------------------
prompt
prompt 8) Start the various processes
prompt ==============================
----------------------------------------------------

connect &&source_user/&&source_pwd@&&sourcegdb as sysdba
exec dbms_capture_adm.start_capture('streams_capture')

connect &&dest_user/&&dest_pwd@&&destgdb as sysdba
exec dbms_apply_adm.start_apply('streams_apply')

prompt Type any key to continue...
pause

----------------------------------------------------
prompt 
prompt 9) Test the capture
prompt ===================
----------------------------------------------------

connect &&source_user/&&source_pwd@&&sourcegdb as sysdba
set pages 1000

declare
  c_ok number:=0;
begin
  while (c_ok<1) loop
    select count(*) into c_ok
      from v$streams_capture
     where capture_name='STREAMS_CAPTURE'
       and state='CAPTURING CHANGES';
     dbms_lock.sleep(1);
  end loop;
end;
/

select * from v$streams_capture;

connect demo/demo@&&sourcegdb

create table T1 (id number,
                 text varchar2(20),
                 constraint T1_PK primary key (id));

connect &&source_user/&&source_pwd@&&sourcegdb as sysdba

exec dbms_lock.sleep(10)

connect demo/demo@&&destgdb

desc T1

prompt You are done!

