SQL & PL/SQL :: Terminate Script If Remote Database Is Down?
Sep 19, 2011
I have a simple script which fetch data from another SQL server database.
The remote SQL Server database is accessed by oracle thru gateway, and a simple DB link is made in local database to the remote oracle datbase (which actually access the MS SQL Server)
The SQL script empties all the data in local database, and fills it with the fresh data from MS SQL server.
Problem is sometimes SQL Server is down, and i lose all the data in local database. The script runs automatically as schedualed job. I want to make my script intelligent enough to terminate the script if the other database is down.
View 7 Replies
ADVERTISEMENT
Aug 23, 2010
lsnrctl RELOAD [listener_name] Hope the above command wont terminate the current sessions to the database.
View 2 Replies
View Related
Jan 7, 2011
I would like to know how to terminate the space between the pages. find the attachment for more details.
View 2 Replies
View Related
Nov 20, 2012
I want to update my table base on changes in a column of a table from remote database automatically. I just want to pick some updated column then update to my own. i have made a trigger but failed.
CREATE OR REPLACE
TRIGGER TG_UPD_ID
after update OF STUD_ID on STUDENT@SCHOOLDB
for each row
begin
update MARKS set ID_ST = :new.STUD_ID where STUDID_STUDENT = :old.IDS;
end;
Error report:
ORA-02021: DDL operations are not allowed on a remote database
02021. 00000 - "DDL operations are not allowed on a remote database"
*Cause: An attempt was made to use a DDL operation on a remote database.
For example, "CREATE TABLE tablename@remotedbname ...".
*Action: To alter the remote database structure, you must connect to the remote database with the appropriate privileges.
View 11 Replies
View Related
Nov 21, 2011
I have 2 database . One is production and other is Integration and I have one table "emp" which is on integration server and a synonym of that table with the same name"emp" on production. On production we have a proc in which we are using "emp", but some time our integration server goes down, so is there any way to ping the integration server inside the proc and if it returns success then we will execute the rest of code of proc , otherwise it will send a mail to us saying that integration server is down.
View 1 Replies
View Related
Sep 3, 2009
I installed oracle 10g and developed simple software using forms 6i. Its working well as a stand alone system.
I want to share it in network. so I installed forms 6i as oracle client , copied the tnsnames.ora from the oracle installation path to forms installation path.(inside NET80/Admin folder)
When i try to connect with user name and password , it comes ORA-12203 Unable to connect to destination. I checked LSNRCTL in the database machine, it is listening.
View 11 Replies
View Related
Oct 4, 2013
i am trying to load data into a table in a remote database schema, and my files are residing on another remote Server, Server having the files does not have a DB installed, i just need to know that if its possible or not..
View 2 Replies
View Related
Feb 16, 2010
As a part of requirement need to clean up some of database objects from database. Before cleaning up need to check for dependencies with in database and also on remote database.
Is there any data dictionary in oracle which proivides information about remote dependencies.
Any other way to get a list of remote dependencies other then manual checking.
View 3 Replies
View Related
Jul 24, 2011
I am using Report Builder 10.1.2.0.2. There is a table in a remote database with synonyms/links defined. I am getting error trying to access those tables. DBA says links/synonyms are correct and I am also able to access that table from SQLTool without error.
I can do a select statement on those tables from 'SQL Query statement' in data model.
Error is generated only if I try to use those tables in 'Formula' or in function/procedure.
I don't know if this is a bug or something else.
View 28 Replies
View Related
Jul 19, 2013
I am trying to connect to a database located at my work from at home. I am running into the following issue (see command and error below):
ansichart@ansivm:~/src/perl$ sqlplus aspxdba/********@aspxp2 SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 18 21:15:09 2013 Copyright (c) 1982, 2011, Oracle.
All rights reserved. ERROR:ORA-12545: Connect failed because target host or object does not exist-- Here is my tnsnames.ora file (I have the service_name censored out for privacy reasons):-
ansichart@ansivm:~/src/perl$ cat $ORACLE_HOME/network/admin/tnsnames.oraaspxp2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orarac9-scan)(PORT = 1521)) ) (CONNECT_DATA =(SERVICE_NAME = *****************)(UR=A) ) )-
I have verified that I can establish a TCP connection to this DB server on the port specified, using the same info from the TNSnames.ora file (see below):-
---ansichart@ansivm:~/src/perl$ nmap -sT -p 1521 orarac9-scan Starting Nmap 5.21 (URL.... ) at 2013-07-18 20:53 CDTNmap scan report for orarac9-scan (10.1.13.115)Host is up (0.0088s latency).PORT STATE SERVICE1521/tcp open oracle Nmap done: 1 IP address (1 host up) scanned in 0.09 secondsansichart@ansivm:~/src/perl$--
I have another terminal opened up to a server at work and I can connect to the DB with sqlplus just fine. I verified that the record for this database on the tnsnames.ora file is the same as it is at work. In conclusion, I can connect to the Database at work, but I cannot at home, even though I am using the same connection information and verified that port 1521 is accessible on this Database server from at home. So why can't I establish a DB connection?
View 6 Replies
View Related
Nov 23, 2012
I have one PLSQL package that does a join of two tables of remote database instance via the datalink. I just wonder where the most calculation(the join) is done, local machine or remote machine? Is there any best practice to have a better performance for such configuration.
View 3 Replies
View Related
Apr 5, 2010
i need to connect to a remote database located on a different server using sqlplus without using database name in connect string.
e.g. if mydb1 (located on another database server on network)
sqlplus username/password
i am able to do this on local database but not on the remote database
View 7 Replies
View Related
May 13, 2010
I have installed Oracle 10g on one system and Oracle developer on another machine, means i have different machines for DB Server and Application server. It all working excellent inside the company premises, but if i want to access my Oracle DB and application server outside the company then it gives me problem..how to access application (forms and reports ) remotely outside the company...having same db.
View 2 Replies
View Related
Jul 28, 2013
How to know the version of Oracle and JDBC of remote databases that is accessing my database through DBLInk.
For example I have a database A , B database is using my database Through a dblink.Now I want to know the version of oracle and jdbc which working on B.
View 4 Replies
View Related
Jun 16, 2010
I have a function declared as PRAGMA AUTONOMOUS_TRANSACTION.
If i execute this function everything is fine.
If I call this function from a remote database, I have this error message:
"ORA-14551: cannot perform a DML operation inside a query".
select function('parameter') from dual;
Result: "OK"
select function@dblink1('parameter') from dual;
Result: "ORA-14551: cannot perform a DML operation inside a query"
View 14 Replies
View Related
Mar 12, 2009
I have created ".sql" script in Unix and I am trying to execute the script at SQL prompt. But I am getting the following error message after getting the output.
ORA-02019: connection description for remote database not found
Disconnected from Oracle Database 10g Enterprise Edition
Release 10.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
View 10 Replies
View Related
Jan 24, 2013
is there a way to copy WF_EVENT_T type column data from local database to remote database.
View 2 Replies
View Related
Apr 9, 2013
There are 2 Oracle databases with pseudo names Remote and Local. I have a function in Remote called FUS.F_Return_10 which simply returns 10 for testing purposes, where FUS is a schema name. In Local I want to create a procedure that will call the above function. Here's the PL/SQL:
CREATE OR REPLACE PROCEDURE TEST
(
V_COUNT OUT NUMBER
)
AS
V_FOO NUMBER(2,0);
BEGIN
[Code]...
There's a Public Database Link called PER_ACC in Local. When I try to create this procedure I get: Encountered symbol "@" when expecting one of the following: .(*%&................
where my mistake is?
View 7 Replies
View Related
May 21, 2010
We have created a new db link. But we are not able access the remote database. When we are trying to access any table using db link system is getting hanged.
We would like to know what are the parameters/permissions which affect the db link access either at data base level or at server level.
One observation we made is that that particular db link is not getting dropped when we tried to drop it.
View 2 Replies
View Related
May 22, 2013
I have created a function for knowing the status of link which connect me with remote database.function is as follows
---------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION get_status_db_link (p_db_link_in VARCHAR2)
RETURN NUMBER AS
rows NUMBER;
v_code NUMBER;
v_errm VARCHAR2(64);
[code]....
Now functin should returns 1 when link is up and it should return 0 when link down but sometime when link is down it continues showing executing it hang my pc.
View 1 Replies
View Related
Feb 18, 2011
I was considering a solution to maintain a replicated copy of a database in a remote office. However we are using SE One edition of oracle, so native support for dataguard is not available. There definitely should be some scripting solutions for this task, but I can't find any to date.
View 7 Replies
View Related
Jun 11, 2011
I created database link from bala1 database of bala user with the following command.
CREATE DATABASE LINK maddy CONNECT TO P10 IDENTIFIED BY secret554
USING 'MADDY.WORLD';
my aim is to create a dblink to access 'maddy' database objects from bala1 database.
I gave select * from v$database@MADDY.WORLD in bala user and it throwed error.
ORA-02019: connection description for remote database not found
View 5 Replies
View Related
Aug 30, 2012
I have oracle 11.2.0.3.0 in Windows 2008 server R2 64 bit version. I have installed database ASMDB with ASM ... Now i have another one machine , I want to clone my database in that machine with Non ASM file system .. Can any one tell me step by step document to do it ?
Can i keep the database name as ASMDB in another Machine ?
View 9 Replies
View Related
Nov 19, 2008
We have eight servers. One of them is for centeral management. The other seven is for development and test databases. Every night, we start a process which exports database schemas (without data) into the centeral server. An example of the process we run is just like below:
CODEcd /data1/backup
mknod exp_pipe_oracle1 p
compress<exp_pipe_oracle1>dev.dmp.Z&
exp user/****@dev file=exp_pipe_oracle1 owner=schema1,schema2,schema3 statistics=none rows=n log=dev.log
As you can see, we use @dev to connect remote database. But dump file is created in local (centeral) server. We are planning to use datapump instead of classic export in future. But I couldn't find a solution, that could take a database export in a remote server and create dump file in local. I have looked at NETWORK_LINK parameter; but it doesn't seem to work for our case.
Is it possible to backup a remote database with datapump and make it create dumpfile in local? (Of course we can use some solutions such as NFS, but we really not prefer this; if datapump has ability to remotly backup.)
View 5 Replies
View Related
Feb 23, 2012
We have access to a remote Oracle database in Germany and need to insert selected data to our local Oracle database. Our problem is that we have to do several joins (7 tables) on the remote database as well as using one where clause (always the same: P.T_LIEF_LFNT_1='12803193').
Unfortunately we do not have rights to create a view on the remote database. Is there another way to send the entire query to the remote database for processing? Also, the query returns approximately 34,000 rows.
Here is our current query:
INSERT INTO PRIMUS(PARTNO,
SORT_FORMAT,
TP_WORKSPACE,
[Code].....
View 3 Replies
View Related
Jan 8, 2013
while testing SYS remote connection in our staging server. Executing the command below is OK:
sqlplus sys/<password>@sales as sysdba
While this is mind-boggling for me...
sqlplus sys@sales as sysdba
Enter password:
ERROR:
ORA-01017: Message 1017 not found; No message file for product=RDBMS,
facility=ORA
Enter user-name:
ERROR:
Error while trying to retrieve text for error ORA-01017
I entered the correct password.I've been simulating this for a couple of times, even tried the copy-and-paste method.
View 8 Replies
View Related
Mar 15, 2011
I working on data transferring from one database server to another database server. but at starting i want to check weather remote database is connected or not?
for e.g.
a := get_remote_connection; --- Calling function for check remote connection return boolean
if a then
<call my data_transfer_proc>
else
exit;
end if;
reply.
View 4 Replies
View Related
Feb 21, 2011
I have created a remote link to a sql server db in oracle 11.0.7 works fine in sql plus. I can select tables by select * from table@linkname...the link is a public link But when I put it in the procedure it says invalid table name. then I created a public synonym for the table - i can select from the synonym in sql plus - but in the procedure it says synonym translation invalid...Also I cant grant any privileges to the synonym as it says ddl operations are not allowed on remote objects
View 1 Replies
View Related
Apr 3, 2013
We are running Oracle 11.2.x DB on Solaris platform. Looking for a way to see if I can send alert log and listener log to a remote syslog server? I know there are some other methods like Epilog and some other scripts that could be used, but wondering if there is a "built-in" method to accomplish this?
View 4 Replies
View Related
Sep 19, 2012
i am working on oracle 10g windows & all my disks are full and i want to add a datafile on other computer . how can i do it ?
View 11 Replies
View Related
