Oracle10g Data Pump EXPDP and IMPDP backup and recovery data

Oracle10g Data Pump EXPDP and IMPDP backup and recovery data

I. Preparations before Database Backup
Create a backup DIRECTORY and authorize it to the user

Steps:
1. log on to sqlplus
Account name: ptemp password: 000000 host string: EMPDB
SqlPlus execution statement: conn ptemp/000000 @ EMPDB as sysdba

Message: logon successful

2. Create a DIRECTORY Backup DIRECTORY
SqlPlus execution statement: create DIRECTORY bakupKF_dir as 'd: \ bakupKF'

Message: the directory is successfully created.

3. Authorize the DIRECTORY to the user
SqlPlus execution statement: grant read, write on directory bakupKF_dir TO ptemp;

Prompt: Authorization successful

Ii. EXPDP backup data (including table structure)

Click: menu-run-cmd
Run the following statement:
1. Back up the table and table definitions of the entire database
Expdp ptemp/powersmart @ empdbtest directory = bakupCS_dir DUMPFILE = ptemp830.dmp

Note: The table definitions and table data of the entire database are backed up. The default value is CONTENT = ALL. Therefore, the definition and table data of the entire database are exported.

2. Back up the data of the entire database table (no table structure definition)
Expdp ptemp/000000 @ empdb directory = bakupKF_dir DUMPFILE = ptemp813data. dmp CONTENT = DATA_ONLY

Note: When CONTENT is set to ALL (default), only the object data is exported when the object definition and ALL its data are exported. If it is DATA_ONLY, only the object data is exported. If it is METADATA_ONLY, only the object definition is exported.

3. Back up data and table definitions of a specified table (multiple tables can be selected)
Expdp ptemp/000000 @ empdb directory = bakupKF_dir DUMPFILE = ptemp813tab. dmp TABLES = TEST_LR, TEST_LR2, TEST_LR3 ......

Note: TABLES = TEST_LR, TEST_LR2, and TEST_LR3 can be connected ",".

Iii. Restore data using IMPDP

1. Restore the table and table definitions of the entire database
Impdp ptemp/000000 @ empdb directory = bakupKF_dir DUMPFILE = ptemp813.dmp TABLE_EXISTS_ACTION = REPLACE

Note: TABBLE_EXISTS_ACTION = {SKIP (default) | APPEND | TRUNCATE | REPLACE}
When this option is set to SKIP, the import job skips existing tables and processes the next object. When it is set to APPEND, data is appended. When it is set to TRUNCATE, the import job truncates the table, then append new data to it. When set to REPLACE, the import job will delete the existing table, recreate the table, and append data. Note that the TRUNCATE option is not applicable to the cluster Table and NETWORK_LINK options.

2. Restore data only (Table truncation restoration)
Impdp ptemp/000000 @ empdb directory = bakupKF_dir DUMPFILE = ptemp813data. dmp CONTENT = DATA_ONLY TABLE_EXISTS_ACTION = REPLACE

Note: When CONTENT = DATA_ONLY is restored, the import can be successful only when the data in the table is empty. Otherwise, data restoration will fail due to "Violation of unique constraints/primary keys. To solve this problem, you can back up the database table data and table definition in 1 mode and recreate the existing table!

3. truncates the table to restore data.
Impdp ptemp/000000 @ empdb directory = bakupKF_dir DUMPFILE = ptemp813data. dmp TABLE_EXISTS_ACTION = TRUNCATE

Note: When TABLE_EXISTS_ACTION = TRUNCATE is restored, the table definition and table data are separated, that is, only the data is restored and the table definition is not restored. However, when the Master/Slave table is restored, the data in the master table fails to be restored due to the primary key relationship, but the data in the slave table is successful.

The solution for restoring full database data can be solved by "1. Restoring the entire database table and table definition" in this article.

4. Restore a specified table
Impdp ptemp/000000 @ empdb directory = bakupKF_dir DUMPFILE = ptemp813tab. dmp TABLES = TEST_LR, TEST_LR2, TEST_LR3 TABLE_EXISTS_ACTION = REPLACE

Iv. database full-database data and table definition practices

1. Business Scenario Description
A company's Development Department currently has two oracle databases, namely the development database EMPDB and the Test Database EMPDBTEST. Now we need to synchronize the data in the test database to the development database, which must be implemented using the oracle Data Pump.

2. database instance information
2.1 EMPDB instance logon information


2.2 EMPDBTEST instance logon information


3. Implementation steps

3.1 create a backup directory
The purpose of creating a backup directory is to tell the database instance which directory is the exclusive directory of the instance and grant the read and write permissions to the database instance user. Here, creating a directory is equivalent to legalizing a directory, that is, registering a directory. Therefore, creating a backup directory is a crucial step.

The Directory creation process is as follows:
① Create a directory for the database instance EMPDBTEST, as shown in:



The execution statement is as follows (in order ):
I conn ptemp/powersmart @ EMPDBTEST as sysdba (Log On as an administrator)
Prompt: connected
II create DIRECTORY bakupCS_dir as 'd: \ ptemp_bakup_CS '(create DIRECTORY)
Prompt: directory created successfully
Note: The d: \ ptemp_bakup_CS directory must be in drive d. If not, create a directory.
III grant read, write on directory bakupCS_dir to ptemp
Prompt: Authorization successful
② Create a directory for the database instance EMPDBTEST. The process is as follows.
The execution statement is as follows (in order ):
I conn ptemp/000000 @ EMPDB as sysdba
II create DIRECTORY bakupKF_dir as 'd: \ ptemp_bakup_KF'
III grant read, write on directory bakupKF_dir to ptemp

After the preceding operations are completed, the D disk should have the following two folders:


3.2 back up test database data
Back up the full-database data and table definitions of empdbtest to the ptemp_bakup_CS folder on drive D. the backup file name is PTEMP830.DMP.
Procedure: Start-run-cmd
Then run the following statement in cmd:
Expdp ptemp/powersmart @ empdbtest directory = bakupCS_dir DUMPFILE = ptemp830.dmp

Execution Process

Exported successfully. The PTEMP830.DMP file will be generated in the ptemp_bakup_CS folder.

3.3 synchronize data to the Development Database

Copy the PTEMP830.DMP file under the ptemp_bakup_CS folder to the ptemp_bakup_KF folder to synchronize data.
The execution statement is as follows:
Impdp ptemp/000000 @ empdb directory = bakupKF_dir DUMPFILE = ptemp830.dmp TABLE_EXISTS_ACTION = REPLACE

The execution process is as follows:


The import process takes a lot of time, about 5-10 minutes. If the computer screen remains static, it is normal. Please wait!