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.
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 NOSQL> select name, log_mode from v$database;
NAME LOG_MODE
--------- ------------
ORCL NOARCHIVELOGThere 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 7Put 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 ARCHIVELOGSQL> 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 7The 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 4182MIt is verified that the files are being copied in the indicated path:
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