Using DBMS_PDB.RECOVER

Hi guys, just passing here to post how you should use DBMS_PDB.RECOVER in order to successfully recover your PDB description XML file if you receive the error ORA-65139: Mismatch between XML metadata file and data file <DATAFILE_PATH> .

To successfully execute this procedure, you must have the description XML file. It’s quite simple when you know exactly what to do. The confusion is that you must point all the PDB’s datafiles excluding the ones from UNDO tablespace. That’s because when you are using a Multitenant Architecture, the UNDO tablespace is used for all pluggable databases. Also, all datafiles should be separated with a comma without any spaces, so it should look like this:

BEGIN
DBMS_PDB.RECOVER(PDB_DESCR_FILE=> ‘/home/oracle/PDB01.xml’, PDB_NAME=>’PDB01′, FILENAMES=> ‘+DATA/PDB01/DATAFILE/sysaux_262_863892639,+DATA/PDB01/DATAFILE/system_261_863892625,
+DATA/PDB01/DATAFILE/users_265_863892763′);
END;
/

After that, you can just execute the statement below, if you don’t want to copy datafiles to a new location:

SQL> CREATE PLUGGABLE DATABASE PDB01 USING ‘/home/oracle/PDB01.xml’ NOCOPY TEMPFILE REUSE;

Quite easy, right?! Yes, but I took some time to use this procedure, so that’s why I’m pointing it right to you. Cya, guys!

CellMetric Tool

Hey guys, it’s been a while since my last post as I’ve been quite busy on the last months at a personal project which could be used to collect some current metrics from a Exadata Storage Server. I got a beta version now, so it’s stills under development but I hope you enjoy this tool. It’s a Java App which was built and tested under ESS image version 12.1.2.1.1.150316.2 and Java version 1.7.0_72 . Java 7.0 is deployed under ESS image version 12.1.2.1.1

Well what this app call CellMetric does is quite simple, it executes CellCLI throw SSH – so Node Equivalency to cellmonitor uses needs to be set correctly -, list the current metrics from the cell that you provided using saving this output to a XML file and then print the results on the screen. That easy. The only attention is that the Database I/O Load Metric information is top ordered listing only the hugest 15 databases. The way how you execute it is java CellMetric -top -cell <cellHostName> . Below you can see an image from the execution time:

CellMetric Image

So just download this CellMetric_v1.2.zip place both file on your Exadata Database Machine and then enjoy it. Cya!!

Patching Exadata QFSP July 2015

Hey, everyone! I’m here with this shortly post about patching Exadata QFSP July 2015. My teammate and I have recently patched our X2-2 Half Rack environment from 11.2.3.3.0.131014.1 to 12.1.2.1.2.150617.1 so I want to THANK them (Vitor Eduardo, Claudio Angerami, Bruno Palma, Anselmo Ribeiro and Edmilson Carmo) for the great job we’ve done. There are no big news, nothing really changed from the other post that I made before, the big key is to pay attention on the ‘Known Issues’ and address them as founded. Also, analyze the RPMs that will be deleted in order to guarantee same functionality as before. After that, if everything is fine your platform should be ready to patch.

So let’s go for it! .Just a point here, we changed the real hostnames and IPs from the servers, cells and switches.

  • IB SWITCHES UPGRADE:

[root@exa01db01 patch_12.1.2.1.2.150617.1]# ./patchmgr -ibswitches /root/ib_switches -upgrade

With arguments: -ibswitches /root/ib_switches -upgrade
2015-08-08 17:01:37 -0300 [INFO] pid file: /var/log/exadatatmp/_EXA_AXE_patchmgr.lock : 98220
PID TTY TIME CMD
2015-08-08 17:01:42 -0300 ++++++++++++++++++ Logs so far begin ++++++++++
2015-08-08 17:01:42 -0300 ++++++++++++++++++ Logs so far end ++++++++++
2015-08-08 17:01:42 -0300 1 of 1 :SUCCESS: DO: Initiate upgrade of InfiniBand switches to 2.1.5-1. Expect up to 15 minutes for each switch
2015-08-08 17:45:23 -0300 ++++++++++++++++++ Logs so far begin ++++++++++
—– InfiniBand switch update process started Sat Aug 8 17:01:42 BRT 2015 —–
[NOTE ] Log file at /var/log/cellos/upgradeIBSwitch.log

[INFO ] List of InfiniBand switches for upgrade: ( exa01sw-ib2 exa01sw-ib3 )
[PROMPT ] Use the default password for all switches? (y/n) [n]:
[PROMPT ] Updating only 2 switch(es). Are you sure you want to continue? (y/n) [n]:
[SUCCESS ] Verifying Network connectivity to exa01sw-ib2
[SUCCESS ] Verifying Network connectivity to exa01sw-ib3
[SUCCESS ] Validating verify-topology output
[INFO ] Proceeding with upgrade of InfiniBand switches to version 2.1.5_1
[INFO ] Master Subnet Manager is set to “exa01sw-ib2” in all Switches

[INFO ] ———- Starting with IBSwitch exa01sw-ib2
[SUCCESS ] Disable Subnet Manager on exa01sw-ib2
[SUCCESS ] Copy firmware packages to exa01sw-ib2
[SUCCESS ] exa01sw-ib2 is at 2.1.3-4. Meets minimal patching level 2.1.3-4
[SUCCESS ] Verifying that /tmp has 120M in exa01sw-ib2, found 138M
[SUCCESS ] Verifying that / has 80M in exa01sw-ib2, found 198M
[SUCCESS ] Verifying that exa01sw-ib2 has 120M free memory, found 239M
[SUCCESS ] Verifying host details in /etc/hosts and /etc/sysconfig/network-scripts/ifcfg-eth[0,1] for exa01sw-ib2
[SUCCESS ] Verifying that exa01sw-ib2 has at least 1 NTP Server, found 1
[INFO ] Manually validate the following entries Date:(YYYY-MM-DD) 2015-08-08 Time:(HH:MM:SS) 17:03:03
[SUCCESS ] Execute plugin check for Patch Check Prereq on exa01sw-ib2
[SUCCESS ] Pre-update validation on exa01sw-ib2
[INFO ] Starting upgrade on exa01sw-ib2 to 2.1.5_1. Please give upto 10 mins for the process to complete. DO NOT INTERRUPT or HIT CTRL+C during the upgrade
[SUCCESS ] Execute plugin check for Patching on exa01sw-ib2
[SUCCESS ] Load firmware 2.1.5_1 onto exa01sw-ib2
[SUCCESS ] Disable Subnet Manager on exa01sw-ib2
[SUCCESS ] Verify that /conf/configvalid is set to 1 on exa01sw-ib2
[SUCCESS ] Set SMPriority to 5 on exa01sw-ib2
[INFO ] Rebooting exa01sw-ib2. Wait for 240 secs before continuing
[SUCCESS ] Reboot exa01sw-ib2
[SUCCESS ] Restart Subnet Manager on exa01sw-ib2
[INFO ] Starting post-update validation on exa01sw-ib2
[SUCCESS ] Inifiniband switch exa01sw-ib2 is at target patching level
[SUCCESS ] Verifying host details in /etc/hosts and /etc/sysconfig/network-scripts/ifcfg-eth[0,1] for exa01sw-ib2
[SUCCESS ] Verifying that exa01sw-ib2 has at least 1 NTP Server, found 1
[INFO ] Manually validate the following entries Date:(YYYY-MM-DD) 2015-08-08 Time:(HH:MM:SS) 17:19:42
[SUCCESS ] Firmware verification on InfiniBand switch exa01sw-ib2
[SUCCESS ] Execute plugin check for Post Patch on exa01sw-ib2
[SUCCESS ] Post-check validation on IBSwitch exa01sw-ib2
[SUCCESS ] Update switch exa01sw-ib2 to 2.1.5_1

[INFO ] ———- Starting with InfiniBand Switch exa01sw-ib3
[SUCCESS ] Disable Subnet Manager on exa01sw-ib3
[SUCCESS ] Copy firmware packages to exa01sw-ib3
[SUCCESS ] exa01sw-ib3 is at 2.1.3-4. Meets minimal patching level 2.1.3-4
[SUCCESS ] Verifying that /tmp has 120M in exa01sw-ib3, found 138M
[SUCCESS ] Verifying that / has 80M in exa01sw-ib3, found 199M
[SUCCESS ] Verifying that exa01sw-ib3 has 120M free memory, found 237M
[SUCCESS ] Verifying host details in /etc/hosts and /etc/sysconfig/network-scripts/ifcfg-eth[0,1] for exa01sw-ib3
[SUCCESS ] Verifying that exa01sw-ib3 has at least 1 NTP Server, found 1
[INFO ] Manually validate the following entries Date:(YYYY-MM-DD) 2015-08-08 Time:(HH:MM:SS) 17:24:40
[SUCCESS ] Execute plugin check for Patch Check Prereq on exa01sw-ib3
[SUCCESS ] Pre-update validation on exa01sw-ib3
[INFO ] Starting upgrade on exa01sw-ib3 to 2.1.5_1. Please give upto 10 mins for the process to complete. DO NOT INTERRUPT or HIT CTRL+C during the upgrade
[SUCCESS ] Execute plugin check for Patching on exa01sw-ib3
[SUCCESS ] Load firmware 2.1.5_1 onto exa01sw-ib3
[SUCCESS ] Disable Subnet Manager on exa01sw-ib3
[SUCCESS ] Verify that /conf/configvalid is set to 1 on exa01sw-ib3
[SUCCESS ] Set SMPriority to 5 on exa01sw-ib3
[INFO ] Rebooting exa01sw-ib3. Wait for 240 secs before continuing
[SUCCESS ] Reboot exa01sw-ib3
[SUCCESS ] Restart Subnet Manager on exa01sw-ib3
[INFO ] Starting post-update validation on exa01sw-ib3
[SUCCESS ] Inifiniband switch exa01sw-ib3 is at target patching level
[SUCCESS ] Verifying host details in /etc/hosts and /etc/sysconfig/network-scripts/ifcfg-eth[0,1] for exa01sw-ib3
[SUCCESS ] Verifying that exa01sw-ib3 has at least 1 NTP Server, found 1
[INFO ] Manually validate the following entries Date:(YYYY-MM-DD) 2015-08-08 Time:(HH:MM:SS) 17:41:09
[SUCCESS ] Firmware verification on InfiniBand switch exa01sw-ib3
[SUCCESS ] Execute plugin check for Post Patch on exa01sw-ib3
[SUCCESS ] Post-check validation on IBSwitch exa01sw-ib3
[SUCCESS ] Update switch exa01sw-ib3 to 2.1.5_1
[INFO ] InfiniBand Switches ( exa01sw-ib2 exa01sw-ib3 ) updated to 2.1.5_1
[SUCCESS ] Overall status

—– InfiniBand switch update process ended Sat Aug 8 17:45:23 BRT 2015 —–
2015-08-08 17:45:23 -0300 ++++++++++++++++++ Logs so far end ++++++++++
2015-08-08 17:45:23 -0300 1 of 1 :SUCCESS: DONE: Upgrade InfiniBand switch(es) to 2.1.5-1.
================PatchMgr run ended 2015-08-08 17:45:23 -0300 ===========


  • CELL NODES UPGRADE:

For this one, you need to execute -reset_force and -cleanup procedures before upgrade, as below:

[root@ndcing01db01 patch_12.1.2.1.2.150617.1]# ./patchmgr -cells /root/cell_group -reset_force
================PatchMgr run started 2015-08-08 00:21:13 -0300 ===========
With arguments: -cells /root/cell_group -reset_force
2015-08-08 00:21:13 -0300 [INFO] pid file: /var/log/exadatatmp/_EXA_AXE_patchmgr.lock : 72003
PID TTY TIME CMD
[INFO] Reset force was successful.
2015-08-08 00:21:18 -0300 :DONE: reset_force
================PatchMgr run ended 2015-08-08 00:21:18 -0300 ===========

[root@ndcing01db01 patch_12.1.2.1.2.150617.1]# ./patchmgr -cells /root/cell_group -cleanup
================PatchMgr run started 2015-08-08 00:21:45 -0300 ===========
With arguments: -cells /root/cell_group -cleanup
2015-08-08 00:22:50 -0300 :Working: DO: Cleanup …
2015-08-08 00:22:51 -0300 ++++++++++++++++++ Logs so far begin ++++++++++
[INFO] Reset force was successful.
2015-08-08 00:22:51 -0300 ++++++++++++++++++ Logs so far end ++++++++++
2015-08-08 00:22:51 -0300 :SUCCESS: DONE: Cleanup
================PatchMgr run ended 2015-08-08 00:22:51 -0300 ===========

After that, you are ready to patch the cells in a rolling fashion:

[root@ndcing01db01 patch_12.1.2.1.2.150617.1]# ./patchmgr -cells /root/cell_group -patch -rolling
================PatchMgr run started 2015-08-08 00:26:02 -0300 ===========
With arguments: -cells /root/cell_group -patch -rolling
2015-08-08 00:27:07 -0300 :Working: DO: Check cells have ssh equivalence for root user. Up to 10 seconds per cell …
2015-08-08 00:27:07 -0300 ++++++++++++++++++ Logs so far begin ++++++++++
2015-08-08 00:27:08 -0300 ++++++++++++++++++ Logs so far end ++++++++++
2015-08-08 00:27:08 -0300 :SUCCESS: DONE: Check cells have ssh equivalence for root user.
2015-08-08 00:27:11 -0300 :Working: DO: Initialize files, check space and state of cell services. Up to 1 minute …
2015-08-08 00:27:28 -0300 ++++++++++++++++++ Logs so far begin ++++++++++
.
.
.

I didn’t posted the whole log as this is a big one, but it is here so feel free to take a look at it. And finally we went to database nodes.

  • DB NODES UPGRADE:

[root@exa01db01 5.150701]# ./dbnodeupdate.sh -u -l /u01/install/quarterly_full_jul2015/21339383/Infrastructure/12.1.2.1.2/ExadataDatabaseServer_OL6/p21151982_121212_Linux-x86-64.zip -s

After this procedure thee server get reboot and it takes a while to bring up. If everything is fine with the patching, complete the patching:

[root@exa01db01 5.150701]# ./dbnodeupdate.sh -c

That’s it guys, upgrade finished successfully, everything went smoothly. Hope you enjoyed it!

Storage Exadata: Protocolo iDB

Salve galera do mal! Eis que estou aqui de volta pra falar mais um assunto da série Storage Exadata. Irei abordar hoje o Intelligent Database Protocol, para os íntimos apenas iDB. Este protocolo é único e somente pode ser encontrado em servidores com storage Exadata e isso não é a toa. O protocolo está implementado no kernel do banco de dados Oracle e foi construído sobre o padrão de indústria RDS (Reliable Datagram Sockets versão 3) e executa sobre a Infiniband ZDP (Zero-Loss Zero-Copy Datagram Protocol) que é uma implementação cópia zero (Zero-Copy) do protocolo RDS, com isso é eliminada a desnecessária cópia de blocos, permitindo maior eficiência na rede.

É graças a este protocolo utilizado em comunicação entre os storage servers e os database nodes que é possível identificar o tipo de I/O que é enviado pelo servidor de banco de dados, por exemplo, seria impossível ter o benefício de cell offloading ou Smart Scan sem este protocolo. Dentro do servidor de storage, estão em execução três principais processos: CELLSRV; Management Server; e Restart Server. Em especial sobre o protocolo iDB, o processo CELLSRV é responsável pelas requisições de I/O efetuadas via iDB para poder efetuar as tratativas inteligentes como o cell offloading.

Foi aqui, no meu ponto de vista, que a Oracle acertou na construção de um hardware que entendesse o que o banco de dados está executando, sem isso nós teríamos apenas servidores ODA (Oracle Database Appliance) no mercado. E é por isso que o Exadata Storage Server é o futuro dos Engineered Systems e será muito difícil novos hardwares tomarem o seu lugar. Pois hardware e inteligência em infraestrutura todo mundo consegue copiar ou aprimorar muito rápido, porém software demora um pouco mais. Galera vou ficando por aqui, aquele abraaaaaaaa.

Overload em Pacotes

Salve, salve, pessoal! Hoje passando por aqui para deixar um post que trata do assunto de Overload em subprogramas nos banco de dados Oracle. Esta técnica é mais conhecida pelos desenvolvedores de PL/SQL e serve para viabilizar a interação do usuário com a chamada de uma função ou procedure dentro de um pacote. Sendo assim, um pacote que encontra-se “sobrecarregado” possui a características de permitir o usuário efetuar a chamada sem necessitar estar amarrado a um tipo de dado.

Alguns pacotes nativos do Oracle possuem essa característica e nós nem nos demos conta. Você já parou para perceber que quando você executa a instrução DBMS_OUTPUT.PUT_LINE(VAR_01) , você nunca liga para o tipo de dado que será colocado na variável VAR_01. Se será um número, uma string ou uma data? Imagine que para um mesmo código, você quer dar a possibilidade ao usuário de efetuar a chamada passando um único parâmetro que pode ser numérico, caracter, LOB ou até mesmo um boolean. É para isso que serve o Overload.

O exemplo que irei exibir é bem simples, portanto, no código abaixo será criado o pacote sobrecarga com os procedimentos execucao que escreverá o valor na tela, referente a cada tipo de chamada. Vejamos abaixo:

pack.sobrecarga

packbody.sobrecarga

sobrecarga.execucao

Percebam que as variáveis declaradas em cada um dos procedimentos são diferentes, assim como os códigos que estão dentro destes. Isso foi feito propositalmente, até mesmo para exemplificar melhor para vocês. Quando forem criar códigos como este, entendam também que existem algumas restrições no uso, entre estas:

  • Subprogramas Standalones não podem ser configuradas, porém você pode utilizar de overload em uma rotina declarada dentro de um bloco PL/SQL anônimo;
  • Subprogramas que diferem somente pelo modo, ou seja, você não pode criar um subprograma com um mesmo tipo porém em um é entrada e no outro saída;
  • Subprogramas com subtipos diferentes, ou seja, um subprograma com overload não pode possuir uma chamada com uma variável do tipo integer e outra real;
  • Subprogramas que diferem somente no tipo de retorno;

Se estas premissas forem cumpridas, você terá configurado com êxito o seu código. Esta técnica pode ser utilizada também em blocos agrupados e métodos de tipo. Bom pessoal, por hoje é só! Aquele abraaaaaaaaaa!

Error ‘resource ora.driver.afd does not exist’

Hi guys! Recently I’ve being through an issue after a recently PSU has being applied on an environment that has AFD configured and working. The error that is being experienced when you are trying to check the state or configure the AFD which is ASMCMD-9527: AFD is loaded, but resource ora.driver.afd does not exist. So the point is that I didn’t find any official document that address this issue so what I’m doing here is that I’m following the same steps when you applied a PSU on a grid infrastructure that has ACFS drivers loaded (you need first to remove ACFS drivers and after that PSU has being applied, you can load the ACFS drivers back againd). There is a picture bellow that shows the error when you try to configure/deconfigure AFD after you applied the PSU.

AFD_01

So as you can see from the error message, the ACFS driver is the one that is preventing you from configure/deconfigure the AFD on the server, now lets go into the simple steps to fix it. Bring the Oracle Restart down, in order to uninstall the ACFS and AFD drivers, then just re-install them:

AFD_02AFD_05

All the steps were performed using a privileged user (root), after that you can check that the AFD was configured correctly.

AFD_06

So that’s it guys, I hope it helps you! See ya!

Configure AFD on a new server

Hi guys, I’ve seen some fellows complaining about how to configure AFD on a new server. Well, actually Oracle doesn’t mention no way how to do this, but I’ll show you bellow the way to go. There are simple tasks to perform but the most important of them is to apply PSU 1 or later on it, cause AFD may cause your system to crash MOS: 12.1.0.2 Minimum PSU Level to Configure ASM Filter Driver (AFD) (Doc ID 1943048.1). There are two ways of doing this, the first one would be to deploy a Stand-alone Grid Infrastructure as usual and them migrate the disks to AFD and the second one is to perform a software only install and configure it from the begging. On this post, I’ll mention about the second choice which is the one that Oracle doesn’t mention about it.

First thing, perform a software only install on your server. After the installation has finished you need to configure the grid infrastructure as a Stand-alone, so pick the first option and run it as root:

standalone.start

Then start the ora.cssd resource, after that add and start both listener and the ASM instance:

cssd.listener.asm

Now you are good to go to configure the ASM Filter Driver and label the devices which you want to use, but before first you need to stop the Oracle Restart. Also, you need to use root user in order to configure AFD (it gives you an error but you can ignore it):

afd_configure

And for the end, apply the last PSU in order to avoid your system from crashing:

afd_configure

That’s it, now you have a fresh new server configured with AFD. See you!!