oracle logical backups / exports and imports in oracle
Logical backup involves reading a set of databse records and writing them into a file. Export utility is used for taking backup and Import utility is used to recover from backup.
Export and Import are used primarily for the following tasks:
- data archival
- upgrading to new releases of Oracle
- backing up Oracle databases
- moving data between Oracle databases
Export and Import allow you to accomplish the following tasks:
- store Oracle data in operating system files independent of any database.
- store definitions of database objects (such as tables, clusters, and indexes) with or without the data.
- store inactive or temporary data.
- back up only tables whose data has changed since the last export, using either an incremental or a cumulative export.
- restore tables that were accidentally dropped, provided they were exported recently.
- restore a database by importing from incremental or cumulative exports.
- selectively back up parts of your database in a way that requires less storage space than a system backup.
- move data from an older to a newer version of Oracle.
- move data between Oracle databases.
- move data between different hardware and operating- system environments.
- move data from one owner to another.
- move data from one tablespace or schema to another.save space or reduce fragmentation in your database.
The values in the parameter files can be as follows:
BUFFER - The parameter BUFFER determines the maximum number of rows in an array fetched by Export.
Buffer_size = rows_in_array * maximum_row_size
If a table having a LONG datatype is Exported , or if BUFFER is specified as zero, only one row at a time is fetched.
COMPRESS - Specifies how Export will manage the initial extent for the table data. This parameter is helpful during database re-organization. Export the objects (especially tables and indexes) with COMPRESS=Y. If your table was spawning 20 Extents of 1M each (which is not desirable, taking into account performance), if you export the table with COMPRESS=Y, the DDL generated will have initial of 20M. Later on when importing the extents will be coalesced. Sometime it is found desirable to export with COMPRESS=N, in situations where you do not have contiguous space on your disk (tablespace), and you do not want your imports to bomb.
CONSISTENT - If massive updates/rollbacks are taking place when you have kicked off the exports, then the exports will not be consistent under normal conditions. If you set CONSISTENT=Y then a rollback segment is designated to backout the effect of any uncommitted transactions. I.e. a value changed to 100 when you have kicked off the exports and before the exports finishes, the changes being undone. The negative effect are exports being slower cause they have to check the consistency by cross referring the rollback segments.
CONSTRAINTS - A flag to indicate whether to export table constraints.
DIRECT - Specifying DIRECT=Y causes export to do Direct Path Exports, bypassing the evaluation layer.
FEEDBACK - Specify that Export should display a progress meter in the for of a dot for x number of rows exported. For example setting FEEDBACK=10, would display a dot each time 10 rows has been exported. This is done for each table exported.
FILE - The name of the export file.
FULL - Specifies whether Export should export the entire database or not. This includes all users, there schemas, data dictionaries.
GRANTS - Specifies whether grants should be exported or not.
INCTYPE - Specifies the type of incremental export. Options are COMPLETE, CUMULATIVE, and INCREMENTAL.
INDEXES - Specifies whether indexes should be exported or not.
LOG - Specifies a file name to receive information and error messages.
OWNER - Specifies a list of usernames whose objects will be exported.
RECORD - A flag to indicate whether to record an incremental or cumulative export in the database.
RECORDLENGTH- Specifies the length, in bytes, of the file record. The RECORDLENGTH parameter is necessary when you must transfer the export file to another operating system that uses a different default value.
ROWS - Specifies whether the rows of table data should be exported or not.
STATISTICS - Specifies the type of database optimizer statistics to generate when the exported data is imported later. Options are ESTIMATE, COMPUTE and NONE
TABLES - Specifies the list of tables to export.
USERID - Specifies the username/password of the user initiating the export
Export Session in Full Database Mode
Only users with the DBA role or the EXP_FULL_DATABASE role can export in full database mode.
]$ exp SYSTEM/password FILE=dba.dmp LOG=dba.log FULL=y
Export Session in User Mode
User mode exports can be used to back up one or more database users
]$ exp scott/tiger FILE=scott.dmp LOG=scott.log OWNER=scott COMPRESS=y
Export Sessions in Table Mode
In table mode, you can export table data or the table definitions.
]$ exp SYSTEM/password FILE=tabledata.dmp LOG=tabledata.log TABLES=(scott.emp,blake.dept)