Home » Open Source » Programming Interfaces » C# Application to backup Schema to .dmp file (Oracle 12.2.0.1.0, Windows Server 2016)
C# Application to backup Schema to .dmp file [message #676838] Sun, 21 July 2019 19:57 Go to next message
Dandaman
Messages: 15
Registered: July 2019
Junior Member
Hi I am trying to figure out the best approach that a c# application on a end users desktop could backup an Oracle database schema.

End user does not have Oracle or any Oracle tools installed.
Have been trying to run SQL dbms_datapump to create the dmp and log file in a folder accessible to the Oracle database server.

I generate my SQL command in c# console application and run it via
command.ExecuteNonQuery();

But I keep getting the error
"ORA-00922: missing or invalid option".

When I copy and paste the SQL that I output to the console and run within SQL Developer on the Oracle Database server the script runs without error.

So the question is what don't I understand?

Is it the method in which the SQL is sent to the Oracle server that causes the script to fail?

Regards Dan.
(At the end of the line of researching and seeking guidance.)
Re: C# Application to backup Schema to .dmp file [message #676839 is a reply to message #676838] Sun, 21 July 2019 21:18 Go to previous messageGo to next message
BlackSwan
Messages: 26605
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

We can't say what might be wrong, since you don't show us what you actually do.
Re: C# Application to backup Schema to .dmp file [message #676840 is a reply to message #676839] Sun, 21 July 2019 22:51 Go to previous messageGo to next message
Dandaman
Messages: 15
Registered: July 2019
Junior Member
Ah OK sorry I was just trying to get confirmation on the concept being valid before posting the code and PL/SQL script.

So here it is one instance of the generated PL/SQL:
set scan off
set serveroutput on
set escape off
whenever sqlerror exit
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_Fresh66', version => 'COMPATIBLE');
	tryGetStatus := 1;
	dbms_datapump.set_parallel(handle => h1, degree => 1);
	dbms_datapump.add_file(handle => h1, filename => 'BBE_FRESH66_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_FRESH6'')');
	dbms_datapump.add_file(handle => h1, filename => 'BBE_FRESH266_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;
/

That works fine when run via SQL Developer.

But when the same/similar SQL script is sent via an Oracle database connection i get "ORA-00922: missing or invalid option".

The C# code for the console app is like so:
private static bool DataPump(string OracleAdminConnectionString, string OracleConnectionString, string datafileName, string userName)
        {
            bool stagesComplete = false;
            string JobName = "Mig2_" + datafileName;

            string upperDatafile = datafileName.ToUpper(); // DMP file must be in UPPER case.
            
            if (JobName.Length>30)  // Job Name must be not exceed 30 characters.
                JobName = JobName.Substring(0, 30);

            string sql = "CREATE OR REPLACE DIRECTORY MIGDMP3 AS 'C:\\MigrationDump'";
            Console.WriteLine(sql);
            stagesComplete = executeOneOracleSQL(OracleAdminConnectionString, sql);
            if (!stagesComplete) return false;

            sql = string.Format("GRANT READ, WRITE ON DIRECTORY MIGDMP3 TO {0}", userName);
            Console.WriteLine(sql);
            stagesComplete = executeOneOracleSQL(OracleAdminConnectionString, sql);
            if (!stagesComplete) return false;

            sql = string.Format("GRANT export full database TO {0}", userName);
            Console.WriteLine(sql);
            stagesComplete = executeOneOracleSQL(OracleAdminConnectionString, sql);
            if (!stagesComplete) return false;

            sql = 
                "set scan off\n" +
                "set serveroutput on\n" +
                "set escape off\n" +
                "whenever sqlerror exit\n" +
                "DECLARE\n" +
                    "h1 number;\n" +
                    "s varchar2(1000);\n" +
                    "errorvarchar varchar2(100):= 'ERROR';\n" +
                    "tryGetStatus number := 0;\n" +
                    "success_with_info EXCEPTION;\n" +
                    "PRAGMA EXCEPTION_INIT(success_with_info, -31627);\n" +
                "begin\n" +
                    "\th1 := dbms_datapump.open (operation => 'EXPORT', job_mode => 'SCHEMA', job_name => '" + JobName + "', version => 'COMPATIBLE');\n" +
                    "\ttryGetStatus := 1;\n" +
                    "\tdbms_datapump.set_parallel(handle => h1, degree => 1);\n" +
                    "\tdbms_datapump.add_file(handle => h1, filename => '" + upperDatafile + "_EXPDAT.LOG', directory => 'MIGDMP3', filetype => 3);\n" +
                    "\tdbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 1);\n" +
                    "\tdbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''" + userName + "'')');\n" +
                    "\tdbms_datapump.add_file(handle => h1, filename => '" + upperDatafile + "_EXPDAT%U.DMP', directory => 'MIGDMP3', filesize => '100M', filetype => 1);\n" +
                    "\tdbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1);\n" +
                    "\tdbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC');\n" +
                    "\tdbms_datapump.set_parameter(handle => h1, name => 'ESTIMATE', value => 'BLOCKS');\n" +
                    "\tdbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);\n" +
                    "\tdbms_datapump.detach(handle => h1);\n" +
                    "\terrorvarchar := 'NO_ERROR';\n" +
                "EXCEPTION\n" +
                    "WHEN OTHERS THEN\n" +
                    "BEGIN\n" +
                        "IF((errorvarchar = 'ERROR')AND(tryGetStatus = 1)) THEN\n" +
                        "DBMS_DATAPUMP.DETACH(h1);\n" +
                        "END IF;\n" +
                    "EXCEPTION\n" +
                    "WHEN OTHERS THEN\n" +
                        "NULL;\n" +
                    "END;\n" +
                 "RAISE;\n" +
            "END;\n" +
            "/\n\n";
            Console.WriteLine(sql);

            stagesComplete = executeOneOracleSQL(OracleConnectionString, sql);
            if (!stagesComplete) return false;

            return true;
        }

private static bool executeOneOracleSQL(string connectionString, string sql)
        {
            bool executionComplete = false;
            // This will use the connection defined to connect to Oracle and execute the SQL statement past to it.
            using (OracleConnection conn = new OracleConnection(connectionString))
            {
                try
                {
                    conn.Open();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    return executionComplete;
                }
                
                using (OracleTransaction transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted))
                {
                    using (OracleCommand command = new OracleCommand())
                    {
                        command.Connection = conn;
                        command.CommandType = CommandType.Text;
                        command.CommandTimeout = 120;
                        command.Transaction = transaction;

                        command.CommandText = sql;
                        //command.ArrayBindCount = dataRows;
                        command.Prepare();
                        try
                        {
                            command.ExecuteNonQuery();
                            transaction.Commit();
                            executionComplete = true;
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.Message);
                            transaction.Rollback();
                        }
                        command.Parameters.Clear();
                    }
                }
                conn.Close();
                conn.Dispose();
            }
            return executionComplete;
        }
Re: C# Application to backup Schema to .dmp file [message #676841 is a reply to message #676840] Mon, 22 July 2019 01:28 Go to previous messageGo to next message
John Watson
Messages: 8022
Registered: January 2010
Location: Global Village
Senior Member
THese commands
set scan off
set serveroutput on
set escape off
whenever sqlerror exit
are not SQL or PL/SQL, they are SQL*Plus commands. I may be wrong, but I would be surprised if your executeOneOracleSQL function can understand them.
Re: C# Application to backup Schema to .dmp file [message #676844 is a reply to message #676841] Mon, 22 July 2019 19:42 Go to previous messageGo to next message
Dandaman
Messages: 15
Registered: July 2019
Junior Member
Oh my that is all that was blocking it. Thank you so much!

I will research more those commands and what they do and if the Database server administrators will allow or need them to complete the export.

Working solution to backup schema to .dmp file.
private static bool DataPump(string OracleAdminConnectionString, string OracleConnectionString, string datafileName, string userName)
        {
            bool stagesComplete = false;
            string JobName = "Mig2_" + datafileName;

            string upperDatafile = datafileName.ToUpper(); // DMP file must be in UPPER case.
            
            if (JobName.Length>30)  // Job Name must be not exceed 30 characters.
                JobName = JobName.Substring(0, 30);

            string sql = "CREATE OR REPLACE DIRECTORY MIGDMP3 AS 'C:\\MigrationDump'";
            Console.WriteLine(sql);
            stagesComplete = executeOneOracleSQL(OracleAdminConnectionString, sql);
            if (!stagesComplete) return false;

            sql = string.Format("GRANT READ, WRITE ON DIRECTORY MIGDMP3 TO {0}", userName);
            Console.WriteLine(sql);
            stagesComplete = executeOneOracleSQL(OracleAdminConnectionString, sql);
            if (!stagesComplete) return false;

            sql = string.Format("GRANT export full database TO {0}", userName);
            Console.WriteLine(sql);
            stagesComplete = executeOneOracleSQL(OracleAdminConnectionString, sql);
            if (!stagesComplete) return false;

            sql = 
                "DECLARE\n" +
                    "h1 number;\n" +
                    "s varchar2(1000);\n" +
                    "errorvarchar varchar2(100):= 'ERROR';\n" +
                    "tryGetStatus number := 0;\n" +
                    "success_with_info EXCEPTION;\n" +
                    "PRAGMA EXCEPTION_INIT(success_with_info, -31627);\n" +
                "begin\n" +
                    "\th1 := dbms_datapump.open (operation => 'EXPORT', job_mode => 'SCHEMA', job_name => '" + JobName + "', version => 'COMPATIBLE');\n" +
                    "\ttryGetStatus := 1;\n" +
                    "\tdbms_datapump.set_parallel(handle => h1, degree => 1);\n" +
                    "\tdbms_datapump.add_file(handle => h1, filename => '" + upperDatafile + "_EXPDAT.LOG', directory => 'MIGDMP3', filetype => 3);\n" +
                    "\tdbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 1);\n" +
                    "\tdbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''" + userName + "'')');\n" +
                    "\tdbms_datapump.add_file(handle => h1, filename => '" + upperDatafile + "_EXPDAT%U.DMP', directory => 'MIGDMP3', filesize => '100M', filetype => 1);\n" +
                    "\tdbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1);\n" +
                    "\tdbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC');\n" +
                    "\tdbms_datapump.set_parameter(handle => h1, name => 'ESTIMATE', value => 'BLOCKS');\n" +
                    "\tdbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);\n" +
                    "\tdbms_datapump.detach(handle => h1);\n" +
                    "\terrorvarchar := 'NO_ERROR';\n" +
                "EXCEPTION\n" +
                    "WHEN OTHERS THEN\n" +
                    "BEGIN\n" +
                        "IF((errorvarchar = 'ERROR')AND(tryGetStatus = 1)) THEN\n" +
                        "DBMS_DATAPUMP.DETACH(h1);\n" +
                        "END IF;\n" +
                    "EXCEPTION\n" +
                    "WHEN OTHERS THEN\n" +
                        "NULL;\n" +
                    "END;\n" +
                 "RAISE;\n" +
            "END;\n" +
            "\n\n";
            Console.WriteLine(sql);

            stagesComplete = executeOneOracleSQL(OracleConnectionString, sql);
            if (!stagesComplete) return false;

            return true;
        }
Re: C# Application to backup Schema to .dmp file [message #676845 is a reply to message #676844] Mon, 22 July 2019 20:00 Go to previous messageGo to next message
Dandaman
Messages: 15
Registered: July 2019
Junior Member
So is this approach OK for backing up a snapshot of the current state of a 'database'?
Typically with our product its just made up of all the tables and indexes. No procedures or anything else.
The aim for me is to be able to backup the schema so it can be restored over the top of another existing schema.

I have read about RMAN and exp/impdp but not sure how they can be used well for a c# application as they need to be invoked on the Oracle database server via the SQL Plus tool?
End users of the c# application will not have any clue on how to use Oracle.
Re: C# Application to backup Schema to .dmp file [message #676846 is a reply to message #676845] Tue, 23 July 2019 01:26 Go to previous messageGo to next message
John Watson
Messages: 8022
Registered: January 2010
Location: Global Village
Senior Member
An export is not a backup, and certainly not a snapshot. ORacle supports user managed backups and server managed backups, described here
https://docs.oracle.com/en/database/oracle/oracle-database/19/bradv/index.html

Re: C# Application to backup Schema to .dmp file [message #676855 is a reply to message #676838] Tue, 23 July 2019 18:21 Go to previous messageGo to next message
Dandaman
Messages: 15
Registered: July 2019
Junior Member
Hi John.
Yeah don't need a full backup as the functionality I am trying to prototype is not for disaster recovery.
Our relational tables are fairly simply in that what defines as specific database.
We provide our user with a production and test schema/database.
Want to provide functionality to essentially backup the state of the production database and then import the schema's data over the top of the test schema.

Is that a bad way of achieving that task?
Don't want to be that guy that knows enough to be dangerous Razz
Re: C# Application to backup Schema to .dmp file [message #676858 is a reply to message #676855] Wed, 24 July 2019 00:40 Go to previous messageGo to next message
Dandaman
Messages: 15
Registered: July 2019
Junior Member
This is for any other developer out there trying to figure out what this output from Oracle means:

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 means that the oracle server being run does not have permission to write to the folder.
If you have a local folder which is ready only for example or where your windows account does not have permission to it, or a network folder that you don't have permission to either.
Re: C# Application to backup Schema to .dmp file [message #676860 is a reply to message #676855] Wed, 24 July 2019 01:38 Go to previous messageGo to next message
John Watson
Messages: 8022
Registered: January 2010
Location: Global Village
Senior Member
Dandaman wrote on Wed, 24 July 2019 00:21
Hi John.
Yeah don't need a full backup as the functionality I am trying to prototype is not for disaster recovery.
Our relational tables are fairly simply in that what defines as specific database.
We provide our user with a production and test schema/database.
Want to provide functionality to essentially backup the state of the production database and then import the schema's data over the top of the test schema.

Is that a bad way of achieving that task?
Don't want to be that guy that knows enough to be dangerous Razz
I see: you do not want a backup, you want a facility to copy tables from one database to another. Sure, use Data Pump. And, to avoid the problem of have to stage files on disc, use network mode:
https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/datapump-import-utility.html#GUID-0871E56B-07EB-43B3-91DA-D1F457C F6182

There is no need to write any C# code, your users can run the impdp utility locally and do it all with that.
Re: C# Application to backup Schema to .dmp file [message #676863 is a reply to message #676860] Wed, 24 July 2019 02:21 Go to previous messageGo to next message
Dandaman
Messages: 15
Registered: July 2019
Junior Member
Thanks very much for the link! I will read up on it all tomorrow.
The end users are not technical so it would be us doing it.
Plus they only get our shared application with no direct access to the database server and what not. Pretty locked down.
Re: C# Application to backup Schema to .dmp file [message #676877 is a reply to message #676863] Wed, 24 July 2019 20:07 Go to previous messageGo to next message
Dandaman
Messages: 15
Registered: July 2019
Junior Member
I see that the CREATE DATABASE LINK or CREATE PUBLIC DATABASE LINK is for linking the two databases together. Then that link can be used to import one or more tables from that database into another one.

That could be an alternative approach instead of creating a file to be managed manually by us or the end user.

But since this is such a critical task I would like to see the backup process being invoked after hours while no users are using the database. Then for the restore/import process it should be able to be run multiple times in depend of the source production database. I.e. user does stuff in the test database and stuffs something up so they want to restore it again and do the tests again.
Re: C# Application to backup Schema to .dmp file [message #676881 is a reply to message #676877] Thu, 25 July 2019 01:14 Go to previous messageGo to next message
John Watson
Messages: 8022
Registered: January 2010
Location: Global Village
Senior Member
Quote:
user does stuff in the test database and stuffs something up so they want to restore it again and do the tests again.
This is a perfect usage case for the Flashback Database facility.
Re: C# Application to backup Schema to .dmp file [message #676896 is a reply to message #676881] Thu, 25 July 2019 19:41 Go to previous message
Dandaman
Messages: 15
Registered: July 2019
Junior Member
Flashback looks interesting. Will read up on that one to see if its a valid option to put within our test databases..... though not sure the product manager will want it encase someone uses it in a production database!
Previous Topic: Looking for more information about a bug
Goto Forum:
  


Current Time: Mon Oct 14 17:56:47 CDT 2019