Data Pump expdp and impdp backup and restoration/restoration of Oracle Database (detailed process), expdpimpdp

Data Pump expdp and impdp backup and restoration/restoration of Oracle Database (detailed process), expdpimpdp

Note: There are many ways to back up and restore the oracle database. This article only describes how to back up and restore the database using expdp and impdp Data Pump. This method can back up the data in the database table and table.

1. Backup Step 1: log on to oracle using system

Open the doscommand line interface and use the system user to log on to oracle. Format: sqlplus user name/password @ Instance name (or use tools such as plsql and sqlyog to log on ).

C: \ Users \ Administrator> sqlplus system/000000 @ orcl?

Step 2: Create a logical directory

Create a backup logical Directory, which is not a real directory (the content in single quotes is the backup directory)

SQL> create directory data as 'd: \ app \ Administrator \ admin \ orcl \ dpdump ';

Note: If the directory already exists, you need to delete it before creating it (if it does not exist, ignore the following two SQL statements ):??

SQL> drop directory data;

SQL> create directory data as 'd: \ app \ Administrator \ admin \ orcl \ dpdump ';

Step 3: Authorize the user

SQL> grant read, write on directory data to jeecg_test;

Step 4: export the database

Log out of the database and go to the doscommand line interface. Run the following commands to export the database:

Expdp jeecg_test/000000 @ orcl directory = data dumpfile = JEECG_20180226.DMP logfile = jeecg. log schemas = jeecg_test

Explanation:

Directory is the directory created by step 2.

Dumpfile is the exported file name, which is stored in the directory.

Schemas is followed by the user name

2. Preparations before restoring the database

Note: restoration can be performed locally or on another computer.

Step 1: Delete tablespaces and users

You must delete the original tablespace and user before importing the table. If you have not created the tablespace before, skip this step.

Drop tablespace JEECG_TEST including contents and datafiles;

Drop user jeecg_test cascade;

Step 2: Create a tablespace and a user

Create tablespace JEECG_TEST datafile 'd: \ app \ shuhao \ Administrator \ orcl \ dpdump \ JEECG_TEST.DBF 'size 50 m? Autoextend on;

Note: The file name in single quotes is the same as that in the tablespace.

Create user jeecg_test identified by 000000 default tablespace JEECG_TEST temporary tablespace temp;

Step 3: Authorize the user

Grant connect to JEECG_TEST ;?

Grant resource to JEECG_TEST ;?

Grant dba to JEECG_TEST;

Note: dba has the highest level of permissions. You can create databases and tables.

Iii. Restore Step 1: log on to oracle using system

Open the doscommand line interface and use system to log on to oracle. Format: sqlplus username/password @ Instance name (or use tools such as plsql and sqlyog to log on ).

C: \ Users \ Administrator> sqlplus system/000000 @ orcl?

Step 2: Create a logical directory

Create a Restore directory (the content in single quotes is the imported Directory, which is the same as the previously created directory)

SQL> create directory data as 'd: \ app \ Administrator \ admin \ orcl \ dpdump ';

Note: If the directory already exists, you need to delete it before creating it (if it does not exist, ignore the following two SQL statements ):??

SQL> drop directory data;

SQL> create directory data as 'd: \ app \ Administrator \ admin \ orcl \ dpdump ';

Step 3: Authorize the target user

SQL> grant read, write on directory data to jeecg_test;

Step 4: create a real directory to store backup files

Create a real directory at the corresponding location and put the backup file JEECG_20180226.DMP in the real directory.

Step 5: import the backup file

Doscommand line: execute the following command

Impdp jeecg_test/000000 @ orcl directory = data dumpfile = JEECG_20180226.DMP? Logfile = jeecg. log remap_schema = JEECG_TEST: JEECG_TEST

Note: remap_schema = JEECG_TEST: JEECG_TEST indicates to import the data of the JEECG_TEST user on the left to the JEECG_TEST user on the right.

OK. The above is the process of backing up and restoring the oracle database. so easy! Pai_^