Operations on Oracle log files :

1. Forcing log file switches:
ALTER SYSTEM switch logfile;
or
ALTER SYSTEM checkpoint;
2. Clear A Log File If It Has Become Corrupt:
ALTER DATABASE CLEAR LOGFILE GROUP group_number;
3. This statement overcomes two situations where dropping redo logs is not possible: If there are only two log groups and if the corrupt redo log file belongs to the current group:
ALTER DATABASE CLEAR LOGFILE GROUP 4;
4. Clear A Log File If It Has Become Corrupt And Avoid Archiving:
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP group_number;
5. Use this version of clearing a log file if the corrupt log file has not been archived:
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;
6. Privileges Related To Managing Log Files:
ALTER DATABASE
ALTER SYSTEM
7. Init File Parameters Related To Log Files:
log_checkpoint_timeout … set to 0
8. Managing Log File Members:
ALTER DATABASE
ADD LOGFILE MEMBER ‘log_member_path_and_name’
TO GROUP group_number;
9. Adding log file group members:
ALTER DATABASE
ADD LOGFILE MEMBER ‘/oracle/dbs/log2b.rdo’ TO GROUP 2;
10. Droping log file group members:
ALTER DATABASE
DROP LOGFILE MEMBER log_member_path_and_name’;
ALTER DATABASE
DROP LOGFILE MEMBER ‘/oracle/dbs/log3c.rdo’;
11. To create a new group of online redo log files, use the SQL statement ALTER DATABASE with the ADD LOGFILE clause:

The following statement adds a new group of redo Oracle log files to the database:

ALTER DATABASE ADD LOGFILE (’/oracle/dbs/log1c.rdo’, ‘/
oracle/dbs/log2c.rdo’) SIZE 500K;

fonte: www.databasedesign-resource.com

## Script para Coleta de Dados – Volumetria ORACLE
set line 150
set linesize 150
col “Tamanho (MB)” format 999,999,999
col SERVIDOR format a20
col SO format a30
col “MODO ARQUIVAMENTO” format a20
col “TOTAL” format 999,999,999

spool coleta_dados.txt

prompt
prompt ===========================================================
prompt BANCO DE DADOS
prompt ===========================================================
prompt
set pagesize 10000
select a.instance_name as “INSTANCIA”, a.host_name as “SERVIDOR”,  b.platform_name as “SO”, a.status as “STATUS”, a.archiver as “MODO ARQUIVAMENTO”,
to_char(b.created,’DD-MM-RRRR HH24:MI:SS’) as “DATA CRIACAO”
from v$instance a, v$database b
where a.instance_name=b.name;

prompt
prompt ===========================================================
prompt BANCO DE DADOS
prompt ===========================================================
prompt
select name, type, value from v$parameter;

prompt
prompt ===========================================================
prompt VERSAO DO BANCO DE DADOS
prompt ===========================================================
prompt

set pagesize 0
select * from v$version;

prompt
prompt ===========================================================
prompt VOLUMETRIA FISICA
prompt ===========================================================
prompt
set pagesize 10000
select sum(bytes)/1024/1024 as “Tamanho (MB)” from dba_data_files;

prompt
prompt ===========================================================
prompt VOLUMETRIA LOGICA
prompt ===========================================================
prompt
select sum(bytes)/1024/1024 as “Tamanho (MB)” from dba_segments;

prompt
prompt ===========================================================
prompt RESUMO DE OCUPACAO DE ESPACO POR ESQUEMA
prompt ===========================================================
prompt
select owner as “ESQUEMA”, segment_type as “TIPO DE OBJETO”, sum(bytes)/1024/1024 as “TAMANHO (MB)”
from dba_segments
group by owner, segment_type
order by owner;

prompt
prompt ===========================================================
prompt TABLESPACES
prompt ===========================================================
prompt
select tablespace_name as “TABLESPACE”, block_size as “BLOCO DE DADOS”, status as “STATUS”, logging as “LOGGING”,
extent_management as “GER.EXTENSAO”, allocation_type as “TIPO DE ALOCACAO”, segment_space_management as “GER.SEGMENTO”,
retention as “RETENCAO”
from dba_tablespaces;

prompt
prompt ===========================================================
prompt RESUMO POR TABLESPACES
prompt ===========================================================
prompt
select decode(grouping(tablespace_name),0,null,1,’TOTAL (MB) =’) as “1″,
tablespace_name as “TABLESPACE”, segment_type as “TIPO DE OBJETO”, sum(bytes)/1024/1024 as “TAMANHO (MB)”
from dba_segments
group by rollup(tablespace_name, segment_type)
order by tablespace_name;

prompt
prompt ===========================================================
prompt VERIFICACAO DE ESTATISTICAS NAS TABELAS
prompt ===========================================================
prompt
select decode(to_char(last_analyzed,’DD-MM-RRRR’),null,’SEM ESTATISTICA’,to_char(last_analyzed,’DD-MM-RRRR’)) as “ANALISE”,
count(table_name) as “TOTAL DE TABELAS”
from dba_tables
group by to_char(last_analyzed,’DD-MM-RRRR’)
order by to_char(last_analyzed,’DD-MM-RRRR’);

prompt
prompt ===========================================================
prompt VERIFICACAO DE ESTATISTICAS NOS INDICES
prompt ===========================================================
prompt
select decode(to_char(last_analyzed,’DD-MM-RRRR’),null,’SEM ESTATISTICA’,to_char(last_analyzed,’DD-MM-RRRR’)) as “ANALISE”,
count(index_name) as “TOTAL DE INDICES”
from dba_indexes
group by to_char(last_analyzed,’DD-MM-RRRR’)
order by to_char(last_analyzed,’DD-MM-RRRR’);

prompt
prompt ===========================================================
prompt VERIFICACAO DO STATUS NOS INDICES
prompt ===========================================================
prompt
select a.status as “STATUS”, count(a.index_name) as “TOTAL”, sum(b.bytes)/1024/1024 as “TAMANHO (MB)”
from dba_indexes a, dba_segments b
where a.index_name=b.segment_name
group by a.status;

spool off