Backup and recovery of Oracle database pump

Backup and recovery of Oracle database pump

Directory

I. Preparation for database backup and pre-recovery ... 1

1. Authorize and create DIRECTORY objects for database users ... 1 _toc388455817

second, the data pump backup and Recovery ... 1

1. Database backup ... 1

2. Database recovery ... 2

Third, enter the interactive mode ... 4

I. Preparation for database backup and pre-recovery1, authorizing the database user and creatingDIRECTORYObject

Execute the following command on the SQL command line (the user executing the SQL will have directory creation and authorization permissions):

Grant create any directory to username; (Username---User performing backup/restore)

Create directory Res_dir as ' D:\dbres '; (res_dir-the directory name used in the RESTORE command---Place the exported file in this directory)

Create directory Bak_dir as ' C:\dbback '; (bak_dir-the directory name used in the backup command, the command to delete directory drop directory bak_dir;)

second, the data pump backup and Recovery

Data pump includes backup/restore table, Backup/restore user, Backup/restore tablespace, Backup/Restore Database 4 ways to do the following backup/restore commands in the operating system command line to complete the relevant operation

1.Database backup

(1) Backup table

EXPDP database username/password "@ DB instance name" directory= Directory name tables= table 1, table 2 dumpfile= exported DMP file logfile= The log file generated by the DMP file;

Example: EXPDP username/[email protected] tables=tablename1,tablename2 directory=bak_dir dumpfile=bakfile.dmp LOGFILE= BakLog.log;

(2) Backup user (schema, corresponding to user)

EXPDP database username/password "@ Instance name" schemas= User 1, user 2 directory= directory name dumpfile= exported DMP file logfile= The log file generated when the DMP file was exported;

Example: EXPDP username/[email protected] schemas=user01,user02 directory=bak_dir dumpfile=bakfile.dmp LOGFILE=bakLog.log;

(3) Backup table space

EXPDP database username/password "@ Instance name" directory= Directory name tablespaces= tablespace 1, table Space 2 dumpfile= exported DMP file logfile= The log file generated when the DMP file was exported;

Example: EXPDP username/[email protected] Directory=dump_dir

Tablespaces=tablespace01,tablespace02 dumpfile=bakfile.dmp Logfile=baklog.log;

(4) Backing up the database

EXPDP database username/password "@ Instance name" directory= Directory name full=y dumpfile= the exported DMP file logfile= The log file generated when the DMP file was exported;

Example: EXPDP username/password directory=dump_dir full=y dumpfile=bakfile.dmp logfile=baklog.log;

(5) Database backup file is too large, the DMP file will be divided into several small files, to back up the user as an example

EXPDP database user name/password "@ Instance name" schemas= User 1, user 2 directory= directory name Dumpfile=bak _%u. dmp filesize= File Size logfile= Log file generated when the DMP file is exported;

Example: EXPDP username/[email protected] schemas=user01,user02,user03 directory=bak_dir dumpfile=bak _%U.dmp Filesize=5G LOG File= BakLog.log;

2.Database recovery

a , CONTENT include : All, Data_only and the metadata_only . All is the default

When this option is set to all indicates that all contents of the data and table structure are exported;

When this option is set to data_only , it indicates that the data is exported;

When this option is set to metadata_only , it indicates that the exported metadata (table structure);

b , which is used to load all objects of the source scheme into the target schema, and the mapping of the original and target objects . schemas= Original object remap_schema= original object : target Object

C that specifies the action to be performed by the import job when the table already exists , default is Skip,tabble_exists_action={skip | APPEND | TRUNCATE | REPLACE}

When this option is set toSKIPwhen,The import job skips the existing table to process the next object;When set toAPPENDwhen,data is appended,to beTRUNCATEwhen,The import job truncates the table,and append new data to it .;When set toREPLACEwhen,An import job deletes an existing table,reconstruction of Table disease append Data,Note, TRUNCATEoption does not apply to cluster tables andNetwork_linkOptions

(1) Recovery form

IMPDP database user name/password "@ Instance name" tables= Table 1, table 2 "Remap_schema=ori:tar content=data_only | All | Metadata_only table_exists_action= SKIP | APPEND | TRUNCATE | REPLACE" directory= directory name dumpfile= imported DMP file logfile= log file generated when importing DMP file;

Example: IMPDP username/passwd tables= tablename1,tablename2 directory=res_dir dumpfile= resfile.dmp LGFILE=resLog.log;

(2) Restore users

IMPDP database user name/password "@ Instance name" schemas= User 1, User 2 "remap_schema= ori:tar content=data_only | All | Metadata_only table_exists_action=SKIP | APPEND | TRUNCATE | REPLACE"directory= directory name dumpfile= imported DMP file logfile= log file generated when importing DMP file

Example: IMPDP username/[email protected] schemas=user1,user2 directory= res_dir dumpfile= resfile.dmp LOGFILE= resLog.log;

(3) Restore table space

IMPDP database username/password "@ Instance name" "Remap_schema=ori:tar content=data_only | All | Metadata_only table_exists_action= SKIP | APPEND | TRUNCATE | Frplace" directory= directory name tablespaces= tablespace 1, table Space 2 dumpfile= the imported DMP file logfile= The log file generated when the DMP file was imported;

Example: IMPDP username/password directory=res_dir Tablespaces=tablespace1, Tablespace2 dumpfile=resfile.dmp logfile= ResLog.log;;

(4) Recovering a database

IMPDP database username/password "@ Instance name" "Remap_schema=ori:tar content=data_only | All | Metadata_only table_exists_action= SKIP | APPEND | TRUNCATE | Frplace"directory= directory name dumpfile= the imported DMP file Full=y logfile= The log file generated when importing the DMP file;

Example: IMPDP username/password directory=res_dir full=y dumpfile=resfile.dmp logfile=reslog.log;

(5) The database recovers multiple DMP small files to restore the user as an example

IMPDP database user name/password "@ Instance name" schemas= User 1, User 2 "content=data_only | All | Metadata_onlytable_exists_action=SKIP | APPEND | TRUNCATE | Frplace"directory= directory name dumpfile=res _%u. dmp filesize= File size logfile= log file generated when the DMP file was imported;

Example: IMPDP username/[email protected] schemas=user1,user2,user3,user4 directory=res_dir dumpfile=res _%U.dmp Filesize=5G Logfile= ResLog.log;

Third, enter the interactive mode

Execute the following command on the Linux operating system command line:

CTRL + C exits the current state output mode

EXPDP Username/[email protected] Attach=expfull (name of Backup or restore Task) Enter interactive mode

Import>stop_job Stop the current task

Import>start_job Start a stopped task

Import> Status View the state information for the task.

Import>exit_client Exit Export Interactive mode

Backup and recovery of Oracle database pump