In Oracle Database backup, the simplest and most reliable method is Cold Backup (Offline Backup). Since it is completed using only OS copy commands without using Recovery Manager (Oracle RMAN), it is characterized by its ease of understanding, even for beginners.
This article explains the specific procedures and precautions for Oracle Cold Backup, which protects files by stopping the database and maintaining consistency.
Conclusion: Shortest Procedure for Cold Backup (3 Steps)
The basics of a cold backup consist of three stages: “Stop,” “Copy,” and “Start.”
- Stop DB: Execute
SHUTDOWN IMMEDIATEin SQL*Plus (ensuring consistency). - OS Copy: Copy data files, control files, log files, etc., to another directory.
- Start DB: Execute
STARTUPto resume operations.
Background and Basics: How Cold Backup Works
A Cold Backup is a backup obtained by completely stopping the database instance and ensuring that the consistency between data files (System Change Number (SCN)) is synchronized.
- Pros: Simple procedure. No complex log application (
RECOVERoperation) is required during recovery. - Cons: The database must be stopped during the operation (occurrence of downtime).
Procedure and Implementation: From File Confirmation to Backup
1. Confirm the target files for backup (Perform while running)
To avoid confusion about “which files to copy” after stopping, check the paths while the database is running.
-- Confirm control files
SELECT name FROM v$controlfile;
-- Confirm data files
SELECT name FROM v$datafile;
-- Confirm REDO log files
SELECT member FROM v$logfile;
-- Confirm parameter file (spfile)
SHOW PARAMETER spfile
SQL> SELECT name FROM v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/V19/control01.ctl
/u01/app/oracle/oradata/V19/control02.ctl
SQL> SELECT name FROM v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/V19/system01.dbf
/u01/app/oracle/oradata/V19/sysaux01.dbf
/u01/app/oracle/oradata/V19/undotbs01.dbf
/u01/app/oracle/oradata/V19/users01.dbf
SQL> SELECT member FROM v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/V19/redo03.log
/u01/app/oracle/oradata/V19/redo02.log
/u01/app/oracle/oradata/V19/redo01.log
SQL> set lin 1000
SQL> SHOW PARAMETER spfile
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
spfile string /u01/app/oracle/product/19.0.0
/dbhome_1/dbs/spfilev19.ora
Note: The password file (
orapw<SID>) is also a backup target but is not displayed inV$views. It is typically located in$ORACLE_HOME/dbs(Linux) or%ORACLE_HOME%\database\(Windows).
2. Shut down the database normally
Stop the database with the IMMEDIATE option to roll back uncompleted transactions and align the file states.
SQL> SHUTDOWN IMMEDIATE;
-- Confirm "Database closed" and "Database dismounted"
3. Copy files using OS commands
Return to the OS prompt (bash or command prompt) and execute the copy.
In the case of Linux/Unix Bash
# Maintain permissions and timestamps with the -p option
cp -p /u01/app/oracle/oradata/V19/*.dbf /backup/v19/
cp -p /u01/app/oracle/oradata/V19/*.ctl /backup/v19/
cp -p /u01/app/oracle/oradata/V19/*.log /backup/v19/
cp -p $ORACLE_HOME/dbs/spfileV19.ora /backup/v19/
cp -p $ORACLE_HOME/dbs/orapwV19 /backup/v19/
In the case of Windows DOS
:: /Y disables overwrite confirmation
copy C:\oracle\oradata\V19\*.dbf D:\backup\v19\ /Y
copy C:\oracle\oradata\V19\*.ctl D:\backup\v19\ /Y
copy C:\oracle\oradata\V19\*.log D:\backup\v19\ /Y
copy C:\oracle\product\19.0.0\dbhome_1\dbs\spfileV19.ora D:\backup\v19\ /Y
copy C:\oracle\product\19.0.0\dbhome_1\database\PWDV19.ora D:\backup\v19\ /Y
4. Restart the database
After the copy is complete, start the database from SQL*Plus.
SQL> STARTUP
Restore Procedure: Recovery Method in Case of Emergency
This is the procedure to return to the state at the time of backup when data is corrupted.
- Stop DB:
SHUTDOWN IMMEDIATE;(ConsiderABORTif it cannot be started). - Restore Files:
- Linux:
cp -p /backup/v19/* /u01/app/oracle/oradata/V19/ - Windows:
copy D:\backup\v19\* C:\oracle\oradata\V19\ /Y
- Linux:
- Start DB:
STARTUP
Operational and Security Precautions
- Lack of Consistency: Files copied immediately after a
SHUTDOWN ABORT(forced termination) may not allow the database to start upon restoration. Always stop withIMMEDIATE. - Storage: Always specify a physical disk or network drive different from the one used for DB operations as the backup destination.
- Permissions: In Linux, ensure that the owner of the files after copying is the
oracleuser and that the permissions (e.g., 640) are correct.
FAQ: Frequently Asked Questions
Q1. Is Cold Backup effective even in ARCHIVELOG mode? Yes, it is effective. However, a Cold Backup is for returning to the state at “that point in time.” If you want to recover to the most recent state, combine RMAN online backups with the application of archived redo logs.
Q2. What should I do in an environment where the database cannot be stopped? In that case, Cold Backup cannot be used. Set the database to ARCHIVELOG mode and select “Hot Backup (Online Backup)” using Oracle RMAN.
Q3. Is it always necessary to copy REDO log files? Yes. To start a Cold Backup (Consistent Backup) as is, REDO log files that are paired with the data files are required.
Summary
- Cold Backup is a reliable backup method performed while the database is stopped.
- It can be executed using only OS commands (cp or copy) without using Oracle RMAN.
- Operations are simple: just restore the files and
STARTUPduring recovery. - Ideal for development/test environments where downtime is acceptable, or for small-scale databases.
This article is based on Oracle Database 19c (screens or default values may differ for other versions).
[reference]
Oracle Database Backup and Recovery Reference, 19c
コメント