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.

What is Oracle?

Oracle is a relational database management system (RDBMS) that is intended to guarantee that mistakes in a manual database system are kept to a minimum. It’s also multi-platform, meaning it can operate on a variety of operating systems. It is the first commercial relational database management system designed specifically for organizations processing database records.

It’s available in a variety of configurations, including on-premises, cloud, and hybrid. Besides, depending on your requirements, the application is available in a variety of versions: Standard Edition, Standard Edition One, Enterprise Edition, Express Edition, and Personal Edition. To interact with the databases, Oracle supports SQL as a query language. Oracle is also compatible with operating systems like Windows, Unix, Linus, and macOS.

Key Features of Oracle Database

Here are some features of Oracle Database:

• Client/Server Architecture: Oracle is built on Distributed Systems, which offer the same level of data consistency and transparency as non-distributed systems, as well as all of the benefits of a local Database Management System. Oracle implements a Client/Server Model to take full use of this distributed architecture. As a result, Oracle enables the separation of processing into Client and Server application applications.
• Portability: Oracle is readily portable to work on a broad range of operating systems. As a result, any Oracle-based application can be ported to any other operating system without requiring major modifications. It can be ported over 100 different hardware platforms and almost 20 networking protocols. 
• Analytics: OracleDB implements OLAP (Oracle Analytic Processing) and Oracle Advanced Analytics to give solutions for analytical computations on corporate data.
• Availability: The OracleDB database is never down or unavailable. It provides and maintains database availability 24 hours a day, seven days a week. Oracle Data Guard and Real Application Cluster (RAC) are responsible for OracleDB’s high availability. In RAC, one cluster node serves as a primary database, while the other serves as a secondary database, which is a duplicate of the primary database. Data is available on another cluster node in the event of a failure, making the system highly available and ensuring that it is constantly up and running.
• Backup and Recovery: Oracle has advanced recovery tools that allow users to recover data from practically any type of database loss. In the event of a breakdown, the database is quickly restored, guaranteeing high availability. Also, while the compromised sections of the database are being retrieved, the unharmed parts of the database are still accessible.
• Database Integrity: By imposing Data Integrity, Oracle can assure the consistency of all data saved. This saves a significant amount of engineering resources that would have been used otherwise.
• Security: Oracle is the preferred database for most businesses, and as a result, it houses a lot of sensitive data. As a result, it ensures that strong security features are available to restrict and monitor data access and usage. It offers superior isolation solutions ranging from separate key encryption management to pluggable databases. However, you will need to upgrade to a higher-cost edition for more comprehensive security features, such as audits and monitoring.

Redo Log In Oracle Database

The redo log, which comprises two or more preallocated files containing all changes made to the database as they happen, is the most important structure for recovery operations. Every Oracle Database instance contains a redo log that protects the database in the event of an instance failure.

The redo log for each database instance is also called a redo thread when speaking of numerous database instances. In most cases, only one database instance accesses an Oracle Database, hence only one thread is available. However, in an Oracle Real Application Clusters environment, two or more instances can access the same database at the same time.  Each instance has its redo thread, reducing contention for a single set of redo log files and removing the possibility of a performance bottleneck.

It is important to note that there are a lot of redo records in the redo log files. A redo record, which is sometimes called a redo entry, is made up of a group of change vectors, each of which represents a single database block revision.

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, in an Oracle database, there are two types of logging modes: Noarchivelog and Archivelog. In Noarchivelog mode, filled online redo logs are not archived instead they will be overwritten. And in Archivelog, after the online redo logs are filled, they will migrate to the archive location.

Archive Logging Oracle

An Archive Logging Oracle redo logging file is a duplicate of one of the filled members of a redo log group. It contains the redo entries as well as the unique log sequence number of the same redo log group member. When the database is in ARCHIVELOG mode, the log writer process (LGWR) cannot reuse and thereby overwrite a redo log group until it has been archived.

ARCHIVELOG mode is required for making online backups and recovering specific types of databases. Using offline or online backups, the user can accomplish complete and point-in-time recovery from media (disk) failures by configuring the database to run in ARCHIVELOG mode. If ARCHIVELOG mode is turned off, the database can be restored from a backup in the event of a failure, but it cannot be rolled back to the point before the failure.

You can leverage Archive Logging Oracle to perform the following:

• Retrieve data from a database.
• Updating a backup database.
• Get information about the history of a database using the LogMiner utility.
• Allows you to run RMAN Online Backup, Flashback Database, and User Managed Hot Backup.

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

The archiving of the redo log is enabled when you operate a database in ARCHIVELOG mode. If you have an Archive Logging Oracle log, you can utilize a backup taken when the database is open and the system is in regular operation. While NOARCHIVELOG mode protects a database against instance failure, ARCHIVELOG mode can also guard against media failure.

To become more and more efficient in handling your Databases, it is preferable for you to integrate them with a solution that you can carry out Data Integration and Management procedures for you without much ado and that is where Hevo Data, a Cloud-based ETL Tool, comes in. Hevo Data supports 100+ Data Sources and helps you transfer your data from these sources (including Oracle) to your Data Warehouses in a matter of minutes, all this, without writing any code!

Visit our Website to Explore Hevo

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. Hevo offers plans & pricing for different use cases and business needs, check them out!

Share your experience of learning Archive Logging Oracle in the comments section below!