Dba Imp Exp Friend

DBA

EXPORT/IMPORT –THE DBA’S BEST FRIEND Michael S. Abbey, The Pythian Group Inc.

PREAMBLE Export and import—the undisputed workhorses of Oracle utilities. We’ve been using export and import since this software first hit the streets and are learning more about them each time we put them to use. They play a role in the design and implementation of a backup/recovery strategy. In this paper we will introduce export and import and •

Look at a handful of their common parameters

•

Discuss some of their non-shared parameters

•

Discuss the role they play in a robust comprehensive backup strategy. At the live presentation during the conference, we will look at export/import modes of operation and exporting data through a UNIX compressed pipe. Export and import, from day one, require a thorough understanding of what they can do, what they are designed to accomplish, and what can go wrong when not used properly. Let’s get our feet wet.

EXPORT AND IMPORT PARAMETERS Both these handy utilities share many parameters, whereas others are unique to each one. Online help is displayed by running each program with the parameter value help=y as shown next. For purposes of this paper, I have cut out the bottom of these two help displays as the parameters listed towards the bottom are for more advanced discussions of export and import. /u01/app/oracle/product/9.0.1> exp help=y Export: Release 9.0.1.0.0 - Production on Thu Sep 13 21:55:49 2001 (c) Copyright 2001 Oracle Corporation. All rights reserved. You can let Export prompt you for parameters by entering the EXP command followed by your username/password: Example: EXP SCOTT/TIGER Or, you can control how Export runs by entering the EXP command followed by various arguments. To specify parameters, you use keywords: Format: EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN) Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR) or TABLES=(T1:P1,T1:P2), if T1 is partitioned table USERID must be the first parameter on the command line. Keyword Description (Default) Keyword Description (Default) -------------------------------------------------------------------------USERID username/password FULL export entire file (N) BUFFER size of data buffer OWNER list of owner usernames FILE output files (EXPDAT.DMP) TABLES list of table names COMPRESS import into one extent (Y) RECORDLENGTH length of IO record GRANTS export grants (Y) INCTYPE incremental export type INDEXES export indexes (Y) RECORD track incr. export (Y) DIRECT direct path (N) TRIGGERS export triggers (Y) LOG log file of screen output STATISTICS analyze objects (ESTIMATE) ROWS export data rows (Y) PARFILE parameter filename CONSISTENT cross-table consistency CONSTRAINTS export constraints (Y) FEEDBACK display progress every x rows (0) Paper 503

DBA

FILESIZE

maximum size of each dump file

Export terminated successfully without warnings.

/u01/app/oracle/product/9.0.1> imp help=y Import: Release 9.0.1.0.0 - Production on Sat Sep 15 15:41:19 2001 (c) Copyright 2001 Oracle Corporation. All rights reserved. You can let Import prompt you for parameters by entering the IMP command followed by your username/password: Example: IMP SCOTT/TIGER Or, you can control how Import runs by entering the IMP command followed by various arguments. To specify parameters, you use keywords: Format: IMP KEYWORD=value or KEYWORD=(value1,value2,...,valueN) Example: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N or TABLES=(T1:P1,T1:P2), if T1 is partitioned table USERID must be the first parameter on the command line. Keyword Description (Default) Keyword Description (Default) -------------------------------------------------------------------------USERID username/password FULL import entire file (N) BUFFER size of data buffer FROMUSER list of owner usernames FILE input files (EXPDAT.DMP) TOUSER list of usernames SHOW just list file contents (N) TABLES list of table names IGNORE ignore create errors (N) RECORDLENGTH length of IO record GRANTS import grants (Y) INCTYPE incremental import type INDEXES import indexes (Y) COMMIT commit array insert (N) ROWS import data rows (Y) PARFILE parameter filename LOG log file of screen output CONSTRAINTS import constraints (Y) INDEXFILE write table/index info to specified file Import terminated successfully without warnings.

The sheer volume of these parameters seems overwhelming at first—let’s delve into some common parameters first then look at some unique to each tool. After each section, we will look at a few common requirements and how to use export and import with the parameters discussed so far.

PARAMETERS COMMON TO EXPORT AND IMPORT Although we will not look at all the parameters listed in these help screens, we’ll spend some time on the most common. The secret to becoming comfortable with export and import is to keep it simple at first, then move forward into using more complicated scenarios.

USERID This parameter is followed by the username and password running the export/import session. Normally the account is deliberately privilege rich so it can export data from or import data into multiple schemas. If you run from an account the $ character (e.g., OPS$ABRAMSON) in UNIX, you need to escape the $ or enclose the username/password combination in single quotes as shown next. exp userid='ops$abramson/yatfg' imp userid=ops\$abramson/yatfg BUFFER

This parameter instructs Oracle on how much memory to allocate as a work are for the export/import session. The default allocated is simply not enough and you would be wise right from the start to code values of 1,000,000 bytes or more. A few examples of using buffer are shown next. Paper 503

DBA

exp userid='ops$abramson/yatfg' buffer=10485760 imp userid=ops\$abramson/yatfg buffer=209715200 FILE

We use this parameter to tell export/import what file to use for the session. Normally we specify a file name and Oracle tacks the extension .dmp on the end. From day one, be smart and make your file names meaningful as shown in the next commented listing. # Export full database exp userid=ops\$abramson/yatfg buffer=10485760 file=fulldb_tpg . . . # Export user manytrees exp userid=ops\$abramson/yatfg file=manytrees_user . . . GRANTS {Y|N}

This parameter instructs export and import as follows: •

Export—grants=y causes SQL grant statements to be written to the export file.

•

Import—grants=y ensures that grant statements found in the export file are executed after objects and data are brought into the target schema(s).

Coding grants=n causes the message Note: grants on tables/views/sequences/roles will not be exported to be displayed. This is not fatal to the export session. INDEXES {Y|N}

Coding indexes=y on an export is dealt with in the same manner as the behaviour of the grants parameter. The indexes themselves are not dumped to the export file, simply the appropriate SQL statements required to create the indexes after their table data is brought in by import. Coding indexes=n causes the message Note: indexes on tables will not be exported, as well not fatal to the export session. ROWS {Y|N}

This parameter instructs export to extract the data as well as the data definitions to the export file. On the other end, this parameter influences import as to whether the data itself (rows in the tables in the export file) should be brought in to the specified schema(s). Coding rows=n displays the warning Note: table data (rows) will not be exported. FULL {Y|N}

This parameter, though common to export and import, has different meanings to each. •

When used with export, full=y instructs Oracle to do a full export, extracting all data definitions and data for all schemas in the database. For example, SQL statements to create the tablespaces are written to an export file written with this parameter set to y.

•

When used with import, full=y instructs Oracle to import the entire contents of the export file. Think of this parameter as a directive to import the full file.

LOG

This parameter is followed by the name of a file to track and save the operation and results of the export/import session. Be smart when naming the log file. The last line of the log file will display two pieces of very useful information—the completion status of the work and whether there were any errors encountered. These errors are usually in the form of Oracle or EXP/IMP messages as shown next. EXP-00011: OPS$ABRAMSON.ALTIMA does not exist Paper 503

DBA

Export terminated successfully . . . IMP-00015: following statement Import terminated successfully . . . ORA-01435: user does not exist Import terminated successfully

with warnings. failed because the object already exists: with warnings. with warnings.

PARFILE

This parameter is followed by the name of a text file containing further parameters and values to control the export/import session.

EXAMPLE #1 Requirement: Perform an export of data from the WATERS account (password DIAMOND); trap the output for subsequent inspection Solution: exp userid=waters/diamond file=waters_data log=waters_data_out buffer=2000000

EXAMPLE #2 Requirement: Perform an export of the same user, but with no grants or actual data; as well, generate a log file. Solution: exp userid=waters/diamond file=waters_no_data log=waters_no_data_out buffer=2000000 grants=n rows=n

EXAMPLE #3 Requirement: After rebuilding a database on another machine, bring the WATERS schema data into the fresh repository. Solution: imp userid=waters/diamond file=waters_data log=waters_data_in buffer=2000000

PARAMETERS ONLY USED WITH EXPORT There is some protection against coding an export parameter with import and vice versa. The listing below illustrates the feedback provided by one utility (e.g., export) when trying to use the other’s exclusive parameter. EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help EXP-00000: Export terminated unsuccessfully OWNER

This parameter lists one or more owners whose objects are to be exported. The schema names, if more than one, are separated by commas. The list of multiple schemas should be bound by parentheses as shown in the next listing. This listing as well shows the problem that comes up when using parentheses on the UNIX command line in ksh and bash. exp owner=(track,avenue) ksh: syntax error: `(' unexpected exp owner=(track,avenue) bash: syntax error near unexpected token `owner=(t'

Note how the singular of owner is still used even if exporting multiple owners. TABLES

This parameter instructs export to include the specified list of tables in the export session. As with owner, the same rules with commas and parentheses apply. TRIGGERS {Y|N}

This parameter, when coded with a value n, tells export not to write table level trigger PL/SQL code to the export file. In many situations, this is desirable to ensure that triggers are not created and allowed to fire unbeknownst to you as you perform an Paper 503

DBA

import. Rest assured, the behaviour of triggers on an import is such that the triggers are created after the corresponding table has been defined and its date brought in. The directive triggers=n DOES NOT WORK in any version of Oracle8i; this is a known bug that is not fixed until 9i.

EXAMPLE #4 Requirement: As user GILMOUR (password crazy) perform an export of data from the WATERS schema. Solution: exp userid=gilmour/crazy file=waters_data log=waters_data_out buffer=2000000 owner=gilmour

EXAMPLE #5 Requirement: As user GILMOUR (password crazy) perform an export of data from only the BRICK_IN_THE_WALL table belonging to WATERS. Solution: exp userid=gilmour/crazy file=waters_data log=waters_data_out buffer=2000000 tables=waters.brick_in_the_wall This example is the first time (in this paper) you could very easily find yourself in a pickle had you coded owner=waters and tables=brick_in_the_wall. This would generate an Oracle EXP error similar to the following. We will be covering more about conflicts between parameters at the presentation during Live 2002. EXP-00026: conflicting modes specified EXP-00000: Export terminated unsuccessfully

EXAMPLE #6 Requirement: As user GILMOUR (password crazy) perform an export of data belonging to MASON, WRIGHT, and COUNSEL. Solution: exp userid=gilmour/crazy file=mwc_data log=mwc_data_out buffer=2000000 owner=\(mason,wright,counsel\)

PARAMETERS ONLY USED WITH IMPORT The import-only set of parameters require a little more explanation, so let’s look at the three main one. IGNORE {Y|N}

At first glance, this parameter can be confusing—its value instructs Oracle what to do on an import when it tries to bring a table in that already exists in the target schema. The logic shown in the next listing best describes the behaviour of At first glance, this parameter can be confusing—its value instructs Oracle what to do on an import when it tries to bring a table in that already exists in the target schema. The logic shown in the next listing best describes the behaviour of y and n for the ignore parameter. if ignore=y (is coded with the call to import) then if table to be imported already exists then ignore the error raised by trying to re-create an existing table end if bring that table’s data into the target schema else (ignore=n is coded) if table to be imported already exists then IMP-00015: following statement failed because the object already exists: else create the table bring that table’s data into the target schema end if end if COMMIT {Y|N}

This parameter when turned on with a y value, instructs Oracle to issue a commit statement after every pre-determined amount of data has been written to the target tables. This amount is affected by the size you code for its sister parameter buffer. The larger a buffer, the more data will be written to the target schema between commits. In situations where rollback is Paper 503

DBA

an issue (e.g., importing the multi-million / multi-gigabyte tables), using commit=y can help ensure the import sessions runs successfully to completion. FROMUSER / TOUSER

These two parameters are the pièce de resistance (also referred to as the cat’s meow in America) with import. This is how you take data from one schema (PIPERAT for example) and bring it into target schema with a different name (say something like THEGATESOFDAWN). The next listing show an import session using these parameters. imp userid=mason/nick file=piperat_full fromuser=piperat touser=thegatesofdawn \ > buffer=2000000 log=p_2_t ignore=y INDEXFILE

The value coded for this parameter is the name of a text file to which all the table, index, and constraint definition SQL*Plus text will be written. No data is brought in and no objects are created. This text file can be edited and run in SQL*Plus. A snippet of code from an import using indexfile=echoes.sql is shown next. REM CREATE TABLE "PIPERAT"."UMMAGUMMA" ("FIRST_NAME" VARCHAR2(30), REM "LAST_NAME" VARCHAR2(15), "EMAIL" VARCHAR2(100), "NOT_CONFIRMED" REM VARCHAR2(1)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING REM STORAGE(INITIAL 32768 NEXT 8192 MINEXTENTS 1 MAXEXTENTS 2147483645 REM PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) REM TABLESPACE "RELICS" ; REM ... 83 rows CONNECT PIPERAT; CREATE INDEX "PIPERAT"."UG_1" ON "UMMAGUMMA" ("EMAIL" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "RELICSX" LOGGING ;

EXAMPLE #7 Requirement: As user WISHYOU (password werehere), create a text file containing all the SQL statements contained in an export file pulled from the AXIOM database. Solution: imp userid=wishyou/werehere file=axiom_full log=axiom_full_in buffer=2000000 full=y indexfile=axiom_full.sql

EXAMPLE #8 Requirement: As user THELUNATIC (password isinthegrass), create a copy of the USANDTHEM schema in the MONEY target schema. The tables in the MONEY schema have been created and are all empty. Solution: imp userid=thelunatic/isinthegrass file=usandthem_full log=usandthem_full_in buffer=2000000 fromuser=usandthem touser=money ignore=y

EXPORT AND IMPORT’S ROLE IN BACKUP Export and import are easily more than 50% of many installations’ backup strategy. Most sites plan exports to run during the wee hours of the morning, hopefully when then systems are least used each 24 hour period. If quiet time does not exist, export can run while systems are being used though its running is another drain on your server’s precious resources. The best way to summarize the complete export-based backup is shown in Table 1.

Paper 503

DBA

Component

Parameter highlights

Discussion

Full database export – just the data

full=y log=fulldb_noic indexes=n file=fulldb_noic constraints=n

I like to use the noic terminology that stands for no indexes/constraints. Export will warn you that indexes and constraints are not being exported, but we know better, that it doesn’t matter, since they will be trapped in the next export.

Full database export – everything but the data

full=y log=fulldb_nor rows=n file=fulldb_nor

The nor acronym stands for no rows. The size of this export file is normally very very small compared to its noic counterpart. There’s not much in this export other than the SQL statements to create just about anything in the database except the SYS schema.

Table 1: Full database exports

CHECK THE INTEGRITY OF YOUR EXPORTS It has often been said that a backup is next to useless unless it has been tested as part of a recovery strategy. Using the indexfile parameter with import is the quickest way to verify the usefulness of the exports you write as part of system backups. The next listing shows the steps in writing the full database export, then quickly checking the export file’s integrity. /home/pythian> exp userid=expf/yyhs7s8 full=y indexes=n constraints=n log=fulldb_noic \ > file=fulldb_noic buffer=5000000 Export: Release 9.0.1.0.0 - Production on Sun Dec 23 20:07:12 2001 Copyright (c) 2001 Oracle Corporation.

All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production With the Partitioning option JServer Release 9.0.1.0.0 - Production Export done in WE8DEC character set and WE8DEC NCHAR character set About to export the entire database ... . exporting tablespace definitions . exporting profiles . exporting user definitions . exporting roles . . . . exporting synonyms . exporting views . . . exporting statistics Export terminated successfully without warnings. /home/pythian> imp userid=expf/yyhs7s8 full=y indexfile=ftest.sql buffer=10000000 Import: Release 9.0.1.0.0 - Production on Sun Dec 23 20:15:25 2001 Copyright (c) 2001 Oracle Corporation.

All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production With the Partitioning option JServer Release 9.0.1.0.0 - Production Export file created by EXPORT:V09.01.00 via conventional path import done in WE8DEC character set and WE8DEC NCHAR character set . . . . . . . . . Import terminated successfully without warnings. Paper 503

DBA

This method for verification of your exports is quick and almost fool proof. Murphy’s law dictates that something may end up going wrong some day with one of your database exports. Checking the export file using this indexfile= approach will increase the likelihood that your exports are usable and will not return an all too familiar error as shown next. IMP-00009: abnormal end of export file Import terminated successfully with warnings.

WRAPUP We have covered quite a bit of ground in this paper. Many readers may already be familiar with the in’s and out’s of using export and import. As you become more and more comfortable with their power and their parameters, you will understand the relationship between parameters and how they affect one another. My advice to any DBA, be it one with 6 weeks or 6 years experience, is get to know export and import like the back of your hand. Experiment with different export scenarios and test your exports, test your exports, and test your exports.

ABOUT THE AUTHOR:

Michael S. Abbey is the lead in a very successful offering of remote DBA services with the Pythian Group in Ottawa Canada. Michael has co-authored 10 works in the Oracle Press series, the most successful being the Beginner’s Guide series. Michael is an instructor at the IOUG’s University Master Class, delivering guidance in the area of Advanced DBA Best Practices. He has been working with the Oracle Server for the past 16 years, having seen versions 3 through 9i.

Paper 503