How to Setup Oracle Database In Archivelog Mode

In this Oracle database tutorial, I will explain to you about what is archivelog mode and how to setup Oracle database archivelog mode.

What is an archivelog mode in Oracle Database?

In Oracle, database Archivelog mode enables complete recovery from disk failure because all changes made to the database are permanently stored in an archivedlog. These log files are used with backups of data files to perform media recovery.

• For example-
• ABVC company has set up a database where they were storing different data and company profiles suddenly due to a heavy flood their whole database was lost.
• Because they didn’t create a backup and they asked us to recreate the database with a backup so we recreated the database and added a backup to the database using Archivelog which created space and stored the different data in it.
• The ABVC company is now running well even when there is a flood or any kind of natural disaster they have a backup of their database so that they can retrieve all the lost data and continue working.

How to setup Oracle Database in Archivelog Mode

We can setup Oracle Database in Archivelog by using two ways

• Using Oracle SQL Developer Tool
• Using Command Prompt

Setup Oracle Database in Archivelog Mode using Oracle SQL Developer Tool

Let us see how to set up archivelog mode in the Oracle database using Oracle SQL Developer Tool.

Step-1:

Open Oracle Database System and make a new connection as shown below

• Click on the green PLUS icon

• After which, a New/select database connection window will open where we have to give Name -> then select the role as SYSDBA -> Username -> Password-> Declare Service Name (ORCL) and click on Test.

• The test result is successful, we have to click on Connect.

Step-2:

Then we can see the oracle sql developer screen which has appeared where we have to run the query.

Step-3:

First, we have to check the Archivelog list by using this query

archive log list;

 This query explains to us whether the database is in archive mode or not.

Step-4:

Write the query as follows

 Shutdown
 Startup mount
 Alter database archivelog;
 alter database open;

In here

• Shutdown: this means it will shut down the database.
• Startup mount: means that the database will be in mount mode(mount mode refers to the control files which are accessed for the first time).
• Alter database archivelog: means setting the database in archive log mode(alter means to add or modify the existing tables or columns).
• Alter database open: this means that we are finally opening the database.

Step-5:

We get the output as

Database log mode                                                  Archive log Mode
Automatic archival                                                   Enabled
Archive destination                                                  USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence                                                 5
Next log sequence to archive                                             7
Current log sequence                                                          7

Setup Oracle Database in Archivelog Mode using Command Prompt

Let us see how to set up archivelog mode in the Oracle database using the Command prompt.

Step-1:

First, we have to check the Archivelog list by using this query.

archive log list;

 This query explains to us whether the database is in archive mode or not.

• Then you will see the output as

Step-2:

After which we have to write the command as follows

 Shutdown or shut immediate

• This means it will shutdown the database.

Startup mount

• This means that the database will be in the mount mode(mount mode refers to the control files which are accessed for the first time)

Alter database archivelog;

• This means set the database in archive log mode(alter means to add or modify the existing tables or columns)

alter database open;

• This means that we are finally opening the database.

Then again on checking the query, we get the output as

archive log list;

Conclusion

In this Oracle database tutorial, we understood what is archivelog mode, and how to set up archivelog mode in the Oracle database.

You may also like:

• How To Check Oracle Database Read Write Mode
• How to Check and Set Database Timezone in Oracle