Oracle Generic Heterogeneous Services and Transparent Gateways

RHEL 4.5 Oracle 10.2

1. install package unixODBC
2. install package freeTDS, if u don’t have it for ur linux go to www.freetds.org and get the source
3. configure /etc/freetds.conf (path can be different)
4. try to connect with tsql
5. configure files /etc/odbc.ini, /etc/odbcinst.ini, see www.unixodbc.org for details
6. try to connect with isql (user and password are obligatory parameters)
7. create and customize an initialization file for your Generic Connectivity agent $ORACLE_HOME/hs/admin/inithsodbc.ora

# HS init parameters

#

HS_FDS_CONNECT_INFO = mssql

HS_FDS_TRACE_LEVEL = off

HS_FDS_SHAREABLE_NAME = /usr/lib/libodbc.so

#

# ODBC specific environment variables

#

set ODBCINI=/etc/odbc.ini

8. add record in tnsnames.ora

HS1 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = tcp)(HOST = sydora2)(PORT = 1521))

)

(CONNECT_DATA =

(SID = hsodbc)

)

(HS=OK)

)

9. add record in listener.ora

SID_LIST_LISTENER_SYDORA2 =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /opt/oracle/app/oracle/product/10.2.0/db_1)

(PROGRAM = extproc)

)

(SID_DESC =

(SID_NAME = hsodbc)

(ORACLE_HOME = /opt/oracle/app/oracle/product/10.2.0/db_1)

(PROGRAM = hsodbc)

(ENVS=LD_LIBRARY_PATH=/opt/oracle/app/oracle/product/10.2.0/db_1/lib:/usr/local/lib:/usr/lib)

)

)

10. restart listener
11. create and test db link
12. repeat steps on another node if it is a cluster and test it on each node

cloning host with oracle database 9.2, 10.2

scenario:
host was cloned to VMware.
oracle version 9.2.0.6
windows server 2003

it was also repeated with
oracle database 10.2.03
windows server 2003 SP1

task:
rename database, instance and listener on new host
old db name: dcsdrp01
new db name: dcsdrt01
solution:

1. change tnsnames.ora for existing database (or u might connect to real production if u use tns)

2. add new database in tnsnames.ora

3. change listener.ora if u need listener (it has wrong host name)

4. create proper directories under d:\oracle\admin (depends on name convention and directory structure)

5. prepare new init.ora, default location for init.ora in windows is $ORACLE_HOME\database

6. prepare script to recreate control file.

use alter database backup controlfile to trace; and look in udump directory.

7. shutdown new database

8. configure listener.ora for new database

9. create password file for new database

orapwd file=D:\oracle\ora92\database\PWDDCSDRT01.ora password=password entries=10

10.  create windows service and START it! (you can not login to idle instance without it!)

oradim -new -sid DCSDRT01 -intpwd password -startmode a -pfile D:\oracle\admin\DCSDRT01\pfile\init.ora

11.  make sure original database (dcsdrp01) is down

12.  rename directories according to new init.ora file.

13.  delete current control files

14.  set ORACLE_SID=dcsdrt01

15.  sqlplus "/ as sysdba"

16.  startup nomount

17.  select * from v$instance;

18.  @new_controlfile.sql

19. alter database open resetlogs;

20. alter database rename global_name to dcsdrt01;

21. alter system switch logfile;

22. check connectivity thru listener

connect sys/password@dcsdrt01 as sysdba;

alter user system identified by password;

connect system/password@dcsdrt01

23. delete windows service of old instance

oradim -delete -sid dcsdrp01

24. disable or delete windows service of old listener

sc delete OracleOUIHome_dcsdrc2TNSListenerlistener_9i_dcsdrp01

Oracle LogMiner (1)

Environment:

Oracle 9i (9.2.0.6)

select * from v$logfile;
-- using online redo log
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/redo/01/dckidq01/redo11.log', OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);

select * from V$LOGMNR_CONTENTS
where seg_name ='S_PERIOD';
-- end and cleanup
EXECUTE DBMS_LOGMNR.END_LOGMNR;