Erro ORA-12899 em Import

Hoje tive participei da solução de um problema na execução do comando Imp.exe .No momento do import estávamos recebendo o seguinte erro.

ORA-12899: valor muito grande para a coluna "schema"."tabela"."coluna" (real: 105, máximo: 100).

Vale a pena observar a seguinte mensagem do log do import :


importação realizada nos conjuntos de caracteres WE8MSWIN1252 e NCHAR AL16UTF16
o servidor de importação usa o conjunto de caracteres AL32UTF8 (conversão de charset possível)

O nosso problema aconteceu pelo fato de o banco de origem ter um charset diferente do banco de destino.Qual a solução que tivemos ?No momento do export.exe devemos dizer o charset que queremos para garantir o import.

NLS_LANG="AMERICAN_AMERICA.AL32UTF8"

fica a dica

Configuração do modo ARCHIVELOG

Configuração do modo ARCHIVELOG

Para fazermos backups consistentes de um banco de dados Oracle aberto,precisamos que esta instancia esteja

no modo ARCHIVELOG ativado.Com isso conseguimos fazer o chamado BACKUP ON-LINE.

O Oracle armazena os dados do redo log de forma ciclica.Quando o ultimo arquivo do redo log ativo for

preenchido o processo LGWR (log Write) irá substituir o conteúdo do primeiro arquivo redo.

Se a instancia do banco de dados estiver em modo ARCHIVELOG , o processo ARC (archive) irá fazer uma cópia

redo log depois do processo LGWR terminar de gravar o redo log.

Para fazer uso do modo ARCHIVELOG algumas configurações devem ser feitas.Vamos listar a recomendadas :

(1) Configure o parametro db_recovery_file_dest

ex : log_archive_dest = 'c:\app\oracle\arch'

(2) Defina o parametro Log_arquive_dest_n (pelo menos 1 )

ex : log_archive_dest_1 = 'c:\app\oracle\arch'

log_archive_dest_2 = 'c:\app\oracle\arch\backup'

log_archive_dest_3 = 'c:\app\oracle\arch\backup\bkp'

(3) Ative o modo ARCHIVELOG

sql> shutdown immediate ;

sql> startup mount

sql> alter database archivelog ;

sql> alter database open ;

Materialized view

São nada mais que objetos do banco de dados Oracle que contém dados locais de tabelas remotas ou são usadas pra criar uma agregação de tabelas em um determinado intervalo de tempo..Segue algumas dicas de uso de uma Materialized View :

Criação :

create materialized view Minha_mview

refresh [fast | complete | force]

start with sysdate

next sysdate + 1/24

with primary key

as select * from dba_objects ;

Sendo :

refresh [fast | complete | force] as opções de refresh da view :

Fast = Somente alterações entre o intervalo.

Complete = Recria toda a estrutura da view materializada mesmo que não seja necessário.

Force = Faz o fast de ser for possível,caso contrário faz o processo complete.

start with sysdate :

Data de primeira populacao da view materializada.

next sysdate + 1/24 :

Data da próxima população da view .

WIDTH PRIMARY KEY :

É utilizado para criar a Chave-Primária, que é baseada na chave-primária da tabela master.

View para conhecer as Materialized view do seu database :

dba_mviews

dba_mview_refresh_times

Outros comandos úteis :

Fazer manualmente o refresh fast da MV :

execute dbms_mview.refresh ('');

Fazer manualmente o refresh Complete da MV :

execute dbms_mview.refresh ('','C');

Create Tablespace

Muito importante na criação da sua Tablespace levar em consideração :

(1) O volume de informações que serão armazenado nela

(2) O volume de leituras e escritas que estas informações irão receber.

(3) informações sobre disco e armazenamento físico(Velocidade de disco e etc..)

Digo isso pelo fato de ja ter visto um SGDB oracle com uma tablespace com datafile em um hdb 2.5'' conectado via porta ubs .Um tablespace que era acessada por +- 200 usuários em concorrencia.

Segue um exemplo básico da criação de um tablespace :

create tablespace TSP_AUDT

datafile 'C:\ORACLE\PRODUCT\11XE\TSP__AUDT.dbf'

size 128M

autoextend on

maxsize 1024M

extent management local

uniform size 64K

View para obter maiores informações sobre Tablespaces e Datafiles :

V$datafile

V$tablespace

Memory_Target

Com as novas opções do Automatic Memory Management (AMM) do oracle 11g surgiu os novos para metros Memory_max_target eMemory_target.A estrutura dos paramentros de memória ficaram da seguinte forma hierarquica acima mostrada .

Vale a pena pensar como essa novas opções irão influenciar os parametros PGA_AGGREGATE_TARGET ( chamarei de PAT) e SGA_TARGET (chamarei de SGAT)

Considerando o Memory_Target definido com um valor acima de 0 :

Caso o SGAT e PAT estajam com valores definidos ,o comportamento do oracle será considerar o Memory_Target_max = SGAT + PAT

Caso SGAT esteja definido e PAT nao esteja definido ,o comportamento do oracle será considerar a PAT = memory_target - SGAT.

Caso SGAT nao esteja definido e PAT esteja definido, o comportamento do oracle será considerar SGA_target inicialmente min(memory_target - PAT , Sga_max_size) .

Caso SGAT e PAT nao estejam definidos estes valores serão auto ajustado para SGAT com 60 % da memory_target e PAT com 40 % da memory_target.

como estaos meu parametros nesse momento ? Veja com a View abaixo :

Sql > Show Parameter target ;

ADR - Automatic Diagnostic Repository

Uma funcionalidade muito útil para um DBA que vejo ser pouco comentada é o ADR.Ele se trata de uma repositório automatico do Oracle (só utilizei na versao 11G) que guarda os log do seu SGDB.

Para descobrir as pastas onde ficam os seu log segue um select em uma view :

Select name,value from v$diag_info ;

Onde podemos considerar :

adr_home = endereco

adr_home/trace - Arquivos que armazenam informações de processos de primeiro e segundo plano.

adr_home/alert - Guarda os logs de alerta no formato XML.

adr_home/trace - Guarda os logs de alerta no formato TXT.

adr_home/cdump - informações sobre dumps de memória.

adr_home/incident/incdir_n - guarda informamoes sobre dumps de incidentes.

Existe uma ferramenta chamda ADRCI que você pode chamar pelo terminal do linux ou prompt do DOS que te auxilia a leitura dos arquivos existentes nestas pastas.Essa ferramenta fica para um post futuro.

Cursor_Sharing

Nesta semana estive diante de um problema de uma aplicação com o uso de "literais" como parametros em alguns select como um exemplo :

Select COD_PAIS , DS_PAIS FROM PAISES WHERE COD_CONT = 'EU' ;

Com isso estou com problema de uma queries simples como essa acima ter para cada execução ,um plano de execução em memória no Oracle.Como não é fácil "convencer" a um desenvolvedor que esse tipo de escrita de select não é a melhor e que ao invés de usar uma literal como parâmetro ele poderia utilizar a nossa Bind variable .Resolvi procurar algo que diminuísse o estrago que estava acontecendo.

O parâmetro Cursor_Sharing trabalha nessa direção .Ele aceita 3 valores, sendo eles: FORCE, SIMILAR e EXACT.

EXACT – é o padrão para banco de dados Oracle. O parâmetro EXACT envia ao otimizador do Oracle que toda e qualquer instrução deve ser igual inclusive os valores literais.Este é o parametro do meu SGDB e com isso fico dependendo da boa vontade do desenvolvedor.

FORCE – O parâmetro troca as variáveis literais por valores bind, em sua execução, fazendo com que o HARD-PARSE se transforme em SOFT. O valor FORCE no parametro CURSOR_SHARING, gera apenas 1 plano de execução.Porem não consegui identificar em que esta opção pode nos trazer problemas.Pense bem.Se ela fosse livre de riscos ,poque não seria a opção default ??

SIMILAR – O parâmetro troca as variáveis literais por valores bind, em sua execução.

O valor SIMILAR no parametro CURSOR_SHARING, parece agir da mesma forma que o valor FORCE,mas diferente do FORCE , o SIMILAR gera mais de um plano de execução (se a tabela estiver com a coleta de estatísticas atualizada).

O CURSOR_SHARING é um cara meio problemático. Se você fizer uma busca no

Metalink, vai ver vários problemas associados. Quando migramos para o 11g

onde eu trabalho, houve um ORA-0600 .Porem ,pode ser uma alternativa.

Lista de Processos Oracle em Execução na Instancia

Segue uma query para descobrirmos a lista de Processos Oracle em Execução na Instancia :

Select Sid,Serial#,Process,Name,Description

From V$session Ss

Join V$bgprocess Bg

On Bg.Paddr = Ss.Paddr

Forma rápida de Eliminar Sessões Maléficas

Todos nós conhecemos a opção do commando "KILL"muito utilizado para eliminar sessões maléficas ou incoerentes num database.Mas que não executou um Kill numa sessão e depois disso recebe a seguinte resposta “session market for kill” e esta sessão continua por um determinado período perturbando ? Isto acontece porque existe toda um controle do Oracle para confirmar que realmente a sessão pode ser morta .

Em contato com algums DBA venho vendo que um alternativa muito interessante vem sendo o uso do commando disconnect para estas sessões como no exemplo abaixo :

SQL> ALTER SYSTEM DISCONNECT SESSION ‘sid,serial#’ IMMEDIATE;

a Opção immediate pode ser modificada pela opção POST_TRANSACTION (esperar o fim da Transação corrente).Em momentos críticos ,pode ser um alternativa ao Kill Session .

Sql para Monitorar suas Tablespaces

Vai a Dica :

Select d.tablespace_name as Tablespace , sum(d.bytes) as total , sum(f.bytes) as livre

from dba_data_files d

left join dba_free_space f

on d.tablespace_name = f.Tablespace_name

group by d.tablespace_name

Descubra o Tamanho de suas Tabelas

SELECT t.table_name AS "Table Name",
t.num_rows AS "Rows",
t.avg_row_len AS "Avg Row Len",
Trunc((t.blocks * p.value)/1024) AS "Size KB",
t.last_analyzed AS "Last Analyzed"
FROM dba_tables t,
v$parameter p
WHERE t.owner = :powner
AND p.name = 'db_block_size'

ORDER by t.table_name;

10 Motivos para utilizar o RMAN

Segue alguns motivos :

(1) RMAN vem com software da Oracle e é livre para usar (a partir da versão 8 do Oracle).

(2) RMAN se torna mais simples a cada dia. O simples backup BACKUP DATABASE banco de dados.

(3) Quando você usa o RMAN você está 100% certo de que é feito o backup do banco de dados.

(4) Com o RMAN pode validar um banco de dados antes de backup e também validar um banco de dados depois de ter backup.

(5) RMAN é a única ferramenta que oferece suporte a backup incremental.

(6) O RMAN faz a compressão e criptografia dos seus dados.

(7) Detecta e corrigi blocos conrropidos.

(8) Pode ter backup em paralelo e também restaurar a operação pode ser feita por paralelo.

(9) Vários relatórios e consultas através do RMAN.

(10) RMAN é uma ferramenta inteligente que nos ajudam a decidir consulta diversos como quais arquivos precisam ter backup, backup, que é obsoleto, que backup é expirado.

ora-01115 io error reading block from file

Problema que um cliente me relatou no log do backup (export full=y) .

EXP-00056: ORACLE error 1115 encountered

ORA-01115: IO error reading block from file 1 (block # 112022)

ORA-01110: data file 1: 'C:\ORACLE\ORADATA\SYSTEM01.DBF'

ORA-27091: skgfqio: unable to queue I/O

OSD-04006: ReadFile() failure, unable to read from file

O Banco de dados não estava em archivelog e o backup era feito apenas com export, o que fazia que uma tabela não tivesse seus dados exportados. O que podemos fazer neste caso ?

Tentativa 1
Primeiro tentei recuparar o datafile com o Oracle dbv (database verify utility)

dbv file='C:\ORACLE\ORADATA\SYSTEM01.DBF'

Infelizmente o procedimento não resolveu o nosso problema ....

Tentativa 2

Parti para tentar descobri que informações estão contidas no bloco do S.O defeituosos.

SELECT *
FROM DBA_EXTENTS
WHERE FILE_ID= :p1
AND :p2 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS;

onde :p1 é o numero do datafile e :p2 é o numero do bloco defeituoso.

o que consegui fazer ? fiz um tabela clone (com a mesma estrutura da tabela com informacao em perigo) e fiz uma cópia de todas as informações possíveis.

Depois disso fiz um truncate na tabela com os dados com problema (no meu caso esta tabela não tinha relacionamento que outra tabela tivesse chave dependente).

Movi a tabela para uma nova tablespace temporário e preenchi esta tabela com os dados da tabela temporária.Depois disso fiz uma exportação Full da informação do SGDB.Troquei o HD defeituoso e refizemos a instalação do banco e o import dos dados.

Infelizmente a situação não garantiu em 100% a recuperação dos dados mas existiu uma forma de converter o problema e não torna-lo em algo ainda pior.

é muito importante uma politica de Backup segura !!!

Segue o link que me ajudou para chegar a uma solucao :

http://forums.oracle.com/forums/thread.jspa?threadID=2127758

Importancia do Modo archivelog

Vejo que existe muitas pessoas que questionam a utilização do modo "Archivelog". Eu mesmo ja pensei que com a utilização do bom e funcional Exp ou Expdp estariamos tento um backup seguro do banco.Na verdade o Exp e o ExpDp nos garante um bom backup sim ,mas um backup referente a informação contida no database.Apesar de funcional temos algumas complicações quando definimos este tipo de backup ,pois no momento do restore a janela necessária para se refazer o ambiente do database e o recuperacao da informação podem ser fatores que prejudiquem e muito o ambiente que esta utilizando o SGDB.

Com a utilizaçao do Rman conjuntamente o uso do Archivelog,temos um ambinete que alem de proteger as informações do SGDB de certa forma protege também todo o ambiente configurado do SGDB.Vou dar um exemplo :

Imaginemos um ambiente que trabalha 24 horas,7 dias da semana e que o backup utilizando o Rman só pode ser feito 3 dias na semana,ou seja dia sim ,dia não. Suponhamos que 20 horas após o ultimo backup schedulado,tivemos a perda de um datafile de uma tablespace muito importante.Apesar de conseguir fazer o restore com o Rman , perderiamos cerca de 20 horas de informações preciosas.Neste momento o archivelog vem nos salvar.Com os arquivos de archive ,conseguimos recuperar essas 20 horas de informação entre o backup do Rman e a pane que levou a perda do datafile.Muitas pessoas me perguntam a diferenca do recovery e do restore no Rman e fica um exemplo.

Portanto archivelog Sim !!!

Perda de um datafile de uma Tablespace

O que podemos fazer .

Exemplo considerando uso do modo archivelog e backup com Rman

(1) De um shutdown no database

(2) pelo Rman faça um restore dos datafiles que foram perdidos.

(3) pelo Rman faça um recovery do Banco.

(4) passe o banco para o status de open (startup open)

(5) Faça um backup das informações (exp ou Expdp)

(6) verifique a integridade da recuperação do Arquivo.

Perda dos controlfiles

o que podemos fazer para tentar recuperar as informações do controlfile perdido .

(1) Tenta levantar o banco em modo open,se não for possível , faça em modo nomount.
(2) execute o arquivo de trace,com o comando de recriação de datafile.
(3) Agora levante o banco em modo open.
(4) Faça backup full de seus dados.
(5) Analise a saúde do seu database.

Perda de Grupo de redo Log Online inativo

O que podemos fazer :
(1) shutdown immediate ;
(2) Startup mount ;
(3) Checar com view v$log para determinar se o arquivo foi arquivado.
(4) Checar no V$datafile para determinar se há algum datafile offline,que possa requere o log que não foi arquivado para traze-lo de volta.Faça um commando alter database clear logfile.As palavras unrecoverable datafile são necessárias.
(5) O datafile e sua tablespace devem ser dropadas,porque o redo necessário para traze-lo de volta para online foi "limpo" e não há cópia dele.
(6) adicione um novo grupo de redo log exemplo :
alter database add logfile group xxx size xx mb.
(7) drop o group file danificado
alter database drop logfile gorup xxxx onde xxxx é o group danificado
(8) adicione os menbros restantes do grupo excluido no novo grupo criado.
(9) abra o banco e faça um backup full dos seus dados.

Savepoints

Segue um exemplo bem simples de um comando que vejo ser muito pouco utilizado no controle de transações, o Savepoints .

insert into empregados (id_empregado,nome,sobrenome,salario) values (1,'Patricia','Moreira',1000) ;

insert into empregados (id_empregado,nome,sobrenome,salario) values (1,'Bruno','Gomes',1000) ;

COMMIT ;

UPDATE EMPREGADOS
SET SALARIO = 5000
WHERE SOBRENOME = 'MOREIRA';

SAVEPOINT BANDA_MOREIRA ;

UPDATE EMPREGADOS
SET SALARIO = 6000
WHERE SOBRENOME = 'GOMES';

SAVEPOINT BANDA_GOMES ;

SELECT SUM(salrio)
FROM empregados;

ROLLBACK TO SAVEPOINT BANDA_GOMES ;

UPDATE EMPREGADOS
SET SALARIO = 4000
WHERE SOBRENOME = 'GOMES' ;

commit ;

DML , DDL , DCL E TCL

Você sabe a diferença entre DML , DDL , DCL E TCL ? Se pretende fazer uma prova oracle é bom ter isso na cabeça.

DML (Data manipulation Language)
Select,Insert,Update,Delete e Merge

DDL(Data Defenition Language)
Create,Alter,Drop,Rename,Truncate e Comment

DCL ( Data Control Language)
Grant e Revoke

TCL (Transaction Control Language)
Commit,RollBack e Savepoint .

Exemplo de Cold Backup para Servidores Windows

Segue um exemplo de cold Backup :

Para o melhor funcionamento,devemos ter no servidor a seguinte estrutura de diretórios :

C:\cold_bkp_orcl\script .

Dentro do diretório C:\cold_bkp_orcl\script teremos os seguintes script

COLDBK.bat com o seguinte conteúdo :

rem --------------COLDBK.bat---------------
c:
cd\
cd cold_bkp_orcl
del *.* /F /Q
sqlplus "system/senha" @C:\cold_bkp_orcl\script\coldbk.SQL

sqlplus "system/senha" @C:\cold_bkp_orcl\script\Spfile-copy.SQL

COLDBK.sql com o seguinte conteúdo :

--------------COLDBK.SQL---------------
set term off
set head off
set feedback off
set verify off
spool C:\cold_bkp_orcl\startcopy.bat
select 'copy '||name||' C:\cold_bkp_orcl' from v$datafile;
select 'copy '||name||' C:\cold_bkp_orcl' from v$controlfile;
select 'copy '||name||' C:\cold_bkp_orcl' from v$tempfile;
select 'copy '||member||' C:\cold_bkp_orcl' from v$logfile;
create pfile='C:\cold_bkp_orcl\spfile.ora' from spfile ;
spool off
shutdown IMMEDIATE
host C:\cold_bkp_orcl\startcopy.bat
startup
host del C:\cold_bkp_orcl\startcopy.bat
set term on
set head on
set feedback on
set verify on
-------------------------------

basicamente fazemos o shutdown do banco de dados e depois fazemos cópias dos seguintes arquivos

datafiles
controlfiles
tempfiles
logfiles
SPfile

Apostila de RMAN - Conceitos Básicos

Pessoal . Fiz um "cata cata"de artigos bons sobre RMAN na internet e fiz esta pequena apostila, bem básica sobre a utilização e configuração do RMAN.

Segue o Link do material :

https://rapidshare.com/files/460918067/RMAN_-_CONCEITOS_BASICOS.pdf

Verificar se Indices devem passar por Rebuild

(1) -- CRIAR A TABELA ONDE ARMAZENAMENTOS AS INFORMAÇÕES

CREATE TABLE MMED_INDICE_STATUS (INDEX_NAME VARCHAR(100),PCT_DELETED NUMBER,PCT_USED NUMBER,BLOCK NUMBER,DTEXECUAO DATE DEFAULT SYSDATE) ;

ALTER TABLE MMED_INDICE_STATUS ADD CONSTRAINT PK_MMED_IND_STATUS PRIMARY KEY (INDEX_NAME,DTEXECUAO) ;


(2) -- RODAR A QUERY ABAIXO E PEGAR O RESULTADO DELA PARA EXECUAR

SELECT COMM
FROM(
SELECT 1 AS ID,
ROWNUM AS ID2,
'ANALYZE INDEX ' ||INDEX_NAME || ' VALIDATE STRUCTURE ; ' AS COMM
FROM DBA_INDEXES
WHERE OWNER = '&user'

UNION ALL

SELECT 2 AS ID,
ROWNUM ID2,
'INSERT INTO MMED_INDICE_STATUS SELECT NAME,TRUNC(100*(NVL(DEL_LF_ROWS,1)/NVL(LF_ROWS,1))) PCT_DELETED, PCT_USED ,BLOCKS ,SYSDATE FROM INDEX_STATS ;' AS COMM
FROM DBA_INDEXES
WHERE OWNER = '&user'
ORDER BY ID2,ID
)

(3) -- EXECUTAR O RESULTADO DO PASSO 3 E DAR COMMIT .

COMMIT ;



(4) -- VERIFICAR OS INDICES COM MAIS DE 30 % DE FRAGMENTAÇÃO

SELECT DISTINCT A.* ,B.TABLE_NAME
FROM MMED_INDICE_STATUS A
JOIN DBA_INDEXES B ON B.INDEX_NAME = A.INDEX_NAME
WHERE PCT_DELETED > 20 AND PCT_DELETED < 100
ORDER BY PCT_DELETED DESC


(5) -- AJUSTA OS INDICES QUE FORAM ENCONTADOS COM FRAGMENTAÇÃO MAIOR QUE 20 %

-- RODAR A QUERY ABEIXO E DEPOIS EXECUTAR O RESULTADO

SELECT DISTINCT 'ALTER INDEX ' || A.INDEX_NAME || ' REBUILD TABLESPACE TSP_CLINICA_INDEX ; ' AS RESULTADO
FROM MMED_INDICE_STATUS A
JOIN DBA_INDEXES B ON B.INDEX_NAME = A.INDEX_NAME
WHERE PCT_DELETED > 20 AND PCT_DELETED < 100

Views importantes

Segue uma lista de Views importantes que podem ser utilizadas mesmo com o banco de dados não estando no estado de open .

Views que podem ser utilizadas no estado de NOMOUNT

V$PARAMETER : Mostra informações sobre os parametros de inicialização do sistema.
V$SGA : Informações sobre a SGA da Instancia.
V$OPTION : opções da instancia ,exemplo se está em um ambiente em cluster ou se está utilizando ASM.
V$SESSION : informações sobre as sessões existentes na Instancia.
V$VERSION : Informações sobre a versões dos aplicativos que juntos formas o seu RDBMS.
V$INSTANCE : Informações básicas da instancia oracle.

Views que podem ser utilizados no estado de MOUNT.

V$CONTROLFILE : informações sobre os arquivos de controle.
V$DATAFILE : informações sobre os data files componentes das Tablespaces.
V$LOGFILE : informações dos arquivos de Redo Log que estiverem online.

Documentação Oracle

Muitos não conhecem mas a oracle tem um site oficial e aberto com toda documentação disponível sobre os seus produtos.Vejo alguns DBA reclamarem que este site não é tão atualizado como o MetaLink mas acredito que seja uma boa fonte de ganho de conhecimento.Segue a dica :

http://www.oracle.com/technetwork/indexes/documentation/index.html

Dicas Para Otimização de Queries

Pessoal ,Segue algumas dicas que devemos sempre pensar quando motamos um novo código Sql ,principalmente nas aplicações.

(1) Entenda como utilizar o Parce do Oracle.Isso Economiza Shared Pool

SELECT sysdate from dual ; é diferente de select sysdate from dual ;

Select * from Emp where id_emp = :pvar1 é diferente de Select * from Emp where id_emp = :pvar2

(2) Conheca os dados da sua aplicacao no momento da criacao de seu Sql.Seja seletivo na informacao que você ira trazer na sua consulta Sql.

(3) Conheça as Colunas candidatas a índices são apenas aquelas que são mencionadas após WHERE e AND, em uma cláusula SQL;

(4) não use colunas como índice se esta é sempre mencionada através do uso de uma função (exemplo, FLOOR ou ABS), ou manipulacão de string e conversão (exemplo, SUBSTR ou TO_NUMBER);

Exemplo: SELECT nm FROM empregado WHERE SUBSTR(nome,1,5) = ‘BRUNO’;

(5) Durante o processo de seleção das colunas para criaçao de um índices compostos, verifique aquelas colunas que são utilizadas no WHERE e AND juntas durante a aplicação em muitas situaçoes. Se elas são utilizadas separadamente no WHERE e AND tanto quanto em conjunto, dois índices simples pode ser melhor;

(6) Muito CUIDADO com Inner e Left Joins de suas SQL !!! Um query mal montada não tem indice que resolva !!!

Erros ao tentar conectar uma instancia Oracle

Agora iremos verificar alguns erro básicos referentes a conexão a instancias de SGDB Oracle.Como exemplo utilizaremos o sqlplus e o commando connnect com a seguinte sintaxe :

conn [usuario] /[senha]@[instancia]

Alguns erros comuns serão abaixo exemplificados com seus possíveis erros.

==> ORA-01017: invalid username/password; logon denied

você conseguiu estabelecer contato com sua instancia Oracle porem errou a senha do usuário .

==> ORA-12505: TNS:listener does not currently know of SID given in connect

Ou o SID é incorreto no arquivo tnsnames, ou o listener não está comnseguindo comunicacao com ele. Verifique o tnsnames.ora primeiro. Se lhe parece bem, fazer um 'status lsnrctl' em seu servidor, para ver quais bancos de dados que o ouvinte está ouvindo.

==> ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect

Isso é um erro comum e significa que, enquanto o listener se contactável, o banco de dados (ou melhor, o serviço) especificado no tnsnames não está respondendo ao listener.Provavelmente a instancia oracle desejada não está disponível.

Teste de Comunicação entre Client e o Listener

Para fazer um simples teste podemos utilizar o comando tnsping

Ex : tnsping orcl11g

No caso positivo da comunicação serão exibidas algumas informações num formato semelhante a mensagem abaixo :

Usado o adaptador TNSNAMES para resolver o apelido
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.100.202)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (10 ms)

Podemos receber uma mensagem de erro e ai vai algumas das mais conhecidas :

TNS-03505: Failed to resolve name

O nome do banco de dados especificado não foi encontrado no tnsnames.ora, onames ou LDAP. Isso significa que TnsPing nem mesmo ido tão longe como tentando fazer contato com um servidor - ele simplesmente não consegue encontrar nenhum registro de banco de dados que você está tentando TnsPing. Verifique se você digitou o nome do banco de dados corretamente, e que tem uma entrada no tnsnames.ora.

TNS-12545: Connect failed because target host or object does not exist

O host especificado no tnsnames não está contactável. Verifique se você digitou o nome do host corretamente. Se tiver, tente pingar o host com 'ping . Se o "host desconhecido" ping retorna, fale com o administrador da rede. Pode ser que você tenha um problema de DNS (você pode tentar usar o endereço IP se tiver a mão). Se você receber "host unreachable", mais uma vez falar com o responsável da rede, o problema poderia ser reduzido a um problema de roteamento ou firewall.

TNS-12541: TNS:no listener

O nome do host era válido, mas o Listener não estava contactável. Coisas a verificar é que o tnsnames tem a porta correta (e hostname) especificada, e que o ouvinte está rodando no servidor e usando a porta correta.

Verificar Hora que foi o ultimo Startup da Instancia

select to_char(startup_time, 'dd/mm/yyyy hh24:mi:ss') as "Startup time"
from v$instance ;
/

Recovery Manager (RMAN) Básico em exemplos - Parte 4

Exemplo de Restore/Recovery de um database :

Um banco de Dados inteiro
RMAN> STARTUP FORCE MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN;

Uma tablespace
RMAN> SQL 'ALTER TABLESPACE users OFFLINE';
RMAN> RESTORE TABLESPACE users;
RMAN> RECOVER TABLESPACE users;
RMAN> SQL 'ALTER TABLESPACE users ONLINE';

Um datafile
RMAN> SQL 'ALTER DATABASE DATAFILE 64 OFFLINE';
RMAN> RESTORE DATAFILE 64;
RMAN> RECOVER DATAFILE 64;
RMAN> SQL 'ALTER DATABASE DATAFILE 64 ONLINE';

Passos a serem seguidos :

(1) coloque o banco no status mount .
(2) Restore os arquivos necessários com o commando RESTORE.
(3) Recover os datafiles com o commando RECOVER .
(4) Volte o banco ao status de open

Verificar Objetos Inválidos na instância

SELECT OWNER,
NAME ,
TYPE,
LINE||'/'||POSITION "LINE_COL",
TEXT "ERROR"
FROM DBA_ERRORS
ORDER BY LINE

Recovery Manager (RMAN) Básico em exemplos - Parte 3

Comando Auxiliares para utilização do RMAN :

SHOW : Exibe as configuracoes permanentes do RMAN .Configuracoes como canais automaticos,estratégia de retenção , numero de cópias e etc... .

LIST : Exibe as informações sobre os backup efetuados.

REPORT : Bem mais sofisticado que as duas opções acima.Ira te mostar uma visão detalhado do repositório.

RMAN para Backup de Archive.

Os arquivos gerados pelo processo de archive log podem participar do backup do RMAN.

Utilizando o comando BACKUP ARCHIVELOG onde o escopo é a definicao de quais arquivos de archive participarão do Backup.

a opção de escopo padrao é a ALL ,mas temos from time, until time etc...

Recovery Manager (RMAN) Básico em exemplos - Parte 2

Para uma utilizacao mais profissional do RMAN.Deveremos configurar e alocar o canais (channels).Atraves dos canais podemos refinar nosso backup.

Para configurarmos um canal default podemos utilizar o exemplo abaixo :

configure default device type format Parallelism maxpiecesize

onde :
tipo : podemos utilizar disk ou sbt(fita).
formato : nome padrao dos arquivos a serem gerados (explicado opções na post parte 1)
n : Nível de paralelismo a ser utilizado
nn : Tamanho permitido para cada pedaco dos arquivos do backup.

A Alocação poderá ser feita no momento da execucao do backup com o commando allocate

exemplo :

allocate channel teste_ram type disk format 'c:\bkp\%s_%U.bkp' ;
backup

Recovery Manager (RMAN) Básico em exemplos - Parte 1

PASSO 1 - Garantir que o banco está com archivelog ativado.

Para executar o RMAN , no prompt do sistema operacional chame : Rman

c:\ rman target /


Comando BACKUP

Para eloborarmos backup temos que utilizar o comando backup com as seguites opções

Full : Todos os blocos do banco target serao copiados

Incremental Level : Nivel incremental do Backup

database : Determina a cópia de todos os datafiles.Podemos também especificar a
OPÇAO INCLUDE CURRENT CONTROL FILE

Tablespace : Permite determinar a tablespace a ser parte do backup

datafile : Permite especificar um o mais datafiles :

Format : Permite determinar o nome dos arquivos onde :

%s : Numero do Backup set
%p : Identifica o peice dentro do backup
%d : Possibilita a insercao do nome do banco target
%u : Insere um conjunto de 8 caracteres .
%U : igual a %u || %p || %c

Exemplo : BACKUP format'c:\bkp\%d_%s_%p.bkp' incremental level 0 database

Parametrosde inicialização modificaveis

select name
,      value
from   v$parameter
where  ismodified != 'FALSE'

Paramentos de incilização não Default

select  name
, value
from v$parameter
where isdefault = 'FALSE'
and value is not null
order by name

Identificar queries com parse Alto

(1) identificar as queries com maior numero de versões .Neste caso vamos verificar as consultas sql que tiveram mais de 20 versões de execução .

select sql_id,version_count from v$sqlarea where version_count > 20 ;

Exemplo de resultado :

SQL_ID VERSION_COUNT
-------------------------------------------------------------
56wzt9qfdnrju 63
9u8t5m5cqx844 81
0x5xgr3mcddb1 56
6thv1hstvkcg9 187
3083syarq6g7j 113

(2) vamos verificar qual o sql de uma destas versões .

select SQL_TEXT from v$sql where SQL_ID = '8p1pztfdxut3u' and rownum = 1

(3) depois disso pegamos o motivo das vários execuções com esse script abaixo :

select * from v$sql_shared_cursor where sql_id = '8p1pztfdxut3u'

Essa querie a partir da quinta coluna traz campos com resultado S ou N .As colunas que estiverem com 'Y'

estão dizendo o motivo dessa query filha (child_address) não ter utilizado o parse da query original (adress).

os motivos que podem acontecer são os seguintes :

UNBOUND_CURSOR,
SQL_TYPE_MISMATCH,
OPTIMIZER_MISMATCH,
OUTLINE_MISMATCH,
STATS_ROW_MISMATCH,
LITERAL_MISMATCH,
FORCE_HARD_PARSE,
EXPLAIN_PLAN_CURSOR,
BUFFERED_DML_MISMATCH,
PDML_ENV_MISMATCH,
INST_DRTLD_MISMATCH,
SLAVE_QC_MISMATCH,
TYPECHECK_MISMATCH,
AUTH_CHECK_MISMATCH,
BIND_MISMATCH,
DESCRIBE_MISMATCH,
LANGUAGE_MISMATCH,
TRANSLATION_MISMATCH,
BIND_EQUIV_FAILURE,
INSUFF_PRIVS,
INSUFF_PRIVS_REM,
REMOTE_TRANS_MISMATCH,
LOGMINER_SESSION_MISMATCH,
INCOMP_LTRL_MISMATCH,
OVERLAP_TIME_MISMATCH,
EDITION_MISMATCH,
MV_QUERY_GEN_MISMATCH,
USER_BIND_PEEK_MISMATCH,
TYPCHK_DEP_MISMATCH,
NO_TRIGGER_MISMATCH,
FLASHBACK_CURSOR,
ANYDATA_TRANSFORMATION,
INCOMPLETE_CURSOR,
TOP_LEVEL_RPI_CURSOR,
DIFFERENT_LONG_LENGTH,
LOGICAL_STANDBY_APPLY,
DIFF_CALL_DURN,
BIND_UACS_DIFF,
PLSQL_CMP_SWITCHS_DIFF,
CURSOR_PARTS_MISMATCH,
STB_OBJECT_MISMATCH,
CROSSEDITION_TRIGGER_MISMATCH,
PQ_SLAVE_MISMATCH,
TOP_LEVEL_DDL_MISMATCH,
MULTI_PX_MISMATCH,
BIND_PEEKED_PQ_MISMATCH,
MV_REWRITE_MISMATCH,
ROLL_INVALID_MISMATCH,
OPTIMIZER_MODE_MISMATCH,
PX_MISMATCH,
MV_STALEOBJ_MISMATCH,
FLASHBACK_TABLE_MISMATCH,
LITREP_COMP_MISMATCH,
PLSQL_DEBUG,
LOAD_OPTIMIZER_STATS,
ACL_MISMATCH,
FLASHBACK_ARCHIVE_MISMATCH,
LOCK_USER_SCHEMA_FAILED,
REMOTE_MAPPING_MISMATCH,
LOAD_RUNTIME_HEAP_FAILED,
HASH_MATCH_FAILED,
PURGED_CURSOR,
BIND_LENGTH_UPGRADEABLE

Backup com Datapump

Criacao do diretorio

create directory my_dir as 'c:\temp\'

Exportação

expdp system/****** schemas=scott dumpfile=scott.dp directory=my_dir

Importação

impdp system/****** schemas=scott dumpfile=scott.dp directory=my_dir

Edições do Oracle 11 G

Entenda as diferenças de cada versão :

http://www.oracle.com/us/products/database/product-editions-066501.html

Uso da PGA por sessões

SELECT SS.USERNAME,
SS.OSUSER ,
ST.SID "SID",
SN.NAME "TYPE",
CEIL(ST.VALUE / 1024 / 1024) "MB"
FROM V$SESSTAT ST
JOIN V$SESSION SS ON ST.SID = SS.SID
JOIN V$STATNAME SN ON ST.STATISTIC# = SN.STATISTIC#
WHERE UPPER(SN.NAME) LIKE '%PGA%'
AND SS.TYPE <> 'BACKGROUND'
ORDER BY ST.SID,ST.VALUE DESC

Capturar queries executadas em um determinado Periodo

(1) Primeiro crie a tabela que guardara as queires do momento 1 :

CREATE TABLE SQL_EXEC_BEFORE AS
SELECT EXECUTIONS
, HASH_VALUE
FROM V$SQLAREA

(2) Depois gere outra tabela para guardar as queries do momento 2 :

CREATE TABLE SQL_EXEC_AFTER AS
SELECT EXECUTIONS
, HASH_VALUE
FROM V$SQLAREA

(3) Com esta querie abaixo você identifica os sql que foram executados e iniciados entre o periodo 1 e 2 :

SELECT dd.HASH_VALUE ,dd.SQL_TEXT
FROM V$SQLTEXT dd
WHERE dd.HASH_VALUE in ( SELECT AFT.HASH_VALUE
FROM SQL_EXEC_BEFORE BEF ,SQL_EXEC_AFTER AFT
WHERE AFT.EXECUTIONS > BEF.EXECUTIONS
AND AFT.HASH_VALUE = BEF.HASH_VALUE (+)
)
ORDER BY
HASH_VALUE
, PIECE

Roles e Grants de um usuário

Select Usuario,Permissao
from(
Select x.Grantee As Usuario ,y.Granted_Role As Permissao
From Dba_Role_Privs X
join Role_Role_Privs y on y.Role = x.Granted_Role

union all

Select x.Grantee As Usuario ,y.PRIVILEGE As Permissao
From Dba_Role_Privs X
join Role_sys_Privs y on y.Role = x.Granted_Role

)
where usuario = upper(:usuario)
order by usuario desc

Oracle 11 G release 2 - DBA_users

Um nota interessante é que a senha criptografada do Dba_users na release 2 do oracle 11g não possue conteudo.

Com isso a velha tecnica de mudar a senha de um usuario.fazr modificacoes de dados e voltar a senha deste usuário com alter user identified by value não é mais possível.

Roles , Grants - Cuidados com permissões

Segue alguns scripts para identificarmos como estão relacionados as grants e roles padrões do oracle 11g.

Select Role ,Granted_Role As Filho , 'ROLE' As Tipo
From Role_Role_Privs
Union All
Select Role ,PRIVILEGE As Filho , 'GRANT' As Tipo
From Role_sys_Privs
order by role

Como dar acesso a todos os objetos de um schema para :

SELECT decode(object_type,
'TABLE','GRANT SELECT, INSERT, UPDATE, DELETE , REFERENCES ON'||&OWNER||'.',
'VIEW','GRANT SELECT ON '||&OWNER||'.',
'SEQUENCE','GRANT SELECT ON '||&OWNER||'.',
'PROCEDURE','GRANT EXECUTE ON '||&OWNER||'.',
'PACKAGE','GRANT EXECUTE ON '||&OWNER||'.',
'FUNCTION','GRANT EXECUTE ON'||&OWNER||'.' )||object_name||' TO MI_PROPIO_ROLE ;'
FROM user_objects
WHERE OBJECT_TYPE IN ( 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'PACKAGE','FUNCTION')
Order By Object_Type

Roles e Grants de um usuário :

Select Grantee, Granted_Role
FROM dba_role_privs ;

Pacote Statspack

Segue algumas dicas de como instalar e utilizar o pacote Statspack da Oracle.

(1) Intalacao

Crie um tablespace de pelo menos 300 mb
execute o comando que esta em : /rdbms/admin/spcreate

@?/rdbms/admin/spcreate

(2) Gerar uma analise do seu banco

Execute o comando :

exec statspack.snap;

(3) Eliminar uma Analise

Execute o comando :

@?/rdbms/admin/sppurge;

(3) Gerando um relatorio da analise

Execute o comando :

@?/rdbms/admin/spreport.sql

(4) Gerando um relatorio da analise

Execute o comando :

col "Date/Time" format a30
select snap_id
,       snap_level
, to_char(snap_time,'HH24:MI:SS DD-MM-YYYY') "Date/Time"
from stats$snapshot
, v$database
order by snap_id
/

(5) Removendo o Statspack

Execute o comando :

@?/rdbms/admin/spauto.sql

(6) Shedulando analise automatica

Execute o comando :

@?/rdbms/admin/spdrop


 Apesar de ser um recurso orinal do oracle 9i,continua sendo muito útil.