REDO LOG FILE

Redo log files are filled with redo records. A redo record also called a redo entry, is made up of a group of change vectors, each of which is a description of a change made to a single block in the database. For example, if you change a salary value in an employee table, you generate a redo record containing change vectors that describe changes to the data segment block for the table, the undo segment data block, and the transaction table of the undo segments.

Redo entries record data that you can use to reconstruct all changes made to the database, including the undo segments. Therefore, the redo log also protects rollback data. When you recover the database using redo data, the database reads the change vectors in the redo records and applies the changes to the relevant blocks.

SQL> select * from v$log;

SQL> select group#,status,sequence# from v$log;

    GROUP# STATUS            SEQUENCE#
---------- ---------------- ----------
         1 CURRENT                  91
         2 INACTIVE                 89
         3 INACTIVE                 90

SQL> select * from v$logfile;

Active (Current) and Inactive Redo Log Files

Oracle Database uses only one redo log file at a time to store redo records written from the redo log buffer. The redo log file that LGWR is actively writing to is called the current redo log file.

Redo log files that are required for instance recovery are called active redo log files. Redo log files that are no longer required for instance recovery are called inactive redo log files.

If you have enabled archiving (the database is in ARCHIVELOG mode), then the database cannot reuse or overwrite an active online log file until one of the archiver background processes (ARCn) has archived its contents. If archiving is disabled (the database is in NOARCHIVELOG mode), then when the last redo log file is full, LGWR continues by overwriting the first available active file.

Add New redo log file

SQL> alter database add logfile group 4 '/u01/app/oracle/oradata/TESTDB/redo04.dbf' 
size 100m;

Database altered.

Add Member to an existing group

SQL> alter database add logfile member '/u01/app/oracle/oradata/TESTDB/redo04b.log' 
to group 4;

Database altered.

Drop member

SQL> alter database drop logfile member '/u01/app/oracle/oradata/TESTDB/redo04b.log';

Database altered.

Drop Logfile Group

SQL> alter database drop logfile group 4;

Database altered.


SQL> select group#,status from v$log;

    GROUP#                STATUS
----------            ----------------
         1               CURRENT
         2               INACTIVE
         3               INACTIVE

Adding Two  member

SQL> alter database add logfile group 4 ('/u01/app/oracle/oradata/TESTDB/redo04a.log',
'/u01/app/oracle/oradata/TESTDB/redo04C.log') size 100m;

Database altered.

SQL> select group#,member from v$logfile;

    GROUP#         MEMBER
----------       ---------------------------                       
         4    /u01/app/oracle/oradata/TESTDB/redo04a.log

         4     /u01/app/oracle/oradata/TESTDB/redo04C.log

Log Switches and Log Sequence Numbers

A log switch is a point at which the database stops writing to one redo log file and begins writing to another. Normally, a log switch occurs when the current redo log file is completely filled and writing must continue to the next redo log file. However, you can configure log switches to occur at regular intervals, regardless of whether the current redo log file is completely filled. You can also force log switches manually.

Oracle Database assigns each redo log file a new log sequence number every time a log switch occurs and LGWR begins writing to it. When the database archives redo log files, the archived log retains its log sequence number. A redo log file that is cycled back for use is given the next available log sequence number.

Each online or archived redo log file is uniquely identified by its log sequence number. During a crash, instance, or media recovery, the database properly applies to redo log files in ascending order by using the log sequence number of the necessary archived and redo log files.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

ARCHIVELOG MODE

How to Enable archive log mode

Once you have configured the flash recovery area, you can put the database in ARCHIVELOG mode. Unfortunately, this requires that the database be shutdown first with the shutdown command (however, from earlier in the chapter, we note that shutdown immediate is the best option). Once you have shutdown the database, you will start the database in mount Stage with the startup mount command. Then put the database in ARCHIVELOG mode, and finally open the database. Here is an example of how this all works from the command line.

SQL> archive log list;


Database log mode               No Archive Mode
Automatic archival              Disabled
Archive destination             d:oracleoradataDB10Garchive
Oldest online log sequence      427
Current log sequence            429

SQL> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1610609200 bytes
Fixed Size               8897072 bytes
Variable Size            385875968 bytes
Database Buffers         1207959552 bytes
Redo Buffers             7876608 bytes
Database mounted.


SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;

Database log mode                  Archive Mode
Automatic archival                 Enabled
Archive destination                d:oracleoradataDB10Garchive
Oldest online log sequence         426
Next log sequence to archive       427
Current log sequence               428

How to Disable archive log mode 

SQL> archive log list;

Database log mode                Archive Mode
Automatic archival               Enabled
Archive destination              d:oracleoradataDB10Garchive
Oldest online log sequence       426
Next log sequence to archive     427
Current log sequence             428


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1610609200 bytes
Fixed Size               8897072 bytes
Variable Size            385875968 bytes
Database Buffers         1207959552 bytes
Redo Buffers             7876608 bytes
Database mounted.



SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.


SQL> archive log list;

Database log mode                NoArchive Mode
Automatic archival               Enabled
Archive destination              d:oracleoradataDB10Garchive
Oldest online log sequence       426
Next log sequence to archive     427
Current log sequence             428

Change Archive log mode and Destination in oracle 19c

[oracle@training ~]$ export ORACLE_SID=training1

[oracle@training ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 28 23:30:16 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 2432695144 bytes
Fixed Size                  8899432 bytes
Variable Size             536870912 bytes
Database Buffers         1879048192 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     89
Current log sequence           91


SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - 
Production Version 19.3.0.0.0

[oracle@training ~]$ mkdir -p /u01/ARC_BKP

[oracle@training ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 28 23:38:09 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

To check the current archiving location

SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST

NAME                       TYPE            VALUE
---------------------  ---------------   --------------
db_recovery_file_dest       string      /u01/app/oracle /fast_recovery_area
                                                 
db_recovery_file_dest_size  big integer  8256M

SQL> alter system set DB_RECOVERY_FILE_DEST='/u01/ARC_BKP';

System altered.

SQL> alter system set log_archive_dest=’/u01/ARC_BKP’;

System altered.

SQL> alter database close;

Database altered.

SQL> alter database archivelog;

Database altered.


SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 -
 Production Version 19.3.0.0.0
[oracle@training u01]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 28 23:52:05 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.


SQL> startup
ORACLE instance started.

Total System Global Area 2432695144 bytes
Fixed Size                  8899432 bytes
Variable Size             536870912 bytes
Database Buffers         1879048192 bytes
Redo Buffers                7876608 bytes

Database mounted.

Database opened.

SQL> archive log list;
Database log mode                Archive Mode
Automatic archival               Enabled
Archive destination              /u01/ARC_BKP
Oldest online log sequence       89
Next log sequence to archive     91
Current log sequence             91

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8