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

Quản trị 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;

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