Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2010
    Posts
    8

    Unanswered: Help needed plsssss

    i am new to shell scripting..

    I had a requirement for shell script that needs to find whether the database was backed up or not in last 24 hrs .i am tring to query it in my catalog database if i query

    select owner from from dba_objects where object_name='rc_database';

    it will give me all the databases that are reciding on thar catalog database

    and i need to query

    select BACKUP_TYPE,START_TIME,COMPLETION_TIME from metat.rc_backup_piece where start_time > (sysdate -1);

    gives the back ups started since last day out put is like this for this query (metat is the one of the database name)

    in this way the loop need to run for all the database names that we get from the rc_database view ,

    the o/p for this statement looks like

    B START_TIM COMPLETIO
    - --------- ---------
    L 16-AUG-10 16-AUG-10
    D 16-AUG-10 16-AUG-10
    L 16-AUG-10 16-AUG-10
    D 16-AUG-10 16-AUG-10

    so if no lines are selected for this start_time then need to get a mail for the group.


    THANKS ALL PLEASE HELP ME OUT

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    never mind, reread request.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Check out UTL_MAIL. This allows you to send an email directly from an oracle procedure.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool What? Not another script...

    Quote Originally Posted by banumyname View Post
    i am new to shell scripting..

    I had a requirement for shell script that needs to find whether the database was backed up or not in last 24 hrs .i am tring to query it in my catalog database if i query
    So, you had the shell script requirement but now you need an SQL query?

    select owner from from dba_objects where object_name='rc_database';

    it will give me all the databases that are reciding on thar catalog database
    This query will give the user (schema) names that have an RMAN catalog.
    Unless your instalation is different, It is highly unlikely to have a separate rman catalog for each database.

    and i need to query

    select BACKUP_TYPE,START_TIME,COMPLETION_TIME from metat.rc_backup_piece where start_time > (sysdate -1);

    gives the back ups started since last day out put is like this for this query (metat is the one of the database name)

    in this way the loop need to run for all the database names that we get from the rc_database view ,

    the o/p for this statement looks like

    B START_TIM COMPLETIO
    - --------- ---------
    L 16-AUG-10 16-AUG-10
    D 16-AUG-10 16-AUG-10
    L 16-AUG-10 16-AUG-10
    D 16-AUG-10 16-AUG-10

    so if no lines are selected for this start_time then need to get a mail for the group.


    THANKS ALL PLEASE HELP ME OUT
    In order to get the correct information and assuming you do have one catalog for all your databases (or in any case one for each dev, test and prod environments), you will actually need to join these views:

    RC_DATABASE <- (DB_KEY) -> RC_DATABASE_INCARNATION
    +
    |
    (DB_KEY)
    |
    +--> RC_BACKUP_PIECE

    Then you need to check that ALL databases from RC_DATABASE view have backups and if not, send e-mail using UTL_MAIL as beilstwh suggests.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •