DBA Oracle 11g – Layout e Gerenciamento

A forma como o DBA planeja o layout dos tablespaces em um BD afeta diretamente seu desempelnho e sua gerenciabilidade.

Tipos de tablespace: permanente: dados mantidos em arquivos apos encerramento da sessão, undo: fornece consistencia de leitura para instruções select que acessam tabelas que estão sendo modificadas e tambem para permitir recuperação em caso de falhas, pois, armazena os dados que estão sendo alterados/inseridos antes do commit/rollback, temporario: contem dados transitorios enquanto durar a sessão.

Tablespace SYSTEM: é gerenciado localmente, toda a utilização de espaço é gerenciada por um segmento na primeira parte do primeiro arquivo de dados do tablespace.

Tablespace SYSAUX: Verificar a utilização no Enterprise Manager Database Control, link Tablespaces, link SYSAUX

sysaux

COM O EM Database Control é possivel identificar pontos de grande volume de E/S.

Undo: Vários tablespace de undo podem existir, porem somente um pode estar ativo em um dado momento. Permitem fazer rollback nas transações.

Resumo DBA Oracle 11 – Backup e Recuperação

O oracle dá suporte a muitas formas de backup e recuperação.

Exportação e Importação: independente de plataforma. É considerado um backup lógico. Usa o comando expdb para exportar e impdb para recuperar. Legivel em varias versões do oracle. Não é a solução robusta para dados volateis.

Backup offline: tambem chamado de backup a frio, pois é preciso fazer um shutdown immediate no BD e copiar todos os arquivos, inclusive o SPFILE e arquivos de senha.

Backup online: somente se o BD estiver em modo ARCHIVELOG. O BD fica disponivel para os usuarios mesmo durante o backup.

RMAN

A ferramente de backup Recovery Manager, fornece muitas vantagens. Pode ser controlado por controlfile ou por catalogo no BD. Mais recomendado é criar o catalogo.

Resumo DBA Oracle 11g

Resumo da leitura do Livro Manual do DBA Oracle 11g.

CONCEITOS

Instancia: uma instancia Oracle existe na memoria do servidor. É o SGA System Global Area onde os processos em segundo plano interagem entre a SGA e os arquivos de banco de dados.
A instancia que se quer conectar é identificada pela variavel $ORACLE_SID.

Tablespace: consiste em um ou mais arquivos de dados. Um arquivo de dados pode ser parte de apenas um tablespace. Existiram no minimo duas tablespaces a SYSTEM e a SYSAUX.

Usar o Oracle Managed Files (OMF) facilita o gerenciamento, pois o DBA, pode especificar os locais e a forma como serão criados os arquivos de dados, de redo log e de controle.

Os tablespaces devem ser gerenciados localmente.

As subdivisões de um tablespace: blocos é a menor unidade de armazenamento. Extensões é o agrupamento de blocos. Segmentos é um grupo de extensões.

Temos 4 tipos de segmentos: segmentos de dados, segmentos de indices, segmentos temporarios e segmentos de rollback.

Os segmentos de rollback são gerenciados, a partir da versão 11, pelo Automatic Undo Management é está ativado por padrão.

TABELAS

Parcicionamento de tabelas: Traz vantagens para o DBA, como velocidade, evita perda de dados, backups parcelados. A oracle recomenda o particionamento para tabelas que tenham mais de 2GB.

CONTRAINTS

É uma regra para uma ou mais colunas para impor a regra de negocio. Podem ser ativadas ou desativadas a qualquer momento usando aneble ou disable. Depois pode ser necessario validar os dados usando validate ou novalidate.

Temos NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK. Tambem é possivel criar triggers com validações totalmente personalizadas.

INDICES

O indice armazena o valor da coluna indexada junto com o RowID físico da linha. Pode ser de uma ou mais colunas. Temos indices únicos, não-unicos, chave invertida, baseados em função e bitmap.

VISÕES

Permite uma apresentação personalizada dos dados de uma ou mais tabelas. Não armazena dados, apenas a definição de uma consulta.

As visões materializadas armazena os resultados da consulta juntamente com a definição, para acelerar o processo. É utilizada em ambiente de warehouse. São como indices, por otimizar consultas.

USUARIOS E ESQUEMAS

O acesso ao BD é concedido para uma conta conhecida como usuario, e, se o usuário cria objetos então eles ficam armazenados no esquema que tem o mesmo nome do usuário. O usuario ou DBA podem conceder acesso a outros usuários.

Autenticão: autenticação de banco de dados onde a senha ármazenada no BD. Autenticação de sistema operacional assume que o usuario tem os privilegios. Autenticação de rede usa soluções baseadas em Public Key Infrastructure.

Perfis: permite gerenciar recursos. Normalmente os usuario tem perfil DEFAULT.

Sequencias: permite atribuir numeros sequenciais que são unicos a uma coluna de uma tabela automaticamente.

Sinônimos: apelido para simplificar a referencia a um objeto.

PL/SQL

Linguagem procedural para SQL.

Procedures e Funções: Blocos nomeads de PL/SQL com instruçoes divididas em 3 seções, sendo a primeira a seção de declaração de variaveis, s egunda seção executável e por fim a seção de exceções. A função retorna dados como uma instrução select e a procedure retorna um código de status. Tam vantagens de já estarem compiladas e terem uma copia no shared pool.

Pacotes: agrupam funções e procedures.

Trigger: são blocos disparados quando ocorre um determinado evento pre-programado.

ESTRUTURA DE ARMAZENAMENTO FÍSICO DO ORACLE

Arquivos de dados: arquivo físico no sistema operacional. Um tablespace pode ter vários arquivos de dados. A Oracle recomenda que os arquivos de dados não devem ultrapassar 2GB.

Arquivos d redo log: sempre que são adicionados, alterados ou removidos dados ou qualquer outro objeto, uma entrada é adicionada no redo log atual. Normalmente se tem 3 redo logs que são usados de forma circular. São utilizados para recuperação em caso de falhas. Ao ser reiniciada a instancia aplicará automaticaemente as entradas pendentes nos arquivos de dados. Para evitar a perda de um arquivo de redo log, é necessário criar copias/multiplexar em outros discos.

Arquvos de controle: contem o nome do banco de dados, data de criação locais dos arquivos de dados, e redo logs, configurações do RMAN. É um arquivo muito importante e deve ser multiplexado tambem.

Arquivos de log arquivados: o ideal é trabalhar no modo ARCHIVELOG, onde o arquivo de redo log preenchido é enviado para um ou mais destino especificado e vai esar disponivel para reconstruir o BD a qualquer momento que se queira voltar no tempo. O uso de multiplos destinos para os logs arquivados de redo log preenchidos é crucial para manter a alta disponibilidade, veja mais em Oracle Data Guard.

Arquivos de parametros de inicialização: ao iniciar uma instancia dois arquivos são acionados. O init<SID>.ora, conhecido como init.ora ou PFILE, e o SPFILE. Primeiramente procura pelo SPFILE no $ORACLE_HOME/dbs/spfile<SID>.ora ou spfile.ora. Se naõ encontrar procura por um PFILE com init<SID>.ora ou init.ora. Especificamo as localizações para arquivos de rastreamento, controle redologs, dados, definem o tamenho da SGA quantidade de usuários etc…

O SPFILE permite que os comandos alter system sejam gravados e o DBA não precisa de lembrar de fazer no spfile<SID>.ora. Este é o novo padrão da versão 11g.

Dica fora do livro: Para saber o conteudo do SPFILE, crie o PFILE a partir do SPFILE. Primeiramente faça uma copia do PFILE ($ORACLE_HOME/dbs/init<SID>.ora).

$ sqlplus /nolog
connect / as sysdba;
CREATE pfile FROM spfile;
quit;

Agora voce pode ver e editar o init<SID>.ora, e então recriar o SPFILE:

connect / as sysdba;
shutdown immediate;
startup pfile=init<SID>.ora;

shutdown immediate;
CREATE spfile FROM pfile;
startup;

Não há necessidade de recriar o SPFILE, usando os comandos alter system automaticamente grava no SPFILE.

Arquivos de log de alerta: são gravados no local da BACKGROUND_DUMP_DEST,  pelo oracle em caso de algo errado, tambem os comandos de alter database e alter system, operações com tablespaces, falta de espaço, redo logs corrompidos.

Para saber o conteudo de BACKGROUND_DUMP_DEST:

select * from v$parameter where name like ‘%dump%’ order by name;

Analisando os arquivos principais *.log é possivel acompanhar todos os eventos importantes no BD.

O arquivos de rastreamento: tambem são gravados neste local. Porem são os relativos a sessão dos usuarios são gravados em USER_DUMP_DEST e são gravados quando ocorre algum erro ou quando usado o comando alter session set sql_trace=true. Informações de rastreamento são geradas para cadas instrução SQL que o usuario execute.

Estes arquvivos podem ser excluídos a qualquer momento. Normalmente estes arquivos são renomeados diariamente.

Arquivos de backup: podem ser copia dos arquivos de dados, redo log, controle, entre outros, chamado de backup a frio. Tambem pelo RMAN que pode gerar backup completo e incremental, gerando um arquivo especial legivel somente pelo RMAN. O RMAN é a melhor opção para gerar um backup seguro.

Arquivos de senha: usado para autenticar os administradores do BD. São concedidos provilegios de SYSDBA e SYSOPER. Outros usuarios são autenticados dentro do BD. Utiliztario orapwd gerencia a criação destas senhas.

MULTIPLEXANDO ARQUIVOS

Reduz ou elimina os problemas de perda de dados casados por falhas de mídia.

ASM Automatic Storage Management: solução de multiplexação que automatiza o layout dos arquivos de dados, controle, redo log por meio de distribuição destes arquivos pelos discos disponiveis.

Multiplexação Manual de control files: configurando parametros de inicialização e forncendo um local adicional para os arquivos. Para saber se á está multiplexado faça:

select * from v$controlfile;

Se aparecer mais de um arquivo é porque ja está multiplexado. Para multiplexar ou mudar os locais, use o comando alter system.

alter system
set control_files = ‘/u02/oradata/ORCL/control01.ctl’,
‘/u02/oradata/ORCL/control02.ctl’,
‘/u02/oradata/ORCL/control03.ctl’,
‘/u02/oradata/ORCL/control04.ctl’
scope=spfile;

Este comando vai gerar um erro pois a instancia está em execução. Mas na proxima reinicialização será aplicada a alteração.

Se acrescentou algum control file, lembre-se de parar o BD, com shutdown immediate, copiar um dos controlfiles existentes para o novo controlfile. Se alterou o local, então mova os controlfiles para o novo local. Aplique as permissões dos usuários e inicialize o BD, com startup.

Multiplexação manual de redo log: é necessário usar grupo de arquivos. Use o comando alter database. Para saber os arquivos de redolog use:

SELECT * FROM v$logfile;

A instalação traz tres grupos por padrão. Assim é possivel adicionar arquivos a estes grupos:

alter database add logfile member ‘/u02/oradata/ORCL/redo01b.dbf’ to group 1;

ou acrescentar novos grupos:

alter database add logfile group 4 (‘/u02/oradata/ORCL/log5a.dbf’,’/u03/oradata/ORCL/log5b.dbf’, ‘/u03/oradata/ORCL/log5c.dbf’) size 250m;

O ideal é utiliza o ASM Automatic Storage Management para gerenciar a multiplexação.

Deixe seu Ubuntu mais rápido

Por várias vezes, em várias ocasiões percebo a maquina lenta. Vou verificar no top e la está ele o “trackerd”. Este vilão que rouba a velocidade.

Para remove-lo use o seguinte comando:

sudo apt-get remove tracker
sudo apt-get autoremove

Lembrete: sempre que atualizar para nova versão, ele será automaticamente ativado e voce terá que remover novamente.

Backup HOT Oracle

O ideal do Backup, e fazer sem parar o servidor. Para tanto é necessário ativar o modo ARCHIVELOG. Verifique se já está habilitado fazendo a seguinte consulta:

sqlplus SYSTEM/SENHA@HOT/INSTANCE
SELECT LOG_MODE FROM SYS.V$DATABASE;

Se retornar “NOARCHIVELOG”, faça os seguintes ajustes para passar para o modo ARCHIVELOG.

Localize o arquivo init.ora e ajuste:

cd $ORACLE_HOME/dbs
sudo vi init.ora

Localize e ajuste as seguintes variaveis:

log_archive_start = true
#log_archive_dest = disk$rdbms:[oracle.archive]
log_archive_dest = “/u03/archivelog”
log_archive_format = “T%TS%S.ARC”

Referencia ARCHIVELOG : Oracle Docs

Utilize o sqlplus para parar o BD, e mudar para o modo ARCHIVELOG

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

Faça a consulta no V$DATABASE novamente para ver se ativou o ARCHIVELOG.

sqlplus SYSTEM/SENHA@HOT/INSTANCE
SELECT LOG_MODE FROM SYS.V$DATABASE;

Uma vez ativado, não precisa mais executar estes comandos.

Agora vamos

Monitoramento de espaço no Oracle

O aumento de espaço para comportar novos dados, implica em aumentar a TABLESPACE. Pode-se espandir o DATAFILE atual ou criar novo DATAFILE. Normalmente prefere-se criar novo DATAFILE para facilitar o processo de backup e e gerenciamento do sistema.

Para saber o percentual de cada TABLESPACE:

select a.TABLESPACE_NAME,
 a.BYTES bytes_used,
 b.BYTES bytes_free,
 b.largest,
 round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from  
 (
  select  TABLESPACE_NAME,
   sum(BYTES) BYTES
  from  dba_data_files
  group  by TABLESPACE_NAME
 )
 a,
 (
  select  TABLESPACE_NAME,
   sum(BYTES) BYTES ,
   max(BYTES) largest
  from  dba_free_space
  group  by TABLESPACE_NAME
 )
 b
where  a.TABLESPACE_NAME=b.TABLESPACE_NAME
order  by ((a.BYTES-b.BYTES)/a.BYTES) desc

Para expandir a TABLESPACE:

ALTER TABLESPACE TS_XYZ
add datafile '/u02/oradata/ORCL/TS_XYZ_02.DBF' size 2000M

Oracle SQL*Plus

Referencia: Blog da Lilian Barroso

Veja também:
SQL*Plus

Para conectar usando o sqlplus:

sqlplus USUARIO/SENHA@HOST/INSTANCIA
Exemplo: sqlplus SYSTEM/SENHA@192.168.0.2/ORCL

Abaixo, alguns comandos simples e poderosos para as coisas do dia-a-dia:

DATAFILES E TABLESPACES

Verificando o tamanho total de um banco de dados:
select sum(bytes)1024/1024 as tamanho_mb
from dba_segments;

Ver datafiles por Tablespace
column file_name format a70
select file_name, bytes/1024/1024
from dba_data_files
where tablespace_name = ‘XXXY’
/* substituir pelo nome da tablespace */
order by file_name;

Acrescentando datafiles
alter tablespace TABLESPACE /* substituir pela tablespace desejada */
add datafile ‘/diretorio/datafile.dbf’ /* substituir pelo endereço/datafile */
size 2000m;

Alterando o tamanho de um datafile:
alter database datafile  ‘/desenv/data/u01/oradata/tbs_idx.dbf’ resize 1000m;

Verificando o tipo de informação que fica em uma determinada tablespace:
select DISTINCT (segment_name),
segment_type
from dba_segments
where tablespace_name = ‘&tablespace’;

DADOS DE SESSÕES

Ver tempo desde a última atividade:
set lines 100 pages 999
select username,
floor(last_call_et / 60) ‘minutos’,
status
from   v$session
where  username is not null
order by last_call_et
/

Ver sessões organizadas por tempo de logon:
set lines 100 pages 999
col ID        format a15
col osuser    format a15
col login_time    format a14
select     username,
osuser,
sid || ‘,’ || serial# ‘ID’,
status,
to_char(logon_time, ‘hh24:mi dd/mm/yy’) login_time,
last_call_et
from    v$session
where    username is not null
order    by login_time
/

Mostrar o sql corrente do usuário:
Select t.sql_text
from   v$sqltext t, v$sqlarea a
where  (a.address, a.hash_value) in
(select sql_address, sql_hash_value
from v$session
where username like ‘&username’)
and a.HASH_VALUE = t.HASH_VALUE
order by t.piece
/

Visualizando as queries em execução no momento:
select sql_text
from v$sqltext
where hash_value in (select sql_hash_value
from v$session
WHERE SQL_HASH_VALUE <> 0
and schemaname <> ‘SYS’)
order by hash_value, piece

SENHAS
Salvar a senha dos system e sys:
select password, username from dba_users;

Recuperando a senha de um usuário
alter user lilian identified by values ‘E2D51514EXX0936D’; /* substituir pela senha recuperada pelo script anterior. */

ARCHIVES
Cancelar temporariamente a geração de archives
alter system set log_archive_dest_state_1 = ‘DEFER’;

Copiar os archives para outro destino:
ALTER SYSTEM SET log_archive_dest_2  = ‘LOCATION=/caminho’; /* onde /caminho deverá ser o caminho de destino dos logs */

Apagar a cópia dos archives para outro destino:
ALTER SYSTEM SET log_archive_dest_2  = ”;

Forçar a alternancia de log e o consequente archivamento
alter system switch logfile;

Ver as sequencias e o grupo de log ativo:
select sequence#, STATUs from v$log;

Ver alert_log  e traces do banco:
show parameter background_dump_dest

Vendo a última sequencia de log arquivado:
select max( sequence#) from v$log_history

Verificar as sequências por data:
select * from v$log_history
where FIRST_TIME like ‘<data>’
/* substiutir pela data desejada */
and sequence# > <sequencia> /* substituir pela sequência desejada */

QUERIES
Encontrando o texto de uma query:
select s.SQL_FULLTEXT
from sys.v_$sqlarea s
where s.HASH_VALUE = &hash;

Oracle Lento

Referencia: Blog do Portilho

Wait Events de I/O

O Wait Events abaixo são eventos relacionados a I/O (discos rígidos).
Se estes eventos aparecerem muito na V$SESSION_WAIT, certamente há um problema de I/O no Banco de Dados (está usando RAID 5?;-) ).

async disk IO
control file parallel write
control file sequential read
db file parallel write
db file scattered read
db file sequential read
direct path read
direct path write
log file parallel write
log file sync

Dois destes eventos merecem atenção especial:

db file scattered read – Um Full Table Scan está ocorrendo: Se este evento aparecer repetidamente na V$SESSION_WAIT, pode significar um SELECT que teria uma performance melhor com um índice apropriado.
db file sequential read
– Leitura de índice: Se este evento aparecer repetidamente na V$SESSION_WAIT para um mesmo SID, pode significar leituras desnecessárias de índice, como aquelas forçadas por um HINT, onde um Full Table Scan seria melhor (sim, isto acontece muito).

Então, para pegar as WAITS do banco no momento atual:
SQL> SELECT SID, EVENT, SECONDS_IN_WAIT FROM V$SESSION_WAIT ORDER BY W.SECONDS_IN_WAIT;

Para pegar o SQL que está causando estas WAITs (troque o 754 abaixo pelo SID que pegou no SELECT anterior):
SQL> SELECT A.SQL_TEXT FROM V$SQLTEXT A, V$SESSION B WHERE A.ADDRESS = B.SQL_ADDRESS AND A.HASH_VALUE = B.SQL_HASH_VALUE AND B.SID = 754 ORDER BY PIECE;

Oracle Backup & Recovery

Backup e Recovery é uma parte muito importante da administração de Banco de Dados. Se o computador onde está o Banco de Dados, apresentar algum problema, ou se o HD sofrer alguma avaria ou perda de dados, o Backup evitará que a empresa sofra um grande prejuizo.

Para um backup eficiente e com garantia de recuperação maxima possivel, é necessario ativar o modo ARCHIVELOG. Neste modo todas as inclusões e alterações serão gravadas tambem em arquivos separados. Estes arquivos devem ser copiados para o dispositivo de backup (fita, CD-ROM, DVD, etc…) e removidos para liberar espaço.

Deve-se efetuar o Backup Total que consiste em copiar os arquivos do sistema operacional para o dispositivo de backup. Obrigatoriamente o BD deve estar parado. Deve-se copair todos os arquivos arquivos de controle e de dados, como tambem os arquivos de redo logs.

Uma estratégia de backup interessante é parar o banco de dados no final de semana e fazer um backup total, e durante a semana fazer backup diarios dos arquivos de redo logs apenas.

Com este dois backups armazenados corretamente pode-se recuperar o banco de dados em qualquer momento que se queira. Basta baixar o BD da fita de domingo, que contem o backup total e aplicar os ‘Archived Redo  Logs’ e depois aplicar tambem as alterações nao gravadas “uncommitted”  dos ‘Online Redo Logs’.

  • Datafiles

Consiste em arquivos fisicos onde são gravados os dados e os indices.

  • Control Files

Arquivos de controle que informam o localizaçao e a situação de cada datafile. Pode ser multiplex, ou seja, gravado mais de uma copia do mesmo control file em local ou HD diferente.

  • Redo Logs

Arquivos onde são armazenadas todas as inclusões e alterações de dados. São limitados em tamanho e quando atinge determinado o tamanho maximo, um novo arquivo é criado.

iMasters Oracle

RMAN Backup em NOARCHIVELOG

Control File

Script Backup Frio

iMasters Backup Full

Archived Logs

Visão Geral de Backup & Recovery no Banco de Dados Oracle

PHP & Oracle: Paginação