1 Reply Latest reply: Nov 3, 2011 5:10 AM by Rijesh RSS

    Advanced replication

    dkoracle
      Hi Team,

      Source (MDS) --> solaris 8 , oracle 8i
      Dest (MAster site) --> RHEL 5.7 , 10.2.0.5 db

      I have successfully completed replication between two sites under asynchronous mode of propagation

      I need your sincere help or someone' help from forum to explain me certain things which keeps me in state of blinking !!

      I can do DML operations from one site to other and vice-versa..Its really a nice experience to feel it

      1) When i execute a DML statement from local site that goes to local deferred queue then implicitly triggers are enabled which drives the transactions to propagate to remote site tables and vice versa happens..

      Now, since it operates in asynchronous mode i could see transactions are scheduled so i stays in deferred queue for sometime later pushed to other sites..Which table i should query to look up what DML is settled in queues ??

      2) I have given for every 1 min scheduled push should occur and every 1 min auto purge of successfully propagated entries from queue..Now there is a need for manual purge and push too..How do i override auto with manual operations ???
      Its very similar to redo and archive logs in database where auto swich and manual switch are possible..

      Please advice...

      Regards
      dkoracle
        • 1. Re: Advanced replication
          Rijesh
          Hello,

          The implicit triggers on the tables will help you to capture the transactions and enqueue it to the deferred queue. The push job (dbms_job) will actually push all these deferred transactions to the target or destination database.

          As far as I have seen (I am pretty much experienced with Oracle Advanced Replication and Streams) there is no need for you to schedule the purge job for every 1 minute interval. You can schedule it to run in every 30 mins or 1 hour and that should be sufficient. The push needs to be scheduled based on your business requirements for the data on the destination database.

          Answering your questions:

          1. The deferred transactions are in the system queue called SYSTEM.DEF$_AQCALL. You can check:

          SQL> select name,queue_table from dba_queues where owner='SYSTEM';

          NAME QUEUE_TABLE
          ------------------------------ ------------------------------
          DEF$_AQERROR DEF$_AQERROR
          AQ$_DEF$_AQERROR_E DEF$_AQERROR
          DEF$_AQCALL DEF$_AQCALL
          AQ$_DEF$_AQCALL_E DEF$_AQCALL

          The deferred transactions are basically stored in queue table SYSTEM.DEF$_AQCALL and accessed via the queue interface DEF$_AQCALL (same name for both). The errored transactions are kept in SYSTEM.DEF$_AQERROR. These are stored as individual calls in a transaction. Oracle has some procedures which allows you print these transactions and you can view the statements in them.

          2. Generally you dont need to do a manual push or purge, provided if your job executes fine. What generally happens is, if there is a network issue or any other kinds of issues, your job keeps failing. After the job has failed 16 times, it gets broken and then does not auto-execute further. In that case you will have to manually execute the job with following (this generally happens only to push, purge normally works fine):

          select log_user,job,what from dba_jobs where what like '%push%';
          select log_user,job,what from dba_jobs where what like '%purge%';
          connect <log_user>/<pwd>
          execute dbms_job.run(<job>);
          execute dbms_job.run(<job>);

          This will enable the job and will auto-execute next time.

          Hope this is helpful.

          Thanks,
          Rijesh