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

1. Backup: 1.1. Create a database_exp.bat batch file

First, create the DMP and log files for seven days, and then create a batch file for exporting the database. This script can back up the database for the last seven days, as shown in database_ext.bat:

 
Del F: \ mydata_mytest \ oradata7.dmp
Del F: \ mydata_mytest \ oradata7.log
Ren F: \ mydata_mytest \ oradata6.dmp oradata7.dmp
Ren F: \ mydata_mytest \ oradata5.dmp oradata6.dmp
Ren F: \ mydata_mytest \ oradata4.dmp oradata5.dmp
Ren F: \ mydata_mytest \ oradata3.dmp oradata4.dmp
Ren F: \ mydata_mytest \ oradata2.dmp oradata3.dmp
Ren F: \ mydata_mytest \ oradata. dmp oradata2.dmp
Ren F: \ mydata_mytest \ oradata6.log oradata7.log
Ren F: \ mydata_mytest \ oradata5.log oradata6.log
Ren F: \ mydata_mytest \ oradata4.log oradata5.log
Ren F: \ mydata_mytest \ oradata3.log oradata4.log
Ren F: \ mydata_mytest \ oradata2.log oradata3.log
Ren F: \ mydata_mytest \ oradata. Log oradata2.log
Exp xuwei/xuwei @ orcl file = F: \ mydata_mytest \ oradata. dmp log = F: \ mydata_mytest \ oradata. Log
 

(PS: 2011-12-29)

Database_exp.bat parsing:The above batch processing statement is actually very simple, that is, to make seven DMP files into a 7-length queue, and then delete the queue header each time the database is backed up, that is, the last oracle7.dmp file to be backed up. At this time, oralce6.dmp becomes the queue Header element, named oralce6.dmp oracle7.dmp, and so on. Rename oracle. dmp to oracle2.dmp, and then use the exp command to export the database file. The exported file is stored in the Oracle. dmp file.

I encountered a problem today: there is a text. dmp file. I only want to import one or two tables in this. dmp file. How should I implement this. In fact, the IMP command has a series of parameters. We can use the tables parameter to control the database tables to be imported. When importing a table, the import command is as follows:

 
IMP username/password @ Sid file = F: \ test. dmp tables = workflow ignore = y
 

If you want to import two or more tables, you must enclose them in parentheses. The import command is as follows:

 
IMP username/password @ Sid file = F: \ test. dmp tables = (EMP, workflow) Ignore = y
 

1.2. Execute database_ext.bat to process files in batches

You can double-click the batch file or drag it to cmd for execution.

2. Restore 2.1. Create a droptables. SQL script

This SQL script is used to execute database stored procedures. The content of droptable. SQL is as follows:

 
ExecuteDroptables ();
Exit
 

2.2. Create a stored procedure

This script is executed to delete all existing tables in the database before restoring the database.

The droptables stored procedure is used to delete all tables in the current database. The stored procedure is as follows:

 Create   Or   Replace  
 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:

2.3. Create an imp. bat batch file

The batch processing file imp. bat is used to restore data. The principle is to import the previously backed up DMP database file to the database. The content of imp. bat is as follows:

 
Sqlplus xuwei/xuwei @ orcl @ F: \ mydata_mytest \ droptables. SQL
IMP xuwei/xuwei @ orcl file = F: \ mydata_mytest \ oradata. dmp full = y ignore = y
 

2.4. Notes on BAT file naming

The two batch files are named database_imp.bat and database_exp.bat respectively. BAT and Exp. bat, double-clicking these two files will cause errors, and dragging them to the CMD command can be executed normally. This is because the file name of the batch processing file cannot be the same as the command word. The above imp and exp are both the command name, so they cannot be used as the BAT file name.

3. Use Windows Task Scheduler Program

Open the scheduler in the control panel and configure the scheduler as shown in.

Note: If a scheduled task is executed in Windows, you must enable the scheduled task program service,

The service corresponding to the scheduled task program is taskschedule, as shown in: