This discussion is archived
13 Replies Latest reply: Dec 7, 2012 6:27 AM by david howard RSS

New to Oracle DBA

david howard Newbie
Currently Being Moderated
Recently I've been thrust (kicking and screaming) into a role of DBA. My supervisor has put me through the Oracle Sys Admin Workshops I&II, but that is a starting point; it seemed that many of the features that were touched upon are not being used in our environment (ASM,AMM,RMAN,EM) which makes my life less fun!
My job now is to monitor the database for any performance issues, proactive failure avoidance, backup and restore (the BU/R is handled by NetBackup/BCV with Symmetrix disk mirroring between).

I've been a systems administrator for many years, and I've been able to script a few health checks outside of the DB; at the OS level. I am beginning to look into some of the v$ views that allow me to see what is happening in our system.
I have a couple of generalized questions I'd like to pose (if I need a seperate thread for each question let me know and I'll repost):
1. How do I determine what features are being used by the database (outside of the features mentioned earlier)?
2. What should I be monitoring on a <interval> basis (hourly, daily, monthly)?
3. What should I be looking for when I monitor? (right now i look at the alert log every day for "ORA-" entries and send alerts...)

I'm sure I'll have many many more questions, but these are the ones I have on my mind at the current time.
I apologize for the generality of my questions, I'm sure we'll get more granular as the discussion continues.

We are running the following:
Oracle 11gR2 in HA mode (Veritas clustering NOT oracle clustering or grid control) running on Solaris 10...
Thanks in advance for helping me understand this complex beast!
  • 1. Re: New to Oracle DBA
    sb92075 Guru
    Currently Being Moderated
    http://docs.oracle.com

    Start by Reading The Fine Manual, Concepts Manual
  • 2. Re: New to Oracle DBA
    moreajays Pro
    Currently Being Moderated
    I have written this for You !!

    http://moreajays.blogspot.in/2012/12/what-is-oracle-database.html

    Thanks,
    Ajay More
    http://moreajays.blogspot.com
  • 3. Re: New to Oracle DBA
    L-MachineGun Pro
    Currently Being Moderated
    1. How do I determine what features are being used by the database (outside of the features mentioned earlier)?
    Easiest: download and execute RDA (Note ID 314422.1)
    2. What should I be monitoring on a <interval> basis (hourly, daily, monthly)?

    3. What should I be looking for when I monitor? (right now i look at the alert log every day for "ORA-" entries and send alerts...)
    Every Oracle rdbms comes with an "Enterprise Manager" utility (free) which can be used to monitor the database.
    You can start by using the default Enterprise Manager settings.

    :p
  • 4. Re: New to Oracle DBA
    david howard Newbie
    Currently Being Moderated
    @moreajay - Thank you that was very helpful information. I was looking for information along the lines of what is posted here:
    Oracle DBA Daily/Weekly checks
    That is an old thread, I was hoping to find current up to date information...
  • 5. Re: New to Oracle DBA
    sb92075 Guru
    Currently Being Moderated
    http://www.orafaq.com/wiki/Scripts
  • 6. Re: New to Oracle DBA
    jgarry Guru
    Currently Being Moderated
    975181 wrote:
    Recently I've been thrust (kicking and screaming) into a role of DBA. My supervisor has put me through the Oracle Sys Admin Workshops I&II, but that is a starting point; it seemed that many of the features that were touched upon are not being used in our environment (ASM,AMM,RMAN,EM) which makes my life less fun!
    My job now is to monitor the database for any performance issues, proactive failure avoidance, backup and restore (the BU/R is handled by NetBackup/BCV with Symmetrix disk mirroring between).
    Modern Oracle is pretty tame, most performance problems wind up being application issues. Avoid "best practices" and people with prescriptive remedies. When you do have a problem, you need to solve that problem, not start twiddling knobs. There is plenty of instrumentation, though some things can get pretty dense for newbies.

    >
    I've been a systems administrator for many years, and I've been able to script a few health checks outside of the DB; at the OS level. I am beginning to look into some of the v$ views that allow me to see what is happening in our system.
    I have a couple of generalized questions I'd like to pose (if I need a seperate thread for each question let me know and I'll repost):
    Normally one question at a time gets more answers, but some of us will do a list anyways. One thing you will learn as a DBA is you have to change focus rapidly between qualitatively diverse subjects. That is an extreme understatement.
    1. How do I determine what features are being used by the database (outside of the features mentioned earlier)?
    There is a table called DBA_FEATURE_USAGE_STATISTICS that tracks some things. Also, when you log into sqlplus, you get a list of some options. This winds up being quite a complex subject, as there are some features the db uses internallly that you are not allowed to use unless they are licensed. As a DBA you are expected to not even access the tables or views, and to turn off access through dbconsole/enterprise manager (dbconsole is a single db enterprise manager included as a default. Enterprise manager is often used as a synonym for the db part of grid. Explanations may vary) . You are also expected to know that these things can get silently turned back on when you patch. Oracle has a feature called OCM, which helpfully tries to tell support about your system, if not unconfigured.

    Learn to use the docs to search for things like DBA_FEATURE_USAGE_STATISTICS. Yes, it is hard to search for things you don't know what they are called.
    2. What should I be monitoring on a <interval> basis (hourly, daily, monthly)?
    Remember to post the exact version of Oracle when posting here. The version would look like 11.2.0.3. So, it depends, and it depends if you are using grid control. The default dbconsole has some decent notifications. The details of what to monitor are site dependent and should be explicit. Some place send some alerts out at 3AM to your cell phone, others think it is good enough to send you an email for when the first person on the team gets in. You have to watch out for the former if you are the sole dba, you can get run ragged without some pushback against management that has a funny idea of uptime requirements. Some places people are afraid to say boo even if there is something wrong, so you do have to watch things. If you can get licensing, the top activity screen in dbconsole/em is real nice for just leaving it up on your screen.

    In the past, you had a whole list of things to watch for, nowadays it's easy to have sufficient space and just get to know your system, how much cpu and i/o in pretty little graphs is normal. In most places you learn pretty quick what makes the phone ring.

    But the first rule is "don't lose data." That means, fully understand how to backup and how to restore in your sleep, for various scenarios.
    3. What should I be looking for when I monitor? (right now i look at the alert log every day for "ORA-" entries and send alerts...)
    Yes, you need to know what they mean.

    >
    I'm sure I'll have many many more questions, but these are the ones I have on my mind at the current time.
    I apologize for the generality of my questions, I'm sure we'll get more granular as the discussion continues.

    We are running the following:
    Oracle 11gR2 in HA mode (Veritas clustering NOT oracle clustering or grid control) running on Solaris 10...
    Thanks in advance for helping me understand this complex beast!
    Welcome to our world! You will see there can be some stylistic variance in how people administer. The modern training pushes people to be "DBA 2.0," a fancy way of saying to be slaves to the GUI's. That's only useful up to a point, you do have to have someone around who is willing to dive into the command line and understand what is really going on. People here really want to help, follow the "ask smart questions faq" and the various faqs here. There are some real gurus here (not just in badges), as well as a vast following 24 hours.

    A note about googling: There is a lot of good information on the web. There is also a lot of bad advice. Use a critical eye, even here and in the docs. So, learn to deal with the docs, learn to search before asking, give all requested information when posting even if it seems oddly requested. Remember, we can't see what you do, people may jump to wrong conclusions if you don't cut and paste to show exactly what you did and the response. Many here have been doing this a long time and can ask to see information to answer the question you didn't know to ask.
  • 7. Re: New to Oracle DBA
    david howard Newbie
    Currently Being Moderated
    I tried to request EM for 11gR2 and I got this reply from Oracle support:
    Hello David,


    The CSI # __xxxxxxxx__ does not contain the appropriate license for the product that you are requesting.In order to receive a Version Update for< Enterprise Manager 11g Grid Control> you will need to provide a supported CSI including that product license.

    If you believe this to be a mistake at Oracle, kindly contact your Local Support Sales Representative. You could also call the toll free number for sales support in US which is800-833-3536.



    Perhaps I requested the wrong thing...This is all new to me and very very confusing and overwhelming!
  • 8. Re: New to Oracle DBA
    moreajays Pro
    Currently Being Moderated
    Hi,

    1. How do I determine what features are being used by the database (outside of the features mentioned earlier)?
    A>

    Below query will give the features that are currently enabled on you database.
    select * from v$option where VALUE='TRUE';

    This one will give you details usage report if db features being used

    select * from DBA_FEATURE_USAGE_STATISTICS where CURRENTLY_USED='TRUE';

    2. What should I be monitoring on a <interval> basis (hourly, daily, monthly)?
    A>
    Being DBA it should be always hourly/minutely monitoring , but it is not possible being human either grid control or cronjobs (with auto mail alerts) should be scheduled.
    AWR/ADDM features are available which allows us to go in past & track what was happening in DB giving excellent statistics of the DB

    3. What should I be looking for when I monitor? (right now i look at the alert log every day for "ORA-" entries and send alerts...)
    A>
    As Grid needs licenses the easiest way for you being a SYSADMIN is to do few shell scripting and place some life long job in cron
    Such cron's should captured ORA- , err , Warning , corrupt , fail , down , stop strings of alert log
    Apart from this you can also have scripts in place for locking/Blocking session in DB , TOP cpu/memory/IO consuming sql's scheduled to mail you once server metric utilization goes high
    One most important is space alert , OS free space , tablespace free space sql's should always be runing every min in cron


    Thanks,
    Ajay More
    http://moreajays.blogspot.com
  • 9. Re: New to Oracle DBA
    jgarry Guru
    Currently Being Moderated
    Yes, but you have "dbconsole." Many people use the terms interchangeably. See http://docs.oracle.com/cd/E11882_01/server.112/e10897/em_manage.htm#ADMQS032
  • 10. Re: New to Oracle DBA
    EdStevens Guru
    Currently Being Moderated
    975181 wrote:
    Recently I've been thrust (kicking and screaming) into a role of DBA. My supervisor has put me through the Oracle Sys Admin Workshops I&II, but that is a starting point; it seemed that many of the features that were touched upon are not being used in our environment (ASM,AMM,RMAN,EM) which makes my life less fun!
    My job now is to monitor the database for any performance issues, proactive failure avoidance, backup and restore (the BU/R is handled by NetBackup/BCV with Symmetrix disk mirroring between).
    I can't help bu wonder about why some of those arent' used. Particularly rman and EM.
    Especially rman. Your one task that trumps all others is to be able to restore the database in the event of a catastrophe. With no to minimal loss of data. I know you are drinking from a firehose right now. But keep backup and recovery at the top of your list. I DO NOT TRUST sysadmins that tell me "don't worry about your database tools for backup. I've got you covered". Unless you have recently tested your ability to recover with your current backup strategy, you have to consider your strategy is fatally flawed and you have no usable backups.

    I can't emphasize that enough. About once a month we see someone who has irretrievably lost their database. They were either not taking a backup at all, or were relying on a backup strategy that was fundamentally flawed. I barely dodged the bullet myself about a year ago when our SA decided to reorganize the SAN ....


    >
    I've been a systems administrator for many years, and I've been able to script a few health checks outside of the DB; at the OS level. I am beginning to look into some of the v$ views that allow me to see what is happening in our system.
    I have a couple of generalized questions I'd like to pose (if I need a seperate thread for each question let me know and I'll repost):
    1. How do I determine what features are being used by the database (outside of the features mentioned earlier)?
    2. What should I be monitoring on a <interval> basis (hourly, daily, monthly)?
    3. What should I be looking for when I monitor? (right now i look at the alert log every day for "ORA-" entries and send alerts...)

    I'm sure I'll have many many more questions, but these are the ones I have on my mind at the current time.
    I apologize for the generality of my questions, I'm sure we'll get more granular as the discussion continues.

    We are running the following:
    Oracle 11gR2 in HA mode (Veritas clustering NOT oracle clustering or grid control) running on Solaris 10...
    Thanks in advance for helping me understand this complex beast!
  • 11. Re: New to Oracle DBA
    EdStevens Guru
    Currently Being Moderated
    975181 wrote:
    I tried to request EM for 11gR2 and I got this reply from Oracle support:
    Hello David,


    The CSI # __xxxxxxxx__ does not contain the appropriate license for the product that you are requesting.In order to receive a Version Update for< Enterprise Manager 11g Grid Control> you will need to provide a supported CSI including that product license.

    If you believe this to be a mistake at Oracle, kindly contact your Local Support Sales Representative. You could also call the toll free number for sales support in US which is800-833-3536.



    Perhaps I requested the wrong thing...This is all new to me and very very confusing and overwhelming!
    the term "oracle enterprise manager" or "OEM" gets used in a rather generic manner to refer to either Grid Control or dbcontrol. Grid is seperately licensed and installed. It provides a single point interface to all of your databases (and other resources) across the enterprise. dbcontrol provides the same basic functionality, but manages a single database. dbcontrol is free and comes with the basic database installation. All you need to do is configure it. Both products are web based.

    And while I'm at it let throw in a bit more about the docs. As an experienced SA I'm sure you understand the concept of rtfm. But there are a ton of them, so to help you focus a bit on what's important.
    Go to tahiti.oracle.com.

    Drill down to your product and version.

    <b><i><u>BOOKMARK THAT LOCATION</u></i></b>

    Spend a few minutes just getting familiar with what is available here. Take special note of the "books" and "search" tabs. Under the "books" tab (for 10.x) or the "Master Book List" link (for 11.x) you will find the complete documentation library.

    Spend a few minutes just getting familiar with what <b><i><u>kind</u></i></b> of documentation is available there by simply browsing the titles under the "Books" tab.

    Open the Reference Manual and spend a few minutes looking through the table of contents to get familiar with what <b><i><u>kind</u></i></b> of information is available there.

    Do the same with the SQL Reference Manual.

    Do the same with the Utilities manual.

    You don't have to read the above in depth. They are <b><i><u>reference</b></i></u> manuals. Just get familiar with <b><i><u>what</b></i></u> is there to <b><i><u>be</b></i></u> referenced. Ninety percent of the questions asked on this forum can be answered in less than 5 minutes by simply searching one of the above manuals.

    Then set yourself a plan to dig deeper.
    - Read a chapter a day from the Concepts Manual.
    - Take a look in your alert log. One of the first things listed at startup is the initialization parms with non-default values. Read up on each one of them (listed in your alert log) in the Reference Manual.
    - Take a look at your listener.ora, tnsnames.ora, and sqlnet.ora files. Go to the Network Administrators manual and read up on everything you see in those files.
    - When you have finished reading the Concepts Manual, do it again.
  • 12. Re: New to Oracle DBA
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    1. How do I determine what features are being used by the database (outside of the features mentioned earlier)?
    You can check "DBA_FEATURE_USAGE_STATISTICS"
    2. What should I be monitoring on a <interval> basis (hourly, daily, monthly)?
    Depend on the database usage but its good to monitor your database via OEM
    3. What should I be looking for when I monitor? (right now i look at the alert log every day for "ORA-" entries and send alerts...)
    Alertlog
    Performance
    Check Via OS command .
  • 13. Re: New to Oracle DBA
    david howard Newbie
    Currently Being Moderated
    Thank you all for your helpful advice. I'm taking a look at all of the links and documentation. I'm sure I'll have more questions, after all, answer only breed more questions.
    Thanks and have a great weekend!

    Edited by: 975181 on Dec 7, 2012 6:26 AM

Legend

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