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;