In Oracle Database, the method of taking a backup while the database is running is called a Hot Backup (Online Backup). While using RMAN (Recovery Manager) is fundamentally recommended, there are cases where manual acquisition using OS commands is required due to environmental constraints.
This article provides a detailed explanation of the correct Oracle Hot Backup procedure tailored for a 19c environment, maintaining all steps from the original source without omission.
Conclusion: Overall Flow of Hot Backup (7 Steps)
A Hot Backup acquires all elements (data files, control files, logs, etc.) without omission in the following order:
- Mode Verification: Confirm the database is in ARCHIVELOG mode.
- Log Switch: Execute
ALTER SYSTEM ARCHIVE LOG CURRENT;. - Declaration: Execute
ALTER TABLESPACE <tablespace_name> BEGIN BACKUP;. - Copy: Duplicate data files using OS commands (
cporcopy). - Termination: Execute
ALTER TABLESPACE <tablespace_name> END BACKUP;. - Control Files etc.: Backup control files, parameter files, and password files.
- Re-switch: Switch logs again and copy the ARCHIVELOG files.
Prerequisites: Verification and Configuration of ARCHIVELOG Mode
To perform a Hot Backup, the database must be in ARCHIVELOG mode.
Verify Current Mode
Execute the following SQL to check the current settings.
SELECT LOG_MODE FROM V$DATABASE;
- ARCHIVELOG: Hot Backup is possible.
- NOARCHIVELOG: Hot Backup is not possible. Change the mode using the following procedure.
Steps to Change to ARCHIVELOG Mode
-- 1. Start the database in MOUNT mode
SHUTDOWN IMMEDIATE
STARTUP MOUNT
-- 2. Change to ARCHIVELOG mode
ALTER DATABASE ARCHIVELOG;
-- 3. Open the database
ALTER DATABASE OPEN;
-- 4. Re-verify the setting
SELECT LOG_MODE FROM V$DATABASE;
Implementation: Complete Hot Backup Execution Process
The following explains all steps introduced in the original article without omitting any commands.
1. Switch ARCHIVELOG
Force the current REDO logs in memory to be archived to disk.
ALTER SYSTEM ARCHIVE LOG CURRENT;
2. Put Database into Hot Backup Mode
Transition the target tablespace into backup mode.
ALTER TABLESPACE <tablespace_name> BEGIN BACKUP;
3. Copy Data Files
While the tablespace is in backup mode, copy the files using OS commands. SQL to verify file paths:
SELECT FILE_NAME FROM DBA_DATA_FILES;
Example of copy execution (for Linux):
cp /u01/app/oracle/oradata/ORCL/users01.dbf /backup/users01.dbf
4. End Backup Mode
Immediately release the backup mode after the copy is complete. If you forget to release it, REDO logs will continue to be generated in large quantities.
ALTER TABLESPACE <tablespace_name> END BACKUP;
5. Backup Control Files
Since control files are not covered by BEGIN BACKUP, backup them individually using SQL commands.
-- Acquire in binary format
ALTER DATABASE BACKUP CONTROLFILE TO '/backup/control01.ctl';
-- To acquire in trace file (text) format
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
6. Switch and Backup ARCHIVELOG
Perform another switch to finalize the logs generated during the backup period.
ALTER SYSTEM ARCHIVE LOG CURRENT;
Afterward, copy the archive files using OS commands. SQL to verify the path: SHOW PARAMETER log_archive_dest Example of copy execution:
cp /u01/app/oracle/archivelog/* /backup/archivelog/
7. Backup Initialization Parameters and Password Files
Do not forget to protect the instance configuration information.
Backup SPFILE:
CREATE PFILE='/backup/initORCL.ora' FROM SPFILE;
Copy PFILE (Linux example):
cp $ORACLE_HOME/dbs/initORCL.ora /backup/initORCL.ora
Copy Password File (Linux example):
cp $ORACLE_HOME/dbs/orapwORCL /backup/orapwORCL
Restore and Recovery Methods for Hot Backup
This is the flow when restoring using the acquired files.
- Placement of Files: Return the backed-up data files and control files to their original locations.
cp /backup/users01.dbf /u01/app/oracle/oradata/ORCL/users01.dbfcp /backup/control01.ctl /u01/app/oracle/oradata/ORCL/control01.ctl - Mount: Start the database in the MOUNT state.
STARTUP MOUNT; - Execute Recovery: Apply ARCHIVELOGs to ensure consistency.
RECOVER DATABASE; -- Apply ARCHIVELOGs following instructions - Open: Open the database after completion.
ALTER DATABASE OPEN;
Operational Notes: Benefits and Pitfalls
- Caution: While a tablespace is in the
BEGIN BACKUPstate, entire blocks are written to the REDO logs, leading to a significantly higher volume of log generation than usual. - Risk: Due to the high number of manual operations, omissions of
END BACKUPor copy errors are common drawbacks. - Recommendation: Unless there is a specific reason, it is strongly recommended to use RMAN, which can automate and simplify these complex procedures.
FAQ: Frequently Asked Questions
Q1. Can I use files copied via OS commands if I forgot to execute BEGIN BACKUP? No, you cannot. Files currently in use have their contents updated during the copy process, resulting in “fractured blocks.” They will not allow the database to start even if restored.
Q2. Is it okay to put all tablespaces into BEGIN BACKUP at once? It is syntactically possible (ALTER DATABASE BEGIN BACKUP;), but since the REDO log write load will concentrate at a single point, it is safer to perform it sequentially for each tablespace in a production system.
Q3. What if I am told “Log required” during recovery? You need to apply the ARCHIVELOGs acquired after the backup finished. Specify the files acquired in Step 6.
Summary
- ARCHIVELOG mode is mandatory for Hot Backup.
- Execute OS copy between
BEGIN BACKUPandEND BACKUP. - Backup control files, SPFILE, and ARCHIVELOGs as a set.
- Consider migrating to RMAN as much as possible to prevent procedural errors.
Note: This article is intended for Oracle Database 19c (screens or default values may differ for other versions).
[reference]
Oracle Database Backup and Recovery Reference, 19c
コメント