Properly altering system archive log enhances data recovery and storage efficiency. By adjusting log format and path, and performing regular backups, you ensure the security, reliability, and optimized management of your database system.
Updated by Dan Zeng on 2025/02/19
In Oracle Database, archive logs are a key component in ensuring data reliability and persistence, especially when the database is backed up and restored. To meet different backup requirements and storage strategies, administrators often need to adjust the format and storage path of the archive logs.
Properly configuring these parameters not only improves the database's ability to recover from failures, but also optimizes the efficient use of storage resources. This article will guide you through the step-by-step process of altering database archivelog oracle, including verifying that the database is in archive mode, modifying the format of the archive log, and changing the storage path for the archive log, and ultimately restarting the database to make the new settings take effect. By following these steps, you can effectively manage and optimize the archive logging policy for Oracle database.
Verify that archive mode is enabled in the database by using the following command:
SQL> archive log list;
The output should look something like this:
Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 3 Next log sequence to archive 5 Current log sequence 5
The default format is %t_%s_%r.dbf.Let's change the format to %t_%s_%r.arch. This is a static parameter, so a database restart is required for it to take effect.
Check the current format first
SQL> show parameter log_archive_format;
You should see something like this:
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_format string %t_%s_%r.dbf<br>
Then use the following command to modify the archive log format.
SQL> alter system set log_archive_format='%t_%s_%r.arch' scope=spfile;
Check the current log path first
SQL> show parameter DB_RECOVERY_FILE_DEST;
The output should look like:
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /oracle/app/fast_recovery_area db_recovery_file_dest_size big integer 4182M
Next, modify the archive log storage path:
SQL> alter system set log_archive_dest_1 = 'location=/data1/arch' scope=spfile;
After making these changes, restart the database to apply them. Then manually switch the logs to verify if they are being generated correctly:
alter system switch logfile;
When managing and backing up databases, it is critical to ensure the security and recoverability of your data. Vinchin Backup & Recovery provides a powerful database backup and recovery solution that supports a wide range of databases, including Oracle DB, MySQL, SQL Server, PostgreSQL, Postgres Pro and MariaDB. It protects database systems installed on physical and virtual machines, ensuring efficient and reliable data protection regardless of the environment.
One of the highlights of Vinchin is its efficient backup strategy, which reduces the size of database backups by nearly 70% without consuming the CPU of the production environment. This feature greatly improves the availability of backup storage while increasing backup efficiency and minimizing the impact of backup operations on system performance.
In addition, Vinchin supports efficient hot backups of MySQL, SQL Server and Oracle, ensuring that the normal operation of databases is not affected during the backup process. Whether it is a full, differential, incremental or transaction log backup, Vinchin provides you with a flexible backup strategy that allows you to set up a backup plan according to your actual needs, ensuring data security and business continuity.
Vinchin Backup & Recovery's operation is very simple, just a few simple steps.
1.Just select databases on the host
2.Then select backup destination
3.Select strategies
4.Finally submit the job
Vinchin Backup & Recovery is a comprehensive and efficient database backup solution for businesses and organizations of all sizes, which is ideal for database administrators as it can effectively safeguard the security of databases and improve the efficiency of backup management. To help you experience its power, we've secured a 60-day free trial so you can test it out in real-world environments and get a feel for its backup and recovery capabilities.
For more information or to get a free trial, please feel free to contact us and we will provide you with professional technical support and services to help you take full advantage of Vinchin Backup & Recovery.
Q1: How does executing this command affect database performance?
A1: Executing this command causes the database to temporarily suspend processing user requests until all specified logs have been archived. This may result in a brief performance degradation or increase in response time.
Q2: Can it be used in a production environment?
A2: Yes, but it should be used with caution in high load production environments, preferably during low peak hours to minimize the impact on user experience.
Properly altering system archive log all enhances data recovery and storage efficiency. By adjusting log format and path, and performing regular backups with Vinchin Backup & Recovery, you can ensure the security, reliability, and optimized management of your database system.
Share on:
