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;

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