14 Replies Latest reply: Jul 21, 2014 11:50 PM by 2713139 RSS

    Archive log format change while copying from production to standby database.

    2713139

      Hi everyone,

       

      i am facing a issue recently, while copying archives from production to standby , i observed that archive log format is changes, while recovery was stopped because archive format has been changed,

       

      both production and standby has same version

      db version: 11.2.0.3.0

       

       

      SQL> show parameter archive_format;

       

       

      NAME                                 TYPE        VALUE

      ------------------------------------ ----------- ------------------------------

      log_archive_format                   string     %t_%s_%r.dbf

       

       

      my archive log format in production & standby  is o1_mf_1_8207_9w7w3k9f_.arc

       

      we copy the archives from production to standby every half an hour  by using shell script

       

      command is rsync -e ssh -Pazv /u01/app/oracle/fast_recovery_area/CDMSP1/archivelog/ oracle@145.12.2.51:/u01/app/oracle/fast_recovery_area/CDMSP1/archivelog/  >> /home/oracle/archivecopy_log/archivecopy_`date '+%Y-%m-%d'`.log

       

      i have checked in standby archivelog location it is showing the same format as there in production

      everything working fine, but while performing recovery in standby it throws error

       

      recover standby database

      (auto)

       

      ORA-00279: change 63336236 generated at 07/14/2014 20:00:01 needed for thread 1

      ORA-00289: suggestion :

      /u01/app/oracle/fast_recovery_area/OCPROD/archivelog/2014_07_14/.o1_mf_1_8207_9w

      7w3k9f_.arc.BKCpHU

      ORA-00280: change 63336236 for thread 1 is in sequence #8207

      ORA-00278: log file

      '/u01/app/oracle/fast_recovery_area/OCPROD/archivelog/2014_07_14/o1_mf_1_8206_9w

      7tc9s4_.arc' no longer needed for this recovery

       

       

      ORA-00283: recovery session canceled due to errors

      ORA-00333: redo log read error block 2048 count 2048

      ORA-01112: media recovery not started

       

       

      as you can see that .arc after it is getting extension of BKCpHU and also a dot before

      .o1_mf_1_8207_9w7w3k9f_.arc.BKCpHU

       

       

       

      because of that extension recovery stopped it, then i applied the archive manually, it works fine. but how to stop this error

       

       

      can you please reply as soon as possible,

       

       

      thanks in advance

       

       

      Regards,

      sanjay

        • 1. Re: Archive log format change while copying from production to standby database.
          Nip-Oracle

          Here, we are messing up with OMF file names.  LOG_ARCHIVE_FORMAT has nothing to do here.

           

          After copying the files manually, catalog using RMAN  at standby:

           

           

          $ rman target /

           

          RMAN> catalog  start with '/home/oracle/archivecopy_log/' ;

           

          Then RECOVER command will be able to detect the correct file names.

          • 2. Re: Archive log format change while copying from production to standby database.
            2713139

            HI NiP-Oracle ,

             

            thanks for reply,  we r not using rman at standby , we use custom script for recovery, while copying from production to standby archive log extension changing  it is like hidden format, after 15 to 20min it dissapear and luks like same as production archive format, my question is why it is coming hidden format, and later on it change to normal format,

             

             

             

            regards,

            sanjay

            • 3. Re: Archive log format change while copying from production to standby database.
              Hemant K Chitale

              copy commands (like rsync) would "change" the file name while the copy is in progress. The leading . (dot) makes it a "hidden" file.  The additional BKCpHU is likely its way of making a unique file name.  It should revert the file to the correct name after the copy is completed.  Either you applied the archivelog while the copy was in progress (i.e. rsync hadn't yet renamed the file) OR the copy itself had terminated.

               

              Hemant K Chitale


              • 4. Re: Archive log format change while copying from production to standby database.
                2713139

                Hi Hemanth,

                 

                Excellent, that is what happening , when i copy normal files also, same dot and extension creating,  but it interrupt my recovery process in the night time,

                 

                we r copying archives prod to standby every half an hour , night 10.15 we run recovery script. so this archive extension creates problem. recovery terminated in the middle. you understand the scenario well. please suggest me any alternative, thanks in advance.

                 

                 

                regards,

                sanjay.

                • 5. Re: Archive log format change while copying from production to standby database.
                  Hemant K Chitale

                  You have to synchronise the copy and the recovery.  When I had this scripted, I had a "lock" file that prevented both from running at the same time.  If a copy is running, it creates a lock file.  The recover does not proceed when it sees the lock file.  Once the copy is completed, it removes the lock file.  The recover, if it starts in the absence of a lock file, creates a lock file (which prevents the copy from running).

                   

                  Hemant K Chitale


                  • 6. Re: Archive log format change while copying from production to standby database.
                    2713139

                    Hi Hemanth,

                     

                    Thanks for your quick reply

                     

                    i got your point but could please let me know how to do that lock file,  iam sharing with you rsync command how we copy from prod to standby and also recovery script which is run in stnadby, please check and guide us to do any modification , it would be greatful

                     

                    rsync script:

                    echo "DATE and TIME=`date '+%Y-%m-%d,%H:%M:%S'`" >> /home/oracle/archivecopy_log/archivecopy_`date '+%Y-%m-%d'`.log

                    rsync -e ssh -Pazv /u01/app/oracle/fast_recovery_area/prod/archivelog/ oracle@145.12.2.51:/u01/app/oracle/fast_recovery_area/PROD/archivelog/ >> /home/oracle/archivecopy_log/archivecopy_`date '+%Y-%m-%d'`.log

                    ping -c 145.12.2.51 >> /home/oracle/archivecopy_log/archivecopy_`date '+%Y-%m-%d'`.log

                     

                     

                    recovery script:

                    col fn new_value filename;

                    select '/home/oracle/applied_archive_log/'||to_char(sysdate, 'DD-MON-YYYY-HH24miss')||'.log' as fn from dual;

                    spool &filename

                    select name as DB_Name from v$database;

                    recover standby database;

                    auto

                    spool off

                    disconn

                     

                     

                    thanks again for your time and patience,

                     

                     

                    regards,

                    sanjay.

                    • 7. Re: Archive log format change while copying from production to standby database.
                      Hemant K Chitale

                      This is an outline.

                       

                      if [ -f lockfile] then

                      echo "lockfile exists.  Presume another rsync or a recover is running"

                      exit

                      else

                      touch lockfile

                      ....proceed with rsync

                      rm lockfile

                      fi

                       

                      and the same with the scripted RECOVER command.

                       

                      Hemant K Chitale

                       


                      • 8. Re: Archive log format change while copying from production to standby database.
                        2713139

                        hi Hemanth ,

                         

                         

                        thanks for the script,

                         

                        what is the use of this script, i understood the meaning of the whole script but why we creating lock file and deleting the lock file.??

                         

                        2. suppose i copied archive logs from prod to standby  9.30 pm.   if i execute the recovery script at 9.40 p.m then what happens do i get recover till 9.30 pm or 9 p.m

                         

                        3.if i am not wrong  i copied the archives at 8.30 p.m while copy  ite hidden file is created, it is exist till 9.30 p.m  on that time how can i resolve the issue,?


                        4.  where can i execute this lock file procedure i mean is it in production or standby??

                         

                        can i execute like this??

                         

                        f [ -f lockfile] then

                        echo "lockfile exists.  Presume another rsync or a recover is running"

                        exit

                        else

                        touch lockfile

                        echo "DATE and TIME=`date '+%Y-%m-%d,%H:%M:%S'`" >> /home/oracle/archivecopy_log/archivecopy_`date '+%Y-%m-%d'`.log

                        rsync -e ssh -Pazv /u01/app/oracle/fast_recovery_area/prod/archivelog/ oracle@145.12.2.51:/u01/app/oracle/fast_recovery_area/PROD/archivelog/ >> /home/oracle/archivecopy_log/archivecopy_`date '+%Y-%m-%d'`.log

                        ping -c 145.12.2.51 >> /home/oracle/archivecopy_log/archivecopy_`date '+%Y-%m-%d'`.log

                        rm lockfile

                        fi

                         

                        waiting for your reply, thnaks again,

                         

                         

                        regards,

                        sanjay

                         

                         

                        • 9. Re: Archive log format change while copying from production to standby database.
                          Hemant K Chitale

                          Quite obviously, the lockfile must exist at both sites !

                          At the primary to prevent two rsyncs from running together.

                          At the standby to prevent rysnc running with recover (and vice versa) and two recovers running together.

                           

                          >suppose i copied archive logs from prod to standby  9.30 pm.   if i execute the recovery script at 9.40 p.m then what happens do i get recover till 9.30 pm or 9 p.m

                          It depends on how YOU script the recover.  You could chose to recover to 9:30. You could choose to recover to 9:00


                          >if i am not wrong  i copied the archives at 8.30 p.m while copy  ite hidden file is created, it is exist till 9.30 p.m  on that time how can i resolve the issue,?

                          if the hidden file still exists, it would mean that your  rsync has failed or is incomplete.  Properly scripted, the lockfile would prevent a recover from attempting to use the incomplete file.

                           

                          I only provided an outline.  Scripting the lockfile at both sites is for you to do.

                           

                          Think of how Oracle prevents two sessions from attempting to update the same row concurrently.  It uses locking.

                          Surely, you need to ensure that two rsyncs of the same file don't run concurrently or that a recover doesn't attempt to use a file that is incomplete !!

                           

                          Hemant K Chitale

                          • 10. Re: Archive log format change while copying from production to standby database.
                            2713139

                            Dear Hemant,

                             

                            sorry for the late reply,

                             

                             

                            i will test this in my environment and will get back to you by evening , 

                             

                             

                            finally last question,

                             

                             

                            i have rsync script in production i will add those lines in that  script,  i assuming that  it will lock until the copy has finished to standby,   recovery wiull happen only the atual files, not the hidden files..  am i right??

                             

                             

                            thanking you

                             

                             

                            regards,

                            sanjay

                            • 11. Re: Archive log format change while copying from production to standby database.
                              Hemant K Chitale

                              Can a RECOVER DATABASE see any archivelogs that don't have the expected format name ?

                               

                              Hemant K Chitale


                              • 12. Re: Archive log format change while copying from production to standby database.
                                2713139

                                Dear Hemant,

                                 

                                we dint mention any format in production and standby database, so archive format is like this o1_mf_1_8207_9w7w3k9f_.arc,   you know that  dint mention log_archive_dest, archives going to default destination flash recovery area.

                                 

                                in recovery database it is taking this format o1_mf_1_8207_9w7w3k9f_.arc if any extension or any dot then recovery is terminated,

                                 

                                i think i have given right information, please let me know anything else,

                                 

                                 

                                regards,

                                Sanjay

                                • 13. Re: Archive log format change while copying from production to standby database.
                                  2713139

                                  Dear Hemant,

                                   

                                  i have given archive log destination  pointed FRA, now archives are creating with .dbf extension, looks like everything okay. copying archives successfully to standby, i will monitor for two days if doesn't work i will come to you.  thanks for your valuable time.

                                   

                                  regards,

                                  sanjay.

                                  • 14. Re: Archive log format change while copying from production to standby database.
                                    2713139

                                    Hi Hemant,

                                     

                                    The issue is resolved, Thnks for you quick response, i have done like what ever i said last comment, everything fine. im closing this discusiion.