Professional Web Applications Themes

DATABASE LINKS - Oracle Server

I am working for the Business Investigation Department. They requested a database link to production so they can perform their research to the production environment through this database link. Their investigation consists of procedures that read files of any number between 50,000 to 1,000,000 records and perform queries against the production database - one by one. Since their customers make about twenty to thirty different requests every day, they don't have time to make up test data. Therefore they also wish to test their procedure with production data using this same databalink link. I believe this whole thing to be ...

  1. #1

    Default DATABASE LINKS

    I am working for the Business Investigation Department. They requested
    a database link to production so they can perform their research to
    the production environment through this database link.
    Their investigation consists of procedures that read files of any
    number between 50,000 to 1,000,000 records and perform queries against
    the production database - one by one. Since their customers make about
    twenty to thirty different requests every day, they don't have time to
    make up test data. Therefore they also wish to test their procedure
    with production data using this same databalink link.

    I believe this whole thing to be crazy. Can someone give me some very
    good technical explanation and/or point me to somewhere I can get a
    technical paper of something I can give to my boss to stop my boss?
    Rose Guest

  2. #2

    Default Re: DATABASE LINKS

    You have a lot of newsgroups in your post. I am removing all except one.

    --
    "Rose" <com> wrote in message
    news:google.com... 

    The last sentence above seems to indicate the Business Investigation
    Department (what is this department ? an computer/IT or client department?)
    wants to create a database link in a non-production database to a production
    database.
     

    Again trying to make sure I understand you correctly... performing queriES
    over a database link 50,000 to 1,000,000 times via a procedure (Plsql?)!
     

    Everytime you use the database link , you are hitting/impacting! the
    production environment.
     

    Is the above setup strictly for testing some new procedure in a test
    database and then later moving it to production where the production setup
    would not use a database link? If so, then it might be ok assuming EVERYONE
    knows this is obviously impacting (and probably severely impacting!) the
    production database everytime they test it in the test database.

    Seems you should propose a better solution like copying the production data
    (or a subset of it) to the test environment? Or setup the test database
    tables in production and test in production without a database link
    (assuming they don't mind impacting production because that is what the
    database link proposal will do).

    If not possible to copy production data to the test database(size?), then
    they should just create the procedure and test in the production database.
    It might be easier to setup the test database tables in the production
    database.

    Anyway, the proposal will obviously impact production with the database link
    (and this is ok?) and it will probably be more of an impact than if they
    just do it staight into the production database without a database link
    (assuming size is a problem and so data cannot be copied to the test
    database).



    Burt Guest

  3. #3

    Default Re: DATABASE LINKS

    I'd just make the link and try the queries to see how slow they are and if
    they have any effect on production apps. (It sounds like you don't have a
    test or development environment to try this in.). If the queries are slow
    or impact production apps, you could suggest an alternative - perhaps
    materialized views.


    "Rose" <com> wrote in message
    news:google.com... 


    Van Guest

  4. #4

    Default Re: DATABASE LINKS

    Rose wrote:
     
    Total insanity. Point you to something? How about bone-head IS/IT
    managment. Course 100
    for those that failed the Boolean logic question "When does 1 equal 1?"

    Unless you think you can torpedo this idiocy without losing your job
    tell them they can have
    exactly what they want as soon as they respond to a tactfully worded
    memo you send to them
    appraising them of the risk, appraising them of the fact that your
    advice is not to do it, and asking
    them to accept full responsibility for the consequences of their actions.

    Asking people to sign their names to pieces of paper on which they
    accept "full responsibility" often
    has more power than all the reasoned arguments that can be constructed.

    --
    Daniel Morgan
    http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
    http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
    damorganx.washington.edu
    (replace 'x' with a 'u' to reply)

    Daniel Guest

  5. #5

    Default Re: DATABASE LINKS

    Van Messner wrote:
     [/ref]
    The issue has nothing to do with speed. There might be zero impact on
    speed. The issue is
    entirely encapsulated in the phrase "they also wish to test their
    procedure with production data using
    this same databalink link."

    In other words, at any moment, they reserve the right to throw in a
    Cartesian join and bring the
    entire production database to its knees.

    Another possible solution Van. Look at giving them a profile that
    tightly restricts their use of resources.
    Also look at the DBMS_RESOURCE_MANAGER built-in package.

    --
    Daniel Morgan
    http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
    http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
    damorganx.washington.edu
    (replace 'x' with a 'u' to reply)

    Daniel Guest

  6. #6

    Default Re: DATABASE LINKS

    Rose wrote:
     

    I'd not do this. The risk of messing up the production database is too
    high should they do more than reading, and depending on the machines and
    the databases configuration you may run into serious performance
    problems with big queries - which both are a definite nono for the
    production database.

    What I'd do is the following: Set up another seperate machine and run a
    mirror database on it, either by doing a daily export/import from
    production to mirror or by linking the mirror database directly to the
    production database. (The latter has the additional advantage that,
    should the production machine fail, you can switch to the mirror machine
    quickly and call it the new production machine. It requires more
    administrative effort though to set up.) Then let them do their research
    on the mirror database, in an additional seperate tablespace and under
    an additional seperate user.

    Yours,
    Stephan

    FlameDance Guest

  7. #7

    Default Re: DATABASE LINKS

    You guys are all right. They obviously shouldn't be playing with production
    this way. But I've seen a lot of Oracle at a lot of companies, and not
    having test and/or devl and/or user acceptance is all too common. And not
    having a proper backup schedule, and not practicing recovery, and having
    business users with enough clout who always get their way, etc, etc.
    The op didn't say what she could control and what she couldn't. BUT... if
    all she's doing is selecting from a 50,000 to 1,000,000 table over a link 30
    times a day, she probably won't get into too much trouble.


    "FlameDance" <de> wrote in message
    news:bomh95$nt4$03$t-online.com... 
    >
    > I'd not do this. The risk of messing up the production database is too
    > high should they do more than reading, and depending on the machines and
    > the databases configuration you may run into serious performance
    > problems with big queries - which both are a definite nono for the
    > production database.
    >
    > What I'd do is the following: Set up another seperate machine and run a
    > mirror database on it, either by doing a daily export/import from
    > production to mirror or by linking the mirror database directly to the
    > production database. (The latter has the additional advantage that,
    > should the production machine fail, you can switch to the mirror machine
    > quickly and call it the new production machine. It requires more
    > administrative effort though to set up.) Then let them do their research
    > on the mirror database, in an additional seperate tablespace and under
    > an additional seperate user.
    >
    > Yours,
    > Stephan
    >[/ref]


    Van Guest

  8. #8

    Default Re: DATABASE LINKS

    little bit of a late post but...

    "they don't have time to make up test data" -- it takes less time than
    discussing why they need to use the production database for testing

    if all they need is realistic test data, just export the necessary tables
    from the production database, then import them into the 'test' database --
    the import can be rerun (after truncating the 'test' tables) anytime they
    need a predictable test environment

    "Van Messner" <net> wrote in message
    news:supernews.com... 
    production 
    30 [/ref][/ref]
    against [/ref][/ref]
    about [/ref][/ref]
    to 
    > >
    > > I'd not do this. The risk of messing up the production database is too
    > > high should they do more than reading, and depending on the machines and
    > > the databases configuration you may run into serious performance
    > > problems with big queries - which both are a definite nono for the
    > > production database.
    > >
    > > What I'd do is the following: Set up another seperate machine and run a
    > > mirror database on it, either by doing a daily export/import from
    > > production to mirror or by linking the mirror database directly to the
    > > production database. (The latter has the additional advantage that,
    > > should the production machine fail, you can switch to the mirror machine
    > > quickly and call it the new production machine. It requires more
    > > administrative effort though to set up.) Then let them do their research
    > > on the mirror database, in an additional seperate tablespace and under
    > > an additional seperate user.
    > >
    > > Yours,
    > > Stephan
    > >[/ref]
    >
    >[/ref]


    mcstock Guest

  9. #9

    Default Re: DATABASE LINKS

    "mcstock" <com> wrote in message news:<com>... 
    > production 
    > 30 [/ref]
    > against [/ref]
    > about [/ref]
    > to 
    > >
    > >[/ref][/ref]

    I am sorry if I was not clear. "The production tables average about 80
    million records". They are reading about 50,000 to 1,000,000 rows of
    data from flat files and doing queries on the production tables using
    this data as search criteria.
    Rose Guest

  10. #10

    Default Re: DATABASE LINKS

    com (Rose) wrote in message news:<google.com>... [/ref]
    [and other good comments deleted] 

    Rose,
    Is the export idea not acceptable to you for some reason?

    Just saying "about 80million records" doesn't really tell us much. Is
    that all in one table? Or maybe 10 tables of 8million records each?

    Depending on the bytes per record that can be anything from less than
    1Gigabyte to potentially terebytes. Do you have any backup process.
    From that backup, you should be able to create a development
    environment for the testers to play in. (If the backup is too big, I'd
    start wondering what plans you have for disaster recovery.)

    Are they testing just the quality of the production data? (e.g.
    verifying the existance of production records based on an external
    source?) Then the separate DB instance seems the best solution to me.

    Or are they testing performance on the production DB? If so, then how
    do they plan to factor out the overhead of the DB link?

    Or for some bizarre reason they feel the need to have a "real-time"
    view of the production data? For TESTING? I don't believe it.

    The test approach sounds more like something done in a Datawarehouse.
    There's nothing I (and obviously others here) see in the information
    you provide that justifies access to production data.

    HTH,
    ed
    Ed Guest

Similar Threads

  1. Code for email links within database generated pages?
    By Burton S. in forum Dreamweaver AppDev
    Replies: 7
    Last Post: May 13th, 04:11 PM
  2. How to make display links from mysql database???
    By Kostas in forum PHP Development
    Replies: 1
    Last Post: November 26th, 08:40 PM
  3. Making links from text in a database
    By LotusAndy webforumsuser@macromedia.com in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 16th, 09:58 AM
  4. ?Hard links, Soft links, & Aliases--Explain
    By Fred Moore in forum Mac Applications & Software
    Replies: 5
    Last Post: July 2nd, 02:33 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139