Blog do DBA Valentim

How to get Oracle Database dependents objects

2017-06-09T17:05:00.000-03:00

Hi folks, (português mais abaixo)

After some time I decided to continue posting at this blog even having a channel on youtube. My channel was created to share some stuff like tips, experiences, tutorials about Databases (specially Oracle), Linux and new techologies all in portuguese language.
If you are interested in this stuff, please go to https://www.youtube.com/channel/UCDncbo0hpBTEYMjZNZwsfmw and subscribe.

Today, I'm glad to share with you a query to get objects dependencies in Oracle Databases. It's very useful when you have to change some object and want to see if some others objects have to be updated too to avoid a broken application. Here I'm using a hierarchical query to get object depedency by level:

SELECT LPAD('-',level) || owner || '.' || name ||'('|| type || ')' DEPENDENCY
FROM dba_dependencies
CONNECT BY PRIOR owner = referenced_owner AND prior name = referenced_name
AND prior type = referenced_type
START WITH referenced_owner = '&owner'
AND referenced_name = '&object'
AND owner IS NOT NULL;

You have to provide OWNER and OBJECT you are going to change. The depedencies will be tree-like displayed as below:

SYS@TEST > /
Enter value for owner: HR
Enter value for object: JOB_HISTORY

-HR.ADD_JOB_HISTORY(PROCEDURE)
-HR.UPDATE_JOB_HISTORY(TRIGGER)

2 rows selected.

Output above means UPDATE_JOB_HISTORY trigger depends on ADD_JOB_HISTORY procedure that depends on JOB_HISTORY table (provided object). Let's check:

SYS@TEST > select text from dba_source where name='UPDATE_JOB_HISTORY';

TRIGGER update_job_history
AFTER UPDATE OF job_id, department_id ON employees
FOR EACH ROW
BEGIN
add_job_history(:old.employee_id, :old.hire_date, sysdate,
:old.job_id, :old.department_id);
END;

7 rows selected.

SYS@TEST > select text from dba_source where name='ADD_JOB_HISTORY';

PROCEDURE add_job_history
( p_emp_id job_history.employee_id%type
, p_start_date job_history.start_date%type
, p_end_date job_history.end_date%type
, p_job_id job_history.job_id%type
, p_department_id job_history.department_id%type
)
IS
BEGIN
INSERT INTO job_history (employee_id, start_date, end_date,
job_id, department_id)
VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END add_job_history;

13 rows selected.

I hope this helps you, as DBA or DEVELOPER, to avoid broke things up when changing objects.

Til next post.
Eduardo Valentim
-------------------------------------------------- Português --------------------------------------------------
Fala pessoal,

Depois de algum tempo decidi continuar postando nesse blog mesmo tendo um canal no youtube. Meu canal foi criado para compartilhar algumas coisas como dicas, experiências, tutoriais sobre bancos de dados (principalmente Oracle), Linux e novas tecnologias tudo em português. Se estiver interessado nesse tipo de coisa, por favor acesse https://www.youtube.com/channel/UCDncbo0hpBTEYMjZNZwsfmw e assine o canal.

Hoje estou feliz de trazer pra você uma consulta para obter as dependências de objetos no banco de dados Oracle. É muito útil quando você tem que alterar algum objeto e quer ver se outros objetos também precisam ser atualizados para evitar que a aplicação "quebre". Aqui estou usando uma consulta hierárquica para obter o nível de dependência dos objetos:

SELECT LPAD('-',level) || owner || '.' || name ||'('|| type || ')' DEPENDENCY
FROM dba_dependencies
CONNECT BY PRIOR owner = referenced_owner AND prior name = referenced_name
AND prior type = referenced_type
START WITH referenced_owner = '&owner'
AND referenced_name = '&object'
AND owner IS NOT NULL;

Você precisa fornecer o OWNER e o OBJECT que você modificará. As dependências aparecerão no estilo árvore conforme abaixo:

SYS@TEST > /
Enter value for owner: HR
Enter value for object: JOB_HISTORY

-HR.ADD_JOB_HISTORY(PROCEDURE)
-HR.UPDATE_JOB_HISTORY(TRIGGER)

2 rows selected.

A saída acima significa que a trigger UPDATE_JOB_HISTORY depende da procedure ADD_JOB_HISTORY que depende da tabela JOB_HISTORY (objeto informado). Vamos verificar:

SYS@TEST > select text from dba_source where name='UPDATE_JOB_HISTORY';

TRIGGER update_job_history
AFTER UPDATE OF job_id, department_id ON employees
FOR EACH ROW
BEGIN
  add_job_history(:old.employee_id, :old.hire_date, sysdate,
:old.job_id, :old.department_id);
END;

7 rows selected.

SYS@TEST > select text from dba_source where name='ADD_JOB_HISTORY';

PROCEDURE add_job_history
( p_emp_id job_history.employee_id%type
, p_start_date job_history.start_date%type
, p_end_date job_history.end_date%type
, p_job_id job_history.job_id%type
, p_department_id job_history.department_id%type
)
IS
BEGIN
INSERT INTO job_history (employee_id, start_date, end_date,
job_id, department_id)
VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END add_job_history;

13 rows selected.

Espero que ajude você, como DBA ou DESENVOLVEDOR, a evitar quebrar as coisas quando estiver alterando objetos!

Até o próximo post.
Eduardo Valentim.

Installing Oracle GI. Database 11g (11.2.0.1) on OEL 6.5

2014-08-15T22:32:00.005-03:00

Amigos,
Criei este tutorial em vídeo para ajudá-los a instalar o Oracle GI com Database em um Linux 6 (Oracle Enterprise Linux ou Red Hat Enterprise Linux). Sabemos que há uma certa incompatibilidade (erro durante execução do root.sh do GI) nesta versão, mas foram contornadas e demonstradas neste vídeo. Quaisquer dúvidas ou comentários para melhorar os tutoriais são bem-vindos!

Abaixo algumas listagens de comandos para auxiliar na instalação:

- Pacotes para instalar o VMWARE TOOLS:

cd /mnt/Packages
yum -y install kernel-uek-headers
rpm -Uvh kernel-uek-devel*x86_64*
rpm -Uvh binutils-2*x86_64*
rpm -Uvh gcc-4*x86_64*

- Pacotes para a instalação do Oracle:

rpm -Uvh glibc-2*x86_64* nss-softokn-freebl-3*x86_64*
rpm -Uvh glibc-2*i686* nss-softokn-freebl-3*i686*
rpm -Uvh compat-libstdc++-33*x86_64*
rpm -Uvh glibc-common-2*x86_64*
rpm -Uvh glibc-devel-2*x86_64*
rpm -Uvh glibc-devel-2*i686*
rpm -Uvh glibc-headers-2*x86_64*
rpm -Uvh elfutils-libelf-0*x86_64*
rpm -Uvh elfutils-libelf-devel-0*x86_64*
rpm -Uvh libstdc++-devel-4*x86_64*
rpm -Uvh gcc-c++-4*x86_64*
rpm -Uvh ksh-*x86_64*
rpm -Uvh libaio-0*x86_64*
rpm -Uvh libaio-devel-0*x86_64*
rpm -Uvh libaio-0*i686*
rpm -Uvh libaio-devel-0*i686*
rpm -Uvh libgcc-4*x86_64*
rpm -Uvh libgcc-4*i686*
rpm -Uvh libstdc++-4*x86_64*
rpm -Uvh libstdc++-4*i686*
rpm -Uvh make-3.81*x86_64*
rpm -Uvh numactl-devel-2*x86_64*
rpm -Uvh sysstat-9*x86_64*
rpm -Uvh compat-libstdc++-33*i686*
rpm -Uvh compat-libcap*
rpm -Uvh unixODBC-2*x86_64*
rpm -Uvh unixODBC-devel-2*x86_64*
rpm -Uvh oracleasm-support-2*x86_64*
rpm -Uvh ksh-*x86_64*

- Parâmetros de Kernel:

kernel.sem = 250 32000 100 256
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576

- Limites de Kernel:

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 4096
oracle hard nofile 65536
oracle soft stack 10240

grid soft nproc 2047
grid hard nproc 16384
grid soft nofile 4096
grid hard nofile 65536
grid soft stack 10240

- Bash Profiles:

/home/grid/.bash_profile:

export ORACLE_BASE=/u01/app/grid
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/grid
export ORACLE_SID=+ASM
export PATH=$PATH:$ORACLE_HOME/bin
umask 022

/home/oracle/.bash_profile:

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_UNQNAME=orcl
export ORACLE_SID=orcl
export PATH=$PATH:$ORACLE_HOME/bin
umask 022

- Texto para inserir no script "/u01/app/grid/product/11.2.0/grid/crs/install/s_crsconfig_lib.pm" antes de executar o "root.sh" para evitar o erro "ohasd failed to start: Inappropriate ioctl for device":

my $UPSTART_OHASD_SERVICE = "oracle-ohasd";
my $INITCTL = "/sbin/initctl";

($status, @output) = system_cmd_capture ("$INITCTL start $UPSTART_OHASD_SERVICE");
if (0 != $status)
{
error ("Failed to start $UPSTART_OHASD_SERVICE, error: $!");
return $FAILED;
}

- Conteúdo do /etc/init/oracle-ohasd.conf criado antes de executar o "root.sh":

start on runlevel [35]
stop on runlevel [!35]
respawn
exec /etc/init.d/init.ohasd run >/dev/null 2>&1 </dev/null

Gerenciando Trilha de Auditoria Padrão e FGA no Oracle Database 11g

2013-11-18T14:48:00.002-03:00

Caros amigos, hoje vou compartilhar algo com vocês que eu criei e me ajudou muito em gerenciar o espaço ocupado pelo Audit Trail (trilha de auditoria) do banco de dados Oracle 11g quando usamos Auditoria Padrão ou FGA (Fine Grained Auditing). Por padrão o banco de dados Oracle 11g configura auditoria de algumas ações como por exemplo: logon, logoff, uso de privilégios "ANY", etc. gerando dados em tabelas como a SYS.AUD$ (para a auditoria padrão) e a SYS.FGA_LOG$ (para a FGA).
Com o tempo, a quantidade gerada de dados nestas tabelas pode fazer com que o tablespace SYSTEM cresça bastante e por isso é interessante separar os dados de auditoria em um tablespace diferente.
Se você tiver adquirido o recurso Oracle Audit Vault esta trilha de auditoria é gerenciada automaticamente, ou seja, é copiada para o banco de dados do Audit Vault e removida do banco de dados de origem. Além disso, o Audit Vault tem outros recursos de relatórios, envio de alertas e uma interface mais amigável para consulta de auditoria.

Tablespaces são unidades lógicas de armazenamento que o banco de dados Oracle utiliza para gravar seus dados. Nos tablespaces existem segmentos que são tabelas ou índices por exemplo. O que seria interessante criar é um tablespace novo para gravação destes dados de auditoria.

Criei um script que faz tudo isso de forma automática. Além de configurar a auditoria recomendada ele também move as tabelas de auditoria para fora do tablespace SYSTEM (de sistema, onde ficam outras tabelas internas do Oracle) e, mensalmente, copia dados de auditoria para uma outra tabela para consultas históricas deixando sempre a tabela de auditoria com poucos dados. No exemplo, uma tabela é criada no mesmo banco de dados, porém você pode adaptá-lo para armazenar a tabela em outro banco de dados.

Abaixo o script com a explicação sobre cada parte faz. Executar com usuário "SYS":

-- 1. Configurando auditoria (precisa de restart). Gerencie arquivos de auditoria gerados para o usuário SYS em audit_file_dest.

AUDIT ALL ON SYS.AUD$ BY ACCESS;
AUDIT ALL ON SYS.FGA_LOG$ BY ACCESS;
ALTER SYSTEM SET AUDIT_SYS_OPERATIONS=TRUE SCOPE=SPFILE SID='*';
ALTER SYSTEM SET AUDIT_TRAIL='DB_EXTENDED' SCOPE=SPFILE SID='*';

-- 2. Checando espaço necessário para criar tablespace TS_AUDIT_TRAIL de auditoria e conta AUDIT_TRAIL (precisa usar OMF, do contrário especifique o caminho manualmente). A conta AUDIT_TRAIL foi bloqueada propositalmente, pois não precisaremos ativá-la.

DECLARE V_BYTES NUMBER;
BEGIN
SELECT SUM(BYTES)*2 INTO V_BYTES
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = 'AUD$'
OR SEGMENT_NAME = 'FGA_LOG$'
OR SEGMENT_NAME IN
(SELECT SEGMENT_NAME
FROM DBA_LOBS
WHERE TABLE_NAME = 'AUD$' OR TABLE_NAME = 'FGA_LOG$');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('* Um tablespace TS_AUDIT_TRAIL com '||ROUND(V_BYTES/1024/1024)||' MBs vai ser criado. *');
DBMS_OUTPUT.PUT_LINE(CHR(10));
END;
/

DECLARE V_BYTES NUMBER;
BEGIN
SELECT SUM(BYTES)*2 INTO V_BYTES
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = 'AUD$'
OR SEGMENT_NAME = 'FGA_LOG$'
OR SEGMENT_NAME IN
(SELECT SEGMENT_NAME
FROM DBA_LOBS
WHERE TABLE_NAME = 'AUD$' OR TABLE_NAME = 'FGA_LOG$');
EXECUTE IMMEDIATE 'CREATE TABLESPACE TS_AUDIT_TRAIL DATAFILE SIZE '||V_BYTES||' AUTOEXTEND ON NEXT 100M SEGMENT SPACE MANAGEMENT AUTO';
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('* Tablespace com '||ROUND(V_BYTES/1024/1024)||' MBs criado com sucesso.');
END;
/

CREATE USER AUDIT_TRAIL IDENTIFIED BY aud$user$oracle
DEFAULT TABLESPACE TS_AUDIT_TRAIL
QUOTA UNLIMITED ON TS_AUDIT_TRAIL
ACCOUNT LOCK;

-- 3. Criando tabelas de histórico para arquivamento de dados de auditoria (crie índices posteriormente se desejar). A coluna "PLHOL" está sendo alterada de LONG para CLOB para permitir a cópia. Não haverá impacto durante a auditoria, pois esta coluna não é utilizada e provavelmente será removida no futuro.

ALTER TABLE SYS.FGA_LOG$ MODIFY(PLHOL CLOB);
CREATE TABLE AUDIT_TRAIL.AUDIT_ARCHIVE COMPRESS BASIC TABLESPACE TS_AUDIT_TRAIL AS SELECT * FROM SYS.AUD$;
CREATE TABLE AUDIT_TRAIL.AUDIT_ARCHIVE_FGA COMPRESS BASIC TABLESPACE TS_AUDIT_TRAIL AS SELECT * FROM SYS.FGA_LOG$;
DELETE FROM SYS.AUD$ WHERE NTIMESTAMP#<=(SELECT MAX(NTIMESTAMP#) FROM AUDIT_TRAIL.AUDIT_ARCHIVE);
DELETE FROM SYS.FGA_LOG$ WHERE NTIMESTAMP#<=(SELECT MAX(NTIMESTAMP#) FROM AUDIT_TRAIL.AUDIT_ARCHIVE_FGA);
COMMIT;

-- 4. Movendo auditoria para novo tablespace TS_AUDIT_TRAIL.

BEGIN
SYS.DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
AUDIT_TRAIL_LOCATION_VALUE => 'TS_AUDIT_TRAIL');
END;
/

-- 5. Realizando shrink da tabela AUD$ e FGA_LOG$ com coleta de estatisticas.

ALTER TABLE SYS.AUD$ ENABLE ROW MOVEMENT;
ALTER TABLE SYS.AUD$ SHRINK SPACE CASCADE;
ALTER TABLE SYS.FGA_LOG$ ENABLE ROW MOVEMENT;
ALTER TABLE SYS.FGA_LOG$ SHRINK SPACE CASCADE;
EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','AUD$');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','FGA_LOG$');

-------------------------------------------------------------------------------------
--Mantendo o audit trail. Configurar agendamento.
-------------------------------------------------------------------------------------

-- 6. Configurando Scheduler Job para executar manutenção de auditoria dia 01 de cada mês. Você pode alterar a frequência conforme a sua necessidade.

BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'SYS.AUDIT_TRAIL_MAINTENANCE'
,start_date => NULL
,repeat_interval => 'FREQ=MONTHLY;BYMONTHDAY=1;BYHOUR=00;BYMINUTE=00;BYSECOND=00'
,end_date => NULL
,job_class => 'DEFAULT_JOB_CLASS'
,job_type => 'PLSQL_BLOCK'
,job_action => 'BEGIN
INSERT INTO AUDIT_TRAIL.AUDIT_ARCHIVE SELECT * FROM SYS.AUD$;
INSERT INTO AUDIT_TRAIL.AUDIT_ARCHIVE_FGA SELECT * FROM SYS.FGA_LOG$;
DELETE FROM SYS.AUD$ WHERE NTIMESTAMP#<=(SELECT MAX(NTIMESTAMP#) FROM AUDIT_TRAIL.AUDIT_ARCHIVE);
DELETE FROM SYS.FGA_LOG$ WHERE NTIMESTAMP#<=(SELECT MAX(NTIMESTAMP#) FROM AUDIT_TRAIL.AUDIT_ARCHIVE_FGA);
COMMIT;
EXECUTE IMMEDIATE ''ALTER TABLE AUDIT_TRAIL.AUDIT_ARCHIVE MOVE COMPRESS BASIC'';
EXECUTE IMMEDIATE ''ALTER TABLE AUDIT_TRAIL.AUDIT_ARCHIVE_FGA MOVE COMPRESS BASIC'';
DBMS_STATS.GATHER_TABLE_STATS(''SYS'',''AUD$'');
DBMS_STATS.GATHER_TABLE_STATS(''SYS'',''FGA_LOG$'');
END;'
,comments => NULL
);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYS.AUDIT_TRAIL_MAINTENANCE'
,attribute => 'RESTARTABLE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYS.AUDIT_TRAIL_MAINTENANCE'
,attribute => 'LOGGING_LEVEL'
,value => SYS.DBMS_SCHEDULER.LOGGING_RUNS);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'SYS.AUDIT_TRAIL_MAINTENANCE'
,attribute => 'MAX_FAILURES');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'SYS.AUDIT_TRAIL_MAINTENANCE'
,attribute => 'MAX_RUNS');
BEGIN
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYS.AUDIT_TRAIL_MAINTENANCE'
,attribute => 'STOP_ON_WINDOW_CLOSE'
,value => FALSE);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYS.AUDIT_TRAIL_MAINTENANCE'
,attribute => 'JOB_PRIORITY'
,value => 3);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'SYS.AUDIT_TRAIL_MAINTENANCE'
,attribute => 'SCHEDULE_LIMIT');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYS.AUDIT_TRAIL_MAINTENANCE'
,attribute => 'AUTO_DROP'
,value => FALSE);

SYS.DBMS_SCHEDULER.ENABLE
(name => 'SYS.AUDIT_TRAIL_MAINTENANCE');
END;
/

-- 7. Testando a execucao do Job. Verifique a saida LAST_RUN_STATUS abaixo. Em caso de "FAILED", verificar os erros consultando as tabelas DBA_SCHEDULER_JOB_RUN_DETAILS.

BEGIN
DBMS_SCHEDULER.run_job (job_name => 'SYS.AUDIT_TRAIL_MAINTENANCE',use_current_session => FALSE);
END;
/
COLUMN LAST_RUN_STATUS format a11
SELECT STATUS LAST_RUN_STATUS
FROM DBA_SCHEDULER_JOB_RUN_DETAILS
WHERE JOB_NAME = 'AUDIT_TRAIL_MAINTENANCE'
AND ACTUAL_START_DATE=(SELECT MAX(ACTUAL_START_DATE) FROM DBA_SCHEDULER_JOB_RUN_DETAILS);

----------------- Processo concluído -----------------

Caso necessite exportar a tabela de auditoria para expurgar dados antigos, basta executar o script abaixo no sistema operacional UNIX. Depois poderá excluir os dados da tabela AUDIT_ARCHIVE:

data=`date +%d-%m-%Y-%H:%M`
expdp <user/pass> dumpfile=EXPORT_AUD_$data.DMP logfile=EXPORT_AUD_$data.LOG version=COMPATIBLE directory=<directory> schemas=AUDIT_TRAIL include=table:\"IN$\'AUDIT_ARCHIVE\'$\"

Até mais!

Evitando FULL TABLE SCANS quando usar IS NULL em cláusulas WHERE

2013-11-12T11:20:00.001-03:00

Olá pessoal,

Neste post vou falar sobre um tuning simples, mas eficaz quando trabalhamos com valores nulos em filtros.

Antes de tudo, contextualizando:

O que é uma tabela?
R. Uma tabela é um objeto do tipo SEGMENTO no banco de dados que armazena dados em forma de linhas e colunas, certo? Tipo uma planilha do excel:

O que é um índice?
R. Índice é outro objeto do tipo SEGMENTO que, uma vez criado em uma tabela por exemplo poderá ser utilizado para agilizar a pesquisa quando alguns critérios forem utilizados. Imagine um índice de um livro. Ele é feito para agilizar a pesquisa e você encontrar o assunto que está procurando de forma mais rápida. No banco de dados é similar, o índice é constituído basicamente do valor da coluna (ou colunas) indexada da tabela com um ponteiro que aponta para a linha (ou linhas) que possui aquele valor. Basicamente assim:

- Funções é um objeto de código de programação que retorna algum valor. Por exemplo: a função SUM retorna o somatório dos valores, a função AVG a média de valores, a função MIN retorna o valor mínimo de uma determinada coluna, a função UPPER transforma valores minúsculos em maiúsculos. Por exemplo:

SELECT UPPER('texto') FROM DUAL;
-- Retorno da consulta usando a função:
TEXTO

Agora vamos pra questão em si.

Imagine agora uma tabela chamada TB_ALUNOS em que há uma coluna chamada TELEFONE. Nesta coluna TELEFONE são cadastrados os TELEFONES dos alunos, mas você percebe que nesta coluna são permitidos valores NULOS, ou seja, permite que nenhum telefone seja inserido. Além disso você cria um índice nesta coluna TELEFONE para agilizar pesquisas filtradas pelo telefone. Até aí tudo bem, mas diante disso você quer saber quais alunos NÃO possuem telefones cadastrados. O que você faz? Traduzindo para a linguagem SQL seria assim:

SELECT * FROM tb_alunos WHERE telefone IS NULL;ou SELECT * FROM tb_alunos WHERE telefone='';

- Exemplo de criação de um índice comum na coluna TELEFONE do exemplo:

CREATE INDEX idx_alunos_telefone ON tb_alunos(telefone);

O plano de execução gerado para a consulta acima realizaria um FULL TABLE SCAN (FTS) mesmo se o índice fosse criado (levariam aproximadamente 9 minutos de execução):

Execution Plan
----------------------------------------------------------
Plan hash value: 2837633100
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 57664 | 2027K| 44448 (1)| 00:08:54 |
|* 1 | TABLE ACCESS FULL| TB_ALUNOS | 57664 | 2027K| 44448 (1)| 00:08:54 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TELEFONE" IS NULL)

Quando usamos um filtro procurando por valores NULOS em uma tabela, o banco de dados executa um FTS (Full Table Scan) que é uma leitura COMPLETA da tabela por linhas que possuem valores NULOS, ou seja, alunos que não possuem telefones cadastrados. Como o valor NULO na verdade é a ausência de valor, ou seja, NADA foi gravado naquela coluna, então como indexar?
Quando você criou um índice para aquela coluna, todos os valores que não existiam (NULOS) também não existiam no índice. Então o Oracle não teria como usar o índice para saber quais seriam os valores NULOS.

Existe duas formas mais conhecidas de contornar (leia-se "workaround" ou "adaptações técnicas").

1. Criando um índice baseado em função (Function-Based Index):

Existem vários tipos de índices que você pode criar para várias situações. Neste caso você pode criar um índice baseado em função (FUNCTION BASED INDEX) que irá gerar dados no índice de acordo com uma função, mesmo que os dados não estejam (ou não existam) na origem (coluna ou colunas). Se criarmos um índice desse tipo usando a função NVL, o valor NULO será substituído pelo valor arbitrário informado. Vamos explicar melhor. Veja primeiro o exemplo de uso da função NVL:

SELECT nome,NVL(telefone,'SEM TELEFONE') FROM tb_alunos;

Nessa consulta, todos os alunos que não possuírem dados na coluna TELEFONE, ou seja, que não possuírem telefone cadastrado, ao invés de não aparecer valor, vai aparecer "SEM TELEFONE" no resultado da consulta:

NOME TELEFONE
--------- -------------
Fernando 98989898
Renato 78787878
Natália SEM TELEFONE
Hamilton 45454545

Ou seja, a função NLV substituiu o valor NULO por um texto específico durante a execução. Claro que esse texto é apenas ilustrativo, ou seja, só aparece quando consultamos os dados. Não estamos alterando a tabela, apenas a forma de visualizar o resultado. Isso que o comando SELECT faz, consultas.

Agora vamos misturar a função com o índice criando um FUNCTION-BASED INDEX para usá-lo depois em uma consulta:

CREATE INDEX idx_alunos_nvltelefone ON tb_alunos(NVL(telefone,0));

Esse índice será criado um pouco diferente do índice comum. Ao invés de não criar valores nulos, ele irá criar valores "0" onde existirem NULOS (valores arbitrários). Assim a consulta abaixo utilizará este índice ao invés de realizar um FTS:

SELECT * FROM tb_alunos WHERE NVL(telefone,0)=0;

------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 32 | 1376 | 19 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_ALUNOS | 32 | 1376 | 19 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_ALUNOS_NVLTELEFONE | 32 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(NVL("TELEFONE",0)=0)

2. Criando um índice composto (Composite Index) com valor arbitrário (Oracle 11g):

Um índice composto é quando criamos um índice formado por mais de uma coluna. No nosso caso, vamos especificar a primeira coluna TELEFONE e a segunda coluna um valor arbitrário "0". Veja o comando de criação por exemplo:

CREATE INDEX idx_alunos_nometelefone ON tb_alunos(telefone,0);

A coluna TELEFONE da tabela TB_ALUNOS foi concatenada (junta) a "0" no índice, possível na versão 11g. Dessa forma garantimos que sempre haverá valor indexado, pois os valores das colunas serão concatenadas. Onde for NULO na coluna TELEFONE, na outra coluna haverá "0", portanto há entrada no índice. Veja o exemplo da consulta:

SELECT * FROM tb_alunos WHERE telefone IS NULL;

Execution Plan
----------------------------------------------------------
Plan hash value: 1405752932
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 57664 | 2027K| 27682 (1)| 00:05:33 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_ALUNOS | 57664 | 2027K| 27682 (1)| 00:05:33 |
|* 2 | INDEX RANGE SCAN | IDX_ALUNOS_TELEFONENULL | 57664 | | 160 (0)| 00:00:02 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TELEFONE" IS NULL)

Em alguns casos, mesmo com índices voltados para os valores NULOS, um FTS pode ser mais vantajoso, pois se a quantidade retornada for maior que 50%, por padrão, o Oracle irá executar um FTS ao invés de usar o índice.

Copiar a instalação Oracle de um Linux para outro funciona?

2013-10-18T11:28:00.001-03:00

Ok ok, eu sei que eu não atualizo meu blog com frequência, mas estou aqui de volta pra falar sobre uma questão que de vez em quando me perguntam:

Copiar uma instalação já realizada do Oracle (Oracle Base e Oracle Home) de um sistema operacional Linux para um outro funciona?

Bem, se o sistema operacional for equivalente, ou seja, mesma distribuição, mesma versão e mesma arquitetura (32 ou 64 bits) posso dizer que funciona sim. Se a distribuição for diferente eu já não posso garantir que funcionaria. Versões diferentes talvez funcione, mas arquiteturas diferentes não vai funcionar.

Muitas vezes sabemos que é muito mais cômodo copiarmos a instalação de uma máquina para outra quando queremos clonar ou migrar esta instalação. Eu concordo, mas faça isso apenas se não tiver outro jeito ou se for muito complicado realizar uma nova instalação do software Oracle, pois uma nova instalação poderá sair melhor que a antiga, mais atualizada, mais limpa.
Saiba que mesmo copiando a instalação você terá que fazer as configurações de pré-requisitos de instalação Oracle (configurações de kernel, limites, permissões, etc.) com exceção de alguns pacotes, pois há pacotes que são necessários apenas durante a instalação, mas é bom instalar para garantir. Vai que é preciso instalar posteriormente alguma coisa.

Tive que fazer essa pequena "manobra" recentemente, pois a instalação era versão 10g e, por estar descontinuado, a Oracle não disponibilizava facilmente o download. Daí foi necessário transferir a instalação para uma outra máquina. Era um OEL5.2 para um OEL5.8 funcionando sem problemas, mas mantive configurações gerais do S.O, hostname, ip e demais configurações de pré-requisitos para o funcionamento do Oracle. Tudo isso para restabelecer o ambiente do cliente o mais breve possível.

Até mais!

Erros comuns ao se conectar ao Oracle no Windows

2012-08-17T14:07:00.002-03:00

Caros amigos,

Geralmente temos problemas quando, pela primeira vez, encontramos um banco de dados Oracle instalado em um servidor Windows. Apesar de mais fácil, a instalação do Windows também deve ser realizada conforme as recomendações da Oracle e com atenção.
Quando não é feita assim é que acontecem os desafios de administração posteriormente. Abaixo algumas situações desafiadoras que podemos encontrar por aí:

1. Centenas de instalações Oracle em uma única máquina.
Evite isso! Faça a instalação essencial do software Oracle na máquina. Client, Database, InstantClient, etc. Escolha um e pronto. Geralmente é encontrado em um servidor windows várias instalações client e/ou banco de dados gerando vários Oracle Homes tornando a administração complicada. Quando você faz uma configuração em um Home (em um tnsnames.ora por exemplo), você percebe que não surtiu efeito e provavelmente é por isso.

Para descobrir em que Oracle Home você está, você pode executar o comando "TNSPING qualquer_coisa" apenas para ele mostrar qual arquivo sqlnet.ora ele usou como parâmetro. Assim:

C:\Documents and Settings\oracle>tnsping asdf

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 17-AUG-2012 13:37:52

Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
C:\app\oracle\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
TNS-03505: Failed to resolve name

Perceba que seu Oracle Home é o que está em negrito.

2. Erro de adaptador de protocolo (ORA-12650: TNS:protocol adapter error).
Erro bastante comum quando se tenta acessar a instância local via "sqlplus / as sysdba" no prompt de comando. Neste caso você deve verificar os seguintes itens:
a. Verifique se o serviço relacionado à instância está no ar;
b. Verifique em que Oracle Home está e se a variável PATH aponta para o caminho $ORACLE_HOME/bin que deveria;
c. Verifique o log de alerta para ver se a instância realmente está no ar;
d. Verifique se o usuário que está usando está no grupo "ora_dba" (criado na instalação);
e. Verifique as variáveis de ambiente e registro em busca das definições para ORACLE_HOME/ORACLE_BASE/ORACLE_SID;
f. Se não descobrir.... google it! Pode ser coisa pior.

3. Erro de privilégios insuficientes (ORA-01031: insufficient privileges)
Também muito comum e geralmente tem a ver com dois itens:
a. Usuário fora do grupo "ora_dba" (criado na instalação);
b. Configuração no arquivo SQLNET.ORA: Se o parâmetro "SQLNET.AUTHENTICATION_SERVICES" estiver definido para NONE, não permitirá conexões "/ as sysdba". Defina o parâmetro para NTS ou ALL e voltará a permitir.

4. Falha na escrita do log de eventos do Windows - EventViewer (ORA-28056: Writing audit records to Windows Event Log failed.)
Esse eu vi hoje e não deve ser muito comum, mas pode acontecer quando ocorre alguma tentativa frustrada na escrita de informações no log de eventos do Windows. Como o erro abaixo:

C:\Documents and Settings\oracle>sqlplus / as sysdba
TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 17-AUG-2012 14:25:22
Copyright (c) 1997, 2010, Oracle. All rights reserved.
ORA-28056: Writing audit records to Windows Event Log failed
OSD-157797404: Message 157797404 not found; product=RDBMS; facility=SOSD
ORA-28056: Writing audit records to Windows Event Log failed
OSD-157797404: Message 157797404 not found; product=RDBMS; facility=SOSD
O/S-Error: (OS 1502) The event log file is full.

As mensagens em negrito vão te ajudar na identificação do problema.

O banco de dados Oracle pode querer escrever algumas informações no log de eventos do sistema (Linux ou Windows). Se ele não conseguir, este erro é retornado. O motivo pode ser por log cheio ou, no caso do windows mais especificamente, o serviço "Log de Eventos" não estar no ar.

Esses são os problemas mais comuns. Se tiver mais algum, manda aí!

Restore database 10g em 11gR2 em mesma arquitetura

2012-06-28T16:54:00.002-03:00

Caros amigos,

Precisei restaurar um backup de uma base de dados 10g, porém não achei mais a instalação do software Oracle versão 10g e por isso tive que instalar no 11g e efetuar o Upgrade.
A restauração ocorreu normal. Preparei o ambiente (spfile, pastas, etc) e comecei a restauração:

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=OLDDB
export PATH=$PATH:/u01/app/oracle/product/11.2.0/dbhome_1/bin:.:/usr/bin:/etc:/usr/sbin:/usr/ucb:/usr/bin/X11:/sbin:/u01/app/oracle/product/11.2.0/dbhome_1/OPatch
RMAN> restore controlfile from '/u01/olddb/o1_mf_ncsn0_TAG20120309T190017_7onzdd6z_.bkp';
RMAN> alter database mount;
RMAN> catalog start with '/u01/olddb' noprompt;
RMAN> run{
set newname for datafile 1 to new;
set newname for datafile 2 to new;
set newname for datafile 3 to new;
set newname for datafile 4 to new;
restore database;
switch datafile all;
recover database;}
RMAN> exit

--# Pronto, agora o open resetlogs no SQLPLUS e a tentativa de execução do script de Pre-Upgrade:

SQL> alter database open resetlogs upgrade; --# (ou STARTUP UPGRADE após um "alter database open resetlogs")
Database altered.
SQL> @$ORACLE_HOME/rdbms/admin/utlu112i.sql
ERROR:
ORA-04023: Object SYS.STANDARD could not be validated or authorized

DECLARE
*
ERROR at line 1:
ORA-04023: Object SYS.STANDARD could not be validated or authorized

ERROR:
ORA-04023: Object SYS.STANDARD could not be validated or authorized

--# Pelo erro não ia dar muito certo, mas eu tentei executar o UPGRADE:

SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The first time this script is run, there should be no error messages
DOC> generated; all normal upgrade error messages are suppressed.
DOC>
.. --#(para brevidade)
DOC>#######################################################################
DOC>#######################################################################
DOC> The following error is generated if the pre-upgrade tool has not been
DOC> run in the old ORACLE_HOME home prior to upgrading a pre-11.2 database:
DOC>
DOC> SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
DOC> *
DOC> ERROR at line 1:
DOC> ORA-01722: invalid number
DOC>
DOC> o Action:
DOC> Shutdown database ("alter system checkpoint" and then "shutdown abort").
DOC> Revert to the original oracle home and start the database.
DOC> Run pre-upgrade tool against the database.
DOC> Review and take appropriate actions based on the pre-upgrade
DOC> output before opening the datatabase in the new software version.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
*
ERROR at line 1:
ORA-01722: invalid number

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

--# Em contato com o suporte Oracle eles pediram pra eu fazer isso e rodar o Upgrade de novo, mas não deu certo:

SQL> ALTER TABLE registry$database ADD TZ_VERSION number;
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql --# mesmo erro acima
...
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
*
ERROR at line 1:
ORA-01722: invalid number

--# Ele pediu então que eu verificasse as tabelas abaixo e comparasse o valor do TZ_VERSION da visão registry$database que deveria estar com o mesmo valor do campo VERSION na visão v$timezone_file:

SQL> select * from registry$database ;
PLATFORM_ID PLATFORM_NAME EDITION TZ_VERSION
----------- ----------------------------------------------------------------------------------------------------- ------------------------------ ----------
6 AIX-Based Systems (64-bit)

SQL> Select version from v$timezone_file;

VERSION
----------
14

--# Pelo jeito não estava, então ele pediu pra inserir:

SQL> INSERT into registry$database

(platform_id, platform_name, edition, tz_version)
VALUES ((select platform_id from v$database),
(select platform_name from v$database),
NULL,
(select version from v$timezone_file));

--# Ficou assim:

SQL> select * from registry$database ;
PLATFORM_ID PLATFORM_NAME EDITION TZ_VERSION
----------- ----------------------------------------------------------------------------------------------------- ------------------------------ ----------
6 AIX-Based Systems (64-bit)
6 AIX-Based Systems (64-bit) 14

--# Aparentemente não ia dar certo, mas executei e deu o erro confirmando o que deveria ser feito:

SQL> @catupgrd.sql
... --# (para brevidade)
DOC> output before opening the datatabase in the new software version.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
Session altered.

Table created.

Table altered.
((SELECT tz_version from registry$database) is null)
*
ERROR at line 8:
ORA-01427: single-row subquery returns more than one row
SQL> delete from registry$database where tz_version is null;
1 row deleted.
SQL> commit;
Commit complete.
SQL> @catupgrd.sql --Sucesso!
SQL> @utlrp.sql --Sucesso!
SQL> @utlu112s.sql --Tudo VALID! (exceto para o Oracle Multimedia, mas não era necessário. Se precisar basta reconfigurar o recurso)

No final das contas deu tudo certo. Não consegui achar na internet uma solução que fosse satisfatória e só consegui com um direcionamento do suporte Oracle.
Até mais!

Script facilitador de vida - RAC Edition

2012-02-21T11:39:00.002-03:00

Caros amigos leitores.

O trabalho é árduo e o tempo é curto. Aproveitei o carnaval para fazer umas migrações para um ambiente em RAC e como ficaram várias instâncias (por enquanto 7 por nó e vai aumentar depois) fiz um script pra executar comandos em várias bases assim como o post anterior "Script Facilitador de Vidas - Várias bases de dados". Como disse o "Grande Soldador": viu a necessidade, atenda! O script anterior era válido para instância única então fiz esse para quem trabalha com Oracle RAC.

De início o script pede o número do nó no ambiente cluster. Depois pede o utilitário para execução que cadastrei o SQLPLUS e o RMAN. Por último o comando que será executado.
Na execução, o script abaixo criará um arquivo de entrada que é chamado pelo utilitário escolhido. Podem alterar o script para adaptar a alguma necessidade como: catálogo rman, etc. Como o script anterior, usa referências no "/etc/oratab".

Sei que o GRID CONTROL, que também utilizamos, possui um recurso de executar comando em várias bases de dados, mas não fornece output, apenas se o comando deu erro ou não.

Segue abaixo o script e a saída como teste (desculpem a minha mania de colocar em inglês, prefiro pra evitar os problemas com acentos):

#!/bin/bash
echo "Provide Node Number (RAC):"
read node_number
if [ -z $node_number ]; then
   echo "You have to provide a valid node number (datatype number(1))"
fi
echo "Provide utility:"
echo "SQLPLUS => 1"
echo "RMAN => 2"
read utility
if [ -z $utility ]; then
   echo "You have to provide a valid utility number (datatype number(1))"
fi
echo "Provide command(s):"
read commands
#if [ -z $commands ]; then
#   echo "You have to provide a valid command (datatype varchar)"
#fi
case $utility in
1) touch commands.sql
        echo "prompt -- Output for instance &_connect_identifier:" > commands.sql
        echo "$commands" >> commands.sql
         for i in `cat /etc/oratab|grep -v "^*"|sed '/^$/d'|sed '/^#/d'|sed '/^+/d'|cut -d ":" -f 1`; do `echo "export ORACLE_SID=$i$node_number"`; sqlplus -S / as sysdba @commands<<EOF
prompt
exit
EOF
         done
         rm commands.sql
         echo "End of SQLPLUS execution.";;
2) touch commands.rman
         echo $commands > commands.rman
     for i in `cat /etc/oratab|grep -v "^*"|sed '/^$/d'|sed '/^#/d'|sed '/^+/d'|cut -d ":" -f 1`; do `echo "export ORACLE_SID=$i$node_number"`;rman catalog rman/rmancat@rman target / cmdfile=commands.rman<<EOF
exit
EOF
         done
         rm commands.rman
         echo "End of RMAN execution.";;
esac

Saída:

[oracle@node2]/home/oracle$ ./exec_commands.sh
Provide Node Number (RAC):
2
Provide utility:
SQLPLUS => 1
RMAN => 2
1
Provide command(s):
show parameter instance_name
-- Output for instance MICROSOFT2:

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string MICROSOFT2

-- Output for instance GOOGLE2:

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string GOOGLE 2

-- Output for instance APPLE2:

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string APPLE2

-- Output for instance ORACLE2:

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string ORACLE2

-- Output for instance DATAMANAGER2:

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string DATAMANAGER2

-- Output for instance SAP2:

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string SAP2

-- Output for instance AMAZON2:

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string AMAZON2

End of SQLPLUS execution.

Façam bom uso.

Script facilitador de vida - Várias bases de dados

2011-09-29T14:05:00.001-03:00

E aí galera!?

Bom, hoje tive que criar um procedimento de desligamento para todas as bases de dados do lugar onde eu trabalho. Foi solicitado para documentação.
Pensei no trabalho que iria ser fazer um script para cada base de dados em cada servidor (e não são poucos) que fizesse o seguinte:

export ORACLE_SID=(instância1)
sqlplus / as sysdba <<EOF
shutdown immediate;
exit
EOF
export ORACLE_SID=(instância2)
sqlplus / as sysdba <<EOF
....

Criei um script onde isso poderia tornar as coisas mais fáceis e que se aplica a qualquer servidor do ambiente:
(UPDATE 17/01/2012 - Script atualizado para ignorar linhas que começam com "*" geralmente criadas quando um agente do grid control é instalado)
(UPDATE 06/03/2012 - Script reformulado, agora ele solicita o comando. É similar ao script do post "RAC EDITION")

#!/bin/bash
echo "Provide utility:"
echo "SQLPLUS => 1"
echo "RMAN => 2"
read utility
if [ -z $utility ]; then
   echo "You have to provide a valid utility number (datatype number(1))"
fi
echo "Provide command(s):"
read commands
#if [ -z $commands ]; then
#   echo "You have to provide a valid command (datatype varchar)"
#fi
case $utility in
1) touch commands.sql
        echo "prompt -- Output for instance &_connect_identifier:" > commands.sql
        echo "$commands" >> commands.sql
         for i in `cat /etc/oratab|grep -v "^*"|sed '/^$/d'|sed '/^#/d'|sed '/^+/d'|cut -d ":" -f 1`; do `echo "export ORACLE_SID=$i$node_number"`; sqlplus -S / as sysdba @commands<<EOF
prompt
exit
EOF
         done
         rm commands.sql
         echo "End of SQLPLUS execution.";;
2) touch commands.rman
         echo $commands > commands.rman
     for i in `cat /etc/oratab|grep -v "^*"|sed '/^$/d'|sed '/^#/d'|sed '/^+/d'|cut -d ":" -f 1`; do `echo "export ORACLE_SID=$i$node_number"`;rman catalog rman/rmancat@rman target / cmdfile=commands.rman<<EOF
exit
EOF
         done
         rm commands.rman
         echo "End of RMAN execution.";;
esac

Esse script consulta o arquivo /etc/oratab que, no ambiente LINUX/UNIX, possui a lista das instâncias (criadas pelo DBCA), faz o filtro e usa como parâmetro para executar o comando dentro do FOR.

O interessante é que vários tipos de comandos podem ser utilizados nesse caso, basta ter o /etc/oratab atualizado!

Como otimizar backups de BIGFILES usando RMAN MULTISECTION - 11g

2011-08-17T15:02:00.004-03:00

OBS.: Reformulei este antigo post que falava sobre BIGFILE. Anteriormente eu passei um script que quebrava o arquivo em partes iguais de acordo com o valor do paralelismo, mas se o arquivo for muito grande vai demorar da mesma forma. Não é obrigatório ser igual ao número de canais. É possível quebrar o arquivo em tamanhos menores e quando o canal terminar uma parte iniciará a outra. Desta forma é mais recomendável.

O Bigfile pode ajudar em vários fatores, mas é temos que saber utilizá-lo. O tempo necessário para backup/restore aumenta consideravelmente quando trabalhamos com ele sem MULTISECTION. Prefira SMALLFILE quando possível. Melhor ainda: faça um balanço entre usar BIGFILE e SMALLFILE, analisando a questão da quantidade de arquivos, tempos de backup/restore, limitações, etc.

Foi incluído um recurso interessante no RMAN (Recovery Manager) do Oracle 11g que são os "backups multissessão" . Resumindo, para as tablespaces BIGFILE (tablespaces que possuem somente um arquivo de dados com tamanho limite bem maior, 32TB para blocos de 8KB por exemplo, do que o tradicional SMALLFILE de 32GB de limite) o backup pode ser realizado "quebrando" o BIGFILE em partes otimizando o tempo de backup.

Fiz um teste de tempo no ambiente em que trabalho atualmente e consegui diminuir bastante o tempo, mesmo sendo um backup em disco. Sempre é necessário verificar a infraestrutura para determinar o real custo/benefício de se utilizar paralelismo (consequência de utilizar multissessão). Em ambientes onde são utilizados backups em fita por exemplo, é necessário verificar quantos drives serão utilizados durante o backup sem comprometer o resto do ambiente e em ambientes onde os backups são realizados em disco é bom verificar se o paralelismo irá sobrecarregar o I/O podendo levar até mais tempo do que anteriormente. OBS.: Paralelismo de backups só na ENTERPRISE EDITION.

No teste, uma base de dados "X" possuía 190GB (180GB só uma tablespace BIGFILE) e o backup levava mais tempo que outra base de dados com o dobro do espaço alocado (o tamanho final de saída em disco do backup era praticamente o mesmo). Tudo isso por conta do BIGFILE.

O backup deve usar a cláusula SECTION SIZE (tamanho) para quebrar os BIGFILES do banco de dados. Veja os exemplos:

RMAN> backup section size 5G tablespace BIG_DATA; (para apenas o tablespace)

ou

RMAN> backup section size 5G database; (o RMAN automaticamente fará a quebra para todos os tablespaces bigfiles do banco de dados durante o backup total.)

Somente adicionando a cláusula, o backup que levava 8h30min, caiu pra 2h30min.

Restore no RMAN falha com "ORA-01180: can not create datafile 1"

2011-06-21T11:00:00.002-03:00

Depois de um tempo ausente, volto com este probleminha que já aconteceu comigo duas vezes.
Pediram-me para que eu restaurasse um banco de dados para criação de uma base de testes (ainda bem) e no momento da restauração algo assim aconteceu:

creating datafile No=1 name=/....../o1_mf_system_5n2w3nky_.dbf
released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/05/2010 09:34:12
ORA-01180: can not create datafile 1
ORA-01110: data file 1: '/....../o1_mf_system_5n2w3nky_.dbf'

Segundo o METALINK, isso acontece porque "O Rman acha que não tem backups pra restaurar, então ele recria os arquivos para aplicar os archives. No entanto isso não funciona para a tablespace SYSTEM". De fato é verdade. Quando tentamos restaurar os demais arquivos dá certo, só não para a SYSTEM.

Bom, no metalink ele diz que quando você executa um LIST BACKUP diz que existem backups disponíveis. O problema é que existem entradas duplicadas no controlfile, duas incarnações com o mesmo reset SCN:

DB Key	Inc Key	DB Name	DB ID	STATUS	Reset SCN	Reset Time
1	1	ORCL	3441889539	PARENT	1	20-NOV-07
3 (*)	3	ORCL	3441889539	CURRENT	2388963095	30-APR-08
2 (*)	2	ORCL	3441889539	PARENT	2388963095	26-APR-08

A única forma de isso acontecer é se o controlfile foi restaurado de um backup frio via sistema operacional e então outro resetlogs foi executado, gerando o mesmo SCN.

Todos os backups pertencentes a ambas as incarnações (2 e 3) se tornam ao mesmo tempo backups órfãos e atuais.

O metalink informa que isso é um BUG (Bug 5844752).

Mas existe também outra condição que pode resultar neste problema (no meu caso). Quando há um RESETLOGS gerando uma nova incarnação (daí são criados alguns arquivos físicos, etc) e é executado um "RESET DATABASE TO INCARNATION X" para voltar a incarnação anterior (por motivos ainda desconhecidos). Então a incarnação nova (criada com o resetlogs) fica ORPHAN (órfã) gerando o mesmo problema.

Usando a mesma tabela acima como exemplo, o resultado fica mais ou menos assim:

DB Key	Inc Key	DB Name	DB ID	STATUS	Reset SCN	Reset Time
1	1	ORCL	3441889539	CURRENT	1	20-NOV-07
2	2	ORCL	3441889539	ORPHAN	2388963095	30-APR-08

Desta forma o erro acontece na hora da restauração.

O problema é você precisar restaurar esse backup e ele estar nestas condições, por isso é importante saber se está tudo em ordem (incarnações anteriores PARENT e a última CURRENT) antes de realizar os backups para que não aconteçam problemas durante a restauração.

Para os dois casos similares a recriação do controlfile, recatalogação dos backups e restore pode funcionar. Isso para contornar o problema em caso de tentativa de restauração do backup.

O procedimento é:
1. Restaurar o controlfile primeiramente;
2. Gerar um script de recriação do controlfile (backup controlfile to trace;)
3. Recriar o controlfile;
4. Recatalogar os backups;
5. Executar a restauração;
6. Rezar.

Para corrigir o problema na base atual (para que os backups fiquem OK) você pode apenas executar um "RESET DATABASE TO INCARNATION X" voltando para última incarnação (que estava ORPHAN). Recriar o controlfile também deve solucionar o problema, mas é interessante analisar o caso e, preferencialmente, realizar um backup para executar um método ou o outro. Comigo, no caso da incarnação ORPHAN, corrigi o problema apenas executando um "RESET DATABASE TO INCARNATION". Após isso executei um backup e um restore sem problemas.

UPDATE 11/07/2011:

Passei por um problema desse aí galera, mais uma vez, semana passada. No entanto eu percebo que esse problema pode acontecer em situações diferentes e nem sempre vai ter uma explicação bem concreta sobre o caso.

Um amigo que trabalha comigo estava tentando restaurar uma base em outro servidor. Copiou o backup e na hora de restaurar o erro era diferente.
O RMAN dizia que não existia backups daqueles datafiles, apesar de estar listando no RMAN normalmente:

RMAN-06023: no backup or copy of datafile 1 found to restore

Achávamos que era por causa do NFS (estávamos testando restaurar pelo NFS por conta do pouco espaço em disco local), mas mesmo copiando dava o mesmo erro.

Decidi então listar as incarnações novamente no servidor onde o backup estava sendo restaurado pra ver o que tinha lá. Apareceram 3 incarnações:

PARENT 12-FEV-2008
PARENT 23-OUT-2010
CURRENT 01-JUL-2011

Em produção apareciam só as duas primeiras:

PARENT ...
CURRENT ...

Ninguém realizou resetlogs algum no servidor antes de ser tentado restaurar essa base de dados e muito menos em produção.

Restauramos o controlfile novamente e listamos as incarnações. Apareceu igual como produção. Após recatalogarmos os arquivos de backup, para registrar inclusive os backups de archives, e apareceu a bendita incarnação fantasma de 01-JUL-2011. Sem explicação resolvemos executando um RESET DATABASE TO INCARNATION X (anterior a de 01-JUL) e o backup foi restaurado sem problemas. Alguma coisinha errada tava informando ao banco de dados esse suposto "resetlogs" mesmo sem ter sido executado em produção.

Até mais.

MERGE na SQLOBJ$AUXDATA com FULL TABLE SCAN e outros problemas - Oracle Database 11g

2011-04-25T16:18:00.000-03:00

A versão Oracle Database 11gR2 realmente é uma versão repleta de recursos e melhorias que nos ajudam muito, mas muito mesmo. Algumas coisas mudaram, como o novo SPM (SQL Plan Management) que passa a gerenciar melhor a questão de planos trabalhando junto com a sua base SMB (SQL Management Base).
Quando um comando SQL é "hard parsed", o CBO (Cost Based Optimizer) produz vários planos de execução e seleciona um com o custo mais baixo. Se uma baseline de plano de SQL estiver presente, o otimizador compara apenas o plano recém-produzido com os planos já existentes na baseline. Se um plano adequado é encontrado ele é marcado como ACEITO (ACCEPTED) e o plano é utilizado. Se a baseline não tiver um plano aceito, o otimizador avalia os planos aceitos na baseline e usa o que tiver menor custo. Se o plano de execução originalmente produzido pelo otimizador teve um custo menor que o presente na baseline, ele é adicionado a baseline como um plano NÃO-ACEITO (NON-ACCEPTED), então não é utilizado até ser verificado para não degradar o desempenho. Se uma mudança no sistema afeta todos os planos existentes aceitos, então eles são considerados como não-reproduzíveis (NON-REPRODUCIBLE), neste caso o otimizador usará o plano com o custo mais baixo.
É uma estratégia chamada de "Conservative Plan Selection Strategy". Como o otimizador preferencialmente usa um plano de execução executado e testado, mesmo que um novo plano pareça ter um desempenho melhor, somente quando o novo plano for provado que executará bem será aceito para uso.
Por padrão o parâmetro "OPTIMIZER_USE_SQL_PLAN_BASELINES" vem habilitado que faz com que o Oracle utilize baselines dos planos SQL armazenados na SMB.

Nem tudo são flores, pois justamente com este recurso tivemos problemas em uma das base de dados que hoje administro. O parâmetro OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES também foi habilitado (FALSE por padrão) para que o oracle verificasse quando deve capturar baselines de plano SQL automaticamente para tentar melhorar o desempenho, pois até então o desempenho estava muito abaixo do esperado, com alto processamento, etc. Queríamos que o banco de dados capturasse as baselines e tentasse reutilizá-las quando possível. A alteração deste parâmetro para TRUE também não adiantou, não houve melhoria significativa, pois a aplicação utilizava poucas variáveis BIND no código.

Pelo gráfico do EM o desempenho estava de fato muito ruim, com altas taxas de espera relacionados a CPU então geramos um AWR para saber quais eram os TOP SQL.
Eis que surge:

MERGE INTO sqlobj$auxdata USING dual ON (:1 IS NULL) WHEN MATCHED THEN UPDATE SET description = :2, creator = nvl(:3, creator), origin = :4, version = :5, created = :6, last_modified = :7, last_verified = nvl(:8, last_verified), parse_cpu_time = null, optimizer_cost = nvl(:9, optimizer_cost), module = nvl(:10, module), action = nvl(:11, action), priority = nvl(:12, priority), optimizer_env = nvl(:13, optimizer_env), bind_data = nvl(:14, bind_data), parsing_schema_name = nvl(:15, parsing_schema_name), executions = nvl(:16, executions), elapsed_time = nvl(:17, elapsed_time), cpu_time = nvl(:18, cpu_time), buffer_gets = nvl(:19, buffer_gets), disk_reads = nvl(:20, disk_reads), direct_writes = nvl(:21, direct_writes), rows_processed = nvl(:22, rows_processed), fetches = nvl(:23, fetches), end_of_fetch_count = nvl(:24, end_of_fetch_count), task_id = nvl(:25, task_id), task_exec_name = nvl(:26, task_exec_name), task_obj_id = nvl(:27, task_obj_id), task_fnd_id = nvl(:28, task_fnd_id), task_rec_id = nvl(:29, task_rec_id), flags = 0, spare1 = null, spare2 = null WHERE signature = :30 AND category = :31 AND obj_type = :32 AND plan_id = :33 WHEN NOT MATCHED THEN INSERT (signature, category, obj_type, plan_id, description, creator, origin, version, created, last_modified, last_verified, parse_cpu_time, optimizer_cost, module, action, priority, optimizer_env, bind_data, parsing_schema_name, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, direct_writes, rows_processed, fetches, end_of_fetch_count, task_id, task_exec_name, task_obj_id, task_fnd_id, task_rec_id, flags, spare1, spare2) VALUES (:34, :35, :36, :37, :38, :39, :40, :41, :42, :43, null, null, :44, :45, :46, :47, :48, :49, :50, :51, :52, :53, :54, :55, :56, :57, :58, :59, :60, :61, :62, :63, :64, 0, null, null)

Executando FULL TABLE SCAN na SQLOBJ$AUXDATA. Justamente o comportamento do SPM com aquele parâmetro OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES habilitado. No suporte Oracle dizia que era necessário aplicar um patch, pois se tratava de um BUG (BUG 11719151). Na descrição do bug, dizia que o BUG poderia ocorrer quando quando o parâmetro OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES estivesse definido para TRUE. Definimos para FALSE, mas mesmo assim nada mudou. Abrimos então um chamado com a Oracle para saber de fato o que fazer já que, teoricamente, o BUG só ocorria quando o OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES estava FALSE como também o desempenho já estava ruim antes mesmo de alterarmos o parâmetro.
Depois de algumas análises recomendaram o PATCH 11719151 de fato para corrigir o problema. O Patch poderia ser aplicado ONLINE e assim fizemos usando um comando similar a esse (para RAC com dois nós):

opatch apply online -connectString SID_Node1:Username_Node1:Password_Node1:Node1_Name,SID_Node2:Username_Node2:Password_Node2:Node2_Name

Nada sai do ar. Apesar do ambiente ser cluster, não foi necessário desligar instância alguma, os binários são alterados online, etc. Há uma ocupação extra de memória, mas tudo ocorreu bem para o primeiro banco de dados.

Após a aplicação do patch no primeiro banco de dados, o com problema, o consumo de desempenho simplesmente mudou completamente. Dá pra perceber no gráfico:

Ficou uma beleza, tudo ocorreu bem na aplicação do patch ATÉ AGORA.O patch altera os binários e acessa o banco de dados para realizar algumas alterações. Provavelmente isso poderia acontecer com as demais bases, pois utilizavam o mesmo recurso (11g) então teríamos que continuar aplicando nas demais bases de dados usando os comandos:

opatch util enableonlinepatch -connectString SID_Node1:Username_Node1:Password_Node1:Node1_Name,SID_Node2:Username_Node2:Password_Node2:Node2_Name -id 11719151 # Banco 2

opatch util enableonlinepatch -connectString SID_Node1:Username_Node1:Password_Node1:Node1_Name,SID_Node2:Username_Node2:Password_Node2:Node2_Name -id 11719151 # Banco 3

opatch util enableonlinepatch -connectString SID_Node1:Username_Node1:Password_Node1:Node1_Name,SID_Node2:Username_Node2:Password_Node2:Node2_Name -id 11719151 # Banco 4 * PAU

Quando chegamos no quarto banco de dados (de 8) outro problema aconteceu. O banco de dados passou a consumir 100% de CPU constantemente, só resolvia se abortássemos o banco, pois nada era possível fazer na máquina por conta do alto processamento. O ABORT foi sofrido, mas conseguimos. Toda vez que a instância subia, 100% CPU. Informamos ao suporte Oracle, mas eles pediram diagnósticos de AWR desta base, etc, mas era impossível, pois CPU 100% direto. Ainda bem que esta base não estava em produção, decidimos então recriá-la e "resolveu" o problema.
Para as demais bases, deu tudo certo na aplicação do patch.

Na primeira base, que estava com problemas de desempenho, tudo estava normal, porém foi percebido um comportamento estranho. Toda e qualquer consulta que já havia sido executada, ou seja, que já existia na SMB, estava dando ORA-01008. Muitas consultas na aplicação já haviam sido realizadas, então o sistema estava praticamente inoperante.
Em contato com o suporte Oracle, informamos os parâmetros em questão, inclusive o OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES já estava FALSE, ele pediu que o parâmetro OPTIMIZER_USE_SQL_PLAN_BASELINES fosse desabilitado também desabilitando assim o recurso de SMB. Neste caso o erro ORA-01008 desapareceu, portanto isso foi uma ação de contorno e então solicitamos que este problema fosse analisado pelo suporte Oracle.

Eles abriram então o BUG 11887892 e estão desenvolvendo uma solução.

Por enquanto está tudo bem e estável, porém sem este recurso do 11g por enquanto. :(

Agradecimentos ao Suporte Oracle que se demonstrou rápido e objetivo em todas as nossas solicitações.

UPDATE 25/04/2011:
O suporte Oracle nos pediu que o patch aplicado ONLINE fosse removido e reaplicado OFFLINE, pois, segundo eles, houve uma falha no desenvolvimento do patch (faltou a inclusão de um módulo) que ocasiona o problema apenas quando é aplicado ONLINE.
Tivemos então que remover o patch ONLINE, baixar tudo que estava no HOME e reaplicar no modo OFFLINE.
Depois disso tudo ficou mais bonito e os problemas não aconteceram mais. Pude voltar o parâmetro OPTIMIZER_USE_SQL_PLAN_BASELINES para TRUE.
Após o acontecido, a Oracle modificou a documentação do patch removendo a opção de aplicação ONLINE do patch.
Até a próxima!

ORA-600 kskopen1 - Oracle 10g 10.2.0.4 no AIX 6.1 64bits

2011-03-30T14:49:00.000-03:00

Vi este erro hoje quando foi tentado iniciar uma base de dados (fase OPEN):

ORA-00600: internal error code, arguments: [kskopen1], [], [], [], [], [], [], []

Segundo o trace gerado poderia ser algo relacionado ao Resource Manager:

...
Dump of memory from 0x0000000104AAF424 to 0x0000000104AAF564
104AAF420 25642C20 43505573 203D2025 [%d, CPUs = %]
104AAF430 640A0049 4F70656E 696E6720 77697468 [d..IOpening with]
104AAF440 20696E74 65726E61 6C205265 736F7572 [ internal Resour]
104AAF450 6365204D 616E6167 65722070 6C616E0A [ce Manager plan.]
104AAF460 0049424D 6B736B6F 70656E31 0049424D [.IBMkskopen1.IBM]
104AAF470 6B736B74 68657869 7428293A 20206465 [kskthexit(): de]
104AAF480 6C657469 6E672074 68726561 64203078 [leting thread 0x]
104AAF490 25782C20 76632030 7825782C 20736573 [%x, vc 0x%x, ses]
104AAF4A0 73203078 25700A00 6B736B74 6872736F [s 0x%p..kskthrso]
...

Algum recurso foi habilitado e não era suportado gerando o erro.

Há uma forma de desabilitar o uso do Resource Manager o que possibilita abrir o banco de dados:

alter system set "_resource_manager_always_on"=FALSE scope=spfile;

Desta forma o banco de dados subiu, daí é só remover o recurso não suportado e pronto, reabilitar o Resource Manager. (Metalink ID 854192.1)

Até mais.

ENQ: TX - ROW LOCK CONTENTION no Oracle RAC

2011-03-10T10:02:00.002-03:00

Feliz ano novo (para os que consideram o início do ano após o carnaval),
Desculpem minha ausência, mas é que o tempo é curto e muitas coisas estão acontecendo ao mesmo tempo.
Preciso informar a vocês um evento em particular que ocorreu comigo em um dos ambientes em que trabalho. O ambiente é em RAC 11g e um sistema foi migrado.
Quando este foi migrado, foi percebido um alto índice de ocorrência do evento "ENQ: TX - ROW LOCK CONTENTION" que não acontecia antes. Isso parece claro, porém não foi tão fácil assim resolver. Este evento acontecia em INSERTS, UPDATES e DELETES causando um "pequeno" estresse ao utilizar a aplicação. Anteriormente o sistema funcionava normalmente em SINGLE INSTANCE, mas os problemas vieram após "virar" pro RAC.
Sabemos que falta de índices em chaves estrangeiras causam problemas de ROW LOCK, mas não era o caso, pois não acontecia anteriormente. Quando ocorre este evento em INSERTS, geralmente, é pelo fato de uma sessão estar inserindo um valor X e outra sessão estar inserindo o mesmo valor X causando a esta a espera ROW LOCK. Se a primeira sessão confirmasse (COMMIT) o erro de CONSTRAINT violada apareceria para a segunda, mas se a primeira não confirmasse (ROLLBACK) a segunda iria inserir com sucesso.
Isso também foi analisado, mas não era o caso. Algumas vezes até DEADLOCK ocorria com apenas traces e informações geradas de uma sessão.
Foi percebido que o ambiente usava MTS (Microsoft Transaction Server) que tratava as transações como distribuídas no Oracle. No caso do RAC o que acontecia era que um usuário quando entrava no sistema WEB abria várias sessões, algumas em uma instância e outras na outra instância. Notei que quando isso acontecia causava tais bloqueios.
Para resolver da melhor forma possível, criei serviços voltados para este tipo de configuração e adaptei ao ambiente do sistema com MTS.

Primeiramente o comando abaixo criou o serviço "WEB_XA1" com FAILOVER, Load Balance Goal SHORT runtime SERVICE_TIME, DTP TRUE (-x, processamento de transações distribuídas), notificações AQ TRUE (Fast Start App Failover), instância preferencial WEB1, instância disponível WEB2. Significa que quem conectar no WEB_XA1, todas suas sessões cairão somente na instância WEB1 e caso esta não esteja disponível as sessões serão transferidas/abertas na WEB2.

$ srvctl add service -d WEB -s WEB_XA1 -P BASIC -e SELECT -m BASIC -j SHORT -B SERVICE_TIME -x TRUE -q TRUE -r WEB1 -a WEB2

Depois criei outro serviço, mas com a instância preferencial e disponível trocados:

$ srvctl add service -d WEB -s WEB_XA2 -P BASIC -e SELECT -m BASIC -j SHORT -B SERVICE_TIME -x TRUE -q TRUE -r WEB2 -a WEB1

Fiz isso porque no ambiente possuem 4 servidores de aplicação, então em 2 servidores foi configurado para acessar o serviço WEB_XA1 e o outro WEB_XA2 para balancear a carga. Após isso configurado o evento de espera "ENQ: TX - ROW LOCK CONTENTION" simplesmente desapareceu.

Fica aí a dica pra quem enfrentar isto após migrações para ambiente RAC e transações distribuídas.

Até a próxima.

O parâmetro de inicialização AUDIT_SYSLOG_LEVEL - Oracle Database 10g

2011-01-23T22:50:00.002-03:00

O parâmetro de inicialização AUDIT_SYSLOG_LEVEL é parcialmente documentado. Várias imprecisões na documentação sugerem que o parâmetro é menos útil do que realmente é. Ações de banco de dados pelo SYS e/ou administradores de banco de dados ou os operadores podem ser auditados para registrar nos arquivos de log (pelo deamon do syslog) do sistema operacional UNIX pertencentes ao root. Isso impede que usuários de banco de dados privilegiados removam os registros de auditoria que contêm um registo das suas atividades. A configuração padrão é a auditoria CONNECT, STARTUP e SHUTDOWN com privilégios SYSDBA ou SYSOPER com escrita nos arquivos pertencentes ao proprietário do software Oracle, embora não havendo auditoria do SQL, PL/SQL e outras ações com esses privilégios ou outros privilégios, tais como a role DBA. Em outras palavras, com exceção das operações descritas acima, a auditoria padrão (veja o parâmetro AUDIT_TRAIL), assim como auditoria fina (veja o pacote DBMS_FGA), estão desligadas por padrão. Como conseqüência, não haverá nenhum vestígio de muitas atividades realizadas por usuários privilegiados. Auditoria de arquivos do sistema operacional pertencente ao proprietário do software Oracle (AUDIT_TRAIL = OS) ou para a tabela de banco de dados SYS.AUD$ (AUDIT_TRAIL = DB) pode ser contornada, uma vez que os DBAs têm normalmente acesso à conta do proprietário do software Oracle UNIX, bem como a SYS.AUD$, o que lhes permite facilmente remover registros de auditoria gerados por suas ações. Auditoria pelo syslog UNIX também é útil para a detecção de invasões por hackers.

- Syslog

Um novo recurso do Oracle 10g é a capacidade de gravar trilhas de auditoria usando o syslog em sistemas UNIX. Este mecanismo consiste em um processo daemon chamado syslogd (veja "man syslogd") que aceita mensagens de registro dos pedidos através da função syslog na biblioteca C (veja "man syslog"). O arquivo de configuração para o syslogd é normalmente /etc/syslog.conf e as mensagens de log vão para arquivos em /var/log e /var/adm dependendo da variante do UNIX. O nome do arquivo de log é determinada por uma seqüência que consiste em um nome de unidade e uma prioridade ou nível. A maioria destes pode ser utilizado na definição do AUDIT_SYSLOG_LEVEL. Cada entrada no /etc/syslog.conf atribui um nome de arquivo de log para uma determinada combinação de facilidade e prioridade. Ao colocar a entrada "user.notice /var/log/oracle_dbms" no arquivo syslog.conf e fazendo com que o syslogd releia o arquivo de configuração enviando um sinal de hang-up com o comando kill, qualquer entrada posterior no log de uma instância ORACLE com o parâmetro configurado AUDIT_SYSLOG_LEVEL = user.notice será gravado no arquivo /var/log/oracle_dbms.

No Windows, quando o parâmetro AUDIT_SYS_OPERATIONS é definido para TRUE, operações com SYSDBA ou SYSOPER são escritas também para o Event Log.

O manual "Oracle Database Reference 10g Release 2" fala sobre o AUDIT_SYSLOG_LEVEL assim:

AUDIT_SYSLOG_LEVEL permite que os logs de auditoria SO sejam escritos para o sistema via utilitário syslog, isso se o parâmetro AUDIT_TRAIL for definido para "OS". O valor da facilidade pode ser qualquer dos seguintes: USER, LOCAL0, LOCAL7, SYSLOG, DAEMON, KERN, MAIL, AUTH, LPR, NEWS, UUCP ou CRON. O Valor do nível pode ser quaisquer dos seguintes: NOTICE, INFO, DEBUG, WARNING, ERR, CRIT, ALERT, EMERG.

Testes do novo recurso em ambientes Solaris 10 e Red Hat Linux mostram que a documentação é imprecisa em 3 pontos:

1. AUDIT_SYSLOG_LEVEL é independente do AUDIT_TRAIL. Quando o AUDIT_SYSLOG_LEVEL é definido e o AUDIT_TRAIL tem seu valor padrão para NONE, ações CONNECT, STARTUP e SHUTDOWN são logados via syslog.
2. Definindo os parâmetros AUDIT_SYSLOG_LEVEL e AUDIT_SYS_OPERATIONS=TRUE causa qualquer ação como comandos SQL e PL/SQL executados com privilégios SYSDBA ou SYSOPER serem logados via syslog, mesmo se AUDIT_TRAIL=NONE.

3. Somente certas combinações de facilidades e níveis são aceitáveis. Combinações não aceitáveis causam o erro "ORA-32028: Syslog facility or level not recognized" e assim impedindo que a instância DBMS se inicie.

Se a documentação fosse precisa, não seria possível auditar ações executadas com privilégios SYSDBA ou SYSOPER ao log do systema, enquanto ações de auditoria por outros usuarios para a tabela SYS.AUD$ do dicionário de dados. Entretanto, esta limitação não existe.

- Usando AUDIT_SYSLOG_LEVEL

Como citado anteriormente, a string definida ao parâmetro AUDIT_SYSLOG_LEVEL deve consistir de um nome de uma facilidade e uma prioridade ou nível. Curiosamente, quando executa-se um SHOW PARAMETER ou um SELECT FROM V$PARAMETER, meramente a facilidade é visível, o ponto e o nível são suprimidos (teste na versão 10.2.0.3). Por exemplo, com a entrada *.audit_syslog_level='USER.NOTICE' no SPFILE usado para iniciar a instância, SHOW PARAMETER mostra:

SQL> SHOW PARAMETER audit_syslog_level
NAME TYPE VALUE
------------------------------------ ----------- -----
audit_syslog_level string USER

SQL> SELECT value FROM v$parameter WHERE name='audit_syslog_level';
VALUE
-----
USER

Ainda assim, quando executa-se CONNECT / AS SYSDBA, a facilidade e o nível logado em /var/adm/messages (no Solaris) é "user.notice":

Feb 21 11:45:52 dbserver Oracle Audit[27742]: [ID 441842 user.notice]
ACTION : 'CONNECT'
Feb 21 11:45:52 dbserver DATABASE USER: '/'
Feb 21 11:45:52 dbserver PRIVILEGE : SYSDBA
Feb 21 11:45:52 dbserver CLIENT USER: oracle
Feb 21 11:45:52 dbserver CLIENT TERMINAL: pts/3
Feb 21 11:45:52 dbserver STATUS: 0

Se um SPFILE for utilizado, a definição completa do parâmetro é visualizada consultando a V$SPPARAMETER:

SQL> SELECT value FROM v$spparameter WHERE name='audit_syslog_level';
VALUE
-----------
user.notice

- Auditando usuários não-privilegiados

Claro, você pode também direcionar os registros de auditoria relativos aos usuários não-privilegiados no log de sistema, definindo AUDIT_TRAIL = OS além de AUDIT_SYSLOG_LEVEL. Os usuários não-privilegiados não podem apagar as trilhas de auditoria. A busca por autores com consultas em views de auditoria, como DBA_AUDIT_STATEMENT ou DBA_AUDIT_OBJECT, é mais fácil do que procurar no log de sistema. Por estas razões, mantendo as trilhas de auditoria de usuários não-privilegiados dentro do banco de dados com AUDIT_TRAIL = DB é o preferido. Com o último ajuste, trilhas de auditoria são gravados na tabela SYS.AUD$ e pode ser consultado através das views de dicionário acima mencionadas. Definindo AUDIT_TRAIL=NONE, desliga auditoria de ações por usuários não-privilegiados.
Abaixo um exemplo depois de habilitar auditoria para conexões no banco de dados estabilizadas por usuários não-privilegiados:

SQL> AUDIT CONNECT BY appuser /* audit_trail=os set */;

Feb 21 11:41:14 dbserver Oracle Audit[27684]: [ID 930208 user.notice]
SESSIONID: "15" ENTRYID: "1" STATEMENT: "1" USERID: "APPUSER"
USERHOST: "dbserver" TERMINAL: "pts/3" ACTION: "100" RETURNCODE: "0"
COMMENT$TEXT: "Authenticated by: DATABASE" OS$USERID: "oracle"
PRIV$USED: 5

Outra entrada é adicionada ao /var/adm/messages (no Solaris) quando uma sessão do banco de dados encerrou:

Feb 21 11:44:41 dbserver Oracle Audit[27684]: [ID 162490 user.notice]
SESSIONID: "15" ENTRYID: "1" ACTION: "101" RETURNCODE: "0"
LOGOFF$PREAD: "1" LOGOFF$LREAD: "17" LOGOFF$LWRITE: "0" LOGOFF$DEAD:
"0" SESSIONCPU: "2"

Perceba que dados adicionais são fornecidos nas ações LOGON (100) e LOGOFF (101) conforme as colunas na visão DBA_AUDIT_SESSION:

SQL> SELECT action, name FROM audit_actions WHERE action IN (100,101)
ACTION NAME
------ ------
100 LOGON
101 LOGOFF

Quando o parâmetro é definido para AUDIT_SYSLOG_LEVEL=AUTH.INFO, AUDIT_SYS_OPERATIONS=FALSE e AUDIT_TRAIL=NONE, ações CONNECT,STARTUP e SHUTDOWN são logados via syslog. Com estas configurações um shutdown de uma instância escreve entradas similares as seguintes para o /var/adm/messages:

Feb 21 14:40:01 dbserver Oracle Audit[29036]:[ID 63719 auth.info] ACTION:'SHUTDOWN'
Feb 21 14:40:01 dbserver DATABASE USER: '/'
Feb 21 14:40:01 dbserver PRIVILEGE : SYSDBA
Feb 21 14:40:01 dbserver CLIENT USER: oracle
Feb 21 14:40:01 dbserver CLIENT TERMINAL: pts/3
Feb 21 14:40:01 dbserver STATUS: 0

Quando o parâmetro é definido para AUDIT_SYSLOG_LEVEL=AUTH.INFO, AUDIT_SYS_OPERATIONS=TRUE, e AUDIT_TRAIL=NONE, comandos SQL and PL/SQL executados com privilégios SYSDBA ou SYSOPER são também logados via syslog. Excluindo um usuário depois de se conectar com / AS SYSDBA resulta numa entrada similar no syslog:

Feb 21 14:46:53 dbserver Oracle Audit[29170]: [ID 853627 auth.info]
ACTION : 'drop user appuser'
Feb 21 14:46:53 dbserver DATABASE USER: '/'
Feb 21 14:46:53 dbserver PRIVILEGE : SYSDBA
Feb 21 14:46:53 dbserver CLIENT USER: oracle
Feb 21 14:46:53 dbserver CLIENT TERMINAL: pts/3
Feb 21 14:46:53 dbserver STATUS: 0

Até mais!!

Trecho retirado do livro (traduzido e com adaptações):
LONG, Norbert. Secrets of the Oracle Database. Apress, New York, p.03-08, 2009

ALTER USER IDENTIFIED BY VALUES

2011-01-04T16:36:00.006-03:00

O comando CREATE/ALTER USER <usuario> IDENTIFIED BY VALUES '<password hash>' não é documentado. É utilizado internamente para armazenar as senhas que foram anteriormente salvas com o utilitário export na tabela do dicionário de dados SYS.USER$. Em algumas situações onde o DBA precisa se conectar com um certo usuário, mas não sabe a senha dele ou a possibilidade de salvar estas senhas ganha-se tempo ao invés de sair perguntando por elas.

Às vezes você como DBA precisa realizar alguma manutenção que é preciso ser com aquele schema específico, mas a empresa não quer fornecer a senha do usuário/schema então este recurso pode ser utilizado da seguinte maneira:

Você se conecta com um usuário administrativo (DBA ou SYSDBA) e executa a consulta e anota o resultado:

SQL> spool senha.log

SQL> SELECT password FROM dba_users WHERE username='SCOTT';

PASSWORD

------------------------------

F894844C34402B67

Agora você altera temporariamente a senha do usuário para qualquer uma e poderá realizar a manutenção desejada:

SQL> ALTER USER scott IDENTIFIED BY manutencao;

Após a manutenção você apenas retorna para o valor desejado e a senha voltará a ser a mesma de antes:

SQL> ALTER USER scott IDENTIFIED BY VALUES 'F894844C34402B67';

Pronto. É uma ação simples, mas muitos não sabem desta boa idéia na hora de executar alguma manutenção com esses requisitos. Em migrações também pode ser útil.

Existe também uma outra utilidade para esse comando, é gerar "senhas impossíveis". A Oracle recomenda que você bloqueie contas internas do banco de dados como CTXSYS, XDB, OLAPSYS, etc., mas dessa forma você permite que outros descubram o que está instalado como funcionalidade no banco de dados e permitir que explorem vulnerabilidades. Se a conta estiver apenas bloqueada o "invasor" verá “ORA-28000: the account is locked” e assim descobrindo que essa funcionalidade existe. Nesse contexto podem ser geradas senhas impossíveis que utilizam o mesmo comando "... IDENTIFIED BY VALUES" impossibilitando que o invasor perceba que a funcionalidade existe. Se ele tentasse se conectar outro erro seria gerado: “ORA-01017: invalid username/password; logon denied”.

Para gerar um password impossível é fácil, basta você colocar um texto comum após o VALUES. Desta forma o Oracle nunca poderia decodificar o seu "hash" em uma senha:

SQL> ALTER USER olapsys IDENTIFIED BY VALUES 'IMPOSSIBLE' ACCOUNT UNLOCK;

Quando você verificar a senha na visão DBA_TABLES você irá ver:

SQL> SELECT username,password FROM DBA_USERS ORDER BY username;

USERNAME PASSWORD

-------- --------

MGMT_VIEW 108DD8F2F748347B

OLAPSYS IMPOSSIBLE

ORACLE_OCM 6D17CF1EB1611F94

Até a próxima!

Feliz 2011!

2011-01-03T09:37:00.000-03:00

Feliz 2011 pessoal! Que este ano seja ótimo para nós, com certificações, saúde, reconhecimentos e outras conquistas. Em breve estarei postando mais alguns artigos interessantes por aí. Fiquem ligados!

Habilitar auditoria em falha de logins - Oracle Database 10g e 11g

2010-12-29T13:05:00.001-03:00

Algumas vezes queremos descobrir quem é o infeliz que está bloqueando uma conta no banco de dados. Para tornar as coisas mais simples você pode habilitar a auditoria no banco de dados e ativar a auditoria neste evento específico. Isso ajuda a descobrir qual é a máquina, qual o erro retornado, quando foi, dentre outras informações.

No 10g é necessário realizar os seguintes procedimentos, mas no 11g a auditoria já é habilitada por padrão:

1. Defina o parâmetro audit_trail para "DB" no spfile/pfile e reinicie a instância:

SQL> alter system set audit_trail=DB scope=spfile ;

System altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup ;

ORACLE instance started.

2. Agora habilite o audit no evento desejado de falha de login:

SQL> audit session whenever not successful ;

Audit succedded.

3. Para testar efetue uma conexão com o banco de dados informando senha errada e execute a consulta:

SQL> select os_username,username,userhost,to_char(timestamp,'mm/dd/yyyy hh24:mi:ss') timestamp,returncode

from dba_audit_session

where action_name = 'LOGON'

and returncode > 0

order by timestamp;

Até mais!

BUG mudando "db_cache_size" em instância ASM - Oracle 11gR2 RAC

2010-12-28T12:52:00.000-03:00

Galera, mais uma que eu preciso citar. Estava eu achando que ia ser simples o procedimento de alterar alguns parâmetros na instância ASM do Oracle, mas não foi bem assim.

A Oracle recomenda fortemente que você utilize o AMM (Automatic Memory Management) pra tudo aceitando os valores padrão , inclusive instâncias ASM, mas isso, inclusive citado em alguns lugares, pode não ser muito legal, pois pro ASM é melhor memória estática. Esse detalhe vale pra versão 11.1, mas pra versão 11.2 ainda não sei.

Bom, precisei primeiramente alterar o parâmetro "processes" das instâncias ASM, pois estão penduradas 8 bases e o limite de processos estourou rapidinho. Ao tentar alterar este valor de acordo com a documentação "ASM Best Practices" e reiniciar a instância ASM o erro era mostrado:

ORA-04031: unable to allocate "string" bytes of shared memory ("string","string","string","string")

Foi aí que comecei a pensar em aumentar os parâmetros de memória e desabilitar o AMM (MEMORY_TARGET=0 e SGA_TARGET=0).

Redefinindo os parâmetros conforme documentação deu tudo certo. Aumentei o limite de processos do ASM, mas um parâmetro em especial dava erro ao ser alterado como os demais (scope=spfile): db_cache_size. O erro:

ORA-07445[kcbw_check_resize_errors] [SIGSEGV]

E minha conexão caía na instância ASM. Segundo o Oracle MySupport (a.k.a. Metalink) isso é um bug (ID 553176.1) e para contorná-lo é necessário criar um pfile, alterar o parâmetro e criar um spfile desde pfile alterado.

Depois disso tudo subiu tranquilo.

Até mais e Busquem Conhecimento!

ASM New Features 11g

2010-12-27T21:05:00.002-03:00

Poisé galera, lá vamos nós! Hoje vou aproveitar que estou passando por experiências com o ASM num ambiente RAC Extended 11gR2 e comentar algumas mais importantes New Features do ASM. Alguns buxos também apareceram e tivemos que resolver também. O ASM (Automatic Storage Management) é um grande recurso da Oracle que foi desenvolvido com o intuito de facilitar a administração de volumes físicos para serem utilizados pelo banco de dados. Possui vários recursos interessantes como espelhamento de discos, striping, etc. e alguns novos na versão 11g.

Quando você cria um diskgroup no ASM da primeira vez na versão 11g ele cria como padrão compatível com 10g (10.1), ou seja, os atributos COMPATIBLE.ASM e COMPATIBLE.RDBMS estão 10.1 e desta forma tais novos recursos não seriam habilitados. Creio que o Oracle faz isso para permitir que você utilize numa possível migração 10g>11g recursos como Transportable Tablespace ou afins. O atributo COMPATIBLE.ASM permite que novos recursos do ASM sejam utilizados e o COMPATIBLE.RDBMS é comparada com o valor do parâmetro "compatible" da instância do banco de dados que está ligado àquele diskgroup. Há uma dependência entre os dois atributos onde o COMPATIBLE.RDBMS não pode estar numa versão superior ao do COMPATIBLE.ASM. Se você não for utilizar recursos como Transportable Tablespaces ou afins para realizar uma migração de banco de dados ou vai utilizar aquele mesmo diskgroup pra armazenar bancos de dados 10g e 11g, é recomendável que estes atributos sejam modificados para 11.1 ou 11.2 dependendo da versão. Depois dos atributos modificados você poderá usufruir dos novos recursos.

Para alterar os atributos:

SQL> alter diskgroup DATA set attribute 'compatible.asm'='11.2';

SQL> alter diskgroup DATA set attribute 'compatible.rdbms'='11.2';

OBS: Depois de atualizado o valor não pode ser retornado para um versão anterior.

Para mudar o comportamento padrão na criação dos Diskgroups:

SQL> alter system set "_asm_compatibility"='11.2' scope=spfile sid='*';

SQL> alter system set "_rdbms_compatibility"='11.2' scope=spfile sid='*';

Restart das instâncias ASM e pronto.

Privilégio novo: SYSASM:

Esse novo privilégio faz com que algumas rotinas administrativas do ASM fiquem mais restritas. Para executar rotinas como gerenciamento de diskgroups, etc será necessário entrar com esse privilégio:

$ sqlplus / as sysasm

Fast Mirror Resync:

No 10g quando algum disco do diskgroup era perdido por algum motivo, ele já era excluído de imediato. Após repor o disco era necessário adicioná-lo novamente e a operação de rebalanceamento se iniciava. No 11g o disco é posto OFFLINE e o ASM mantém as informações dos extents para enviar àquele disco quando o problema for resolvido. Quando o disco ficar OK basta executar o comando "ALTER DISKGROUP dg_name ONLINE ALL;" para repor todos os discos que estavam offline em online novamente. O processo de rebalanceamento será muito mais rápido. Por padrão você tem 3.6Horas para repor este disco antes que ele seja eliminado, mas isso é outro atributo que pode ser alterado.

Prefered Read;

Um recurso bem interessante. Parâmetros são definidos em cada instância ASM, útil quando o ambiente é RAC Extended (Um nó em um site e outro nó em outro site), que irão priorizar a leitura do FAILGROUP daquele DISKGROUP, ou seja, o storage do site 1 terá preferência de leitura no site 1 e no site 2 leitura preferencial do storage 2. Isso otimiza bastante o desempenho em leitura evitando "round-trips", ou seja, voltas e voltas para entregar um dado. Imagine uma sessão de um usuário que está no site 1 que é aberta no site 2, executa uma consulta que traz dados do storage do site 1 (o Oracle não sabe diferenciar onde é mais perto), o dado sai do site 1 para o site 2 e volta pro site 1 entregando o resultado para a sessão. Pense numa viagem!
Para habilitar o recurso:

SQL> alter system set asm_preferred_read_failure_groups='DATA.SITE1','FLASH.SITE1' sid='+ASM1';

SQL> alter system set asm_preferred_read_failure_groups='DATA.SITE2','FLASH.SITE2' sid='+ASM2';

Suporte para large allocation units (AUs);

O ASM agora suporta unidades de alocações de até 64MB. Unidades maiores podem ser vantajoso quando o ambiente é DataWarehouse onde há aplicações que utilizam leitura sequencial.

Rolling upgrade e patching*;

Na versão 11g do ASM é possível realizar atualizações e correções com o ambiente no ar. Este recurso de chama Rolling Migration e requer que o Clusterware esteja na versão mais nova no ambiente.
Antes de começar é preciso colocar as instâncias no modo Rolling Migration:

SQL> ALTER SYSTEM START ROLLING MIGRATION TO 11.2.0.2;

Este comando não executa exatamente a migração, ele prepara as instâncias ASM no cluster para a migração. Este estado não persiste se as instâncias falharem durante o processo.

Para verificar o estado do ambiente ASM (Normal ou Rolling Migration) execute a consulta:

SQL> SELECT SYS_CONTEXT('sys_cluster_properties', 'cluster_state') FROM

DUAL;

Quando estiver em "Rolling Migration" cada instância ASM pode ser desligada e atualizada. Quando o software for atualizado em uma instância, ela pode ser reiniciada e os diskgroups montados. Desta forma uma instância ficará numa versão mais nova que as demais no ambiente, isso só é permitido no estado "Rolling Migration".

Resumindo, os passos para execução do processo de Rolling Migration são:

1. Tenha certeza que o software para a nova versão está instalado em todos os nós.

2. Tenha certeza que o Clusterware está na versão mais alta.

3. Tenha certeza que as instâncias ASM estão na mesma versão Oracle.

4. Tenha certeza de que não há operações de rebalanceamento em execução (v$asm_operation). Se tiver, aguarde a conclusão.

5. Coloque o cluster ASM em "Rolling Migration".

6. Desligue uma instância ASM.

7. Inicie uma instância ASM do ORACLE_HOME com o novo software.

8. Repita os passos 4 e 5 até que todas as instâncias ASM sejam atualizadas.

9. Quanto o software estiver atualizado em todas as instâncias, desabilite o modo Rolling Migration:

SQL> ALTER SYSTEM STOP ROLLING MIGRATION;

* Trecho retirado do livro (traduzido e com adaptações):

LONG, R.; VALLATH, M.; VENGURLEKAR, N. Oracle Automatic Storage Management. Oracle Press, New York, p.60-61, 2007

Até mais!

Erro ORA-01455 ao realizar backup no RMAN - Oracle 11gR2

2010-12-27T15:12:00.000-03:00

Pessoal, me deparei com esse probleminha legal hoje. Quando tentava-se realizar backup do banco de dados, ou seja, um simples BACKUP DATABASE pelo RMAN o erro ORA-01455 era reportado:

O Problema:

RMAN> backup database;

Starting backup at 27-DEC-10

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=137 device type=DISK

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of backup command at 12/27/2010 13:11:25

ORA-01455: converting column overflows integer datatype

Uma pesquisa no Oracle MySupport (a.k.a. Metalink) informava que o problema poderia ser relacionado a um campo numérico do catálogo do RMAN muito pequeno (2) e que deveria ser aumentado (5) e executar um UPGRADE CATALOG, mas no meu caso não havia catálogo... E agora?

DEBUG nele:

$ rman target / debug log=saida.log

RMAN> BACKUP DATABASE;

RMAN> EXIT;

Olhando o log do debug com calma (é preciso) encontrei o seguinte (meio a uma carrada de informações):

DBGSQL: TARGET> select trunc(sysdate) - trunc(:bscompletion) into :days from sys.dual

DBGSQL: sqlcode = 1455

DBGSQL: B :bscompletion = "25-JAN-11"

DBGSQL: error: ORA-01455: converting column overflows integer datatype (krmkosqlerr)

Com isso percebi com o LIST BACKUP que alguns backups foram feitos com a data errada do servidor, ou seja, o servidor estava em 2011, já tinha passado o reveillon com um backup SUPER atualizado ;). O erro não tinha muito a ver com o que estava acontecendo pelo visto, mas o velho DEBUG é bastante útil!
Bom, acabou que o RMAN não fazia mais backups.

A Solução:

O backup foi movido para outro local apenas para arquivar e executado um CROSSCHECK BACKUP para que essa "sujeira" fosse retirada. Deu certo, o backup rolou tranquilamente, porém no presente.

É isso aí, até a próxima!

O começo...

2010-12-26T18:13:00.001-03:00

Meus caros visitantes, amigos e colegas. Estou começando no mundo dos blogueiros e prometo ser fiel ao que prometi neste blog. Irei compartilhar meus conhecimentos, experiências, CAUSOS, etc. Fiquem à vontade para visitar e comentar. Assim que eu preparar bons conteúdos irei postá-los aqui. Fiquem sincronizados, sigam o blog e adicionem o FEED para ficar por dentro! Até mais!