2013-07-26

Could You Live Like a DBA?

Have you ever wondered how everyday life would pan out if you took the DBA approach to life's events? Here are a few thoughts.

Backups: You have a nightly backup routine. This means a differential backup every night, when you tell your spouse everything that has happened during the day in minute detail, and a full backup on weekends when you tell them everything that has happened during the week in your entire life. I Don't think either of you would sleep much.

Replication: You are on the phone continuously, telling your twin sibling at a remote location everything that you are experiencing. Everything. What you see, hear, smell, eat, work on etc. Your twin, of course, has no life of her own, and is living vicariously through you, ever prepared for you to drop dead so she can take over. Talk about sibling rivalry.

Restores: Remember that nightly backup you transferred to your spouse earlier? You periodically and randomly ask your spouse to repeat some of those conversations back to you, ensuring no detail is skipped, so you can confirm that the backup is working. Marriage counselor's number handy?

Scans: When you go looking for the soy sauce in your pantry, you read the label on every single item in there: all the spices, all the jars and bottles, every bag of chips, every bottle of juice and soda, everything, because the items are arranged in no particular order. It takes you a few hours to put together one meal, and your kids have gone to bed hungry.

Seeks: After scanning through your pantry multiple times and getting tired of how long it takes each time, you decide to rearrange all the items in order by alphabet. Now you can retrieve the soy sauce in no time. The kids are happy again.

Fragmentation and Re-indexing: Over time, as your spouse adds items to the pantry at random locations (because he is not a DBA) and removes items leaving gaps on the shelves, the ordering gets so out of whack that you revert to going through all items again to find the soy sauce. Eventually, you decide to create an automated re-indexing plan by having your spouse rearrange everything by alphabet once a week. Divorce lawyer's number handy?

More Backups (because, you know, a DBA can never have enough backups): When you prepare a sandwich for your kid's school lunch, you also make a second sandwich. You pack the first one in your child's lunch box, and you courier the second one to arrive at school just in time for lunch. You don't tell anybody that you have a third one in your car, you know, just in case...

Adding Records: When you discuss baby names with your spouse for the upcoming addition to your family, you very briefly consider an integer for the name, and then even more briefly think that a tinyint would be more appropriate, before you think of the divorce lawyer and decide to shut up. You do, however, bring up theBobby Tables joke because you think it's funny. Your spouse does not.

Searching: You wonder on a daily basis why you can't just enter this into Google's search box: select * from closeby_restaurants where cuisine = 'chinese' and distance_from_home <= 2 miles and deliciousness = 'awesome' and service_time = 'quick'...

Normalization: When your spouse asks you for a copy of your shopping list before he goes shopping, you point him to the location of the original list instead because you do not want to create duplicates. He throws his hands up and leaves without your list. Your items never get bought.

Normalization 2: You do not have a driver's license, or a bank account, or a passport, or a mortgage in your name, because you are incapable of filling out any forms in duplicate, leave alone triplicate.

Set-based Operations: After the kids have gone to bed and you sit down with your spouse to watch some TV, you roll out all the TVs, desktops, laptops, tablets and phones in your house into the living room and fire up a different episode of your favorite show on each one at the same time, because you know that set-based operations are better than episode-by-agonizing-episode. The kids were very entertained when you did this with their cartoons. But the spouse - not so much.
If your domestic life isn't exactly a picture of happiness and you're getting grief from your spouse, kids, and siblings, now you know why. You're a DBA!

Taken from the editorials from http://www.sqlservercentral.com/ (http://www.sqlservercentral.com/articles/humor/99113/)

2007-12-04

The SQL / PLSQL developper mantra...

I read an article written by the fabulous Tom Kyte that says something that I think for many years :

I have a pretty simple mantra when it comes to developing database software, and I have written this many times over the years:

  • You should do it in a single SQL statement if at all possible.
  • If you cannot do it in a single SQL statement, do it in PL/SQL.
  • If you cannot do it in PL/SQL, try a Java stored procedure.
  • If you cannot do it in Java, do it in a C external procedure.
  • If you cannot do it in a C external procedure, you might want to seriously think about why it is you need to do it.

If you can do it in a single SQL statement, by all means do it in a single SQL statement. Do not waste time, energy, and CPU cycles writing procedural code that will run slower than regular SQL.

2007-10-04

Oracle 10g R2 RAC & iScsi on Windows Server 2003

You can read there a POC of Oracle 10g R2 RAC on a Windows Server 2003 using an iScsi device from Wasabi...

Oracle Agent 10g and multiple Oracle Inventories

Find Oracle documentation (b16242) at there
exactly at here


There, we find "14.2 Enabling Multi-Inventory Support for Configuration Management"
Every time you install an Oracle software product on a host computer, Oracle Universal Installer saves information about the software installation on your hard disk. The directories and files that contain this software configuration information are referred to as the Oracle Universal Installer inventory.

When you use Enterprise Manager to monitor your Oracle software installations, Enterprise Manager takes advantage of information saved in the Universal Installer inventory.
As it gathers information about the configuration of your host computer, by default, Enterprise Manager assumes that you have one Oracle Universal Installer inventory on the host. Specifically, Enterprise Manager recognizes the inventory that Oracle Universal Installer uses when you run the Universal Installer on the host.
However, in some cases, you may have more than one inventory. For example, you may have worked with Oracle Support to clone your Oracle software installations. For those cases, you can use the following procedure to be sure that Enterprise Manager can track and manage the software information in multiple inventories on the same host.

Caution:
Enabling support for multiple inventories is optional and available only for advanced users who are familiar with the Oracle Universal Installer inventory architecture and who have previously worked with multiple inventories on a managed host. This procedure is not required for hosts where normal installations have been performed.

To set up Enterprise Manager so it can read multiple inventories on a host:

  1. Locate the OUIinventories.add file in the following directory $ORACLE_HOME/_/sysman/config
    The Management Agent state listed in this example represents an installation for Database Control. For more information about the Management Agent state to use for other installations, see Section 14.2.1, "AGENT_HOME Versus AGENT_STATE Directories" .
  2. Open OUIinventories.add using a text editor.
    Instructions within the file describe the format to use when identifying multiple inventories, as well other techniques for mapping Oracle Homes.
  3. Carefully review the instructions within the file.
  4. Add entries to the file for each additional inventory on the managed host.
  5. Save your changes and close the file.

During its next collection of host configuration information, Enterprise Manager will start gathering software configuration information from the inventories that you identified in the OUIinventories.add file, in addition to the default inventory that Enterprise Manager normally collects.
Alternatively, to see the data gathered from the additional inventories before the next regularly-scheduled collection, navigate to the Host home page in the Grid Control Console, click the Configuration tab, and click the Refresh Data icon next to the page timestamp.

Note:
If there any irrecoverable problems during the collection of the default inventory (for example, if the inventory file or directory protections prevent Enterprise Manager from reading the inventory), inventories listed in OUIinventories.add file are also not collected.

If the Enterprise Manager is able to read the default inventory, but there is a problem reading an additional inventory listed in the OUIinventories.add file, Enterprise Manager issues a collection warning for those inventories. However, Enterprise Manager does collect the configuration information for the other inventories.


Before the script :

  1. note on the "(for example, if the inventory file or directory protections prevent Enterprise Manager from reading the inventory)"
    Be carrefull of this... The IAS guys that have installed some of our ias instances, have installed them with specific users/groups... and my user (part of the dba group) hasn't the rigths to read the ias files... and then my agent discovers nothing... :-(
  2. $HOME/OUIinventories.add.orig is the original copy of $ORACLE_HOME/sysman/config/OUIinventories.add
    Just to begin with a "clean" file each time we regenerate.... Even if original the file contains only comments... ;-)
  3. iagt10g@uxhades2da > cat $HOME/oraInventories.txt
    /opt/app/oracle/app/oradev8i/oraInventory
    /opt/app/oracle/app/oradev9i/oraInventory
    /opt/app/ias/oraInventory
    /opt/app/ias/infra/oraInventory
    /opt/app/ias/Inventory/oraInventoryForms
    /opt/app/ias/Inventory/oraInventoryA916dev1012
    /opt/app/ias/Inventory/oraInventoryA927dev1012
    /opt/app/ias/Inventory/oraInventoryAsInfradev1012
    /opt/app/ias/Inventory/oraInventoryas1012infra
    /opt/app/ias/Inventory/oraInventoryas1012mid
    /opt/app/ias/Inventory/oraInventoryrepca_101203
    /opt/app/ias/Inventory/oraInventorya918dev101202
    /opt/app/ias/Inventory/oraInventorya917dev101202
    /opt/app/ias/Inventory/oraInventorya916dev101202
    /opt/app/ias/Inventory/oraInventoryaCOMAD00D
    /opt/app/ias/Inventory/oraInventorydevappn1013
    /opt/app/ias/Inventory/oraInventoryA949dev101202

    iagt10g@uxhades1da > cat $HOME/oraInventories.txt
    /opt/app/ias/iagt10g/oraInventory
    /opt/oracle/app/iagt10g/oraInventory
    /opt/oracle/app/iagtacc/oraInventory
    /opt/oracle/app/iagtadm/oraInventory
    /opt/oracle/app/iagtdev/oraInventory
    /opt/oracle/app/oraacc10g/oraInventory
    /opt/oracle/app/oraacc8i/oraInventory
    /opt/oracle/app/oraacc9i/oraInventory
    /opt/oracle/app/oraadm10g/oraInventory
    /opt/oracle/app/oraadm8i/oraInventory
    /opt/oracle/app/oraadm9i/oraInventory
    /opt/oracle/app/oradev10g/oraInventory
    /opt/oracle/app/oradev8i/oraInventory
    /opt/oracle/app/oradev9i/oraInventory
    /opt/oracle/app/oraedu8i/oraInventory
    /opt/oracle/app/oraedu9i/oraInventory
    /opt/oracle/app/oratst8i/oraInventory


###############################################################
# Christian Clemmen - 2005/07 - v1.0 - creation
# info@infor-conseils.be
###############################################################
#!/bin/ksh

cp $HOME/OUIinventories.add.orig $ORACLE_HOME/sysman/config/OUIinventories.add

for inv in `cat $HOME/oraInventories.txt`
do
loc=$HOME/oraInst.`echo $inv | cut -d/ -f6`.loc
echo "inventory_loc=$inv" > $loc
echo "inst_group=dba" >> $loc
echo "inventory: $loc" >> $ORACLE_HOME/sysman/config/OUIinventories.add
done


In the $HOME, we have now:
-rw-r--r-- 1 iagt10g dba 68 May 23 08:16 oraInst.oraacc10g.loc
-rw-r--r-- 1 iagt10g dba 66 May 23 08:16 oraInst.iagtdev.loc
-rw-r--r-- 1 iagt10g dba 66 May 23 08:16 oraInst.iagtadm.loc
-rw-r--r-- 1 iagt10g dba 66 May 23 08:16 oraInst.iagtacc.loc
-rw-r--r-- 1 iagt10g dba 66 May 23 08:16 oraInst.iagt10g.loc
-rw-r--r-- 1 iagt10g dba 67 May 23 08:16 oraInst.oratst8i.loc
-rw-r--r-- 1 iagt10g dba 67 May 23 08:16 oraInst.oraedu9i.loc
-rw-r--r-- 1 iagt10g dba 67 May 23 08:16 oraInst.oraedu8i.loc
-rw-r--r-- 1 iagt10g dba 67 May 23 08:16 oraInst.oradev9i.loc
-rw-r--r-- 1 iagt10g dba 67 May 23 08:16 oraInst.oradev8i.loc
-rw-r--r-- 1 iagt10g dba 68 May 23 08:16 oraInst.oradev10g.loc
-rw-r--r-- 1 iagt10g dba 67 May 23 08:16 oraInst.oraadm9i.loc
-rw-r--r-- 1 iagt10g dba 67 May 23 08:16 oraInst.oraadm8i.loc
-rw-r--r-- 1 iagt10g dba 68 May 23 08:16 oraInst.oraadm10g.loc
-rw-r--r-- 1 iagt10g dba 67 May 23 08:16 oraInst.oraacc9i.loc
-rw-r--r-- 1 iagt10g dba 67 May 23 08:16 oraInst.oraacc8i.loc

Each of them contains something like :
iagt10g@uxhades1da > cat oraInst.oraacc8i.loc
inventory_loc=/opt/oracle/app/oraacc8i/oraInventory
inst_group=dba

Content of $ORACLE_HOME/sysman/config/OUIinventories.add after script:
inventory: /home/iagt10g/oraInst.iagt10g.loc
inventory: /home/iagt10g/oraInst.iagtacc.loc
inventory: /home/iagt10g/oraInst.iagtadm.loc
inventory: /home/iagt10g/oraInst.iagtdev.loc
inventory: /home/iagt10g/oraInst.oraacc10g.loc
inventory: /home/iagt10g/oraInst.oraacc8i.loc
inventory: /home/iagt10g/oraInst.oraacc9i.loc
inventory: /home/iagt10g/oraInst.oraadm10g.loc
inventory: /home/iagt10g/oraInst.oraadm8i.loc
inventory: /home/iagt10g/oraInst.oraadm9i.loc
inventory: /home/iagt10g/oraInst.oradev10g.loc
inventory: /home/iagt10g/oraInst.oradev8i.loc
inventory: /home/iagt10g/oraInst.oradev9i.loc
inventory: /home/iagt10g/oraInst.oraedu8i.loc
inventory: /home/iagt10g/oraInst.oraedu9i.loc
inventory: /home/iagt10g/oraInst.oratst8i.loc

Mount ISO - Virtual cdrom

There are excellent software packages that allow users to mount ISO files as virtual CD-ROMs.

Microsoft has a free, 60kb program that does it! Of course, it is not supported... and it's not as friendly as the software listed above; however, it works and it's free.

You can download it here


Here's the readme:
Readme for Virtual CD-ROM Control Panel v2.0.1.1

THIS TOOL IS UNSUPPORTED BY MICROSOFT PRODUCT SUPPORT SERVICES


System Requirements
===================
- Windows XP Home or Windows XP Professional

Installation instructions
=========================
1. Copy VCdRom.sys to your %systemroot%\system32\drivers folder.
2. Execute VCdControlTool.exe
3. Click "Driver control"
4. If the "Install Driver" button is available, click it. Navigate to the %systemroot%\system32\drivers folder, select VCdRom.sys, and click Open.
5. Click "Start"
6. Click OK
7. Click "Add Drive" to add a drive to the drive list. Ensure that the drive added is not a local drive. If it is, continue to click "Add Drive" until an unused drive letter is available.
8. Select an unused drive letter from the drive list and click "Mount".
9. Navigate to the image file, select it, and click "OK". UNC naming conventions should not be used, however mapped network drives should be OK.

You may now use the drive letter as if it were a local CD-ROM device. When you are finished you may unmount, stop, and remove the driver from memory using the driver control.

2007-04-19

Bug 5458753 SQL may execute in wrong schema

I found this issue this afternoon....
Waaw... completely crazy!!!!


This issue is very very rare in 10.1 but can occur
much more easily in 10.2.0.2 .

If different schemas have tables / views etc.. with identical names
and sessions from different schemas execute IDENTICAL SQL
statements then it is possible for such SQL sentences to get
executed against the wrong schema objects. This can result
in:
  • Wrong results from SELECT SQL
  • Logical data corruption for DML SQL
  • Unexpected errors such as ORA-2291 / ORA-1 from DML
  • Parse errors if the underlying table definition differs.
(eg: ORA-904 etc..)

eg:
Consider an application with seperate sessions executing
"select mycol from mytable where key=:B1" as user A and user B
where both users have a table called "MYTABLE".
In this case it is possible for user A to end up executing the
SQL against B.MYTABLE instead of against A.MYTABLE.

Workaround:
There is no simple workaround which can avoid this fully.
The issue can be avoided by prefixing object names with the schema name.
eg: In the above example change the SQL to use
"select mycol from A.mytable" for user A and
"select mycol from B.mytable" for user B.

If SQL cannot be changed then it can help to reduce shared pool
load (as the problem occurs when cursors are reloaded having
been aged out / invalidated). Using DBMS_SHARED_POOL.KEEP for
affected cursors can also help.



More info in Metalink note: 392673.1

2006-04-28

Invalid public synonyms on Oracle 10g

Don't know why but sometimes, some public synonyms on the dba_xxxx views become invalid...
I write a little script to regenerate them:

/******************************************
2006-04-18 - V1.0 - Creation of the script
(c) INFOR-Conseils
oracle _at_ infor-conseils.be
Regenerate invalid public synonyms
******************************************/
prompt system@&&database
conn system@&&database

set pages 0
set feed off
set linesize 1000
set trimsp on
spool _your_disk_\recrpubsyn.sql
select 'CREATE OR REPLACE PUBLIC SYNONYM 'object_name' for 'object_name';' from dba_objects where status = 'INVALID'and object_type = 'SYNONYM' and owner = 'PUBLIC';
spool off
@@_your_disk_\recrpubsyn.sql
host _the_remove_command_ _your_disk_\recrpubsyn.sql

exit;



_your_disk_ must be replaced by a path in your OS
e.g d:\ on Windows or /home/oracle on Linux/Unix.
The _the_remove_command_ must be replaced by del on Windows and by rm on Linux/Unix

2006-04-26

ORA-29531: no method get_crlf in class oracle/plsql/net/TCPConnection

When trying to send a mail with the oracle provided package UTL_SMTP you can get the error ORA-29531: no method get_crlf in class oracle/plsql/net/TCPConnection:

  • Error: ORA-29531: no method get_crlf in class oracle/plsql/net/TCPConnection
  • ORA-06512: at "SYS.UTL_TCP", line 678
  • ORA-06512: at "SYS.UTL_TCP", line 671
  • ORA-06512: at "SYS.UTL_SMTP", line 99
  • ORA-06512: at "SYS.UTL_SMTP", line 121
  • ORA-06512: at "S716.S724_PA_UTIL", line 2470
  • ORA-06512: at line 2


Metalink Note 160798.1 says:

UTL_SMTP Fails with 'ORA-29531 no Method get_crlf' After Applying Patchset 8.1.7.2 or Above

  • fact: Oracle Server - Enterprise Edition 8.1.7
  • symptom: Error when using UTL_SMTP
  • symptom: ORA-29531: no method get_crlf in class oracle/plsql/net/TCPConnection
  • symptom: ORA-6512: at "SYS.UTL_TCP"
  • symptom: ORA-6512: at "SYS.UTL_SMTP"
  • change: patchset 8.1.7.2 or above applied
  • cause: The steps in the patchset readme for 8172+ does not include re-running initplsj or manually loading this jar.
  • Original problem is caused by Bug 1721293 UTL_SMTP FAILS WITH ORA-29531 .

fix:

1. connect as SYS
2. run $ORACLE_HOME/rdbms/admin/initplsj.sql

Results of the script:
SQL*Plus: Release 8.1.7.0.0 - Production on Wed Apr 26 10:15:39 2006
(c) Copyright 2000 Oracle Corporation. All rights reserved.

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production
SQL> @$ORACLE_HOME/rdbms/admin/initplsj.sql
Call completed.
Call completed.
SQL> exit


And now all is running succesfully :-)


2006-04-25

La dernière mise à jour de Microsoft renferme bien des surprises!

Le patch estampillé MS06-015 semble causer des soucis à certaines personnes. Corrigeant une faille dans l’Explorer de Windows, le patch provoque plusieurs problèmes dont :

  • L’impossibilité d’accéder à certains dossiers particuliers comme « Mes Documents » ou « Mes Images »
  • Un arrêt du fonctionnement des applications Office lorsque l’on essaye d’ouvrir ou enregistrer un fichier dans « Mes Documents »
  • L’impossibilité d’ouvrir un fichier Office depuis « Mes Documents »
  • Un arrêt de certains logiciels lorsque l’on utilise la fonction Fichier -> Ouvrir
  • L’impossibilité d’afficher une page dans Internet Explorer après avoir écrit dans la barre d’adresses
  • Utiliser la fonction du menu contextuel « Envoyer vers » n’a aucun effet
  • La touche « + » dans l’explorateur Windows n’a aucun effet
  • Certaines applications cessent de fonctionner lorsque l’on ouvre ou enregistre des fichiers dans « Mes Documents »

Ces problèmes peuvent être causés (ce n’est pas automatique) par un nouvel exécutable nommé VERCLSID.EXE qui doit valider les extensions du shell avant leur mise en marche. Sur certaines machines, dans certaines conditions, cet exécutable cesse en fait de répondre et provoque, par effet boule de neige, un arrêt du fonctionnement de certaines applications, notamment :

  • Le logiciel Share-to-Web de HP : le service Hpgs2wnd.exe se retrouve notamment avec le logiciel HP PhotoSmart, les imprimantes DeskJet qui incluent un lecteur de cartes, les scanners HP, certains graveurs de CD et les appareils photo de la marque.
  • Kerio Personal Firewall qui peut empêcher VERCLSID.EXE de fonctionner en essayant de marquer le fichier lorsqu’il est appelé.

Ce problème se retrouve sur toutes les versions de Windows depuis 2000 (XP et 2003 Server) et peut être réglé pour les deux applications nommées ci-dessus.


Pour HP Share-to-Web :
  1. Lancer l’éditeur de registre (regedit)
  2. Se rendre à la clé : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Shell Extensions\Cached
  3. Faire un clic droit sur la clé « Cached» et cliquer sur Nouveau -> Valeur DWord et la nommer: {A4DF5659-0801-4A60-9607-1C48695EFDA9} {000214E6-0000-0000-C000-000000000046} 0x401
  4. Donner à cette valeur le chiffre 1
  5. Fermer l’éditeur puis relancer la machine ou tuer le processus VERCLSID.EXE dans le gestionnaire des tâches

Pour le firewall Kerio, il faut configurer le logiciel pour que VERCLSID.EXE puisse s’exécuter sans être contrôlé.

Source Microsoft
Article original ici