Oracle Backup and Recovery

When you upgrade a production server, or replace a database server, and build a test environment, you need to back up the production database and possible future restores.

1.EXPDP Export

EXPDP DMS/123456 version=10.2. 0.1. 0 Directory=data_pump_dir dumpfile=10g_20180623_dms. DMP logfile=to_20180623. log schemas=Sp_dms

dms/123456 is the user name password with export permission, version is the exported edition, the default is the server version, mainly for compatibility with the import of low-version database servers

Directory is the catalog of exported files and can be queried by the following statements

Select *  from Dba_directories;

DumpFile is the export file name, logfile is the log file name, schemas is which user data is exported
Execution effect:

2.IMPDP Import

IMPDP dms/123456 directory=data_pump_dir dumpfile= 10g_20180623.dmp  remap_schema=sp_dms:sp_next remap_ Tablespace=users:bymis

Remap_schema is the original database user: The target database user Remap_tablespace is the original tablespace: the target table space. When two databases use a different database user, table space, these two parameters are enabled and the system is considered the same by default.

3.EXP Export

Backups are slow compared to EXPDP exports, but there is an advantage that the client can execute commands without requiring the server host to execute commands.

Exp Sp_test/[email protected] file=d:test.dmp log=d:exp.log owner=sp_test

Sp_test/[email protected] is the database user name password and network configuration database service connection which database user data the owner is exporting

4.imp Import

Files exported with exp, used in conjunction with

Imp sp_test/file=log=d:test.txt Ignore=y  fromuser= ...  Touser=...  

Ignore ignores the error and imports the data into the existing table, otherwise skips the table without importing any data. Fromuser is the source database user Touser is the target database user

5.plsql Tool Backup

User Login---"Tool---" Export user objects, in the popup page, options database user, output file directory, click the Export button, will export the user's table structure, sequence, functions and other objects created.

User Login---"Tool---" Export table, on the SQL Insert tab, select all the tables, click the Export button, the tool will export the table record data. Exporting large pieces of table data, such as CLOB, is not supported.

　

Oracle Backup and Recovery