Monday, July 13, 2009

Accessing Sqlserver from Oracle Database

Scenario:I want to access sql server 2000 database from oracle server 11g.

Following are the details about the servers.

Sql Server
=========
OS= Windows 2003 Server
Sqlserver Version= 2000
IP=192.168.1.100
default port=1433
DB name=hds

Oracle Server
============
OS= AIX 6.1
DB Version=11.1.0.7

Download Oracle Gateway software from OTN
http://download.oracle.com/otn/aix/oracle11g/aix.ppc64_11gR1_gateways.zip

1. Install oracle gateway under the ORACLE_HOME
2. Configure the files under $ORACLE_HOME/dg4msql/admin
copy the default initdg4msql.ora to your init{SID}.ora
cp initdg4msql.ora inithds.ora    (in my case my sqlserver db name is hds)


more inithds.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=192.168.1.100:1433//hds
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

3. Similarly after configuring the gateway file, configure the tnsnames.ora and listener.ora files as:
cd $ORACLE_HOME/network/admin
more listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/11.1.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC=
(SID_NAME=hds)
(ORACLE_HOME=/u01/app/oracle/11.1.0/db_1)
(PROGRAM=dg4msql)
)
)

more tnsnames.ora
CARS2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = EDWDEV)(PORT = 1521))
)
(CONNECT_DATA =
(SID = hds))
(HS=OK)
)
)

4. After restarting the listener check the tnsping status. It should return ok result.
5. Then connect with sqlplus and create a dblink as:
sql>CREATE PUBLIC DATABASE LINK CARS_LNK CONNECT TO “edw” IDENTIFIED BY “edw” USING 'CARS';
Database link created.
sql>
6. After that try to select any data using db link it prompt me errors:
SQL> SELECT * FROM DUAL@cars_lnk;
D
-
X

1 row selected.
SQL>

Please note that in Oracle 11g The User/Passwords are case sensitive so make sure to enclose both in double quots.
If you have multiple sqlserver databases then you have to perform same steps and create seperate init.ora files and place the entries in tnsnames.ora and listener.ora files.
Reference:

http://download-uk.oracle.com/docs/cd/B28359_01/gateways.111/b31043/sqlserver.htm
metalink note : 437374.1

Cheers!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Tracking & auditing changes in init.ora parameters

Auditing changes to init.ora parameters (via pfile or spfile) is an important DBA task.  Sometimes, users which have “alter system” privilege can make unauthorized changes to the initialization parameters in the spfile on a production database.  Hence, auditing changes to parameters is a critical DBA task.  Fortunately, it's quite simple to audit these changes by implementing the audit_sys_operations=true.

Here is a method to track changes to the initialization parameters.  In order to track all changes to parameters we can use audit for the alter system statement for any specific user

We should follow below steps to track changes to init.ora parameters:

1. ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
2. SHUTDOWN IMMEDIATE
3. STARTUP
4. CREATE USER TEST IDENTIFIED BY TEST;
5. GRANT DBA TO TEST;
6. AUDIT ALTER SYSTEM BY test;
7. CONN TEST/TEST
8. ALTER SYSTEM SET AUDIT_TRAIL=db SCOPE=SPFILE;

9.    Create an alert script to notify the DBA when a parameter has changed.

Let's start by finding the action_name in the dba_audit_trail view for the alter system command:

SQL> select username, timestamp, action_name from dba_audit_trail;

USERNAME                       TIMESTAMP  ACTION_NAME
------------------------------ -------------   ----------------------------
TEST                               29-MAY-09    ALTER SYSTEM


STEP 1
- We can track changes made by SYS user by setting audit_sys_operations parameter to TRUE.

SQL> alter system set audit_sys_operations=true scope=spfile;
System altered.

STEP 2 - Next, we bounce the instance to make the change take effect:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.

Here we see our auditing parameters:
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /home/oracle/oracle/product/10 .2.0/db_1/admin/fkhalid/adump
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB

SQL> alter system set audit_trail=db scope=spfile;
System altered.

STEP 3 - Here we go to the adump directory and examine the audit files:
SQL> host
[oracle@localhost bin]$ cd /home/oracle/oracle/product/10.2.0/db_1/admin/kam/adump/

[oracle@localhost adump]$ ls
ora_5449.aud ora_5476.aud ora_5477.aud ora_5548.aud ora_5575.aud ora_5576.aud

[oracle@localhost adump]$ cat ora_5576.aud
Audit file /home/oracle/oracle/product/10.2.0/db_1/admin/kam/adump/ora_5576.aud
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1/
System name:    Linux
Node name:      localhost.localdomain
Release:        2.6.18-92.el5
Version:        #1 SMP Tue Jun 10 18:49:47 EDT 2008
Machine:        i686
Instance name: kam
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 5576, image: oracle@localhost.localdomain (TNS V1-V3)
Fri May 29 02:38:30 2009
ACTION : 'alter system set audit_trail=db scope=spfile'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/2
STATUS: 0

STEP 4 - Now, create a crontab job to seek new entries in the adump directory. 
#******************************************************
# list the full-names of all possible adump files . . . .
#******************************************************
rm -f /tmp/audit_list.lst
find $DBA/$ORACLE_SID/adump/*.trc -mtime -1 -print >> /tmp/audit_list.lst
STEP 5 - When found, send the DBA an e-mail:
# If initialization paramneter has changed, send an e-mail
if [ -f /tmp/audit_list.lst]; then
then
    # Now, be sure that we don't clog the mailbox.
    # the following statement checks to look for existing mail,
    # and only sends mail when mailbox is empty . . . 
    if [ ! -s /var/spool/mail/oramy_sid ]
    then
       cat /oracle/MY_SID/scripts/oracheck.log | mail oramy_sid 
    fi
    sendmail  . . . 
fi

Please beware that using the auditing command imposes additional work on the production database.

Cheers!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Database Auditing (DDL & DML)

Today I want to share some simple trigger to set the database wise auditing.

Method1:

CREATE TABLE ddl_audit_log (
   stamp     DATE,
   username  VARCHAR2(30),
   osuser    VARCHAR2(30),
   machine   VARCHAR2(30),
   terminal  VARCHAR2(30),
   operation VARCHAR2(30),
   objtype   VARCHAR2(30),
   objname   VARCHAR2(30))
/

CREATE OR REPLACE TRIGGER fkhalid.audit_ddl_changes
   AFTER create OR drop OR alter
      ON FKHALID.SCHEMA  -- for individual schema auditing
      -- ON DATABASE     -- for whole database auditing
BEGIN
  INSERT INTO fkhalid.ddl_audit_log VALUES
        (SYSDATE,
         SYS_CONTEXT('USERENV', 'SESSION_USER')||' FOR '||sys.dictionary_obj_owner,
         SYS_CONTEXT('USERENV', 'OS_USER'),
         SYS_CONTEXT('USERENV', 'HOST'),
         SYS_CONTEXT('USERENV', 'TERMINAL'),
         ORA_SYSEVENT,
         ORA_DICT_OBJ_TYPE,
         ORA_DICT_OBJ_NAME
        );
END;
/
show errors

-- Now, let's try that
CREATE TABLE my_test_table (col1 DATE)
/
DROP TABLE my_test_table purge
/
SELECT * FROM ddl_audit_log
/


Method 2:

CREATE TABLE FKHALID.EVENT_TABLE
(
  OBJECT_OWNER  VARCHAR2(100 BYTE),
  OBJECT_NAME   VARCHAR2(30 BYTE),
  OBJECT_TYPE   VARCHAR2(20 BYTE),
  DATE_CREATED  DATE,
  DDL_TYPE      varchar2(50)
);

create or replace trigger fkhalid.after_create_trg
  after create
   on fkhalid.schema
begin
  /* This trigger tracks the creation of table, index, sequence,
      procedure, function, package and type. */
   if sys.dictionary_obj_type='TABLE'
   OR sys.dictionary_obj_type='SEQUENCE'
    OR sys.dictionary_obj_type='PROCEDURE'
     OR sys.dictionary_obj_type='INDEX'
      OR sys.dictionary_obj_type='FUNCTION'
       OR sys.dictionary_obj_type='TYPE'
        OR sys.dictionary_obj_type='PACKAGE'
    then
     insert into fkhalid.event_table values
          (sys.dictionary_obj_owner,sys.dictionary_obj_name, sys.dictionary_obj_type, sysdate,'CREATE');
   end if;
end;
/

create or replace trigger fkhalid.after_alter_trg
  after alter
   on fkhalid.schema
begin
  /* This trigger tracks the creation of table, index, sequence,
      procedure, function, package and type. */
   if sys.dictionary_obj_type='TABLE'
   OR sys.dictionary_obj_type='SEQUENCE'
    OR sys.dictionary_obj_type='PROCEDURE'
     OR sys.dictionary_obj_type='INDEX'
      OR sys.dictionary_obj_type='FUNCTION'
       OR sys.dictionary_obj_type='TYPE'
        OR sys.dictionary_obj_type='PACKAGE'
    then
     insert into fkhalid.event_table values
          (sys.dictionary_obj_owner,sys.dictionary_obj_name, sys.dictionary_obj_type, sysdate,'ALTER');
   end if;
end;
/

alter table test_table2
modify (ename varchar2(100),enumber number);

create table test_table2
(ename varchar2(20),enumber number);


Method 3:

SHOW PARAMETER AUDIT
ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
SHUTDOWN
STARTUP

-----------------------------------------------------
CREATE USER audit_test IDENTIFIED BY password
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp1
  QUOTA UNLIMITED ON users;

GRANT connect TO audit_test;
GRANT create table, create procedure TO audit_test;
AUDIT ALL BY audit_test BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY audit_test BY ACCESS;
AUDIT EXECUTE PROCEDURE BY audit_test BY ACCESS;
-----------------------------------------------------
CREATE TABLE test_tab (
  id  NUMBER
);

INSERT INTO test_tab (id) VALUES (1);
UPDATE test_tab SET id = id;
SELECT * FROM test_tab;
DELETE FROM test_tab;

DROP TABLE test_tab;
-----------------------------------------------------

SELECT username,
    extended_timestamp,
        owner,
        obj_name,
        action_name
FROM    dba_audit_trail
WHERE   owner = 'audit_test'
ORDER BY timestamp;

Hope this will help
Cheers!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!