Create Database link for MS-SQL in Oracle

High level Steps:

1. Install and configure FreeTDS to get required ODBC drivers
2. Configure tns and listener services
3. Create DB Link

Detailed Steps:

Step 1)

Download Freetds from the following link

ftp://ftp.freetds.org/pub/freetds/stable/freetds-patched.tar.gz

Install Freetds as root user.

Installation steps:

1) Unzip the freetds-patched.tar.gz compressed file.
2) Change directory to unzipped location and execute the following one by one.
3) ./configure –prefix=/usr/local/freetds
4) make
5) make install

Free TDS installation is completed.

Step 2)

Edit /etc/odbc.ini and add the following lines.

[<DB NAME>-connector]
Description = MS SQL connection to ‘<DB NAME>’ database
Driver = FreeTDS
Database = <MS SQL DB NAME>
Server = <MS SQL DB HOSTNAME>
UserName = <MS SQL DB Username>
Password = <MS SQL DB Password>
Trace = Yes
TraceFile = /tmp/freetds.log
TDS_Version = 7.0
Port = <MS SQL Port Number>

Example:

[MYDB-connector]
Description = MS SQL connection to ‘MYDB’ database
Driver = FreeTDS
Database = MYDB
Server = MYSQL-DB-HOST
UserName = sriram
Password = sql@123
Trace = Yes
TraceFile = /tmp/freetds.log
TDS_Version = 7.0
Port = 1433
Step 3)

Edit /etc/odbcinst.ini file and add the following lines. Make sure libstdsodbc.so is available at the specified location or change the location appropriately.

# Driver for MS_SQL
[FreeTDS]
Description = ODBC for Microsoft SQL
Driver = /usr/local/freetds/lib/libtdsodbc.so
UsageCount = 1
Threading = 2
Step 4)

1) Navigate to $ORACLE_HOME/hs/admin

2) Create a file with name initdg4mssql.ora like specified below. Following is the content of initdg4mssql.ora from my machine.

#############################################################################
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC

#
# HS init parameters
#
HS_FDS_CONNECT_INFO =MYDB-connector
HS_FDS_TRACE_LEVEL = 0
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_RPC_FETCH_REBLOCKING= OFF
HS_FDS_FETCH_ROWS = 1
#HS_NLS_NCHAR = AMERICAN_AMERICA.US7ASCII
#HS_LANGUAGE = AMERICAN_AMERICA.US7ASCII
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
HS_FDS_SUPPORT_STATISTICS=FALSE

#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set
#############################################################################
Step 5)

Configure TNS and listener entries.

1) Navigate to $TNS_ADMIN.

2) Edit tnsnames.ora and append the following as per your environment. Assign an unused port to the new listener service

dg4mssql =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=<ORACLE HOST>)(PORT=<LISTENER PORT>))
(CONNECT_DATA=(SID=dg4mssql))
(HS=OK)
)

MYDB =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=<MS SQL DB HOST>)(PORT=<MS SQL SERVER PORT>))
(CONNECT_DATA=(SID=MYDB))
(HS=OK)
)

3) Edit listener.ora and append the following. You might face challenges related to configuring and starting listener service, so make sure you cross check the listner.ora and tnsnames.ora if you have any issues starting the listener.
LISTENER_dg4mssql =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <ORACLE HOST>)(PORT = <LISTENER PORT>))
)
)

SID_LIST_LISTENER_dg4mssql =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME= <ORACLE_HOME>)
(SID_NAME = dg4mssql)
(PROGRAM=dg4odbc)
)
)

Step 6)  Create DB Link in Oracle Database for MS-SQL Database.
CREATE DATABASE LINK SQLSERVER.MYSERVER CONNECT TO “SRIRAM” IDENTIFIED BY “sql@123” USING ‘dg4mssql’;
Once the above steps are complete, you should be able to access MS-SQL data from Oracle database using database link.

Oracle EBS R12.2 startup and shutdown scripts

Create the following 2 files to start and stop R12.2 application tier. Change the apps and weblogic passwords appropriately.
start_R12.sh
============
/u01/oracle/DEV01/fs1/inst/apps/dev01_server2/admin/scripts/adstrtal.sh apps/apps <<EOF
admin123
EOF
——————————————————————————————-
stop_R12.sh
============
/u01/oracle/DEV01/fs1/inst/apps/dev01_server2/admin/scripts/adstpall.sh apps/apps <<EOF
admin123
EOF

How to restart Oracle Standby Database (DG)

————————————————–
STARTUP PROCESS
————————————————–

Starting Primary Database: (PROD)

sqlplus “/as sysdba”

SQL> Startup

Startup Standby Database: (PRODDG)

SQL> sqlplus “/as sysdba”

SQL> startup nomount — Nomount state

SQL> alter database mount standby database;  — Mount in Standby mode

SQL> alter database recover managed standby database disconnect from session; — Start redo apply (recovery)

To verify the logs are applied on Standby, execute the below syntax on both primary and standby

SQL> select thread#, sequence#-1 from v$log where status = CURRENT;

SQL> select * from v$archive_dest_status where status != ‘INACTIVE’;

SQL> select sequence#, applied, to_char(first_time, mm/dd/yy hh24:mi:ss) first from v$archived_log order by first_time;

————————————————–
SHUTDOWN PROCESS
————————————————–

Shutting down Standby Database:

sqlplus “/as sysdba”

SQL> alter database recover managed standby database cancel;  — Cancel recovery fist.

SQL> shutdown immediate — Shutdown database

Shuttting down Primary Database:

SQL> sqlplus “/as sysdba”

SQL> shutdown immediate

Switchover and switch back oracle standby database using Dataguard broker

————————————————————————————————————————————
SWITCHOVER Process using Datagaurd Broker
————————————————————————————————————————————

Login to Datagarud Broker on Primary Database

dgmgrl sys/<SYS_PWD>@PROD

Verify status of the databases

DGMGRL> show configuration ##– It should return the status Successful

DGMGRL> show database ‘PROD’ ##– To verify PROD

DGMGRL> show database ‘PRODDG’

Perfrom Switchover. (When performed a switchover Standby Database becomes Primary and Primary database becomes Standby)

DGMGRL> switchover to ‘PRODDG’;

— Ignore any warnings in alert log, and it’s usual when tns switch is in progress.

————————————————————————————————————————————

Verify the status of the standby database ‘PRODDG’

Login to Standby server (PRODDG) as SYSDBA

sqlplus “/as sysdba”

select name,open_mode from v$database ##– It should return PROD and Read,Write

————————————————————————————————————————————

Now login to Primary server (PROD) as SYSDBA to make it standby

sqlplus “/as sysdba”

startup nomount — Nomount state

alter database mount standby database;  — Mount in Standby mode

————————————————————————————————————————————

Login to Dataguard Broker and verify the configuration (It must show PROD as Standby database and PRODDG as Primary database)

DGMGRL> show configuration ##– It should return the status Successful

————————————————————————————————————————————
Switch Back to PROD
————————————————————————————————————————————

DGMGRL> switchover to ‘PROD’;

And follow the recovery procedure on Standby PRODDG

How to open Oracle Apps R12 forms in Internet Explorer 11 (IE. 11)

1) In internet explorer 11 , navigate to Tools –> Compatibility View Settings . Then add Domain name in the URL (ex: orapps.com in erp.oraapps.com) using Add this website option.

2) Navigate to Tools –> Internet options –> Security . Select internet and click on Custom level. Scroll down till end of the options and change the option Enable XSS filter to  Disable.

Adding Step 3 (08-Mar-2016)

3) Add EBS URL as Intranet Site in Internet Options to avoid most of the Java security related issues.

 

This solution worked for me and I am able to open R12 forms in Internet Explorer version 11.

How to create APPS readonly user in Oracle Applications

Step 1:

Connect as SYS user to create APPSRO user

create user appsro identified by appsro default tablespace APPS_TS_TX_DATA;

grant connect, resource to appsro;

grant create synonym to appsro;

exit;

 

Step 2:

Connect as APPS user and run the SQL commands:

bash $ sqlplus apps/apps

SQL>set head off

SQL> set newpage none

SQL> set pagesize 9999

SQL> spool create_synonyms.sql

 

SQL> select ‘create synonym ‘ || OBJECT_NAME || ‘ for ‘ || OWNER ||’.’ ||OBJECT_NAME || ‘;’ from all_objects where OWNER not in (‘SYS’,’SYSTEM’) and OBJECT_NAME not like ‘%/%’ and OBJECT_TYPE in (‘TABLE’,’VIEW’,’SYNONYM’,’PACKAGE’,’PACKAGE BODY’,’PROCEDURE’,’FUNCTION’);

 

SQL> spool off

 

SQL> spool grant_select.sql

 

SQL> select ‘grant select on ‘|| OWNER ||’.’ ||OBJECT_NAME || ‘ to appsro;’ from all_objects where OWNER not in (‘SYS’,’SYSTEM’) and OBJECT_NAME not like ‘%/%’ and OBJECT_TYPE in (‘TABLE’,’VIEW’,’SYNONYM’);

 

SQL> spool off

SQL> spool grant_execute.sql

 

SQL> select ‘grant execute on ‘|| OWNER ||’.’ ||OBJECT_NAME || ‘ to appsro;’ from all_objects where OWNER not in (‘SYS’,’SYSTEM’) and OBJECT_NAME not like ‘%/%’ and OBJECT_TYPE in (‘PACKAGE’,’PROCEDURE’,’FUNCTION’);

 

SQL> spool off

SQL> exit;

 

Step 3:

 

– connect as sysdba :

bash $ sqlplus “/as sysdba”

SQL> @grant_select.sql  — To grant select privileges on TABLES,VIEWS and SYNONYMS.

SQL>@grant_execute.sql — To grant execute privileges on PACKAGES, FUNCTIONS and PROCEDURES.

SQL> exit;

 

Step 4:

 

– connect as appsro

bash $ sqlplus appsro/appsro

SQL> @create_synonyms.sql

SQL> exit;

EAM Meter Creation API sample code

declare
l_return_status VARCHAR2(80);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
l_new_meter_id NUMBER;
Begin
eam_meter_pub.create_meter(
1.0,–p_api_version IN Number,
FND_API.G_FALSE,–p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
FND_API.G_FALSE,–p_commit IN VARCHAR2 := FND_API.G_FALSE,
FND_API.G_VALID_LEVEL_FULL,–p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
l_return_status,–x_return_status OUT nocopy VARCHAR2,
l_msg_count,–x_msg_count OUT nocopy NUMBER,
l_msg_data,–x_msg_data OUT nocopy VARCHAR2,
‘Sample Meter’,–p_meter_name IN VARCHAR2,
‘HR’,–p_meter_uom IN VARCHAR2,
1,–p_METER_TYPE IN Number default 1,
1,–p_VALUE_CHANGE_DIR IN Number DEFAULT 1,
‘Y’,–p_USED_IN_SCHEDULING IN VARCHAR2 default ‘N’,
20,–p_USER_DEFINED_RATE IN Number default null,
450,–p_USE_PAST_READING IN Number default null,
‘Testining Meter API’,–p_DESCRIPTION IN VARCHAR2 default null,
sysdate,–p_FROM_EFFECTIVE_DATE IN DATE default null,
null,–p_TO_EFFECTIVE_DATE IN DATE default null,
null,–p_source_meter_id IN Number DEFAULT NULL,
1,–p_factor IN NUMBER DEFAULT 1,
null,–p_relationship_start_date IN DATE default null,
null,–p_ATTRIBUTE_CATEGORY IN VARCHAR2 default null,
null,–p_ATTRIBUTE1 IN VARCHAR2 default null,
null,–p_ATTRIBUTE2 IN VARCHAR2 default null,
null,–p_ATTRIBUTE3 IN VARCHAR2 default null,
null,–p_ATTRIBUTE4 IN VARCHAR2 default null,
null,–p_ATTRIBUTE5 IN VARCHAR2 default null,
null,–p_ATTRIBUTE6 IN VARCHAR2 default null,
null,–p_ATTRIBUTE7 IN VARCHAR2 default null,
null,–p_ATTRIBUTE8 IN VARCHAR2 default null,
null,–p_ATTRIBUTE9 IN VARCHAR2 default null,
null,–p_ATTRIBUTE10 IN VARCHAR2 default null,
null,–p_ATTRIBUTE11 IN VARCHAR2 default null,
null,–p_ATTRIBUTE12 IN VARCHAR2 default null,
null,–p_ATTRIBUTE13 IN VARCHAR2 default null,
null,–p_ATTRIBUTE14 IN VARCHAR2 default null,
null,–p_ATTRIBUTE15 IN VARCHAR2 default null,
null,–p_ATTRIBUTE16 IN VARCHAR2 default null,
null,–p_ATTRIBUTE17 IN VARCHAR2 default null,
null,–p_ATTRIBUTE18 IN VARCHAR2 default null,
null,–p_ATTRIBUTE19 IN VARCHAR2 default null,
null,–p_ATTRIBUTE20 IN VARCHAR2 default null,
null,–p_ATTRIBUTE21 IN VARCHAR2 default null,
null,–p_ATTRIBUTE22 IN VARCHAR2 default null,
null,–p_ATTRIBUTE23 IN VARCHAR2 default null,
null,–p_ATTRIBUTE24 IN VARCHAR2 default null,
null,–p_ATTRIBUTE25 IN VARCHAR2 default null,
null,–p_ATTRIBUTE26 IN VARCHAR2 default null,
null,–p_ATTRIBUTE27 IN VARCHAR2 default null,
null,–p_ATTRIBUTE28 IN VARCHAR2 default null,
null,–p_ATTRIBUTE29 IN VARCHAR2 default null,
null,–p_ATTRIBUTE30 IN VARCHAR2 default null,
‘N’,–p_TMPL_FLAG IN VARCHAR2 default ‘N’,
null,–p_SOURCE_TMPL_ID IN Number default null,
0,–p_INITIAL_READING IN Number default 0,
sysdate,–P_INITIAL_READING_DATE IN DATE default SYSDATE,
‘N’,–P_EAM_REQUIRED_FLAG IN VARCHAR2 default ‘N’,
l_new_meter_id);–x_new_meter_id OUT nocopy Number);

IF l_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
DBMS_OUTPUT.put_line (‘Create EAM Meter is Successful : ‘||l_new_meter_id);
ELSE
DBMS_OUTPUT.put_line (‘Create EAM Meter Failed with the error :’||l_msg_count||’ ‘||l_return_status);
ROLLBACK;
END IF;
end;

How to fetch ddl of a table in oracle.

 

simply execute dbms_metadata.get_ddl

——–——–——–——–——–——–

spool ddl.sql

set heading off;
set echo off;
Set pages 999;
set long 90000;

SELECT dbms_metadata.get_ddl(‘TABLE’, ‘TABLE_NAME’) FROM dual;

spool off;

——–——–——–——–——–——–

FDPSTP failed due to ORA-20005: object statistics are locked

This is an expected behavior after 11g upgrade.

Below query shows the list of objects where stats are  locked.

SELECT table_name,stattype_locked FROM dba_tab_statistics where stattype_locked=’ALL’

Solution:

exec dbms_stats.unlock_table_stats(‘<OWNER>’,'<TABLE_NAME>’);

ORA-20002: Version of statistics table APPLSYS.FND_STATTAB is too old

I have faced this issue after upgrading E-Biz database from 9.2 to 11gR2.

Issue:

ORA-20002: Version of statistics table APPLSYS.FND_STATTAB is too old

Solution:

1. Connect to sqlplus / as sysdba
2. Backup FND_STATTAB table
2. run: EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE(‘APPLSYS’,’FND_STATTAB’);