Windows Scheduled tasks + batch files for scheduled backup and recovery of Oracle databases

1. Backup:

Ps:2014-1-15

If the exported DMP data file is not large, it is exported directly every day, do not save only seven days of data. Then, the file is packaged by WinRAR, and I find that the DMP files are still very compressed.

Then you need to consider adding a date to the end of the exported DMP file. The bat script has a function of date and time, and we can get the desired date by the following function. If the requirement date is end with "-", the format of the date needs to be yyyy-m-d, not yyyy/m/d.

C:\users\user>echo%date:~0,10%-%time:~0,2%-%time:~3,2%-%time:~6,2%2014-01-15-15-52-43

After we get the date format, our export script can be modified to

Exp xuwei/[email protected] FILE=F:\MYDATA_MYTEST\ORADATA_%DATE:~0,10%-%TIME:~0,2%-%TIME:~3,2%-%TIME:~6,2%.DMP log =f:\mydata_mytest\oradata_%date:~0,10%-%time:~0,2%-%time:~3,2%-%time:~6,2%.log

You then create a scheduled task that executes once a day.

Ps:2014-4-3

Today found that the above script can not be executed, this is because%time% has a problem, the hours in the time, less than two will not automatically fill 0 (the system is less than two bits, is the fill of the space, so we judge the space-time lattice, with the use of the replacement of the three), specific reference: http://blog.csdn.net/ bagboy_taobao_com/article/details/7198578. Modify the following script as follows:

@echo Offset nls_lang= American_america. Al32utf8set hour=%time:~,2%if "%time:~,1%" = = "" Set Hour=0%time:~1,1%set temp=%date:~0,4%-%date:~5,2%-%date:~8,2%-% Hour%-%time:~3,2%-%time:~6,2%exp Baol4dev_cgn/[email protected] File=d:\db_backup\daily_backup\oradata_%temp%.dmp Log=d:\db_backup\daily_backup\oradata_%temp%.logpause

Ps:2014-06-19 for the time format

Http://www.sql9.com/?id=86

http://bbs.csdn.net/topics/110143577

The date format above is not optimized because the output of%date:~0,10% may be 2014/06/19 or 2014-06-19, depending on the system's setting for the date format. Therefore, the data cannot be taken directly from all fields, but the date is taken out separately.

C:\users\wei.xu>echo%date:~0,4%%date:~5,2%%date:~8,2%%time:~0,2%%time:~3,2%%time:~6,2%20140619170757

1.1. Create a database_exp.bat batch file

First create a seven-day DMP and log empty file, and then create a batch file to export the database, the script can back up the last seven days of the database, Database_ext.bat, as shown in the following:

Del F:\mydata_mytest\oradata7.log











ren f:\mydata_mytest\oradata.log oradata2.log
Exp Xuwei/[email protected] file=f:\mydata_mytest\oradata.dmp Log=f:\mydata_mytest\oradata.log

(ps:2013-8-8)

The Ren command is the first one with a path, the second parameter is without a path, and is indicated in the directory of the first file.

(ps:2011-12-29)

Database_exp.bat parsing: The above batch statement is actually very simple, is to let seven DMP files to form a 7-length queue, and then each time the database backup to delete the queue header, That is, back to the last Oracle7.dmp file, this time oralce6.dmp become the head of the queue element, Oralce6.dmp named Oracle7.dmp, the following analogy. Know that finally rename Oracle.dmp to Oracle2.dmp, then use the EXP command to export the database file, and the exported file is stored in the Oracle.dmp file.

Today, there is a problem, that is, there is a text.dmp file, I just want to import this. dmp file in a table or two tables, then how should be implemented. In fact, the IMP command has a series of parameters, we can control the database tables that need to be imported by tables this parameter. When importing a table, the import command is as follows:

Imp username/[email protected] file=f:\test.dmp tables=workflow ignore=y

If you are importing two or more tables, you need to enclose them in parentheses and import the commands as follows:

Imp username/[email protected] file=f:\test.dmp tables= (emp,workflow) ignore=y

1.2. Execute Database_ext.bat Batch File

You can double-click the batch file, or you can drag the batch file to cmd to execute it.

2. Recovery 2.1. Create a Droptables.sql script

This SQL script is used to execute the database stored procedure. The contents of the Droptable.sql are as follows:

Execute Droptables ();
Exit

2.2. Create a stored procedure

The reason to execute this script is to delete all existing tables in the database before recovering the database.

The droptables stored procedure is used to delete all tables in the current database, as shown in the following stored procedure:

Procedure Droptables is
V_sql VARCHAR2 (2000);
CURSOR cur is select table_name from User_tables ORDER by TABLE_NAME;
Begin for rows in cur loop v_sql: = ' drop TABLE ' | | rows.table_name| | ' cascade constraints ';
Dbms_output.put_line (V_sql);
Execute immediate v_sql;
End Loop;
Execute immediate ' purge recyclebin ';
End Droptables;

You can create a stored procedure in SQL developer, as shown in:

ps-2014-2-25 execution of stored procedures

Under the CMD command window

Execute Droptables (); exit

Under the SQL window

Begindroptables (); end;

2.3. Create a imp.bat batch file

Imp.bat This batch file is used to recover data by importing the DMP database files previously backed up into the database. The Imp.bat content is as follows:

Sqlplus Xuwei/[email protected] @f:\mydata_mytest\droptables.sql
Imp xuwei/[email protected] file=f:\mydata_mytest\oradata.dmp full=y ignore=y

2.4. bat file naming note points

The two batch files described above are named Database_imp.bat and Database_exp.bat, and if they are named Imp.bat and Exp.bat, double-clicking on the two files will make an error, and dragging them to the CMD command will perform normally. This is because the filename of the batch file cannot be the same as the command word, the above IMP and exp are the names of the commands, so they cannot be used to make the file name of the bat file.

3. Using Windows Task Scheduler

In Control Panel, open the Scheduled Tasks program, and then click Configure as Scheduled Tasks.

Note: If a Windows system performs a scheduled task, you must turn on the Scheduler service.

The service for the scheduled task program is called Taskschedule, as shown in:

Windows Scheduled tasks + batch files for scheduled backup and recovery of Oracle databases