Put a database in Archivelog mode in Oracle

Hello everyone, today we are going to propose a task that when we have an Oracle database we will have to perform, or at least think about it, which is how to put a database in Archivelog mode. With this procedure, as with the change of location of the control files, which we discussed a few weeks ago, we must be careful if we do it in production environments, since it also requires a restart of the instance, with the consequent affectation of the applications that connect to the database.

Database Archivelog Mode

The Redo Log files are the files where the changes that occur in the database are automatically saved. These files are used for recovery in case of instance failure. When there is a change in the database, in addition to the mechanisms for the change to be made in the database, it is also written to the Redo log files. The writing of the changes in the redo log files is in series, starting in the first file, and continuing with the following ones. It is cyclical, when you reach the end of the last file, you return to the beginning of the first. This means that the information in the file is overwritten.

Put a database in ARCHIVELOG GPSOS mode

With this procedure, a database can only be recovered in case of a fall to where the information is overwritten, that is, the changes that have been saved in the Redo Log files that exist. To be able to recover the database in case of a fall, the Archivelog mode of the database is used. In this mode, a copy of each Redo Log file is made when it is completed to another location to save it in case it is necessary in the future for such recovery.

How the database is configured

We have several ways to know if the database is in Archivelog mode or not, making queries to the views of the catalog of the same database.

The ARCHIVER column In the v$instance and LOG_MODE views in v$database tell us how the database is operating

SQL> select * from v$instance;

--------------- ----------------
----------------- -------- ------------ --- ---------- ------- ---------------
---------- --- ----------------- ------------------ --------- ---
              1 orcl
pruhost        25/08/20 OPEN         NO           1 STOPPED
SQL> select name, log_mode from v$database;

--------- ------------

There is also a command that tells us the mode of the database without accessing the catalog views.

SQL> archive log list
Modo log de la base de datos              Modo de No Archivado
Archivado automßtico             Desactivado
Destino del archivo            USE_DB_RECOVERY_FILE_DEST
Secuencia de log en lÝnea mßs antigua     5
Secuencia de log actual           7

Put a database in Archivelog mode

You can specify the archivelog mode of the database at the time of instance creation with the DBCA.

To put the database in archivelog mode, it is necessary to specify a path where the archivelog files should be saved once they are created. Up to 30 copies of the file can be stored by specifying different paths.

As you can see from the image, no path is specified in the “Edit Archive Mode Parameters” dialog box.
In this case, the generated archivelog files are stored in the Fast_Recovery_Area or Flash_Recovery_Area in versions prior to 11g.

It can also be done manually, after the creation of the database. To change the mode of the database, it has to be in MOUNT mode, so it is necessary to stop it to be able to start it in this mode.

SQL> shutdown immediate
Base de datos cerrada.
Base de datos desmontada.
Instancia ORACLE cerrada.
SQL> startup mount
Instancia ORACLE iniciada.

Total System Global Area 1068937216 bytes
Fixed Size                  2288080 bytes
Variable Size             671090224 bytes
Database Buffers          390070272 bytes
Redo Buffers                5488640 bytes
Base de datos montada.

Once in MOUNT mode, the database is put into archivelog mode and opened so that users can reconnect.

SQL> alter database archivelog;

Base de datos modificada.

SQL> alter database open;

Base de datos modificada.

Check Database Mode

you can verify that the database is in archivelog mode

SQL> select name, log_mode from v$database;

--------- ------------
SQL> archive log list
Modo log de la base de datos              Modo de Archivado
Archivado automßtico             Activado
Destino del archivo            USE_DB_RECOVERY_FILE_DEST
Secuencia de log en lÝnea mßs antigua     5
Siguiente secuencia de log para archivar   7
Secuencia de log actual           7

The destination of the Archivelog files is indicated in the “File destination” line, and in this case, the value shown by this line is that the path indicated by the DB_RECOVERY_FILE_DEST parameter is being used. You can check that parameter with the statement:

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      F:\fast_recovery_area
db_recovery_file_dest_size           big integer 4182M

It is verified that the files are being copied to the indicated path:

Put a database in Archivelog mode

If you want to change the destination path of the files or add a new destination (up to 30) we can execute the following:

ALTER SYSTEM SET log_archive_dest_1='LOCATION=F:\ORCL';

And we check that the files are generated in the new path:

We hope you found it useful.


Database computer

More info: https://franhormigo.wordpress.com/2017/06/17/activar-desactivar-y-comprobar-el-modo-archivelog/

If you have questions about its administration or related to Oracle or SQL Server, do not hesitate to contact us who will be happy to help!!

If you don’t want to miss tricks like this and stay up to date with this world, subscribe to our newsletter. We will keep you informed with only one email per month.

Comments are closed.