Oracle for humans

In this demo VMware Fusion (or for Windows VMware workstation), Enterprise-R5-U5-Server-i386-dvd.iso, Oracle Database Gateways 11g Release 2 (11.2.0.1.0) for Linux x86-64 will be used.







--> Make a directory for gateway software installation by running following commandmkdir -p /data/app/gateway
[oracle@samir gateways]$ pwd
/mnt/hgfs/SOFTWARE/gateways
[oracle@samir gateways]$ ./runInstaller

Choose next
-->

Name = oracle2mssqlserver
Path = /data/app/gateway


-->


Check dependencies


--> In the next screen we will need following:
 
1. SQL server database server host name        : mssqlserver
2. SQL server Database server port number     : 1433
3. SQL server instance name                            : WINMINI-MSSQL\SAMIR
4. SQL server database name                           : home

--> NB
Press “Next”


Check summary end press install.



-->
 
--> /data/app/oracle/root.sh
[root@samir Server]# /data/app/gateway/root.sh
Running Oracle 11g root.sh script...
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /data/app/gateway
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: n
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: n
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: n
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.

Press OK

-->

--> Done




Configuration assistant "Oracle Net Configuration Assistant" failed
-----------------------------------------------------------------------------
The "/data/app/gateway/cfgtoollogs/configToolFailedCommands" script contains all commands that failed, were skipped or were cancelled. This file may be used to run these configuration assistants outside of OUI. Note that you may have to update this script with passwords (if any) before executing the same.

cat /data/app/gateway/cfgtoollogs/configToolFailedCommands
# Copyright (c) 1999, 2009, Oracle. All rights reserved.
/data/app/gateway/bin/netca /orahome /data/app/gateway /orahnam oracle2mssqlserver /instype custom /inscomp client,oraclenet,server /insprtcl tcp /cfg local /authadp NO_VALUE /responseFile /data/app/gateway/network/install/netca_typ.rsp


No errors, I have canceled installation of listener.

Press NEXT + exit.

Next step will be Gateway configuration:

-->
cat initdg4msql.ora

HS_FDS_CONNECT_INFO=[mssqlserver]:1433//home
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER


Lets check Listener.
-->
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl01)
(ORACLE_HOME = /data/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = orcl01)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl02)
(ORACLE_HOME = /data/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = orcl02)
)
(SID_DESC =
(SID_NAME=dg4msql)
(ORACLE_HOME=/data/app/gateway)
(PROGRAM=dg4msql)
(ENVS=LD_LIBRARY_PATH=/data/app/gateway/dg4msql/driver/lib;/data/app/oracle/product/11.2.0/dbhome_1)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = samir)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /data/app/oracle


lsnrctl

LSNRCTL> reload
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 29-OCT-2013 10:57:26
Uptime 0 days 8 hr. 57 min. 10 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /data/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /data/app/oracle/diag/tnslsnr/samir/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=samir)(PORT=1521)))
Services Summary...
Service "dg4msql" has 1 instance(s).
Instance "dg4msql", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl01" has 2 instance(s).
Instance "orcl01", status UNKNOWN, has 1 handler(s) for this service...
Instance "orcl01", status READY, has 1 handler(s) for this service...
Service "orcl01XDB" has 1 instance(s).
Instance "orcl01", status READY, has 1 handler(s) for this service...
Service "orcl02" has 1 instance(s).
Instance "orcl02", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

--> Check tnsnames , add following red block to tnsnames.ora

ORCL02 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = samir)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl02)
)
)
ORCL01 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = samir)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl01)
)
)
dg4msql =
(DESCRIPTION =
(ADDRESS = (PROTOCOL=TCP)(HOST=samir)(PORT=1521))
(CONNECT_DATA = (SID=dg4msql))
(HS=OK)
)

-->

[oracle@samir ~]$ sqlplus / as sysdba
SQL> ; select name from v$database;
NAME
---------
ORCL01

create public database link micro
connect to "ja"
identified by "******"
using 'dg4msql';

SQL> select count (*) from racun@micro;
COUNT(*)
----------
19434


SQL> desc myracun
ERROR:
ORA-04043: object myracun does not exist

SQL> create view myracun
as
select * from racun@micro
/
View created.

SQL>  desc myracun
Name Null? Type
----------------------------------------- -------- ---------------
Datum NOT NULL VARCHAR2(12)
Rekening NOT NULL NVARCHAR2(34)
Begunstigde NOT NULL NVARCHAR2(45)
Tegenrekening NOT NULL NVARCHAR2(34)
Bedrag NOT NULL NUMBER(9,2)
Mutatiecode NOT NULL NVARCHAR2(45)
Mededelingen NOT NULL NVARCHAR2(260)
Mutatiepost NOT NULL NVARCHAR2(45)

SQL> select count (*) from myracun;
COUNT(*)
----------
19434
NB
-->