FAQ
Outside of reinstantiating a primary database after a failover in Data
Guard, are there any compelling reasons to use Flashback database versus
RMAN for a 10gr2 database that's only about 100GB in size? It definitely
seems like RMAN still holds the advantage.

I like flashback query and flashback table but I'm having a hard time
swallowing the need for flashback database.

Search Discussions

  • Hrishy at Feb 20, 2008 at 9:37 am
    Hi Brian

    At the place i work falshback database is used mostly
    for testing purposes so clones of production database
    are available to the devlopers very easily.

    regards
    Hrishy
    --- Brian Lucas wrote:
    Outside of reinstantiating a primary database after
    a failover in Data
    Guard, are there any compelling reasons to use
    Flashback database versus
    RMAN for a 10gr2 database that's only about 100GB in
    size? It definitely
    seems like RMAN still holds the advantage.

    I like flashback query and flashback table but I'm
    having a hard time
    swallowing the need for flashback database.
    Sent from Yahoo! Mail - a smarter inbox http://uk.mail.yahoo.com
  • Niall Litchfield at Feb 20, 2008 at 12:41 pm
    We have a training environment that is a copy of production with some setup
    for training courses done. Flashback database after the course is over is
    much nicer than re-clone. It strikes me that it might make a good "poor
    man's Real Application Testing" environment as well - at least for
    the what-if type testing that RAT enables.

    Niall
    On Feb 19, 2008 5:51 PM, Brian Lucas wrote:

    Outside of reinstantiating a primary database after a failover in Data
    Guard, are there any compelling reasons to use Flashback database versus
    RMAN for a 10gr2 database that's only about 100GB in size? It definitely
    seems like RMAN still holds the advantage.

    I like flashback query and flashback table but I'm having a hard time
    swallowing the need for flashback database.
    --
    Niall Litchfield
    Oracle DBA
    http://www.orawin.info

    --
    http://www.freelists.org/webpage/oracle-l
  • Kevin Lidh at Feb 21, 2008 at 4:19 pm
    We use RMAN for all our backups but recently we had a failover situation
    with one of our DG primary databases. We were able to flashback the
    original primary to the SCN where the failover occurred and start it up
    as the standby (with a few other steps). Then we switched back
    seamlessly. It was very handy.

    Kevin
    On Wed, 2008-02-20 at 12:41 +0000, Niall Litchfield wrote:
    We have a training environment that is a copy of production with some
    setup for training courses done. Flashback database after the course
    is over is much nicer than re-clone. It strikes me that it might make
    a good "poor man's Real Application Testing" environment as well - at
    least for the what-if type testing that RAT enables.

    Niall


    On Feb 19, 2008 5:51 PM, Brian Lucas wrote:
    Outside of reinstantiating a primary database after a failover
    in Data Guard, are there any compelling reasons to use
    Flashback database versus RMAN for a 10gr2 database that's
    only about 100GB in size? It definitely seems like RMAN still
    holds the advantage.

    I like flashback query and flashback table but I'm having a
    hard time swallowing the need for flashback database.



    --
    Niall Litchfield
    Oracle DBA
    http://www.orawin.info
    --
    http://www.freelists.org/webpage/oracle-l
  • Jeremiah Wilton at Feb 21, 2008 at 6:13 pm

    Kevin Lidh wrote:

    ...recently we had a failover situation with one of our DG primary
    databases. We were able to flashback the original primary to the SCN
    where the failover occurred and start it up as the standby... Then
    we switched back seamlessly. It was very handy.
    I think this just points out one of the reasons database flashback was a
    source of concern for the original poster. Although you were able to flash
    back the former primary back to the failover SCN, that must have meant
    throwing out all changes that had been made on the original primary
    subsequent to that SCN. Perhaps the number of such changes was very low for
    you. In failover situations, the primary may not be taking changes anyway,
    but a lot depends on how contemporary the logs on the standby are. For
    businesses with continuous operations, there are few situations in which any
    such data loss would be acceptable.

    Database flashback flashes the whole database back, negating all changes
    made subsequent to the SCN to which you revert. Much literature touts
    flashback as a good way to back out logical corruption, such as application
    and user-initiated data loss. However, because of the necessity of data
    loss when using DB flashback, I question how realistic it is for the vast
    majority of deployments.

    Most people responding to this thread have stated they use DB flashback for
    test labs and benchmarking, to allow repeatable activities on a single
    database from a known starting point. I also use it in a similar manner
    alongside DB replay, so that when I am testing a particular change such as
    an initialization parameter, I can test with identical workload iteratively
    with a variety of settings, from the starting point of that workload.

    Regards,

    Jeremiah Wilton
    ORA-600 Consulting
    http://www.ora-600.net
  • JApplewhite_at_austinisd.org at Feb 21, 2008 at 9:26 pm
    Our Developers wrote our Special Ed Student Info. application in Perl -
    about which I know zip. Without going into gory details of why, their
    code repeatedly checks for the existence of specific tables (out of the
    150,000+ tables in our Production Student Info.) by doing
    "Select Count(*) From All_Tables Where Table_Name = ''"
    whick is very inefficient, especially with 150,000 tables.

    First of all, I want them to not check for table existence and code in
    Exception Handling, but they are resistent to my suggestions.

    Second, if they don't code the Exception Handling, I want them to use a
    utility function that we have that more efficiently checks for table
    existence and returns a Boolean - True if exists, False if not exists.

    My two questions:
    1. Can Exception Handling be easily done in Perl? If so, any good
    examples I can point them to?
    2. They say that Perl can't handle Boolean values. Is that true? Any
    guidance here?

    Thanks.

    Jack C. Applewhite - Database Administrator
    Austin I.S.D.
    414.9715 (phone) / 935.5929 (pager)
  • Guang Mei at Feb 21, 2008 at 9:52 pm
    Would



    Select Count(*) From All_Tables Where Table_Name = '' and
    rownum=1



    Help?



    Guang

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of
    JApplewhite_at_austinisd.org
    Sent: Thursday, February 21, 2008 4:26 PM
    To: oracle-l_at_freelists.org
    Cc: oracle-l-bounce_at_freelists.org
    Subject: Perl Issues



    Our Developers wrote our Special Ed Student Info. application in Perl -
    about which I know zip. Without going into gory details of why, their
    code repeatedly checks for the existence of specific tables (out of the
    150,000+ tables in our Production Student Info.) by doing
    "Select Count(*) From All_Tables Where Table_Name = ''"
    whick is very inefficient, especially with 150,000 tables.

    First of all, I want them to not check for table existence and code in
    Exception Handling, but they are resistent to my suggestions.

    Second, if they don't code the Exception Handling, I want them to use a
    utility function that we have that more efficiently checks for table
    existence and returns a Boolean - True if exists, False if not exists.

    My two questions:
    1. Can Exception Handling be easily done in Perl? If so, any good
    examples I can point them to?
    2. They say that Perl can't handle Boolean values. Is that true? Any
    guidance here?

    Thanks.

    Jack C. Applewhite - Database Administrator
    Austin I.S.D.
    414.9715 (phone) / 935.5929 (pager)
  • JApplewhite_at_austinisd.org at Feb 22, 2008 at 5:06 pm
    Nope. Any query against All_Tables (or DBA_Tables or User_Tables) results
    in a Full Table Scan of OBJ$. Having 150,000+ tables and 230,000+ indexes
    in that one schema in the database results in about 420,000 rows in OBJ$.
    Any query using Table_Name causes over 6,400 Consistent Gets. Not
    horrible, but, when there are hundreds or thousands of such queries in a
    session, it adds up to a huge impact. In a "normal" database you'd
    probably not notice.

    As Sys in one of your databases, set Autotrace On in SQL*Plus, do the
    select below, and check out the Explain Plan - pretty convoluted, eh? See
    how many rows your OBJ$ has and how many Consistent Gets the query takes -
    there's a correlation, at least in my little brain.

    Thanks.

    Jack C. Applewhite - Database Administrator
    Austin I.S.D.
    414.9715 (phone) / 935.5929 (pager)

    "Guang Mei"
    Sent by: oracle-l-bounce_at_freelists.org
    02/21/2008 04:33 PM
    Please respond to
    GuangMei_at_crd.com

    To

    cc

    Subject
    RE: Perl Issues

    Would


    Select Count(*) From All_Tables Where Table_Name = '’ and
    rownum=1


    Help?


    Guang

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of JApplewhite_at_austinisd.org
    Sent: Thursday, February 21, 2008 4:26 PM
    To: oracle-l_at_freelists.org
    Cc: oracle-l-bounce_at_freelists.org
    Subject: Perl Issues


    Our Developers wrote our Special Ed Student Info. application in Perl -
    about which I know zip. Without going into gory details of why, their
    code repeatedly checks for the existence of specific tables (out of the
    150,000+ tables in our Production Student Info.) by doing
    "Select Count(*) From All_Tables Where Table_Name = ''"
    whick is very inefficient, especially with 150,000 tables.

    First of all, I want them to not check for table existence and code in
    Exception Handling, but they are resistent to my suggestions.

    Second, if they don't code the Exception Handling, I want them to use a
    utility function that we have that more efficiently checks for table
    existence and returns a Boolean - True if exists, False if not exists.

    My two questions:
    1. Can Exception Handling be easily done in Perl? If so, any good
    examples I can point them to?
    2. They say that Perl can't handle Boolean values. Is that true? Any
    guidance here?

    Thanks.

    Jack C. Applewhite - Database Administrator
  • Rich Jesse at Feb 22, 2008 at 5:50 pm
    Interesting -- I try this and I get index hits on OBJ$, albeit with a paltry
    12K rows in the ALL_TABLES view.

    What version of Oracle? This could be an issue with stats on the dictionary
    tables, either having them in 9i or not having them (or having bad ones) in
    10g.

    Rich

    p.s. I will not complain about my table layout. Ever.
    Nope. Any query against All_Tables (or DBA_Tables or User_Tables) results
    in a Full Table Scan of OBJ$. Having 150,000+ tables and 230,000+ indexes
    in that one schema in the database results in about 420,000 rows in OBJ$.
    Any query using Table_Name causes over 6,400 Consistent Gets. Not
    horrible, but, when there are hundreds or thousands of such queries in a
    session, it adds up to a huge impact. In a "normal" database you'd
    probably not notice.

    As Sys in one of your databases, set Autotrace On in SQL*Plus, do the
    select below, and check out the Explain Plan - pretty convoluted, eh? See
    how many rows your OBJ$ has and how many Consistent Gets the query takes -
    there's a correlation, at least in my little brain.

    Thanks.

    Jack C. Applewhite - Database Administrator
    --
    http://www.freelists.org/webpage/oracle-l
  • JApplewhite_at_austinisd.org at Feb 22, 2008 at 8:56 pm
    9i (9.2.0.4) on RHEL 3.0 64bit. No stats on the Data Dictionary.

    Here's what I did as Sys.

    Set Autotrace On

    Select '1' From All_Tables Where Table_Name = 'PLAN_TABLE';

    Execution Plan

    0 SELECT STATEMENT Optimizer=CHOOSE
    1 0 FILTER

    2 1 NESTED LOOPS
    3 2 NESTED LOOPS
    4 3 NESTED LOOPS (OUTER)
    5 4 NESTED LOOPS (OUTER)
    6 5 NESTED LOOPS (OUTER)
    7 6 NESTED LOOPS (OUTER)
    8 7 NESTED LOOPS
    9 8 TABLE ACCESS (FULL) OF 'OBJ$'
    10 8 TABLE ACCESS (CLUSTER) OF 'TAB$'
    11 10 INDEX (UNIQUE SCAN) OF 'I_OBJ#' (NON-UNIQUE)
    12 7 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
    13 12 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
    14 6 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
    15 5 TABLE ACCESS (CLUSTER) OF 'USER$'
    16 15 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
    17 4 TABLE ACCESS (CLUSTER) OF 'SEG$'
    18 17 INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (NON-UNIQUE)
    19 3 TABLE ACCESS (CLUSTER) OF 'TS$'
    20 19 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
    21 2 TABLE ACCESS (CLUSTER) OF 'USER$'
    22 21 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
    23 1 NESTED LOOPS
    24 23 FIXED TABLE (FULL) OF 'X$KZSRO'
    25 23 INDEX (RANGE SCAN) OF 'I_OBJAUTH2' (NON-UNIQUE)
    26 1 FIXED TABLE (FULL) OF 'X$KZSPR'

    Statistics

    7 recursive calls
    0 db block gets
    6464 consistent gets
    53 physical reads
    0 redo size
    485 bytes sent via SQL*Net to client
    652 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    1 rows processed

    Jack C. Applewhite - Database Administrator
    Austin I.S.D.
    414.9715 (phone) / 935.5929 (pager)

    "Rich Jesse"
    Sent by: oracle-l-bounce_at_freelists.org
    02/22/2008 12:32 PM
    Please respond to
    rjoralist_at_society.servebeer.com

    To
    oracle-l_at_freelists.org
    cc

    Subject
    RE: Perl Issues

    Interesting -- I try this and I get index hits on OBJ$, albeit with a
    paltry
    12K rows in the ALL_TABLES view.

    What version of Oracle? This could be an issue with stats on the
    dictionary
    tables, either having them in 9i or not having them (or having bad ones)
    in
    10g.

    Rich

    p.s. I will not complain about my table layout. Ever.
    Nope. Any query against All_Tables (or DBA_Tables or User_Tables) results
    in a Full Table Scan of OBJ$. Having 150,000+ tables and 230,000+ indexes
    in that one schema in the database results in about 420,000 rows in OBJ$.
    Any query using Table_Name causes over 6,400 Consistent Gets. Not
    horrible, but, when there are hundreds or thousands of such queries in a
    session, it adds up to a huge impact. In a "normal" database you'd
    probably not notice.

    As Sys in one of your databases, set Autotrace On in SQL*Plus, do the
    select below, and check out the Explain Plan - pretty convoluted, eh? See
    how many rows your OBJ$ has and how many Consistent Gets the query takes -
    there's a correlation, at least in my little brain.

    Thanks.

    Jack C. Applewhite - Database Administrator
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Job Miller at Feb 21, 2008 at 10:30 pm
    The Perl DBI list is a pretty good place for this stuff.


    some of the methods they use for metadata gathering are probably the culprits for the all_table queries. They are generated by DBI when they ask for specific metadata I suspect.


    exception handling is pretty basic stuff in DBI and DBD::Oracle


    there is a huge section in the man page about it.



    http://linux.die.net/man/3/dbi


    Typically "RaiseError" is used in conjunction with "eval { ... }" to catch the exception that's been thrown and followed by an "if ($@) { ... }" block to handle the caught exception. For example:
    eval { ... $sth->execute(); ... }; if ($@) { # $sth->err and $DBI::err will be true if error was from DBI warn $@; # print the error ... # do whatever you need to deal with the error }


    in regards to Boolean:


    DBD::Oracle does not explicitly support most Oracle data types. It simply asks Oracle to return them as strings and Oracle does so. Mostly. Similarly when binding placeholder values DBD::Oracle binds them as strings and Oracle converts them to the appropriate type, such as DATE, when used.


    [...]


    There are some types, like BOOLEAN, that Oracle does not automatically convert to or from strings (pity). These need to be converted explicitly using SQL or PL/SQL functions.



    **just give them a 1 or 0 output from your procedure if it exists or doesn't and don't worry about the boolean


    JApplewhite_at_austinisd.org wrote:


    Our Developers wrote our Special Ed Student Info. application in Perl - about which I know zip. Without going into gory details of why, their code repeatedly checks for the existence of specific tables (out of the 150,000+ tables in our Production Student Info.) by doing
    "Select Count(*) From All_Tables Where Table_Name = ''"
    whick is very inefficient, especially with 150,000 tables.

    First of all, I want them to not check for table existence and code in Exception Handling, but they are resistent to my suggestions.

    Second, if they don't code the Exception Handling, I want them to use a utility function that we have that more efficiently checks for table existence and returns a Boolean - True if exists, False if not exists.

    My two questions:
    1. Can Exception Handling be easily done in Perl? If so, any good examples I can point them to?
    2. They say that Perl can't handle Boolean values. Is that true? Any guidance here?

    Thanks.

    Jack C. Applewhite - Database Administrator
    Austin I.S.D.
    414.9715 (phone) / 935.5929 (pager)



    Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.
  • Jeremiah Wilton at Feb 21, 2008 at 10:39 pm

    JApplewhite_at_austinisd.org wrote:

    1.  Can Exception Handling be easily done in Perl?  If so, any good
    examples I can point them to?

    Yes.

    use DBI;
    my $dbh = DBI->connect(

    'dbi:Oracle:','', '',
    { ora_session_mode => 2, # / as sysdba
    PrintError => 0 #Suppresses the automatic error
    reporting to STDERR
    }

    ) or die "Cannot connect: $DBI::errstr\n";

    my $sth = $dbh->prepare(

    "select foo from bar"
    ) or print "Caught an exception preparing the

    cursor: $DBI::errstr\n";
    print "Duh, I'm still alive! Now I can do something since I know I caught
    an error\n";

    $dbh->disconnect;

    Regards,

    Jeremiah Wilton
    ORA-600 Consulting
    http://www.ora-600.net
  • Jeremiah Wilton at Feb 21, 2008 at 11:08 pm

    JApplewhite_at_austinisd.org wrote:

    2.  They say that Perl can't handle Boolean values.  Is that true?  Any
    guidance here?

    They could always use PL/SQL to evaluate a Boolean expression and return
    anything they wanted. But if they don't like Booleans, give them 0 and 1
    instead. They still should have exception handling. What if some error
    they haven't thought of happens?

    Jeremiah Wilton
    ORA-600 Consulting
    http://www.ora-600.net
  • Baumgartel, Paul at Feb 22, 2008 at 3:26 pm
    150,000 tables? Good grief! I admit that I know nothing about student
    information applications, but that seems like an awful lot of tables.
    Why so many?


    Paul Baumgartel
    CREDIT SUISSE

    Information Technology
    Prime Services Databases Americas
    One Madison Avenue
    New York, NY 10010
    USA

    Phone 212.538.1143
    paul.baumgartel_at_credit-suisse.com
    www.credit-suisse.com



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of
    JApplewhite_at_austinisd.org
    Sent: Thursday, February 21, 2008 4:26 PM
    To: oracle-l_at_freelists.org
    Cc: oracle-l-bounce_at_freelists.org
    Subject: Perl Issues

    Our Developers wrote our Special Ed Student Info. application in Perl -
    about which I know zip. Without going into gory details of why, their
    code repeatedly checks for the existence of specific tables (out of the
    150,000+ tables in our Production Student Info.) by doing
    "Select Count(*) From All_Tables Where Table_Name = ''"
    whick is very inefficient, especially with 150,000 tables.

    First of all, I want them to not check for table existence and code in
    Exception Handling, but they are resistent to my suggestions.

    Second, if they don't code the Exception Handling, I want them to use a
    utility function that we have that more efficiently checks for table
    existence and returns a Boolean - True if exists, False if not exists.

    My two questions:
    1. Can Exception Handling be easily done in Perl? If so, any good
    examples I can point them to?
    2. They say that Perl can't handle Boolean values. Is that true? Any
    guidance here?

    Thanks.

    Jack C. Applewhite - Database Administrator
    Austin I.S.D.
    414.9715 (phone) / 935.5929 (pager)

    Please access the attached hyperlink for an important electronic communications disclaimer:

    http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
  • JApplewhite_at_austinisd.org at Feb 22, 2008 at 4:48 pm
    Very bad database design by the Vendor - Surprise, Surprise. There are
    really only between 100 and 300 (depending on whether the Campus is
    Elementary or Secondary and what options they implement) tables used by
    the application. However, in its infinite wisdom, the Vendor chose to
    implement a set of tables per Campus per SchoolYear. So, there's a set of
    tables for School A, another set of tables for School B, and so on. We
    have about 150 Campuses, so you can see that's a lot of tables.

    Not only that, but there's a set of tables for each School per SchoolYear.
    Since we now have 8 SchoolYears' worth of data, we now have over 150,000
    tables (with over 230,000 indexes). So you can see why queries of
    All_Tables is costly - there are about 420,000 rows in OBJ$.

    If that weren't enough, each Campus actually accesses their data in local
    dBase III "databases" on Windows servers. A nightly process synchronizes
    the local dBase files with the central Oracle database tables. Now, I
    started out using dBase II on CP/M in the early 80's, but never expected
    to see dBase still around in the 21st Century! What a hoot!

    Needless to say, we're very good at using SQL to write SQL scripts that
    act on large numbers of tables, as well as producing large Export and
    Import parameter files - we've found that the limit to the number of
    tables in a Tables= list to be just short of 32,000. Our Developers (for
    "home grown" apps) are very proficient at using Native Dynamic SQL -
    necessary since they have to switch table names when they switch Campuses.

    And then there's our Financials/HR App, written largely in COBOL .... but,
    that's another Vendor and another silly story....;-)

    Jack C. Applewhite - Database Administrator
    Austin I.S.D.
    414.9715 (phone) / 935.5929 (pager)

    "Baumgartel, Paul"
    02/22/2008 09:29 AM

    To
    JApplewhite_at_austinisd.org, oracle-l_at_freelists.org
    cc

    Subject
    RE: Perl Issues

    150,000 tables? Good grief! I admit that I know nothing about student
    information applications, but that seems like an awful lot of tables. Why
    so many?

    Paul Baumgartel
    CREDIT SUISSE

    Information Technology
    Prime Services Databases Americas
    One Madison Avenue
    New York, NY 10010
    USA

    Phone 212.538.1143
    paul.baumgartel_at_credit-suisse.com
    www.credit-suisse.com

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of JApplewhite_at_austinisd.org
    Sent: Thursday, February 21, 2008 4:26 PM
    To: oracle-l_at_freelists.org
    Cc: oracle-l-bounce_at_freelists.org
    Subject: Perl Issues

    Our Developers wrote our Special Ed Student Info. application in Perl -
    about which I know zip. Without going into gory details of why, their
    code repeatedly checks for the existence of specific tables (out of the
    150,000+ tables in our Production Student Info.) by doing
    "Select Count(*) From All_Tables Where Table_Name = ''"
    whick is very inefficient, especially with 150,000 tables.

    First of all, I want them to not check for table existence and code in
    Exception Handling, but they are resistent to my suggestions.

    Second, if they don't code the Exception Handling, I want them to use a
    utility function that we have that more efficiently checks for table
    existence and returns a Boolean - True if exists, False if not exists.

    My two questions:
    1. Can Exception Handling be easily done in Perl? If so, any good
    examples I can point them to?
    2. They say that Perl can't handle Boolean values. Is that true? Any
    guidance here?

    Thanks.

    Jack C. Applewhite - Database Administrator
  • Baumgartel, Paul at Feb 22, 2008 at 5:06 pm
    Good grief, indeed. I'll think twice in the future before complaining
    about bad database/application design and/or the use of obsolete
    software. You have my deepest sympathy.


    Paul Baumgartel
    CREDIT SUISSE

    Information Technology
    Prime Services Databases Americas
    One Madison Avenue
    New York, NY 10010
    USA

    Phone 212.538.1143
    paul.baumgartel_at_credit-suisse.com
    www.credit-suisse.com


    From: JApplewhite_at_austinisd.org
    Sent: Friday, February 22, 2008 11:48 AM
    To: Baumgartel, Paul
    Cc: oracle-l_at_freelists.org
    Subject: RE: Perl Issues

    Very bad database design by the Vendor - Surprise, Surprise. There are
    really only between 100 and 300 (depending on whether the Campus is
    Elementary or Secondary and what options they implement) tables used by
    the application. However, in its infinite wisdom, the Vendor chose to
    implement a set of tables per Campus per SchoolYear. So, there's a set
    of tables for School A, another set of tables for School B, and so on.
    We have about 150 Campuses, so you can see that's a lot of tables.

    Not only that, but there's a set of tables for each School per
    SchoolYear. Since we now have 8 SchoolYears' worth of data, we now have
    over 150,000 tables (with over 230,000 indexes). So you can see why
    queries of All_Tables is costly - there are about 420,000 rows in OBJ$.

    If that weren't enough, each Campus actually accesses their data in
    local dBase III "databases" on Windows servers. A nightly process
    synchronizes the local dBase files with the central Oracle database
    tables. Now, I started out using dBase II on CP/M in the early 80's,
    but never expected to see dBase still around in the 21st Century! What
    a hoot!

    Needless to say, we're very good at using SQL to write SQL scripts that
    act on large numbers of tables, as well as producing large Export and
    Import parameter files - we've found that the limit to the number of
    tables in a Tables= list to be just short of 32,000. Our Developers
    (for "home grown" apps) are very proficient at using Native Dynamic SQL
    - necessary since they have to switch table names when they switch
    Campuses.

    And then there's our Financials/HR App, written largely in COBOL ....
    but, that's another Vendor and another silly story....;-)

    Jack C. Applewhite - Database Administrator
    Austin I.S.D.
    414.9715 (phone) / 935.5929 (pager)

    "Baumgartel, Paul"

    02/22/2008 09:29 AM

    To
    JApplewhite_at_austinisd.org, oracle-l_at_freelists.org
    cc
    Subject
    RE: Perl Issues



    150,000 tables? Good grief! I admit that I know nothing about student
    information applications, but that seems like an awful lot of tables.
    Why so many?

    Paul Baumgartel
    CREDIT SUISSE

    Information Technology
    Prime Services Databases Americas
    One Madison Avenue
    New York, NY 10010
    USA

    Phone 212.538.1143
    paul.baumgartel_at_credit-suisse.com
    www.credit-suisse.com

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of
    JApplewhite_at_austinisd.org
    Sent: Thursday, February 21, 2008 4:26 PM
    To: oracle-l_at_freelists.org
    Cc: oracle-l-bounce_at_freelists.org
    Subject: Perl Issues

    Our Developers wrote our Special Ed Student Info. application in Perl -
    about which I know zip. Without going into gory details of why, their
    code repeatedly checks for the existence of specific tables (out of the
    150,000+ tables in our Production Student Info.) by doing
    "Select Count(*) From All_Tables Where Table_Name = ''"
    whick is very inefficient, especially with 150,000 tables.

    First of all, I want them to not check for table existence and code in
    Exception Handling, but they are resistent to my suggestions.

    Second, if they don't code the Exception Handling, I want them to use a
    utility function that we have that more efficiently checks for table
    existence and returns a Boolean - True if exists, False if not exists.

    My two questions:
    1. Can Exception Handling be easily done in Perl? If so, any good
    examples I can point them to?
    2. They say that Perl can't handle Boolean values. Is that true? Any
    guidance here?

    Thanks.

    Jack C. Applewhite - Database Administrator

    Please access the attached hyperlink for an important electronic communications disclaimer:

    http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
  • Jared Still at Feb 22, 2008 at 6:02 pm

    On Thu, Feb 21, 2008 at 1:26 PM, wrote:

    My two questions:
    1. Can Exception Handling be easily done in Perl? If so, any good
    examples I can point them to?
    As others have already stated, Perl is very robust in the exception handling
    department.
    2. They say that Perl can't handle Boolean values. Is that true? Any
    guidance here?
    There's no direct boolean type, but that really should not matter.
    The 'if' statement detects 0 as false and non-zero as true.

    Here's a trivial example;

    my $sql=q{select rownum from all_users order by rownum};
    my $sth = $dbh->prepare($sql,{ora_check_sql => 0});
    $sth->execute;

    while( my $ary = $sth->fetchrow_arrayref ) {

    my $rownum = $ary->[0];
    my $bool = $rownum % 2; # % is modulus operator
    if ($bool) {

    print "this is true - $rownum : $bool\n";
    } else {

    print "this is false - $rownum : $bool\n"
    }
    }

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • David Lord at Feb 20, 2008 at 12:55 pm
    Brian,

    I don't think the choice is either-or. You can't recover from a media
    failure or a disaster using flashback database so it isn't a
    replacement for RMAN.

    Regards
    David Lord
    On 19/02/2008, Brian Lucas wrote:
    Outside of reinstantiating a primary database after a failover in Data
    Guard, are there any compelling reasons to use Flashback database versus
    RMAN for a 10gr2 database that's only about 100GB in size? It definitely
    seems like RMAN still holds the advantage.

    I like flashback query and flashback table but I'm having a hard time
    swallowing the need for flashback database.
    --
    http://www.freelists.org/webpage/oracle-l
  • Robert Freeman at Feb 21, 2008 at 8:12 pm
    There are some interesting twists on flashback in 11g that makes it even more usable. Flashback transaction, in particular, is an interesting feature. Now, one can roll back a given *committed* transaction AND (optionally) and dependent transactions. I will say that at this time Flashback Transaction *can* be a bit slow to implement due to it's dependency with Log Miner, but in the testing I've done it works quite well. There is also a nice interface into OEM that makes it's use quite easy.

    That being said, it makes the flashback features of Oracle more usable as a recovery operation.

    Cheers!!

    Robert


    Robert G. Freeman
    Author:
    Oracle Database 11g New Features (Oracle Press)
    Portable DBA: Oracle (Oracle Press)
    Oracle Database 10g New Features (Oracle Press)
    Oracle9i RMAN Backup and Recovery (Oracle Press)
    Oracle9i New Feature
    Blog: http://robertgfreeman.blogspot.com (Oracle Press)

    Original Message ----
    From: Jeremiah Wilton
    To: kevin.lidh_at_gmail.com; niall.litchfield_at_gmail.com
    Cc: moabrivers_at_gmail.com; Oracle L
    Sent: Thursday, February 21, 2008 11:13:47 AM
    Subject: RE: Flashback database versus RMAN

    Kevin
    Lidh
    wrote:

    >
    ...recently
    we
    had
    a
    failover
    situation
    with
    one
    of
    our
    DG
    primary
    >
    databases.
    We
    were
    able
    to
    flashback
    the
    original
    primary
    to
    the
    SCN

    >
    where
    the
    failover
    occurred
    and
    start
    it
    up
    as
    the
    standby...
    Then
    >
    we
    switched
    back
    seamlessly.
    It
    was
    very
    handy.

    I
    think
    this
    just
    points
    out
    one
    of
    the
    reasons
    database
    flashback
    was
    a
    source
    of
    concern
    for
    the
    original
    poster.
    Although
    you
    were
    able
    to
    flash
    back
    the
    former
    primary
    back
    to
    the
    failover
    SCN,

    that
    must
    have
    meant
    throwing
    out
    all
    changes
    that
    had
    been
    made
    on
    the
    original
    primary
    subsequent
    to
    that
    SCN.

    Perhaps
    the
    number
    of
    such
    changes
    was
    very
    low
    for
    you.
    In
    failover
    situations,
    the
    primary
    may
    not
    be
    taking
    changes
    anyway,
    but
    a
    lot
    depends
    on
    how
    contemporary
    the
    logs
    on
    the
    standby
    are.
    For
    businesses
    with
    continuous
    operations,
    there
    are
    few
    situations
    in
    which
    any
    such
    data
    loss
    would
    be
    acceptable.

    Database
    flashback
    flashes
    the
    whole
    database
    back,
    negating
    all
    changes
    made
    subsequent
    to
    the
    SCN

    to
    which
    you
    revert.
    Much
    literature
    touts
    flashback
    as
    a
    good
    way
    to
    back
    out
    logical
    corruption,
    such
    as
    application
    and
    user-initiated
    data
    loss.
    However,
    because
    of
    the
    necessity
    of
    data
    loss
    when
    using
    DB
    flashback,
    I
    question
    how
    realistic
    it
    is
    for
    the
    vast
    majority
    of
    deployments.

    Most
    people
    responding
    to
    this
    thread
    have
    stated
    they
    use
    DB
    flashback
    for
    test
    labs
    and
    benchmarking,
    to
    allow
    repeatable
    activities
    on
    a
    single
    database
    from
    a
    known
    starting
    point.
    I
    also
    use
    it
    in
    a
    similar
    manner
    alongside
    DB
    replay,
    so
    that
    when
    I
    am
    testing
    a
    particular
    change
    such
    as
    an
    initialization
    parameter,
    I
    can
    test
    with
    identical
    workload
    iteratively
    with
    a
    variety
    of
    settings,
    from
    the
    starting
    point
    of
    that
    workload.

    Regards,

    Jeremiah
    Wilton
    ORA-600

    Consulting
    http://www.ora-600.net
  • Hrishy at Feb 25, 2008 at 11:19 am
    Hi Robert

    Any idea when do we see the 11g Backup and recovery
    book added into your signature :-D

    regards
    Hrishy

    Sent from Yahoo! Mail.
    A Smarter Inbox. http://uk.docs.yahoo.com/nowyoucan.html

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedFeb 19, '08 at 5:51p
activeFeb 25, '08 at 11:19a
posts20
users13
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase