Specific issue on data pump API in oracle

oracleplsql

I have a client/server architecture. Using an Oracle dbms on the database server side.
I need to perform a user-triggered (from client side) backup of the database, where the best way to perform that is using a stored procedure on the server side which the client may call, as the client has no oracle tools to perform the backup. I've searched thorough inside available solutions and have found that using a stored procedure is the best way.
Well, then I found that using oracle data pump API is the best way to use inside a PL/SQl stored procedure.

My specific questions about the API are…

I would like to ask about two issues …

—- The first —–

the detach function to detach the handler, is it necessary to be used at the end of the procedure? and what if I don't use it? I read the Oracle documentation but I didn't get their point, they say it doesn't terminate the job but indicates that the user is not interested in it, an when I use detach at the end of my procedure the exported .dmp file disappears.

—- The second —–

to perform a user (client side) triggered back up as the modification are only to the data, I used TABLE parameter for the export operation. But the version parameter… what should it be? I also read the documentation but couldn't determine what I need (LATEST or COMPATIBLE) ?

Thanks

Best Answer

DETACH is not required, once you submit the job using dp api, it will run in the background. Once job starts, the dumpfile will remain unless job is killed. Once you run DBMS_DATAPUMP.START_JOB (handle => nhandle); the job will start executing. However, once you start and before DP begins (there is short delay when it prepares), if you detach during this time, the job is effectively cancelled and dumpfile will disappear.
Unless you need the dumpfile be backword compatible with certain prior versions of Oracle, you should leave this parameter alone, the default value is fine.

Related Solutions

Logging/profiling an unpredictably slow stored procedure

You can set tracing at the ADMIN level. Your timing is suspiciously similar to an issue I dealt with in April/May this year. The admin level trace file showed a 5 second gap when reading from a socket. (10:44:05 -> 10:44:11) NTTRD is the read from tcp socket call. I'd definitely work this issue with Oracle support, but if you see gaps like the one below then the pause is somewhere in your network stack or hardware (i.e outside Oracle). In our case Oracle support pointed us towards network switch logs etc, but the culprit turned out to be an issue in the Microsoft Windows TCP stack down to the new Receive Window Auto-Tuning feature (see http://support.microsoft.com/kb/983528 for example if on Server 2008). Obviously it would be pure fluke if your issue was the same as ours, but if you can see the gaps in the admin level trace file it's a fair bet that it will turn out to be a network issue.

[08-APR-2011 10:44:05:938] nsprecv: reading from transport...
[08-APR-2011 10:44:05:938] nttrd: entry
[08-APR-2011 10:44:11:095] nttrd: socket 2156 had bytes read=60
[08-APR-2011 10:44:11:095] nttrd: exit
[08-APR-2011 10:44:11:095] nsprecv: 60 bytes from transport
[08-APR-2011 10:44:11:095] nsprecv: tlen=60, plen=60, type=6

Create a “barrier” (prevent parallel execution) in Oracle PL/SQL

Oracle has several locking mechanisms that you can use to serialize operations:

You could create a single-row table that is locked at the beginning of the procedure. The lock will be locked until the transaction ends (commit/rollback).
You could request a lock with DBMS_LOCK.request. This lock can be maintained for the duration of the session or until you call DBMS_LOCK.release. This lock can be maintained across commits.

Example of serialization using DBMS_LOCK:

SQL> CREATE OR REPLACE PROCEDURE serial IS
  2     l_lock_handle  VARCHAR2(128 BYTE);
  3     l_lock_request INTEGER;
  4  BEGIN
  5     dbms_lock.allocate_unique(lockname=> 'MY_SERIAL_PROC', lockhandle => l_lock_handle);
  6     LOOP
  7        l_lock_request := dbms_lock.request(lockhandle        => l_lock_handle,
  8                                            timeout           => 5,
  9                                            release_on_commit => FALSE);
 10        CASE l_lock_request
 11           WHEN 0 THEN
 12              EXIT; -- success
 13           WHEN 1 THEN
 14              dbms_output.put_line('lock already reserved, wait...');
 15              dbms_lock.sleep(5); -- sleep 5 seconds before retrying
 16           ELSE
 17              raise_application_error(-20001, 'Lock error: ' || l_lock_request);
 18        END CASE; --
 19     END LOOP;
 20     ----------------------------------------
 21     -- serialized block of code           --
 22     -- (lock will be kept accross commit) --
 23     ----------------------------------------
 24     dbms_lock.sleep(30);
 25     ----------------------------------------
 26     -- End of serialized code             --
 27     ----------------------------------------
 28     l_lock_request := dbms_lock.release(lockhandle => l_lock_handle);
 29  END;
 30  /

Procedure created

Only one session can run this procedure at the same time:

session1> exec serial;             
                                   session2> exec serial;
                                   lock already reserved, wait...
                                   lock already reserved, wait...
                                   lock already reserved, wait...

PL/SQL procedure successfully completed                            

                                   PL/SQL procedure successfully completed.

Best Answer

Related Solutions

Logging/profiling an unpredictably slow stored procedure

Create a “barrier” (prevent parallel execution) in Oracle PL/SQL

Related Question