Skip to main content
๐Ÿ““ Tibero Online Manual Click here ๐Ÿ““

Search

Welcome to Tibero GTS!

Archive Log Usage Analysis

Document Type | Technical Information

Category | Administration

Applicable Product Version | Tibero 7.2.3

Document Number | TADTI113

Overview

This document guides you on how to analyze archive log usage and identify the causes of excessive archive log generation.

  • Test Environment:  Tibero 7.2.3

 

Method

1. Check TIBERO Configuration and Parameters

(1) Check TIBERO Configuration

Check TIBERO configuration such as SINGLE / TSC / TAC via the $TB_SID.tip file

  • In TAC, archive logs are recorded per THREAD (per node)
  • In SINGLE, archive logs are recorded only on a single node
  • Even when DATAFILE is used as RAW-DEVICE, archive logs are created in the FILE SYSTEM, so it is the same

 

(2) Check REDO Log Configuration

Check REDO LOG THREAD / GROUP / SIZE

SQL > select thread#, group#, bytes / 1024 / 1024 as "redo_size(MB)" from v$log order by thread#

   THREAD#     GROUP# redo_size(MB)
---------- ---------- -------------
        0          0           100
        0          1           100

2 rows selected.

 

(3) Check Archive Log Parameters

Check parameters related to archive log path / multiplexing / NetBackup / overwrite

SQL > select name, value from V$PARAMETER where name like '%LOG_ARCHIVE%';

NAME                           VALUE
------------------------------ --------------------------------------------------
LOG_ARCHIVE_DEST               /tibero/tibero7/database/t2/archive/
USE_NBU_FOR_ARCHIVELOG         NO
STANDBY_LOG_ARCHIVE_FORMAT     st-t%t-r%r-s%s.arc
NBU_ARCHIVE_RETRY_CNT          2
NBU_ARCHIVE_POLICY_NAME        TIBERO_ARCHIVE_POLICY
NBU_ARCHIVELOG_SEARCH          NO
LOG_ARCHIVE_RETRY_TIME         3
LOG_ARCHIVE_OVERWRITE          NO
LOG_ARCHIVE_FORMAT             log-t%t-r%r-s%s.arc
LOG_ARCHIVE_DEST_9
LOG_ARCHIVE_DEST_8
LOG_ARCHIVE_DEST_7
LOG_ARCHIVE_DEST_6
LOG_ARCHIVE_DEST_5
LOG_ARCHIVE_DEST_4
LOG_ARCHIVE_DEST_3
LOG_ARCHIVE_DEST_2
LOG_ARCHIVE_DEST_1
FLASHBACK_LOG_ARCHIVE_FORMAT   fb-t%t-r%r-s%s.arc
FLASHBACK_LOG_ARCHIVE_DEST     /tibero/tibero7/database/t2/fb_archive/

 

2. TIBERO Disk Usage

(1) Check TIBERO Process FD

Check processes that are accessing the archive logs

Note
Archive logs should not be opened by TIBERO processes except during redo log switches and recovery.

## Check FD using lsof
$ lsof -u tibero |grep tbsvr |grep arc

## If lsof is not available, check processes opening specific files with fuser
$ fuser  *.arc

 

(2) Check TIBERO Disk Usage

Check the size of the archive log directory

Note
In TAC, archive logs are created separately per THREAD (node), so check the path for each node.

## Check archive log path for THREAD#0
SQL > show parameter LOG_ARCHIVE_DEST
----------------------------------------------------------------------------------- -------- ---------------------------------------------------------------------------------
FLASHBACK_LOG_ARCHIVE_DEST                                                          DIRNAME  /tibero/tibero7/database/t2/fb_archive/
LOG_ARCHIVE_DEST                                                                    DIRNAME  /tibero/tibero7/database/t2/archive/

[tibero@devdb archive]$ df -h /tibero/tibero7/database/t2/archive/
Filesystem           Size  Used Avail Use% Mounted on
/dev/mapper/ol-root   44G   37G  7.3G  84% /



[tibero@devdb archive]$ pwd
/tibero/tibero7/database/t2/archive
[tibero@devdb archive]$ du -skh
82M

 

3. TIBERO Archive Log Generation Frequency

(1) Check via V$ARCHIVED_LOG View

Note
In TAC, archive log information from all nodes is included.

## Check by day
SQL > select to_char(first_time, 'YY/MM') as archived_date,
 count(*)
        from v$archived_log
    group by to_char(first_time,'YY/MM');
       

ARCHIVED_DATE   COUNT(*)
------------- ----------
25/11                 11

## Check by hour
SQL > select to_char(first_time, 'YY/MM/DD hh24') as archived_date, count(*)
        from v$archived_log
    group by to_char(first_time,'YY/MM/DD hh24');

ARCHIVED_DATE   COUNT(*)
------------- ----------
25/11/21              11

 

(2) Analysis Using iLOG

  • In TAC, iLOG occurs per node, so analysis must be performed per node.
  • Use the tbiv -r option to specify a particular time range. 
$ tbiv -s -t                                   # Analyze active iLOG
$ tbiv -s -t backup_0908_093838/               # Analyze a specific backup directory
$ tbiv -s -t */*                               # Analyze all backup directories 
$ tbiv -s -t -r 20220908000000 20221109100000  # Add -r option to analyze active or backup iLOG within range

# Monthly analysis of all backup directories
$ tbiv -s -t */* 2>/dev/null \
|grep "LOG GROUP" \
|grep "ARCHIVED" \
|sed 's/\// /g' \
|awk '{print $1}' \
|sort |uniq -c 

# Daily analysis of a specific backup directory
$ tbiv -s -t  backup_0908_093838/ 2>/dev/null \
|grep "LOG GROUP" \
|grep "ARCHIVED" \
|awk '{print $1}' \
|sort |uniq -c 

# Hourly analysis of active iLOG
$ tbiv -s -t  2>/dev/null \
|grep "LOG GROUP" \
|grep "ARCHIVED" \
|sed 's/:/ /g'  \
|awk '{print $1" "$2}' \
|sort |uniq -c
  • When there are many iLOG files, the tbiv utility may fail with a segmentation fault; it is recommended to use the -r option (range) and check the iLOG backup directories to output only the necessary analysis range.
  • If the open files limit via ulimit -a is low, errors may occur due to file open count limits when running the tbiv utility.

 

4. Check DML via TIBERO TPR

Check DML Usage

Check the usage of INSERT / DELETE / UPDATE in TPR

Note
Only available within the TPR_SNAPSHOT_RETENTION period.

## Check TPR extractable range 
SQL > alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
SQL > select 
thread#, min(begin_interval_time), min(end_interval_time)
        from 
_tpr_snapshot
    group by 
thread#;

## Extract TPR for all instances
SQL > alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
SQL > exec dbms_tpr.report_text('date', 'date');

## Extract TPR for specific instance
alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
exec dbms_tpr.report_text( 'date', 'date',instance_no=>'0');
exec dbms_tpr.report_text( 'date', 'date',instance_no=>'1');

## Check DML counts, output result: DML TYPE  count size time
grep -E  "DML " TPR_file |grep -v "time"

  • TPR information can only be obtained from data remaining in the TPR_SNAPSHOT table, so it has limited use.

    Since TPR contains performance information related to REDO SWITCH, the usable information from TPR for archive usage analysis is mainly the count of DML operations.

 

5. Analyze Objects with High TIBERO Archive Log Usage

(1) Extract Archive Log Dump

Analyze specific archive logs at the time of a log switch

SQL > alter system dump logfile 'archive_log_file[absolute_path]';
                              or
[from archive log path]
$ tblodump archive_log_file >> [log_dump_output_filename]

 

(2) Analyze Archive Log Dump File

# Output result: cnt=frequency smgt=segment ID
$ grep -E "op=TD_DBLK" dump_filename \
|grep -v "UNDO" \
|sed 's/=/ /g' \
|awk '{print $NF}' \
|sort -n \
|uniq -c \
|sort -r

Example result)
1003 [0|00_00010492]
 951 [0|00_00010493]
 841 [0|00_00001315]
 
 ...

 

(3) Identify OBJECT by Segment ID from Dump Analysis Result

SQL > select 
owner, object_name, object_type, object_id 
        from 
dba_objects
       where
 object_id in (segment_IDs);

Note

To reduce archive log usage, it is necessary to have a high understanding of business services and related applications for changes such as DML style modification / batch job adjustments / data deletion methods and the involvement of TIBERO engineers is limited. 

As a basic recommendation, it is advised to modify policies to shorten the archive log backup interval.

 

 

 

Related to