Oracle Data Pump database export import and scheduled backup

One, database backup

1. Enter the database

Sqlplus system/Password

sqlplus username/[email protected] assysdba

2. Create directory D:\temp\dmp (DPDATA1 database name)

Create directory dpdata1 as ' d:\temp\dmp ';

(Delete directories drop directory dpdata1;)

3. View the Management Manager directory (and see if the operating system exists, because Oracle does not care if the directory exists, and if it does not exist, an error occurs)

SELECT * from Dba_directories;

4. Give Sshe users permission to operate in the specified directory, preferably given by administrators such as system.

Grant Read,write on directory dpdata1 to Sshe;

5. Create this directory on localhost d:\temp\dump

6. EXPDP system/password directory=dpdata1 dumpfile=sshe.dmp logfile=sshe.log schemas=sshe

Second, database restore

1. Login Database

Sqlplus system/Password @ dpdata1

2. Delete the target database user

Drop user system cascade;

3. Now to create the user again, you can create it by command, or you can create it in Plsql, which is created in Plsql.

Log in to the database with system, then right-users--the new

Then fill in your account name, must be the same as the user at the time of backup, and assign the appropriate permissions

Finally click on the app to complete the user creation.

4. Return to DOS command window, no longer sql>

Enter import command: IMPDP system/[email protected] directory=dpdata1 dumpfile=sshe. DMP Logfile=sshe.log Schemas=sshe

Import Successful!

three, scheduled to perform backup tasks

1. Make a batch that is named by month and day.
2. Make a batch that is named by month and day. Create a new file in the D:\temp\ directory, named. bat, my name is Sshe.bat, and edit it as follows:

Set Backupfile=imbrv6r3_%date:~0,4%-%date:~5,2%-%date:~8,2%.dmp

Set logfile= Imbrv6r3%date:~0,4%-%date:~5,2%-%date:~8,2%.logrem Delete 3days files

forfiles/p "E:\temp\dmp"/d-3/C "cmd/c echo deleting @file ... && del/f @path" CD E:\temp\dmp backup Schemasse T backupfile=%date:~0,4%-%date:~5,2%-%date:~8,2%.dmp

Set Logfile=%date:~0,4%-%date:~5,2%-%date:~8,2%.log

EXPDP system/cape directory=imbrv6r3 dumpfile=%backupfile% logfile=%logfile% schemas=system parallel=4

(Note: The yellow part is the variable imbrv6r3= database name, e:\temp\dmp= virtual directory path, system/cape= username/password)

1. Create a new task in the Task Scheduler Select a fixed time to run Sshe.bat
2. Right-click My Computer: Manage-Task Scheduler-Task Scheduler library to see the scheduled tasks you just added

Oracle Data Pump database export import and scheduled backup