Log Switches

set lines 1000
clear columns
col "Day" for a3
col total for 999
col "h0" for 999
col "h1" for 999
col "h2" for 999
col "h3" for 999
col "h4" for 999
col "h5" for 999
col "h6" for 999
col "h7" for 999
col "h8" for 999
col "h9" for 999
col "h10" for 999
col "h11" for 999
col "h12" for 999
col "h13" for 999
col "h14" for 999
col "h15" for 999
col "h16" for 999
col "h17" for 999
col "h18" for 999
col "h19" for 999
col "h20" for 999
col "h21" for 999
col "h22" for 999
col "h23" for 999

SELECT TRUNC (first_time) "Date", TO_CHAR (first_time, 'Dy') "Day",
COUNT (1) "Total",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '00', 1, 0)) "h0",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '01', 1, 0)) "h1",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '02', 1, 0)) "h2",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '03', 1, 0)) "h3",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '04', 1, 0)) "h4",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '05', 1, 0)) "h5",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '06', 1, 0)) "h6",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '07', 1, 0)) "h7",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '08', 1, 0)) "h8",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '09', 1, 0)) "h9",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '10', 1, 0)) "h10",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '11', 1, 0)) "h11",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '12', 1, 0)) "h12",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '13', 1, 0)) "h13",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '14', 1, 0)) "h14",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '15', 1, 0)) "h15",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '16', 1, 0)) "h16",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '17', 1, 0)) "h17",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '18', 1, 0)) "h18",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '19', 1, 0)) "h19",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '20', 1, 0)) "h20",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '21', 1, 0)) "h21",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '22', 1, 0)) "h22",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '23', 1, 0)) "h23"
FROM v$log_history
GROUP BY TRUNC (first_time), TO_CHAR (first_time, 'Dy')
ORDER BY 1
/

Redo Log files

select group#,status,BYTES/1024/1024 mb from v$log;

select * from v$logfile;

Creating Redo Logs

alter database add logfile
('/u01/app/oracle/oradata/orcl/redo_g4m1.rdo',
'/u01/app/oracle/oradata/orcl/redo_g4m2.rdo')
size 100m;

Creating Standby Redo Logs

ASM

alter database add logfile ('+DATA','+RECO') 1G;
FILE SYSTEM ALTER DATABASE ADD STANDBY LOGFILE
('/oracle/dbs/log1c.rdo','/oracle/dbs/log2c.rdo') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 10
('/oracle/dbs/log1c.rdo','/oracle/dbs/log2c.rdo') SIZE 50M;

alter database drop logfile group 1;

Supplemental Logging

SQL> select
   SUPPLEMENTAL_LOG_DATA_MIN,
   SUPPLEMENTAL_LOG_DATA_PK,
   SUPPLEMENTAL_LOG_DATA_UI
from
   v$database;

SUPPLEME SUP SUP
-------- --- ---
NO       NO  NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SQL> select
   SUPPLEMENTAL_LOG_DATA_MIN,
   SUPPLEMENTAL_LOG_DATA_PK,
   SUPPLEMENTAL_LOG_DATA_UI
from
   v$database;

SUPPLEME SUP SUP
-------- --- ---
YES      NO  NO

How to Add Redo Log Files to RAC DB

Query Log File Location, Status and Size

   set echo off
set feedback off
set linesize 120
set pagesize 35
set trim on
set trims on
set lines 120
col group# format 999
col thread# format 999
col member format a70 wrap
col status format a10
col archived format a10
col fsize format 999 heading "Size (MB)"

select l.group#, l.thread#,
f.member,
l.archived,
l.status,
(bytes/1024/1024) fsize
from
v$log l, v$logfile f
where f.group# = l.group#
order by 1,2;


Change Log File Size

ALTER DATABASE ADD LOGFILE GROUP 6 ('+DATAC1','+RECOC1') SIZE 2G;
alter database drop logfile group 2;
HOME