step by step Reading MSSQL DB data from Oracle 19c database using DB link & ODBC Drivers :

 Reading MSSQL DB data from Oracle 19c  database using DB link & ODBC Drivers  :

****************************************************************************

List of steps:

**************

1. Root - Install Microsoft SQL server ODBC drivers.

  sudo ACCEPT_EULA=Y yum install -y msodbcsql17

2. Root - Create ODBC entry in /etc/odbc.ini file pointing to remote.

3. Oracle - create init parameter file initora_19c_DB_MSSQL. Ora

4. Oracle - register SID in listener.ora.

5. Oracle- Create entry in tnsnames.ora pointing to SID.

6. Oracle - create DB link in oracle database.

7. Oracle- Optionally - Create synonym.

8. Install Microsoft SQL server ODBC drivers

How to install the Microsoft ODBC Driver for SQL Server on Linux. It also includes instructions for the optional command-line tools for SQL Server and the unixODBC development headers.

Here we are taking up the ODBC 17 drivers. Please go to the below link and ask to unix team to install the driver on the server.

https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver17&tabs=redhat18-install%2Credhat17-install%2Cdebian8-install%2Credhat7-13-install%2Crhel7-offline

Once installed the package they need to run the ODBC driver using below command.

  sudo ACCEPT_EULA=Y yum install -y msodbcsql17

9. Create ODBC entry in /etc/odbc.ini file pointing to remote

To create an ODBC entry in /etc/odbc.ini that points to a remote database, you need to define a Data Source Name (DSN) within the file. This DSN will contain the necessary parameters to connect to your remote database.

Here is an example of an odbc.ini entry for a remote database.

[DS_MSSQL]

Description = MSSQL

Driver = /usr/lib64/libmsodbcsql-17.so

Server = 20.20.30.30.\MSSQL_instance1

Port = 57500

Database = MSSQL_DB

UID = user_ora2mssql

Explanation of the parameters:

• [DS_MSSQL]: This is the DSN name. You can choose any descriptive name for your connection.

• Description: A brief description of the data source.

• Driver: The name of the ODBC driver you have installed for your specific database SQL Server. Ensure the corresponding driver is correctly installed and configured in /etc/odbcinst.ini.

• Server : The hostname or IP address of your remote database server.

• Port: The port number on which the database server is listening for  SQL Server.

• Database: The name of the specific database you want to connect to on the remote server.

• UID: The username for authenticating with the database.

10. Create init parameter file initora_19c_DB_MSSQL. Ora

To create a Heterogeneous Services (HS) initialization parameter file for connecting to a SQL Server database named ora_19c_DB, you would create a text file named initora_19c_DB_MSSQL.ora and place it in the $ORACLE_HOME/hs/admin directory. This file contains parameters specific to the gateway, not the main Oracle database.

The file should be located in the $ORACLE_HOME/hs/admin directory.

Below is a sample configuration. The specific parameters required will depend on your environment and the specific Oracle Database Gateway version you are using.

vi $ORACLE_HOME/hs/admin/initora_19c_DB_MSSQL.ora

# needed for the database gateway for odbc

# HS init parameters

HS_FDS_CONNECT_INFO = DS_MSSQL

HS_FDS_SHAREABLE_NAME = /usr/lib64/libmsodbcsql-17.so

HS_LANGUAGE= AL16UTF16

HS_FDS_REMOTE_DB_CHARSET=AMERICAN_AMERICA.AL16UTF16

HS_NLS_NCHAR=UCS2

#

# ODBS SPECIFIC ENVIRONMEANT VARIABLES

#

set ODBCINI=/etc/odbc.ini

11. register SID in listener.ora. 

Add the HS SID listener in listener file To register an Oracle System Identifier (SID) for a database instance or a Heterogeneous Services (HS) agent in the listener.ora file, you need to perform static registration. This involves manually adding a SID_DESC entry to the SID_LIST section of the file. 

The listener. Ora file is typically located in the $ORACLE_HOME/network/admin directory

Add a SID_DESC entry:-

Within the SID_LIST_<listener_name> section (by default SID_LIST_LISTENER), add a new SID_DESC block. This block specifies the details for the service you want to register.For an Oracle Database Instance

==================================

LISTENER_ora_19c_DB =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 30.30.30.50)(PORT = 1587))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1587))

    )

  )

 

SID_LIST_LISTENER_ora_19c_DB =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = ora_19c_DB)

      (ORACLE_HOME = /oracle/ora_19c_DB/12201)

    )

    (SID_DESC =

      (SID_NAME = ora_19c_DB_MSSQL)

      (ORACLE_HOME = /oracle/ora_19c_DB/12201)

      (PROGRAM = /oracle/ora_19c_DB/12201/bin/dg4odbc)

    )

  )

12. Create entry in tnsnames.ora pointing to SID.

To create an entry in the tnsnames.ora file pointing to an Oracle SID, follow these steps:

• Locate the tnsnames.ora file: This file is typically found in the $ORACLE_HOME/network/admin directory. If the TNS_ADMIN environment variable is set, it will point to the directory containing the tnsnames.ora file.

• Open the tnsnames.ora file: Use a text editor to open the tnsnames.ora file.

• To create an entry in the tnsnames.ora file for an Oracle Heterogeneous Services (HS) SID, you need to manually edit the file to include a specific connect descriptor. The HS=OK parameter is crucial in the CONNECT_DATA section to direct Oracle Net to use Heterogeneous Services.

TNS_MSSQL.WORLD =

(DESCRIPTION =

    (SDU = 32768)

    (ADDRESS_LIST =

        (ADDRESS =

          (PROTOCOL = TCP)

          (HOST = 30.30.30.50)

          (PORT = 1587)

        )

    )

    (CONNECT_DATA =

       (SID =ora_19c_DB_MSSQL)

         )

        (HS=OK)

  )

• (HS = OK): This mandatory parameter tells Oracle Net to connect to a non-Oracle system using Heterogeneous Services.

13. Oracle - create DB link in oracle database.

• Permissions: 

Ensure the user creating the database link has the necessary CREATE DATABASE LINK privilege. The remote_user in the CONNECT TO clause must exist in the remote database and have privileges to access the objects you intend to use via the link.

• Public vs. Private: 

Database links can be PUBLIC (accessible by all users) or private (owned by the user who created it). To create a public link, add the PUBLIC keyword after CREATE.

• GLOBAL_NAMES parameter: 

The GLOBAL_NAMES initialization parameter can impact database link behavior. If set to TRUE, the database link name must match the global name of the remote database. Consider setting it to FALSE if strict name matching is not desired.

Note :- Before you creating the DB link they need to be create the SQL server authentication user in SQL server then only you make connection between from Oracle to MSSQL.

Create SQL Server Authentication User:- 

• In SQL Server Management Studio (SSMS): 

o Go to Security → Logins → New Login.

o Choose SQL Server Authentication (not Windows Authentication).

o Set a strong password and ensure Enforce password policy is configured as needed.

o Map the login to the required database and assign appropriate roles (e.g., db_datareader.

Syntax :-         CREATE DATABASE LINK my_remote_link     CONNECT TO remote_user IDENTIFIED     BY remote_password    USING 'REMOTE_DB_ALIAS';

Example :- create public database link  mssql_dblink6 connect to "user_ora2mssql" identified by "" using 'TNS_MSSQL';

Select  count(*)  from  "view_test"@mssql_dblink6;

 

14. Optionally - Create synonym.

To create a synonym for a remote object accessed through a database link in Oracle.

Once the database link is established, you can create a synonym for a specific object (table, view, sequence, etc.) in the remote database.

CREATE SYNONYM synonym_name FOR remote_table_name@db_link_n

Example :- Create public synonym mssqlview   For view_test@mssql_dblink6;

After creating the synonym, you can query the remote table using the synonym name as if it were a local object:

Select count(*) from mssqlview.

This will read the Data from MSSQL from Oracle DB

 

 

step by step to read oracle DB data from MSSQL server using linked server in MSSQL database

 Linked Server in MS SQL Server

-----------------------------------

Source : MS SQL 2019 EE on Windows 22

Target : Oracle 19c R2 EE on Linux 8 - IP - 192.168.56.74 Port- 1583 ServiceName- ORA_DB_19c

Pre-Req:

-----------

a. You must have Oracle client installed on MSSQL server.

b. Working Network Connection between both Servers.

   (If required create exception in Firewall.)

c. Remote (Oracle) DB credentials.

d. Proper TNS entry for remote (Oracle) DB.

Steps:

---------

1. Create User in Remote DB Server

Create user oracle identified by oracle123;

grant create session to oracle;

2. Provide Required Permission to User as per your need

grant select any table to oracle;

grant select any dictionary to oracle;

3. Create TNS Entry in Oracle Client TNSNAMES.ora in Source Server (MS SQL Server)

ORA_DB_19c=(DESCRIPTION=

(CONNECT_DATA=

(SERVICE_NAME=ORA_DB_19c))

(ADDRESS=

(PROTOCOL=TCP)(HOST=12.16.53.12)(PORT=1583)

)

)

4. Create Linked Server Using SSMS

You Need 3 Values : 

a. LINKED SERVER NAME -- Any Name you want to use , Just Like any DBLink. --ORACLE_DB

b. Product Name -- Oracle

c. Data Source -- TNS Service Name of Oracle DB

d. Fill Credentials of Remote DB in Security Section --oracle DB user name & password

5. Enable AllowInProcess in SQL Server

EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1

6. Test Linked Server

7. Fetch records from Oracle DB using Linked Server in MS SQL Server

ORACLE_DB---Linked server name

SELECT * FROM OPENQUERY([ORACLE_DB] , 'select name,open_mode,host_name from v$database,v$instance') ;

How to search AD group from windows 10?

 How to search AD group from windows 10:

try this on Run command:

windows 7:  C:\Windows\System32\rundll32.exe  dsquery.dll,OpenQueryWindow

Windows 10 :  rundll32 dsquery.dll,OpenQueryWindow

MS SQL server mirroring concepts

All about SQL server mirroring
pre-request for SQL server mirroring

1.    mirroring available after 2005

2.     Primary Db should be in full recovery mode

3.     mirrored Db in recovery mode

4.     Primary & Mirrored Db server has same version & same Sservic pack same version (either Standard or Enterprise)

5.     witness server has been  anything sql express also

6.     Server should be on same domain name. becoz it communicates with AD

7.     you cannot use this as a secondary read-only database to achieve scalability

8.     Database name same on both servers

9.     Operating mode

High-performance mode
Transaction safety
Witness state
OFF
NULL (no witness)2
High-safety mode without automatic failover
FULL
NULL (no witness)
High-safety mode with automatic failover1
FULL
CONNECTED

10) to check the mirror state

SELECT mirroring_safety_level_desc, mirroring_witness_name, mirroring_witness_state_desc FROM sys.database_mirroring
11) protection modes
high performance
high safety
high safety with automatic fail-over


12)Restrictions for DB mirroring:
Only user databases can be mirrored. You cannot mirror the master, msdb, tempdb, or model databases.
A mirrored database cannot be renamed during a database mirroring session.
Database mirroring does not support FILESTREAM. A FILESTREAM filegroup cannot be created on the principal server. Database mirroring cannot be configured for a database that contains FILESTREAM filegroups.
Database mirroring is not supported with either cross-database transactions or distributed transaction
Maximum 10 databases per instance can support on a 32-bit system.
b) Database mirroring is not supported with either cross-database transactions or distributed transactions.


Advantages of mirroring include automatic fixing of corrupted pages and storage is not a SPOF


13)What is End Point? How u create end point?
An endpoint is a network protocol which is used to communicate Principal, Mirror and Witness servers over the network.
Creation of an end point:-
Create endpoint <endpoint name> State=started/stopped/disabled
as tcp (listener port=5022/5023) for database_mirroring (role=partner/witness)

14)What is the default of end points (port numbers) of principal, mirror and witness servers? How to find the Port numbers?
The default port numbers of principal, mirror and Witness servers are 5022, 5023 and 5024.
To Find Port Number:- SELECT name, port FROM sys.tcp_endpoints

15)In which Recovery model we can use in Mirroring?
In mirroring the principal and mirror databases are used only full recovery model

16)What is the syntax to stop the Database Mirroring?
Alter database <database name> set partner off

17)How to monitoring Mirroring?

There are six methods are available for monitoring the Database Mirroring
a) Database Mirroring Monitor:- Database Mirroring Monitor is a GUI tool that shows update status and to configure warning thresholds.
To open DM Monitor:- Right click on Principal Database > Tasks > Select Launch Database Mirroring Monitor.
b) SQL Server Management Studio:- A green arrow on the mirror server is indicates running well. A red arrow indicates problems that need to investigate.
c) SQL Server Log:- It provides information of Mirroring establishment and status. If any errors occurs it will be logged to SQL Server log and Windows event log.
d) Performance Monitor:- It can provides real-time information about Database mirroring. We can use performance counters to get status of the database mirroring such as Bytes received/sec, Bytes sent/sec, Transaction delay etc.
e) Profiler:- Profiler many events are providing the status of the Database mirroring
f) System Stored Procedures:-
? sp_dbmmonitoraddmonitoring
? sp_dbmmonitorchangemonitoring
? sp_dbmmonitorhelpmonitoring
? sp_dbmmonitordropmonitoring


18)What are the Database Mirroring states?

1) SYNCHRONIZING:-
The contents of the mirror database are lagging behind the contents of the principal database. The principal server is sending log records to the mirror server, which is applying the changes to the mirror database to roll it forward.
At the start of a database mirroring session, the database is in the SYNCHRONIZING state. The principal server is serving the database, and the mirror is trying to catch up.
2) SYNCHRONIZED:-
When the mirror server becomes sufficiently caught up to the principal server, the mirroring state changes to SYNCHRONIZED. The database remains in this state as long as the principal server continues to send changes to the mirror server and the mirror server continues to apply changes to the mirror database.
If transaction safety is set to FULL, automatic failover and manual failover are both supported in the SYNCHRONIZED state, there is no data loss after a failover.
If transaction safety is off, some data loss is always possible, even in the SYNCHRONIZED state.
3) SUSPENDED:-
The mirror copy of the database is not available. The principal database is running without sending any logs to the mirror server, a condition known as running exposed. This is the state after a failover.
  A session can also become SUSPENDED as a result of redo errors or if the administrator pauses the session
  SUSPENDED is a persistent state that survives partner shutdowns and startups.
4) PENDING_FAILOVER:-
  This state is found only on the principal server after a failover has begun, but the server has not transitioned into the mirror role.
  When the failover is initiated, the principal database goes into the PENDING_FAILOVER state, quickly terminates any user connections, and takes over the mirror role soon thereafter.
5) DISCONNECTED:-
  The partner has lost communication with the other partner

Database mirroring provides protection at the database level, whereas a cluster solution provides protection at the SQL Server instance level

19)What are the Disadvantages of Database Mirroring?

Potential data lost is possible in asynchronous operation mode. RTO will vary and depend on several factors, such as propagation interval time and bandwidth speed.
It only works at database level and not at server level. It only propagates changes at database level, no server level objects, such as logins and fixed server role membership, can be propagated.
Automatic server failover may not be suitable for application using multiple databases.

20)query to check mirroring status.



select  mirroring_state,mirroring_state_desc,mirroring_role,mirroring_role_desc,mirroring_safety_level_desc,mirroring_partner_name,mirroring_witness_name  from sys.database_mirroring;

scrip to find sql server last startup time

SELECT    [sqlserver_start_time] AS [LastStartupDate] FROM    [sys].[dm_os_sys_info]

scrip to detach /attach a sql database from instance?

USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'DB_NAME'
GO

CREATE DATABASE [test2] ON ( FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test2.mdf'), ( FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test2_1.ldf' ) FOR ATTACH ;

script to create sql database manually?

USE [master]
GO

CREATE DATABASE [test2] ON  PRIMARY
( NAME = N'test', FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test2.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'test_log', FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test2_1.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [test2] SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [test2].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [test2] SET ANSI_NULL_DEFAULT OFF
GO

ALTER DATABASE [test2] SET ANSI_NULLS OFF
GO

ALTER DATABASE [test2] SET ANSI_PADDING OFF
GO

ALTER DATABASE [test2] SET ANSI_WARNINGS OFF
GO

ALTER DATABASE [test2] SET ARITHABORT OFF
GO

ALTER DATABASE [test2] SET AUTO_CLOSE OFF
GO

ALTER DATABASE [test2] SET AUTO_CREATE_STATISTICS ON
GO

ALTER DATABASE [test2] SET AUTO_SHRINK OFF
GO

ALTER DATABASE [test2] SET AUTO_UPDATE_STATISTICS ON
GO

ALTER DATABASE [test2] SET CURSOR_CLOSE_ON_COMMIT OFF
GO

ALTER DATABASE [test2] SET CURSOR_DEFAULT  GLOBAL
GO

ALTER DATABASE [test2] SET CONCAT_NULL_YIELDS_NULL OFF
GO

ALTER DATABASE [test2] SET NUMERIC_ROUNDABORT OFF
GO

ALTER DATABASE [test2] SET QUOTED_IDENTIFIER OFF
GO

ALTER DATABASE [test2] SET RECURSIVE_TRIGGERS OFF
GO

ALTER DATABASE [test2] SET  DISABLE_BROKER
GO

ALTER DATABASE [test2] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO

ALTER DATABASE [test2] SET DATE_CORRELATION_OPTIMIZATION OFF
GO

ALTER DATABASE [test2] SET TRUSTWORTHY OFF
GO

ALTER DATABASE [test2] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO

ALTER DATABASE [test2] SET PARAMETERIZATION SIMPLE
GO

ALTER DATABASE [test2] SET READ_COMMITTED_SNAPSHOT OFF
GO

ALTER DATABASE [test2] SET HONOR_BROKER_PRIORITY OFF
GO

ALTER DATABASE [test2] SET  READ_WRITE
GO

ALTER DATABASE [test2] SET RECOVERY SIMPLE
GO

ALTER DATABASE [test2] SET  MULTI_USER
GO

ALTER DATABASE [test2] SET PAGE_VERIFY CHECKSUM
GO

ALTER DATABASE [test2] SET DB_CHAINING OFF
GO



GO

scrip to restore sql database from full backup?

RESTORE DATABASE [test] FROM  DISK = N'C:bakup\test.bak' WITH  FILE = 2,  NOUNLOAD,  STATS = 10

script to take full database backup on sql server?

BACKUP DATABASE [test] TO  DISK = N'C:\Backup\test.bak' WITH NOFORMAT, NOINIT,  NAME = N'bala-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

queries used to check SQL cluster view status

check about sql cluster
-------------------------------
select serverproperty('ComputerNamePhysicalNetBIOS');

SELECT * FROM fn_virtualservernodes();

SELECT * FROM sys.dm_os_cluster_nodes ;

how to do restore in sql server 2008 for whole database

1.take the full backup from ms studio
2.transfer the backup piece from source to target (i.e cmd prompt and c: or shared folder  to target server.
3.
you may generate the script from msq sql studio  alter this wherever you want to place the mdf,ldf files alter it based on the available  disk space.

RESTORE DATABASE [DB__name] FROM  DISK = N'E:\backup\DB__name_july28\DB__name_full_bakup.bak' WITH  FILE = 1,  MOVE N'DB__name_Data' TO N'E:\SQLData\DB__name.MDF',  MOVE N'DB__name_Log' TO N'F:\SQLLog\DB__name_1.LDF',  NOUNLOAD,  STATS = 5

4.run dbcc to check the integrity of the  database.

5.check all the user permission everything good.

6.fix the orphan user account using the below steps
EXEC sp_change_users_login 'Report'

steps to create db link from oracle to MSSQL using gateway server

1.CREATE PUBLIC DATABASE LINK "DB_link_name"
   CONNECT TO "PTC_SSRSRMS" IDENTIFIED BY VALUES '06D0D'
   USING 'DB__name'

2.tnsnames.ora on oracle server oracledb_servername

DB__name=
  (DESCRIPTION=
    (ADDRESS= (PROTOCOL=TCP) (PORT=1621) (HOST=gateway_servername))
    (CONNECT_DATA= (SID=DB__name)) (HS=OK))


3.update on C:\Oracle\11g\dg4msql
initDB__name.ora

# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server

#
# HS init parameters
#
HS_FDS_CONNECT_INFO=sqlserver name//sql db name
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

4.listener.ora
SID_DESC =
      (SID_NAME = DB__name)
      (ORACLE_HOME = c:\oracle\11g)
      (PROGRAM = dg4msql)
    )

5.tnsnames.ora
DB__name.WORLD =
  (DESCRIPTION=
    (ADDRESS= (PROTOCOL=TCP) (PORT=1621) (HOST=gateway_servername))
    (CONNECT_DATA= (SID=DB__name)) (HS=OK))

how to check active sessions on SQL server

sp_who
sp_who2 stored procedure used to list the connection and blkby column will indicate that blocked columns