Perform Data Filtering in Oracle Link to SQL Server

,

In SQL Server, it’s easy to set up a linked server to Oracle and get data from there. You only need a UserID and a Password. If you need to filter a lot of data, it’s recommended to do the filtering where the biggest amount of data resides, in this case that is the Oracle side. In this tip we will use the Oracle alert log as an example.

If you can’t create and populate tables there, having a database link to SQL Server becomes crucial. This also allows you to easily maintain this table and be able to use this filtered information from SSRS, SSIS, or in a query from SQL Server. You will learn several steps on setting up and troubleshooting a Linux Oracle database link to consume SQL Server data. We will use two Docker images, one with Oracle and the other with SQL Server, so we can test every step taken and indicate the possible errors you may encounter.

Overview

The steps to follow are:

  1. Install Docker and create a SQL Server container
  2. Create an Oracle container
  3. Get requirements for the ODBC driver
  4. Install ODBC, verify connectivity, and create the SQL Server filtering table
  5. Set up in the Oracle container the database gateway and the database link
  6. Filter the data in Oracle

Install Docker and create a SQL Server container

Here is an excellent article from my friend Carlos Robles on how to Run and Manage SQL Server 2019 CTP 2.0 RHEL Docker Container. It contains very detailed steps and is very easy to follow. The only difference here is that I installed Docker Desktop in Windows and used the latest stable image for Microsoft SQL Server using this command:

docker run --name SqlTest -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=@Sq1T3st" -p 1433:1433 -d mcr.microsoft.com/mssql/server

Once the image has been downloaded, we can confirm it’s running with this command:

docker ps -f 'name=SqlTest'

Now, we need to know which IP address was assigned to it; this is found using this command:

docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' SqlTest

The output is: 172.17.0.3

If you need to login into this container for troubleshooting, you can do with the following command:

docker exec -it SqlTest /bin/bash

And from there you can, for example, install telnet and test the connectivity to the Oracle database (once it has been set up).

Create an Oracle container

We follow the same steps as above, but using the latest stable image from Oracle, with the command below:

docker run -d -it --name OracleTest -P store/oracle/database-enterprise:12.2.0.1

Note: You need to be logged in Docker Hub web page, agree to the terms of service for this image, and be logged in Docker Desktop using the command, docker login.

Once it’s been downloaded, we can confirm it’s running with this command:

docker ps -f 'name=OracleTest'

Now, we need to know which IP address was assigned to it; this is found using this command:

docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' OracleTest

The output is: 172.17.0.2

If you need to login into this container as root, you can do with the following command:

docker exec -u 0 -it OracleTest /bin/bash

And from there you can, for example, install telnet and test the connectivity to the SQL Server database.

Note the difference when you login as normal user using docker exec -it OracleTest /bin/bash:

Get requirements for ODBC

First, we login into the Oracle container as a normal user (to be able to login into the Oracle database). Once logged in, we will check the following:

  • OS: for Linux run cat /etc/os-release

  • OS 32 or 64 bit: uname -m. The output is: x86_64, which means it is 64-bit.
  • Database Gateway running: dg4odbc

  • Oracle 32 or 64 bit: login to SQL*Plus using sqlplus sys/Oradoc_db1@ORCLCDB as sysdba

  • Once logged in SQL*Plus, SELECT BANNER FROM V$VERSION;

  • Characterset (included in SQL Server collation): Once logged in SQL*Plus, SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET'; the output is: AL32UTF8

Install ODBC, verify connectivity, and create the SQL Server filtering table

Because Oracle, Database Gateway and Linux are all 64-bit, we can install the 64-bit ODBC provided by Microsoft in this page. The installation instructions are located in this page depending on your OS. So we login to Oracle container as root, and then run the following commands:

curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo

ACCEPT_EULA=Y yum install msodbcsql17

It will ask for confirmation on the install and upgrades, as well as the download size. Once you confirm, it will download them and ask you for a security verification:

Once you confirm, it will finish the installation:

The main components you will be using are unixODBC (diver manager) and msodbcsql17 (actual driver). You don’t need to install the other packages mentioned in the Microsoft web pages. Also, there are other ODBCs apart from the one from Microsoft:

However, I couldn’t find any difference in terms of performance. You can run your own performance metrics in Oracle by using tkprof. All of them allow you to run queries and execute stored procedures and functions. A key difference would be if any of the components (Oracle, Database Gateway or Linux) was 32-bit; in that case, you need to evaluate which component to use (unixODBC and unixODBC-devel need to be installed and compiled as 32-bit before FreeTDS can be installed; the other ODBC options are paid).

Because unixODBC was a dependency for msodbcsql17, once it has been installed, we can get its configuration by running odbcinst -j:

Once msodbcsql17 has been installed, you will see the following entry in odbcinst.ini:

And now we need to add our SQL Server entry in odbc.ini:

Now, we can test it works by using the command isql -v SqlTest sa @Sq1T3st:

And we can create our filtering table with the following commands (see how we did it for SQL Server Error Log):

CREATE TABLE [tbl_ErrorLogExclusion] ([StringValue] VARCHAR(1024));
TRUNCATE TABLE [tbl_ErrorLogExclusion];
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%ADDED INTERVAL PARTITION%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Adding new file%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Adjusting%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%advisory is%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('All previous backups and archived redo logs for this database are unusable%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%alter database%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%alter pluggable database%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('ALTER SYSTEM SET%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('ARCH:%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Archiving is disabled');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Assigning activation%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%audit_file_dest%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%audit_trail%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%automatic SQL Tuning Advisor run for special tuning task%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Autotune of undo retention is%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Buffer Cache Full DB Caching mode changing%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('CATALOG RECOVERY AREA%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Changing di2dbun%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('CLI notifier%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Completed:%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('CREATE TABLESPACE%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%Current log#%seq#%mem#%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Clearing Resource Manager%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Closing scheduler window%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Closing Resource Manager%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%compatible%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Completed redo application%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%control_files%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%create pluggable database%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Create Relation%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Database Characterset%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Database has been shutdown%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Database ID%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Database mounted%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Database name changed%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Database SCN%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Datafile conversion complete%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%db_block_size%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%db_domain%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%db_files%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%db_name%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%db_recovery_file_dest%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('DBID will be changed%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('DBNAME will be changed%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Deleted file%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Deleting old file%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%diagnostic_dest%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Dictionary check%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%dispatchers%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%Domain name:%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('drop pluggable database%including datafiles');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%enable_pluggable_database%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%encrypt_new_tablespaces%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Endian type%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%exiting (PID:%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%filesystemio_options%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Global Name changed%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('HS:%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Initial number of CPU%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%instance mounted%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%instance restart operation%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Instance shutdown complete%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%java_pool_size%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('JIT: pid%requesting stop%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%large_pool_size%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('LGWR slave%created%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('LICENSE_MAX_SESSION%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('LICENSE_MAX_USERS%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('LICENSE_SESSIONS_WARNING%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('License high water mark%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%limit based on physical memory%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('LOGSTDBY%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Lost write protection%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Machine:%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Modify parameter file and generate a new password file before restarting%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Network throttle feature%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%nls_language%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%nls_territory%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('No Resource Manager plan active');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Node name:%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('NOTE:%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Number of processor%in the system is%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Opatch validation is skipped for%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%open_cursors%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Opening pdb with no Resource Manager plan active%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('ORACLE_BASE%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('ORACLE_HOME:%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Oracle Database%Release%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Oracle running with ofslib:%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('OS process%idle for%exiting%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%Per process system memlock%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%pga_aggregate_target%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%physical memory size is%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Ping without log force is disabled%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Pluggable Database%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Post plug operations are now complete%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%processes%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Recovery of Online Redo Log:%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Release:%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%remote_login_passwordfile%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('replication_dependency_tracking%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('RESETLOGS after complete recovery%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Resetting resetlogs activation%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Resize operation completed for file%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Setting recovery target incarnation to%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Setting Resource Manager%plan%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%sga_target%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%shared_pool_size%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%SHARED GLOBAL AREA (SGA)%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Shared IO Pool defaulting to%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Shared memory segment for instance monitoring created%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Shutting down archive processes%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Shutting down instance%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Started redo application at%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Started redo scan%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%started with pid=%, OS id=%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%starting (PID:%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Starting background process%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Starting datafile conversion%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Starting ORACLE instance (normal)%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('starting up%dispatcher%for network address%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Starting up%server%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Stopping background process%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Stopping Emon pool');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%streams_pool_size%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Succesful%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Successful%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%Successfully onlined Undo Tablespace%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('SYS auditing is enabled%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('System name:%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('System parameters with non-default values:%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%Thread%advanced to log sequence%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Thread%cannot allocate new log%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Thread%closed at log sequence%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Thread%opened at log sequence%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Unable to obtain current patch information due to error: 20001%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%undo_tablespace%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Undo initialization finished serial%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Using default pga_aggregate_limit of%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Using%parameter default value as%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Using parameter settings in%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%utility finished successfully%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%utility finished succesfully%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('%utility started%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Verifying%file header compatibility for tablespace%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('Version:%');
INSERT INTO [tbl_ErrorLogExclusion] VALUES ('VM name:%');

To exit isql enter the command quit.

Set up the database gateway and the database link in the Oracle container

Anything that gets set up here, even when the tests indicate that it is working, doesn’t mean you will be able to get data from SQL Server. It is until you query the database link that you will be able to confirm it. You must be logged in as normal user in the Oracle container. Then, you need to locate the file libodbc that gets installed with unixODBC using the command whereis libodbc:

We need to copy the example interface between Database Gateway and unixODBC with the following command:

cp $ORACLE_HOME/hs/admin/initdg4odbc.ora $ORACLE_HOME/hs/admin/initSqlTest.ora

Note: It must start with “init”, end in “.ora”, and in the middle it must match exactly the name of the Service ID in other Oracle files. For clarity, I will be using “SqlTest” everywhere, that’s why the name in this case is “initSqlTest.ora”. Then we edit the file and enter the following values:

SqlTest is the name in /etc/odbc.ini, which is the file where we added our SQL Server ODBC connection information. /usr/lib64/libodbc.so is the file that got installed when we installed unixODBC (driver manager). We set the trace level to 4 to debug possible issues.

Before editing the next file, we need to know the value of the ORACLE_HOME by typing: $ORACLE_HOME

And we need to know where the listener is configured with the following command: lsnrctl status

We can see it is located in $ORACLE_HOME/admin/ORCLCDB/listener.ora, so we edit this listener file with the following values:

We added the last eight lines starting on “SID_LIST_LISTENER=”. In the line that starts with “ORACLE_HOME” you must enter the full path, and the value for PROGRAM must be dg4odbc (Database Gateway, previously known as Heterogeneous Services). Once it has been modified, we need to restart the listener with the command lsnrctl reload:

We need to confirm the new listener is working by running the command lsnrctl status and search for an entry named SqlTest:

The next file to modify is named tnsnames.ora, which is located in $TNS_ADMIN/tnsnames.ora, you need to enter the following values:

We added the last six lines starting on “SqlTest=”. Basically we’re adding an entry that is listening on the local machine and the Oracle port, named SqlTest, that is using Heterogeneous Services. Once it has been added, we can confirm it works with the command tnsping SqlTest:

And finally, we need to connect to Oracle using the command sqlplus sys/Oradoc_db1@ORCLCDB as sysdba. Then we create the database link with the command CREATE PUBLIC DATABASE LINK SqlTest CONNECT TO sa IDENTIFIED BY "@Sq1T3st" USING 'SqlTest';

And we can query the SQL Server table with the command SELECT * FROM tbl_ErrorLogExclusion@SqlTest;

Note how the previous set up succeeded, but we’re unable to retrieve data from SQL Server. But because we enabled logging in initSqlTest.ora, we can view the file /tmp/sql.log to see what is the issue:

In line 13 we see the following: Str In = [DNSletUDS;W=S13t][length = 31]

But we’re expecting to see the following: Str In = [DSN=SqlTest;UID=SA;PWD=********][length = 31]

You can see every second character is missing, it’s because it expects the DSN sent as DOUBLE BYTE although it isn’t.

This is a problem with an initialization parameter in initSqlTest.ora, as my Oracle DBA friend, Marvin Gonzalez, helped me to troubleshoot, and this is due to the character set being set as AL32UTF8, so we need to edit this file and add the parameter HS_LANGUAGE as follows:

And now when we query the data from the linked server, it succeeds. We need to remove tracing in initSqlTest.ora as this adds overhead in the data retrieval, and it ends as follows:

Filter the data in Oracle

We need to be logged in as a normal user in the Oracle container and connect to Oracle using the command sqlplus sys/Oradoc_db1@ORCLCDB as sysdba. Then we can run the following query (note that 3/24 denotes the past three hours):

SELECT MAX(A.ORIGINATING_TIMESTAMP) ORIGINATING_TIMESTAMP, A.MESSAGE_TEXT
  FROM V$DIAG_ALERT_EXT A
LEFT JOIN tbl_ErrorLogExclusion@SqlTest T ON A.MESSAGE_TEXT LIKE T."StringValue"
 WHERE T."StringValue" IS NULL
   AND A.ORIGINATING_TIMESTAMP > CAST(SYSDATE-3/24 AS TIMESTAMP)
GROUP BY A.MESSAGE_TEXT;

In your environment you can do more complex stuff, like joining from several tables from both Oracle and SQL Server, create complex PL/SQL, execute SQL Server stored procedures and functions, execute an Oracle package from SSIS, generate reports using Oracle data in SSRS, or consume the data in a T-SQL statement using a linked server to Oracle.

Further reading

 

Rate

5 (1)

Share

Share

Rate

5 (1)