SQL Server: Creating Linked Server with Oracle

So far I have used two ways to create linked server with Oracle. By
1. MS OLE DB Provider for ODBC Driver
2. OraOLEDB.Oracle Provider

For first one steps are like
[this actions will be done where the sql server resides. assume oracle db is in remote machine]
1. Install Oracle Client with all options.
2. Reboot the machine (not sure mandatroy or not)
3. Go to tnsnames.ora and note the oracle database info you want to connect
4. Create a system DSN name through adminsitrative tool --> Data Source --> System Driver --> Add new [configure and test it oracle user name and password]
5. Right Click of Provider MSDASQL in SSMS->Server Objects->Linked Server and check the box "Allow In Process"
6. Now open SSMS and put the below queries
exec master.dbo.sp_addlinkedserver @server = N'TEST2ORACLE', @srvproduct=N'DSN_NAME', @provider=N'MSDASQL', @datasrc=N'DSN_NAME'
exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TEST2ORACLE',@useself=N'False',@locallogin=NULL,@rmtuser=N'oracleusername',
@rmtpassword='password'


For first one steps are like

1.  Install Oracle Client [here I don't suggest to do it first. Instead one can try with installing ODAC first]
link for ODAC download [read the readme file as installation guide]
http://www.oracle.com/technetwork/database/windows/downloads/utilsoft-087491.html

2. No system DSN needs to be created. Instead you can use either TNS Service name of Oracle DB or conncetion string. Below link contains a great description about all the steps

http://www.mssqltips.com/sqlservertip/1433/how-to-setup-linked-servers-for-sql-server-and-oracle-64-bit-client/

3. For trouble shooting this link may be useful

http://sqlserverpedia.com/blog/sql-server-bloggers/cannot-create-an-instance-of-ole-db-provider-%E2%80%98oraoledb-oracle%E2%80%99-for-linked-server/


Other ref:
http://msdn.microsoft.com/en-us/library/ms190479.aspx

Comments

Post a Comment

Popular posts from this blog

Restoring Master, Model and MSDB from Netbackup

Restoring System Databases got two parts: Taking the instance in single user mode and Restoring the Master Database If you need to rebuild the system databases on top of this you can use the below command: "C:\Program Files\Microsoft SQL Server\ 100 \Setup Bootstrap\ SQLServer2008R2 \setup.exe" /ACTION=REBUILDDATABASE /INSTANCENAME=SQL008 /SQLSYSADMINACCOUNTS=domain\ServiceUserName /SAPWD="yourpasswordOfSAuser" /SQLCOLLATION="Latin1_General_CI_AS" here 100 is for SQL Server 2008R2. It can vary case by case. How to take instance to single user mode: Two situation can happen: 1. Standalone Instance  2. Failover Cluster Instance 1. Standalone Instance: Go to SQL Config Manager SQL Service Properties -> Advanced Properties -> Startup Parameters put -m; in the Startup Parameters Apply and Okay - it will ask to restart the SQL Service to take effect. Stop and Start the service from Config Manager or you can stop and serv...
Read more

Oracle Job Operations

As a DBA, often I need to deal with Client's Jobs. Sometimes the jobs got hault, needs to break etc etc. Here are some useful commands for this sort of operations. ## Job Owner Schema can do this execute dbms_job.remove(444); execute dbms_job.broken(444, true); ## If Sys User needs to remove the job exec SYS.DBMS_IJOB.REMOVE(444); ## to find which jobs are running SELECT a.sid, c.serial#, a.job, a.failures, to_char(a.this_date, 'mm/dd/yyyy hh:mi pm') startdatetime, b.what FROM dba_jobs_running a, dba_jobs b, v$session c WHERE a.job = b.job AND a.sid = c.sid order by a.this_date ##let say we have only sid and serial and we need to kill the process from both OS and database. So first we need to find out the SPID. this query for any RAC database select P.SPID ,S.SID ,S.SERIAL#, S.SCHEMANAME, S.PROGRAM, S.INST_ID, S.OSUSER, S.STATUS from gv$process p, gv$session s where P.ADDR=S.PADDR and S.SID=888 and s.serial#=9293; ## kill the job al...
Read more