Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to use the logical backup tool of Oracle database". In the daily operation, I believe that many people have doubts about how to use the logical backup tool of Oracle database. The editor has consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "how to use the logical backup tool of Oracle database". Next, please follow the editor to study!

Oracle database can export and import data through the data pump expdp tool. Before introducing the use of the tool, it is important to note that when the newly created table after Oracle11g does not insert data, it will not allocate segment, that is, it will not allocate space, and when using expdp export, such empty tables will not be exported, resulting in incomplete database structure (this case does not include tables that have data that was later emptied). Therefore, in order not to miss the data, process the empty table once (manually allocate space) when exporting:

1. If there is no data when querying those tables through user_tables (Note: to improve accuracy, you can first perform a table analysis analysis table on all tables under the user), and then directly generate a statement to manually assign segment through the following statement:

Select 'alter table' | | table_name | | 'allocate extent;' from user_tables where num_rows=0

PS: of course, the above statement will also query out the tables that were emptied after having the data, but in order to eliminate the trouble one by one, the segment will be re-allocated directly.

two。 You can execute the generated statements in batches.

1. After the above processing is completed, let's talk about the use of the expdp export tool:

The files exported by the 1.expdp tool can only be stored locally in the database server, and cannot be exported remotely to other servers (exp tool exports remotely to the database server locally, and the exported files can only be exported and stored locally to the database server, and then can be transferred to the backup server that you want to store.

The expdp tool needs to create a directory in the database to map to the specified system directory where the dmp files are stored:

# create a directory where dmp files are stored in the system (of course, the Oracle user must have permission to read and write): [oracle@local ~] $mkdir / data/dmpfile# logs in to the database [oracle@local ~] $sqlplus / as sysdba# using the sys user (or a user with DBA permission) to check whether the current instance is the instance name SQL > select instance_name from v$instance to be exported by the user. # create the directory name as expdp, and the mapping directory is the directory / data/dmpfile SQL > create directory expdp as'/ data/dmpfile'; # you just created. Grant read and write permission to the directory to the user or directly to public, which means that all users can use it, and different users do not have to authorize it each time. SQL > Grant read,write on directory expdp to PUBLIC;# launch database SQL > exit# export specified user data for backup [oracle@local ~] $expdp username/userpassword directory=expdp dumpfile=orcl_full20200216.dmp loggile=orcl_full20200216.log

2.impdp is the import tool of the data pump. To import dmp files, you need to put the dmp files on the database server that performs the import operation. The directory where dmp is stored is the directory where the directory mapping is imported. Of course, the system Oracle users must have read and write permissions:

# create a dmp file to store the system directory on the import database server (of course, the Oracle user must have permission to read and write): [oracle@localhost ~] $mkdir / data/dmpfile2 [oracle@localhost ~] $mv orcl_full20200216.dmp / data/dmpfile2# use the sys user (or a user with DBA permission) to log in to the database [oracle@localhost ~] $sqlplus / as sysdba# to check whether the current instance is to be performed. Imported database SQL > select instance_name from v$instance # create the directory name as expdp, and the mapping directory is the directory / data/dmpfile2 SQL > create directory expdp2 as'/ data/dmpfile2'; # you just created. Grant read and write permission to the directory to the user or directly to public, which means that all users can use it, and different users do not have to authorize it each time. SQL > Grant read,write on directory expdp2 to PUBLIC;# launch database SQL > exit# export specified user data for backup [oracle@localhost ~] $impdp username/userpassword directory=expdp2 dumpfile=orcl_full20200216.dmp loggile=orcl_full20200216.log

Matters needing attention in the use of data pump tools

1.expdp data pump tools are upwards compatible, such as dmp files exported by Oracle 11G using expdp, which can be directly imported into Oracle 12C database, but dmp files exported by Oracle 12C cannot be directly imported into Oracle 11G. A parameter is added to the export statement when expdp is exported, for example, Oracle 12C exports the dmp file into Oracle 11G database, and adds VERSION=11.1.0.0.0.: after the export statement

[oracle@local ~] $expdp username/userpassword directory=expdp dumpfile=orcl_full20200216.dmp loggile=orcl_full20200216.log VERSION=11.1.0.0.0

two。 The character set should be consistent, and the database imported by the database character set should be consistent with the exported database to ensure that the data is imported normally.

The method of querying database character set:

Select userenv ('language') from dual

3. Oracle system users must have read and write permissions for the directories created before export and import to store dmp files. In order to avoid such errors, remember that all operations are performed under oracle system users. If root users are used to create directories to store dmp files, be sure to modify the authorization to belong to the master group after creation. For example:

[root@local ~] # mkdir / data/dmpfile [root@local ~] # chown oracle:oinstall / data [root@local ~] # cd / data [root@local ~] # chown oracle:oinstall / dmpfile

Note: do not easily use chown-R / data/dmpfile for authorization. If other services are deployed in the / data directory, this method will cause problems with other deployment services under / data. In order to prevent this error, step-by-step authorization is safer.

4. When using expdp for data migration, the imported database must be consistent to create the same tablespace as the exported database, especially if there are multiple data files in a tablespace, so as to prevent the data from being unable to be stored in the database during import.

5. When exporting part of the data from a library with a large amount of data, although the amount of exported data is very small, it is often reported that there is insufficient table space during import because the exported data contains metadata in the table space (such as the size of the space occupied by the table).

You need to add the compress parameter to the export.

6. Special export method:

(1) Export according to the query statement:

[oracle@local ~] $expdp username/userpassword directory=expdp dumpfile=orcl_full20200216.dmp loggile=orcl_full20200216.log QUERY=employees: "WHERE department_id > 10"

(2) specify a table name or specify multiple table exports:

# Export all tables beginning with sys [oracle@local ~] $expdp username/userpassword directory=expdp dumpfile=orcl_full20200216.dmp loggile=orcl_full20200216.log tables= (username.sys%) # Export specified multiple tables [oracle@local ~] $expdp username/userpassword directory=expdp dumpfile=orcl_full20200216.dmp loggile=orcl_full20200216.log tables= (BTRULES, CDRULES, CXRULES, DTBTRULES, HLXRULES)

(3) specify the dmp size to export:

# each dmp size is 2G [oracle@local ~] $expdp username/userpassword directory=expdp dumpfile=full_xzxt_%U.dmp filesize=2G loggile=orcl_full20200216.log

(4) only the table structure is exported

[oracle@local ~] $expdp username/userpassword directory=expdp dumpfile=orcl_full20200216.dmp loggile=orcl_full20200216.log content=metadata_only

(5) only the table structure is exported

[oracle@local ~] $expdp username/userpassword directory=expdp dumpfile=orcl_full20200216.dmp loggile=orcl_full20200216.log content=data_only

(6) Import different users, export users as scott, and import users as zhang:

[oracle@local ~] $impdp zhang/zhang directory=expdp dumpfile=scott.dmp logfile=scott.log schemas=scott remap_schema=scott:zhang at this point, the study on "how to use the logical backup tool of Oracle database" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.