5 Replies Latest reply on Mar 22, 2012 12:59 PM by CKPT

    Question on redo log files at the standby

    John_75
      Oracle version: 10.2.0.5
      Platform : AIX

      We have 2 node RAC primary with 2 node RAC standby
      Primary Instance1 named as cmapcp1
      Primary Instance2 named as cmapcp2
      
      
      Standby Instance1 named as cmapcp3
      Standby Instance2 named as cmapcp4
      At standby side
      SQL> show parameter log_file_name_convert
      
      NAME                 TYPE                 VALUE
      -------------------- -------------------- ------------------------------
      log_file_name_conver string               cmapcp1, cmapcp3, cmapcp2, cmapcp4
       
      Despite the value set for log_file_name_convert, I don't see any change in names of Online and Standby redo logs at the Standby site.


      -- From primary
      SQL> select member,type from v$logfile;
      
      MEMBER                                             TYPE
      -------------------------------------------------- -------
      +CMAPCP_DATA01/cmapcp/cmapcp_log01.dbf             ONLINE
      +CMAPCP_DATA01/cmapcp/cmapcp_log02.dbf             ONLINE
      +CMAPCP_DATA01/cmapcp/cmapcp_log03.dbf             ONLINE
      +CMAPCP_DATA01/cmapcp/cmapcp_log04.dbf             ONLINE
      +CMAPCP_DATA01/cmapcp/cmapcp_log05.dbf             ONLINE
      +CMAPCP_DATA01/cmapcp/cmapcp_log06.dbf             ONLINE
      +CMAPCP_DATA01/cmapcp/cmapcp_log11.dbf             STANDBY
      +CMAPCP_DATA01/cmapcp/cmapcp_log12.dbf             STANDBY
      +CMAPCP_DATA01/cmapcp/cmapcp_log13.dbf             STANDBY
      +CMAPCP_DATA01/cmapcp/cmapcp_log14.dbf             STANDBY
      +CMAPCP_DATA01/cmapcp/cmapcp_log15.dbf             STANDBY
      +CMAPCP_DATA01/cmapcp/cmapcp_log16.dbf             STANDBY
      +CMAPCP_DATA01/cmapcp/cmapcp_log17.dbf             STANDBY
      +CMAPCP_DATA01/cmapcp/cmapcp_log18.dbf             STANDBY
      +CMAPCP_DATA01/cmapcp/cmapcp_log19.dbf             STANDBY
      +CMAPCP_DATA01/cmapcp/cmapcp_log20.dbf             STANDBY
      
      16 rows selected.
      -- From standby
      SQL> select member,type from v$logfile;
      
      MEMBER                                             TYPE
      -------------------------------------------------- -------
      +CMAPCP_DATA01/cmapcp/cmapcp_log01.dbf             ONLINE
      +CMAPCP_DATA01/cmapcp/cmapcp_log02.dbf             ONLINE
      +CMAPCP_DATA01/cmapcp/cmapcp_log03.dbf             ONLINE
      +CMAPCP_DATA01/cmapcp/cmapcp_log04.dbf             ONLINE
      +CMAPCP_DATA01/cmapcp/cmapcp_log05.dbf             ONLINE
      +CMAPCP_DATA01/cmapcp/cmapcp_log06.dbf             ONLINE
      +CMAPCP_DATA01/cmapcp/cmapcp_log11.dbf             STANDBY
      +CMAPCP_DATA01/cmapcp/cmapcp_log12.dbf             STANDBY
      +CMAPCP_DATA01/cmapcp/cmapcp_log13.dbf             STANDBY
      +CMAPCP_DATA01/cmapcp/cmapcp_log14.dbf             STANDBY
      +CMAPCP_DATA01/cmapcp/cmapcp_log15.dbf             STANDBY
      +CMAPCP_DATA01/cmapcp/cmapcp_log16.dbf             STANDBY
      +CMAPCP_DATA01/cmapcp/cmapcp_log17.dbf             STANDBY
      +CMAPCP_DATA01/cmapcp/cmapcp_log18.dbf             STANDBY
      +CMAPCP_DATA01/cmapcp/cmapcp_log19.dbf             STANDBY
      +CMAPCP_DATA01/cmapcp/cmapcp_log20.dbf             STANDBY
      
      16 rows selected.
      --- Another thing I noticed, v$log doesn't list Standby Redo Logs. This is expected behaviour , I guess
      Below is the output from Primary and Standby (it is the same)
      set linesize 200
      set pagesize 50
      col member for a50
      break on INST SKIP PAGE on GROUP# SKIP 1
      
      select l.thread# inst, l.group#,lf.member, lf.type
          from v$log l , v$logfile lf
          where l.group# = lf.group#
          order by 1,2 ;
      
            INST     GROUP# MEMBER                                             TYPE
      ---------- ---------- -------------------------------------------------- -------
               1          1 +CMAPCP_DATA01/cmapcp/cmapcp_log01.dbf             ONLINE
      
                          2 +CMAPCP_DATA01/cmapcp/cmapcp_log02.dbf             ONLINE
      
                          3 +CMAPCP_DATA01/cmapcp/cmapcp_log03.dbf             ONLINE
      
      
            INST     GROUP# MEMBER                                             TYPE
      ---------- ---------- -------------------------------------------------- -------
               2          4 +CMAPCP_DATA01/cmapcp/cmapcp_log04.dbf             ONLINE
      
                          5 +CMAPCP_DATA01/cmapcp/cmapcp_log05.dbf             ONLINE
      
                          6 +CMAPCP_DATA01/cmapcp/cmapcp_log06.dbf             ONLINE
        • 1. Re: Question on redo log files at the standby
          CKPT
          Type of the redo log files you can see only from v$logfile
          SQL> show parameter log_file_name_convert
           
          NAME                 TYPE                 VALUE
          -------------------- -------------------- ------------------------------
          log_file_name_conver string               cmapcp1, cmapcp3, cmapcp2, cmapcp4
          What is the value you mentioned here in log_file_name_convert, It should be locations. Check syntax http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams112.htm

          your LOG_FILE_NAME_CONVERT is wrong, & Have you checked in V$STANDBY_LOG ?
          1 person found this helpful
          • 2. Re: Question on redo log files at the standby
            mseberg
            John

            Think of the Primary and Standby as one database in two roles.

            In Primary role the REDO is used and the Standby REDO is not used.

            In Standby role the REDO is not used and the Standby REDO is used.

            Yes v$log does not list SRL.

            v$standby_log does.

            I generally keep a pfile version of the spfile around so I can always check parameters super quick.


            Best Regards

            mseberg
            1 person found this helpful
            • 3. Re: Question on redo log files at the standby
              John_75
              Thank you ckpt, mseberg.

              I think log_file_name_convert is set wrongly as you've mentioned. But If I don't want to any change to name of Online or standby redo log files in standby, I don't have to set log_file_name_convert at all. Right ?
              • 4. Re: Question on redo log files at the standby
                mseberg
                Its more about location

                Same folder structure then you don't need it.

                LOG_FILE_NAME_CONVERT converts the filename of a new log file on the primary database to the filename of a log file on the Standby database.

                http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/initparams126.htm#REFRN10098


                Best Regards

                mseberg
                • 5. Re: Question on redo log files at the standby
                  CKPT
                  John_75 wrote:
                  Thank you ckpt, mseberg.

                  I think log_file_name_convert is set wrongly as you've mentioned. But If I don't want to any change to name of Online or standby redo log files in standby, I don't have to set log_file_name_convert at all. Right ?
                  From Same link

                  If you specify an odd number of strings (the last string has no corresponding replacement string), an error is signalled during startup. If the filename being converted matches more than one pattern in the pattern/replace string list, the first matched pattern takes effect. There is no limit on the number of pairs that you can specify in this parameter (other than the hard limit of the maximum length of multivalue parameters).