7 Replies Latest reply: May 15, 2013 5:48 AM by Mahir M. Quluzade RSS

    When ALTER SYSTEM ARCHIVE LOG  happens implicitly.....

    user9085814
      Hi ,

      When scanning the v$archived_log from PRIMARY SIDE , I saw below output

      NAME CREATOR REGISTR SEQUENCE# FAL
      -------------------------- ------- ------------ ----------------- ---
      /u05/PROD/archivelogs/ARC_816992406_1_22885.dbf ARCH ARCH 22885 NO
      /u05/PROD/archivelogs/ARC_816992406_1_22886.dbf FGRD FGRD 22886 NO


      Then I went to alert log file and check for the sequence#, i found that

      When Log switch happens through * ALTER SYSTEM ARCHIVE LOG * , then for that sequence the archive log CREATOR and REGISTRAR will be FGRD .
      When Log switch happens automatically ( 90% full of log file ) , then for that sequence the archive log CREATOR and REGISTRAR will be ARCH .


      Though * ALTER SYSTEM ARCHIVE LOG * happens synchronously means control will not come back to the process unless and until the underlying log file has been
      archived completely ,so the CREATOR and REGISTRAR is FGRD.

      My question here is , In which situation * ALTER SYSTEM ARCHIVE LOG * will happen implicitly .


      Thanks
        • 1. Re: When ALTER SYSTEM ARCHIVE LOG  happens implicitly.....
          Mahir M. Quluzade
          user9085814 wrote:
          Hi ,

          When scanning the v$archived_log from PRIMARY SIDE , I saw below output

          NAME CREATOR REGISTR SEQUENCE# FAL
          -------------------------- ------- ------------ ----------------- ---
          /u05/PROD/archivelogs/ARC_816992406_1_22885.dbf ARCH ARCH 22885 NO
          /u05/PROD/archivelogs/ARC_816992406_1_22886.dbf FGRD FGRD 22886 NO


          Then I went to alert log file and check for the sequence#, i found that

          When Log switch happens through * ALTER SYSTEM ARCHIVE LOG * , then for that sequence the archive log CREATOR and REGISTRAR will be FGRD .
          When Log switch happens automatically ( 90% full of log file ) , then for that sequence the archive log CREATOR and REGISTRAR will be ARCH .


          Though * ALTER SYSTEM ARCHIVE LOG * happens synchronously means control will not come back to the process unless and until the underlying log file has been
          archived completely ,so the CREATOR and REGISTRAR is FGRD.

          My question here is , In which situation * ALTER SYSTEM ARCHIVE LOG * will happen implicitly .


          Thanks
          Hi,

          Archived log is created by ARCH process, Foreground process (manully archive), RMAN, LGWR .

          I think bellow example shall answer your question.
           
           SQL>  select creator,count(*) from v$archived_log group by creator; 
          
          CREATOR   COUNT(*)
          ------- ----------
          FGRD           1
          LGWR       254
          ARCH            526
          
          SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;   -- manually archive log  FGRD
          
          System altered.
          
          SQL>  select creator,count(*) from v$archived_log group by creator;
          
          CREATOR   COUNT(*)
          ------- ----------
          FGRD           2
          LGWR     255
          ARCH            526
          
          SQL> alter system switch logfile;  -- when switch logfile, ARCH process archive log 
          
          System altered.
          
          SQL> select creator,count(*) from v$archived_log group by creator;
          
          CREATOR   COUNT(*)
          ------- ----------
          FGRD           2
          LGWR       256
          ARCH            527
          
          SQL> 
          Regards
          Mahir M. Quluzade
          • 2. Re: When ALTER SYSTEM ARCHIVE LOG  happens implicitly.....
            user9085814
            Thanks for the Reply ...

            I am agree with you .

            The enviornment that i am taking abt is DATAGUARD Enviornment .

            I have not issued ALTER SYSTEM ARCHIVE LOG CURRENT from any session (Foreground Process ) of the Primary DB .
            But still i can see ALTER SYSTEM ARCHIVE LOG in the alter log file .

            So my question on which scenario ALTER SYSTEM ARCHIVE LOG will be captured in alert log file .
            becoz it's an error to execute ALTER SYSTEM ARCHIVE LOG from Foreground Process .


            Thanks..
            • 3. Re: When ALTER SYSTEM ARCHIVE LOG  happens implicitly.....
              Hemant K Chitale
              Possibly you have script / job scheduled to issue the ALTER SYSTEM ARCHIVE LOG command at regular / frequent intervals. Since this is the Primary for a Standby environment, the previous DBA might have configured such a script. You should be using the instance parameter ARCHIVE_LAG_TARGET to configure automatic archive log generation at a pre-determined frequency.


              Hemant K Chitale
              • 4. Re: When ALTER SYSTEM ARCHIVE LOG  happens implicitly.....
                Mahir M. Quluzade
                Hi,
                user9085814 wrote:
                Thanks for the Reply ...

                I am agree with you .

                The enviornment that i am taking abt is DATAGUARD Enviornment .

                I have not issued ALTER SYSTEM ARCHIVE LOG CURRENT from any session (Foreground Process ) of the Primary DB .
                But still i can see ALTER SYSTEM ARCHIVE LOG in the alter log file .
                When you call ALTER SYSTEM ARCHIVE LOG CURRENT command, then oracle write to alert log as :

                On Primary
                 
                 Wed May 15 09:16:56 2013
                 Archived Log entry 789 added for thread 1 sequence 396 ID 0xf23a6e3f dest 1:
                 *ALTER SYSTEM ARCHIVE LOG*
                 Thread 1 cannot allocate new log, sequence 398
                >
                So my question on which scenario ALTER SYSTEM ARCHIVE LOG will be captured in alert log file .
                becoz it's an error to execute ALTER SYSTEM ARCHIVE LOG from Foreground Process .
                Did you had a RMAN script backup archived logs which is call ALTER SYSTEM ARCHIVE LOG CURRENT
                 RMAN> run 
                           {
                              sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
                              backup  archivelog all delete all input;
                           }
                If you try ALTER SYSTEM ARCHIVE LOG CURRENT command on stanby, you must get error ORA-01109: database not open. It means database is not opened READ/WRITE mode.
                Then we can't use ALTER SYSTEM ARCHIVE LOG CURRENT on standby database.

                Thanks for interesting thread
                Mahir M. Quluzade
                http://www.mahir-quluzade.com
                • 5. Re: When ALTER SYSTEM ARCHIVE LOG  happens implicitly.....
                  user9085814
                  Thanks for your help ...

                  I have checked in our enviornment ...And found that a script is running on behalf of RMAN to take the archival log backup on a regular interval of time.
                  That is causing a log switch whose creator is FGRD process and Foreground archival is getting captured in the alert log file.


                  Thanks....
                  • 6. Re: When ALTER SYSTEM ARCHIVE LOG  happens implicitly.....
                    user9085814
                    Thanks for your reply ... It is really helpful .....
                    • 7. Re: When ALTER SYSTEM ARCHIVE LOG  happens implicitly.....
                      Mahir M. Quluzade
                      user9085814 wrote:
                      Thanks for your help ...

                      I have checked in our enviornment ...And found that a script is running on behalf of RMAN to take the archival log backup on a regular interval of time.
                      That is causing a log switch whose creator is FGRD process and Foreground archival is getting captured in the alert log file.


                      Thanks....
                      You are welcome!