Multiple EXTPROC in 11.2 /EXADATA and 12c PDBs

we know that external procedures or C libraries can interface with oracle database  through extproc. it is defined as follows.

CREATE OR REPLACE  LIBRARY libname as '$APP_V1_HOME/appexe.so' within the database and write a plsql wrapper over it.

There are two ways to set the value for the environment variable APP_V1_HOME defined during library creation. They are

•  extproc.ora
•  SID_LIST_LISTENER.

extproc.ora : The value of APP_VI_HOME used to be stored in listener.ora under SID_LIST_LISTENER until oracle version 11gr2.  However in 11gr2, Oracle introduced the concept of a single file where all the environment variables can be stored. The file is in $ORACLE_HOME/hs/admin/extproc.ora . There are no configuration changes required for either listener.ora or tnsnames.ora.

NOTE: In case this is a RAC or exadata, update the extproc.ora on ORACLE_HOME and not GRID home across all the nodes.

Working with the above example, the information in extproc.ora will be

SET APP_V1_HOME=/home/app/9.3

SET EXTPROC_DLL=ANY

if you had a second app or a newer version of the app in a different directory, all we need to do is add that info in extproc.ora as follows

# Specify the EXTPROC_DLLS environment variable to restrict the DLLs that extproc is allowed to load.

# Without the EXTPROC_DLLS environment variable, extproc loads DLLs from ORACLE_HOME/lib 

SET EXTPROC_DLL=ANY

# APP version V1

SET APP_V1_HOME=/home/app/9.3

#newer app version V2

SET APP_V2_HOME=/home/app/9.4

and define the library in the database as follows

CREATE OR REPLACE  LIBRARY libname as '$APP_V2_HOME/appexe.so' .

Now the above will work within the database and across different databases in the same ORACLE_HOME.

2) SID_LIST_LISTENER

Until 11.2 , the only way to configure extproc was through listener.ora configuration. 

The steps are

1) configure listener.ora

2) add tnsname.ora entry

3) define library with AGENT clause

1) Configure listener.ora.

Create a  separate listener for extproc and define the individual environments in SID_LIST LISTENER.

In case of RAC /Exadata environment, use the ORACLE_HOME to do this. The GRID_HOME is maintained by OraAgent and does some automatic configuration of LOCAL_LISTENER parameters if the file is not touched. Also, i see no additional advantage starting listener for extproc from GRID_HOME.

Below is the sample listing of listener.ora. Note that the enthronement variable APP_HOME points to different directories for different SID_NAME.

The  SID_NAME NEED NOT correspond to the database names. They are just place holders.

LISTENER_DLIB =
(DESCRIPTION_LIST =
  (DESCRIPTION =
  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROCDLIB))
  )
)

SID_LIST_LISTENER_DLIB =
  (SID_LIST =
   (SID_DESC=
      (SID_NAME=DLIB93)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
      (PROGRAM=extproc)
      (ENVS="LD_LIBRARY_PATH=/home/app/9.3,EXTPROC_DLLS=ANY",APP_HOME=/home/app/9.3)

    )
   (SID_DESC=
      (SID_NAME=DLIB94)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
      (PROGRAM=extproc)
      (ENVS="LD_LIBRARY_PATH=/home/app/9.4,EXTPROC_DLLS=ANY,APP_HOME=/home/app/9.4")

    )
  )

2) configure tnsnames.ora

exaproc_93 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROCDLIB))
    )
    (CONNECT_DATA =
      (SID = DLIB93)
      (PRESENTATION = RO)
    )
  )

exaproc_94 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROCDLIB))
    )
    (CONNECT_DATA =
      (SID = DLIB94)
      (PRESENTATION = RO)
    )
  )

to test the validity, you could start your listener as

lsnrctl start listener_dlib

and check the connect string.

tnsping exaproc_93 

3) configure the LIBRARY ...AGENT.

CREATE <PUBLIC> DATABASE LINK  DBLIB_AGENT using 'extproc_93';

CREATE OR REPLACE  LIBRARY libname as '$APP_HOME/appexe.so'  agent 'DBLIB_AGENT';

observe the AGENT clause in library definition.

Whenever the library is called, oracle looks at the DBLINK in the AGENT clause. If it exists, then the tnsnames.ora file in the DBLINK is lookedup. The tnsnames.ora will in turn point to the SID information in listener.ora. The environment info for that listener corresponding to the SID is pickup dynamically from listener.

  AGENT --> DB LINK --> tnsnames.ora --> listener.ora --> ENVS for that SID.

So , if you need to point your application to different version of shared library without touching the application code, you can simply change the connect string of the DBLINK or change the ENVS info in listener.ora itself.

eg. DROP   <PUBLIC> DATABASE LINK  DBLIB_AGENT ; -- with 'exaproc_64'

    CREATE <PUBLIC> DATABASE LINK  DBLIB_AGENT using 'exaproc_94';

Speed Delete huge amount of rows using dbms_parallel_execute (performance)

The best method to delete  millions of rows  from a large table is to simply rebuild the table.
However, in some environments, that is not possible and you want to really delete the data as quickly as possible. Additional complication could be that you need to delete rows from a master-detail table. The solution to this is to have as many sessions as possible run the delete statements. Each of this sessions can focus on a chunk of data to delete so that they can work in parallel.

This process could be built manually or we could use a new 11g package dbms_parallel_execute.

 Usually, the table you want data to be deleted from, may be the master table with many Detail tables having Foreign Key Constraints (FK) . In this case, you will have to delete the Child table data and then the master table data all in parallel chunks.

below is a sample script where i start 50 concurrent sessions and delete about 40 million rows from the master table and its corresponding detail table ...

drop table po_parallel purge;

-- create table with key columns that needs to be deleted based on the delete logic.

create table po_parallel as (select /*+ parallel(a,16) full(a) hash(a,bc) */ a.PO_TXN_KEY from "APS"."PO_TXN" a,
APS.cal bc where bc.cal_key = a.batch_cal_key and bc.actv_flg = 'N'  ;

/* if you need to delete rows from master-detail tables based on master key column, you need to create a procedure. If it is delete from a single table, then a simple delete statement will be enough.
Note: One common  rookie mistake is to write a delete statement without the where IN clause to pick the pre-selected key-columns. */

create or replace
PROCEDURE parallel_DML_PO (p_start_id IN NUMBER, p_end_id IN NUMBER) AS
BEGIN

    -- Delete from the detail table before the master.
  Delete from APS.INVC_TXN  where PO_TXN_KEY in    ( select po_txn_key from PO_PARALLEL WHERE PO_TXN_KEY BETWEEN p_start_id AND p_end_id);

Delete from APS.PO_TXN_ATTR  where PO_TXN_KEY in ( select po_txn_key from PO_PARALLEL  WHERE PO_TXN_KEY BETWEEN p_start_id AND p_end_id);

Delete  from APS.PO_TXN a  where PO_TXN_KEY in   ( select po_txn_key from PO_PARALLEL  WHERE PO_TXN_KEY BETWEEN p_start_id AND p_end_id);

commit;

end  parallel_DML_PO;

/

-- Next you can run the procedure to execute in parallel.

DECLARE
  l_task     VARCHAR2(30) := 'test_task';
  l_sql_stmt VARCHAR2(32767);
  l_try      NUMBER;
  l_status   NUMBER;
BEGIN

   -- create a task
  DBMS_PARALLEL_EXECUTE.create_task (task_name => l_task);
   -- point to key column and set batch size
  DBMS_PARALLEL_EXECUTE.create_chunks_by_number_col

(task_name    => l_task,
 table_owner  => 'VIJAY',
 table_name   => 'PO_PARALLEL',
 table_column => 'PO_TXN_KEY',
 chunk_size   => 100000);

    -- specify the sql statement
  l_sql_stmt := 'BEGIN parallel_DML_PO(:start_id, :end_id); END;';

   -- run task in parallel
  DBMS_PARALLEL_EXECUTE.run_task(task_name      => l_task,
                                 sql_stmt       => l_sql_stmt,
                                 language_flag  => DBMS_SQL.NATIVE,
                                 parallel_level => 50);

  -- If there is error, RESUME it for at most 2 times.
  l_try := 0;
  l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task);
  WHILE(l_try < 2 and l_status != DBMS_PARALLEL_EXECUTE.FINISHED)
  Loop
    l_try := l_try + 1;
    DBMS_PARALLEL_EXECUTE.resume_task(l_task);
    l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task);
  END LOOP;

-- DBMS_PARALLEL_EXECUTE.drop_task(l_task);
END;
/

-- to monitor the progress see

SELECT chunk_id, status, start_id, end_id
FROM   user_parallel_execute_chunks
WHERE  task_name = 'test_task'
ORDER BY chunk_id;

Once the job is complete you can drop the task, which will drop the associated chunk information also.

BEGIN
  DBMS_PARALLEL_EXECUTE.drop_task('test_task');
END;
/

While this is a example specific to my database, A decade later, I had to do some complex inserts. I did speed it up using FORALL, however, since I needed billions of rows generated, I used this method to insert billions of rows in parallel. You can check that example here.

 

Clone Schema using Datapump API

There is no single command for cloning a schema/user. But you can use datapump expdp and impdp utility with remap_schema option to clone a schema. However, i ran into a situation where the developers did not have access to O.S . The limitation of Datapump is that it runs as a background process and not a client tool.  This means that the dumpfile is generated in the DB server where the data is being exported in.  If the developer does not have access to the O.S, then he cannot access the dumpfile generated..

 There is a workaround to this situation.  There is a feature of datapump utility where you can directly import data across a DB Link without exporting. Now you use this feature along with using the Datapump API instead of command prompt and we have a way to clone schema.

You can look at Oracle document to write better scripts. But, below is one i used. I have highlighted places where you will need to change value according to your environment.



set serveroutput on
DECLARE
  ind NUMBER;              -- Loop index
  spos NUMBER;             -- String starting position
  slen NUMBER;             -- String length for output
  h1 NUMBER;               -- Data Pump job handle
  percent_done NUMBER;     -- Percentage of job complete
  job_state VARCHAR2(30);  -- To keep track of job state
  le ku$_LogEntry;         -- For WIP and error messages
  js ku$_JobStatus;        -- The job status from get_status
  jd ku$_JobDesc;          -- The job description from get_status
  sts ku$_Status;          -- The status object returned by get_status
  --v_psw dba_users.password%type;
  v_psw varchar2(30) ;  -- password new user
  v_row varchar2(8) ;   -- mode rows=N si '1'
  cur_scn number ;      -- source
BEGIN
-- mode rows=N si '1'
-- v_row :=1;
--cur_scn:=6030373057 ;
--select password into v_psw from dba_users where username = '&user_c';
  h1 := Dbms_DataPump.Open(operation => 'IMPORT', job_mode => 'SCHEMA', job_name => 'impdp_vkumar3', version => 'COMPATIBLE', remote_link => 'DBLINKNAME_VK' );
    dbms_datapump.set_parallel(handle => h1, degree => 2);
    -- uncomment if you want the logfile written
    -- Dbms_DataPump.Add_File(handle => h1, filename => 'vkumar_imp1', directory => 'DATA_PUMP_DIR', filetype => 3);
    -- enter value for source schame
    dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''VIJAY_PROD'')'); 
-- cause bug Bug 5071931 DATAPUMP IMPORT WITH REMAP TABLESPACE, AND SCHEMA IS VERY SLOW , exclure le calcul des stats
  DBMS_DATAPUMP.METADATA_FILTER(handle=> h1, name => 'EXCLUDE_PATH_EXPR', value => '=''TABLE_STATISTICS''');
  DBMS_DATAPUMP.METADATA_FILTER(handle=> h1, name => 'EXCLUDE_PATH_EXPR', value => '=''INDEX_STATISTICS''');
  -- remap source to destination
  Dbms_DataPump.METADATA_REMAP( h1 , 'REMAP_SCHEMA', 'VIJAY_PROD' , 'VIJAY_DEV' );
  -- USE SKIP or REPLACE option if file exists.
  Dbms_DataPump.Set_Parameter(handle => h1, name => 'TABLE_EXISTS_ACTION', value => 'SKIP');
  --Dbms_DataPump.Set_Parameter(handle => h1, name => 'FLASHBACK_SCN', value => cur_scn);
-- Initial extent clause omit
  dbms_datapump.metadata_transform ( h1, 'STORAGE' , 0 , null ) ;
--  dbms_datapump.metadata_transform ( handle => h1, name => 'SEGMENT_ATTRIBUTES' , value => 'n' ) ;
-- test equivalent to ROWS=N
-- mode rows=N si '1'
  IF v_row = '1' THEN
        dbms_datapump.data_filter(handle=> h1, name=> 'INCLUDE_ROWS' , value=>0);
  END IF;
-- Start the job. An exception will be returned if something is not set up
-- properly.One possible exception that will be handled differently is the
-- success_with_info exception. success_with_info means the job started
-- successfully, but more information is available through get_status about
-- conditions around the start_job that the user might want to be aware of.
    begin
    dbms_datapump.start_job(h1);
    dbms_output.put_line('Data Pump job started successfully');
    exception
      when others then
        if sqlcode = dbms_datapump.success_with_info_num
        then
          dbms_output.put_line('Data Pump job started with info available:');
          dbms_datapump.get_status(h1,
                                   dbms_datapump.ku$_status_job_error,0,
                                   job_state,sts);
          if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)
          then
            le := sts.error;
            if le is not null
            then
              ind := le.FIRST;
              while ind is not null loop
                dbms_output.put_line(le(ind).LogText);
                ind := le.NEXT(ind);
              end loop;
            end if;
          end if;
        else
          raise;
        end if;
  end;
-- The export job should now be running. In the following loop, we will monitor
-- the job until it completes. In the meantime, progress information is
-- displayed.
 percent_done := 0;
  job_state := 'UNDEFINED';
  while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
    dbms_datapump.get_status(h1,
           dbms_datapump.ku$_status_job_error +
           dbms_datapump.ku$_status_job_status +
           dbms_datapump.ku$_status_wip,-1,job_state,sts);
    js := sts.job_status;
-- If the percentage done changed, display the new value.
     if js.percent_done != percent_done
    then
      dbms_output.put_line('*** Job percent done = ' ||
                           to_char(js.percent_done));
      percent_done := js.percent_done;
    end if;
-- Display any work-in-progress (WIP) or error messages that were received for
-- the job.
      if (bitand(sts.mask,dbms_datapump.ku$_status_wip) != 0)
    then
      le := sts.wip;
    else
      if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)
      then
        le := sts.error;
      else
        le := null;
      end if;
    end if;
    if le is not null
    then
      ind := le.FIRST;
      while ind is not null loop
        dbms_output.put_line(le(ind).LogText);
        ind := le.NEXT(ind);
      end loop;
    end if;
  end loop;
-- Indicate that the job finished and detach from it.
  dbms_output.put_line('Job has completed');
  dbms_output.put_line('Final job state = ' || job_state);
  dbms_datapump.detach(h1);
-- test if v_psw != 'EXIST' and != 'idem' , then set this psw , else password would be the original one from target user
 -- IF v_psw != 'EXIST' and v_psw != 'idem' THEN
   --     execute immediate 'alter user &user_c identified by '||v_psw||' ' ;
--  END IF;
-- Any exceptions that propagated to this point will be captured. The
-- details will be retrieved from get_status and displayed.
  exception
    when others then
      dbms_output.put_line('Exception in Data Pump job');
      dbms_datapump.get_status(h1,dbms_datapump.ku$_status_job_error,0,
                               job_state,sts);
      if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)
      then
        le := sts.error;
        if le is not null
        then
          ind := le.FIRST;
          while ind is not null loop
            spos := 1;
            slen := length(le(ind).LogText);
            if slen > 255
            then
              slen := 255;
            end if;
            while slen > 0 loop
              dbms_output.put_line(substr(le(ind).LogText,spos,slen));
              spos := spos + 255;
              slen := length(le(ind).LogText) + 1 - spos;
            end loop;
            ind := le.NEXT(ind);
          end loop;
        end if;
      end if;
END;
/
-- end
-- compile & stats
 exec dbms_output.put_line('** Compilation and Statistics gathering ...') ;
 EXEC DBMS_UTILITY.compile_schema(schema => 'VIJAY_DEV1');
 exec DBMS_STATS.GATHER_SCHEMA_STATS ( 'VIJAY_DEV',DBMS_STATS.AUTO_SAMPLE_SIZE,null,'FOR ALL INDEXED COLUMNS SIZE AUTO',2,'ALL',TRUE) ;

Parse xml documents directly from filesystem without loading into Oracle database

First, create a database directory object and grant it read,write privilages.
Create or replace directory xmlload as '/tmp/xmlload';
grant read,write on XMLLOAD to <username>;

-- to avoid some ora-600 errors in 11.2.0.2 version of DB.
alter session set events='31156 trace name context forever, level 0x400';


If a DBA is tasked to read a xml file, he will like to see the output as rows and columns after parsing base on xquery condition...

E.g

WITH vendxml_col AS
(select XMLTYPE(bfilename('XMLLOAD','template.xml'),NLS_CHARSET_ID('AL32UTF8'))vend_doc from dual)
-- Above WITH used to simulate your table/data read from file.
SELECT  a.name,a.operation,a.searchable
  FROM vendxml_col,
       XMLTABLE('for $i in /import_data/template/template_attribute
                                    where $i/@name eq "UPN"
                                    or $i/@searchable eq "n"
                                   return $i'
                PASSING vendxml_col.vend_doc
                COLUMNS
                name varchar2(300) PATH '@name',
operation varchar2(500) PATH '@operation',
searchable varchar2(1) PATH '@searchable') a;

However, if you  want a xml output  as a result of your parsing you can simply read xmltype instead of xmltable.

select xmlquery(
'for $i in /import_data/template/template_attribute
                                    where $i/@name eq "VIJAY"
                                    or $i/@operation_flag=fn:true()
                                   return $i'
PASSING d.xml_doc  returning content) the_result
from
(select xmltype (bfilename('XMLLOAD','template.xml'),NLS_CHARSET_ID('AL32UTF8')) xml_doc from dual) d;

Oracle database link with easy connect, RAC, bequeath connection.

The old way of creating a database link in oracle database is to use the syntax
create database link linkname connect to <username> identified by <password> using 'connect_string';
where connect string maps to a entry in tnsnames.ora.

If you did not want to depend on any files in Oracle local directries, then you had the workaround of  replacing the connect string with the complelete description as follows.
create database  link linkname connect to <username> identified by <password> using
'(DESCRIPTION =   
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.101)(PORT = 1521))   
(CONNECT_DATA =     
(SERVICE_NAME = VIJAY)))';

Now, you can use the same concept and connect using the easy connect syntax as follows.

 create  database link abc1 connect to <username> identified by <password> using '//10.0.10.101:1521/vijay';

If this is a RAC environment and you need to access one specific instance, then the syntax would be 

create  database link abc1 connect to <username> identified by <password> using '//10.0.10.101:1521/vijay/vijay1';


If for some reason, you need to create a database link to read data from the same host (loopback ) you could use a bequeath connection in the database as follows.


create   database link beq_link connect to <username> identified by <password>
USING
  '(description=(address=(protocol=beq)(program=/u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle))
    (CONNECT_DATA = (SERVICE = orclprd)))';

oracle 11g DB in a Virtualbox ( vbox)

For those who want to play with Oracle Database 11.2.x in a Linux environment, check this out.

 The only changes i did was to use NAT network adapter to be able to connect to internet from within the VM.

HV enqueue – contention during parallel inserts.

While using insert /*+ append */ to insert into a 11gr2 database, I found 80% of the waits being enq: HV - contention . 

 
     HV enqueue –  From the description of event which says Lock used to broker the high watermark during parallel inserts, you would assume that we have to tweak the insert statement .

Solution:
After few tests, the source of the problem was found to be the underlying datafile extending, The files were extending by only 4M. I resized the datafile to a size i expect the data to grow.
  alter database datafile '/filename'  resize xxG;
The wait event completely disappeared and  the insert was 3 times faster.