Ver redo logs atuais

SQL> select sequence#,group#, members,FIRST_CHANGE#, bytes/1024/1024, status from v$log order by 2;

Ver arquivos de Redo

SQL> col MEMBER format a50;
select group#,member from v$logfile order by 1;

1 2	SQL> col MEMBER format a50; select group#,member from v$logfile order by 1;

Adicionar novo redolog

SQL> alter database add logfile group 4 '/u01/app/oracle/oradata/orcl/redo04.log' size 500M;

Adicionar um membro ao grupo existente

SQL> alter database add logfile member '/u02/fra/apollo/oradata/redo01_b.log' to group 1;

Adicionar log já multiplexado

SQL> alter database add logfile group 4 ('/u01/oracle/oradata/orcl/redo04.log','/u02/oracle/oradata/orcl/redo04_b.log') size 500M;

Remover um membro de um grupo

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u02/oracle/oradata/APOLLO/redo05_b.log';

Adicionar redo ASM

SQL> ALTER DATABASE ADD LOGFILE size 1G;

Alterando o log current

SQL> alter system switch logfile;

Criando um checkpoint do banco

SQL> ALTER SYSTEM CHECKPOINT;

Deletando os redo logs

SQL> alter database drop logfile group 1;

Recriar redolog que foi apagado do disco (Ativo)

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP <group number>;

Recriar redolog que foi apagado do disco (Inativo)

SQL> alter database clear logfile group 4;

Alterar frequência de switch logfile;

SQL> alter system set archive_lag_target=1800 scope=both;

Mostrar frequência de swich logfile

Bidimencional

SQL> SELECT 
	to_char(first_time,'YYYY-MON-DD') day,
	to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
	to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
	to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
	to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
	to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
	to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
	to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
	to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
	to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "0",
	to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
	to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
	to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
	to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
	to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
	to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
	to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
	to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
	to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
	to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
	to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
	to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
	to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
	to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
	to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
from
	v$log_history
	GROUP by 
to_char(first_time,'YYYY-MON-DD') order by day;

SQL> SELECT

to_char(first_time,'YYYY-MON-DD') day,

to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",

to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",

to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",

to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",

to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",

to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",

to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",

to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",

to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "0",

to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",

to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",

to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",

to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",

to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",

to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",

to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",

to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",

to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",

to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",

to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",

to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",

to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",

to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",

to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"

from

v$log_history

GROUP by

to_char(first_time,'YYYY-MON-DD') order by day;

Linear

SQL> col day format a15;
col hour format a4;
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
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')
asc;

SQL> col day format a15;

col hour format a4;

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

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

asc;

Criar vários redos

SQL> begin
  for i in 1..10 loop       
	execute immediate 'alter database add logfile size 1G';
  end loop;
end; 
/

SQL> begin

for i in 1..10 loop

execute immediate 'alter database add logfile size 1G';

end loop;

end;

Referências:
http://www.oracledistilled.com/oracle-database/administration/multiplexing-the-redo-log-files/
http://www.dba-oracle.com/t_log_switch_frequency.htm

REDO Logs