dexp logic export and dimp logic import of Dameng database

To learn more about Damon database, please pay attention to Damon cloud adaptation center.
https://eco.dameng.com/

1, Function introduction

Logical export (dexp) and logical import (dimp) are two command-line tools of DM database, which are used to realize logical backup and logical restore of DM database respectively. Both logical backup and logical restore are completed in the online mode, which refers to the backup and restore during the normal operation of the database server. Dexp and dimp are built-in tools of DM database. As long as DM database is installed, they can be found in the installation directory / dmdbms/bin.

Logical export and logical import database objects are divided into four levels: database level, user level, schema level and table level. The four levels are independent and mutually exclusive and cannot exist at the same time. Four levels of functionality:

  • Database level (FULL): export or import all objects in the entire database.
  • OWNER: export or import all objects owned by one or more users.
  • Schema level (SCHEMAS): export or import all objects in one or more SCHEMAS.
  • TABLES: exports or imports one or more specified TABLES or table partitions.

2, dexp logic export

dexp function

dexp tool can perform logical backup at database level, user level, schema level and table level for local or remote databases. The contents of backup are very flexible. You can choose whether to back up indexes, data rows and permissions, and whether to ignore various constraints (foreign key constraints, non empty constraints, unique constraints, etc.). Before backup, you can also choose to generate log files to record the backup process for viewing.

Help information can be printed through. / dexp help.

Export case

Example 1: full library export

[dmdba@localhost bin]$ ./dexp userid=SYSDBA/SYSDBA@LOCALHOST:5236 FULL=Y FILE=FULL_BAK.DMP LOG=FULL_BAK.LOG DIRECTORY=/dm8/backup

Example 2: single user export

[dmdba@localhost bin]$ ./dexp userid=SYSDBA/SYSDBA@LOCALHOST:5236 OWNER=SYSDBA FILE=OWNER_SYSDBA_BAK.DMP LOG=OWNER_SYSDBA_BAK.LOG DIRECTORY=/dm8/backup

Example 3: multi user export

[dmdba@localhost bin]$ ./dexp userid=SYSDBA/SYSDBA@LOCALHOST:5236 OWNER=SYSDBA,TEST FILE=OWNER_SYSDBA_TEST_BAK.DMP LOG=OWNER_SYSDBA_TEST_BAK.LOG DIRECTORY=/dm8/backup

Example 4: single mode export

[dmdba@localhost bin]$ ./dexp userid=SYSDBA/SYSDBA@LOCALHOST:5236 SCHEMAS=DMHR FILE=SCHEMA_DMHR_BAK.DMP LOG=SCHEMA_DMHR_BAK.LOG DIRECTORY=/dm8/backup

Example 5: multi mode export

[dmdba@localhost bin]$  ./dexp userid=SYSDBA/SYSDBA@LOCALHOST:5236 SCHEMAS=DMHR,SYSDBA FILE=SCHEMA_DMHR_SYSDBA_BAK.DMP LOG=SCHEMA_DMHR_SYSDBA_BAK.LOG DIRECTORY=/dm8/backup

Example 6: single table export

[dmdba@localhost bin]$ ./dexp userid=SYSDBA/SYSDBA@LOCALHOST:5236 TABLES=DMHR.EMPLOYEE FILE=TABLES_EMPLOYEE_BAK.DMP LOG=TABLES_EMPLOYEE_BAK.LOG DIRECTORY=/dm8/backup

Example 7: multi table export

[dmdba@localhost bin]$ ./dexp userid=SYSDBA/SYSDBA@LOCALHOST:5236 TABLES=DMHR.EMPLOYEE,DMHR.DEPARTMENT FILE=TABLES_EMPLOYEE_DEPARTMENT_BAK.DMP LOG=TABLES_EMPLOYEE_DEPARTMENT_BAK.LOG DIRECTORY=/dm8/backup

Example 8: export data through QUERY filtering

[dmdba@localhost bin]$ ./dexp userid=SYSDBA/SYSDBA@LOCALHOST:5236 TABLES=DMHR.EMPLOYEE FILE=QUERY_EMPLOYEE_BAK.DMP LOG=QUERY_EMPLOYEE_BAK.LOG DIRECTORY=/dm8/backup QUERY="WHERE SALARY>10000"

Example 9: export only data objects through ROWS

[dmdba@localhost bin]$ ./dexp userid=SYSDBA/SYSDBA@LOCALHOST:5236 FULL=Y FILE=NOROWS_BAK.DMP LOG=NOROWS_BAK.LOG DIRECTORY=/dm8/backup ROWS=N

Test the difference between dexp and dexpdp

There are two ways to write dexp tool names: dexp and dexpdp. The syntax of the two is exactly the same. The only difference is that the files exported by dexp must be stored on the client and the files exported by dexpdp must be stored on the server.

Test 1: the dexp export directory is the server

E:\dmdba\bin> dexp userid=SYSDBA/SYSDBA@192.168.26.20:5236 FULL=Y FILE=FULL_BAK.DMP LOG=FULL_BAK.LOG DIRECTORY=/dm8/backup


Export failed, prompt nonexistent file path / dm8/backup

Test 2: the dexp export directory is the client

E:\dmdba\bin> dexp userid=SYSDBA/SYSDBA@192.168.26.20:5236 FULL=Y FILE=FULL_BAK.DMP LOG=FULL_BAK.LOG DIRECTORY=E:\dmdba\backup


see file

Export succeeded

Test 3: the dexpdp export directory is the client

E:\dmdba\bin> dexpdp userid=SYSDBA/SYSDBA@192.168.26.20:5236 FULL=Y FILE=FULL_BAK.DMP LOG=FULL_BAK.LOG DIRECTORY=E:\dmdba\backup


Export failed, prompting the nonexistent file path E:\dmdba\backup

Test 4: the dexpdp export directory is the server

E:\dmdba\bin> dexpdp userid=SYSDBA/SYSDBA@192.168.26.20:5236 FULL=Y FILE=DEXPDP_FULL_BAK.DMP LOG=DEXPDP_FULL_BAK.LOG DIRECTORY=/dm8/backup

see file

Export succeeded

conclusion

The files exported by dexp must be stored on the client side and the files exported by dexpdp must be stored on the server side.

3, dimp logical import

dimp function

dimp logic import tool uses the backup file generated by dexp tool to restore the local or remote database online. dimp import is the reverse of dexp export. The restore method can be flexibly selected, such as whether to ignore the creation errors caused by the existence of objects, whether to import constraints, whether to import indexes, whether to compile during import, whether to generate logs, etc.

Print help information through. / dimp help

Import case

Example 1: full database import

[dmdba@localhost bin]$ ./dimp userid=SYSDBA/SYSDBA@LOCALHOST:5236 full=y DIRECTORY=/dm8/backup/ FILE=FULL_BAK.DMP  LOG=IMP_FULL.LOG

Example 2: single user import

[dmdba@localhost bin]$ ./dimp userid=SYSDBA/SYSDBA@LOCALHOST:5236 OWNER=SYSDBA DIRECTORY=/dm8/backup/ FILE=OWNER_SYSDBA_BAK.DMP  LOG=IMP_OWNER.LOG

Example 3: multi user import

[dmdba@localhost bin]$ ./dimp userid=SYSDBA/SYSDBA@LOCALHOST:5236 OWNER=SYSDBA,TEST DIRECTORY=/dm8/backup/ FILE=OWNER_SYSDBA_TEST_BAK.DMP  LOG=IMP_OWNER.LOG

Example 4: single mode import

[dmdba@localhost bin]$ ./dimp userid=SYSDBA/SYSDBA@LOCALHOST:5236 SCHEMAS=DMHR DIRECTORY=/dm8/backup/ FILE=SCHEMA_DMHR_BAK.DMP  LOG=IMP_SCHEMAS.LOG

Example 5: multi mode import

[dmdba@localhost bin]$ ./dimp userid=SYSDBA/SYSDBA@LOCALHOST:5236 SCHEMAS=DMHR,SYSDBA DIRECTORY=/dm8/backup/ FILE=SCHEMA_DMHR_SYSDBA_BAK.DMP  LOG=IMP_SCHEMAS.LOG

Example 6: single table import

[dmdba@localhost bin]$ ./dimp userid=SYSDBA/SYSDBA@LOCALHOST:5236 TABLES=DMHR.EMPLOYEE DIRECTORY=/dm8/backup/ FILE=TABLES_EMPLOYEE_BAK.DMP  LOG=IMP_TABLES.LOG

Example 7: multi table import

[dmdba@localhost bin]$ ./dimp userid=SYSDBA/SYSDBA@LOCALHOST:5236 TABLES=DMHR.EMPLOYEE,DMHR.DEPARTMENT DIRECTORY=/dm8/backup/ FILE=TABLES_EMPLOYEE_DEPARTMENT_BAK.DMP  LOG=IMP_TABLES.LOG

Example 8: do not import data rows

[dmdba@localhost bin]$ ./dimp userid=SYSDBA/SYSDBA@LOCALHOST:5236 full=y DIRECTORY=/dm8/backup/ FILE=FULL_BAK.DMP  LOG=IMP_FULL.LOG ROWS=N

Test the difference between dimp and dimpdp

dimp tool names can be written in two ways: dimp and dimpdp. The syntax of the two is exactly the same. The only difference is that the files imported by dimp must be stored on the client side and the files imported by dimpdp must be stored on the server side.

Test 1: the dimp import file directory is the server

E:\dmdba\bin> ./dimp userid=SYSDBA/SYSDBA@192.168.26.20:5236 full=y DIRECTORY=/dm8/backup/ FILE=FULL_BAK.DMP  LOG=IMP_FULL.LOG


Import failed, prompting the nonexistent file path / dm8/backup/

Test 2: the dimp import file directory is the client

E:\dmdba\bin> dimp userid=SYSDBA/SYSDBA@192.168.26.20:5236 full=y DIRECTORY=E:\dmdba\backup FILE=FULL_BAK.DMP  LOG=IMP_FULL.LOG


Import succeeded

Test 3: dimpdp import file directory is client

E:\dmdba\bin> dimpdp userid=SYSDBA/SYSDBA@192.168.26.20:5236 full=y DIRECTORY=E:\dmdba\backup FILE=FULL_BAK.DMP  LOG=IMP_FULL.LOG


Import failed

Test 4: the dimpdp import file directory is the server

E:\dmdba\bin> dimpdp userid=SYSDBA/SYSDBA@192.168.26.20:5236 full=y DIRECTORY=/dm8/backup FILE=FULL_BAK.DMP  LOG=IMP_FULL.LOG

Import succeeded

conclusion

The files imported by dimp must be stored on the client, and the files imported by dimpdp must be stored on the server.

Tags: Database Big Data PostgreSQL

Posted on Wed, 22 Sep 2021 04:11:28 -0400 by niroshan