Pessoal,
Nesta semana, estou compartilhando scripts para efetuar consultas sobre as Options e Features de um Banco de Dados Oracle.
As consultas sobre as Options são importantes para verificar quais Options do BD estamos utilizando e conferir se possuímos licenciamento delas. A consulta sobre Features ajudará a descobrir quais recursos do Oracle Database vocês poderão utilizar em sua instalação de Banco de Dados.
Para mais informações sobre Options e licenciamento no Oracle 10G entrem no link: http://download.oracle.com/docs/cd/B19306_01/license.102/b14199/options.htm.
Para mais informações sobre Options e licenciamento no Oracle 11G entrem no link: http://download.oracle.com/docs/cd/E11882_01/license.112/e10594/options.htm
Segue abaixo a relação dos scripts disponibilizados neste artigo:
- consultar_recursos_suportados_bd.sql
Verificar as Options que foram utilizadas em um Banco de Dados;
- consultar_options_naoutilizadas_BD.sql
Verificar as Options que NÃO foram utilizadas em um Banco de Dados;
- consultar_recursos_suportados_bd.sql:
Verificar as Features do Oracle Database suportadas em sua instalação de Banco de Dados
Para fazer download dos scripts (arquivo script_options.zip), acessem a pasta Oracle/Scripts, em pastas públicas, no MEU SKY DRIVE.
Até o próximo post!
25 de fev. de 2011
17 de fev. de 2011
Criando tabelas particionadas para otimizar consultas em tabelas
Olá pessoal,
As principais características das partições são:
- Possuem os mesmos atributos lógicos:
Todas as partições possuem as mesmas colunas, constraints e índices;
- Atributos físicos diferentes:
Para melhor desempenho as partições devem ser armazenadas em tablespaces distintos. Se possível, cada tablespace deve ser armazenado em um disco diferente;
- São transparentes para as aplicações:
As aplicações referenciam as tabelas particionadas do mesmo modo que referenciam as tabelas heap (normais), pois as aplicações fazem referência às tabelas e não às partições.
As tabelas podem ser particionadas através de vários métodos:
- Particionamento por faixa:
As partições são divididas em faixas lógicas de valores de colunas, como por exemplo, meses de um ano.
- Particionamento por hash:
As partições são divididas com base no valor hash de uma chave de particionamento.
- Particionamento por lista:
As partições são divididas por listas discretas de valores, fornecidas pelo DBA.
- Particionamento por faixa/hash:
As partições são divididas utilizando-se como base o método de faixa e, em cada partição por faixa, criando-se subpartições por hash.
- Particionamento por faixa/lista:
As partições são divididas primeiro em uma faixa de valores e, depois, com base em valores discretos.
PASSO 1: Criando os tablespaces que irão armazenar a tabela heap (CLIENTE) e as 3 partições da tabela particionada (CLIENTE_PART):
CREATE TABLESPACE TBS_NORMAL LOGGING DATAFILE 'clientes_normal.dbf' SIZE 1M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TBS_PART_ATE_1920 LOGGING DATAFILE 'clientes_PART_ATE_1920.dbf' SIZE 1M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TBS_PART_1920_1970 LOGGING DATAFILE 'clientes_PART_1920_1970.dbf' SIZE 1M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TBS_PART_MAIOR_1970 LOGGING DATAFILE 'clientes_PART_MAIOR_1970.dbf' SIZE 1M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
Obs.: Substitua os nomes dos arquivos (em vermelho) pelo caminho completo do arquivo (pasta + nome do arquivo).
Ex. no Windows:
C:\Oracle\10GR2\ORACLE_SID\clientes_normal.dbf ---> ORACLE_SID = nome do BD Ex. no Linux:
/ora01/dados/ORACLE_SID/clientes_normal.dbf ---> ORACLE_SID = nome do BD
PASSO 2: Criando as tabelas para armazenar clientes:
a) Criando a tabela heap:
CREATE TABLE CLIENTES (
ID_CLIENTE NUMBER(6,0) PRIMARY KEY ,
NM_CLIENTE VARCHAR2(50) NOT NULL ENABLE,
NR_ANO_NASCIMENTO NUMBER(4) NOT NULL ENABLE,
ID_SEXO CHAR(1),
NR_CPF VARCHAR2(11)
) TABLESPACE TBS_NORMAL;
b) Criando a tabela particionada:
CREATE TABLE CLIENTES_PART
(
ID_CLIENTE NUMBER(6,0) PRIMARY KEY ,
NM_CLIENTE VARCHAR2(50) NOT NULL ENABLE,
NR_ANO_NASCIMENTO NUMBER(4) NOT NULL ENABLE,
ID_SEXO CHAR(1),
NR_CPF VARCHAR2(11)
)
PARTITION BY RANGE (NR_ANO_NASCIMENTO)
(
PARTITION PART_ATE_1920 VALUES LESS THAN (1920) TABLESPACE TBS_PART_ATE_1920,
PARTITION PART_1920_1970 VALUES LESS THAN (1970) TABLESPACE TBS_PART_1920_1970,
PARTITION PART_MAIOR_1970 VALUES LESS THAN (MAXVALUE) TABLESPACE TBS_PART_MAIOR_1970);
PASSO 3: Inserindo dados (300.000 linhas) nas 2 tabelas criadas no passo anterior :
DECLARE
I NUMBER;
v_ano number := 1800;
BEGIN
for i in 1..100000 loop
INSERT INTO CLIENTES (ID_CLIENTE, NM_CLIENTE, NR_ANO_NASCIMENTO, ID_SEXO)
VALUES (I, 'Nome ' || I, V_ANO , 'M');
INSERT INTO CLIENTES_PART (ID_CLIENTE, NM_CLIENTE, NR_ANO_NASCIMENTO, ID_SEXO)
VALUES (I, 'Nome ' || I, V_ANO , 'M');
v_ano:=v_ano+1;
IF V_ANO = 1919 THEN
v_ano:=1800;
end if;
end loop;
V_ANO:= 1920;
for i in 100001..200000 loop
INSERT INTO CLIENTES (ID_CLIENTE, NM_CLIENTE, NR_ANO_NASCIMENTO, ID_SEXO)
VALUES (I, 'Nome ' || I, v_ano , 'F');
INSERT INTO CLIENTES_PART (ID_CLIENTE, NM_CLIENTE, NR_ANO_NASCIMENTO, ID_SEXO)
VALUES (I, 'Nome ' || I, v_ano , 'F');
v_ano:=v_ano+1;
IF V_ANO = 1969 THEN
V_ANO:=1920;
end if;
end loop;
V_ANO:= 1970;
for i in 200001..300000 loop
INSERT INTO CLIENTES (ID_CLIENTE, NM_CLIENTE, NR_ANO_NASCIMENTO, ID_SEXO)
VALUES (I, 'Nome ' || I, v_ano , 'F');
INSERT INTO CLIENTES_PART (ID_CLIENTE, NM_CLIENTE, NR_ANO_NASCIMENTO, ID_SEXO)
VALUES (I, 'Nome ' || I, v_ano , 'F');
v_ano:=v_ano+1;
IF V_ANO = 2011 THEN
V_ANO:=1970;
end if;
END LOOP;
COMMIT;
END;
Passo 4: Comparando o desempenho de consultas entre as tabelas normal e particionada:
a) Gerando o plano de execução de uma consulta na tabela heap (CLIENTES), filtrando os dados de retorno para exibir somente clientes que nasceram no ano de 1920:
EXPLAIN PLAN FOR
SELECT * FROM CLIENTES
WHERE NR_ANO_NASCIMENTO IN (1920);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Resultado:
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1610 | 90160 | 284 (3) | 00:00:04
|* 1 | TABLE ACCESS FULL| CLIENTES | 1610 | 90160 | 284 (3) | 00:00:04
-------------------------------------------------------------------------------
Obs.: No meu ambiente de testes, o plano de execução informou que o tempo de retorno da consulta é de 4s.
b) Gerando o plano de execução de uma consulta na tabela particionada (CLIENTES_PART), filtrando os dados de retorno para exibir somente clientes que nasceram no ano de 1920:
EXPLAIN PLAN FOR
SELECT * FROM CLIENTES_PART
WHERE NR_ANO_NASCIMENTO IN (1920);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Resultado:
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2157 | 117K| 123 (11) | 00:00:02
| 1 | PARTITION RANGE SINGLE| | 2157 | 117K| 123 (11)| 00:00:02
|* 2 | TABLE ACCESS FULL | CLIENTES_PART | 2157 | 117K| 123 (11)| 00:00:02
---------------------------------------------------------------------------------------------------
Obs.: No meu ambiente de testes, o plano de execução informou que o tempo de retorno da consulta é de 2s.
CONCLUSÃO:
Script para limpeza dos testes efetuados neste artigo:
DROP TABLE CLIENTES PURGE;
DROP TABLE CLIENTES_PART PURGE;
DROP TABLESPACE TBS_NORMAL INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE TBS_PART_ATE_1920 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE TBS_PART_1920_1970 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE TBS_PART_MAIOR_1970 INCLUDING CONTENTS AND DATAFILES;
Referências:
- Material do curso oficial: Oracle Database 10G Administration Workshop II.
Neste artigo vou apresentar para vocês o conceito de tabelas particionadas no Oracle Database e vou demonstrar como criar uma tabela particionada para possibilitar ganho de perfomance no acesso e atualização dos dados.
Uma tabela particionada é uma tabela dividida em partes menores, chamadas partições, que são criadas para facilitar o gerenciamento e possibilitar melhor desempenho em consultas e atualizações.
As principais características das partições são:
- Possuem os mesmos atributos lógicos:
Todas as partições possuem as mesmas colunas, constraints e índices;
- Atributos físicos diferentes:
Para melhor desempenho as partições devem ser armazenadas em tablespaces distintos. Se possível, cada tablespace deve ser armazenado em um disco diferente;
- São transparentes para as aplicações:
As aplicações referenciam as tabelas particionadas do mesmo modo que referenciam as tabelas heap (normais), pois as aplicações fazem referência às tabelas e não às partições.
As tabelas podem ser particionadas através de vários métodos:
- Particionamento por faixa:
As partições são divididas em faixas lógicas de valores de colunas, como por exemplo, meses de um ano.
- Particionamento por hash:
As partições são divididas com base no valor hash de uma chave de particionamento.
- Particionamento por lista:
As partições são divididas por listas discretas de valores, fornecidas pelo DBA.
- Particionamento por faixa/hash:
As partições são divididas utilizando-se como base o método de faixa e, em cada partição por faixa, criando-se subpartições por hash.
- Particionamento por faixa/lista:
As partições são divididas primeiro em uma faixa de valores e, depois, com base em valores discretos.
Para criar tabelas particionadas, no Oracle Database 10G Enterprise Edition (versão do Oracle mais utilizada), é necessário obter licenciamento da option Oracle Partitioning.
A melhor forma de otimizar o acesso e atualização dos dados em tabelas particionadas é armazenar as partições em discos diferentes. A minha recomendação principal para criar tabelas particionadas é utilizar este recurso somente quando uma tabela irá armazenar uma "enorme" quantidade de dados. Tabelas pequenas (com poucas linhas e/ou colunas com poucos dados) dificilmente terão ganhos de performance se forem particionadas.
O método de particionamento mais comumente utilizado é o método de Particionamento por faixa, que será o método do exemplo deste artigo. Para demonstrar o ganho de performance de uma consulta em tabelas particionadas, criaremos 2 tabelas com a mesma estrutura e mesmos dados. A tabela CLIENTE será uma tabela heap (normal) e a tabela CLIENTE_PART será uma tabela particionada, com 3 partições, divididas por faixas de valores através da coluna que armazena a data de nascimento. As tabelas armazenarão cada uma 300 mil registros de clientes.
--------------------------------------------------------------------------
Para iniciar o passo-a-passo abaixo, é necessário conectar-se previamente no Banco de Dados desejado, através do SQL Plus, SQL Delevoper ou outra ferramenta compatível, com um usuário que tenha privilégios para criar tabelas e tablespaces. De preferência não se conecte ou crie as tabelas no schema do usuário SYS.
--------------------------------------------------------------------------
PASSO 1: Criando os tablespaces que irão armazenar a tabela heap (CLIENTE) e as 3 partições da tabela particionada (CLIENTE_PART):
--------------------------------------------------------------------------
Se possível, crie os tablespaces TBS_PART_ATE_1920, TBS_PART_1920_1970
e TBS_PART_MAIOR_1970 em discos diferentes.
--------------------------------------------------------------------------
CREATE TABLESPACE TBS_NORMAL LOGGING DATAFILE 'clientes_normal.dbf' SIZE 1M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TBS_PART_ATE_1920 LOGGING DATAFILE 'clientes_PART_ATE_1920.dbf' SIZE 1M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TBS_PART_1920_1970 LOGGING DATAFILE 'clientes_PART_1920_1970.dbf' SIZE 1M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TBS_PART_MAIOR_1970 LOGGING DATAFILE 'clientes_PART_MAIOR_1970.dbf' SIZE 1M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
Obs.: Substitua os nomes dos arquivos (em vermelho) pelo caminho completo do arquivo (pasta + nome do arquivo).
Ex. no Windows:
C:\Oracle\10GR2\ORACLE_SID\clientes_normal.dbf ---> ORACLE_SID = nome do BD Ex. no Linux:
/ora01/dados/ORACLE_SID/clientes_normal.dbf ---> ORACLE_SID = nome do BD
PASSO 2: Criando as tabelas para armazenar clientes:
a) Criando a tabela heap:
CREATE TABLE CLIENTES (
ID_CLIENTE NUMBER(6,0) PRIMARY KEY ,
NM_CLIENTE VARCHAR2(50) NOT NULL ENABLE,
NR_ANO_NASCIMENTO NUMBER(4) NOT NULL ENABLE,
ID_SEXO CHAR(1),
NR_CPF VARCHAR2(11)
) TABLESPACE TBS_NORMAL;
b) Criando a tabela particionada:
CREATE TABLE CLIENTES_PART
(
ID_CLIENTE NUMBER(6,0) PRIMARY KEY ,
NM_CLIENTE VARCHAR2(50) NOT NULL ENABLE,
NR_ANO_NASCIMENTO NUMBER(4) NOT NULL ENABLE,
ID_SEXO CHAR(1),
NR_CPF VARCHAR2(11)
)
PARTITION BY RANGE (NR_ANO_NASCIMENTO)
(
PARTITION PART_ATE_1920 VALUES LESS THAN (1920) TABLESPACE TBS_PART_ATE_1920,
PARTITION PART_1920_1970 VALUES LESS THAN (1970) TABLESPACE TBS_PART_1920_1970,
PARTITION PART_MAIOR_1970 VALUES LESS THAN (MAXVALUE) TABLESPACE TBS_PART_MAIOR_1970);
PASSO 3: Inserindo dados (300.000 linhas) nas 2 tabelas criadas no passo anterior :
DECLARE
I NUMBER;
v_ano number := 1800;
BEGIN
for i in 1..100000 loop
INSERT INTO CLIENTES (ID_CLIENTE, NM_CLIENTE, NR_ANO_NASCIMENTO, ID_SEXO)
VALUES (I, 'Nome ' || I, V_ANO , 'M');
INSERT INTO CLIENTES_PART (ID_CLIENTE, NM_CLIENTE, NR_ANO_NASCIMENTO, ID_SEXO)
VALUES (I, 'Nome ' || I, V_ANO , 'M');
v_ano:=v_ano+1;
IF V_ANO = 1919 THEN
v_ano:=1800;
end if;
end loop;
V_ANO:= 1920;
for i in 100001..200000 loop
INSERT INTO CLIENTES (ID_CLIENTE, NM_CLIENTE, NR_ANO_NASCIMENTO, ID_SEXO)
VALUES (I, 'Nome ' || I, v_ano , 'F');
INSERT INTO CLIENTES_PART (ID_CLIENTE, NM_CLIENTE, NR_ANO_NASCIMENTO, ID_SEXO)
VALUES (I, 'Nome ' || I, v_ano , 'F');
v_ano:=v_ano+1;
IF V_ANO = 1969 THEN
V_ANO:=1920;
end if;
end loop;
V_ANO:= 1970;
for i in 200001..300000 loop
INSERT INTO CLIENTES (ID_CLIENTE, NM_CLIENTE, NR_ANO_NASCIMENTO, ID_SEXO)
VALUES (I, 'Nome ' || I, v_ano , 'F');
INSERT INTO CLIENTES_PART (ID_CLIENTE, NM_CLIENTE, NR_ANO_NASCIMENTO, ID_SEXO)
VALUES (I, 'Nome ' || I, v_ano , 'F');
v_ano:=v_ano+1;
IF V_ANO = 2011 THEN
V_ANO:=1970;
end if;
END LOOP;
COMMIT;
END;
Passo 4: Comparando o desempenho de consultas entre as tabelas normal e particionada:
a) Gerando o plano de execução de uma consulta na tabela heap (CLIENTES), filtrando os dados de retorno para exibir somente clientes que nasceram no ano de 1920:
EXPLAIN PLAN FOR
SELECT * FROM CLIENTES
WHERE NR_ANO_NASCIMENTO IN (1920);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Resultado:
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1610 | 90160 | 284 (3) | 00:00:04
|* 1 | TABLE ACCESS FULL| CLIENTES | 1610 | 90160 | 284 (3) | 00:00:04
-------------------------------------------------------------------------------
Obs.: No meu ambiente de testes, o plano de execução informou que o tempo de retorno da consulta é de 4s.
b) Gerando o plano de execução de uma consulta na tabela particionada (CLIENTES_PART), filtrando os dados de retorno para exibir somente clientes que nasceram no ano de 1920:
EXPLAIN PLAN FOR
SELECT * FROM CLIENTES_PART
WHERE NR_ANO_NASCIMENTO IN (1920);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Resultado:
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2157 | 117K| 123 (11) | 00:00:02
| 1 | PARTITION RANGE SINGLE| | 2157 | 117K| 123 (11)| 00:00:02
|* 2 | TABLE ACCESS FULL | CLIENTES_PART | 2157 | 117K| 123 (11)| 00:00:02
---------------------------------------------------------------------------------------------------
Obs.: No meu ambiente de testes, o plano de execução informou que o tempo de retorno da consulta é de 2s.
CONCLUSÃO:
Nos testes deste artigo pudemos verificar uma situação em que tivemos um ganho de performance de 50% no tempo de execução de uma consulta ao utilizar uma tabela particionada. Em determinadas situações, principalmente em tabelas muito grandes, o ganho de performance pode ser ainda maior, mas ressalto que, nem todas as consultas ou atualizações terão o mesmo desempenho. Se a tabela for pequena o tempo de execução poderá até piorar.
No exemplo que vimos neste artigo, a consulta foi efetuada filtrando dados em uma coluna que não tinha índices, portanto, o Oracle teve que fazer um FULL TABLE SCAN (FTS) na tabela. O tempo do FTS na tabela particionada foi menor do que na tabela heap, pois os dados do retorno da consulta estavam todos na partição do tablespace TBS_PART_1920_1970, que tinha menos dados que o tablespace TBS_NORMAL, da tabela heap.
Script para limpeza dos testes efetuados neste artigo:
DROP TABLE CLIENTES PURGE;
DROP TABLE CLIENTES_PART PURGE;
DROP TABLESPACE TBS_NORMAL INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE TBS_PART_ATE_1920 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE TBS_PART_1920_1970 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE TBS_PART_MAIOR_1970 INCLUDING CONTENTS AND DATAFILES;
Referências:
- Material do curso oficial: Oracle Database 10G Administration Workshop II.
4 de fev. de 2011
Criptografando tabelas com o TDE
Pessoal,
Neste artigo irei apresentar o recurso de segurança do Oracle Database (existente a partir da versão 10G release 2) chamado Transparent Data Encryption (TDE). Este recurso permite proteger dados confidenciais de colunas e índices de tabelas, criptografando os dados e armazenando-os de modo seguro (criptografados) nos arquivos de dados do Sistema Operacional. Aos consultar estes dados (criptografados), eles são decriptografados antes de serem apresentados para o usuário final. A criptografia e decriptografia dos dados ocorre de forma transparente, sem necessidade de linhas de código adicionais na aplicação ou no banco de dados.
Com TDE, ao criptografar os dados de uma coluna de uma tabela, é gerada uma chave de criptografia para a tabela, que é armazenada no Dicionário de Dados (D.D.) para posterior decriptografia. Por sua vez, a chave de criptografia da tabela também é criptografada por outra chave, que é chamada chave mestra e que é armazenada fora do Banco de Dados, em um local seguro chamado Oracle Wallet.
Quando um usuário do Banco de Dados altera ou inclui dados (dados de entrada) em uma coluna criptografada, o Oracle Database recupera a chave mestra do Wallet, decriptografa a chave da tabela (recuperada no D.D.) e usa esta chave para criptografar os dados de entrada, antes de armazená-los no Banco de Dados. Nos dados, antes da criptografia, são adicionadas palavras extras chamadas SALT, que alteram os dados originais e que ajudam a dificultar "ataques hackers" de decriptografia. SALT é adicionado por padrão no TDE e pode ser utilizado somente ao criptografar tabelas. Índices não podem conter SALT.
Com TDE os dados podem ser criptografados utilizando um dos quatro seguintes algoritmos de criptografia: 3DES168, AES128, AES192 e AES256. AES192 é o algoritmo padrão.
É importante ressaltar que ao utilizar TDE nas tabelas, há um custo de performance para criptografar e decriptografar as colunas. Em testes do artigo Transparent Data Encryption (TDE) in Oracle 10g Database Release 2 (http://www.oracle-base.com/articles/10g/TransparentDataEncryption_10gR2.php), o tempo de execução das instruções SQL para atualizações e consultas em uma tabela (que possui o total de 2 colunas e somente 1 coluna criptografada) aumentou aproximadamente 40%.
Para utilizar TDE, no Oracle Database 10G Enterprise Edition (versão do Oracle mais utilizada), é necessário obter licenciamento da option Oracle Advanced Security (ver http://download.oracle.com/docs/cd/B19306_01/license.102/b14199/options.htm#CIHJHABF)
Para demonstrar a utilização de TDE, seguiremos os passos abaixo:
--------------------------------------------------------------------------
Para iniciar o passo-a-passo abaixo, é necessário conectar-se previamente no Banco de Dados desejado, através do SQL Plus, com um usuário com privilégios administrativos (usuário contendo a role DBA ou o privilégio de sistema SYSDBA), que não seja o SYS, pois se o passo 2 for executado pelo SYS ele irá falhar (o Oracle Database não permite utilizar TDE em objetos do schema SYS).
--------------------------------------------------------------------------
PASSO 1: Criando e referenciando o Wallet
a) Acrescente no arquivo sqlnet.ora da pasta network/admin do Oracle Home o seguinte bloco:
ENCRYPTION_WALLET_LOCATION =
(SOURCE=
(METHOD=file)
(METHOD_DATA=
(DIRECTORY=\ORACLE_BASE\admin\ORACLE_SID\wallet)))
O parâmetro DIRECTORY do bloco de código acima indica o caminho do arquivo Wallet que será criado no item "b)" deste passo e está redigido de acordo com os padrões do sistema de arquivos do Windows. Neste item, substitua os valores:
- ORACLE_BASE: pelo diretório correspondente ao Oracle Base em sua instalação de BD.
- ORACLE_SID: pelo nome da instância de BD.
Obs.: Se o diretório wallet não existir, crie-o dentro da pasta \ORACLE_BASE\admin\ORACLE_SID. A pasta "\ORACLE_BASE\admin\ORACLE_SID\wallet" é o caminho padrão de criação de Wallets.
b) Crie o Wallet e defina a chave mestra na instância de Banco de Dados (BD) em execução:
No prompt do SQL digite:
ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "Teste123";
Obs.: Substitua Teste123 pela senha desejada. Este item define uma senha mestra e automaticamente cria o Wallet na pasta padrão referenciada no item "a)".
PASSO 2: Criando e inserindo dados em uma tabela com uma coluna criptografada
Execute no SQL Plus os comandos abaixo:
CREATE TABLE table_tde_test (
ID NUMBER,
DATA1 VARCHAR2(40),
DATA2 VARCHAR2(40) ENCRYPT);
INSERT INTO table_tde_test (ID, DATA1, DATA2)
VALUES (1, 'Dados NÃO criptogrados na coluna DATA1!','Dados criptogrados na coluna DATA2!');
COMMIT;
ALTER SYSTEM FLUSH BUFFER_CACHE; --> força atualização dos dados no arquivo de dados da tabela
PASSO 3: Testando o TDE no nível da tabela
a) Execute um SELECT na tabela criada no passo anterior:
SELECT * FROM table_tde_test;
Resultado:
Todos os dados da tabela serão retornados, inclusive os dados da coluna DATA2, que estão criptografados, pois o Wallet foi criado e está aberto desde o passo 1.
b) Feche o Wallet:
ALTER SYSTEM SET WALLET CLOSE;
Resultado:
Wallet fechado.
c) Execute novamente o SELECT do item "a)":
SELECT * FROM table_tde_test;
Resultado:
O SELECT irá disparar o erro ORA-28365, pois com o Wallet fechado não é possível recuperar a senha de criptografia da tabela e decriptografar os dados.
d) Execute um novo SELECT sem retornar os dados da coluna criptografada (DATA2):
SELECT ID, DATA1 FROM table_tde_test;
Resultado:
A instrução SELECT irá retornar com sucesso pois sem a incluir a coluna DATA2, não é necessário consultar o Wallet.
PASSO 4: Testando o TDE no nível do arquivo de dados
a) Localize o arquivo de dados em que a tabela foi criada. A query abaixo poderá ser executada para descobrir o caminho completo do arquivo de dados se o usuário conectado tiver privilégios administrativos ou tiver privilégios de SELECT nas visões DBA_TABLES e DBA_DATA_FILES:
SELECT F.FILE_NAME
FROM DBA_TABLES T
INNER JOIN DBA_DATA_FILES F
ON T.TABLESPACE_NAME = F.TABLESPACE_NAME
WHERE T.TABLE_NAME = 'TABLE_TDE_TEST';
b) Entre na pasta localizada no item anterior:
- Se estiver usando SO Linux digite no prompt de comandos:
strings FILE.DBF | grep "criptogrados na coluna DATA"
Obs.: Substitua FILE.DBF pelo nome do arquivo de dados identificado no item anterior.
Com TDE, ao criptografar os dados de uma coluna de uma tabela, é gerada uma chave de criptografia para a tabela, que é armazenada no Dicionário de Dados (D.D.) para posterior decriptografia. Por sua vez, a chave de criptografia da tabela também é criptografada por outra chave, que é chamada chave mestra e que é armazenada fora do Banco de Dados, em um local seguro chamado Oracle Wallet.
Quando um usuário do Banco de Dados altera ou inclui dados (dados de entrada) em uma coluna criptografada, o Oracle Database recupera a chave mestra do Wallet, decriptografa a chave da tabela (recuperada no D.D.) e usa esta chave para criptografar os dados de entrada, antes de armazená-los no Banco de Dados. Nos dados, antes da criptografia, são adicionadas palavras extras chamadas SALT, que alteram os dados originais e que ajudam a dificultar "ataques hackers" de decriptografia. SALT é adicionado por padrão no TDE e pode ser utilizado somente ao criptografar tabelas. Índices não podem conter SALT.
Com TDE os dados podem ser criptografados utilizando um dos quatro seguintes algoritmos de criptografia: 3DES168, AES128, AES192 e AES256. AES192 é o algoritmo padrão.
É importante ressaltar que ao utilizar TDE nas tabelas, há um custo de performance para criptografar e decriptografar as colunas. Em testes do artigo Transparent Data Encryption (TDE) in Oracle 10g Database Release 2 (http://www.oracle-base.com/articles/10g/TransparentDataEncryption_10gR2.php), o tempo de execução das instruções SQL para atualizações e consultas em uma tabela (que possui o total de 2 colunas e somente 1 coluna criptografada) aumentou aproximadamente 40%.
Para utilizar TDE, no Oracle Database 10G Enterprise Edition (versão do Oracle mais utilizada), é necessário obter licenciamento da option Oracle Advanced Security (ver http://download.oracle.com/docs/cd/B19306_01/license.102/b14199/options.htm#CIHJHABF)
Para demonstrar a utilização de TDE, seguiremos os passos abaixo:
--------------------------------------------------------------------------
Para iniciar o passo-a-passo abaixo, é necessário conectar-se previamente no Banco de Dados desejado, através do SQL Plus, com um usuário com privilégios administrativos (usuário contendo a role DBA ou o privilégio de sistema SYSDBA), que não seja o SYS, pois se o passo 2 for executado pelo SYS ele irá falhar (o Oracle Database não permite utilizar TDE em objetos do schema SYS).
--------------------------------------------------------------------------
PASSO 1: Criando e referenciando o Wallet
a) Acrescente no arquivo sqlnet.ora da pasta network/admin do Oracle Home o seguinte bloco:
ENCRYPTION_WALLET_LOCATION =
(SOURCE=
(METHOD=file)
(METHOD_DATA=
(DIRECTORY=\ORACLE_BASE\admin\ORACLE_SID\wallet)))
O parâmetro DIRECTORY do bloco de código acima indica o caminho do arquivo Wallet que será criado no item "b)" deste passo e está redigido de acordo com os padrões do sistema de arquivos do Windows. Neste item, substitua os valores:
- ORACLE_BASE: pelo diretório correspondente ao Oracle Base em sua instalação de BD.
- ORACLE_SID: pelo nome da instância de BD.
Obs.: Se o diretório wallet não existir, crie-o dentro da pasta \ORACLE_BASE\admin\ORACLE_SID. A pasta "\ORACLE_BASE\admin\ORACLE_SID\wallet" é o caminho padrão de criação de Wallets.
b) Crie o Wallet e defina a chave mestra na instância de Banco de Dados (BD) em execução:
No prompt do SQL digite:
ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "Teste123";
Obs.: Substitua Teste123 pela senha desejada. Este item define uma senha mestra e automaticamente cria o Wallet na pasta padrão referenciada no item "a)".
PASSO 2: Criando e inserindo dados em uma tabela com uma coluna criptografada
Execute no SQL Plus os comandos abaixo:
CREATE TABLE table_tde_test (
ID NUMBER,
DATA1 VARCHAR2(40),
DATA2 VARCHAR2(40) ENCRYPT);
INSERT INTO table_tde_test (ID, DATA1, DATA2)
VALUES (1, 'Dados NÃO criptogrados na coluna DATA1!','Dados criptogrados na coluna DATA2!');
COMMIT;
ALTER SYSTEM FLUSH BUFFER_CACHE; --> força atualização dos dados no arquivo de dados da tabela
PASSO 3: Testando o TDE no nível da tabela
a) Execute um SELECT na tabela criada no passo anterior:
SELECT * FROM table_tde_test;
Resultado:
Todos os dados da tabela serão retornados, inclusive os dados da coluna DATA2, que estão criptografados, pois o Wallet foi criado e está aberto desde o passo 1.
b) Feche o Wallet:
ALTER SYSTEM SET WALLET CLOSE;
Resultado:
Wallet fechado.
c) Execute novamente o SELECT do item "a)":
SELECT * FROM table_tde_test;
Resultado:
O SELECT irá disparar o erro ORA-28365, pois com o Wallet fechado não é possível recuperar a senha de criptografia da tabela e decriptografar os dados.
d) Execute um novo SELECT sem retornar os dados da coluna criptografada (DATA2):
SELECT ID, DATA1 FROM table_tde_test;
Resultado:
A instrução SELECT irá retornar com sucesso pois sem a incluir a coluna DATA2, não é necessário consultar o Wallet.
PASSO 4: Testando o TDE no nível do arquivo de dados
a) Localize o arquivo de dados em que a tabela foi criada. A query abaixo poderá ser executada para descobrir o caminho completo do arquivo de dados se o usuário conectado tiver privilégios administrativos ou tiver privilégios de SELECT nas visões DBA_TABLES e DBA_DATA_FILES:
SELECT F.FILE_NAME
FROM DBA_TABLES T
INNER JOIN DBA_DATA_FILES F
ON T.TABLESPACE_NAME = F.TABLESPACE_NAME
WHERE T.TABLE_NAME = 'TABLE_TDE_TEST';
b) Entre na pasta localizada no item anterior:
- Se estiver usando SO Linux digite no prompt de comandos:
strings FILE.DBF | grep "criptogrados na coluna DATA"
Obs.: Substitua FILE.DBF pelo nome do arquivo de dados identificado no item anterior.
Scripts Scheduler Jobs
Olá Pessoal,
Nesta semana, estou compartilhando scripts que podem ser utilizados para recuperar informações sobre os principais objetos relacionados aos recursos de scheduler jobs (jobs, programs, schedulers, chains e windows).
Nesta semana, estou compartilhando scripts que podem ser utilizados para recuperar informações sobre os principais objetos relacionados aos recursos de scheduler jobs (jobs, programs, schedulers, chains e windows).
Os scripts são:
1- consultar_scheduler_jobs.sql:
Permite recuperar informações sobre as tarefas (jobs) criadas em uma instância de Banco de Dados, tais como: nome, tipo, intervalo de repetição, data de início, total de vezes que o job foi executado, total de vezes que o job falhou etc.
2- consultar_scheduler_programs.sql:
Permite recuperar informações sobre os programas de tarefas (programs) criados em uma instância de Banco de Dados, tais como: nome, proprietário, tipo, ação etc.
Permite recuperar informações sobre os programas de tarefas (programs) criados em uma instância de Banco de Dados, tais como: nome, proprietário, tipo, ação etc.
3- consultar_scheduler_schedules.sql:
Permite recuperar informações sobre os agendamentos de tarefas (schedules) criados em uma instância de Banco de Dados, tais como: nome, proprietário, tipo, intervalo de repetição etc.
4- consultar_scheduler_windows.sql:
5- consultar_scheduler_chains.sql:
4- consultar_scheduler_windows.sql:
Permite recuperar informações sobre as janelas de agendamentos (windows) criadas em uma instância de Banco de Dados, tais como: nome, proprietário, tipo, intervalo de repetição, duração, prioridade etc.
5- consultar_scheduler_chains.sql:
Permite recuperar informações sobre as cadeias de programas (chains) criadas em uma instância de Banco de Dados, tais como: nome, proprietário, qtde. passos, qtde. regras etc.
Para fazer download dos scripts (arquivo scripts_scheduler.zip), acessem a pasta Oracle/Scripts, em Pastas Públicas, no MEU SKY DRIVE. Descompacte o arquivo zip informando uma senha que deverá ser obtida assinando a newsletter que encontra-se no painel direito deste blog.
Até a próxima!
Até a próxima!
Assinar:
Comentários (Atom)
