Solved

Is there a way I can query remote DB without using remote DB.?

Posted on 2014-03-13
17
373 Views
Last Modified: 2014-03-29
Is there a way I can query remote DB without using remote DB.?

Through java/unix or by anyway.
0
Comment
Question by:sakthikumar
  • 6
  • 2
  • 2
  • +5
17 Comments
 

Author Comment

by:sakthikumar
ID: 39925746
I am planning to query production db, and based on the results I want to alert users by sending email from dev. db.

But, my dbas are not allowing me to create db links. Is there anyother way i can accomplish this.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39925763
you can send alerts from the production database itself after checking/verifying right. Why do you need to get the results into the dev database ?
0
 

Author Comment

by:sakthikumar
ID: 39926033
anything if i am changing deploying in prod db, need lot of approvals.

if this is successful, we can implement into production .
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
ID: 39926077
What you are describing is a production process running out of a non-production environment.

So, yes, it's certainly possible using a java stored procedure with a jdbc connection to your production database; but that seems ill advised.  If your notifications are important they should be part of production.  What if your development database goes down?

If you're going to use java, you don't even need the dev database at all.  Just write a java program that queries your production database and sends emails.    It could run on a production app server.  Similarly you could write a unix shell script that uses sqlplus to query data and then mail the results  using cron on a production server.

So, yes, it's possible to do within development; but you probably have other production systems that could be better suited.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39926084
my view is also more less in the lines of sdstuber here....

Do not connect the prod environments/databases to the DEV/UAT databases by whatever means it is with db links, executables developed in c or pro*c or whatever programming language as there is always risk which comes with it for the production environment.

The best thing to do here is to follow the process though it takes time & approvals as that would be the long term/strategic way of putting in things which would help even after we move out of roles & responsibilites over time :)

Thanks,
0
 
LVL 23

Expert Comment

by:David
ID: 39926229
I'd like to hear more about your actual goal, to notify people about certain result sets.  There are "best-practice" techniques already available, subject of course to your database version.  For instance, email may be sent after:

Test for some Boolean outcome (statement was successful/unsuccessful)
For some threshold or event (storage is filling up)
Results from a scheduled job
0
 

Author Comment

by:sakthikumar
ID: 39932161
This option is needed for developers(support team) only.
Just to have an alert when there is some problem.
This is not of high importance, but having something like this will be very useful.

SDSTUBER / DVZ,

Please tell me which technique we can use to accomplish this.

If Unix, can you give some sample shell scripting code,
like
1. How to connect to prod. db, execute the query fetch the results.
2. Connect back to dev. db and execute the procedure(already available)
     to send email with the results.
0
 

Author Comment

by:sakthikumar
ID: 39932163
This would be of great help to me.
0
 
LVL 23

Expert Comment

by:David
ID: 39932636
Provide a sample statement (on what condition / event do you want to send a message); which O/S and version?  

Are your DBAs using Enterprise Manager? In all environments or just prod?  Does your shop allow Developers EE access (should).
0
 
LVL 10

Expert Comment

by:dbmullen
ID: 39932641
I'm not sure I understand, I'm assuming you have a way to "deploy" code into production.  Fill out the forms, get the approvals, and deploy you code/process into production.  seems pretty basic.

regardless, if your non-prod and prod username/password match, you could do something like this connected to non-prod:

declare
  count_me number;
begin
  select count(*) into count_me from owner.table@prod_db;
if something-you-care-about then
          send-email-stuff;
end if;
end;
/
0
 

Author Comment

by:sakthikumar
ID: 39937473
I have a query, if it retrieves any record from production, then it should create an alert.

DBAs use only in production.

EE ACCESS?
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 39958011
Responding to the neglected question alert.

Where does the dev database come into question?

Just do what sdstuber suggested but without the dev database.

Create a shell script on any machine that uses sqlplus to connect to prod, run some queries, check the results and use mailx to send an alert.

Something like (just typed in...  No Unix to test with):
#/bin/sh

sqlplus username/password@prod_database > /tmp/junk.log  << EOF
    select case when count(*) = 1 then 'ERROR' end from dual;
EOF

ERROR_COUNT=`grep -c ERROR /tmp/junk.log`

if [ "$ERROR_COUNT" -gt 1 ]; then
    mailx -s "Something bad happened in production" myemail@someserver.com
fi

Open in new window


You can add as many selects from wherever you want.  Just make sure whatever code you write adds a distinct string like 'ERROR' in the output for grep to count.

I suppose you 'could' send the emai from the dev database with another quick sqlplus call that connects to the dev database but why?

somethig like this using UTL_MAIL with all the correct parameters:
sqlplus devuser/devpassword@devdatabase << EOF
exec utl_mail ...
EOF

Open in new window



All that said:
I have a stored procedure in my production databases that checks everything I deem important and the prod database itself emails me every morning.  In the procedure the subject line of the email changes from OK to PROBLEM if something is hinky.  If I see 'OK', I never have to read the email...

No email, one of two things:
-Database is down (or has major problems).
-Email server is down.
0
 
LVL 12

Expert Comment

by:Sharon Seth
ID: 39958281
The obvious question , can you even access prod db directly outside a prod env ?
0
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 39959238
Here is the short answer:  In order to query the prod database, you have to establish a connection to that database.  There are a lot of ways to establish a connection, but all of them require that you have access to a profile in prod with adequate rights to run the query.

You've already indicated that your DBAs won't allow you to create a link in the dev DB to prod.  That isn't surprising.

You have two basic categories of options:

1) Local access from the prod system.  Do you have access to the prod system?  Once logged on to the prod system, do you have adequate credentials to run queries on the prod database?  If so, then the best approach is to set up a scheduled job that runs the needed query and sends the email - all right from the prod system.

2) Remote access to prod system.  Do you have the ability to access the prod database remotely from dev (or any other system)?  If so, you could use sqlplus, or ODBC, JDBC, DRDA.

Hard to make suggestions without knowing what rights you have and what connection methods are an option.
0
 

Author Closing Comment

by:sakthikumar
ID: 39964574
Thanks  slightwv,

I am very poor in Unix, I am not able accomplish this,

Thats the reason I want someone to help me in this pinch of code.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Every server (virtual or physical) needs a console: and the console can be provided through hardware directly connected, software for remote connections, local connections, through a KVM, etc. This document explains the different types of consol…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question