Home » Infrastructure » Windows » Export .dmp files to a LAN location? (Oracle 12.2.0.1.0, Windows Server 2016)
Export .dmp files to a LAN location? [message #676847] Tue, 23 July 2019 01:38 Go to next message
Dandaman
Messages: 15
Registered: July 2019
Junior Member
It is my understanding that the directory parameter of the dbms_datapump.add_file has to be a local path on the database server.
For shared infrastructure where multiple users use the same database server and want to access say a .dmp schema backup they would need to get this from a networked location they have access to and not a folder on the database server? I guess they could access a share on the Oracle database server... but really don't want any end users knowing about the server name of the Oracle server.

For specific instance the following
CREATE OR REPLACE DIRECTORY MIGDMP3 AS '\\myfileserver\dmpBackupFolder';
GRANT READ, WRITE ON DIRECTORY MIGDMP3 TO C##BBE_FRESH13;
GRANT export full database TO C##BBE_FRESH13;
DECLARE
h1 number;
s varchar2(1000);
errorvarchar varchar2(100):= 'ERROR';
tryGetStatus number := 0;
success_with_info EXCEPTION;
PRAGMA EXCEPTION_INIT(success_with_info, -31627);
begin
        h1 := dbms_datapump.open (operation => 'EXPORT', job_mode => 'SCHEMA', job_name => 'Mig2_BBE_13v5', version => 'COMPATIBLE');
        tryGetStatus := 1;
        dbms_datapump.set_parallel(handle => h1, degree => 1);
        dbms_datapump.add_file(handle => h1, filename => 'BBE_FRESH13v5_EXPDAT.LOG', directory => 'MIGDMP3', filetype => 3);
        dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 1);
        dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''C##BBE_FRESH13'')');
        dbms_datapump.add_file(handle => h1, filename => 'BBE_FRESH13v5_EXPDAT%U.DMP', directory => 'MIGDMP3', filesize => '100M', filetype => 1);
        dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1);
        dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC');
        dbms_datapump.set_parameter(handle => h1, name => 'ESTIMATE', value => 'BLOCKS');
        dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);
        dbms_datapump.detach(handle => h1);
        errorvarchar := 'NO_ERROR';
EXCEPTION
WHEN OTHERS THEN
BEGIN
IF((errorvarchar = 'ERROR')AND(tryGetStatus = 1)) THEN
DBMS_DATAPUMP.DETACH(h1);
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
RAISE;
END;

results in....


Directory MIGDMP3 created.


Grant succeeded.


Grant succeeded.


Error starting at line : 4 in command -
DECLARE
h1 number;
s varchar2(1000);
errorvarchar varchar2(100):= 'ERROR';
tryGetStatus number := 0;
success_with_info EXCEPTION;
PRAGMA EXCEPTION_INIT(success_with_info, -31627);
begin
        h1 := dbms_datapump.open (operation => 'EXPORT', job_mode => 'SCHEMA', job_name => 'Mig2_BBE_13v5', version => 'COMPATIBLE');
        tryGetStatus := 1;
        dbms_datapump.set_parallel(handle => h1, degree => 1);
        dbms_datapump.add_file(handle => h1, filename => 'BBE_FRESH13v5_EXPDAT.LOG', directory => 'MIGDMP3', filetype => 3);
        dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 1);
        dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''C##BBE_FRESH13'')');
        dbms_datapump.add_file(handle => h1, filename => 'BBE_FRESH13v5_EXPDAT%U.DMP', directory => 'MIGDMP3', filesize => '100M', filetype => 1);
        dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1);
        dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC');
        dbms_datapump.set_parameter(handle => h1, name => 'ESTIMATE', value => 'BLOCKS');
        dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);
        dbms_datapump.detach(handle => h1);
        errorvarchar := 'NO_ERROR';
EXCEPTION
WHEN OTHERS THEN
BEGIN
IF((errorvarchar = 'ERROR')AND(tryGetStatus = 1)) THEN
DBMS_DATAPUMP.DETACH(h1);
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
RAISE;
END;
Error report -
ORA-39002: invalid operation
ORA-06512: at line 32
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4932
ORA-06512: at "SYS.DBMS_DATAPUMP", line 5183
ORA-06512: at line 12
39002. 00000 -  "invalid operation"
*Cause:    The current API cannot be executed because of inconsistencies
           between the API and the current definition of the job.
           Subsequent messages supplied by DBMS_DATAPUMP.GET_STATUS
           will further describe the error.
*Action:   Modify the API call to be consistent with the current job or
           redefine the job in a manner that will support the specified API.


It took a while but i take
ORA-06512: at line 12 39002. 00000 - "invalid operation"
and
ORA-39002: invalid operation
as it has issues with this network location. Like access denied/no write permissions etc.

I see the service is running under "NT SERVICE\OracleServerXXX". And do see from post http://www.orafaq.com/forum/m/653950/?srch=network+directory#msg_653950 that giving this network access could open up security holes.

So what is the best approach for creating a .dmp file in a LAN location?
To answer my own question the best approach is:
1. Never to export directly to network location. (Security/export performance)?
2. Always export locally and make the user get the file from the shared folder from the Oracle Database server?
3. Export locally and automate synchronisation of that folder to clients shared network folder?
4. Export to UNC and do XXX to allow it to happen?

Re: Export .dmp files to a LAN location? [message #676848 is a reply to message #676847] Tue, 23 July 2019 01:54 Go to previous messageGo to next message
Dandaman
Messages: 15
Registered: July 2019
Junior Member
Something else to help answer the question is during an Import process the database is best located on the Oracle server for performance.
(I am only talking in sizes of 2GB to 10GB).

So really each user of a shared Oracle Database server will need their own space to backup databases to. Then when importing the .dmp file over another schema/username the user would just provide the filename details and possibly path for that previously exported file...

I think this is the best approach... Otherwise don't give end users any access to it and make them wait for the Database Administrator..... If I was that database administrator I wouldn't want to have to continuous tickets to backup schema A to restore over schema B all the time...
Re: Export .dmp files to a LAN location? [message #676849 is a reply to message #676848] Tue, 23 July 2019 06:42 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Dandaman wrote on Tue, 23 July 2019 01:54
Something else to help answer the question is during an Import process the database is best located on the Oracle server for performance.
(I am only talking in sizes of 2GB to 10GB).
Huh? The database is always - by definition - located "on the oracle server". So I do not understand your statement "during an Import process the database is best located on the Oracle server"


Quote:
So really each user of a shared Oracle Database server will need their own space to backup databases to. Then when importing the .dmp file over another schema/username the user would just provide the filename details and possibly path for that previously exported file...
Why is "each user" createing their own "backup"? (And by the way, an export is NOT a backup of the database. It can be considered as a logical backup of some/many/most objects in the database.)


Quote:
I think this is the best approach... Otherwise don't give end users any access to it and make them wait for the Database Administrator..... If I was that database administrator I wouldn't want to have to continuous tickets to backup schema A to restore over schema B all the time...
If there are "continuous tickets to backup schema A to restore over schema B all the time" then it sounds to me like you are doing business incorrectly. Perhaps if you explained in more detail exactly why "users" have a continuous need to do this in the first place.
Re: Export .dmp files to a LAN location? [message #676850 is a reply to message #676847] Tue, 23 July 2019 06:49 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I would think it unlikely that the Windows account under which the database instance is running would have any sort of permissions on a UNC path to a remote machine. So you'll have to do it the other way: give your users access to the DB server's file system. It does rather beg the question "why?". Backup and restore/recover (which Data Pump is certainly not) would usually be the DBA's responsibility, not the users'.

[Updated on: Tue, 23 July 2019 06:52]

Report message to a moderator

Re: Export .dmp files to a LAN location? [message #676852 is a reply to message #676849] Tue, 23 July 2019 09:01 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
EdStevens wrote on Tue, 23 July 2019 06:42
Dandaman wrote on Tue, 23 July 2019 01:54
Something else to help answer the question is during an Import process the database is best located on the Oracle server for performance.
(I am only talking in sizes of 2GB to 10GB).
Huh? The database is always - by definition - located "on the oracle server". So I do not understand your statement "during an Import process the database is best located on the Oracle server"

Reminds me of a old film clip I saw many, many (many) years ago. REporter was interviewing an aircraft engineer about a new plane he had just designed:
Reporter: Will this new airplane fly with only one engine?
Designer: Flies best with only one engine. That's all it has.
Re: Export .dmp files to a LAN location? [message #676856 is a reply to message #676847] Tue, 23 July 2019 18:50 Go to previous messageGo to next message
Dandaman
Messages: 15
Registered: July 2019
Junior Member
Hi guys,
Thanks for having a look at my brain dump.

My initial thought was to allow users to export to .dmp file to a network location so they could copy their data back to their premises from a semi shared cloud environment.
Then either within the cloud environment or on premises they could import the .dmp file into a schema to have that exact same state of data from their production instance.

We currently have a method of backing up the data into an SQLite file and then being able to use that SQLite file to create the database on either SQL Server or Oracle. SQL Server given credentials to create a database and on Oracle a schema that all the tables are created on and all the data is inserted into those tables from the SQLite file.

I am attempting to prototype a method of using Oracle backup or export functionality to achieve a similar user experience but just for Oracle databases. So users could export their schema's data to .dmp file and then restore it over the top of another schema, for testing purposes or migration. (Our current approach is only good for databases under 2GB once over that size the process just takes to long.)

The turn around to get the DBA's to perform the database 'refresh' of the test data from the production is quite long, depending on their availability. With this functionality the users can do it when they need without having to wait on a DBA. They would only be given the schema's username and password so they can access the schema and do the import process to make that schema's data equal to the others.

I do confuse I am more experienced and familiar with SQL Server and SQLite which might give insight to my point of view in how I see and manage database instances.

Backup and disaster recovery is all handled separately by the DBA team so not trying to replace that.

So in the end I guess my question is.... "Is export and import of schema data on OK approach for 1. copying data over the top of another schema to get the same state of the tables, indexes and data?".

Which has gotten away from the topic though of exporting .dmp files to a network location instead of the Oracle servers local disk but is related to my purpose of the post. Network location not as necessary though if each client has their own storage space on the Oracle server and they can only access that space and nothing else on it.

Oracle database server designed to be used by multiple different companies/clients at the same time? (I wear many many hats and not a certified DBA but learnt a lot from the applications development and managemnet)
Re: Export .dmp files to a LAN location? [message #676866 is a reply to message #676856] Wed, 24 July 2019 06:42 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Dandaman wrote on Tue, 23 July 2019 18:50


We currently have a method of backing up the data into an SQLite file and then being able to use that SQLite file to create the database on either SQL Server or Oracle. SQL Server given credentials to create a database and on Oracle a schema that all the tables are created on and all the data is inserted into those tables from the SQLite file.

If you already have an acceptable method that is "universal" (works for both MSSQL and Oracle) then why are you seeking to replace that with an Oracle-only method? What problem are you trying to solve?
Re: Export .dmp files to a LAN location? [message #676876 is a reply to message #676866] Wed, 24 July 2019 19:23 Go to previous messageGo to next message
Dandaman
Messages: 15
Registered: July 2019
Junior Member
The answer to that question is performance.

The current process takes a very long time for databases greater than 2GB. Also the backup process can cause time out expired errors (SQL Server timeout error... forget the Oracle equivalent).
I believe its from database read locking as the full table scan is performed for each table all within a long running transaction.

The problem to solve is backup current state of a database to restore/import over the top of another schema for testing purposes. Within the same environment in another offsite environment. Our cloud vs their own on Premises.

This is to do for production data, UAT data, non-prod test data or even just a snapshot of the data for us to diagnose and identify defects with the data or our code with that data locally within our premises.
Re: Export .dmp files to a LAN location? [message #676878 is a reply to message #676876] Wed, 24 July 2019 20:11 Go to previous messageGo to next message
Dandaman
Messages: 15
Registered: July 2019
Junior Member
But in the end I would like to know what specifically is needed to be able to use the Datapump export function to a UNC location that is not on the Oracle server.
I.e. within our own premises Oracle Server backup to our file server a database.
Because this then removes the need for anyone else needing access to the Oracle database server.

Should I just go ahead and give the service running the oracle server access to a specific folder on the other server?
There has to be a secure method of doing such a thing.
What if a Database was very large and the database server just doesn't have enough space to create a .dmp file on that database servers drives... would have to use a UNC on another computer for that?
Re: Export .dmp files to a LAN location? [message #676882 is a reply to message #676876] Thu, 25 July 2019 01:16 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
I believe its from database read locking as the full table scan is performed for each table all within a long running transaction.
No. Oracle doesn't lock while reading data.
Re: Export .dmp files to a LAN location? [message #676884 is a reply to message #676882] Thu, 25 July 2019 06:41 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
John Watson wrote on Thu, 25 July 2019 01:16
Quote:
I believe its from database read locking as the full table scan is performed for each table all within a long running transaction.
No. Oracle doesn't lock while reading data.
But I'll bet his current MS process does.
Re: Export .dmp files to a LAN location? [message #676885 is a reply to message #676878] Thu, 25 July 2019 06:47 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Dandaman wrote on Wed, 24 July 2019 20:11
But in the end I would like to know what specifically is needed to be able to use the Datapump export function to a UNC location that is not on the Oracle server.
I.e. within our own premises Oracle Server backup to our file server a database.
Because this then removes the need for anyone else needing access to the Oracle database server.

Should I just go ahead and give the service running the oracle server access to a specific folder on the other server?
There has to be a secure method of doing such a thing.
What if a Database was very large and the database server just doesn't have enough space to create a .dmp file on that database servers drives... would have to use a UNC on another computer for that?
expdp and impdp run within the database, so they can only write/read files to/from locations that are directly accessible to the server on which the database is located. That does NOT mean it has to be physically local disk.

That said, have you looked into importing directly from the source database, using a network link?
See the documentation.
Re: Export .dmp files to a LAN location? [message #676895 is a reply to message #676885] Thu, 25 July 2019 19:26 Go to previous messageGo to next message
Dandaman
Messages: 15
Registered: July 2019
Junior Member
Yeah the NETWORK_LINK created via the CREATE DATABASE LINK or CREATE PUBLIC DATABASE LINK.
My understanding is that is for direct database to database connection. That could work but still want to go down the physical export and import process. So the .dmp can be restore on another Oracle server in another premises or within the same one.

[Updated on: Thu, 25 July 2019 19:26]

Report message to a moderator

Re: Export .dmp files to a LAN location? [message #676908 is a reply to message #676895] Fri, 26 July 2019 07:25 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Dandaman wrote on Thu, 25 July 2019 19:26
Yeah the NETWORK_LINK created via the CREATE DATABASE LINK or CREATE PUBLIC DATABASE LINK.
My understanding is that is for direct database to database connection. That could work but still want to go down the physical export and import process. So the .dmp can be restore on another Oracle server in another premises or within the same one.
If I read that correctly, you want to "go down the physical export and import process" because you believe it is required, it is the only way, to "restore on another Oracle server in another premises". I my understanding of your reasoning is correct, then your assumption is incorrect. A database link can work across any two databases, as long as there is network connectivity. Those two databases can be anywhere.

You have two options as has already been discussed ad nauseum:
1) Create a disk storage area that is mounted on, available to, both the source database and target database. As for keeping the .dmp files of different users/schemas/developers separate, you could either:
1a) actually create different directories on the source server, with matching directory objects within the database.
1b) use a single directory, but establish naming conventions for the different users' dmp files. That would be simpler, and would be my preference if using shared disk. I see no problem with different users seeing others' dmp files, as long as they can differentiate whose is whose.

2) Direct import over database link.
Previous Topic: REST service does not respond
Next Topic: SQL Load Multiple files to Single table
Goto Forum:
  


Current Time: Thu Mar 28 04:49:38 CDT 2024