Oracle Database is one of the most widely used databases in the world today. With its powerful features, stability, and security, it has been widely applied across many fields. For DBAs, ensuring the normal operation of the database and the security of data is an extremely important task. In daily database maintenance work, performing regular database backups is an essential task. Oracle Database provides multiple backup methods, including manual backup, RMAN backup, etc., with automated backup being one of the methods that effectively ensures data security. Automated backup not only reduces manual operations and minimizes errors but also allows for quick data recovery in the event of a database failure.
Why Is Automated Backup Necessary?
As the core data storage of applications, databases contain a large amount of critical information, such as user data and configuration information. If the database experiences unexpected failures or data loss, it may have serious impacts or even cause significant losses to the business. Therefore, regularly backing up the database and ensuring secure storage of backup files is crucial.
Manual backups have many drawbacks, such as being prone to negligence and not being performed in a timely manner. Thus, automated backup becomes a better choice. By writing scripts to achieve automated backups, efficiency can be improved, human errors reduced, and data security better ensured.
Automated Backup Methods of Oracle Database
In Oracle Database, automated backup can be achieved using the operating system’s task scheduler (such as cron jobs) and Oracle’s built-in tools. By writing scripts, scheduled tasks can be set to automatically execute backup operations at predefined times. Commonly used automated backup tools in Oracle include Data Pump and RMAN. Data Pump is used to export database data, while RMAN is primarily responsible for database backup and recovery.
Data Pump (expdp/impdp) provides efficient database export/import with features like compression, network transfer, and parallel processing. It allows DBAs to define export parameters and automate backups via cron jobs. RMAN (Recovery Manager) is designed for comprehensive database backup and recovery, supporting incremental and physical backups, as well as tracking backup files. It enables automated backups through scripting and OS scheduling. Oracle Enterprise Manager (OEM) offers a GUI for backup management, while third-party tools also integrate with Oracle for automated backups.
How to Set Up Daily Automatic Oracle Database Backup on Windows?
In a Windows environment, daily automated backup and deletion of Oracle Database backups can be achieved using Task Scheduler.
1. Create a Batch File (.bat)
1.1 Simple Script
exp user/password@ORCL direct=y buffer=100000 file=D:\backup\dmp%date:~0,10% LOG=D:\backup\data.log
forfiles /p "D:\backup" /s /m *.dmp /d -7 /c "cmd /c del @path"
Explanation:
The exp command is Oracle's dump backup command, where %date:~0,10% extracts the date in DOS.
forfiles is a Windows file search command, which searches for .dmp files in the backup directory created within the last three days and deletes them.
Parameters of forfiles:
/p specifies the search path (in this case, the directory where backup files are stored).
/m searches files using a mask (e.g., .dmp for backup dump files).
/d filters files modified before or after a specific date (-3 means files older than three days).
/s includes subdirectories.
/c specifies the command to execute on each file (e.g., cmd /c del @file to delete the file).
1.2 Advanced Script
@echo off
echo ================================================
echo Oracle Database Automated Backup Script in Windows
echo 1. Uses the current date as the backup file name.
echo 2. Automatically deletes backups older than 7 days.
echo ================================================
:: Extract year, month, day, hour, minute, and second.
set dd=%DATE:~8,2%
set mm=%DATE:~5,2%
set yy=%DATE:~0,4%
set Tss=%TIME:~6,2%
set Tmm=%TIME:~3,2%
set Thh=%TIME:~0,2%
set Thh=%Thh: =0%
:: Generate timestamp in YYYYMMDDHHmmss format.
set BACKUPDATE=%yy%%mm%%dd%%Thh%%Tmm%%Tss%
:: Set username, password, and database name.
set USER=admin
set PASSWORD=admin
set DATABASE=ORCL
:: Create backup directories if they do not exist.
if not exist "D:\backup_database\data" mkdir D:\backup_database\data
if not exist "D:\backup_database\log" mkdir D:\backup_database\log
set DATADIR=D:\backup_database\data
set LOGDIR=D:\backup_database\log
:: Perform the backup.
exp %USER%/%PASSWORD%@%DATABASE% file=%DATADIR%\data_%BACKUPDATE%.dmp log=%LOGDIR%\log_%BACKUPDATE%.log
:: Delete backups and logs older than 7 days.
forfiles /p "%DATADIR%" /s /m *.* /d -7 /c "cmd /c del @path"
forfiles /p "%LOGDIR%" /s /m *.* /d -7 /c "cmd /c del @path"
exit
Note: If Chinese characters appear garbled when executing the script, save the file as ANSI format using Notepad.
2. Create a Scheduled Task in Windows
Open Windows Task Scheduler:
Control Panel → System and Security → Administrative Tools → Task Scheduler
Open the "Create Basic Task" wizard from the right panel.
Enter a task name and an optional description, then click Next.
Set the trigger as Daily.
Choose Start a Program, browse for the batch file, and complete the setup.
How to Set Up Daily Automatic Oracle Database Backup on Linux?
1. Identify Database Environment Variables
If you are using the root account, you must first switch to the database user:
su oracle
Check the environment variables:
cat ~/.bash_profile
Ensure the following environment variables are set:
export PATH
export ORACLE_BASE=/home/nnc_db/app
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=orcl
2. Create the Backup Script
Edit a new script file:
vi bak.sh
Copy and paste the environment variables into `bak.sh`. Then, define a variable `date` (used for naming backup files) and use the `expdp` command to export the database:
date=$(date +%Y%m%d)
expdp db_username/db_password@db_instance dumpfile=${date}_db_auto_backup.dmp schemas=db_username compression=allSave and exit.
3. Schedule the Backup Using crontab
Edit the crontab file:
crontab -e
In the text editor, add the following line:
0 0 * * * /home/nnc_db/bak.sh
This means the script `/home/nnc_db/bak.sh` will run daily at 12:00 AM. Adjust the time and file path as needed.
Restart the cron service:
service crond restart
4. Verify That the Task Runs Successfully
Some users have reported that crontab tasks may not take effect immediately within the first three minutes after being set up. To test if your script is running properly, you can schedule it to run every minute:
* * * * * /home/nnc_db/bak.sh
If the script runs correctly, the issue is likely due to a delay in crontab activation.
Additionally, running the script manually will display detailed output, but when scheduled via `crontab`, there will be no direct output. To check if the script is executing, review logs in:
/var/spool/mail/
Oracle Automated Disaster Recovery Tool
Vinchin Backup & Recovery is a robust backup and recovery tool for virtual machines and databases, which provides advanced backup and disaster recovery features, including automatic VM backup, agentless backup, LAN/LAN-Free backup, offsite copy, instant recovery, effective data reduction, cloud archive and etc., strictly following 3-2-1 golden backup architecture to comprehensively secure your data security and integrity. Besides, data encryption and anti-ransomware protection offer you dual insurance to protect your database backups.
It only takes 4 steps for you to backup Oracle database:
1. Select the backup object.
2. Select backup destination.
3. Configure backup strategies.
Here, you can setup schedules for your backup jobs. You can perform full backups weekly or monthly and perform incremental backups on daily basis.
4. Review and submit the job.
Try the 60-day full featured free trail of Vinchin Backup & Recovery now to easily protect your Oracle data and achieve automated backup and fast recovery!
Auto Backup Oracle Database FAQs
1. What is the difference between RMAN and Data Pump?
RMAN is primarily used for backup and recovery, allowing for physical backups of database files, incremental backups, and point-in-time recovery. In contrast, Data Pump is used for logical backups, exporting and importing database objects like tables, schemas, or entire databases in a portable format. While RMAN is best for full database recovery, Data Pump is ideal for data migration, replication, and selective data extraction.
2. How do I restore a single table from RMAN backup?
Use RMAN tablespace point-in-time recovery or Data Pump to restore a single table.
Example with Data Pump:
impdp system/password directory=DATA_PUMP_DIR dumpfile=backup.dmp remap_table=old_table:new_table
Conclusion
Automated backup of Oracle Database is essential for ensuring data security, minimizing human errors, and enabling quick recovery in case of failures. By leveraging built-in tools like Data Pump and RMAN, along with operating system schedulers, DBAs can implement efficient backup strategies on both Windows and Linux systems. Properly configured automated backups reduce the risk of data loss and ensure business continuity, making them an indispensable part of database maintenance.
