Oracle automatic data backup

1, Oracle automatically backs up single table data for one month

Mode 1: three step processing (batch file creation, sql file writing, task plan setting)

1. Step 1: create a batch file

 

@echo off
Set OrclSid=orcl
Set ExpUser=Database name
Set ExpPass=Database password
Set FileDir=D:\oracleBack\log
Set SysDate=%date:~0,4%%date:~5,2%%date:~8,2%_%time:~0,2%%time:~3,2%%time:~6,2%
Set FileName=%ExpUser%~%OrclSid%_%SysDate%

@echo * * * * * * * * * * * * * * * * * * * * 
@echo * * * * * * * * * * * * * * * * * * * * 
@echo * * * * * ORACLE Database backup * * * * * * 
@echo * * * * * * * * * * * * * * * * * * * * 
@echo * * * * * * * * * * * * * * * * * * * *

@echo service name = %OrclSid% 
@echo user name = %ExpUser% 
@echo password = %ExpPass% 
@echo catalog = %FileDir% 
@echo Time = %SysDate% 
@echo file name = %FileName%.rar

@echo Start database backup... 
exp %ExpUser%/%ExpPass%@%OrclSid% file=%FileDir%/%FileName%.dmp log=%FileDir%/%FileName%.log tables=(T_SAP_IMPORT_DATA) query=\"where to_char(CREATED_DATE,'yyyy-MM-dd HH24-mi-ss') < to_char(SYSDATE,'yyyy-MM-dd HH24-mi-ss') and to_char(CREATED_DATE,'yyyy-MM-dd HH24-mi-ss')>to_char(add_months(sysdate,-1),'yyyy-MM-dd HH24-mi-ss')\" 
@echo Database backup complete!

@echo Log in to the database
sqlplus %ExpUser%/%ExpPass%@%OrclSid% @D:\oracleBack\bat\truncate.sql>truncate.txt

@echo Import backed up data
imp  %ExpUser%/%ExpPass%@%OrclSid% file=%FileDir%/%FileName%.dmp ignore=Y  tables=(T_SAP_IMPORT_DATA)

@echo Sign out cmd
~exit

 

2. Step 2: write the sql file to clear the database and free the space

 

truncate table T_SAP_IMPORT_DATA;
exit;

 

 

3. Step 3: use the task scheduler provided by windows

A) Open task scheduler and create a new basic task plan

 

 

B) Fill in the name and description and click Next

 

 

C) The trigger selects the corresponding rules, as shown in the figure, and executes once a day

 

 

 

D) Set the execution time and click Next

 

 

E) Next step

 

 

F) Fill in the path and starting parameters of the batch file (the folder where the batch file is located)

 

 

G) Click Finish

 

 

H) Double click the created task twice

 

 

1) Select whether the user is logged in or not to start

 

 

J) Enter the user name and password, OK

 

At this point, the task scheduler of windows is used to execute the batch file to realize the automatic backup of one month's data in Oracle database.

Tags: Oracle Database SQL Windows

Posted on Mon, 04 May 2020 14:04:35 -0400 by TRB