Conteúdo voltado para banco de dados Oracle e SQL Server

Oracle Archive

Um recurso pouco utilizado, porém muito útil no Oracle Database é setar informações da aplicação nas views de sistema V$SESSION / GV$SESSION. É comum alguma consulta estar impactando o ambiente e as informações contidas nas views de sistema e as informações não serem tão precisas a ponto de ir direto no problema. É possível, por exemplo, colocar o nome da aplicação ou da etapa do processo em que está sendo executado, e com isso você saberia se o processo que está impactando é o início ou o fim do processo.

Mãos á obra ..

Abra uma query no Oracle e execute a query abaixo. Nesse caso, eu estou informando que minha aplicação é o controle de estoque e que o procedimento que está sendo executado é a baixa de produtos devolvidos:


exec dbms_application_info.set_module(module_name => 'Controle de Estoque',action_name => 'Baixa dos produtos devolvidos');

PL/SQL procedure successfully completed.

Após execução do pacote acima, a query na v$session será populada com os dados:


select osuser, module, action from v$session where action is not null

noraldino <strong>Controle de Estoque Baixa dos produtos devolvidos</strong>


As informações contidas no pacote podem ser customizadas de acordo com sua necessidade. É recomendado que esse procedimento seja repassado ao time de desenvolvimento para que seja incluído esse pacote antes das execuções de procedimentos, pois na hora do troubleshooting essas informações podem ser muito úteis e poupar muito tempo dos envolvidos.

Enjoy !

Esse erro comum ocorre quando você tenta executar uma operação em uma tabela que foi alterada e a transação ainda está pendente. No nosso caso, estávamos tentando truncar uma tabela de B.I. e o erro era retornado instantaneamente: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired !

O select abaixo ajudou a identificar a causa raiz, ele lista quais sessões estão aguardando commit ou roolback. No meu caso foi uma transação aberta há mais de 30 dias e, alinhando com a área, matamos a sessão:

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,
S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT 
FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, 
V$PROCESS P, V$SQL SQ 
WHERE L.OBJECT_ID = O.OBJECT_ID 
AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR 
AND S.SQL_ADDRESS = SQ.ADDRESS;

Dica: Em ambiente Oracle RAC troque o V$ por GV$ !

Faça bom uso do script.

Criei um banco do zero, movi os dados da instância anterior para o mesmo e fui conectar no RMAN/CATALOGO para ver se estava tudo ok. No exato momento recebi o segunte erro:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of crosscheck command at 07/18/2018 15:15:45
RMAN-12010: automatic channel allocation initialization failed
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20001: target database not found in recovery catalog

Esse erro acontece porque o banco de dados criado não foi registrado no catálogo. Um comando extremamente simples resolve o problema, basta registrar o database no catálogo : )

Com o usuário Oracle e as variáveis do banco setadas, execute o seguinte comando para conectar no banco de dados:

rman target / catalog rman/mypassword@repositorio;

A seguinte mensagem de sucesso será apresentada na tela:

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Jul 18 15:15:39 2018

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: GALOMINEIRO (DBID=131313131313)
connected to recovery catalog database

Após conectar, basta digitar o comando register database para que o banco de dados seja registrado no catálogo:

RMAN> register database;

E acompanhe a execução do procedimento:

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

Pronto! A partir desse momento o banco de dados estará devidamente registrado no catálogo. Basta conectar no catálogo e rodar o select abaixo para garantir que o banco está de fato no catalogo:

select * from rc_database where name = 'VS24'
65432165	50023546	131313131313	GALOMINEIRO	654984613565	18/07/18

Há poucos dias atrás precisei instalar os agentes do Oracle Cloud Control 13c em 3 servidores de produção, porém ele dava o erro em que a porta xxx estava sendo usada. Verifiquei que já haviam outros agentes rodando nas máquinas e eram referentes á versões anteriores, para que os novos agentes fossem instalados, esse antigos deveriam ser removidos.

Mãos á obra:

Localize o agent_home da instalação anterior e exporta a variável AGENT_HOME. No meu caso ela estava em /u01/app/oracle/agent12c/core/12.1.0.4.0:


export AGENT_HOME=/u01/app/oracle/agent12c/core/12.1.0.4.0

Em seguida execute o comando abaixo para remoção do agente:


$perl $AGENT_HOME/sysman/install/AgentDeinstall.pl -agentHome $AGENT_HOME

Após executar o código acima, a mensagem abaixo informará na tela o progresso da desinstalação:

** Esse processo só retira o agente do servidor, para retirar do Oracle Cloud Control deverá ser feita uma operação á parte.
Agent Oracle Home: /u01/app/oracle/agent12c/core/12.1.0.4.0

agentHome = /u01/app/oracle/agent12c/core/12.1.0.4.0

NOTE: The agent base directory: /u01/app/oracle/agent12c will be removed after successful deinstallation of agent home.

DetachHome Command executed:/u01/app/oracle/agent12c/core/12.1.0.4.0/oui/bin/runInstaller -detachHome -force -depHomesOnly -silent ORACLE_HOME=/u01/app/oracle/agent12c/core/12.1.0.4.0 -waitForCompletion -invPtrLoc /u01/app/oracle/agent12c/core/12.1.0.4.0/oraInst.loc
Iniciando Oracle Universal Installer…

Verificando espaço de swap: deve ser superior a 500 MB. 99999 MB Reais Passado
The inventory pointer is located at /u01/app/oracle/agent12c/core/12.1.0.4.0/oraInst.loc
‘DetachHome’ bem-sucedido.
Iniciando Oracle Universal Installer…

Verificando espaço de swap: deve ser superior a 500 MB. 99999 MB Reais Passado
The inventory pointer is located at /u01/app/oracle/agent12c/core/12.1.0.4.0/oraInst.loc
Não foi possível atualizar o Oracle home ‘/u01/app/oracle/agent12c/sbin’ pois ele não existe.

Deinstall Command executed:/u01/app/oracle/agent12c/core/12.1.0.4.0/oui/bin/runInstaller -deinstall -silent “REMOVE_HOMES={/u01/app/oracle/agent12c/core/12.1.0.4.0}” -waitForCompletion -removeAllFiles -invPtrLoc /u01/app/oracle/agent12c/core/12.1.0.4.0/oraInst.loc
Iniciando Oracle Universal Installer…

Verificando espaço de swap: deve ser superior a 500 MB. 99999 MB Reais Passado
Preparando para iniciar o Oracle Universal Installer de /tmp/OraInstall2018-06-28_10-38-07AM. Aguarde …Oracle Universal Installer, Versão 11.1.0.12.0 Produção
Copyright (C) 1999, 2014, Oracle. Todos os direitos reservados.

Iniciando desinstalação

Desinstalação em andamento (Quinta-feira, 28 de Junho de 2018 10h38min12s BRT)
O assistente de configuração “Agent Deinstall Assistant” foi bem-sucedido
……………………………………………………… 100% Concluído.

Desinstalação bem-sucedida

Fim das fases de instalação.(Quinta-feira, 28 de Junho de 2018 10h38min20s BRT)
Fim das desinstalações
Verifique “/u01/app/oraInventory/logs/silentInstall2018-06-28_10-38-07AM.log” para obter mais detalhes.
cannot fetch initial working directory: Arquivo ou diretório não encontrado at /u01/app/oracle/agent12c/core/12.1.0.4.0/sysman/install/AgentDeinstall.pl line 303
cannot fetch initial working directory: Arquivo ou diretório não encontrado at /u01/app/oracle/agent12c/core/12.1.0.4.0/sysman/install/AgentDeinstall.pl line 303
cannot fetch initial working directory: Arquivo ou diretório não encontrado at /u01/app/oracle/agent12c/core/12.1.0.4.0/sysman/install/AgentDeinstall.pl line 303

NOTE: The targets monitored by this Management Agent will not be deleted in the Enterprise Manager Repository by this deinstall script. Make sure to delete the targets manually from the Cloud Control Console for a successful deinstallation.

Tarefa comum e corriqueira de um DBA é criar novos hosts para testes, ambientes de produção, desenvolvimento e homologação. Há poucos dias atrás precisei criar um servidor para instalação do Oracle Cloud Control 13c e fui surpreendido com a facilidade em alterar o nome de um servidor.

Nas versões anteriores era preciso alterar o arquivo /etc/hosts e reiniciar o S.O., porém na versão 7 basta digitar os comandos abaixo:

1 º – Checar o nome do servidor:

[root@localhost ~]# hostnamectl status
Static hostname: localhost.localdomain
Icon name: computer-vm
Chassis: vm
Machine ID: 5e2ea38c44ec49a497be3d6919cd9e96
Boot ID: 1996d9bfa13b48978e6cf411711a5e21
Virtualization: xen
Operating System: Oracle Linux Server 7.4
CPE OS Name: cpe:/o:oracle:linux:7:4:server
Kernel: Linux 4.1.12-94.3.9.el7uek.x86_64
Architecture: x86-64

2 º – Alterar o nome do servidor:

[root@localhost ~]# hostnamectl set-hostname SERVER002

3º – Checar o nome do servidor

[root@localhost ~]# hostnamectl status
Static hostname: SERVER002.localdomain
Icon name: computer-vm
Chassis: vm
Virtualization: xen
Operating System: Oracle Linux Server 7.4
CPE OS Name: cpe:/o:oracle:linux:7:4:server
Kernel: Linux 4.1.12-94.3.9.el7uek.x86_64
Architecture: x86-64

Foi só executar o passo 2 que o servidor já teve seu nome alterado, nem foi preciso reiniciar o S.O. para atingir tal objetivo.

Tenho um servidor de banco de dados que possui diversas instâncias do Oracle rodando sob um único ASM e recentemente precisei fazer um levantamento de quais eram os maiores bancos dentro dos DISKGROUPs de FRA e DATA. Inicialmente fiz um select em cada instância e joguei para uma planilha, mas .. são 23 instâncias, então certamente esse não era o melhor caminho : )

O script abaixo deve ser rodado na instância do ASM, basta trocar o que está na linha: WHERE gname=’FRA_ASM_DG’ pelo nome do seu diskgroup.

col gname form a10
col dbname form a10
col file_type form a14

SELECT
    gname,
    dbname,
    file_type,
    round(SUM(space)/1024/1024) mb,
    round(SUM(space)/1024/1024/1024) gb,
    COUNT(*) "#FILES"
FROM
    (
        SELECT
            gname,
            regexp_substr(full_alias_path, '[[:alnum:]_]*',1,4) dbname,
            file_type,
            space,
            aname,
            system_created,
            alias_directory
        FROM
            (
                SELECT
                    concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,
                    system_created,
                    alias_directory,
                    file_type,
                    space,
                    level,
                    gname,
                    aname
                FROM
                    (
                        SELECT
                            b.name            gname,
                            a.parent_index    pindex,
                            a.name            aname,
                            a.reference_index rindex ,
                            a.system_created,
                            a.alias_directory,
                            c.type file_type,
                            c.space
                        FROM
                            v$asm_alias a,
                            v$asm_diskgroup b,
                            v$asm_file c
                        WHERE
                            a.group_number = b.group_number
                        AND a.group_number = c.group_number(+)
                        AND a.file_number = c.file_number(+)
                        AND a.file_incarnation = c.incarnation(+) ) START WITH (mod(pindex, power(2, 24))) = 0
                AND rindex IN
                    (
                        SELECT
                            a.reference_index
                        FROM
                            v$asm_alias a,
                            v$asm_diskgroup b
                        WHERE
                            a.group_number = b.group_number
                        AND (
                                mod(a.parent_index, power(2, 24))) = 0
                    ) CONNECT BY prior rindex = pindex )
        WHERE
            NOT file_type IS NULL
            and system_created = 'Y' )
WHERE gname='FRA_ASM_DG'
GROUP BY
    gname,
    dbname,
    file_type
ORDER BY
4 desc;

O resultado deve ser algo parecido com o output abaixo:


GNAME      DBNAME     FILE_TYPE              MB         GB     #FILES
---------- ---------- -------------- ---------- ---------- ----------
FRA_ASM_DG LP142      ARCHIVELOG         241766        236         22
FRA_ASM_DG LP852      ARCHIVELOG         162494        159         66
FRA_ASM_DG LP90       ARCHIVELOG         147272        144          8
FRA_ASM_DG LP74       ONLINELOG           92376         90          8
FRA_ASM_DG LP65       ONLINELOG           81928         80          4
FRA_ASM_DG LP66       ONLINELOG           61448         60          4
FRA_ASM_DG LP98       ARCHIVELOG          58618         57        163
FRA_ASM_DG LP41       ARCHIVELOG          56984         56          8
FRA_ASM_DG LP42       ARCHIVELOG          33384         33         20
FRA_ASM_DG LP44       ONLINELOG           32776         32          4
FRA_ASM_DG LP22       ARCHIVELOG          31224         30       2807


Feito isso você terá noção de quais são os maiores bancos dentro do seu ASM, nesse caso, o LP142 está utilizando 236GB dentro do diskgroup de FRA e o LP22 está usando apenas 30GB.

Até a próxima.

Um erro muito comum em empresas que possuem equipes distintas de DBA, AD e Desenvolvimento é criar as estruturas de banco sem o devido cuidado em indexar as FK’s. Sistemas e melhorias são feitos diariamente e isso pode incluir novos dados, colunas, constraints e demais objetos no banco dedados.

Um script que costumo rodar semanalmente é o que verifica se as FK’s estão indexadas, pois dependendo do uso da tabela, podemso ter um aumento do wait TM CONTENTION. NO meu caso, todos os schemas começam com DB, portanto eu ajustei em 2 lugares para trazer somente os dados referente á esses schemas:

  AND a.owner LIKE 'DB%' 
  WHERE  c.index_owner LIKE 'DB%' 

Basta adaptar o script ás suas necessidades, ou se preferir não filtrar nada, retira esse trecho do código e execute-o.

O script é simples mas de grande utilidade, conforme pode ser visualizado abaixo:

SELECT CASE 
         WHEN b.table_name IS NULL THEN 'unindexed' 
         ELSE 'indexed' 
       END               AS status, 
       a.owner, 
       a.table_name      AS table_name, 
       a.constraint_name AS fk_name, 
       a.fk_columns      AS fk_columns, 
       b.index_name      AS index_name, 
       b.index_columns   AS index_columns 
FROM   (SELECT a.owner, 
               a.table_name, 
               a.constraint_name, 
               Listagg(a.column_name, ',') 
                 within GROUP (ORDER BY a.position) fk_columns 
        FROM   dba_cons_columns a, 
               dba_constraints b 
        WHERE  a.constraint_name = b.constraint_name 
               AND b.constraint_type = 'R' 
               AND a.owner LIKE 'DB%' 
               AND a.owner = b.owner 
        GROUP  BY a.owner, 
                  a.table_name, 
                  a.constraint_name) a, 
       (SELECT table_name, 
               index_name, 
               Listagg(c.column_name, ',') 
                 within GROUP (ORDER BY c.column_position) index_columns 
        FROM   dba_ind_columns c 
        WHERE  c.index_owner LIKE 'DB%' 
        GROUP  BY table_name, 
                  index_name) b 
WHERE  a.table_name = b.table_name(+) 
       AND b.index_columns(+) LIKE a.fk_columns 
                                   || '%' 
ORDER  BY 1 DESC, 
          2; 

Uma simples maneira de limpar o arquivo de log do listener (Listener.log) é escrevendo nele mesmo um valor simbólico.

Abaixo vamos aprender como limpar de maneira rapida o log do listener. É recomendável que você compacte e guarde em algum lugar para consultas futuras.

Ao listar
-rw-r—–   1 oracle dba   15G Jul 21 19:05 listener.log

Execute o comando:
# cat /dev/null > listener.log

E verifique que o arquivo foi sobrescrito:
-rw-r—–   1 oracle dba  1,1K Jul 21 19:09 listener.log

 

 

Em algumas situações a tablespace UNDO é preciso ser trocada, e apesar de parecer uma tarefa difícil, dada a importancia dessa tablespace, o procedimento é bem simples.

O parâmetro que controla a tablespace UNDO em uso é o: undo_tablespace, que pode ser verificado no sqlplus através do show parameter:

# show parameter undo_tablespace;

No nosso caso, a tablespace em uso é a UNDOTBS1. Para trocar a tablespace é preciso realizar os seguintes passos utilizando o SQLPLUS:

1º – Criar uma nova tablespace de UNDO:

# CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE ‘+DATA_ASM_DG’ SIZE 2G AUTOEXTEND ON NEXT 1G;

 

2º – Alterar o parâmetro UNDO_TABLESPACE para o nome dessa nova tablespace criada no passo anterior:

# ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2 SCOPE=SPFILE;

*Muitos DBA’s já alteram a tablespace com o SCOPE=BOTH, pois ela já realiza a alteração no SPFILE e em memória. Nesse caso a alteração já entra em vigor no momento da alteração e  é persistida no SPFILE após o restart da instância. Eu particularmente prefiro realizar o procedimento em um horário acordado com a gerência e já fazer o restart da instância alterando apenas no SPFILE.

 

3º – Monitorar o uso da tablespace UNDO anterior. O select abaixo informa se ainda há alguma transação na UNDO. Quando o count(*) trouxer o resultado igual zero é porque você já pode dropar a tablespace anterior:

SELECT count(*)
FROM v$rollname a,v$rollstat b
WHERE a.usn = b.usn
AND a.name IN (
SELECT segment_name
FROM dba_segments
WHERE tablespace_name = 'UNDOTBS1'
);

*o nome a ser incluído aqui é o da tablespace original

 

4º – Por fim remova a tablespace para reutilizar o espaço do ASM:

# DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

 

5º – Ao final do processo, basta verificar no SQLPlus a tablespace que está em uso:

# show parameter undo_tablespace;

 

Esse é o procedimento para trocar a tablespace de UNDO. Vale lembrar que para o ambiente RAC você vai encontrar uma UNDO diferente para cada instância do cluster.