0401-0403

Previous Table of Contents Next

Page 401

 #         Written by Kelly Leigh and Dave Kinchen # #          Usage: export_ts tablespace, tablespace, tablespace # #          Requirements:  must be able to access DBA_TABLES #          and must be assigned the role EXP_FULL_DATABASE #          developed on a DGUX platform ##### # Setup filenames that we need to create the export file # Check to see if there are any tablespaces listed # if not then display the usage instructions if [ "$*" = "" ]   then     echo "Description:"     echo "      export_ts is a DBA utility to export all tables in"     echo "      one or more tablespaces. export_ts accepts a single"     echo "      (or list) of tablespace names separated by a space."     echo "Usage:"     echo "      export_ts (tablespace name) (tablespace_name) "     echo "Example:"     echo "      export_ts users"     exit fi # Create list of tablespaces to run through export echo "Creating a list of tables in tablespaces: $*" TSLIST="" for TS in $*   do     TSLIST="$TSLIST,'$TS'"   done TSLIST=`echo $TSLIST  cut -c2-` TSLIST=`echo $TSLIST  tr `[a-z]' `[A-Z]'` # Select table names from sqlplus and pipe them out to a listing file # Send the output of the sqlplus statement to the bit bucket since # the spool file will have it. sqlplus -s / << EOF  > export_ts.spl set pages 0; set heading off; set feed off; SELECT owner  `.'  table_name FROM dba_tables WHERE tablespace_name IN (${TSLIST}) ORDER BY   owner,   table_name / EOF # Echo the listing for the DBA to verify echo "TABLE LISTING:" more export_ts.spl echo "Continue with the export? (Y/N) \c" 

Page 402

 read CHOICE CHOICE=`echo $CHOICE  tr `[a-z]' `[A-Z]'` # If yes then first, add commas to the spool file if [ "$CHOICE" = "Y" ]   then     echo "USERID=/" > $PFILE     echo "FILE=export_ts.dmp" >> $PFILE     echo "TABLES=" >> $PFILE     cat export_ts.spl  sed -e "s/  *$//" -e `$!s/$/,/' >> $PFILE     echo "\n\nStarting the export.  All export data will be written to export_ts .dmp"     exp parfile=export_ts.par   else     echo "Aborting export." fi 

Types of Exports: Complete, Incremental, and Cumulative

The parameter INCTYPE tells Export to create one of three types of export files: complete, incremental, or cumulative. By using the incremental and cumulative exports, you can save space and time because only the changed tables are exported. The complete option exports the full database; it's the default for INCTYPE.

Complete

To use the complete parameter, the user must be able to do a full database export (FULL=Y). A full database export will export all the database objects so that database can be re-created from the ground up. The full database export will act as the starting point for a complete import. You must already have a complete export for the incremental and cumulative exports to be of any value because you import them after you import the complete export.

Incremental

Incremental exports are where you can save time by exporting only the changed tables in the database. Most databases have tables that change slowly or never at all. If your database is fairly stable, you should consider using the incremental exports. These exports are usually faster than complete exports and consume less space. The export has only tables in it that have changed since the last incremental export, or if no other incremental exports exist, it exports tables that have changed since the last cumulative export. Lastly, Export looks for tables that have changed since the last complete export if a cumulative export does not exist. To use the incremental exports, you import them after the complete and cumulative exports are finished.

Cumulative

A cumulative export, in a sense, makes all the incremental exports unnecessary. The cumulative export will export all the tables since the last complete export, including all the tables currently exported in the incremental exports. You do not need the incremental exports once a cumulative is completed.

Page 403

A Backup Strategy

You can use Import and Export as a primary backup scheme for Oracle databases. It is simple and reliable, and you can move the export file to a different platform if necessary. Incremental exports can reduce the time needed for the nightly exports. Exporting using an incremental export could decrease a system's downtime during the week and increase processing time and user satisfaction at the same time. Weekly cumulative exports provide for fairly quick recoveries . Monthly complete exports are sufficient on most databases. This scenario takes a full month to repeat itself, and you can also import your data files fairly quickly, if needed.

NOTE

Obviously, you should not store the export files on any device that contains any Oracle data files, control files, and so on. If you were to store export files on any device that contains any Oracle data files or control files, a media failure on that device could very likely make your Oracle database unrecoverable!

Importing a database that you backed up using the incremental export is easy. You should be able to do this in your sleep. To re-create the database, you need the complete export along with all the cumulative exports and incremental exports since the last cumulative export. You begin the recovery by importing the complete export. Next, you import all the cumulative exports; finally, import the incremental exports.

Import

The Import utility is the converse of the Export utility. On the surface, Import seems to be a simple utility supplied by Oracle ”indeed, it functions that way ”but it also has quite a few powerful features. It is responsible for reading the export files to recreate the database objects as well as whatever state they were exported in originally. Import can also convert between ASCII and EBCDIC to enable you to move data between platforms. We discuss the more common features later. Import can work interactively or from the command line. When used interactively, Import prompts the user for the necessary parameters to complete the import. It is often easier to supply the parameters on the command line or from a parameter file. Import, like Export, uses parameter files. Table 17.2 provides a full list of the import options and their features.

Import can read export files created by Export version 5.1.22 and later.

Import Usage

The following code line is an example of using Import:

 imp userid=scott/tiger file=expdat.dmp  [other options] 

Previous Table of Contents Next