Backing up a database is probably the most important activity of a DBA. All applications rely on the database, and if there is any interruption with database access, it should be resolved at earliest time possible. Now, what can be the disruptions? The oracle executable and software can be corrupted or the disk on which the oracle software resides may be lost. The processor or memory or the I/O controller of the server on which the database resides may be lost. The disks on which oracle datafiles reside may be lost. whatever be the problem, it is ultimately the oracle DBA who has to restore and recover till the point of loss.

sometimes, it may be the reverse, DBA may be asked to take back the database to a past point in time, say before 10 minutes, because some careless user dropped an important object and this has to be restored, even if 10 minutes of transactions , we can afford to lose or ask users to redo.

There are basically two types of database backups (here, we are talking only about the loss of datafiles, and other oracle files and not about the backup of Oracle software itself- which is the usual system admin task)

One is the ‘logical‘ backup, which is a backup of database using oracle utilities such as ‘exp’ or ‘expdb’, which produces a single file which can be read only by corresponding oracle utility ‘imp’ or ‘impdb’. However, this backup is not a ‘production’ backup, this cannot be used for recovery purposes, as it will only restore the database to a past time, which was the time the database was logically backed-up. so, even if the frequency of the logical backup is twice a day and let us say, last backup was taken at 9:00 hours in the morning, and next backup is scheduled at 18:00 hours at night, and database is lost at 16:00 hrs, the database can be backed up only as at 9:00 hours, so the transactions between 9:00 to 16:00 hrs will be lost. Obviously this is not a good backup method. The only use of this method is for duplicating a database, (but for this also, cloning is faster and easier) and for migrating a database from one version to another, or for migrating a database from one O/S platform to another.

The other type of backup is the ‘physical’ backup, where the datafiles are copied to an archived location, and if any of datafiles are lost, the copy of the datafile is restored to the location, and database is recovered from point of timestamp of the datafile copy to the current timestamp, by applying the changes from redo logs (current redo logs and archived redo logs)

There are two types of such physical backups, one is off-line backup, which is again not a production backup, as it involves shutting down the database, which is not feasible in a 24X7 database environment. The other practical. and useful type is the ‘online’ backup, which is backing up the datafiles when the database is open and in use.

The physical backups can be taken either through user-scripts, outside the oracle using the copy commands of operating system (however before copying the datafiles, the associated tablespace should be put in backup mode, by using oracle command ‘alter tablespace <tablespace_name> begin backup’, (or) alternatively and prefeably, oracle’s own utility called RMAN (recovery manager) can be used. When RMAN is used, there is no necessity to begin and end backups, scripts can be created once and automated to run at desired times.

Again, using RMAN, there are two possibilities of backup, one is to make a backupset (which is a oracle proprietory format) from the datafiles, or to make copies of datafiles (simple o/s level copy). We will see the physical backups in later session. Now, we will see the logical backups.

The logical backups are taken using the oracle supplied utility ‘exp‘ or ‘expdp’(data pump).These are to be executed from the o/s prompt. First we will see the exp utility, this is around for quite sometime and it is a ‘client‘ utility. i.e, exp unlike ‘expdp’ can be run from any client machine where the oracle client software is installed, and database can be accessed through service name.The dump file is also created in the client box, unlike in ‘expdp’ (data pump) utility where it is only created in the server.

At o/s prompt, run either

c: exp parfile=exp_01_somename.par

or

c: exp userid=scott/tiger@sample full=y file=’c:dumpexp01.dmp’

i.e, either a parameter file can be created putting in all parameters, and this parameter file should be issued as a parameter to the exp command (or) all parameters can be given in the exp command line itself.

Now, the mandatory parameters are only two, the userid/password and the mode of export (full=y (or) full=n) if full=n, then user to be exported is to be given, and if export is table-level, then the table names are to be given. Rest parameters will be taken as default values, file name if not given will be taken as exp.dmp and it will be created in the folder from where the ‘exp’ utility is run.

The useful parameters for exp are;

• userid (username/password@servicename)
• buffer (buffer-size used by export to fetch rows from database)
• file (name and location of export file produced)
• grants (whether grants to be included in export)
• indexes(whether indexes to be included)
• rows(whether rows are included or only table structure is exported)
• compress(if ‘Y’, then export compresses all data of a segment in a single initial extent)
• full(if ‘Y’ it is a database level export)
• owner (if owner=scott, then only scott’s schema is exported)
• tables( table names which are only to be exported are given here)
• Direct( If ‘Y’ then direct-path export which is very fast is executed)

There are three modes of exports, complete exports, cumulative exports and incremental exports, but these will not be studied, as only complete export is meaningful, we have seen logical backups cannot be reliable production quality backups, these are more of one-time activity at time of migration, etc.

Now, import is the complementary activity, the utility name is ‘imp’ Again for import, there are parameters. The parameters which are important from imp point of view are

• ignore (if ‘Y’ then errors during import are ignored- like table structure already existing etc)
• fromuser (schema from where export was taken)
• touser (schema to which data needs to be imported)
• indexfile(whether indexes need to be created while importing or only file containing scripts for indexes to be created) Now this is a very important parameter which can speed up import, we can opt to create indexes later. This has another side-effect, we can edit this file to change the tablespace storage parameters for the objects and then create the objects.This is useful for reorganising the storage. No more relevant in oracle 10g and 11g, but earlier versions would benefit.

There are three views which hold information on export and import done on the databases and these views are INCEXP, INCFIL and INCVID. You can describe these views (only as sysdba user) and check the contents to know their usage.

practice using ‘exp’ utility to export data from say ‘scott’ schema, and then import the data into another schema. Play around with the parameters and see the effects. Create the indexfile and open it in notepad and edit it and see how you can create the indexes using these scripts.