-- Create database link upfront. 


-- Create users on both sites
conn sys/phoenix@phoenix as sysdba
GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE 
  TO strm1 IDENTIFIED BY strm1;
alter user strm1 default tablespace users quota unlimited on users;

conn strm1/strm1@phoenix
drop table t1;
create table t1 as select object_id,object_name, object_type from all_objects nologging;
alter table t1 ADD constraint t1_pk primary key (object_id);

conn sys/venus@venus as sysdba
GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE 
  TO strm2 IDENTIFIED BY strm2;
alter user strm2 default tablespace users quota unlimited on users;

conn strm2/strm2@venus
create database link phoenix connect to strm1 identified by strm1 using 'phoenix';
create table t1 as select * from t1@phoenix nologging;
-- What about pk constraint? indexes?

conn sys/phoenix@phoenix as sysdba

-- Stream administrator account
GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE 
  TO strmadmin IDENTIFIED BY strmadmin;

alter user strmadmin default tablespace users quota unlimited on users;
GRANT EXECUTE ON DBMS_AQADM            TO strmadmin;
GRANT EXECUTE ON DBMS_CAPTURE_ADM      TO strmadmin;
GRANT EXECUTE ON DBMS_PROPAGATION_ADM  TO strmadmin;
GRANT EXECUTE ON DBMS_STREAMS_ADM      TO strmadmin;

BEGIN 
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege    => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, 
    grantee      => 'strmadmin', 
    grant_option => FALSE);
END;
/

BEGIN 
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege    => DBMS_RULE_ADM.CREATE_RULE_OBJ, 
    grantee      => 'strmadmin', 
    grant_option => FALSE);
END;
/

conn strmadmin/strmadmin@phoenix

EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

-- Note the difference in granted privileges
conn sys/venus@venus as sysdba

GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE 
  TO strmadmin IDENTIFIED BY strmadmin;
alter user strmadmin default tablespace users quota unlimited on users;

-- take note of this grant
grant all on strm2.t1 to strmadmin;

GRANT EXECUTE ON DBMS_APPLY_ADM        TO strmadmin;
GRANT EXECUTE ON DBMS_AQADM            TO strmadmin;
GRANT EXECUTE ON DBMS_STREAMS_ADM      TO strmadmin;

BEGIN 
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege    => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, 
    grantee      => 'strmadmin', 
    grant_option => FALSE);
END;
/

BEGIN 
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege    => DBMS_RULE_ADM.CREATE_RULE_OBJ, 
    grantee      => 'strmadmin', 
    grant_option => FALSE);
END;
/

conn strmadmin/strmadmin@venus
EXEC  DBMS_STREAMS_ADM.SET_UP_QUEUE();
-- Checking things out before going further...

-- set up LogMiner as data capturing site
conn sys/phoenix@phoenix as sysdba

CREATE TABLESPACE Logminer
    LOGGING 
    DATAFILE 'D:\ORACLE\ORADATA\phoenix\logminer.ora' SIZE 25M EXTENT 
    MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('LOGMINER');
-- What happens after this step?

-- Set up supplemenatl log group
conn strm1/strm1@phoenix
alter table t1 ADD SUPPLEMENTAL LOG GROUP log_group_t1_pk (object_id) always;

-- Set up propagation
-- Make sure the database link to the destination has been created in stream admin
conn strmadmin/strmadmin@phoenix
create database link venus connect to strmadmin identified by strmadmin using 'venus';
select name, QUEUE_TABLE from user_queues;

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
    table_name              => 'strm1.t1', 
    streams_name            => 'phoenix_to_venus', 
    source_queue_name       => 'strmadmin.streams_queue',
    destination_queue_name  => 'strmadmin.streams_queue@venus',
    include_dml             =>  true,
    include_ddl             =>  true,
    source_database         => 'phoenix');
END;
/
-- Check things again
-- Propagation folder in OEM shows phoenix_to_venus.

-- Configure capture process
-- This step takes a while
-- Make sure database is running in Archievelog mode
-- Does strmadmin needs privileges to strm1.t1?
-- Error on 2nd run, stream_name will not be removed after dropping user?
-- Remove from OEM, error on this step with DBMS_ proc error, reboot.
-- This step took 20-30 minutes on the 2nd run. Bugs?
BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name     => 'strm1.t1',   
    streams_type   => 'capture',
    streams_name   => 'capture_t1',
    queue_name     => 'strmadmin.streams_queue',
    include_dml    =>  true,
    include_ddl    =>  true);
END;
/
-- Check again

SELECT * FROM DBA_CAPTURE_PREPARED_TABLES;

-- Instantiation should be done AFTER the capture process has been created.
-- Make sure table exists on both sites before starting instantiation.
-- Instantiate table t1 at replicated site
-- exp userid=strm1/strm1@phoenix FILE=t1_instant.dmp TABLES=t1 
-- OBJECT_CONSISTENT=y ROWS=n

-- Drop supplemental log group
conn strm2/strm2@venus
SELECT * FROM DBA_APPLY_INSTANTIATED_OBJECTS;
alter table t1 drop SUPPLEMENTAL LOG GROUP log_group_t1_pk;
-- ORA-30568: cannot drop log group - nonexistent log group
-- Why?
-- Not working. set SCN manually, dumb ass

-- Set SCN manually
conn sys/phoenix@phoenix as sysdba
SET serveroutput ON
DECLARE
-- Declare variable to hold instantiation SCN
iscn NUMBER;
BEGIN
iscn:=DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_OUTPUT.PUT_LINE ('Instantiation SCN is: ' || iscn);
END;
/
-- Input SCN and schema
conn sys/venus@venus as sysdba
BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN (
source_object_name => 'strm2.t1',
source_database_name => 'phoenix',
instantiation_scn => &iscn);
END;
/
-- Check prepared table at source and instatiated table at destination.

-- Configure apply process
conn strmadmin/strmadmin@venus
BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name      => 'strm2.t1',
    streams_type    => 'apply', 
    streams_name    => 'apply_strm2_t1',
    queue_name      => 'strmadmin.streams_queue',
    include_dml     =>  true,
    include_ddl     =>  true,
    source_database => 'phoenix');
END;
/
BEGIN
  DBMS_APPLY_ADM.SET_PARAMETER(
    apply_name  => 'apply_strm2_t1', 
    parameter   => 'disable_on_error', 
    value       => 'n');
END;
/
-- From OEM at phoenix, distributed/AQs/queue tables/strmadmin/Streams_queue_table/streams_queue
-- subscriber strmadmin.streams_queue from venus is now shown
-- Check apply

-- Start apply process
BEGIN
  DBMS_APPLY_ADM.START_APPLY(
    apply_name  => 'apply_strm2_t1');
END;
/
-- Check apply again

-- Start capture process
conn strmadmin/strmadmin@phoenix
BEGIN
  DBMS_CAPTURE_ADM.START_CAPTURE(
    capture_name  => 'capture_t1');
END;
/
conn strm1/strm1@phoenix
update t1 set object_name='chnage name' where object_id=7559;
commit;
-- Final check
-- From OEM, elapsed LCR time changed