Dia a dia na T.I. [Vivência de um DBA]

ORAKILL - Matando processos Oracle em servidor com S.O. Windows.

2014-02-01T16:09:00.003-03:00

Muito boa tarde, a todos!

Hoje gostaria de contar uma experiência que tive com o Oracle (na versão 10G) rodando em um servidor com Windows Server 2003.

A situação foi mais ou menos a seguinte: um sistema consultava uma view, que fazia referência a um banco de dados remoto (via DBLink). Devido a um problema, o banco remoto ficou indisponível por algum tempo no momento de uma transação do sistema. Com isso, a projeção dos dados não fora possível, e simplesmente o banco não retornava nada e a aplicação ficava "tentando" consultar os dados. Depois de um tempo, o banco de dados remoto retornou, porém a session ainda estava tentando consumir a informação pela view. A uma altura dessa, o usuário que disparou a requisição já tinha saído ou fechado o browser, ou qualquer outra coisa, porém a session ficou executando no servidor de banco. Qualquer outra consulta na view não retornava nada, ou melhor, ficava rodando por horas sem concluir a projeção. Tentei recompilar a view e não conseguia. Criei uma outra view idêntica a anterior (imaginando que o problema podia ser no banco remoto), e consegui cria-la e usa-la normalmente. Então tentei matar a sessão que estava gerando o lock no objeto (através do comando: ALTER SYSTEM KILL SESSION 'sid,serial#';), mas não consegui sucesso.

O que precisava fazer estava claro, matar a session pois ela estava bloqueando o objeto.

Para fazer isso, precisei matar o processo/thread diretamente no servidor, que nesse caso possuía um sistema operacional Windows Server.

Em um ambiente UNIX, é possível ver todos os processos ativos no servidor, enquanto que no WINDOWS, os processos Oracle ficam ativos no programa oracle.exe como threads. No UNIX finalizamos uma sessão pelo SO com o comando: KILL -9 SPID. Podemos saber o SPID através da consulta:

select s.username, s.osuser, s.program, s.sid, s.serial#, p.spid

from v$session s,v$process p

where s.paddr = p.addr;

Já para Windows, para matar um processo usamos o comando ORAKILL, com a seguinte sintaxe:

ORAKILL SID THREAD

Onde SID: a instância Oracle;

e THREAD: a thread (spid da consulta acima) que deseja matar.

Exemplo:

orakill housework 902

Kill of thread id 902 in instance housework successfully signalled.

Com esse procedimento foi possível matar a session bloqueada, recompilar o objeto e acessá-lo normalmente.

Para auxiliar esse processo, o DBA também pode utilizar programas para matar a session. Burleson indica alguns:

- QuickSlice;

- PStat;

- Process Explorer.

Eu particularmente gosto do Process Explorer pois é bem prático. É possível ver a interface da ferramenta na figura abaixo.

Interface do Process Explorer. (Fonte: Autor do documento)

É possível fazer o download da ferramenta no site da Microsoft.

Bom pessoal, Por hoje é só!

Até a próxima!

Fonte:

BURLESON CONSULTING - ORAKILL TIPS. Disponível em: http://fabalvesdba.blogspot.com.br/2011/12/orakill-o-assassino-de-processos-oracle.html. Acessado: 01/02/2014.

FABIO ALVES - ORAKILL - O Assassino de processos oracle no windows. Disponível em: http://www.dba-oracle.com/tips_oracle_orakill.htm. Acessado: 30/01/2014.

DIOGO NOMURA - Orakill. Disponível em: http://dhnomura.blogspot.com.br/2009/02/orakill.html. Acessado: 01/02/2014.

SCOTT STEPHENS - Oracle Tip: Kill runaway Oracle processes on Windows with OraKill. Disponível em: http://www.techrepublic.com/article/oracle-tip-kill-runaway-oracle-processes-on-windows-with-orakill/. Acessado: 01/02/2014.

Heterogeneous Services – Conectividade entre Oracle e MS SQL Server

2013-08-22T14:16:00.000-03:00

Olá pessoal!

Recentemente realizei uma atividade e vou compartilhar com vocês. Surgiu uma demanda para mim para estabelecer conexão entre uma base de dados Microsoft SQL Server 2000 e um Oracle 10G. Para atender à demanda foi utilizada a tecnologia “Oracle Database Gateway for MS SQL Server”.

Existem algumas formas de se configurar o link entre bases heterogêneas (bancos de dados distintos), e o que utilizei foi via ODBC (Open Database Connectivity), conforme figura 1.

Figura 1 Oracle Gateway via ODBC. (Fonte: DATADIRECT)

Vou descrever o passo a passo que realizei para configurar.

1 – Configuração de um Data Source ODBC (fonte de dados)

Primeiramente foi necessário criar um OBDC para o banco SQL Server. Esse passo envolveu outra instituição, que teve que dar permissão ao IP do servidor Oracle para acessar o servidor MS SQL Server. Além da liberação do IP, a empresa me passou um usuário e senha do banco remoto para acesso e configuração.

2 – Configuração do INIT.ORA

Após o estabelecimento de conectividade entre os bancos via ODBC, foi realizada uma configuração no servidor Oracle.

No diretório “<ORACLE_HOME>\hs\admin\ “ (vale lembrar que esse banco Oracle estava instalado em um servidor Windows Server 2003), provavelmente existe um arquivo chamado inithsodbc.ora, que poderia ter sido utilizado, mas como precisava acessar duas bases SQL Server distintas, criei outros init.ora e não alterei o inithsodbc.ora.

Criação dos arquivos .ORA:

init<SID1>.ora

# This is a sample agent init file that contains the HS parameters that are

# needed for an ODBC Agent.

# HS init parameters

HS_FDS_CONNECT_INFO = DS_ODBC_SQLSERVER1

HS_FDS_TRACE_LEVEL = off

# Environment variables required for the non-Oracle system

#set <envvar>=<value>

init<SID2>.ora

# This is a sample agent init file that contains the HS parameters that are

# needed for an ODBC Agent.

# HS init parameters

HS_FDS_CONNECT_INFO = DS_ODBC_SQLSERVER2

HS_FDS_TRACE_LEVEL = off

# Environment variables required for the non-Oracle system

#set <envvar>=<value>

Existem outros parâmetros que podem ser configurados nesse arquivo, a alteração de apenas esses atende a minha necessidade. O parâmetro HS_FDS_CONNECT_INFO está setado para o data source ODBC que criamos anteriormente no passo 1.

É importante saber que o nome do arquivo deve ser iniciado com “init”, e que o SID1 e SID2 serão “interpretados” pela configuração do TNSNAMES.ORA e LISTENER.ORA., então merecem um cuidado especial.

3 – Criação/alteração de LISTENER.ORA

No diretório “<ORACLE_HOME >\network\admin\” foi necessária uma alteração no arquivo LISTENER.ORA. No meu caso, criei um listener dedicado para esse serviço, para não impactar nos demais.

As alterações foram as listadas abaixo:

(...)

SID_LIST_LISTENERHS =

(SID_LIST =

(SID_DESC =

(SID_NAME = SID1)

(ORACLE_HOME = <ORACLE_HOME> )

(PROGRAM = hsodbc)

)

(SID_DESC =

(SID_NAME = SID2)

(ORACLE_HOME = <ORACLE_HOME> )

(PROGRAM = hsodbc)

)

(...)

LISTENERHS =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = <xxxxxx IP_HOST_ORACLE xxxxx>)(PORT = 1522))

)

(...)

Foi criado um listener chamado “LISTENERHS” em outra porta: 1522. Atentar para a lista de serviços que o listener vai atender (SID_LIST_LISTENERHS), lá consta o SID1 e SID2, ambos referenciando o programa HSODBC.

Para startar o lisntener novo, utilize o comando:

lsnrctl start LISTENERHS

Se houver necessidade, seguem os comandos para verificar o status e parar o listener.

lsnrctl status LISTENERHS

lsnrctl stop LISTENERHS

4 – Alteração no TNSNAMES.ORA

No diretório “<ORACLE_HOME >\network\admin\”, deve ser alterado o arquivo TNSNAMES.ORA, para acresentar os novos acessos aos bancos SQL Server.

HS_ODBC _SID1 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = <xxxxxx IP_HOST_ORACLE xxxxx>)(PORT = 1522))

)

(CONNECT_DATA =

(SID = SID1)

)

(HS=OK)

)

HS_ODBC_SID2 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = <xxxxxx IP_HOST_ORACLE xxxxx>)(PORT = 1522))

)

(CONNECT_DATA =

(SID = SID2)

)

(HS=OK)

)

5 – Criação do DBLINK no Oracle Database

Foram criados dois dblinks privados em um schema do Oracle que irá gerenciar (receber e tratar) as informações do MS SQL Server.

CREATE DATABASE LINK "BDLINK_SID1"

CONNECT TO "<user_bd_sqlserver_1>"

IDENTIFIED BY "<pass_bd_sqlserver_1>"

USING ’ HS_ODBC_SID2';

CREATE DATABASE LINK " BDLINK_SID2"

CONNECT TO "<user_bd_sqlserver_2>"

IDENTIFIED BY "<pass_bd_sqlserver_2>"

USING ‘HS_ODBC_SID2’;

Lembrando que para os dblinks no Oracle, foram criados com os mesmos usuário/senha que foi configurado na fonte de dados ODBC, e o tns usado (USING) o que foi configurado no arquivo TNSNAMES.ORA.

Pronto!

Agora é só testar:

select * from tabela_sqlserverSID1@DBLINK_SID1;

select * from tabela_sqlserverSID2@DBLINK_SID2;

Bom, pessoal...

É isso!

Espero ter ajudado no entendimento da configuração usada por mim para resolver uma demanda de “comunicação” entre bancos heterogêneos: Oracle 10g e MS SQL Server 2000.

Até a próxima!

Referências:

ORACLE - Configuring Oracle Database Gateway for ODBC. Disponível em: http://docs.oracle.com/cd/B28359_01/gateways.111/b31043/configodbc.htm. Acessado em: 21/08/2013.

ORACLE - Oracle Database Gateway. Installation and Configuration Guide. 11g Release 1 (11.1) for Microsoft Windows. Disponível em: http://docs.oracle.com/cd/B28359_01/gateways.111/b31043.pdf. Acessado em: 20/08/2013.

BURLESON CONSULTING - Creating Multiple Listeners Tips. Disponível em http://www.dba-oracle.com/t_configure_multiple_listeners.htm. Acessado em:21/08/2013.

DATADIRECT - Connect for ODBC with Oracle database. Gateway for ODBC (DG4ODBC). Disponível em: http://www.datadirect.com/resources/odbc/oracle-database-gateway/index.html. Acessado: 21/08/2013.

Reorganizando os dados em uma tabela no Oracle Database

2013-07-30T22:21:00.001-03:00

Olá pessoal!

No artigo de hoje falarei sobre um assunto que entendo como uma extensão do artigo Recriação de índices - Rebuild Index que escrevi tempos atrás.

Bom, o assunto é como efetuar uma reorganização dos dados de uma tabela em um banco de dados Oracle. Uma tabela pode se apresentar fragmentada, ou seja, com espaços não utilizados entre os espaços usados pela tabela.

Li um artigo bastante interessante, onde o autor, Mohammad, afirma que existem quatro formas de se reorganizar tabelas fragmentadas:
1) alter table ... move + rebuild indexes
2) export / truncate / import
3) create table as select
4) dbms_redefinition

No post de hoje vamos falar sobre a primeira alternativa.

Para isso vamos criar uma tabela de teste, inserir dados e manipula-los com o objetivo de criarmos lacunas de espaços na tabela. depois vamos realizar uma reorganização dos dados do objeto visando o ganho de espaço, um melhor plano de execução das consultas contra a tabela, além de uma organização melhor dos dados.

Primeiro vamos criar a tabela conforme script abaixo:

CREATE TABLE tbl_reorg
(
codigo NUMBER (8) NOT NULL,
descricao VARCHAR2 (100) NOT NULL
);

Agora vamos popular a tabela:

BEGIN
FOR i IN REVERSE 1 .. 1000000
LOOP
INSERT INTO tbl_reorg (codigo, descricao) VALUES (i, 'registro da posição: ' || i);
END LOOP;
COMMIT;
END;

Verificando o tamanho da tabela após a inserção dos dados com o script abaixo. A projeção dos dados pode ser vista na figura 1.
SELECT SEGMENT_NAME, ROUND (SUM (bytes) / 1024 / 1024, 2) size_mb
FROM dba_segments
WHERE segment_name = 'TBL_REORG'
GROUP BY SEGMENT_NAME;

Figura 1: Tamanho da tabela TBL_REORG. (Fonte: autoria própria)

Vamos coletar as estatísticas da tabela pois como pode ser observado pela consulta abaixo, não existem dados estatísticos sobre o objeto.

SELECT owner,
table_name,
blocks,
empty_blocks,
num_rows,
TO_CHAR (last_analyzed, 'DD-MM-RRRR HH24:MI:SS') AS "ANALYZE"
FROM dba_tables
WHERE table_name = 'TBL_REORG';

O script abaixo coleta as estatísticas da tabela.
exec dbms_stats.gather_table_stats (ownname=>'HOUSEWORK',tabname=>'TBL_REORG',estimate_percent=>30,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>6);

Vale lembrar que existem outras formas de coleta de estatísticas. Falei um pouco sobre isso no artigo Estatísticas no banco de dados Oracle.

Agora vamos apagar alguns dados para gerar os espaços em branco na tabela.
delete from tbl_reorg where codigo between 10000 and 20000;
delete from tbl_reorg where codigo between 50000 and 200000;
delete from tbl_reorg where codigo between 400000 and 700000;
delete from tbl_reorg where codigo > 900000;
commit;

Vamos verificar o tamanho da tabela após algumas exclusões. O resultado pode ser visto na figura 2.
SELECT SEGMENT_NAME, ROUND (SUM (bytes) / 1024 / 1024, 2) size_mb
FROM dba_segments
WHERE segment_name = 'TBL_REORG'
GROUP BY SEGMENT_NAME;

Figura 2: Tamanho da tabela TBL_REORG após exclusão de alguns registros. (Fonte: autoria própria)

Bom...

Para resolvermos esse problema de fragmentação, basta reconstruir o mapa binário da tabela, para isso vamos usar o comando MOVE.

Não vamos mover a tabela para outra tablespace, vamos move-la na própria tablespace com o script abaixo:
ALTER TABLE tbl_reorg MOVE;

Verificando novamente o tamanho da tabela, se tem o resultado exibido na figura 3.

Figura 3: Tamanho da tabela TBL_REORG após exclusão de dados e MOVE. (Fonte: autoria própria)

Após a execução do MOVE, a tabela foi reorganizada e o tamanho da tabela caiu de 42MB para 19MB, fazendo apenas uma reconstrução dos extents da tabela.

É interessante também realizar uma nova coleta das estatísticas da tabela após o MOVE, para que seja "traçado" um plano de execução mais otimizado de consultas contra o objeto.

exec dbms_stats.gather_table_stats (ownname=>'HOUSEWORK',tabname=>'TBL_REORG',estimate_percent=>30,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>6);

Pois é pessoal...

Esse foi um dos métodos para resolver o problema da fragmentação de uma tabela no banco de dados, vale lembrar que essa situação pode causar perda de performance em um sistema de banco de dados então vale a pena ter um cuidado especial sobre o assunto.

Bom, é isso aí!

Até a próxima!

Referências:

Reis, Bruno - How to do reorg in a table in Oracle Database / Como fazer reorg em uma tabela no Banco de Dados Oracle. Disponível em: http://brunors.com/how-to-do-reorg-in-a-table-in-oracle-database-como-fazer-reorg-em-uma-tabela-no-banco-de-dados-oracle/. Acessado: 29/07/2013.

Almeida, Rodrigo - Entendendo a Marca d’água e fragmentação de tabelas. Disponível em : http://profissionaloracle.com.br/blogs/rodrigoalmeida/2008/10/07/entendendo-a-marca-dagua-e-fragmentacao-de-tabelas/. Acessado: 29/07/2013.

Taj, Mohammad - Table Fragmentation. Disponível em: http://dbataj.blogspot.com.br/2007/07/table-fragmentation.html. Acessado: 29/07/2013.

Tirinhas de humor sobre Banco de Dados.

2013-07-22T10:40:00.001-03:00

Olá pessoal,

Enquanto termino de escrever um material para postar, seguem algumas tirinhas do site Vida de Programador.

Essas são sobre banco de dados, mas tem várias outras bastante interessante. Acessem lá e confiram!

Segurança da informação é tudo! (Fonte: Vida de Programador)

SID do BD? Quem é esse? (Fonte: Vida de Programador)

Otimizando o banco de dados. (Fonte: Vida de Programador)

Fonte:

Vida de programador /*Linhas de código da vida de um programador*/ - Acesso à base de dados. Disponível em: http://vidadeprogramador.com.br/2013/05/02/acesso-a-base-de-dados/. Acessado em: 21/07/2013.

Vida de programador /*Linhas de código da vida de um programador*/ - SID do Banco. Disponível em: http://vidadeprogramador.com.br/2012/07/01/sid-do-banco/. Acessado em: 21/07/2013.

Vida de programador /*Linhas de código da vida de um programador*/ - Otimização do BD. Disponível em: http://vidadeprogramador.com.br/2012/08/03/otimizacao-do-bd/. Acessado em: 21/07/2013.

Geração de relatório Excel no SQL*Plus

2013-07-03T09:11:00.001-03:00

Olá pessoal.

Hoje vou falar sobre algo que pode ser bem interessante para administradores de banco de dados.

Muitas vezes nós DBA's recebemos solicitações para geração de relatórios em ambiente de produção (pelo menos onde trabalho isso é muito comum).

A questão é que sempre utilizamos o SQL*Plus para solicitações dos analistas e, na maioria das vezes, é desejado que a projeção da informação seja salva em planilha.

Para resolver essa situação, podemos utilizar um tipo de saída do SQL*Plus que torna o layout do arquivo gerado mais "apresentável". Na verdade, a projeção será gerada dentro de um código HTML, mas podemos gerá-lo como um XLS por se tratar de uma estrutura em tabelas através do markup html.

O comando, em uma forma bem básica, é o seguinte:

set feed off markup html on
spool c:\temp\relatorio.xls
SELECT * FROM hr.employees;
spool off
set markup html off

O arquivo gerado pode ser aberto no MS-Excel pois o spool foi para um ".xls".

Para se ter uma melhora na apresentação do arquivo, pode-se editá-lo retirando a consulta executada (que deve aparecer no início do arquivo) e o comando "spool off" (que deve aparecer no final do arquivo).

Bom pessoal, fica a dica!

Até a próxima.

Referências:

Oracle – Generating HTML Reports from SQL*Plus. Disponível em: http://docs.oracle.com/cd/B13789_01/server.101/b12170/ch8.htm. Acessado: 102/07/2013.

Vídeo: Desenvolvedores vs. DBA

2013-06-12T20:17:00.000-03:00

Meus caros,

Assisti a esse vídeo e achei muito engraçado e interessante.

Legal é que mesmo sendo uma edição, ficou muito bem casado com o texto, e com a realidade do dia-a-dia de um DBA, inclusive fazendo críticas à equipe de desenvolvimento (frequentes na vivência do profissional de banco).

Assistam e digam se não é bem real!

Arquitetura Oracle Database 11g - Estrutura de memória

2013-06-10T22:23:00.001-03:00

Pessoal,

Estou de férias do trabalho e estou aproveitando para resolver algumas coisas e viajar para descansar, por isso estou com pouco tempo para desenvolver algo para o Blog.

Bom...

Estava pretendendo escrever sobre a estrutura de memória do banco de dados Oracle. Foi então que em busca de material para referência, encontrei um vídeo que explica de forma muito interessante o assunto. Não gostei muito do áudio, mas achei o conteúdo legal.

Vejam e tirem suas próprias conclusões.

Valeu pessoal!!

Até a próxima...

Bilionários da T.I.

2013-05-27T12:22:00.001-03:00

Muito bom dia a todos!

Saiu na semana passada (se não estou enganado, no dia 24/05/2013) uma lista atualizada das pessoas mais ricas do mundo. Essa pesquisa foi elaborada pela Bloomberg. Como os assuntos abordados nesse blog são sobre T.I. (Tecnologia da Informação), abstrai dessa lista apenas os bilionários dessa área.

É importante salientar que Larry Ellison (da Oracle) está no TOP 10 da lista de bilionários. Muito justo! (hehehe)

Bom...

Segue a lista dos bilionários da T.I.:

Bilionários da T.I. (Fonte: Bloomberg)

1) Bill Gates (U$ 72.4 Bilhões) - cofundador da Microsoft #1

2) Larry Ellison (U$ 41 Bilhões) - fundador da Oracle #8

3) Larry Page (U$ 25.5 Bilhões) - cofundador e CEO do Google #20

4) Sergey Brin (U$ 25.2 Bilhões) - cofundador do Google #23

5) Jeff Bezos (U$ 24.5 Bilhões) - fundador e presidente da Amazon #24

6) Steve Ballmer (U$ 16.7 Bilhões) - CEO da Microsoft #44

7) Paul Allen (U$ 15.3 Bilhões) - cofundador da Microsoft #54

8) Michael Dell (U$ 14.8 Bilhões) fundador da Dell #59

9) Jim Goodnight (U$ 12.3 Bilhões) - CEO da SAS Institute #80

10) Mark Zuckerberg (U$ 12 Bilhões) - cofundador e CEO do Facebook #87

11) Lee Kun Hee (U$ 11 Bilhões) - presidente da Samsung Electronics #94

12) Azim Premji (U$ 11 Bilhões) - presidente da Wipro #96

Por hoje é só, pessoal!
É melhor seguir trabalhando que um dia chegaremos lá!! (hehehe)
Até a próxima.

Referências:

Bloomberg. Disponível em: http://www.bloomberg.com/billionaires/2013-05-24/aaa. Acessado: 26/05/2013.

Um pouco de conceitos da Oracle sobre pfile e spfile

2013-05-21T22:17:00.000-03:00

Olá pessoal!

O processo para inicialização de uma instância no banco Oracle é feito a partir da leitura do seu arquivo de inicialização, e os tipos desse arquivo é o assunto que vou abordar hoje.

Existem dois tipos de arquivos de inicialização para o Oracle: o PFILE (que é um arquivo texto conhecido também por init.ora) e o SPFILE que é um arquivo binário de parâmetros do servidor (conhecido como spfile.ora).

No post de hoje vou falar sobre alguns conceitos relacionados a esses arquivos, porém não vou entrar no detalhe sobre parâmetros, ou pelo menos não é minha ideia, pois o intuito desse artigo é falar sobre o que são esses arquivos, como e quando são usados.

O PFILE

Esse arquivo armazena os parâmetros de inicialização para "levantar" uma instância Oracle. Através desse arquivo, o DBA pode controlar a memória em seu banco de dados Oracle, atribuindo valores para os parâmetros de memória no arquivo INIT.ORA para o seu sistema. A localização deste arquivo varia, dependendo do sistema operacional em questão. Por exemplo:

- No UNIX ou Linux, o init.ora poderá ser encontrado em: $ORACLE_HOME/dbs

- No Windows, o diretório será: ORACLE_HOME/database

É possível que em alguns sistemas, exista mais de um arquivo init.ora, o que possibilita ter diferentes bases de dados com seus próprios parâmetros.

Por exemplo, initDESENV.ora pode controlar o banco de dados de desenvolvimento, initHML.ora o banco de dados de homologação, e initPROD.ora banco de dados de produção; ou ainda diferentes ambientes, como: initRH.ora, initFIN.ora, initADM.ora...

Como o PFILE é um arquivo de texto puro, ele pode ser editado no VI do UNIX ou no Notepad do Windows.

Para saber quais parâmetros podem ser alterados no PFILE, recomendo a leitura dos artigos: “INIT.ORA Parameters A-L” e “INIT.ORA Parameters M-Z” da TOAD WORLD.

O SPFILE (server parameter file)

O arquivo SPFILE é uma versão binária do PFILE. O SPFILE não pode ser alterado diretamente, porém através do comando ALTER SYSTEM é possível mudar alguns de seus parâmetros dinamicamente, ou seja, sem a necessidade de reiniciar o banco de dados. Para saber quais parâmetros podem ser alterados dinamicamente, pode-se executar uma consulta em uma view do schema SYS, conforme script abaixo:

SELECT * FROM V$PARAMETER;

O SPFILE possui algumas vantagens em relação ao PFILE, pois a Oracle criou procedimentos de otimização automática do banco baseadas no uso do SPFILE.

É possível verificar se existe o SPFILE com o seguinte comando:

SHOW PARAMETER SPFILE;

Para criar o SPFILE a partir do PFILE, pode-se utilizar os seguintes comandos:

CREATE SPFILE FROM PFILE;

CREATE SPFILE FROM PFILE=’/U01/APP/ORACLE/PRODUCT/11.2.0/DBHOME_1/DBS/initSID.ORA’

CREATE SPFILE=’/U01/APP/ORACLE/PRODUCT/11.2.0/DBHOME_1/DBS/spfileSID.ORA’ FROM PFILE=’/U01/APP/ORACLE/PRODUCT/11.2.0/DBHOME_1/DBS/initSID.ORA’

Após de qualquer um desses comandos, para efetivar a criação, é necessário reiniciar o banco:

SHUTDOWN IMMEDIATE;

STARTUP

Pode existir a necessidade, devido a alguma mudança dinâmica dos parâmetros do banco, que o DBA necessite gerar um novo init.ora (PFILE), a partir do SPFILE. O comando para realizar essa criação é:

CREATE PFILE FROM SPFILE;

Executando esse script, será criado um PFILE chamado init.ora no diretório $ORACLE_HOME/dbs (Linux/Unix) ou no $ORACLE_HOME/database (Windows).

É possível também que o DBA especifique um diretório de sua preferência para gravar o PFILE, basta executar comando:

CREATE PFILE=C:/TEMP/initTESTE.ORA FROM SPFILE;

Caso seja necessário iniciar um banco Oracle com o PFILE pode-se utilizar o seguinte comando:

STARTUP OPEN PFILE='/OPT/ORACLE/PRODUCT/9IR2/DBS/initSID.ORA'

Uma consulta interessante para saber se sua instância está utilizando o SPFILE ou PFILE, é apresentada abaixo:

SELECT DECODE (VALUE, NULL, 'PFILE', 'SPFILE') "INIT FILE TYPE" FROM SYS.V_$PARAMETER WHERE NAME = 'SPFILE';

É importante saber que para iniciar uma instância Oracle, os parâmetros de inicialização são buscados nos diretórios devidos (mencionados anteriormente com a variação entre UNIX e WINDOWS), na seguinte ordem:

1. spfileSID.ora

2. spfile.ora

3. initSID.ora

4. init.ora

Bom pessoal, por hoje é só!

Espero ter ajudado a entender um pouco sobre arquivos de inicialização no banco Oracle.

Sinta-se a vontade para deixar seu comentário com críticas, sugestões ou elogios sobre os artigos. Isso me ajuda a saber se agrado ou não com meus artigos e seus temas.

Grande abraço e até a próxima!

Referências:

ORACLE HOME. Disponível em: http://www.oracle-home.ro/Oracle_Database/Maintenance/PFILEvsSPFILE.html. Acessado em: 20/05/2013.

Carvalho, Pedro - Arquivos de Parâmetros PFILE e SPFILE do Oracle. Disponível em: http://www.pedrofcarvalho.com.br/PDF/ORACLE_SPFILE_PFILE.pdf. Acessado em: 20/05/2013.

Oracle - Starting Up a Database. Disponível em: http://docs.oracle.com/cd/B28359_01/server.111/b28310/start001.htm. Acessado em: 21/05/2013.

Oracle - Specifying Initialization Parameters. Disponível em: http://docs.oracle.com/cd/B28359_01/server.111/b28310/create005.htm. Acessado em: 21/05/2013.

TOAD WORLD - INIT.ORA Parameters A-L. INIT.ORA Disponível em: http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/ARC513A/Default.aspx. Acessado em: 21/05/2013.

TOAD WORLD - INIT.ORA Parameters M-Z. Disponível em: http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/ARC513B/Default.aspx. Acessado em: 21/05/2013.

TOAD WORLD - INIT.ORA Parameters. Disponível em: http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/ARC513/Default.aspx. Acessado em: 21/05/2013.

TOAD WORLD - Creating an SPFILE. Disponível em: http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/SPF1/Default.aspx. Acessado em: 21/05/2013.

Recriação de índices - Rebuild Index

2013-05-14T21:55:00.002-03:00

Olá pessoal!

Hoje vou falar um pouco sobre rebuild de índice no Oracle.

No dia 04/04/2013 postei uma situação que eu tinha vivido no trabalho. O nome do post foi: "Recuperar uma instância Oracleapós a exclusão acidental de um datafile". Em uma passagem do que escrevi, cito um comando (ALTER TABLE OWNER.TBL_TABELA MOVE TABLESPACE TBS50MB) que move uma tabela para outra tablespace (ou a mesma tablespace), porém em contrapartida torna os índices da tabela em questão "inutilizável", e é sobre isso que falarei agora!

Quando um índice está com status inutilizável (UNUSABLE), ele necessita ser reparado antes que possa ser usado. Diferentemente de um objeto PL/SQL por exemplo, pois quando é acessado a primeira vez o objeto é recompilado automaticamente pelo Oracle.

Para identificar se um índice está inutilizável, podemos usar a consulta abaixo:

SELECT owner, table_name, index_name

FROM dba_indexes

WHERE status = 'UNUSABLE';

Mas o que fazer para tornar o ínidice utilizável novamente?

Bom pessoal...

A forma que vou usar para tornar um índice novamente preparado para utilização, é muito importante também para desfragmentar o índice (caso o mesmo esteja fragmentado).

O problema da fragmentação aparece quando atualizamos ou apagamos (update/delete) dados de uma tabela. Se os campos alterados possuem índices associados, provavelmente o índice ficará fragmentado. Podemos dizer que a fragmentação é a existência de espaços não utilizados (ou disponíveis) no meio de um espaço utilizado. Isso porque os espaços que são liberados durante manipulação dos dados (update/delete) não são imediatamente reutilizado, o que resulta em “espaços livres" ou "blocos de dados espalhados nas tablespaces “.

Basicamente, a sintaxe que vou apresentar aqui é:

ALTER INDEX owner.nome_do_indice REBUILD;

Mas vamos fazer com que esse script seja gerado para todos os indices que estão inutilizáveis no banco.

SELECT ' alter index '

|| owner

|| '.'

|| index_name

|| ' rebuild; '

FROM dba_indexes

WHERE status = 'UNUSABLE';

A projeção dessa query pode ser usado para reconstruir os índices, e pode também desfragmentar o índice. Não necessariamente a desfragmentação do índice ocorrerá pois dependerá da disponibilidade de espaço livre da tablespace.

Por hoje é só!

Espero ter contribuído e agregado algum conhecimento a você leitor.

Fico grato por você ler meu post, e indico a leitura dos demais artigos publicados.

Um grande abraço e até a próxima!

Explicação sobre RAID

2013-05-10T11:54:00.006-03:00

Muito bom dia, pessoal!

Hoje vou compartilhar rapidamente com vocês uma figura que vi e achei muito interessante. Se trata de uma "explicação" sobre RAID que encontrei no blog do sábio DBA Ricardo Portilho.

Muito legal e se assemelha muito com a realidade!!

Confiram na figura abaixo.

Figura 1 - Entendendo o que é RAID. (Fonte: Blog do Portilho)

Referência:

Blog do Portilho - GPO Blogs. Entendendo RAID. Dsponível em: http://profissionaloracle.com.br/blogs/portilho/2009/07/28/entendendo-raid/ Acessado: 09/05/2013.

Um pouco sobre redo log file e archive

2013-05-04T22:28:00.001-03:00

Olá, meus caros!

Recentemente passei por um problema na empresa onde trabalho e achei um tema interessante para se abordar aqui no blog. Resumidamente a situação foi a seguinte: um dos servidores de banco de dados foi inesperadamente desligado, e quando a máquina foi ligada, no momento em que tentávamos “levantar” uma instância, não foi possível pois um dos arquivos de redo log estava corrompido. Foi uma situação muito tensa, pois era uma base importante para o negócio da empresa. Não vou entrar no detalhe do que aconteceu, tampouco falar da solução, mas motivado pelo problema ocorrido vou falar hoje um pouco sobre redo log file e archive.

Bom, pessoal...

Para começar, acho de extrema importância conhecermos algumas estruturas do banco. São elas: log buffer, log writer e archiver. Basicamente, o primeiro (log buffer) é uma área de memória pequena onde são gravadas instruções de desfazer uma alteração (comando DML) disparada contra a base, antes de essa instrução ser gravada no disco (no redo log file). Eu escrevi algo sobre essa estrutura com um pouco mais de riqueza no artigo: [Hard Parse]Parte 01 - Execução de comandos DML. O log writer é o processo de background responsável pela gravação do conteúdo do log buffer no redo log file. O archiver também é um processo de background, porém sua existência está condicionada a uma configuração da instância (ARCHIVELOG). O archiver copia o conteúdo dos arquivos de redo log (que são arquivos cíclicos e reutilizáveis – falarei melhor sobre isso) em arquivos chamados de archives (ou redo log arquivado). Cada uma dessas estruturas podem ser alvo de bastante discussão e conteúdo, que não focarei nesse post, mas é importante que entendamos ao menos o básico para não nos atrapalharmos mais adiante em outras explicações.

Redo Log File

Esse arquivo é uma cadeia contínua e cronológica de cada vetor de alteração (“instruções” para desfazer um DML de alteração – insert, update, delete). Uma utilidade de fácil entendimento desses arquivos é na recuperação de uma base após um problema que necessite “voltar” o backup. Para que não haja perda de trabalho até o momento do crash ou erro, os arquivos de redo log podem ser aplicados ao backup para refazer o trabalho, adiantando-os no tempo até o momento em que o dano ocorreu.

John Watson afirma que o redo log pode ser dividido em dois tipos: redo log online e o redo log arquivado (os archives).

Cada banco de dados deve ter por definição pelo menos dois redo log online. Arquivos de redo log compõe um grupo de arquivo, e são conhecidos como membros. Um banco de dados requer ao menos dois grupos com pelo menos um membro para funcionar. Para um melhor desempenho, podem ser criados mais de dois grupos, e para uma maior segurança, deve-se criar mais de um membro por grupo (caso opte por essa configuração não é necessário se preocupar com desempenho ou sincronismo, pois o processo responsável irá gravar em todos os membros de forma paralela mantendo-os idênticos). A razão do requisito mínimo para o banco Oracle funcionar ser um mínimo de dois grupos é devido à situação de que enquanto um dos grupos (o de status current) está recebendo as alterações (através de comando do processo de background Log Writer – LGWR), o outro está sendo “arquivado” (pelo processo de background Archiver – ARCn), ou seja, está sendo gerado um Archive a partir de um Redo Log File com status inactive. Um passo a passo do processo pode ser o seguinte: ao tempo em que as sessões dos usuários atualizam registros (no buffer cache), elas também gravam vetores de alterações mínimos no redo log buffer, e o LGWR continuamente grava os dados desse buffer para os arquivos que compõem o grupo de arquivos de redo log atual (com status current). Por serem de tamanhos limitados, os redo log se alternam quando estiverem completamente cheios, tornando o outro grupo o atual. Se o banco de dados estiver configurado para gerar Archive (o que é uma segurança para que não haja perda de dados), os processos ARCn arquivarão (ou copiarão) os membros do redo log file que compõe o primeiro grupo. Quando o segundo grupo é totalmente preenchido, o LGWR mudará de volta para o primeiro (setará seu status como current) e irá sobrescrever seu conteúdo (que já foi arquivado), ao mesmo tempo, o ARCn começará a arquivar o segundo grupo (de status inactive). Basicamente é dessa forma que se dá esse processo, de forma cíclica (alternância de log) e reutilizando os membros dos grupos de redo log file.

É importante saber que é possível adicionar ou excluir redo log files (inclusive com tamanhos diferentes dos demais) a qualquer momento, sem a necessidade de reiniciar a instância.

Para criar um novo grupo de redo log file, podemos usar o comando abaixo:

ALTER DATABASE ADD LOGFILE ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo') SIZE 4M;

Ou criar um novo grupo especificando o seu número:

ALTER DATABASE ADD LOGFILE GROUP 10 ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo') SIZE 4M;

E para adicionar um membro a um grupo, podemos utilizar o comando

ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2b.rdo' TO GROUP 2;

Ou de uma forma alternativa de identificar um grupo, que é identificando seus membros, conforme exemplo abaixo:

ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2c.rdo' TO ('/oracle/dbs/log2a.rdo', '/oracle/dbs/log2b.rdo');

Para excluir um grupo do redo log file, segundo a Oracle, existem três restrições. São elas:

- É necessário que a mais de dois grupos de redo log file, para após a exclusão ainda existirem o mínimo aceitável pelo banco de dados Oracle (conforme explicado anteriormente);

- Só é possível excluir grupos de redo log com status inactive. Para excluir o grupo atual (current), é necessário primeiro alternar o log para torná-lo inactive.

- Tenha certeza que o redo log file foi arquivado (se estiver no modo archive) antes de excluí-lo. Na view V$LOG é possível verificar se já ocorreu o arquivamento.

O comando da exclusão é o seguinte:

ALTER DATABASE DROP LOGFILE GROUP 3;

Para excluir um membro, deve-se atender aos requisitos citados para o grupo pois seguem as mesmas restrições. a Sintaxe é a seguinte:

ALTER DATABASE DROP LOGFILE MEMBER '/oracle/dbs/log3c.rdo';

Para forçar que o banco alterne o grupo de redo log atual, podemos utilizar o comando:

ALTER SYSTEM SWITCH LOGFILE;

Archive

Vamos falar um pouco sobre o tal redo log file arquivado, ou archive.

O banco Oracle garante que sua instância nunca fique corrompida, por meio dos redo log files, porém para garantir que não haja perda de dados após uma falha, é necessário ter um registro de todas as alterações aplicadas ao banco de dados desde o último backup. Por default, um banco de dados é criado em modo NOARCHIVELOG, o que significa que os redo log files são sobrescritos sem que tenha ocorrido o arquivamento dos seus dados. Quando alteramos o banco para o modo ARCHIVELOG, a Oracle afirma que é impossível que ocorra perda de dados (desde que os archives gerados após o último backup estejam disponíveis. Nesse modo, um novo processo de background (na verdade são quatro processos por default) será iniciado automaticamente: o Archiver (ARCn), isso nas versões do Oracle a partir do 10G.

É importante saber que a transição do modo NOARCHIVELOG para ARCHIVELOG (ou o contrário) só pode ser feita enquanto o banco está no modo mount após um shutdown e deve ser feito por um usuário com privilégio SYSDBA. Também é necessário fazer a configuração nos parâmetros de inicialização que controlam os nomes e os locais dos archives.

Para habilitar o modo ARCHIVELOG, podemos usar os comandos abaixo:

SQLPLUS /NOLOG

CONNECT / AS SYSDBA

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=’LOCATION=/oracle/dbs/archives’ SCOPE=SPFILE;

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE OPEN;

Bom pessoal…

Por hoje é isso!

Espero ter ajudado no entendimento sobre o redo log file do Oracle e o Archive.

Grande abraço a todos e até a próxima.

Referências:

Oracle® Database Administrator's Guide - 11g Release 1 (11.1) - Creating Redo Log Groups and Members. Disponível em: http://docs.oracle.com/cd/B28359_01/server.111/b28310/onlineredo003.htm. Acessado em 03/05/2013.

Oracle® Database Administrator's Guide - 11g Release 1 (11.1) - Dropping Redo Log Groups and Members. Disponível em: http://docs.oracle.com/cd/B28359_01/server.111/b28310/onlineredo005.htm. Acessado em 03/05/2013.

WATSON, JOHN – OCA Oracle Database 11g – Administração I – Guia do Exame 1z0-052. Editora: BOOKMAN.

ODA - Oracle Database Appliance

2013-04-25T23:09:00.000-03:00

Muito bom dia a todos!

Hoje vou fazer um breve comentário sobre a “tecnologia” da Oracle conhecida como ODA.

Para dar início, gostaria de informar que não trabalho com ODA, mas tenho estudado bastante sobre o sistema e o acho bastante interessante.

Bom...

ODA é a abreviação de Oracle Database Appliance, que segundo definição Oracle é um sistema projetado que consiste em hardware e software que economiza tempo e custo para os clientes, simplificando a implantação, manutenção e o suporte de soluções de banco de dados de alta disponibilidade.

Com essa tecnologia, a Oracle proporciona um sistema totalmente integrado de software, servidores, storage, e conectado à rede em um único equipamento que oferece serviços de alta disponibilidade em banco de dados, aplicável tanto para serviços operacionais (OLTP) quanto para aplicações gerenciais em data warehouse (OLAP). A figura 1 exibe a parte da frente de uma ODA.

Figura 1 - Frente da ODA. (Fonte: Oracle Corporation)

No vídeo abaixo é possível ter uma visão melhor do que é a ODA.

Falando um pouco dos recursos de uma ODA:

✔ Equipamento de banco de dados completamente integrado em um equipamento único
✔ Componentes com redundância e hot-swap: servidor, armazenamento, placa de rede, fonte e ventilador
✔ Sistema Operacional Oracle Linux
✔ Dois Servidores de banco de dados
✔ 24 núcleos de processadores
✔ 192 GB de memória
✔ 12 TB de armazenamento com triple-mirror (espelhamento triplo), oferecendo 4 TB de armazenamento em RAID-1
✔ 292 GB de armazenamento em drive de estado sólido
✔ Oracle Database, Enterprise Edition 11g

✔ Oracle Real Application Clusters ou Oracle Real Application Clusters One Node

✔ Oracle Automatic Storage Management (ASM)
✔ Oracle Enterprise Manager

Abaixo é possível conferir uma apresentação de Renato Martins sobre ODA.

O valor estimado para um sistema desse, segundo a 4Partner, está a partir de R$ 260.000,00.

Para concluir, indico que assistam esse vídeo abaixo que trás uma explicação sobre a ODA com um toque de humor. É muito bom!

Bom pessoal, por hoje é só!

Espero ter ajudado.

Até a próxima

Fonte:

4Partner - Oracle Database Appliance. Fácil. Confiável. Acessível. Disponível em: http://oracledatabaseappliance.com.br/oracle/hardware/oracle-database-appliance/solucao-completa-de-hardware-e-software.html. Acessado em: 22/04/2013.

Oracle Corporation - About Oracle Database Appliance. Disponível em: http://docs.oracle.com/cd/E22693_01/doc.21/e22692/abtoda.htm. Acessado em: 22/04/2013.

Felipe do Carmo - Oracle Database Appliance. Disponível em: http://felipedocarmo.blogspot.com.br/2011/12/oracle-database-appliance.html. Acessado em: 2504/2013.

Documentos úteis para um DBA

2013-04-23T20:39:00.003-03:00

Olá a todos!

Hoje quero falar um pouco sobre algo que li recentemente em um artigo publicado em 2007 e achei bastante interessante. O artigo fala sobre alguns documentos que um DBA deve lidar. Esses documentos são úteis para organizar e gerenciar atividades do DBA, além do mais, um documento também serve de registro técnico sobre algo.

Bom...

Para começar queria deixar claro uma coisa... documentar é um trabalho muito chato!! Hehehe

Brincadeiras a parte, a documentação é algo de extrema importância, e temos que ter sempre em mente que criar ou atualizar um documento não é apenas um trabalho desnecessário que consome tempo e torna uma demanda mais lenta. Documentação é um item necessário e obrigatório em qualquer projeto, servindo a vários propósitos. As metodologias estão aí para isso, e logicamente, devido à maturidade dos processos, agregam muito valor ao serviço.

É bastante normal na T.I. (vou corrigir isso, pois normal não é!!)... É bastante frequente na T.I. encontrar profissionais que não gostam de documentar, seja esse documento de qualquer natureza. Talvez seja uma cultura que trazemos da escola ou faculdade, mas que devemos mudar!

Li certa vez (não me recordo onde), que em termos de documentação nos serviços de T.I., as mulheres são muito mais comprometidas e responsáveis que os homens. E levando em consideração minha experiência profissional, eu concordo plenamente com isso! Não podemos generalizar, mas em uma visão superficial pode-se notar que mulheres são mais organizadas e dedicadas em trabalhos de documentação (obviamente existem exceções).

Segundo Pichiliani, “em geral, é difícil encontrar profissionais, em particular DBAs, que trabalhem com muita documentação. Isso se deve tanto à questão cultural como a questão que envolve a disponibilidade de tempo para criar e manter a documentação atualizada.”

A seguir é apresentada uma lista de documentos elencadas por Pichiliani, úteis tanto para quem trabalha tanto como DBA como analista ou desenvolvedor.

1) MER (Modelo Entidade Relacionamento)

O MER, chamado também de modelo de dados ou diagrama de entidade-relacionamento, é a principal documentação de um banco de dados. Nesse documento deve constar todas as entidades (com seus atributos, tipos de dados e restrições) do banco e seus relacionamentos. O MER pode ser de três tipos: conceitual, lógico e físico, e cada um desses têm uma particularidade. Não entrarei no detalhe, mas recomendo a leitura do artigo de Adail Faleiro, que aborda de forma objetiva o assunto.

2) Documento de Padrões de Variáveis

Utilizar nomes de fácil entendimento em classes, métodos, atributos, etc, é cada vez mais frequente como pré-requisito para quem trabalha com desenvolvimento. Sendo assim, utilizar padrões é sempre uma boa prática de desenvolvimento. Com foco em banco de dados, possuir um padrão de nomes para tabelas, colunas, restrições, procedimentos, funções e demais objetos de dados é muito importante. Segundo Pichiliani, a idéia é possuir um padrão eficaz que seja fácil de ser utilizado, além de fazer sentido no contexto de banco de dados. Para formalizar a adoção do padrão, Pichiliani recomenda criar um documento explicando os detalhes deste padrão tais como: se os nomes devem ser em português, se as palavras devem estar no plural ou se há um limite no tamanho da coluna.

3) Plano de Capacidade

Fiz um post recentemente sobre as tarefas de um DBA, e das atividades que cito (conforme orientação da própria Oracle) é a de “avaliar o hardware do servidor de banco de dados”, e de acordo com Pichiliani, além de economizar recursos, dimensionar os recursos necessários mostra que existe um controle não apenas para o ambiente atual, mas também para os recursos que podem ser necessários no futuro. Para que esse dimensionamento seja registrado (podendo evitar possíveis problemas), o DBA deve elaborar um documento de Plano de Capacidade. Neste documento, informações sobre espaço de armazenamento e crescimento médio dos ambientes, utilização de CPU, utilização de memória e outros requisitos técnicos; devem estar contidos para exibir o funcionamento do servidor atual e até embasar aquisição de novos recursos (hardware ou software).

4) Dicionário de dados

O dicionário de dados é um documento onde deve constar uma coleção de metadados que contém definições e representações de elementos de dados. Ele pode ser considerado um documento complementar ao MER. No documento deve conter algumas das seguintes informações definição sobre os objetos, perfis de usuários, papéis e privilégios, restrições de integridade, valores possíveis para um campo, quantidade típica de valores armazenados, etc. Vale lembrar que este documento deve sempre estar atualizado com a base de dados atual, para evitar desgastes e problemas.

5) Política de segurança

De acordo com Pichiliani, um documento que informa a política de segurança é um objeto não necessariamente técnico que envolve os procedimentos, responsabilidades e atribuições relacionadas à segurança das informações e disponibilização delas. Geralmente documentos dessa natureza contêm regras de usuários e senhas, tais como: nível de dificuldade da senha, atualização periódica dela, bloquei após repetidas tentativas mal sucedidas.

6) Acordo de nível de serviço (ANS) ou SLA (Service Level Agreement)

O SLA é um acordo firmado entre a área de T.I. e seu cliente, que descreve o serviço, suas metas de nível de serviço, além dos papéis e responsabilidades das partes envolvidas no acordo. Este acordo deve deixar claro quais serviços estão sendo oferecidos (serviços específicos) e o nível de cada serviço (horas de funcionamento, downtime, horário do suporte, etc). Segundo Pichiliani, e com foco em banco de dados, pode-se utilizar um SLA interno, onde o DBA se compromete a dar algum tipo de retorno (feedback) ao solicitante, nem que seja apenas a informação de que está ciente da solicitação (mesmo que ainda não tenha uma solução para a situação). Acordo de nível de serviço é algo que está muito em alta no mercado de T.I., por isso sugiro a leitura da biblioteca ITIL V3.

7) Diagrama de arquitetura

Geralmente as organizações possuem diversos ambientes de banco de dados, que podem ser separados de acordo com sua utilidade, como por exemplo:

- ambiente de desenvolvimento – banco utilizado por desenvolvedores e analistas de sistemas para criação de aplicativos;

- ambiente de homologação – banco utilizado para realização de validações de usuários antes de implantação de um aplicativo;

- ambiente de produção – onde os usuários finais trabalham com os dados reais dos sistemas.

O DBA deve elaborar um diagrama de arquitetura para facilitar o gerenciamento desses ambientes, onde deverá apresentar de forma gráfica, quais servidores pertencem ao ambiente de desenvolvimento, homologação e produção. Este tipo de diagrama contém informações relacionadas à estrutura arquitetural dos ambientes e é extremamente útil para quem não conhece a organização física e lógica dos componentes da rede e dos servidores.

8) Estratégia de Backup

Mais uma das tarefas de todo DBA que postei num artigo passado é a de “Realizar backup, restaurar e recuperar o banco de dados”. Todo DBA deve possuir uma estratégia de backup adequada que deve ser criada de acordo com as necessidades de recuperação e disponibilidade do sistema, ou seja, vai depender do quanto de downtime e tempo de recuperação é aceitável de acordado com os SLA’s. Esse tipo de documentação é importante para registro oficial sobre o que pode ser recuperado e em quais circunstâncias.

É isso aí, pessoal...

Obviamente existem vários outros documentos importantes para um DBA, mais esses são alguns dos mais interessantes, e que todo DBA deve manter.

Espero ter ajudado!

Até a próxima.

Referências:

Pichiliani, Mauro - Documentação do DBA – 11/06/2007. Disponível em: http://imasters.com.br/artigo/6392/sql-server/documentacao-do-dba/ . Acessado em: 18/04/2013

Faleiro, Adail Tiago Lima - Modelo Entidade Relacionamento (MER). Disponível em: http://www.devmedia.com.br/modelo-entidade-relacionamento-mer/19821. Acessado em: 19/04/2013.

Vídeo sobre recuperação de desastre

2013-04-18T23:08:00.000-03:00

Olá a todos!

Estava procurando um documento em minha máquina, quando me deparei com anotações que tinha realizado enquanto cursava minha especialização em banco de dados. As anotações eram da disciplina “Contingência e Alta Disponibilidade”. Me lembrei de um vídeo muito interessante que o professor tinha exibido para a turma. O vídeo era sobre um teste de recuperação de desastre com uma solução da HP.

Foi então que comecei a procurar o vídeo e achei!

Confiram e tirem suas conclusões.

Esse vídeo está no youtube, mas seu endereço original está no site da HP.

Em breve, quando estiver com um tempinho a mais extra, falarei sobre algumas coisas que aprendi sobre contingência e alta disponibilidade, focando mais para a área de banco de dados.

Até a próxima!

Tarefas de um administrador de banco de dados Oracle

2013-04-11T21:40:00.000-03:00

Olá a todos!

Tenho andado bastante sobrecarregado no trabalho e em outras atividades, o que tem resultado em pouco tempo de dedicação a posts aqui no blog.

Mas vou superar isso! hehehe

Bom... Hoje vou falar sobre algo bem básico que são as tarefas de um Administrador de Banco de Dados.

Identificar um DBA não é uma tarefa fácil, mesmo porque “tudo sempre é culpa do DBA”. Se ocorre um crash no banco no banco, chamam o DBA, até aí tudo bem, mas o DBA também pode ser chamado se a rede falhar, se os servidores derem uma pane ou se ocorrer um erro na aplicação, se a impressora está sem tinta ou se o telefone está com defeito...hehe (esses últimos acredito que qualquer profissional de T.I. está sujeito). Brincadeiras a parte, imaginasse que algumas dessas situações estão relacionadas às funções do DBA porque praticamente qualquer falha no ambiente de T.I. impede que os usuários finais acessem o banco de dados, logo faz do DBA o primeiro ponto de contato natural.

Abstraindo essas expectativas excessivas , seguindo as orientações da Oracle, que inclusive é cobrado no exame (1Z0-052 na versão 11G) para a certificação OCA, as tarefas de um DBA se resumem, segundo John Watson, basicamente nas seguintes:

Avaliar o hardware do servidor de banco de dados

Este item diz respeito à realização de previsões precisas relacionadas à quantidade de memória, espaço em disco e CPU. O DBA deverá saber dimensionar os recursos necessáriaos para garantir que as aplicações serão bem executadas sem demandar recursos desnecessários, mantendo assim um desempenho bom sem extrapolar o orçamento disponível.

Instalar e manter o software Oracle

A instalação de um banco de dados Oracle e a criação de instâncias, é algo básico para um DBA, mas é de fundamental importância que este saiba também realizar instalações de patches críticos e patches de manutenção. Obviamente que antes de se implantar uma atualização em ambiente de produção, os patches devem ser adequadamente testados, uma vez que o DBA é o responsável pelo bom funcionamento do ambiente de banco de dados.

Planejar o banco de dados

Configurar o armazenamento físico de um banco de dados podem impactar na performance dos sistemas e seu gerenciamento. O DBA deve também estar ciente do impacto de diferentes estruturas de armazenamento nos dispositivos, como sistemas de discos e fitas.

Monitorar e ajustar o desempenho do banco de dados

Essa é uma atividade que deve ser contínua e frequente para os sistemas de banco de dados. Um bom DBA será capaz de antecipar os problemas de desempenho e corrigi-los antes que surjam, sempre trabalhando de forma proativa através, por exemplo, da realização de tuning (de configuração e de queries).

Auxiliar os desenvolvedores nos projetos de aplicações e ajustes de SQL

Alguns DBAs gastam muito tempo ajustando SQL, porém há quem diga (e particularmente concordo) que essa tarefa é função dos programadores. Onde o DBA pode participar, é ajudando a identificar as áreas com problemas para que eles (os desenvolvedores possam) resolver, e evitar problemas futuros.

Manter contato com fornecedores, usuários finais, desenvolvedores, gerentes e outros grupos de suporte

Como técnico com enfoque mais completo do ambiente, o DBA deve ter um papel de “liderança” na coordenação de planejamentos e ações de todos os grupos envolvidos no ambiente de T.I.

Realizar backup, restaurar e recuperar o banco de dados

Possivelmente a tarefa mais importante de um DBA é essa. O DBA deve criar rotinas que deverão garantir a disponibilidade do ambiente de banco de dados, preferencialmente combinando o mais próximo possível de 100% de tempo de atividade e 0% de perda de dados.

Não há certo ou errado, apenas a conformidade (ou a falha dela) aos objetivos combinados, e para garantir que a tarefa será bem sucedida, testes frequentes devem ser realizados.

Gerenciar os usuários do sistema e manter a segurança do banco de dados

Essa é outra parte crítica do trabalho do DBA. Como na tarefa de manter a disponibilidade do ambiente, para a atividade de segurança também não há certo e errado, apenas a conformidade com os acordos realizados. O DBA deve configurar os procedimentos que garantirão a conformidade e monitorar a seu funcionamento.

Bom pessoal...

Era isso que tinha para falar por hoje, o que obviamente não define a função de um DBA, mas trás características e funções interessantes. Vale lembrar que o amplo escopo da função de um DBA requer estudo contínuo e desenvolvimento pessoal, estudo do banco de dados Oracle e tecnologias relacionadas. Também requer vocação para educar e disseminar o conhecimento, talvez a parte mais gratificante do trabalho, por isso que escrevo e publico esses artigos aqui no blog.

Espero ter ajudado!

Até a próxima!

Referência:

WATSON, JOHN – OCA Oracle Database 11g – Administração I – Guia do Exame 1z0-052. Editora: BOOKMAN.

Recuperar uma instância Oracle após a exclusão acidental de um datafile

2013-04-04T23:15:00.002-03:00

Muito boa noite a todos!!

Hoje vou compartilhar com vocês mais um problema que passei, e realmente fiquei preocupado até achar a solução. Foi algo muito interessante e acho que muitos já devem ter passado por problemas parecidos (ao menos em nível de stress).

A história é mais ou menos a seguinte...

Estou realizando um trabalho de padronização de TABLESPACES em todas as bases de dados Oracle da organização onde trabalho. Esse trabalho consiste basicamente em criar as “N” tablespaces, divididas por tamanhos de extents distintos, como por exemplo: TBS50MB, TBS100MB, TBS500MB, etc.

Para alocar as tabelas e índices em uma tablespaces compatível com o tamanho do objeto, foi realizado um cálculo (que não vem ao caso) para que a tabela “residisse” em uma tablespace compatível com seu tamanho. Essa alteração é importante para muitas coisas, inclusive para facilitar o gerenciamento e melhorar performance dos scripts contra esses objetos.

O procedimento que estava realizando, e ainda estou pois são muitas bases, era o seguinte:

1- Criação das tablespaces “corretas”;

2- Realização da movimentação das tabelas e índices das tablespaces “antigas” para as novas através dos scripts abaixo:

a. ALTER TABLE OWNER.TBL_TABELA MOVE TABLESPACE TBS50MB;

b. ALTER INDEX IDX_TBL_01 REBUILD TABLESPACE TBS50MB;

3- Após mover todos os objetos da tablespace, realizava o drop da tablespace antiga com o comando:

a. DROP TABLESPACE TBSOLD INCLUDING CONTENTS AND DATAFILES;

4- Depois de dropar a tablespace, acessava o servidor da base em questão e realizava a exclusão física do datafile (pois às vezes não eram excluídos pelo comando acima) e às vezes tinha que “derrubar” e iniciar a instância para conseguir excluir o datafile. Fazia isso para liberar espaço em disco gasto desnecessariamente, uma vez que os datafiles não seriam mais utilizados.

Basicamente foi isso que estava fazendo (mas pode acreditar, não é tão simples quanto parece).

Foi então que em uma das execuções, acabei interrompendo o trabalho para fazer alguma outra coisa, e quando retornei para essa atividade, executei o descrito no passo 4 antes do passo 3, ou seja, excluí o datafile antigo (e vazio) fisicamente antes de dropar a tablespace. Resultado, quando tentei “levantar” a instância, não consegui, pois quanto realizei o startup, foi solicitado o datafile excluído acidentalmente.

Na hora que dei conta de que tinha feito uma besteira, fui logo verificar como estava o backup, pois seria o pior dos casos realizar o recovery da base. Obviamente comecei essa tarefa de padronização das tablespaces pelas bases de teste e desenvolvimento, o que me fez respirar mais tranquilo para manter a “estabilidade emocional” (hehe).

Conversei com um colega também DBA sobre o que tinha acontecido e começamos a ver as possibilidades de solução que tínhamos para a situação. Procurei na internet sobre o assunto, foi então que encontrei, em um blog, um artigo com a seguinte chamada: How Drop Tablespace and Recover Oracle Database When Accidentally Delete Datafile.

Pessoal, isso foi um “achado”! (hehehe)

O autor expos uma situação muito parecida com a que vivi e descrevi acima, e a solução que ele apresentou foi mais ou menos a seguinte:

1- Conectar no SQL*Plus como SYSDBA

a. SQLPLUS /NOLOG

b. CONNECT / AS SYSDBA

2- O banco de dados deve estar no estado mount para conseguirmos executar o comando, então montemos o banco:

a. STARTUP MOUNT;

3- Em seguida, o comando que tornará o datafile off-line, e nos permitirá iniciar a instância (abrir o banco):

a. ALTER DATABASE DATAFILE ' /opt/oracle/oradata/workspace/TBSOLD01.dbf' OFFLINE DROP;

4- Agora vamos abrir o banco:

a. ALTER DATABASE OPEN;

5- Agora sim! Com a instância iniciada pude finalmente eliminar a tablespace do banco com o comando:

a. DROP TABLESPACE TBSOLD INCLUDING CONTENTS AND DATAFILES;

É isso aí pessoal!

Realmente foi um susto o que ocorreu, um problema que pode acontecer com qualquer um (ou não... hehehe), mas felizmente foi tudo resolvido de forma tranquila.

Foi um ótimo aprendizado mas espero que vocês não passem por isso, porém caso ocorra, já sabem com solucionar, ok?!?!

Por hoje é só e até a próxima!

Referência:

MY DIGITAL LIFE - How Drop Tablespace and Recover Oracle Database When Accidentally Delete Datafile. Disponível em: http://www.mydigitallife.info/how-drop-tablespace-and-recover-oracle-database-when-accidentally-delete-datafile/. Acessado em: 04/04/2013.

Particionamento de dados no banco de dados Oracle

2013-04-02T21:12:00.000-03:00

Olá a todos!

Hoje vou falar sobre particionamento de tabelas e índices no banco de dados Oracle, mas precisamente sobre uma situação que passei recentemente no trabalho.

Para entender a situação que passei, é necessário um conhecimento básico sobre particionamento de tabelas e índices no banco de dados Oracle. Particionamento é alvo de muitos artigos e documentos oficiais da Oracle, portanto não quero entrar nesse mérito, mas recomendo a leitura de Borovina e Legatti que explicam de forma clara e objetiva as definições e conceitos envolvidos. Também tem uma vasta documentação da Oracle disponível em vários artigos, um deles fala sobre: Particionamento no Banco de Dados Oracle 11g. Os links para esses artigos são citados nas referências desse post.

Agora vou apresentar de fato a situação que vivi:

Em uma instância Oracle 10g (na versão 10.2.0.3) tenho uma tabela particionada por lista.

A estrutura da tabela é mais ou menos a seguinte:

CREATE TABLE TBL_TABELA

(

COD NUMBER(9) NOT NULL,

CADASTRO NUMBER(9) NOT NULL,

MES NUMBER(2),

ANO NUMBER(4)

)

TABLESPACE TBS_DEFAULT

PARTITION BY LIST (ANO)

(

PARTITION P_2008 VALUES (2008) TABLESPACE TBS_P_2008,

PARTITION P_2009 VALUES (2009) TABLESPACE TBS_P_2009,

PARTITION P_2010 VALUES (2010) TABLESPACE TBS_P_2010,

PARTITION P_2011 VALUES (2011) TABLESPACE TBS_P_2011,

PARTITION P_DEFAULT VALUES (DEFAULT) TABLESPACE TBS_DEFAULT

);

Meu problema é que os dados dos anos diferentes de 2008,2009,2010 e 2011 cairão, obviamente, na partição default.

O que tenho que fazer, é criar partições para os anos de 2012 e 2013, porém a presença da partição DEFAULT me impede de fazer isso.

É importante saber que a partição default está com muitos dados (2012 e 2013) e não posso simplesmente dropar ela.

Essa era a minha situação problemática, que inclusive tentei ajuda no fórum de Rodrigo Almeida.

No início dessa semana, pesquisando sobre o assunto, achei uma técnica interessante para resolver o assunto.

Para inserir uma nova partição, com a presença de uma partição default, inicialmente criei as tablespaces para os anos de 2012 e 2013, conforme exemplo abaixo:

CREATE TABLESPACE TBS_2012 DATAFILE

'/opt/oracle/oradata/workspace/tbs_2012.dbf' SIZE 2048M AUTOEXTEND OFF

NOLOGGING

ONLINE

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100M

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO

FLASHBACK ON;

Em seguida utilizei o comando SPLIT PARTITION para realizer a divisão de uma partição, esse é o ponto crucial da solução do problema.

A sintaxe que utilizei foi mais ou menos a seguinte (para o ano de 2012):

ALTER TABLE TBL_TABELA

SPLIT PARTITION P_DEFAULT VALUES (2012)

INTO ( PARTITION P_2012 TABLESPACE TBS_2012,PARTITION P_DEFAULT);

O resultado após a execução desse comando é uma partição P_2012 (que estará na tablespace TBS_2012) contendo os dados da tabela do ano de 2012 e os dados dos outros anos ficarão na partição P_DEFAULT (exceto para os anos que existem partições referentes), no meu caso apenas os dados de 2013.

Depois fiz a mesma coisa para a partição do ano de 2013, e pronto. Resolvida a situação!

Bom pessoal, por enquanto é isso.

Grande abraço e até a próxima!

Referências:

LEGATTI, EDUARDO - Oracle Blog - Um pouco sobre índices particionados no Oracle. Disponível em: http://eduardolegatti.blogspot.com.br/2011/12/um-pouco-sobre-indices-particionados-no.html. Acessado em: 02/04/2013.

BOROVINA, JOÃO MARCELO - Particionamento de Dados: Uma introdução aos conceitos e aplicação. Disponível em: http://www.devmedia.com.br/particionamento-de-dados-uma-introducao-aos-conceitos-e-aplicacao/7299. Acessado em 02/04/2013.

ALMEIDA, RODRIGO - Fórum: Problema com tabela particionada no Oracle 10g. Disponível em: http://www.rodrigoalmeida.net/forum/viewtopic.php?f=3&t=891&sid=2642261a63b71d2453109f92000e0a96 Acessado em: 01/04/2013.

Oracle Corporation - Particionamento no Banco de Dados Oracle 11g -Um artigo técnico da Oracle. Junho de 2007. Disponível em: http://www.oracle.com/technetwork/pt/database/enterprise-edition/documentation/particionamento-banco-de-dados-11g-432098-ptb.pdf. Acessado em: 31/03/2013.

Oracle Corporation - Maintaining Partitions. Disponível em: http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin002.htm. Acessado em: 01/04/2013.

Notícia interessante sobre a Oracle

2013-04-01T08:08:00.000-03:00

Bom dia a todos!

Ontem estava lendo umas notícias, quando me deparei com uma que foge um pouco o assunto de banco de dados, mas é interessante por se tratar de tecnologia.

A notícia foi publicada 23/02/2013, e fala sobre ataques cibernéticos diante da “vulnerabilidade” da tecnologia JAVA. Na reportagem fala da ocorrências de ataques em empresas bastante conhecidas tais como: Apple, Facebook e Twitter; e que os criminosos que tentam invadir sistemas focaram na linguagem Java pois julgam que atualmente possui pontos de ataque mais fáceis.

A Oracle, que é a proprietária da linguagem Java (após a aquisição da Sun Microsystems) desde o ano de 2010, vem corrigindo os furos na linguagem, mas assim que as falhas são reparadas, novas são expostas, segundo a reportagem.

Confiram a matéria completa no site: http://www.advivo.com.br/blog/luisnassif/ataques-ciberneticos-expoem-oracle

É uma matéria interessante!

Fica a dica, e até a próxima!

As funções numéricas ROUND e TRUNC no banco de dados Oracle

2013-03-25T09:19:00.001-03:00

Olá pessoal!

Tive uma semana conturbada e não deu para postar nada, mas comecei a escrever algo e terminei há pouco.

Bom...

Hoje vou falar sobre duas funções de linha simples: round e trunc.

Só relembrando as primeiras aulas de banco de dados da faculdade, funções de linha simples são as que operam com uma linha de conjunto de dados por vez. Se uma instrução SQL seleciona 20 linhas (registros), a função será executada 20 vezes, uma para cada linha.

As funções round e trunc podem ser usadas em campos do tipo numérico e data, mas minha abordagem será apenas sobre número.

A função ROUND

Essa função realiza uma operação de arredondamento de um valor com base em uma precisão especificada.

A sintaxe da função ROUND pode ser observada na Figura 1.

Figura 1 - Sintaxe da função ROUND. (Fonte: Oracle Corporation)

O grau de arredondamento ou precisão é opcional e caso exista, deve ser um valor decimal, e para o caso desse valor ser “n”, onde n > 0, o dígito significante ao arredondamento é encontrado (n+1) do lado direito do ponto decimal, ou seja:

SELECT ROUND (1.259, 2) valor FROM DUAL;

A precisão é igual a 2 nesse exemplo, portanto o dígito de arredondamento nesse caso será o de posição 2 após (lado direito) o ponto, nesse caso o valor “5”.

Para o caso do valor da precisão ser um número negativo, o dígito significante ao arredondamento é “n” colocado do lado esquerdo do ponto:

SELECT ROUND (136.479, -1) valor FROM DUAL;

Nesse caso a precisão é -1, então o arredondamento será feito no primeiro dígito após o ponto decimal, nesse caso o valor “6”.

Se o valor numérico do dígito significante for maior ou igual a 5, um arredondamento para cima ocorrerá, caso contrário será um arredondamento para baixo. Se não for informada a precisão de arredondamento para a função round, o grau padrão de arredondamento é zero, significando que a origem será arredondada para o número inteiro mais próximo (lado esquerdo do ponto decimal).

Vou dar uns exemplos para facilitar o entendimento:

SELECT ROUND (1652.479, 2) AS valor FROM DUAL

UNION ALL

SELECT ROUND (1652.479, 1) AS valor FROM DUAL

UNION ALL

SELECT ROUND (1652.479) AS valor FROM DUAL

UNION ALL

SELECT ROUND (1652.479, -1) AS valor FROM DUAL

UNION ALL

SELECT ROUND (1652.479, -2) AS valor FROM DUAL;

O resultado para as consultas do exemplo pode ser conferido na Figura 2.

Figura 2 - Resultado de consultas utilizando o comando round. (Fonte: autoria própria)

A função TRUNC

A função trunc realiza uma operação de truncamento em um valor numérico com base em uma precisão decimal especificada, que assim como a função round, é opcional. A diferença entre as funções round e trunc é que a segunda função “quebra” o resultado na precisão decimal especificada e não tenta arredondar para cima ou para baixo.

A sintaxe do comando trunc pode ser configurada na Figura 3.

Figura 3 - Sintaxe da função TRUNC. (Fonte: Oracle Corporation)

Como os conceitos entre as funções round e trunc são bem parecidos, vou partir para exemplos do comando TRUNC para facilitar o entendimento:

SELECT TRUNC (1652.479123, 3) AS valor FROM DUAL

UNION ALL

SELECT TRUNC (1652.479123, 2) AS valor FROM DUAL

UNION ALL

SELECT TRUNC (1652.479123, 1) AS valor FROM DUAL

UNION ALL

SELECT TRUNC (1652.479123) AS valor FROM DUAL

UNION ALL

SELECT TRUNC (1652.479123, -1) AS valor FROM DUAL

UNION ALL

SELECT TRUNC (1652.479123, -2) AS valor FROM DUAL

UNION ALL

SELECT TRUNC (1652.479123, -3) AS valor FROM DUAL;

Figura 4 - Resultado de consultas utilizando o comando trunc. (Fonte: autoria própria)

Esses comandos de linhas simples são bastante eficientes e muito útil quando se precisa trabalhar com precisões.

Espero ter conseguido passar o entendimento sobre as funções.

Grande abraço e até a próxima!

Referências:

WATSON, JOHN – Ramklass, Roopesh (2010) – OCA Oracle Database 11g – Fundamentos I SQL – Manual do Exame 1z0-051. Editora: ALTABOOKS.

ORACLE CORPORATION – ROUND (number). Disponível em: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions135.htm. Acessado em 24/03/2013.

ORACLE CORPORATION – TRUNC (number). Disponível em: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions200.htm. Acessado em 24/03/2013.

Query hierárquica no banco de dados Oracle

2013-03-20T11:10:00.000-03:00

Olá pessoal,

Hoje vou falar sobre consultas hierárquicas no banco de dados Oracle. Esse tipo de estrutura é muito útil para várias situações desde criação de itens de um menu até uma simulação de informação.

Bom...

Uma consulta hierárquica relaciona registros com base em uma relação de parentesco. Para que seja possível realizar uma consulta de forma hierárquica de um conjunto de dados, é necessário que exista uma relação de lógica entre os registros (pai e filho).

Vou dar um exemplo mais prático: criação de itens de menu. Digamos que queiramos criar um menu da seguinte forma:

1.Item 1

--1.1.Item 1, parte 1

--1.2.Item 1, parte 2

----1.2.1.Item 1, parte 2, seção 1

----1.2.2.Item 1, parte 2, seção 2

2.Item 2

--2.1.Item 2, parte 1

--2.2.Item 2, parte 2

Vou criar uma tabela chamada MENU para ajudar no entendimento:

CREATE TABLE menu

(

cod NUMBER (4),

codpai NUMBER (4),

campo VARCHAR (30),

CONSTRAINT PK_MENU PRIMARY KEY (COD),

CONSTRAINT FK_MENU_CODPAI FOREIGN KEY (CODPAI) REFERENCES DIARIO.MENU (COD)

);

Agora vou popular a tabela com registros de mesmo valor do citado no exemplo literal acima:

INSERT INTO MENU VALUES (1, NULL, 'Item 1');

INSERT INTO MENU VALUES (2,1,'Item 1,parte 1');

INSERT INTO MENU VALUES (3,1,'Item 1,parte 2');

INSERT INTO MENU VALUES (4,3,'Item 1,parte 2, seção 1');

INSERT INTO MENU VALUES (5,3,'Item 1,parte 2, seção 2');

INSERT INTO MENU VALUES (6,NULL,'Item 2');

INSERT INTO MENU VALUES (7,6,'Item 2,parte 1');

INSERT INTO MENU VALUES (8,6,'Item 2,parte 2');

COMMIT;

Verificando de forma normal os registros executando a consulta abaixo temos o resultado da Figura 2:

SELECT cod, codpai, campo FROM MENU ORDER BY 1 ASC, 2 ASC;

Figura 1 - Resultado da consulta do exemplo 1. (Fonte: autoria própria)

Agora vamos ver como realizar uma consulta hierárquica contra essa tabela. Para isso, vamos entender um pouco do conceito.

A estrutura utilizada para realizar consultas dessa natureza é: START WITH ... CONNECT BY, e sua sintaxe pode ser vista na Figura 2.

Figura 2 - Sintaxe para consultas hierárquicas. (Fonte: Oracle Corporation)

O comando START WITH:

Determina quais são os registros “raiz” que devem ser utilizados para iniciar a consulta. No nosso caso, serão os registros com “codpai” nulo.

O comando CONNECT BY PRIOR:

Este comando especifica a relação entre registros pai e filho. No nosso caso, “cod” deverá ter um correspondente em “codpai”. É importante ressaltar que se a igualdade for invertida, ou seja, “codpai” foi igual a “cod” o resultado será totalmente diferente, pois o Oracle considera o argumento da esquerda como pai (ou raiz) e o da direita da igualdade como filho.

A consulta hierárquica para atender a solução é a seguinte:

SELECT cod,

codpai,

LEVEL,

LPAD (' ',(LEVEL - 1) * 3, '-') || campo AS "Descrição"

FROM menu

START WITH codpai IS NULL

CONNECT BY PRIOR cod = codpai

ORDER BY cod ASC, codpai ASC, campo ASC;

A pseudo-coluna LEVEL sempre está disponível para consultas hierárquicas e serve para indicar o nível em que o registro se encontra em relação à raiz.

O resultado da consulta acima pode ser conferido na Figura 3.

Figura 3 - Resultado da consulta do exemplo 1. (Fonte: autoria própria)

Consultas hierárquicas podem ter muitas utilidades, e os comandos connect by e start with podem nos ajudar de várias outras formas.

Por exemplo, uma simulação de valores de 1 a 10:

SELECT LEVEL "Número" FROM DUAL CONNECT BY LEVEL <= 10 ORDER BY 1 ASC;

O resultado da consulta acima pode ser conferido na Figura 4.

Figura 4 - Simulação de contagem de 1 a 10. (Fonte: autoria própria)

Recentemente passei por uma situação e usei connect by e start with para resolver. A situação foi a seguinte, precisava saber qual o primeiro domingo do mês corrente.

Alguém se habilita com uma solução?

Bom...

A que usei foi a seguinte: simulei dados para os 7 primeiros dias do mês (e com certeza o primeiro domingo estará entre eles), e numa consulta mais externa verifiquei qual das datas correspondia ao primeiro dia da semana, no caso o domingo. A consulta foi a seguinte:

SELECT dia

FROM (SELECT TRUNC (SYSDATE, 'MM') + b.simulado dia FROM DUAL a,

( SELECT LEVEL - 1 simulado FROM DUAL CONNECT BY LEVEL <= 7) b

)

WHERE TO_CHAR (dia, 'D') = '1';

O resultado para a consulta pode ser conferida na Figura 5.

Figura 5 – Consulta para saber o primeiro domingo do mês corrente. (Fonte: autoria própria)

Essa foi uma forma simples e prática para a solução do meu problema.

Por hoje é isso, pessoal!

Espero ter conseguido passar o entendimento de forma clara e objetiva.

Até a próxima!

Referências:

Oracle Corporation – Hierarchical Queries. Disponível em: http://docs.oracle.com/cd/B28359_01/server.111/b28286/queries003.htm. Acessado: 18/03/2013.

Lusa, Diego Antonio - Uso do CONNECT BY no Oracle. Disponível em: http://www.devmedia.com.br/uso-do-connect-by-no-oracle/23647. Acessado:18/03/2013.

Estatísticas no banco de dados Oracle

2013-03-18T10:29:00.000-03:00

Olá pessoal!

Hoje quero falar um pouco sobre as estatísticas no banco de dados Oracle e a importância delas para o bom desempenho dos scripts submetidos ao banco.

Inicialmente, vou abordar os métodos existentes para realizar a coleta das estatísticas, com algumas características e tentar estabelecer uma comparação entre eles.

Lembrando que em um post anterior, comentei sobre como verificar se as estatísticas de sua instância Oracle foram atualizadas recentemente.

Vou fazer uma explicação rápida sobre plano de execução (isso porque pretendo fazer um post sobre o assunto em outro momento). O Plano de Execução de uma instrução SQL é uma seqüência de operações que o Banco de Dados Oracle realiza para executar uma instrução. O plano de uma instrução pode se alterar conforme o ambiente em que está sendo executado: versão do Oracle, hardware, parâmetros de servidor (sistema ou sessão), volume de dados e estatísticas. Esse último item é o ponto que quero focar no artigo de hoje.

Estatística é uma coleção de dados detalhados sobre o banco de dados e seus objetos. As estatísticas são usadas pelo otimizador de consultas do Oracle para escolher o melhor plano de execução a ser executado.

Abaixo, seguindo a definição da Oracle, seguem itens inclusos nas estatísticas:

- Para o caso de tabela:

- Número de linhas (registros)

- Número de blocos de dados

- Comprimento médio das linhas

- Para as colunas:

- Número de valores distintos na coluna (NDV – Number of Distinct Values)

- Número de colunas nulas

- Distribuição dos dados (histogramas)

- Estatísticas dos índices:

- Número de blocos folha

- Níveis

- Fator de agrupamento

- Estatísticas de sistema:

- Desempenho e utilização de I/O

- Desempenho e utilização de CPU

Entendendo quais informações são levadas em consideração nas estatísticas, torna de mais fácil entendimento a razão que elas auxiliam na escolha do otimizador do Oracle de “traçar” um plano de execução mais eficiente para acessar um dado (ou um conjunto de dados) da base.

Existem algumas maneiras de se atualizar as estatísticas, inclusive o próprio banco de dados Oracle já realiza diante de algumas circunstâncias, tais como modificação significativa na tabela (mais de 10% dos registros caso o objeto esteja habilitado para monitoramento). As formas de atualização das estatísticas são as seguintes:

Atualização através do comando ANALYZE:

A sintaxe para coletar estatísticas de uma tabela é a seguinte:

ANALYZE TABLE OWNER.TABELA COMPUTE STATISTICS;

Algumas características sobre essa estrutura de atualização das estatísticas, segundo Fábio Prado:

a. Coleta ou exclui estatísticas sobre índices, tabelas ou clusters; de forma exata ou estimada em um número ou percentual de registros;

b. Pode não precisar a informação de cardinalidade da tabela no que diz respeito a valores distintos nas colunas (NDV);

c. Foi bastante eficiente nas versões anteriores do banco de dados Oracle, mas nas versões atuais existem apenas para manter a compatibilidade.

Atualização através do pacote DBMS_UTILITY:

A sintaxe para coletar estatísticas de um esquema é a seguinte:

EXEC DBMS_UTILITY.ANALYZE_SCHEMA (OWNER,'ESTIMATE',99,20,'FOR TABLE');

ou pode-se atualizar as estatísticas de toda a instância através do commando:

EXEC DBMS_UTILITY.ANALYZE_DATABASE ('ESTIMATE',100,20,'FOR TABLE');

É importante lembrar que diferente do comando ANALYZE, o pacote DBMS_UTILITY pode coletar as estatísticas de um esquema ou de todo um banco de dados.

Atualização através do pacote DBMS_STATS:

A sintaxe para coletar estatísticas de uma tabela é a seguinte:

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'OWNER', TABNAME=>'TABELA', ESTIMATE_PERCENT=>20);

A sintaxe para coletar estatísticas de um esquema é a seguinte:

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('OWNER',ESTIMATE_PERCENT=> 20);

A sintaxe para coletar estatísticas de toda a instância é a seguinte:

EXEC DBMS_STATS.GATHER_DATABASE_STATS;

Para coletar estatísticas de sistema (dicionário de dados):

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

Algumas características sobre essa estrutura de atualização das estatísticas, também segundo Fábio Prado:

a. Foi introduzido no Oracle 8i e hoje é o método mais eficiente para coletar estatísticas para o CBO. É o método de coleta de estatísticas atualmente recomendado pela Oracle;

b. Permite coletar estatísticas exatas ou estimadas de objetos individualmente (tabelas, índices, cluster etc), esquemas, banco de dados completo e de sistema;

c. Permite execução paralela, transferência de estatísticas entre servidores e é mais preciso que os métodos anteriores pois gera histogramas, que são extremamente úteis para otimizar scripts SQL que acessam colunas com valores dispersos.

É importante saber que utilizar o pacote DBMS_STATS é a forma mais completa e justamente por esse motivo pode ser o método mais lento de coleta de estatística entre os apresentados. Para garantir um desempenho otimizado do seu banco, é de fundamental importância que as estatísticas dele estejam atualizadas. Lembro novamente que o Oracle realiza de forma automática a coleta das estatísticas diante de algumas situações (isso nas versões “mais recentes” do banco Oracle – a partir do 10g): diariamente caso as estatísticas estejam desatualizadas ou quando existir muita movimentação dos registros – cerca de 10% do total (configurável) – do objeto (que também significa que as estatísticas estão desatualizadas).

Bom pessoal. Por hoje é isso!

Espero ter conseguido passar o básico sobre coleta de estatísticas no BD Oracle.

Até a próxima!

Referências:

ORACLE CORPORATION - MANAGING OPTIMIZER STATISTICS. Disponível em: http://docs.oracle.com/cd/B13789_01/server.101/b10752/stats.htm. Acessado em: 02/03/2013.

ORACLE CORPORATION - DBMS_STATS. Disponível em: http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_stats.htm. Acessado em: 02/03/2013.

PRADO, FÁBIO. Disponível em: http://www.fabioprado.net/2012/04/coletando-estatisticas-para-o.html. Acessado em: 17/03/2013.

DBPEDIAS. Disponível: http://dbpedias.com/wiki/Oracle:DBMS_UTILITY.ANALYZE_DATABASE. Acessado em: 02/03/2013.

DBPEDIAS. Disponível em: http://dbpedias.com/wiki/Oracle:DBMS_UTILITY.ANALYZE_SCHEMA. Acessado em: 02/03/2013.

Query para verificar atualização das estatísticas de tabelas e índices do banco de dados Oracle

2013-03-15T10:31:00.001-03:00

Muito bom dia, a todos!

Hoje vou falar sobre como verificar se as estatísticas das tabelas e índices da sua instância estão atualizadas. Quero lembrar que o otimizador do banco de dados Oracle nas versões “mais recentes”, otimizador baseado em custo (CBO - Cost-Based Optimizer), que é o grande responsável por traçar o plano de execução, (definindo a “melhor” forma de se acessar um dado optando pelo “mais eficiente” método de acesso), utiliza as estatísticas para fazê-lo. Dessa forma, pode-se afirmar que a coleta e atualização das estatísticas está diretamente ligada à performance do banco de dados.

Bom...

Criei duas consultas para verificarmos quando foi a última vez que o objeto (índice ou tabela) teve suas estatísticas coletadas. É importante saber que o banco Oracle realiza de forma automática a coleta das estatísticas para o caso de uma variação muito grande do tamanho do objeto (cerca de 10%), mas isso é um assunto que falarei em outra oportunidade.

Na projeção da primeira consulta, deve-se observar a data de atualização das estatísticas das tabelas do seu ambiente:

SELECT STAT.OWNER AS "Schema proprietário",

STAT.TABLE_NAME AS "Nome do objeto",

STAT.OBJECT_TYPE AS "Tipo do objeto",

STAT.NUM_ROWS AS "Quant. de Linhas",

STAT.LAST_ANALYZED AS "Última coleta das estatísticas"

FROM SYS.DBA_TAB_STATISTICS STAT

WHERE STAT.OWNER NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')

ORDER BY LAST_ANALYZED;

Já na projeção da segunda (abaixo), é possível saber quando as estatísticas dos índices foram coletadas pela última vez:

SELECT STAT.OWNER AS "Schema proprietário",

STAT. TABLE_NAME AS "Nome do objeto",

STAT.OBJECT_TYPE AS "Tipo do objeto",

STAT.NUM_ROWS AS "Quant. de Linhas",

STAT.LAST_ANALYZED AS "Última coleta das estatísticas"

FROM SYS.DBA_IND_STATISTICS STAT

WHERE STAT.OWNER NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')

ORDER BY LAST_ANALYZED;

Os campos projetados em ambas as consultas são:

"Schema proprietário": - campo que informa o esquema dono do objeto;

"Nome do objeto": - campo que informa o nome do objeto;

"Tipo do objeto": - informa o tipo do campo, no caso: tabela ou índice;

"Quant. de Linhas": - exibe a quantidade de registros do objeto;

"Última coleta das estatísticas": - data e hora da realização da última coleta (mais recente) das estatísticas do objeto em questão.

Na seleção da informação, abstraí alguns esquemas, pois são os reservados e utilizados pelo sistema, e não cabem termos conhecimento das estatísticas desses usuários.

A ordenação da informação está em ordem ascendente de data, ou seja, da menor para a maior apenas para facilitar e sabermos que os objetos com as estatísticas mais desatualizadas aparecerão na frente dos dados.

Por hoje é só!

Até a próxima!

Query para identificar os scripts com alto consumo de tempo de CPU no banco de dados Oracle

2013-03-14T09:26:00.000-03:00

Olá pessoal!

Existem algumas formas de se analisar os scripts submetidos ao banco de dados Oracle quanto a sua performance. Uma dessas formas é através de uma view do esquema SYS chamada V_$SQLAREA, ou através do sinônimo público V$SQLAREA que está setado para a tabela.

Bom, de qualquer forma, nesse objeto existem informações interessantes para o acompanhamento dos scripts submetidos ao servidor de banco, mesmo que eles não tenham sido concluídos com sucesso.

A consulta é a seguinte:

SELECT *

FROM ( SELECT ROUND ( ( (cpu_time / 1000000) / 60), 2) AS "Tempo total de CPU",

executions AS "Quant. exec.",

rows_processed AS "Quant. linhas proc.",

disk_reads AS "Leituras no disco",

first_load_time AS "Primeira utilização",

last_load_time AS "Última utilização",

parsing_schema_name AS "Usuário analisado",

sql_text AS "SQL exec."

FROM v$sqlarea

WHERE parsing_schema_name NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')

ORDER BY 1 DESC)

WHERE ROWNUM <= 10;

Nesse caso, a seleção da consulta mais externa (cláusula where) faz com que o retorno sejam de apenas os 10 scripts com maior tempo total de CPU (garantido no sort da consulta interna), mas podemos alterar esse valor ou retirar a condição. Na seleção da consulta mais interna, retirei os usuários de controle do DBA e do próprio SGBD Oracle, para que a análise possa ser feita apenas por aplicações e pessoas.

Vou explicar rapidamente as informações da projeção (cláusula select):

"Tempo total de CPU" – nesse “campo” é retornado o tempo total de CPU gasto para executar todas as vezes o script em questão. Fiz uma continha para exibir o tempo em minutos;

"Quant. exec" – nesse “campo” é retornado a quantidade de vezes que o SQL em questão foi executado desde sua primeira vez que foi submetido;

"Quant. linhas proc." – nesse “campo” é exibido a quantidade total de linhas processadas em todas as execuções da SQL em questão;

"Leituras no disco" – nesse “campo” é retornado a quantidade total de leituras realizadas no disco;

"Primeira utilização" – informa a data e hora da primeira utilização da SQL em questão, ou pelo menos a mais recente das “primeiras vezes”;

"Última utilização" – informa a data e hora da última utilização da SQL em questão;

"Usuário analisado " – informa qual o usuário da instância Oracle que realizou a SQL;

"SQL exec." – informa o script SQL submetido.

Grande abraço e até a próxima!!

O comando SQL: MERGE

2013-03-13T11:01:00.003-03:00

Muito bom dia a todos!

Hoje vou falar sobre um comando SQL que não vejo muita gente usar mas que é bem poderoso. Esse comando é o MERGE.

Vejo muito código em PL/SQL da seguinte forma:

DECLARE

var_existe NUMBER (4) := 0;

BEGIN

SELECT COUNT (cod)

INTO var_existe

FROM tabela

WHERE cod = :par_cod;

IF var_existe > 0 THEN

--Significa que existe valor para o parametro buscado (par_cod)

UPDATE tabela SET campo = :par_campo WHERE cod = :par_cod;

ELSE

--Significa que NÃO existe valor para o parametro buscado

INSERT INTO tabela VALUES (:par_cod, :par_campo);

END IF;

END;

Abstraindo o nome da tabela e seus campos, e levando em consideração à estrutura do bloco de código, basicamente o que é feito é um teste se um determinado valor (:par_cod) existe na tabela, caso exista, será realizada uma alteração (UPDATE) caso contrário será inserido um registro com aquele código (INSERT).

Agora vou falar um pouco sobre o MERGE e depois associar o comando com o bloco PL/SQL acima.

Segundo Watson, O merge foi introduzido no SQL com o padrão SQL1999, e implementado no Oracle a partir da versão 9i. No Oracle Database 10g o comando foi aprimorado para entrar em conformidade com o padrão SQL2003.

O propósito principal do comando merge é justamente o mesmo do bloco lógico apresentado anteriormente. Uma operação merge não faz nada que não possa ser feito com as instruções INSERT, UPDATE e DELETE – mas com uma passagem pelos dados de origem, ela pode fazer as três coisas. Código alternativo sem MERGE iria exigir três passagem pelos dados, uma para cada comando.

Os dados de Origem para uma instrução MERGE pode ser uma tabela ou uma sub-consulta qualquer. A condição usada para encontrar linhas correspondentes no destino é similar a uma cláusula WHERE. As cláusulas que atualizam, inserem ou excluem linhas são semelhantes aos comandos update, insert e delete. Alguns autores e profissionais de banco de dados afirmam que o MERGE é o mais complicado dos comandos DML, porém um dos mais poderosos.

A sintaxe do MERGE, em sua forma básica é a informada na Figura 1.

Figura 1 - Estrutura do comando MERGE. (Fonte: Orale Corporation)

Na Figura 1, as operações DML não estão especificadas, então seguem algumas imagens complementares.

Na Figura 2, é apresentada a sintaxe para realizar um UPDATE na linha para o caso de correspondência (MATCHED) na comparação realizada no comando.

Essa mesma estrutura apresentada na Figura 2 pode ser utilizada para a exclusão de linhas na tabela em questão.

Figura 2 - UPDATE/DELETE no MERGE caso a comparação realizada corresponda. (Fonte: Orale Corporation)

Para o caso de não existir uma correspondência na comparação realizada no comando, um INSERT pode ser realizado conforme apresentado na Figura 3.

Figura 3 - INSERT no MERGE caso a comparação realizada não corresponda. (Fonte: Orale Corporation)

Em ambas as situações, Figura 2 e 3, existem um trecho chamado “where_clause”, que nada mais é que uma cláusula WHERE normal, no formado: WHERE condição.

Na Figura 1, existe um trecho chamado “error_loggin_clause”, que possui sua sintaxe conforme Figura 4. Sua utilidade é para o caso de erros na execução do comando MERGE. O registro na tabela de log será efetivado caso ocorra algum problema. Para se criar uma tabela para o log do Oracle usa-se o pacote DBMS_ERRLOG.CREATE_ERROR_LOG. Como não é nosso foco principal não abordarei profundamente a questão, mas recomendo a leitura do assunto para um entendimento melhor (é interessante).

Figura 4 - Tratamento de erros no comando MERGE. (Fonte: Oracle Corporation)

Aplicando a sintaxe acima ao nosso bloco lógico citado no início do post temos o seguinte:

MERGE INTO tabela t

USING (SELECT :par_cod AS cod FROM DUAL) t_aux

ON (t.cod = t_aux.cod)

WHEN MATCHED THEN

UPDATE SET campo = :par_campo

WHEN NOT MATCHED THEN

INSERT VALUES (:par_cod, :par_campo);

Fiz uma simulação de consulta utilizando a tabela DUAL, apenas para depois testar se o valor “passado por parâmetro” (par_cod) existe na tabela.

A sintaxe está bem simplificada, mas é bastante potente e mais performático em relação ao bloco apresentado anteriormente.

Fica a dica: avaliem se em algum script seu existe uma situação que possa ser utilizado o MERGE e faça um teste. Vale o aprendizado!

Espero conseguido passar o conhecimento!

Grande abraço e até a próxima...

Referências:

Watson, John; Ramklass, Roopesh (2010) – OCA Oracle Database 11g – Fundamentos I SQL – Manual do Exame 1z0-051. Editora: ALTABOOKS.

Oracle Corporation – MERGE. Disponível em http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm. Acessado em 13/03/2013.