Backup series--exp/imp Basic usage Guide

Concept:

1. For backup recovery and data migration, you can implement full-library, user-level, table-level data backup and recovery.

2. For data in the G-class or within the G-level, emphasizing high availability, can tolerate a small number of data loss of the database system, EXP/IMP is the logical backup method can be tried

3. In the client program, but can be used on both the client and the server.

Export: Exporting data from the database to the dump file

Import: Importing data from the dump file into the database

Dump file: Dump file in binary format, not editable manually, can be used across os/across versions

Usage scenarios:

Transferring data between databases

Backup and recovery of databases

three different modes

1: Table mode, will specify the table data exp/imp.

Export:
Export one or more tables:
$ exp user/pwd file=/dir/xxx.dmp Log=xxx.log tables=table1,table2
Export some data from a table
$ exp user/pwd file=/dir/xxx.dmp log=xxx.log tables=table1 query= "where col1=\" ... \
and col2 \<...\ "
Import:
Import one or several tables
$ imp user/pwd file=/dir/xxx.dmp log=xxx.log tables=table1,table2fromuser=dbuser
Touser=dbuser2 commit=y Ignore=y

 

Detailed process:

Create a table space

Sys@orcl>createtablespace Test_tbs

2 datafile '/home/oracle/test/test_data02.dbf '

3 size5m

4 autoextend on MaxSize 512M;

Create two users

Sys@orcl>createuser testuser identified by testuser default Tablespace Test_tbs;

User created.

Sys@orcl>createuser TestUser2 identified by testuser2 default Tablespace Test_tbs;

User created.

Delegating to new users

Sys@orcl>grant CREATE session,create TABLE to TestUser;

Grant succeeded.

Sys@orcl>grant CREATE session,create TABLE to TestUser2;

Grant succeeded.

Give users the quotas they can use in the table space

Sys@orcl>alteruser TestUser1 quota 50M on Test_tbs;

User altered.

Sys@orcl>alteruser TestUser2 quota 50M on Test_tbs;

User altered.

Login User TestUser

sys@orcl>conntestuser/testuser;

Connected.

Create a table, insert data

Testuser@orcl>create table Testuser.exptest (a int);

Table created.

Testuser@orcl>insertinto testuser.exptest values (1);

1 row created.

testuser@orcl>commit;

Commit complete.

testuser@orcl>select* from Testuser.exptest;

A

----------

1

Login user TestUser2, make sure no exptest table

testuser2@orcl>select* from Testuser2.exptest;

SELECT * Fromtestuser2.exptest

*

ERROR at line 1:

Ora-00942:tableor view does not exist

Switch to the Linux terminal interface and login to the Oracle user

[root@11g_s ~] #su –oracle

[oracle@11g_s~]$ exp TESTUSER/TESTUSER@ORCL tables=exptest file ~/test/exp/tb_exp.dmplog=~/test/exp/testuserexp.log

Export:release11.2.0.1.0-production on Tue Feb 23 18:53:25 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to:oracle Database 11g Enterprise Edition release 11.2.0.1.0-production

With Thepartitioning, OLAP, Data Mining and real application testing options

Export done Inus7ascii Character set and UTF8 NCHAR character set

Server useswe8mswin1252 Character Set (possible charset conversion)

About to exportspecified tables via conventional Path ...

. . Exportingtable exptest 1 rows exported

Exp-00011:testuser. FILE does not exist

Exp-00009:noprivilege to Export/home/oracle/test/exp/tb_exp ' s table DMP

Exportterminated successfully with warnings.

[oracle@11g_s~]$ exp TESTUSER/TESTUSER@ORCL tables=exptest file=~/test/exp/tb_exp.dmplog=~/test/exp/ TestuserExp.log

Export:release11.2.0.1.0-production on Tue Feb 23 18:54:20 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to:oracle Database 11g Enterprise Edition release 11.2.0.1.0-production

With Thepartitioning, OLAP, Data Mining and real application testing options

Export done Inus7ascii Character set and UTF8 NCHAR character set

Server useswe8mswin1252 Character Set (possible charset conversion)

About to exportspecified tables via conventional Path ...

. . Exportingtable exptest 1 rows exported

Export terminated successfully without warnings.

[oracle@11g_s~]$ imp testuser2/testuser2@orcl fromuser=testuser touser=testuser2file=~/test/exp/tb_exp.dmp log=~/ Test/exp/tb_imp.log

Import:release11.2.0.1.0-production on Tue Feb 23 18:58:36 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to:oracle Database 11g Enterprise Edition release 11.2.0.1.0-production

With Thepartitioning, OLAP, Data Mining and real application testing options

Export filecreated by export:v11.02.00 via conventional path

Warning:theobjects were exported by TESTUSER

Import done Inus7ascii character set and UTF8 NCHAR character set

Import serveruses we8mswin1252 Character set (possible charset conversion)

. Importingtestuser ' s objects into TESTUSER2

. . Importingtable "Exptest" 1rows imported

Import terminated successfully without warnings.

Switch to Sqlplus interface, login TestUser2 user to see if the import was successful

testuser2@orcl>select* from Testuser2.exptest;

A

----------

1

Exp/imp success of tables between different users

2: User mode, will specify the user's all objects and data export/import

 

Export:

$exp user/pwd file=/dir/xxx.dmp log=xxx.log owner= (XX,YY) # Database account number to export data

Export Data Objects only, do not export data (rows=n)

$exp user/pwd file=/dir/xxx.dmp log=xxx.log owner=user rows=n #是否导出行数据, the default y,n is no

Import:

$imp user/pwd file=/dir/xxx.dmp log=xxx.log fromuser=dbuser touser=dbuser2

Commit=yignore=y

Detailed process:

[oracle@11g_s~]$ exp Bankuser/bankuser file=~/bank/exp/bank_user.dmp log=~/bank/exp/

Bank_user.logowner=bankuser

Export:release11.2.0.1.0-production on Wed Feb 24 04:29:34 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to:oracle Database 11g Enterprise Edition release 11.2.0.1.0-production

With Thepartitioning, OLAP, Data Mining and real application testing options

Export done inwe8mswin1252 Character set and UTF8 NCHAR character set

. Exportingpre-schema Procedural objects and actions

. exportingforeign function library names for user Bankuser

. Exportingpublic type synonyms

. Exportingprivate type synonyms

. Exportingobject type definitions for user Bankuser

About to Exportbankuser ' s objects ...

. Exportingdatabase Links

. Exportingsequence numbers

. Exportingcluster definitions

. About Toexport Bankuser ' s tables via conventional Path ...

. . Exportingtable exptest 2 rows exported

. Exportingsynonyms

. Exportingviews

. exportingstored procedures

. Exportingoperators

. exportingreferential integrity constraints

. Exportingtriggers

. Exportingindextypes

. Exportingbitmap, functional and extensible indexes

. Exportingposttables actions

. exportingmaterialized views

. Exportingsnapshot logs

. Exporting jobqueues

. Exportingrefresh Groups and children

. Exportingdimensions

. Exportingpost-schema Procedural objects and actions

. Exportingstatistics

Export terminated successfully without warnings.

[oracle@11g_s~]$ imp bankuser2/bankuser2 file=~/bank/exp/bank_user.dmp log=~/bank/exp/

Bank_user.logfromuser=bankuser Touser=bankuser2 commit=y Ignore=y

Import:release11.2.0.1.0-production on Wed Feb 24 04:32:34 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to:oracle Database 11g Enterprise Edition release 11.2.0.1.0-production

With Thepartitioning, OLAP, Data Mining and real application testing options

Export filecreated by export:v11.02.00 via conventional path

Warning:theobjects were exported by Bankuser

Import done inwe8mswin1252 character set and UTF8 NCHAR character set

. . Importingtable "Exptest" 2rows imported

Import terminated successfully without warnings.

3. Whole library to export/import all the objects in the database

Export:

EXPUSERID=SYS/SYS@ORCL buffer=8192 file=x:\*.dmp log=x:\*.log full=y grants=y

The most import commands in actual work

IMPUSERID=SYS/SYS@ORCL buffer=81920 file=x:\*.dmp log=x:\*.log full=y grants=y

Ignore explains:

When a table (test) already exists in the database to be imported, if the table has no uniqueness constraints, the parameter ignore=y when imported, the data is completely imported into the table without an error.

When a table already has a uniqueness constraint, especially a PRIMARY key constraint, only records that do not exist in the primary key are imported at the time of the import. There will be a warning during the import process.

The operation is similar to other modes