Put a DB in Archivelog mode in Oracle

Hello everyone, today we are going to propose a task that when we have an Oracle DB we will have to perform, or at least think about it, which is how to put a DB in Archivelog mode . With this procedure, as with the procedure of change location of 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 impact on the applications that connect to the database.

Database Archivelog mode

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

Put a DB in GPSOS Archivelog mode

With this procedure, a DB can only be recovered in the event of a crash as far as the information is overwritten, that is, the changes that have been saved in the existing Redo Log files. In order to recover the database in the event of a crash, the Archivelog mode of the database is used. In this mode, a copy of each Redo Log file is made when its writing to another location is completed to save it in case it is necessary in the future for said recovery.

How the database is configured

We have several ways of knowing if the database is in Archivelog mode or not, by querying the views of the catalog of the same database.

The spine ARCHIVER In views v $ instance and LOG_MODE in v $ database they tell us the way the DB is operating

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_ STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- -------- ------------ --- ---------- ------- ---------------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
              1 orcl
pruhost
12.2.0.1.0        25/08/20 OPEN         NO           1 STOPPED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO
SQL> select name, log_mode from v$database;

NAME      LOG_MODE
--------- ------------
ORCL     NOARCHIVELOG

There is also a command that tells us the mode of the DB 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 DB in Archivelog mode

The archivelog mode of the DB can be specified at the time of creation of the instance with the DBCA.

To put the DB 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.
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 DB. To change the mode of the database, it must be in MOUNT mode, so it is necessary to stop it in order to start it in this mode.

SQL> shutdown immediate
Base de datos cerrada.
Base de datos desmontada.
Instancia ORACLE cerrada.
SQL>
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 DB 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

It can be verified that the DB is in Archivelog mode

SQL> select name, log_mode from v$database;

NAME      LOG_MODE
--------- ------------
ORCL     ARCHIVELOG
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 “Archive destination” line, and in this case, the value that this line shows is that the path indicated by the DB_RECOVERY_FILE_DEST parameter is being used. You can check this parameter with the instruction:

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 in the indicated path:

Put a DB 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 it has been useful to you.

Greetings.

Database Team

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 that we 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 a single email per month.

Still do not know Query Performance ? Find out how it can help you in your Oracle environment. More information on their page LinkedIn .

Follow GPS on LinkedIn