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

LMT AND DMT TABLESPACE

LMT TABLESPACE

The LMT tablespace is implemented by adding EXTENT MANAGEMENT LOCAL clause to 
the tablespace definition.LMT tablespaces automate extent management and remove
the ability to specify the NEXT storage parameter. 

The only exception is when NEXT is used with MINEXTENTS at table creation time.
LMT means Locally Management  system,LMT tablespace size is uniform.
By default uniform size is 1,By default lmt tablespace is Autoallocate.

Auto allocate

SQL> create tablespace india datafile '/u01/app/oracle/oradata/TESTDB/india01.dbf' 
size 50m;

Tablespace created.

Uniform Size

SQL> create tablespace USA datafile '/u01/app/oracle/oradata/TESTDB/USA01.dbf' 
size 50m  extent management local uniform size 512k;

Tablespace created.

Deferred segment creation

SQL> create table india (reg_id number,reg_name varchar2(200));

Table created.

SQL>  select count(*) from dba_segments where segment_name='INDIA';

  COUNT(*)
----------
        0
SQL> select count(*) from dba_extents where segment_name='INDIA';

  COUNT(*)
----------
         0

SQL> insert into india values (5,'tamil');

1 row created.

SQL>  select count(*) from dba_segments where segment_name='INDIA';

  COUNT(*)
----------
         1

SQL>  select count(*) from dba_extents where segment_name='INDIA';

  COUNT(*)
----------
         1

SQL>  create table italy (reg_no number,reg_name varchar2(2000)) 
segment creation immediate;

Table created.


SQL> show parameter deferred_segment_creation;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE

SQL> alter system set deferred_segment_creation=false;

System altered.

SQL>  show parameter deferred_segment_creation;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     FALSE

Extents

Continuous blocks it’s called extents,Data Stored in blocks,blocks Stored in extents.
By default Extents Size 64k.

TYPES OF EXTENTS

Initial          - Initial is First No of transaction by default 100.

Next             - Next no of transaction is 200.
  
Minextents       - minimum number of transactions by default 1.

Maxextents       - maximum number of transactions by default Unlimited.

Pct increase     - pct increase by default 100.

BLOCKS

Smallest storage unit it’s called Blocks.After database creation 
we can specify block size but before database creation we can’t change block size.
block size is 2k,4k,8k,16k,32k. By default Block size is 8k.

Check Block Size in Database

SQL> show parameter db_block_size;

NAME                     TYPE        VALUE
--------------------- ----------- --------------
db_block_size           integer     8192


SQL> select block_size,tablespace_name from dba_tablespaces;

BLOCK_SIZE            TABLESPACE_NAME
----------        ------------------------------
      8192 	        SYSTEM
      8192 		SYSAUX
      8192 		UNDOTBS1
      8192 		TEMP
      8192 		USERS
      8192 		TEST
      8192 		INDIA

create New Tablespace

SQL>  create tablespace spain datafile'/u01/app/oracle/oradata/TESTDB/spain01,dbf'
size 100m blocksize 16k;

Tablespace created.

SQL>  select block_size,tablespace_name from dba_tablespaces;

BLOCK_SIZE        TABLESPACE_NAME
----------      ---------------------
      8192            SYSTEM
      8192            SYSAUX
      8192            UNDOTBS1
      8192  	      TEMP
      8192 	      USERS
      8192 	      TEST
      8192 	      INDIA
     16384 	      SPAIN

Block Utilization Parameters

INITRANS       

MAXTRANS    

PCTUSED        

PCTFREE   

Initans is the initial number of transactions by default 1. 
Maxtrans is Maximum number of Transactions default 255.Inserted data Stored in 
pct used by default allocate space is 89%, in case pct used is full Remaining 
data goes to Next pct used. Update happens in  pct free by default allocate space 10%,
pct used and pct free information stored by block header by default allocated space 1%.

Block-level problems

Row chaining

Row migration

Row chaining 

Inserted data stored in pct used,that pct used is full remaining data goes to next pct 
used it is called Row chaining.Row chaining occurs when a row can't physically fit 
into an Oracle block. 

Another block is required to store the remainder of the row.Chaining can cause serious 
performance problems and is especially prevalent with those storing multimedia data or 
large binary objects (blobs). 

You should pay special attention to the DB_BLOCK_SIZE parameter 
when you create your database.Block sizes of 4 kilobytes or more are the norm, 
not the exception.

Row migration 

Date updates happen for  pct free ,that updated data stored in pct used.pct used is 
full that time remaining data goes to the next pct used that is called row Migration.
Migration of an Oracle row occurs when a row is updated in an Oracle block and the 
amount of free space in the block is not adequate to store all of the row's data. 
The row is migrated to another physical block in the table. 
The problem is that the indexes that refer to the migrated row are still pointing 
to the block where the row used to be, and hence the table reads are doubled. 
Note however that full table scans will scan blocks as they come and will perform the 
same number of reads whether the rows are migrated or not.

 

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