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