Chủ Nhật, 29 tháng 11, 2020

[VIP5] Quản lý Online Redo Log Files trong Oracle Database từ A-Z

--1. Check

set echo off
set feedback off
set linesize 120
set pagesize 35
set trim on
set trims on
set lines 120
col group# format 999
col thread# format 999
col member format a70 wrap
col status format a10
col archived format a10
col fsize format 999 heading "Size (MB)"

select l.group#, l.thread#,
f.member,
l.archived,
l.status,
(bytes/1024/1024) fsize
from
v$log l, v$logfile f
where f.group# = l.group#
order by 1,2

SELECT * FROM V$LOG

--where status in ('INACTIVE','ACTIVE','CURRENT')

order by group#;

SELECT thread#, count(*) FROM V$LOG group by thread#;

SELECT * FROM V$LOGFILE order by group#;

SQL> COL DAY FORMAT a15;

SQL> COL HOUR FORMAT a4;

SQL> COL TOTAL FORMAT 999;

SELECT TO_CHAR(FIRST_TIME,'YYYY-MM-DD') DAY,

TO_CHAR(FIRST_TIME,'HH24') HOUR,

COUNT(*) TOTAL

FROM V$LOG_HISTORY

WHERE first_time >= sysdate - 1

GROUP BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD'),TO_CHAR(FIRST_TIME,'HH24')

ORDER BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD'),TO_CHAR(FIRST_TIME,'HH24')

desc;

-- Theo doi archived log sinh ra

select trunc(completion_time), round(sum(blocks*block_size)/1024/1024/1024,2) "Archived Log GB" from V$ARCHIVED_LOG

where trunc(completion_time) >= trunc(sysdate-90)

--and trunc(completion_time)>= to_date(trunc(sysdate),'dd/mm/yyyy')

and dest_id=1

group by trunc(completion_time)

order by trunc(completion_time) desc;

-- Tong Archived log sinh ra theo gio theo ngay

select to_char(next_time,'YYYY-MM-DD hh24') Hour, round(sum(size_in_byte)/1024/1024,2) as size_in_mb, count(*) log_switch from (

select thread# ,sequence#, FIRST_CHANGE#,blocks*BLOCK_SIZE as size_in_byte, next_time

from v$archived_log where name is not null group by thread# ,sequence#, FIRST_CHANGE#,blocks*BLOCK_SIZE, next_time)

group by to_char(next_time,'YYYY-MM-DD hh24') order by 1 desc;

--Achived log sinh ra theo gio moi ngay

select

to_char(COMPLETION_TIME,'YYYY-MM-DD') day,

to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'00',1,0)),'999') "00h",

to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'01',1,0)),'999') "01h",

to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'02',1,0)),'999') "02h",

to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'03',1,0)),'999') "03h",

to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'04',1,0)),'999') "04h",

to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'05',1,0)),'999') "05h",

to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'06',1,0)),'999') "06h",

to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'07',1,0)),'999') "07h",

to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'08',1,0)),'999') "08h",

to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'09',1,0)),'999') "09h",

to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'10',1,0)),'999') "10h",

to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'11',1,0)),'999') "11h",

to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'12',1,0)),'999') "12h",

to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'13',1,0)),'999') "13h",

to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'14',1,0)),'999') "14h",

to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'15',1,0)),'999') "15h",

to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'16',1,0)),'999') "16h",

to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'17',1,0)),'999') "17h",

to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'18',1,0)),'999') "18h",

to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'19',1,0)),'999') "19h",

to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'20',1,0)),'999') "20h",

to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'21',1,0)),'999') "21h",

to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'22',1,0)),'999') "22h",

to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'23',1,0)),'999') "23h",

round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024,0)||' GB' "Total GB in a day",COUNT(*) "Total switch log in a day"

from v$archived_log

where to_date(COMPLETION_TIME) > sysdate-31

group by to_char(COMPLETION_TIME,'YYYY-MM-DD')

order by day desc;

Các view khác:

v$thread

V$ARCHIVE_DEST

V$LOG_HISTORY

V$DATABASE

--2. Add log file vào redo log group

--ADD LOG GROUP VỚI ĐỦ 2 MEMBER

-- Kiểm tra dung lượng trước khi add
-- File system
# df -h

--Chú ý: Thread 1 là Instance 1 của RAC, thread 2 là Instance 2 của RAC; còn single instance thì chỉ có thread 1

alter database add logfile thread 1 group 1 ('/u09/oracle/data/DBAViet/redo01a.log','/u10/oracle/data/DBAViet/redo01b.log') size 1G;

alter database add logfile thread 1 group 2 ('/u09/oracle/data/DBAViet/redo02a.log','/u10/oracle/data/DBAViet/redo02b.log') size 1G;

alter database add logfile thread 1 group 3 ('/u09/oracle/data/DBAViet/redo03a.log','/u10/oracle/data/DBAViet/redo03b.log') size 1G;

alter database add logfile thread 2 group 11 ('/u09/oracle/data/DBAViet/redo11a.log','/u10/oracle/data/DBAViet/redo12b.log') size 1G;

alter database add logfile thread 2 group 12 ('/u09/oracle/data/DBAViet/redo12a.log','/u10/oracle/data/DBAViet/redo12b.log') ' size 1G;

alter database add logfile thread 2 group 13 ('/u09/oracle/data/DBAViet/redo13a.log','/u10/oracle/data/DBAViet/redo13b.log') size 1G;

(có thể dung lượng nhỏ hơn, tùy DB:

alter database add logfile thread 2 group 13 ('/u09/oracle/data/DBAViet/redo13a.log','/u10/oracle/data/DBAViet/redo13b.log') size 100M;)

-- ADD LOG MEMBER TỚI GROUP CÓ SẴN

ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/fast_recovery_area/db12c/DB12C/onlinelog/redo01b.log' TO GROUP 1

ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2c.rdo'
    TO ('/oracle/dbs/log2a.rdo', '/oracle/dbs/log2b.rdo');

-- ASM

--Kiểm tra dung lượng diskgroup

select * from v$asm_diskgroup;

--Chú ý: Thread 1 là Instance 1 của RAC, thread 2 là Instance 2 của RAC; còn single instance thì chỉ có thread 1

alter database add logfile thread 1 group 1 ('+DATA','+RECO') size 1G;

alter database add logfile thread 2 group 11 ('+DATA','+RECO') size 1G;

alter database add logfile thread 1 group 2 ('+DATA','+RECO') size 1G;

alter database add logfile thread 2 group 12 ('+DATA','+RECO') size 1G;

alter database add logfile thread 1 group 3 '+DATA' size 1G;

alter database add logfile thread 2 group 13 '+DATA' size 1G;

-- ADD LOG MEMBER TỚI GROUP CÓ SẴN

ALTER DATABASE ADD LOGFILE MEMBER '+RECO' TO GROUP 1

Note: Chú ý nên sử dụng OMF

DÙNG OMF SINH ONLINE REDO LOG VỚI FILE SYSTEM

-- File System
DB_CREATE_ONLINE_LOG_DEST_1='/u02/oradata'
DB_CREATE_ONLINE_LOG_DEST2 ='/u03/oradata'

-- File System
ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_1='/u02/oradata';
ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST2 ='/u03/oradata';

Khi đó tạo online redo log: ALTER DATABASE ADD LOGFILE GROUP 1 THREAD 1 size 1G sẽ tạo ra 1 member nằm ở /u02/oradata và /u03/oradata (với file system)

Dùng OMF SINH ONLINE REDO LOG ASM

-- ASM: Có thể bỏ qua với Oracle DB 12c trở lên
DB_CREATE_ONLINE_LOG_DEST_1='+DATA'
DB_CREATE_ONLINE_LOG_DEST_2='+RECO'

-- ASM
ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_1='+DATA';
ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST2 ='+RECO';

Khi đó tạo online redo log: ALTER DATABASE ADD LOGFILE GROUP 1 THREAD 1 size 1G sẽ tạo ra 1 member nằm ở +DATA/redolog/ và +RECO/redolog/

Note: Khi xóa ALTER DATABASE DROP LOGFILE GROUP 3 nó sẽ xóa toàn bộ các redo log file.

Chú ý: Khi tạo redo log hoặc sing lại cần chú ý:

1. Redo nhỏ quá ảnh hưởng tiêu cực gì?

--> Switch log nhiều, mỗi lần switch lại checkpoint, khi checkpoint là lúc ghi dữ liệu dirty block xuống Datafile, từ đó làm giảm hiệu năng của DB
2. Redo lớn quá ảnh hưởng tiêu cực gì?

--> Nguy cơ mất dữ liệu nếu lỗi redo log (trạng thái curent, active) do chưa ghi dữ liệu dirty block xuống datafile, còn bất chợt instance down mà redo log còn nguyên thì không bị mất dữ liệu đã
commit (dữ liệu chưa commit sẽ được rollback sau khi open)

3. Size redo như nào là hợp lý?
--> Oracle khuyến cáo 20 phút switch 1 lần, tức 1 tiếng 3 lần, tất nhiên 4-6 lần vẫn OK, với Product archived log sinh ra lớn (2-5TB archived log/ngày)

VD: 1 ngày sinh ra 2TB Archied Redo log, 1h sinh 85.3GB, switch 3 lần thì redo log size là 28GB, switch 4 lần redo log size là 21GB, switch 6 lần redo log size là 14 GB --> Thầy khuyến nghị redo log file đặt 20GB cho tròn

Quản trị redo log anh/em tham khảo: https://www.tranvanbinh.vn/2020/11/quan-tri-redo-log-files.html

Quản trị archived log anh/em tham khảo:
https://www.tranvanbinh.vn/2020/11/quan-tri-archied-redo-log-files.html

--3. Xóa log file từ group

-- Switch log sang file mới
alter system switch logfile;

-- Kiểm tra đảm bảo đủ 2 member trong 1 group, tối thiểu 03 group

select * from v$logfile order by 1;

--Status đảm bảo luôn có group inactive thi drop duoc

select * from v$log order by 1;

--Xóa log file, group

Alter database drop logfile group 1;

Alter database drop logfile member '/u10/oracle/data/DBAViet/redo01b.log';

--. Xóa trắng Redo Log File

ALTER DATABASE CLEAR LOGFILE GROUP 3;

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

Các câu lệnh khác:

--Bắt Log Switches để chuyển sang log file tiếp theo

ALTER SYSTEM SWITCH LOGFILE;

============================

THỦ TỤC TẠO LẠI ONLINE REDO LOG

Bạn lăn tăn khi nào cần tạo lại online redo log và cách tạo lại như thế nào an toàn?

-- Monitor Alert log xuất hiện cảnh báo:

Thread 2 cannot allocate new log, sequence 362494

Checkpoint not complete

-- Số lần switch log quá nhiều, lên đến 95 lần/1 giờ:

-- Monitor số lần switch log theo từng giờ và so sánh với các ngày trước trong 31 ngày gần nhất

select