0

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

Improve this question

1 Answer 1

Reset to default
1

  1. 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.

  2. 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.

Improve this answer
4
  • But I want to ask some tiny questions plz... 1-how to manage multiple client-side users calling this backup stored procedure on the server-side using the same db user concurrently? what will happen? Is there a need to detach each user or what 2- If I don't detach the job, does it remains in the memory like dangled or something and is there a need to create a different job name each time the procedure is called(e.g. time-stamped naming)?
    – Median Hilal
    Commented Jun 3, 2014 at 19:26
  • 3-what is the best way to get a consistent db backup? I also read the oracle documentation, I saw something about time-stamped backup... Do you think it's better? or the best way is to ensure that the database is offline while the backup is taking place? Thank you. I know this is too much questions. but actually these issues really threatens my application success.
    – Median Hilal
    Commented Jun 3, 2014 at 19:28
  • 3. if you want consistent db backup, you can use flashback_time or flashback_scn with expdp. However you need to ensure that you have sufficient undo for that.
    – Raj
    Commented Jun 4, 2014 at 20:00
  • 1
    1. jobs won't clash into each other, they will probably run concurrently and it might slow down a bit (or your san/FS might take a hit due to all the writes. I'd avoid running such jobs cincurrently (even with noparallel, each job creates 3 processes). 2. You should create unique names (timestamp/guid/sequence whatever pleases you)
    – Raj
    Commented Jun 4, 2014 at 20:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.