--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')
-- 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;
📧 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