How to enable Archivelog

First we need to check does our database run on archive log mode or not. Check if the database is in Archivelog mode.

SQL> select log_mode from sys.v$database;

- Advertisement -

· Execution statements to change the database to Archivelog mode.

– The path in which the Archivelog files will be generated must be included. The following statement modifies the database parameter file. The change is applied once the database is restarted.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION =/archivelog/dbametrix/' COMMENT = 'Modification to save the Archive logs' SCOPE = SPFILE;

– The following statement extracts the value assigned to the log_archive_dest_1 parameter
select * from v$parameter where name = 'log_archive_dest_1';

- Advertisement -

Steps to follow to change the database to Archivelog mode.

– Download the database, start the database in MOUNT mode.
– Alter the database modifying it to ARCHIVELOG mode
– Modify the Log_archive_format parameters with the name format with which the ARCHIVELOG files will be generated.
– After the previous processes, the database must be opened

shutdown immediate;
startup mount;
alter database archivelog;
alter system set log_archive_format = 'arc_dbametrix% t_% s_% r.arc' scope = spfile;
alter database open;

– The LOG_ARCHIVE_START parameter must not be modified in Oracle. This parameter must have a value of TRUE when the database is on Oracle9i.

SQL> alter system set LOG_ARCHIVE_START = TRUE SCOPE = spfile;

– The script allows to validate the mode in which the database is located.

SQL> select log_mode from v$database;

– The script supplies the information about the current mode of the database, the path in which the Archivelog files are stored. the current sequence in which the redologs are found and the next sequence.

SQL> archive log list;

– The Script allows a rotation of the redologs forcing the database to generate an Archivelog in case of being in Archivelog mode.

SQL> alter system switch logfile;

– Execution statements to change the database to NOArchivelog mode.
– Steps to follow to change the database to NOArchivelog mode.
– Download the database, start the database in MOUNT mode.

SQL> shutdown immediate;
SQL> startup mount;

– Alter the database by modifying it to NOARCHIVELOG mode

SQL> alter database noarchivelog;

– The log_archive_start parameter is no longer used as of Oracle, therefore it is modified to assign it a default value.

SQL> alter system reset log_archive_start scope = spfile;

8.1 ABOUT REDOLOGS

Recommendations.

By organization, the databases should always keep the consecutive groups starting from 1. Do not multiplex the redologs in ASM and filesystem systems at the same time, since it deteriorates the response.

If ASM is available leave them on this file system.

– The following statements allow you to validate the information in the RedoLog files.
The following statement provides information on the structure of the RedoLog Groups.
Provides group number, sequence number for each RedoLog group, number of group members, and current group status.

SQL> SELECT * from V$LOG;

– The following sentence provides information about the RedoLog.
Provides the group number to which the RedoLog belongs, the type of Log file, the path and name of the RedoLog

SQL> SELECT * from V$LOGfile;

– The following statement forces the database to change or rotate the RedoLog file it is using.

SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM CHECKPOINT;

– The following sentence allows creating a RedoLog group with their respective members and sizes. The group number will be assigned by the database.
It is recommended to create at least 5 groups of 250MB each.

SQL> ALTER DATABASE ADD LOGFILE ('LOCATION_1', 'LOCATION_2') SIZE 250M;

– ELIMINATION OF UNUSED REDOLOG

The following statement allows you to remove a specific group from RedoLog.
It is then necessary to delete the physical RedoLog files on the server

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert. This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.

- Advertisement -