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