> General > ALERT: Database > Connect ALERT to Oracle 11g 🔒

Connecter ALERT à Oracle 11g 🔒

Introduction

Creating a connection to an Oracle data base can be used export data from Alert to the data base. It can also be used in a script running in Alert for a variety of purpose.
It will be necessary to configure the connection both

Configuration Oracle

Start by creating a user specifically for Alert. In this guide we will name this user ALERT.

How to install Oracle Data Base on Oracle Linux 7.5: insert English tutorial: https://www.youtube.com/watch?v=5V93VwQnmew

Our server is vmwOracle11. Oracle's web interface can be accessed at the following address: https:// vmwOracle11:1158/em/

In order to create a new user or a new tablespace you must log into a account with high enough privileges. We will log in as system administrator in this guide

You can create a tablespace in Storage/Tablespace

Before creating a tablespace, a file must be created and selected. The data will be stored in this file.

In order to create or modify users, one must go to "Server/Security/Users" and click on "Create" to create a new user.

The following screen grab shows how to create a new user.

We have now created a user called "Alert" with the password "alert". The default tablespace for this user is the one we have defined.

Once a user has been created, its properties must be modified as follows:

General Properties:

User's "Role"

User system privileges:

The newly created user now has all the necessary privileges.

We should now configure the PC on which Alert was installed.

Configuration of Alert's PC

Oracle 11g must first be installed.

Oracle Client Configuration

Once the client has been installed, connection to the base must be configured. Launch the program "Oracle Net Manager" in ""Start / Programs / Oracle - OraClient11g_home1 / Configuration and migration tools / Net Manager"..

The service must be given a name. Make sure to take note of this name as you will need it when creating the UDL

The next window showcases a connection to the base created in our example. Here we named our service "mmioracle", this name will be used for the UDL.

The "Service Name" is usually the name of the data base instance as shown bellow. It is advised to test the connection every step of the way to make sure the connection is still functional.

Please note the name given to the service.

UDL File

In order to initiate the connection from Alert we will use a UDL file. This will allow us to test the connection independently from Alert.

We will create a UDL file supporting two types of connections:

A connection for Alert's export data base (OLEDB)
A connection for the message processor (ODBC)

A UDL can be created simply by right clicking, creating a new text file and renaming it with a .udl extension.

We will name our example file OraDB.udl.

Once the file has been created, double click it (note 64/32-bit case) on it. Select the "Supplier" tab. The following window opens:

We're now going to choose the data provider that will depend on the use of Alert data.

OLEDB connection, Export database

The prefered connector is "Oracle Provider for OLE DB". Select it and click "next".

A new configuration box appears. Connexion parameters matching the data source previously given in Oracle must then be entered alongside the login and password of the newly created Oracle user.

For the purpose of this example, our data source will be "mmioracle11".

We will use "Alert" as our user login.

You can test the connection by clicking "Connection test". The test must return a positive result.

It is the UDL file that will have to be provided in Alert.

ODBC Connection and Message Processor

The correct connector that should be used for an ODBC connection is "Microsoft OLE DB Provider for ODBC Drivers".Next, we're going to use a connection string which will be the information to be filled in for Message Processor connections. Make sure you run the configuration as administrator.

Here we describe how to set up the connection string from scratch. If an ODBC data source for the desired server is already configured, creating the connection string will be simpler.

After validating the supplier, the following screen appears:

Select "Use connection string" and click on "Create...".

Then select the "Machine data source" tab.

No source corresponding to our server exists, so we're going to create a new machine data source. To do this, click on "New...".

The following wizard appears:

Select "System data source" and click "Next >". Then select "Oracle in instantclient_11_2": Version depends on installed drivers.

Validate with "Next >", then "Finish".

Finally, give the data source a name (here AlertOracle) and choose the name of the Oracle service, in our example MMIORACLE11.

We also enter the Oracle user name (the server's IP address is already included in the mmioracle11 source).

You can test the configuration using the "Test Connection" button.

The new data source has been added to the list of machine data sources. Simply select it.

And press "OK".

To complete the connection string, enter the Oracle server login and password.

For our example, the login defined on the database is "Alert" with the password "alert1".

The window looks like this:

The connection can be checked by pressing the "Test connection" button.

The valid connection string can then be used for a message processor.

To find out more

Oracle stop - startup on Linux

How do I stop and start Oracle on a Linux machine?

Start

The database has been correctly installed and configured. After booting the machine, to launch the base do :

Log into linux with an operator who has rights to the database (in our example, "oracle").

With the correct password:

Use SQL commands in the Oracle bin directory.

Start the SQL console :

Make « startup »

Exit SQL console. « exit »

Start services :

« ./lsnrctl »

Console LSNRCTL

« LSNRCTL> start »

Exit

« LSNRCTL> quit »

Then press "emctl start dbconsole".

Stop

Before stopping the Linux machine, the database and associated services must be stopped.