1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Streaming error

Discussion in 'Security, Backup and Recovery' started by abu.asfar, Sep 9, 2009.

  1. abu.asfar

    abu.asfar Guest

    Hi All,
    I am new in the streaming part of oracle. I have two databases. I want to do online replication of the database. one is the main database and the other acts as backup. to do so i am using oracle first i tried for schema level streaming which failed now i am trying for table level streaming. to do so i follwed the following procedure:

    *************************

    ALTER SYSTEM SET JOB_QUEUE_PROCESSES=1;
    ALTER SYSTEM SET AQ_TM_PROCESSES=1;
    ALTER SYSTEM SET GLOBAL_NAMES=TRUE;
    ALTER SYSTEM SET COMPATIBLE='10.2.0' SCOPE=SPFILE;
    ALTER SYSTEM SET LOG_PARALLELISM=1 SCOPE=SPFILE;
    SHUTDOWN IMMEDIATE;
    STARTUP;

    CONN sys/password@DBA1 AS SYSDBA

    CREATE USER strmadmin IDENTIFIED BY strmadminpw
    DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;

    GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE TO strmadmin;

    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;
    GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin;
    GRANT EXECUTE ON DBMS_FLASHBACK 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;
    /

    CONNECT strmadmin/strmadminpw@DBA1

    EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

    CREATE DATABASE LINK dba2 CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING 'DBA2';

    GRANT ALL ON scott.dept TO strmadmin;


    CONN sys/password@DBA1 AS SYSDBA

    CREATE TABLESPACE logmnr_ts DATAFILE '/u01/app/oracle/oradata/DBA1/logmnr01.dbf'
    SIZE 25 M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

    EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logmnr_ts');

    CONN sys/password@DBA1 AS SYSDBA

    ALTER TABLE scott.dept ADD SUPPLEMENTAL LOG GROUP log_group_dept_pk (deptno) ALWAYS;

    CONNECT strmadmin/strmadminpw@DBA1

    BEGIN
    DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
    table_name => 'scott.dept',
    streams_name => 'dba1_to_dba2',
    source_queue_name => 'strmadmin.streams_queue',
    destination_queue_name => 'strmadmin.streams_queue@dba2',
    include_dml => true,
    include_ddl => true,
    source_database => 'dba1');
    END;
    /

    SELECT job,
    TO_CHAR(last_date, 'DD-Mon-YYYY HH24:MI:SS') last_date,
    TO_CHAR(next_date, 'DD-Mon-YYYY HH24:MI:SS') next_date,
    what
    FROM dba_jobs;

    CONNECT strmadmin/strmadminpw@DBA1

    BEGIN
    DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name => 'scott.dept',
    streams_type => 'capture',
    streams_name => 'capture_simp',
    queue_name => 'strmadmin.streams_queue',
    include_dml => true,
    include_ddl => true);
    END;
    /

    exp userid=scott/tiger@dba1 FILE=dept_instant.dmp TABLES=dept OBJECT_CONSISTENT=y ROWS=n

    imp userid=scott/tiger@dba2 FILE=dept_instant.dmp IGNORE=y COMMIT=y LOG=import.log STREAMS_INSTANTIATION=y

    CONN sys/password@DBA2 AS SYSDBA

    ALTER TABLE scott.dept DROP SUPPLEMENTAL LOG GROUP log_group_dept_pk;

    CONNECT strmadmin/strmadminpw@dba1

    DECLARE
    v_scn NUMBER;
    BEGIN
    v_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
    DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@DBA2(
    source_object_name => 'scott.dept',
    source_database_name => 'dba1',
    instantiation_scn => v_scn);
    END;
    /

    CONNECT strmadmin/strmadminpw@DBA2
    BEGIN
    DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name => 'scott.dept',
    streams_type => 'apply',
    streams_name => 'apply_simp',
    queue_name => 'strmadmin.streams_queue',
    include_dml => true,
    include_ddl => true,
    source_database => 'dba1');
    END;
    /

    CONNECT strmadmin/strmadminpw@DBA2
    BEGIN
    DBMS_APPLY_ADM.SET_PARAMETER(
    apply_name => 'apply_simp',
    parameter => 'disable_on_error',
    value => 'n');

    DBMS_APPLY_ADM.START_APPLY(
    apply_name => 'apply_simp');
    END;
    /

    CONNECT strmadmin/strmadminpw@DBA1
    BEGIN
    DBMS_CAPTURE_ADM.START_CAPTURE(
    capture_name => 'capture_simp');
    END;
    /


    *************************
    i have oracle 10g (10.2.0) as main server and oracle 10g (10.1.0) as backup server. at the following point i got the errors.
    1. whenever i tries to change the compatiblity of the database first it get chagned and then it starts giving error. for this i have to change the spfile.
    2. the same is happenignwhen i am changing log_parrallelism.
    3. whenever i am running the procedure DBMS_STREAMS_ADM.ADD_TABLE_RULES it is giving the erro ORA-00258. when i go to arcieve mode of database it runs.
    4. After doing all these stuffs still it is not changing at the backup level.

    Help me.................
     
  2. UnixGuru

    UnixGuru Active Member

    Messages:
    28
    Likes Received:
    1
    Trophy Points:
    90
    HI merely saying that it's giving me errors won't help. What specific error are you getting doing exactly what?
     
  3. COREY

    COREY Guest

    thank you for the help
    can i ask any other q related to this one?
     
  4. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Sure, just post a new thread.