Oracle Database Gateway Setup On Linux (For MSSQL Connections)

Here are the steps for MSSQL Connections.

  • Download the Compatible Oracle Gateway Version depending on your database version.
  • Setup via GUI. Be sure that ORACLE_BASE is as same as existing database, but ORACLE_HOME should be different than database's ORACLE_HOME. 
    database oracle_home: /oracle/db/19/dbhome_1
    gateway oracle_home: /oracle/db/19/sql_gateway

  • Specify only for SQL Server tools as that you'll need, ignore the rest. You can enter MSSQL Database ip, servername, port and databasename.
  • On the next step where you have to configure listener, assign different port than existing database port (for example, GATEWAY_LISTENER)
  • After GUI Setup has been finished, copy the init file like below and edit it. For the example, QADB is our remote MSSQL database name.

[root@testserver admin]# cd /oracle/db/19/sql_gateway/dg4msql/admin

[root@testserver admin]#cp initdg4msql.ora initQADB.ora

[root@testserver admin]# vi initQADB.ora



#

# HS init parameters

#

HS_FDS_CONNECT_INFO=[<targetserver>]:<targetport>//QADB


  • Insert lines below at Gateway's listener.ora file. Take care that there is already a second link example, thus you may only insert  only the first one.



SID_LIST_LISTENER_GATEWAY =

(SID_LIST =

(SID_DESC =

(SID_NAME=QADB)

(ORACLE_HOME=/oracle/db/19/sql_gateway)

(ENVS=LD_LIBRARY_PATH=/oracle/db/19/sql_gateway/dg4msql/driver/lib:/oracle

/db/19/sql_gateway/lib)

(PROGRAM=/oracle/db/19/sql_gateway/bin/dg4msql)

)

(

SID_DESC =

(SID_NAME=MYDBLINK)

(ORACLE_HOME=/oracle/db/19/sql_gateway)

(ENVS=LD_LIBRARY_PATH=/oracle/db/19/sql_gateway/dg4msql/driver/lib:

/oracle/db/19/sql_gateway/lib)

(PROGRAM=/oracle/db/19/sql_gateway/bin/dg4msql)

)

)



  • Edit the database's tnsnames.ora file.

            oracle@testserver admin]$ cd $ORACLE_HOME/network/admin

[oracle@testserver admin]$ vi tnsnames.ora





QADB =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = testserver.sm.gov.tr)(PORT = 1528))

)

(CONNECT_DATA =

(SID = QADB)

)

(HS = OK)

)





MYDBLINK =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = testserver.sm.gov.tr)(PORT = 1528))

)

(CONNECT_DATA =

(SID = MYDBLINK)

)

(HS = OK)

)

  • Restart the gateway listener service. Before restart, you need to set some variables in order to run command.

            [oracle@testserver admin]$ export ORACLE_HOME=/oracle/db/19/sql_gateway

[oracle@testserver admin]$ export TNS_ADMIN=/oracle/db/19/sql_gateway/network/admin

[oracle@testserver admin]$ export PATH=/oracle/db/19/sql_gateway/bin:$PATH

[oracle@testserver admin]$ lsnrctl stop LISTENER_GATEWAY

[oracle@testserver admin]$ lsnrctl start LISTENER_GATEWAY


  • Create database link over sqlplus after setting database variables via applying correct path variables.

[oracle@testserver ~]$ cd

[oracle@testserver ~]$ . .bash_profile

[oracle@testserver ~]$ sql



SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 28 14:11:46 2022

Version 19.13.0.0.0



Copyright (c) 1982, 2021, Oracle. All rights reserved.





Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.13.0.0.0



SQL> create database link MYDBLINK connect to "testuser" identified by

 "123456" using 'MYDBLINK';



Database link created.



SQL> select count(*) from "testtable "@MYDBLINK;



COUNT(*)

----------

2



SQL>

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.13.0.0.0 

  • If you wanna add another database, you can start over from 6th step (listener.ora configuration) and apply similar steps.













Comments

Post a Comment

Popular posts from this blog

Oracle Database Upgrade With OPatch Tool (RHEL/Centos/OEL)

Follow these steps to upgrade an Oracle Database using the OPatch tool: 1. Download and Prepare Files Download the patch you want to install along with the latest OPatch tool from Oracle's website. Transfer all downloaded files to the /tmp/ directory, and unzip them. Ensure the correct permissions and ownership are set: chown -R oracle:oinstall p26710464_122010_Linux-x86-64.zip chown -R oracle:oinstall p6880880_122010_Linux-x86-64.zip unzip p26710464_122010_Linux-x86-64.zip unzip p6880880_122010_Linux-x86-64.zip 2. Replace the OPatch Folder Replace the current OPatch folder inside the Oracle database files with the new one: cd $ORACLE_HOME cp -R OPatch OPatch_yedek # Backup current OPatch folder. rm -rf OPatch cp -R /tmp/Opatch . Verify the OPatch version and list applied patches: cd OPatch ./opatch lspatches 3. Check for Conflicts Navigate to the patch folder and run the following command to check for conflicts before starting the upgrade: cd /tmp/26710464/ $O
Read more

POSTGRESQL UPGRADE WITH PG_UPGRADE UTILITY IN RHEL/CENTOS/OEL

Upgrading PostgreSQL can be a smooth process if you follow the necessary steps carefully. Below is a comprehensive guide to help you through the upgrade. Table of Contents Prerequisites Install the New PostgreSQL Version Set Up New Directories and Permissions Initialize the New Database Configure PostgreSQL Settings Stop and Disable Old PostgreSQL Services Run the Upgrade Process Finalize the Upgrade Post-Upgrade Tasks Prerequisites Before initiating the upgrade, ensure the following prerequisites are met: Internet Connection: Required for downloading packages. PostgreSQL RPM Packages: Necessary for installation. Free Disk Space: Ensure you have more free space than the current database size. Compatibility Checks: Verify PostgreSQL extension compatibility. Crontab Jobs: Identify any cron jobs related to PostgreSQL. Note: Skipping these checks may result in errors during the upgrade process. Install the New PostgreSQL Versio
Read more

Backup Recovery Scenarios

 -----taking full backup (compressed) ------------ RUN {   ALLOCATE CHANNEL ch1 DEVICE TYPE DISK FORMAT '/backup/%U';   ALLOCATE CHANNEL ch2 DEVICE TYPE DISK FORMAT '/backup/%U';   ALLOCATE CHANNEL ch3 DEVICE TYPE DISK FORMAT '/backup/%U';   ALLOCATE CHANNEL ch4 DEVICE TYPE DISK FORMAT '/backup/%U';      BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;      RELEASE CHANNEL ch1;   RELEASE CHANNEL ch2;   RELEASE CHANNEL ch3;   RELEASE CHANNEL ch4; } ------- some datafiles are missing, full backup available ------- rman target / sql 'startup mount'; list backup of database summary; --Buradaki tag bilgisi alınır ve restore başlatılır. RESTORE DATABASE from tag TAG20240317T180940; RECOVER DATABASE; -- RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 126 and starting SCN of 2605758 -- If you get an error like above, run the command below: recover database until sequence 126; alter database open resetlogs; --
Read more