Archive Logging Oracle 101: How to Manage Archived Logs Simplified

Oracle has grown to become one of the major RDBMS (Relational Database Management System) solution providers in the IT industry. Oracle is an Object-Relational Database Management System (ORDBMS) because it includes object-oriented functionality such as user-defined types, inheritance, and polymorphism. Oracle maintains a close eye on business demands and stays up with technology advancements and needs.

One of Oracle Database’s most important features in Archive Logging Oracle is the ability to save populated groups of Oracle Database – Redo Log (Transaction Log Files) files to one or more offline destinations, or just the Archive Logging Oracle functionality. We shall learn more about this in the following sections.

Why is Oracle the Backbone of Many Enterprise Applications?

Oracle is a technology company that offers a wide range of software products, most notably its Oracle Database which is used for database management. Oracle offers cloud, enterprise resource planning (ERP), and customer relationship management (CRM) solutions along with database solutions.

Key Features of Oracle that Make it Popular

1. Database Management: Oracle Database provides robust and secure data management for data-driven decision-making in the enterprise.
2. High Performance and Scalability: It can handle high transaction rates and scale up for growing data and users.
3. Security: Advanced security features, including encryption and user authentication, to protect sensitive data and compliance.
4. Integration: Oracle products integrate with many applications and technologies to simplify across departments.
5. Complex Applications: Oracle databases can handle complex enterprise applications, providing real-time data processing and analytics.

Explore the best Oracle ETL tools to simplify your data integration and drive efficient data workflows. Dive deeper into our detailed guide.

How do Redo Records work?

Redo records are buffered cyclically in the redo log buffer of the System Global Area (SGA) and are documented to one of the redo log files by the Log Writer (LGWR) database background process. The System Global Area (SGA) is a collection of shared memory structures (SGA components) that hold data and control for a single Oracle Database instance. Cached data blocks and shared SQL areas are two examples of data kept in the SGA.

LGWR sends transaction redo records from the SGA’s redo log buffer to a redo log file whenever a transaction is committed and assigns a system change number (SCN) to each committed transaction’s redo records. The user process is alerted that a transaction has been committed only after all related redo records have been securely written to disk in the online logs.

Depending on whether archiving is allowed, LGWR has access to filled redo log files for reuse.

• After the changes recorded in the redo log file have been written to the data files, a filled redo log file is accessible if archiving is disabled (the database is in NOARCHIVELOG mode).

• After the changes recorded in the redo log file have been written to the data files and the file has been archived, a filled redo log file is accessible to LGWR if archiving is enabled (the database is in ARCHIVELOG mode).

In other words, an Oracle database has two logging modes: Noarchivelog and Archivelog. In Noarchivelog mode, filled online redo logs are not archived but will be overwritten. In Archivelog, after the online redo logs are filled, they will migrate to the archive location.

Learn how to manage date/time values using the EXTRACT function in Oracle, a useful tool when handling archived logs in Oracle’s Archive Logging process.

What Is the Archived Redo Log?

Oracle Database allows you to save completed groups of redo log files to one or more offline locations, collectively called the archived redo log or the archive log. The process of converting redo log files into archived ones is called archiving. This can only occur if the database is operating in ARCHIVELOG mode. You have the option to choose between automatic or manual archiving. You can use archived redo logs to:

1. Recover a database
2. Update a standby database
3. Get information about the history of a database using the LogMiner utility

NOARCHIVELOG and ARCHIVELOG Modes: Which one should you choose?

When managing an Oracle database, one critical decision involves choosing between NOARCHIVELOG and ARCHIVELOG modes. Each mode has its implications for data recovery, performance, and storage requirements. 

Choose NOARCHIVELOG Mode if:

• You can afford to lose data since the last backup, such as in development or testing environments.
• You prioritize performance and storage efficiency over data protection.

Choose ARCHIVELOG Mode if:

• You require flexibility in backup and recovery options, enabling point-in-time recovery.
• Data integrity and recovery are critical, as in production environments where data loss is unacceptable.

Checking if ARCHIVELOG in Enabled

Two ways can check whether Archive Logging Oracle mode is enabled or not.

1) By SQL*Plus command

To use this command, you need to log in to the database first.

C: UsersHevo>sqlplus sys@orclcdb as sysdba
...
Enter password:

Use SQL*Plus command Archive Logging Oracle list to retrieve some related information about redo and Archive Logging Oracle.

ARCHIVELOG Mode

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival                Enabled
Archive destination               USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence        9023
Current log sequence              9025

NOARCHIVELOG Mode

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival                Disabled
Archive destination               USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence        9023
Current log sequence              9025

2) SQL query

ARCHIVELOG Mode

SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG

NOARCHIVELOG Mode

SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG

Change Archivelog Destination In Oracle

• Check ARCHIVELOG location before the change. 
• Check Current Archivelog Location:
  • SQL —> archive log list. 
• Change ARCHIVELOG location. Keep in mind to first set the FRA value before changing. 

Note: When you decide to use an FRA to store your Archive Logging Oracle redo logs, make sure it has enough space to accommodate the quantity of archive logging redo logs created between backups. Remember that the FRA usually contains a variety of files, including RMAN backup files, flashback logs, and so on. If you use an FRA, keep in mind that the production of other types of files may influence the amount of space required by archive redo log files.

• Check the status of the ARCHIVELOG location.
  • SQL> ARCHIVE LOG LIST;

Enable Archivelog Mode

Before enabling Archive Logging Oracle mode, there are a few things to consider regarding the database:

1. Determine the size of the disk on which our Archive Logging Oracle information will be stored, and allot disk space accordingly. This is a significant issue because Archive Logging Oracle files eat up disk space.
2. Another key question is how long you will keep the Archive Logging Oracle records.
3. It is required to determine whether the Archive Logging Oracle files will be kept in a single directory or on many drives.
4. The format of your archival logs must be determined. It will be easy to distinguish it from other database files as a result.

SQL> Shut immediate;
SQL> Startup mount;
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list;

Disable Archivelog Mode

SQL> shut immediate;
SQL> startup mount;
SQL> alter database noarchivelog;
SQL> alter database open;
SQL> archive log list;

Changing the Database Archiving Mode

Use the ALTER DATABASE command with the ARCHIVELOG or NOARCHIVELOG clause to modify the database’s archiving mode. To change the archiving mode, you must be logged in as an administrator to the database.

To change the database archiving mode from NOARCHIVELOG to ARCHIVELOG, carry out the following:

1. Shut down the database instance.

SHUTDOWN

Before you can switch the database Archive Logging Oracle mode, you must close any open databases and shut down any connected instances. If any data files require media recovery, you cannot change the mode from ARCHIVELOG to NOARCHIVELOG.

2. Make a database backup: Always back up a database before making any substantial changes to protect yourself from mishaps. This will be your final database backup in NOARCHIVELOG mode, and it can be utilized if something goes wrong during the switch to ARCHIVELOG mode. For more information about database backups, see Oracle Database Backup and Recovery Basics.

3. Include the initialization parameters that indicate the Archive Logging Oracle log file destinations in the initialization parameter file (see “Specifying Archive Destinations”).

4. Create a new instance and mount the database, but do not open it.

STARTUP MOUNT

5. Change the archiving mode for the database. Then, for typical activities, open the database.

ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN; 

6. Shut down the database.

SHUTDOWN IMMEDIATE

7. Create a database backup: The control file is updated when the database archiving mode is changed. After changing the database archiving mode, you must back up all of your database files and control files. Because it was taken in NOARCHIVELOG mode, any prior backup is no longer useful.

Note: To use your database in manual archiving mode, follow the steps outlined in “Changing the Database Archiving Mode.” When specifying the new mode in step 5, however, use the following statement:

ALTER DATABASE ARCHIVELOG MANUAL;

If you use manual ARCHIVELOG mode to run your database, you must archive inactive groups of filled redo log files, or else your database will be temporarily stopped. Connect with administrator capabilities to manually archive a full redo log group. Make that the database is mounted but not active. To manually archive filled redo log files, use the ALTER SYSTEM command with the ARCHIVE LOG clause. All unarchived log files are archived with the following statement:

ALTER SYSTEM ARCHIVE LOG ALL;

Archive Logging Oracle Log Generation

If you are requested for the frequency of periodic log switches and the size of Archive Logging Oracle created at a particular time, you can use the queries below to get the information.

Daily Archive Log Generation

select trunc(COMPLETION_TIME,'DD') Day, thread#, 
round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,
count(*) Archives_Generated from v$archived_log 
group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;

Hourly Archive Log Generation

set pages 1000
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

select trunc(COMPLETION_TIME,'HH') Hour,thread# , 
round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,
count(*) Archives from v$archived_log 
group by trunc(COMPLETION_TIME,'HH'),thread#  order by 1 ;

Stating Archive Destinations

You have the option of archiving redo logs to a single location or multiplexing them. If you only wish to archive to one location, use the LOG_ARCHIVE_DEST initialization option to indicate the location. If you wish to multiplex the archived logs, you may use LOG_ARCHIVE_DEST_n parameters to archive to up to ten locations or the LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST parameters to Archive Logging Oracle just to a primary and secondary destination.

For using LOG_ARCHIVE_DEST_n initialization parameter

1. To shut down the database, use SQL*Plus.

SHUTDOWN

2. To select one to ten archiving sites, use the LOG_ARCHIVE_DEST_n initialization option. The LOCATION keyword gives a pathname particular to the operating system. Enter, for example:

LOG_ARCHIVE_DEST_1 = 'LOCATION = /disk1/archive'
LOG_ARCHIVE_DEST_2 = 'LOCATION = /disk2/archive'
LOG_ARCHIVE_DEST_3 = 'LOCATION = /disk3/archive'

Use the SERVICE keyword to indicate a valid net service name from the tnsnames.ora file if you’re archiving to a standby database. Enter, for example:

LOG_ARCHIVE_DEST_5 = 'SERVICE = standby1'

3. Optionally, use %t to include the thread number in the file name, %s to include the log sequence number, and %r to include the resetlogs ID in the LOG_ARCHIVE_FORMAT  initialization option. To pad the file name to the left with zeroes, use capital letters (%T, %S, and %R).

E.g., 

LOG_ARCHIVE_FORMAT = arch_%t_%s_%r.arc

For using LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST

1. To shut down the database, use SQL*Plus.

SHUTDOWN

2. Specify destinations for the LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST parameters (you can also use the ALTER SYSTEM command to dynamically specify LOG_ARCHIVE_DUPLEX_DEST). Enter, for example:

LOG_ARCHIVE_DEST = '/disk1/archive'
LOG_ARCHIVE_DUPLEX_DEST = '/disk2/archive'

3. As mentioned in step 3 for method 1, set the LOG_ARCHIVE_FORMAT initialization parameter.

Conclusion

In conclusion, adopting archive logging in Oracle databases is essential for maintaining data integrity and recovery capabilities. For example, during a major e-commerce sale, the ability to quickly restore the database after a critical failure will help avoid significant revenue loss and maintain customer trust. Furthermore, Hevo simplifies the process of migrating your Oracle data, making it easier to leverage the benefits of archive logging. If you’re looking to ensure your data’s safety and streamline your migration process, consider trying Hevo today with their 14-day free trial!

Frequently Asked Questions