Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Oracle Concepts - Imports and Exports

Oracle Tips by Burleson Consulting

Imports/Exports

Imports and exports extract or insert an Oracle-readable copy of the actual data and structures in the database. The exports can be used to recover single data structures to the date and time the export was taken. Exports come in three types: full, cumulative, and incremental. Full, as its name implies, provides a full logical copy of the database and its structures. A cumulative provides a complete copy of altered structures since the last full or the last cumulative export. Incremental exports provide a complete copy of altered structures since the last incremental, cumulative, or full export.

Limitations on export/import:

* A database must be running to perform either an export or import.

* Export files shouldn?t be edited and can only be used by import.

* (Import only) imports full tables; it can?t be used to do a conditional load.

* Exported data is only a logical copy of the data. An export can only allow recovery to the date and time the export was taken.

* Imports and exports are accomplished using the Oracle IMPORT and EXPORT utilities.

Exports

For exports, the EXPORT utility is used. The format for using this command follows:

Format:  EXP KEYWORD=value ?or? KEYWORD=(list of values)

Example: EXP AULT/AUTHOR GRANTS=N TABLES=(CHAPTERS, EDITORS,ADVANCES)

Keyword

Description (Default)

USERID

username/password

BUFFER

size of data buffer

FILE

output file (EXPDAT.DMP)

COMPRESS

import into one extent (Y)

GRANTS

export grants (Y)

INDEXES

export indexes(Y)

ROWS

export data rows (Y)

CONSTRAINTS

export table constraints (Y)

CONSISTENT

cross-table consistency (N)

LOG

log file of screen output (None)

STATISTICS

analyze objects (ESTIMATE)

DIRECT

Bypass the SQLcommand processing layer (N) (new in Oracle8)

FEEDBACK

Show a process meter (a dot) every X rows exported (0 ? Xvalue)

HELP

Shows help listing     

MLS

MLS_LABEL_FORMAT  Used with secure Oracle; we won't cover these.

FULL

export entire file (N)

OWNER

list of owner usernames

TABLES

list of table names

RECORDLENGTH

length of IO record

INCTYPE

incremental export type

RECORD

track incr. export (Y)

PARFILE

parameter file name

Exports should be automated and scheduled to run automatically. An export methodology should be worked out such that the DBA is reasonably certain a deleted file can be recovered. The parameters for export can either be placed on the command line, or, in a parameter file which can then be accessed using the PARFILE command line option.

IMPORT

The format of the IMPORT command follows.

Format:  IMP KEYWORD=value ?or? KEYWORD=(list of values)

Example: IMP AULT/AUTHOR IGNORE=Y TABLES=(EXPENSES, ADVANCES) FULL=N

Keyword

Description (Default)

USERID

username/password

BUFFER

size of data buffer

FILE

Output file (EXPDAT.DMP)

SHOW

just list file contents (N)

IGNORE

Ignore create errors (N)

RECORDLENGTH

length of IOrecord

GRANTS

Import grants (Y)

INDEXES

Import indexes (Y)

ROWS

Import data rows (Y)

LOG

log file of screen output

INDEXFILE

write table/index info to specified file

FULL

Import entire file (N)

FROMUSER

list of owner usernames

TOUSER

list of usernames

TABLES

list of table names

FEEDBACK

Provide dot status graph (0)

INCTYPE

incremental import type

COMMIT

commit array insert (N)

PARFILE

parameter file name

DESTROY

overwrite tablespace data (N)

CHARSET

char. set of export file (NLS_LANG)

Under Oracle7, the user must be granted the EXP_FULL_DATABASE role in order to do full exports. In order to perform a full import, the user must have the IMP_FULL_DATABASE role. The users with the DBA role are granted these implicitly.

An example of when the DBA would want to grant these roles to a user would be a user whose password is specified in the command script used for doing the automatic exports. If the only role granted to the user is CREATE_SESSION and EXP_FULL_DATABASE even if the user?s password is compromised, they won?t be able to do much damage.

Archive Logs

The redo logs store all transactions that alter the database, all committed updates, adds, or deletes of tables, structures, or data. If archiving is disabled, only data in the current off-line and on-line redo logs can be recovered. If the system recycles through all redo logs, the old ones are reused, destroying their contents. If  archive logging is enabled, the redo logs are written out to storage before reuse. Archive logging allows recovery to a specific point in time since the last full cold backup or complete off-line backup. Under Oracle8i archive logs can be duplexed. The initialization parameters that control archive logging are:

Parameter

Meaning

LOG_ARCHIVE_START

If set to TRUE start archive process

LOG_ARCHIVE_BUFFERS

Number of log archive buffers

LOG_ARCHIVE_BUFFER_SIZE

Size of the log archive buffers

LOG_ARCHIVE_MIN_SUCCEED_DEST

Percentage of archive logs which must reach destinations

LOG_ARCHIVE_DEST

Primary Archive Log Location

LOG_ARCHIVE_DUPLEX_DEST

Secondary Archive Log Location

LOG_ARCHIVE_DEST_1

Archive tertiary location 1

LOG_ARCHIVE_DEST_2

Archive tertiary location 2

LOG_ARCHIVE_DEST_3

Archive tertiary location 3

LOG_ARCHIVE_DEST_4

Archive tertiary location 4

LOG_ARCHIVE_DEST_5

Archive tertiary location 5

LOG_ARCHIVE_FORMAT

 

Specifies the format for archive log names, %s and %t add the sequence and redo thread numbers to the format.

Under Oracle7 and Oracle8 redo logs are specified in groups; each group forms a shadow set and is archived together. Under Oracle7 archive logs can also be assigned to threads for use in parallel or shared instances. Individual logs are called members. Threads hold groups that hold members. Each member of a redo log group is the same size and should be on separate physical platters. Oracle automatically synchronizes members of a group into a shadow set.

Redo logs cannot be used to recover a database brought back from a full export.

To switch a database that is not currently using archive logging to use archive logging the steps are:

1. Shutdown database using immediate or normal options

2. Edit the initialization parameter file to include appropriate archive log parameters, at a minimum:

       ARCHIVE_LOG_START = TRUE
ARCHIVE_LOG_DEST = destination (operating system specific path to archive log destination)
       ARCHIVE_LOG_FORMAT = arch_%t_%s.arc
 

Usually the defaults for LOG_ARCHIVE_BUFFERS and LOG_ARCHIVE_BUFFER_SIZE are sufficient.

3. Using the appropriate interface (svrmgrl, svrmg23, sqlplus) startup the database in mounted mode:

       Svrmgrl> connect internal/password as sysdba
       Svrmgrl> startup mount pfile=<initialization file location>

4. Use the ALTER DATABASE command to reset the ARCHIVELOG mode:

       Svrmgrl> ALTER DATABASE ARCHIVELOG;

5. Use the ALTER DATABASE command to open the database.

       Svrmgrl> ALTER DATABASE OPEN;

6. Either shutdown and perform a cold backup or perform a hot backup. Since this is the first backup, I would suggest a cold backup be used. This is the baseline backup of your database.

7. Restart the database as you would normally.


Proper use of these backup/recovery tools allows the DBA to recover from any possible failure.


 
 
 
Get the Complete
Oracle Utility Information 

The landmark book "Advanced Oracle Utilities The Definitive Reference"  contains over 600 pages of filled with valuable information on Oracle's secret utilities. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.
 


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.