8 Replies Latest reply on Oct 3, 2019 3:01 AM by Dude!

    delete archivelogs ASM

    Robeen

      Oracle Database 12.1.0.2

      Solaris 11.4

       

      Hello Team,

       

      I would like some help on writing a script to be executed on as ASMCMD as grid user.

       

      After I spool the output of query below

       

      SET PAGESIZE 1000 LINESIZE 500 ECHO OFF TRIMS ON TAB OFF FEEDBACK OFF HEADING OFF SERVEROUTPUT OFF

      SPOOL ./remove.sh

      SELECT

      -- r.CONSUMER_NAME,

      'rm ' || r.NAME

      -- , to_char(r.FIRST_SCN),

      -- to_char(r.NEXT_SCN),

      -- case when (r.next_scn > c.required_checkpoint_scn) then 'NO' else 'YES' end purgable

      FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c

      WHERE r.CONSUMER_NAME = c.CAPTURE_NAME and r.next_scn < c.required_checkpoint_scn;

      spool off

       

      Entry of remove.sh:

       

      rm +RECO/foreign/1_388_1018439098.dbf

      rm +RECO/foreign/1_389_1018439098.dbf

      rm +RECO/foreign/1_390_1018439098.dbf

      rm +RECO/foreign/2_294_1018439098.dbf

      rm +RECO/foreign/1_391_1018439098.dbf

      rm +RECO/foreign/1_392_1018439098.dbf

      rm +RECO/foreign/1_393_1018439098.dbf

      rm +RECO/foreign/1_394_1018439098.dbf

      rm +RECO/foreign/1_395_1018439098.dbf

      rm +RECO/foreign/1_396_1018439098.dbf

      rm +RECO/foreign/1_397_1018439098.dbf

      rm +RECO/foreign/1_398_1018439098.dbf

      rm +RECO/foreign/1_399_1018439098.dbf

      rm +RECO/foreign/1_400_1018439098.dbf

      rm +RECO/foreign/1_401_1018439098.dbf

      rm +RECO/foreign/1_402_1018439098.dbf

      rm +RECO/foreign/1_403_1018439098.dbf

      rm +RECO/foreign/1_404_1018439098.dbf

      rm +RECO/foreign/1_405_1018439098.dbf

      rm +RECO/foreign/1_406_1018439098.dbf

      rm +RECO/foreign/1_407_1018439098.dbf

      .....

       

      NExt, I write a script to login as ASMCMD and execute the above 'sh' file.

       

      Is there a way I can execute the sh file in ASMCMD?

       

      asmcmd -p « EOF

      remove.sh

      EOF

       

      Regards,

       

      Joe

        • 1. Re: delete archivelogs ASM
          EdStevens

          Robeen wrote:

           

          Oracle Database 12.1.0.2

          Solaris 11.4

           

          Hello Team,

           

          I would like some help on writing a script to be executed on as ASMCMD as grid user.

           

          After I spool the output of query below

           

          SET PAGESIZE 1000 LINESIZE 500 ECHO OFF TRIMS ON TAB OFF FEEDBACK OFF HEADING OFF SERVEROUTPUT OFF

          SPOOL ./remove.sh

          SELECT

          -- r.CONSUMER_NAME,

          'rm ' || r.NAME

          -- , to_char(r.FIRST_SCN),

          -- to_char(r.NEXT_SCN),

          -- case when (r.next_scn > c.required_checkpoint_scn) then 'NO' else 'YES' end purgable

          FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c

          WHERE r.CONSUMER_NAME = c.CAPTURE_NAME and r.next_scn < c.required_checkpoint_scn;

          spool off

           

          Entry of remove.sh:

           

          rm +RECO/foreign/1_388_1018439098.dbf

          rm +RECO/foreign/1_389_1018439098.dbf

          rm +RECO/foreign/1_390_1018439098.dbf

          rm +RECO/foreign/2_294_1018439098.dbf

          rm +RECO/foreign/1_391_1018439098.dbf

          rm +RECO/foreign/1_392_1018439098.dbf

          rm +RECO/foreign/1_393_1018439098.dbf

          rm +RECO/foreign/1_394_1018439098.dbf

          rm +RECO/foreign/1_395_1018439098.dbf

          rm +RECO/foreign/1_396_1018439098.dbf

          rm +RECO/foreign/1_397_1018439098.dbf

          rm +RECO/foreign/1_398_1018439098.dbf

          rm +RECO/foreign/1_399_1018439098.dbf

          rm +RECO/foreign/1_400_1018439098.dbf

          rm +RECO/foreign/1_401_1018439098.dbf

          rm +RECO/foreign/1_402_1018439098.dbf

          rm +RECO/foreign/1_403_1018439098.dbf

          rm +RECO/foreign/1_404_1018439098.dbf

          rm +RECO/foreign/1_405_1018439098.dbf

          rm +RECO/foreign/1_406_1018439098.dbf

          rm +RECO/foreign/1_407_1018439098.dbf

          .....

           

          NExt, I write a script to login as ASMCMD and execute the above 'sh' file.

           

          Is there a way I can execute the sh file in ASMCMD?

           

          asmcmd -p « EOF

          remove.sh

          EOF

           

          Regards,

           

          Joe

          asmcmd is not a linux shell processor. Period. Full stop.

           

          Why would you want to deleted these archivelogs outside of rman?  What will you do when you need to perform a recovery and rman can't do it because it can't find one of those files?

          • 2. Re: delete archivelogs ASM
            Robeen

            Please check this doc

             

            2011174.1

            if I do not delete them manually, the disk group will be full. Is there any way to delete using RMAN?

            • 3. Re: delete archivelogs ASM
              Dude!

              You have various options. For example:

               

              runthis=$(cat remove.sh)

              asmcmd -p << EOF

              $runthis

              EOF

               

              or

               

              runthis=$(cat remove.sh)

              asmcmd -p <<< "$runthis"

               

              or, and probably more appropriate:

               

              cat remove.sh | asmcmd -p

               

              However, I find it strange that you want to use the asmcmd utility to delete archivelogs. Normally you would use RMAN to delete archivelogs. But this is something I suggest to discuss in the RMAN forum.

              1 person found this helpful
              • 4. Re: delete archivelogs ASM
                EdStevens

                Robeen wrote:

                 

                Please check this doc

                 

                2011174.1

                if I do not delete them manually, the disk group will be full. Is there any way to delete using RMAN?

                Seriously?

                 

                 

                https://docs.oracle.com/database/121/RCMRF/rcmsynta015.htm#RCMRF121

                https://docs.oracle.com/database/121/RCMRF/rcmsubcl002.htm#GUID-DDF929FF-796B-4AD6-AE58-F831CAFC7703__BABJGBFG

                 

                https://lmgtfy.com/?q=example+of+oracle+rman+delete+archivelog

                 

                I like to include deleting them with the backup:

                 

                backup as compressed backupset archivelog all delete all input
                • 5. Re: delete archivelogs ASM
                  Dude!

                  I'd say that's not necessarily the best option, compared to "backup database plus archivelog delete input", which includes the current log after the database backup, unlike "backup archivelog" which does not. This can make a big difference when trying to restore and recover the database. But again, I suggest to discuss this in the RMAN forum.

                  • 6. Re: delete archivelogs ASM
                    EdStevens

                    Dude! wrote:

                     

                    I'd say that's not necessarily the best option, compared to "backup database plus archivelog delete input", which includes the current log after the database backup, unlike "backup archivelog" which does not. This can make a big difference when trying to restore and recover the database. But again, I suggest to discuss this in the RMAN forum.

                    Agreed on both counts.

                    • 7. Re: delete archivelogs ASM
                      Robeen

                      I cannot delete all archivelogs because some are needed  by Golden Gate

                      • 8. Re: delete archivelogs ASM
                        Dude!

                        RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

                         

                        Oracle GoldenGate uses a mechanism that prevents archive logs from being deleted before they are processed. If you need more info I suggest to ask in the GoldenGate forum.