10 Replies Latest reply: Aug 19, 2011 12:28 PM by user299115 RSS

    log_archive_format & Goldengate

    user299115
      Hi,

      Note: Please apologize me for posting every thing in plain text. I could not find how to tag the text in this page.

      I am new to OGG and trying to get my hands dirty. Please help me to understand how to specify the ALTARCHIVEDLOGFORMAT when database is using FRA.

      Oracle 11.2.0.1 OGG 11.1

      My database is using FRA so oracle is not using the format specified in log_archive_format parameter. Where as, when i explicitly set TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT ARC%S_%R.%T (same as log_archive_format value) extract is abended with the below error message

      2011-08-19 15:02:40 ERROR OGG-00446 Could not find archived log for sequence 28 thread 1 under alternative destinations. SQL <SELECT MAX(sequence#) FROM v$log WHERE thread# = :ora_thread AND status in ('INVALIDATED', 'CURRENT', 'ACTIVE')>. Last alternative log tried F:\app\VT0046014\flash_recovery_area\ora11g\ARCHIVELOG\ARC00028_0755020128.001, error retrieving redo file name for sequence 28, archived = 1, use_alternate = 0Not able to establish initial position for sequence 28, rba 11790864.

      sample archivelog files ...

      O1_MF_1_29_74VZSXOH_.ARC
      O1_MF_1_30_74VZY20Z_.ARC
      O1_MF_1_31_74W801CQ_.ARC

      Please see my extract param file ...

      EXTRACT extsrc
      --CHECKPARAMS
      SETENV (ORACLE_HOME = "F:\app\VT0046014\product\11.2.0\dbhome_1")
      SETENV (ORACLE_SID = "ora11g")
      USERID gguser@ora11g, PASSWORD gguser
      EXTTRAIL F:\GoldenGate\GGS11\dirdat\lt
      TRANLOGOPTIONS ALTARCHIVELOGDEST PRIMARY F:\app\VT0046014\flash_recovery_area\ora11g\ARCHIVELOG
      TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT ARC%S_%R.%T
      TABLE scott.allobjects;
      TABLE Scott.allobjects1;


      Please see the output of showch command ...

      GGSCI (STLAP05376) 67> info extsrc showch

      EXTRACT EXTSRC Last Started 2011-08-19 14:59 Status ABENDED
      Checkpoint Lag 47:32:00 (updated 00:16:10 ago)
      Log Read Checkpoint Oracle Redo Logs
      2011-08-17 15:27:27 Seqno 28, RBA 11793336


      Current Checkpoint Detail:

      Read Checkpoint #1

      Oracle Redo Log

      Startup Checkpoint (starting position in the data source):
      Sequence #: 28
      RBA: 11790864
      Timestamp: 2011-08-17 15:27:27.000000
      Redo File: F:\APP\VT0046014\ORADATA\ORA11G\REDO01.LOG

      Recovery Checkpoint (position of oldest unprocessed transaction in the data source):
      Sequence #: 28
      RBA: 11790864
      Timestamp: 2011-08-17 15:27:27.000000
      Redo File: F:\APP\VT0046014\FLASH_RECOVERY_AREA\ORA11G\ARCHIVELOG\2011_08_19\O1_MF_1_28_74VRNO00_.ARC

      Current Checkpoint (position of last record read in the data source):
      Sequence #: 28
      RBA: 11793336
      Timestamp: 2011-08-17 15:27:27.000000
      Redo File: F:\APP\VT0046014\ORADATA\ORA11G\REDO01.LOG

      Write Checkpoint #1

      GGS Log Trail

      Current Checkpoint (current write position):
      Sequence #: 36
      RBA: 1051
      Timestamp: 2011-08-19 14:59:27.718000
      Extract Trail: F:\GoldenGate\GGS11\dirdat\lt

      Header:
      Version = 2
      Record Source = A
      Type = 4
      # Input Checkpoints = 1
      # Output Checkpoints = 1

      File Information:
      Block Size = 2048
      Max Blocks = 100
      Record Length = 2048
      Current Offset = 0

      Configuration:
      Data Source = 3
      Transaction Integrity = 1
      Task Type = 0

      Status:
      Start Time = 2011-08-19 14:59:27
      Last Update Time = 2011-08-19 14:59:27
      Stop Status = A
      Last Result = 0

      Please let me know if any one need any more information to look in.

      Regards,
      -Vijay
        • 1. Re: log_archive_format & Goldengate
          Annamalai A
          hi,

          Do you have ASM setup in your database configuration?
          • 2. Re: log_archive_format & Goldengate
            stevencallan
            If you note the format of the reported archived redo log, you can see how that appears to be an Oracle Managed File. You are trying to tell GoldenGate how the files are named, but they are not named that way.

            What value are you using for log_archive_format?
            http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/initparams124.htm#REFRN10089

            Creating archived logs using OMF:
            http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/omf003.htm#CACCJIDH

            From the GoldenGate Reference guide, ALTARCHIVEDLOGFORMAT:

            Specifies a string that overrides the archive log
            format of the source database. <string> accepts the same
            specifier as Oracle's parameter LOG_ARCHIVE_FORMAT.
            Extract uses the supplied format specifier to derive the log
            file name

            ALTARCHIVEDLOGFORMAT
            <string>
            [INSTANCE <instance_name>]
            [THREADID <id>]

            You'll see this more with RAC, which is probably not your case, nor is ASM.
            • 3. Re: log_archive_format & Goldengate
              user299115
              @ASP - No, I am not using ASM.

              Thanks.

              Regards,
              -Vijay
              • 4. Re: log_archive_format & Goldengate
                user299115
                Hi Steve,

                Thanks for your reply.

                You are right. I am telling GG to see for the archive logs which the same format given for log_archive_format parameter

                log_archive_format=ARC%S_%R.%T

                where as Oracle generating archive logs in OMF format because DB_RECOVERY_FILE_DEST is set. Is there any way to know what format OMF is using so that I can specify the same with ALTARCHIVELOGDEST? Please find my extract parameters once again.

                EXTRACT extsrc
                --CHECKPARAMS
                SETENV (ORACLE_HOME = "F:\app\VT0046014\product\11.2.0\dbhome_1")
                SETENV (ORACLE_SID = "ora11g")
                USERID gguser@ora11g, PASSWORD gguser
                EXTTRAIL F:\GoldenGate\GGS11\dirdat\lt
                TRANLOGOPTIONS ALTARCHIVELOGDEST PRIMARY F:\app\VT0046014\flash_recovery_area\ora11g\ARCHIVELOG
                TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT ARC%S_%R.%T -- this is same as log_archive_format parameter value
                TABLE scott.allobjects;
                TABLE Scott.allobjects1;

                Thanks.

                Regards,
                -Vijay
                • 5. Re: log_archive_format & Goldengate
                  user299115
                  FYI - please see the archive logs generating

                  O1_MF_1_28_74VRNO00_.ARC
                  O1_MF_1_29_74VZSXOH_.ARC
                  O1_MF_1_30_74VZY20Z_.ARC
                  O1_MF_1_31_74W801CQ_.ARC

                  I am not able to understand the last part of the naming convention ...

                  01_MF_%t_%s_*?????*.ARC

                  is that %a (activation ID)??

                  Thanks.

                  Regards,
                  -Vijay
                  • 6. Re: log_archive_format & Goldengate
                    stevencallan
                    I would just stay away/out of the FRA to begin with, especially with respect to even putting archived redo logs there in the first place. There is definitely a school of thought regarding that approach. See page 425 of Pro Oracle Database 11g Administration. Or, set a second location and use it for GoldenGate.

                    Overall, GoldenGate is going to query the database for the default/other location anyway, so why do you want to set an alternate location or format to begin with?

                    http://download.oracle.com/docs/cd/B10500_01/server.920/a96521/omf.htm#1013628
                    Datafile o1_mf_%t_%u_.dbf /u01/oradata/payroll/o1_mf_tbs1_2ixfh90q_.dbf

                    %t is the tablespace name. At most, eight characters of the tablespace name are used. If eight characters causes the name to be too long, then the tablespace name is truncated. Placing the tablespace name before the uniqueness string means that all the datafiles for a tablespace appear next to each other in an alphabetic file listing.
                    %u is an eight character string that guarantees uniqueness
                    %g is the online redo log file group number

                    Edited by: stevencallan on Aug 19, 2011 10:14 AM
                    • 7. Re: log_archive_format & Goldengate
                      user299115
                      Hi Steve,

                      I believe GG is counting on log_archive_format string when looking into archive logs when ever required. That's the reason it is not able to find the logs. I will create other destination for GG and test (in actual production it requires more SAN storage). However, I still want to dig deep to find how to handle OMF format without second location.

                      Regarding format, I think you have specified for data file which is not relevant here.

                      Thanks.

                      Regards,
                      -Vijay
                      • 8. Re: log_archive_format & Goldengate
                        stevencallan
                        %u is relevant
                        • 9. Re: log_archive_format & Goldengate
                          user299115
                          you are right. I have gone thru the link you have provided. I will test this as well and update.

                          Regards,
                          -Vijay
                          • 10. Re: log_archive_format & Goldengate
                            user299115
                            EXTRACT extsrc
                            --CHECKPARAMS
                            SETENV (ORACLE_HOME = "F:\app\VT0046014\product\11.2.0\dbhome_1")
                            SETENV (ORACLE_SID = "ora11g")
                            USERID gguser@ora11g, PASSWORD gguser
                            EXTTRAIL F:\GoldenGate\GGS11\dirdat\lt
                            TRANLOGOPTIONS ALTARCHIVELOGDEST PRIMARY F:\app\VT0046014\flash_recovery_area\ora11g\ARCHIVELOG
                            TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT *01_MF_%t_%s_%u_.ARC*
                            TABLE scott.allobjects;
                            TABLE Scott.allobjects1;

                            this format specification is giving an error because %r also mandatory in archive log format. I haven't seen %r any where in the archive log name.

                            +2011-08-19 22:41:44 ERROR OGG-00659 Unknown specifier in archive log format.+

                            +2011-08-19 22:41:44 ERROR OGG-01668 PROCESS ABENDING.+

                            I am yet to test my scenario by setting alternate archive log destination.

                            Thanks,
                            -Vijay