Decrypt the Oracle backup tool-exp/imp

Decrypt the Oracle backup tool-expimp whether it is an O & M worker or a database administrator, data backup and restoration is the focus of our daily work, and a reasonable backup policy is formulated, the use of appropriate backup tools is essential for everyone. Today we will introduce Oracle's backup tools exp and imp. ORACLE databases have two backup methods.

Decrypt the Oracle backup tool-exp/imp whether it is an O & M worker or a database administrator, data backup and restoration is the focus of our daily work, and a reasonable backup policy is formulated, the use of appropriate backup tools is essential for everyone. Today we will introduce Oracle's backup tools exp and imp. ORACLE databases have two backup methods.

Decrypt the Oracle backup tool-exp/imp

Whether it is an O & M worker or a database administrator, data backup and restoration are the focus of our daily work. Formulating reasonable backup policies and using appropriate backup tools are essential skills for everyone on the IT team, today, we will introduce Oracle's backup tools exp and imp.

ORACLE databases have two backup methods.

• Category 1: physical backup. This method enables complete database recovery, but the database must run in the recovery mode (the business database runs in the non-recovery mode ), and requires a large number of external storage devices, such as tape libraries;

• Category 2: The backup mode is logical backup, which is used by the business database. This method does not require the database to run in the back-to-block mode. This method not only simplifies backup, but also eliminates the need for external storage devices.

1. Description of exp keywords:

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151111/1210526020-0.jpg "title =" jietru. PNG "alt =" wKioL1V1YF2yLMX9AAS3vlDGmsY914.jpg "/>

1. Export all tables under a user

exp scott/lipengfeifile=scott_all_tables.dmp log=scott_all_tables.log

2. Export some tables under the scott user

(1)expscott/lipengfei tables=\(emp,salgrade\) file=scott_emp_salgrade.dmplog=scott_emp_salgrade.log(2)exp scott/lipengfei tables=empfile=scott_emp.dmp log=scott_emp.log

3. Use of parameter files

The vi/home/oracle/dept.txt file contains the following content: userid = scott/lipengfeilog =/home/oracle/scott_dept.logfile =/home/oracle/scott_dept.dmptables = dept

Reference parameter file:

exp parfile=/home/oracle/dept.txt

4. Export based on conditions

(1) specify conditions in the parameter file

Exp parfile =/home/oracle/emp.txt vi/home/oracle/emp.txt the content is as follows: userid = scott/lipengfei log =/home/oracle/emp. log file =/home/oracle/emp. dmp tables = emp query = 'where sal> 100'

(2) the condition is a number.

exp scott/lipengfei tables=empquery="'where sal >1000'" file=/home/oracle/emp.dmplog=/home/oracle/emp.log

(3) The condition contains a string

exp scott/lipengfei tables=empquery="'where sal >1000 and job=''CLERK'''"file=/home/oracle/emp.dmp log=/home/oracle/emp.log

(4) parameter file with a string in the processing condition

Exp parfile =/home/oracle/emp.txt vi/home/oracle/emp.txt the content is as follows: userid = scott/lipengfeilog =/home/oracle/emp. logfile =/home/oracle/emp. dmptables = empquery = 'where sal> 1000 and job = ''cler '''

5. Export all tables of a user

(1) Create tablespaces, users, and authorizations

create tablespace li datafile'/oracle/app/oradata/ecom/li.dbf' size 30M AUTOEXTEND OFF;create user li identified by li defaulttablespace li;alter user li account unlock;grant connect,resource to li;

(2) create tablespaces, users, and authorizations

create tablespace peng datafile'/oracle/app/oradata/ecom/peng.dbf' size 30M AUTOEXTEND OFF;create user peng identified by peng defaulttablespace peng;alter user peng account unlock;grant connect,resource to peng;

(3) create tablespaces, users, and authorizations

create tablespace fei datafile'/oracle/app/oradata/ecom/fei.dbf' size 30M AUTOEXTEND OFF;create user fei identified by fei defaulttablespace fei;alter user fei account unlock;grant connect,resource to fei;

(4) create a table and initialize data

sqlplus li/licreate table haha(id int);insert into haha values(1);commit;

(5) create a table and initialize data

sqlplus peng/pengcreate table hehe(id int);insert into hehe values(1);commit;

(6) create a table and initialize data

sqlplus fei/feicreate table hihi(id int);insert into hihi values(1);commit;

(7) export all objects of the above three users

exp \'sys/lipengfei as sysdba\'file=/home/oracle/li_peng_fei.dmp log=/home/oracle/li_peng_fei.logowner=\(li,peng,fei\)

6. You do not want to export indexes, export constraints, export authorization, or table-related triggers.

exp scott/lipengfeifile=scott_all_tables.dmp log=scott_all_tables.log indexes=N constraints=Ngrants=N triggers=N

7. The exported file is too large to exceed the file system limit. [fat32 cannot exceed 4 GB for a single file, ntfs cannot exceed 2 TB for a single file, and ext3 is ideal for a single file cannot exceed 2 TB]

exp scott/lipengfei filesize=500M  file=scott_all_tables1.dmpscott_all_tables2.dmp log=scott_all_tables.log

• If The filesize parameter is specified, the file parameter must be modified accordingly. Exp may generate multiple dmp files during export. Therefore, you must name each file in the file parameter (multiple names are separated by commas)

• If the file name specified by the file parameter is more than the actually generated file, the specified file will not be generated.

• If the file name specified by the file parameter is less than the actually generated file,

• After the specified file is used up, a new file name is displayed.

• If no one is there, the entire export task will be stopped here.

Then you may ask, how do you know how much space the data to be exported occupies?

select sum(bytes)/1024/1024"total(M)" from user_segments;

Ii. Description of imp keywords:

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151111/1210525N9-1.jpg "title =" 123.png" alt = "wKiom1V1lobAcXRyAAQHmj3HGPI627.jpg"/>

1. Import Data

(1) Export data by user

exp li/li file=li_all_tables.dmplog=li_all_tables.log

(2) simulated data loss

sqlplus li/liSQL> drop table haha;

(3) Restore backup data

imp li/li file=li_all_tables.dmplog=li_all_tables.log

2. Import a specified table to another user

(1) import the backup set under the li user to the peng user

imp peng/peng fromuser=li touser=pengfile=li_all_tables.dmp log=li_to_peng_all_tables.log

(2) Log On As A peng user to verify the data

sqlplus peng/pengSQL> select  tname from tab;

The operation above seems successful? Others do not. Although the data has been imported successfully, it is not a rigorous method and may accidentally bury a ray in the database.

SQL> show userSQL> select username,default_tablespacefrom user_users;SQL> select table_name,tablespace_namefrom user_tables;

Strange? Although the default tablespace of the peng user is peng, the newly imported haha table is stored in the li tablespace. Solution:

(3) Table space permission Control

sqlplus / as sysdbaalter user peng quota unlimited on peng;revoke unlimited tablespace from peng;

(4) Clear the tables and data that the peng user just imported

sqlplus peng/pengdrop table haha;

(5) import the backup set under the li user to the peng user again

imp peng/peng fromuser=li touser=pengfile=li_all_tables.dmp log=li_to_peng_all_tables.log ignore=y

(6) Log On As A peng user to verify the data

sqlplus peng/pengSQL> select table_name,tablespace_namefrom user_tables;

The ignore parameter is used above. If the object to be imported already exists, an error is returned by default.

Ignore = N [Default]. The error object is skipped, and imp continues subsequent operations.

Ignore = Y, automatically ignore the fact that the object already exists, continue to import data, duplicate data will appear, may be removed manually.

3. Import the table structure to the specified user

(1) log on to the peng user and delete the specified table and data.

sqlplus peng/pengSQL> drop table haha;

(2) Use the backup set generated above to restore only the table structure

imp peng/peng fromuser=li touser=pengfile=li_all_tables.dmp log=li_to_peng_all_tables.log ignore=y rows=N

(3) Log On As A peng user to verify whether only the table structure is restored and no data exists.

sqlplus peng/pengSQL> select tname from tab;SQL> select * from haha;

A long time ago, Oracle began to provide a tool for extracting table, schema, or whole database definitions and importing them to other modes or data: exp/imp

At that time, the database size was very small (hundreds of MB, even a large database), and the database requirements were not that high, unlike today, 7x24 high concurrency, high availability, and in some fields, exp/imp is also regarded as a backup recovery tool and continues to this day. If you use exp to back up dozens, hundreds, or even larger databases, and use this method as the backup policy for the production database, this is too unreasonable.