Browsing:

Category: Oracle

How do I completely uninstall a Oracle server

Uninstalling Oracle is not a daily event.

well, maybe if you are an oracle consultant of course.  But for me it was the first time, I am more an MSSQL person, with some Oracle knowledge. So, I will guide you through a very decent uninstall which is quite effective and save for the network.

First we stop all the Oracle services, next use the OUI to uninstall your Oracle product. You have to select all products that need to be removed. In our case, we just remove everything.

Second, we need to manually remove all the registry keys

HKEY_LOCAL_MACHINE\SOFTWARE
HKEY_LOCAL_MACHINE\SOFTWARE\Classes remove keys beginning with Ora/ORCL
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services  HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\ApplicatioHKEY_LOCAL_MACHINE\SOFTWARE\ODBC remove all keys related with the "Oracle ODBC Driver"

Now we need to remove Oracle from your environment Variables and remove references under 'PATH'. e.g."I:\ora9idbcl\bin;I:\oradev6\BIN;D:\oracle\product\10.2.0\BIN"

Delete the oracle icons from StartMenu/Programs and ProgramFiles and remove any oracle related .ini files left behind in the Windows directory.

Then you reboot your computer.

Last steps are to remove the last bits of oracle left on your server, after this reboot you can start cleaning up or archive all Oracle files, backups etc.

Maybe you have a windows task running, in our case a task for a hotbackup has to be stopped and some analyze tasks.

Just run a search on 'ora' on the server to see if any vital oracle thingies have been left behind and you're done!


Recover your corrupt datafiles in oracle – ora-00376

Everyone in softwareland has been there, you're at home and people start calling you about the application shooting error messages with no clear reason. you go through all your logfiles, Windows application server, eventviewer and end up in your database logs, and then find out your tables give an error when trying to retrieve the data. This just happened to me, so i wrote this small tutorial for people encountering this unexpected ora-00376 and ora-01110 'file cannot be read at this time', we can fairly easy recover the corrupt datafiles without having to take the database offline.

.

use the statement: select * from v$datafile; to read the status on your database files. In our case it says some files are in recovery mode and are therefore unreadable. A possible cause for this could be backup software locking the database files or a linked server job from mssql keeps them occupied.

In my case, I found 5 files in recover mode:
E:\ORADATA\DAT02DATA.DBF
E:\ORADATA\IDX01.DBF
E:\ORADATA\UNDOTBS01.DBF
E:\ORADATA\SYSAUX01.DBF
E:\ORADATA\DAT02DATA01.DBF

Now that we found the cause for some tables and views in the database not being available, it's time to solve this. We fire up the sqlPlus toolie and connect to the database ( you could also use cmd, if sqlplus is not installed).
run: recover datafile 'E:\ORADATA\DAT02DATA.DBF';
Sqlplus will then ask you for a filename, auto or cancel. We choose to specify the filename, since auto option won't work, since it can't find the archive files in the place it suggested, we give in the location and filename of the archive file that it needs in order to do a recovery. As you can see in the example below, it's looking for the archive files in a different location then where the archive files actually are located. enter the correct location. E:\orabck\oraarch\arc20637_0639339860.001

After you have entered all the correct file locations suggested for the archive files, the message: log applied media recovery complete will be displayed.
we run the statement select * from v$datafile; again and you'll see the status of the file is now offline.

All we need to do now is bring the file back online, to achieve this we run the statement:
alter database datafile 'E:\ORADATA\DAT02DATA.DBF' online;

Repeat the steps above for all the files in recovery status and the database is succesfully recovered again and running.

The advantage of this recovery is that we don't have to bringt the database offline and depending on what files are in recovery mode, people can still access the database and even parts of the application.


Linked sever mssql to Oracle part 2

In addition to my former blog post Create a Linked server, I will now show you what you can do with the linked server and how to run scheduled jobs between the databaseservers.

In our last post we created a link between a Oracle 10G database and MSSQL2008 server. Now that we established the connection we can schedule jobs in the mssql database to get the data synced with the oracle server.

You can view and query your oracle database in linked server you created under mssql, but you can also send jobs and data to the oracle server

Create a job step and insert a query to specify the data that needs to be copied. We want to insert new and modified organisation data to a financial database.

The code for my Scheduled job:

 DECLARE @dtLastRun DATETIME
 DECLARE @dtNow DATETIME
 SELECT @dtLastRun = CAST(EV000_ALPHANUM_VALUE AS DATETIME) FROM EV000_SYS_PARMS WHERE EV000_APPLICATION='AA' AND EV000_CODE='ZZZ'
 SET @dtNow=getDate()
 BEGIN TRY
 INSERT INTO DECADE_INTERFACE..FinancialDB_RELATIES
 SELECT
 CASE ISNULL(EV870_KEYWORD_2, '') WHEN ''
 THEN EV870_ACCT_CODE
 ELSE EV870_KEYWORD_2 END AS RELA_ID,
 REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, EV870_ENTER_STAMP, 126), '-', ''), ':', ''), 'T', '') AS DATUM_INVOER,
 CASE
 WHEN EV870_AR_DESIG = '0' THEN 'C'
 WHEN EV870_AP_DESIG = '0' THEN 'D'
 ELSE 'B' END AS SOORT_RELA,
 EV870_ALPHA_SEARCH_KEY AS ZOEKNAAM,
 DECADE_LAND_CODE AS TAAL_ID,
 DECADE_LAND_CODE AS LAND_ID,
 0 AS IND_GEBLOKKEERD,
 LEFT(EV870_NAME, 40) AS NAAM_RGL_1,
 '' AS NAAM_RGL_2,
 LEFT(CASE WHEN EV878_ORG_CODE IS NULL THEN ISNULL(EV870_ADDRESS_L1, '')
 ELSE ISNULL(EV878_ADDRESS_L1, '') END, 40) AS STRAATNAAM_RGL_1,
 LEFT(CASE WHEN EV878_ORG_CODE IS NULL THEN ISNULL(EV870_ADDRESS_L2, '')
 ELSE ISNULL(EV878_ADDRESS_L2, '') END, 40) AS STRAATNAAM_RGL_2,
 LEFT(CASE WHEN EV878_ORG_CODE IS NULL THEN ISNULL(EV870_ADDRESS_L3, '')
 ELSE ISNULL(EV878_ADDRESS_L3, '') END, 40) AS STRAATNAAM_RGL_3,
 LEFT(CASE WHEN EV878_ORG_CODE IS NULL THEN ISNULL(EV870_POSTAL_CODE, '')
 ELSE ISNULL(EV878_POSTAL_CODE, '') END, 15) AS POSTCODE,
 LEFT(CASE WHEN EV878_ORG_CODE IS NULL THEN ISNULL(EV870_CITY, '')
 ELSE ISNULL(EV878_CITY, '') END, 40) AS PLAATSNAAM_RGL_1,
 '' AS PLAATSNAAM_RGL_2,
 LEFT(CASE WHEN EV878_ORG_CODE IS NULL THEN ISNULL(EV870_ADDRESS_L1, '')
 ELSE ISNULL(EV878_ADDRESS_L1, '') END, 15) AS POSTBUSNR,
 LEFT(CASE WHEN EV878_ORG_CODE IS NULL THEN ISNULL(EV870_POSTAL_CODE, '')
 ELSE ISNULL(EV878_POSTAL_CODE, '') END, 15) AS POSTBUS_POSTCODE,
 LEFT(CASE WHEN EV878_ORG_CODE IS NULL THEN ISNULL(EV870_CITY, '')
 ELSE ISNULL(EV878_CITY, '') END, 40) AS POSTBUS_PLAATSNAAM_RGL_1,
 '' AS POSTBUS_PLAATSNAAM_RGL_2,
 LEFT(ISNULL((SELECT TOP 1 CC810_COMM_CODE FROM CC810_COMM_DIR
 WHERE CC810_ORG_CODE = EV870_ACCT_CODE
 AND CC810_ACCT_CODE = EV870_ACCT_CODE
 AND CC810_COMM_TYPE = 'MAIN'), ISNULL(EV870_MAIN_PHONE, '')), 15) AS TELEFOONNR,
 LEFT(ISNULL((SELECT TOP 1 CC810_COMM_CODE FROM CC810_COMM_DIR
 WHERE CC810_ORG_CODE = EV870_ACCT_CODE
 AND CC810_ACCT_CODE = EV870_ACCT_CODE
 AND CC810_COMM_TYPE = 'FAX'), ISNULL(EV870_MAIN_FAX, '')), 15) AS FAXNR,
 '' AS CONTACTPERSOON,
 0 AS IND_FOUT
 FROM EV870_ACCT_MASTER
 LEFT OUTER JOIN EV878_ACCT_ADDRESSES
 ON EV878_ORG_CODE = EV870_ORG_CODE
 AND EV878_REF_CODE = EV870_ACCT_CODE
 AND EV878_ADDR_TYPE = 'F'
 LEFT OUTER JOIN U_D_VERTAALTABEL_LANDEN
 ON UNGERBOECK_LAND_CODE = EV870_COUNTRY
 WHERE EV870_CLASS = 'O'
 AND (EV870_AR_DESIG != '0'
 OR EV870_AP_DESIG != '0')
 AND (EV870_ENTER_STAMP >= @dtLastRun
 OR EV870_UPD_STAMP >= @dtLastRun)
 UPDATE EV000_SYS_PARMS
 SET EV000_ALPHANUM_VALUE = CAST(@dtNow AS VARCHAR)
 WHERE EV000_APPLICATION='AA' AND EV000_CODE='ZZZ'
 END TRY
 BEGIN CATCH
 PRINT 'ERROR!'
 END CATCH

We run this job every 30 minutes daily and our job is done, that was easy!

We can write as much jobs and add multiple linked servers as you like.


Creating a linked server Oracle to MsSQL and common Error 7302 Could not create an instance of OLE DB provider ‘MSDAORA’

In some cases it can be handy if you can query a database from one database server to the other and copy data and tables.

Now the fun part is, we can use this on different databases as well, like Oracle, MySql e.g. how cool is that!
Let's set up a linked server to Oracle.
If you don't have oracle available, you can download and install the Oracle Express edition, which we can get for free from the Oracle site.

After you have succesfully installed Oracle server, fire up MSSQL.

First we are going to create a new database for this Linked Server Project, let's call it the 'FunFactory' a great program from the '80.

Insert some tables and rows and we're done! Now we open the Server Objects in the Object Explorer and right click 'add new linked server':

Now it's very important to fill in the right credentials: You need to connect Oracle on it's TNS name, for example, Oracle Express the default is XE, now press 'script from', to see the script that will execute when you press ok.

Ok, enought with the boring GUI, we are going to create another linked server, but this time we script it:

DECLARE @vcLinkedServer VARCHAR(255)
DECLARE @vcLocalUser VARCHAR(255)
DECLARE @vcRemoteUser VARCHAR(255)
DECLARE @vcRemotePass VARCHAR(255)
DECLARE @vcRemoteTNS VARCHAR(255)
DECLARE @ncStatement NVARCHAR(MAX)
SET @vcLinkedServer = 'FunFactory'
SET @vcLocalUser = SYSTEM_USER
SET @vcRemoteTNS ='XE'
SET @vcRemoteUser = 'Fun'
SET @vcRemotePass = 'Factory'
/*
SET @ncStatement = 'USE MASTER
GRANT ALTER ANY LINKED SERVER TO ' + SYSTEM_USER + '
USE ' + DB_NAME()
EXEC sp_ExecuteSQL @ncStatement
*/
BEGIN TRY
EXEC sp_DropServer @vcLinkedServer, 'droplogins'
END TRY
BEGIN CATCH
END CATCH
EXEC sp_AddLinkedServer @vcLinkedServer, 'Oracle', 'OraOLEDB.Oracle', @vcRemoteTNS
EXEC sp_AddLinkedSrvLogin @vcLinkedServer, 'FALSE', @vcLocalUser, @vcRemoteUser, @vcRemotePass
DECLARE @vcTableName VARCHAR(255)
DECLARE @vcFieldName VARCHAR(255)
DECLARE @vcFieldType VARCHAR(255)
DECLARE @vcColumnsDest VARCHAR(MAX)
DECLARE @vcColumnsSrc VARCHAR(MAX)
DECLARE @iIsNullable INT

That's better! To check if both linked servers excist, we do a spfile check:

exec sp_helpserver

Great, this part seems to work!

Now we need to make sure we can establish a connection to Oracle
make sure Oracle is installed properly or it will result in some common errors:

Error 7302: Could not create an instance of OLE DB provider 'MSDAORA'

There is 2 things you need to do here:

Check if provider oraOLEDB is available

And make sure ‘Allow In process’is checked in the provider options

 

If it still does not work:
Make sure correct OLEDB (and oracle client) are installed. If not, search for ODAC on the Oracle site.

http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

In our case SQL is on a 64 bit server, so we choose the ODAC x64 zip file
In order to get it installed, we need an ‘oracle way’ just running the install.bat won’t do the trick, or oracle wouldn’t be oracle 😉
We run the install from cmd or Powershell:

C:\Teletraan_I\Mellie>D:
D:\>cd ODAC112021_x64
D:\ODAC112021_x64>install
D:\ODAC112021_x64>install all d:\oracle orahome
D:\ODAC112021_x64>cd..
D:\>cd oracle
D:\oracle>configure
D:\oracle>configure all orahome

On last thing could stand in your way, a classic oraclient failure:
The path is not inserted in the environmental settings:
Go the PATH and add the location of the newly installed oracle home.

PATH:D:\oracle\bin;D:\oracle;

Now the Oracle dir is created on your computer and the linked server is airing, Hooray! It kinda feels like swearing in the Church, installing an oracle client on your SQL server, but what needs to be done….

You can now write queries to copy oracle tables over to your SQL server and what ever you want to do with data in a database!!! I absolutely love this Linked server thingy!

In my next Linked Server post, I will show you some cool stuff that you can do with data in Linked servers! note: here is the link: Creating jobs and queries with linked server

Note:
After installing a new service pack to the mssql server, the linked server stopped working, giving an 7302 error 'Could not create an instance of OLE DB provider 'MSDAORA'. If your LS stops working after it has been working fine for months and all the tips above are working, i found a quick work around to get your Linked server to work again, just simply check the dynamic parameter box and save and then uncheck it again. Weird but it does work!

If this quick fix does not do the trick, I wrote a more detailed troubleshoot on linked server here: Troubleshoot linked server


Create System DSN with oracle

Let’s say we want to connect our oracle database server to this new local console application we are going to build, we will first need to create a System DSN with oracle via Window’s ODBC.

We will do a tnsping command in a cmd box - c:\>tnsping network.domain (for example Melliepc.mellie) to see if our connection does exist.

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = Melliepc)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = Myoradb))) OK (220 msec)

Next step is to install the Oracle Instance Client Basic on our machine and don't forget to install the Oracle ODBC
C:\Program Files\Oracle\instantclient_10_2\odbc_install.exe] as administrator (this is to Register Oracle ODBC).
That was not so hard, now we are gonna create a new system-DSN:
go to your Configuration screen –> administrative tools (ODBC) as administrator.

Choose System-DSN -->Add new, select the correct source, in my case this will be Oracle instantclient_10_2.

ora_dns

All that is left now is to fill in the correct values for our database and click the test button, if successful, the connection to our database is ready. Yay!

ora_dns1

Now we can start using data from the oracle database in our project, What a joy!