This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Nov 16, 2012 10:44 AM by jgarry RSS

Database walk-through - What to look for ??

Suddhasatwa_Bhaumik Newbie
Currently Being Moderated
Good Morning.

Let's assume that you're invited to my organisation to look into my platform, which has thousands of databases if not less, and you are requested to check various aspects of these databases to tell me where I am going wrong w.r.t administration, health checks, backups, etc.

What would be the points that you'd cover during such an excercise?
E.g., how the backups are being taken? What parameters to monitor day by day? Script(s) to identify bad queries? Etc ..

Some suggestions in this regard would be really helpful.

Thanks,

Suddhasatwa
  • 1. Re: Database walk-through - What to look for ??
    Girish Sharma Guru
    Currently Being Moderated
    I would prepare a list of those database/db servers who found top 10 mistakes in my examination and then suggest to admin to remove them. What are Top Ten Mistakes Found in Oracle Systems see here :
    http://docs.oracle.com/cd/B19306_01/server.102/b14211/technique.htm#i11221

    Regards
    Girish Sharma
  • 2. Re: Database walk-through - What to look for ??
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    Hi,

    Apart from solution above...

    you need to check all the database are having valid backup and are being taken backup regularly. need to check if there are DR for databases then standby and primay are in synch....

    If there are no DR for important database then suggest for DR.

    And you have specified above........
  • 3. Re: Database walk-through - What to look for ??
    Aman.... Oracle ACE
    Currently Being Moderated
    I am not sure that thousands of db's can be checked just like that for obvious issues but a quick look at the EM would be able to reveal a lot of things especially related to Performance , policy violations etc.

    HTH
    Aman....
  • 4. Re: Database walk-through - What to look for ??
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    SBhaumik_DBA wrote:

    Let's assume that you're invited to my organisation to look into my platform, which has thousands of databases if not less, and you are requested to check various aspects of these databases to tell me where I am going wrong w.r.t administration, health checks, backups, etc.

    What would be the points that you'd cover during such an excercise?
    I would ask for a better problem description and what the goals are of the exercise. What does who expect as output from the exercise for which reason to achieve what?
    E.g., how the backups are being taken? What parameters to monitor day by day? Script(s) to identify bad queries? Etc ..
    Useless approach. Take one issue raised. Identify bad queries. Bad ito what. CPU used? I/O consumed? Memory needed? Number of times executed?

    A bad query can be a single simplistic SQL statement that executes in 1s, and is executed a million times per day. As that SQL can be and should be executed in less than 0.5s if the correct index was used.

    A good query can be a single complex SQL that spans over 3 pages of A4 printout, is executed twice a day, with an execution plan that makes your head hurt when looking at it, and takes 5 hours to execute. As it does incredible complex processing to produce invaluable critical business data with an optimally designed SQL (using the best indexes, the most appropriate join algorithms, etc).

    Thinking that one can simplistically run a script, identify an issue like a "bad SQL", is dangerously naive. As is the concept that management typically has of "database health"...
  • 5. Re: Database walk-through - What to look for ??
    Suddhasatwa_Bhaumik Newbie
    Currently Being Moderated
    Thanks for all your inputs and thanks for redirection to the Oracle documentation.

    Here is what I summarize:
    Bad Connection Management
    Bad Use of Cursors and the Shared Pool
    Bad SQL - Top resource consuming SQL's
    Use of Nonstandard Initialization Parameters
    Getting Database I/O Wrong
    Redo Log Setup Problems
    Long Full Table Scans
    High Amounts of Recursive (SYS) SQL
    Deployment and Migration Errors
    Backup strategies
    Restoration/Recovery Strategies
    DR setup/policy
    User account policies
    Patch Management policies
    EOL of DB versions
    What other aspects can we add into it?
    Please advice

    Thanks,
    Suddhasatwa
  • 6. Re: Database walk-through - What to look for ??
    Suddhasatwa_Bhaumik Newbie
    Currently Being Moderated
    Hello Billy

    Thanks for your note.

    Purpose of this excercise is to come up with a definitive list of actions/areas which a DBA would look for in an environment so as to decide if everything is in place as it should be. Of course there would be changes to it depending on the application running on top of it/them and how they are being ran.

    As I said in my original post, I would kindly request you/all to imagine yourself in a situation wherein "you" have visited a client-site environment, and you are requested to "investigate" the platform as a DBA in whatsoever respect possible, and finally "_provide improvement suggestions_" to client.

    Please share your thoughts on the same.

    Thanks,

    Suddhasatwa
  • 7. Re: Database walk-through - What to look for ??
    Suddhasatwa_Bhaumik Newbie
    Currently Being Moderated
    Hello Aman

    Thanks for your note. That's one of the points I would include in my list - I.e., to install OEMGC to monitor all databases from one OEM.

    Do you foresee any performance issues because of OEM agents running on such a large number of databases?

    In case, and there can be many examples, wherein client is not ready to "pay" Oracle for the OEMGC licensing. In that case, we can have our own tool created whose agents/sub-scripts would run in each of the databases, after a certain interval of time .. let's say after every 10 mins, and report any/all issues in them to a central server from where we can monitor all of them at once? Note: The output of these scripts can be displayed in a web portal. [ A simple Apache installation would do in this case ... ]

    Please share your thoughts.

    Thanks,

    Suddhasatwa
  • 8. Re: Database walk-through - What to look for ??
    Aman.... Oracle ACE
    Currently Being Moderated
    Is it a realistic question or we are just chasing some wild thought only ?

    Aman....
  • 9. Re: Database walk-through - What to look for ??
    Girish Sharma Guru
    Currently Being Moderated
    SBhaumik_DBA wrote:
    Thanks for all your inputs and thanks for redirection to the Oracle documentation.

    Here is what I summarize:
    Bad Connection Management
    Bad Use of Cursors and the Shared Pool
    Bad SQL - Top resource consuming SQL's
    Use of Nonstandard Initialization Parameters
    Getting Database I/O Wrong
    Redo Log Setup Problems
    Long Full Table Scans
    High Amounts of Recursive (SYS) SQL
    Deployment and Migration Errors
    Backup strategies
    Restoration/Recovery Strategies
    DR setup/policy
    User account policies
    Patch Management policies
    EOL of DB versions
    What other aspects can we add into it?
    I think non, because this seems me complete list and much more provided by Oracle itself. I shall wait with you to see Aman's and Billy's response, who are definitely far far knowledgeable and experienced; no doubt.

    Regards
    Girish Sharma
  • 10. Re: Database walk-through - What to look for ??
    Suddhasatwa_Bhaumik Newbie
    Currently Being Moderated
    Hi Aman

    No - this is realistic! :-)
    Kindly share your thoughts on this, and also on the points that I have listed out in earlier post.

    Thanks,
    Suddhasatwa
  • 11. Re: Database walk-through - What to look for ??
    Aman.... Oracle ACE
    Currently Being Moderated
    Have a look at this MOS note, it may give you some lead,
    *How to Perform a Health Check on the Database [ID 122669.1]*

    Aman....
  • 12. Re: Database walk-through - What to look for ??
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    SBhaumik_DBA wrote:

    As I said in my original post, I would kindly request you/all to imagine yourself in a situation wherein "you" have visited a client-site environment, and you are requested to "investigate" the platform as a DBA in whatsoever respect possible, and finally "_provide improvement suggestions_" to client.
    You do that by starting to ask questions to management. What are the real and perceived problems? What is the architecture? What are the policies ito security? Auditing? Vendor support and maintenance? What are the critical business requirements that the platform and database need to address? Etc.

    I would not start out by running "scripts" pretending to be an all-knowing Oracle database guru.

    I would start by applying fundamental software engineering principles. User requirements and expectations. Defining that and defining the real or perceived problems. Before doing anything else.
  • 13. Re: Database walk-through - What to look for ??
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Another comment as to why approaching this issue with "running scripts" is flawed.

    Metrics alone are useless. I/O calls per second, CPU utilisation, memory used, network bandwidth consumed, transactions per second, number of users/sessions - these all are MEANINGLESS on their own.

    Unless you have a baseline for comparing those metrics against. A metric of a 100 IOPS alone does not tell you anything. It is not possible to say whether this is good/bad/wrong/performance problem/whatever if you cannot compare that against some baseline.

    Running scripts will give you primarily metrics. And that alone are meaningless when you want to determine whether there are problems with the performance or architecture or database or application.
  • 14. Re: Database walk-through - What to look for ??
    Suddhasatwa_Bhaumik Newbie
    Currently Being Moderated
    Thanks for your notes. That adds up a few more points in my list as the primary ones now! :-)

    Regards,
    Suddhasatwa
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points