Oracle EXPDP scheduled backup all steps in detail [go]

Backup the database with Oracle command, generate DMP file, save the whole set of user and table data information. Easy to restore. Plus widnows Batch processing Bat command, to achieve a daily 0-point backup, now send the experience to everyone!

Tools/Materials

• ORACLE11G 11.2.0 64-bit

Baidu Experience: jingyan.baidu.com

Method/Step

2. 1

   Open cmd in "Run"

   Steps to read
4. 2

   Input command: Sqlplus System/[email protected]

   Where system is the user name of the login database

   123456 is the password for the user name

   ORCL is the database instance name

   Steps to read

6. 3

   if the prompt command is not valid, it is that you have not configured the environment variables for Oracle (as I was plagued by this problem), configure the Oracle environment variables as follows:

   1. Right-click My Computer, select Properties, select Advanced, click Environment Variables 2, select Path, click Edit, and then add a semicolon ":" to the last side of the Variable Value text box for "path". Then add the semicolon followed by the directory path of the Sqlplus file, such as "C:\Program Files\oracle\product\10.2.0\db_1\bin", and then click on the "OK" button. 3. If the variables "Oracle_home" and "Oracle_sid" are not found in the variables column under the System Variables list box, you need to do the following, otherwise you do not need to do so. 4. In "System Variables", click "New" button, enter "Oracle_home" in "Variable name", enter the path of ORACLE's installation directory in the variable value, such as "C:\Program files\oracle\product\10.2.0\db_1", Then click on the "OK" button. 5. Click the "New" button in "System Variable", enter "Oracle_sid" in "Variable name", enter the instance name of the database in the variable value, then click "OK" button.

   Oracle_home:oracle database Software Installation directory Oracle_sid:oracle DB instance name Oracle DB instance name: The identity used to contact the operating system, that is, the exchange between the database and the operating system is represented by the database instance name.

   END

Baidu Experience: jingyan.baidu.com

Step two, EXPDP backup command

2. 1

   To create a backup directory for Oracle:

   Sql>create directory dpdata1 as ' D:\temp\dmp ';

   This is only a directory set in Oracle and does not really create

   Steps to read
4. 2

   You can see this directory through this command.

   Sql>select * from Dba_directories;

   Steps to read
6. 3

   Assign to the owning user right to export the data table

   Sql>grant read,write on the directory dpdata1 to Sshe;

   Steps to read
8. 4

   OK, to a key step, before a lot of detours, but the other is a small problem, in the online bits and a lot of information, and finally found the reason.

   First, to switch back to the command window, do not run under sql>, and then enter the command:

   EXPDP System/[email protected] directory=dpdata1 dumpfile=sshe.dmp logfile=sshe.log schemas=sshe

   Steps to read

10. 5

    There was a mistake. ORA-39002: Invalid operation

    The reason is D:\temp\dmp This directory does not exist , do not think that the creation directory will automatically generate folders, and do not think that after the execution of EXPDP automatically generated folders, you have to manually create this directory folder. Create a new folder and try again, it's a success! Congratulations, you have successfully backed up!

    Steps to read
12. 6

    If you ask, how to restore the exported DMP file back. Well, to do the bottom, in order to save you to find ways to trouble, here I also do a demo (general backup of the data is to wait until the database has a problem to use on).

    First, log in to the database again Sqlplus System/[email protected]

    Then, delete the target database users drop user Sshe cascade;

    Steps to read
14. 7

    Now to create the user again, you can create it with commands, 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.

    steps to read steps to read
16. 8

    Returns a 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!

    steps to read END

Baidu Experience: jingyan.baidu.com

Step 3 Perform the backup task at a scheduled time

1. 1

   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 the following code

   -------------------------------------------------------------------------

   @echo Offrem set Backupfile=f_database_%date:~0,4%-%date:~5,2%-%date:~8,2%.dmprem set logfile=f_database_%date:~0,4 %-%date:~5,2%-%date:~8,2%.logrem Delete 30days filesforfiles/p "D:\temp\dmp"/d-30/c "cmd/c echo deleting @f Ile && del/f @path "CD D:\temp\dmp

   REM Backup Schemasset backupfile=sshe_%date:~0,4%-%date:~5,2%-%date:~8,2%.dmpset logfile=sshe_%date:~0,4%-%date:~ 5,2%-%DATE:~8,2%.LOGEXPDP sshe/sshe directory=dir_dp dumpfile=%backupfile% logfile=%logfile% schemas=sshe parallel=4

   -------------------------------------------------------------------------------

   You can try it out and it will automatically generate a backup file named after the date

   forfiles/p "D:\temp\dmp"/d-30/c "cmd/c echo deleting @file ... && del/f @path" is to delete the backup files after 30 days of this directory, which is quite Useful.

3. 2

   Do a Windows nightly auto Run database backup of bat, my name is Auto.bat edit the following

   ------------------------------------------------------------------------------

   @ECHO offschtasks/create/tn sshe Database scheduled backup /tr "D:\temp\sshe_bak.bat"/sc daily/st 00:00:00/ru "System" Pauseexit

   ------------------------------------------------------------------------------

5. 3

   Double-clicking the Auto.bat will add a timed task to Windows, and the Sshe.bat will be performed automatically every 0:0 P.M. to complete the automatic backup.

7. 4

   Right-click My Computer: Manage-Task Scheduler-Task Scheduler library to see the scheduled tasks you just added

   Steps to read
9. 5

   You can set your computer's clock to 23 points, 59 minutes, 50 seconds, wait 10 seconds, and see if the automatic backup succeeds. If it succeeds, it's done!!

   Original: http://jingyan.baidu.com/article/9113f81b2040862b3314c757.html

Oracle EXPDP scheduled backup all steps in detail [go]