1 2 Previous Next 22 Replies Latest reply on Feb 4, 2013 6:16 PM by jgarry Go to original post
      • 15. Re: Daily activities of a DBA
        988947
        I.     Daily Procedures

        A.     Verify all instances are up
        Make sure the database is available. Log into each instance and run daily reports or test scripts. Some sites may wish to automate this.
        Optional implementation: use Oracle Enterprise Manager's 'probe' event.
        B.     Look for any new alert log entries
        •     Connect to each managed system.
        •     Use 'telnet' or comparable program.
        •     For each managed instance, go to the background dump destination, usually $ORACLE_BASE/<SID>/bdump. Make sure to look under each managed database's SID.
        •     At the prompt, use the Unix ‘tail’ command to see the alert_<SID>.log, or otherwise examine the most recent entries in the file.
        •     If any ORA-errors have appeared since the previous time you looked, note them in the Database Recovery Log and investigate each one. The recovery log is in <file>.
        C.     Verify DBSNMP is running
        1.     Log on to each managed machine to check for the 'dbsnmp' process.
        For Unix: at the command line, type ps –ef | grep dbsnmp. There should be two dbsnmp processes running. If not, restart DBSNMP. (Some sites have this disabled on purpose; if this is the case, remove this item from your list, or change it to "verify that DBSNMP is NOT running".)
        D.     Verify success of database backup
        E.     Verify success of database archiving to tape
        F.     Verify enough resources for acceptable performance
        1.     Verify free space in tablespaces.
        For each instance, verify that enough free space exists in each tablespace to handle the day’s expected growth. As of <date>, the minimum free space for <repeat for each tablespace>: [ < tablespace > is < amount > ]. When incoming data is stable, and average daily growth can be calculated, then the minimum free space should be at least <time to order, get, and install more disks> days’ data growth.
        a)     Go to each instance, run free.sql to check free mb in tablespaces.
        Compare to the minimum free MB for that tablespace. Note any low-space conditions and correct.
        b)     Go to each instance, run space.sql to check percentage free in tablespaces.
        Compare to the minimum percent free for that tablespace. Note any low-space conditions and correct.
        2.     Verify rollback segment.
        Status should be ONLINE, not OFFLINE or FULL, except in some cases you may have a special rollback segment for large batch jobs whose normal status is OFFLINE.
        a)     Optional: each database may have a list of rollback segment names and their expected statuses.
        b)     For current status of each ONLINE or FULL rollback segment (by ID not by name), query on V$ROLLSTAT.
        c)     For storage parameters and names of ALL rollback segment, query on DBA_ROLLBACK_SEGS. That view’s STATUS field is less accurate than V$ROLLSTAT, however, as it lacks the PENDING OFFLINE and FULL statuses, showing these as OFFLINE and ONLINE respectively.
        3.     Identify bad growth projections.
        Look for segments in the database that are running out of resources (e.g. extents) or growing at an excessive rate. The storage parameters of these segments may need to be adjusted. For example, if any object reached 200 as the number of current extents, AND it's an object that is supposed to get large, upgrade the max_extents to unlimited.
        a)     To gather daily sizing information, run analyze5pct.sql. If you are collecting nightly volumetrics, skip this step.
        b)     To check current extents, run nr_extents.sql
        c)     Query current table sizing information
        d)     Query current index sizing information
        e)     Query growth trends
        4.     Identify space-bound objects.
        Space-bound objects’ next_extents are bigger than the largest extent that the tablespace can offer. Space-bound objects can harm database operation. If we get such object, first need to investigate the situation. Then we can use ALTER TABLESPACE <tablespace> COALESCE. Or add another datafile.
        a)     Run spacebound.sql. If all is well, zero rows will be returned.
        5.     Processes to review contention for CPU, memory, network or disk resources.
        a)     To check CPU utilization, go to x:\web\phase2\default.htm =>system metrics=>CPU utilization page. 400 is the maximum CPU utilization because there are 4 CPUs on phxdev and phxprd machine. We need to investigate if CPU utilization keeps above 350 for a while.

        G.     Copy Archived Logs to Standby Database and Roll Forward
        If you have a Standby Database, copy the appropriate Archived Logs to the expected location on the standby machine and apply those logs (roll forward the changes) to the standby database. This keeps the standby database up-to-date.

        The copying of logs, the applying of them, or both, can in some cases be automated. If you have automated them, then your daily task should be to confirm that this happened correctly each day.
        H.     Read DBA manuals for one hour
        Nothing is more valuable in the long run than that the DBA be as widely experienced, and as widely read, as possible. Readings should include DBA manuals, trade journals, and possibly newsgroups or mailing lists.
        • 16. Re: Daily activities of a DBA
          988947
          Oracle dba Daily Procedures

          A.     Verify all instances are up
          Make sure the database is available. Log into each instance and run daily reports or test scripts. Some sites may wish to automate this.
          Optional implementation: use Oracle Enterprise Manager's 'probe' event.
          B.     Look for any new alert log entries
          •     Connect to each managed system.
          •     Use 'telnet' or comparable program.
          •     For each managed instance, go to the background dump destination, usually $ORACLE_BASE/<SID>/bdump. Make sure to look under each managed database's SID.
          •     At the prompt, use the Unix ‘tail’ command to see the alert_<SID>.log, or otherwise examine the most recent entries in the file.
          •     If any ORA-errors have appeared since the previous time you looked, note them in the Database Recovery Log and investigate each one. The recovery log is in <file>.
          C.     Verify DBSNMP is running
          1.     Log on to each managed machine to check for the 'dbsnmp' process.
          For Unix: at the command line, type ps –ef | grep dbsnmp. There should be two dbsnmp processes running. If not, restart DBSNMP. (Some sites have this disabled on purpose; if this is the case, remove this item from your list, or change it to "verify that DBSNMP is NOT running".)
          D.     Verify success of database backup
          E.     Verify success of database archiving to tape
          F.     Verify enough resources for acceptable performance
          1.     Verify free space in tablespaces.
          For each instance, verify that enough free space exists in each tablespace to handle the day’s expected growth. As of <date>, the minimum free space for <repeat for each tablespace>: [ < tablespace > is < amount > ]. When incoming data is stable, and average daily growth can be calculated, then the minimum free space should be at least <time to order, get, and install more disks> days’ data growth.
          a)     Go to each instance, run free.sql to check free mb in tablespaces.
          Compare to the minimum free MB for that tablespace. Note any low-space conditions and correct.
          b)     Go to each instance, run space.sql to check percentage free in tablespaces.
          Compare to the minimum percent free for that tablespace. Note any low-space conditions and correct.
          2.     Verify rollback segment.
          Status should be ONLINE, not OFFLINE or FULL, except in some cases you may have a special rollback segment for large batch jobs whose normal status is OFFLINE.
          a)     Optional: each database may have a list of rollback segment names and their expected statuses.
          b)     For current status of each ONLINE or FULL rollback segment (by ID not by name), query on V$ROLLSTAT.
          c)     For storage parameters and names of ALL rollback segment, query on DBA_ROLLBACK_SEGS. That view’s STATUS field is less accurate than V$ROLLSTAT, however, as it lacks the PENDING OFFLINE and FULL statuses, showing these as OFFLINE and ONLINE respectively.
          3.     Identify bad growth projections.
          Look for segments in the database that are running out of resources (e.g. extents) or growing at an excessive rate. The storage parameters of these segments may need to be adjusted. For example, if any object reached 200 as the number of current extents, AND it's an object that is supposed to get large, upgrade the max_extents to unlimited.
          a)     To gather daily sizing information, run analyze5pct.sql. If you are collecting nightly volumetrics, skip this step.
          b)     To check current extents, run nr_extents.sql
          c)     Query current table sizing information
          d)     Query current index sizing information
          e)     Query growth trends
          4.     Identify space-bound objects.
          Space-bound objects’ next_extents are bigger than the largest extent that the tablespace can offer. Space-bound objects can harm database operation. If we get such object, first need to investigate the situation. Then we can use ALTER TABLESPACE <tablespace> COALESCE. Or add another datafile.
          a)     Run spacebound.sql. If all is well, zero rows will be returned.
          5.     Processes to review contention for CPU, memory, network or disk resources.
          a)     To check CPU utilization, go to x:\web\phase2\default.htm =>system metrics=>CPU utilization page. 400 is the maximum CPU utilization because there are 4 CPUs on phxdev and phxprd machine. We need to investigate if CPU utilization keeps above 350 for a while.

          G.     Copy Archived Logs to Standby Database and Roll Forward
          If you have a Standby Database, copy the appropriate Archived Logs to the expected location on the standby machine and apply those logs (roll forward the changes) to the standby database. This keeps the standby database up-to-date.

          The copying of logs, the applying of them, or both, can in some cases be automated. If you have automated them, then your daily task should be to confirm that this happened correctly each day.
          H.     Read DBA manuals for one hour
          Nothing is more valuable in the long run than that the DBA be as widely experienced, and as widely read, as possible. Readings should include DBA manuals, trade journals, and possibly newsgroups or mailing lists.
          • 17. Re: Daily activities of a DBA
            SomeoneElse
            Z. Making sure you don't respond to 6 year old threads.
            • 18. Re: Daily activities of a DBA
              Billy~Verreynne
              Besides resurrecting an old thread (zombifying it?), any DBA that does that list of daily activities is a poor DBA and should not be employed.

              Why? One word. Automation.
              • 19. Re: Daily activities of a DBA
                damorgan
                I agree with both the most recent posts.

                1. Start a new thread ... please don't restart an old thread.

                2. Automation.

                That said there are some things that must be done, at least in part, the old way. Here is my short list.
                http://www.morganslibrary.org/reference/dba_best_practices.html
                Running these can be automated ... reviewing the results still requires that both eyes be open and the brain engaged.
                • 20. Re: Daily activities of a DBA
                  EdStevens
                  985944 wrote:
                  I.     Daily Procedures
                  <snip>
                  ============================================================================


                  Welcome to the forum. Did you notice that you were responding to a message that is over FIVE YEARS old?

                  Before proceeding, please read the forum FAQ. The link is in the upper right corner of this very page.

                  After reading the FAQ, please open your own thread, describing your own problem and what diagnostic steps you have already taken.

                  Moderator - please lock this thread.

                  ============================================================================
                  • 21. Re: Daily activities of a DBA
                    damorgan
                    Moderator ... please lock ALL threads older than some arbitrary value ... perhaps 3-4 months.

                    And, perhaps, automate the task as long as we are on the subject.

                    Thanks.
                    • 22. Re: Daily activities of a DBA
                      jgarry
                      This is true in the general case, but there are exceptions. I know I have gone back and updated some old threads of mine for some legitimate reason. I doubt that there is some setting that allows OP's to unlock their threads, that would be one answer if possible (ie, locked for age versus locked for cause).

                      The more fundamental problem is modern searches and social software tend to either ignore aging or care more about transient extremely recent posts.
                      1 2 Previous Next