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

Quản lý Online Redo Log Files trong Oracle Database

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

--+ Begin dropping the redolog groups with STATUS=’INACTIVE’ – As mentioned earlier Oracle will not allow you to drop a current online redolog file.
alter database drop logfile group 1;
alter database drop logfile group 2;
--alter database drop logfile group 3;
alter database drop logfile group 4;
alter database drop logfile group 5;

alter database drop logfile group 7;

cd /data/oradata/prepaid/
mv /data/oradata/prepaid/redo01.log /data/oradata/dbaviet/redo01.log.bkp
mv /data/oradata/dbaviet/redo01.log /data/oradata/dbaviet/redo02.log.bkp
mv /data/oradata/dbaviet/redo01.log /data/oradata/dbaviet/redo03.log.bkp
mv /data/oradata/dbaviet/redo01.log /data/oradata/dbaviet/redo04.log.bkp
mv /data/oradata/dbaviet/redo01.log /data/oradata/dbaviet/redo05.log.bkp
mv /data/oradata/dbaviet/redo01.log /data/oradata/dbaviet/redo06.log.bkp
mv /data/oradata/dbaviet/redo01.log /data/oradata/dbaviet/redo07.log.bkp
mv /data/oradata/dbaviet/redo01.log /data/oradata/dbaviet/redo08.log.bkp

--Recreate group with both members
select * from v$log;    

select * from v$logfile;

alter database drop logfile group 6;
alter database drop logfile group 7;

alter database add logfile thread 1 group 1 '/u02/oradata/dbaviet/redo01.log'  size 512m;
alter database add logfile thread 1 group 2 '/u02/oradata/dbaviet/redo02.log'  size 512m;

--alter database add logfile thread 2 group 3 '/u02/oradata/dbaviet/redo03.log'  size 512m;
alter database add logfile thread 2 group 4 '/u02/oradata/dbaviet/redo04.log'  size 512m;

alter database add logfile thread 2 group 5 '/u02/oradata/dbaviet/redo05.log'  size 512m;

alter database add logfile thread 2 group 6 '/u02/oradata/dbaviet/redo06.log'  size 512m;

alter database add logfile thread 1 group 7 '/u02/oradata/dbaviet/redo07.log'  size 512m;


-- Tao lai group 8 thread 1: Van con /data
-- Group# 8 INACTIVE thi drop logfile
select * from v$log;    

select * from v$logfile

alter database drop logfile group 8;

alter database add logfile thread 1 group 8 '/u02/oradata/dbaviet/redo08.log'  size 512m;

alter system switch logfile;

LỖI PHÁT SINH


Log file switch (checkpoint incomplete)

Nguyên nhân: online log quá nhỏ, ít so với DB

Khắc phục:

Dùng 1 trong 2 cách sau:

    * Tăng size của online redo logs: Câu lệnh bên trên, phải tạo lại chứ ko resize được

    * Bổ sung thêm redo log groups: 

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

KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH: 
📧 Mail: binhoracle@gmail.com ☎️ Mobile: 0902912888 ⚡️ Skype: tranbinh48ca 👨 Facebook: https://www.facebook.com/BinhOracleMaster 👨 Inbox Messenger: https://m.me/101036604657441 (profile) 👨 Fanpage: https://www.facebook.com/tranvanbinh.vn 👨 Inbox Fanpage: https://m.me/tranvanbinh.vn 👨👩 Group FB: https://www.facebook.com/groups/OracleDBAVietNam 👨 Website: http://www.tranvanbinh.vn 👨 Blogger: https://tranvanbinhmaster.blogspot.com 🎬 Youtube: http://bit.ly/ytb_binhoraclemaster 👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi 👨 Linkin: https://www.linkedin.com/in/binhoracle 👨 Twitter: https://twitter.com/binhoracle 👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội #OracleTutorial #OracleDBA #OracleDatabaseAdministration #học oracle database #oca #ocp #oce #ocm

ĐỌC NHIỀU

Trần Văn Bình - Oracle Database Master