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

Oracle Archive

Há alguns dias atrás precisei localizar a string PRAGMA AUTONOMOUS_TRANSACTION em todos os objetos do banco de dados. No SQL Server uma busca rápida pela sys.comments ou sys.sql_modules retorna quais objetos possuem essa string em seu código.

No Oracle a busca também é simples, existem 3 views que ajudam essa busca, são elas: ALL_SOURCE, DBA_SOURCE e USER_SOURCE.

  • ALL_SOURCE: lista todos os objetos que possuem a string desejada somente nos objetos que o usuário logado tem acesso.
  • DBA_SOURCE: lista todos os objetos que possuem a string desejada em todos os objetos da instância.
  • USER_SOURCE: lista somente os objetos cujo usuário logado seja dono.

O trecho PL-SQL para buscar os dados são:

SELECT *
FROM ALL_SOURCE
WHERE UPPER(text) LIKE UPPER(‘%PRAGMA AUTONOMOUS_TRANSACTION%’)
ORDER BY type, name, line;

Abaixo o exemplo da busca em minha base de dados encontrando os objetos que possuem essa string:

Feito isso basta editar a function FC_ASS e ir até a linha 6 para verificar a evidência do código.

Muitas pessoas ao executar operações DDL (alteração de tabelas, inclusão de FK’s, etc) recebem o seguinte erro no SQLPLUS, SQL Developer e afins:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired.

Esse erro ocorre devido á tabela que está sendo alterada estar ocupada com outras operações de manipulação de dados. Nesse caso você sempre receberá o erro ORA-00054 enquanto a tabela que você deseja alterar não estiver disponível.

Para resolver o problema é preciso agendar um horário de baixa atividade no banco de dados, tais como períodos da madrugada ou finais de semana! Fazendo assim, você evita a concorrência durante o dia e consegue executar o script sem esperar por muito tempo. Para ajudar nesse trabalho, existem 2 parâmetros que podem facilitar muito a execução dessa tarefa. São eles:

  • ALTER SESSION ENABLE PARALLEL DDL;
  • ALTER SESSION SET DDL_LOCK_TIMEOUT= 600;

O parâmetro ENABLE PARALLEL DDL habilita a sessão para realizar operações DDL em paralelo com o intuito de reduzir o tempo da operação e o DDL_LOCK_TIMEOUT especifica o tempo em segundos que a sessão vai aguardar por um lock na tabela a ser alterada.

O padrão para o ENABLE PARALLEL DDL é zero, o que indica que você nunca vai esperar por um lock. Sempre deixo o padrão de 10 minutos (600 segundos) quando preciso realizar alguma operação em uma grande tabela no banco de dados.

Abaixo segue o exemplo de execução dos comandos:

enable-paralel-ddl

Feito isso basta rodar a query e aguardar o tempo necessário para executá-la.

 

 

 

 

Algumas informações podem ser listadas diretamente no shell, porém o output é muito grande e atrapalha a busca por determinadas informações. No RMAN quando você lista os backups realizados, o utilitário retorna milhares de linhas com informações referente aos datafiles backupeados.

Uma opção interessante para salvar as informações do RMAN e posteriormente localiza-las com NOTEPAD++, notepad e afins é a opção LOG, que pode ser realizada da seguinte maneira:

[server01:inst01:/home/oracle]$ rman target / log=/home/oracle/log_rman_20161114.log

Feito isso, o prompt do RMAN abrirá normalmente:

rman_blog_log_destination

Os comandos listados acima tiveram seu output enviados para o arquivo: /home/oracle/log_rman_20161114.log e podem ser acessados com o editor VI ou comando CAT, TAIL, etc.

 

Geralmente após algum desligamento programado ou um incidente no servidor, rede elétrica, etc, é preciso voltar o RAC em seu pleno funcionamento e alguns comandos são úteis para verificar se o serviço está UP:

– Verificar o status do banco de dados através do comando srvctl:

# srvctl status database -d inst0

Resultado:

A instância inst01 não está em execução no nó dbserver01
A instância inst02 está em execução no nó dbserver02
A instância inst03 não está em execução no nó dbserver03

No nosso caso os serviços inst01 e inst03 nãoi estão rodando os 2 nós do RAC, somente em 1. É preciso executar o comando: srvctl start database -d inst0 novamente para que o serviço suba. Se ocorrer algum erro, basta ir no alert log do oracle home ou do grid para verificar as inconsistências

 

– Realizar a checkagem completa no cluster. Acesse a pasta bin do GRID: cd $GRID_HOME/bin e logo depois execute o comando abaixo:

# cd $GRID_HOME/bin

# ./crsctl check cluster -all

Resultado:

[dbserver01:inst01:/u01/app/12.1.0/grid/bin]$ ./crsctl check cluster -all
**************************************************************
dbserver01:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
dbserver02:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
dbserver03:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************

 

 – Listar os nodeapps para validação dos recursos de rede:

# srvctl status nodeapps

[dbserver01:vs012:/u01/app/12.1.0/grid/bin]$ srvctl status nodeapps
O VIP dbserver01-vip está ativado
O VIP dbserver01-vip está em execução no nó: dbserver01
O VIP dbserver02-vip está ativado
O VIP dbserver02-vip está em execução no nó: dbserver02
A rede está ativada
A rede está em execução no nó: dbserver01
A rede está em execução no nó: dbserver02
O ONS está ativado
O daemon ONS está em execução no nó: dbserver01
O daemon ONS está em execução no nó: dbserver02
PRKO-2166 : GSD não existe no nó(s) : dbserver01,dbserver02

 

– Para uma verificação mais detalhada, podemos usar o: crsctl stat res -t que lista os dados de cada recurso do cluster:

Acesse a pasta bin do GRID: # cd $GRID_HOME/bin

Execute o comando: # ./crsctl stat res -t

check_cluster_blog

Com base nas informações, podemos ver que os recursos ora.ACFS_BACKUP.BKP_ORACLE.advm, ora.ACFS_BACKUP.VOL_ACFS.advm e ora.ACFS_BACKUP.dg estão OFFLINE, sendo necessário intervenção para colocá-los online, conforme faremos no item 6.

– Para verificar o status de cada um dos itens OFFLINE, basta executar o comando abaixo:

# crsctl status resource ora.ACFS_BACKUP.BKP_ORACLE.advm

Resultado:

NAME=ora.ACFS_BACKUP.BKP_ORACLE.advm
TYPE=ora.volume.type
TARGET=ONLINE , ONLINE
STATE=OFFLINE, OFFLINE

– Para startar um recurso que está offline, basta executar o comando crsctl start resource [nome do recurso]:

# ./crsctl start resource ora.ACFS_BACKUP.BKP_ORACLE.advm

O resultado do comando é semelhante á esse abaixo, porém pode existir vários erros de acordo com o recurso e devem ser resolvidos de acordo com os erros na tela:

CRS-2672: Attempting to start ‘ora.ACFS_BACKUP.dg’ on ‘dbserver01’
CRS-2672: Attempting to start ‘ora.ACFS_BACKUP.dg’ on ‘dbserver02’

Esse é um checklist básico de verificação dos serviços. Outra variáveis podem ser verificadas tais como alert.log do oracle e do grid, logs do storage, rede e análise do /var/log/messages a nível de S.O.

Quando realizamos a instalação do Oracle Database, são instalados os binários e os referidos bancos de dados (dbca). Ror default os bancos não startam nem fazem o shutdown de maneira autônoma, é preciso entrar no SQLPLUS, rodar o comando shutdown quando necessita desligar o servidor ou o comando startup após o religamento ou reboot do servidor para que o banco seja startado e suas tabelas ficarem acessíveis.

O primeiro passo para automatizar esse processo, é acessando o arquivo /etc/oratab no servidor Linux e chegar algumas informações:

[root@localhost /]# vi /etc/oratab

verificar-oratab-linux

Para cada vez que você roda o DBCA e cria um banco de dados, uma nova linha é adicionada nesse arquivo com o nome do banco. No nosso caso o banco em questão é o cdb1. Note que há uma letra N no final da linha, que indica se o banco será startado quando o utilitário dbstart invocá-lo ou se será desligado quando o comando dbshut for utilizado.

Faça a edição dessa linha para Y e saia do editor do VI, deixando o arquivo desse jeito:

verificar-oratab-linux2

Depois de feito isso, basta configurar um serviço na pasta /etc/init.d com o nome dbora com os comandos abaixo:

#vi /etc/init.d/dbora

Copie e cole o comando abaixo, alterado o ORACLE_HOME para o que está setado no bash_profile do usuário oracle:

#!/bin/sh
# chkconfig: 345 99 10
ORACLE_HOME=(coloque aqui o caminho do seu ORACLE_HOME)
#
ORACLE=oracle
PATH=${PATH}:$ORACLE_HOME/bin
export ORACLE_HOME PATH
#
case $1 in
‘start’)
runuser -l $ORACLE -c “$ORACLE_HOME/bin/dbstart $ORACLE_HOME &”
touch /var/lock/subsys/dbora
;;
‘stop’)
runuser -l $ORACLE -c “$ORACLE_HOME/bin/dbshut $ORACLE_HOME”
rm -f /var/lock/subsys/dbora
;;
*)
echo “usage: $0 {start|stop}”
exit
;;
esac
#
exit

No meu ambiente ficou exatamente dessa maneira:

arquivo-etc-initd-dbora

Veja que no ORACLE_HOME eu coloquei o caminho da instalação do ORACLE em meu servidor, isso pode mudar de instalação pra instalação. Para verificar qual seu ORACLE_HOME, basta logar co mo usuário oracle no terminar e digitar o comando env e listar as variáveis.

Após criar o arquivo acima, basta setar as permissões corretas e definir o dbora como um serviço, conforme scripts abaixo:

#chmod 750 /etc/init.d/dbora
#chkconfig –add dbora

verificando-servico-e-permissoes

Verificando pelo chkconfig, podemos chegar a conclusão que o dbora foi incluído como serviço e com as opções: 3, 4 e 5 setadas como ON. isso aconteceu pelo fato de a 2ª linha do arquivo dbora conter essas informações: # chkconfig: 345 99 10 !

Para testar o funcionamento do script basta fazer um reboot no seu servidor de testes e atestar o correto desligamento e startup do banco através dos logs do Oracle.

 

Baixe e instale o Oracle Linux 7 diretamente do site da Oracle, após concluir a instalação rode o YUM UPDATE para atualizar todos os pacotes do Sistema operacional.

– Garanta que no arquivo /etc/hosts tem o nome completo da máquina, conforme exemplo abaixo:

[root@localhost ~]# vi /etc/hosts

192.168.0.1        localhost.localdomain         localhost
10.0.12.15       localhost.localdomain  localhost

– Ajustar o SELINUX, editar o arquivo config e alterar a linha SELINUX de enforced para permissive. Logo depois digitar o comando setenforce Permissive:

[root@localhost ~]# vi /etc/selinux/config
[root@localhost ~]# SELINUX = permissive
[root@localhost ~]# setenforce Permissive

Para verificar se as configurações foram aplicadas, digite o comando [root@localhost ~]# sestatus no shell e verificar a saída:

oracle-linux-selinux

 

– Crie os usuários e grupos abaixo:

groupadd -g 54321 oinstall
groupadd -g 54322 dba
groupadd -g 54323 oper
groupadd -g 54324 backupdba
groupadd -g 54325 dgdba
groupadd -g 54326 kmdba
groupadd -g 54327 asmdba
groupadd -g 54328 asmoper
groupadd -g 54329 asmadmin

Crie um usuário chamado oracle e o adicione nos grupos oinstall, dba e oper, conforme script abaixo:
[root@localhost ~]# useradd -u 54321 -g oinstall -G dba,oper oracle

Altere a senha do usuário oracle:

[root@localhost ~]# passwd oracle

Para visualizar os grupos criados, basta digitar: # cat /etc/group

oracle-linux-grupos-criados

 

– Desabilitar o Firewall, em algumas distribuições é preciso rodar o comando somente para o desabilitar o IPTABLES, em outras, tais como Oracle Linux 7 é preciso parar e desabilitar o serviço FIREWALLD:

[root@localhost ~]# service iptables stop
[root@localhost ~]# chkconfig iptables off
[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# systemctl disable firewalld

– Instale os pré-requisitos da oracle para o banco de dados. Esse pacote cria usuários, ajusta arquivos de configuração e instala os pacotes necessários.

[root@localhost ~]# yum install oracle-rdbms-server-12cR1-preinstall -y
Após rodar o comando acima, execute as instalações abaixo que são um complemento do preinstall:
# From Public Yum or ULN
 yum install binutils -y
  yum install compat-libcap1 -y
  yum install compat-libstdc++-33 -y
  yum install compat-libstdc++-33.i686 -y
  yum install gcc -y
  yum install gcc-c++ -y
  yum install glibc -y
  yum install glibc.i686 -y
  yum install glibc-devel -y
  yum install glibc-devel.i686 -y
  yum install ksh -y
  yum install libgcc -y
  yum install libgcc.i686 -y
  yum install libstdc++ -y
  yum install xhost -y
  yum install unzip -y
  yum install xclock -y
  yum install libstdc++.i686 -y
  yum install libstdc++-devel -y
  yum install libstdc++-devel.i686 -y
  yum install libaio -y
  yum install libaio.i686 -y
  yum install libaio-devel -y
  yum install libaio-devel.i686 -y
  yum install libXext -y
  yum install libXext.i686 -y
  yum install libXtst -y
  yum install libXtst.i686 -y
  yum install libX11 -y
  yum install libX11.i686 -y
  yum install libXau -y
  yum install libXau.i686 -y
  yum install libxcb -y
  yum install libxcb.i686 -y
  yum install libXi -y
  yum install libXi.i686 -y
  yum install make -y
  yum install sysstat -y
  yum install unixODBC -y
  yum install unixODBC-devel -y

– Permitir a execução de programas de modo interativo. Edite o arquivo sshd_config para permitir a emulação de softwares via terminal e deixe as configurações abaixo habilitadas:

[root@localhost ~]# vi /etc/ssh/sshd_config
AllowAgentForwarding yes
AllowTcpForwarding yes
X11Forwarding yes
X11DisplayOffset 10
X11UseLocalhost yes
PermitTTY yes
PrintMotd yes

Saia do linux e conecte novamente para que as configurações acima façam efeito e digite o comando abaixo com o usuário root, veja que aparecerá uma mensagem informativa logo após o comando ser executado:

[root@localhost ~]# xhost +
access control disabled, clients can connect from any host

E os comandos abaixo com o usuário oracle:

[oracle@localhost ~]# export DISPLAY

[oracle@localhost ~]# DISPLAY=:0.0

Para mais informações visitar o link: http://www.cyberciti.biz/faq/x11-connection-rejected-because-of-wrong-authentication/

–  Alterar o arquivo /etc/security/limits.d/[??]-nproc.conf, sendo que o sinal de interrogação deve ser trocado pelos números que estiverem em seu arquivo:

[root@localhost ~]# vi /etc/security/limits.d/20-nproc.conf

Comentar a linha abaixo:
#*          soft    nproc    1024

E adicionar essa linha no arquivo:
* - nproc 16384

 

 – Adicionar as seguintes linhas no arquivo /etc/security/limits.conf:

[root@localhost ~]# vi /etc/security/limits.conf

oracle        soft         nofile      1024
oracle        hard       nofile      65536
oracle        soft         nproc      16384
oracle        hard       nproc      16384
oracle        soft         stack       10240
oracle       hard        stack       32768

 

– Criar o diretório de instalação do Oracle e dar as devidas permissões:

[root@localhost ~]# mkdir -p /u01/app/oracle/product/12.1.0.2/db_1
[root@localhost ~]# chown -R oracle:oinstall /u01
[root@localhost ~]# chmod -R 775 /u01

10º – Adicionar o conteúdo abaixo no bash_profile do usuário Oracle (/home/oracle/.bash_profile):

[root@localhost ~]# vi /home/oracle/.bash_profile

# Oracle Settings
export TMP=/tmp
export TMPDIR=$TMP

export ORACLE_HOSTNAME=nomedasuamaquina.nomedoseudominio
export ORACLE_UNQNAME=cdb1
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0.2/db_1
export ORACLE_SID=cdb1

export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

11º – Copiar a instalação do oracle (parte 1 e 2) e descompactar:

[root@localhost ~]# unzip linuxamd64_12102_database_1of2.zip
[root@localhost ~]# unzip linuxamd64_12102_database_2of2.zip

 

12º – Acessar a pasta database (# cd /database ) e listar os arquivos com o ls -l

 

Por aqui terminamos a configuração do S.O. Oracle Linux para suportar a instalação do SGBD Oracle. No próximo artigo daremos início á instalação do banco de dados através do comando ./runInstaler de dentro da pasta database.