4 Replies Latest reply on Aug 11, 2020 4:36 AM by ORASCN

    replicat time since checkpoint increasing

    Nazeem8051

      Hello, I have a replicat whose time since chkp is increasing. As I know, this happens when there is a huge transaction running on the source db. Here is what the replicat's status shows -

       

      GGSCI (xxx.pldc.kp.org) 5> send RDDHDS01, status

      Sending STATUS request to REPLICAT RDDHDS01 ...

        Current status: Processing data

        Sequence #: 54

        RBA: 468,956,146

        1,388,400 records in current transaction.

      STOP request pending end-of-transaction (1,388,400 records so far).

       

      Now when I check the source's extract looks I do not see any active queries, but there is a session which has been running since 7/21 and it does not seem to be doing anything but the extract seems to thinking that the transaction is still active. Here is the extract output -

       

      GGSCI (somehost) 3> info EDDHDS01, showch

      EXTRACT    EDDHDS01  Last Started 2020-08-06 18:36   Status RUNNING

      Checkpoint Lag       00:00:00 (updated 00:00:05 ago)

      Process ID           176059

      Log Read Checkpoint  Oracle Integrated Redo Logs

                           2020-08-08 11:51:33

                           SCN 2861.2159606154 (12290061040010)

      Current Checkpoint Detail:

      Read Checkpoint #1

        Oracle Integrated Redo Log

        Startup Checkpoint (starting position in the data source):

          Timestamp: 2020-07-14 03:34:00.000000

          SCN: 0.0 (0)

        Recovery Checkpoint (position of oldest unprocessed transaction in the data source):

          Timestamp: 2020-07-21 05:42:06.000000

          SCN: 2861.159632680 (12288061066536)

        Current Checkpoint (position of last record read in the data source):

          Timestamp: 2020-08-08 11:51:33.000000

          SCN: 2861.2159606154 (12290061040010)

        BR Previous Recovery Checkpoint:

          Timestamp: 2020-08-06 15:34:54.000000

          SCN: 2861.1863450803 (12289764884659)

        BR Begin Recovery Checkpoint:

          Timestamp: 2020-08-08 10:39:56.000000

          SCN: 2861.2157664399 (12290059098255)

        BR End Recovery Checkpoint:

          Timestamp: 2020-08-08 10:40:17.000000

          SCN: 2861.2157676572 (12290059110428)

      Write Checkpoint #1

        GGS Log Trail

        Current Checkpoint (current write position):

          Sequence #: 92

          RBA: 418192441

          Timestamp: 2020-08-08 11:51:37.726896

          Extract Trail: /somelocation..../EX

          Seqno Length: 9

          Flip Seqno Length: No

          Trail Type: EXTTRAIL

      Header:

        Version = 2

        Record Source = A

        Type = 18

        # Input Checkpoints = 1

        # Output Checkpoints = 1

      Configuration:

        Data Source = 3

        Transaction Integrity = 1

        Task Type = 0

      Status:

        Start Time = 2020-08-06 18:36:39

        Last Update Time = 2020-08-08 11:51:37

        Stop Status = A

        Last Result = 400

       

      But when I check in the db, there is this active session, trying to kill it with kill session and disconnect session are not helping i.e. it says session marked for kill but the session remains active (its been couple of days it was killed)...the rollback by now should have completed. I do not see the OS process as well.

       

      How do I get rid of this orphan session and remove "time since chkp" for the replicat? Please advice.

        • 1. Re: replicat time since checkpoint increasing
          ORASCN

          Hi ,

           

          GGSCI (xxx.pldc.kp.org) 5> send RDDHDS01, status

          Sending STATUS request to REPLICAT RDDHDS01 ...

            Current status: Processing data

            Sequence #: 54  --------------------------------------------------------> The status show the seq# as 54

            RBA: 468,956,146

            1,388,400 records in current transaction.

          STOP request pending end-of-transaction (1,388,400 records so far).

           

          The replicat process is processing a long running transaction. Follow the below steps,

           

          INFO RDDHDS01

           

          Note down the read checkpoint,

           

          ls -lrt <replicat read checkpoint>

           

          Check how many trail file is pending for the replicat process to read.

           

          Regards,

          Veera

           

          Regards,

          Veera

          • 2. Re: replicat time since checkpoint increasing
            user13010474

            Hi Veera, there are 50 more trail files to be processed. Just to clarify, even though replicat is processing an old long transaction and there have been transactions in the trail files which completed after this long running transaction, what does "time since chkp" really implies in this case?

             

            Can you please advice on how to get rid of the orphan session on the source system?

            • 3. Re: replicat time since checkpoint increasing
              3505860

              Hi,

               

              On the source system you can run the below from ggsci to identify the transactions that the extract is currently keeping a track of.

              SEND EXTRCAT <extract_name> SHOWTRANS

              This will list the transactions on source DB

              You can use SEND EXTRACT <extract_name> SKIPTRANS parameter if you want to skip any particular trtansaction from this list.

              https://docs.oracle.com/goldengate/1212/gg-winux/GWURF/ggsci_commands014.htm#GWURF163

              • 4. Re: replicat time since checkpoint increasing
                ORASCN

                Hi,

                 

                Time since Checkpoint will get updated only when the process sees a COMMIT. Until that the time since checkpoint will be increasing. Your replicat process is processing / reading a long running transaction and still it didn't see any commit. Once it sees a commit, automatically the Time Since Checkpoint will be reset to 0. This is expected.

                 

                Please use the below command to check the ongoing transactions in the Source system

                 

                send extract <ext_name> showch

                 

                This will list the open transactions. Using the details, check what those transactions are in the database. If the transaction is INVALID you can go ahead and kill those. Also, you  can check with your application team to check on those transactions.

                 

                Regards,

                Veera