Friday, November 11, 2011

Integrating Oracle database with IBM WebSphere MQ Series


Demonstration of configuring Oracle Database 11gR2 with IBM Websphere MQ series v7

1.    Install IBM Websphere MQ client on DB server. We installed recent version 7.
Download Location:

2.    Loading Database Objects into the Database
Using SQL*Plus, run ORACLE_HOME/mgw/admin/catmgw.sql as user SYS as SYSDBA. This script loads the database objects necessary for Messaging Gateway, including roles, tables, views, object types, and PL/SQL packages. It creates public synonyms for Messaging Gateway PL/SQL packages. It creates two roles, MGW_ADMINISTRATOR_ROLE and MGW_AGENT_ROLE, with certain privileges granted. All objects are owned by SYS.

3.    Setting Up for WebSphere MQ Base Java or JMS
The WebSphere MQ client and WebSphere MQ classes for Java and JMS must be installed on the computer where the Messaging Gateway agent runs. In this section MQ_HOME refers to the location of the installed client. On the Linux operating system, this location is always /opt/mqm.

For our case, Websphere MQSeries V7 has built in MQ base java & JMS classes. So we didn’t need to worry about it. For later versions, these two options needs to be installed seperatedly.

4.    Creating an Oracle Messaging Gateway Administration User
To perform Messaging Gateway administration work, a database user must be created with MGW_ADMINISTRATOR_ROLE privileges
CREATE USER admin_user IDENTIFIED BY admin_password;
GRANT CREATE SESSION to admin_user;
GRANT MGW_ADMINISTRATOR_ROLE to admin_user;

5.    Creating an Oracle Messaging Gateway Agent User
To establish the Messaging Gateway agent connection back to the database, a database user with MGW_AGENT_ROLE privileges must be created/
CREATE USER agent_user IDENTIFIED BY agent_password;
GRANT CREATE SESSION to agent_user;
GRANT MGW_AGENT_ROLE to agent_user;

6.    Configuring Oracle Messaging Gateway Connection Information
After the Messaging Gateway agent user is created, the administration user uses DBMS_MGWADM.ALTER_AGENT to configure Messaging Gateway with the username, password, and database connect string used by the Messaging Gateway agent to connect back to the database. Use the Messaging Gateway username and password that you created. The database connect string parameter can be set to either a net service name in tnsnames.ora (with IPC protocol for better performance) or NULL. If NULL, then the oracle_sid parameter must be set in mgw.ora.
For this release, always specify a not NULL value for the database connect string parameter when calling DBMS_MGWADM.ALTER_AGENT.
EXEC DBMS_MGWADM.ALTER_AGENT( agent_name  => 'default_agent', username    => 'agent_user', password    => 'agent_password', database    => 'TESTDB');

7.    Modifying listener.ora for the External Procedure
You must modify listener.ora so that the Messaging Gateway PL/SQL packages can call the external procedure.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = TESTHOST)(PORT = 1521))
    )
        (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
  )))

# Add a SID_DESC
SID_LIST_LISTENER=
 (SID_LIST=
 (SID_DESC =
   (SID_NAME= mgwextproc)
   (ENVS="LD_LIBRARY_PATH=/app/oracle/oradbms/eedb/11.2/jdk/jre/lib/sparcv9/server:/app/oracle/oradbms/eedb/11.2/lib")
   (ORACLE_HOME=/app/oracle/oradbms/eedb/11.2)
   (PROGRAM = extproc))
)

8.    Modifying tnsnames.ora for the External Procedure
For the external procedure, configure a net service name MGW_AGENT in tnsnames.ora whose connect descriptor matches the information configured in listener.ora

The net service name must be MGW_AGENT (this value is fixed). The KEY value must match the KEY value specified for the IPC protocol in listener.ora. The SID value must match the value specified for SID_NAME of the SID_DESC entry in listener.ora.
Here is an example:
MGW_AGENT =
(DESCRIPTION=
   (ADDRESS_LIST= (ADDRESS= (PROTOCOL=IPC)(KEY=EXTPROC)))
   (CONNECT_DATA= (SID=mgwextproc) (PRESENTATION=RO)))

AGENT_SERVICE =
(DESCRIPTION =
 (ADDRESS_LIST= (ADDRESS= (PROTOCOL=IPC)(KEY=EXTPROC)))
   (CONNECT_DATA= (SID=TESTDB)(PRESENTATION=RO)))

9.    Setting Up a mgw.ora Initialization File
The Messaging Gateway default initialization file ORACLE_HOME/mgw/admin/mgw.ora is a text file. The Messaging Gateway external procedure uses it to get initialization parameters to start the Messaging Gateway agent. Copy ORACLE_HOME/mgw/admin/sample_mgw.ora to mgw.ora and modify it according to your situation.
# If a value is not specified, the default will be used
log_directory=/app/oracle/oradbms/eedb/11.2/mgw/log

# Set logging level to logging only.  Note:  whitespace is OK
log_level  = 3

# set the LD_LIBRARY_PATH we need for the MGW Agent (UNIX only)
set LD_LIBRARY_PATH=/app/oracle/oradbms/eedb/11.2/jdk/jre/lib/sparc:/app/oracle/oradbms/eedb/11.2/rdbms/lib:/app/oracle/oradbms/eedb/11.2/lib:/opt/mqm/java/lib:/app/oracle/oradbms/eedb/11.2/jdk/jre/lib/sparc/

# MGW_PRE_PATH will be prepended to the PATH inherited
# by the MGW external process
# WINDOWS_NT only: This variable must be set to indicate where the library
#   jvm.dll may be found.
#set MGW_PRE_PATH=\myOracleHome\jdk\jre\bin\classic


# set the CLASSPATH we need for the MGW Agent

set CLASSPATH=/app/oracle/oradbms/eedb/11.2/jdbc/lib/ojdbc5.jar:/app/oracle/oradbms/eedb/11.2/jdk/jre/lib/i18n.jar:/app/oracle/oradbms/eedb/11.2/jdk/jre/lib/rt.jar:/app/oracle/oradbms/eedb/11.2/sqlj/lib/runtime12.jar:/app/oracle/oradbms/eedb/11.2/sqlj/lib/translator.jar:/app/oracle/oradbms/eedb/11.2/jlib/orai18n.jar:/app/oracle/oradbms/eedb/11.2/jlib/jta.jar:/app/oracle/oradbms/eedb/11.2/rdbms/jlib/jmscommon.jar:/app/oracle/oradbms/eedb/11.2/rdbms/jlib/aqapi.jar:/opt/mqm/java/lib/com.ibm.mq.jar:/opt/mqm/java/lib/com.ibm.mqjms.jar:/opt/mqm/java/lib/connector.jar:/opt/mqm/java/lib/jms.jar:/opt/mqm/java/lib/jndi.jar:/opt/mqm/java/lib/jta.jar:/opt/mqm/java/lib/connector.jar:/opt/mqm/java/lib:/opt/mqm/java/lib/com.ibm.mqbind.jar:/opt/mqm/java/lib/com.ibm.jms.jar
# ensure that ORACLE_SID is set in case we don't want to specify a service
# name
set ORACLE_SID=TESTDB


10. Verifying the Oracle Messaging Gateway Setup
The following procedure verifies the setup and includes a simple startup and shutdown of the Messaging Gateway agent:

a.    Start the database listeners.
b.    Start the listener for the external procedure and other listeners for the regular database connection.
c.    Test the database connect string for the Messaging Gateway agent user.
    sqlplus agent_user/agent_password@agent_database.

    If it is successful, then the Messaging Gateway agent is able to connect to the database.
     Run sqlplus agent_user/agent_password@MGW_AGENT.
This should fail with "ORA-28547: connection to server failed, probable Oracle Net admin error". Any other error indicates that the tnsnames.ora, listener.ora, or both are not correct.

d.    Connect as admin_user and call DBMS_MGWADM.STARTUP to start the Messaging Gateway agent.
exec dbms_mgwadm.shutdown
exec dbms_mgwadm.startup

select agent_status,agent_ping from mgw_gateway;

AGENT_STATUS
AGENT_PING
RUNNING
REACHABLE

Wednesday, October 26, 2011

Gather Optimizer statistics, 11g

In Oracle 11g: The automatic statistics gathering job for Oracle 11g is an internal procedure named GATHER_DATABASE_STATS_JOB_PROC.

Below are the steps in case if we want to delete & gather schema stats on adhoc basis.

Gathering statistics for all objects in a schema
  1. Ensure that the automatic statistics gathering job is scheduled, using the following SQL.
SELECT  ENABLED FROM DBA_SCHEDULER_PROGRAMS WHERE PROGRAM_NAME = 'GATHER_STATS_PROG';
Output should be 'TRUE'.

Note: GATHER_STATS_PROG is the program that runs the internal procedure GATHER_DATABASE_STATS_JOB_PROC.

  1. Delete any existing statistics, using the following command from SQLPLUS.
EXEC DBMS_STATS.DELETE_SCHEMA_STATS('SCOTT');

  1. Change the default "ESTIMATE_PERCENT" to 100% for DBMS_STATS. Use the following procedure from SQLPLUS to change the default. Run this using the Oracle SYSDBA user ID:
EXEC DBMS_STATS.SET_PARAM('ESTIMATE_PERCENT','100');

  1. Run GATHER_DATABASE_STATS using the following command from SQLPLUS. Run this command using the Oracle SYSDBA user ID.
EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', DEGREE => 2, CASCADE => TRUE);

Gather Schema statistics

exec dbms_stats.gather_schema_stats(
       ownname => 'SCOTT',
       cascade => TRUE,
       method_opt => 'FOR ALL COLUMNS SIZE AUTO' );
Gathering statistics an individual table

exec dbms_stats.gather_table_stats(  -
       ownname => '  Schema_name ', -
       tabname => '  Table_name  ', -
       estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,  -
       cascade => TRUE,  -
       method_opt => 'FOR ALL COLUMNS SIZE AUTO' );
NOTE: For a more cautious approach as outlined in the text above and where column statistics are known not to be beneficial, Replace:

method_opt => 'FOR ALL COLUMNS SIZE AUTO'
 with 
method_opt => 'FOR ALL COLUMNS SIZE 1'

Note that the defaults for statistics gathering on different versions of Oracle are not necessarily the same, for example:
  • ESTIMATE_PERCENT: defaults:
    •  9i : 100%
    • 10g : DBMS_STATS.AUTO_SAMPLE_SIZE (using very small estimate percentage)
    • 11g: DBMS_STATS.AUTO_SAMPLE_SIZE (using larger estimate percentage - 100%)
  • METHOD_OPT: defaults:
    • 9i: "FOR ALL COLUMNS SIZE 1" effectively no detailed column statistics.
    • 10g and 11g: "FOR ALL COLUMNS SIZE AUTO" - This setting means that DBMS_STATS decides which columns to add histogram to where it believes that they may help to produce a better plan. 

Export and Import schema statistics
Easy way to export and import schema statistics – in case the optimizer is behaving strangely and you want to re-import old statistics when the optimizer behaved properly.

Create the table to store the statistics- 
EXEC DBMS_STATS.CREATE_STAT_TABLE(‘TEST’, ‘STATS_TABLE’);

Export schema stats – will be stored in the ‘STATS_TABLE’
EXEC DBMS_STATS.export_schema_stats(‘TEST’,’STATS_TABLE’,NULL,’TEST’);

If required import these statistics back to TEST schema.
EXEC DBMS_STATS.import_schema_stats(‘TEST’,’STATS_TABLE’,NULL,’TEST’);

Finally drop the table created to backup the schema stats

EXEC DBMS_STATS.drop_stat_table(‘TEST’,’STATS_TABLE’);


Friday, October 21, 2011

Datafiles with highest I/O activity


col name format a40
set linesize 140
select * from (
select name,phyrds, phywrts,readtim,writetim
from v$filestat a, v$datafile b
where a.file#=b.file#
order by readtim desc) where rownum <6;

Sessions/Segments/Datafile with HIGH Physical Reads/Writes,I/O


Sessions with High physical Reads
set linesize 120
col os_user format a10
col username format a15
col pid format 9999999999
PROMPT SESSIONS SORTED BY PHYSICAL READS
PROMPT
select
OSUSER os_user,username,
PROCESS pid,
ses.SID sid,
SERIAL#,
PHYSICAL_READS,
BLOCK_CHANGES
from v$session ses,
v$sess_io sio
where ses.SID = sio.SID
and username is not null
and status=’ACTIVE’
order by PHYSICAL_READS;

 Segments with high Physical Reads
set pagesize 200
setlinesize 120
col segment_name format a20
col owner format a10

select segment_name,object_type,total_physical_reads
from ( select owner||'.'||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
where statistic_name in ('physical reads')
order by total_physical_reads desc)
where rownum <=10;


Segments with high Physical Writes
set pagesize 200
setlinesize 120
col segment_name format a20
col owner format a10
select segment_name,object_type,total_physical_writes
from ( select owner||'.'||object_name as segment_name,object_type,
value as total_physical_writes
from v$segment_statistics
where statistic_name in ('physical writes')
order by total_physical_writes desc)
where rownum <=10;

Datafiles with highest I/O activity
col name format a40
set linesize 140
select * from (
select name,phyrds, phywrts,readtim,writetim
from v$filestat a, v$datafile b
where a.file#=b.file#
order by readtim desc) where rownum <6;








Thursday, October 20, 2011

Tranfer all Roles and Grants to Another Database

Run in source database:

SET pages 0
SPOOL privs.sql
-- create roles
SELECT 'create role ' || ROLE || ';'
  FROM dba_roles;
-- grant roles
SELECT    'grant '
       || granted_role
       || ' to '
       || grantee
       || DECODE (admin_option, 'YES', ' with admin option;', ';')
  FROM dba_role_privs;
-- grant system privileges
SELECT    'grant '
       || PRIVILEGE
       || ' to '
       || grantee
       || DECODE (admin_option, 'YES', ' with admin option;', ';')
  FROM dba_sys_privs;
-- grant object privileges
BREAK on myGrantor
COL myCommand newline
SELECT   'prompt ' || grantor || '
connect ' || grantor mygrantor,
            'grant '
         || PRIVILEGE
         || ' on '
         || owner
         || '.'
         || table_name
         || ' to '
         || grantee
         || DECODE (grantable, 'YES', ' with grant option;', ';') mycommand
    FROM dba_tab_privs
ORDER BY grantor;
-- grant column privileges
SELECT   'connect ' || grantor mygrantor,
            'grant '
         || PRIVILEGE
         || ' on '
         || owner
         || '.'
         || table_name
         || '('
         || column_name
         || ')'
         || ' to '
         || grantee
         || DECODE (grantable, 'YES', ' with grant option;', ';') mycommand
    FROM dba_col_privs
ORDER BY grantor;
QUIT

This will generate spool file "privs.sql" in current working directory

Remove references to users from privs.sql that does not exist in target database

connect as user with dba role on target database and run script privs.sql Object privileges can be granted by "object owner" only. Script will ask for password of each user who granted object privilege.