SOAP Calls from Oracle

Hello,

Webservices are becoming a staple in inter-business data exchange.

If you’re running Oracle, the whole ETL process can be integrated in a few short steps.

First, you need to setup the ACL for the service requested.

exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(

acl          => ‘companyX.xml’,

description  => ‘Network permissions for CompanyX server’,

principal    => ‘LocalOracleAccount’,

is_grant     => TRUE,

privilege    => ‘connect’);

EXEC DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(

acl         => ‘companyX.xml’,

host        => ‘companyX.dataservices.com’,

lower_port  => 80);

GRANT XDB_WEBSERVICES to LocalOracleAccount;

GRANT XDB_WEBSERVICES_OVER_HTTP to LocalOracleAccount;

Then you’re ready to fetch the data from the service: Note: You need to know the EndPoint for initial WSDL/WADL. See the v_target_url variable.

DECLARE

extract_clob    CLOB;

v_soap_action     varchar2(255);

v_soap_request  varchar2(32767);

v_soap_response  varchar2(32767);

v_http_req   utl_http.req;

v_http_resp   utl_http.resp;

v_target_url in varchar2 default ‘http://companyx.dataservices.com/DATA/oltpservices.asmx?WSDL’;

v_soap_envelope  varchar2(5000):='<soap:Envelope xmlns:soap=”http://www.w3.org/2003/05/soap-envelope

xmlns:oltp=”dataservices/DATA/oltpservices/”>

<soap:Header/>    <soap:Body>

<oltp:DailySalesaggregation>

</soap:Body>

</soap:Envelope>’;

BEGIN

DBMS_LOB.createtemporary(extract_clob, FALSE);

v_soap_request := v_soap_envelope;

http_req:= utl_http.begin_request( v_target_url, ‘POST’, ‘HTTP/1.1’);

utl_http.set_body_charset(v_http_req, ‘UTF-8’);

utl_http.set_header(v_http_req, ‘Content-Type’, ‘text/xml;charset=”UTF-8″‘);

utl_http.set_header(v_http_req, ‘Content-Length’, length(v_soap_request));

utl_http.set_header(v_http_req, ‘SOAPAction’, v_soap_action);

utl_http.write_text(v_http_req, v_soap_request);

v_http_resp:= utl_http.get_response(v_http_req);

LOOP

UTL_HTTP.read_text(v_http_resp, v_soap_response, 32767);

DBMS_LOB.writeappend (extract_clob, LENGTH(v_soap_response), v_soap_response);

END LOOP;

EXCEPTION   WHEN UTL_HTTP.end_of_body THEN

UTL_HTTP.end_response(v_http_resp);

At this point you can manipulate the lob to transform and load the extracted data in the database.

 

Pierre

Lost your APEX admin password?

Recently I stumbled on an Apex 4.0 installation containing critical applications to which new users were needed. The problem was that the consultants involved in the development/deployment had left the site. In order to proceed I needed the admin password which was unknown to me.

So I proceeded like this:

  • Logged into the database as SYSDBA
  • Changed the APEX user’s password, in this occurence the username was: APEX_040000
  • Once logged as APEX_040000 I set the security group id to 10:

        exec wwv_flow_api.set_security_group_id(p_security_group_id=>10);

  • Created a new ADMIN user to gain access:

        exec wwv_flow_fnd_user_api.create_fnd_user(p_user_name => ‘tempo_admin’, p_web_password => ‘tempo_admin’) ;

  • Then went to URL: http://server:port/pls/htmldb/htmldb_admin (if you don’t know the port, look in portlist.ini).
  • Entered the temporary admin user and password.
  • At this point I was prompted to change the password.
  • Once in, I changed the ADMIN user password, created the new users in their respective workspace and dropped the temporary admin user.

 

I hope this will help.

Pierre

Some Oracle 10g Best Practices

  • Learn to decipher Awr reports.
  • Create index only where needed, drop unneeded indexes.
  • Use outlines if code is not modifiable.
  • Use RAID-5 for OLTP systems, 85-95% of io’s are reads anyway.
  • Create tablespace with extent management local and segment space management auto
  • If you cannot use the datapump:
    • TIP #1: Export with buffer=several hundred MB, recordlength=65535, direct=y
    • TIP #2: Import with commit=n, buffer=several hundred MB, recordlength=65535; and a large UNDO.
  • Use SGA_TARGET, don’t bother setting up the sga yourself.
  • In the Grid Control, set tablespace alert threshold to 92%, the default at 97% is too risky. Better, create a monitoring template.
  • Rebuild indexes online regularly; forget about validating the need to since some application indexes might be locked all the time. And as it is time consuming to find which indexes really need a rebuild.
    • TIP: If you can afford, recreate the index instead, it’s better than a rebuild
  • On a normal OLTP database, let Oracle gather the stats. Don’t bother.
  • Reorganize the tables by using alter table move tablespace ….
  • Cache all small static table into a buffer keep pool.
  • Scan the db regularly for costly full table scans.
  • Forget about the myth of restarting the database weekly to supposedly clean the instance, it flushes your cache and some views might suffer from this. In one instance, the view took 1h30 to re-cache itself.
  • If you see queries with lots of outer joins, validate each and every outer joins, some developers don’t take any chances!!! At one client, I saved them 2 cpu by removing 7 outer joins in a table trigger.

Compare timestamps in a text file

I needed to compare 2 timestamps in a log file and come up with the newest.

Here’s the little script I wrote:

  1. I needed to remove the blanks and non-numerical characters
  2. Then compare the numerical strings

Before proceeding make sure to convert your string in this order: year, month, day, hh24, mi, ss

#!/bin/sh

DATE1=”2008 September 12 11:30:45″

DATE2=”2008 September 12 12:45:32″

dateval1=`print ${DATE1} | sed ‘s/[^0-9]//g’`

echo $dateval1

20080912113045

echo $dateval2

20080912124532

if [ $dateval1 -gt $dateval2 ]; then
echo $dateval1
else
echo $dateval2
fi

Rman Backup script on Windows

Hello,

Here’s a script I created recently to backup a database under Windows with RMAN.

Hope you enjoy it.

Pierre

The script takes 4 parameters:

  • Target Database Sid
  • Rman Database Sid
  • Rman Database Password
  • Level of Rman backup

—–  Begin Script —–

SET ORACLE_HOME=E:\ORACLEORA10G
SET ORACLE_SID=%1%
SET RMAN_DB=%2%
SET CATALOG_PASS=%3%
SET LEVELBCK=%4%
SET CATALOG_PASS=rman/%CATALOG_PASS%@%RMAN_DB%
SET RMAN_DEST=K:\SAUVEGARDESRMAN%ORACLE_SID%
SET LOG_DEST=K:\SAUVEGARDESLOGS
SET NLS_LANG=AMERICAN_AMERICA.UTF8
SET TMPDIR=E:\ORACLETEMPSCRIPTS

@echo on

cd %TMPDIR%

REM This bit generates the RMAN script to backup database,
REM archivelogs and control file and then crosscheck output.
echo run { > rman_%ORACLE_SID%.rcv
echo allocate channel d1 type disk; >> rman_%ORACLE_SID%.rcv
echo allocate channel d2 type disk; >> rman_%ORACLE_SID%.rcv
echo allocate channel d3 type disk; >> rman_%ORACLE_SID%.rcv
echo backup incremental level %LEVELBCK% format
‘%RMAN_DEST%DBF_%ORACLE_SID%_t%%t_s%%s_p%%p.db’
database;>>rman_%ORACLE_SID%.rcv
echo sql ‘alter system archive log current’; >>rman_%ORACLE_SID%.rcv
echo backup archivelog all format
‘%RMAN_DEST%ARC_%ORACLE_SID%_t%%t_s%%s_p%%p.arc’;>>rman_%ORACLE_SID%.rcv
echo backup current controlfile format
‘%RMAN_DEST%CTL_%ORACLE_SID%_t%%t_s%%s_p%%p.ctl’;>>rman_%ORACLE_SID%.rcv
echo release channel d1; >> rman_%ORACLE_SID%.rcv
echo release channel d2; >> rman_%ORACLE_SID%.rcv
echo release channel d3; >> rman_%ORACLE_SID%.rcv
echo } >> rman_%ORACLE_SID%.rcv
echo allocate channel for maintenance type disk; >> rman_%ORACLE_SID%.rcv
echo sql ‘alter system archive log current’; >> rman_%ORACLE_SID%.rcv
echo crosscheck backup; >> rman_%ORACLE_SID%.rcv
echo crosscheck backup of archivelog all; >> rman_%ORACLE_SID%.rcv
echo crosscheck backup of controlfile; >> rman_%ORACLE_SID%.rcv
echo release channel; >> rman_%ORACLE_SID%.rcv
echo exit >> rman_%ORACLE_SID%.rcv

REM This starts RMAN, executes the script created earlier, then exits and
tidies up.
cd %RMAN_DEST%
if %LEVELBCK%==1 goto INCR
if %LEVELBCK%==0 goto FULL

:FULL
del %RMAN_DEST%*.* /q
rman target / catalog=%CATALOG_PASS% cmdfile=%TMPDIR%rman_%ORACLE_SID%.rcv
msglog=%LOG_DEST%rman_%ORACLE_SID%.log
del %TMPDIR%rman_%ORACLE_SID%.rcv
goto END
:INCR
rman target / catalog=%CATALOG_PASS% cmdfile=%TMPDIR%rman_%ORACLE_SID%.rcv
msglog=%LOG_DEST%rman_%ORACLE_SID%.log
del %TMPDIR%rman_%ORACLE_SID%.rcv
:END

—- end script —-

Network import with DataPump

With DataPump, there is no need to create an export file anymore, if the sole purpose is to import data.

 

Before proceeding with the import, the following needs to be configured:

 

  • Create a streams pool (50Mb)
  • Create a directory (Since there is no export file involved, it serves as the location of the datapump logfile, there is no export file involved)
  • Grant read-write privileges on the datapump directory to the datapump user
  • Create a public database link to the remote database

 

Import with DataPump

Generic Example: (in this case I connected to the remote schema directly)

 

  1. alter system set streams_pool_size=50Mb scope=both;
  2. create directory DATAPUMP_DIR as ‘/export/datapump’;
  3. grant read, write on directory DATAPUMP_DIR to DPEDEV;
  4. create public database link RDPEPRD connect to DPEPRD identified by DPEPRD using ‘rdpeprd’;
  5. impdp dpedev/dpedev directory=datapump_dir network_link=RDPEPRD REMAP_SCHEMA=DPEPRD:DPEDEV

 Make sure the remote user has the EXP_FULL_DATABASE role

Pierre

10 Facts about Automatic Statistics Gathering

I regularly receive inquiries about automatic statistics gathering which is controlled by a default job (gather_stats_job) installed with any new 10G/11G database.

 

Here are some facts I’ve compiled so far:

 

  • Job runs daily in 10g, weekly in 11g

 

  • Job runs in the default maintenance window (opened from 10pm to 6am)

 

  • Gather statistics on objects with missing or stale statistics

 

  • Can be resource intensive depending on daily activity

 

  • Objects that are in most need are processed first

 

  • The default gathering parameters can be modified using DBMS_STATS.SET_PARAM

 

  • Run DBMS_SCHEDULER.DISABLE(‘GATHER_STATS_JOB’) to disable the job

 

  • For highly volatile tables (truncated once or many times during the day), it’s preferrable to set the statistics to NULL and let the dynamic sampling kick in, use DBMS_STATS.DELETE_STATS and LOCK_TABLE_STATS

 

  • Statistics are not gathered on external tables, they must be gathered individually using DBMS_STATS.GATHER_TABLE_STATS

 

  • Statistics are not gathered on fixed objects , use GATHER_FIXED_OBJECTS_STATS during a representative workload