In Oracle Database operations, ARCHIVELOG mode is essential for preventing data loss and enabling recovery to a “specific point in time” in the event of a failure.
If you are looking to “take database backups online” or “restore the database to the state immediately preceding a failure,” enabling this setting is the solution. This article provides a professional perspective on the mechanics of Oracle ARCHIVELOG, specific switching procedures in a 19c environment, and operational precautions.
- Conclusion: ARCHIVELOG Mode To-Do List
- What is ARCHIVELOG Mode? (Basic Knowledge)
- How to Check ARCHIVELOG Mode
- Implementation: Steps to Change to ARCHIVELOG Mode
- Reverse Procedure: How to Revert to NOARCHIVELOG Mode
- Troubleshooting: Common Errors
- Operational Precautions
- FAQ: Frequently Asked Questions
- Summary
Conclusion: ARCHIVELOG Mode To-Do List
Switching to ARCHIVELOG mode requires temporarily stopping the database and performing the operation in the MOUNT state.
- Confirm Current Mode: Check using the
V$DATABASEview. - Clean Shutdown: Execute
SHUTDOWN IMMEDIATE. - Start in Mount State: Use
STARTUP MOUNTto load the control files. - Change Mode: Execute
ALTER DATABASE ARCHIVELOG. - Open DB: Resume service with
ALTER DATABASE OPEN. - Configure Destination: Define the archive output location using
LOG_ARCHIVE_DEST_1, etc.
What is ARCHIVELOG Mode? (Basic Knowledge)
Oracle Database utilizes Online REDO Log Files to record data change history. Normally, these files are overwritten once they become full (NOARCHIVELOG mode).
ARCHIVELOG mode is an operational mode where a copy of the REDO log is saved to a separate intermediate location (Archive Log File) before it is overwritten.
Main Benefits
- Point-in-Time Recovery (PITR): Recovery can be performed by specifying a specific time, such as just before an erroneous operation.
- Online Backup: Backups can be obtained while the database is running.
- Data Protection: Disaster recovery is possible by remotely transferring archive logs (e.g., using Oracle Data Guard).
How to Check ARCHIVELOG Mode
First, confirm the current settings. Log in using a user with administrative privileges (SYSDBA) via SQL*Plus.
-- Check the current log mode
SELECT LOG_MODE FROM V$DATABASE;
- ARCHIVELOG: Enabled (Backup operations are possible).
- NOARCHIVELOG: Disabled (Default state. Data protection during failure is limited).
Implementation: Steps to Change to ARCHIVELOG Mode
For 19c (CDB/PDB architecture), configuration is typically performed at the CDB (Root Container) level.
[Prerequisites]
- Privileges: SYSDBA privileges are required.
- Environment: ORACLE_SID must be set correctly.
- Risk: A temporary database stoppage (downtime) will occur.
1. Start the Database in Mount State
Mode changes cannot be executed while the database is open.
-- 1. Shutdown the database normally
SHUTDOWN IMMEDIATE;
-- 2. Start in mount state
STARTUP MOUNT;
2. Enable ARCHIVELOG Mode
Switch to the setting that saves logs using the following command.
-- Change to ARCHIVELOG mode
ALTER DATABASE ARCHIVELOG;
-- Open the database
ALTER DATABASE OPEN;
3. Configure the Archive Log Destination (Output Location)
If the destination is not set or the Fast Recovery Area (FRA) becomes full, it can cause the DB to stop.
-- Check the destination parameter
SHOW PARAMETER LOG_ARCHIVE_DEST_1;
-- Example of specifying a specific directory as the destination (e.g., /u01/app/oracle/arch)
-- Add "LOCATION=" to the beginning of the path
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/arch' SCOPE=BOTH;
Note: Specifying SCOPE=BOTH ensures the change is applied immediately and remains effective after a restart.
Reverse Procedure: How to Revert to NOARCHIVELOG Mode
Steps for cases such as stopping archive output in a test environment.
SHUTDOWN IMMEDIATE;STARTUP MOUNT;ALTER DATABASE NOARCHIVELOG;ALTER DATABASE OPEN;
Troubleshooting: Common Errors
| Error Code | Cause | Action |
| ORA-00257 | Archive destination disk is full | Delete unnecessary archive logs using RMAN or expand the space. |
| ORA-01126 | Attempted change without mounting the database | Perform SHUTDOWN, then retry STARTUP MOUNT. |
Operational Precautions
- Monitor Disk Capacity: Archive logs are not deleted automatically. If left unattended, they will exhaust disk space and cause the database to hang (stop).
- Backups via RMAN: The standard operational practice is to delete archive logs after they have been backed up using Oracle Recovery Manager (RMAN).
- Performance: Consider that I/O overhead from archive output occurs during large-scale data updates (such as batch processing).
FAQ: Frequently Asked Questions
Q. Can the mode be changed for each PDB (Pluggable Database)?
A. No. ARCHIVELOG mode is an instance-wide setting (per CDB), and it cannot be toggled for individual PDBs.
Q. Is a restart required after changing the settings?
A. Since ALTER DATABASE ARCHIVELOG itself is an operation performed in the mount state, it is complete once you finally perform ALTER DATABASE OPEN. However, if you modified the LOG_ARCHIVE_DEST parameter with SCOPE=SPFILE, a restart is required.
Q. Can I delete archive logs manually?
A. Direct deletion using OS commands is not recommended. Oracle will lose track of the file’s existence, leading to inconsistencies during recovery. Always use RMAN commands for deletion.
Summary
- ARCHIVELOG mode is essential for online backups and recovery to any point in time.
- Changing the setting requires stopping the database and starting it in mount mode.
- Once operation begins, do not neglect monitoring the free space at the archive destination.
This article is based on Oracle Database 19c (screens or default values may differ in other versions).
[reference]
Oracle Database Backup and Recovery Reference, 19c
コメント