--1. Check

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 to group

--File system

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

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

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;

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

--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

--DROP AND RECREATE ONLINE REDOLOG FILES

--Method to drop and recreate online redolog files with 2 members to each group

--1.Firstly ORACLE will never allow you to drop the current ONLINE redolog file –

-- Size, status logfile group, thread

select * from v$log;

--Group# Thread# Sequence# BytesMembers Archived

--1 1 17449 524288000 1 YES INACTIVE 11950575940971 28/05/2017 1:36:20 AM

--2 1 17448 524288000 1 YES INACTIVE 11949968296967 27/05/2017 2:05:34 AM

--3 2 26642 524288000 1 YES INACTIVE 11951023181485 28/05/2017 6:30:26 PM

--4 2 26643 524288000 1 YES INACTIVE 11951187419487 29/05/2017 1:35:35 AM

--5 2 26644 524288000 1 YES INACTIVE 11951568250526 29/05/2017 3:17:19 PM

--6 2 26645 524288000 1 NO CURRENT 11951840525221 30/05/2017 1:14:52 AM

--7 1 17450 524288000 1 YES INACTIVE 11951187419492 29/05/2017 1:35:36 AM

--8 1 17451 524288000 1 NO CURRENT 11951840525227 30/05/2017 1:14:53 AM

-- D/s duong dan logfile

select * from v$logfile order by 1;

--1 ONLINE /data/oradata/dbaviet/redo01.log NO

--2 ONLINE /data/oradata/dbaviet/redo02.log NO

--3 ONLINE /data/oradata/dbaviet/redo03.log NO

--4 ONLINE /data/oradata/dbaviet/redo04.log NO

--5 ONLINE /data/oradata/dbaviet/redo05.log NO

--6 ONLINE /data/oradata/dbaviet/redo06.log NO

--7 ONLINE /data/oradata/dbaviet/redo07.log NO

--8 ONLINE /data/oradata/dbaviet/redo08.log NO

--10 STANDBY /data/oradata/dbaviet/standby_redo10.log NO

--11 STANDBY /data/oradata/dbaviet/standby_redo11.log NO

--12 STANDBY /data/oradata/dbaviet/standby_redo12.log NO

--13 STANDBY /data/oradata/dbaviet/standby_redo13.log NO

--14 STANDBY /data/oradata/dbaviet/standby_redo14.log NO

--15 STANDBY /data/oradata/dbaviet/standby_redo15.log NO

alter database drop logfile group 10;

alter database drop logfile group 11;

alter database drop logfile group 12;

alter database drop logfile group 13;

alter database drop logfile group 14;

alter database drop logfile group 15;

--Intention is to drop and recreate these online logs with a different size.