tag:blogger.com,1999:blog-18355829693318898892024-03-05T11:22:39.009-03:00Banco TunadoAnonymoushttp://www.blogger.com/profile/08245597551579856718noreply@blogger.comBlogger7125tag:blogger.com,1999:blog-1835582969331889889.post-73742783390125962892017-02-15T13:07:00.001-02:002017-02-15T13:10:33.218-02:00Instalando Oracle Database 12c em Silent Mode (Sem interface Gráfica)Nem sempre iremos trabalhar em ambientes onde há disponível uma interface gráfica. Dessa forma, a instalação "Next, Next, Next" do OUI não é possível.<br />
<br />
Felizmente, podemos realizar a instalação utilizando o Oracle Universal Installer (OUI) em silent mode. Isto é, sem a utilização da interface gráfica.<br />
<br />
Nosso ambiente está em uma Máquina Virtual VirtualBox, com CentOS 7 Minimal instalado. Essa distribuição pode ser obtida aqui e se trata de uma instalação mínima, somente com os recursos necessários para se iniciar o Sistema Operacional. Dessa forma, qualquer pacote adicional que necessitamos deverá ser instalado separadamente, utilizando YUM.<br />
<br />
Como se trata de um ambiente "zero", iniciaremos preparando o ambiente para receber a instalação do Oracle.<br />
<br />
<a name='more'></a><b>Preparando a VM para compartilhar arquivos com a máquina host.</b><br />
<br />
Como estamos utilizando uma Máquina Virtual criada utiliando o VirtualBox, precisamos instalar as ferramentas adicionais para que o compartilhamento de arquivos entre a máquina host e a máquina virtual seja habilitado.<br />
<br />
O VirtualBox Guest Additions depende dos pacotes gcc, kernel-devel e make. Abaixo, os passos para instalação desses pacotes:<br />
<div>
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: #888888;"># Como root</span>
<span style="color: #c65d09; font-weight: bold;">[root@localhost ~]#</span> yum update
<span style="color: #c65d09; font-weight: bold;">[root@localhost ~]#</span> yum install gcc kernel-devel make
<span style="color: #888888;"># Reboot</span>
<span style="color: #c65d09; font-weight: bold;">[root@localhost ~]#</span> shutdown -r now
</pre>
</div>
<b><br /></b>
<b>Instalando o VirtualBox Guest Additions.</b><br />
<br />
No Oracle VirtualBox, Clique em Dispositivos -> Instalar Guest Additions.<br />
<br />
Se não for possível, faça download do Guest Additions manualmente e insira a imagem em sua controladora de Discos.<br />
<br />
Para dowload manual do Guest Additions, acesse o link http://download.virtualbox.org/virtualbox/, navegue até a pasta correspondente à sua versão do VirtualBox, e faça download do arquivo VBoxGuestAdditions_<version>.iso. No meu caso, VBoxGuestAdditions_5.1.14.iso.<br />
<br />
Para adicionar o disco em sua controladora de Discos, no Menu Configuração, aba Armazenamento, Selecione o disco da Controladora IDE e abra o arquivo VBoxGuestAdditions_5.1.14.iso.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhH-R2kUn0AdcQWJ6uU20yc_AHoNe1OK59jYVHurH63W9JF2YQQK58NRLj2wYaTztfa4nBGbx9WjtN-yyA9QsHk-q_yP2SALVq7XGGwidCszr0VkQMRHxtAWfpaG3usCzf2vFAbwoDNKq8/s1600/Add+Disco+Iso.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="184" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhH-R2kUn0AdcQWJ6uU20yc_AHoNe1OK59jYVHurH63W9JF2YQQK58NRLj2wYaTztfa4nBGbx9WjtN-yyA9QsHk-q_yP2SALVq7XGGwidCszr0VkQMRHxtAWfpaG3usCzf2vFAbwoDNKq8/s400/Add+Disco+Iso.png" width="400" /></a></div>
<br />
Monte o disco que acabou de inserir, faça a instalação do pacote necessário e, finalmente, instale o pacote de Funções adicionais.<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: #888888;"># Como root</span>
<span style="color: #c65d09; font-weight: bold;">[root@localhost ~]#</span> mount /dev/sr0 /mnt
<span style="color: #c65d09; font-weight: bold;">[root@localhost ~]#</span> yum install bzip2
<span style="color: #c65d09; font-weight: bold;">[root@localhost ~]#</span> <span style="color: #007020;">cd</span> /mnt
<span style="color: #c65d09; font-weight: bold;">[root@localhost ~]#</span> ./VBoxLinuxAdditions.run
</pre>
</div>
<br />
Desligue a máquina virtual<br />
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: #c65d09; font-weight: bold;">[root@localhost ~]#</span> shutdown -h now
</pre>
</div>
<br />
No painel do VirtualBox, selecione a máquina virtual e clique em Configurações e navegue até Pastas Compartilhadas.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgaw6X08k1Xw-yScntADyJ7rwNIUIkKDQgYrGGVyGxl-HAOcd_IC7wYUBD8nfwcSnqGZMD7OE0_nzBQexVL4FIiGP3qFiWMjKm6OMLA1JreaTPHzHcM1UKeA_0-EF3igepCf10pguAF9fg/s1600/1-0.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="183" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgaw6X08k1Xw-yScntADyJ7rwNIUIkKDQgYrGGVyGxl-HAOcd_IC7wYUBD8nfwcSnqGZMD7OE0_nzBQexVL4FIiGP3qFiWMjKm6OMLA1JreaTPHzHcM1UKeA_0-EF3igepCf10pguAF9fg/s400/1-0.png" width="400" /></a></div>
<br />
Adicione um novo compartilhamento.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBvDEiH7ytlI-RRXogjlUCE0nnFlEQyoGfrwwjdWB1kW5SVYFpv5gY-EzSMb9kUkDp28niGX9gJwU8e-3XNHZPP3CXxFeKOyfzJO6m2lHDDVpP2NirtATHT6MF_TcA1pD5arlN714PpiY/s1600/2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="183" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBvDEiH7ytlI-RRXogjlUCE0nnFlEQyoGfrwwjdWB1kW5SVYFpv5gY-EzSMb9kUkDp28niGX9gJwU8e-3XNHZPP3CXxFeKOyfzJO6m2lHDDVpP2NirtATHT6MF_TcA1pD5arlN714PpiY/s400/2.png" width="400" /></a></div>
<br />
Inicie novamente a máquina virtual e verifique se a pasta foi adicionada.<br />
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: #c65d09; font-weight: bold;">[root@localhost ~]#</span> <span style="color: #007020;">cd</span> /media
<span style="color: #c65d09; font-weight: bold;">[root@localhost media]#</span> ls -l
<span style="color: #888888;">total 4</span>
<span style="color: #888888;">drwxrwx---. 1 root vboxsf 4096 Fev 8 12:17 sf_Compartilhamento</span>
</pre>
</div>
<br />
<b>Preparando o ambiente para instalação do Oracle Database 12c.</b><br />
<b><br /></b>
Faça o Download do instalador do Oracle 12c e o salve na pasta compartilhada com a máquina virtual.<br />
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: #c65d09; font-weight: bold;">[root@localhost ~]#</span> <span style="color: #007020;">cd</span> /media/sf_Compartilhamento/Oracle
<span style="color: #c65d09; font-weight: bold;">[root@localhost Oracle]#</span> ls -l
<span style="color: #888888;">total 2625096</span>
<span style="color: #888888;">-rwxrwx---. 1 root vboxsf 1673544724 Fev 13 13:27 linuxamd64_12102_database_1of2.zip</span>
<span style="color: #888888;">-rwxrwx---. 1 root vboxsf 1014530602 Fev 13 13:15 linuxamd64_12102_database_2of2.zip</span>
</pre>
</div>
<br />
<b>Instalação Oracle 12c - Pré Requisitos.</b><br />
<b><br /></b>
Uma nota importante: Mantenha a documentação ao seu lado sempre que for realizar a instalação.<br />
<br />
<b>Configuração do arquivo hosts.</b><br />
<b><br /></b>
É altamente recomendável que o nome do servidor esteja no /etc/hosts.<br />
<br />
Por exemplo:<br />
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: #888888;">127.0.0.1 dbora dbora.localdomain.com localhost localhost.localdomain localhost4 localhost4.localdomain4</span>
</pre>
</div>
<br />
<b>Instalação dos pacotes de Sistema Operacional necessários.</b><br />
<b><br /></b>
Na sessão de Pré Requisitos de Sistema do <a href="http://docs.oracle.com/database/121/LADBI/pre_install.htm#LADBI80757" target="_blank">Database Installation Guide</a>, identificamos os pacotes necessários à instalação do Oracle 12cR1:<br />
<br />
Observação: Como estamos utilizando a versão 7 do CentOS, instalamos os mesmos pacotes referentes ao Red Hat 7.<br />
<br />
<i>binutils-2.23.52.0.1-12.el7.x86_64</i><br />
<i>compat-libcap1-1.10-3.el7.x86_64</i><br />
<i>compat-libstdc++-33-3.2.3-71.el7.i686 </i><br />
<i>compat-libstdc++-33-3.2.3-71.el7.x86_64</i><br />
<i>gcc-4.8.2-3.el7.x86_64 </i><br />
<i>gcc-c++-4.8.2-3.el7.x86_64 </i><br />
<i>glibc-2.17-36.el7.i686 </i><br />
<i>glibc-2.17-36.el7.x86_64 </i><br />
<i>glibc-devel-2.17-36.el7.i686 </i><br />
<i>glibc-devel-2.17-36.el7.x86_64 </i><br />
<i>ksh</i><br />
<i>libaio-0.3.109-9.el7.i686 </i><br />
<i>libaio-0.3.109-9.el7.x86_64 </i><br />
<i>libaio-devel-0.3.109-9.el7.i686 </i><br />
<i>libaio-devel-0.3.109-9.el7.x86_64 </i><br />
<i>libgcc-4.8.2-3.el7.i686 </i><br />
<i>libgcc-4.8.2-3.el7.x86_64 </i><br />
<i>libstdc++-4.8.2-3.el7.i686 </i><br />
<i>libstdc++-4.8.2-3.el7.x86_64 </i><br />
<i>libstdc++-devel-4.8.2-3.el7.i686 </i><br />
<i>libstdc++-devel-4.8.2-3.el7.x86_64 </i><br />
<i>libXi-1.7.2-1.el7.i686 </i><br />
<i>libXi-1.7.2-1.el7.x86_64 </i><br />
<i>libXtst-1.2.2-1.el7.i686 </i><br />
<i>libXtst-1.2.2-1.el7.x86_64 </i><br />
<i>make-3.82-19.el7.x86_64 </i><br />
<i>sysstat-10.1.5-1.el7.x86_64</i><br />
<br />
Para instalar todos os pacotes necessários de uma só vez:<br />
<div>
<br /></div>
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: #c65d09; font-weight: bold;">[root@localhost ~]#</span> yum install binutils compat-libcap1 /
<span style="color: #888888;">compat-libstdc++-33.i686 compat-libstdc++-33 gcc gcc-c++ /</span>
<span style="color: #888888;">glibc.i686 glibc glibc-devel.i686 glibc-devel ksh libaio.i686 /</span>
<span style="color: #888888;">libaio.x86_64 libaio-devel.i686 libaio-devel libgcc.i686 /</span>
<span style="color: #888888;">libgcc libstdc++.i686 libstdc++ libstdc++-devel.i686 /</span>
<span style="color: #888888;">libstdc++-devel libXi.i686 libXi libXtst.i686 libXtst /</span>
<span style="color: #888888;">make sysstat</span>
</pre>
</div>
<br />
Verifique se todos os pacotes foram instalados e correspondem à versão mínima necessária.<br />
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: #c65d09; font-weight: bold;">[root@localhost ~]#</span> rpm -q binutils compat-libcap1 /
<span style="color: #888888;">compat-libstdc++-33.i686 compat-libstdc++-33 gcc /</span>
<span style="color: #888888;">gcc-c++ glibc.i686 glibc glibc-devel.i686 /</span>
<span style="color: #888888;">glibc-devel ksh libaio.i686 libaio.x86_64 /</span>
<span style="color: #888888;">libaio-devel.i686 libaio-devel libgcc.i686 /</span>
<span style="color: #888888;">libgcc libstdc++.i686 libstdc++ libstdc++-devel.i686 /</span>
<span style="color: #888888;">libstdc++-devel libXi.i686 libXi libXtst.i686 libXtst /</span>
<span style="color: #888888;">make sysstat</span>
<span style="color: #888888;">binutils-2.25.1-22.base.el7.x86_64</span>
<span style="color: #888888;">compat-libcap1-1.10-7.el7.x86_64</span>
<span style="color: #888888;">compat-libstdc++-33-3.2.3-72.el7.i686</span>
<span style="color: #888888;">compat-libstdc++-33-3.2.3-72.el7.x86_64</span>
<span style="color: #888888;">compat-libstdc++-33-3.2.3-72.el7.i686</span>
<span style="color: #888888;">gcc-4.8.5-11.el7.x86_64</span>
<span style="color: #888888;">gcc-c++-4.8.5-11.el7.x86_64</span>
<span style="color: #888888;">glibc-2.17-157.el7_3.1.i686</span>
<span style="color: #888888;">glibc-2.17-157.el7_3.1.x86_64</span>
<span style="color: #888888;">glibc-2.17-157.el7_3.1.i686</span>
<span style="color: #888888;">glibc-devel-2.17-157.el7_3.1.i686</span>
<span style="color: #888888;">glibc-devel-2.17-157.el7_3.1.x86_64</span>
<span style="color: #888888;">glibc-devel-2.17-157.el7_3.1.i686</span>
<span style="color: #888888;">ksh-20120801-26.el7.x86_64</span>
<span style="color: #888888;">libaio-0.3.109-13.el7.i686</span>
<span style="color: #888888;">libaio-0.3.109-13.el7.x86_64</span>
<span style="color: #888888;">libaio-devel-0.3.109-13.el7.i686</span>
<span style="color: #888888;">libaio-devel-0.3.109-13.el7.i686</span>
<span style="color: #888888;">libaio-devel-0.3.109-13.el7.x86_64</span>
<span style="color: #888888;">libgcc-4.8.5-11.el7.i686</span>
<span style="color: #888888;">libgcc-4.8.5-11.el7.x86_64</span>
<span style="color: #888888;">libgcc-4.8.5-11.el7.i686</span>
<span style="color: #888888;">libstdc++-4.8.5-11.el7.i686</span>
<span style="color: #888888;">libstdc++-4.8.5-11.el7.x86_64</span>
<span style="color: #888888;">libstdc++-4.8.5-11.el7.i686</span>
<span style="color: #888888;">libstdc++-devel-4.8.5-11.el7.i686</span>
<span style="color: #888888;">libstdc++-devel-4.8.5-11.el7.i686</span>
<span style="color: #888888;">libstdc++-devel-4.8.5-11.el7.x86_64</span>
<span style="color: #888888;">libXi-1.7.4-2.el7.i686</span>
<span style="color: #888888;">libXi-1.7.4-2.el7.x86_64</span>
<span style="color: #888888;">libXi-1.7.4-2.el7.i686</span>
<span style="color: #888888;">libXtst-1.2.2-2.1.el7.i686</span>
<span style="color: #888888;">libXtst-1.2.2-2.1.el7.x86_64</span>
<span style="color: #888888;">libXtst-1.2.2-2.1.el7.i686</span>
<span style="color: #888888;">make-3.82-23.el7.x86_64</span>
<span style="color: #888888;">sysstat-10.1.5-11.el7.x86_64</span>
</pre>
</div>
<br />
<b>Criação dos grupos e usuários de sistema operacional.</b><br />
<br />
Nem sempre o DBA será a única figura administrando o ambiente, e diversas atribuições como backup, operações, administração dos discos (asm) podem ser atribuidas a pessoas diferentes.<br />
<br />
Por este motivo, as permissões dos diversos arquivos do software são atribuídas a grupos de Sistema Operacional Diferentes. Assim, podemos criar usuários de sistema operacional individuais e associá-los aos grupos desejados baseados em suas funções.<br />
<br />
Se quiser mais informações sobre os grupos e suas funções, <a href="http://docs.oracle.com/database/121/LADBI/usr_grps.htm#BABCAEAD" target="_blank">veja aqui</a>.<br />
<br />
<b>Criação dos grupos de sistema operacional.</b><br />
<br />
A administração do ambiente oracle pode ser diferenciada através dos grupos de sistema operacional. Cada grupo possui permissões específicas para realizar algumas atividades.<br />
<br />
Neste exemplo, utilizaremos apenas 2 grupos, e atribuiriemos todas as permissões a eles: oinstall e dba.<br />
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: #c65d09; font-weight: bold;">[root@localhost ~]#</span> groupadd dba
<span style="color: #c65d09; font-weight: bold;">[root@localhost ~]#</span> groupadd oinstall
</pre>
</div>
<b><br /></b>
<b>Criação do usuário de sistema operacional.</b><br />
O oracle também nos permite instalar produtos diferentes em diferentes owners no sistema operacional.<br />
<br />
Por exemplo, podemos instalar o software de Banco de Dados Oracle no owner (usuário) oracle, e o software Grid Infrastructure (não coberto neste texto) no owner grid.<br />
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: #c65d09; font-weight: bold;">[root@localhost ~]#</span> /usr/sbin/useradd -g oinstall -G dba oracle
<span style="color: #c65d09; font-weight: bold;">[root@localhost home]#</span> passwd oracle
<span style="color: #888888;">Changing password for user oracle.</span>
<span style="color: #888888;">New password: </span>
<span style="color: #888888;">BAD PASSWORD: The password is shorter than 8 characters</span>
<span style="color: #888888;">Retype new password: </span>
<span style="color: #888888;">passwd: all authentication tokens updated successfully.</span>
</pre>
</div>
<br />
O primeiro comando cria o usuário oracle, atribui como seu grupo principal oinstall e o torna membro do grupo DBA.<br />
<br />
Assim que criado, o diretório home do usuário oracle é criado em /home/oracle. Este é o diretório padrão. Poderíamos atribuir qualquer diretório como home para o usuário oracle, bastaria criarmos o diretório préviamente e informar utilizando a opção -d (useradd -d /qualquer/diretório...)<br />
<br />
O segundo comando cria uma senha para o usuário oracle.<br />
<b><br /></b>
<b>Criando o diretório base da instalação do Oracle (ORACLE_BASE).</b><br />
<br />
Também necessitamos criar o diretório base de onde o software será criado. Todos os subdiretórios serão/devem ser criados abaixo deste.<br />
<br />
O usuário oracle precisa ser o 'dono' desse diretório e ter permissão de escrita/leitura em todos os sub-diretórios abaixo deste.<br />
<br />
Como root, crie o seguinte diretórios e sete as permissões necessárias:<br />
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: #c65d09; font-weight: bold;">[root@localhost ~]#</span> mkdir -p /u01/app/oracle
<span style="color: #c65d09; font-weight: bold;">[root@localhost ~]#</span> chown -R oracle:oinstall /u01
<span style="color: #c65d09; font-weight: bold;">[root@localhost ~]#</span> chmod -R 775 /u01
</pre>
</div>
<b><br /></b>
<b>Descompactando os arquivos de instalação.</b><br />
<br />
Faça a cópia dos pacotes de instalação para um diretório local, altere o owner dos arquivos para o usuário oracle e descompacte os pacotes de instalação.<br />
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: #888888;">#Como root</span>
<span style="color: #c65d09; font-weight: bold;">[root@localhost Oracle]#</span> yum install unzip
<span style="color: #c65d09; font-weight: bold;">[root@localhost ~]#</span> mkdir /home/oracle/install
<span style="color: #c65d09; font-weight: bold;">[root@localhost ~]#</span> <span style="color: #007020;">cd</span> /media/sf_Compartilhamento/Oracle
<span style="color: #c65d09; font-weight: bold;">[root@localhost ~]#</span> cp * /home/oracle/install
<span style="color: #c65d09; font-weight: bold;">[root@localhost ~]#</span> chown -R oracle:oinstall /home/oracle/install
<span style="color: #888888;">#Altere para o usuário oracle</span>
<span style="color: #c65d09; font-weight: bold;">[root@localhost Oracle]#</span> su - oracle
<span style="color: #c65d09; font-weight: bold;">[oracle@localhost ~]$</span> <span style="color: #007020;">cd </span>install/
<span style="color: #c65d09; font-weight: bold;">[oracle@localhost install]$</span> unzip linuxamd64_12102_database_1of2.zip
<span style="color: #c65d09; font-weight: bold;">[oracle@localhost install]$</span> unzip linuxamd64_12102_database_2of2.zip
</pre>
</div>
<b><br /></b>
<b>Criação do arquivo de resposta - Response File.</b><br />
<br />
Como já dito, o Oracle Universal Installer permite a instalação sem a utilização de ferramenta gráfica. Para tanto, basta informarmos ao instalador todas as opções de instalação que seriam informadas na UI.<br />
<br />
A melhor forma de se fazer isso, é com o arquivo de resposta.<br />
<br />
Criamos um arquivo de texto com todas as opções da instalação e informamos a localização deste arquivo para o instalador através da opção -responseFile.<br />
<br />
Existe um template do response file incluído nos arquivos da instalação, no diretório response do arquivo extraído, chamado db_install.rsp.<br />
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: #888888;">#Diretório onde os arquivos .zip foram descompactados</span>
<span style="color: #c65d09; font-weight: bold;">[oracle@localhost install]$</span> <span style="color: #007020;">cd </span>database/response/
<span style="color: #c65d09; font-weight: bold;">[oracle@localhost response]$</span> ls -l
<span style="color: #888888;">total 112</span>
<span style="color: #888888;">-rwxrwxr-x. 1 oracle oinstall 74822 Apr 4 2014 dbca.rsp</span>
<span style="color: #888888;">-rw-rw-r--. 1 oracle oinstall 25036 Jul 7 2014 db_install.rsp</span>
<span style="color: #888888;">-rwxrwxr-x. 1 oracle oinstall 6038 Jan 24 2014 netca.rsp</span>
</pre>
</div>
<br />
Abaixo o arquivo de resposta que sendo utilizado, com as opções mais básicas para uma instalação standalone do Oracle EE:<br />
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: #888888;">oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v12.1.0</span>
<span style="color: #888888;">oracle.install.option=INSTALL_DB_SWONLY</span>
<span style="color: #888888;">ORACLE_HOSTNAME=dbora.localdomain.com</span>
<span style="color: #888888;">INVENTORY_LOCATION=/u01/app/oraInventory</span>
<span style="color: #888888;">ORACLE_BASE=/u01/app/oracle</span>
<span style="color: #888888;">ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1</span>
<span style="color: #888888;">oracle.install.db.InstallEdition=EE</span>
<span style="color: #888888;">UNIX_GROUP_NAME=oinstall</span>
<span style="color: #888888;">oracle.install.db.DBA_GROUP=dba</span>
<span style="color: #888888;">oracle.install.db.BACKUPDBA_GROUP=dba</span>
<span style="color: #888888;">oracle.install.db.DGDBA_GROUP=dba</span>
<span style="color: #888888;">oracle.install.db.KMDBA_GROUP=dba</span>
<span style="color: #888888;">SECURITY_UPDATES_VIA_MYORACLESUPPORT=false</span>
<span style="color: #888888;">DECLINE_SECURITY_UPDATES=true</span>
<span style="color: #888888;">oracle.installer.autoupdates.option=SKIP_UPDATES</span>
</pre>
</div>
<b><br /></b>
<b>Verificando se todos os pré-requisitos foram atendidos utilizando o instalador.</b><br />
<br />
Para garantir que todos os pré-requisitos foram atendidos, podemos utilizar a opção -executePrereqs do instalador.<br />
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;">./runInstaller -silent -executePrereqs -responseFile <span style="background-color: #fff0f0;">"/home/oracle/install/db_install.rsp"</span>
</pre>
</div>
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: #c65d09; font-weight: bold;">[oracle@localhost database]$</span> ./runInstaller -silent -executePrereqs -responseFile <span style="background-color: #fff0f0;">"/home/oracle/install/db_install.rsp"</span>
<span style="color: #888888;">Starting Oracle Universal Installer...</span>
<span style="color: #888888;">Checking Temp space: must be greater than 500 MB. Actual 12014 MB Passed</span>
<span style="color: #888888;">Checking swap space: must be greater than 150 MB. Actual 2047 MB Passed</span>
<span style="color: #888888;">Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-02-14_09-48-00AM. Please wait ...[oracle@localhost database]$ [FATAL] [INS-13013] Target environment does not meet some mandatory requirements.</span>
<span style="color: #888888;"> CAUSE: Some of the mandatory prerequisites are not met. See logs for details. /tmp/OraInstall2017-02-14_09-48-00AM/installActions2017-02-14_09-48-00AM.log</span>
<span style="color: #888888;"> ACTION: Identify the list of failed prerequisite checks from the log: /tmp/OraInstall2017-02-14_09-48-00AM/installActions2017-02-14_09-48-00AM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.</span>
<span style="color: #888888;">A log of this session is currently saved as: /tmp/OraInstall2017-02-14_09-48-00AM/installActions2017-02-14_09-48-00AM.log. Oracle recommends that if you want to keep this log, you should move it from the temporary location.</span>
<span style="color: #888888;">Verificando os requisitos que não foram atendidos.</span>
<span style="color: #888888;">O instalador cria um arquivo de log com todos os pre requisitos que não foram atendidos.</span>
<span style="color: #888888;">De forma resumida, esses foram os pre-requisitos que falharam em minha instalação:</span>
<span style="color: #888888;">INFO: ------------------List of failed Tasks------------------</span>
<span style="color: #888888;">INFO: *********************************************</span>
<span style="color: #888888;">INFO: Hard Limit: maximum open file descriptors: This is a prerequisite condition to test whether the hard limit for "maximum open file descriptors" is set correctly.</span>
<span style="color: #888888;">INFO: Severity:CRITICAL</span>
<span style="color: #888888;">INFO: OverallStatus:VERIFICATION_FAILED</span>
<span style="color: #888888;">INFO: *********************************************</span>
<span style="color: #888888;">INFO: Hard Limit: maximum user processes: This is a prerequisite condition to test whether the hard limit for "maximum user processes" is set correctly.</span>
<span style="color: #888888;">INFO: Severity:IGNORABLE</span>
<span style="color: #888888;">INFO: OverallStatus:VERIFICATION_FAILED</span>
<span style="color: #888888;">INFO: *********************************************</span>
<span style="color: #888888;">INFO: OS Kernel Parameter: semmsl: This is a prerequisite condition to test whether the OS kernel parameter "semmsl" is properly set.</span>
<span style="color: #888888;">INFO: Severity:IGNORABLE</span>
<span style="color: #888888;">INFO: OverallStatus:WARNING</span>
<span style="color: #888888;">INFO: *********************************************</span>
<span style="color: #888888;">INFO: OS Kernel Parameter: semmns: This is a prerequisite condition to test whether the OS kernel parameter "semmns" is properly set.</span>
<span style="color: #888888;">INFO: Severity:IGNORABLE</span>
<span style="color: #888888;">INFO: OverallStatus:WARNING</span>
<span style="color: #888888;">INFO: *********************************************</span>
<span style="color: #888888;">INFO: OS Kernel Parameter: semopm: This is a prerequisite condition to test whether the OS kernel parameter "semopm" is properly set.</span>
<span style="color: #888888;">INFO: Severity:IGNORABLE</span>
<span style="color: #888888;">INFO: OverallStatus:VERIFICATION_FAILED</span>
<span style="color: #888888;">INFO: *********************************************</span>
<span style="color: #888888;">INFO: OS Kernel Parameter: semmni: This is a prerequisite condition to test whether the OS kernel parameter "semmni" is properly set.</span>
<span style="color: #888888;">INFO: Severity:IGNORABLE</span>
<span style="color: #888888;">INFO: OverallStatus:WARNING</span>
<span style="color: #888888;">INFO: *********************************************</span>
<span style="color: #888888;">INFO: OS Kernel Parameter: shmmax: This is a prerequisite condition to test whether the OS kernel parameter "shmmax" is properly set.</span>
<span style="color: #888888;">INFO: Severity:IGNORABLE</span>
<span style="color: #888888;">INFO: OverallStatus:OPERATION_FAILED</span>
<span style="color: #888888;">INFO: *********************************************</span>
<span style="color: #888888;">INFO: OS Kernel Parameter: shmmni: This is a prerequisite condition to test whether the OS kernel parameter "shmmni" is properly set.</span>
<span style="color: #888888;">INFO: Severity:IGNORABLE</span>
<span style="color: #888888;">INFO: OverallStatus:WARNING</span>
<span style="color: #888888;">INFO: *********************************************</span>
<span style="color: #888888;">INFO: OS Kernel Parameter: shmall: This is a prerequisite condition to test whether the OS kernel parameter "shmall" is properly set.</span>
<span style="color: #888888;">INFO: Severity:IGNORABLE</span>
<span style="color: #888888;">INFO: OverallStatus:OPERATION_FAILED</span>
<span style="color: #888888;">INFO: *********************************************</span>
<span style="color: #888888;">INFO: OS Kernel Parameter: file-max: This is a prerequisite condition to test whether the OS kernel parameter "file-max" is properly set.</span>
<span style="color: #888888;">INFO: Severity:IGNORABLE</span>
<span style="color: #888888;">INFO: OverallStatus:VERIFICATION_FAILED</span>
<span style="color: #888888;">INFO: *********************************************</span>
<span style="color: #888888;">INFO: OS Kernel Parameter: ip_local_port_range: This is a prerequisite condition to test whether the OS kernel parameter "ip_local_port_range" is properly set.</span>
<span style="color: #888888;">INFO: Severity:IGNORABLE</span>
<span style="color: #888888;">INFO: OverallStatus:WARNING</span>
<span style="color: #888888;">INFO: *********************************************</span>
<span style="color: #888888;">INFO: OS Kernel Parameter: rmem_default: This is a prerequisite condition to test whether the OS kernel parameter "rmem_default" is properly set.</span>
<span style="color: #888888;">INFO: Severity:IGNORABLE</span>
<span style="color: #888888;">INFO: OverallStatus:VERIFICATION_FAILED</span>
<span style="color: #888888;">INFO: *********************************************</span>
<span style="color: #888888;">INFO: OS Kernel Parameter: rmem_max: This is a prerequisite condition to test whether the OS kernel parameter "rmem_max" is properly set.</span>
<span style="color: #888888;">INFO: Severity:IGNORABLE</span>
<span style="color: #888888;">INFO: OverallStatus:VERIFICATION_FAILED</span>
<span style="color: #888888;">INFO: *********************************************</span>
<span style="color: #888888;">INFO: OS Kernel Parameter: wmem_default: This is a prerequisite condition to test whether the OS kernel parameter "wmem_default" is properly set.</span>
<span style="color: #888888;">INFO: Severity:IGNORABLE</span>
<span style="color: #888888;">INFO: OverallStatus:VERIFICATION_FAILED</span>
<span style="color: #888888;">INFO: *********************************************</span>
<span style="color: #888888;">INFO: OS Kernel Parameter: wmem_max: This is a prerequisite condition to test whether the OS kernel parameter "wmem_max" is properly set.</span>
<span style="color: #888888;">INFO: Severity:IGNORABLE</span>
<span style="color: #888888;">INFO: OverallStatus:VERIFICATION_FAILED</span>
<span style="color: #888888;">INFO: *********************************************</span>
<span style="color: #888888;">INFO: OS Kernel Parameter: aio-max-nr: This is a prerequisite condition to test whether the OS kernel parameter "aio-max-nr" is properly set.</span>
<span style="color: #888888;">INFO: Severity:IGNORABLE</span>
<span style="color: #888888;">INFO: OverallStatus:VERIFICATION_FAILED</span>
<span style="color: #888888;">INFO: -----------------End of failed Tasks List----------------</span>
</pre>
</div>
<br />
Percebemos que falhamos na maioria dos parâmetros de Kernel. Propositalmente, claro :)<br />
<br />
Bem, vamos corrigi-los.<br />
<b><br /></b>
<b>Configuração dos Parâmetros de Kernel.</b><br />
<br />
Na <a href="http://docs.oracle.com/database/121/LADBI/app_manual.htm#CIHGDACA" target="_blank">documentação</a> encontramos os valores mínimos para os parâmetros de kernel:<br />
<br />
<i>semmsl 250</i><br />
<i>semmns 32000</i><br />
<i>semopm 100</i><br />
<i>semmni 128</i><br />
<i>shmmax (50% da memória fisica em bytes)</i><br />
<i>shmall (50% do tamanho da memória fisica em páginas)</i><br />
<i>shmmni 4096</i><br />
<i>panic_on_oops 1</i><br />
<i>file-max 6815744</i><br />
<i>aio-max-nr 1048576</i><br />
<i>ip_local_port_range (Minimum: 9000 Maximum: 65500)</i><br />
<i>rmem_default 262144</i><br />
<i>rmem_max 4194304</i><br />
<i>wmem_default 262144</i><br />
<i>wmem_max 1048576</i><br />
<br />
Verifique os valores corrente dos parâmetros de kernel:<br />
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: #888888;">/sbin/sysctl -a | grep sem</span>
<span style="color: #888888;">/sbin/sysctl -a | grep shm</span>
<span style="color: #888888;">/sbin/sysctl -a | grep file-max</span>
<span style="color: #888888;">/sbin/sysctl -a | grep rmem_default</span>
<span style="color: #888888;">/sbin/sysctl -a | grep rmem_max</span>
<span style="color: #888888;">/sbin/sysctl -a | grep wmem_default</span>
<span style="color: #888888;">/sbin/sysctl -a | grep wmem_max</span>
<span style="color: #888888;">/sbin/sysctl -a | grep aio-max-nr</span>
</pre>
</div>
<b><br /></b>
<b>Calculando os valores de SHMMAX e SHMALL</b><br />
<br />
O sistema sendo utilizado neste é exercício é uma Máquina virtual com 1Gb de RAM.<br />
<br />
Dessa forma, podemos calcular o tamanho de SHMMAX (50% da memória física em bytes) da seguinte forma: (Total de Memória RAM em Bytes) / 2<br />
<br />
<i>Exemplo: 1 * 1024 * 1024 * 1024 / 2 = 536870912</i><br />
<br />
O tamanho mínimo de SHMALL é definido como 50% do total de memória física EM PÁGINAS.<br />
<br />
O valor default do tamanho de página no CentOS (e na maioria dos sistemas operacionais linux) é 4K (4096 bytes).<br />
<br />
Se quiser confirmar:<br />
<br /></div>
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: #c65d09; font-weight: bold;">[root@localhost ~]$</span> getconf PAGE_SIZE
<span style="color: #888888;">4096</span>
</pre>
</div>
<br />
Assim, calculamos nosso SHMALL da seguinte forma: (Total de Memória RAM em Bytes / 2 / 4096)<br />
<br />
<i>Exemplo: 536870912 / 4096 = 131072</i><br />
<b><br /></b>
<b>Modificando os parâmetros de kernel.</b><br />
<br />
Como root, usando qualquer editor de texto, criamos ou editamos o arquivo /etc/sysctl.conf e adicionamos ou editamos as seguintes linhas:<br />
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: #888888;">fs.aio-max-nr = 1048576</span>
<span style="color: #888888;">fs.file-max = 6815744</span>
<span style="color: #888888;">kernel.shmall = 131072</span>
<span style="color: #888888;">kernel.shmmax =536870912</span>
<span style="color: #888888;">kernel.shmmni = 4096</span>
<span style="color: #888888;">kernel.sem = 250 32000 100 128</span>
<span style="color: #888888;">net.ipv4.ip_local_port_range = 9000 65500</span>
<span style="color: #888888;">net.core.rmem_default = 262144</span>
<span style="color: #888888;">net.core.rmem_max = 4194304</span>
<span style="color: #888888;">net.core.wmem_default = 262144</span>
<span style="color: #888888;">net.core.wmem_max = 1048576</span>
</pre>
</div>
<br />
Os valores especificados no /etc/sysctl.conf irão persistir assim que o sistema for reiniciado.<br />
<b><br /></b>
<b>Configuração dos limites de shell para o usuário oracle.</b><br />
<br />
Na documentação encontramos os valores mínimos para os limites por usuário:<br />
<br />
<i>nofile Soft: 1024 </i><br />
<i>nofile Hard: 65536</i><br />
<i>nproc Soft: 2047</i><br />
<i>nproc Hard: 16384</i><br />
<i>stack Soft: 10240</i><br />
<i>stack Hard: Entre 10240 e 32768</i><br />
<br />
Verifique os valores corrente dos parâmetros de limite de shell:<br />
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: #888888;">#Como Oracle:</span>
<span style="color: #888888;">#Verifique os limites soft e hard para o file descriptor (nofile):</span>
<span style="color: #888888;">$ ulimit -Sn</span>
<span style="color: #888888;">$ ulimit -Hn</span>
<span style="color: #888888;">#Verifique os limites soft e hard para o numero de processos (nproc):</span>
<span style="color: #888888;">$ ulimit -Su</span>
<span style="color: #888888;">$ ulimit -Hu</span>
<span style="color: #888888;">#Verifique os limites soft e hard para o parâmetro stack (stack):</span>
<span style="color: #888888;">$ ulimit -Ss</span>
<span style="color: #888888;">$ ulimit -Hs</span>
</pre>
</div>
<b><br /></b>
<b>Modificando os parâmetros de limite do usuário oracle.</b><br />
<br />
Como root, usando qualquer editor de texto, criamos ou editamos o arquivo /etc/security/limits.conf e adicionamos ou editamos as seguintes linhas:<br />
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: #888888;">oracle soft nofile 1024</span>
<span style="color: #888888;">oracle hard nofile 65536</span>
<span style="color: #888888;">oracle soft nproc 2047</span>
<span style="color: #888888;">oracle hard nproc 16384</span>
<span style="color: #888888;">oracle soft stack 10240</span>
<span style="color: #888888;">oracle hard stack 32768</span>
</pre>
</div>
<br />
Reinicie o sistema para que os parâmetros de kernel e limite do usuário sejam alterados.<br />
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: #c65d09; font-weight: bold;">[root@localhost ~]#</span> shutdown -r now
</pre>
</div>
<br />
Execute novamente o runInstaller e confirme que todos os pre requisitos mandatórios foram atendidos.<br />
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: #c65d09; font-weight: bold;">[oracle@localhost database]$</span> ./runInstaller -silent -executePrereqs -responseFile <span style="background-color: #fff0f0;">"/home/oracle/install/db_install.rsp"</span>
</pre>
</div>
<br />
Note que alguns parâmetros podem ser ignorados. Como essa é uma instalação laboratorial, optaremos por ignorar alguns parâmetros não mandatórios.<br />
<b><br /></b>
<b>Instalando o Oracle Database R12</b><br />
<br />
Como oracle, vamos executar o instalador, passando o parâmetros -silent, informando que não desejamos utilizar a Interface Gráfica; o parâmetro -responseFile, identificando o arquivo de resposta que contém os parâmetros de instalação; e o parâmetro -ignoreSysPrereqs, informando ao instalador que vamos ignorar os parâmetros marcados como IGNORABLE.<br />
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: #c65d09; font-weight: bold;">[oracle@localhost database]$</span> ./runInstaller -silent -responseFile <span style="background-color: #fff0f0;">"/home/oracle/install/db_install.rsp"</span> -ignoreSysPrereqs -showProgress
</pre>
</div>
<br />
Ao final, o instalador exibirá a seguinte mensagem:<br />
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: #888888;">The installation of Oracle Database 12c was successful.</span>
<span style="color: #888888;">Please check '/u01/app/oraInventory/logs/silentInstall2017-02-14_12-42-42PM.log' for more details.</span>
<span style="color: #888888;">As a root user, execute the following script(s):</span>
<span style="color: #888888;"> 1. /u01/app/oraInventory/orainstRoot.sh</span>
<span style="color: #888888;"> 2. /u01/app/oracle/product/12.1.0/dbhome_1/root.sh</span>
<span style="color: #888888;">Successfully Setup Software.</span>
</pre>
</div>
<br />
Abra uma nova sessão como root e execute os scripts informados pelo log.<br />
<b><br /></b>
<b>Importante:</b> Os scripts não devem ser executados em paralelo. Roda um depois o outro na ordem correta.<br />
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: #c65d09; font-weight: bold;">[root@localhost ~]#</span> /u01/app/oraInventory/orainstRoot.sh
<span style="color: #888888;">Changing permissions of /u01/app/oraInventory.</span>
<span style="color: #888888;">Adding read,write permissions for group.</span>
<span style="color: #888888;">Removing read,write,execute permissions for world.</span>
<span style="color: #888888;">Changing groupname of /u01/app/oraInventory to oinstall.</span>
<span style="color: #888888;">The execution of the script is complete.</span>
</pre>
</div>
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: #c65d09; font-weight: bold;">[root@localhost ~]#</span> /u01/app/oracle/product/12.1.0/dbhome_1/root.sh
<span style="color: #888888;">Check /u01/app/oracle/product/12.1.0/dbhome_1/install/root_localhost.localdomain_2017-02-14_12-53-50.log for the output of root script</span>
</pre>
</div>
<br />
Acompanhe a execução do último script:<br />
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: #c65d09; font-weight: bold;">[root@localhost ~]#</span> tail -f /u01/app/oracle/product/12.1.0/dbhome_1/install/root_localhost.localdomain_2017-02-14_12-53-50.log
<span style="color: #888888;"> Copying dbhome to /usr/local/bin ...</span>
<span style="color: #888888;"> Copying oraenv to /usr/local/bin ...</span>
<span style="color: #888888;"> Copying coraenv to /usr/local/bin ...</span>
<span style="color: #888888;">Creating /etc/oratab file...</span>
<span style="color: #888888;">Entries will be added to the /etc/oratab file as needed by</span>
<span style="color: #888888;">Database Configuration Assistant when a database is created</span>
<span style="color: #888888;">Finished running generic part of root script.</span>
<span style="color: #888888;">Now product-specific root actions will be performed.</span>
</pre>
</div>
<br />
Com isso, concluímos a instalação do software Oracle Database 12c.<br />
<b><br /></b>
<b>Executando um teste rápido para garantirmos que o software foi instalado</b><br />
<b><br /></b>
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: #c65d09; font-weight: bold;">[oracle@localhost bin]$</span> <span style="color: #007020;">export </span><span style="color: #996633;">ORACLE_HOME</span><span style="color: #333333;">=</span>/u01/app/oracle/product/12.1.0/dbhome_1
<span style="color: #c65d09; font-weight: bold;">[oracle@localhost bin]$</span> <span style="color: #007020;">export </span><span style="color: #996633;">ORACLE_SID</span><span style="color: #333333;">=</span>TESTDB
<span style="color: #c65d09; font-weight: bold;">[oracle@localhost bin]$</span> ./sqlplus / as sysdba
<span style="color: #888888;">SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 14 13:00:55 2017</span>
<span style="color: #888888;">Copyright (c) 1982, 2014, Oracle. All rights reserved.</span>
<span style="color: #888888;">Connected to an idle instance.</span>
<span style="color: #888888;">SQL> </span>
</pre>
</div>
<br />
<a href="http://bancotunado.blogspot.com.br/2016/02/criando-um-database-utilizando-dbca-em.html">Clique aqui e acesse o artigo sobre a criação de um banco de dados utilizando DBCA.</a>Anonymoushttp://www.blogger.com/profile/08245597551579856718noreply@blogger.com0tag:blogger.com,1999:blog-1835582969331889889.post-70664525058543805362016-02-29T16:17:00.000-03:002016-03-02T10:06:34.070-03:00Criando um Database utilizando DBCA em Silent ModeA ferramenta gráfica DBCA (Database Configuration Assistant) normalmente é utilizada para criar, modificar ou mesmo excluir um banco de dados. Mas podem haver momentos em que o uso da interface gráfica não seja possível, seja inviável ou até mesmo que você não queira utilizar da ferramenta gráfica (para uma criação rápida de um banco utilizando um template, por exemplo).<br />
<br />
Para esses momentos, o DBCA (e praticamente todos as ferramentas que envolvam a administração de bancos de dados Oracle) te permite utilização sem o acionamento da UI (User Interface), através do modo silencioso (silent mode).<br />
<br />
Neste artigo vamos explorar a criação de um banco de dados standalone cujos datafiles são armazenados no disco local.<br />
<br />
<a name='more'></a><br />
<br />
Em um artigo futuro, vamos exploraremos a criação de um banco de dados RAC utilizando o DBCA.<br />
<br />
Para a criação de um banco de dados através do DBCA em modo silencioso (silent mode) é utilizado o argumento "-silent" como um parâmetro durante a execução do DBCA. Outros parâmetros incluem o global database name, nome da instância, localização dos datafiles, qual template a ser utilizado para a criação do bd e etc.<br />
<br />
Muitos dos parâmetros são opcionais e podem ser ignorados, porém, caso não informe algum dos parâmetros obrigatórios o DBCA irá perguntar qual valor utilizar durante sua execução.<br />
<br />
Também podemos utilizar um arquivo de resposta (Reponse File), que conterá todos os argumentos a serem utilizados durante a execução do DBCA, o que facilita quando há a necessidade de criar muitos bancos parecidos, onde são alterados apenas alguns dos aspectos individuais de cada novo database.<br />
<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><br /></td><td><pre style="line-height: 125%; margin: 0;">dbca -createDatabase -responseFile /localizacao/do/arquivo.rsp
</pre>
</td></tr>
</tbody></table>
</div>
<br />
<b>Criando um novo Banco de Dados utilizando DBCA em Silent Mode</b><br />
<br />
Abaixo há uma explicação de cada um dos argumentos utilizados em nosso exercício:<br />
<br />
<b>-silent </b><br />
Instrui o DBCA a rodar em modo silencioso (Silent Mode)<br />
<br />
<b>-createDatabase</b><br />
Informa ao DBCA que estamos criando um novo banco de dados.<br />
<br />
<b>-templateName</b><br />
Informa ao DBCA qual template a ser utilizado na criação do novo banco de dados. O Template General_Purpose é um template default, já existente na biblioteca do DBCA.<br />
<br />
A vantagem da utilização de templates é que você não precisa especificar as muitas outras opções para a criação do banco, como a localização dos datafiles e controlfiles por exemplo.<br />
<br />
O DBCA irá pegar esses valores do template e, caso você informe algum parâmetro diferente dos que existem no template, fará com que o DBCA utilize o valor do argumento passado por você, ignorando o que está no template.<br />
<br />
<b>-gdbname</b><br />
O Global Database Name de nosso novo banco de dados.<br />
<br />
<b>-sid</b><br />
O nome da instância que será criada e iniciada durante o processo.<br />
<br />
<b>-responseFile</b><br />
Informamos ao DBCA que não vamos utilizar um response file<br />
<br />
<b>-characterSet</b><br />
Estamos utilizando o characterSet AL32UTF8.<br />
<br />
<b>-totalMemory</b><br />
O Total de memória que será alocada (em MB) para este novo banco de dados.<br />
<br />
<b>-emConfiguration</b><br />
Informamos ao DBCA que não queremos que o serviço do Enterprise Manager seja iniciado.<br />
<br />
<i><span style="font-size: x-small;">*Verifique ao final deste artigo uma lista com todos os argumentos que podem ser utilizados na criação do banco.</span></i><br />
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"></pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #c65d09; font-weight: bold;">[oracle@localhost dbhome_1]$</span> <span style="color: #007020;">export </span><span style="color: #996633;">ORACLE_HOME</span><span style="color: #333333;">=</span>/u01/app/oracle/product/12.1.0/dbhome_1
<span style="color: #c65d09; font-weight: bold;">[oracle@localhost dbhome_1]$</span> <span style="color: #007020;">export </span><span style="color: #996633;">ORACLE_BASE</span><span style="color: #333333;">=</span>/u01/app/oracle<span style="color: #333333;">[</span>oracle@localhost dbhome_1<span style="color: #333333;">]</span><span style="color: #996633;">$ </span>
<span style="color: #c65d09; font-weight: bold;">[oracle@localhost dbhome_1]$</span> dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname STUDYDB -sid STUDYDB -responseFile NO_VALUE -characterSet AL32UTF8 -totalMemory 500 -emConfiguration NONE
<span style="color: #888888;">Enter SYS user password: </span>
<span style="color: #888888;"> </span>
<span style="color: #888888;">Enter SYSTEM user password: </span>
<span style="color: #888888;"> </span>
<span style="color: #888888;">Copying database files</span>
<span style="color: #888888;">1% complete</span>
<span style="color: #888888;">3% complete</span>
<span style="color: #888888;">37% complete</span>
<span style="color: #888888;">Creating and starting Oracle instance</span>
<span style="color: #888888;">40% complete</span>
<span style="color: #888888;">45% complete</span>
<span style="color: #888888;">50% complete</span>
<span style="color: #888888;">55% complete</span>
<span style="color: #888888;">56% complete</span>
<span style="color: #888888;">60% complete</span>
<span style="color: #888888;">62% complete</span>
<span style="color: #888888;">Completing Database Creation</span>
<span style="color: #888888;">66% complete</span>
<span style="color: #888888;">70% complete</span>
<span style="color: #888888;">73% complete</span>
<span style="color: #888888;">85% complete</span>
<span style="color: #888888;">96% complete</span>
<span style="color: #888888;">100% complete</span>
<span style="color: #888888;">Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/STUDYDB/STUDYDB.log" for further details.</span>
</pre>
</td></tr>
</tbody></table>
</div>
<br />
<b>Lista de parâmetros utilizados na criação de um novo banco de dados com DBCA:</b><br />
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><br /></td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #c65d09; font-weight: bold;">[oracle@localhost ~]$</span> dbca -help
<span style="color: #888888;">dbca [-silent | -progressOnly] {<command> <options> } | { [<command> [options] ] -responseFile <response file > } [-continueOnNonFatalErrors <true | false>]</span>
<span style="color: #888888;"> <command> : -createDatabase | -configureDatabase | -createTemplateFromDB | -createCloneTemplate | -generateScripts | -deleteDatabase | -createPluggableDatabase | -unplugDatabase | -deletePluggableDatabase | -configurePluggableDatabase</span>
</pre>
</td></tr>
</tbody></table>
</div>
<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><br /></td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #c65d09; font-weight: bold;">[oracle@localhost ~]$</span> dbca -createDatabase -help
<span style="color: #888888;">Create a database by specifying the following parameters:</span>
<span style="color: #888888;"> -createDatabase</span>
<span style="color: #888888;"> -templateName <name of an existing template in default location or the complete template path></span>
<span style="color: #888888;"> [-cloneTemplate]</span>
<span style="color: #888888;"> -gdbName <global database name></span>
<span style="color: #888888;"> [-ignorePreReqs] <ignore prerequisite checks for current operation></span>
<span style="color: #888888;"> [-sid <database system identifier>]</span>
<span style="color: #888888;"> [-createAsContainerDatabase <true|false>]</span>
<span style="color: #888888;"> [-numberOfPDBs <Number of Pluggable databases to be created, default is 0>]</span>
<span style="color: #888888;"> [-pdbName <New Pluggable Database Name>]</span>
<span style="color: #888888;"> [-pdbAdminPassword <PDB Administrator user Password, required only when creating new PDB>]</span>
<span style="color: #888888;"> [-sysPassword <SYS user password>]</span>
<span style="color: #888888;"> [-systemPassword <SYSTEM user password>]</span>
<span style="color: #888888;"> [-emConfiguration <DBEXPRESS|CENTRAL|BOTH|NONE>]</span>
<span style="color: #888888;"> -dbsnmpPassword <DBSNMP user password></span>
<span style="color: #888888;"> [-omsHost <EM management server host name></span>
<span style="color: #888888;"> -omsPort <EM management server port number></span>
<span style="color: #888888;"> -emUser <EM Admin username to add or modify targets></span>
<span style="color: #888888;"> -emPassword <EM Admin user password></span>
<span style="color: #888888;"> -emExpressPort <EM Database Express port number>]]</span>
<span style="color: #888888;"> [-dvConfiguration <true | false Specify "true" to configure and enable Database Vault </span>
<span style="color: #888888;"> -dvUserName <Specify Database Vault Owner user name></span>
<span style="color: #888888;"> -dvUserPassword <Specify Database Vault Owner password></span>
<span style="color: #888888;"> -dvAccountManagerName <Specify separate Database Vault Account Manager ></span>
<span style="color: #888888;"> -dvAccountManagerPassword <Specify Database Vault Account Manager password>]</span>
<span style="color: #888888;"> [-olsConfiguration <true | false Specify "true" to configure and enable Oracle Label Security ></span>
<span style="color: #888888;"> [-datafileDestination <destination directory for all database files.> | </span>
<span style="color: #888888;"> -datafileNames <a text file containing database objects such as controlfiles, tablespaces, redo log files and spfile to their corresponding raw device file names mappings in name=value format.>]</span>
<span style="color: #888888;"> [-redoLogFileSize <size of each redo log file in megabytes>]</span>
<span style="color: #888888;"> [-recoveryAreaDestination <destination directory for all recovery files. Specify "NONE" for disabling Fast Recovery Area.>]</span>
<span style="color: #888888;"> [-datafileJarLocation <location of the data file jar, used only for clone database creation>]</span>
<span style="color: #888888;"> [-storageType < FS | ASM > </span>
<span style="color: #888888;"> [-asmsnmpPassword <ASMSNMP password for ASM monitoring>]</span>
<span style="color: #888888;"> -diskGroupName <database area disk group name></span>
<span style="color: #888888;"> -recoveryGroupName <recovery area disk group name></span>
<span style="color: #888888;"> [-characterSet <character set for the database>]</span>
<span style="color: #888888;"> [-nationalCharacterSet <national character set for the database>]</span>
<span style="color: #888888;"> [-registerWithDirService <true | false> </span>
<span style="color: #888888;"> -dirServiceUserName <user name for directory service></span>
<span style="color: #888888;"> -dirServicePassword <password for directory service ></span>
<span style="color: #888888;"> -walletPassword <password for database wallet >]</span>
<span style="color: #888888;"> [-listeners <list of listeners to configure the database with>]</span>
<span style="color: #888888;"> [-variablesFile <file name for the variable-value pair for variables in the template>]]</span>
<span style="color: #888888;"> [-variables <comma separated list of name=value pairs>]</span>
<span style="color: #888888;"> [-initParams <comma separated list of name=value pairs>]</span>
<span style="color: #888888;"> [-sampleSchema <true | false> ]</span>
<span style="color: #888888;"> [-memoryPercentage <percentage of physical memory for Oracle>]</span>
<span style="color: #888888;"> [-automaticMemoryManagement <true | false> ]</span>
<span style="color: #888888;"> [-totalMemory <memory allocated for Oracle in MB>]</span>
<span style="color: #888888;"> [-databaseType <MULTIPURPOSE|DATA_WAREHOUSING|OLTP>]]</span>
</pre>
</td></tr>
</tbody></table>
</div>
<br />Anonymoushttp://www.blogger.com/profile/08245597551579856718noreply@blogger.com0tag:blogger.com,1999:blog-1835582969331889889.post-38560176505583845662016-02-28T16:31:00.001-03:002016-03-02T10:06:45.794-03:00Oracle® 12c Database Configuration Assistant (DBCA) travando no CentOS 7 / VirtualBox 5Recentemente fiz a instalação do CentOS 7 virtualizado em um VirtualBox 5.0.14 rodando sobre o Ubuntu 14.04 para estudos e testes.<br />
<br />
Logo após isso, fiz a instalação do Oracle 12c (12.0.1.2) em minha nova máquina virtual. A instalação correu bem até iniciar o DBCA para criar o banco de dados de testes.<br />
<br />
O DBCA ficou sem resposta, e travou enquanto exibia a splash screen.<br />
<a name='more'></a><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjrR5XDJ9oKjs1rSvEC5hBbwYh-LeJFv0Rkb7RSHLNFRoKVItQXcgXlYy7Gm9I0sYRr_SXltm4BnorKhgijiqCt14Qev6qSJBSpmn2lM_knavisFM8d8__v1JPlgKunt_Lw7d83_XORQQI/s1600/DBCA+Splash+Screen+2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="480" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjrR5XDJ9oKjs1rSvEC5hBbwYh-LeJFv0Rkb7RSHLNFRoKVItQXcgXlYy7Gm9I0sYRr_SXltm4BnorKhgijiqCt14Qev6qSJBSpmn2lM_knavisFM8d8__v1JPlgKunt_Lw7d83_XORQQI/s640/DBCA+Splash+Screen+2.png" width="640" /></a></div>
<br />
O arquivo de log $ORACLE_BASE/cfgtoollogs/dbca/trace.log_OraDB12Home1_<timestamp> mostrava o seguinte:<br />
<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #888888;">[main] [ 2016-02-28 09:44:15.717 EST ] [Host.<init>:1090] Begin tracing..</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:16.835 EST ] [UIHost.createHelpSet:485] HelpSetParseExceptionjava.lang.NullPointerException</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:18.418 EST ] [Host.checkIfBigClusterAndHubNode:1710] Not a cluster environment: exiting BigCluster Check</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:18.434 EST ] [InventoryUtil.getOUIInvSession:349] setting OUI READ level to ACCESSLEVEL_READ_LOCKLESS</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:18.435 EST ] [InventoryUtil.isCRSHome:386] Homeinfo /u01/app/oracle/product/12.1.0/dbhome_1,1</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:18.814 EST ] [Host.validateGridHome:3878] Validation false</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:18.815 EST ] [Host.startOperation:2395] Source db null</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:18.815 EST ] [Host.startOperation:2396] GDB Name null</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:18.855 EST ] [Host.startOperation:2397] MgmtDB sid -MGMTDB</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:18.855 EST ] [Host.startOperation:2398] MgmtDB name _mgmtdb</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:18.869 EST ] [OracleHome.getVersion:991] OracleHome.getVersion called. Current Version: null</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:18.872 EST ] [InventoryUtil.getOUIInvSession:349] setting OUI READ level to ACCESSLEVEL_READ_LOCKLESS</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:18.872 EST ] [OracleHome.getVersion:1010] Homeinfo /u01/app/oracle/product/12.1.0/dbhome_1,1</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.039 EST ] [OracleHome.getVersion:1038] OracleHome.server.getVersion Version: 12.1.0.2.0</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.039 EST ] [OracleHome.getVersion:1059] Current Version From Inventory: 12.1.0.2.0</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.040 EST ] [OracleHome.getVersion:991] OracleHome.getVersion called. Current Version: 12.1.0.2.0</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.040 EST ] [OracleHome.getVersion:1059] Current Version From Inventory: 12.1.0.2.0</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.041 EST ] [CommonUtils.createPasswordFile:1243] calling new orapwd for 11.1 or higher</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.041 EST ] [OracleHome.getVersion:991] OracleHome.getVersion called. Current Version: 12.1.0.2.0</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.041 EST ] [OracleHome.getVersion:1059] Current Version From Inventory: 12.1.0.2.0</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.042 EST ] [OracleHome.getVersion:991] OracleHome.getVersion called. Current Version: 12.1.0.2.0</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.042 EST ] [OracleHome.getVersion:1059] Current Version From Inventory: 12.1.0.2.0</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.042 EST ] [CommonUtils.getPasswordFileCreateCmd:1182] for new orapwd for 11.1 or higher</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.049 EST ] [OracleHome.getVersion:991] OracleHome.getVersion called. Current Version: 12.1.0.2.0</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.049 EST ] [OracleHome.getVersion:1059] Current Version From Inventory: 12.1.0.2.0</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.050 EST ] [OracleHome.getVersion:991] OracleHome.getVersion called. Current Version: 12.1.0.2.0</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.050 EST ] [OracleHome.getVersion:1059] Current Version From Inventory: 12.1.0.2.0</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.050 EST ] [CommonUtils.getPasswordFileCreateCmd:1213] /u01/app/oracle/product/12.1.0/dbhome_1/bin/orapwd</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.051 EST ] [CommonUtils.getPasswordFileCreateCmd:1213] file=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwDBUA4418856</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.051 EST ] [CommonUtils.getPasswordFileCreateCmd:1213] force=y</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.051 EST ] [CommonUtils.getPasswordFileCreateCmd:1213] format=12</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.052 EST ] [OsUtilsBase.execProg:2123] beginning execProg with input array.</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.186 EST ] [OsUtilsBase.execProg:2160] finished execProg with input array. Status:0</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.186 EST ] [OracleHome.initOptionsStopOnError:1356] Initializing Database Options with for dummy sid=DBUA4418856 using initfile=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initDBUA4418856.ora using pwdfile=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwDBUA4418856</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.207 EST ] [OracleHome.getVersion:991] OracleHome.getVersion called. Current Version: 12.1.0.2.0</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.208 EST ] [OracleHome.getVersion:1059] Current Version From Inventory: 12.1.0.2.0</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.208 EST ] [OracleHome.getVersion:991] OracleHome.getVersion called. Current Version: 12.1.0.2.0</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.209 EST ] [OracleHome.getVersion:1059] Current Version From Inventory: 12.1.0.2.0</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.209 EST ] [OracleHome.getVersion:991] OracleHome.getVersion called. Current Version: 12.1.0.2.0</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.210 EST ] [OracleHome.getVersion:1059] Current Version From Inventory: 12.1.0.2.0</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.210 EST ] [SQLPlusEngine.getCmmdParams:225] m_home 12.1.0.2.0</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.211 EST ] [SQLPlusEngine.getCmmdParams:226] version > 112 true</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.211 EST ] [OsUtilsBase.getBaseFromOrabase:659] oraBaseUtility /u01/app/oracle/product/12.1.0/dbhome_1/bin/orabase</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.212 EST ] [OsUtilsBase.getBaseFromOrabase:668] cmds: /u01/app/oracle/product/12.1.0/dbhome_1/bin/orabase</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.213 EST ] [OsUtilsBase.getBaseFromOrabase:672] envs: ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.230 EST ] [OsUtilsBase.getBaseFromOrabase:682] baseLocation from orabase </span>
<span style="color: #888888;">/u01/app/oracle</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.231 EST ] [OsUtilsBase.getBaseFromOrabase:707] orabaseLocation= /u01/app/oracle</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.232 EST ] [SQLEngine.getEnvParams:602] Default NLS_LANG: AMERICAN_AMERICA.AL32UTF8</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.233 EST ] [SQLEngine.getEnvParams:612] NLS_LANG: AMERICAN_AMERICA.AL32UTF8</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.249 EST ] [SQLEngine.initialize:358] Execing SQLPLUS/SVRMGR process...</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.255 EST ] [SQLEngine.initialize:395] m_bReaderStarted: false</span>
<span style="color: #888888;">[main] [ 2016-02-28 09:44:19.256 EST ] [SQLEngine.initialize:399] Starting Reader Thread... </span>
<span style="color: red;"><b>[main] [ 2016-02-28 09:44:20.400 EST ] [OracleHome.initOptionsStopOnError:1370] executing: startup nomount pfile='/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initDBUA4418856.ora'</b></span>
</pre>
</td></tr>
</tbody></table>
</div>
<br />
Aparentemente o DBCA travava ao startar o banco através do sqlplus.<br />
<br />
Realizei um pequeno teste, criando um init.ora e startando o banco com sqlplus:<br />
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><br /></td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #c65d09; font-weight: bold;">[oracle@localhost ~]$</span> <span style="color: #007020;">export </span><span style="color: #996633;">ORACLE_HOME</span><span style="color: #333333;">=</span>/u01/app/oracle/product/12.1.0/dbhome_1
<span style="color: #c65d09; font-weight: bold;">[oracle@localhost ~]$</span> <span style="color: #007020;">export </span><span style="color: #996633;">PATH</span><span style="color: #333333;">=</span><span style="color: #996633;">$ORACLE_HOME</span>/bin:<span style="color: #996633;">$PATH</span>
<span style="color: #c65d09; font-weight: bold;">[oracle@localhost ~]$</span> <span style="color: #007020;">cd</span> <span style="color: #996633;">$ORACLE_HOME</span>
<span style="color: #c65d09; font-weight: bold;">[oracle@localhost dbhome_1]$</span> <span style="color: #007020;">cd </span>dbs
<span style="color: #c65d09; font-weight: bold;">[oracle@localhost dbs]$</span> ls -ltr
<span style="color: #888888;">total 16</span>
<span style="color: #888888;">-rw-r--r--. 1 oracle oinstall 2992 Feb 3 2012 init.ora</span>
<span style="color: #888888;">-rw-r-----. 1 oracle oinstall 0 Feb 23 18:32 lkinstDBUA3245942</span>
<span style="color: #888888;">-rw-r-----. 1 oracle oinstall 0 Feb 23 18:36 lkinstDBUA3608884</span>
<span style="color: #888888;">-rw-r-----. 1 oracle oinstall 7680 Feb 23 18:57 orapwSTUDYDB.old</span>
<span style="color: #888888;">-rw-r-----. 1 oracle oinstall 0 Feb 23 18:57 lkinstSTUDYDB.old</span>
<span style="color: #888888;">-rw-r--r--. 1 oracle oinstall 683 Feb 23 21:49 initSTUDYDB.ora</span>
<span style="color: #888888;">-rw-r-----. 1 oracle oinstall 0 Feb 23 21:51 lkinstSTUDYDB</span>
<span style="color: #c65d09; font-weight: bold;">[oracle@localhost dbs]$</span> <span style="color: #007020;">export </span><span style="color: #996633;">ORACLE_SID</span><span style="color: #333333;">=</span>STUDYDB
<span style="color: #c65d09; font-weight: bold;">[oracle@localhost dbs]$</span> sqlplus / as sysdba
<span style="color: #888888;">SQL*Plus: Release 12.1.0.2.0 Production on Sun Feb 28 09:38:13 2016</span>
<span style="color: #888888;">Copyright (c) 1982, 2014, Oracle. All rights reserved.</span>
<span style="color: #888888;">Connected to an idle instance.</span>
<span style="color: #888888;">SQL> startup nomount</span>
</pre>
</td></tr>
</tbody></table>
</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjXi745OsmBDnH1eh-fZgKJk_XcmJxcMpLkPJoTOinTQqSfCJj53nPx_OO4QTY5_1t0pOUptx5eq2SYMO3N42nSbEtabYgOIM07QhfVUNeN9-BKenR4onzMhJr20hh0K3yQH_oLUI1lZvg/s1600/sqlplus+hang.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="440" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjXi745OsmBDnH1eh-fZgKJk_XcmJxcMpLkPJoTOinTQqSfCJj53nPx_OO4QTY5_1t0pOUptx5eq2SYMO3N42nSbEtabYgOIM07QhfVUNeN9-BKenR4onzMhJr20hh0K3yQH_oLUI1lZvg/s640/sqlplus+hang.png" width="640" /></a></div>
<br />
<br />
Da mesma forma, o cursor jamais retornava.<br />
<br />
O alert.log exibia o seguinte:<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><br /></td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #888888;">Sun Feb 28 09:38:28 2016</span>
<span style="color: #888888;">Starting ORACLE instance (normal) (OS id: 5593)</span>
<span style="color: #888888;">Sun Feb 28 09:38:28 2016</span>
<span style="color: #888888;">CLI notifier numLatches:5 maxDescs:521</span>
<span style="color: #888888;">Sun Feb 28 09:38:28 2016</span>
<span style="color: #888888;">**********************************************************************</span>
<span style="color: #888888;">Sun Feb 28 09:38:28 2016</span>
<span style="color: #888888;">Dump of system resources acquired for SHARED GLOBAL AREA (SGA) </span>
<span style="color: #888888;">Sun Feb 28 09:38:28 2016</span>
<span style="color: #888888;"> Per process system memlock (soft) limit = 64K</span>
<span style="color: #888888;">Sun Feb 28 09:38:28 2016</span>
<span style="color: #888888;"> Expected per process system memlock (soft) limit to lock</span>
<span style="color: #888888;"> SHARED GLOBAL AREA (SGA) into memory: 500M</span>
<span style="color: #888888;">Sun Feb 28 09:38:28 2016</span>
<span style="color: #888888;"> Available system pagesizes:</span>
<span style="color: #888888;"> 4K, 2048K </span>
<span style="color: #888888;">Sun Feb 28 09:38:28 2016</span>
<span style="color: #888888;"> Supported system pagesize(s):</span>
<span style="color: #888888;">Sun Feb 28 09:38:28 2016</span>
<span style="color: #888888;"> PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s)</span>
<span style="color: #888888;">Sun Feb 28 09:38:28 2016</span>
<span style="color: #888888;"> 4K Configured 128003 128003 NONE</span>
<span style="color: #888888;">Sun Feb 28 09:38:28 2016</span>
<span style="color: #888888;"> Reason for not supporting certain system pagesizes: </span>
<span style="color: #888888;">Sun Feb 28 09:38:28 2016</span>
<span style="color: #888888;"> 2048K - Dynamic allocate and free memory regions</span>
<span style="color: #888888;">Sun Feb 28 09:38:28 2016</span>
<span style="color: #888888;">RECOMMENDATION:</span>
<span style="color: #888888;">Sun Feb 28 09:38:28 2016</span>
<span style="color: #888888;"> 1. Increase per process memlock (soft) limit to at least 500MB</span>
<span style="color: #888888;"> to lock 100% of SHARED GLOBAL AREA (SGA) pages into physical memory</span>
<span style="color: #888888;">Sun Feb 28 09:38:28 2016</span>
<span style="color: #888888;">**********************************************************************</span>
</pre>
</td></tr>
</tbody></table>
</div>
<br />
Fiz um double check em todos os pre-requisitos da instalação do banco, incluindo os parâmetros de kernel, e tudo estava dentro dos conformes.<br />
<br />
Após algumas tentativas e investigação, eu voltei minha atenção para o virtualbox. Fiz alterações em diversos parâmetros do virtualbox, até encontrar o culpado: O parâmetro <i>"Interface de paravirtualização"</i> na aba <i>"Aceleração"</i>.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiqXylehjMcrXLdmIWXSy_3r4ALvh7rU_Bgok5_9owC7tZtgs8J0uDIik7dTgniMz5pT5MG0uSn1JKoSIhEDgfBvniNQV7oUEZiLm47xAcps0JTsuDUMm0BKMOSjcXnl_20TvMyjOKBVwI/s1600/vmBox1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="296" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiqXylehjMcrXLdmIWXSy_3r4ALvh7rU_Bgok5_9owC7tZtgs8J0uDIik7dTgniMz5pT5MG0uSn1JKoSIhEDgfBvniNQV7oUEZiLm47xAcps0JTsuDUMm0BKMOSjcXnl_20TvMyjOKBVwI/s640/vmBox1.png" width="640" /></a></div>
<br />
Alterando de "Padrão" para "Nenhuma" resolveu o problema:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEglI_1dx4H1CwwzTb4b2Lvy4pRijrOOp1N7gPVkzWtovInHXCerAOhQQw7CIPAJbl9d8yEjMe4lcwWYOf6ll-47I2dDbRTVeqTMdO4-Zf21gP3YcsnoP4mYbQTz_nFt8ZZbUiNwJhLQ2yQ/s1600/vmBox2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="296" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEglI_1dx4H1CwwzTb4b2Lvy4pRijrOOp1N7gPVkzWtovInHXCerAOhQQw7CIPAJbl9d8yEjMe4lcwWYOf6ll-47I2dDbRTVeqTMdO4-Zf21gP3YcsnoP4mYbQTz_nFt8ZZbUiNwJhLQ2yQ/s640/vmBox2.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><br /></td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #c65d09; font-weight: bold;">[oracle@localhost dbhome_1]$</span> <span style="color: #007020;">export </span><span style="color: #996633;">ORACLE_HOME</span><span style="color: #333333;">=</span>/u01/app/oracle/product/12.1.0/dbhome_1
<span style="color: #c65d09; font-weight: bold;">[oracle@localhost dbhome_1]$</span> <span style="color: #007020;">export </span><span style="color: #996633;">PATH</span><span style="color: #333333;">=</span><span style="color: #996633;">$ORACLE_HOME</span>/bin:<span style="color: #996633;">$PATH</span>
<span style="color: #c65d09; font-weight: bold;">[oracle@localhost dbhome_1]$</span> <span style="color: #007020;">export </span><span style="color: #996633;">ORACLE_SID</span><span style="color: #333333;">=</span>STUDYDB
<span style="color: #c65d09; font-weight: bold;">[oracle@localhost dbhome_1]$</span> sqlplus / as sysdba
<span style="color: #888888;">SQL*Plus: Release 12.1.0.2.0 Production on Sun Feb 28 09:54:18 2016</span>
<span style="color: #888888;">Copyright (c) 1982, 2014, Oracle. All rights reserved.</span>
<span style="color: #888888;">Connected to an idle instance.</span>
<span style="color: #888888;">SQL> startup nomount</span>
<span style="color: #888888;">ORACLE instance started.</span>
<span style="color: #888888;">Total System Global Area 524288000 bytes</span>
<span style="color: #888888;">Fixed Size 2926320 bytes</span>
<span style="color: #888888;">Variable Size 436209936 bytes</span>
<span style="color: #888888;">Database Buffers 79691776 bytes</span>
<span style="color: #888888;">Redo Buffers 5459968 bytes</span>
<span style="color: #888888;">SQL> </span>
</pre>
</td></tr>
</tbody></table>
</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhSmjNqRNN7jN0VvD7iluzVN2RcRJqvImI_MnuLpc6ElL-wXBeNbBaFfVcl7X3iP9JdPUr3c2iAzPc0UEXqEqWXQ4CADQDuC5UOsCxv0xgQrVeJDX3hvp5bGSk8mqBRWzPLyNMF52l9wMQ/s1600/sqlplus+ok.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="440" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhSmjNqRNN7jN0VvD7iluzVN2RcRJqvImI_MnuLpc6ElL-wXBeNbBaFfVcl7X3iP9JdPUr3c2iAzPc0UEXqEqWXQ4CADQDuC5UOsCxv0xgQrVeJDX3hvp5bGSk8mqBRWzPLyNMF52l9wMQ/s640/sqlplus+ok.png" width="640" /></a></div>
<br />Anonymoushttp://www.blogger.com/profile/08245597551579856718noreply@blogger.com0tag:blogger.com,1999:blog-1835582969331889889.post-22645494187804774592016-02-19T20:18:00.000-02:002016-03-02T10:06:59.070-03:00USM driver install actions failed - ACFS-9459: ADVM/ACFS is not supported on this OS version Durante upgrade do Oracle Grid Infrastructure: rootupgrade.sh / root.sh (11.2.0.4).Recentemente um problema durante o upgrade do GI tirou meu sono.<br />
<br />
A versão destino seria a 11.2.0.4 (mais recente para a 11gR2) partindo da versão 11.2.0.3. O sistema operacional em questão é o Oracle Enterprise Linux 5.11 no kernel 2.6.18-406.0.0.0.1.el5<br />
<br />
Realizei todo a pré checagem dos requisitos minimos, bem como a execução do cluvfy mostrou que o ambiente estava pronto para o upgrade.<br />
<br />
Tudo correu bem durante a instalação da nova versão do Grid Infranstructure, até que chegou o momento da execução do rootupgrade.sh que faria o startup do software na nova versão, shutdown da antiga e o upgrade em si de todos os seus componentes.<br />
<br />
A execução do rootupgrade.sh falhou com o seguinte erro:<br />
<br />
<div>
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;">1
2</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #888888;">USM driver install actions failed</span>
<span style="color: #888888;">/u01/app/11.2.0.4/grid/perl/bin/perl -I/u01/app/11.2.0.4/grid/perl/lib -I/u01/app/11.2.0.4/grid/crs/install /u01/app/11.2.0.4/grid/crs/install/rootcrs.pl execution failed</span>
</pre>
</td></tr>
</tbody></table>
</div>
<br />
<a name='more'></a><br />
<br />
O processo falha durante a instalação do ACFS.</div>
<br />
O log detalhado rootcrs_hostname.log (localizado em $GRID_HOME/cfgtoollogs) mostrava as seguinte informações:<br />
<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #888888;">2015-11-15 08:37:14: The 'ROOTCRS_ACFSINST' is either in START/FAILED state</span>
<span style="color: #888888;">2015-11-15 08:37:14: Executing '/u01/app/11.2.0.4/grid/bin/acfsroot install'</span>
<span style="color: #888888;">2015-11-15 08:37:14: Executing cmd: /u01/app/11.2.0.4/grid/bin/acfsroot install</span>
<span style="color: #888888;">2015-11-15 08:37:32: Command output:</span>
<span style="color: #888888;"><span style="color: #888888;">> ACFS-9459: ADVM/ACFS is not supported on this OS version: </span><b><span style="color: red;">'2.6.18-274.3.1.0.1.el5'</span></b></span>
<span style="color: #888888;">>End Command output</span>
<span style="color: #888888;">2015-11-15 08:37:32: /u01/app/11.2.0.4/grid/bin/acfsroot install ... failed</span>
<span style="color: #888888;">2015-11-15 08:37:32: USM driver install status is 0</span>
<span style="color: #888888;">2015-11-15 08:37:32: USM driver install actions failed</span>
<span style="color: #888888;">2015-11-15 08:37:32: Running as user grid: /u01/app/11.2.0.4/grid/bin/cluutil -ckpt -oraclebase /u01/app/grid -writeckpt -name ROOTCRS_ACFSINST -state FAIL</span>
<span style="color: #888888;">2015-11-15 08:37:32: s_run_as_user2: Running /bin/su grid -c ' /u01/app/11.2.0.4/grid/bin/cluutil -ckpt -oraclebase /u01/app/grid -writeckpt -name ROOTCRS_ACFSINST -state FAIL '</span>
</pre>
</td></tr>
</tbody></table>
</div>
<br />
O log diz que o ACFS não é suportado na versão corrente do kernel utilizado, e logo notei a discrepância entre a versão do kernel identificada pelo rootupgrade e a versão que meu Sistema Operacional indica através do uname.<br />
<div>
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;">1
2</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #c65d09; font-weight: bold;">[grid@rac301 grid]$</span> uname -r
<span style="color: blue;"><b>2.6.18-406.0.0.0.1.el5</b></span>
</pre>
</td></tr>
</tbody></table>
</div>
<br />
Conversei com o Administrador do sistema operacional que me informou que o kernel havia sido atualizado da versão 2.6.18-274 para 2.6.18-406 utilizando a ferramenta kpslice da Oracle, que permite o upgrade do kernel sem a necessidade de reiniciar o sistema.<br />
<br />
Isso explica o fato do sistema encontrar versões diferentes do kernel:<br />
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;">1
2
3
4
5</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #c65d09; font-weight: bold;">[grid@rac301 grid]$</span> uname -r
<span style="color: #888888;">2.6.18-406.0.0.0.1.el5</span>
<span style="color: #c65d09; font-weight: bold;">[grid@rac301 x86_grid]$</span> /bin/uname -r
<span style="color: #888888;">2.6.18-274.3.1.0.1.el5</span>
</pre>
</td></tr>
</tbody></table>
</div>
<br />
Muito embora a <a href="http://docs.oracle.com/cd/E11882_01/install.112/e47689/pre_install.htm#LADBI1110" target="_blank">documentação</a> diga que a versão mínima do kernel para o Oracle Enterprise Linux 5 Update 5 x86-64 seja 2.6.18, ainda assim o rootupgrade.sh falha e "pensa" que estou utilizando uma versão inferior do kernel 2.6.18.<br />
<br />
Este erro "ADVM/ACFS is not supported on this OS version: '2.6.18-274.3.1.0.1.el5'" está diretamente relacionado às bibliotecas do USM não serem carregadas corretamente ou não serem encontradas, vide notas 1670098.1, 1590701.1 e 1265276.1; e bug 11846686.<br />
<br />
Embora não utilizemos ACFS neste ambiente, o rootupgrade.sh requer uma estrutura de diretórios decente para poder completar o upgrade.<br />
<br />
Verificando a estrutura de diretórios dos drivers do USM, encontrei o seguinte:<br />
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;">1
2
3</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #c65d09; font-weight: bold;">[root@rac301 ~]#</span> ls -l /u01/app/11.2.0.4/grid/install/usm/Oracle/EL5/x86_64
<span style="color: #888888;">total 1</span>
<span style="color: #888888;">drwxr-xr-x 4 grid oinstall 4096 Nov 15 10:08 2.6.18-8</span>
</pre>
</td></tr>
</tbody></table>
</div>
<br />
Com isso, utilizei symbolic links para corrigir a estrutura de pastas de forma que o USM encontrasse uma versão válida do driver para meu kernel:<br />
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #c65d09; font-weight: bold;">[root@btcbosgbdb302 ~]#</span> <span style="color: #007020;">cd</span> /u01/app/11.2.0.4/grid/install/usm/Oracle/EL5/x86_64/
<span style="color: #c65d09; font-weight: bold;">[grid@rac301 x86_64]$</span> ln -s 2.6.18-8 2.6.18-406
<span style="color: #c65d09; font-weight: bold;">[root@rac301 x86_64]#</span> ls -l /u01/app/11.2.0.4/grid/install/usm/Oracle/EL5/x86_64
<span style="color: #888888;">total 2</span>
<span style="color: #888888;">lrwxrwxrwx 1 grid oinstall 8 Nov 15 10:10 2.6.18-406 > 2.6.18-8</span>
<span style="color: #888888;">drwxr-xr-x 4 grid oinstall 4096 Nov 15 10:08 2.6.18-8</span>
<span style="color: #c65d09; font-weight: bold;">[grid@rac301 x86_64]$</span> <span style="color: #007020;">cd </span>2.6.18-8/
<span style="color: #c65d09; font-weight: bold;">[grid@rac301 2.6.18-8]$</span> ls -l
<span style="color: #888888;">total 8</span>
<span style="color: #888888;">drwxr-xr-x 3 grid oinstall 4096 Nov 18 13:47 2.6.18-8.el5-x86_64/</span>
<span style="color: #888888;">drwxr-xr-x 3 grid oinstall 4096 Nov 18 13:47 2.6.18-8.el5xen-x86_64/</span>
<span style="color: #c65d09; font-weight: bold;">[grid@rac301 2.6.18-8]$</span> ln -s 2.6.18-8.el5-x86_64 2.6.18-406.el5-x86_64
<span style="color: #c65d09; font-weight: bold;">[grid@rac301 2.6.18-8]$</span> ln -s 2.6.18-8.el5xen-x86_64 2.6.18-406.el5xen-x86_64
<span style="color: #c65d09; font-weight: bold;">[grid@rac301 2.6.18-8]$</span> ls -l
<span style="color: #888888;">total 8</span>
<span style="color: #888888;">lrwxrwxrwx 1 grid oinstall 19 Nov 18 14:48 2.6.18-406.el5-x86_64 -> 2.6.18-8.el5-x86_64/</span>
<span style="color: #888888;">lrwxrwxrwx 1 grid oinstall 22 Nov 18 14:48 2.6.18-406.el5xen-x86_64 -> 2.6.18-8.el5xen-x86_64/</span>
<span style="color: #888888;">drwxr-xr-x 3 grid oinstall 4096 Nov 18 13:47 2.6.18-8.el5-x86_64/</span>
<span style="color: #888888;">drwxr-xr-x 3 grid oinstall 4096 Nov 18 13:47 2.6.18-8.el5xen-x86_64/</span>
</pre>
</td></tr>
</tbody></table>
</div>
<br />
Logo após isso, rodei novamente o rootupgrade.sh e, novamente o script falhou :'(<br />
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;">1
2</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #888888;">USM driver install actions failed</span>
<span style="color: #888888;">/u01/app/11.2.0.4/grid/perl/bin/perl -I/u01/app/11.2.0.4/grid/perl/lib -I/u01/app/11.2.0.4/grid/crs/install /u01/app/11.2.0.4/grid/crs/install/rootcrs.pl execution failed</span>
</pre>
</td></tr>
</tbody></table>
</div>
<br />
Então, criei o symbolic link também para a versão antiga do Kernel:<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;">1
2
3
4
5
6
7
8
9</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #c65d09; font-weight: bold;">[root@rac301 ~]#</span> <span style="color: #007020;">cd</span> /u01/app/11.2.0.4/grid/install/usm/Oracle/EL5/x86_64/
<span style="color: #c65d09; font-weight: bold;">[grid@rac301 x86_64]$</span> ln -s 2.6.18-8 2.6.18-274
<span style="color: #c65d09; font-weight: bold;">[grid@rac301 x86_64]$</span> <span style="color: #007020;">cd </span>2.6.18-8/
<span style="color: #c65d09; font-weight: bold;">[grid@rac301 2.6.18-8]$</span> ln -s 2.6.18-8.el5-x86_64 2.6.18-274.el5-x86_64
<span style="color: #c65d09; font-weight: bold;">[grid@rac301 2.6.18-8]$</span> ln -s 2.6.18-8.el5xen-x86_64 2.6.18-274.el5xen-x86_64
</pre>
</td></tr>
</tbody></table>
</div>
<br />
E, Bingo !!!<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;">1</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #888888;">Configure Oracle Grid Infrastructure for a Cluster ... succeeded</span>
</pre>
</td></tr>
</tbody></table>
</div>
<br /></div>
Anonymoushttp://www.blogger.com/profile/08245597551579856718noreply@blogger.com0tag:blogger.com,1999:blog-1835582969331889889.post-67022893044780465612012-03-27T12:23:00.000-03:002016-03-02T10:05:51.816-03:00Espaço em disco. Algo que pode te dar uma boa dor-de-cabeça.Muitos já se viram frente à necessidade de mais espaço em uma ou outra tablespace.<br />
<br />
Algo natural no dia-a-dia conforme o crescimento natural do banco de dados.<br />
<br />
Nem sempre isso é um problema caso seu espaço disponível em disco possa suprir sua necessidade de aumento de uma tablespace em particular.<br />
<br />
Este cenário torna-se um problema; Um problemão na verdade; quando o espaço em disco disponível para o crescimento de suas tablespaces é próximo de Zero.<br />
<br />
De onde tirar espaço? Se sua tablespace atingir 100% de espaço em uso, muito provavelmente sua aplicação parará de funcionar devidamente.<br />
<br />
Bem, raramente todas suas tablespaces atingirão 100% de uso ao mesmo tempo, então, por que não liberar algum espaço dessas tablespaces que contém algum espaço livre e incluir este espaço em sua tablespace problemática? Fácil, não? A solução mais óbiva? Bom, nem sempre.<br />
<br />
<a name='more'></a><br />
<br />
O problema dessa manobra está justamente no espaço não utilizado pelos datafiles daquela tablespace com espaço livre.<br />
<br />
Todo datafile possui o que chamamos de "High Water Mark" (Ou Marca D'água do nível Máximo).<br />
<br />
Isso é bem fácil de ilustrar. Imagine um copo com água até um determinado ponto. Então você coloca um canudo neste copo e bebe um pouco dessa água. Naturalmente, no ponto onde havia água antes de ser bebida ficará uma "Marca d´água" onde antes foi o nível máximo de água.<br />
<br />
Transferindo este exemplo aos datafiles de sua tablespace, essa "Marca D'água" será o ponto máximo onde uma vez houveram dados neste datafile.<br />
<br />
Os blocos necessários para gravação em disco neste datafile já foram utilizados e, embora os dados tenham sido apagados, seu espaço já foi requisitado no disco e não pode ser reclamado novamente pelo banco.<br />
<br />
Portanto, apenas por verificar o espaço disponível nos datafiles não quer dizer que será possível reclama-lo por completo ao SO.<br />
<br />
Abaixo explico uma técnica para identificar o espaço livre em suas tablespaces e o espaço livre em seus datafiles, bem como suas respectivas Hight Water Mark.<br />
<br />
<br />
Primeiro identificamos o espaço disponível em banco.<br />
<br />
Este script pode ser utilizado para isso:<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21</pre>
</td><td><pre style="line-height: 125%; margin: 0;">col <span style="color: #aa6600;">"Tablespace"</span> <span style="color: #008800; font-weight: bold;">for</span> a13
col <span style="color: #aa6600;">"Used MB"</span> <span style="color: #008800; font-weight: bold;">for</span> <span style="color: #0000dd; font-weight: bold;">99</span>,<span style="color: #0000dd; font-weight: bold;">999</span>,<span style="color: #0000dd; font-weight: bold;">999</span>
col <span style="color: #aa6600;">"Free MB"</span> <span style="color: #008800; font-weight: bold;">for</span> <span style="color: #0000dd; font-weight: bold;">99</span>,<span style="color: #0000dd; font-weight: bold;">999</span>,<span style="color: #0000dd; font-weight: bold;">999</span>
col <span style="color: #aa6600;">"Total MB"</span> <span style="color: #008800; font-weight: bold;">for</span> <span style="color: #0000dd; font-weight: bold;">99</span>,<span style="color: #0000dd; font-weight: bold;">999</span>,<span style="color: #0000dd; font-weight: bold;">999</span>
<span style="color: #008800; font-weight: bold;">select</span> fs.tablespace_name <span style="color: #aa6600;">"Tablespace"</span>,
(df.totalspace <span style="color: #333333;">-</span> fs.freespace) <span style="color: #aa6600;">"Used MB"</span>,
fs.freespace <span style="color: #aa6600;">"Free MB"</span>,
df.totalspace <span style="color: #aa6600;">"Total MB"</span>,
round(<span style="color: #0000dd; font-weight: bold;">100</span> <span style="color: #333333;">*</span> (fs.freespace <span style="color: #333333;">/</span> df.totalspace)) <span style="color: #aa6600;">"Pct. Free"</span>
<span style="color: #008800; font-weight: bold;">from</span>
(<span style="color: #008800; font-weight: bold;">select</span> tablespace_name,
round(<span style="color: #008800; font-weight: bold;">sum</span>(bytes) <span style="color: #333333;">/</span> <span style="color: #0000dd; font-weight: bold;">1048576</span>) TotalSpace
<span style="color: #008800; font-weight: bold;">from</span> dba_data_files
<span style="color: #008800; font-weight: bold;">group</span> <span style="color: #008800; font-weight: bold;">by</span> tablespace_name) df,
(<span style="color: #008800; font-weight: bold;">select</span> tablespace_name,
round(<span style="color: #008800; font-weight: bold;">sum</span>(bytes) <span style="color: #333333;">/</span> <span style="color: #0000dd; font-weight: bold;">1048576</span>) FreeSpace
<span style="color: #008800; font-weight: bold;">from</span> dba_free_space
<span style="color: #008800; font-weight: bold;">group</span> <span style="color: #008800; font-weight: bold;">by</span> tablespace_name) fs
<span style="color: #008800; font-weight: bold;">where</span> df.tablespace_name <span style="color: #333333;">=</span> fs.tablespace_name
;
</pre>
</td></tr>
</tbody></table>
</div>
<br />
<div>
<br /></div>
<div>
O resultado é algo parecido com isso:</div>
<div>
<br /></div>
<div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">Tablespace Used MB Free MB Total MB Pct. Free</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">------------- ----------- ----------- ----------- ----------</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">SYSAUX <span style="color: red;"><b>1,379</b></span> 161 1,540 10</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">UNDOTBS1 37 1,093 1,130 97</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">TS1 31 29,969 30,000 100</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">USERS 1 119 120 99</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">SYSTEM 717 283 1,000 28</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">UNDOTBS3 27 3,663 3,690 99</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">UNDOTBS2 114 816 930 88</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">7 rows selected.</span></div>
</div>
<div>
<br /></div>
<div>
Notem que o que está na coluna Free MB trata-se do espaço disponível para a tablespace mas não reflete o que já foi escrito e gravado na Hight Water Mark.</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
O procedimento abaixo pose ser utilizado para identificar o tamanho utilizado e disponível de todos os datafiles presentes no banco, informando inclusive até onde podem ser redimensionados.<br />
<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #008800; font-weight: bold;">set</span> verify <span style="color: #008800; font-weight: bold;">off</span>
<span style="color: #008800; font-weight: bold;">column</span> file_name format a50 word_wrapped
<span style="color: #008800; font-weight: bold;">column</span> smallest format <span style="color: #0000dd; font-weight: bold;">999</span>,<span style="color: #0000dd; font-weight: bold;">990</span> heading <span style="color: #aa6600;">"Smallest|Size|Poss."</span>
<span style="color: #008800; font-weight: bold;">column</span> currsize format <span style="color: #0000dd; font-weight: bold;">999</span>,<span style="color: #0000dd; font-weight: bold;">990</span> heading <span style="color: #aa6600;">"Current|Size"</span>
<span style="color: #008800; font-weight: bold;">column</span> savings format <span style="color: #0000dd; font-weight: bold;">999</span>,<span style="color: #0000dd; font-weight: bold;">990</span> heading <span style="color: #aa6600;">"Poss.|Savings"</span>
break <span style="color: #008800; font-weight: bold;">on</span> report
compute <span style="color: #008800; font-weight: bold;">sum</span> <span style="color: #008800; font-weight: bold;">of</span> savings <span style="color: #008800; font-weight: bold;">on</span> report
<span style="color: #888888;">-- Primeiro Bloco: Identifica o tamanho de blksize e armazena em uma variável para uso posterior.</span>
<span style="color: #008800; font-weight: bold;">column</span> value new_val blksize
<span style="color: #008800; font-weight: bold;">select</span> value <span style="color: #008800; font-weight: bold;">from</span> v<span style="background-color: #ffaaaa; color: red;">$</span><span style="color: #008800; font-weight: bold;">parameter</span> <span style="color: #008800; font-weight: bold;">where</span> name <span style="color: #333333;">=</span> <span style="background-color: #fff0f0;">'db_block_size'</span>
<span style="color: #333333;">/</span>
<span style="color: #888888;">-- Segundo bloco. Mostra todos os datafiles do banco e mostra o tamanho utilizado, o tamanho total atual, e até quanto voce pode economizar com ele.</span>
<span style="color: #008800; font-weight: bold;">select</span> file_name,
ceil( (nvl(hwm,<span style="color: #0000dd; font-weight: bold;">1</span>)<span style="color: #333333;">*&&</span>blksize)<span style="color: #333333;">/</span><span style="color: #0000dd; font-weight: bold;">1024</span><span style="color: #333333;">/</span><span style="color: #0000dd; font-weight: bold;">1024</span> ) smallest,
ceil( blocks<span style="color: #333333;">*&&</span>blksize<span style="color: #333333;">/</span><span style="color: #0000dd; font-weight: bold;">1024</span><span style="color: #333333;">/</span><span style="color: #0000dd; font-weight: bold;">1024</span>) currsize,
ceil( blocks<span style="color: #333333;">*&&</span>blksize<span style="color: #333333;">/</span><span style="color: #0000dd; font-weight: bold;">1024</span><span style="color: #333333;">/</span><span style="color: #0000dd; font-weight: bold;">1024</span>) <span style="color: #333333;">-</span>
ceil( (nvl(hwm,<span style="color: #0000dd; font-weight: bold;">1</span>)<span style="color: #333333;">*&&</span>blksize)<span style="color: #333333;">/</span><span style="color: #0000dd; font-weight: bold;">1024</span><span style="color: #333333;">/</span><span style="color: #0000dd; font-weight: bold;">1024</span> ) savings
<span style="color: #008800; font-weight: bold;">from</span> dba_data_files a,
( <span style="color: #008800; font-weight: bold;">select</span> file_id, <span style="color: #008800; font-weight: bold;">max</span>(block_id<span style="color: #333333;">+</span>blocks<span style="color: #333333;">-</span><span style="color: #0000dd; font-weight: bold;">1</span>) hwm
<span style="color: #008800; font-weight: bold;">from</span> dba_extents
<span style="color: #008800; font-weight: bold;">group</span> <span style="color: #008800; font-weight: bold;">by</span> file_id ) b
<span style="color: #008800; font-weight: bold;">where</span> a.file_id <span style="color: #333333;">=</span> b.file_id(<span style="color: #333333;">+</span>)
<span style="color: #333333;">/</span>
</pre>
</td></tr>
</tbody></table>
</div>
<br />
<br />
Tenham Paciência, o resultado pode demorar um pouco dependendo do tamanho de seus datafiles.</div>
<div>
<br /></div>
<div>
O resultado será algo parecido com isso:</div>
<div>
<br /></div>
<div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> Smallest</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> Size Current Poss.</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">FILE_NAME Poss. Size Savings</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">-------------------------------------------------- -------- -------- --------</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">+DG_DATA_BPP/bpp/datafile/system.259.765636177 718 1,000 282</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">+DG_DATA_BPP/bpp/datafile/sysaux.260.765636181 <b><span style="color: red;">1,450</span></b> 1,540 90</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">+DG_DATA_BPP/bpp/datafile/undotbs2.263.765636193 118 930 812</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">+DG_DATA_BPP/bpp/datafile/undotbs3.264.765636195 31 3,690 3,659</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">+DG_DATA_BPP/bpp/datafile/ts1.271.765711963 32 30,000 29,968</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">+DG_DATA_BPP/bpp/datafile/undotbs1.261.765636187 66 1,130 1,064</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">+DG_DATA_BPP/bpp/datafile/users.265.765636195 1 120 119</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> --------</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">sum 35,994</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">7 rows selected.</span></div>
</div>
<div>
<br /></div>
<div>
A primeira coluna (FILE_NAME) exibe o Path completo do datafile.</div>
<div>
<br /></div>
<div>
A segunda coluna (Smallest Size Poss.) exibe o espaço já utilizado pelo datafile.</div>
<div>
<br /></div>
<div>
A terceira coluna (Current Size) exibe o tamanho atual do datafile.</div>
<div>
<br /></div>
<div>
E a terceira coluna (Poss. Savings) exibe o quanto pode ser devolvido ao disco e utilizado posteriormente em outro datafile.</div>
<div>
<br /></div>
<div>
Notem, por exemplo, o datafile <span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;">sysaux.260.765636181.</span><span style="font-family: inherit;"> Este está indicando que está em uso 1450Mb pelo datafile.</span></div>
<div>
<span style="font-family: inherit;">No entanto, em nossa listagem anterior podemos notar que o espaço livre nessa tablespace é de 1379Mb, o que Ilustra a High Water Mark deste datafile. Portanto, nós conseguiremos reduzir este datafile até o tamanho de 1450Mb, recuperando 90Mb para o disco e, ainda assim, a listagem de tablespaces exibirá um espaço livre de 71Mb para a tablespace Sysaux, conforme ilustrado abaixo:</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">Primeiro a ilustração de não ser possível redimensionar a tablespace abaixo da high water mark:</span><br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;">1
2
3
4
5</pre>
</td><td><pre style="line-height: 125%; margin: 0;">SQL<span style="color: #333333;">></span> <span style="color: #008800; font-weight: bold;">alter</span> <span style="color: #008800; font-weight: bold;">database</span> datafile <span style="background-color: #fff0f0;">'+DG_DATA_BPP/bpp/datafile/sysaux.260.765636181'</span> resize <span style="color: #6600ee; font-weight: bold;">1379</span>m;
<span style="color: #888888;">alter database datafile '+DG_DATA_BPP/bpp/datafile/sysaux.260.765636181' resize 1379m</span>
<span style="color: #888888;">*</span>
<span style="color: #888888;">ERROR at line 1:</span>
<span style="color: #888888;">ORA-03297: file contains used data beyond requested RESIZE value</span>
</pre>
</td></tr>
</tbody></table>
</div>
<span style="font-family: inherit;"><br /></span></div>
<div>
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">Em seguida, quando utilizamos como novo tamanho a posição atual da high water mark, podemos reduzir ao máximo permitido este datafile, recuperando um bom espaço para o disco:</span><br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;">1
2
3</pre>
</td><td><pre style="line-height: 125%; margin: 0;">SQL<span style="color: #333333;">></span> <span style="color: #008800; font-weight: bold;">alter</span> <span style="color: #008800; font-weight: bold;">database</span> datafile <span style="background-color: #fff0f0;">'+DG_DATA_BPP/bpp/datafile/sysaux.260.765636181'</span> resize <span style="color: #6600ee; font-weight: bold;">1450</span>m;
<span style="color: #888888;">Database altered.</span>
</pre>
</td></tr>
</tbody></table>
</div>
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><br /></span></div>
<div>
<div style="font-family: 'Courier New', Courier, monospace; font-size: small;">
Tablespace Used MB Free MB Total MB Pct. Free</div>
<div style="font-family: 'Courier New', Courier, monospace; font-size: small;">
------------- ----------- ----------- ----------- ----------</div>
<div style="font-family: 'Courier New', Courier, monospace; font-size: small;">
<b><span style="color: red;">SYSAUX 1,379 71 1,450 5</span></b></div>
<div style="font-family: 'Courier New', Courier, monospace; font-size: small;">
UNDOTBS1 37 1,093 1,130 97</div>
<div style="font-family: 'Courier New', Courier, monospace; font-size: small;">
TS1 31 29,969 30,000 100</div>
<div style="font-family: 'Courier New', Courier, monospace; font-size: small;">
USERS 1 119 120 99</div>
<div style="font-family: 'Courier New', Courier, monospace; font-size: small;">
SYSTEM 717 283 1,000 28</div>
<div style="font-family: 'Courier New', Courier, monospace; font-size: small;">
UNDOTBS3 28 3,662 3,690 99</div>
<div style="font-family: 'Courier New', Courier, monospace; font-size: small;">
UNDOTBS2 114 816 930 88</div>
<div style="font-family: 'Courier New', Courier, monospace; font-size: small;">
<br /></div>
<div style="font-family: 'Courier New', Courier, monospace; font-size: small;">
<br /></div>
<div>
<span style="font-family: inherit;">Espero que tenham gostado e que essa informação seja útil.</span></div>
<div>
<span style="font-family: inherit;"><br /></span></div>
<div>
<span style="font-family: inherit;">Forte abraço a todos.</span></div>
<div>
<span style="font-family: inherit;"><br /></span></div>
<div>
<span style="font-family: inherit;">Comentem a vontade.</span></div>
</div>
Anonymoushttp://www.blogger.com/profile/08245597551579856718noreply@blogger.com3tag:blogger.com,1999:blog-1835582969331889889.post-35147731874586943592012-03-26T15:16:00.000-03:002016-03-02T10:06:05.622-03:00O Destruidor WHERE ROWNUM = :valor;Boa tarde amigos DBAs.<br />
<br />
Hoje venho falar sobre um dos maiores terrores para Performance de um Banco Oracle:<br />
<br />
Uma query com WHERE rownum = :valor;<br />
<br />
Bom, isso pode parecer algo normal à primeira vista. Claro, uma query onde você quer que seja exibida somente a enésima linha do resultado. Nada muito trabalhoso.<br />
<br />
Okay. Agora eu lhes pergunto. Imagine Uma tabela com Milhões de linhas, que é consultada (Join) com uma outra tabela com milhões de linhas, o resultado desse join deve ser ordenado e SOMENTE a primeira linha deve ser capturada e utilizada como subquery para uma query Pai, que é a responsável por trazer os registros que o cliente precisa.<br />
<br />
<a name='more'></a><br />
<br />
:)<br />
Complicado de entender? Okay.. aqui vai um caso real.<br />
Obs: O nome de todas as tabelas e colunas foram alterados para preservar o cliente.<br />
<br />
<br />
Em um determinado servidor, identifiquei que de tempos em tempos (Aproximadamente de 15 em 15 minutos, a CPU era utilizada em 98% por um único processo do SO, pertencente ao oracle).<br />
<br />
*Linhas do utilitário TOP exibindo os processos que mais consumiam a cpu do servidor:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><b>27261 </b>oracle 20 0 5369m 391m 385m R 99.9 0.6 2:46.64 oracle</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: x-small;"><b>27279 </b>oracle 18 0 5369m 456m 449m R 98.9 0.7 3:43.36 oracle</span><br />
<div>
<br /></div>
<br />
<br />
Com base nos PIDs encontrados, podemos pesquisar ao que se referem.<br />
Obs: O PID informado pelo SO é correspondente ao SPID da view v$process do oracle.<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11
12
13
14</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #008800; font-weight: bold;">column</span> THREADID <span style="color: #008800; font-weight: bold;">for</span> a8
<span style="color: #008800; font-weight: bold;">column</span> USERNAME <span style="color: #008800; font-weight: bold;">for</span> a15
<span style="color: #008800; font-weight: bold;">select</span> vp<span style="color: #6600ee; font-weight: bold;">.</span>spid THREADID,
s<span style="color: #6600ee; font-weight: bold;">.</span>USERNAME,
s<span style="color: #6600ee; font-weight: bold;">.</span>STATUS,
t<span style="color: #6600ee; font-weight: bold;">.</span>PIECE,
t<span style="color: #6600ee; font-weight: bold;">.</span>SQL_TEXT
<span style="color: #008800; font-weight: bold;">from</span> v<span style="background-color: #ffaaaa; color: red;">$</span><span style="color: #008800; font-weight: bold;">session</span> s,
v<span style="background-color: #ffaaaa; color: red;">$</span>sqltext t ,
v<span style="background-color: #ffaaaa; color: red;">$</span>process vp
<span style="color: #008800; font-weight: bold;">where</span> s<span style="color: #6600ee; font-weight: bold;">.</span>SQL_ADDRESS <span style="color: #333333;">=</span> t<span style="color: #6600ee; font-weight: bold;">.</span>ADDRESS
<span style="color: #008800; font-weight: bold;">and</span> vp<span style="color: #6600ee; font-weight: bold;">.</span>addr <span style="color: #333333;">=</span> s<span style="color: #6600ee; font-weight: bold;">.</span>paddr(<span style="color: #333333;">+</span>)
<span style="color: #008800; font-weight: bold;">and</span> vp<span style="color: #6600ee; font-weight: bold;">.</span>spid <span style="color: #008800; font-weight: bold;">in</span> (<span style="color: #6600ee; font-weight: bold;">27261</span>, <span style="color: #6600ee; font-weight: bold;">27279</span>)
<span style="color: #008800; font-weight: bold;">order</span> <span style="color: #008800; font-weight: bold;">by</span> s<span style="color: #6600ee; font-weight: bold;">.</span>sid, t<span style="color: #6600ee; font-weight: bold;">.</span>PIECE;
</pre>
</td></tr>
</tbody></table>
</div>
<br />
Aqui o resultado obtido:<br />
<div>
(Query exibindo as atividades relacionadas ao PID)<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22</pre>
</td><td><pre style="line-height: 125%; margin: 0;">THREADID USERNAME STATUS PIECE SQL_TEXT
<span style="font-style: italic;">-------- -------- -------- ----- ----------------------------------------------------------------</span>
27279 USRWEB ACTIVE 0 <span style="font-weight: bold;">SELECT</span> main.ID_CANCEL_REASON <span style="font-weight: bold;">as</span> IDT1_0_, main.TX_CANCEL_REASON
27279 USRWEB ACTIVE 1 <span style="font-weight: bold;">as</span> TX2_43_0_ <span style="font-weight: bold;">FROM</span> cancel_request_reason main, (
27279 USRWEB ACTIVE 2 <span style="font-weight: bold;">SELECT</span> cpd.id_cancel_req_reason, cpd.id_cancel_req
27279 USRWEB ACTIVE 3 <span style="font-weight: bold;">FROM</span> cancel_request_detail cpd, cancel_request
27279 USRWEB ACTIVE 4 cp, cancel_request_detail_dept cdct <span style="font-weight: bold;">WHERE</span> (cpd.id_cancel_req
27279 USRWEB ACTIVE 5 = cp.id_cancel_req <span style="font-weight: bold;">OR</span> cdct.id_cancel_req
27279 USRWEB ACTIVE 6 = cp.id_cancel_req) <span style="font-weight: bold;">AND</span> cp.id_online_request
27279 USRWEB ACTIVE 7 = :"SYS_B_0" <span style="font-weight: bold;">AND</span> rownum = :"SYS_B_1") ma
27279 USRWEB ACTIVE 8 in_1_ <span style="font-weight: bold;">WHERE</span> main.ID_CANCEL_REASON = main_1_.
27279 USRWEB ACTIVE 9 id_cancel_req_reason
27261 USRWEB ACTIVE 0 <span style="font-weight: bold;">SELECT</span> main.ID_CANCEL_REASON <span style="font-weight: bold;">as</span> IDT1_0_, main.TX_CANCEL_REASON
27261 USRWEB ACTIVE 1 <span style="font-weight: bold;">as</span> TX2_43_0_ <span style="font-weight: bold;">FROM</span> cancel_request_reason main, (
27261 USRWEB ACTIVE 2 <span style="font-weight: bold;">SELECT</span> cpd.id_cancel_req_reason, cpd.id_cancel_req
27261 USRWEB ACTIVE 3 <span style="font-weight: bold;">FROM</span> cancel_request_detail cpd, cancel_request
27261 USRWEB ACTIVE 4 cp, cancel_request_detail_dept cdct <span style="font-weight: bold;">WHERE</span> (cpd.id_cancel_req
27261 USRWEB ACTIVE 5 = cp.id_cancel_req <span style="font-weight: bold;">OR</span> cdct.id_cancel_req
27261 USRWEB ACTIVE 6 = cp.id_cancel_req) <span style="font-weight: bold;">AND</span> cp.id_online_request
27261 USRWEB ACTIVE 7 = :"SYS_B_0" <span style="font-weight: bold;">AND</span> rownum = :"SYS_B_1") ma
27261 USRWEB ACTIVE 8 in_1_ <span style="font-weight: bold;">WHERE</span> main.ID_CANCEL_REASON = main_1_.
27261 USRWEB ACTIVE 9 id_cancel_req_reason
</pre>
</td></tr>
</tbody></table>
</div>
<br />
Formatei a query para que ficasse legível:<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11
12</pre>
</td><td><pre style="line-height: 125%; margin: 0;"><span style="color: #008800; font-weight: bold;">SELECT</span> main<span style="color: #6600ee; font-weight: bold;">.</span>ID_CANCEL_REASON <span style="color: #008800; font-weight: bold;">as</span> IDT1_0_,
main<span style="color: #6600ee; font-weight: bold;">.</span>TX_CANCEL_REASON <span style="color: #008800; font-weight: bold;">as</span> TX2_43_0_
<span style="color: #008800; font-weight: bold;">FROM</span> USRWEB<span style="color: #6600ee; font-weight: bold;">.</span>cancel_request_reason main,
(<span style="color: #008800; font-weight: bold;">SELECT</span> cpd<span style="color: #6600ee; font-weight: bold;">.</span>id_cancel_req_reason,
cpd<span style="color: #6600ee; font-weight: bold;">.</span>id_cancel_req
<span style="color: #008800; font-weight: bold;">FROM</span> USRWEB<span style="color: #6600ee; font-weight: bold;">.</span>cancel_request_detail cpd,
USRWEB<span style="color: #6600ee; font-weight: bold;">.</span>cancel_request cp,
USRWEB<span style="color: #6600ee; font-weight: bold;">.</span>cancel_request_detail_dept cdct
<span style="color: #008800; font-weight: bold;">WHERE</span> (cpd<span style="color: #6600ee; font-weight: bold;">.</span>id_cancel_req <span style="color: #333333;">=</span> cp<span style="color: #6600ee; font-weight: bold;">.</span>id_cancel_req <span style="color: #008800; font-weight: bold;">OR</span> cdct<span style="color: #6600ee; font-weight: bold;">.</span>id_cancel_req <span style="color: #333333;">=</span> cp<span style="color: #6600ee; font-weight: bold;">.</span>id_cancel_req)
<span style="color: #008800; font-weight: bold;">AND</span> cp<span style="color: #6600ee; font-weight: bold;">.</span>id_online_request <span style="color: #333333;">=</span> <span style="color: #996633;">:"SYS_B_0"</span>
<span style="color: red;"><b><span style="color: #008800; font-weight: bold;">AND</span> rownum <span style="color: #333333;">=</span> <span style="color: #996633;">:"SYS_B_1"</span></b></span>) main_1_
<span style="color: #008800; font-weight: bold;">WHERE</span> main<span style="color: #6600ee; font-weight: bold;">.</span>ID_CANCEL_REASON <span style="color: #333333;">=</span> main_1_<span style="color: #6600ee; font-weight: bold;">.</span>id_cancel_req_reason;
</pre>
</td></tr>
</tbody></table>
</div>
<br />
Como você pode observar, durante a execução desta query, o consumo de CPU chega em 99% apenas para o processo em execução.</div>
<div>
<span style="font-family: inherit;"><br /></span></div>
<div>
<div>
Executei manualmente essa query, inclusive passando os valores como bind variables e constatei o comportamento bem como o tempo levado para resposta da mesma.</div>
<div>
<br /></div>
<div>
Isso ocorre, basicamente, devido à condição AND rownum = :"SYS_B_1", que faz com que o Oracle primeiro resolva toda a query, ordene o resultado e te retorne o valor da linha especificada pela bind variable SYS_B_1.</div>
<div>
<br /></div>
<div>
Também, ao utilizarmos rownum em uma condição where, o ORACLE força a utilização do plano de ação FIRST_ROWS ao invés de ALL_ROWS, o que diminui drasticamente a performance da query e elimina a execução em paralelismo.</div>
<div>
<br /></div>
<div>
Após algum tempo analisando essa query, consegui chegar a um tempo de execução de <span style="color: blue; font-size: large;"><b>6 segundos</b></span> ao invés dos <span style="color: red; font-size: large;"><b>7 minutos</b></span> que a query leva originalmente.</div>
<div>
<br /></div>
<div>
Abaixo segue a execução da query como ela está construída, e com a modificação proposta.</div>
<div>
<br /></div>
<div>
<div>
(Query como foi escrita originalmente)<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19</pre>
</td><td><pre style="line-height: 125%; margin: 0;">SQL<span style="color: #333333;">></span> <span style="color: #008800; font-weight: bold;">SELECT</span> main<span style="color: #6600ee; font-weight: bold;">.</span>ID_CANCEL_REASON <span style="color: #008800; font-weight: bold;">as</span> IDT1_0_,
main<span style="color: #6600ee; font-weight: bold;">.</span>TX_CANCEL_REASON <span style="color: #008800; font-weight: bold;">as</span> TX2_43_0_
<span style="color: #008800; font-weight: bold;">FROM</span> USRWEB<span style="color: #6600ee; font-weight: bold;">.</span>cancel_request_reason main,
(<span style="color: #008800; font-weight: bold;">SELECT</span> cpd<span style="color: #6600ee; font-weight: bold;">.</span>id_cancel_req_reason,
cpd<span style="color: #6600ee; font-weight: bold;">.</span>id_cancel_req
<span style="color: #008800; font-weight: bold;">FROM</span> USRWEB<span style="color: #6600ee; font-weight: bold;">.</span>cancel_request_detail cpd,
USRWEB<span style="color: #6600ee; font-weight: bold;">.</span>cancel_request cp,
USRWEB<span style="color: #6600ee; font-weight: bold;">.</span>cancel_request_detail_dept cdct
<span style="color: #008800; font-weight: bold;">WHERE</span> (cpd<span style="color: #6600ee; font-weight: bold;">.</span>id_cancel_req <span style="color: #333333;">=</span> cp<span style="color: #6600ee; font-weight: bold;">.</span>id_cancel_req <span style="color: #008800; font-weight: bold;">OR</span> cdct<span style="color: #6600ee; font-weight: bold;">.</span>id_cancel_req <span style="color: #333333;">=</span> cp<span style="color: #6600ee; font-weight: bold;">.</span>id_cancel_req)
<span style="color: #008800; font-weight: bold;">AND</span> cp<span style="color: #6600ee; font-weight: bold;">.</span>id_online_request <span style="color: #333333;">=</span> <span style="color: #996633;">:"SYS_B_0"</span>
<span style="color: #008800; font-weight: bold;">AND</span> rownum <span style="color: #333333;">=</span> <span style="color: #996633;">:"SYS_B_1"</span>) main_1_
<span style="color: #008800; font-weight: bold;">WHERE</span> main<span style="color: #6600ee; font-weight: bold;">.</span>ID_CANCEL_REASON <span style="color: #333333;">=</span> main_1_<span style="color: #6600ee; font-weight: bold;">.</span>id_cancel_req_reason;
<span style="color: #888888;"> IDT1_0_ TX2_43_0_</span>
<span style="color: #888888;">---------- ----------------------------------------------------------------------------------------------------</span>
<span style="color: #888888;"> 14 Incorrect Credit</span>
<span style="color: red;"><b>Elapsed: 00:06:25.01</b></span>
</pre>
</td></tr>
</tbody></table>
</div>
<br />
(Query re-escrita para termos ganho de performance)<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20</pre>
</td><td><pre style="line-height: 125%; margin: 0;">SQL<span style="color: #333333;">></span> <span style="color: #008800; font-weight: bold;">SELECT</span> main<span style="color: #6600ee; font-weight: bold;">.</span>ID_CANCEL_REASON <span style="color: #008800; font-weight: bold;">as</span> IDT1_0_,
main<span style="color: #6600ee; font-weight: bold;">.</span>TX_CANCEL_REASON <span style="color: #008800; font-weight: bold;">as</span> TX2_43_0_
<span style="color: #008800; font-weight: bold;">FROM</span> USRWEB<span style="color: #6600ee; font-weight: bold;">.</span>cancel_request_reason main,
(<span style="color: #008800; font-weight: bold;">select</span> <span style="color: #333333;">*</span> <span style="color: #008800; font-weight: bold;">from</span>
(<span style="color: #008800; font-weight: bold;">SELECT</span> cpd<span style="color: #6600ee; font-weight: bold;">.</span>id_cancel_req_reason,
cpd<span style="color: #6600ee; font-weight: bold;">.</span>id_cancel_req,
ROW_NUMBER() <span style="color: #008800; font-weight: bold;">OVER</span> (<span style="color: #008800; font-weight: bold;">ORDER</span> <span style="color: #008800; font-weight: bold;">BY</span> id_online_request) <span style="color: #008800; font-weight: bold;">as</span> rn
<span style="color: #008800; font-weight: bold;">FROM</span> USRWEB<span style="color: #6600ee; font-weight: bold;">.</span>cancel_request_detail cpd,
USRWEB<span style="color: #6600ee; font-weight: bold;">.</span>cancel_request cp,
USRWEB<span style="color: #6600ee; font-weight: bold;">.</span>cancel_request_detail_dept cdct
<span style="color: #008800; font-weight: bold;">WHERE</span> (cpd<span style="color: #6600ee; font-weight: bold;">.</span>id_cancel_req <span style="color: #333333;">=</span> cp<span style="color: #6600ee; font-weight: bold;">.</span>id_cancel_req <span style="color: #008800; font-weight: bold;">OR</span> cdct<span style="color: #6600ee; font-weight: bold;">.</span>id_cancel_req <span style="color: #333333;">=</span> cp<span style="color: #6600ee; font-weight: bold;">.</span>id_cancel_req)
<span style="color: #008800; font-weight: bold;">AND</span> cp<span style="color: #6600ee; font-weight: bold;">.</span>id_online_request <span style="color: #333333;">=</span> <span style="color: #996633;">:"SYS_B_0"</span>)
<span style="color: #008800; font-weight: bold;">where</span> rn <span style="color: #333333;">=</span> <span style="color: #996633;">:"SYS_B_1"</span>) main_1_
<span style="color: #008800; font-weight: bold;">WHERE</span> main<span style="color: #6600ee; font-weight: bold;">.</span>ID_CANCEL_REASON <span style="color: #333333;">=</span> main_1_<span style="color: #6600ee; font-weight: bold;">.</span>id_cancel_req_reason;
<span style="color: #888888;"> IDT1_0_ TX2_43_0_</span>
<span style="color: #888888;">---------- ----------------------------------------------------------------------------------------------------</span>
<span style="color: #888888;"> 14 Incorrect Credit</span>
<span style="color: blue;"><b>Elapsed: 00:00:06.04</b></span>
</pre>
</td></tr>
</tbody></table>
</div>
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">Como pode notar, após a modificação da query, a mesma caiu de 6.25 Minutos para 6.04 Segundos.</span></div>
</div>
</div>
<div>
<div>
<br /></div>
<div>
Executei novamente a query original algumas vezes em seguida, bem como a query modificada para eliminarmos qualquer possibilidade de o resultado da query modificada estar em cache.</div>
<div>
Todas as vezes que rodei a query original, ela levou em torno de 6 a 7 minutos para concluir, e a query modificada em torno de 5 a 6 segundos.</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
A modificação proposta está no seguinte:</div>
<div>
<br /></div>
<div>
Ao invés de utilizarmos o tradicional rownum como filtro para a subquery, é extremamente mais vantajoso usarmos a Função ROW_NUMBER(), utilizando como critério de ordem a coluna id_online_request, que é a chave primária da tabela.</div>
<div>
<br /></div>
<div>
Para isso, dividi a subquery em uma segunda subquery para que pudéssemos ter o valor retornado pela função ROW_NUMBER() e utilizei este valor como cláusula where para a mesma.</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
Essencialmente, a lógica da query é a mesma, porém, quando olhamos para o plano de execução da mesma, notamos a diferença.</div>
<div>
<br /></div>
<div>
No plano de execução para a query com a modificação, notamos que o oracle acrescenta uma ação WINDOW NOSORT STOPKEY que faz com que a execução da mesma seja finalizada assim que um valor é encontrado para o Oracle, eliminando todo o processamento restante oriundo da subquery em sí, tornando a execução MUITO MAIS RÁPIDA.</div>
<div>
<br /></div>
<div>
Este é o plano de execução para a query MODIFICADA para utilizar ROW_NUMBER()<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42</pre>
</td><td><pre style="line-height: 125%; margin: 0;">PLAN_TABLE_OUTPUT
<span style="font-style: italic;">------------------------------------------------------------------------------------------------------------------</span>
Plan hash <span style="font-weight: bold;">value</span>: 3866720052
<span style="font-style: italic;">------------------------------------------------------------------------------------------------------------------</span>
| Id | Operation | <span style="font-weight: bold;">Name</span> | <span style="font-weight: bold;">Rows</span> | Bytes | <span style="font-weight: bold;">Cost</span> (%CPU)| Time |
<span style="font-style: italic;">------------------------------------------------------------------------------------------------------------------</span>
| 0 | <span style="font-weight: bold;">SELECT</span> <span style="font-weight: bold;">STATEMENT</span> | | 36M| 1691M| 7266 (9)| 00:01:28 |
|* 1 | HASH <span style="font-weight: bold;">JOIN</span> | | 36M| 1691M| 7266 (9)| 00:01:28 |
| 2 | <span style="font-weight: bold;">TABLE</span> <span style="font-weight: bold;">ACCESS</span> <span style="font-weight: bold;">FULL</span> | cancel_request_reason | 8 | 184 | 3 (0)| 00:00:01 |
|* 3 | <span style="font-weight: bold;">VIEW</span> | | 36M| 897M| 7021 (5)| 00:01:25 |
|* 4 | <span style="color: red;"><span style="font-weight: bold;">WINDOW</span> NOSORT STOPKEY</span> | | 36M| 828M| 7021 (5)| 00:01:25 |
| 5 | CONCATENATION | | | | | |
|* 6 | HASH <span style="font-weight: bold;">JOIN</span> | | 36M| 828M| 7000 (5)| 00:01:25 |
| 7 | <span style="font-weight: bold;">INDEX</span> FAST <span style="font-weight: bold;">FULL</span> SCAN | IDX_CARD_DEPT_CAN_DET_001 | 954 | 4770 | 2 (0)| 00:00:01 |
| 8 | MERGE <span style="font-weight: bold;">JOIN</span> CARTESIAN | | 5727K| 103M| 6756 (2)| 00:01:22 |
| 9 | <span style="font-weight: bold;">TABLE</span> <span style="font-weight: bold;">ACCESS</span> <span style="font-weight: bold;">BY</span> <span style="font-weight: bold;">INDEX</span> ROWID| cancel_request | 1 | 11 | 5 (0)| 00:00:01 |
|* 10 | <span style="font-weight: bold;">INDEX</span> <span style="font-weight: bold;">RANGE</span> SCAN | CANC_REQUEST_ORA_001 | 1 | | 3 (0)| 00:00:01 |
| 11 | BUFFER SORT | | 4789K| 36M| 6751 (2)| 00:01:22 |
| 12 | <span style="font-weight: bold;">TABLE</span> <span style="font-weight: bold;">ACCESS</span> <span style="font-weight: bold;">FULL</span> | cancel_request_detail | 4789K| 36M| 6751 (2)| 00:01:22 |
| 13 | NESTED LOOPS | | 868 | 20832 | 20 (0)| 00:00:01 |
| 14 | NESTED LOOPS | | 18 | 342 | 8 (0)| 00:00:01 |
| 15 | <span style="font-weight: bold;">TABLE</span> <span style="font-weight: bold;">ACCESS</span> <span style="font-weight: bold;">BY</span> <span style="font-weight: bold;">INDEX</span> ROWID| cancel_request | 1 | 11 | 5 (0)| 00:00:01 |
|* 16 | <span style="font-weight: bold;">INDEX</span> <span style="font-weight: bold;">RANGE</span> SCAN | CANC_REQUEST_ORA_001 | 1 | | 3 (0)| 00:00:01 |
| 17 | <span style="font-weight: bold;">TABLE</span> <span style="font-weight: bold;">ACCESS</span> <span style="font-weight: bold;">BY</span> <span style="font-weight: bold;">INDEX</span> ROWID| cancel_request_detail | 15 | 120 | 3 (0)| 00:00:01 |
|* 18 | <span style="font-weight: bold;">INDEX</span> <span style="font-weight: bold;">RANGE</span> SCAN | IDX_CANC_REQ_DET_003 | 16 | | 2 (0)| 00:00:01 |
|* 19 | <span style="font-weight: bold;">INDEX</span> FAST <span style="font-weight: bold;">FULL</span> SCAN | IDX_CARD_DEPT_CAN_DET_001 | 48 | 240 | 1 (0)| 00:00:01 |
<span style="font-style: italic;">------------------------------------------------------------------------------------------------------------------</span>
Predicate Information (identified <span style="font-weight: bold;">by</span> operation id):
<span style="font-style: italic;">---------------------------------------------------</span>
1 - <span style="font-weight: bold;">access</span>(<span style="font-style: italic;">"MAIN"</span>.<span style="font-style: italic;">"ID_CANCEL_REASON"</span>=<span style="font-style: italic;">"from$_subquery$_003"</span>.<span style="font-style: italic;">"id_cancel_req_reason"</span>)
3 - filter(<span style="font-style: italic;">"RN"</span>=TO_NUMBER(:SYS_B_1))
4 - filter(ROW_NUMBER() <span style="font-weight: bold;">OVER</span> ( <span style="font-weight: bold;">ORDER</span> <span style="font-weight: bold;">BY</span> <span style="font-style: italic;">"id_online_request"</span>)<=TO_NUMBER(:SYS_B_1))
6 - <span style="font-weight: bold;">access</span>(<span style="font-style: italic;">"CDCT"</span>.<span style="font-style: italic;">"id_cancel_req"</span>=<span style="font-style: italic;">"CP"</span>.<span style="font-style: italic;">"id_cancel_req"</span>)
10 - <span style="font-weight: bold;">access</span>(<span style="font-style: italic;">"CP"</span>.<span style="font-style: italic;">"id_online_request"</span>=TO_NUMBER(:SYS_B_0))
16 - <span style="font-weight: bold;">access</span>(<span style="font-style: italic;">"CP"</span>.<span style="font-style: italic;">"id_online_request"</span>=TO_NUMBER(:SYS_B_0))
18 - <span style="font-weight: bold;">access</span>(<span style="font-style: italic;">"CPD"</span>.<span style="font-style: italic;">"id_cancel_req"</span>=<span style="font-style: italic;">"CP"</span>.<span style="font-style: italic;">"id_cancel_req"</span>)
19 - filter(LNNVL(<span style="font-style: italic;">"CDCT"</span>.<span style="font-style: italic;">"id_cancel_req"</span>=<span style="font-style: italic;">"CP"</span>.<span style="font-style: italic;">"id_cancel_req"</span>))
38 <span style="font-weight: bold;">rows</span> selected.
</pre>
</td></tr>
</tbody></table>
</div>
<br />
Este é o plano de execução para a query Original:<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<table><tbody>
<tr><td><pre style="line-height: 125%; margin: 0;"> 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44</pre>
</td><td><pre style="line-height: 125%; margin: 0;">PLAN_TABLE_OUTPUT
<span style="font-style: italic;">-------------------------------------------------------------------------------------------------------------------</span>
Plan hash <span style="font-weight: bold;">value</span>: 3966394659
<span style="font-style: italic;">-------------------------------------------------------------------------------------------------------------------</span>
| Id | Operation | <span style="font-weight: bold;">Name</span> | <span style="font-weight: bold;">Rows</span> | Bytes | <span style="font-weight: bold;">Cost</span> (%CPU)| Time |
<span style="font-style: italic;">-------------------------------------------------------------------------------------------------------------------</span>
| 0 | <span style="font-weight: bold;">SELECT</span> <span style="font-weight: bold;">STATEMENT</span> | | 36M| 897M| 7265 (9)| 00:01:28 |
|* 1 | HASH <span style="font-weight: bold;">JOIN</span> | | 36M| 897M| 7265 (9)| 00:01:28 |
| 2 | <span style="font-weight: bold;">TABLE</span> <span style="font-weight: bold;">ACCESS</span> <span style="font-weight: bold;">FULL</span> | cancel_request_reason | 8 | 184 | 3 (0)| 00:00:01 |
| 3 | <span style="font-weight: bold;">VIEW</span> | | 36M| 103M| 7020 (5)| 00:01:25 |
| 4 | COUNT | | | | | |
| 5 | CONCATENATION | | | | | |
|* 6 | FILTER | | | | | |
|* 7 | HASH <span style="font-weight: bold;">JOIN</span> | | 36M| 828M| 7000 (5)| 00:01:25 |
| 8 | <span style="font-weight: bold;">INDEX</span> FAST <span style="font-weight: bold;">FULL</span> SCAN | IDX_CARD_DEPT_CAN_DET_001 | 954 | 4770 | 2 (0)| 00:00:01 |
| 9 | MERGE <span style="font-weight: bold;">JOIN</span> CARTESIAN | | 5727K| 103M| 6756 (2)| 00:01:22 |
| 10 | <span style="font-weight: bold;">TABLE</span> <span style="font-weight: bold;">ACCESS</span> <span style="font-weight: bold;">BY</span> <span style="font-weight: bold;">INDEX</span> ROWID| cancel_request | 1 | 11 | 5 (0)| 00:00:01 |
|* 11 | <span style="font-weight: bold;">INDEX</span> <span style="font-weight: bold;">RANGE</span> SCAN | CANC_REQUEST_ORA_001 | 1 | | 3 (0)| 00:00:01 |
| 12 | BUFFER SORT | | 4789K| 36M| 6751 (2)| 00:01:22 |
| 13 | <span style="font-weight: bold;">TABLE</span> <span style="font-weight: bold;">ACCESS</span> <span style="font-weight: bold;">FULL</span> | cancel_request_detail | 4789K| 36M| 6751 (2)| 00:01:22 |
|* 14 | FILTER | | | | | |
| 15 | NESTED LOOPS | | 868 | 20832 | 20 (0)| 00:00:01 |
| 16 | NESTED LOOPS | | 18 | 342 | 8 (0)| 00:00:01 |
| 17 | <span style="font-weight: bold;">TABLE</span> <span style="font-weight: bold;">ACCESS</span> <span style="font-weight: bold;">BY</span> <span style="font-weight: bold;">INDEX</span> ROWID| cancel_request | 1 | 11 | 5 (0)| 00:00:01 |
|* 18 | <span style="font-weight: bold;">INDEX</span> <span style="font-weight: bold;">RANGE</span> SCAN | CANC_REQUEST_ORA_001 | 1 | | 3 (0)| 00:00:01 |
| 19 | <span style="font-weight: bold;">TABLE</span> <span style="font-weight: bold;">ACCESS</span> <span style="font-weight: bold;">BY</span> <span style="font-weight: bold;">INDEX</span> ROWID| cancel_request_detail | 15 | 120 | 3 (0)| 00:00:01 |
|* 20 | <span style="font-weight: bold;">INDEX</span> <span style="font-weight: bold;">RANGE</span> SCAN | IDX_CANC_REQ_DET_003 | 16 | | 2 (0)| 00:00:01 |
|* 21 | <span style="font-weight: bold;">INDEX</span> FAST <span style="font-weight: bold;">FULL</span> SCAN | IDX_CARD_DEPT_CAN_DET_001 | 48 | 240 | 1 (0)| 00:00:01 |
<span style="font-style: italic;">-------------------------------------------------------------------------------------------------------------------</span>
Predicate Information (identified <span style="font-weight: bold;">by</span> operation id):
<span style="font-style: italic;">---------------------------------------------------</span>
1 - <span style="font-weight: bold;">access</span>(<span style="font-style: italic;">"MAIN"</span>.<span style="font-style: italic;">"ID_CANCEL_REASON"</span>=<span style="font-style: italic;">"MAIN_1_"</span>.<span style="font-style: italic;">"id_cancel_req_reason"</span>)
6 - filter(ROWNUM=TO_NUMBER(:SYS_B_1))
7 - <span style="font-weight: bold;">access</span>(<span style="font-style: italic;">"CDCT"</span>.<span style="font-style: italic;">"id_cancel_req"</span>=<span style="font-style: italic;">"CP"</span>.<span style="font-style: italic;">"id_cancel_req"</span>)
11 - <span style="font-weight: bold;">access</span>(<span style="font-style: italic;">"CP"</span>.<span style="font-style: italic;">"id_online_request"</span>=TO_NUMBER(:SYS_B_0))
14 - filter(ROWNUM=TO_NUMBER(:SYS_B_1))
18 - <span style="font-weight: bold;">access</span>(<span style="font-style: italic;">"CP"</span>.<span style="font-style: italic;">"id_online_request"</span>=TO_NUMBER(:SYS_B_0))
20 - <span style="font-weight: bold;">access</span>(<span style="font-style: italic;">"CPD"</span>.<span style="font-style: italic;">"id_cancel_req"</span>=<span style="font-style: italic;">"CP"</span>.<span style="font-style: italic;">"id_cancel_req"</span>)
21 - filter(LNNVL(<span style="font-style: italic;">"CDCT"</span>.<span style="font-style: italic;">"id_cancel_req"</span>=<span style="font-style: italic;">"CP"</span>.<span style="font-style: italic;">"id_cancel_req"</span>))
40 <span style="font-weight: bold;">rows</span> selected.
</pre>
</td></tr>
</tbody></table>
</div>
<br />
Espero que tenham gostado.<br />
<br />
Até o próximo post.</div>
</div>
Anonymoushttp://www.blogger.com/profile/08245597551579856718noreply@blogger.com2tag:blogger.com,1999:blog-1835582969331889889.post-33446045134116594302012-03-26T11:54:00.002-03:002012-03-27T08:52:52.059-03:00Tudo novo do mesmo... só que diferente<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEju8UfMNG1Sm8blnQM6fiVetytwCsZ4U5fNW4hWoydZsPIC-7CNAETZ6ngxJ0EWppaxsNOKQ7mRHfwxdtb6Tp8GCgXXx7MA5yUYBKlfUEhAku9_vZhy93tq7lSPtGVGQEDlFSxWpzebtxI/s1600/logo_oracle.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="240" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEju8UfMNG1Sm8blnQM6fiVetytwCsZ4U5fNW4hWoydZsPIC-7CNAETZ6ngxJ0EWppaxsNOKQ7mRHfwxdtb6Tp8GCgXXx7MA5yUYBKlfUEhAku9_vZhy93tq7lSPtGVGQEDlFSxWpzebtxI/s320/logo_oracle.jpg" width="320" /></a>Olá amigos DBAs, conhecedores e entusiastas.<br />
<br />
Inicio hoje meu primeiro Blog sobre tecnologia. Bom, meu PRIMEIRO BLOG no geral e inicio com a proposta de abordar sobre tecnologia.<br />
<br />
Okay, pode parecer algo não tão original assim, mas minha ideia é trazer ao leitor uma abordagem mais Prática sobre o assunto, especialmente no que diz respeito à Administração de Bancos de Dados e, mais especificamente ainda, à administração de Bancos de Dados Oracle.<br />
<br />
Inicio falando um pouco sobre mim.<br />
<br />
Trabalho com tecnologia Oracle ha aproximadamente 7 anos, sendo 3 desses anos, exclusivamente como DBA.<br />
<br />
<a name='more'></a><br /><br />
DBA, para quem não sabe, é uma sigla para DataBase Administrator (Administrador de Bancos de Dados, num português abrasileirado =D ).<br />
<br />
Trabalhei por cinco anos como Analista Desenvolvedor Java e Oracle PL/Sql, o que na época, diziam ser a combinação perfeita. O que eu concordo até hoje.<br />
<br />
Deixando as preferencias de lado, o fato de ter trabalhado como Desenvolvedor por este tempo, permitiu que eu conhecesse de uma forma mais aprofundada sobre o funcionamento de queryes, planos de execução, índices, cursores e algumas outras coisas que me proporcionaram uma visão um tanto quanto privilégiada do aspecto "Performance de SQL".<br />
<br />
E é este o principal motivo pelo qual escrevo este blog: Tentar trazer até vocês uma abordagem não somente técnica sobre diversos "perrengues" pelos quais já passei, como foram resolvidos, como a performance do ambiente foi afetada. Tudo isso tentando trazer ao máximo exemplos do mundo real de tudo que REALMENTE funcionou.<br />
<br />
Espero agradar a Muitos e não desagradar a tantos mas, de maneira mais especial, espero que com este blog possamos trocar experiências e crescer juntos tecnicamente.<br />
<br />
Forte abraço a todos.Anonymoushttp://www.blogger.com/profile/08245597551579856718noreply@blogger.com4