10 Replies Latest reply: Dec 17, 2012 7:03 AM by Richard Harrison . RSS

    Some Automation...

    user8983130
      Hi,

      *1.* I am using oracle 11g R2 and RHEL 5.5. We do have a data guard environment. Archive log sent to remote location and thus standby gets updated. I use the following command to delete archive log from standby:

      #select max(sequence#) from v$archived_log where applied='YES';
      #delete archivelog all completed before 'sysdate-3';

      I have to do this manually, Is there any way that i automated the task so that i can keep 1-2 days archivelog in my standby and delete the others which have been already applied??

      *2.* Background process and user process write trace files to a fix location which becomes very big day by day. What can i do with these trace files?? I don't think they are not always required. How to delete them ??

      Regards,
        • 1. Re: Some Automation...
          hitgon
          1. I am using oracle 11g R2 and RHEL 5.5. We do have a data guard environment. Archive log sent to remote location and thus standby gets updated. I use the following command to delete archive log from standby:

          #select max(sequence#) from v$archived_log where applied='YES';
          #delete archivelog all completed before 'sysdate-3';

          I have to do this manually, Is there any way that i automated the task so that i can keep 1-2 days archivelog in my standby and delete the others which have been already applied??

          Comments:==>
          In RHEL you can configure the cronjob in OS Level and call the shellscript(rman commands) periodically and get tasks automatically
          In Database you can also configure the Database Scheduler and call the shellscript(rman commands) periodically and get tasks automatically


          2. Background process and user process write trace files to a fix location which becomes very big day by day. What can i do with these trace files?? I don't think they are not always required. How to delete them ??
          Comments:==>

          You can simply put the manual commands inside the shell script and invoke periodically via the cronjob or database scheduler

          Sample commands
          find /u01/app/oracle/diag/rdbms/orcl/orcl2/trace -name '*.trc' -mtime +7 -exec ls -ltr {} \;
          find /u01/app/oracle/diag/rdbms/orcl/orcl2/trace -name '*.trm' -mtime +7 -exec ls -ltr {} \;

          Regards
          Hitgon

          Edited by: hitgon on Dec 8, 2012 4:17 PM
          • 2. Re: Some Automation...
            damorgan
            I can't think of anything I would want to use in an 11gR2 database system than unnecessary cron job.

            To the OP: Anything you can do with a cron job ... you can do better with the DBMS_SCHEDULER built-in package: Especially this.
            • 3. Re: Some Automation...
              phaeus
              Hello,
              you can use a Fast Recovery Area. This area deletes automatilciy archive log that can be deleted under Space Pressure.

              regards
              Peter
              • 4. Re: Some Automation...
                user8983130
                thanks for your reply!!

                Would u show the full command/ steps how can i accomplished this...

                Regards,
                • 5. Re: Some Automation...
                  EdStevens
                  user8983130 wrote:
                  thanks for your reply!!

                  Would u show the full command/ steps how can i accomplished this...

                  Regards,
                  The clue was the reference to 'dbms_scheduler'. First place to look is the docs at tahiti.oracle.com. http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_sched.htm#CIHHBGGI

                  And as always there are examples all over the internet. Google is your friend. Interestingly enough, if you google 'dbms_scheduler' one the top hits will be a comprehensive site put together by the same damorgan you were responding to.
                  • 6. Re: Some Automation...
                    user8983130
                    Hey...this command only find the logs..but don't delete them

                    What should i do to delete after finding them??? Commands are:

                    find /u01/app/oracle/diag/rdbms/orcl/orcl2/trace -name '*.trc' -mtime +7 -exec ls -ltr {} \;
                    find /u01/app/oracle/diag/rdbms/orcl/orcl2/trace -name '*.trm' -mtime +7 -exec ls -ltr {} \;
                    • 7. Re: Some Automation...
                      494959
                      Hi ,

                      find /u01/app/oracle/diag/rdbms/orcl/orcl2/trace -name '*.trc' -mtime +7 -exec ls -ltr {} \;
                      find /u01/app/oracle/diag/rdbms/orcl/orcl2/trace -name '*.trm' -mtime +7 -exec ls -ltr {} \;

                      instead of the ls -ltr user rm
                      find /u01/app/oracle/diag/rdbms/orcl/orcl2/trace -name '*.trc' -mtime +7 -exec rm  {} \;
                      find /u01/app/oracle/diag/rdbms/orcl/orcl2/trace -name '*.trm' -mtime +7 -exec rm {} \; 
                      • 8. Re: Some Automation...
                        jgarry
                        You should find and redirect the output to save what you've found before you delete them. That way you can go back and look to see what went wrong.
                        • 9. Re: Some Automation...
                          moreajays
                          Hi ,

                          For 1st , Use RMAN to Backup archive with delete input option it will delete archives from primary once it has been backed up & only if it is not required on Standby for recovery

                          Thanks,
                          Ajay More
                          http://moreajays.blogspot.com
                          • 10. Re: Some Automation...
                            Richard Harrison .
                            Hi,
                            ADR can do it for you:

                            http://gavinsoorma.com/2010/09/purging-trace-and-dump-files-with-11g-adrci/

                            Also for your archive logs on the standby as someone else already suggested - use a recovery area - then everything is automatic no messing around with scripts.

                            Regards,
                            Harry