Dialog with Oracle

Problem/Question/Abstract:

Oracle Enterprise manager gives the database administrator enough instruments to accomplish a database backup. But what if you want to provide your Delphi database application with its own facility to perform this task? This option could be very useful especially for relatively small Oracle databases, where the DBA-responsible person usually has a lot of other functions in the organization. To extend the Delphi application capacity to have a dialog with Oracle is not only interesting, but also easy. Both Delphi and Oracle give us quite a few suitable approaches.

Answer:

BACKING UP AN ORACLE DATABASE

Most probably you will plan the backing up as an additional supporting activity in your Delphi database server application. In this case the most useful approach could be to use the Oracle's Export utility. It allows you to carry out the task even if the database is open and available for use. The result is a file which consists of a view of the database's objects. You could use the Import utility afterwards if you need to recover the data.To operate Export/Import even from an outside application is not only an easy task, but also allows backing up a single user or even a single table. There is one important thing you should consider when using the Export. The information in your backup file will not be logically consistent if someone makes changes to the database while the Export takes place. You could prevent this in two ways: scheduling the Export for a time when no one else can access the data: nights, weekends (if applicable in your case) or killing all the users' sessions before starting the Export and switching to restricted mode. Performing a full offline backup is another option which could also be performed by your Delphi application. If the process finishes successfully, the result is the most reliable you could achieve. As distinct from using the Export, where the system audit options are not exported, now you are copying the datafiles, the online redo log files and the current control file, so you will have on side all the files that constitute your database. Your result will be consistent only if the entire set of files corresponds to one exact point in time. To achieve this, you should initiate this backup only after the database is shut down in normal priority, not after an instance crash or shutdown abort!

Of course, a lot of additional information about the backup could be found in the Oracle technical documentation. But after this very short outline we will turn to the Delphi's implementation of the database backup.

THE DIALOG PRINCIPLES

For my project I have developed an unit which enables scheduling and accomplishing of the main Oracle backup approaches: export, export in Resricted session mode and Full Offline Backup. Moreover, Delphi can receive information back from the Oracle on the running of the backup process and report this to the user. Both Delphi and Oracle talk face to face to exchange information, so the word 'Dialog' is really very apt.

A table contains the tasks planned by the user. In my implementation, this is a Paradox table, but you could use just a file and a TclientDataSet to keep this data. I do not recommend placing it in Oracle, especially if you are going to shut down the database. Day, Hour and Minute fields provide information about the time of the task, the Repetition (‘This’/’Every’) field allows both one-time and repeated backups to be carried out, the User/Password field indicates which user is to be exported and, finally, two boolean fields mark if this is a restricted session or Full shutdown backup task. The Locate function scans the table every minute to check the current task. If a task is identified, Delphi gathers from Oracle some additional data, if need be, and “ talks “ to Oracle about what it is supposed to do. Oracle executes and Delphi controls the results.

I use three ways to “talk” to Oracle: the SQL, the Export Utility (EXP) and the Server Manager (SVRMGR). Trough run-time produced batch files Delphi sends commands directly to the last two. Curiously this is exactly the approach Oracle uses by itself when the client is commanding the server. And Oracle has the good manners to return the rvcesults after a command execution in the form of log files if you ask it for this favour. It is not hard for Delphi to read the files and report to you in the end.

This is the ‘dialog’ scheme. The implementation is, as I have already mentioned, very easy to carry out.Before starting, you should bear in mind some features of the different Oracle versions. For instance , there are different ways to call the ServerManager and the Export in different versions. For Oracle 7.3 these are ‘SVRMGR23’ and‘EXP73’ , but for Oracle 8.05 ‘EXP80’ and ‘SVRMGR30’.


Figure 1:The Oracle Task Scheduler.

The export command in Oracle 8 doesn't insist on password identification of the exported user. You could turn to Oracle documentation for more details about your specific version. It is also very important to give the OS a chance to complete fully one task before asking for the result. So I have used the popular “ExecandWait” function instead of ShellExecute. The last consideration is the user you are using to connect to Oracle to perform the backups. You should have permissions to do all the operations, and you should know the identification of your user in order not to kill its sessions if you are going to switch to restricted mode. By bad style – I do like the unlimited power – I am still using INTERNAL but Oracle has promised to drop it, so using just your system manager user is more recommendable.

EXPORT AND THE RESTRICTED SESSION OPTION

To export you should send a command line something like:

exp73 system/manager userid=ntr/kelantr
file=\\ntr_nt\Data\MbBackup\ntr LOG=ORAEXP80.LOG ;

The first parameter here is your user identification, the second is the user you are intending to export and the last is the logfile where the results will be reported by Oracle (additional parameters are available, but for my task this combination works well). It is advisable to identify the log file in a unique way for every operation. This could be an additional information source for you. I have used the time parameters in the Task table to create names for the log files, all the other parameters are also from the Task table. Before the export Delphi rewrites the batch file with “fresh stuff”, executes it, reads the Oracle log file and reports the resume on the screen. That is the scheme .
If you want to switch to restricted mode before export, some additional work should be done. First, you should obtain a list of all sessions. It could be gained by querying the V$SESSION view. For example,

SELECT SID, SERIAL# FROM V$SESSION WHERE USER#<>0

gives all the sessions except those owned by INTERNAL. You should update this statement according to your user. Next kill the sessions, which is easy already having their SID:

ALTER SYSTEM KILL SESSION {the SID of the session}

And finally, you restrict the database &#8211;

'ALTER SYSTEM ENABLE RESTRICTED SESSION'

Now no one else can access it. At this point you are free to do your consistent export. Don't forget to disable the restricted session, otherwise your clients will not be able to access the database after the export.

THE FULL OFFLINE BACKUP

This process is a little bit more complex, but also achievable by Delphi. Before starting, you should have a list of the datafiles and the redo log files by quering Oracle:

'SELECT NAME FROM V$DATAFILE'
'SELECT MEMBER FROM V$LOGFILE'

These are the files you will copy after the shutdown. You could also copy the controlfile, or back it up by using the ALTER DATABASE command with the BACKUP CONTROLFILE TO &#8216;filename&#8217; option. See the Oracle documentation for more information about backing up control files. It is important to shut the database down with immediate, rather than abort priority. A simple script is sent by Delphi to Server Manager to obtain this:

Connect internal/oracle;
Shutdown immediate;
Exit;

After shutdown, Delphi re-checks the log file and &#8211; ithe shutdown is successful, starts copying thfiles on the selected destination. I find the Windows.CopyFile function useful enough for this task, altough other approaches are also possible. The last step is to restart the database after the backup with pretty simple set of commands to the Server Manager:

Connect internal/Oracle;
Startup;
Exit;

Delphi re-checks the execution and reports the results.

CONCLUSION

Working on this topic, I enjoyed how open for dialog both Delphi and Oracle can be. It is enjoyable and it can reveal new approaches and new ideas for database developers. So, the Delphi/Oracle combination is something worth trying: they not only work together excellently, but also talk to each other in many ways and for the benefit of different developers' and administrators' tasks.


Component Download: DialogWithOra.zip