OraFAQ Forum: Programming Interfaces » C# Application to backup Schema to .dmp file

Home » Open Source » Programming Interfaces » C# Application to backup Schema to .dmp file (Oracle 12.2.0.1.0, Windows Server 2016)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

C# Application to backup Schema to .dmp file [message #676838]

Sun, 21 July 2019 19:57

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

Report message to a moderator

Re: C# Application to backup Schema to .dmp file [message #676839 is a reply to message #676838]

Sun, 21 July 2019 21:18

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: C# Application to backup Schema to .dmp file [message #676840 is a reply to message #676839]

Sun, 21 July 2019 22:51

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;
        }

Report message to a moderator

Re: C# Application to backup Schema to .dmp file [message #676841 is a reply to message #676840]

Mon, 22 July 2019 01:28

John Watson
Messages: 8962
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.

Report message to a moderator

Re: C# Application to backup Schema to .dmp file [message #676844 is a reply to message #676841]

Mon, 22 July 2019 19:42

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;
        }

Report message to a moderator

Re: C# Application to backup Schema to .dmp file [message #676845 is a reply to message #676844]

Mon, 22 July 2019 20:00

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.

Report message to a moderator

Re: C# Application to backup Schema to .dmp file [message #676846 is a reply to message #676845]

Tue, 23 July 2019 01:26

John Watson
Messages: 8962
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

Report message to a moderator

Re: C# Application to backup Schema to .dmp file [message #676855 is a reply to message #676838]

Tue, 23 July 2019 18:21

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

Report message to a moderator

Re: C# Application to backup Schema to .dmp file [message #676858 is a reply to message #676855]

Wed, 24 July 2019 00:40

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.

Report message to a moderator

Re: C# Application to backup Schema to .dmp file [message #676860 is a reply to message #676855]

Wed, 24 July 2019 01:38

John Watson
Messages: 8962
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

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.

Report message to a moderator

Re: C# Application to backup Schema to .dmp file [message #676863 is a reply to message #676860]

Wed, 24 July 2019 02:21

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.

Report message to a moderator

Re: C# Application to backup Schema to .dmp file [message #676877 is a reply to message #676863]

Wed, 24 July 2019 20:07

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.

Report message to a moderator

Re: C# Application to backup Schema to .dmp file [message #676881 is a reply to message #676877]

Thu, 25 July 2019 01:14

John Watson
Messages: 8962
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.

Report message to a moderator

Re: C# Application to backup Schema to .dmp file [message #676896 is a reply to message #676881]

Thu, 25 July 2019 19:41

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!

Report message to a moderator

Previous Topic:	Looking for more information about a bug
Next Topic:	Oracle 12.1.0.2.0 OleDB driver pads trailing spaces

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 21 09:51:14 CST 2024