Readers,
I had my first request from a Reader (a few months ago) to do a blog post on an alternate blog. A while back I did a posting about how to utilize the BCP process to import/export datasets in a more efficient manner. A Reader asked me if I could show how its done when your using Oracle DB instead of MS SQL DB. So, this is that blog. We are going to look at using the BCP Process to import and export a dataset when its in an Oracle DB. You can read my original blog HERE.
With that said, the process is pretty much the same process as running it with MS SQL DB. The options will be the same and the steps should be pretty much the same as my original post on BCP.
To Start, you are going to need a data set to export. You will also need access to the Management Console and the associated user that goes with the setup/configuration. Once you have that, you can start.
In my demonstration, I am using version 12 patch 30 (because it’s the only Oracle machine available).
Go ahead and sign into the Sage X3 Management console just like before. If you have the correct account creds, then you will see the Data-Application configuration with stuff in it like sever info, app and runtime. If you don’t see anything, you have the wrong user account.
Next you want to click on the Folders tab from the top bar to load the folder (endpoint) data listings
Select the folder you want to export (in my case its SEED) and click the Export button from menu bar.
So far, this should look pretty familiar. The steps are pretty much the same (as I already said).
Now the important part. On the Export folder popup, you will see an option labeled: “Export data to Oracle native dump files This function uses Oracle Export (exp). On mono-tier architecture : Data Pump (expdp) is used only if ‘exp’ is not installed or fails.”. If you noticed, the name of the button changed and now displays based on the DB installed. It is also super long button name.
Check that box and whack that OK button to start.
Now, if you left the defaults, you may get a few popups after hitting ok. Like this one:
I said yes.
Or this one:
Which I also said OK to (because I want it to use it)
After all of that warning and saying YES and OK to stuff, it should kick off the export process
Hopefully it finishes fast (if not faster) than the SQL Server test. It should be at least the same amount of time.
After you run the export, you can get the log trace from the Console Traces (default location C:\Users\<install user profile>\AppData\Roaming\Sage\Console\traces)
|
26/07/2022 19:58:34 : INFO - --------------------------------------------------------------------- 26/07/2022 19:58:34 : INFO - Folder configuration 26/07/2022 19:58:34 : INFO - --------------------------------------------------------------------- 26/07/2022 19:58:34 : INFO - Starting configuration from Runtime X3V12ORART0 'x3erpv12oravm' 26/07/2022 19:58:34 : INFO - Starting creating the SVG directory 26/07/2022 19:58:35 : INFO - Delete directory 'D:\Sage\X3V12ORA\folders\SEED\SVG' 26/07/2022 19:58:35 : INFO - End of creation of the backup directories 26/07/2022 19:58:35 : INFO - Starting generating the folder table list 26/07/2022 19:58:35 : INFO - Launching the generation script of the folder table list - D:\Sage\X3V12ORA\folders\SEED 26/07/2022 19:58:36 : INFO - Recovering the folder table list 26/07/2022 19:58:36 : INFO - Launching the generation script of the folder view list - D:\Sage\X3V12ORA\folders\SEED 26/07/2022 19:58:37 : INFO - Recovering the view table list 26/07/2022 19:58:37 : INFO - 1968 tables found 26/07/2022 19:58:37 : INFO - End of generation of the table list (00:00:01.6589839) 26/07/2022 19:58:37 : INFO - Starting exporting the folder tables - SEED - ORACLE - Native dump mode 26/07/2022 19:58:37 : INFO - Starting exporting the folder SEED tables to D:\Sage\X3V12ORA\folders\SEED\SVG - Windows 26/07/2022 19:58:37 : INFO - Script 'D:\Sage\X3V12ORA\folders\SEED\SVG\_valfilbat.export.ps1' generated. 26/07/2022 19:58:37 : INFO - Executing script 'D:\Sage\X3V12ORA\folders\SEED\SVG\_valfilbat.export.ps1' on host x3erpv12oravm, 1679 table(s) - 289 view(s) 26/07/2022 19:58:58 : INFO - D:\Sage\X3V12ORA\folders\SEED\SVG\_valfilbat.export.ps1 execution done 26/07/2022 19:58:58 : INFO - End of extraction of the tables (00:00:21.1470235) 26/07/2022 19:58:58 : INFO - Starting extracting the sequences 26/07/2022 19:58:58 : INFO - Extracting the sequences - SEED 26/07/2022 19:58:58 : INFO - Starting extracting the sequences - Windows / Oracle 26/07/2022 19:59:07 : INFO - End of extraction of the sequences - Oracle 26/07/2022 19:59:07 : INFO - End of extraction of the tables 26/07/2022 19:59:07 : INFO - Starting generating the srf files 26/07/2022 19:59:07 : INFO - Generating the folder srf files - D:\Sage\X3V12ORA\folders\SEED\SVG 26/07/2022 20:00:01 : INFO - End of generation of the srf files (00:00:54.0636439) 26/07/2022 20:00:01 : INFO - Starting exporting the database 26/07/2022 20:00:01 : INFO - Exporting the database - SEED (ORACLE Native dump mode) 26/07/2022 20:00:01 : INFO - Starting exporting the database - Oracle 26/07/2022 20:00:02 : INFO - Use '%ORACLE_HOME%\bin\exp' Oracle utility to build the dump 'D:\Sage\X3V12ORA\folders\SEED\SVG\SEED.dmp' (Remote Oracle database 19.0) 26/07/2022 20:00:02 : INFO - Command: Call D:\Sage\X3V12ORA\runtime\bin\env.bat %ORACLE_HOME%\bin\exp system/<hidden> file=D:\Sage\X3V12ORA\folders\SEED\SVG\SEED.dmp STATISTICS=NONE DIRECT=Y BUFFER=32768 RECORDLENGTH=32768 OWNER=SEED 26/07/2022 20:00:02 : INFO - Launching tool 'exp' from x3erpv12oravm to export dump file D:\Sage\X3V12ORA\folders\SEED\SVG\SEED.dmp ... 26/07/2022 20:02:40 : INFO - exp executed 26/07/2022 20:02:40 : INFO - Dump done with exp : D:\Sage\X3V12ORA\folders\SEED\SVG\SEED.dmp 26/07/2022 20:02:40 : INFO - 26/07/2022 20:02:40 : INFO - Export: Release 19.0.0.0.0 - Production on Tue Jul 26 20:00:03 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.3.0.0.0 Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set About to export specified users ... . exporting pre-schema procedural objects and actions . exporting foreign function library names for user SEED . exporting PUBLIC type synonyms . exporting private type synonyms . exporting object type definitions for user SEED About to export SEED's objects ... . exporting database links . exporting sequence numbers . exporting cluster definitions . about to export SEED's tables via Direct Path ... . . exporting table ABANK 17 rows exported . . exporting table ABICOND 398 rows exported . . exporting table ABIDATMRT 16 rows exported . . exporting table ABIDATWRH 1 rows exported . . exporting table ABIDIM 178 rows exported . . exporting table ABIDIMFLD 2885 rows exported . . . (skipping to the end and not going to show 1400+ tables exporting) . . exporting table YTMP 5 rows exported . exporting synonyms . exporting views . exporting stored procedures . exporting operators . exporting referential integrity constraints . exporting triggers . exporting indextypes . exporting bitmap, functional and extensible indexes . exporting posttables actions . exporting materialized views . exporting snapshot logs . exporting job queues . exporting refresh groups and children . exporting dimensions . exporting post-schema procedural objects and actions . exporting statistics Export terminated successfully without warnings. 26/07/2022 20:02:40 : INFO - Dump file : D:\Sage\X3V12ORA\folders\SEED\SVG\SEED.dmp 26/07/2022 20:02:40 : INFO - End of database export - Oracle (00:02:39.0722377) 26/07/2022 20:02:41 : INFO - End of configuration (00:04:06.6379053) 26/07/2022 20:02:41 : INFO - --------------------------------------------------------------------- 26/07/2022 20:02:41 : INFO - End of configuration 26/07/2022 20:02:41 : INFO - --------------------------------------------------------------------- |
So, I shorten the actual log (because it was about 40 pages) just to show start and end time and no errors.
That’s it for the exporting…
Now on to the importing…
Folder Import process is the same process but you’re using the import instead of export. You check the same button on the import popup, and it will run through the same utility tools.
You want to go to the same Management Console \ Folders tab that we previously started at, but this time you want to click on the Import menu button
I am just going to import the SEED folder over the SEED folder (which you can do). This is typically referred to as a folder refresh.
A Very Important Note: When importing with this method, the exported folder must have used the same Bulk Copy for the import Bulk Copy to work. You cannot use the function on a folder that was exported with the original/tradition export.
On the Import a folder popup you want to check the “Import from .dmp / .pdmp files with ‘Oracle imp’ / ‘Oracle impdp’.” checkbox before running the import. So, check the box and hit the Ok button.
The import process will start as soon as you click OK.
When it’s finished you can click ok to the popup for completion. Sometimes you may see a WARNING message (like in my screenshot above. If you click on the TRACE tab within the Configuration, you can see what the WARNING was about. Most cases it will be elevated privileges as was with mine. That is ok, I don’t need 100% access to all of Oracle DB just our specific X3 schema. So, something like this is ok.
Anyways, the folder still updates, and I can still access it (which is good).
And there you have it. As you can tell its pretty much the same as running it with MS SQL. The buttons and stuff have different names, but the main process is the same.
There is one small, teeny, tiny caveat to this, on Oracle DB you might need to have the Oracle SQL Developer tools installed so that the SAFE Console can utilize them.
This is what it looks like:
Anyways…
