How to find remote session executing over a database link

Select /*+ ORDERED */
substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10) "ORIGIN",
substr(g.K2GTITID_ORA,1,35) "GTXID",
substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) "LSESSION" ,
substr(decode(bitand(ksuseidl,11),1,'ACTIVE',0,
decode(bitand(ksuseflg,4096),0,'INACTIVE','CACHED'),
2,'SNIPED',3,'SNIPED', 'KILLED'),1,1) "S",
substr(event,1,10) "WAITING"
from x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w
where g.K2GTDXCB =t.ktcxbxba
and g.K2GTDSES=t.ktcxbses
and s.addr=g.K2GTDSES
and w.sid=s.indx;

GTXID is the same on both databases.

################################### Sample output ###################################

##Destination

3   LBREPDB01-51715  LBREP.aa2c0b4f.94.11.4694801  5447.62951   I   SQL*Net me

##Source

2   LB\MARIAMI-41196:4058  LBREP.aa2c0b4f.94.11.4694801 87.36231  I  SQL*Net me

More Details:

SID – 87
SERIAL – 36231

Advertisements

Proxy User Authentication: Create DBLINK in another schema

Problem

We need to create private database link in another schema. But we don’t know the password for this user.

Solution (Proxy user)

For example, my username is marik and database link should be created in dbcleanup’s schema.

SQL> alter user DBCLEANUP grant connect through marik;
SQL> connect marik[DBCLEANUP]/123@orcl
SQL> show user
USER is "marik[DBCLEANUP]"
SQL> create database link MYLINK 
connect to LINKUSER identified by "password" using 'ORCL2';

Proxy users can be identified using the PROXY_USERS view.

SQL> select * from proxy_users;

The proxy authentication can be revoked using the following command.

SQL> alter user DBCLEANUP revoke connect through marik ;

ORA-01017: invalid username/password; logon denied(database link error)

When I was creating database link from Oracle 10g to 11g like that:

SQL> create database link mylink_name connect to myuser_name identified by mypassword using 'MYSID';

Database link created

got ORA-01017 error.

Cause:

This may happen if in 11g database, there is enabled the following parameter:

SQL> show parameter sec_case_sensitive_logon;

NAME                                 TYPE        VALUE
----------------------------------- --------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE

 

Solution:

Recreate database link by the following way:

SQL> drop database link mylink_name;

Database link dropped

SQL> create database link mylink_name connect to "myuser_name" identified by "mypassword" using 'MYSID';

Database link created

To check it:

SQL> select * from dual@mylink_name;

DUMMY
-----
X

Create Database Link in Another Schema

Creating database link has several restrictions. One of them is that you can’t create DB link in another schema, for example the following script is not correct:

create database link hr.remote_db_link
connect to k identified by k
using 'test'


If you run this then database link will be created with the name “hr.remote_db_link”  in your schema not in HR schema.

To solve this, there can be done the following:

–Create procedure from our user in HR which will execute database creation script

 create procedure hr."db_create_link_proc"
 is
 begin
      execute immediate  'create database link remote_db_link
                          connect to k identified by k
                          using ''test'' ';
 end;

–Execute that procedure from our schema

 begin
      hr."db_create_link_proc";
 end;

–Then you can drop this procedure from our schema

drop procedure hr."db_create_link_proc"

That is all.

Oracle Heterogeneous Services(hsODBC)

Heterogeneous Services provide the ability to communicate with non-Oracle databases and services.

I will discuss, how to communicate with SQL Server 2005 from Oracle.

1. First of all, you should have a trusted username in SQL Server 2005 to connect to the SQL database.

If you don’t have it, do the following:

1.1 Connect to the SQL Server 2005 database.
1.2 In the Object Explorer, expand the Security folder, then Logins and create new login.

New Login...(Sql Server 2005)

1.3 In General, type Login name, choose SQL Server authentication, type password for user, uncheck User must change password at next login, choose English in Default language.

Login_New(SQL Server 2005)

1.4 In the Server Roles, public and sysadmin roles should be checked.

Login Properties_Server Roles(SQL Server 2005)

1.5 In the User Mapping, check the database for which this user will be owner(Note that this database should be the database from where you want to query data).

Login_Properties_User_Mapping(SQL Server 2005)

1.6 In Status.

Login_Properties_Status

1.7 Right click on the server in the Object Explorer window, choose properties, in Security, choose SQL Server and Windows Authentication mode.

Server Properties_Sequrity(SQL Server 2005)

1.8 Reload the server.

2. Now, when you already have a trusted user in SQL Server 2005. Let’s install ODBC driver for sql server on the computer where Oracle is installed.

2.1 Start -> Control Panel -> Administrative Tools -> Data Sources (ODBC), go to System DSN tab, click Add button.

ODBC Data Source Administrator_System DSN

2.2 Choose SQL Server, click Finish.

Create New Data Source

2.3 In the Name field, type some name, we will use it later as a SID_NAME. In Server field, type the IP of the server, where SQL Server 2005 database is installed.

Create a New Data Source to SQL Server(Data Source)

2.4 Choose second radio button, and type Login ID and Password for the user, which is a trusted user in SQL Server 2005(We have created it before, section 1).

Create a New Data Source to SQL Server

2.5 Check Change the default database to, and choose your database(Note this should be the database from where you want to query data).

Microsoft_SQL_Server_DNS_Configuration

2.6 Leave default settings.

Microsoft_SQL_Server_DNS_Configuration_2

2.7  This window shows you the summary, click Test Data Source button.

ODBC Microsoft SQL Server Setup

2.8 If you have indicated the correct settings, it should be successful.

SQL Server ODBC Data Source Test

3. Now, it’s time to configure in Oracle the following files: listener.ora, tnsnames.ora and %ORACLE_HOME%\hs\admin\inithsodbc.ora.

3.1 In listener.ora file add the following entries:

listenerradiustosql =
    (ADDRESS_LIST=
        (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
         (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))
)
SID_LIST_listenerradiustosql=
(SID_LIST=
   (SID_DESC=
     (SID_NAME=radiustosql)
     (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)--Your oracle home
     (PROGRAM=hsodbc)
    )
)

Note: HOST must be localhost(127.0.0.1) not the computer IP, on where oracle is installed.
PORT is non-default port 1522, or choose the port which is free.
You should have two different listeners, one for Oracle itself and other one for radiustosql.

3.2 In tnsnames.ora file add the following entries:

radiustosql  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
    (CONNECT_DATA=(SID=radiustosql))
    (HS=OK)
  )

3.3  Rename %ORACLE_HOME%\hs\admin\inithsodbc.ora to %ORACLE_HOME%\hs\admin\initradiustosql.ora and change its content to the following:

# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = radiustosql
HS_FDS_TRACE_LEVEL = OFF

#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

3.4 Reload both Listeners: Oracle listener and newly created listenerradiustosql.

–To start default listener

>lsnrctl stop
>lsnrctl start

–To start non-default listener

>lsnrctl stop listenerradiustosql
>lsnrctl start listenerradiustosql

4. Connect to Oracle database and create database link.

4.1
–To create database link

create database link radiustosql
connect to m identified by itsPassword using 'radiustosql'

–To check if it works, run the following:

SELECT *
FROM dual@radiustosql

–Output

DUMMY
--------
X