Tuesday 25 March 2014

WAITER AND BLOCKER



Login to the database as SYS as SYSDBA and run the script which is create lock tables. $ORACLE_HOME/rdbms/admin/catblock.sql

SQL > @$ORACLE_HOME/rdbms/admin/catblock.sql

This will create following views.

dba_locks
dba_kgllock
dba_lock_internal
dba_dml_locks
dba_ddl_locks
dba_waiters
dba_blockers


---DBA_WAITERS---
Its shows which session is waiting for resources. In an Oracle RAC environment, this only applies if the waiter is on the same instance.

COLUMN: WAITING_SESSION
COLUMN: HOLDING_SESSION


---DBA_BLOCKERS---
Its shows which session is blocking to another session and resources.  In an Oracle RAC environment, this only applies if the blocker is on the same instance.


COLUMN : HOLDING_SESSION

@@Below the Demonstration.

                Open 3 sesison
                session 1: login as scott
                session 2: login as scott
                session 3: login as sys

                session 1: update emp set sal=sal*100 where deptno =20;   # No commit Perform
                session 2: update emp set sal=200;   # No commit Perform
                session 3:  select waiting_session,holding_session,lock_type,mode_held from dba_waiters;

--select * from dba_blockers;

                session 1: rollback; else killed the session by sys.
                session 2:  select waiting_session,holding_session,lock_type,mode_held from dba_waiters; # lock has been released


## Detect the object_name which is holding the lock by session.

                Open 3 sesison
                session 1: login as scott
                session 2: login as scott
                session 3: login as sys

                session 1: update emp set sal=sal*100 where deptno =20;   # No commit Perform
                session 2: update emp set sal=200;   # No commit Perform
                session 3:  select waiting_session,holding_session,lock_type,mode_held from dba_waiters;
               
                session 3: select user#,sid,row_wait_obj#,username,wait_class,blocking_session from v$session where blocking_session=<HOLDING_SESSION>;
                session 3: select owner,object_id,object_name from dba_objects where object_id=<ROW_WAIT_OBJ#>;
                session 3: select serial# from v$session where sid=<WAITING_SESSION>;
                session 3: alter system kill session 'sid,serial#';



Usage of Mixed Case Database Object Names Is Dangerous

Some versions back, Oracle would not allow to create database object names with mixed cases,
even if we tried to create them, we could not.In newer versions of Oracle we can create tables, columns, indexes etc using mixed case or lower case, when the names are put inside double quotes.

For example:-

 CREATE TABLE "Customer" (
"CustomerID" number(10)
...
);

CREATE INDEX "IDX_Customer_CustomerID" on "Customer"("CustomerID");


We created table named “Customer” with a column “CustomerID” and the index is named “IDX_Customer_CustomerID”. In the above example we can see that mixed case or lower case is supported and the table, column and index are created in the database. When these names are used, we have to reference them everywhere using the lower case letters. The following statement would be invalid.


Invalid SQL1
 SELECT * FROM Customer

 
                                    While the below statement is valid.


Valid SQL1
 SELECT * FROM "Customer"
 


We can even create a table such as “CusTomer” and it would be valid.

Customer table with different case

 CREATE TABLE "CusTomer" (
"CustomerID" number(10)
...
);


With the large amount of work involved in matching the case for the name of the database object, every time a DML/DDL statement is used against a database object, the confusion it creates when duplicate database objects are allowed to be created (“Customer” and “CusTomer” are valid names).

We can see that its better to avoid using database objects with mixed case names.

Friday 21 March 2014

How to setup Oracle Database Gateway for SQL Server

Data Fetch on Oracle Server From SQL Server


Installation ORACLE GATEWAY FOR MS SQL



Install Database Gateway
 
 

Change home location if you want

 
 

Select Database Gateway for database vendor, here we are installing gateway for MSSQL Server 

 

 Add details of SQL Server (HOSTNAME, INSTANCE NAME, and DATABASE) 

 
 


Net Configuration Assistant will be started automatically, run wizard to create and configure Listener for database gateway

 
 

As already a listener is running for database named LISTENER so it is better to create a new listener with different name here we gave name LISTENEREG to datbase gateway listener 

 

Use any available port other than 1521 
 
 
 
 
 
 
 
 
IN MY CASE

--------------------------------------------------------------------------------------------
Post Installation Configuration


 Edit tnsnames file in Database Home
File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
--------------------------------------------------------------------------------------------


# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

dg4msql =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.2)(PORT=1521))
(CONNECT_DATA=(SID=dg4msql))
(HS=OK)
)

adventureDW =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.2)(PORT=1521))
(CONNECT_DATA=(SID=adventureDW))
(HS=OK)
)

afcssql =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.2)(PORT=1521))
(CONNECT_DATA=(SID=afcssql))
(HS=OK)
)


-------------------------------------------------------------------------------------------------------
 Edit listener file in Database Home
File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
--------------------------------------------------------------------------------------------------------------------
 
 
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =
        (ADDRESS_LIST=
                  (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.2)(PORT=1521))
)
 
SID_LIST_LISTENER=
      (SID_LIST=
            (SID_DESC=
                   (SID_NAME=dg4msql)
                   (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
                   (PROGRAM=dg4msql)
           )
            (SID_DESC=
                   (SID_NAME=adventureDW)                         # SQL DB NAME FOR TEMP
                   (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
                   (PROGRAM=dg4msql)
         )
          (SID_DESC=
                 (SID_NAME=afcssql)                                         # SQL DB NAME
                 (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
                 (PROGRAM=dg4msql)
       )
)

 
ADR_BASE_LISTENER = /u01/app/oracle



CREATE
------------------------------------------------------------------------------------------------------------
initadventureDW.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=[SQL2005MAGRANIA]:1433//AdventureWorksDW
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

initafcssql.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=[SQL2005MAGRANIA]:1433//AFCSSQL
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

 


initdg4msql.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=[SQL2005MAGRANIA]:1433//AdventureWorks
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

 

DATABASE LINK

That last step is to configure and test the db link….
 
CREATE PUBLIC DATABASE LINK 'mssqldb' CONNECT TO 'mssql_user' IDENTIFIED BY 'mssql_pass' USING '
dg4dmssqldb'

 
and test it….
SELECT * FROM MSSQLTABLE@

Wrap up

So far we’re seeing fast response times for our queries going through the gateway.  Hopefully the gateway will allow us to move our data from the staging environment to the ERP at the same pace before we started running into issues with the linked server.  Actually, I’m surprised by the performance of gateway, we’re able to move a table that is 58 columns wide and contains 138,000 records in about 13-14 seconds.