Forgot your password?    
+ Reply to Thread
Results 1 to 1 of 1

Thread: Logical Backup for Oracle Databse using export and import Utility

  1. #1

    Post Logical Backup for Oracle Databse using export and import Utility

    Export (exp), Import (imp) are Oracle utilities which allow you to write data in an ORACLE-binary format from the database into operating system files and to read data back from those operating system files.

    Imports/Exports Modes:--

    1)Full Export/Import
    2)Tablespace
    3)User and
    4)Table (With partition).

    Export and Import Modes

    The Export and Import utilities support four modes of operation:

    Full: Exports and imports a full database. Only users with the EXP_FULL_DATABASE and IMP_FULL_DATABASE roles can use this mode. Use the FULL parameter to specify this mode.

    Tablespace: Enables a privileged user to move a set of tablespaces from one Oracle database to another. Use the TRANSPORT_TABLESPACE parameter to specify this mode.

    User: Enables you to export and import all objects that belong to you (such as tables, grants, indexes, and procedures). A privileged user importing in user mode can import all objects in the schemas of a specified set of users. Use the OWNER parameter to specify this mode in Export, and use the FROMUSER parameter to specify this mode in Import.

    Table: Enables you to export and import specific tables and partitions. A privileged user can qualify the tables by specifying the schema that contains them. Use the TABLES parameter to specify this mode.



    *** Before executing the exp/imp scripts use the nohup command in $ prompt.***
    $nohup

    If the size of the undo tablespace is small then change the size of the undo tablespace i.e increase the size of the undo tablespace.

    Options For Exp Utility:--

    Exp Option Default Value Description
    ----------------------------------------------------------------------------------------------------------------------
    userid =======>> username/password

    buffer ========>> Specifies the size, in bytes, of the buffer (array) used to insert the data OR Specifies the size, in bytes, of the buffer used to fetch the rows. If 0 is specified, only one row is fetched at a time. This parameter only applies to conventional (non direct) exports.

    "buffer_size = rows_in_array * maximum_row_size"

    compress ======>> Y When “Y”, export will mark the table to be loaded as one extent OR import into one extent (Y).


    consistent =======>> N Specifies the set transaction read only statement for export, ensuring data consistency. This option should be set to “Y” if activity is anticipated while the exp command is executing. If ‘Y’ is set, confirm that there is sufficient undo segment space to avoid the export session getting the ORA-1555 Snapshot too old error. Or cross-table consistency(N).



    constraints ========>> Y Specifies whether table constraints should be exported with table data.



    direct ===========>> N Determines whether to use direct or conventional path export. Direct path exports bypass the SQL command, thereby enhancing performance (Direct=Y).




    File ===============>> The name of the export file. Multiple files can be listed, separated by commas. When export fills the filesize, it will begin writing to the next file in the list.



    Full =========>> N The entire database is exported,if full=y.



    grants ======>>> Y Specifies object grants to export.



    log =========>>> log file of screen output



    owner ==========>>> Only the owner’s objects will be exported.




    parfile ===========>>> parameter filename .



    query ==========>>> Allows a subset of rows from a table to be exported, based on a SQL,where clause.


    rows ==========>>> Y export data rows. If (rows=N),it will export the objects without data.



    statistics ===========>>> ESTIMATE Indicates the level of statistics generated when the data is imported. Other options include COMPUTE and NONE.



    tables =======>>> Indicates that the type of export is tablespace-mode, in which all tables assigned to the listed tablespaces will be exported. This option requires the EXP_FULL_DATABASE role.
    OR
    list of table names.


    You can see all the parameters(options) of the exp utility with the help of this command:--
    $exp help=Y


    For Imp Utility:--

    Options:--

    USERID =========>> username/password


    BUFFER ========>> size of data buffer


    IGNORE =========>> ignore create errors (N),Mostly Ignore=Y is taken.

    If IGNORE=y, object creation errors are not reported. The database object is not replaced. If the object is a table, rows are imported into it. Note that only object creation errors are ignored; all other errors (such as operating system, database, and SQL errors) are reported and processing may stop.

    COMMIT=======> y prevents rollback segments from growing inordinately large and improves the performance of large imports. COMMIT=y is advisable if the table has a uniqueness constraint

    default commit========>> N.



    FILE ==========>> input files (EXPDAT.DMP).


    FILESIZE =============>> maximum size of each dump file.


    FROMUSER ==========>> list of owner usernames.

    TOUSER ============>> list of usernames.


    LOG ========>>> log file of screen output.


    PARFILE =======>>> parameter filename.

    These above are the important parameters of Exp/imp utility options.
    Last edited by vineettyagi; 01-19-2012 at 05:29 PM.

+ Reply to Thread

Similar Threads

  1. Oracle Identity Manager : Use oimbulkload Utility in OIM for loading User data.
    By Hemant in forum Oracle Fusion Middleware 11G - OIM , OAM , ODS , Weblogic , Webgate ..
    Replies: 3
    Last Post: 09-17-2012, 06:28 PM
  2. Data Pump export consistant Backup using FLASHBACK_TIME paramter
    By Amit in forum Backup, Restoration & Recovery, RMAN, Flashback, Datapump, Export, Import
    Replies: 0
    Last Post: 07-15-2012, 08:05 PM
  3. Create Repository for Oracle Fusion Middleware Components using RCU Utility
    By Hemant in forum Oracle Fusion Middleware 11G - OIM , OAM , ODS , Weblogic , Webgate ..
    Replies: 0
    Last Post: 04-18-2012, 01:35 AM
  4. Determine SCNs applied on the LOGICAL STANDBY
    By Hemant in forum Database Scripts
    Replies: 0
    Last Post: 02-03-2012, 06:38 PM
  5. Tablespace transportation using export
    By shoaibansari in forum Backup, Restoration & Recovery, RMAN, Flashback, Datapump, Export, Import
    Replies: 0
    Last Post: 01-09-2012, 05:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics